Поиск и подстановка по нескольким условиям

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно :) - без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

vlookup-2cols1.png

Нужно найти и вытащить цену заданного товара (Нектарин) в определенном месяце (Январь), т.е. получить на выходе152, но автоматически, т.е. с помощью формулы. ВПР в чистом виде тут не поможет, но есть несколько других способов решить эту задачу.

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

vlookup-2cols2.png

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

vlookup-2cols3.png

Плюсы: Простой способ, знакомая функция, работает с любыми данными.

Минусы: Надо делать дополнительный столбец и потом, возможно, еще и прятать его от пользователя. При изменении числа строк в таблице - допротягивать формулу сцепки на новые строки (хотя это можно упростить применением умной таблицы).

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS), появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

vlookup-2cols4.png

Плюсы: Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы: Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

  1. Выделите пустую зеленую ячейку, где должен быть результат.
  2. Введите в строке формул в нее следующую формулу:

    vlookup-2cols6.png

  3. Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы: Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы: Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны "с запасом" или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Ссылки по теме

 



MCH
21.07.2015 00:27:28
А как же СУММПРОИЗВ:
=СУММПРОИЗВ((B2:B161=H3)*(C2:C161=J3)*L3)

Либо формула массива:
=МИН(ЕСЛИ((B2:B161=H3)*(C2:C161=J3);L3))

Где вместо МИН можно использовать МАКС или СУММ (если сочетания Товар/Месяц уникальны)
23.07.2015 13:32:15
Еще есть вариант), на тот случай, если массив ниже или выше, или просто в рамках какого-то диапазона.
Данную формулу мне подсказал человек с форума под ником Евгений

=ИНДЕКС($A$1:$D$173;МАКС(ЕСЛИ($A$1:$D$173=G3&I3;СТРОКА($A$1:$D$173)));МАКС(ЕСЛИ($A$1:$D$173=K2;СТОЛБЕЦ($A$1:$D$173))))

Пример по ссылке ниже:
Скачать
22.08.2015 11:35:56
Совершенно согласен, Михаил. Но нужно помнить, что эти формулы работают только для чисел и при наличии дубликатов сочетаний Товар-Месяц будет их суммировать, а не выдавать первое встретившееся значение, т.е. это скорее для суммирования по двум условиям, чем для выборки.
MCH
23.08.2015 09:29:36
Пример с СУММПРОИЗВ() работает аналогично, как и приведенный "Способ 2" с СУММЕСЛИМН() с теми же самыми ограничениями (работает с числами и необходимы уникальные сочетания товар/месяц иначе получим сумму),но есть и преимущество - работает в  2003 Excel.
Я привел эти примеры, как дополнительные варианты "выборки".

Ну и еще вариант через ПРОСМОТР:
=ПРОСМОТР(2;1/(A2:A161=G3)/(B2:B161=I3);C2:C161)

Из преимуществ - не требует массивного ввода, работает в любой версии Excel, не нужно делать конкатенацию как в примере с ВПР, работает существенно быстрее, чем конструкция с ИНДЕКС(ПОИСКПОЗ) при использовании нулевого интервального просмотра (точного поиска). Особенность - при нескольких сочетаниях товар/месяц, попадающих под выборку - возвратит значение последнего совпадения.

Аналог данной формулы через ИНДЕКС(ПОИСКПОЗ):
=ИНДЕКС(C2:C161;ПОИСКПОЗ(2;1/(A2:A161=G3)/(B2:B161=I3)))

Требует массивного ввода и вернет последнее совпадение.

Для первого совпадения:
=ИНДЕКС(C2:C161;ПОИСКПОЗ(1;(A2:A161=G3)*(B2:B161=I3);0))

Но на большом количестве данных эта формула будет работать существенно медленнее , чем предыдущие два варианта.
07.02.2017 17:12:53
=МИН(ЕСЛИ((B2:B161=H3)*(C2:C161=J3);L3))

Откуда появляется переменная "L3" в формуле, если ее надо вычислить??
24.07.2015 13:09:52
Привет Николай!
Я часто использую 1-й способ. Приходится анализировать информацию в разные дни и за разные, не равномерные промежутки времени. Чтобы иметь объективную информацию, дату и время я преобразую в вот такую штуку 25.03.2015 2:39:48 = 25_3_2015_2_39_48, далее ВПР вытягиваю значения, в пустых ячейках ставлю формулу =НД() и строю график .
25.07.2015 22:02:52
Приветствую Николай! А нет ли в планах у вас создания видеоурока на тему анализа "что если" ?
01.09.2015 16:34:39
еще один вариант:
=СРЗНАЧЕСЛИМН(C:C;A:A;G3;B:B;I3)
соответственно при дубляже строк он выдаст их среднюю.


