Гид по формуле ВПР в Excel для интернет-маркетолога

Для каких задач интернет-маркетолога полезен ВПР

С помощью vlookup можно автоматизировать работу с данными рекламных кампаний, легко анализировать и оптимизировать рекламные стратегии. Вот несколько примеров задач:

• Сравнение данных. Если у вас есть таблица с ключевыми словами и их показателями (CTR, CPC, конверсии), можно сопоставить эти данные с другими, например с таблицей по ставкам или бюджетам.
• Сегментация. Если у вас таблица с сегментами аудитории и их характеристиками (например, возраст, пол, география), по такому же принципу можно узнать, какие сегменты наиболее эффективны для каждой из ваших рекламных кампаний.
• Анализ эффективности. С vlookup легко сравнить результаты нескольких рекламных кампаний, чтобы принять решение о том, какие кампании нужно оптимизировать или приостановить, а на какие добавить бюджет.
• Автоматизация отчётности. Если вы формируете отчёты, функция автоматически копирует и вставляет показатели по результатам кампании из нескольких листов на один. Это значительно ускорит процесс создания отчётов.

Как применять ВПР в Excel

Добавьте данные из разных таблиц в один файл на разные листы. Функция состоит из четырёх параметров: искомое значение, место для поиска значения, номер колонки и тип совпадения. Как сделать, чтобы она заработала:

1. На примере два листа таблицы: «CRM» и «Директ». Задача: перенести данные по продажам с листа «CRM» на лист «Директ».

2. Перед началом работы обязательно сформируйте таблицу в Excel — выделите таблицу и нажмите «Форматировать как таблицу», иначе программа может не увидеть столбцы. Добавьте к таблице на листе «Директ» новый столбец и назовите его «Продажи».

3. Нажмите f(x) — расположена слева от строки ссылок — и выберите в списке функций ВПР. Определите параметр «Искомое значение» — то, что нужно найти на листе: число, текст или ссылка на ячейку. В нашем случае это название кампании — A2.

4. Выберите диапазон данных, из которого нужно будет взять информацию. На нашем примере диапазон данных — это таблица. Щёлкаем на лист «CRM» и выделяем мышкой всю информацию, за исключением названия столбцов. В поле «Таблица» теперь отображается текст «Таблица 2».

5. Укажите выбранный номер столбца, из которого следует взять значение. В нашем примере это столбец № 2 листа «CRM».

6. Выберите интервальный просмотр, то есть тип совпадения: приблизительное или точное, «ИСТИНА» или «ЛОЖЬ» соответственно. Приблизительное совпадение лучше использовать при работе с числами. Точное совпадение — при работе с наименованиями. Мы будем использовать приблизительное. Формула собрана, получилось: =ВПР(A2;Таблица2;2;ИСТИНА). Нажмите «ОК».

7. Столбец «Продажи» перенесён. Все значения подставились в таблицу.

Формула ВПР

В ячейку, в которой вы хотите получить результат, нужно вставить формулу:

=ВПР(искомое значение; место поиска; номер столбца; точное совпадение 0 (ЛОЖЬ) или приблизительное 1 (ИСТИНА)).

ВПР для нескольких условий

Бывает нужно сделать выборку из большой таблицы по двум показателям. Функция не поддерживает поиск по двум критериям. Чтобы решить эту задачу, используйте оператор «&».

1. Задача: найти «Клики» по двум критериям: «Название рекламной кампании» и «Период». Создайте вспомогательный столбец, чтобы внести туда информацию со всех колонок с помощью оператора «&». В нашем случае получается так: =F2&G2.

2. Рядом создайте новую таблицу с колонками: «Название рекламной кампании», «Период» и «Клики». Заполняем «Название рекламной кампании», «Период». В строку «Клики» вводим функцию =ВПР(A2&B2;E2:I6;5;ЛОЖЬ), в неё подтягивается значение из первой таблицы. Задача решена.

ВПР по частичному совпадению

