Скоро наш журнал обновит свой дизайн
Close
Будь в курсе
Подпишитесь на рассылку, чтобы не пропускать новые выпуски журнала
лекция
Аналитика и маркетинг
Дизайн дашбордов в гугль‑таблицах
Привет, друзья, с вами Куличевский, и сегодня мы будем учиться создавать дашборды в гугль‑таблицах. Короткий план занятия:

  1. Зачем это всё нужно:
    • что такое дашборды;
    • почему в гугль‑таблицах.
  2. Как работают таблицы:
    • формулы;
    • адресация;
    • сводные таблицы.
  3. Дизайн дашбордов:
    • архитектура;
    • процесс

Что такое дашборд

Сначала поговорим, зачем это всё нужно, что такое дашборды, почему в гугль‑таблицах. Затем повторим, как работают гугль‑таблицы и табличные процессоры в общем. Поговорим о формулах, сводных таблицах, сложных формулах, адресации, это всё понадобится потом для сложной практики. Потом поговорим непосредственно о дизайне дашбордов. Я расскажу об архитектуре и о том, как я подхожу непосредственно к процессу дизайна, к процессу создания, какие шаги и в каком порядке я делаю. Параллельно я вам покажу процесс дизайна маркетингового дашборда, который вы потом сможете использовать в своей работе. В конце занятия я вам дам исходные данные, с которыми я работал, чтобы вы тоже могли попрактиковаться, посоздавать собственные дашборды. Можете создавать точно такой же, как создавал я, просто повторяя шаги, а можете вносить какие‑то изменения.

Ну, начнём. Вот что мы будем делать.

Будем создавать маркетинговый дашборд. Это дизайн, который я сделал давным‑давно, ещё в «Групоне». Тогда я ещё делал его в Экселе, поговорим об Экселе чуть позже. Это дашборд, который я обновлял раз в неделю и отправлял топ‑менеджерам компании: генеральному директору, техническому директору и т. д. Отправлял я это для того, чтобы они понимали, что происходит в маркетинге. Что их может интересовать из маркетинга? Весь вопрос можно сформулировать как «Насколько эффективно мы тратим деньги?». Им интересно, сколько денег мы потратили, сколько мы получили новых зарегистрированных пользователей, сколько из этих пользователей стали покупателями и сколько мы заработали денег. Им всё это интересно смотреть именно понедельно, потому что у нас там практически вся аналитика была понедельная. Неделя — это был цикл планирования. И это было интересно смотреть по рекламным каналам. Только, так как мы постоянно экспериментировали с новыми рекламными каналами и у нас их было активно несколько сотен, естественно, им было неинтересно смотреть на все, им было интересно смотреть на те, куда уходит 90% наших денег. Таких каналов было всего 8, по‑моему. Вот я и делал такую таблицу, обновлял и высылал всем по почте.

Смотрите, что она умеет. Во‑первых, что мы здесь видим: данные по неделям, количество регистраций, расходы, новые покупатели и выручка. Ровно те показатели, которые интересны топ‑менеджменту. Эти показатели у нас разбиваются по неделям, и на дашборде мы видим последние восемь недель, чтобы была понятна динамика, иначе они увидят, что мы потратили 240 тысяч долларов, и у них могут возникнуть вопросы: «А это много или мало? А сколько было на прошлой неделе, на позапрошлой? Мы растём или падаем?» Если мы показываем восемь недель, то всё это видно. По каждому показателю есть маленький график, который показывает динамику. Дальше у нас идут суммарные данные, если здесь у нас — сколько регистраций было за такую‑то неделю, то здесь — сколько регистраций всего на конец недели. Они только копятся, никогда не убывают. И под абсолютными значениями показываются относительные значения, то есть относительные KPI, это конверсия покупателей, стоимость регистрации и выручка с регистрации. Это именно какая конверсия была на этой неделе. Дальше суммарные KPI: конверсия, стоимость регистрации, стоимость покупателя и тут же окупаемость, какая‑то бешеная. А, ну сейчас покажу, почему бешеная. В чём отличие суммарных и относительных KPI? Здесь мы считали недельные показатели, то есть как себя вёл определённый рекламный канал именно на каждой неделе, а тут мы считаем накопительные показатели, то есть сколько у нас всего, какая средняя конверсия с начала времён. Все эти данные можно посмотреть в разрезах по рекламным каналам. Можно кликнуть сюда и выбрать, например, Яндекс.

Всё сразу пересчиталось. За секунду у нас появились новые показатели. Можно выбрать, например, «Вконтакте». Всё тоже пересчиталось, видим новые показатели. Тут есть как платные рекламные каналы, так и бесплатные. Есть вот Site, www и invite. Site и www на самом деле одно и то же, просто в «Групоне» так было настроено, что если человек приходил на сайт www.groupon.ru">www.groupon.ru">www.groupon.ru">www.groupon.ru, то его записывали в источник Site, а если в www.groupon.ru, то его записывали в www, но на самом деле это одно и то же. Invite — это те, кого пригласили другие пользователи. Кликнем на Site, видим, тоже все показатели пересчитались, но отличие от платных в том, что у нас расход везде по нулям, и поэтому у нас тут ошибки. Кстати, ошибки — это плохо, по‑хорошему надо использовать формулу if error, которая эти ошибки бы исправляла и вводила какое‑то значение, но об этом чуть позже. Сразу расскажу, если мы выберем Grand total, все источники суммарные, вот тут 8000% арлай получается именно потому, что мы считаем арлай как суммарную выручку на суммарные расходы по всем каналам. В суммарную выручку просто попадают и бесплатные каналы тоже, поэтому она очень высокая, поэтому и арлай получается очень высоким. Если мы посмотрим другой канал, например «Вконтакте», то тут он тоже какой‑то супервысокий, видимо, у нас был очень выгодный период в то время, не помню уже. Ну, не суть. Можно не смотреть на конкретные цифры, они могут отличаться от реальности, и они точно будут отличаться от цифр, которые будут в вашем бизнесе, потому что у вас будут другие данные. Главное, смотреть на логику. Это короткая демонстрация.

Теперь давайте поговорим о том, что такое дашборд в принципе и чем он отличается, например, от маркетингового отчёта или от аналитического отчёта. Отличие на самом деле одно, и из него вытекает всё остальное. Если отчёт одноразовый, то есть вы провели какой‑то анализ, посмотрели на какие‑то данные, составили всё это в табличку и послали, то дашборд многоразовый. Предполагается, что вы его один раз сделали, а потом данные в нём будут обновляться, а сам дашборд будет оставаться такого же дизайна. Исходя из этого, при создании дизайна дашборда нужно учитывать то, что данные будут постоянно добавляться, и сам процесс добавления этих данных должен быть удобным, в идеальном случае вообще автоматизированным, чтобы данные просто добавлялись, всё пересчитывалось и никому ничего не нужно было делать. В чуть менее идеальном варианте нужно в любом случае количество действий по обновлению этого дашборда сводить к минимуму. Чем больше действий, тем больше трудозатрат и тем больше вероятность совершить ошибку. Этот дашборд, так как он сделан в гугль‑таблицах, он полуавтоматизированный. Например, допустим, прошла новая неделя, и вы хотите, чтобы вот здесь 24 неделя стала 25, вы просто берёте, добавляете сюда нужную циферку, и всё пересчитывается. Только тут данных по 25 неделе ещё нет, поэтому тут всё пустое. Предположим, что мы были сейчас на 20 неделе, всё пересчиталось на 20 неделю, пошла 21, всё сдвинулось, 22, всё сдвинулось. Новые данные мы не вставляем в каждую ячеечку копипастой, а вставляем в другой лист, гугль‑таблицы делают над ним всякие вычисления, и сюда, в дашборд, уже выпадают рассчитанные значения. О том, как конкретно это происходит, поговорим чуть позже.

Следующее: почему именно гугль‑таблицы? Вообще в мире есть огромное количество разного софта для дизайна дашбордов. В принципе, этот софт, как и любой специализированный софт для решения какой‑то задачи, удобнее, чем какой‑то универсальный софт. Гугль‑таблицы — это универсальный софт.


Почему гугль‑таблицы

Следующий вопрос — почему гугль‑таблицы. Тут две части ответа:

  1. Почему гугль‑таблицы, а не специализированный софт для дизайна дашбордов.
  2. Почему гугль‑таблицы, а не Эксель.
Начнём с первого вопроса. Есть огромное количество софта, предназначенного для дизайна дашбордов. Я показываю вам в гугль‑таблицах, потому что гугль‑таблицы универсальны и бесплатны. У каждого из вас к ним есть доступ, вам не нужно никакой специальной регистрации, вам не нужно ничего устанавливать. Гугль‑таблицам всё равно, на какой системе вы работаете. Они у каждого есть, они доступны, и они могут делать почти всё, что может делать специализированный софт. Вы можете задизайнить практически любой дашборд. Два существенных недостатка — это то, что в специализированном софте вы можете гораздо лучше всё автоматизировать, по крайней мере, вам будет проще это автоматизировать, во‑вторых, специализированный софт лучше работает с большим количеством данных. Но, так как мы с вами будем говорить об архитектуре и процессе дизайна дашборда, поняв и научившись их делать в гугль‑таблицах, вы сможете спокойно перенести их в любой другой софт. Вы будете отлично понимать, как это работает, как оно должно работать и как к этому подойти. Считайте, это базовый навык, который вы потом перенесёте.

Почему гугль‑таблицы, а не Эксель. Я честно скажу, изначально вот этот дашборд был сделан в Экселе, потому что гугль‑таблиц тогда не существовало. У гугль‑таблиц есть несколько очень важных преимуществ перед Экселем. Они бесплатные, они не зависят от платформы, и они есть у всех. Но это не все преимущества. Есть ещё одно очень важное отличие. Дашборд вы редко делаете для себя, обычно вы хотите его сделать, чтобы на него смотрела ваша команда, ваше руководство, кто‑нибудь ещё. Вы будете его шерить. Если вы будете дизайнить дашборд в Экселе, то процесс шера будет примерно такой: послать табличку по имейлу или закинуть табличку в дропбокс. Если с дашбордом будете работать не только вы, а кто‑то ещё, то появляются адские проблемы с версионностью, я вот забыл послать и т. д. В общем, неудобно. Гугль‑таблицы онлайн, их можно шерить, с ними могут работать несколько человек одновременно. Это всё гораздо удобней. Единственное преимущество Экселя перед гугль‑таблицами в том, что Эксель лучше работает с большими данными, он мощнее. Эксель начинает лагать на бо́льшем количестве строк, чем гугль‑таблицы. Но на практике я с этим сталкивался очень редко. Более того, если вы начинаете пытаться загрузить в гугль‑таблицы 300–500 тысяч строк и они начинают лагать, я считаю, что это знак, что вы что‑то делаете не так. Возможно, вам нужно добавить ещё какой‑то слой обработки данных в базе данных или ещё где‑то, чтобы всё это дело убрать.

Это о том, почему мы работаем в гугль‑таблицах, а не в Экселе или ещё каком‑то специализированном софте.
Формулы

Идём дальше. Как работают гугль‑таблицы. Прежде чем мы полезем в дизайн, я хочу, чтобы все вы находились на одном уровне понимания того, что происходит, как всё это дело работает. Я уверен, что среди вас есть более уверенные пользователи, менее уверенные пользователи, хочу, чтобы все стали уверенными. Давайте создадим новую таблицу и пройдёмся по основам. Я хочу вам рассказать о том, как работают формулы, адресация и сводные таблицы. С формулами я потом ещё расскажу про вложенные формулы. Рассмотрим базовые формулы, как работают формулы поиска, и я вам покажу, как искать информацию. Это нормально, если вы не знаете, как работает та или иная формула. Не нужно ни в коем случае их помнить, но важно уметь искать, понимать, где найти информацию о какой‑либо формуле. Или если у вас есть какая‑то задача, но вы не знаете, какая формула эту задачу решит, то сможете это найти. Мы с вами попробуем это всё сделать.

Вот перед вами таблица. Что о ней нужно знать? В первую очередь нужно знать вообще, из чего она состоит, это важно. Таблица состоит из строк и столбцов. Строки пронумерованы цифрами, столбцы — латинскими буквами. На пересечении строки и столбца находится ячейка. У ячейки есть адрес. Этот адрес состоит из комбинации названия столбца и номера строки. Мы сейчас находимся в ячейке B5.


Помимо адреса у ячейки есть значение, которое внутрь неё записано. Сейчас все ячейки пустые. Если мы добавим сюда цифру 5, то в ячейке B5 сохранится цифра 5. Все значения могут быть трёх типов: числа, над которыми можно проводить математические операции; текст и формулы. Заметьте, что по умолчанию текст всегда выравнивается по левому краю, а числа по правому. Иногда то, что выглядит как число, может быть не числом. Например, у нас есть число 23452, которое на самом деле не число, а текст. Его складывать с какими‑то другими числами не получится. А, нет, смотрите, гугль‑таблицы умные, они поняли, что его можно распознать как число, и распознали. Эксель, по‑моему, тут может выдать ошибку. В общем, не стоит так делать. Как я сделал из числа текст? Я добавил апостроф в начале. Если апостроф находится перед значением ячейки, то он говорит, что это текст. Третий тип данных ячейки — это формулы. Все формулы начинаются со знака «равно». Дальше идёт какое‑то служебное слово, которое показывает, какую формулу мы хотим использовать. Например, если мы хотим посчитать сумму значений в ячейках, мы пишем СУММА. Дальше обязательно кавычки открываются, кавычки закрываются. Между кавычками пишется значение параметров, которые идут в эту формулу. В случае суммы нам нужно указать, сумму чего мы хотим посчитать. Тут мы можем указать как значение напрямую, например чисел 1, 2 и 3, он отлично сложит их и получит 6, а также мы можем сюда добавить адрес ячейки. Например, мы хотим посчитать 1, 2 и B5. Если мы введём B5, гугль‑таблицы поймут, что это адрес ячейки, и подставят в формулу значение, находящееся в ячейке B5, в данном случае это значение 5. Если сейчас мы поменяем значение ячейки B5, оно автоматически подставится в формулу. В формулах можно указывать не только значения ячеек, но можно указывать диапазоны. Для того чтобы указать диапазон, мы просто берём, зажимаем и протягиваем. Смотрите, как у нас обозначаются диапазоны. Они идут от начальной ячейки до конечной ячейки. Начальная — левая верхняя, конечная — правая нижняя. Самая простая формула, которая может быть, — это просто ссылка на ячейку. Если вы введёте =B5, это тоже является формулой, и он просто вернёт значение ячейки B5 в ту ячейку, где у вас находится формула.

Смотрите, какие могут быть штуки. Допустим, у нас есть данные: месяц, выручка и количество покупателей.

Вы, например, хотите перевести всю выручку из рублей в доллары. У вас есть курс доллара. Кстати, я вам сейчас покажу пример классной формулы, которая есть тоже в гугль‑таблицах, это курс любой валюты, он подсчитывается сам. Есть такая =GOOGLEFINANCE, дальше нужно указать тикер, это как раз из какой валюты в какую вы будете переводить. В кавычках, как текст, мы хотим перевести из долларов в рубли, "USDRUB".

Вот на текущий момент один доллар стоит 57,548 рублей. Это вам просто такая фича, для удобства.

Добавим поле «Выручка $». Вы хотите пересчитать выручку в рублях. Вы понимаете, что вам просто надо поделить выручку в рублях на курс доллара. Для этого нужно использовать формулу. Нажимаем «равно», кликаем по выручке в рублях, ставим слеш, как знак «делить», и кликаем по курсу доллара. Получилось 17,38 долларов. Не обращайте пока внимания на то, что у нас где‑то доллар в конце, где‑то в начале, не так важно. Но мы же хотим пересчитать не один месяц, мы хотим пересчитать все месяцы. Что нам для этого нужно сделать? Мы можем взять и размножить эту формулу на всю строку. Есть несколько способов это сделать.

Копировать формулу и вставить формулу. Копируем, выделяем, зажимаем шифт и нажимаем ctrl+v/cmd+v. Оно вставилось, но у нас появляется ошибка. Что делать, когда появляется ошибка. Во‑первых, он вам сразу говорит, что за ошибка. Div делить на 0 — это ошибка деления на 0. Если вы наведёте на ошибку, то он вам выдаст более подробную информацию. Как её исправить? Вы видите, что он пытается поделить на ноль, пытаемся понять почему. Можете кликнуть сюда и увидеть, что произошло. Видите, раньше вы делили 1000 на 57, а теперь формула на шаг ниже, и теперь он все значения тоже перенёс на шаг ниже. Это, в общем, удобно, так как вам нужно всё из них поделить, но теперь у вас возникает ошибка, потому что у вас курс доллара здесь, а он пытается поделить на ячейку ниже. Так быть не должно. Он делает это потому, что по умолчанию в гугль‑таблицах, как и в экселе, все адреса формул считаются относительными. Это значит, что каждый раз, когда вы будете переносить формулу, у вас адреса используемых формул будут переноситься на соответствующее количество ячеек вниз, влево, вправо или вверх. В большинстве случаев это полезно, но иногда это вредно. Для того чтобы исправить ситуацию, нам нужно, чтобы выручка сдвигалась ниже каждый раз, но мы не хотим, чтобы курс доллара куда‑то сдвигался. Мы можем адрес ячейки, в которой находится курс доллара, изменить с относительного на абсолютный. Для этого мы можем добавить знак доллара. Мы можем добавить знак доллара перед G и перед 1.

Это говорит, что мы хотим зафиксировать этот адрес, чтобы он не сдвигался ни по вертикали, ни по горизонтали. Сейчас у нас визуально ничего не изменилось, но если мы скопируем, вставим, то у нас всё пересчитается. Смотрим, что происходит. Вот тут, например, у нас выручка сдвинулась, а курс доллара не сдвинулся. Кажется, что всё идеально, но есть ещё одна штука, которую я вам хочу показать. А что, если мы хотим взять и скопировать эту ячейку вот сюда? Допустим, мы хотим, чтобы выручка при этом всё равно осталась такой, какая она есть. Если мы перенесём её сюда, то выручка внезапно изменится на 60 центов. Повторяем процесс, кликаем по формуле и смотрим, что произошло. У нас курс остался на месте, но выручка в рублях сместилась на два столбца правее, как и сама ячейка. Мы этого не хотим. Тогда мы можем в формуле поставить доллар перед номером столбца, но не ставить перед номером строки. Таким образом мы говорим: «Мы не против, чтобы строка смещалась, но мы не хотим, чтобы столбец смещался». Можем сделать наоборот, тогда у нас будем смещаться столбец, но не будет смещаться строка. Сейчас копируем, вставляем, и теперь доллары сохранились. Теперь подставим ещё вот сюда, и у нас сразу всё посчиталось, потому что у нас строка сместилась, а столбец не изменился.

