Двумерный поиск в таблице (ВПР 2D)
Если вы знакомы с функцией ВПР (VLOOKUP) или ее горизонтальным аналогом ГПР (HLOOKUP), то должны помнить, что эта замечательные функции ищут информацию только по одному параметру, т.е. в одномерном массиве - по строке или по столбцу. А если нам необходимо выбирать данные из двумерной таблицы по совпадению сразу двух параметров - и по строке и по столбцу одновременно? Давайте рассмотрим несколько жизненных примеров таких задач и их решения.
Пример 1. Найти значение по товару и городу
Предположим, что у нас имеется вот такой двумерный массив данных по городам и товарам:
Пользователь вводит (или выбирает из выпадающих списков) в желтых ячейках нужный товар и город. В зеленой ячейке нам нужно формулой найти и вывести число из таблицы, соответствующее выбранным параметрам. Фактически, мы хотим найти значение ячейки с пересечения определенной строки и столбца в таблице. Для наглядности, разобъем задачу на три этапа.
- Во-первых, нам нужно определить номер строки, соответствующей выбранному пользователем в желтой ячейке товару. Это поможет сделать функция ПОИСКПОЗ (MATCH) из категории Ссылки и массивы (Lookup and Reference). В частности, формула ПОИСКПОЗ(J2; A2:A10; 0) даст нам нужный результат (для Яблока это будет число 6). Первый аргумент этой функции - искомое значение (Яблоко из желтой ячейки J2), второй - диапазон ячеек, где мы ищем товар (столбец с товарами в таблице - A2:A10), третий аргумент задает тип поиска (0 - точное совпадение наименования, приблизительный поиск запрещен).
- Во-вторых, совершенно аналогичным способом мы должны определить порядковый номер столбца в таблице с нужным нам городом. Функция ПОИСКПОЗ(J3; B1:F1; 0) сделает это и выдаст, например, для Киева, выбранного пользователем в желтой ячейке J3 значение 4.
- И, наконец, в-третьих, нам нужна функция, которая умеет выдавать содержимое ячейки из таблицы по номеру строки и столбца - функция ИНДЕКС (INDEX) из той же категории Ссылки и массивы (Lookup and Reference). Первый аргумент этой функции - диапазон ячеек (в нашем случае это вся таблица, т.е. B2:F10), второй - номер строки, третий - номер столбца (а их мы определим с помощью функций ПОИСКПОЗ).
Итого, соединяя все вышеперечисленное в одну формулу, получаем для зеленой ячейки решение:
=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))
или в английском варианте
=INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0))
Пример 2. Приблизительный двумерный поиск
Слегка модифицируем предыдущий пример. Предположим, что у нас имеется вот такая ситуация:
Идея в том, что пользователь должен ввести в желтые ячейки высоту и ширину двери для, например, шкафа, которую он хочеть заказать у компании-производителя, а в серой ячейке должна появиться ее стоимость из таблицы. Важный нюанс в том, что если пользователь вводит нестандартные значения размеров, то они должны автоматически округлиться до ближайших имеющихся в таблице и в серой ячейке должна появиться стоимость изготовления двери для этих округленных стандарных размеров.
Решение для серой ячейки будет практически полностью аналогично предыдущему примеру:
=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))
=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))
Разница только в последнем аргументе обеих функций ПОИСКПОЗ (MATCH) - Типу сопоставления (здесь он равен минус 1). Это некий аналог четвертого аргумента функции ВПР (VLOOKUP) - Интервального просмотра (Range Lookup). Вообще говоря, возможных значений для него три:
- 1 - поиск ближайшего наименьшего числа, т.е. введенные пользователем размеры двери округлялись бы до ближайших наименьших подходящих размеров из таблицы. В нашем случае высота 500 округлилась бы до 450, а ширина 480 до 300, и стоимость двери была бы 135.
- -1 - поиск ближайшего наибольшего числа, т.е. нестандартная высота 500 округлялась бы до 700, а ширина 480 - до 600 и стоимость составила бы уже 462. Для бизнеса так гораздо интереснее! :)
- 0 - поиск точного соответствия без каких либо округлений. Используется для 100%-го совпадения искомого значения с одним из значений в таблице. Естественно, применяется при поиске текстовых параметров (как в прошлом примере), т.к. для них округление невозможно.
Важно отметить, что при использовании приблизительного поиска с округлением диапазон поиска - а значит и вся таблица - должна быть отсортирована по возрастанию (для Типа сопоставления = 1) или по убыванию (для Типа сопоставления = -1) по строчкам и по столбцам. Иначе приблизительный поиск корректно работать не будет!
Для точного поиска (Тип сопоставления = 0) сортировка не нужна и никакой роли не играет.
P.S. Обратная задача
В комментах неоднократно интересуются - а как сделать обратную операцию, т.е. определить в первом примере город и товар если мы знаем значение из таблицы? Тут потребуются две небольшие формулы массива (не забудьте ввести их с помощью сочетания клавиш Ctrl+Shift+Enter, а не обычного Enter):
Принцип их работы следующий:
- перебираем все ячейки в диапазоне B2:F10 и ищем совпадение с искомым значением (13) из ячейки J4 с помощью функции ЕСЛИ (IF)
- когда нашли совпадение, то определяем номер строки (столбца) первого элемента в таблице в этой строке (столбце) с помощью функций СТОЛБЕЦ (COLUMN) и СТРОКА (ROW)
- выдергиваем значение города или товара из таблицы с помощью функции ИНДЕКС (INDEX)
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для подстановки значений
- Динамическая выборка из списка функциями ИНДЕКС и ПОИСКПОЗ
- Улучшаем функцию ВПР (VLOOKUP2)
- ВПР (VLOOKUP) с учетом регистра
- Многоразовый ВПР для вывода сразу всех значений
Хочу сделать для нескольких таблиц. Но почему то не ищет значение G3.
Спасибо!
=ДВССЫЛ(Товар) ДВССЫЛ(Город).
=ДВССЫЛ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(товар;" ";"_");"-";"_")) ДВССЫЛ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(город;" ";"_");"-";"_"))
Думаю, вам нужно копать в направлении функции СУММЕСЛИМН - там до 128 условий поддерживается, но вид таблицы нужен немного другой.
Ввести 13 в ячейку и получить в одной ячейке "Яблоко", а в другой "Киев"? 13 повторяться внутри таблицы не может.
А как сделать наоборот, вести значение, и получить товар и город ( первый пример)
Спасибо
Так же был бы рад объяснению принципа работы обратного посика. Не могу понять логику посика. Условие IF, при поиске номера строки (столбца) всегда выдает "1" если есть совпадение, и "0" если не найдено совпадений. Не могу понять логику как она должна понять в какой строке (столбце) это происходит.
Судя по комментариям обратный поиск ни у кого не работает.
{=INDEX(А2:А10;SMALL(IF(NOT(ISERROR(SEARCH(J4;B2:F10)));ROW(B2:F10));1)-ROW(A1);1)}
Киев и Яблоко в одной строке, в ячейках рядом?
Заранее спасибо, с уважением, Олег
А как можно найти город и товар, если искомого значения в массиве нет?Таблица аналогичная, но нужен поиск не точного значения, а минимально или максимально приближенного к искомому значению?
Уже весь мозг вывихнул...
Не актуально, разобрался
Значение в массиве могут повторятся но в строке и столбце значение уникально. Как подставив город и значение получить название продукта? Киев/78 = Помидор
А как при первом варианте суммировать данные?
Подскажите, как сделать суммеслимн из подобной таблицы? Например, у меня 2 столбца "Киев", и мне необходимо просуммировать яблоки с двух складов в Киеве. Получается, что для такой формы таблицы нельзя воспользоваться формулой суммеслимн, тогда какое сочетание функций сделать?
СУММ(ЕСЛИ(B2:F10=J9;СТРОКА(B2:F10)-СТРОКА($A$1);0));1)
Что тут сумируется ? СУММ
B2:F10=J9 тут понятно , диапазон значений равен значению в нашей ячейке
СТРОКА(B2:F10)-СТРОКА($A$1);0));1) тут не понимаю , поясните пожалуйста
Т.е. по факту найти совпадение не только по продукту, но и по еще одному критерию?
Спасибо.
Согласно первого критерия через ПОИСКПОЗ удается найти номер строки (в первом столбце), далее не получается. Требуется найти столбец в массиве согласно найденной строке, но из-за отсутствия точного совпадения не получается это сделать
И приложить пример обязательно.
Спасибо
Подскажите пожалуйста, имеется ли аналог формулы "СУММЕСЛИМН", мне необходимо суммировать Вес по 2 критериям, по дате и адресу, в наличии 450 000 строк, EXCEL очень сильно подвисает и уходит в перезагрузку. Как можно обойти эту проблему? Даже если кусочками протягивать формулу и превращать в значения, все равно очень долго. Буду очень благодарен за помощь!
Например: в примере в таблице есть число 13 соответственно найдено яблоко и Киев. А если числа 13 нет в таблице, то выводить в ячейках н/д
И да, таблицы с данными на одном листе, а вывод искомых значений - на другом