Получение значения в одном столбце из формулы в другом столбце в Excel

Question

У меня есть таблица Excel, которая выглядит следующим образом:

Date         Original       Actual
11/10/10     15             12
12/10/10     12             10
...
24/10/10     2              1
25/10/10     -2             0

Я хочу знать дату, когда исходный столбец становится отрицательным, 25/10/10 в этом примере.

Мне удалось вычислить значение, используя эту формулу: =MAX(IF(D63:D91 <0,D63:D91)) которая говорит мне -2.

Я просто хочу, чтобы он посмотрел на столбец даты, где первоначальное значение = -2, и сказал мне эту дату, но я не могу найти способ сделать это вообще!

У кого-нибудь есть идеи?

Answer 1

Поскольку LOOKUP не годится, потому что он требует упорядоченного набора, это будет

=INDEX(A63:A91,MATCH(MAX(IF(D63:D91<0,D63:D91)),D63:D91,0))

, вводится как формула массива (Ctrl+Shift+Enter) (при условии, что столбец A содержит даты).

Answer 2

К сожалению, решение GSerg не работает, если есть другие отрицательные значения больше -2, например, -1, далее по таблице.

Чтобы определить первый экземпляр отрицательного значения (в соответствии с датой) и соответствующую дату, я бы предложил:

{=MIN(IF(D63:D91<0,A63:A91,""))}

Обратите внимание, что даты в столбце А не нужно сортировать.

Если вместо этого нужен самый первый экземпляр отрицательной записи независимо от даты, я бы предложил:

{=INDIRECT("A"&MIN(IF(D63:D91<0,ROW(63:91),"")))}

или, если столбец A должен быть динамическим:

{=INDEX(A$1:A91,MIN(IF(D63:D91<0,ROW(63:91),"")))}

надеюсь это поможет.

Answer 3

Я бы добавил скрытый столбец перед датами, поставив с if 1 1, если предыдущее значение в исходном столбце является положительным, а текущее значение в исходном столбце отрицательным, а 0 в противном случае.

И выполните поиск по вертикали на 1 в новом столбце.

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