Функция ВПР в Excel: как маркетологу сэкономить несколько часов работы

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

Что такое ВПР и как её используют в маркетинге и бизнесе 

ВПР — это функция для поиска данных в Excel. Её также называют VLOOKUP, от слов Vertical — «вертикальный», и look up — «просмотр». Она нужна, чтобы быстро сопоставить строки из двух таблиц и перенести значения из нужной колонки. Проще говоря: у вас есть две таблицы, общий столбец и задача — быстро всё соединить без ручного копирования. В этом и помогает ВПР.

Эта опция пригодится везде, где есть большое количество цифр, списков и наименований: в продажах, HR, бухгалтерии и маркетинге. Конкретно в маркетинге ВПР решает несколько задач: 

Объединяет данные. Метрики часто хранятся в разных таблицах: трафик — в одной, лиды — в другой, расходы — в третьей. ВПР связывает их по общему параметру (например, по кампании или ID) и собирает всё в одном месте.

Автоматически рассчитывает метрики. Чтобы узнать CPL, CPA или ROI, нужно сопоставить затраты и результат. На ручной перенос уходит несколько часов, а ВПР подтягивает данные за секунды. Это касается и отчётов. Часто статистику нужно обновлять раз в несколько дней, а с ВПР у вас будет автоматический перерасчёт. 

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

Как работает функция ВПР: справка для маркетолога

Формула ВПР в Excel состоит из четырёх аргументов:

1. Искомое значение. По нему Excel перебирает информацию в другой таблице. Обычно это идентификатор заявки или заказа, который уже есть в основной базе данных. Допусти, у вас есть таблица с заявками с сайта и столбец «ID заказа». Тогда ВПР ищет каждое значение из этого столбца во второй таблице с продажами или CRM.
2. Таблица с данными. В ней содержится диапазон ячеек, в которых хранится информация о клиентах, товарах или рекламных кампаниях. Например, у маркетолога есть таблица с продажами по ID заказа, где указаны сумма покупки, товар и дата. ВПР проверяет каждый ID из первой таблицы в этом диапазоне, потом добавляет данные. 
3. Номер столбца. Когда Excel найдёт нужный ID в диапазоне, он возьмёт данные из указанного столбца. Excel считает первый столбец диапазона как «1», второй — «2», третий — «3» и так далее. Цифра в формуле ВПР указывает, из какого столбца взять данные.
4. Интервальный просмотр. Здесь выбирается тип поиска:

• Точное совпадение — 0, ЛОЖЬ или FALSE. Excel подтянет данные только если значения совпадают полностью, например, когда у каждой заявки есть уникальный ID заказа и нужно подтянуть к нему точную сумму и товар.
• Неточное совпадение — 1, ИСТИНА или TRUE. В этом случае Excel ищет не точное значение, а ближайшее подходящее из диапазона.Например, у вас есть таблица с порогами скидок: 1–10 товаров — 5%, 11–20 товаров — 10%, 21–30 товаров — 15%. Если заказ на 13 товаров, точного значения в таблице нет, и ВПР подтянет ближайший нижний порог — 10%.

Далее разберём, как использовать ВПР на практике с учётом всех четырёх аргументов.

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

Для начала предупредим: ВПР работает только с одним диапазоном за раз. Это значит, что формула ищет данные в одной таблице и возвращает значение из неё. Если у вас несколько источников, их придётся связывать поочерёдно, каждый раз заново запуская ВПР.

👉 А теперь представим ситуацию. Вы маркетолог клининговой компании, запускаете рекламу на разные виды уборки: генеральная, ежедневная, после ремонта. Ваша цель — понять стоимость заявки по каждому виду услуги и по каждой кампании.

Есть два источника данных в Excel:

• Лист «Заказы» (CRM) — таблица с кампаниями, видами услуг и количеством заявок без расходов на рекламу.
• Лист «Реклама» — таблица с рекламными расходами по кампаниям.

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

👉 Далее рассмотрим, как запустить функцию ВПР в Excel. На четвёртом шаге разберём формулу для рассчёта стоимости одной заявки.

