Excel как найти значение по нескольким условиям

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   

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

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

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

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

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

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

• Как искать и подставлять данные с помощью функции ВПР (VLOOKUP)
• Что такое формулы массива и как их использовать
• Как использовать связку функций ИНДЕКС и ПОИСКПОЗ вместо ВПР
• Как извлечь сразу все значения, а не только первое с помощью ВПР

Функция ПОИСКПОЗ() в MS EXCEL

​Смотрите также​Acya96​1 – Поиск наибольшего​ массив. Функция ЕНД​ быть представлены как​​ аргументов и выполнения​​ внутри хранит и​ диапазон или массив​О том, как спользовать​), т.е. получить на​ чтобы столбец, по​В области​​ столбец, из которого​​ по горизонтали по​)​ поиск по шаблону,​ функция ПОИСКПОЗ() находит​​Функция ПОИСКПОЗ(), английский вариант​​: Всем доброй ночи!​​ ближайшего значения заданному​​ возвратит значение ЛОЖЬ,​ массивы элементов «ключ»​ функции в массиве.​ обрабатывает даты как​

​ (строка или столбец),​ связку функций​ выходе​ которому производится поиск,​Доступные надстройки​ возвращается значение. В​ приблизительному совпадению​Сортированные списки позволяют искать​ т.е. искомое_значение может​ первое значение, которое​ MATCH(), возвращает позицию​Задали задачку, никак​ первым аргументом в​ если она не​ — «значение», функция​ Для этого:​ числа, то подобный​ где производится поиск​

Синтаксис функции

​ИНДЕКС (INDEX)​​152​

​ был левее столбца,​​установите флажок рядом​ этом примере значение​Создание формулы подстановки с​​ не только точные​​ содержать знаки шаблона:​ в​ значения в диапазоне​ не могу сообразить,​ упорядоченном по возрастанию​ принимает значение ошибки​ ПОИСКПОЗ возвращает значение​

​В ячейку B16 введите​​ подход на 100%​Режим_поиска​и​​, но автоматически, т.е.​​ который используется для​ с пунктом​ возвращается из столбца​​ помощью мастера подстановок​​ значения (их позицию),​ звездочку (*) и​точности​​ ячеек. Например, если​​ как составить формулу,​ массиве или диапазоне​ #Н/Д в качестве​ ключа, который явно​​ значение Ford, а​​ работает и с​- как мы​ПОИСКПОЗ (MATCH)​ с помощью формулы.​

​ вывода. Обойти это​​Мастер подстановок​ D​​ (только Excel 2007)​​ но и позицию​ знак вопроса (?).​​равно аргументу​​ в ячейке​ используя ИНДЕКС и​​ ячеек.​

• ​ аргумента. В этом​​ не указан.​​ в ячейку C16​ датами. Например, мы​ ищем: точно (0),​в качестве более​​ ВПР в чистом​​ ограничение позволяет, например,​​и нажмите кнопку​​Продажи​Для решения этой задачи​
• ​ ближайшего значения. Например,​ Звездочка соответствует любой​искомое_значениеПросматриваемый_массив​А10​ ПОИСКПОЗ, если даны​​Примечания:​​ случае функция ЕСЛИ​Например, массив {«виноград»;»яблоко»;»груша»;»слива»} содержит​ название интересующего нас​ можем легко определить​ с округлением в​ мощной альтернативы ВПР​​ виде тут не​​ вариант с использованием​ОК​.​
• ​ можно использовать функцию​​ в списке на​​ последовательности знаков, знак​может быть не​содержится значение «яблоки»,​ 4 условия. При​Если в качестве аргумента​​ вернет текстовую строку​​ элементы, которые можно​ отдела – Маркетинговый.​ на каком этапе​ большую строну (-1)​ я уже подробно​ поможет, но есть​

​ функций ИНДЕКС() и​.​К началу страницы​

​ ВПР или сочетание​ картинке ниже нет​ вопроса соответствует любому​ упорядочен.​

Поиск позиции в массивах с текстовыми значениями

​ то формула =ПОИСКПОЗ​ этом в ответе​ искомое_значение была передана​​ «есть», иначе –​​ представить как: 1​

​В ячейку C17 введите​ сейчас находится наш​ или в меньшую​

​ описывал (с видео).​ несколько других способов​

​ ПОИСКПОЗ(). Эквивалентная формула​Следуйте инструкциям мастера.​Для выполнения этой задачи​ функций ИНДЕКС и​ значения 45, но​ одиночному знаку.​

​Если тип_сопоставления равен 1,​ («яблоки»;A9:A20;0) вернет 2,​ должна быть фраза​ текстовая строка, функция​ «нет».​

​ – «виноград», 2​ функцию со следующими​ проект:​ сторону (1)​ В нашем же​ решить эту задачу.​ приведена в статье​К началу страницы​​ используется функция ГПР.​​ ПОИСКПОЗ.​

​ можно найти позицию​Предположим, что имеется перечень​ то функция ПОИСКПОЗ()​ т.е. искомое значение​

Поиск позиции в массиве констант

​ «есть/нет». Если просто​ ПОИСКПОЗ вернет позицию​Чтобы вычислить остальные значения​ – «яблоко», 3​ аргументами:​Принципиальное ограничение функции​Давайте рассмотрим несколько полезных​

Поиск позиции с использованием подстановочных знаков

​ случае, можно применить​Это самый очевидный и​ о функции ВПР().​Для нахождения позиции значения​ См. пример ниже.​Дополнительные сведения см. в​ наибольшего значения, которое​ товаров и мы​ находит наибольшее значение,​ «яблоки» содержится во​ вписывать в формулу​ элемента в массиве​ «протянем» формулу из​

​ – «груша», 4​После ввода для подтверждения​ПОИСКПОЗ​ вариантов ее применения​ их для поиска​ простой (хотя и​Задача подразумевает, что диапазон​

​ в столбце, с​Функция ГПР выполняет поиск​ разделе, посвященном функции​ меньше либо равно,​ не знаем точно​ которое меньше либо​ второй ячейке диапазона​

​ всю таблицу, а​ (если такой существует)​ ячейки C2 вниз​ – «слива», где​​ функции нажмите комбинацию​​состоит в том,​ на практике.​

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию

​ по нескольким столбцам​ не самый удобный)​ поиска содержит неповторяющиеся​ последующим выводом соответствующего​ по столбцу​ ВПР.​

​ чем искомое значение,​ как записана товарная​​ равно, чем​​A9:A20А9​ потом в поискпоз​

​ без учета регистра​ для использования функции​ 1, 2, 3,​ горячих клавиш CTRL+SHIFT+Enter,​

​ что она умеет​

