backtotop
Поиск по матрице

Categories: Формулы

Как скопировать формулу в Excel?
Как посчитать количество пустых ячеек Excel? Функция СЧИТАТЬПУСТОТЫ

Предположим нам задан месяц, город и зависящая от них стоимость продукта. Необходимо найти соответствующее им значение в большом двухмерном массиве/матрице/таблице и вывести его в ячейку, формулой. По сути нам нужно вернуть значение на пересечении нужных строки и столбца таблицы. Поиск в двумерном массиве 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);)

Поиск в двумерном массиве Excel

Как видите, формулу на рисунке (с использованием столбцов) можно продлить ниже в другие ячейки.

Описание конструкции формулы:

  1. Функция ВПР осуществляет поиск нужного нам значения (Города, синий столбец G) в большой таблице $B:$E (отмечена зеленым). Для этого нам необходимо указать в каком же именно столбце этой таблицы ВПРу искать город — втором, третьем или четвертом (C, D, E)
  2. Номер этого столбца найдем при помощи функции ПОИСКПОЗ, которая находит номер элемента в массиве. А точнее найдем номер значения фиолетового столбца H (месяца) в массиве $B$2:$E$2. Тем самым мы определили, что ВПР надо использовать третий столбец.
  3. Четвертый аргумент оставляем пустым (после последней «;»), т.к. по умолчанию можно оставить ЛОЖЬ или пусто. Подробнее здесь.

Обратите внимание! Нужно обязательно выполнить два условия, чтобы формула считалась правильно!

— Ширина массива $B$2:$E$2 в ПОИСКПОЗ и ширина таблицы $B:$E должны совпадать.

— Данные в массиве для ПОИСКПОЗ ($B$2:$E$2) должны быть отсортированы от большего к меньшему или от А до Я!

Получилось:

Поиск в двумерном массиве Excel 2

Если есть вопросы — не стесняйтесь писать комментарии!

 

Поделитесь нашей статьей в ваших соцсетях:

(Visited 3 327 times, 10 visits today)
Как скопировать формулу в Excel?
Как посчитать количество пустых ячеек Excel? Функция СЧИТАТЬПУСТОТЫ

Комментарии:

К этой записи пока нет комментариев. Возможно вы будете первым?

Оставьте ваш комментарий здесь:

You have to agree to the comment policy.

4 × два =