Шаг 1. Добавляем новый столбец «Расход, ₽» в таблицу с заказами. Именно сюда Excel будет переносить данные о расходах на рекламные кампании. В нашем случае это затраты на каналы, которые указаны в листе «Реклама». 

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

Шаг 2. Запускаем функцию ВПР. В ячейке первой строки столбца «Расход, ₽» открываем окно построителя формул через кнопку Fx → Вставить функцию → ВПР. В открывшемся окне видим четыре поля: «Искомое значение», «Таблица», «Номер столбца» и «Интервальный просмотр». Начинаем их заполнять.

Искомое значение — ссылка на ячейку с ключом, который содержится в обеих таблицах. В нем случае это кампания. Кликаем на ячейку B2 и Excel автоматически вставляет ссылку на неё в поле. Это нужно, чтобы функция знала, по какому значению искать соответствие.

Таблица — диапазон, где искать данные. Переходим на лист «Реклама» и выделяем диапазон с расходами A2:B7. Excel автоматически вставит Реклама!A2:B7. Дальше нужно закрепить значение, чтобы можно было спокойно переключаться между листами и не ломать функцию. Для этого нажимаем F4 и получаем → Реклама!$A$2:$B$7. 

Если F4 не срабатывает:

• Нажмите сочетание клавиш Fn + F4.
• Используйте Cmd + T на Mac.
• Если всё равно не работает, просто вручную добавьте знак доллара $ между цифрами. Вот так: $A$2:$B$7.

Номер столбца — задаёт путь, откуда брать значение. В нашем диапазоне расходы находятся во втором столбце, поэтому вводим 2. Формула подтянет из него данные.

Интервальный просмотр — указывает, нужно ли точное совпадение. Выбираем ЛОЖЬ (или 0 / FALSE). Это гарантирует, что ВПР найдёт именно ту кампанию, которую указали.

Шаг 3. Нажимаем «ОК». В ячейке появится расход для первой кампании — 60 000 ₽. Теперь нужно подсчитать сумму также для других кампаний. Чтобы подтянуть данные по всем строкам таблицы «Заказы», берём за правый нижний угол ячейки и протягиваем формулу вниз. Excel автоматически подставит расход для всех остальных рекламных кампаний. В итоге видим столбец с расходами.