То, что я вам показал, называется абсолютной и относительной адресацией. Это нам пригодится при дизайне дашбордов.

Есть одна очень важная формула, которая вам понадобится. Только я вам покажу её не прямо сейчас, а после того, как покажу —

Сводные таблицы
Сводные таблицы — это, наверное, основной инструмент Эксель вообще. Их обязательно нужно знать, обязательно нужно уметь с ними работать. Именно в них заключается вся мощь Экселя. Давайте изменим данные, добавим ещё что‑нибудь, и я вам покажу, в чём их мощь. Допустим, у нас данные не только по месяцам, но и по городам. Напишем тут города: Москва, Челябинск и Пекин. Вот у вас есть такая таблица, где у вас есть продажи по городам и месяцам. В реальности у вас может быть этих категорий и данных гораздо больше. И вы хотите всё это проанализировать. Например, вы хотите посмотреть суммарные продажи по месяцам вне зависимости от города. Или вы хотите посмотреть продажи в Челябинске вне зависимости от месяца. Или по городам, сколько всего у вас было продаж в каждом городе за всё время. Если бы у вас были ещё другие какие‑нибудь категории, вы могли бы посмотреть, какие у нас продажи в Москве по такой‑то товарной категории и т. д. Когда у вас табличка с исходными данными состоит из 12 строк, то, в принципе, вы всё это можете посмотреть здесь. Но если у вас данных несколько тысяч строк, а то и несколько миллионов строк, то это уже становится проблематично. Всё ещё можно, но ужасно долго и неэффективно. Для этого придумали сводные таблицы. Показываю, что это такое, заодно показываю, как их делать. Во‑первых, выделим все данные, которые у нас есть. Для того чтобы выделить целиком столбец, я кликнул на название столбца. Точно так же можно кликать по названиям строк и выделять строки полностью. Кликаю по названию столбца, зажимаю шифт, кликаю по названию последнего столбца, иду в Data (данные) и нажимаю Pivot table. У меня создался новый лист, он пока выглядит пустым, но у меня есть вот такая вот таблица. Это и есть сводная таблица. Показываю, что она умеет делать. У вас есть меню, которое позволяет кастомизировать. Допустим, мы хотим посмотреть выручку по городам. У нас есть 4 измерения: строки, столбцы, значения, фильтр. Давайте начнём со значений. Мы хотим посмотреть выручку, а точнее, сумму выручки. Кликаем по Add field (добавить поле).

У вас здесь показываются те заголовки столбцов, которые у вас есть в таблице. Он их берёт из первой строки таблицы, которая у вас есть. Я кликнул по выручке, и он по умолчанию мне сказал — сумма, это агрегация. Он увидел, что это числовые значения, и он может принять решение, как он будет их агрегировать. Он будет их складывать все вместе, он будет их считать, он будет считать уникальные значения, он будет считать среднее, максимальное, минимальное, медиану и т. д. Теперь мы хотим посмотреть выручку по городам. Давайте теперь в строки добавим поле «город». Он сам понял, что у нас есть 3 уникальных города, и посчитал выручку по каждому из этих городов.

Мы решили задачу. Теперь мы хотим посмотреть выручку не по городам, а по месяцам, без городов. Убираем город, выбираем месяц. Теперь у нас есть выручка по месяцам. Допустим, теперь мы хотим, чтобы у нас в строках были города, а в колонках можем добавить месяцы. И вот у нас теперь выручка Москвы в 1–4 месяц, выручка Пекина и Челябинска. И у нас тут же есть суммарная выручка каждого города, а тут суммарная выручка каждого месяца.

Вы это уже делали, вы с этим уже сталкивались, когда делали когортный анализ. Когортный анализ — это одна большая сводная таблица, просто там немного другие данные. Небольшая ремарка, у вас здесь есть такой 0, который занимает кучу места, он занимает целый столбец, целую строку, и вообще непонятно, откуда он взялся и зачем он нужен. Откуда он взялся? Когда мы выбирали данные для сводной таблицы (кстати, изменить данные можно вот здесь, кликаем, и он скажет, откуда берутся исходные данные), мы взяли полностью столбцы с A по E. Если мы посмотрим, что в этих столбцах, то мы видим, что у нас есть какие‑то данные вот здесь, а дальше идёт пустота. Вот эта пустота агрегированная и даёт 0. Зачем он нужен? Нам он не нужен, он скорее мешает, поэтому было бы хорошо его убрать. Для того чтобы убрать, удобно использовать фильтр. Мы можем в фильтре отфильтровать так, чтобы показывался месяц, например, только первый. Всё пересчиталось. Во‑первых, убрался нолик, во‑вторых, убрались все остальные месяцы. Но мы не хотим только первый, мы хотим все. Выберем всё и можем снять галочку с Blanks, это значит — те месяцы, где в ячейках пустота, выводить не нужно.

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

Что ещё мы можем сделать? Мы можем смотреть не только один показатель, а мы можем смотреть несколько показателей. Например, нам нужна и выручка, и количество покупателей. Смотрите, у нас вот здесь написано Values as columns, то есть у нас каждое значение как отдельный столбец. Вот у нас есть 1, и у него подстолбец: сумма выручки и сумма количества заказов. Мне, например, не нравится, как это выглядит, это выглядит коряво. Мы можем изменить as columns на as rows. Теперь у нас вот Москва, и здесь выручка и количество покупателей. Можно сюда добавить ещё всякие поля, они сюда все подставляются. Мы можем убрать тоже из колонок месяц и добавить его тоже в столбцы. И теперь у вас есть Москва, в Москве все эти 4 месяца, а дальше там какие‑то метрики. Всё вообще в столбик получилось. Можете взять и поменять месяц и город местами. Теперь у вас есть месяц и внутри каждого месяца по городу.

В общем, сводные таблицы позволяют крутить и вертеть данные как угодно, это удобно. Давайте сейчас вернёмся к более простому виду, где у нас продажи по городам и по месяцам, и пока на этом остановимся. Это всё, что я хотел показать о сводных таблицах, скоро вы увидите всё это в деле.
Формула VLOOKUP
Она есть и в Экселе, она есть и в Намберс даже, в русском Экселе она называется ВПР. VLOOKUP расшифровывается как Vertical Look Up (вертикальный поиск). Она ищет в какой‑то таблице нужные вам значения. Она очень полезная, если вам нужно достать из какой‑то таблички одно значение и у вас есть какой‑то признак. Например, вот продемонстрирую, как он работает вот здесь, тем более на практике со сводными таблицами VLOOKUP работает.

Допустим, я хочу достать из сводной таблицы мартовскую (месяц номер 3) выручку Пекина. Как я могу это сделать? VLOOKUP ищет исходные значения в первом [столбце] таблицы, а потом, когда он его находит, он идёт горизонтально и возвращает значение ячейки под нужным нам номером. Показываю на примере. Давайте мы лучше сделаем не мартовскую выручку Пекина, а итоговую выручку Пекина. Сначала мы вводим, что мы ищем, вводим Пекин. Дальше мы вводим рейндж, это то, где мы ищем, выделим всю вот эту таблицу. Теперь нам нужно сказать номер столбца с нужным нам значением. В нашем случае это 6 столбец, мы вводим 6. И Sorted, вам не нужно знать, что это значит, просто всегда пишите здесь False. Не заморачивайтесь, можете заморочиться и пойти прочитать документацию, но на практике вам это практически никогда не нужно, просто пишите False.

Вот мы получили значение. Что сейчас произошло? Показываю по порядку. VLOOKUP получила адрес вот этого массива, я ей сказал найти Пекин. Она пошла из этой ячейки вниз по первому столбцу, она ищет только в первом столбце и идёт подряд. Это Пекин? Нет, это не Пекин. Это Пекин? Да, это Пекин, нашла. И мы ей говорим: «Выведи значение шестого столбца строки Пекин». Теперь, когда она нашла вертикально, она идёт горизонтально. И вот оно, наше итоговое значение.

Теперь, допустим, мы хотим не Grand total, а апрель, 4 месяц. Это на один столбец левее, 6 меняем на 5, получаем искомое значение. Допустим, что теперь мы хотим не Пекин, а Челябинск. Вуаля, пересчиталось. Теперь мы хотим не 5 столбец, а 16. Он выдаёт ошибку reference. Наводим и читаем подробнее. Мы говорим ей выбрать значение в 16 столбце вот этого диапазона, а в диапазоне всего 6 столбцов, там нет 16. Она говорит, что такого столбца нет, и выдаёт ошибку. Другой пример, давайте найдём не Пекин или Челябинск, а Нью‑Йорк. Она говорит N/A, not applicable. Нью‑Йорка здесь нет, поэтому он выдаёт ошибку.

Допустим, мы не хотим, чтобы у нас в дашборде показывались сообщения об ошибке. Вместо этого мы хотим, чтобы вводилась какая‑нибудь пустота. Для этого мы можем использовать формулу if error, по‑русски — если ошибка. Что он делает? Он принимает какое‑то значение, значение может быть, например, другой формулой, формулы можно запихивать внутрь других формул. Мы пока поставим ноль. Если эта формула ошибочна, то он возвращает значение какое‑то другое. Пока поставим пустые кавычки.

