- ВКонтакте
- РћРТвЂВВВВВВВВнокласснРСвЂВВВВВВВВРєРСвЂВВВВВВВВ
- РњРѕР№ Р В Р’В Р РЋРЎв„ўР В Р’В Р РЋРІР‚ВВВВВВВВРЎР‚
- Viber
- Skype
- Telegram
Как выделить цветом участки убывания и возрастания на линейном графике?
Я строю линейный график в Google Spreadsheets. Что нужно сделать, чтобы участки убывания и возрастания отображались разными цветами?
Я пытаюсь это сделать, создавая вспомогательные столбцы с помощью формул и перекрывая ими исходный график (здесь в колонке B
находятся отображаемые данные, см. ниже, а формулы составлены так, как если бы они находились в ячейках C2
и D2
):
=if(($B2>$B3)+($B2<$B1), $B2, na())
=if(($B2<=$B3)*($B3>$B4)+($B1<=$B2)*($B2>$B3), $B2, na())
Это текущий результат с неправильно окрашенными участками 12-13, 23-24, 28-29 (трудность представляют зубчатые участки вида [1, 2, 1, 2, 1]
, где возрастание и убывание сменяются на каждом шаге):
Хотелось бы избежать работы со скриптами, создав формулу, которая, например, разделяла бы нисходящие тренды по отдельным столбцам, или добавляла бы промежуточный пустой полушаг между соседними трендами. Не знаю, как это сделать.
Данные, с которыми работаю:
Step | Value |
---|---|
1 | 9577 |
2 | 9601 |
3 | 9454 |
4 | 9329 |
5 | 9422 |
6 | 9238 |
7 | 9517 |
8 | 9589 |
9 | 9603 |
10 | 9564 |
11 | 9610 |
12 | 9623 |
13 | 9616 |
14 | 9633 |
15 | 9249 |
16 | 9525 |
17 | 9549 |
18 | 9498 |
19 | 9391 |
20 | 9512 |
21 | 9530 |
22 | 9550 |
23 | 9565 |
24 | 9541 |
25 | 9568 |
26 | 9567 |
27 | 9571 |
28 | 9595 |
29 | 9527 |
30 | 9621 |
31 | 9566 |
Ответы (1 шт):
Построим колонку убывающих данных из B
с помощью формулы:
=IF(OR($B1>$B2, $B2>$B3), $B2, NA())
В этой формуле мы используем данные из B
если они находятся на ниспадающем треке, а в остальных случаях - NA
. При отображении на графике, у этой линии будут разрывы на пустых ячейках. Но если между соседними ниспадающими треками нет пустых ячеек (т.е. между ними ровно один неубывающий шаг), то эти линии будут соединятся в одну непрерывную, перекрыв своим цветом неубывающий график. Чтобы этого не происходило, разделим убывающие отрезки по четности их позиции. Для этого создадим колонку с точками начала убывающих отрезков. По ней строим накопительные суммы, которые нумеруют убывающие треки. Последние разделяем на два столбца по четности номеров. В этих столбцах между соседними участками всегда будут пустые ячейки.
Рассмотрим пример с зубчатыми данными [3, 2, 5, 4, 6, 3]
. Формулы в первой и последней строках сокращены, чтобы не нагромождать проверку типов за пределами таблицы, но в целом они однотипны и заполняются протягиванием ячеек. Для построения графика используются столбцы Value
, Odd Slope
, Even Slope
, где первому указываем синий цвет, а следующим двум - красный:
Step | Value | Decreasing | Starting Points | CUMSUM | Odd Slope | Even Slope |
---|---|---|---|---|---|---|
1 | 3 | =if($B2>$B3, $B2, na()) | =$B2>$B3 | =0+$D2 | =if(mod($E2, 2), $C2, na()) | =if(not(mod($E2, 2)), $C2, na()) |
2 | 2 | =if(or($B2>$B3, $B3>$B4), $B3, na()) | =and($B2<=$B3, $B3>$B4) | =$E2+$D3 | =if(mod($E3, 2), $C3, na()) | =if(not(mod($E3, 2)), $C3, na()) |
3 | 5 | =if(or($B3>$B4, $B4>$B5), $B4, na()) | =and($B3<=$B4, $B4>$B5) | =$E3+$D4 | =if(mod($E4, 2), $C4, na()) | =if(not(mod($E4, 2)), $C4, na()) |
4 | 4 | =if(or($B4>$B5, $B5>$B6), $B5, na()) | =and($B4<=$B5, $B5>$B6) | =$E4+$D5 | =if(mod($E5, 2), $C5, na()) | =if(not(mod($E5, 2)), $C5, na()) |
5 | 6 | =if(or($B5>$B6, $B6>$B7), $B6, na()) | =and($B5<=$B6, $B6>$B7) | =$E5+$D6 | =if(mod($E6, 2), $C6, na()) | =if(not(mod($E6, 2)), $C6, na()) |
6 | 3 | =if($B6>$B7, $B7, na()) | =false() | =$E6+$D7 | =if(mod($E7, 2), $C7, na()) | =if(not(mod($E7, 2)), $C7, na()) |
P.S. Изложенную выше логику можем записать в виде формулы:
=let(r, count(B2:B), data, indirect("B2:B"&(r+1)),
__doc__, "For data placed in B2:B get declining slopes separated in two columns by their order number",
gt_next, lambda(i, index(data,i) > index(data,i+1)), _1, "greater then next",
lt_prev, lambda(i, index(data,i) < index(data,i-1)), _2, "less then previous",
is_declining, lambda(i, or(gt_next(i), lt_prev(i))), _3, "is in the beginning, middle or in the end of declining slope",
declining_marks, vstack(gt_next(1), map(sequence(r-2, 1, 2), is_declining), lt_prev(r)),
slopes, arrayformula(if(declining_marks, data,)), _4, "Select data placed on the declaining slopes",
starting_points, vstack(gt_next(1), map(sequence(r-2, 1, 2), lambda(i, and(gt_next(i), not(lt_prev(i))))), false()),
_5, "Assign order number to each declining slope",
cumsum, makearray(r, 1, lambda(x, y, sum(arrayformula(n(chooserows(starting_points, sequence(x))))))),
_6, "Place the declining slopes left or right based on the oddity of their order number",
map(slopes, cumsum, lambda(v, n, if(isodd(n), hstack(v, ), hstack(, v)))))
Предполагая, что индексы и данные находятся в первых двух столбцах, формулу размещаем в ячейке C2
. Результатом её выполнения будут два столбца с убывающими участками, между которыми обязательно будут пустые ячейки: