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

Функция ВПР в Excel предназначена для поиска данных по строкам в диапазоне ячеек или таблице и возвращает соответствующие искомые значения.

Функция ВПР удобна при работе с двумя таблицами, которые содержат однотипные данные. Например, имеется таблица заказов на различные продукты с полями «Наименование», «Масса», «Стоимость 1 единицы товара» и «Общая стоимость заказа», заполненными являются только два первых столбца. В отдельной таблице содержатся поля «Наименование» и «Стоимость 1 единицы товара». Таким образом, вторая таблица представляет собой прайс. Чтобы перенести значения стоимости единицы товара из прайса в первую таблицу удобно использовать функцию ВПР. Также данную функцию часто используют для сравнения данных двух таблиц.

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

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

Вид исходной таблицы:

Пример 1.

Создадим компактный вариант таблицы критериев с выпадающим списком. Чтобы создать выпадающий список перейдите в ячейку D2 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

таблицы критериев с выпадающим списком.

В появившемся окне «Проверка вводимых значений» в секции «Тип данных:» выберите опцию «Список». Затем заполните поле «Источник:» ссылкой на диапазон ячеек =$A$2:$A$10, так как показано выше на рисунке.

Для отображения должности каждого сотрудника, выбранного из списка, используем формулу:

ВПР.

Описание аргументов:

  • A14 – ячейка, содержащая искомое значение (список с ФИО сотрудников);
  • A2:B10 – диапазон ячеек со значениями, хранящимися в таблице;
  • 2 – номер столбца, в котором содержится возвращаемое значение.

Пример возвращаемого результата:

отображения должности каждого сотрудника.

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



Поиск значения в диапазоне ячеек по условию

Пример 2. В таблице содержатся данные о пользователях, посетивших сайт за сутки. Определить, посещал ли сайт пользователь с любым ником из списка. Если посещений не было, отобразить соответствующее сообщение. Иначе – отобразить число просмотров.

Вид исходной таблицы:

Пример 2.

Вид таблицы с возвращаемым значением и выпадающим списком как в предыдущем примере:

Вид таблицы с выпадающим списком.

Для расчетов используем следующую формулу:

Функция ЕСЛИ выполняет проверку возвращаемого функцией ВПР значения. Если оно равно 0 (нуль), будет возвращена строка "Не заходил", иначе – возвращен результат конкатенации возвращаемого функцией ВПР значения и подстроки " просмотров".

Примеры расчетов:

Поиск значения в диапазоне по условию.

Функция ВПР и сравнение двух таблиц в Excel если не работает

Пример 3. В двух таблицах хранятся данные о доходах предприятия за каждый месяц двух лет. Определить, насколько средний доход за 3 весенних месяца в 2018 году превысил средний доход за те же месяцы в предыдущем году.

Вид исходной таблицы:

Пример 3.

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

То есть, в качестве аргумента искомое_значение указать диапазон ячеек с искомыми значениями и выполнить функцию в массиве (CTRL+SHIFT+ENTER). Однако при вычислении функция ВПР вернет результаты только для первых месяцев (Март) и полученный результат будет некорректным.

В первую очередь укажем третий необязательный для заполнения аргумент – 0 (или ЛОЖЬ) иначе ВПР вернет некорректный результат. Данный аргумент требует от функции возвращать точное совпадение надетого результата, а не ближайшее по значению. Вот почему иногда не работает функция ВПР в Excel у некоторых пользователей.

Формула для 2017-го года:

=ВПР(A14;$A$3:$B$10;2;0)

И для 2018-го года:

=ВПР(A14;$D$3:$E$10;2;0)

Полученные значения:

Выборка доходов.

С использованием функции СРЗНАЧ определим искомую разницу доходов:

=СРЗНАЧ(E13:E15)-СРЗНАЧА(D13:D15)

Полученный результат:

сравнение двух таблиц.

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

Как работает функция ВПР в Excel при выборке из таблицы значений?

Функция имеет следующую синтаксическую запись:

=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])

Описание аргументов:

  • искомое_значение – обязательный для заполнения аргумент, принимающий числовые, текстовые, логические значения, а также данные ссылочного типа, и представляет собой значение, по которому производится поиск. Например, в таблице с фруктами и их стоимостью можно найти цену груш с помощью функции ВПР, введя в качестве данного аргумента текстовую строку «груша». Искомое значение должно находиться в крайнем левом столбце указанного в качестве таблицы диапазона ячеек (следующий аргумент функции). Для наглядного вида возвращаемого результата можно внести название искомого элемента в ячейку, а данный аргумент указать в виде ссылки на данную ячейку.
  • таблица – обязательный аргумент, принимающий ссылку на диапазон ячеек, в которых будет произведен поиск значения, переданного в качестве аргумента искомое_значение. В указанном диапазоне ячеек столбец с искомым значением должен являться первым слева (например, в диапазоне A1:E6 им будет столбец A:A). Также он должен содержать столбец, в котором содержится возвращаемое значение. Диапазон не должен содержать наименования столбцов.
  • номер_столбца – обязательный аргумент, принимающий целое число из диапазона от 1 до N (N – номер последнего столбца в диапазоне), указывающее номер столбца с возвращаемым значением.
  • [интервальный_просмотр] – необязательный аргумент, принимающий логические значения:
  1. ИСТИНА – поиск ближайшего значения в первом столбце диапазона, переданного в качестве аргумента таблица, при этом данные в этом столбце должны быть отсортированы в алфавитном порядке. Если аргумент явно не указан, значение ИСТИНА устанавливается по умолчанию.
  2. ЛОЖЬ – поиск точного совпадения установленному критерию.

Примечания:

  1. Если в качестве аргумента [интервальный_просмотр] было передано значение ЛОЖЬ (точное совпадение поисковому критерию), а в диапазоне ячеек (аргумент таблица) искомое значение отсутствует, функция ВПР вернет код ошибки #Н/Д.
  2. Если аргумент [интервальный_просмотр] принимает значение ИСТИНА (или явно не указан), однако столбец с искомым значением содержит неотсортированные данные, функция вернет код ошибки #Н/Д. Для получения корректных результатов необходимо выполнить сортировку таблицы или в качестве аргумента [интервальный_просмотр] указать значение ЛОЖЬ.
  3. Если форматы данных, хранимых в ячейках первого столбца таблицы, в которой выполняется поиск с помощью функции ВПР, и переданного в качестве аргумента искомое_значение отличаются (например, искомым значением является число, а в первом столбце таблицы содержатся текстовые строки), функция вернет код ошибки #Н/Д.
  4. Для отображения сообщений о том, что какое-либо значение найти не удалось, можно использовать «обертки» логических функций ЕНД (для перехвата ошибки #Н/Д) или ЕСЛИОШИБКА (для перехвата любых ошибок).

en ru