Формула Excel для определения идентификатора ячейки, когда ряд чисел становится отрицательным

Пример данных

     A            B
1  Date        Amount
2  Apr 1        $6,000
3  May 1        $4,250
4  June 1       $2,750
5  July 1       $1,000
6  Aug 1       -$0.075   <- This Cell/Row
7  Sept 1     -$0.2500

В столбце чисел (на самом деле 100-200 строк), когда значение меняется на отрицательное, например. если это суммы, причитающиеся по кредиту, когда кредит будет погашен. Обратите внимание, что реальная разница между числами колеблется в зависимости от процентов, налогов, разовых платежей и т. д. Поэтому я не могу просто посчитать (всего / платежа) = количество месяцев.

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


excel excel-formula vba
person scunliffe    schedule 16.03.2009    source источник

Ответы (3)


arrow_upward
9
arrow_downward

Функция match возвращает индекс диапазона

=MATCH(matchValue, range, matchType: 0=exact, 1=greater than, -1=less than


=MATCH(0, B2:B7, -1)

Совпадение с первой ячейкой, которая меньше 0 в диапазоне B2:B7. Из ваших выборочных данных это вернет 5

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

person TFD    schedule 16.03.2009

arrow_upward
3
arrow_downward

Используйте формулу MATCH, чтобы определить номер строки.

=MATCH(lookup value, lookup range, lookup type)
=MATCH(0,B1:B7,-1)

Вам нужно будет использовать тип соответствия -1, так как ваши данные расположены в порядке убывания. Этот параметр вернет наименьшее значение, которое больше или равно искомому значению 0.

На основе ваших данных будет возвращен номер строки 5. Вы ожидаете увидеть строку с номером 6, поэтому формулу необходимо расширить следующим образом.

=MATCH(0,B1:B7,-1)+1

Чтобы определить идентификатор ячейки, вам нужно будет обернуть эту формулу в формулу АДРЕС.

=ADDRESS(Row number, Column number)
=ADDRESS(MATCH(0,B1:B7,-1)+1,2)

Это вернет значение $B$6.

Вероятно, было бы полезнее вернуть соответствующую дату или значение. Это можно сделать с помощью формулы СМЕЩЕНИЕ.

=OFFSET(A1,MATCH(0,B1:B7,-1),0)
=OFFSET(A1,MATCH(0,B1:B7,-1),1)

Первая формула вернет дату в формате A6, 1 августа.

Вторая формула вернет значение в ячейке B6, -0,075 доллара США.

person Robert Mearns    schedule 17.03.2009

arrow_upward
1
arrow_downward

Я не уверен, что вы хотите сделать.

Если вы хотите избежать отрицательного числа, вы можете сделать:

=IF(YOUR_CELL_ACTUAL_FORMULA < 0 , 0, YOUR_CELL_ACTUAL_FORMULA)

Если вы хотите знать, когда число станет отрицательным, вы можете сделать:

=MATCH(0, YOUR_AMOUNT_RANGE, -1)

Это даст вам номер первой строки, когда сумма будет отрицательной.

person MarmouCorp    schedule 16.03.2009