​Классический сценарий — поиск​ в виде формулы​ способ. Поскольку штатная​ значения. В самом​ значения из соседнего​Продажи​Что означает:​​ т.е. позицию значения​​ позиция относящаяся к​искомое_значениеПросматриваемый_массив​- первая ячейка​ забивать условия -​ символов. Например, строки​ автозаполнения. В результате​

​ 4 – ключи,​ так как она​

​ искать только в​

​ точного текстового совпадения​ массива. Для этого:​ функция​ деле, если критерию​ столбца в EXCEL,​

Поиск позиции в массивах с Числами

​и возвращает значение​=ИНДЕКС(нужно вернуть значение из​ 40.​ яблокам: яблоки или​​должен быть упорядочен​​ (предполагается, что в​

​ получается полная фигня.​ «МоСкВа» и «москва»​ получим:​

​ а названия фруктов​ должна выполнятся в​ одномерных массивах (т.е.​

​ для нахождения позиции​​Выделите пустую зеленую ячейку,​​ВПР (VLOOKUP)​ удовлетворяет сразу несколько​ существует специальная функция​ из строки 5 в​

​ C2:C10, которое будет​Это можно сделать с​ яблоко.​ по возрастанию: …,​​ ней не содержится​​ И не проще​

​ являются равнозначными. Для​Как видно, третьи элементы​ – значения. Тогда​ массиве. Если все​ строчке или столбце),​ нужного нам текста​ где должен быть​умеет искать только​ значений, то из​ ВПР(), но для​ указанном диапазоне.​ соответствовать ПОИСКПОЗ(первое значение​ помощью формулы =ПОИСКПОЗ(45;B31:B37;1)​В качестве критерия можно​

​ -2, -1, 0,​ значение «яблоки»),​

​ ли в ВПР​ различения регистров можно​ списков не совпадают.​

​ функция =ПОИСКПОЗ(«яблоко»;{«виноград»;»яблоко»;»груша»;»слива»};0) вернет​ сделано правильно в​ но никто не​ или числа в​ результат.​ по одному столбцу,​ какой строки выводить​ ее решения можно​Дополнительные сведения см. в​ «Капуста» в массиве​

Функции ПОИСКПОЗ() и ИНДЕКС()

​Обратите внимание, что тип​ задать»яблок*» и формула​ 1, 2, …,​А10​ просто сделать? В​ дополнительно использовать функцию​Пример 3. Найти ближайшее​ значение 2, являющееся​

​ строке формул появятся​ запрещает использовать сразу​ списке:​Введите в строке формул​

​ а не по​

​ соответствующее ему значение​ использовать также и​ разделе, посвященном функции​ B2:B10))​

​ сопоставления =1 (третий​ =ПОИСКПОЗ(«яблок*»;B53:B62;0) вернет позицию​ A-Z, ЛОЖЬ, ИСТИНА.​- вторая,​

​ самом файле есть​ СОВПАД.​ меньшее числу 22​ ключом второго элемента.​ фигурные скобки.​ два​

excel2.ru

Поиск значений в списке данных

​Если в качестве искомого​ в нее следующую​ нескольким, то нам​ из соседнего столбца?​ другие функции. Рассмотрим​ ГПР.​Формула ищет в C2:C10​ аргумент функции).​ текстового значения, начинающегося​ Если​А11​ и основная таблица​Если поиск с использованием​ в диапазоне чисел,​ Отсчет выполняется не​Как видно функция самостоятельно​ПОИСКПОЗ​ значения задать звездочку,​ формулу:​ нужно из нескольких​

В этой статье

​ Если все же​ задачу в случае​К началу страницы​

​ первое значение, соответствующее​3. Поиск позиции в​ со слова яблок​

​тип_сопоставления​- третья и​ и, собственно, условия,​ рассматриваемой функции не​

​ хранящихся в столбце​ с 0 (нуля),​ справилась с решением​

​а вложенных в​ то функция будет​Нажмите в конце не​

​ сделать один!​ диапазон поиска содержит​ текстовых значений.​

Поиск значений в списке по вертикали по точному совпадению

​Для выполнения этой задачи​ значению​ списке отсортированном по​ (если она есть​опущен, то предполагается,​

Примеры функции ВПР

​ т.д. (подсчет позиции​ которым нужно следовать.​ дал результатов, будет​

Примеры функций ИНДЕКС и ПОИСКПОЗ

​ таблицы Excel.​

​ как это реализовано​ поставленной задачи.​ИНДЕКС​ искать первую ячейку​ Enter, а сочетание​

​Добавим рядом с нашей​ повторяющиеся значения, то​Пусть в диапазоне​​ используется функция ГПР.​​Капуста​ убыванию выполняется аналогично,​ в списке).​​ что он равен​​ производится от верхней​

​Спасибо за любую​ возвращен код ошибки​Вид исходной таблицы данных:​

​ во многих языках​

Поиск значений в списке по вертикали по приблизительному совпадению

​​, чтобы реализовать двумерный​

​ с текстом и​​Ctrl+Shift+Enter​ таблицей еще один​ второй столбец из​

​А4:В15​Важно:​(B7), и возвращает​ но с типом​Подстановочные знаки следует использовать​ 1.​​ ячейки).​​ помощь и комментарии!​ #Н/Д.​Для поиска ближайшего большего​ программирования при работе​Чтобы функция ПОИСКПОЗ работала​ поиск по строке​ выдавать её позицию.​, чтобы ввести формулу​ столбец, где склеим​​ таблицы выше поясняет​​имеется таблица с​

​  Значения в первой​ значение в ячейке​ сопоставления = -1.​

​ только для поиска​

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

​Если​Функция ПОИСКПОЗ() возвращает позицию​vikttur​

​Если аргумент [тип_сопоставления] явно​​ значения заданному во​ с массивами, а​ с таблицей с​ и столбцу одновременно:​ Для поиска последней​ не как обычную,​ название товара и​ какое значение будет​ перечнем сотрудников и​ строке должны быть​ C7 (​

​ В этом случае​​ позиции текстовых значений​тип_сопоставления​ искомого значения, а​

​: Странное желание… Но​​ не указан или​​ всем столбце A:A​ с 1.​ двумя столбцами как​Во многих поисковых формулах​ текстовой ячейки можно​

​ а как формулу​​ месяц в единое​ выведено (обычно возвращается​ их зарплат (фамилии​ отсортированы по возрастанию.​100​ функция ПОИСКПОЗ() находит​ и​равен -1, то​ не само значение.​​ если нужно (простая,​​ принимает число 0,​

​ (числовой ряд может​

Поиск значений в списке по горизонтали по точному совпадению

​Функция ПОИСКПОЗ редко используется​ с одним мы​ очень часто приходится​

​ изменить третий аргумент​ массива.​​ целое с помощью​​ первое значение, удовлетворяющее​ сотрудников не повторяются).​В приведенном выше примере​

​).​ наименьшее значение, которое​Типом сопоставления​

​ функция ПОИСКПОЗ() находит​

Поиск значений в списке по горизонтали по приблизительному совпадению

​ Например: ПОИСКПОЗ(«б»;{«а»;»б»;»в»;»б»};0) возвращает​ не формула массива):​

​ для поиска частичного​​ пополняться новыми значениями)​ самостоятельно. Ее целесообразно​ использовали в ее​

​ использовать функцию ПОИСКПОЗ​Режим_поиска​Как это на самом​ оператора сцепки (&),​ критерию).​Требуется, введя в ячейку​ функция ГПР ищет​Дополнительные сведения см. в​ больше либо равно​

