Функция ВПР в Excel — не так страшно, как кажется: пошаговый гайд для легкой работы

13.12.2023

Анастасия Кузнецова

Excel — пять букв, а сколько страха они нагоняют даже на опытных пользователей. Это мощный инструмент, который помогает легко анализировать и интерпретировать большие объемы данных. Несмотря на появление множества автоматизированных инструментов и новейших технологий, Microsoft Excel используют в повседневной работе около 1,1 млрд людей во всем мире.

Интересно, что офисные работники тратят 38% своего рабочего времени на использование Excel. Несмотря на это, более половины из них никогда не проходили какой-либо формальной подготовки. И зря, ведь инструмент Excel предоставляет широкий набор функций, которые работу с данными, и ВПР является одной из них.

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

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

Представим, что перед вами стоит задача посчитать прибыль магазина за предыдущий месяц. У вас есть две таблицы — одна с ценами, а вторая – с количеством проданного товара. Каждая из них насчитывает несколько тысяч строчек. Итак, путей несколько: принести в жертву свой сон и считать все вручную, или доверить этот процесс Microsoft Excel и специальной функции вертикального просмотра.

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

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

Чтобы использовать ВПР в Excel, вам нужно воспользоваться формулой и ввести отдельные значения в соответствии со своими потребностями:

• lookup_value: ячейка, содержащая значение, которое нужно найти. Например: А5.
• table_array: место, где, по вашему мнению, расположено нужное значение и где вы хотите, чтобы Excel его искал. Например: A1:D10
• column_index_num: столбец, где расположено значение. К примеру: 4.
• range_lookup: для этого можно задать параметры TRUE (приблизительное соответствие) или FALSE (точное соответствие).

Начало работы с ВПР Excel: пошаговая инструкция с примерами

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

• значение, которое необходимо найти;
• диапазон, в котором расположено искомое значение;
• номер столбца в диапазоне, который содержит значение;
• по желанию определитесь с параметрами TRUE или FALSE.

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

=ВПР(требуемое значение; диапазон с искомым значением; номер столбца в диапазоне с искомым значением; (TRUE) или (FALSE)).

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

Итак, у вас есть несколько таблиц, среди которых нужно найти необходимые данные. Для этого с помощью кнопки «fx» необходимо вызвать «Мастер функций». Другие способы: комбинация SHIFT и F3; вкладка «Формула», категория «Ссылки и массивы» и выбор функции ВПР.

Перед нами открылось окно с аргументами функции. Как их заполнять:

• В поле «Значение подстановки» добавляем данные, которые необходимо найти. В поле «Таблица» указываем, какие значения функция должна сопоставить.
• В поле «Номер столбца» ставим номер столбца с данными, которые нужно «подтянуть» в первую таблицу.
• В поле «Точность поиска»: TRUE, если требуются приблизительные данные; FALSE, если нужны точные данные (наиболее оптимально).

Общий пример для функции ВПР Excel

А теперь объясним на конкретном примере: допустим, что нам нужно заполнить цену на абрикосы в столбце F. Цена на абрикосы указана в таблице A:B. Значение, которое нужно перетащить, указано во второй колонке. Поскольку названия товаров в обеих таблицах совпадают, нужно использовать точный поиск — FALSE.

Прописываем соответствующую формулу: =VLOOKUP(D3;A:B;2;0) и протягиваем результат на всю колонку.

Пример 1. Сравнение двух таблиц

Теперь представим, что в прайсе изменились цены, и нам надо их сравнить. Для начала создаем столбик «Новая цена» и прописываем формулу ВПР. При этом новую цену перетаскиваем в соответствующем значении:

Пример 2. ВПР с несколькими условиями

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

К примеру, у нас есть таблица, где цена присваивается артикулу товара. Вторая таблица содержит расшифровку этих артикулов. Задание: заполнить цену в последней таблице.

Прописываем формулу: =VLOOKUP(VLOOKUP(G3;$D$3:$E$15;2;0);$A$3:$B$15;2;0) и протягиваем на весь столбец.

Пример 3. Выпадающий список

Теперь у нас есть список товаров, для которых нужно определить цену. Первый шаг — создание выпадающего списка. Для начала ставим курсор в ячейку, где будет название товара. Переходим в раздел «Данные» и выбираем «Проверка данных».

В ячейке «Тип данных» выбираем список, в поле «Источник» выбираем название товаров в столбце G. Формула подтягивается автоматически.

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

Ошибки, которые могут возникнуть при работе

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

• Функция впр выдает ошибочное значение. Если за точность поиска выбрано «TRUE» (или вообще не указано ни одно значение), то первый столбец необходимо отсортировать по алфавиту или в числовом порядке. Если этого не сделать, то функция выдаст ошибку.
• #N/A. Ошибка возникает тогда, когда точность поиска имеет параметр TRUE, а значение аргумента меньше наименьшего значения в первом столбце таблицы.
• #REF!. Возникает тогда, когда значение номера столбца больше, чем общее количество столбцов в таблице.
• #VALUE!. Если значение в массиве таблицы меньше 1.
• #NAME?. Указывает на отсутствие кавычек в формуле.
•  #SPILL!. Свидетельствует о том, что формула использует весь столбец в качестве ссылки. Чтобы этого избежать, следует использовать традиционный метод и ссылаться не на всю колонку, а на одну ячейку.

Советы по работе с ВПР в Excel

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

• Используйте абсолютные ссылки для точности поиска. Такой шаг помогает направить формулу в один и тот же диапазон поиска.
• Числа и даты должны быть в формате цифр. При работе с формулой ВПР следует убедиться в том, что все числовые данные не представлены в виде текста.
• Перед началом работы отсортируйте первый столбец. Это особенно важно, если в качестве точности поиска вы планируете использовать параметр TRUE.
• Используйте специальные символы для обобщения. Такие символы, как вопросительный знак (?) и звездочка (*) помогут найти подобные значения гораздо быстрее. Например: вопросительный знак соответствует любому одному символу, а звездочка — любой последовательности символов.
• Проверьте данные на наличие неправильных символов. В частности важно убедиться, что текстовые данные не содержат пробелов (как в начале, так и в конце), кавычек или других специальных символов.

Выводы

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