Знаете ли вы, как создать ссылку на заголовок столбца в последней непустой ячейке строки? Лучшее: вам не нужны никакие вспомогательные строки или столбцы. Это так просто:
Объедините четыре функции ЕСЛИОШИБКА (), ИНДЕКС (), МАКС () и ЕСЛИ ()
Управляющий директор по продажам пришлет вам список контрактов, заключаемых в месяц на продукты, которые постепенно прекращаются ①. Вы должны использовать формулу в столбце N, чтобы указать последний месяц продаж для каждого продукта - без дополнительных строк или столбцов. Если больше договоров заключено не было, введите пустую ячейку в столбце N.
Этот, на первый взгляд, банальный пример, оказывается крепким орешком без использования вспомогательных линий или столбцов. в качестве Excel для практики-Читатели ломают орех! У нас есть следующая формула массива в ячейке для решения проблемы N2 создано ②:
{= ЕСЛИОШИБКА (ИНДЕКС ($ B $ 1: $ M $ 1; МАКС (ЕСЛИ (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}
Взгляните, как это работает, шаг за шагом
Начнем с условия IF (), встроенного в формулу массива: {= ЕСЛИ ОШИБКА (ИНДЕКС ($ B $ 1: $ M $ 1; МАКС (ЕСЛИ (B2: M2 ""; КОЛОНКА (B2: M2) -1; -1)));"")}
Условие IF () создает фиктивную вспомогательную строку в формуле массива и проверяет наличие ячеек. Би 2 до M2являются ли они пустыми или нет. Если ячейка пуста, в противном случае с помощью функции COLUMN () возвращается значение -1, соответствующий номер столбца минус значение 1.
В формуле требуется вычитание 1, поскольку первый столбец в таблице содержит не название месяца, а название продукта. Ниже вы узнаете, как использовать функцию ИНДЕКС () для отображения соответствующего названия месяца, которое - если вы не вычитали 1 - из-за использованного дополнительного столбца. А. будет неверно ровно на один столбец.
Если все ячейки в диапазоне B2: M2 пусты, значение -1 (нет продаж) создает ошибку, которую мы используем для представления пустой ячейки. Активную вспомогательную линию можно увидеть на рис. ③ в строке 3.
На следующем шаге вы считываете наибольшее значение с помощью функции MAX (), в которую вложено условие IF (). Это значение 12 в строке 3 (столбец 13 минус 1; см. Вспомогательную строку на рисунке ③):
{= ЕСЛИ ОШИБКА (ИНДЕКС ($ B $ 1: $ M $ 1;МАКС (ЕСЛИ (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}
Вы передаете это значение MAX функции INDEX (). Соответствующий календарный месяц затем считывается в строке 1. Область данных функции ИНДЕКС () - это область $ B $ 1: $ M $ 1. Переданное значение MAX - в примере 12 - означает, что двенадцатое значение в списке, т.е. Декабрь на декабрь месяц:
{= ЕСЛИ ОШИБКА(ИНДЕКС ($ B $ 1: $ M $ 1; МАКС (ЕСЛИ (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}
Если все ячейки в диапазоне месяцев таблицы пусты, то наибольшее значение равно -1 (см. Условие IF () в начале). Если значение -1 передается в функцию INDEX (), это неизбежно приводит к значению ошибки, поскольку область списка функции INDEX () содержит только двенадцать записей и поэтому не может найти запись -1. Вы поймаете это значение ошибки с помощью функции ЕСЛИОШИБКА () и вместо этого вернете пустую строку. В примере это относится к продукту C в строке 4, потому что для этого продукта больше нельзя заключить контракт:
{=ЕСЛИ ОШИБКА(ИНДЕКС ($ B $ 1: $ M $ 1; МАКС (ЕСЛИ (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Поскольку это формула массива, завершите ввод формулы комбинацией клавиш Ctrl + Shift + Enter.
