Как определить столбец, где значение падает ниже 0

Question

Я пытаюсь выяснить формулу, чтобы определить, в каком столбце значение падает ниже 0.

У меня есть тысячи строк и десятки столбцов, так что делать это вручную не вариант

Так что, если мои столбцы и значения следующие:

    A   B   C   D
1   Jul Aug Sep Oct
2   25  10  5   -2
3   10  2   -6  -10

Поэтому для строки 2 я хотел бы вернуть результат "Октябрь" (или 4 в качестве 4-го столбца), поскольку в этом месяце значение падает ниже 0. Для строки 3 я хотел бы вернуть значение "Sep" (или 3 в качестве 3-го столбца).

Есть идеи?

Answer 1

Вы можете сделать это с помощью формулы массива. В строке 2 выберите ячейку, в которой вы хотите формулу, вставьте следующую строку в строку формул и нажмите Ctrl+Shift+Enter.

=INDEX($A$1:$D$1,MIN(IF(A2:D2<0,COLUMN(A2:D2))))

Для справки A1:D1 - это диапазон заголовков столбцов (месяцы), а A2:D2 - значения в строке. Эта формула ищет значения меньше 0, а затем принимает значение с наименьшим номером столбца. Затем он возвращает заголовок месяца в этом номере столбца.

Эта формула может быть заполнена до всех строк.

РЕДАКТИРОВАТЬ:
Если ваши данные не начинаются со столбца А, вам придется внести небольшую корректировку в формулу, чтобы она заработала. В формуле все остается неизменным, за исключением того, что первый аргумент функции INDEX должен быть расширен до столбца A. Например, если ваши данные начинаются в столбце C, вы должны использовать следующую формулу (вводится как формула массива).

=INDEX($A$1:$F$1,MIN(IF(C2:F2<0,COLUMN(C2:F2))))

Причина этого заключается в том, что второй аргумент функции INDEX указывает позицию в массиве, а не обязательно номер столбца. Это различие стирается, если вы просто расширяете массив до столбца A (то есть позиция в массиве будет равна номеру столбца).

Answer 2

Возможно, немного проще использовать INDEX/MATCH, например, эту формулу массива в AV10

=INDEX(AW$1:BM$1,MATCH(TRUE,AW10:BM10<0,0))

подтверждено с помощью CTRL+SHIFT+ENTER

или, если вы предпочитаете избегать CSE, добавьте еще одну функцию INDEX, как эта, и формула может быть введена "нормально"

=INDEX(AW$1:BM$1,MATCH(TRUE,INDEX(AW10:BM10<0,0),0))

Если чисел <0 нет, это приведет к ошибке # N/A ...... или вы можете использовать функцию IFERROR для замены на какой-либо текст, например

=IFERROR(INDEX(AW$1:BM$1,MATCH(TRUE,INDEX(AW10:BM10<0,0),0)),"No negative numbers")