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

Question

Пример:

Имеется электронная таблица с четырьмя столбцами - A, B, C и D, а строка 1 является строкой заголовка ...

А2 = 1
B2 =(случайное положительное целое число)
C2 =(Другое случайное положительное целое число)

А3 = A2+1
В3 = В2-С2
А4 = А3+1
В4 = Б3-С2

...

D2 - проблемная ячейка. В D2 я хочу вернуть значение для столбца A, которое соответствует первому экземпляру значения в столбце B, который равен <= 0.

Answer 1

В том же духе, что и в ответе Пола, я бы использовал Index and Match

=INDEX($A:$A,MATCH(0,$B:$B,-1)+1)

1. Используйте целые столбцы, чтобы их не нужно было корректировать по количеству данных в столбце.
2. Используйте Index а не Offset так как Offset изменчиво, а Index - нет. (Энергозависимые функции пересчитываются при расчете каждого листа, в то время как энергонезависимые функции пересчитываются только при изменении ссылочных данных. Слишком много изменчивых функций может замедлить работу Excel.)

Answer 2

Вы можете использовать MATCH, чтобы получить то, что вам нужно.

MATCH(0,B2:B6,-1)+1 просматривает B2:B6 ищет первое значение, равное или меньшее числа 0 и возвращает его относительное положение. Он индексируется с нуля, поэтому мы добавляем его в соответствии с вашим индексом.

Индекс с нуля означает, что строка B2 равна 0, B3 равна 1, B4 равна 2 и B5 равна 3. Таким образом, MATCH вернет "3".

У вас есть собственный индекс в столбце A, и если он не был последовательным, или содержал другие значения, или не начинался с 1, вы можете использовать смещение, чтобы добраться до него:

=OFFSET(A2,MATCH(0,B2:B6,-1),0)

Так что это использует результат MATCH для обратного отсчета от A2, чтобы найти значение в этой позиции строки.

Answer 3

не могли бы вы рассчитать, используя только B2 и C2?

=CEILING(B2/C2,1)+1

Согласно моему комментарию в ответ Криса - обновленная версия

=INDEX($A:$A,MATCH(TRUE,INDEX($B:$B<=0,0),0))

Это должно избежать проблемы, присущей предложению Криса в том, что вы получаете неправильное значение, когда первое значение <= 0 само равно 0.

Часть $B:$B<0 возвращает "массив" значений TRUE/FALSE , причем первое TRUE явно совпадает со значением первого столбца В <0, затем МАТЧ находит позицию этого первого экземпляра, а ИНДЕКС получает соответствующее значение из столбца А.

Второй ИНДЕКС только для того, чтобы избежать "записи массива" - он работает без этого, т.е.

=INDEX($A:$A,MATCH(TRUE,$B:$B<=0,0))

....... но эта версия должна быть "введена в массив" - то есть подтверждена с помощью CTRL+SHIFT+ENTER .

Это немного более неэффективно, чем предыдущие предложения, используя весь столбец (и это не работает в Excel 2003 или более ранних версиях - в этих версиях вам нужно использовать определенный диапазон).

Обратите внимание, что MATCH с "типом совпадения" -1 согласно предложению Криса должен иметь нисходящие значения в столбце B - эта формула работает, однако столбец B упорядочен.