Использование функции Related из надстройки PowerPivot. Power Pivot: Оконные функции под соусом DAX Powerpivot пример использования

office.microsoft

Менее чем за час вы научитесь создавать в Excel отчеты сводной таблицы, объединяющие данные из нескольких таблиц. Первая часть этого учебника поможет вам импортировать данные и изучить их. Во второй части вы научитесь уточнять модель данных, лежащую в основе отчета, добавлять в отчеты Power View новые вычисления и иерархии, а также оптимизировать их с помощью надстройки Power Pivot.

Начнем с импорта данных.

1. Скачайте образцы данных (ContosoV2) для этого учебника. Извлеките файлы данных и сохраните их в удобном месте, например в папке "Загрузки" или "Мои документы".
2. Откройте в Excel пустую книгу.
3. Выберите Данные > Получение внешних данных > Из Access .
4. Перейдите в папку, содержащую файлы образцов данных, и выберите ContosoSales.
5. Нажмите Открыть . Поскольку вы подключаетесь к файлу базы данных, который содержит несколько таблиц, появится диалоговое окно Выбор таблицы , где вы можете выбрать таблицы, которые нужно импортировать.

1. В диалоговом окне "Выбор таблицы" установите флажок Разрешить выбор нескольких таблиц .
2. Выберите все таблицы и нажмите кнопку ОК .
3. На вкладке "Импорт данных" выберите Отчет сводной таблицы и нажмите кнопку ОК .

ПРИМЕЧАНИЕ. Поздравляем! Вы только что создали модель данных. Модель - это уровень интеграции данных, который создается автоматически, когда вы импортируете несколько таблиц или работаете с ними одновременно в одном отчете сводной таблицы.

Модель практически не видна в Excel, но вы можете просматривать и изменять ее напрямую с помощью надстройки Power Pivot . В Excel наличие модели данных прослеживается тогда, когда вы видите набор таблиц в списке полей сводной таблицы. Существует несколько способов создать модель.

Просмотр данных в сводной таблице

Чтобы просмотреть данные в понятном виде, вы можете перетащить поля в области Значения , Столбцы и Строки в списке полей сводной таблицы.

1. Прокрутите список полей и найдите в нем таблицу FactSales.
2. Выберите столбец SalesAmount. Поскольку он содержит числовые данные, Excel автоматически поместит SalesAmount в область "Значения".
3. Из таблицы DimDate перетащите столбец CalendarYear в область "Столбцы".
4. Из таблицы DimProductSubcategory перетащите столбец ProductSubcategoryName в область "Строки".
5. Из таблицы DimProduct перетащите столбец BrandName в область "Строки", поместив его под подкатегорией.

Сводная таблица должна быть похожа на изображенную ниже.

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

Но что делать, если данные происходят из разных источников или импортируются не одновременно? В общих случаях вы сможете внести новые данные, создав связи на основе совпадающих столбцов. На следующем этапе вы импортируете дополнительные таблицы и узнаете о требованиях к новым связям и этапах их создания.

Добавление дополнительных таблиц

Чтобы научиться устанавливать связи, вам нужны дополнительные несвязанные таблицы. На этом этапе вы получите оставшиеся данные, используемые в этом учебнике, импортировав дополнительную базу данных и вставив данные из двух других книг.

Добавление категорий продуктов

1. Откройте в книге новый лист. В нем будут храниться дополнительные данные.
2. Выберите Данные > Получение внешних данных > Из Access .
3. Перейдите к папке, содержащей файлы образцов данных, и выберите ProductCategories. Нажмите кнопку Открыть .
4. На вкладке "Импорт данных" выберите пункт Таблица и нажмите кнопку ОК .

Добавление географических данных

