Excel впр по двум критериям

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

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

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

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

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

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

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

Итог поиска в таблице по двум условиям:

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

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

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

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

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

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

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

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

—>Категория : Приёмы работы с формулами | —>Добавил : Serge_007 (06.02.2011)

—>Просмотров : 166400 | —>Комменты : 69 | —>Теги : ВПР, формулы эксель, несколько критериев, поискпоз, ВПР по двум критериям, формулы Excel | —>Рейтинг : 4.7 / 12

Почему-либо не работает, выдает #Знач!
в чем быть может дело?

весьма нужен отбор по двум значениям! Помогите!
Спасибо)

Формулы массива вводится нажатием Ctrl+Shift+Enter.
О этом есть в статье.

Амперсанд и функция СЦЕПИТЬ делают полностью одно и тоже.

Здрасти! У меня по данной формуле появилась последующая ситуация:
1) в строке 32 у меня занесены последующие данные столбец B Profi Mass bag столбец С 900 g;
2) в строке 33 столбец B Profi Mass столбец C Profi Mass 2800 g.

Из анализируемого спектра формула берет стоимость за Profi Mass bag 900 g и ставит ее напротив Profi Mass 2800 g?

Предложу собственный вариант поиска по 2м критериям, работает безотказно:

разложу формулу для наглядности:

И(
(A6=ИНДЕКС(Данные!A1:A3500;ПОИСКПОЗ(A6;Данные!A1:A3500;0);1));
(B6=ИНДЕКС(Данные!B1:B3500;ПОИСКПОЗ(B6;Данные!B1:B3500;0);1)));

ИНДЕКС(Данные!A1:F3500;ПОИСКПОЗ(A6;Данные!A1:A3500;0);4);
"Ошибка. Проверьте формулу")

А10 — ссылка на дату определенную по которой отыскиваем
А$1 — выпадающий перечень с выбором наименования кассы.

как мне это решать?, помогите плиз.

Мужчины, спасибо за формулу, жить сделалось легче! =)

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

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

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

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

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

Метод 1. Доп столбец с ключом поиска

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

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

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

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

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

Метод 2. Функция СУММЕСЛИМН

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

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

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

Метод 3. Формула массива

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

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

Как это по сути работает:

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

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

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