Microsoft Excel 2019/2016. Уровень 6. Бизнес — аналитика с использованием Power Pivot, Power Query и 3D Map

Нарастающий итог в Power Query

| Категория: PowerQuery, Надстройки Power, Приемы и советы | Опубликовано 07-02-2021

1

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

Например, есть таблица и необходимо вычислить накопительным итогом по месяцам, начиная с начала года:

Алгоритм действий:

1. Добавить столбец индекса с 1.

2. Добавить пользовательский столбец, например, Диапазон, с использованием функции List.Range.

List.Range(list as list, offset as number, optional count as nullable number) as list

В результате сформированы списки — каждый раз от 1-го элемента до текущего:

3. Суммировать элементы списка функцией List.Sum. Добавить пользовательский столбец, например, Накопительный итог:

=List.Sum([Диапазон])

4. Удалить лишние столбцы, настроить типы данных — результат готов:

… но это не единственный способ. И в нем есть плюсы и минусы.

Отключение «типов данных» в Power Query

| Категория: PowerQuery, Надстройки Power, Приемы и советы | Опубликовано 30-11-2020

Изначально, в настройках Power Query была возможность настройки отключения «типов данных» только в текущем файле: вкладка Данные [Data] — группа Получить и преобразовать данные [Get & Transform Data] — Получить данные [Get Data] — Параметры запроса [Query Options]. В группе Текущая книга [CURRENT WORKBOOK] — Загрузка данных [Data Load] — Определение типов [Type Detection] — Определять типы и заголовки столбцов для неструктурированных источников [Detect column types and headers for unstructured sources]. Большинство пользователей вынуждены или отключать данную опцию в каждом файле или делать отдельный файл-шаблон с нужными настройками.

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

В окне Excel выбрать вкладка Данные [Data] — группа Получить и преобразовать данные [Get & Transform Data] — Получить данные [Get Data] — Параметры запроса [Query Options]. В группе Глобальные [GLOBAL] — Определение типов [Type Detection] — выбрать Никогда не определять типы и заголовки столбцов для неструктурированных источников [Never detect column types and headers for unstructured sources]

2 способа создания отчетов по данным модели Power Pivot

| Категория: Надстройки Power, Приемы и советы | Опубликовано 26-06-2020

1 способ. Из окна Power Pivot

На вкладке Главная воспользоваться кнопкой Сводная таблица и выбрать нужный вариант визуализации: Сводная таблица, Сводная диаграмма и их предлагаемые комбинации.

2 способ. Из окна Microsoft Excel

В окне Microsoft Excel выбрать на вкладке Вставка в группеТаблицы команду Сводная таблица или в группе Диаграммы команду Сводная диаграмма. При наличии в файле модели, появляется дополнительный пункт Использовать модель данных этой книги

О программе

Курс ориентирован на тех, кто уже знаком с Power BI и Excel и хочет повысить свой профессиональный уровень. Вы научитесь обрабатывать данные из разных источников, писать сложные формулы в DAX и создавать отчёты с широкой навигацией, которые ускорят поиск ответов на вопросы бизнеса. Во время обучения примените на практике продвинутые техники Power BI и Excel и создадите интерактивный дашборд с ключевыми метриками вашего проекта.

Варианты обучения

Вариант обучения Стоимость Когда Срок обучения График занятий Вариант обучения Online Дистанционная Стоимость 23 100 р. Когда 2 авг →2 окт Срок обучения 2 месяца График занятий —

Кому подойдет программа

• Опытным пользователям Power BI или Excel

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

• Финансистам

  Создавать рабочие решения под свои задачи без привлечения программистов и затрат на кастомизацию решения

• Аналитикам и маркетологам

  Выполнять сложные выражения DAX в Power BI, Power Pivot, выйти за рамки привычных и не всегда работающих формул и узнать инструмент более подробно

Контакты

Показать на карте

• Москва, Варшавское ш., д. 1, стр. 3 3-й этаж, офис B306-B308
• +7 800 301-39-69
• +7 495 152-55-28

