Предположим, нам задан месяц, город и зависящая от них стоимость продукта. Необходимо найти соответствующее им значение в большом двухмерном массиве/матрице/таблице и вывести его в ячейку, формулой. По сути нам нужно вернуть значение на пересечении нужных строки и столбца таблицы. Поиск в двумерном массиве Excel готовыми функциями не сделаешь — СУММЕСЛИ или просто ВПР здесь уже не подходит. Я использую конструкцию из функций ВПР и ПОИСКПОЗ. Как ее правильно записать смотрим ниже.
Конструкция здесь довольно нетривиальная. Поэтому рекомендую ознакомиться с принципами работы функции ВПР и ПОИСКПОЗ перед прочтением статьи ниже, особенно если не встречались с такими функциями ранее.
Пример
Чтобы было было понятнее, начнем с примера.
Имеется таблица по городам и стоимость услуг, стоимости зависят от месяца.
Необходимо найти стоимость для третьего месяца по городу Краснодару или найти Sij, если по умному. Как будем делать?
Поиск в двумерном массиве Excel. Как записать формулу
Как мы обсудили, готовой формулы в Excel нет. Поэтому создадим комбинацию двух формул. В любой ячейки запишем:
=ВПР($G:$G;$B:$E;ПОИСКПОЗ($H:$H;$B$2:$E$2);)
Где столбец G — искомый город (Краснодар), а столбец H — месяц (третий). При записи можно использовать и просто одну ячейку, например, G4 и H4
=ВПР($G$4;$B:$E;ПОИСКПОЗ($H$4;$B$2:$E$2);)
Как видите, формулу на рисунке (с использованием столбцов) можно продлить ниже в другие ячейки.
Описание конструкции формулы:
- Функция ВПР осуществляет поиск нужного нам значения (Города, синий столбец G) в большой таблице $B:$E (отмечена зеленым). Для этого нам необходимо указать в каком же именно столбце этой таблицы ВПРу искать город — втором, третьем или четвертом (C, D, E)
- Номер этого столбца найдем при помощи функции ПОИСКПОЗ, которая находит номер элемента в массиве. А точнее найдем номер значения фиолетового столбца H (месяца) в массиве $B$2:$E$2. Тем самым мы определили, что ВПР надо использовать третий столбец.
- Четвертый аргумент оставляем пустым (после последней «;»), т.к. по умолчанию можно оставить ЛОЖЬ или пусто. Подробнее здесь.
Обратите внимание! Нужно обязательно выполнить два условия, чтобы формула считалась правильно!
— Ширина массива $B$2:$E$2 в ПОИСКПОЗ и ширина таблицы $B:$E должны совпадать.
— Данные в массиве для ПОИСКПОЗ ($B$2:$E$2) должны быть отсортированы от большего к меньшему или от А до Я!
Получилось:
Если есть вопросы — не стесняйтесь писать комментарии!
Комментарии:
К этой записи пока нет комментариев. Возможно вы будете первым?