Power Query Excel Mac os Как Включить • Power view

Строим дашборд в Excel на базе Power Query и Power Pivot

Основатель «Школы траблшутеров» Олег Брагинский делится секретами эффективных приборных панелей для бизнеса.

С выходом интервью «Исповедь гения эффективности» и запуском продвижения в Сети в 2015 году, мы с партнёром Школы траблшутеров Даниилом Шмиттом озаботились планированием предстоящих активностей и фиксацией количественных результатов.

Уже шесть лет ведём Excel таблицу, в которую бережно заносим показатели вышедших статей, аудио и видео. К 30 ноября 2023 года скопилось 3’500 увидевших свет материалов, опубликованных на наших и внешних ресурсах. Подобное количество виделось недостижимым.

Обычай подбивать результаты оказался не только полезным: систематизировали публикации, построили тематический план, изучили показатели; но и дальновидным: десятки следов в Интернете пропали вместе со статистикой и откликами из-за закрытия каналов и сайтов.

На листе «Навыки» (Рис. 1, A) сделали тематическую классификацию и подбили вхождение в книги, «Покрытие» (B) отвели под публикации и статистику «реакций», вкладка «Сборники» © помогла распределить статьи по аудиоальбомам.

Power Query Excel Mac os Как Включить • Power view

Выборочность распределения ссылок по листам заставила отмести функции ВПР и ИНДЕКС. В результате, ручным дублированием строк создали ненужную работу и наплодили оплошностей. Файл разросся настолько, что еженедельное обновление стало отнимать больше часа.

Трудоёмкость отслеживания многочисленных показателей раздражала. На подмогу пришли надстройки Power Pivot и Power Query, ставшие частью Excel с версии 2016 года.

Начали с нехитрого: спасались от задвоения и снижали долю ручных операций. С помощью «Антисоединения слева» в Power Query выявили полсотни накопленных огрехов: несоответствие наименований, различие ссылок, отсутствие материалов.

Power Query Excel Mac os Как Включить • Power view

Восстановление пропущенного радовало: затыкались бреши незаконченных сборников, снижая плановый объём работ по «закрытию» тем. После чистки избавились от листа «Навыки», загрузив данные из PowerQuery в модель и создав сводную диаграмму на странице «Dashboard» (Рис. 2)

Power Query Excel Mac os Как Включить • Power view

Стало ненужным проставлять единички на пересечении столбцов «Навык» и строк «Статья». Функциональность возросла благодаря полю отбора и сортировки по наименованию и количеству (D). Появились дополнительные фильтры в «Поля сводной диаграммы» (Рис. 3, E):

Power Query Excel Mac os Как Включить • Power view

Лист «Покрытие» (Рис. 4) сделали кратким: добивались быстроты сортировок и фильтров — показатели обновляли макросами и прокручивали таблицу вниз для сверки достижения контрольных величин (F). Статистика была простенькой: без временных срезов, агрегированных значений и размера вклада переменных в результат.

Power Query Excel Mac os Как Включить • Power view

Добавили столбцы: «Навык», «Код», «Сборник» (Рис. 5). Учитывая, что удалённый лист «Навыки» хранил 524 колонки с названиями умений, массу формул и предполагал «сортировку» перетягиванием столбцов, обмен оказался выгодным.

Тем более, что Power Query мгновенно подгружает таблицу с добавленными строками и дозаполненными ячейками: автоматически при открытии файла или через вкладку «Данные» — «Обновить всё». Попутно синхронизируются модель в Power Pivot и сводные диаграммы.

Power Query Excel Mac os Как Включить • Power view

Стали выводить статистику просмотров и реакций на публикации в агрегированном виде и по конкретным Интернет-ресурсам. В Power Query сохранили колонку издания, удалили дубликаты, объединили с таблицей «Покрытие» с помощью «Внешнее левое соединение» (Рис. 6) .

Power Query Excel Mac os Как Включить • Power view

. и загрузили на лист «Dashboard» таблицу агрегированных значений (Рис. 7):

При первом выводе данные выглядели неприглядно и малоинформативно: выбрали макет, задали стиль, маску чисел, условное форматирование. Добавили строки «KPI» и «Осталось», сделали сортировку, установили фильтр. Раз и навсегда: при обновлениях красота не слетает!

Power Query Excel Mac os Как Включить • Power view

Для определения выхлопа на одну публикацию рассчитали средние в Power Query добавлением настраиваемого столбца (Рис. 8):