Встроенные средства языка

• В этом варианте используем встроенную в язык функцию #table.
• Функция принимает два варианта параметров:
  • Первый параметр список имен полей, Второй список списков записей таблицы (см. Создание таблицы на основании списка полей)
  • Первый параметр тип описывающий тип таблицы в виде перечисления в записи имен полей и их типов, Второй параметр список списков записей таблицы (см. Создание таблицы на основании ее типа).

Создание таблицы на основании списка полей

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

• Мы можем просто определить таблицу и передав пустой список вторым параметром просто создать пустую таблицу

#table(   // Список имен полей      { "Digit", "Name" },   // Пустой список     {}  ) 

• Или добавить какие то статические данные:

#table(   // Список имен полей      { "Digit", "Name" },   // Список списков записей таблицы     {       {1,"one"},        {2,"two"},        {3,"three"}     }  ) 

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

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

• Мы можем просто определить таблицу и передав пустой список вторым параметром просто создать пустую таблицу

#table(   // Тип описывающий таблицу через определения в записи типов ее полей:     type table [Digit = number, Name = text],    // Пустой список     {}   }  ) 

• Или добавить какие то статические данные:

#table(     // Тип описывающий таблицу через определения в записи типов ее полей:       type table [Digit = number, Name = text],      // Список списков записей таблицы       {         {1,"one"},          {2,"two"},          {3,"three"}       }  ) 

Шаблоны использования

Таблица для хранения мер в модели

Минимальное необходимый код для создания пустой таблицы хранения мер:

#table(type table [Empty = text] , {} ) 

Использованные источники

😼 Выбор редакции 9 Февраля 2021

7 503

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Описание курса

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

Обучение проходит на последней версии Excel 2019

Устали анализировать разные источники данных в Excel? Вам кажется, что это и долго, и сложно? Пришло время изучить прогрессивные инструменты! Вы сможете анализировать данные из внешних источников, таких как базы данных Access, SQL-сервер, OLAP-куб, файлы Excel, текстовые файлы txt/csv и т.д., загружая их в модель PowerPivot напрямую или после предварительной обработки в Power Query. В модели PowerPivot при необходимости можно создавать связи между этими источниками, выполнять расчеты на языке DAX как с использованием простых вычислений, так и с применением функций. Вы больше не будете зависеть от разработчиков, т.к. сможете сами загружать данные и строить отчеты.

Полученные данные можно будет представить в комбинированных отчетах сводных таблиц и сводных диаграмм, причем как зависимых, так и независимых друг от друга. Подключая срезы к отчетам, Вы будете легко и динамично исследовать влияние различных параметров на результаты. Есть необходимость строить отчеты с привязкой к географической карте мира? Тогда без модели Power Pivot и отчетов 3D Map (PowerMap) Вам не обойтись!

На данном курсе помимо работы с преподавателем предлагается выполнить самостоятельную работу для отработки полного цикла работы с моделью, т.к. шаг за шагом решается сквозная задача от исходных данных до построения аналитических отчетов, позволяющих оценить влияние различных факторов на результат как в одиночку, так и комплексно. Power Pivot, Power Query, 3D Map – это мощные современные инструменты анализа данных в Microsoft Excel!

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

Этот курс читают только сертифицированные тренеры Microsoft!

Минимальная версия Excel для данного курса — 2013. Дополнительно необходимо установить Microsoft Power Query для Excel.

Важно! Проверяйте наличие данных надстроек при работе на Mac.

ПОЛЕЗНЫЕ МАТЕРИАЛЫ • •

Узнать больше

Вы научитесь

• Загружать данные в модель данных PowerPivot из внешних источников
• Создавать связи между таблицами в модели данных
• Импортировать данные в модель PowerPivot с использованием PowerQuery
• Редактировать, создавать вычисления, объединять и добавлять запросы PowerQuery
• Выполнять анализ и моделирование данных с помощью PowerPivot
• Строить DAX-формулы для получения результатов обработки как по простым формулам, так и с применением функций различных категорий, создавая как вычисляемые поля, так и вычисляемые столбцы
• Выполнять построение комбинированных отчетов как в виде сводных таблиц, так и сводных диаграмм
• Строить наглядные отчеты для анализа данных с привязкой к географическому расположению с 3D Map (Power Map).

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

