cpalil

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

cpalil

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 готова. Теперь можно оперировать полученными данными при помощи фильтров. Например, можно сделать выборку всех источников, по которым были заявки. В меню выбираем: Данные -> Создать фильтр.

Источник

Понравилась статья? Поделиться с друзьями:
Комментариев: 1
  1. seoonly.ru

    :cool: удобно

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: