У Брайана есть ряд чисел из 240 ячеек. В этой строке числа неуклонно уменьшаются и в конечном итоге в какой-то момент в этих 240 ячейках станут 0. Нули будут продолжать заполнять оставшиеся ячейки в строке. Брайану нужно написать уравнение, которое вернет последнее ненулевое значение в строке.

Есть множество способов вернуть желаемое значение.

(Разве это не всегда так с Excel? Вы можете придумать множество способов получить результат.) В общем, вы можете использовать обычную формулу или формулу массива.

Если вы хотите использовать обычную формулу, вот одну, которую вы можете попробовать:

=OFFSET(A6,0,(COUNT(A6:IF6)-COUNTIF(A6:IF6,0))-1)

Функция СЧЁТЕСЛИ подсчитывает количество нулевых значений, а функция СЧЁТ определяет количество ячеек в диапазоне. Вычитание одного из другого и корректировка на 1 дает значение СМЕЩЕНИЯ в «массив» ячеек, где находится последнее ненулевое значение. Эта формула предполагает, что значения начинаются в столбце A; если они начинаются в другом столбце, вам необходимо скорректировать значение, указанное в части формулы СЧЁТ / СЧЁТЕСЛИ, чтобы представить смещение от первого столбца.

Вот более короткий вариант формулы, основанный на выполнении смещения с правой стороны диапазона, а не с левой стороны:

=OFFSET(IF6,0,-COUNTIF(A6:IF6,0))

В этом случае важно, чтобы IF6 был фактическим правым концом диапазона. Формула работает путем подсчета количества нулевых значений в диапазоне (все в правой части диапазона), а затем вычисления адреса последней ячейки (IF6) за вычетом количества нулей.

Вот версия, в которой вместо этого используется функция ИНДЕКС:

=INDEX(A6:IF6,,MATCH(0,A6:IF6,0)-1)

Эта версия еще короче, с использованием функции ПРОСМОТР:

=LOOKUP(1,1/(6:6>0),6:6)

Также можно использовать формулы массива. (Формулы массива вводятся нажатием Ctrl + Shift + Enter.) Здесь используется функция КОСВЕННО:

=INDIRECT("R6C" & MAX((A6:IF6>0)*COLUMN(A6:IF6)),FALSE)

Эта формула массива использует интересную реализацию функции ПРОСМОТР для нахождения правильного результата:

=LOOKUP(9.99999999999999E+307,IF(A6:IF6<>0,A6:IF6))

Вот еще одна формула массива, которую можно использовать, на этот раз с помощью функции OFFSET для поиска последнего ненулевого значения в строке 6:

=OFFSET(A6,0,MIN(IF(6:6=0,COLUMN(6:6),300))-2)

Вот еще более короткий вариант:

=MIN(IF(A6:IF6>0,A6:IF6))

Все эти формулы, представленные до сих пор, зависят от того факта, что числа в строке действительно уменьшаются — они идут от любого начального числа и постепенно стремятся к нулю. Если числа не уменьшаются, вы можете использовать другой тип формулы массива для определения последнего ненулевого значения в строке:

=INDEX(6:6,MAX(IF(A6:IF6<>0,COLUMN(A6:IF6))))

Формула сначала определяет максимальный столбец в строке (в данном случае строка 6), значение которого не равно нулю, затем она использует функцию ИНДЕКС для получения значения из этого столбца в этой строке.

Как вы понимаете, существует довольно много способов найти последнее ненулевое значение в строке. Выберите тот, который вам понравится; в этом случае нет правильного или неправильного.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3785) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Last_Non-Zero_Value_in_a_Row [Последнее ненулевое значение в строке].