Пустые кавычки значат пустое множество. То есть в кавычках ничего нет. Получилось пока 0, потому что 0 — это не ошибка, 0 — это значение. Можем заменить 0 на значение ячейки B12, и тогда он вводит пустоту. А ещё мы можем прямо вот здесь обернуть эту формулу в if error.

Теперь тут тоже пустота. Это нам тоже пригодится.

How to google
Я хочу вам показать, как искать информацию о той или иной формуле или о той или иной функции. Если вы постоянно не работаете с инструментом, немудрено, что вы можете забыть какие‑нибудь детальки.

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

И тут можно кликнуть на ссылочку и прочитать ещё больше в хелпе. Аналогично, можно просто гуглить. Заходите в гугль и вводите по одной и той же формуле: Google sheets match. Или можете даже сделать вот так: Google sheets match(), чтобы он точно понимал, какая формула вас интересует, и вы уточняете, что вы хотите именно Google sheets, а не в Экселе или где‑то ещё. И вот вам, пожалуйста, ваш хелп.

Кстати, так как Гугль умный, он вам показывает не только ту формулу, которую вы ищете, но ещё и похожие. Match на самом деле похожи с VLOOKUP. Может быть, вам нужна какая‑то ещё. Тут есть ещё индекс из той же серии. Тут можно начинать читать.

2. Аналогично, допустим, вы не знаете, какая вам нужна формула, но вы знаете, что вы хотите получить. Вы можете точно так же гуглить. Я рекомендую гуглить по‑английски, потому что информации больше, но, наверное, можно и по‑русски. Допустим, вы хотите find value in range. Вы просто вводите, что вы хотите получить, например, найти значения в таблице, и гуглите. Вот, пожалуйста, он вам находит те же VLOOKUP и match. Другой пример, допустим, у вас есть таблицы, где есть какие‑то дубликаты, и мы хотим их убрать. Вот тут даже уже это есть, Google sheet find duplicates. И у вас есть целая инструкция, как убрать дубликаты в таблице.

В общем, в гугле есть всё, всё очень хорошо прописано, замечательный хелп. Не пытайтесь запомнить все формулы и все функции. Просто не бойтесь гуглить.

Архитектура

Пришло время перейти непосредственно к дизайну дашбордов. В дизайне дашборда мы поговорим про архитектуру, про то, из каких блоков состоит дашборд, и про процесс.

Сначала вернёмся к дашборду, который я вам уже показывал. Смотрите, основной принцип — данные отдельно, вычисления отдельно, презентация отдельно. Это самое важное, что есть. То, что вы видите сейчас на экране, — это финальная презентация, это то, что люди будут видеть. Также в гугль‑таблицах полезно и удобно отделять разные блоки на разные листы. У вас есть лист Periods, где есть куча сводных таблиц. Все сводные таблицы — это блок про вычисления, здесь вычисляются нужные вам показатели. Он на отдельном листе. Обратите внимание, что он никак особо не отформатирован. Это сделано специально, потому что красивое форматирование нужно, только если люди на это смотрят, и, если у вас постоянно что‑то считается, оно у вас постоянно слетает, и это неудобно. Поэтому презентацию отделяйте. Дальше, чтоб сводные таблицы посчитались, вам нужны исходные данные. У вас есть два листа, сюда идут исходные данные. Почему именно два листа, а не один? Для этого вернёмся к презентации. Мы считаем эффективность маркетинга, эффективность вложения. Для того чтобы посчитать все нужные нам показатели, у нас есть два источника данных. Первый — это наша собственная система, то, где происходит продажа, где фиксируются продажи. И вам нужны данные о расходах. Расходы рекламные обычно фиксируются в совершенно другом месте, часто даже в куче разных мест, в рекламных каналах. Допустим, вы рекламируетесь в Яндексе, в Фейсбуке, Мэйл ру, покупаете рекламу на РБК и т. д. Каждая из этих рекламных площадок хранит информацию о том, сколько денег на неё потратили. Обычно, по умолчанию, какого‑то единого места, куда эти данные скапливаются, нет, что на самом деле большая проблема, и я настоятельно вам рекомендую потратить усилия и эту систему сделать. Можете сливать всё в гугль‑аналитикс, например, или в вашу собственную базу, но это полезно сделать. Так или иначе, у вас есть два источника данных, и, когда я создавал этот дашборд, они были не связаны. Поэтому, когда я обновлял данные, я делал выгрузку из нашей базы данных о том, сколько мы заработали. Я поговорил с разработчиками, показал им формат данных, что вот мне по неделям по каждому рекламному источнику нужно знать, сколько было аккаунтов, сколько покупателей и сколько выручки. И они мне просто сделали функционал, чтобы он мне по кнопочке это доставал. Точно так же мне были нужны данные о расходах, я просто заходил в каждую рекламную систему, скачивал оттуда отчёт, приводил всё к единому формату и добавлял сюда. Обратите внимание, что и выручка, и расходы вообще не отформатированы. Это вообще не важно. Главное, чтобы тут были правильные значения. Сюда вообще никто никогда не будет заглядывать. Если хотите сделать красиво — делайте на здоровье, но если вдруг что‑то будет некрасиво, то ничего не сломается. У вас есть два источника данных, поэтому у вас есть два листа, куда эти данные поступают. Что здесь есть ещё? Есть листы: источники, недели и города. Это вспомогательные штуки, мы разберёмся чуть подробнее в том, зачем они нужны, чуть позже. Но иногда вам нужны какие‑то вспомогательные штуки, и вы можете просто создать отдельный лист и добавить их в этот лист. Но главный принцип — всё отдельно. Это позволяет быстро добавлять данные, я копирую и вставляю новые данные просто в конец, они потом сами идут в вычисления, и в вычислениях они добавляются все просто в свободные столбцы, и потом уже отсюда данные в красивом виде переносятся в финальную презентацию. Архитектуру любого дашборда лучше делать именно по такой структуре.


Дизайн дашборда
Самое важное — процесс. С чего начать? Есть два подхода. Подход первый: у вас есть какие‑то исходные данные (например, в таком виде), и вы делаете из них какой‑то дашборд. И есть второй подход, когда вы сначала дизайните финальную презентацию и из финальной презентации понимаете — какие данные и в каком виде вам нужны, а потом уже идёте к вычислениям и делаете презентацию. Я настоятельно рекомендую: всегда (когда это возможно) используйте второй подход. И вот почему. Потому что дашборд — это точно такой же информационный продукт, как и сайт, как мобильное приложение, статья, чатбот и т. д. И, как у любого другого информационного продукта, у него есть самое важное свойство — польза. То есть прежде чем собирать какие‑то данные и прежде чем вы начинаете дизайнить какое‑то представление и собирать метрики — вам нужно задуматься, какую пользу этот дашборд будет приносить. Польза не бывает в отрыве от людей, которые пользуются. Самый первый вопрос — это отчёт, то есть кто будет смотреть этот отчёт и зачем он будет это делать.

Помните, когда мы говорили о метриках, мы обсуждали и называли главный вопрос аналитики: «Что конкретно я сделаю, получив (узнав) эти данные?» Тут этот вопрос как никогда актуален. То есть первый вопрос, на который вам нужно ответить, — это кто будет смотреть дашборд и кто и какие действия будет предпринимать по результату этого дашборда. Например (раз уж мы говорим про маркетинг), я могу рассказать по примеру вот этого дашборда то, с чем я сталкиваюсь чаще всего.

То есть я директор по маркетингу и хочу иметь перед собой сводку о том, как работали все каналы, — для того, чтобы принимать решение о бюджетировании. Снизим уровень абстракции: что такое решение о бюджетировании? Я хочу понять, куда мне тратить деньги, а куда мне не тратить деньги. Вот действия, которые я буду предпринимать, узнав эти данные. Теперь вопрос: что мне необходимо узнать для того, чтобы принять то или иное решение? Окей, у меня есть некоторое количество рекламных каналов. По каждому из этих каналов я какие‑то деньги потратил, и в результате этих затрат что‑то произошло. То есть какие‑то люди увидели рекламу, кликнули по баннеру, перешли на сайт, зарегистрировались и что‑то там купили. Соответственно, мне было бы интересно посмотреть по каждому рекламному каналу — сколько денег я потратил, сколько у меня было показов, сколько было кликов, сколько было регистраций и сколько было покупок. И какая была выручка, и какой у меня ROI. Это надо мне как директору по маркетингу. Поэтому дашборд по маркетингу будет с такими метриками. То, что видите вы, — это дашборд не для директора по маркетингу. Это дашборд для немаркетингового топ‑менеджмента компании. Что им интересно? Им интересно более высокоуровневое. Сколько было показов и кликов — для них не такая ценная информация; им интересно, что в итоге было: сколько было выручки и сколько расходов (какой был ROI). То есть если в каком‑то канале был отличный CTR, но хреновая конверсия, и выручка составила x, а в другом канале был хреновый CRT, но отличная конверсия и выручка та же самая, x, то директору по маркетингу это было бы интересно. Топ‑менеджменту это было бы не особо интересно, и для них эти два канала пока одинаковы. И точно так же мне, например, было бы интересно смотреть в каждом канале на кампании, из чего он состоит (в каждом канале несколько кампаний крутится), а им — не интересно (им эти данные ничего не показывают).