Power Query Excel Mac os Как Включить • Power view

Как и для суммарных значений вывели результаты и добавили форматирование (Рис. 9):

Расчёты в Power Query завершили за 15 минут, хотя подобные операции с помощью встроенных функций заняли бы гораздо больше времени, а создание сводных таблиц привело бы к значительному увеличению размера файла.

Power Query Excel Mac os Как Включить • Power view

Чтобы выявить перспективные издания, построили график интенсивности реакций и добавили линию тренда (Рис. 10). Выяснилось, что 11,5% Интернет-ресурсов в сумме дают 86,1% просмотров. В очередной раз убедились: пресловутое правило 80 на 20 не работает.

Power Query Excel Mac os Как Включить • Power view

Визуализацию информации выполнили иерархическими диаграммами совокупного интереса аудитории к Интернет-ресурсам и YouTube-передачам (Рис. 11):

Больше всего интересовал канал «Олег Брагинский»: на нём выпускали максимальное число публикаций в неделю, проводили постоянные эксперименты. Для отслеживания изменений и тренда, без захода в творческую студию ресурса, в Power Pivot построили сопоставление Like for Like (LFL), используя меру с PARALLELPERIOD со сдвигом в месяц.

Power Query Excel Mac os Как Включить • Power view

Так как функция работает с непрерывным набором дат, в Power Pivot создали таблицу «Календарь» и построили связь один ко многим (Рис. 12, G):

Power Query Excel Mac os Как Включить • Power view

Отняли из просмотров текущего месяца значения предыдущего и рассчитали процентную долю интервалов периода. Построили комбинированную диаграмму, где месячное изменение отобразили гистограммой с группировкой, а долю просмотров — графиком с трендом (Рис. 13):

Поверх представления разместили фильтр «Временная шкала» (H), чтобы выбирать диапазон, не раскрывая лишних окон. Несмотря на «убыточные» месяцы, динамика оказалась положительной: после начала экспериментов наметился стабильный рост просмотров.

Power Query Excel Mac os Как Включить • Power view

Вернулись к подсчёту аудио, видео, статей и их распределению по книгам и аудиосборникам: использовали построенную в Power Pivot модель на основе таблиц «Покрытие» и «Сборники». Создали меры (I) с нехитрой функцией COUNT (Рис. 14):

Power Query Excel Mac os Как Включить • Power view

. и вывели семь сводных диаграмм (Рис. 15):

Power Query Excel Mac os Как Включить • Power view

Собрали все представления на новом листе «Dashboard» (Рис. 16):

Полное обновление по команде занимает полминуты, отдельные диаграммы/таблицы «освежаются» за пару секунд. Время добавления публикаций сократилось в 2,5 раза, снизилась вероятность ошибок, ключевые показатели удалось сгруппировать на один экран.

Добавилось пять моделей в Power Pivot, девять запросов в Power Query, 11 диаграмм, две вычисляемые таблицы, лист «Справочник», а файл увеличился лишь на 1,4 MB — приемлемая цена экономии и наглядности.

Ещё раз убедились, что Excel — инструмент «на все случаи жизни», а изучение надстроек — прикладной источник конкурентных преимуществ.

Строим дашборд в Excel на базе Power Query и Power Pivot
Восстановление пропущенного радовало: затыкались бреши незаконченных сборников, снижая плановый объём работ по «закрытию» тем. После чистки избавились от листа «Навыки», загрузив данные из PowerQuery в модель и создав сводную диаграмму на странице «Dashboard» (Рис. 2)
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Расчёты в Power Query завершили за 15 минут, хотя подобные операции с помощью встроенных функций заняли бы гораздо больше времени, а создание сводных таблиц привело бы к значительному увеличению размера файла. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
По сути, в Excel можно было делать локальный OLAP-куб, без необходимости поднимать сервер и поддерживать его. Это и называлось «модель данных», а для совместного доступа ее можно было загрузить на SharePoint.
Power Query Excel Mac os Как Включить • Power view

Как включить power query в excel 2019

Однако, не желая загружать пользовательский интерфейс MS Excel 2016, для Power Query не стали создавать отдельную вкладку на ленте а втиснули его в качестве отдельной группы с банальным названием Скачать & преобразовать на вкладке Данные (хотя сообщество пользователей MS Excel и продолжает использовать старое имя — Power Query).

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

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

Adblock
detector