Как использовать функцию ВПР в Excel

7 апреля 2021 г.

Для многих организаций сбор, сортировка и систематизация данных являются важной деловой практикой. У вас могут быть данные, соответствующие количеству продаж, заработной плате сотрудников, показателям удовлетворенности клиентов и многому другому, и заинтересованные стороны часто просматривают данные, созданные для ответа на конкретный вопрос при принятии важных бизнес-решений. Microsoft Excel с его возможностью создавать электронные таблицы — отличное место для начала, когда у вас есть данные для работы.

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

Подробнее: 12 вопросов и ответов для собеседования в Excel, которые помогут вам подготовиться

Что такое ВПР?

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

Синтаксис ВПР:
=ВПР(искомое_значение, массив_таблиц, индекс_столбца, [rangelookup])

Аргументы ВПР:

• lookup_value = значение, которое вы хотите найти в наборе данных (первый столбец)

• table_array = диапазон или таблица, содержащая искомое значение

• col_index_num = столбец в таблице, из которого нужно искать или извлекать возвращаемое значение

• rangelookup (необязательно) = TRUE (приблизительное совпадение) или FALSE (точное совпадение)

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

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

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

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

Вам может понадобиться использовать функцию ВПР в Microsoft Excel, чтобы:

• Найдите важную информацию, содержащуюся в большой электронной таблице.

• Соедините две части информации.

• Объедините данные, которые появляются в разных таблицах.

• Организуйте свои данные в новые категории.

• Присвойте значения фрагментам информации.

Связанный Как включить навыки работы с Excel в свое резюме

Как использовать ВПР в Excel

Чтобы использовать функцию ВПР в Excel, откройте программу Microsoft Excel и выполните следующие действия:

1. Создайте или откройте электронную таблицу или таблицу

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

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

2. Определите, какую информацию вы хотите получить

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

Связанный Узнайте о том, как стать аналитиком данных

3. Решите, каковы ваши аргументы

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

• Искомое_значение: Аргумент искомого значения — это точка данных или значение, которое вы ищете в своей таблице.

• Table_array: Аргумент массива таблиц — это диапазон ячеек, составляющих вашу таблицу данных.

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

• Range_lookup: указывает ВПР, хотите ли вы приблизительное или точное совпадение. Если вы используете «ИСТИНА» или «1» в своем запросе, вы указываете, что вас устраивает приблизительное совпадение, тогда как «ЛОЖЬ» или «0» означает, что вы хотите точное совпадение.

3. Напишите свой запрос ВПР

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

=ВПР(G2,A2:E9,2,ЛОЖЬ).

Этот ввод функции переводит это:

• Вы хотите, чтобы результаты отображались в ячейке G2 вашей таблицы.

• В вашей таблице есть данные в ячейках от A2 до E9.

• Вы хотите, чтобы функция ВПР извлекала данные из второго столбца.

• Вы бы предпочли точное совпадение.

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

Советы по использованию функции ВПР в Microsoft Excel

Эти советы могут быть полезны, если вам нужно использовать функцию ВПР в Excel:

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

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

• Устранение неполадок #N/A или ERROR возвращается. Получение ошибки #N/A после выполнения вашего запроса просто означает, что искомое значение не существует в таблице.

• Сделайте так, чтобы ошибки #N/A выглядели лучше. Если вы хотите сохранить внешний вид своей таблицы, вы можете написать «Не найдено» в своем запросе ВПР, чтобы заменить любой экземпляр #N/A на «Не найдено» в вашей таблице.

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

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

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