Как пользоваться формулой ВПР в Excel и Google Таблицах
Согласно данным опроса Roistat и MAED, 93% digital-специалистов и маркетологов отмечают, что рутина негативно влияет на их состояние и продуктивность. Чтобы не страдать от рутинных задач, их нужно автоматизировать. И делать это можно не только с помощью инструментов click.ru, но и с помощью функций привычных программ из офисного пакета. Мы подготовили для вас подробную инструкцию по функции ВПР — сопоставлению и копированию в нужном порядке данных из двух таблиц в Excel и Google Таблицах.
Оглавление
Что такое функция ВПР и когда ее нужно применять
ВПР (в англоязычном интерфейсе Excel и Google Таблиц Vlookup — «вертикальный просмотр») — это функция для поиска данных в одной таблице и их переноса в другую. Она позволяет мгновенно обработать большой объем данных, на ручное сопоставление и копирование которых ушли бы часы.
Как работает функция ВПР
Работает ВПР просто — просматривает данные в заданном пользователем диапазоне одной таблицы, а когда находит совпадения, переносит их во вторую таблицу. В формуле 4 аргумента:
- искомое значение;
- таблица;
- номер столбца;
- интервальный просмотр.
Об их значении поговорим ниже, когда будем разбирать, как пользоваться функцией ВПР на конкретном примере.
Чтобы выполнить автоматическое сопоставление данных из двух таблиц, их нужно собрать в одном документе, но на разных листах. Функция ВПР в Гугл таблицах и Эксель работает только с данными столбцов (т. е. ищет совпадения по вертикали). Если нужно отыскать и объединить повторяющиеся данные в строках (по горизонтали), используют функцию ГПР или HLOOP.
Еще один важный момент. Крайний левый столбец таблицы нужно отсортировать по возрастанию, иначе функция не сработает.
Как пользоваться ВПР: руководство с примерами
Рассмотрим, как работать с функцией ВПР, на конкретном примере. У маркетолога есть две таблицы. В одной собраны данные по затратам на размещение рекламы в разных Telegram-каналах, а во второй — данные по количеству полученных с нее лидов.
Данные из таблиц нужно объединить, чтобы оценить эффективность размещения и рассчитать стоимость лидов с каждого канала. Но порядок указания каналов в таблицах разный. Поэтому простым копированием столбцов не обойтись, особенно если строк десятки и сотни. Здесь и поможет функция ВПР.
Нам нужно перенести в первую таблицу данные о количестве лидов в соответствии с каждым каналом. Для этого задаем формулу. Как мы уже сказали выше, в ней 4 аргумента:
- Искомое значение — столбец с исходной информацией в той таблице, куда мы переносим данные. В нашем примере это первый столбец Таблицы 1 — названия каналов;
- Таблица — диапазон поиска данных. В примере это названия каналов из таблицы 1 и количество лидов из Таблицы 2. Заданный диапазон нужно закрепить с помощью кнопки F4 (при работе в ОС Windows) или сочетания клавиш Cmd+T (при работе в macOS);
- Номер столбца — порядковый номер столбца, из которого будут браться данные. В примере это столбец с количеством лидов из Таблицы 2;
- Интервальный просмотр — условие поиска: точное совпадение (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
Аналог ВПР в Гугл Таблицах (VLOOKUP) работает так же. Единственное отличие — придется писать формулу вручную. Для закрепления диапазона в Google Sheets ставят точку с запятой, которая разделяет аргументы.
Вот как выглядит алгоритм объединения данных одной таблицы с другой в Гугл Таблицах через ВПР:
- создаем в Таблице 1 новый столбец для данных («Кол-во лидов»);
- выделяем первую ячейку (С2);
- нажимаем «Вставка» — «Функция» — VLOOKUP;
- в открывшемся окошке задаем аргументы.
Получаем формулу =VLOOKUP(A2;’Лист2′!$A$2:$B$11;2;0).
Не обязательно тратить время и каждый раз задавать формулу ВПР (VLOOKUP) по указанному алгоритму. Можно просто подставлять готовую формулу и менять в ней значения в зависимости от количества ячеек в таблицах и расположения нужных данных.
Почему не работает ВПР в Excel и Google Sheets
При использовании формулы ВПР вместо искомого значения может появляться значок Н/Д (N/A) или #ERROR! в Гугл Таблицах. Это значит, что нужные данные во второй таблице не найдены, поэтому сопоставить данные не получается. Это может случиться по разным причинам, например:
- задана точная сортировка, а точных совпадений нет;
- во второй таблице есть скрытые символы, лишние пробелы (удаляются с помощью функций СЖПРОБЕЛЫ (TRIM) или ПЕЧСИМВ (CLEAN);
- выбран неправильный формат для ячеек, например ячейки с цифрами обозначены как текстовые.
Если в таблицах есть дубли ячеек, формула перенесет в Таблицу 1 только первую найденную запись. Чтобы избежать этой проблемы, перед использованием функции уберите дубликаты с помощью кнопки Removes Duplicates. Она находится во вкладке «Данные». Если дублирующую информацию нельзя убрать, вместо ВПР лучше использовать сводную таблицу.
Обо всех других ошибках при использовании функции можно узнать из руководства от Microsoft.
Как сделать ВПР: в двух словах
Итак, формула ВПР или VLOOKUP в Excel и Google Sheets — удобный инструмент для быстрого объединения данных из двух объемных таблиц. Для этого нужно перенести обе таблицы в один документ, выбрать функцию и задать 4 аргумента.
Достаточно разобраться с синтаксисом ВПР/VLOOKUP, и тогда вы будете задавать ее буквально за несколько секунд. Это не только сокращает количество рутинных операций и освобождает время для более интересной и полезной работы, но и сводит на нет риск ошибок из-за ручного копирования данных.