1. Добавьте еще один лист.
2. Из файлов образцов данных откройте Geography.xlsx, поместите курсор в поле A1, затем нажмите клавиши CTRL+SHIFT+END, чтобы выбрать все данные.
3. Скопируйте данные в буфер обмена.
4. Вставьте данные в только что добавленный пустой лист.
5. Выберите Форматировать как таблицу с любым стилем. Форматирование данных в виде таблицы позволяет присвоить им имя, что очень удобно при задании связей в следующем шаге.
6. Убедитесь, что в окне "Форматирование таблицы" установлен флажок Таблица с заголовками . Нажмите кнопку ОК .
7. Присвойте таблице имя Geography. Выберите вкладку Работа с таблицами > Конструктор и введите название Geography в поле "Имя таблицы".
8. Закройте файл Geography.xlsx, чтобы убрать его из рабочей области.

Добавление данных по магазинам

• Повторите предыдущие шаги для файла Stores.xlsx - вставьте его содержимое в пустой лист. Присвойте таблице имя Stores.

Теперь получилось четыре листа. Лист1 содержит сводную таблицу, лист Лист2 содержит ProductCategories , Лист3 содержит Geography, а Лист4 - Stores. Поскольку вы уже присвоили имена всем таблицам, следующий шаг - создание связей - будет гораздо проще.

Использование полей из новых импортированных таблиц

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

1. Выберите пункт Все в верхней части списка полей сводной таблицы, чтобы просмотреть полный список доступных таблиц.
2. Прокрутите список вниз. Там находятся новые таблицы, которые вы только что добавили.
3. Разверните Stores.
4. Перетащите столбец StoreName в область "Фильтры".
5. Обратите внимание, что Excel выдаст запрос на создание связи. Это уведомление появляется по той причине, что вы использовали поля таблицы, которая не связана с моделью.
6. Нажмите Создать , чтобы открыть диалоговое окно "Создание связи".
7. В области "Таблица" выберите FactSales. Таблица FactSales из образца данных содержит подробные сведения о продажах и стоимости для компании Contoso, а также ключи других таблиц, в том числе коды магазинов, присутствующие в файле Stores.xlsx, импортированном на предыдущем этапе.
8. В области "Столбец (внешний)" выберите StoreKey.
9. В области "Связанная таблица" выберите Stores.
10. В области "Связанный столбец (основной)" выберите StoreKey.
11. Нажмите кнопку ОК .

Связывание ProductSubcategory с ProductCategory

1. В Excel выберите Данные > Отношения > Создать .
2. В области "Таблица" выберите DimProductSubcategory.
3. В области "Столбец (внешний)" выберите ProductCategoryKey.
4. В области "Связанная таблица" выберите Table_ProductCategory.accdb.
5. В области "Связанный столбец (основной)" выберите ProductCategoryKey.
6. Нажмите кнопку ОК .
7. Закройте диалоговое окно Управление связями .

Добавление категорий в сводную таблицу

Хотя в модель данных были добавлены дополнительные таблицы и связи, они еще не используются в сводной таблице. В этой задаче в список полей сводной таблицы добавляется ProductCategory.

1. Все , чтобы отобразить таблицы, присутствующие в модели данных.
2. Прокрутите список вниз.
3. В области "Строки" удалите BrandName.
4. Разверните узел Table_DimProductCategories.accdb.
5. Перетащите ProductCategoryName в область "Строки", поместив его над ProductSubcategory.
6. В области полей сводной таблицы выберите пункт Активны , чтобы только что использованные таблицы стали использоваться в сводной таблице.

Контрольная точка: повторите изученный материал

Вы создали сводную таблицу, которая содержит данные из нескольких таблиц, импортированных на предыдущем этапе. Чтобы свести данные воедино, потребовалось создать связи таблиц, которые Excel использует для согласования строк. Вы узнали, что наличие столбцов с данными о сопоставлении необходимо для поиска связанных строк. В образце данных все таблицы содержат столбец, который можно использовать с этой целью.

Хотя сводная таблица работает, вы, возможно, заметили некоторые оставшиеся недочеты. Список полей сводной таблицы выглядит так, будто в нем есть лишние таблицы (DimEntity) и столбцы (ETLLoadID), никак не связанные с компанией Contoso. Кроме того, данные из таблицы Geography все еще не интегрированы.

