Как считать только отрицательный, справа налево, только по порядку, останавливать счет при появлении положительного значения

Question

В электронной таблице Excel у меня есть шесть ячеек (A1 - F1), содержащих числа.  Я хочу посчитать последовательные (смежные) отрицательные значения справа налево. например,

A1=2,  B1=6,  C1=-3, D1=-5, E1=-6, F1=-9  : Answer G1=4  (four continuous negative values)
A1=2,  B1=6,  C1=3,  D1=5,  E1=-6, F1=-9  : Answer G1=2
A1=2,  B1=-6, C1=3,  D1=5,  E1=-6, F1=-9  : Answer G1=2
A1=-2, B1=-6, C1=-3, D1=5,  E1=6,  F1=-9  : Answer G1=1
A1=-2, B1=-6, C1=-3, D1=3,  E1=-6, F1=-9  : Answer G1=2

Или, похожая ситуация, но со словами, я хочу посчитать, сколько последовательных ячеек содержат RED , начиная справа:

A1=GREEN, B1=GREEN, C1=GREEN, D1=GREEN, E1=RED,   F1=RED  : Answer G1=2
A1=GREEN, B1=GREEN, C1=RED,   D1=GREEN, E1=RED,   F1=RED  : Answer G1=2
A1=GREEN, B1=RED,   C1=RED,   D1=GREEN, E1=GREEN, F1=RED  : Answer G1=1
A1=GREEN, B1=RED,   C1=RED,   D1=RED,   E1=RED,   F1=RED  : Answer G1=5

Я хочу считать справа налево, пока не сломается конкретная серия.

Answer 1

Тип

=6-MAX(IF(A1:F1>=0, COLUMN(A1:F1), 0))

в G1 и нажмите Ctrl+Shift+Enter.  Учитывая ваш первый пример (A1 = 2, B1 = 6, C1 = −3, D1 = −5, E1 = −6, F1 = −9), подвыражение IF(X>=0, COLUMN(X), 0) создает виртуальный массив, который выглядит как (1, 2, 0, 0, 0, 0) - заменяя неотрицательные числа на номера их столбцов и заменяя отрицательные числа на ноль.  Взяв MAX этого, мы получим последний (самый правый) столбец, который содержит неотрицательное число - так что все, что находится за этим, должно быть отрицательным.  Вычитание так, что из 6 говорит нам, сколько (последовательных) отрицательных чисел есть в правом конце ряда.

Ctrl+Shift+Enter делает его формулой массива.  Это необходимо для создания виртуального массива подобного типа.

Для альтернативной версии вопроса решение практически такое же:

=6-MAX(IF(A1:F1<>"RED", COLUMN(A1:F1), 0))

        

Answer 2

Для формулы, которую можно ввести как нормальную формулу, попробуйте:

=IFERROR(LOOKUP(2,1/(A1:F1>=0),{5,4,3,2,1,0}),6)

LOOKUP соответствует последней записи, которая проверяет TRUE Затем мы возвращаем соответствующий счетчик из массива.

Однако, если все значения <0 , LOOKUP возвращает ошибку, поэтому у нас должно быть шесть.