Excel powerpivot курсы. С помощью PowerPivot в Microsoft Excel. Основные сведения об агрегатах в Power Pivot

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

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

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

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

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

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

деинсталлировать MS Office 2010

установить SP3 для Windows XP

установить .NET Framework 4.0 та Visual Studio 2010 Tools for Office Runtime

установить полностью MS Office 2010 (со всеми программами и общими средствами)

установить PowerPivot

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

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

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

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

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

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

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

Миллионы пользователей используют формулы 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.

office.microsoft

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вы создали сводную таблицу, которая содержит данные из нескольких таблиц, импортированных на предыдущем этапе. Чтобы свести данные воедино, потребовалось создать связи таблиц, которые 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.

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

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

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

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

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

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

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

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

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

= - -

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

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

RELATED(ProductCategories)

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

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

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

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

RELATED(DimProductSubcategory)

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

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

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

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

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

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

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

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

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

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

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

ProductKey
ProductLabel
ProductSubcategory

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

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

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

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

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

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

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

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

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

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

Это новое вычисляемое поле вычисляет процент от суммы продаж для заданного контекста фильтра. В нашем случае фильтрами по-прежнему выступают иерархии 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 .

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

Список версий Office, включая Power Pivot, а также список версий, которые не рекомендуется, ознакомьтесь со статьей: где такое PowerPivot?

Ниже описано, как включить Power Pivot перед использованием в первый раз.

Перейдите на вкладку Файл > Параметры > Надстройки .

В поле Управление выберите Надстройки COM и нажмите Перейти .

Установите флажок Microsoft Office Power Pivot и нажмите кнопку ОК . Если установлены другие версии Power Pivot, то они будут также перечислены в списке надстроек COM. Выберите надстройку Power Pivot для Excel.

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

Откройте окно Power Pivot.

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

Устранение неполадок: исчезновение ленты Power Pivot

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

Выберите Файл > Параметры > Надстройки .

В поле Управление выберите Отключенные объекты > Перейти .

Выберите Microsoft OfficePower Pivot и нажмите кнопку Включить .

Если не удается восстановить ленту Power Pivot, выполнив указанные выше действия, или лента исчезает, когда вы закрываете и снова открываете Excel, сделайте следующее:

закройте Excel;

откройте меню Пуск > Выполнить и введите команду regedit;

В редакторе реестра разверните следующий раздел:

Для Excel 2013: HKEY_CURRENT_USER > Software > Microsoft > Office > 15.0 > User Settings .

Для Excel 2016: HKEY_CURRENT_USER > Software > Microsoft > Office > 16.0 > User Settings

щелкните правой кнопкой мыши PowerPivotExcelAddin , а затем нажмите Удалить ;

вернитесь в верхнюю часть редактора реестра;

разверните раздел HKEY_CURRENT_USER > Software > Microsoft > Office > Excel > Addins ;

щелкните правой кнопкой мыши PowerPivotExcelClientAddIn.NativeEntry.1 , а затем нажмите Удалить ;

закройте редактор реестра;

откройте Excel;

включите надстройку, выполнив действия, описанные в начале этой статьи.

[в связи со спорным переносом 1 части поста на geektimes (при том что 2-я часть осталась на хабре) возвращаю 1-ю часть на место]

Работая в сфере аналитики и мониторя различные инструменты BI рано или поздно наталкиваешься на обзор или упоминание надстройки Power Pivot Excel. В моем случае знакомство с ним произошло на конференции Microsoft Data Day.

Особых впечатлений после презентации инструмент не оставил: Да, бесплатен (в рамках лицензии Office), да - есть некий ETL функционал в части получения данных с разрозненных источников (БД,csv,xls, и т.д.), Join-ов этих источников и скармливания в оперативку записей на порядки выше 1 млн.строк в Excel. Короче, посмотрел и забыл.

А вспомнить пришлось, когда появилась необходимость идентификации определённых явлений в данных

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

Собственно, постановка задачи (на обезличенном примере) следующая:

В исходных данных csv файла:

Есть торговые точки, детализированные до строк накладных, при этом допускается для точек с одинаковым наименованием иметь разные адреса только в том случае если они расположены в разных городах, но в исходном массиве данных есть точки, у которых попадаются разные адреса в одном и том же городе при том, что названия точек одинаковые (имя торговой точки уникально, т.е. это единица сети или отдельно стоящая точка). Как частный случай в агрегированном виде:

Поиску и очистке данных штатными средствами office мешают следующие обстоятельства:

Детализация данных до строк накладной
Количество записей в несколько миллионов строк
Отсутствие sql инструментария (К примеру: Access - не в комплекте)

Конечно можно залить любую бесплатную СУБД (хоть десктоп версию, хоть серверную) но для этого во-первых нужны админские права, во-вторых статья была бы уже не про Power Pivot.

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

Думаю, удобнее всего решать и рассказывать итерационно, с допущением что у нас знания по DAX в зачаточном уровне.
Поэтому предлагаю пока оторваться от задачи и рассмотреть некоторые базовые аспекты.

Шаг 1. Чем отличается вычисляемый столбец от вычисляемой меры?
Вот пример вычисляемого столбца для выделения НДС из поля отгрузки с НДС используя встроенные формулы DAX:

ROUND([Отгрузка с НДС]*POWER(1,18;-1)*0,18;2)