Далее: просмотр и расширение модели с помощью Power Pivot

В следующей серии задач вы расширите модель с помощью надстройки Microsoft Office Power Pivot в Microsoft Excel 2013. Вы узнаете, что проще всего создавать связи с помощью представления диаграммы, предусмотренного в этой надстройке. Кроме того, вам предстоит использовать эту надстройку для создания вычислений и иерархий, скрытия элементов, которые не должны отображаться в списке полей, и оптимизации данных для дополнительных отчетов.

ПРИМЕЧАНИЕ. Надстройка Power Pivot в Microsoft Excel 2013 доступна в Office профессиональный плюс.

Добавьте Power Pivot на ленту Excel , включив надстройку Power Pivot.

1. Перейдите на вкладку Файл > Параметры > Надстройки .
2. В поле Управление выберите Надстройки COM > Перейти .
3. Установите флажок Microsoft Office Power Pivot в Microsoft Excel 2013 , а затем нажмите кнопкуОК .

На ленте появится вкладка Power Pivot.

Добавление связи с помощью представления диаграммы в Power Pivot

1. В Excel выберите Лист3, чтобы сделать его активным. Лист3 содержит импортированную ранее таблицу Geography.
2. На ленте выберите Power Pivot > Добавить в модель данных . На этом этапе таблица Geography будет добавлена в модель. Также откроется надстройка Power Pivot, которую можно использовать для выполнения оставшихся этапов задачи.
3. Обратите внимание, что в окне Power Pivot отображаются все таблицы модели, включая таблицу Geography. Просмотрите несколько таблиц. В этой надстройке вы можете просматривать все данные, содержащиеся в модели.
4. В окне Power Pivot в разделе "Вид" выберите Представление диаграммы .
5. С помощью полосы прокрутки измените размер диаграммы таким образом, чтобы видеть все объекты в диаграмме. Обратите внимание, что две таблицы не связаны с остальной диаграммой: DimEntity и Geography.
6. Щелкните правой кнопкой мыши DimEntity, а затем нажмите Удалить . Эта таблица является частью исходной базы данных и не нужна в модели.
7. Настройте масштаб в таблице Geography таким образом, чтобы было видно все ее поля. Можно увеличить диаграмму таблицы с помощью ползунка.
8. Обратите внимание, что таблица Geography содержит столбец GeographyKey. В этом столбце находятся значения, которые являются уникальными идентификаторами каждой строки таблицы Geography. Давайте определим, используют ли другие таблицы в этой модели такой же ключ. Если это так, мы сможем создать связь, которая соединит таблицу с остальной частью модели.
9. Выберите Найти .
10. В поле "Поиск метаданных" введите GeographyKey.
11. Несколько раз нажмите кнопку Найти далее . Значение GeographyKey будет найдено в таблицах Geography и Stores.
12. Перетащите таблицу Geography к таблице Stores.
13. Перетащите столбец GeographyKey в таблице Stores на столбец GeographyKey в таблице Geography. Power Pivot проведет черту между двумя столбцами, обозначающую связь.

В этой задаче вы узнали новый способ добавления таблицы и создания связей. Теперь у вас полностью интегрированная модель, в которой все таблицы соединены и доступны для сводной таблицы на листе Лист1.

СОВЕТ. В представлении схемы некоторые схемы таблиц полностью расширены и отображают столбцы ETLLoadID, LoadDate и UpdateDate. Эти конкретные поля являются частью исходного хранилища данных Contoso и добавлены для поддержки операций извлечения и загрузки данных. В вашей модели они не нужны. Чтобы избавиться от них, выделите и щелкните поля правой кнопкой мыши, а затем нажмитеУдалить .

Создание вычисляемого столбца

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