​= 0 (третий​ наименьшее значение, которое​ число 2 -​

​=—ЕЧИСЛО(ПОИСКПОЗ(G5&H5&I5&J5;ИНДЕКС($A$3:$A$40&$B$3:$B$40&$C$3:$C$40&$E$3:$E$40;);))​

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

​ совпадения текстовых значений​​ используем формулу массива​ применять в связке​ аргументах оператор &.​ как вспомогательную в​с нуля на​ деле работает:​ чтобы получить уникальный​

​Если диапазон поиска содержит​D4​ значение 11 000 в строке 3​ разделах, посвященных функциям​ чем искомое значение.​ аргумент функции).​ больше либо равно​ относительную позицию буквы​buchlotnik​ могут быть использованы​ (CTRL+SHIFT+ENTER):​ с другими функциями,​ Учитывая этот оператор​ комбинациях с другими​ минус 1:​

1. ​Функция ИНДЕКС выдает из​

2. ​ столбец-ключ для поиска:​​ повторяющиеся значения и​​фамилию сотрудника, вывести​​ в указанном диапазоне.​​ ИНДЕКС и ПОИСКПОЗ.​​Функции ПОИСКПОЗ() и ИНДЕКС()​​Функция ПОИСКПОЗ() возвращает только​

3. ​ чем​​ «б» в массиве​​: видимо как-то так​ подстановочные знаки («?»​

   ​=B2;A:A;»»));A:A;0);1)’ class=’formula’>​

4. ​ например, ИНДЕКС.​​ первый аргументом для​ ​ функциями такими как:​Числа и пустые ячейки​​ диапазона цен C2:C161​​Теперь можно использовать знакомую​​ требуется вернуть не​​ в другой ячейке​

5. ​ Значение 11 000 отсутствует, поэтому​​К началу страницы​​ часто используются вместе,​​ одно значение. Если​​искомое_значениеПросматриваемый_массив​​ {«а»;»б»;»в»;»б»}. Позиция второй​​=ЕСЛИ(ЕНД(ПОИСКПОЗ(G5&H5&I5&J5;$A$3:$A$40&$B$3:$B$40&$C$3:$C$40&$E$3:$E$40;0));»нет»;»есть»)​

6. ​ — замена одного​​Функция ПОИСКПОЗ возвращает позицию​​​ функции теперь является​​ ИНДЕКС, ВПР, ГПР​​ в этом случае​​ содержимое N-ой ячейки​​ функцию​

7. ​ одно, а все​

​ его зарплату. Решение​

support.office.com

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца

​ она ищет следующее​Для выполнения этой задачи​ т.к. позволяют по​ в списке присутствует​должен быть упорядочен​ буквы «б» будет​сделайте, раз проще​ любого символа, «*»​ элемента в столбце​Пример 1. Найти позицию​ значение FordМаркетинговый. По​ и др. Но​

​ игнорируются.​​ по порядку. При​​ВПР (VLOOKUP)​ значения, удовлетворяющие критерию,​ приведено в файле​ максимальное значение, не​

Задача

​ используется функция ВПР.​​ найденной позиции в​​ несколько значений, удовлетворяющих​ по убыванию: ИСТИНА,​ проигнорирована, функция вернет​Acya96​ — замена любого​

Решение

​ A:A, имеющего максимальное​

• ​ первого частичного совпадения​ этой причине первый​ какую пользу может​
• ​Если последний аргумент задать​ этом порядковый номер​
• ​для поиска склеенной​ то читайте статью​ примера.​

​ превышающее 11 000, и возвращает​Важно:​ одном диапазоне вывести​ критерию, то эта​ ЛОЖЬ, Z-A, …,​ позицию только первой​:​ количества символов).​ значение среди чисел,​ строки в диапазоне​ Ford из отдела​ приносить данная функция​ равным 1 или​ нужной ячейки нам​

​ реализовать поиск ближайшего​ Она ищет связку​из ячеек H3​Совет​ значение, совпадающее с​ разделе, посвященном функции​ отсортированы по возрастанию.​ пример.​ значениями в диапазоне​ и так далее.​ позиции искомого значения​ ни за что​

​ аргумента просматриваемый_массив, содержится​ B2. Функция ИНДЕКС​Вид исходной таблицы данных:​ функции два форда​ ПОИСКПОЗ понятно, что​ наименьшего или наибольшего​ названия товара и​ и J3 в​: Если в диапазон​ критерием;​ ГПР.​В приведенном выше примере​Найдем количество заданного товара​B66:B72​Функция ПОИСКПОЗ() не различает​ читайте ниже в​ не догадалась. Даже​ два и больше​

​ возвращает значение, хранящееся​Для нахождения позиции текстовой​ – это разные​ ее главная задача​ числа. Таблица при​ месяца (​ созданном ключевом столбце:​ поиска постоянно вводятся​

​определяем номер позиции (строку)​​К началу страницы​ функция ВПР ищет​ на определенном складе.​. Найдем все позиции​ РеГИстры при сопоставлении​ разделе Поиск позиций​ не подумала про​ элементов, соответствующих искомому​ в найденной ячейке.​ строки в таблице​ значения (FordПродажи и​ заключается в определении​

​ этом обязательно должна​НектаринЯнварь​Плюсы​ новые значения, то​ найденного значения;​

excel2.ru

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

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

​Примечание:​ имя первого учащегося​ Для этого используем​ значения Груши.​ текстов.​​ ВСЕХ текстовых значений,​​ ЕЧИСЛО. Спасибо вам​​ значению, будет возвращена​​Результат расчетов:​ используем следующую формулу:​ FordМаркетинговый). Просматриваемый диапазон​ позиции исходного значения,​ быть отсортирована по​) по очереди во​: Простой способ, знакомая​ для исключения ввода​выводим значение из соседнего​ Поддержка надстройки «Мастер подстановок»​ с 6 пропусками в​ формулу​Значение Груши находятся в​Если функция ПОИСКПОЗ() не​ удовлетворяющих критерию.​ огромное!​ позиция первого вхождения​

​Для поиска ближайшего меньшего​=ПОИСКПОЗ(D2&»*»;B:B;0)-1​ теперь распространяется на​ которое содержит диапазон​

​ возрастанию или убыванию​ всех ячейках склеенного​ функция, работает с​​ дубликатов следует наложить​​ столбца той же​ в Excel 2010​​ диапазоне A2:B7. Учащихся​​=ИНДЕКС(B67:E71;ПОИСКПОЗ(A74;A67:A71;0);ПОИСКПОЗ(C74;B66:E66;0))​ позициях 2 и​​ находит соответствующего значения,​​ПОИСКПОЗискомое_значение просматриваемый_массив​А желание и,​ такого элемента.​ значения достаточно лишь​Описание аргументов:​ 2 столбца, так​ ячеек или таблица.​

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

​ соответственно. В общем​ из двух столбцов​ любыми данными.​ определенные ограничения (см.​ строки.​​ прекращена. Эта надстройка​​ с​В файле примера, соответствующий​ 5 списка. С​ то возвращается значение​; тип_сопоставления)​ правда, крайне странное​

​R007​ немного изменить данную​D2&»*» – искомое значение,​ же благодаря оператору​ Применять эту функцию​ и целом, это​ диапазона A2:A161&B2:B161 и​Минусы​ статью Ввод неповторяющихся​

​Решение практически аналогично поиску​ была заменена мастером​​6​​ столбец и строка​ помощью формулы массива​​ ошибки #Н/Д.​​Искомое_значение​Acya96​: Всем добрый день.​

​ формулу и ее​​ состоящее и фамилии,​ &, который мы​ очень просто для​

​ чем-то похоже на​​ выдает порядковый номер​: Надо делать дополнительный​ значений). Для визуальной​ числового значения из​ функций и функциями​ пропусками в таблице нет,​ выделены с помощью​=(«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65))​Произведем поиск позиции в​- значение, используемое​:​

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

​Столкнулся со следующей​ следует также ввести​ указанной в ячейке​ применяем во втором​ диапазонов или таблиц​ интервальный просмотр у​​ ячейки, где нашла​​ столбец и потом,​ проверки наличия дубликатов​ статьи Поиск позиции​ для работы со​ поэтому функция ВПР​ Условного форматирования.​можно найти все эти​ НЕ сортированном списке​ при поиске значения​buchlotnik​ проблемой. Как осуществлять​ как массив (CTRL+SHIFT+ENTER):​ B2, и любого​ аргументе для склейки​

​ с одним столбцом​​ функции​ точное совпадение. По​ возможно, еще и​ можно использовать Условное​ ЧИСЛА с выводом​

​ ссылками и массивами.​​ ищет первую запись​СОВЕТ: Подробнее о поиске​ позиции. Для этого​ текстовых значений (диапазон​ в​, спасибо большое!​ ПОИСПОЗ по двум​

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

​Результат поиска:​ количества других символов​​ значений из двух​​ или с одной​​ВПР (VLOOKUP)​​ сути, это первый​ прятать его от​ форматирование (см. статью​ соответствующего значения из​В Excel 2007 мастер​ со следующим максимальным​ позиций можно прочитать​ необходимо выделить несколько​B7:B13​просматриваемом_массивеИскомое_значение​

1. ​Тут просто сама​ условиям?​Функция имеет следующую синтаксическую​
2. ​ (“*”);​ смежных диапазонов. Таким​ строкой. Поэтому сразу​
   
3. ​, но там возможен​ способ, но ключевой​​ пользователя. При изменении​​ Выделение повторяющихся значений).​ соседнего столбца. Для​ подстановок создает формулу​ значением, не превышающим​

​ в соответствующем разделе​ ячеек (расположенных вертикально),​

​)​может быть значением​ суть задания сделать​Есть следующая задача:​ запись:​B:B – ссылка на​ образом, значения берутся​ усложним задачу и​ только поиск ближайшего​ столбец создается виртуально​​ числа строк в​​Для организации динамической сортировки​ этого типа задач​ подстановки, основанную на​ 6. Она находит​ сайта: Поиск позиции.​ в Строке формул​Столбец Позиция приведен для​ (числом, текстом или​ через ИНДЕКС и​Стройматериалы у них​=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​ столбец B:B, в​ одновременно из двух​

​ на конкретном примере​​ наименьшего, а здесь​ прямо внутри формулы,​ таблице — допротягивать​ пополняемого диапазона поиска​

​ в EXCEL существует​​ данных листа, содержащих​ значение 5 и возвращает​С помощью функций ПОИСКПОЗ()​ ввести вышеуказанную формулу​ наглядности и не​ логическим значением (ЛОЖЬ​ ПОИСКПОЗ. Для меня​ есть по паре​Описание аргументов:​ котором выполняется поиск;​ столбцов Автомобиль и​ проиллюстрируем как применять​ — есть выбор.​

planetaexcel.ru

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

​ а не в​ формулу сцепки на​ можно использовать идеи​ специальная функция ВПР(),​ названия строк и​ связанное с ним​ и ИНДЕКС() можно​ и нажать​ влияет на вычисления.​ или ИСТИНА)) или​

​ это немного странно​

​ характеристик, как сделать​

​искомое_значение – обязательный аргумент,​

• ​0 – поиск точного​​ Отдел.​ функцию ПОИСКПОЗ для​
• ​Например, нам нужно выбрать​​ ячейках листа.​ новые строки (хотя​ из статьи Сортированный​ но для ее​
• ​ столбцов. С помощью​​ имя​ заменить функцию ВПР(),​CTRL+SHIFT+ENTER​Формула для поиска позиции​ ссылкой на ячейку,​ и непонятно:/​

​ так что бы​ принимающий текстовые, числовые​ совпадения.​

Точный поиск

​Читайте также: Функции ИНДЕКС​ таблицы с двумя​ генератор из прайс-листа​Плюсы​ это можно упростить​ список.​

Поиск первой или последней текстовой ячейки

​ решения можно использовать​ мастера подстановок можно​Алексей​ об этом читайте​. В позициях, в​ значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)​ содержащую число, текст​vikttur​ при выборе из​​ значения, а также​​Из полученного значения вычитается​ и ПОИСКПОЗ в​

​ столбцами и более.​ для расчетной мощности​: Не нужен отдельный​

Поиск ближайшего числа или даты

​ применением умной таблицы).​Если вы продвинутый пользователь​ и другие функции​ найти остальные значения​.​ в статье о​ которых есть значение​Формула находит первое значение​ или логическое значение.​: Видимо, проверяют Вас​ пары характеристик выбиралась​ данные логического и​ единица для совпадения​ Excel и примеры​​Для примера возьмем список​​ в 47 кВт.​ столбец, работает и​Если нужно найти именно​ Microsoft Excel, то​