Если вы не уверены в точности искомого значения, а помните только часть, используйте символ звёздочка «*» — она заменяет любое количество символов.

Пример: нужно вывести в ячейку количество визитов по рекламной кампании, а вы забыли точное название кампании, но помните, что в ней было слово «теги».

=ВПР("*теги*";A2:B4;2;ЛОЖЬ)

ВПР для нескольких таблиц

Вертикальный просмотр для работы с несколькими таблицами будет полезен, если данные за какой-то отрезок времени изменились и вы хотите узнать разницу. Например, между просмотрами по рекламной кампании за август 2023 г. и 2024 г.

1. Данные на разных листах — их нужно переместить на один лист.

2. Создайте новую таблицу на этом же листе. При помощи функции вертикального просмотра сделайте перенос данных за август 2023 г. и август 2024 г.

3. Создайте новый столбец «Разница между 2023 г. и 2024 г.». В ячейке D2 поставьте «=» → нажмите ячейку С2 → - (минус, на клавиатуре — знак дефис) → нажмите ячейку B2 → Enter. Задача решена, значения следующих ячеек рассчитались автоматически.

Ошибки при использовании функции

Список наиболее распространённых ошибок:

• #Н/Д — значит, что значение, которое искали, не найдено. В основном происходит из-за того, что поиск производят только по первому столбцу или диапазон поиска не закреплён. Чтобы закрепить диапазон поиска, поставьте значок «$» перед диапазоном и после.
• #ССЫЛКА! — возникает, если ссылка недействительна. В основном случается потому, что столбцы, строки или листы были удалены либо формулу с относительными ссылками скопировали в новое место, где они недействительны. Проверяйте, не ссылаются ли формулы в книге на столбцы, строки или листы, прежде чем удалять их.
• #ИМЯ? — указывает на ошибку в написании функции. Чтобы её устранить, нужно заключить весь текст «в такие кавычки».
• #ЗНАЧ! — у ошибки несколько причин:

  a. Появляется, если номер столбца превышает количество столбцов в диапазоне или меньше 0. Проверьте указанный в формуле номер столбца.

  b. Когда длина строки сравниваемой функции более 255 символов. Если длина больше, нужно её сократить.

  c. Тип данных в колонке поиска не соответствует типу данных искомого значения. Проверьте, если тип искомого данного — число, то в пределах поиска тоже должны стоять значения в числовом формате.

Как быстро научиться работать с ВПР

Если вам нужно быстро освоить ВПР, в курсе «Яндекс про Директ: продвинутый» есть пошаговая инструкция. Читайте модуль об Excel, раздел «Формула для вертикального переноса данных».

Обучение на курсе бесплатное, можно заниматься онлайн в удобное время. Кроме того, вы можете посмотреть другие курсы, которые упростят работу в кабинете Яндекс Директа и помогут получить желаемый результат от вложенных в рекламу средств.

Применить полученные знания по ВПР можно при анализе рекламных кампаний. А чтобы запустить продвижение и начать привлекать клиентов, используйте Яндекс Директ.

Кратко

• ВПР упрощает работу с большими объёмами данных, когда нужно собрать информацию по разным показателям с разных листов таблицы.
• Вертикальный просмотр упростит работу в сервисе Яндекс Директ. С его помощью можно сравнивать данные, делать сегментацию, анализировать эффективность, автоматизировать отчётность.
• Формула ВПР состоит из искомого значения, места для его поиска, номера колонки и типа совпадения.
• Чтобы сделать vlookup по нескольким критериям, используйте оператор «&».
• Чтобы сделать вертикальный просмотр по частичному совпадению, пользуйтесь символом «*».
• Чтобы сравнить данные в нескольких таблицах, перенесите их на один лист и воспользуйтесь функцией вертикального просмотра.
• Если вы хотите быстро освоить ВПР и другие функции, читайте курс «Яндекс про Директ: продвинутый», модуль об Excel.
• Применить знания по ВПР можно при анализе кампаний в Яндекс Директе.