Кейс 8. Сводные таблицы и расчеты

Имеются следующие данные:

• Таблица с расходами по рекламной кампании на Google Adwords:

• Таблица с расходами по рекламной кампании на Yandex Direct:

• Таблица с информацией о доходам с группировкой по источникам рекламы:

Задачи

1. Привести структуру таблиц расходов к одному виду:

2. Подтянуть информацию о доходах в обе таблицы.
3. Рассчитать показатели CR, CPO, ROI для каждой рекламной кампании.
4. Объединить данные в одну таблицу.
5. Сформировать сводные таблицы, настроить условное форматирование и вывести графики.

Делаем расчеты и приводим в заданный вид данные о расходах в Yandex Direct

Столбец "В" таблицы содержит информацию о наименованиях рекламных кампаний. Для каждой рекламной компании имеются данные о расходах с детализацией до уровня дня. Наименование кампании содержится только рядом с первой датой периода.

Для дальнейшей обработки данных нам необходимо сделать элементы таблицы взаимосвязанными. Для этого нужно заполнить каждую пустую строку столбца "В" соответствующими наименованиями рекламных кампаний.

• Удаляем первую строку целиком:

• Выделяем весь столбец "В"
• На вкладке меню "Главная" выбираем команду "Найти и выделить" и выбираем "Выделить группу ячеек"

• В появившемся диалоговом окне выбираем пункт "Пустые ячейки" и нажимаем "ОК"

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

• Нажимаем комбинацию CTRL+Enter
• Пустые ячейки заполнились значениями

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

• Ставим фильтр на заголовок
• В заголовке наименования выбираем значения фильтра "по всем кампаниям"

• Нажимаем "ОК" и удаляем фрагмент отобразившейся таблицы кроме заголовка
• Далее переходим к фильтру заголовка "Дата" и выбираем поля "итого" и "пустые"

• Удаляем полученные данные и снимаем фильтры
• Приводим таблицу к необходимому виду. Добавляем пять столбцов после столбца "Е"

2.1. Рассчитаем CR (Количество кликов / Количество показов) * 100%

• Присваиваем наименование заголовку и вводим формулу:

• Протягиваем значение до конца таблицы

2.2. Заполняем поле "Расходы" значениями

В правой части таблице есть столбец, содержащий информацию о расходах.
Просто скопируем и вставим его в столбец соседний "CR".

2.3. Подтянем информацию о доходах из таблицы "Доходы"

Наименование рекламной кампании в таблице с расходами по Yandex Direct не совпадают с наименованиями из таблицы "Доходы". Значения ячеек с наименованиями кампаний Яндекса на листе доходов выглядят следующим образом:
chelyabinsk_brand_context|20112731

, где правая часть, состоящая из 8 символов (цифр) - это номер рекламной кампании. Его и будем использовать при суммировании дохода.

Для этого нам нужно:
- отфильтровать таблицу "Доходы" по столбцу "А" и критерию "yandex / cpc"
- скопировать полученный диапазон на новый лист (назовем его "Доходы Яндекс")
- отделить номер кампании от наименования при помощи текстовой функции "ПРАВСИМВ"

• Применяем фильтр и копируем таблицу с информацией о доходах с рекламы на Яндексе на новый лист
• Вставляем столбец правее столбца "С"

Сопоставляя номера рекламных кампаний, подтянем информацию о доходах:

Наименование рекламной кампании в таблице с расходами по Yandex Direct не совпадают с наименованиями из таблицы "Доходы". Значения ячеек с наименованиями кампаний Яндекса на листе доходов выглядят следующим образом:
chelyabinsk_brand_context|20112731

, где правая часть, состоящая из 8 символов (цифр) - это номер рекламной кампании. Его и будем использовать при суммировании дохода.

Для этого нам нужно:
- отфильтровать таблицу "Доходы" по столбцу "А" и критерию "yandex / cpc"
- скопировать полученный диапазон на новый лист (назовем его "Доходы Яндекс")
- отделить номер кампании от наименования при помощи текстовой функции "ПРАВСИМВ"

• Встаем на ячейку "В2", открываем Мастер функций и находим функцию "ПРАВСИМВ"
• В поле "Текст" указываем ссылку на ячейку с наименованием
• В поле "Количество знаков" указываем число 8 (номер рекламной кампании состоит из восьми символов)
• Нажимаем "ОК" и протягиваем до конца списк

• На листе с расходами встаем на ячейку "Н2" (доходы), открываем "Мастер функций" и находим "СУММЕСЛИМН"
• Для поля "Диапазон суммирования" указываем на листе "Доходы Яндекс" столбец "G" (Total Value)
• Для поля "Диапазон условия1" указываем на листе "Доходы Яндекс" столбец "D" (столбец с номерами рекламных кампаний)
• Для поля "Условие 1" указываем столбец "А" таблица с расходами по рекламной кампании Яндекс
• Нажимаем "ОК" и протягиваем формулу до конца таблицы

2.4. Рассчитаем СРО (Доходы / Количество показов)

Поскольку делитель или значения столбца "Количество показов" могут иметь нулевые значения, то будут ошибки в формуле. Поэтому создаем формулу в связке с функцией "ЕСЛИОШИБКА"

2.5. Считаем ROI (Доходы - Расходы) / Расходы

• Встаем на ячейку "I2", открываем Мастер функций и находим функцию "ЕСЛИОШИБКА"
• Для поля "Значение" указываем: H2/D2
• Для поля "Значение если ошибка" указываем 0