Резюмируем. Сначала, в первую очередь, вы думаете о пользе: о том, кому вы эту пользу приносите. То есть кто аудитория вашего продукта и какие проблемы вы с помощью этого продукта для неё решите. Дальше вы начинаете по пирамиде расписывать: чтобы решить проблему и принять решения, нужно знать те или иные данные человека. Так вы и получаете, окей, топ‑менеджменту нужно знать по каждому рекламному каналу (вот у нас два канала): сколько было регистраций, сколько мы потратили денег, сколько было покупателей и какая была выручка. Да, и так как это отчёт еженедельный — данные нужно сгруппировать по неделям и показывать за каждую неделю в динамике. Остальное — это местами дополнительно мясцо, местами — жирок, который можно было бы сюда и не вставлять (но было полезно на него посмотреть). Это не только по неделям данные, но и данные за всю историю: какую долю в бизнесе занимает, например, «Вконтакте» против Яндекса. Во «Вконтакте» было почти полмиллиона регистраций, а в Яндексе — всего 75 тысяч. Или там напрямую пришло 199 тысяч. Недельный KPI интересно смотреть (конверсию же полезно смотреть), есть всякие конверсии. То есть начинайте именно с дизайна самого дашборда. Вот как это может выглядеть. Давайте создадим новую таблицу. Так, что нам нужно? Давайте попробуем задизайнить такой же дашборд, как у нас был.
Презентация

Мы поговорили о том, что топ‑менеджменту нужно знать: сколько денег мы потратили (расходы), сколько денег мы заработали (выручка) и откуда эти доходы пришли (сколько было регистраций), сколько новых покупателей мы получили. И нам нужно это знать по неделям. Это мы хотели знать по рекламным каналам, значит, тут будет «Выбор рекламного канала». Небольшая ремарка. Я забыл рассказать, как сделать вот такой дропдаун (тоже базовая функция). В Экселе и в гугль‑таблицах она называется валидация, и я покажу, как она работает. В чём суть? Суть в том, что по умолчанию можно в любую ячейку вносить любые значения. Вы можете сказать, что «мне вообще‑то не нужно вводить любые значения, а нужно вводить только числа от 0 до 100». Тогда он показывает — ошибочка, Invalid. И таких валидаций может быть много типов. Тогда надо выбрать лист From range. Это говорит о том, что в эту ячейку можно вводить только числа, которые находятся в каком‑то списке. И список этот можно сделать List from range, а можно List of items. Сейчас покажу, как это сделать. Мы можем сказать, что тут может быть только «Вконтакте», Mэйл ру и, например, Фейсбук. Вот, видите, появилась такая штучка: то есть он вам сам помогает вводить сюда новые значения (сюда можно вводить что угодно руками). Подсказывает мне, что Фейсбук подходит. И можно кликнуть сюда — появилась вот эта выпадашка. Мы вернёмся к ней чуть позже. В чём отличие List of items от List from range — обсудим чуть позже.

Вот у нас есть наши метрики, вот у нас есть канал, и давайте заполним какие‑нибудь значения. Для того чтобы заполнить значениями, я использую функцию Range between. Эта функция выводит случайные значения «от и до» — например, от нуля до пяти тысяч. Вот, 2777, скопируем его прямо вот сюда. Давайте сразу отформатируем — расходы у нас будут в долларах. Видите, как всё поменялось? Теперь каждый раз я буду это делать. Выручка у нас тоже будет в долларах. Новые регистрации покупателей сделаем. И, чтобы они не прыгали, — заменим их сразу на значения.

Что я сейчас делаю, смотрите: я выбрал весь диапазон и нажал Copy.

А теперь возвращаюсь в левый верхний угол: Edit → Paste special → Values only. Он убрал формулы и оставил только текущие значения — чтобы они больше не прыгали. И сделаем в рамочку всё это дело (добавим дизайна, так сказать). Меня всё время раздражают эти гридлайны. Их можно убрать: View → Gridlines → снимаем галочку. Вот теперь у нас всё красиво. Можем добавить цвета — вот так и здесь так. Здесь тоже сделаем карточку. Это у нас будут недельные показатели. Давайте пока ограничимся недельными, а потом можно будет отдельно добавить итоговые. Мы хотим не просто смотреть на сами абсолютные значения. Мы с вами обсуждали, что в качестве метрик гораздо лучше смотреть на относительные значения, на рейшн, потому что они позволяют больше данных запаковать в одну цифру. Теперь это сюда скопируем и сделаем KPI.

Какие показатели мы хотим смотреть? Вот у нас есть расходы и регистрации. Давайте пойдём подряд — расходы и выручка. Можно посмотреть отношение расходов к выручке: условно назовём его ROI. На самом деле это не совсем ROI, но для нашей задачи сейчас этого хватит. Чтобы его посчитать, мы делаем — «выручка» делить на «расходы». Это у нас будет в процентах, вот 5%. Что у нас ещё есть? У нас есть «расходы» и «регистрации». Мы можем поделить «расходы» на «регистрации», чтобы посчитать стоимость привлечения регистрации. Есть. У нас есть покупатели, и мы можем посчитать стоимость покупателя: «регистрации» делим на «новых покупателей». И последнее, давайте сделаем конверсию в новых покупателей: мы делим «регистрации» на «покупателей» (и всё это в процентах). Да, у нас покупателей гораздо больше, чем регистраций, — поэтому такая огромная конверсия. Но пока нам не важно. Теперь размножим все эти формулы на весь дизайн. Если мы сейчас просто скопируем и вставим — он вставит не только формулы, но и форматирование. На самом деле нам окей, просто сломается одна штука, которую мы сейчас починим.

Цифры и таблички — это хорошо, но нам бы посмотреть как‑то графически — как меняется значение. Для этого отлично подходят графики, и у нас в дашборде есть особый график — называется спарклайн. Это график, который помещается в одну ячейку. Его преимущество перед огромными графиками в том, что он компактный, и в одной таблице их может быть много. Его недостаток перед стандартными графиками — в том, что у него, например, нет осей и нет подписей элементов. Но на моей практике это суперудобная штука, чтобы смотреть динамику. Давайте добавим. Добавляем один столбец. В экселе спарклайн — это отдельный график.

Вы идёте во «вставка», «график», и там есть «тип графика». В гугль‑таблицах спарклайн — это формула. Мы пишем в строке f(x): = sparkline, и он меня спрашивает: «По каким данным мне строить спарклайн?» Мы хотим по вот этим данным (выделяем строку). Здесь есть подсказки: какие‑то параметры просто обведены, а какие‑то — заключены в квадратные скобочки. Те, что в квадратных скобочках [options], — это необязательные, и их можно не вводить. Дзыньк — вот у меня построился спарклайн, классно. Мы можем взять его и скопировать на все строки. Но линия здесь плохо подходит. Все данные бывают дискретными или непрерывными. Вот, например, расходы по каждому месяцу — это дискретные данные. То есть у нас есть ровно за один месяц расходы и расходы за другой, а между ними — пустота, ничего больше там не бывает. А если вы посмотрите на график, то у вас значение как бы перетекает из одного в другое, и создаётся впечатление, будто между ними есть какое‑то промежуточное значение. Это обман, и так делать плохо. Поэтому дискретные значения чаще используют в барчартах (столбчатые диаграммы): они явно показывают, что есть одно значение, второе значение и нет никаких промежуточных значений.

Можем вернуться и вместо спарклайна показать Options — цвет, размер и тип спарклайна. Можем прямо эту подсказку скопировать и вставить. У нас здесь есть: чарттайп, бар, макс 50. То есть они идут подряд: тип графика, горизонтальные столбцы и максимальное значение 50 (нам это не нужно, удаляем его). Теперь посмотрим, как это выглядит, дзыньк — выглядит как‑то совсем не так, как нужно. Почему? Потому что нужен не bar chart, а column chart, то есть колонки. Копируем, вставляем. Чтобы не мучиться с форматированием в этот раз, сделаем вот так: Edit → Paste special → Paste → Formula only. То есть «вставь только формулы, а форматирование оставь себе». Тут же можем менять цвет. То есть здесь просто какой‑то рандомный оранжевый, и он убирается. Можете выбрать любой цвет, который вам нравится. Готово: Edit → Paste special → Formula only. Аналогично можем скопировать и вставить вот сюда (в таблицу ниже). Тут у нас KPI. Обратите внимание, что как раз KPI в дашборде — линейные. Я, например, специально принял решение делать его линиями, потому что KPI — они типа перетекают из одного в другой.

То есть у вас действительно есть конверсия одна, и она становится другой. Но на самом деле это спорное решение, и вы можете использовать и так и сяк. И вообще, гугль‑док — он вам расскажет.

Чтобы продемонстрировать возможности — давайте уберём Chart type и оставим линии (это формат по умолчанию): Copy → Paste → Edit → Paste → Paste only. Готово. И на самом деле, если вы посмотрите, — у нас здесь есть потом ещё большие графики, и мы тоже можем построить большие графики. Но не будем пока этого делать: у нас куча работы, а время заканчивается.

Процесс