Большинство выпускников наших курсов делают успешную карьеру и пользуются уважением работодателей.

Узнать больше

Предварительная подготовка

Требуемая подготовка: Успешное окончание курса 55291AC: Microsoft Excel 2019. Уровень 2. Расширенные возможности или эквивалентная подготовка.

Рекомендуемая подготовка (необязательная): Успешное окончание курса Microsoft Excel 2019/2016. Уровень 3. Анализ и визуализация данных или эквивалентная подготовка.

Для определения уровня предварительной подготовки рекомендуем Вам пройти бесплатное тестирование.

Узнать больше

Тестирование по курсу

• Microsoft Excel 2019/2016. Уровень 6. Бизнес-аналитика с использованием Power Pivot, Power Query и 3D Map

Программа курса

Модуль 1. Введение в PowerPivot. Источники &nbsp(2&nbspак.&nbspч.)

• Особенности и возможности Power Pivot
• Подключение надстройки Power Pivot
• Импорт данных из источников:
  • Файлы Excel
  • Текстовые файлы csv
  • Реляционные базы данных: Access, SQL-server
  • Многомерные источники — службы Microsoft Analysis Services (OLAP-куб)
• Фильтрация данных при импорте
• Управление моделью и создание связей
• Практические упражнения

Модуль 2. Подготовка источника в модель PowerPivot с использованием PowerQuery &nbsp(2&nbspак.&nbspч.)

• Создание запроса из текстового файла и книг Excel
• Работа с данными запроса:
• Редактирование/Преобразование
• Создание простых вычисляемых полей
• Обновление результатов

Добавление запросов – получение единого консолидируемого источника Добавление/удаление результатов запроса в модель PowerPivot Практические упражнения Модуль 3. Вычисления в источниках PowerPivot &nbsp(4&nbspак.&nbspч.)

• Создание вычисляемых столбцов с использованием DAX-формул:
  • Простейшие вычисления
  • Использование функций категорий: Дата и время, Логические, Математические, Текстовые, Фильтр.
• Создание вычисляемых полей (мер)
• Практические упражнения

Модуль 4. Работа с отчетами &nbsp(4&nbspак.&nbspч.)

• Создание отчетов различных видов: Сводная таблица, Сводная диаграмма, Диаграмма и таблица, Две диаграммы, Четыре диаграммы
• Скрытие/отображение полей и таблиц из списка полей сводных таблиц
• Создание и управление наборами
• Создание пользовательских иерархий для использования в отчете
• Фильтрация данных: фильтры, срезы, временная шкала. Подключение фильтров к отчетам
• Создание и управление ключевыми показателями эффективности
• Практические упражнения

Модуль 5. Визуализация данных на географической карте — 3D карта (Power Map)&nbsp(2&nbspак.&nbspч.)

• Создание отчета в 3D карта
• Создание слоя:
  • Настройка географической привязки
  • Выбор визуализации слоя
  • Фильтрация данных
  • Настройка параметров слоя
  • Изменение внешнего вида/темы карты
• Настройка визуализации с использованием сцен
• Работа с несколькими слоями
• Создание видео по данным отчета
• Практические упражнения

Модуль 6. Итоговая работа по курсу&nbsp(2&nbspак.&nbspч.)

Аудиторная нагрузка в классе с преподавателем: 16 ак. ч. + 8 ак. ч. бесплатно*

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

• утренним группам с 8:30 до 10:00
• дневным группам — по 1 ак.ч. до и после занятий (13.15-14.00, 17.10-17.55)

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

Узнать больше

Расписание групп обучения

Данный курс вы можете пройти как в очном формате, так и дистанционно в режиме онлайн . Чтобы записаться на онлайн-обучение, в корзине измените тип обучения на «онлайн» и выберите удобную для вас группу.