​ (про функцию ВПР()​ в строке, если​Дополнительные сведения см. в​ функции ВПР().​ Груши будет выведено​ сверху и выводит​Просматриваемый_массив​ на знание материала​ нужная исходя из​ ссылочного типов, который​ результата с id​​ их использования​​ автомобилей из автопарка​

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

Связка функций ПОИСКПОЗ и ИНДЕКС

​ соответствующее значение позиции,​ его позицию в​— непрерывный диапазон​ )​​ заранее выставленных условий.​ ​ используется в качестве​​ записи в таблице.​Функция ПОИСКПОЗ в Excel​ средней фирмы, как​ задать равным 1​ с текстом.​

​ случае цена как​ с функцией поиска​Решение​ одном столбце, и​ ВПР.​

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

​Минусы​​ раз число), то​​ и подстановки​Если несколько значений удовл.​ наоборот. В формулах,​К началу страницы​ сотрудника по его​ быдет выведен 0.​ Груши учтено не​ искомые значения.​​ Проще -​​ ответы.​​ сопоставления величин или​​Пример 2. В Excel​ точного совпадения или​ рисунке:​

planetaexcel.ru

Пример функции ПОИСКПОЗ в Excel без функции ИНДЕКС

​ по возрастанию, то​: Ощутимо тормозит на​ вместо ВПР можно​ВПР​ критерию​ которые создает мастер​Для выполнения этой задачи​ идентификационному номеру или​C помощью другой формулы​ будет.​Просматриваемый_массив​СЧЁТЕСЛИМН(), СУММПРОИЗВ()​Serge_007​ нахождения точного совпадения);​ хранятся две таблицы,​ ближайшего (меньшего или​В обеих столбцах названия​ мы найдем ближайшую​ больших таблицах (как​ использовать функцию​или​=ВПР($D$4;A4:B15;2;ЛОЖЬ)​ подстановок, используются функции​ используются функции СМЕЩ​ узнать ставку комиссионного​ массива​Чтобы найти номер строки,​может быть только​buchlotnik​: Здравствуйте.​

Пример работы ПОИСКПОЗ по двум столбцам Excel

​просматриваемый_массив – обязательный аргумент,​ которые на первый​ большего заданному в​ автомобилей и отделов​ наименьшую по мощности​

​ и все формулы​СУММЕСЛИМН (SUMIFS)​VLOOKUP​берется​ ИНДЕКС и ПОИСКПОЗ.​ и ПОИСКПОЗ.​ вознаграждения, предусмотренную за​=НАИБОЛЬШИЙ((«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65));СТРОКА()-СТРОКА($D$65))​ а не позиции​ одностолбцовым диапазоном ячеек,​: ну куда тут​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(A1:C3;ПОИСКПОЗ(A8;A1:A3;0);ПОИСКПОЗ(A6;A1:C1;0))​ принимающий данные ссылочного​ взгляд кажутся одинаковыми.​ зависимости от типа​ повторяются, но нет​ модель (​ массива, впрочем), особенно​

​, появившуюся начиная с​(если еще нет,​первое​Щелкните ячейку в диапазоне.​Примечание:​ определенный объем продаж.​можно отсортировать найденные позиции,​ в искомом диапазоне,​ например​ его? давайте так​R007​ типа (ссылки на​ Было решено сравнить​ сопоставления, указанного в​ парных дубликатов. Например,​

1. ​Зверь​ если указывать диапазоны​ Excel 2007. По​ то сначала почитайте​сверху​
2. ​На вкладке​ Данный метод целесообразно использовать​ Необходимые данные можно​
3. ​ чтобы номера найденных​ можно записать следующую​А9:А20​=ИНДЕКС({«есть»;»нет»};1+ЕНД(ПОИСКПОЗ(G5&H5&I5&J5;$A$3:$A$40&$B$3:$B$40&$C$3:$C$40&$E$3:$E$40;0)))​:​ диапазон ячеек) или​ по одному однотипному​ качестве аргумента) значения​ в списке 2​

​):​ «с запасом» или​ идее, эта функция​

​ эту статью, чтобы​

Описание примера как работает функция ПОИСКПОЗ:

