Как пользоваться формулой ВПР в Excel и Google Таблицах

Согласно данным опроса Roistat и MAED, 93% digital-специалистов и маркетологов отмечают негативное влияние рутины на свое состояние. Чтобы не страдать от рутинных задач, их нужно автоматизировать. И делать это можно не только с помощью инструментов click.ru, но и с помощью функций привычных программ из офисного пакета. Мы подготовили для вас подробную инструкцию по функции ВПР — сопоставлению и копированию в нужном порядке данных из двух таблиц в Excel и Google Таблицах.

Что такое функция ВПР и когда ее нужно применять

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

Как работает функция ВПР

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

Важно учесть, что для автоматического сопоставления данных из двух таблиц нужно, чтобы они были в одном документе, на разных листах. Функция ВПР в Гугл таблицах и Эксель работает только с данными столбцов (то есть ищет совпадения по вертикали). Если нужно отыскать и объединить повторяющиеся данные в строках (по горизонтали), то нужно использовать функцию ГПР или HLOOP.

Как пользоваться ВПР: руководство с примерами

Рассмотрим, как работать с функцией ВПР, на конкретном примере. У маркетолога есть две таблицы. В одной собраны данные по затратам на размещение рекламы в разных Telegram-каналах, а во второй — данные по количеству полученных с нее лидов.

Читайте также:

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

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

1. Искомое значение — столбец с исходной информацией в той таблице, куда мы переносим данные. В примере это первый столбец Таблицы 1 — названия каналов;
2. Таблица — диапазон поиска данных. В примере это названия каналов из таблицы 1 и количество лидов из Таблицы 2. Заданный диапазон нужно закрепить с помощью кнопки F4 (при работе в ОС Windows) или сочетания клавиш Cmd+T (при работе в macOS);
3. Номер столбца — порядковый номер столбца в выделенном формате, откуда будут браться данные. В примере это столбец с количеством лидов из Таблицы 2;
4. Интервальный просмотр — условие поиска: точное совпадение (0, FALSE или ЛОЖЬ) и неточное (1, TRUE или ИСТИНА). Практически во всех случаях используется точный поиск, но в работе с цифрами может применяться и поиск с неточным совпадением.

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

• Шаг 1. Добавить вторую таблицу новым листом в первую, если они являются отдельными документами.
• Шаг 2. Создать в Таблице 1 новый столбец, куда будут переноситься данные из Таблицы 2:

• Шаг 3. Нажать на кнопку Fx над таблицей и найти функцию ВПР. Также можно вписать в строку ввода формул или в ячейку =ВПР().
• Шаг 4. Задать аргументы функции, которые мы расписали выше, в открывшейся форме. Искомое значение — указать координаты первой ячейки столбца, для которого нужно найти данные (А2). Таблица — перейти на второй лист, выделить столбец «Количество заявок», нажать F4 (Cmd+T в macOS) для закрепления интервала. Номер столбца — 2 (номер порядковый, определяется внутри выделенного интервала). Интервальный просмотр — ЛОЖЬ, так как нам нужно точное значение.
• Итоговая формула: =ВПР(A2;Лист2!$A$2:$B$11;2;0)

• Шаг 5 — нажать «Ок» и убедиться, что в ячейке С2 Таблицы 1 появилось нужное значение — количество лидов для Канала 1. Теперь остается только растянуть формулу на все остальные ячейки столбца С.

Как пользоваться ВПР в Google Sheets

Аналог ВПР в Гугл Таблицах работает так же. Единственное отличие — придется сразу писать формулу, а не вставлять аргументы в окошко формы. Для закрепления диапазона в Google Sheets ставят точку с запятой, которая разделяет аргументы формулы.

Чтобы объединить данные одной таблицы с другой в Гугл Таблицах через ВПР, создаем в Таблице 1 новый столбец для данных («Кол-во лидов»), выделяем первую ячейку (С2), нажимаем «Вставка» — «Функция» — VLOOKUP и задаем аргументы.

Получаем формулу =VLOOKUP(A2;’Лист2′!$A$2:$B$11;2;0).

Читайте также:

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

Почему не работает ВПР в Excel и Google Sheets

При использовании формулы ВПР вместо искомого значения может появляться значок Н/Д (N/A). Это значит, что нужные данные во второй таблице не найдены, поэтому сопоставить данные не получается. Это может случиться по разным причинам, например:

• задана точная сортировка, а точных совпадений нет;
• во второй таблице есть скрытые символы, лишние пробелы (удаляются с помощью функций СЖПРОБЕЛЫ (TRIM) или ПЕЧСИМВ (CLEAN);
• выбран неправильный формат для ячеек, например ячейки с цифрами обозначены как текстовые.

Также ВПР не сработает, если в таблицах есть дубли ячеек. Формула перенесет в Таблицу 1 только первую найденную запись, а при обнаружении ее дубля выдаст ошибку. Чтобы избежать этой проблемы, стоит перед использованием функции убрать дубликаты с помощью кнопки Removes Duplicates во вкладке «Данные». Если дублирующую информацию нельзя убрать, вместо ВПР лучше использовать сводную таблицу.

Обо всех других ошибках при использовании функции можно узнать из руководства от Microsoft.

Как сделать ВПР: в двух словах

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