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