Как использовать функцию ВПР в Microsoft Excel [+ Видеоурок]

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

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

Функцию ВПР в Microsoft Excel проще использовать, чем вы думаете. Более того, он невероятно мощный, и его определенно стоит иметь в своем арсенале аналитического оружия.

Пропустить:

Как работает ВПР?

VLOOKUP означает «вертикальный поиск». В Excel это означает вертикальный поиск данных в электронной таблице с использованием столбцов электронной таблицы и уникального идентификатора в этих столбцах в качестве основы для поиска. Когда вы просматриваете свои данные, они должны быть расположены вертикально, где бы они ни находились.

Формула ВПР Excel

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

=VLOOKUP(искомое значение, диапазон, содержащий искомое значение, номер столбца в диапазоне, содержащем возвращаемое значение, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).

Это помогает организовать ваши данные таким образом, чтобы искомое значение находилось слева от возвращаемого значения, которое вы хотите найти.

Формула всегда ищет вправо.

При выполнении ВПР в Excel вы, по сути, ищете новые данные в другой электронной таблице, которая связана со старыми данными в вашей текущей. Когда VLOOKUP запускает этот поиск, он всегда ищет новые данные справа от текущих данных.

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

Для получения данных формуле необходим уникальный идентификатор.

Секрет работы VLOOKUP? Уникальные идентификаторы.

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

Хорошо, достаточно объяснений: давайте посмотрим еще один пример ВПР в действии!

Пример ВПР в Excel

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

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

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

1. Определите столбец ячеек, который вы хотите заполнить новыми данными.
2. Выберите «Функция» (Fx) > ВПР и вставьте эту формулу в выделенную ячейку.
3. Введите искомое значение, для которого вы хотите получить новые данные.
4. Введите массив таблицы электронной таблицы, в которой находятся нужные данные.
5. Введите номер столбца данных, которые Excel должен вернуть.
6. Введите поиск диапазона, чтобы найти точное или приблизительное совпадение искомого значения.
7. Нажмите «Готово» (или «Ввод») и заполните новый столбец.

Для справки, вот как выглядит синтаксис функции ВПР:

ВПР(искомое_значение, table_array, col_index_num, range_lookup)

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

1. Определите столбец ячеек, который вы хотите заполнить новыми данными.

Помните, что вы хотите получить данные из другого листа и поместить их на этот. Имея это в виду, пометьте столбец рядом с ячейками, о которых вы хотите получить дополнительную информацию, соответствующим заголовком в верхней ячейке, например «MRR», для ежемесячного регулярного дохода. В этот новый столбец будут попадать извлекаемые вами данные.

2. Выберите «Функция» (Fx) > ВПР и вставьте эту формулу в выделенную ячейку.

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

Найдите и выберите «ВПР» из списка опций, включенных в построитель формул. Затем выберите «ОК» или «Вставить функцию» , чтобы начать создание ВПР. Ячейка, которую вы сейчас выделили в своей таблице, теперь должна выглядеть так: « =VLOOKUP() ».

Вы также можете ввести эту формулу в вызов вручную, введя выделенный выше жирный текст точно в нужную ячейку.

Когда текст =VLOOKUP введен в вашу первую ячейку, пришло время заполнить формулу четырьмя различными критериями. Эти критерии помогут Excel точно определить, где находятся нужные вам данные и что искать.

3. Введите искомое значение, для которого вы хотите получить новые данные.

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

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

4. Введите массив таблицы электронной таблицы, в которой расположены нужные данные.

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

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

Например, если ваши данные расположены в «Лист2» ​​между ячейками C7 и L18, запись массива вашей таблицы будет «Лист2!C7:L18».

5. Введите номер столбца данных, которые Excel должен возвращать.

Под полем массива таблиц вы вводите «индексный номер столбца» массива таблиц, в котором вы ищете. Например, если вы фокусируетесь на столбцах от B до K (обозначаемых как «B:K» при вводе в поле «массив таблицы»), но конкретные значения, которые вам нужны, находятся в столбце K, вы введете «10» в поле «массив таблицы». поле «номер индекса столбца», поскольку столбец K является 10-м столбцом слева.

6. Введите поиск диапазона, чтобы найти точное или приблизительное совпадение искомого значения.

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

Чтобы ответить на ваш животрепещущий вопрос: да, вы можете разрешить Excel искать приблизительное совпадение вместо точного. Для этого просто введите TRUE вместо FALSE в четвертом поле, показанном выше.

Когда VLOOKUP настроен на приблизительное совпадение, он ищет данные, которые наиболее точно соответствуют искомому значению, а не данные, идентичные этому значению. Например, если вы ищете данные, связанные со списком ссылок на веб-сайты, и некоторые из ваших ссылок имеют «https://» в начале, возможно, вам стоит найти приблизительное совпадение на тот случай, если есть ссылки, которые не иметь этого тега «https://». Таким образом, остальная часть ссылки может совпадать без этого начального текстового тега, что приведет к тому, что ваша формула ВПР вернет ошибку, если Excel не сможет ее найти.

7. Нажмите «Готово» (или «Ввод») и заполните новый столбец.

Чтобы официально ввести значения, которые вы хотите, в новый столбец из шага 1, нажмите «Готово» (или «Ввод», в зависимости от вашей версии Excel) после заполнения поля «Поиск диапазона». Это заполнит вашу первую ячейку. Вы можете воспользоваться этой возможностью и просмотреть другую таблицу, чтобы убедиться, что это правильное значение.

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

Учебное пособие по ВПР

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

Источник изображения

Если вы выполнили вышеуказанные шаги, но ваш VLOOKUP по-прежнему не работает, это может быть проблемой с вашим:

• Синтаксис (т. е. то, как вы структурировали формулу)
• Значения (т. е. являются ли данные, которые он ищет, хорошими и правильно отформатированы).

Устранение неполадок синтаксиса ВПР

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

• Относится ли оно к правильному значению поиска для идентификатора ключа?
• Указывает ли он правильный диапазон массива таблиц для значений, которые необходимо получить?
• Указывает ли он правильный лист для диапазона?
• Правильно ли написан этот лист?
• Используется ли правильный синтаксис для ссылки на лист? (например, Pages!B:K или «Лист 1»!B:K)
• Указан правильный номер столбца? (например, A равно 1, B равно 2 и т. д.)
• Является ли True или False правильным маршрутом настройки вашего листа?

Устранение неполадок со значениями ВПР

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

• Значения отформатированы вертикально и справа налево?
• Соответствуют ли значения тому, как вы к ним относитесь?

Например, если вы ищете данные URL-адресов, каждый URL-адрес должен представлять собой строку с соответствующими данными слева от него в той же строке. Если у вас есть URL-адреса в виде заголовков столбцов, а данные перемещаются вертикально, VLOOKUP не будет работать.

Согласно этому примеру, URL-адреса должны совпадать по формату на обоих листах. Если у вас есть один лист, содержащий «https://» в значении, а другой лист не содержит «https://», VLOOKUP не сможет сопоставить значения.

ВПР как мощный инструмент маркетинга

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

Примечание редактора: этот пост был первоначально опубликован в марте 2019 года и обновлен для полноты.