Окей, у нас есть первый набросок дизайна, и теперь мы лучше понимаем, какие нам данные нужны. Давайте прям запишем в файлик. Чтобы оживить всю эту штуку и чтобы тут появились реальные данные — нам нужны следующие показатели (мы их разделим на метрики и измерения). Метрики — это то, что мы считаем; измерения — это то, в каких разрезах мы это считаем. Итак, метрики, которые нужны: расходы, выручка, количество регистраций и количество новых покупателей. Измерения — это рекламный канал и номер недели. Теперь ваша задача — пойти и получить эти данные. Оставим за скобками, как конкретно вы это сделаете. Если у вас есть доступ к базе и вы умеете с ней работать, то добро пожаловать в эскуэль: пишете запрос и получаете. Если у вас доступа к базе нет и вы запрос получить не можете — вы идёте к разработчикам и говорите: «Достань мне, пожалуйста, вот такие показатели в таких‑то разрезах». Чтобы вам было проще и чтобы не тратить время — возьмём данные отсюда (просто скопируем и вставим). Потом, если вы пойдёте к разработчикам, — вы можете вот это всё использовать как пример. Переименуем этот лист как «Дашборд», добавим ещё один лист (назовём его «Данные: выручка») и вставим. У нас здесь сверху куча ошибок, так что пока уберём это и вернёмся чуть позже. Идём в Costs, добавим ещё один лист, «Данные: расходы», и вставим данные. Давайте посмотрим ещё раз — что у нас здесь есть. Есть столбец № 1 (CW). Это календарная неделя. Она в формате год, дефис, номер недели этого года. Дальше у нас есть Source: это название рекламного канала. И есть Costs — сколько денег мы потратили на этот рекламный канал. Если я не ошибаюсь, то здесь доллар. Это не принципиально для дизайна, но когда будете делать свой дашборд — учитывайте, какая у вас там валюта (чтобы вы доллары на рубли не делили). Каждая строчка — это сколько денег мы потратили на рекламный канал за такую‑то неделю. За 52 неделю 2010 года мы потратили столько‑то денег на гугль‑дисплей. За эту же неделю на гугль‑сёрч мы потратили столько‑то денег. И так далее — вот они все идут, за какую‑то историю все наши расходы. Что у нас в выручке? У нас тоже есть номер недели, название источника, количество регистраций Accounts, количество покупателей и выручка. За эту неделю из этого источника пришло столько‑то регистраций, 57 покупателей и такая‑то выручка. Только смотрите, какая интересная штука здесь есть. Я не рекомендую вам так делать: это была наша ошибка; я вам сразу скажу, в чём она заключается, и вы можете сразу её исправить (уже на этапе запроса к программисту). У нас есть неделя, и мы её считали как неделю со старта проекта. То есть условно, в момент, когда мы начали делать аналитику, было 40 недель. И так получилось, что он как раз запустился в январе (или ещё что‑то).

В общем, первая неделя «Дарба» и первая неделя года — совпадали. Мы вот идём‑идём дальше вниз: вот 51, 52, а потом... 53, 54 и т. д. А в году их всего 52 (это порядковый номер). Вот дошли до 62 недели существования «Дарба» или «Групона». Но мы‑то хотим по годам делать, и нам нужно вот эту штуку перевести в формат «год — номер недели». Нам нужно, чтобы 53‑я неделя здесь — стала первой неделей следующего года. Показываю, как это сделать. Вот тут как раз играют дополнительные служебные листы, которые я вам показывал чуть раньше. Создадим лист, назовём его «Недели» и на этом листе сделаем соответствие: какой порядковый номер недели соответствует какой календарной неделе. Назовём столбцы так: Неделя и CW. У нас выручка начинается с 40 недели, и они идут до 65 [это первый столбец]. А здесь [во втором] мы запишем соответствие. 40 неделя — это сороковая неделя 2010 года (старые данные, но это не важно). Мы сделаем такую штуку: concatenate 2010, 40. Что это такое? Формула concatenate — это название операции, concatenate, это склеивание двух текстовых строк в одну. То есть мы говорим: «Склей строку 2010 — дефис и значение ячейки А2». У нас получилось соответствие 40 — 2010–40. Теперь скопируем эту ячейку вот сюда и получаем в конце 65 неделю 2010 года, которой просто нет. Нам нужно, начиная с 53, сделать, что на самом деле это 2011, во‑первых, а во‑вторых — А2–52. Вот так: 2011–1, 2011–2, 2011–3... 2011–13. Только мне не нравится, что на кодировку недели № 40 уходит два символа, после 53 один. Лучше бы нам подставить сюда нолик — чтобы было 01, 02, 03. Есть несколько способов этого добиться. Я вам покажу самый топорный, но он и самый быстрый (чтобы не тратить много времени). Мы добавим одно промежуточное значение, дзыньк (Insert 1 left), и назовём его пока X, и будем вычитать из первого 52. Так, уберём отрицательные значения. Вот, начиная с 52 недели у нас есть разница между порядковым номером недели и пятьюдесятью двумя (где 52 — это количество недель в году). Мы можем добавить условие, что если вот это значение меньше 10, то добавь к нему ноль. Делается таким образом: добавим ещё столбец [справа], используем формулу: if вот эта штука меньше 10, то верни concatenate нолик, запятая, значение, else верни значение. Вот. Что здесь произошло? Смотрите, формула if — это как условие, она работает так, что вы в неё передаёте какое‑то значение, и дальше она говорит: «Если это значение истинно, то возвращаю такое‑то (которое вы сказали), а если ложно, то верну вот это». В данном случае условие, которое мы задали, — это В14 меньше 10 именно в этой ячейке. Значение B14 меньше 10? В14 равно нулю, значит, меньше 10. Тогда верни (concatenate) ноль, В14. Про concatenate я уже рассказывал: она склеивает два текстовых значения, и поэтому мы взяли текстовый нолик, и склеили со значением ячейки В14 (тоже нолик), и получили ноль‑ноль. Аналогично, здесь — 01, 02, 03. Получили такую штуку. Дальше у нас идёт «если В14 меньше 10...», а В14 не меньше 10 (10 = 10), то «верни значение на В24» — и он просто вернул 10. Да, значение 00 не нужно, и начиная с 53 мы можем ввести в формулу не А52, а С15 (=01). Назовём эти столбцы Z1 и Z2, не важно. Это топорное решение я делал второпях, и можно его оформить лучше — чтобы везде были соответствующие значения, — но нам сейчас оно подойдёт.

Зачем мы всё это делали? Возвращаемся к выручке, где у нас порядковые значения. Мы хотим превратить эти порядковые значения в календарные недели. Как это сделать? Добавляем столбец, называем его CW (календарная неделя) и вспоминаем формулу VLOOKUP. Нам нужно взять значение «номер недели» и подставить соответствующее значение календарной недели, которое нам известно (которые мы сделали в столбце на листе «Недели»). Пишем в ячейке: =VLOOKUP. Что мы будем искать? Мы будем искать значение недели (кликаем на В2), запятая. Где будем искать? На листе «Недели»: вот она, наша таблица. Мы просто выделили мышкой, а он подписал название листа («Недели»), восклицательный знак (значит, название листа закончилось) и столбцы с А по D, запятая. Теперь какой номер столбца нам нужно вывести? Мы хотим из календарной недели 4‑й: четыре, запятая, False, равно. И вот он нам вывел нашу календарную неделю. Теперь мы можем скопировать и вставить её на весь столбец и получить нужный нам результат.

Покажу вам ещё один классный способ размножать значения. Способ «выйти» — это небольшой квадратик в правом нижнем углу: если вы наведёте, то у вас появится крест. Вы можете его зажать и протащить значение вниз — и оно размножится. Это само по себе удобно, но так тащить на весь столбик — очень долго. Поэтому вы можете два раза навести и протянуть. Формула автоматически размножилась, и всё у вас ровно до того момента, где у вас заканчиваются данные. Вот, обратите внимание, кстати: 62 неделя превратилась в 10 неделю 2011 года (то, что нам было нужно).

И вот данные мы перенесли. Повторим, что у нас получилось. У нас есть данные о расходах (сколько мы потратили на каждый рекламный канал за каждую неделю) и данные о доходах (сколько с каждого рекламного канала за каждый день пришло регистраций, покупателей и выручки). То есть насколько хорошо сработала та или иная рекламная кампания. Следующий шаг — это построить сводные таблицы и посчитать нужные нам показатели в нужном нам виде.


Сводные таблицы
Для того чтобы всё это посчитать, используются сводные таблицы. Чтобы создать сводную таблицу, как я уже показывал, — это значит просто выделить нужные нам данные, кликнуть по Data и Pivot Table. Автоматически создастся новый лист (назовём его «Сводные таблицы»), и можем начать играться с данными. Вот тут полезно посмотреть на тот дашборд, который мы задизайнили. Что мы видим? У нас данные по неделям идут горизонтально. Нам было бы удобно переносить данные по неделям из сводных таблиц в дашборды — если бы они тоже были горизонтально. Поэтому мы можем номера недель по умолчанию ставить в столбцы. Можем, кстати, выбрать «недели» или «календарные недели». Но так как нам важно сопоставить расходы с доходами (а там расходы у нас в календарной неделе), то будем использовать тут «календарные недели», CW. Пожалуйста, вот вам все недели. Кстати говоря, вопрос: почему даты в столбцы, а не в строки? Вот так кажется более логичным. И даже следующий шаг, к которому я уже подошёл, — мы хотим не только по неделям, но и по каналам, — здесь мы можем выбрать, например, Source, и — вуаля — мы видим всю нашу выручку по каналам по неделям. Ответ в том, что как раз играет такая маленькая мысль о том, как этот дашборд будет себя вести в будущем. Так как он будет обновляться и мы будем туда добавлять новые данные. Конечно, у нас могут добавиться со временем и новые источники, и новые недели. Новые недели будут добавляться постоянно, их количество будет постоянно увеличиваться. Новые источники будут добавляться нечасто. Для того чтобы создать все вычисления, нам нужно использовать несколько сводных таблиц. Например, у нас будет одна сводная таблица с доходами, вторая сводная таблица с расходами и т. д. И если у нас будут добавляться новые недели вертикально — уже через две недели они наедут вот сюда. Получается, что для каждой недели нужно будет добавлять новую строку — что не прикольно. Поэтому я тогда решил (и считаю, что так гораздо удобнее), что у нас недели будут в столбцах, и они просто будут каждую неделю уезжать вправо, и они никогда никому не помешают. Поэтому недели — в колонках, а источники — в строках.

