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

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

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

В данном отчете необходимо найти показатель выручки для определенного торгового представителя в определенную дату. Учитывая условия поиска наш запрос должен содержать 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 из которого берутся значения. Стоит отметить что для просмотра в аргументах функции указывается целая таблица (во втором аргументе), но сам поиск всегда идет по первому столбцу в указанной таблицы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Данная статья посвящена функции ВПР. В ней будет рассмотрена пошаговая инструкция функции ВПР, под названием «Функция ВПР в Excel для чайников». В данной статье мы подробно рассмотрим описание, синтаксис и примеры функции ВПР в Excel. А также рассмотрим, как использовать функцию ВПР с несколькими условиями и разберем основные ошибки, почему не работает функция ВПР.

    Синтаксис и описание функции ВПР в Excel

    Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.

    Как работает функция ВПР? Функция ВПР в Excel выполняет поиск по вашим спискам данных на основе уникального идентификатора и предоставляет вам часть информации, связанную с этим уникальным идентификатором.

    Буква «В» в ВПР означает «вертикальный». Она используется для дифференциации функции ВПР и ГПР, которая ищет значение в верхней строке массива («Г» обозначает «горизонтальный»).

    Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

    Синтаксис функции ВПР выглядит следующим образом:

    Как видите, функция ВПР имеет 4 параметра или аргумента. Первые три параметра обязательные, последний — необязательный.

    1. искомое_значение – это значение для поиска.

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

    • Поиск числа : =ВПР(40; A2:B15; 2) — формула будет искать число 40.
    • Поиск текста : =ВПР(«яблоки»; A2:B15; 2) — формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
    • Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) — формула будет искать значение в ячейке C2.
    1. таблица — это два или более столбца данных.

    Помните, что функция ВПР всегда ищет искомое значение в первом столбце таблицы. Ваш таблица может содержать различные значения, такие как текст, дата, числа или логические значения. Значения нечувствительны к регистру , что означает, что прописные и строчные буквы считаются идентичными.

    Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A — это первый столбец таблицы A2: B15.

    1. номер_столбца — номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.

    Самый левый столбец в указанной таблице равен 1, второй столбец — 2, третий — 3 и т. д.

    Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).

    4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

    Функция ВПР в Excel примеры

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

    Функция ВПР на разных листах

    На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.

    Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

    Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 (« Продукт 3 ») в столбце A (1-й столбец диапазона поиска A2:B9) на листе « Цены »:

    Функция ВПР в Excel – Функция ВПР на разных листах

    Обратите внимание, что рекомендуется использовать абсолютные ссылки на ячейки (со знаком $) в аргументе таблица. Это предотвратит изменение диапазона поиска при копировании формулы ВПР в другие ячейки.

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

    Если вы собираетесь использовать один и тот же диапазон поиска в нескольких формулах ВПР, вы можете создать именованный диапазон для него и ввести имя непосредственно в аргументе таблица вашей формулы ВПР.

    Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле « Имя », слева от панели « Формула ».

    Функция ВПР в Excel – Присвоение имени диапазону

    Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

    Функция ВПР в Excel – Пример функции ВПР с именем диапазона

    Большинство имен диапазонов в Excel применяются ко всей книге, поэтому вам не нужно указывать имя рабочего листа, даже если ваш диапазон поиска находится на другом листе. Такие формулы гораздо более понятны. Кроме того, использование именованных диапазонов может быть хорошей альтернативой абсолютным ссылкам на ячейки. Поскольку именованный диапазон не изменяется, когда формула копируется в другие ячейки, и вы можете быть уверены, что ваш диапазон поиска всегда останется верным.

    Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» —> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

    Функция ВПР в Excel – Пример функции ВПР с именем таблицы

    Полная формула может выглядеть примерно так:

    или даже =ВПР("Продукт 1";Таблица6;2).

    Как и именованные диапазоны, имена столбцов являются постоянными, а ссылки на ячейки не изменятся независимо от того, где копируется формула ВПР.

    Функция ВПР с несколькими условиями

    Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:

    Функция ВПР в Excel – Таблица исходных данных

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

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

    Итак на листе « Цены » вставляем столбец и в ячейке А2 вводим следующую формулу:

    При помощи этой формулы мы сцепляем значение столбца « Продукт » и « Тип ». Заполняем все ячейки.

    Теперь таблица для поиска выглядит следующим образом:

    Функция ВПР в Excel – Добавление вспомогательного столбца
    1. Теперь в ячейке С2 на листе « Продажи » напишем следующую формулу ВПР:

    Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:

    Функция ВПР в Excel – Пример ВПР с несколькими условиями

    Теперь разберем ошибки функции ВПР.

    Почему не работает функция ВПР

    В этой части статьи мы рассмотрим почему не работает функция ВПР и возможные ошибки функции ВПР.

    Неверное расположение столбца, по которому происходит поиск

    Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым.

    • Перенесите столбец, по которому происходит поиск в крайнее левое положение таблицы.
    • Или создайте вспомогательный дублирующий столбец, слева в таблице.

    Не закреплен диапазон таблицы

    Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка #Н/Д, то диапазон таблицы не закреплен.

    • Используйте абсолютные ссылки ($) для закрепления диапазона таблицы, чтобы при заполнении формула использовала один и тот же диапазон.
    • Или используйте именованный диапазон

    Не удалось найти точное совпадение (если в интервальном просмотре выбран поиск точного значения (0)

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

    Отсортируйте первый столбец таблицы по возрастанию наименований.

    Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы.

    Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ.

    Значение номер столбца превышает число столбцов в таблице

    Проверьте номер столбца, содержащий возвращаемое значение.

    В формуле пропущены кавычки

    Если вы используете в качестве искомого значения не ссылку на ячейку, а текст, то его необходимо заключить в кавычки.

    =ВПР("Продукт 1"; Цены!$A$2:$B$8;2;0)

    Надеюсь, что теперь даже для чайников функция ВПР в Excel будет понятна.