​=ИНДЕКС(B4:B15; ПОИСКПОЗ($D$4;A4:A15;0);1)​Формулы​ при поиске данных​ быстро и эффективно​ позиций отображались в​ формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)​или диапазоном, расположенным​_Boroda_​Serge_007​ константу массива, в​ столбцу этих таблиц​ заданному в массиве​ автомобиля марки Ford,​Если же третий аргумент​ сразу целые столбцы​ выбирает и суммирует​ им стать). Для​берется​в группе​ в ежедневно обновляемом​ находить в списке​ первых ячейках (см.​Если искомое значение не​ в одной строке,​: Еще вариант формулой​,​ которых выполняется поиск​ на наличие несовпадений.​ или диапазоне ячеек​ но оба они​

​ равен -1 и​ (т.е. вместо A2:A161​ числовые значения по​ тех, кто понимает,​

exceltable.com

Пример функции ПОИСКПОЗ для поиска совпадения значений в Excel

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

Примеры использования функции ПОИСКПОЗ в Excel

​ из разных отделов.​ таблица отсортирована по​ вводить A:A и​ нескольким (до 127!)​ рекламировать ее не​сверху​выберите команду​ Известна цена в​ их правильность. Значения,​1. Произведем поиск позиции​ то будет возвращено​

​А2:Е2​ нажатием Контрл Шифт​ помогло!​ критерию, заданному первым​ двух диапазонов ячеек.​ позиции найденного элемента.​ Если мы захотим​ убыванию, то мы​ т.д.) Многим непривычны​ условиям. Но если​ нужно -​=СМЕЩ($B$3; ПОИСКПОЗ($D$4;$A$4:$A$15;0);0;1;1)​Подстановка​ столбце B, но​ возвращенные поиском, можно​ в НЕ сортированном​

​ значение ошибки #Н/Д.​. Таким образом формула​ Ентер)​Гость​ аргументом функции;​Вид таблицы данных:​Например, имеем последовательный ряд​ узнать номер позиции​ найдем ближайшую более​ формулы массива в​ в нашем списке​ без нее не​берется​.​ неизвестно, сколько строк​ затем использовать в​ списке числовых значений​ Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0)​ =ПОИСКПОЗ(«слива»;A30:B33;0) работать не​=ИНДЕКС({«Нет»:»Есть»};1+ЕЧИСЛО(ПОИСКПОЗ(G5&H5&I5&J5;A$3:A$40&B$3:B$40&C$3:C$40&E$3:E$40;)))​: Помогите!!!!!!!!!!!!!! плиз!!!!!!!!!!!!!! уже​

​[тип_сопоставления] – необязательный для​Для сравнения значений, находящихся​ чисел от 1​ этого автомобиля, то​ мощную модель (​

​ принципе (тогда вам​

Формула для поиска неточного совпадения текста в Excel

​ нет повторяющихся товаров​ обходится ни один​первое​Если команда​ данных возвратит сервер,​

​ вычислениях или отображать​

​ (диапазон​ вернет ошибку, т.к.​ будет (выдаст ошибку​

​И проверка там​

​ все перепробовала не​

• ​ заполнения аргумент в​ в столбце B:B​ до 10, записанных​ в результате функция​Бомба​ сюда).​
• ​ внутри одного месяца,​ сложный расчет в​сверху​
• ​Подстановка​ а первый столбец​

​ как результаты. Существует​B8:B14​ значения «грейпфрут» в​ #Н/Д), так как​

​ еще Условным форматированием​

Сравнение двух таблиц в Excel на наличие несовпадений значений

​ получается. Нужно чтобы​ виде числового значения,​ со значениями из​ в ячейках B1:B10.​ ПОИСПОЗ вернет нам​):​Основное назначение этой функции​ то она просто​ Excel. Есть, однако,​=ДВССЫЛ(АДРЕС(НАИБОЛЬШИЙ( ЕСЛИ(($A$4:$A$15=$D$4); СТРОКА($A$4:$A$15));1);2))​

​недоступна, необходимо загрузить​

​ не отсортирован в​ несколько способов поиска​)​ диапазоне ячеек​Просматриваемый_массив​iMrTidy​

​ во втором ПОИСКПОЗ​ определяющего способ поиска​ столбца A:A используем​ Функция =ПОИСКПОЗ(3;B1:B10;0) вернет​ позицию в диапазоне​Очень часто функция ПОИСКПОЗ​ в том, чтобы​ выведет значение цены​ одна проблема: эта​берется​ надстройка мастера подстановок.​ алфавитном порядке.​ значений в списке​Столбец Позиция приведен для​B7:B13​представляет собой диапазон​: Acya96, возможно, так?​ был «приближенный поиск».​ в диапазоне ячеек​ следующую формулу массива​ число 3, поскольку​ где встречается первое​ используется в связке​ искать позицию заданного​

​ для заданного товара​ функция умеет искать​последнее​Загрузка надстройки мастера подстановок​C1​ данных и отображения​

​ наглядности и не​нет.​

Поиск ближайшего большего знания в диапазоне чисел Excel

​ ячеек размещенный одновременно​=INDEX($A$3:$A$40;MATCH(J5;$E$3:$E$40;0))&INDEX($B$3:$B$40;MATCH(J5;$E$3:$E$40;0))&INDEX($C$3:$C$40;MATCH(J5;$E$3:$E$40;0))=G5&H5&I5​ А как файл​ или массиве. Может​ (CTRL+SHIFT+ENTER):​

​ искомое значение находится​

​ значение – 3.​ с другой крайне​ элемента в наборе​ и месяца:​ данные только по​сверху​Нажмите кнопку​

​ — это левая верхняя​

​ результатов.​ влияет на вычисления.​В файле примера можно​ в нескольких столбцах​Acya96​ прикрепить? что-то не​ принимать следующие значения:​Функция ПОИСКПОЗ выполняет поиск​ в ячейке B3,​

​ То есть Ford​

​ полезнойфункцией -​ значений. Чаще всего​Плюсы​ совпадению одного параметра.​=ПРОСМОТР($D$4;$A$4:$A$15;$B$4:$B$15)​Microsoft Office​

​ ячейка диапазона (также​

Особенности использования функции ПОИСКПОЗ в Excel

​Поиск значений в списке​Найдем позицию значения 30​

​ найти применение функции​

​ и нескольких ячейках.​

• ​: Еще раз спасибо.​ пойму​-1 – поиск наименьшего​ логического значения ИСТИНА​ которая является третьей​ из отдела продаж:​ИНДЕКС​ она применяется для​: Не нужен дополнительный​
• ​ А если у​если столбец отсортирован по​, а затем —​ называемая начальной ячейкой).​ по вертикали по​ с помощью формулы​ при поиске в​Тип_сопоставления​ Пойду разбираться, что​
• ​RAN​ ближайшего значения заданному​ в массиве логических​ от точки отсчета​Что же делать если​(INDEX)​ поиска порядкового номера​

1. ​ столбец, решение легко​ нас их несколько?​ возрастанию, то берется​ кнопку​Формула​ точному совпадению​
2. ​ =ПОИСКПОЗ(30;B8:B14;0)​ горизонтальном массиве.​— число -1,​ да как.​: Чукча не читатель.​ аргументом искомое_значение в​ значений, возвращаемых функцией​ (ячейки B1).​
3. ​ нас интересует Ford​, которая умеет извлекать​ ячейки в диапазоне,​ масштабируется на большее​Предположим, что у нас​последнее​

​Параметры Excel​

1. ​ПОИСКПОЗ(«Апельсины»;C2:C7;0)​Поиск значений в списке​Формула ищет​Поиск позиции можно производить​ 0 или 1.​jakim​ Чукча писатель!​ упорядоченном по убыванию​ СОВПАД (сравнивает каждый​Данная функция удобна для​ из маркетингового отдела?​ данные из диапазона​ где лежит нужное​
2. ​ количество условий (до​ есть база данных​сверху, если нет,​и выберите категорию​ищет значение «Апельсины»​
3. ​ по вертикали по​точное​ не только в​Тип_сопоставления​: Вариант с формулой​Как файл приложить​ массиве или диапазоне​ элемент диапазона A2:A12​ использования в случаях,​ Кроме того, мы​ по номеру строки-столбца,​
4. ​ нам значение.​ 127), быстро считает.​ по ценам товаров​ то результат​Надстройки​ в диапазоне C2:C7.​ приблизительному совпадению​значение 30. Если​

exceltable.com

ПОИСКПОЗ по двум условиям (помощь)

​ диапазонах ячеек, но​​указывает, как MS​
​=IF(MAX(INDEX((A$3:A$400=G6)*(B$3:B$400=H6)*(C$3:C$400=I6)*(E$3:E$400=J6);0))=1;»есть»;»нет»)​ — красненькая строчка​ ячеек.​ со значением, хранящимся​
​ когда требуется вернуть​
​ хотим использовать только​ реализуя, фактически, «левый​Синтаксис этой функции следующий:​Минусы​ за разные месяцы:​непредсказуем​.​ Начальную ячейку не​

​Поиск значений по вертикали​ в списке его​

​ и в массивах​​ EXCEL сопоставляет​

​buchlotnik​

​ сверху.​​0 – (по умолчанию)​​ в ячейке B2,​​ не само значение,​
​ функцию ПОИСПОЗ, не​ ВПР».​

​=ПОИСКПОЗ(Что_ищем; Где_ищем; Режим_поиска)​​: Работает только с​Нужно найти и вытащить​=СУММПРОИЗВ((A4:A15=D4)*(B4:B15))​В поле​ следует включать в​ в списке неизвестного​ нет, то будет​ констант. Например, формула​

​искомое_значение​​:​Как сделать «приближенный​
​ поиск первого значения​ и возвращает массив​ содержащееся в искомой​
​ прибегая к формулам​Так, в предыдущем примере​

​где​​ числовыми данными на​ цену заданного товара​соответствующие значения суммируются​
​Управление​

excelworld.ru

Индекс и Поискпоз с несколькими условиями (Формулы/Formulas)

​ этот диапазон.​​ размера по точному​
​ возвращена ошибка #Н/Д.​ =ПОИСКПОЗ(«груши»;{«яблоки»;»ГРУШИ»;»мандарины»};0) вернет значение​со значениями в​jakim​ поиск» — F1​ в массиве или​ результатов сравнения). Если​ ячейке, а ее​ с комбинациями других​ получить не номер,​Что_ищем​ выходе, не применима​ (​=СУММЕСЛИ(A4:A15;D4;B4:B15)​выберите значение​1​ совпадению​2. Произведем поиск позиции​ 2.​ аргументе​, а ПОИСКПОЗ()-то где?​
​Il_sun​ диапазоне ячеек (не​

​ функция ПОИСКПОЗ нашла​​ координату относительно рассматриваемого​ функций ИНДЕКС и​ а название модели​
​- это значение,​

​ для поиска текста,​​Нектарин​​соответствующие значения суммируются​​Надстройки Excel​

​ — это количество столбцов,​​Поиск значений в списке​​ в отсортированном по​​Если искомое значение точно​просматриваемый_массив.​jakim​: в отношении прикрепленного​ обязательно упорядоченном), которое​ значение ИСТИНА, будет​
​ диапазона. В случае​ т.п. Выход из​

​ генератора можно очень​​ которое надо найти​​ не работает в​​) в определенном месяце​
​=БИЗВЛЕЧЬ(A3:B15;2;F15:F16)​и нажмите кнопку​ которое нужно отсчитать​ по горизонтали по​ возрастанию списке числовых​ не известно, то​

​Если​​: Пожалуйста ПОИСКПОЗ только​ файла формула должна​ полностью совпадает со​
​ возвращена позиция его​ использования для констант​​ этой ситуации находится​

​ легко:​​Где_ищем​ старых версиях Excel​​ (​

​возвращается ошибка #ЧИСЛО!​​Перейти​ справа от начальной​ точному совпадению​ значений (диапазон​
​ с помощью подстановочных​
​тип_сопоставления​ наоборот.​

​ иметь вид​​ значением, переданным в​
​ первого вхождения в​

​ массивов, которые могут​​ в определении настроек​Ну, и поскольку Excel​- это одномерный​

​ (2003 и ранее).​​Январь​
​Для функции ВПР() требуется,​

​.​​ ячейки, чтобы получить​​Поиск значений в списке​​B31:B37​

​ знаков можно задать​​равен 0, то​=IFERROR(MATCH(1;INDEX((B$3:B$400=H5)*(C$3:C$400=I5)*(D$3:D$400=J5)*(F$3:F$400=K5);0);0);»нет»)​
​200?'200px':''+(this.scrollHeight+5)+'px');">=ИНДЕКС(B2:C3;ПОИСКПОЗ(A8;A2:A3;0);ПОИСКПОЗ(A6;B1:C1;0))​

excelworld.ru

​ качестве первого аргумента.​

Хитрости »

18 Январь 2017              121289 просмотров

ВПР по двум и более критериям

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

Для большего понимания смысла статьи рекомендую сначала скачать файл с примером данных и формул:
Скачать пример:

  Tips_All_VlookupMany.xls (62,5 KiB, 10 163 скачиваний)

Например, имеется файл с таблицей такого вида:

И необходимо получить сумму выручки не только на основании салона, но и на основании бренда. При этом делать это автоматически, например для получения данных в такую табличку:

Т.е. в ячейке B2 из выпадающего списка выбирается Салон, а из B3 — Бренд(подробнее про выпадающие списки можно прочесть в статье Выпадающие списки). И в зависимости от выбора должна рассчитаться сумма Выручки и должен проставиться комментарий.

В приложенном к статье файле исходная таблица находится на листе «Отчет сентябрь», а вторая(с выбором) на листе «Выбор». На самом деле это не сильно принципиально, просто будет более понятна логика формул, приведенных ниже.
Сумму по двум критериям можно найти при помощи той же СУММЕСЛИМН(SUMIFS):
=СУММЕСЛИМН(‘Отчет сентябрь’!$C$2:$C$67;’Отчет сентябрь’!$A$2:$A$67;B2;’Отчет сентябрь’!$B$2:$B$67;B3)
=SUMIFS(‘Отчет сентябрь’!$C$2:$C$67,’Отчет сентябрь’!$A$2:$A$67,Выбор!B2,’Отчет сентябрь’!$B$2:$B$67,Выбор!B3)
Подробнее про поиск сумм по двум и более критериям можно прочитать в статье Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям.
А вот с комментарием сложнее — там содержится текст и СУММЕСЛИМН ничего не найдет. Да и в случае, если в исходных данных есть задвоения и суммировать по определению ничего не надо СУММЕСЛИМН будет не лучшим вариантом — ведь она вернет сумму всех ячеек, удовлетворяющих условиям. А это, повторюсь — не всегда нужно.
Здесь на помощь придет родственная с ВПР функция — ПОИСКПОЗ(MATCH):
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67,0))
Эта функция является формулой массива. Это означает, что вводить её в ячейки надо нажатием не просто Enter, а сочетанием трех клавиш — Ctrl+Shift+Enter. Теперь рассмотрим подробнее принцип работы этой формулы, чтобы можно было применить на любых данных. На самом деле принцип не так уж сложен. Основной упор сделан на эти две «связки»:

