Решаем практические задачи с помощью Power Pivot, закрепляем навыки и знания, полученные в Базовом курсе.
| 1 | Power Pivot Практический №1. Значение показателя на конец месяца (ENDOFMONTH, CALCULATE) | В этом уроке вы узнаете как находить последнее значение показателя на конец месяца. С подобным приходится сталкивать часто, особенно когда речь о финансовых показателях, например, состояние кредитного портфеля. |
| 2 | Power Pivot Практический №2. Нарастающий итог, Анализ клиентской базы (CALCULATE, ALLEXCEPT, ALL, FILTER) | В этом уроке мы научимся считать нарастающий итог на примере анализа роста клиентской базы. Задача прикладная и интересная. |
| 3 | Power Pivot Практический №3. Анализ лояльности клиентов | В этом уроке мы проанализируем нашу клиентскую базу. |
| 4 | Power Pivot Практический №4. Анализ лояльности клиентов 2 | Проанализируем структуру продаж. Разобьем клиентов на группы в зависимости от года первой сделки. |
| 5 | Power Pivot Практический №5. Анализ лояльности клиентов 3 — сколько прошло до второго заказа | Посчитаем количество клиентов, которые сделали второй заказ через 0, 1, 2, 3 и т. д. квартала. |
| 6 | Power Pivot Практический №6. Сравнение всех категорий с выбранной | Научимся сравнивать продажи выбранной категории с остальными. |
| 7 | Power Pivot Практический №7. Динамический фильтр Топ N (HASONEVALUE, RANKX, ALL, IF) | В этом уроке вы узнаете как создать динамический фильтр Топ N, чтобы отображать в сводной таблице только несколько лучших значений. |
| 8 | Power Pivot Практический №8. Функция EARLIER, ABC анализ | Выполним ABC категоризацию в Power Pivot. |
Обзор программы PIVOT
Разное
Полезные уроки по Power Pivot, которые не вошли ни в один курс.
| 1 | Power Pivot Разное №1. Быстрая документация отчета (PP Utilities) | Как быстро получить список всех мер с их формулами и описаниями; Как быстро получить список источников и их взаимосвязи. |
| 2 | Power Pivot Разное №2. Невозможно создать диаграмму этого типа | В этом уроке вы узнаете как обойти ошибку «Невозможно создать диаграмму этого типа», которая возникает при попытке создать определенные диаграммы на основе данных из сводной таблицы. |
Источник: comrade-xl.ru
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Что такое PowerPivot?
Опубликовано 22.04.2013 Автор Ренат Лотфуллин
Как сделать свой мультфильм? | Pivot Animator
Миллионы пользователей используют формулы Excel для выполнения расчетов. Эти расчеты могут быть простыми, как сложение столбцов с числами, или гораздо более сложными, например, моделирование бизнес процессов. Но в любом случае, каждая формула строится с помощью основных операторов и функций, которые являются строительными блоками таких формул.
PowerPivot является для Excel строительным блоком необходимым для создания решений бизнес-аналитики. Вне зависимости, использует ли это решение простые расчеты или что-то гораздо более сложное. PowerPivot включает в себя возможность импорта таблиц из широкого спектра источников данных, выполнять быстрые расчеты больших объемов данных, использовать DAX формулы (Выражения анализа данных) и создавать отчеты сводных таблиц на основе полученной информации.
Выражения DAX очень схожи с формулами Excel. Но, несмотря на то, что список функций DAX во многом совпадает со списком Excel, в выражениях анализа существуют новые функции, которых в Excel нет. Эти функции предназначены для возможности анализа данных, в частности, для связывания таблиц и динамического анализа. Способность создавать расчеты, которые будут динамически оцениваться в различных контекстах, является мощным инструментом. До PowerPivot и DAX, такого рода расчеты часто требовали знания концепции многомерного программирования.
Введение в PowerPivot надстройку
После установки на ленте появится новая вкладка PowerPivot.
Щелчок на Окно PowerPiwot запустит новую вкладку PowerPivot для Excel.
Давайте здесь остановимся, чтобы определиться, как PowerPivot и Excel дополняют друг друга.
PowerPivot обладает встроенным алгоритмом обработки данных, который позволяет 1) хранить и обрабатывать большие объемы данных, чем доступно в Excel, 2) импортировать данные с различных источников данных, 3) моделировать операции, такие как определение связей между таблицами и создание DAX формул.
Надстройка PowerPivot для Excel (см. выше) имеет Excel-ориентированный пользовательский интерфейс.
Excel обладает широким спектром инструментов визуализации, таких как сводные таблицы, сводные диаграммы и срезы. Их также можно использовать для работы с данными PowerPivot.
Excel документ является отличным способом упаковки данных и визуализации. Это означает, что все, что вы видите в окне надстройки хранится, внутри вашей книги. Что облегчает управление и обмен данными.
Импорт данных
Конечно, все начинается с данных, и поэтому PowerPivot позволяет импортировать данные с различных источников. Ниже показан небольшой фрагмент списка возможных ресурсов.
В зависимости от типа источника, вы можете выбрать необходимые таблицы, просматривать и фильтровать поля таблиц, и/или использовать запрос для импорта.
Копировать/вставить
Зачастую пользователям требуется объединить данные, хранящиеся в PowerPivot, с небольшим объемом данных, находящимся на листе. Это возможно сделать с помощью создания связей между таблицами.
Работа с таблицами
После того, как данные были импортированы, они отображаются в окне PowerPivot для Excel.
Если источники данных имеют связи между таблицами, PowerPivot подтянет их автоматически. В противном случае, вы можете создать их вручную.
Зачем создавать связи между таблицами? Такие отношения позволяют корректно ориентироваться и группировать данные, что исключает необходимость сводить связанные столбцы в одну большую таблицу.
Расчеты
Конечная цель PowerPivot для Excel сделать анализ данных простым. В отличие от продуктов анализа данных, которые предназначены для IT-специалистов (например, SQL Server Analysis Services), PowerPivot предназначен для людей, которые используют сводные таблицы в своей ежедневной работе. Идея заключается в том, чтобы пользователи могли применять имеющиеся навыки Excel, без необходимости изучения специализированных языков.
=if([Gross]>100000000, “Отлично”, if([Gross]>10000000, “Хорошо”, “Плохо”))
Поэтому во время работы можно встретить знакомые формулы, такие как ABS, AVERAGE, AVERAGEA (думаю, со временем их тоже переведут на русский язык). Так же есть совершенно новые: ALL, ALLEXEPT, AVERAGEX.
Визуализация
Для того чтобы создавать многофункциональные BI приложения, PowerPivot использует инструменты Excel для визуализации. К счастью у Excel этого добра достаточно: сводные таблицы, сводные диаграммы и срезы.
После нескольких минут форматирования и размещения элементов управления, у меня получилась функциональная автономная книга, которая может работать как приложение.
Итог
Итак, что мы имеем? PowerPivot для Excel – это надстройка, используемая для добавления и анализа больших объемов данных. В Excel книга предоставляет все возможности визуализации данных и взаимодействия с ними. Навыки составления отчетов с помощью сводных таблиц позволят создавать сложные книги, основывающиеся на сложных аналитических данных Excel.
Вам также могут быть интересны следующие статьи
- Создание простейшего дашборда с помощью надстройки PowerView в Excel
- Создание сводной таблицы из данных PowerPivot
- Создание вычисляемого столбца с помощью формул DAX
- Создание графика в Excel с отрицательными и положительными значениями
- Создание квадратной/ вафельной диаграммы в Excel
- Импорт данных из Azure Marketplace в PowerPivot
- Создание связи между таблицами PowerPivot
- Импорт данных в PowerPivot
- Загрузка и установка PowerPivot
- Добавление визуализации в сводную таблицу
Источник: exceltip.ru
Надстройка для Excel — Power Pivot, или жизнь после 1 048 576 строк
Как показывает практика, если в файле Excel больше 50 тысяч строк, да еще формулы типа ВПР, он падает и умирает. Потом восстает, как зомби, чтобы выпить нашу кровь и нервы. Ведет он себя тоже как зомби — еле двигается и «ни черта» не соображает.
Что же делать? Ответ простой: начать работать с надстройкой для Excel — Power Pivot. Этот инструмент создан для работы с данными. Он может легко обрабатывать миллионы строк!
Надстройка Power Pivot в Excel
Power Pivot – это надстройка Excel, с помощью которой можно работать с данными в несколько миллионов строк, объединять таблицы в модель данных и создавать аналитические вычисления.
В «обычном» Excel пользователи ограничены количеством строк в таблице – не более размера листа в 1 048 тысяч строк, но в Power Pivot такого ограничения нет. Надстройка может подключаться к данным из внешних источников и работать с большими объемами информации в миллионы строк.
Открыть надстройку Power Pivot можно, нажав на вкладке меню Power Pivot кнопку Управление. Эта вкладка выглядит одинаково во всех версиях Excel.
Если такой вкладки у вас меню нет, проверьте, та ли у вас версия Excel . Так как Power Pivot представляет собой надстройку COM, то перед первым применением вам может потребоваться добавить её в меню (как это сделать, читайте в предыдущей статье ).
Хорошая новость: начиная c версий после 2019 года компания Microsoft анонсировала включение Power Pivot во все версии Excel.
Работа с данными в Power Pivot
Как правило, разработка отчетов в Power Pivot происходит в следующем порядке:
- Подключение к внешним источникам данных. При загрузке в Power Pivot данные сжимаются в несколько раз с помощью специальных механизмов оптимизации.
- Объединение таблиц в модель данных с помощью создания связей между ними.
- Аналитические вычисления с помощью DAX-формул.
- Построение сводных таблиц и диаграмм на основе модели данных.
Подключения к источникам, связи и вычисления настраиваются в отчете один раз. При изменении исходных данных отчеты можно обновить в меню Данные → Обновить все. Давайте разберем подробнее, как это работает.
Добавление данных в Power Pivot
Чтобы начать работать с Power Pivot, перейдите на вкладку меню Power Pivot → нажмите Управление. Добавить данные в открывшейся надстройке можно несколькими способами:
- С помощью встроенных инструментов импорта.
- Добавить данные из Power Query.
- Также таблицу с данными можно просто скопировать и вставить в Power Pivot из буфера обмена в меню Главная → Вставить.
Способ 1. Подключение к данным с помощью встроенных инструментов импорта.
В Power Pivot есть свои инструменты для импорта внешних данных, которые можно найти на вкладке Главная → кнопки Из базы данных, Из службы данных, Из других источников.
С помощью встроенных инструментов настраивается подключение к 15 видам источников данных.
Увидеть весь список можно в окне «Мастер импорта таблиц», которое открывается в меню Главная → Из других источников.
Настроим подключение к данным на примере файла Excel. Укажите путь к файлу, поставьте галочку «Использовать первую строку в качестве заголовков столбцов», выберите таблицы, жмем «Готово». У вас в окне включится счетчик импорта строк — работает довольно быстро. В результате импорта в окне Power Pivot появятся вкладки с таблицами.
Способ 2. Добавить данные из Power Query.
Загрузка данных с помощью инструментов Power Pivot делается легко, но Power Query лучше подходит для импорта и значительно расширяет возможности аналитики. В нем намного больше доступных источников и возможностей для обработки таблиц произвольного вида.
Чтобы настроить подключение с помощью Power Query, вам нужно создать запрос к источнику данных. Список ранее созданных запросов находится на вкладке «Запросы и подключения». Нажмите на запрос правой кнопкой мышки и выберите Загрузить в… В открывшемся окне доступных вариантов импорта поставьте галочку «Добавить эти данные в модель данных». Задать настройки импорта также можно в самом редакторе Power Query.
К сожалению, в Excel 2010 Power Pivot почти невозможно «подружить» с Power Query и этот новый функционал в старом Excel сильно ограничен.
Интерфейс Power Pivot
Разберем подробнее интерфейс Power Pivot.
В окне Power Pivot есть:
- Лента редактора для вкладок меню Главная, Конструктор, Дополнительно.
- Строка формул на языке DAX.
- Область данных и вычисляемых столбцов.
- Добавление нового вычисляемого столбца.
- Область вычислений, в которой можно писать меры.
- Меню, которое появляется при нажатии правой кнопкой мышки.
- Ярлычки с названиями таблиц данных для переключения между ними (как между листами в «обычном» Excel).
Модель данных и связи
Чтобы перейти к настройке связей между таблицами, выберите в меню Главная → Представление диаграммы (вернутся обратно к просмотру таблиц можно, нажав Представление данных).
Модель данных в Power Pivot – это набор таблиц, объединенных связями.
Графически связь таблиц обозначается линией между ними, как в примере на рисунке. Чтобы создать связь, выделите мышкой поле в одной таблице и «перетащите» его на соответствующее ему поле в области другой таблицы.
Power Pivot поддерживает типы связей «один к одному», «один ко многим».
- Понять, какой именно вид связи задан между таблицами, можно с помощью значков на концах линий: на стороне «один» стоит символ единица — «1», а на стороне «многие» — звездочка «*». Если между таблицами задана связь «один к одному», то на концах линии будут единички «1».
- Поля, которые используются для создания связей, называются ключами связи. В таблицах, которые находятся на стороне «один» (конец линии с единичкой «1») в ключевых столбцах должны содержаться только уникальные значения. В таблицах на стороне «многие» со звездочкой «*» в ключевых столбцах те же значения, но они могут повторяться много раз.
- Стрелка на линии связи обозначает направление фильтрации. Так, на рисунке выше справочники Товары и Города фильтруют таблицы ДанныеФакт и ДанныеПлан.
Если выделить мышкой линию связи в модели данных, то можно увидеть, с помощью каких полей задана связь. Выделенные линии можно удалять. Или, щелкнув по ним дважды, менять связи в открывшемся окне. Также управление связями доступно в окне, которое открывается в меню Конструктор → Управление связями.
Вычисления в Power Pivot
Формулы Power Pivot пишут на языке DAX (Data Analysis Expressions, выражения для анализа данных). DAX-формулы позволяют, по аналогии с формулами Excel, выполнять вычисления и/или настраивать произвольную фильтрацию и представление данных в таблицах.
Язык DAX впервые появился в 2010 году вместе с надстройкой Power Pivot. В этом языке сотни функций, с помощью которых можно создавать аналитические расчеты. Кроме Power Pivot в Excel, DAX-формулы также доступны в Power BI и Analysis Services. То есть эти формулы вам точно пригодятся.
Вычисления с помощью DAX-формул создаются в виде:
- вычисляемых столбцов, как в обычных таблицах Excel.
- мер, которые пишут в области вычислений под таблицей.
Вычисляемые столбцы представляют собой столбцы в таблицах данных, созданные с помощью формул. Чтобы добавить такой столбец, щелкните мышкой дважды по столбцу слева «Добавление столбца», введите название вычисления, а затем знак «=» и формулу в строке формул.
Вычисляемый столбец похож на любой другой столбец в таблице. Также с помощью таких столбцов можно создавать связи в модели. Вычисляемые столбцы в Excel рассчитываются в момент загрузки (обновления) данных, а значения в самом столбце сохраняются, увеличивая размер файла. То есть столбцы – это статические вычисления.
Как и вычисляемые столбцы, меры тоже создаются с помощью DAX-формул. В отличие от столбцов, меры – это динамические вычисления, результат которых зависит от контекста – его можно увидеть в отчете, где мы задаем в разрезе каких полей, фильтров и др. вычислить меру. Меры записываются под таблицей в области вычислений.
Меры в Power Pivot можно превратить в KPI – ключевые показатели эффективности. Для этого выделите меру и нажмите на кнопку Создать KPI в меню Главная. Кроме мер, созданных пользователями, в Excel также есть неявные меры. Они создаются автоматически при формировании сводной таблицы, когда пользователь помещает данные в область значений. Чтобы посмотреть, есть ли у вас в Power Pivot неявные меры, выберите на вкладке Главная → Показать скрытые.
Подробнее о DAX-формулах:
- Основные формулы Power Pivot
- ТОП-20 DAX формул для Power Pivot и Power BI
Источник: finalytics.pro