• Нажимаем "ОК" и протягиваем до конца таблицы

• Считаем значение показателя в связке с функцией "ЕСЛИОШИБКА", поскольку столбец расходы(делитель) содержит нулевые значения
• Таким образом, формула будет иметь следующий вид: =ЕСЛИОШИБКА((H2-G2)/G2;0)

• Протягиваем вниз и получаем столбец, содержащий только числовые значения

2.6. Удаляем все лишнее и оставляем только значения без формул

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

Объединение полученных данных в одну таблицу

• В левой части таблицы с полученными значениями по рекламной кампании Гугл (лист "Данные") вставляем столбец и присваиваем ему наименование "Канал"
• Встаем на ячейку "А2" и вводим значение "Google"

• Протягиваем до конца таблицы

• Выделяем всю полученную таблицу показателей по рекламной кампании Яндекса, копируем и вставляем в итоговую таблицу рекламной кампании Гугл. (лучше брать таблицу целиком вместе с заголовком, что бы убедиться в том, что вставляем данные в нужные столбцы)

При работе с большими массивами данных удобно фиксировать положение верхней строки. Используя эту функцию, можно листать таблицу вниз и видеть заголовки.

• Выделяем заголовок
• В панели меню переходим на вкладку "Вид" и выбираем "Закрепить области"
• И выбираем соответствующую команду

• Убедившись в том, что данные в столбцах обеих таблиц совпадают, удаляем заголовок таблицы, которую вставили.
• И проставляем в столбце "А" значения "Yandex"

Мы получили данные для построения отчета.

Сводные таблицы

• Выделяем диапазон таблицы полностью
• На вкладке меню "Вставка" выбираем команду "Сводная таблица"

• В диалоговом окне все оставляем по умолчанию. Данные мы уже выбрали, а отчет нам нужен на новом листе.

• Нажимаем "ОК". Создается новый лист с полями сводной таблицы:

4.1.Настраиваем поля сводной таблицы

Поля сводной таблицы делятся на пять составляющих:

1. Поля для отображения

Столбцы таблицы, на основании которой строится сводная таблица. Их нужно просто перетащить в одну из ниже перечисленные области.

2. Фильтры

В эту область перетаскиваем поля которые планируется устанавливать в качестве фильтра данных. Удобно использовать для того, что бы сузить разброс и анализировать данные в разрезе разных показателей и группировок.

3. Колонны

Сюда перетаскиваем те поля, значения которых должны расположиться в качестве наименований столбцов сводной таблицы.

4. Строки

В эту область перетаскиваем поля, которые должны стать наименованиями заголовков строк.

5. Значения

В поле значения перетаскиваются поля, которые будут отображены как значения. Если поле содержит числовые значения, то они будут суммированы по умолчанию. В противном случае будут посчитаны количества значений. Так же это возможно менять в настройках сводной таблицы.

4.2. Выведем сводную таблицу по рассчитанными нами показателей в разрезе источников рекламы

Перетащим поле "Канал" в область "Строки". Для этого просто нажимаем мышкой и перетаскиваем поле:

• В сводной таблице появились соответствующие поля
• В поле значений перетягиваем показатели

Получаем искомую таблицу.

4.3. Приведем ее немного в порядок

• Выделяем область значений таблицы
• Присваиваем им финансовый формат с разделителями

• И уберем знаки после запятой

• Данные в таблице стали выглядеть немного лучше


• Можно менять дизайн макета. Для этого нужно поставить курсор на поле сводной таблицы, тем самым мы активируем вкладку меню "Конструктор"
• Выбираем оформление из предложенных

Создание сводной диаграммы доходов в разрезе каналов рекламы

• Выделяем ту же таблицу с общими данными:

• В панели меню переходим на вкладку "Вставка" и выбираем команду "Сводная диаграмма":

• В диалоговом окне указываем место, где нужно разместить диаграмму:

• Нажимаем "ОК", и на указанном листе появляются поля для настройки отображения сводной диаграммы:

5.1. Настройка отображения сводной диаграммы

Настройка отображения полей сводной диаграммы аналогична настройке сводной таблицы. Просто перетягиваем поля таблицы в нужную область.

1. Фильтры

Перетащите в эту область те значения по которым нужно произвести отбор, сузить разброс данных

2. Легенда (ряды)

В эту область перетаскиваем поля, которые должны сгруппировать данные на графике. Например, при изображении структуры расходов туда можно перетащить наименования статей бюджета. Тогда для каждой статьи будет построен свой график. При этом в область фильтра можно так же поместить поле наименования статей и скрыть из вида не затратные статьи бюджета.

3. Ось категорий

Сюда следует перетащить поле, которое сгруппирует данные на осях координат. Например, если перетащить сюда дату, то для каждой даты будут отображены данные из области "Легенда (ряды)".

4. Значения

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

• Перемещаем поле "Каналы" в область "Ось категорий", а поле "Доход" в область "Значения"
• Получаем вид диаграммы

5.2. Работа над дизайном

• Первым делом поменяем название. Для этого просто нужно поставить курсор в поле названия на диаграмме и ввести нужный текст:

• Добавим подписи данных:

• Для того, чтобы график не был загроможден излишеством цифр, уберем оси:

• Перейдем к сводной таблице, на основе которой построена диаграмма, и зададим формат для чисел: