Как выделить цветом участки убывания и возрастания на линейном графике?

Я строю линейный график в 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 шт):

Автор решения: Vitalizzare

Построим колонку убывающих данных из 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())

Final Plot with Correct Coloring


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. Результатом её выполнения будут два столбца с убывающими участками, между которыми обязательно будут пустые ячейки:

результат работы формулы

→ Ссылка