Как видно из примера вычисляемый столбец (Назовем его НДС) работает с каждой атомарной записью по горизонтали.
Теперь добавим вычисляемое поле для цены за штуку без НДС:

ROUND([Отгрузка с НДС]*POWER(1,18;-1)/[Отгрузка шт];2)

Теперь для сравнения добавим в меру расчет средней цены за штуку:

Средняя цена за штуку без НДС: =ROUND(AVERAGE([Поле_Цена за штуку без НДС]);2)

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

При возврате в сводную таблицу Excel это выглядит так:

Обратите внимание, если вычисляемое поле НДС на каждом уровне данных (зеленая обводка на уровне торговой точки, города или итого по таблице) показывает сумму, что в принципе – корректно, то сумма цен вычисляемого поля «Цена за штуку без НДС» (красная обводка) вызывает вопросы.
А вот вычисляемая мера «Средняя цена за штуку без НДС» вполне имеет право на жизнь в рамках данного аналитического куба.

Отсюда делаем вывод, что вычисляемое поле «Цена за штуку без НДС» является вспомогательным инструментом для расчета меры «Средняя цена за штуку без НДС» и дабы не смущать пользователя этим полем мы скроем его из списка клиентских средств, оставив меру средней цены.

Еще одно отличие меры от столбца – она позволяет добавить визуализацию:

К примеру, построим KPI степени разброса цен с целевой границей 35% путем деления корня из дисперсии на среднюю арифметическую.

К_вар:=STDEV.P([Поле_Цена за штуку без НДС])/AVERAGE([Поле_Цена за штуку без НДС])

В итоге видим такую таблицу в Excel (кстати расчетное вспомогательное поле цен уже не в списке доступных полей справа):

Двойной клик на 80%-м коэффициенте показывает, что цены действительно колбасит вокруг средней:

Cильнее чем при коэффициенте 15%:

Итак, на данном шаге мы рассмотрели основные отличия мер от полей в рамках PowerPivot.

Шаг 2. Усложняем: Посчитаем долю каждой записи в общих продажах.
Вот первый пример сравнения подходов оконных функций MS SQL Server и DAX:

Понятно, что в рамках сводных таблиц это делается буквально в 2 клика мышкой не касаясь клавиатуры, но для понимания попробуем это непосредственно в PowerPivot с применением формул.

На sql я бы это написал так (за огрехи не пинать, ибо Word синтаксис SQL Server не проверяет):

Begin Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Отгрузка, шт"/sum("t1.Отгрузка, шт") over () as share from Table as t1 order by "t1.Отгрузка, шт"/sum("t1.Отгрузка, шт") desc

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

=[Отгрузка шт]/CALCULATE(SUM([Отгрузка шт]);ALL("Таблица1"))

Основное внимание обратим к знаменателю: Я уже упоминал выше что основное отличие вычисляемого поля от меры заключается в том что в поле формулы считают по горизонтали (в рамках одной записи) а меры – по вертикали (в рамках одного атрибута). Здесь мы смогли скрестить свойства поля и свойство меры через метод CALCULATE. И если ширину окна в SQL мы отрегулировали через Over() то здесь мы сделали это через All().

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

Оконные функции на sql будут смотреться так:

Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Цена за шт без НДС", CASE WHEN ABS("t1.Цена за шт без НДС" - AVG("t1.Цена за шт без НДС") OVER()) > 3 * STDEV("t1.Цена за шт без НДС") OVER() THEN 1 ELSE 0 END as Outlier from Table as t1 Go

А вот то же самое в DAX:

If(ABS([Поле_Цена за штуку без НДС]-CALCULATE(AVERAGE([Поле_Цена за штуку без НДС]);ALL("Таблица1")))>(3*CALCULATE(STDEV.P([Поле_Цена за штуку без НДС]);all("Таблица1")));1;0)

Как видите, цена несколько высоковата при средней арифметической 40,03 руб.

Шаг 3. Сужаем окна.
Попробуем теперь посчитать в вычисляемом поле каждой записи общее количество записей в рамках того города, к которому принадлежит и данная запись.
На MS sql Server оконные функции будут выглядеть так:

Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Цена за шт без НДС", count("t1.*) OVER(partition by "t1.Город") as cnt from Table as t1 Go

В DAX:
=CALCULATE(COUNTROWS("Таблица1");ALLEXCEPT("Таблица1";"Таблица1"[Город]))

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

Отчетлива видна разница: если обычный расчет количества адресов идет для каждой точки в городе и потом только выводит промежуточный итог для агрегата «Город» то использование оконных функций позволяет присвоить каждой атомарной записи значение любого агрегата, либо использовать его в каких-то промежуточных расчетах вычисляемого поля (как было показано выше).

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

Запрос на SQL Server:

With a1 as (Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Цена за шт без НДС", count(Distinct "t1.Адрес") OVER(partition by "t1.Город", "t1.Имя ТТ") as adrcnt from Table as t1) Select * from a1 where adrcnt>1

Теперь нам ничего не мешает это сделать и в DAX:

CALCULATE(DISTINCTCOUNT("Таблица1"[Адрес]);ALLEXCEPT("Таблица1";"Таблица1"[Город];"Таблица1"[Имя ТТ]))

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

Конечно в процессе изучения (пробежавшись взглядом на другие формулы) становится понятно что DAX в PowerPivot гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.

Надеюсь было интересно.
Продолжение статьи