Содержание
- Нарастающий итог в Power Query
- Отключение «типов данных» в Power Query
- 2 способа создания отчетов по данным модели Power Pivot
- О программе
- Варианты обучения
- Кому подойдет программа
- Контакты
- Встроенные средства языка
- Шаблоны использования
- Использованные источники
- Описание курса
- Вы научитесь
- Предварительная подготовка
- Тестирование по курсу
- Программа курса
- Расписание групп обучения
- Стоимость обучения
- Документы об окончании
Нарастающий итог в Power Query
| Категория: PowerQuery, Надстройки Power, Приемы и советы | Опубликовано 07-02-2021
1
Нарастающий итог в Excel можно вычислить несколькими способами. Например, использовать простейшие формулы или задействовать дополнительные вычисления в сводных таблицах. Однако, если источник получен различными преобразованиями в Power Query, то логично получить и конечный результат в Power Query.
Например, есть таблица и необходимо вычислить накопительным итогом по месяцам, начиная с начала года:
Алгоритм действий:
- Добавить столбец индекса с 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 — инструмент «на все случаи жизни», а изучение надстроек — прикладной источник конкурентных преимуществ.
Описание курса
Данный контент доступен только авторизованным пользователям. Пожалуйста, войдите на сайт, либо зарегистрируйтесь.
Обучение проходит на последней версии 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. Источники  (2 ак. ч.)
- Особенности и возможности Power Pivot
- Подключение надстройки Power Pivot
- Импорт данных из источников:
- Файлы Excel
- Текстовые файлы csv
- Реляционные базы данных: Access, SQL-server
- Многомерные источники – службы Microsoft Analysis Services (OLAP-куб)
- Фильтрация данных при импорте
- Управление моделью и создание связей
- Практические упражнения
Модуль 2. Подготовка источника в модель PowerPivot с использованием PowerQuery  (2 ак. ч.)
- Создание запроса из текстового файла и книг Excel
- Работа с данными запроса:
- Редактирование/Преобразование
- Создание простых вычисляемых полей
- Обновление результатов
Добавление запросов – получение единого консолидируемого источника Добавление/удаление результатов запроса в модель PowerPivot Практические упражнения Модуль 3. Вычисления в источниках PowerPivot  (4 ак. ч.)
- Создание вычисляемых столбцов с использованием DAX-формул:
- Простейшие вычисления
- Использование функций категорий: Дата и время, Логические, Математические, Текстовые, Фильтр.
- Создание вычисляемых полей (мер)
- Практические упражнения
Модуль 4. Работа с отчетами  (4 ак. ч.)
- Создание отчетов различных видов: Сводная таблица, Сводная диаграмма, Диаграмма и таблица, Две диаграммы, Четыре диаграммы
- Скрытие/отображение полей и таблиц из списка полей сводных таблиц
- Создание и управление наборами
- Создание пользовательских иерархий для использования в отчете
- Фильтрация данных: фильтры, срезы, временная шкала. Подключение фильтров к отчетам
- Создание и управление ключевыми показателями эффективности
- Практические упражнения
Модуль 5. Визуализация данных на географической карте – 3D карта (Power Map) (2 ак. ч.)
- Создание отчета в 3D карта
- Создание слоя:
- Настройка географической привязки
- Выбор визуализации слоя
- Фильтрация данных
- Настройка параметров слоя
- Изменение внешнего вида/темы карты
- Настройка визуализации с использованием сцен
- Работа с несколькими слоями
- Создание видео по данным отчета
- Практические упражнения
Модуль 6. Итоговая работа по курсу (2 ак. ч.)
Аудиторная нагрузка в классе с преподавателем: 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% дешевле, если изучать его в составе дипломных программ. Пройдя обучение, Вы получите современную профессию и диплом о переподготовке.
Данное предложение действует только для частных лиц.
ли со статьей или есть что добавить?