Редакция Spark.ru: Строим дашборд в Excel на базе Power Query и Power Pivot

Основатель «Школы траблшутеров» Олег Брагинский делится секретами эффективных приборных панелей для бизнеса. С выходом интервью «Исповедь гения эффективности» и запуском продвижения в Сети в 2015 году, мы с партнёром Школы траблшутеров Даниилом Шмиттом озаботились планированием предстоящих активностей и фиксацией количественных результатов. Уже шесть лет ведём Excel таблицу, в которую бережно заносим показатели вышедших статей, аудио и видео. К 30 ноября 2020 года скопилось 3’500 увидевших свет материалов, опубликованных на наших и внешних ресурсах. Подобное количество виделось недостижимым. Обычай подбивать результаты оказался не только полезным: систематизировали публикации, построили тематический план, изучили показатели; но и дальновидным: десятки следов в Интернете пропали вместе со статистикой и откликами из-за закрытия каналов и сайтов. На листе «Навыки» (Рис. 1, A) сделали тематическую классификацию и подбили вхождение в книги, «Покрытие» (B) отвели под публикации и статистику «реакций», вкладка «Сборники» © помогла распределить статьи по аудиоальбомам. Рис. 1. Лист «Навыки» до оптимизации Выборочность распределения ссылок по листам заставила отмести функции ВПР и ИНДЕКС. В результате, ручным дублированием строк создали ненужную работу и наплодили оплошностей. Файл разросся настолько, что еженедельное обновление стало отнимать больше часа. Трудоёмкость отслеживания многочисленных показателей раздражала. На подмогу пришли надстройки Power Pivot и Power Query, ставшие частью Excel с версии 2016 года. Начали с нехитрого: спасались от задвоения и снижали долю ручных операций. С помощью «Антисоединения слева» в Power Query выявили полсотни накопленных огрехов: несоответствие наименований, различие ссылок, отсутствие материалов. Восстановление пропущенного радовало: затыкались бреши незаконченных сборников, снижая плановый объём работ по «закрытию» тем. После чистки избавились от листа «Навыки», загрузив данные из PowerQuery в модель и создав сводную диаграмму на странице «Dashboard» (Рис. 2) Рис. 2. Распределение публикаций по предметам Стало ненужным проставлять единички на пересечении столбцов «Навык» и строк «Статья». Функциональность возросла благодаря полю отбора и сортировки по наименованию и количеству (D). Появились дополнительные фильтры в «Поля сводной диаграммы» (Рис. 3, E): Рис. 3. Отбор значений диаграммы распределения Лист «Покрытие» (Рис. 4) сделали кратким: добивались быстроты сортировок и фильтров — показатели обновляли макросами и прокручивали таблицу вниз для сверки достижения контрольных величин (F). Статистика была простенькой: без временных срезов, агрегированных значений и размера вклада переменных в результат. Рис. 4. Лист «Покрытие» до оптимизации Добавили столбцы: «Навык», «Код», «Сборник» (Рис. 5). Учитывая, что удалённый лист «Навыки» хранил 524 колонки с названиями умений, массу формул и предполагал «сортировку» перетягиванием столбцов, обмен оказался выгодным. Рис. 5. «Покрытие» после оптимизации Тем более, что Power Query мгновенно подгружает таблицу с добавленными строками и дозаполненными ячейками: автоматически при открытии файла или через вкладку «Данные» — «Обновить всё». Попутно синхронизируются модель в Power Pivot и сводные диаграммы. Стали выводить статистику просмотров и реакций на публикации в агрегированном виде и по конкретным Интернет-ресурсам. В Power Query сохранили колонку издания, удалили дубликаты, объединили с таблицей «Покрытие» с помощью «Внешнее левое соединение» (Рис. 6) ... Рис. 6. Расчёт показателей по изданиям в Power Query ...и загрузили на лист «Dashboard» таблицу агрегированных значений (Рис. 7): Рис. 7. Таблица агрегированных просмотров по изданиям При первом выводе данные выглядели неприглядно и малоинформативно: выбрали макет, задали стиль, маску чисел, условное форматирование. Добавили строки «KPI» и «Осталось», сделали сортировку, установили фильтр. Раз и навсегда: при обновлениях красота не слетает! Для определения выхлопа на одну публикацию рассчитали средние в Power Query добавлением настраиваемого столбца (Рис. 8): Рис. 8. Расчёт средних величин в Power Query Как и для суммарных значений вывели результаты и добавили форматирование (Рис. 9): Рис. 9. Таблица средних показателей по изданиям Расчёты в Power Query завершили за 15 минут, хотя подобные операции с помощью встроенных функций заняли бы гораздо больше времени, а создание сводных таблиц привело бы к значительному увеличению размера файла. Чтобы выявить перспективные издания, построили график интенсивности реакций и добавили линию тренда (Рис. 10). Выяснилось, что 11,5% Интернет-ресурсов в сумме дают 86,1% просмотров. В очередной раз убедились: пресловутое правило 80 на 20 не работает. Рис. 10. Доля просмотров по изданиям Визуализацию информации выполнили иерархическими диаграммами совокупного интереса аудитории к Интернет-ресурсам и YouTube-передачам (Рис. 11): Рис. 11. Дерево вклада просмотров топ-15 изданий и видеопрограмм Больше всего интересовал канал «Олег Брагинский»: на нём выпускали максимальное число публикаций в неделю, проводили постоянные эксперименты. Для отслеживания изменений и тренда, без захода в творческую студию ресурса, в Power Pivot построили сопоставление Like for Like (LFL), используя меру с PARALLELPERIOD со сдвигом в месяц. Так как функция работает с непрерывным набором дат, в Power Pivot создали таблицу «Календарь» и построили связь один ко многим (Рис. 12, G): Рис. 12. Календарь и диаграмма связей в Power Pivot Отняли из просмотров текущего месяца значения предыдущего и рассчитали процентную долю интервалов периода. Построили комбинированную диаграмму, где месячное изменение отобразили гистограммой с группировкой, а долю просмотров — графиком с трендом (Рис. 13): Рис. 13. Помесячное изменение просмотров и интервальный вклад Поверх представления разместили фильтр «Временная шкала» (H), чтобы выбирать диапазон, не раскрывая лишних окон. Несмотря на «убыточные» месяцы, динамика оказалась положительной: после начала экспериментов наметился стабильный рост просмотров. Вернулись к подсчёту аудио, видео, статей и их распределению по книгам и аудиосборникам: использовали построенную в Power Pivot модель на основе таблиц «Покрытие» и «Сборники». Создали меры (I) с нехитрой функцией COUNT (Рис. 14): Рис. 14. Распределение по сборникам и меры в Power Pivot ...и вывели семь сводных диаграмм (Рис. 15): Рис. 15. Визуализация распределений материалов по книгам и аудиоальбомам Собрали все представления на новом листе «Dashboard» (Рис. 16): Рис. 16. Финальная версия приборной панели Полное обновление по команде занимает полминуты, отдельные диаграммы/таблицы «освежаются» за пару секунд. Время добавления публикаций сократилось в 2,5 раза, снизилась вероятность ошибок, ключевые показатели удалось сгруппировать на один экран. Добавилось пять моделей в Power Pivot, девять запросов в Power Query, 11 диаграмм, две вычисляемые таблицы, лист «Справочник», а файл увеличился лишь на 1,4 MB — приемлемая цена экономии и наглядности. Ещё раз убедились, что Excel — инструмент «на все случаи жизни», а изучение надстроек — прикладной источник конкурентных преимуществ.

Источник: spark.ru