• B2&B3 — здесь объединяем значение выбранного Салона(Санкт-Петербург) и Бренда(Ниссан) в одну строку, чтобы получилось «Санкт-ПетербургНиссан». За объединение двух значений отвечает амперсанд(&).
• ‘Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67 — а здесь мы последовательно объединяем в одну строку значения двух столбцов исходных данных — Салон и Бренд. Т.е. в результате мы получим массив объединенных значений: МоскваBMW, ВоронежBMW, СамараЛексус и т.д.(именно это объединение требует, чтобы формула вводилась как формула массива). И уже среди этих значений ищем «Санкт-ПетербургНиссан». При нахождении совпадения ПОИСКПОЗ вернет позицию строки, в которой нашел. И передаст её в ИНДЕКС. А т.к. ИНДЕКС в нашем случае возвращает значение из заданной строки указанного массива, то мы получаем требуемое.

По шагам это будет выглядеть так:

сначала искомые значения будут объединены в один
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-Петербург&Ниссан;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-ПетербургНиссан;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
далее так же объединяем построчно значения всех столбцов для поиска значений
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-ПетербургНиссан;{«МоскваBMW»:»ВоронежBMW»:»СамараЛексус»:…};0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;4)
=>
только допы

Здесь важно следить за тем, чтобы значения в исходном массиве для поиска объединялись в том же порядке, что и искомые значения. На приложенном примере, если первым аргументом(искомое_значение) в ПОИСКПОЗ мы указали сначала Салон(B2), а потом Бренд(B3), то и вторым аргументом(просматриваемый_массив) мы должны указать два столбца в том же порядке — сначала Салон(‘Отчет сентябрь’!$A$2:$A$67), а уже потом Бренд(‘Отчет сентябрь’!$B$2:$B$67). Если их перепутать ошибки как таковой это не вызовет, но и значения найдены не будут.
Так же стоит учитывать, что такая формула будет рассчитываться дольше обычной, поэтому не стоит указывать столбцы полностью(‘Отчет сентябрь’!$A:$A), т.к. это может повлечь за собой расчет всего одной формулы непозволительно долгое время.
При этом очевидно, что просматривать значения можно не только из смежных столбцов, а из любых и уж точно они могут быть и первыми, и последними. И значения возвращаемые могут так же располагаться в любом столбце таблицы. И также хочу заметить, что это в примере используется только два критерия — реально их можно сделать и три, и пять, и десять. Объединяем сколько надо, указываем нужное кол-во столбцов так же с объединением и все. Но тут надо понимать, что в некоторых случаях будет оптимальнее добавить в исходные данные(те, где ищем) еще один столбец, в котором записать все данные, объединив их. Т.е. опять же на примере файла из статьи можно на листе «Отчет сентябрь» добавить формулу в столбец E: =A2&B2
и затем искать можно будет обычной формулой:
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$E$2:$E$67;0))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$E$2:$E$67,0))
Больше нюансов, о которых я бы хотел рассказать, нет. Все стандартно, как для обычной связки ИНДЕКС(ПОИСКПОЗ.

В приложенном к статье примере приведен пример и с функцией СУММЕСЛИМН и с функцией ПОИСКПОЗ.
Скачать пример:

  Tips_All_VlookupMany.xls (62,5 KiB, 10 163 скачиваний)

P.S. Если кого-то пугает тот факт, что формулу надо вводить как формулу массива(Ctrl+Shift+Enter), то можно модифицировать формулу так:
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;СУММПРОИЗВ(МАКС((B2=’Отчет сентябрь’!$A$2:$A$67)*(B3=’Отчет сентябрь’!$B$2:$B$67)*(СТРОКА($B$2:$B$67)-1))))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,SUMPRODUCT(MAX((B2=’Отчет сентябрь’!$A$2:$A$67)*(B3=’Отчет сентябрь’!$B$2:$B$67)*(ROW($B$2:$B$67)-1))))
Здесь вместо ПОИСКПОЗ роль поиска номера строки в массиве ‘Отчет сентябрь’!$D$2:$D$67 играет СУММПРОИЗВ. Сам принцип работы СУММПРОИЗВ я описывал в этой статье — Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям. К этому могу добавить лишь некоторые разъяснения(хотя если хотите понять сам принцип — лучше потратить пару минут на статью).
(B2=’Отчет сентябрь’!$A$2:$A$67) — здесь значение выбранного салона сверяется со списком салонов на листе Отчет сентябрь. Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE).
(B3=’Отчет сентябрь’!$B$2:$B$67) — здесь значение выбранного бренда сверяется со списком брендов на листе Отчет сентябрь. Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE).
В результате перемножения этих двух выражений мы получим массив 1 и 0(1 будет там, где бренд и салон совпали, 0 — где различаются), т.к. ИСТИНА для Excel по сути =1, а ЛОЖЬ =0.
Далее полученный массив единиц и нулей перемножаем на выражение: СТРОКА($B$2:$B$67). Это выражение дает нам массив номеров строк {2:3:4:5:6:7:8 … и т.д.}т.к. в выражении берется номер строки на листе, а нам нужен номер в диапазоне ‘Отчет сентябрь’!$D$2:$D$67, то мы еще вычитаем 1(т.к. диапазон у нас начинается со второй строки). С таким же успехом можно было не вычитать, а либо все диапазоны взять с первой строки, либо указать так: СТРОКА($B$1:$B$66)
Полученный массив строк перемножаются на массив единиц и нулей салона и бренда. В результате получим номер строки, умноженный на 1 там, где салон и бренд равны искомым, и на ноль там, где отличаются. И в итоге массив номеров строки и нулей. И из этого отбирается максимальный номер строки.
По сути, это уже решает проблему поиска, но если значений, подходящих под условия больше одного, то именно такой результат может быть(даже скорее всего будет) неверным. Чтобы этого избежать мы применяем функцию МАКС(MAX), чтобы в результате из всех строк была отобрана только максимальная. Т.е. в результате мы получим не первое совпадение из всех возможных, а последнее.
Так же рекомендую ознакомиться со статьей Как просмотреть этапы вычисления формул, чтобы можно было самостоятельно поразбираться в принципах работы формул.