1. В окне Power Pivot вернитесь к представлению данных.
2. Дайте таблице Table_ProductCategories accdb более понятное имя. Вы будете ссылаться на эту таблицу на следующих этапах, и более короткое имя упростит чтение вычислений. Щелкните правой кнопкой мыши имя таблицы, а затем нажмите Переименовать , введите имя ProductCategories и нажмите клавишу ВВОД.
3. Выберите таблицу FactSales.
4. Выберите Конструктор > Столбцы > Добавить .
5. В строке формул над таблицей введите следующую формулу. Функция автозаполнения поможет ввести полные имена столбцов и таблиц и покажет доступные функции. Вы также можете просто щелкнуть столбец, и Power Pivot добавит его имя в формулу.

= - -

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

1. Переименуйте столбец, щелкнув правой кнопкой CalculatedColumn1 и выбрав Переименовать столбец . Введите Profit и нажмите клавишу ВВОД.
2. Теперь выберите таблицу DimProduct.
3. Выберите Конструктор > Столбцы > Добавить .
4. В строке формул над таблицей введите следующую формулу.

RELATED(ProductCategories)

Функция RELATED возвращает значение из связанной таблицы. В нашем случае таблица ProductCategories содержит названия категорий продуктов, которые потребуется добавить в таблицу DimProduct, когда вы построите иерархию, которая включает сведения о категориях.

1. Когда вы закончите вводить формулу, нажмите клавишу ВВОД, чтобы подтвердить ее.

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

1. Переименовать столбец . Введите ProductCategory и нажмите клавишу ВВОД.
2. Выберите Конструктор > Столбцы > Добавить .
3. В строке формул над таблицей введите следующую формулу, а затем нажмите клавишу ВВОД, чтобы подтвердить ее.

RELATED(DimProductSubcategory)

1. Переименуйте столбец, щелкнув правой кнопкой мыши CalculatedColumn1 и выбрав Переименовать столбец . Введите ProductSubcategory и нажмите клавишу ВВОД.

Создание иерархии

Большинство моделей содержат данные, являющиеся по своей природе иерархическими. Распространенные примеры: данные календаря, географические данные и категории продуктов. Создание иерархий полезно тем, что позволяет перетаскивать элемент (иерархию) в отчет, а не собирать и упорядочивать одни и те же поля каждый раз заново.

1. В Power Pivot перейдите в представление диаграммы. Разверните таблицу DimDate, чтобы было проще работать с ее полями.
2. Нажмите и удерживайте клавишу CTRL и щелкните столбцы CalendarYear, CalendarQuarter и CalendarMonth (потребуется выполнить прокрутку вниз в таблице).
3. Выбрав три столбца, щелкните правой кнопкой мыши один из них и нажмите кнопку Создать иерархию . В нижней части таблицы будет создан родительский узел иерархии Hierarchy 1, а выбранные столбцы будут скопированы в иерархию в качестве дочерних узлов.
4. Введите в качестве имени новой иерархии Dates.
5. Добавьте в иерархию столбец FullDateLabel. Щелкните правой кнопкой мыши FullDateLabel и выберите пункт Добавить в иерархию . Выберите тип Дата. Столбец FullDateLabel содержит дату в полном формате, включая год, месяц и день. Убедитесь, что столбец FullDateLabel появился в иерархии в самом низу. Теперь у вас есть многоуровневая иерархия, которая включает год, квартал, месяц и отдельные календарные дни.
6. Оставаясь в представлении диаграммы, выберите таблицу DimProduct и нажмите кнопку Создать иерархию в заголовке таблицы. В нижней части таблицы появится пустой родительский узел иерархии.
7. Введите в качестве имени новой иерархии Product Categories.
8. Чтобы создать дочерние узлы иерархии, перетащите в иерархию столбцы ProductCategory и ProductSubcategory.
9. Щелкните правой кнопкой мыши ProductName и выберите пункт Добавить в иерархию . Выберите Product Categories.

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

1. Вернитесь назад в Excel .
2. На Листе1 (этот лист содержит сводную таблицу) удалите поля в области "Строки".
3. Замените их в новой иерархией Product Categories в DimProduct.
4. Аналогичным образом замените CalendarYear в области "Столбцы" иерархией Dates из DimDate.

