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

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

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

Перейти к:

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

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

Формула ВПР Excel

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

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

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

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

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

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

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

В чем секрет работы VLOOKUP? Уникальные идентификаторы.

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

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

Пример ВПР в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

Когда текст =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 установлено приблизительное совпадение, он ищет данные, наиболее похожие на искомое значение, а не данные, идентичные этому значению. Например, если вы ищете данные, относящиеся к списку ссылок веб-сайта, и некоторые из ваших ссылок содержат «» в начале, возможно, будет целесообразно найти приблизительное совпадение, если есть ссылки, которые не имеют этого » ключевого слова. Таким образом, остальная часть ссылки может совпадать без этого исходного текстового кода, что приведет к тому, что формула ВПР вернет ошибку, если Excel ее не найдет.

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

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

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

ВПР не работает?

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

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

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

Если вы выполнили описанные выше действия, но ВПР по-прежнему не работает, возможно, проблема связана с:

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

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

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

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

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

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

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

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

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

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

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

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