Вот мы сейчас показываем выручку. Нам помимо выручки нужно показывать ещё кучу всяких параметров. А конкретно — нам нужно количество регистраций, новые покупатели и расходы. Расходы у нас будут в другой сводной таблице — потому что данные в другом месте. Но вот эти все будут из одной. Мы, в принципе, можем, конечно, показать все показатели прямо здесь. Мы можем добавить сюда количество покупателей, количество регистраций, и вот они у нас все здесь есть. В столбцах это, конечно, полный адище — поэтому перенесём в строки. И вот они у нас, все показатели, по каждому каналу. Но я заметил, что так неудобно, потому что дальше, когда мы будем работать с формулой — оживлять дашборд (то есть переносить формулы), — это будет делать супернеудобно, и вы увидите позже, почему. Потому что у нас будет ломаться VLOOKUP. Поэтому я рекомендую делать одну сводную таблицу на один показатель. То есть вот у нас есть один показатель «Выручка» (можем, кстати, его подписать и сделать каким‑нибудь заметным цветом). И можем скопировать несколько раз: здесь будут «Регистрации», а здесь у нас будут «Покупатели». Меняем значение с выручки на количество аккаунтов. У нас везде эти ужасные нули есть, давайте от них избавимся. Поэтому Add filter, добавим любое поле. Хотя нет, важно, какое поле добавлять, — объясню почему. Вот мы сейчас добавили здесь календарные недели. Если мы уберём здесь Blanks, то всё как бы сработает нормально, но теперь получаются фиксированные фильтры. То есть когда мы добавляем новую неделю — нужно будет галочку ставить (что есть фигня). Давайте мы лучше поставим другое поле, например, Revenue. Нет, тоже плохо, давайте брать всё‑таки Source. Это приведёт к тому, что если появится новый рекламный источник, то нужно будет его добавлять руками. Но это редкий случай.

Так, у нас появились расчёты. То есть, в принципе, уже здесь можно смотреть, как себя ведёт каждый рекламный канал, и видеть, сколько чего у нас пришло. Но почему мы выделяем отдельно представление о сводных таблицах? Потому что, во‑первых, тут слишком много всего: тут все недели с начала времён, и их количество будет постоянно увеличиваться. Вам как директору по маркетингу (который постоянно с этим работает), может быть, и так сойдёт. Но генеральному директору — который эти данные видит редко, и он не помнит, что здесь, — придётся разбираться. Это отстой, и так быть не должно. Поэтому будем переносить всё сюда. А давайте прям сразу возьмём и перенесём? Пока мы не прикасались к расходам, а выручку, регистрации и новых покупателей перенесём вот сюда — то есть оживим непосредственно дашборд. Вот тут начинается самая магия и самый кайф формулы VLOOKUP. Смотрите, что нам нужно сделать. Да, кстати, маленькая ремарка. Видите, у нас тут куча всяких недель? Здесь они пронумерованы от 1 до 8, и это ни о чём не говорит (у нас же формат вот такой, год — месяц). Давайте скопируем последние восемь и вставим вот сюда: Edit → Paste special → Values only (мы хотим, чтобы у нас была вот такая неделя). Давайте сделаем так, чтобы выручка читалась у нас уже правильно — чтобы в дашборде выводилось не какое‑то рандомное значение, а значение непосредственно по «Вконтакте» за неделю 2010. Как это сделать? Можно догадаться, что для этого будет использоваться VLOOKUP. Теперь смотрите, что мы ищем: search key будем искать. Так как у нас в сводных таблицах в первом столбце стоит название источника, то и искать мы будем название источника: search key Vkontakte. Он у нас уже есть в этой ячейке, и именно его мы будем менять, и он будет подставляться во все формулы. Дальше. Где мы будем искать? Мы ищем выручку — значит, мы будем искать в сводной таблице с выручкой. Дальше нам нужно будет указать номер столбца, соответствующего нашей неделе. У нас неделя 2011–10, а номер столбца хз. Поставим пока единичку и чуть позже с этим разберёмся. И в конце ставим false (а то всё взорвётся). Мы видим N/A, почему? Потому что не может найти значение «Vkontake». Вот какая фигня: у меня здесь введено неправильное значение, и он не может найти. Исправляю, и он вывел мне первый столбец сводной таблицы — это, собственно, «Вконтакте». Теперь давайте сделаем, чтобы выводилось нужное нам значение. Для этого нам нужно знать номер столбца 2011–10. Давайте посчитаем, какой у него номер. К сожалению, в VLOOKUP нужно указывать именно порядковый номер (число), а не название. Так сделано потому, что сам диапазон, в котором вы ищете, — он может начинаться вовсе не с начала таблицы, и он может сдвигаться вместе с формулой.

То есть указывать нужно относительно. Давайте посчитаем. Вот, кстати, ещё одна клёвая функция Экселя и гугль‑таблиц: я ввёл единичку, ввёл двойку, и теперь Эксель сам поймёт, что мне нужно продолжить, и он меня доведёт до 24 (размножит дальше).

Получается, что для того, чтобы вывести выручку «Вконтакте» за 2011–10 неделю, — мне нужен 24 столбец. И мы можем здесь [в формуле VLOOKUP], например, ввести 24. Вот она, наша выручка. Что мне не нравится? То, что 24 нужно вводить руками в формулу. Это не прикольно, и я предлагаю сделать так. Вывести 24 вот сюда, и, соответственно, вот здесь [слева] будет 24−1: для того чтобы получить предыдущую неделю, мы просто вычтем из 24 единичку. Вот, получаются нужные нам недели. А теперь жёсткое значение, которое мы с вами ввели, мы изменим на адрес ячейки, в которой находится это значение, — и тут ничего не изменится. Но теперь мы можем просто скопировать эту формулу и перенести вот сюда: нажать Edit → Paste special → Formula only. Мы ожидаем получить, что у нас всё пересчитается и каждая формула будет доставать значение из соответствующего столбца. Кликаем. Упс, всё сломалось. Что сломалось — проверяем. Видите, две ошибки: одно значение не найдено, а дальше вообще какой‑то ад. Проверяем — что пошло не так. Кликаем на формуле: он взял 23 откуда нужно, но вот что сломалось — «Вконтакте» ищется теперь из этого значения (пустая ячейка слева). Как исправить? Зафиксировать, используя абсолютную адресацию, — нам не нужно, чтобы это куда‑то вообще съезжало. Повторяем весь процесс (Edit → Paste specials → Formula only). Вуаля — вот наша выручка «Вконтакте». Да, похоже на правду, и видите — график тоже сам пересчитался. И если мы сейчас поменяем на Фейсбук, то всё сломается — потому что Фейсбука там нет. Но до этого мы доберёмся чуть позже. Так, значит, всё сломалось. Ладно, к этому мы вернёмся.