Теперь при просмотре данных легко заметны преимущества иерархий. Вы можете независимо разворачивать и закрывать различные области сводной таблицы, что обеспечивает более полный контроль над использованием доступного места. Кроме того, добавление единой иерархии для областей "Строки" и "Столбцы" обеспечивает возможность мгновенной детализации без необходимости нагромождать несколько полей для получения аналогичного эффекта.

Скрытие столбцов

После создания иерархии Product Categories и ее размещения в DimProductDimProductCategory или DimProductSubcategory в списке полей сводной таблицы больше не нужны. В этой задаче вы узнаете, как скрыть лишние таблицы и столбцы, которые занимают место в списке полей сводной таблицы. Скрытие таблиц и столбцов позволяет оптимизировать работу с отчетами, не затрагивая модель, которая предоставляет связи и вычисления данных.

Можно скрыть как отдельные столбцы, так и их диапазон, а также всю таблицу. Имена столбцов и таблиц становятся неактивными, что отражает их скрытое состояние для клиентских средств подготовки отчетов, использующих модель. Скрытые столбцы отображаются в модели серым, но остаются видимыми в представлении данных, чтобы с ними можно было работать.

1. Убедитесь, что в Power Pivot выбрано представление данных.
2. На вкладках в нижней части экрана щелкните правой кнопкой мыши DimProductSubcategory и выберите.
3. Повторите для ProductCategories.
4. Откройте среду DimProduct.
5. Щелкните правой кнопкой мыши следующие столбцы и выберите пункт Скрыть в клиентских средствах .

• ProductKey
• ProductLabel
• ProductSubcategory

1. Выделите несколько смежных столбцов, начиная с ClassID и заканчивая ProductSubcategory. Щелкните правой кнопкой мыши, чтобы скрыть их.
2. Повторите это действие с другими таблицами, удалив идентификаторы, ключи и другие подробные сведения, которых не должно быть в отчете.

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

Создавать вычисляемые поля в Power Pivot легко, если воспользоваться функцией Автосумма .

1. В таблице FactSales выберите столбец Profit .
2. Выберите Вычисления > Автосумма . Обратите внимание, что было создано новое вычисляемое поле с названием Sum of Profit в ячейке области вычислений прямо под столбцом Profit .
3. В Excel на Листе1 в списке полей выберите в таблице FactSales вычисляемое поле Sum of Profit .

Готово! Как видите, с помощью стандартных агрегатных функций мы всего за несколько минут создали в Power Pivot вычисляемое поле Sum of Profit и добавили его в сводную таблицу. Теперь можно быстро анализировать прибыль, применяя различные фильтры. В этом случае вы видите столбец Sum of Profit, значения которого отфильтрованы согласно иерархиям Product Category и Dates.

Но что делать, если вам необходимо провести более углубленный анализ, например подсчитать продажи по определенному каналу, продукту или категории? Для этого вам нужно создать другое вычисляемое поле, которое вычисляет количество строк, по одной для каждой продажи из таблицы FactSales, в зависимости от используемых фильтров.

1. В таблице FactSales выберите столбец SalesKey .
2. В области Вычисления щелкните стрелку вниз под кнопкой Автосумма > СЧЁТ .
3. Переименуйте новое вычисляемое поле, щелкнув правой кнопкой мыши столбец Count of SalesKey в области вычислений и выбрав команду Переименовать . Введите Count и нажмите клавишу ВВОД.
4. В Excel на Листе1 в списке полей выберите FactSales и нажмите Count .

Обратите внимание, что в сводную таблицу был добавлен новый столбец Count , который отображает количество продаж в зависимости от применяемых фильтров. Как и в случае с вычисляемым столбцом Sum of Profit, вычисляемое поле Count отфильтровано согласно иерархиям Product Category и Dates.

Давайте потренируемся еще. На этот раз вы создадите вычисляемое поле, которое вычисляет процент от суммы продаж в соответствии с конкретным контекстом или фильтром. Однако в отличие от предыдущих вычисляемых полей, которые вы создавали с помощью функции "Автосумма", на этот раз необходимо будет ввести формулу вручную.