Николай
такой вопрос, как вы выбираете функцию без мышки и её завершение?

в ролике в набрали "=ин" и что то нажали (время 8:55), и сразу появилось "=Индекс("
Ролик не видел... но нажат был TAB
18.09.2015 16:52:30
Использую чаще всео слияние всех трёх вариантов, только в качестве ключа использую IndexID=строка(). Затем суммирую по условию колонку IndexID, и уже через ИНДЕКС нахожу нужную колонку. Формула получается:
=ИНДЕКС(Прайс[Цена];СУММЕСЛИМН(Прайс[IndexID];Прайс[Товар];H3;Прайс[Месяц];J3);1)).

А для второго варианта можно исключить повторы используя формулу:

=ЕСЛИОШИБКА(СУММЕСЛИМН(C:C;A:A;G3;B:B;I3)/СЧЁТЕСЛИМН(A:A;G3;B:B;I3);"Ничего не найдено!")
которая вначале просуммирует все найденные значения, а затем разделит на их количество. только нужно учитывать что на ноль делить нельзя/
25.11.2015 12:01:55
А для второго варианта можно исключить повторы используя формулу:

=ЕСЛИОШИБКА(СУММЕСЛИМН(C:C;A:A;G3;B:B;I3)/СЧЁТЕСЛИМН(A:A;G3;B:B;I3);"Ничего не найдено!";)
А почему бы для этого не использовать функцию СРЗНАЧЕСЛИМН()?
СРЗНАЧЕСЛИМН(C:C;A:A;G3;B:B;I3) и все.
02.12.2015 17:54:05
Николай, для 3-го случая можно сделать формулу, очень похожую на Вашу, но без использования массива:

=ИНДЕКС(C:C;ПОИСКПОЗ(G3&I3;ИНДЕКС(A:A&B:B;0);0))
=INDEX(C:C;MATCH(G3&I3;INDEX(A:A&B:B;0);0))

Если в функции индекс в качестве второго параметра ввести 0, то он отрабатывает как массив
06.12.2015 14:32:57
Спасибо, что поделились, Игорь - весьма ценное уточнение.
22.09.2016 11:13:45
Игорь, скажите, пожалуйста, если в связке столбцов А и В формула не находит связку G3&I3, как сделать так чтобы результат в ячейке был "0"...
Пробую вашу формулу, и в этом случае в ячейке выпадает "#Н/Д"....
Заранее спасибо..  
22.09.2016 11:16:48
Наталья, воспользуйтесь функцией
=ЕСЛИОШИБКА(ИНДЕКС(C:C;ПОИСКПОЗ(G3&I3;ИНДЕКС(A:A&B:B;0);0));0)
=IFERROR(INDEX(C:C;MATCH(G3&I3;INDEX(A:A&B:B;0);0));0)
22.09.2016 11:37:06
Урааааа!!!! Заработало....
Спасибо огромное! :)
21.05.2018 19:05:28
Игорь и Николай, большое спасибо!
Формула массива почему-то не сработала. В формуле были ссылки на столбцы, но название умной таблицы не отражалась.
Формула без использование массива сработала.
08.12.2015 12:55:09
А как посчитать сумму к определённой дате.
Например есть в 1м столбце даты (всегда разные) а во втором суммы. Нужно посчитать какая сумма выходит на 25 число каждого месяца.
Пример:
Дата               Оплатили            Выставили счёт            
30.12.13                                          15000 руб.
02.01.14      12000 руб.
15.01.14                                          16000 руб                
31.01.14      45000 руб
05.02.14                                          50000 руб
08.02.14      65000 руб
12.02.14      1000 руб
25.02.14      12000 руб
28.02.14                                          20000 руб
03.03.14      12000 руб
21.03.14      17000 руб
24.05.14                                          40000 руб
24.05.14       20000 руб