Давайте, прежде чем мы вернёмся к починке вот этой штуки, — посчитаем точно так же количество регистраций и количество новых покупателей. Я обычно просто копирую формулу и вставляю её вот сюда. Если я копирую не ячейку целиком, а просто формулу, то в формуле ничего не меняется — то есть в этом случае адресация не перескакивает. Для того чтобы нам вместо выручки начать считать регистрации, единственное, что нам нужно поменять, — это таблица. Нам всё ещё нужен столбец под номером 24, но нам нужна другая таблица. Вот это [сводную таблицу] мы сотрём и заменим. Где у нас «Регистрации»? Вот они — дзыньк, дзыньк. Вот у нас количество регистраций. Здесь делаем то же самое про покупателей: меняем номера строк, копируем и вставляем [в формулу]. Кайф. Немаленькие значения, но ничего не сломалось вроде, и эти все штуки, соответственно, тоже пересчитались. У нас ROI, естественно, миллиарды процентов — потому что мы ещё не делали расходы. Давайте сделаем расходы. Для того чтобы сделать расходы, нам нужно повторить весь этот процесс ещё раз. И единственное, что, — смотрите. У нас сводная таблица делается на основе данных по выручке, а мы хотим, чтобы она делалась на основе данных по расходам. Чтобы это изменить, кликаем на Edit range, стираем всё к хренам, идём в «Расходы» и выбираем исходные данные по расходам. Всё сломалось. Почему всё сломалось — потому что в Values были значения количества покупателей, а в табличке с расходами Costs такого значения нет. Поэтому берём — и кликаем по Сosts. Вот, какая‑то фигня произошла, такое бывает. Разбираемся, что за фигня произошла. У нас явно что‑то не то в строках. Ах, да, в строках у нас Сosts. То есть видите — значения метрик можно вставлять и сюда, но получается ерунда. Удаляем. Мы хотим, чтобы здесь было «Source». Вуаля, готово. Теперь убираем ненужные ролики, и вот у нас есть сводная таблица по костам. Но есть одна штука. Смотрите, у нас здесь меньше рекламных каналов, чем здесь. С этим нужно что‑то делать. Например, выбрать рекламный канал Site, и по нему... костов не будет. Мы это исправим, но чуть позже. Давайте сначала повторим ту же самую операцию с VLOOKUP. Копируем данные вот сюда, меняем сводные таблицы вот так [выделяем строки с покупателями], меняем окраску на чёрную, копируем, вставляем, Edit → Paste special → Only, готово. Почему‑то пустота какая‑то здесь. Почему пустота? Может, я что‑то не покрасил... Так, ладно, будем сейчас разбираться. Мы перенесли формулы, но у нас идёт какой‑то непонятный разнобой. Всё это связано с тем, что у нас неправильные были данные валидации. Узнаем, что мы сделали здесь: мы здесь пошли Data validation и ввели просто какие‑то рандомные значения — просто чтобы показать, как это всё работает. Это не совсем то, что нам нужно. Объясню, почему. Мы можем в этот список просто взять и вписать все значения отсюда [покупатели] — и всё должно заработать. Но если у нас вдруг в какой‑то момент появится новый рекламный источник, который будет в дашборде, — его нужно будет не забыть в эту валидацию вписать. А я вам гарантирую, что вы точно об этом забудете и вспомните только потом, когда уже всем пошлёте и на вас все начнут кричать. Это нехорошо, но, слава богу, мы можем этого избежать. Мы можем пойти в Data validation, и изменить лист Items на List from range (то, о чём мы говорили раньше), и выбрать Range, из которого будем забирать источники, и этот Range будет непосредственно вот эта сводная таблица. И если в сводной таблице что‑то изменится, то в вашем рендже тоже всё изменится.

Кликаем OK, OK, возвращаемся в дашборды, и, вуаля, теперь мы можем выбрать что угодно — в том числе и Total (сводная по всем каналам). Давайте посмотрим на «Вконтакте»: всё ещё какие‑то конские огромные значения. Вот тут, кстати, мне не нравится, что у нас не доллары. Давайте сделаем её в долларах. И так как значения маленькие, то можно добавить десятичное значение. Дашборд, в принципе, готов. Что мы можем сделать? Мы можем убрать рекламные каналы. О, вот что я забыл! У нас же не по всем рекламным каналам есть расходы. Вот мы выбрали Site (это органика), и у нас нет расходов — поэтому здесь N/A, и всё сломалось, и теперь дашборд неюзабельный. Это не прикольно, давайте исправим. Для того чтобы исправить, мы используем формулу if error (помните, я её вам показывал?). В случае, если у нас возникает ошибка, — вы видите пустоту, или нолик, или дефис. Дефис хорошо смотрится. Давайте попробуем так: if error, скобки открываются, value (тут мы ничего не меняем — это формула, которую он будет проверять), запятая, «что выводить, если ошибка», можно сделать под дефис. Дзыньк, и у нас красивый дефис. Формула не меняется, только сделаем её по правому краю. То есть мы как бы показываем, что здесь ничего нет. Скопируем‑вставим это вот сюда: Edit → Paste replace → Only, вуаля. Тут уже что‑то (только всё по правому краю).

В спарклайн сделаем аналогично: if error, запятая... И тут сделаем аналогично. Кстати, вот эту штуку можно скопировать сюда. Вообще — мне надоело делать вставку. Единственное, почему мы мучаемся со вставкой формулы, — это чтобы не слетали границы (рамки). Уберём пока рамки и сделаем copy‑paste. Ничего не изменилось, но теперь — если тут будет ошибка и выведется дефис... И, более того, даже можем сделать так (только убрать [chart type]) — и тоже всё заработает. Тут тоже if error... и тут тоже... и даже тут. Если вам не нравится дефис — вводите что хотите: пустоту, нолик, смайлик. Copy‑paste, и давайте тут тоже так же сделаем. Помните, у меня как раз в каком‑то из мест внезапно выводились ошибки? Это потому, что я где‑то заменил if error, а где‑то — забыл. Когда я менял — там ошибок не было. И вернём рамочки раз, рамочки два. И — всё. Ах, да, мне на самом деле не нравится 23 — оно что значит? Почему оно здесь, почему не пересчитывается?

Новый человек не знает, что такое номер недели. Поэтому давайте сделаем последний штрих — чтобы вот эта штука подставлялась вот этой. То есть вот здесь [2011–10] было бы Edit, а вот эти все вещи [предыдущие недели] пересчитывались бы автоматически — в зависимости от того, что человек введёт. Сделаем так: «Введите неделю» и... [Закрашивает ячейку 2011–10 и соседнюю справа.] Для того, чтобы это сделать, — нам понадобится та табличка недели, с которой мы уже работали. Мы можем добавить ещё один столбец: вот здесь у нас будет календарная неделя (CW), а здесь — тот самый номер соответствия (назовём его Z3). Никогда не называйте поля так, чтобы вы потом не могли понять, почему они так называются. Просто мне сейчас лень выдумывать говорящие названия. Вы так не делайте, вы делайте хорошо.

Так, дашборд. У нас тут есть неделя 2011–10, про которую мы знаем, что она номер 23. Соответственно, эта [предыдущая] будет минус один, дзыньк‑дзыньк. Всё, у вас есть 26 недель. А теперь, внимание, мы сделаем такую штуку. Идём в дашборд, и ещё раз: у нас человек будет вводить вот это значение [2011–10]. А вот эта штука должна посчитаться в зависимости от того, что он введёт. Возвращаемся к формуле VLOOKUP. Что мы будем искать? Вот это значение, К2. Где мы будем его искать? В этой таблице. Но так как VLOOKUP ищет только в первом столбце таблицы — если мы выделим её всю, то он будет искать вот здесь и никогда не найдёт. Мы выделим только вот эту её часть. Какой столбик нам нужно выбрать? Второй. Теперь мы заменим 2011–10 на 2011–8, и всё сломается. Почему? Что ты не можешь найти? Окей, рассказываю, что произошло. Гугль перехитрил меня — он понял, что 2011–08 — это дата, и подставил первое августа 2011 года, следом отформатировал эту дату как 2011–08. Соответственно, VLOOKUP теперь ищет значение даты. Дата в гугль‑таблицах хранится как «количество дней со дня X — с 1 января 1910 года (или 1900 года, не помню). Естественно, он эту дату не находит. Есть два способа решить такую штуку. Во‑первых, перевести всё это в даты и искать дату плюс поменять таблицу. Это долго, и я использую другой способ. Я фиксирую, чтобы вот это значение 2011–08 было 100% текстом. Ставлю апостроф 2011–08, и всё заработало. Почти. Мы хотим, чтобы здесь было 2011–07, потом 2011–06 и т. д. То есть мы хотим, чтобы предыдущие показывались. Кстати, я не знаю, сработает? Попробуем вычесть один. Нет, не сработало. Потому что он так же привёл к дате и вычел из даты один день. Это не то, что нам нужно, — нам нужен соответствующий месяц. Но теперь мы можем сделать VLOOKUP наоборот. Мы можем ему сказать: «А найди‑ка мне месяц, соответствующий вот этому порядковому номеру». То есть «найди 20 и верни вот этот месяц». Единственная проблема в том, что VLOOKUP не умеет вычитать — доставать минус в первый столбец, а умеет вычитать только вправо. Соответственно, мы можем сделать вот такой хак. Так у вас будет и первая, и вторая. Мы сделаем просто равно D2. И здесь — =VLOOKUP, 20. Где будем искать? Уже вот в этих столбцах. И второе, вуаля. А теперь уже вот эту штуку берём и копируем вот сюда. У нас всё ломается почему? Потому что у нас съезжают столбцы. Мы не хотим, чтобы они съезжали, — поэтому мы их фиксируем. Когда мы выводим только столбцы — мы можем тоже их зафиксировать. И копируем ещё раз. И теперь всё считается, только эта штука теперь лишняя. Так, пробуем. Прямо боевая штука. Вводим, например, какой‑нибудь «Вконтакте» и меняем 2001–08 на 2011–6. Всё пересчиталось, смотрим: тут 72 тысячи. 2011–07 — и вот они, 72 тысячи. Основное мясо отчёта готово. Можно добавлять графики, можно добавлять другие метрики — это всё дело техники. Главное — у вас есть три основных модуля: данные, вычисления и сам дашборд. Они разделены, они связаны с помощью VLOOKUP. Вы можете спокойно добавлять новые данные — у вас всё будет работать. На самом деле это всё, что я хотел вам сказать по этому поводу. У вас [на следующей неделе] задание будет: повторить эти шаги и сделать, собственно, дашборд. У вас есть два варианта: либо работаете с данными, которые мы вам предоставим (с ними можно сделать полностью такой же дашборд), либо пробуйте его изменить каким‑то образом. Либо — если чувствуете в себе силы и задор — возьмите собственные данные из какого‑либо бизнеса или интернет‑магазина, подумайте, какие метрики кому нужно показывать, — и сделайте свой собственный работающий дашборд. Посмотрим, что у вас получится. На этом всё. Спасибо. Пока.