1. В таблице FactSales в области вычислений выберите пустую ячейку. Совет: удобнее всего будет размещать вычисляемые поля, начиная с ячейки в левом верхнем углу. Таким образом их будет легче найти. Вы можете перемещаться в любом вычисляемом поле в области вычислений.
2. В строке формул введите следующую формулу, используя IntelliSense: Percentage of All Products:=/CALCULATE(, ALL(DimProduct))
3. Нажмите клавишу ВВОД, чтобы подтвердить формулу.
4. В Excel на Листе1 в списке полей в таблице FactSales выберите Percentage of All Products .
5. В сводной таблице выберите несколько столбцов Percentage of All Products .
6. На вкладке Главная выберите Число > Процентный формат . Для форматирования новых столбцов используйте два десятичных знака после запятой.

Это новое вычисляемое поле вычисляет процент от суммы продаж для заданного контекста фильтра. В нашем случае фильтрами по-прежнему выступают иерархии Product Category и Dates. Среди прочего вы можете увидеть, что процент компьютеров от суммы продаж продуктов со временем увеличился.

Для вас не составит труда создавать формулы для вычисляемых столбцов и полей, если вы знакомы с процедурой создания формул Excel. Но, независимо от того, знакомы вы с формулами Excel или нет, у вас есть отличная возможность изучить основные формулы DAX, пройдя уроки из электронной книги Краткое руководство: основы DAX за 30 минут .

Сохранение работы

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

Следующие шаги

Хотя можно легко импортировать данные из Excel, часто быстрее и эффективнее оказывается импорт с помощью надстройки Power Pivot. Вы можете отфильтровать импортируемые данные, исключив ненужные столбцы. Вы также можете решить, будет ли выполняться извлечение данных с помощью построителя запросов или команды запроса. В качестве следующего шага изучите следующие альтернативные способы:Получение данных из веб-канала данных в Power Pivot и Импорт данных из служб Analysis Services или Power Pivot .

Отчеты Power View предназначены для работы с моделями данных, аналогичными только что построенной вами. Дополнительные материалы о расширенном представлении, которое Power View обеспечивает в Excel:Включение Power View в приложении Excel 2013 и Power View: исследование, визуализация и представление данных .

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

Миллионы пользователей используют формулы 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(>100000000, “Отлично”, if(>10000000, “Хорошо”, “Плохо”))

Поэтому во время работы можно встретить знакомые формулы, такие как ABS, AVERAGE, AVERAGEA (думаю, со временем их тоже переведут на русский язык). Так же есть совершенно новые: ALL, ALLEXEPT, AVERAGEX.

Визуализация

Для того чтобы создавать многофункциональные , PowerPivot использует инструменты Excel для визуализации. К счастью у Excel этого добра достаточно: сводные таблицы, сводные диаграммы и срезы.

После нескольких минут форматирования и размещения элементов управления, у меня получилась функциональная автономная книга, которая может работать как приложение.

Итог

Итак, что мы имеем? PowerPivot для Excel – это надстройка, используемая для добавления и анализа больших объемов данных. В Excel книга предоставляет все возможности визуализации данных и взаимодействия с ними. Навыки составления отчетов с помощью сводных таблиц позволят создавать сложные книги, основывающиеся на сложных аналитических данных Excel.

Те, кто часто в работе пользуются Сводными таблицами , уже смогли оценить насколько мощным инструментом они являются. Однако, к сожалению, сводные таблицы не могут делать отчеты из разных источников данных, будь то базы данных или файлы. Также, когда мы оперируем большими объемами данных, наши обычные компьютеры начинают не справляться с обработкой этих данных, и начинают "тормозить" или вообще зависают. Это касается таблиц, которые имеют более 300 тыс. строк. Итак, чтобы решить все эти проблемы, корпорация Microsoft разработала бесплатное приложение к Excel , которое расширяет возможности обычных Сводных таблиц .

Поэтому, представляем вам надстройку для MS Excel , которая позволит создавать супертаблицы - Power Pivot .

