Пример:
Имеется электронная таблица с четырьмя столбцами - 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.
В том же духе, что и в ответе Пола, я бы использовал Index and Match
=INDEX($A:$A,MATCH(0,$B:$B,-1)+1)
Index а не Offset так как Offset изменчиво, а Index - нет. (Энергозависимые функции пересчитываются при расчете каждого листа, в то время как энергонезависимые функции пересчитываются только при изменении ссылочных данных. Слишком много изменчивых функций может замедлить работу Excel.)Вы можете использовать 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, чтобы найти значение в этой позиции строки.
не могли бы вы рассчитать, используя только 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 упорядочен.