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. элемент – Выручка.
Интересно почитать:  Как в excel отсортировать данные в таблице excel

А любой неверный элемент в памяти заменяется на 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 — выпадающий перечень с выбором наименования кассы.

Интересно почитать:  Диапазон печати в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

Интересно почитать:  Excel режим разработчика

Метод 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 и т.д.) Почти всем непривычны формулы массива в принципе (тогда для вас сюда).

Ссылка на основную публикацию
Adblock
detector