Сводим данные разных источников в одну таблицу

В исходных данных есть три таблицы: Импорт расходов Google Analytics, Данные по трафику с количеством заявок и Выгрузка по сделкам из AMO CRM. Эти данные необходимо объединить в одну таблицу, которая отобразит количество визитов, количество заявок и количество сделок. Для объединения данных из нескольких источников в один свод, необходим какой-то общий параметр, которому будет производиться склейка. В данном случае этим параметром будет Источник / канал.

1. Приводим к нужному формату

В таблице Импорт расходов, этот параметр присутствует, а в выгрузках из AMO CRM и Данных по трафику, его нет, но есть отдельные колонки с источниками и каналами. Чтобы привести их к нужному формату (источник / канал) пишем формулу: =A2&" / "&B2Протягиваем формулу до конца списка.В выгрузке из AMO создаём аналогичный столбец, используем данные utm_source (источник) и utm_medium (канал). Пишем формулу: =H2&" / "&I2, протягиваем до конца списка.Для созданных столбцов прописываем заголовки - Источник / канал.

2. Используем инструмент "Сводная таблица"

Теперь необходимо получить количество сделок по источникам из AMO и Данных по трафику. Для этого используем инструмент Сводная таблица. Выделяем всю таблицу на вкладке AMOCRM (обязательно выделить заголовки). Переходим в меню: Данные -> Сводная таблица…Откроется новая вкладка, на которой будет формироваться сводная таблица. Справа, в блоке «Редактор сводной таблицы», в разделе «Строки», нажимаем добавить и выбираем Источник / канал. В разделе «Значения», добавляем Этап сделки. В итоге мы получим сводную таблицу в которой отображено количество сделок для каждого источника трафика. Называем вкладку Свод AMO.Аналогичным способом создаём таблицу со сводными данными по трафику. В редакторе сводной таблицы, в поле «Строки» выбираем Источник / канал, а в поле «Значения» - SUM из Заявка на сайте (Достигнутые переходы к цели 1).

3. Используем функцию ВПР (вертикальный просмотр)

Теперь необходимо объединить весь трафик с заявками и созданные сделки. В этом поможет функция ВПР (вертикальный просмотр). Суть функции в том, что она берёт значение из одной таблицы, и ищет его в другой таблице. Если есть совпадение, то подставляет значение из определённого столбца по строке, в которой обнаружено совпадение, в противном случае – ошибка. На вкладке Свод по трафику создаём столбец Количество сделок, и прописываем формулу:

=ВПР(A2;'Свод AMO'!A$2:B$29;2;ЛОЖЬ)

A2 – искомое значение; 'Свод AMO'!A$2:B$29 – таблица в которой производится поиск (поиск по первому столбцу таблицы); 2 – столбец из которого будет подставлено значение при совпадении, ЛОЖЬ – искать точное совпадение; $ - используется для того, чтобы при протягивании функции вниз до конца списка, эти значения не менялись. В противном случае выделенный диапазон (таблица) будет смещаться вниз, и функция вернёт некорректные значения.На скрине видно, что в некоторый случаях функция ВПР подставляет значение #Н/Д, это значит, что значение не найдено и функция возвращает ошибку. Чтобы избежать такого отображения, и выводить вместо #Н/Д цифру 0, используем функцию обёртки ЕСЛИОШИБКА. Формула будет выглядеть следующим образом:

 =ЕСЛИОШИБКА(ВПР(A2;'Свод AMO'!A$2:B$29;2;ЛОЖЬ);0)

4. Функция ВПР для таблицы из Данных по трафику

Трафик подтягиваем при помощи ВПР из таблицы Данные по трафику. Предварительно необходимо создать столбец Источник / канал, после столбца Канал с соответствующими значениями. На листе со сводными данными добавляем столбец трафик с формулой:

=ЕСЛИОШИБКА(ВПР(A2;'Импорт расходов Google Analytics'!A$1:H$11;3;ЛОЖЬ);0)

Добавляем столбец Расходы с формулой:

=ЕСЛИОШИБКА(ВПР(A2;'Импорт расходов Google Analytics'!A$1:H$11;6;ЛОЖЬ);0)

Сводная таблица в Excel готова. Теперь можно оперировать полученными данными при помощи фильтров. Например, можно сделать выборку всех источников, по которым были заявки. В меню выбираем: Данные -> Создать фильтр. Чтобы Ваши данные из разных источников трафика не превратились в хаотичный набор цифр, используйте данную инструкцию для аналитики. Провести Аудит РК! Подпишись и следи за выходом новых статей в нашем монстрограмме Остались вопросы? Не нашли ответ на интересующий Вас вопрос? Или не нашли интересующую Вас статью?  Задавайте вопросы и темы статей которые Вас интересуют в комментариях.