Чем онлайн-обучение отличается от других видов обучения?

Сортировать:

Режим обучения

Преподаватель

Место обучения

Сбросить

Дата

Режим обучения

Очно и онлайн

Преподаватель

КулешоваОльга Владимировна

Место обучения

«Таганский»

м.Пролетарская

м.Крестьянская застава

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

КулешоваОльга Владимировна

Место обучения

«Белорусско-Савеловский»

м.Белорусская

м.Савеловская

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

СидороваЕлена Владимировна

Место обучения

«Таганский»

м.Пролетарская

м.Крестьянская застава

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

КулешоваОльга Владимировна

Место обучения

«Белорусско-Савеловский»

м.Белорусская

м.Савеловская

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

СидороваЕлена Владимировна

Место обучения

«Белорусско-Савеловский»

м.Белорусская

м.Савеловская

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

СидороваЕлена Владимировна

Место обучения

«Белорусско-Савеловский»

м.Белорусская

м.Савеловская

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

СидороваЕлена Владимировна

Место обучения

«Таганский»

м.Пролетарская

м.Крестьянская застава

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

СидороваЕлена Владимировна

Место обучения

«Белорусско-Савеловский»

м.Белорусская

м.Савеловская

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

КулешоваОльга Владимировна

Место обучения

«Белорусско-Савеловский»

м.Белорусская

м.Савеловская

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

СидороваЕлена Владимировна

Место обучения

«Таганский»

м.Пролетарская

м.Крестьянская застава

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

КулешоваОльга Владимировна

Место обучения

«Белорусско-Савеловский»

м.Белорусская

м.Савеловская

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

КулешоваОльга Владимировна

Место обучения

«Белорусско-Савеловский»

м.Белорусская

м.Савеловская

Стоимость для физ. лиц

Дата

Режим обучения

Очно и онлайн

Преподаватель

КулешоваОльга Владимировна

Место обучения

«Таганский»

м.Пролетарская

м.Крестьянская застава

Стоимость для физ. лиц

* Данная скидка действительна при заказе и оплате онлайн обучения только сегодня. Запишитесь прямо сейчас со скидкой!

Показать еще

Полное расписание курса

Стоимость обучения

Частным лицам

Очно

17 490 ₽

Онлайн

15 700 ₽ (-10%)

Обучение в кредит

От 933 руб./месяц

Индивидуальное обучение

37 200 ₽ *

Организациям

Очно

19 490 ₽

Онлайн

17 540 ₽ (-10%)

Указана минимальная цена за индивидуальное обучение. Число часов работы с преподавателем в 2 раза меньше, чем при обучении в группе. Если Вам для полного усвоения материала курса потребуется больше часов работы с преподавателем, то они оплачиваются дополнительно. В случае занятий по индивидуальной программе расчёт стоимости обучения и количества необходимых часов производится отдельно.

Длительность индивидуального обучения — минимум 4 академических часа. Стоимость обучения в Москве уточняйте у менеджера. При выездном индивидуальном обучении устанавливается надбавка: +40% от стоимости заказанных часов при выезде в пределах МКАД, +40% от стоимости заказанных часов и + 1% от стоимости заказанных часов за каждый километр удаления от МКАД при выезде в пределах Московской области. Стоимость выезда за пределы Московской области рассчитывается индивидуально менеджерами по работе с корпоративными клиентами.

Для юридических лиц (организаций) указана цена, действующая при полной предоплате.

Узнать больше

Документы об окончании

В зависимости от программы обучения выдаются следующие документы:

Cертификат международного образца

Удостоверение *

Свидетельство

* Для получения удостоверения вам необходимо предоставить копию диплома о высшем или среднем профессиональном образовании.

Сертификаты международного образца выводятся после окончания курса в личном кабинете слушателя.

Больше знаний — меньше стоимость!

Этот курс обойдётся до 30% дешевле, если изучать его в составе дипломных программ. Пройдя обучение, Вы получите современную профессию и диплом о переподготовке.

Данное предложение действует только для частных лиц.