Так что такое PowerPivot?

Power Pivot - это дополнительная надстройка для Excel , которая расширяет функционал Сводных таблиц (Pivot Tables ). Используется только для MS Office 2010. В новой версии Office 2013 PowerPivot уже является неотъемлемым элементом Excel .

Как установить PowerPivot?

Перед установкой PowerPivot , вам необходимо (для Windows XP ):

После установки PowerPivot в Excel появляется дополнительная вкладка:

Нажав PowerPivot Window откроется отдельное окно, в которое можно загружать данные из разных источников.

После закачки данных из различных файлов Excel в вкладку PowerPivot Window , вы сможете работать с ними, и на обычном листе Excel , т.е. добавлять колонки, производить вычисления, используя формулы. Также, появляется уникальная как для Excel "я возможность, делать связи между загруженными таблицами как в Access . Используя таблицы со связями, мы можем делать Сводную таблицу , которая будет брать данные из разных таблиц, без предварительного их ручного объединения, без многократного использования VLOOKUP (ВПР ) , просто и быстро.

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

Также, как мы видим, в Сводной таблице появляются новые дополнительные поля, которые называются Визуальными фильтрами (Slicers ) .

Еще одной важной особенностью расширенных Сводных таблиц является ускоренная обработка больших таблиц (фильтрация, сортировка и т.п.), которые занимают более 1 млн. строк, что в стандартном Excel является почти невозможным.

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

Агрегаты - это способ группирования данных, свертывания данных и получения итоговых данных. В начале работы с необработанными данными из таблиц или других источников данных эти данные часто бывают неструктурированными, то есть представляют собой множество подробных данных, никак не упорядоченных и не сгруппированных. Такое отсутствие сводок или структуры может затруднить обнаружение закономерностей в данных. Таким образом, важную часть моделирования составляет определение агрегатов, которые упрощают и обобщают данные, выявляя закономерности, позволяющие решить поставленную бизнес-задачу.

Наиболее распространенные агрегаты, например, с помощью функции СРЗНАЧ , счёт , DistinctCount , Max , min или Sum , могут автоматически создаваться в измерении с помощью функции автосуммирования. Другие типы агрегатов, например AVERAGEX , COUNTX , CountRows или SUMX возвращают таблицу и требуют формулы, созданной с помощью выражений анализа данных (DAX) .

Основные сведения об агрегатах в Power Pivot

Выбор групп для агрегата

При агрегатной обработке данных они группируются по таким атрибутам, как продукт, цена, регион или дата, а затем определяется формула, работающая для всех данных в группе. Например, если создаются итоговые показатели за год, то это агрегат. Если создается соотношение этого года с предыдущим годом и данные представляются в виде процентов, то это другой тип агрегата.

Метод группировки данных определяется поставленным бизнес-вопросом. Например, агрегаты могут ответить на следующие вопросы.

Счетчики Сколько транзакций было выполнено за месяц?

Средние значения Какие показатели средних продаж в этом месяце у каждого менеджера по продажам?

Минимальные и максимальные значения Какие районы сбыта были в горячей пятерке по количеству проданного товара?

Чтобы создать вычисление, отвечающее на эти вопросы, необходимо иметь подробные данные с числами, которые следует подсчитать или суммировать, и эти числовые данные должны иметь определенную связь с группами, которые будут использоваться для сортировки результатов.

Если поступившие данные не содержат значений, которые можно использовать для группирования (таких как категория товара или географический регион, где расположен магазин), можно создать группы данных путем добавления категорий. При создании групп в Excel необходимо вручную ввести или выделить нужные группы из числа столбцов в рабочем листе. Однако в реляционных системах многие иерархии (например, категории продуктов) хранятся не в той таблице, где хранятся факты или значения. Обычно таблица категорий связана с данными фактов с использованием какого-либо ключа. Например, предположим, что в данных содержатся идентификаторы продуктов, но не их имена или категории. Чтобы добавить категорию в неструктурированный рабочий лист Excel, потребовалось бы скопировать столбец, содержащий названия категорий. Используя Power Pivot, можно импортировать таблицу с категориями продуктов в вашу модель данных, создать связь между таблицей с числовыми данными и списком категорий продуктов, затем использовать категории для группирования данных. Дополнительные сведения можно найти в разделе Создание связи между таблицами .