Ещё один вариант запустить ВПР — вставить готовую формулу сразу в ячейке. Для этого пишем в ней =ВПР(=ВПР(B2;Реклама!$A$2:$B$7;2;ЛОЖЬ)

Шаг 4. Считаем стоимость одной заявки по другой формуле. В первой ячейке столбца F2 вводим формулу =E2/D2. Здесь E2 — расход, подтянувшийся через ВПР, а D2 — количество заявок. Формула делит расход на заявки. Протягиваем её вниз, чтобы рассчитать значения для каждой кампании.

Шаг 5. Округляем суммы. Excel выводит дробные значения, которые неудобно анализировать. Чтобы привести цифры к привычному виду вводим ещё одну формулу — ОКРУГЛ(D2/C2;0).

Что делать, если нужен поиск сразу по двум критериям

Бывают случаи, когда данных много и их нужно сопоставить. Например, проанализировать расход не по рекламному каналу и типу услуги.

В нашем примере с рекламой всё работало просто: каждая кампания встречалась один раз, поэтому формулы =ВПР(B2;Реклама!$A$2:$B$7;2;ЛОЖЬ) было достаточно.

👉 Представим другую ситуацию. В таблице «Реклама» Яндекс встречается три раза — для генеральной, ежедневной и уборки после ремонта. Обычная формула вида =ВПР(B2;Реклама!$A$2:$D$9;4;ЛОЖЬ) найдёт первую строку с «Яндекс» и подтянет расход только по ней. Тип услуги при этом проигнорируется и в результате цифры будут некорректными.

Чтобы ВПР работал по двум критериям, нужно создать единый ключ — объединить два параметра в один столбец. Тогда для Excel это будет одно уникальное значение, и совпадение станет точным. Разберём, как это сделать.

Шаг 1. Создаём объединённый ключ в таблице «Заказы». Добавляем новый столбец и называем его «Связка: канал + услуга». Кликаем в первую ячейку этого столбца — F2.

Вписываем формулу: =B2&C2. Здесь B2 — это канал, а C2 — это тип услуги. Нажимаем Enter и протягиваем формулу вниз.

Шаг 2. Создаём такой же столбец во второй таблице «Реклама». Здесь действуем по аналогии: добавляем столбец с тем же названием и в первой строке пишем: =A2&B2. Нажимаем Enter и тянем формулу вниз.

Шаг 3. Вставляем ВПР. Возвращаемся в таблицу «Заказы». В столбце «Расход, ₽» кликаем в первую ячейку — F2. Вставляем вручную формулу: =ВПР(F2;Реклама!$C$2:$D$11;2;ЛОЖЬ). 

Что может пойти не так: 6 ошибок в построении формулы

Собрали частые причины, почему ВПР не запускается и выдаёт ошибку. Если это ваш случай, попробуйте проверить каждый пункт.

Проблемы при работе с разными файлами. ВПР может не срабатывать или выдавать ошибки, если таблицы находятся в отдельных Excel-файлах.

👉 Что делать:

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

#Н/Д: Нет совпадения. Она появляется, когда Excel не может найти схожие параметры в разных таблицах. Обычно это связано с лишними пробелами в ячейках, неправильным форматом данных, или отсутствием значения в первом столбце диапазона. 

👉Что делать:

• Используйте функцию СЖПРОБЕЛЫ, чтобы убрать лишние пробелы.
• Проверьте формат данных и при необходимости приведите его к числу или тексту (ЗНАЧЕН / ТЕКСТ).
• Убедитесь, что искомое значение есть в первом столбце диапазона.

#ЗНАЧ!: Неверный номер столбца или путь к файлу. Появляется, если номер столбца меньше 1 или больше диапазона, либо Excel не может найти файл.

👉Что делать:

• Проверьте, что номер столбца корректен и входит в диапазон.
• Укажите полный путь к файлу при работе с внешними таблицами.
• Убедитесь, что выбранный диапазон содержит нужные данные и формула корректно возвращает результат.

#ОТКАЗ: Номер столбца выходит за пределы таблицы. Excel выдаёт эту ошибку, когда столбец, из которого нужно взять данные, не включён в диапазон.

👉Что делать:

• Уменьшите номер столбца.
• Расширьте диапазон, чтобы он включал нужный столбец.
• Проверьте правильность указания диапазона.

Неправильный результат. Чаще всего возникает из-за дубликатов — ВПР берёт первое совпадение, или при использовании приблизительного поиска (ЛОЖЬ → ИСТИНА).

👉Что делать:

• Удалите дубликаты из первого столбца диапазона.
• Используйте точный поиск (ЛОЖЬ / 0).
• Проверьте корректность данных в диапазоне.

Некорректный результат при приблизительном поиске. Возникает, если в формуле указано ИСТИНА (или 1), но первый столбец диапазона не отсортирован по возрастанию. В этом случае Excel может вернуть неправильное значение.

👉 Что делать:

• Проверьте, что первый столбец диапазона отсортирован по возрастанию.
• Используйте точный поиск (ЛОЖЬ / 0), если сортировка не подходит для вашей задачи.

Альтернативы ВПР: функции XLOOKUP и INDEX+MATCH

Существуют два аналога ВПР — XLOOKUP и комбинация INDEX+MATCH. Кому-то они покажутся удобнее. Рассказываем, как ими пользоваться. 

XLOOKUP. Это улучшенная функция, которая работает в Google Таблицах и в новых версиях Excel — 365 и 2021+. Она ищет нужное значение в любом направлении и сразу возвращает результат из нужного столбца. Поиск работает точнее: если совпадение не найдено, Excel или Google Таблицы вместо ошибки выведут указанный вами текст, например «не найдено». Это удобно, потому что можно строить отчёты без исправлений.

Синтаксис формулы выглядит так:

=XLOOKUP(искомое; массив_поиска; массив_возврата; "не найдено")

👉 Далее рассмотрим, как использовать формулу на том же примере с клининговой компанией в Google Таблицах. 

Шаг 1. Выбираем ячейку, где хотим получить результат. В нашем случае это столбец «Расход, ₽» в таблице «Заказы».

Шаг 2. Вставляем формулу через строку ввода или через кнопку «Вставить функцию» (Fx). В нашем случае она выглядит так:

=XLOOKUP("Яндекс"; Реклама!A2:A9; Реклама!B2:B9; "не найдено").

• Реклама!A2:A9 — столбец с кампаниями
• Реклама!B2:B9 — столбец с расходами
• "не найдено" — текст при отсутствии совпадения

Шаг 3. Протягиваем формулу вниз. Excel автоматически подставит расходы для остальных кампаний.

INDEX + MATCH. Эта комбинация работает во всех версиях Excel и позволяет искать данные по строкам и столбцам, вне зависимости от их расположения.

Например, если вам нужно найти расход конкретной кампании в таблице, INDEX + MATCH позволяет подтянуть его точно из нужного столбца, даже если столбцы идут в произвольном порядке. Это делает поиск гибче и надёжнее, чем обычный ВПР.

Синтаксис формулы вот такой:

=INDEX(массив_возврата; MATCH(искомое; массив_поиска; 0))

• массив_возврата — столбец, откуда нужно вернуть значение (расходы)
• искомое — кампания, которую ищем
• массив_поиска — столбец, где ищем кампанию
• 0 — точное совпадение

👉Далее рассмотрим, как использовать формулу на том же примере с клининговой компанией в Google Таблицах.

Шаг 1. Определяем критерии поиска. Если одна кампания встречается несколько раз для разных услуг, INDEX + MATCH подтянет только первую строку. Чтобы учитывать разные типы услуг, создаём объединённый ключ. На листе «Заказы» добавляем столбец «Связка: канал + услуга». В первой строке пишем формулу: =A2&B2 (A2 — канал, B2 — тип услуги). Протягиваем формулу вниз. Теперь каждая строка имеет уникальный ключ, например: ЯндексГенеральная. На листе «Реклама» добавляем аналогичный столбец «Связка: канал + услуга». В первой строке пишем формулу: =A2&B2 и протягиваем формулу вниз.

Шаг 2. Даём доступ к интеграционным функциям. Это может понадобиться, если вы работаете с формулой в Google Таблицах в первый раз. Нажмите кнопку «Разрешить доступ», формула INDEX+MATCH будет работать корректно и подтягивать значения из листа «Реклама» в «Заказы».

Шаг 3. Составляем формулу INDEX + MATCH. Для поиска расхода по кампании и типу услуги вводим в первой строке столбца «Расход, ₽» на листе «Заказы»:

INDEX('Реклама'!$D$2:$D$7; MATCH(D2; 'Реклама'!$C$2:$C$7; 0)).

• $ фиксирует диапазоны, чтобы при протягивании вниз они не смещались.
• 'Реклама'!D2:D7 — столбец с расходами на листе «Реклама».
• D2 — ключ «канал + услуга» на листе «Заказы».
• 'Реклама'!C2:C7 — ключи «канал + услуга» на листе «Реклама».
• 0 — точное совпадение.

Шаг 4. Протягиваем формулу вниз. Google Таблицы автоматически подтянет расходы для всех кампаний и услуг.

Для наглядности собрали возможности каждой функции в табличку и сравнили их между собой.

Заключение: что важно запомнить

ВПР — удобный инструмент для маркетолога. Он помогает быстро объединять таблицы и подтягивать данные, экономя время и снижая риск ошибок.

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

Для сложных задач с несколькими критериями используйте INDEX+MATCH или XLOOKUP. Эти функции гибче, показывают отсутствие данных и ускоряют формирование отчётов. Подбирайте инструмент под задачу и проверяйте диапазоны, чтобы цифры получались точными.