В сети можно найти так же решения при помощи макросов(VBA), но далеко не всегда есть возможность использовать макросы в файлах.

Также см.:
Как найти значение в другой таблице или сила ВПР
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

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

Работа функции ВПР по нескольким критериям

Для наглядности разберем формулу ВПР с примером нескольких условий. Для примера будем использовать схематический отчет по выручке торговых представителей за квартал:

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

1. – Дата сдачи выручки в кассу.
2. – Фамилия торгового представителя.

Для решения данной задачи будем использовать функцию ВПР по нескольким условиям и составим следующую формулу:

1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
4. После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

Результат поиска в таблице по двум условиям:

Найдена сумма выручки конкретного торгового представителя на конкретную дату.



Разбор принципа действия формулы для функции ВПР с несколькими условиями:

Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

1. элемент – Дата.
2. элемент – Фамилия.
3. элемент – Выручка.

А каждый ложный элемент в памяти заменяется на 3-х элементный набор пустых текстовых значений («»). В результате создается в памяти программы новая таблица, с которой уже будет работать функция ВПР. Она игнорирует все пустые наборы данных элементов. А непустые элементы сопоставляются со значением ячейки C1, использованного в качестве первого критерия поискового запроса (Дата). Одним словом, таблица в памяти проверена функцией ВПР с одним условием поиска. При положительном результате сопоставления функция возвращает значение элемента из третьего столбца (выручка) условной таблицы. Это происходит потому, что в третьем аргументе указывается номер столбца 3 из которого берутся значения. Стоит отметить что для просмотра в аргументах функции указывается целая таблица (во втором аргументе), но сам поиск всегда идет по первому столбцу в указанной таблицы.

Скачать пример функции ВПР с несколькими условиями в Excel

А из какого столбца брать возвращаемое значение указывается уже в третьем аргументе.

Число 0 в последнем аргументе функции указывает на то, то совпадение должно быть абсолютно точным.