Нужно знать долг на 25 число каждого месяца.
Помогите пожалуйста.
08.05.2016 00:57:14
Здравствуйте, помогите плиз: необходимо чтобы автоматически выбирался месяц планирования (январь или февраль и т.д.) если есть значение в этом месяце? Спасибо
22.09.2016 00:08:18
Николай, добрый день!
Пыталась применить способ №3, для меня он наиболее подходит, но формулу применить не смогла, выдает ошибку (#ЗНАЧ!)...
Создала аналог вашего примера, но формула продолжает выдавать ошибку...
Не понимаю в чем проблема:|
11.01.2017 14:42:21
Наталья, Вы точно проставили фигурные скобки массива после написания формулы? (Ctrl+shift+Enter). Еще проверьте форматы столбцов -условий.
У меня все работает.
Большое спасибо за формулу)
23.01.2017 18:11:04
Спасибо, Николай. Спасибо Игорь. Все заработало отлично.
Жить стало легче, жить стало веселее! =)
+1. Просто спасибо. А то я был "уверен, что ВПР (VLOOKUP) - самая сложная функция в Excel, а я ее уже освоил - значит ничему новому меня не научить." :)
03.04.2017 19:45:31
а если столбы находяться не рядом , получмиться их склеить ?

1
критерий1значзначзначкритерий 2знач то что ищем
авто1ерер11аатекст
авто1ереере11аптекст
мото2ерер22аптекст
мото2рееер22аптаптекст
03.04.2017 19:59:33
подскажите еще как сделать так, чтобы ВПР проматривала в отфильтрованной таблице только видимые ячейки и подставляла в другую отфильтрованную таблицу ?
07.06.2017 13:40:38
Подскажите, пожалуйста. !!!
Никак не могу победить подсчет формулы с ИНДЕКС и ПОИСПОЗ... по первому параметру.
Задача такая..
Есть ряд параметров - по ним в массиве данных нужно найти соответствие по 2-м или нескольким совпадениям.
Причем первый параметр повторяющийся расположен в колонке, второй меняется и расположен в строке.
ПОИСКПОЗ - находит первое совпадение по первому параметру, а второе совпадение игнорирует...
22.06.2017 02:18:46
Добрый день. У меня несколько иная задача. Имеется страница "Реализация" в которую при заполнении товара и даты продажи должна подставляться рекомендуемая цена со страницы "Цены". При этом на странице "Цены" один и тот-же товар может встречаться несколько раз (в том случае если он оприходовался более одного раза и цена закупки была разной). Так-же на странице "Цены" у каждого товара указывается дата установки цен, которая совпадает с датой оприходования. Мне нужно что-бы подставлялась последняя цена. Вариант при котором в разделе "Цены" цена закупки будет динамически меняться при поступлении новой партии товара меня не устраивает, т.к. при этом данные по прибыли за ранее проданный товар будут постоянно меняться, что будет ошибкой. Алгоритм, как должна выглядеть подстановка цены мне в общем-то понятен, но как должна выглядеть формула я сообразить не могу. Алгоритм (как мне кажется) должен быть следующий: Берём дату реализации и ищем ближайшую (раннюю) к ней дату установки цены со страницы "Цены" для выбранного товара, и вытаскиваем цену.
Я конечно понимаю что для этой цели нужен 1С, но товарищ (который попросил меня написать данную таблицу) не может забивать свои товары в общую базу.
Помогите пожалуйста. Данные по ценам нужно брать из столбцов I, J, K (страница "Цены";) и подставлять в столбцы J, K, L (страница "Реализация).
Пример по ссылке: Пример
23.08.2017 16:01:55
Вам не подойдет такой вариант: фильтровать список цен по дате реализации, начиная с самого позднего (по убыванию)?
ВПР будет отбирать самое первое значение, т.е. верхнее.
Т.о. если у вас будет фильтр по дате, то и находить функция будет самую "свежую" цену.
Далее принцип поиска цены тот же, что и в статье...
23.08.2017 14:11:18
Всем привет!
Подскажите, пожалуйста, как можно показать в одной строке в каких месяцах продавался, например, картофель?
Какую формулу следует использовать? Возможно ли это вообще осуществить стандартными формулами без допмакросов?

В моем случае требуется по одному артикулу обуви отобразить В ОДНОЙ СТРОЧКЕ все размеры, какие есть в наличии (столбец артикул обуви, столбец размер обуви).
01.06.2018 11:53:00
Формула с массивом помогла, первые два варианта более сложные задачи, чем в примерах, не решают. Спасибо!
24.08.2018 14:26:12
Добрый день! Помогите, пжл, я видимо на каком то этапе не верно делаю.
Есть список привязки номер телефона - должность, также есть таблица привязка должность -тариф , так мне нужно увязать телефон-должность-тариф. индекс _поискпоз делаю  не верное знач. Помогите, пжл,.. спасибо
27.11.2018 16:08:39
Добрый день! подскажите, возможно ли использование формулы на 3м примере при поиске в разных книгах?
Наверх