Выбор функции для агрегата

После определения и добавления групп необходимо решить, какие математические функции следует использовать для агрегирования. Часто слово "агрегат" используется в качестве синонима математических или статистических операций, применяемых в агрегатах, таких как суммирование, определение средних значений, определение минимума или подсчет. Тем не менее Power Pivot позволяет создавать пользовательские формулы для агрегирования в дополнение к стандартным агрегатам и в Power Pivot и в Excel.

Например, при наличии того же набора значений и группирований, использованных в предыдущих экземплярах, можно создать пользовательские агрегаты, которые могут ответить на следующие вопросы.

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

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

Сгруппированные минимальные и максимальные значения Какие районы сбыта были ведущими для каждой категории продукта или для каждого стимулирования сбыта?

Добавление агрегатов к формулам и сводным таблицам

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

Добавление группирований в сводную таблицу

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

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

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

Работа с группированиями в формуле

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

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

Дополнительные сведения о создании формул с подстановками см. в статье Подстановка в формулах PowerPivot .

Использование фильтров в агрегатах

Новой функцией Power Pivot является возможность применения фильтров к столбцам и таблицам данных не только в пользовательском интерфейсе и в сводной таблице или диаграмме, но также и в каждой формуле, используемой для вычисления агрегатов. Фильтры можно использовать в формулах в вычисляемых столбцах и в s.

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

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

Дополнительные сведения см. в статье Фильтрация данных в формулах .

Сравнение агрегатных функций Excel с агрегатными функциями DAX

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

Стандартные агрегатные функции

Агрегатные функции DAX

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

В следующей таблице перечислены агрегатные функции, доступные в DAX.

Различия между агрегатными функциями DAX и Excel

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

Способ вычисления столбцов в статистическом выражении также отличается от способа обработки статистических выражений в Excel. Проиллюстрировать это поможет пример.

Предположим, требуется получить сумму значений в столбце Amount таблицы Sales, для чего создается следующая формула:

SUM("Sales")

В самом простом случае функция возвращает значения из одного неотфильтрованного столбца, и результат будет таким же, как в приложении Excel, в котором всегда просто суммируются значения в столбце Amount. Тем не менее в Power Pivot формула интерпретируется как "Получить значение в столбце Amount для каждой строки таблицы Sales и затем сложить эти отдельные значения". Power Pivot вычисляет каждую строку, для которой выполняется агрегирование, и вычисляет единичное скалярное значение для каждой строки, а затем агрегирует эти значения. Поэтому результат формулы может быть разным, если к таблице применялись фильтры или если значения вычислялись на основе других агрегатов, где могли использоваться фильтры. Дополнительные сведения см. в статье Контекст в формулах DAX .

Функции логики операций со временем DAX

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

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

Другие функции в разделе "функция логикиопераций со временем" - функции, которые можно использовать для получения дат или настраиваемых диапазонов дат для использования в агрегате. Например, с помощью функции DATESINPERIOD можно получить диапазон дат и использовать этот набор дат в качестве аргумента другой функции для вычисления пользовательского агрегата только по этим датам.

Microsoft Excel 2019/2016. Level 6. Business Intelligence with PowerPivot, PowerView and PowerMap

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

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

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

PowerPivot, PowerQuery, PowerView и PowerMap – это мощные современные инструменты анализа данных в Microsoft Excel!

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

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

Обратите внимание на необходимое ПО для каждой версии Excel!

Для 2010 :

1. Microsoft Power Pivot для Excel

Для 2013 :

1. Microsoft Power Query для Excel
2. Power Map Preview for Excel 2013
3. Microsoft Silverlight

Для 2016/2019

1. Microsoft Silverlight

ВАЖНО! Данные надстройки не поддерживаются в Excel для Mac.