Двусторонний поиск определяет значение на пересечении столбца и строки. В этом совете описаны два способа выполнения двустороннего поиска.
На рис. 121.1 показана таблица с диапазоном ячеек, где отображаются продажи продукции по месяцам. Чтобы извлечь продажи для определенного месяца и продукта, пользователь вводит месяц в ячейку В1
и название продукта в ячейку В2
.
Рис. 121.1. Таблица предоставляет возможность двустороннего поиска
Для упрощения данного процесса таблица применяет именованные диапазоны (табл. 121.1).
Название | Диапазон |
---|---|
Месяц | B1 |
Продукт | B2 |
Таблица | D1:H14 |
Список_месяцев | D1:D14 |
Список_продуктов | D1:H1 |
Следующая формула (в ячейке В4) использует функцию ПОИСКПО3 для возврата позиции месяца в диапазоне Список_месяцев. Например, если месяц — январь, то формула возвращает значение 2, поскольку январь является вторым пунктом диапазона Список_месяцев (первый пункт — пустая ячейка, D1): =ПОИСКПОЗ(Месяц;Список_месяцев;0)
.
Формула в ячейке В5 работает аналогично, но использует диапазон Список_продуктов: =ПОИСКПОЗ(Продукт;Список_продуктов;0)
.
Окончательная формула в ячейке В6 возвращает соответствующее количество продаж. Она использует функцию ИНДЕКС с результатами из ячеек В4 и В5: =ИНДЕКС(Таблица;B4;B5)
.
Вы можете объединить эти формулы в одну, как показано здесь: =ИНДЕКС(Таблица;MATCH(ПОИСКПОЗ;Список_месяцев;0);ПОИСКПОЗ(Продукт;Список_продуктов;0))
.
Второй метод выполнения двустороннего поиска чуть проще, но требует создания имени для каждой строки и столбца в таблице. Быстрый способ задать имя каждой строке и столбцу состоит в выборе таблицы и выполнении команды Формулы ► Определенные имена ► Создать из выделенного. В диалоговом окне Создание имен из выделенного диапазона укажите, что имена находятся в верхней строке и левом столбце. Нажмите ОК, и Excel создаст имена. После создания имен вы можете применять простую формулу для выполнения двустороннего поиска, например эту: =Колесо_Июль
. Формула, в которой используется оператор пересечения диапазона, возвращает данные о продажах цепных колес за июль.