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 .

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

Наиболее распространенные агрегаты, например, с помощью функции СРЗНАЧ , счёт , 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 можно получить диапазон дат и использовать этот набор дат в качестве аргумента другой функции для вычисления пользовательского агрегата только по этим датам.

[в связи со спорным переносом 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 гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.

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

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

Что такое Power Pivot

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

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

Надстройка Excel Power Pivot работает только в среде Office 2010 и выше и обладает рядом возможностей:

• выполняет все вычисления непосредственно в оперативной памяти компьютера, что позволяет добиться высокой производительности системы, а также обеспечить возможность обработки больших объемов данных (размеры исходных таблиц могут достигать миллионов строк);
• позволяет создавать на своей базе сводные таблицы. Прикладная ценность Excel PowerPivot для финансистов и аналитиков заключается в том, что с помощью надстройки теперь можно самостоятельно обрабатывать такие объемы информации, для которых ранее требовалось использовать специализированное ПО (СУБД) и привлекать квалифицированных ИТ-специалистов;
• содержит язык выражений анализа данных (Data Analysis eXpression, сокращенно DAX), предназначенный для разработки новых правил бизнес-логики. Упрощенно его можно считать продвинутым вариантом формул массива. Формулы языка DAX специально разрабатывались таким образом, чтобы быть максимально приближенными к синтаксису Excel. Несмотря на свое сходство со стандартными функциями листа Excel, формулы DAX составляются только внутри оболочки Power Pivot. Язык DAX обладает широким спектром возможностей, а информация о различных способах его применения доступна в интернете.

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

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

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

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

Справка

Использование формул массива

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

Давайте рассмотрим выражение (см. рис. 1). Входными параметрами у него выступают два диапазона ячеек: «Строка», выделенная желтым цветом, и «Столбец» с синей заливкой. В области, ограниченной этими диапазонами, введена формула массива следующего вида: {=Строка Столбец}.

Эта формула - компактная запись операции сложения пар элементов из исходных массивов. Как видно из рисунка, у нее одинаковое написание для всех элементов диапазона из области действия. При этом значения в ячейках получившейся таблицы отличаются друг от друга, что объясняется контекстом исполнения. Например, выделенная на рисунке ячейка E4 расположена в третьем столбце и второй строке массива, что соответствует второму элементу массива «Строка» и третьему элементу массива «Столбец».

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

Работа со стандартными формулами Excel

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

На рисунке 2 исходные данные представлены столбцами «Компания», «Услуга», «Период», «Доход». Мы хотим создать два новых вычисляемых столбца «Доход_Сумм», где рассчитан совокупный доход по клиенту в определенном периоде, а также «Доход_Уровень», который в зависимости от уровня «Доход_Сумм» определяет доходную группу (уровни дохода указаны в правой части рис. 2).

Для удобства и наглядности последующих вычислений сразу преобразуем исходные данные в режим «Таблицы». Для этого воспользуемся соответствующей командой на вкладке «Вставка» и присвоим ей собственное имя «Таблица_Фактов».

Наша цель. Для каждой строки таблицы фактов рассчитать суммарный доход, который соответствует клиенту и месяцу. Например, «Клиенту, А» в феврале 2013 года было оказано три услуги на сумму 15, 15 и 25 единиц. Поэтому в каждой из строк, которые относятся к данному периоду, должно быть проставлено одно и то же значение - 55 единиц (15 + 15 + 25). Такая операция выполняется в два этапа.

Этап 1. Среди всех строк таблицы фактов фильтруются те, у которых с текущей строкой совпадают значения атрибутов «Компания» и «Период».

Этап 2. Суммируются значения атрибута «Доход» в получившемся отфильтрованном множестве.

Для выполнения данной задачи хорошо подходит функция СУММЕСЛИМН, позволяющая суммировать диапазоны сразу по нескольким условиям. Главный вопрос: каким образом вводить эту формулу в вычисляемый столбец. Ведь в таблицах любая формула должна задаваться сразу для всех строк одновременно. Нам же требуется определить всего одну, но такую, чтобы контекст ее исполнения менялся в зависимости от параметров активной строки.

Введем в любую из строк столбца «Доход_Сумм» выражение 1. Достаточно нажать Enter, после чего оно поместится во всех строках таблицы.

Выражение 1:
СУММЕСЛИМН([Доход]; [Компания]; Таблица_Фактов[[#Эта строка];[Компания]]; [Период]; Таблица_Фактов[[#Эта строка]; [Период]])

Разберем, как работает формула. При каждом вызове (в каждой строке) функции СУММЕСЛИМН используется сразу вся таблица фактов (ее столбец «Доход»). Но при каждой итерации суммируются только те строки, которые соответствуют значениям атрибутов текущей строки. Для этого в формулу добавлен специальный аргумент - [# Эта строка], который играет роль ссылки на текущую строку. В частности, для строк, относящихся к «Клиенту, А» и февралю 2013, значение параметра [[#Эта строка];[Компания]] будет равным «Клиент, А», а [[#Эта строка]; [Период]] - «01.02.2013».

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

Отметим, что представленный формат записи существенно отличается от штатного режима Excel, где входные параметры указываются в формулах явным образом. Например, мы можем преобразовать таблицу фактов обратно в обычный диапазон («Преобразовать в диапазон» на вкладке «Работа с таблицами»). Тогда, к примеру, выражение 1 для строки 5 примет вид выражения 2:

Выражение 2:
=СУММЕСЛИМН(Обычный_Диапазон!$D$2:$D$37; Обычный_Диапазон!$A$2:$A$37; Обычный_Диапазон!$A5; Обычный_Диапазон!$C$2:$C$37; Обычный_Диапазон!$C5)

Для строки 6 выражение будет выглядеть идентично, с тем лишь отличием, что вместо $A5 будет $A6 (а вместо $C5 - $C6). При этом результат вычисления выражения 2 совпадает со значением выражения 1 в соответствующей строке, что позволяет считать их тождественными.

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

В частности, функция ИНДЕКС позволяет в любой ячейке рабочей книги элементарно получить значение из двумерного массива, нам нужно лишь знать соответствующий идентификатор объекта. Напишем в столбце «Доход_Уровень» выражение 3:

Выражение 3:
=ИНДЕКС(Уровни[Уровень]; ПОИСКПОЗ(Таблица_Фактов[[#Эта строка]; [Доход_Сумм]]; Уровни[Начало]))

Представленная в выражении 3 формула, как и выражение 1, выполняется в текущем контексте таблицы фактов - в нем используется ссылка на активную строку. Кроме того, при помощи функции ИНДЕКС мы обращаемся к внешнему объекту - таблице «Уровни». В ней с помощью функции ПОИСКПОЗ ищется подходящая строка.

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

Работа в Excel Power Pivot

Рассмотрим, как в Power Pivot выполняются операции, описанные в первой части статьи.

Установка Excel Power Pivot. Сначала надстройку следует установить (скачать ее можно бесплатно с официального сайта Microsoft). На ленте Excel должна появиться новая вкладка «Power Pivot». Если надстройка не включилась сразу, ее нужно включить в ручном режиме: «Параметры Excel» - «Надстройки», в списке «Управление» выбрать значение «Надстройки COM» - «Перейти». Остается поставить галочку напротив пункта «Power Pivot for Excel». Окно активно только в документах формата xlsx.

Подготовка исходных данных . В БД PowerPivot поддерживается широкий перечень разнообразных источников. В частности, в нее можно загрузить данные из СУБД MS Access или SQL Server. В этом случае объем обрабатываемой информации может исчисляться миллионами строк. Но для обычных пользователей более ценной является возможность хранить исходную информацию на листах обычной книги Excel.

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

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

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

При копировании исходных данных на новые листы последним рекомендуется сразу присваивать информативные названия. При последующем импорте данных в среду Power Pivot исходные имена объектов становятся названиями соответствующих таблиц в модели.

Далее создаем новую книгу Excel, которую назовем «ИсхДанные». Поместим в нее на один лист с названием «Таблица_Фактов» таблицу с исходными данными, а на другой лист «Уровни» - справочную таблицу с уровнями доходности.

Теперь давайте создадим еще одну книгу Excel, вызовем в ней окно PowerPivot и импортируем в нее данные из файла «ИсхДанные». Для импорта данных нужно вызвать в меню «Файл» команду «Получить внешние данные из других источников», а затем тип «Файл Excel», запустив мастер импорта данных.

На первом этапе его работы указываем физическое размещение файла с данными. Советуем сразу включить опцию «Использовать первую строку для заголовков столбцов» - тогда столбцы PowerPivot в автоматическом режиме получат имена столбцов из книги Excel.

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

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

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

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

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

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

Как и в обычных таблицах, формулы DAX выполняются в контексте строки. Но в отличие от таблиц, в них нет необходимости указывать параметр [# Эта строка] явным образом. Проще говоря, даже не записывая параметр [# Эта строка] в формулу DAX, можно считать, что он в ней присутствует, и использовать его в вычислениях. Такая особенность хоть и делает формулы DAX менее наглядными, но зато позволяет записывать выражения более компактно.

Учитывая перечисленные особенности работы с формулами DAX, создадим новый столбец «Доходность» со следующей формулой (выражение 4):

Выражение 4:
=CALCULATE(sum([Сумма]); ALLEXCEPT("Таблица_Фактов"; "Таблица_Фактов"[Компания]; "Таблица_Фактов"[Период]))

Разберем принцип работы выражения 4.

Сначала данный оператор для каждой строки таблицы фактов получает на вход временную таблицу, совпадающую с самой таблицей фактов. Затем к этой промежуточной таблице применяются фильтры, определенные текущим контекстом. В ней остаются строки, атрибуты которых совпадают со значениями текущей строки: [[#Эта строка]; [Компания]], [[#Эта строка]; [Услуга]], [[#Эта строка]; [Период]], [[#Эта строка]; [Сумма]].

Однако выражение ALLEXCEPT ("Таблица_Фактов"; "Таблица_Фактов" [Компания]; "Таблица_Фактов" [Период]) требует, чтобы для всех столбцов, кроме [Компания] и [Период], фильтры текущего контекста были убраны. Получается, что в промежуточной таблице мы оставляем только те строки, у которых с текущей строкой совпадают значения атрибутов [Компания] и [Период]. В отфильтрованной таким образом таблице выбирается атрибут [Сумма], значения которого затем суммируются.

На первый взгляд, формула может показаться чересчур запутанной, но в действительности она лишь воспроизводит вычисления выражения 1, которое мы приводили в самом начале статьи. Значения в столбце «Доходность» будут наглядным подтверждением, поскольку они совпадают со столбцом «Доход_Сумм» на рис. 2.

Теперь добавим в таблицу еще один вычисляемый столбец - «Техн_Уровень». В нем напротив каждой рассчитанной на первом этапе суммы укажем численное значение шкалы из таблицы «Уровни». Фактически нам требуется в базовой «Таблице_Фактов» получить значения из другой таблицы. Как и в случае с обычными расчетами в Excel, для этого достаточно выбрать подходящую функцию и передать ей в качестве аргумента идентификатор нужного объекта.

Введем в столбец «Техн_Уровень» следующую формулу (выражение 5):

Выражение 5:
=CALCULATE(max("Уровни"[Начало]);filter(all("Уровни"[Начало]);"Уровни"[Начало]

В представленном выражении агрегирующая функция MAX() применяется уже к внешней таблице «Уровни». Сначала она фильтруется в соответствии с ограничениями текущего контекста - в таблице оставляются только те строки, которые меньше числа в столбце «Доходность» активной строки.

Наконец, давайте добавим в нашу таблицу последний вычисляемый столбец - «Дох_Уровень». В нем мы будем выводить строку с названием уровня из таблицы «Уровни». Такие операции выполняются с помощью функций ВПР в Excel и ГПР (VLOOKUP и HLOOKUP). В языке DAX существует функция LOOKUPVALUE, аналогичная оператору ВПР.

Поэтому в столбец «Дох_Уровень» достаточно записать простое выражение следующего вида (выражение 6):

Выражение 6:
=LOOKUPVALUE("Уровни"[Уровень]; "Уровни"[Начало]; [Техн_Уровень])

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

Для ограничения множества аналитик в Excel воспользуемся командой «Скрыть из набора клиентских средств» (для этого достаточно кликнуть правой кнопкой мыши на нужном нам столбце и выбрать соответствующий пункт в контекстном меню). Скрытые столбцы в конструкторе моделей закрашиваются серым цветом. Для перехода в среду Excel и работы с получившимся сводным отчетом достаточно выбрать команду «Переключиться в книгу Excel».

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

Справочную информацию по работе с надстройкой PowerPivot и языком DAX, в том числе руководство «Обучение основам DAX за 30 минут» на официальном сайте MS Office, можно найти в интернете.

Бесплатно распространяемая надстройка PowerPivot появилась в версии Excel 2010. Она включает шесть весьма полезных инструментов и создана независимыми разработчиками. В Excel 2013 функции PowerPivot наряду с моделью данных встроены непосредственно в ядро программы.

Модель данных, которая рассматривалась , предоставляет доступ лишь к некоторым возможностям PowerPivot, а для получения доступа ко всем инструментам придется установить саму надстройку PowerPivot, входящую в комплект поставки Office 2013 Professional Plus. Надстройку можно также получить, подписавшись на Office 365 Business. Если же вы пользуетесь версией Office 2013 Home and Student либо Standard, механизм PowerPivot по-прежнему доступен, однако окно PowerPivot заблокировано, а доступ к некоторым возможностям закрыт.

Скачать заметку в формате или , примеры в (содержит несколько файлов Excel и базу данных в формате txt)

Преимущества и недостатки PowerPivot и модели данных

Начнем с рассмотрения трех основных возможностей PowerPivot и проанализируем, каким образом они реализованы в каждой версии Excel.

Создание сводных таблиц на основе нескольких исходных таблиц без использования функции ВПР. Эта возможность доступна во всех версиях Excel 2013. В версии Standard вы не увидите слово PowerPivot (вместо него используется фраза модель данных ). Если же установлена надстройка PowerPivot, создание связей между таблицами легко выполняется с помощью графического представления.

Импорт 100 млн. строк в рабочую книгу. Таблицы PowerPivot могут включать неограниченное число строк. Мне приходилось видеть подобные таблицы, состоящие из 100 млн. строк. Единственное ограничение - размер файла книги, равный 2 Гбайт, и объем оперативной памяти. Благодаря использованию алгоритма сжатия VertiPaq текстовый файл объемом 50 Мбайт сжимается до 4 Мбайт, занимаемых файлом с таблицей PowerPivot. Если количество столбцов в подобной таблице равно 10, количество строк будет составлять 950 млн. Эта возможность доступна во всех версиях Excel 2013. В версии Office Standard можно импортировать записи из таблицы PowerPivot и создавать сводные таблицы, но при этом не допускается просмотр записей. Для выполнения просмотра следует установить надстройку PowerPivot. Невозможность просмотра записей сводной таблицы является серьезным неудобством, поскольку перед созданием любого отчета будет нелишним просмотреть входящую в этот отчет информацию.

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

Преимущества модели данных PowerPivot, доступные во всех версиях Excel. Ниже приведен ряд дополнительных преимуществ, обеспечиваемых моделью данных:

• Появилась функция вычисления итогов Число различных элементов (см., например, ). До появления этой функции приходилось определять число вхождений различных элементов вручную.
• В области итогов сводной таблицы могут учитываться только отфильтрованные или все данные. Для начала при создании сводной таблицы включите данные в модель данных (рис. 1). В сводной таблице выберите фильтр Первые 10… В области итогов суммируются показатели, соответствующие первой десятке (рис. 2а). Теперь можно создать итог, включающий показатели для заказчиков с небольшими суммами заказов, которые были отфильтрованы при создании отчета. Перейдите на контекстную вкладку Конструктор и в раскрывающемся меню Промежуточные итоги выберите опцию Включить отобранные фильтром элементы в итоги (рис. 2б). Обратите внимание, если не создать модель данных, эта опция промежуточных итогов будет недоступна для выбора. Также обратите внимание, что в названии Общий итог появилась звездочка (*).
• В сводных таблицах Excel 2010 появился новый замечательный инструмент - наборы, с помощью которых обеспечивается создание асимметричных отчетов. Единственное ограничение наборов - возможность их использования исключительно при работе со сводными таблицами OLAP (подробнее см. ). Если же открыть обычную таблицу с помощью надстройки PowerPivot, она автоматически преобразуется в сводную таблицу OLAP.
• Те, кто привыкли использовать функцию для извлечения данных из сводных таблиц, могут выполнить на одно действие меньше и преобразовать сводную таблицу в формулы по кубу. Эти формулы можно вырезать и вставить в любом выбранном вами формате.

Рис. 2. Включение в итоги ранее отфильтрованных значений: (а) итоги включают доход по 10 лучшим заказчикам; (б) итоги включают доход по всем заказчиках, хотя отражены в сводной таблице только первые 10

Преимущества надстройки PowerPivot в версии Excel Pro Plus. Если вы пользуетесь версией Excel Pro Plus, которая поддерживает надстройку PowerPivot, вы получите следующие преимущества:

• Таблица PowerPivot, в которой может содержаться до 100 млн. строк.
• Можно сортировать, фильтровать и добавлять вычисления в таблицу.
• Режим графического проектирования, в котором можно создавать связи путем перетаскивания полей.
• Возможность изменения свойств поля в модели. Можно выбирать поля, которые отображаются либо не отображаются в списке полей сводной таблицы.
• Возможность отсортировать элемент ПолеА (название месяца) по элементу ПолеБ (номер месяца).
• Возможность задать числовой формат по умолчанию, применяемый при отображении полей в сводной таблице. Эта возможность недоступна в обычной сводной таблице.
• Возможность определения полей, которые представляют товар, географический регион либо являются ссылками на изображения.
• Некое подобие ключевых показателей эффективности. Эти показатели проще применять в сводных таблицах, чем наборы значков.
• Возможность доступа к панелям Power View и Power Map (подробнее см. ).

Ограничения модели данных. Модель данных позволяет встраивать обычные данные Excel в модель OLAP. Эта операция сопряжена с некоторыми преимуществами и недостатками. Разработчики Excel 2013 постарались устранить ограничения и недостатки модели данных, но некоторые из них все равно остались:

• Ограниченный набор функций вычисления итогов. Несмотря на появление таких новых функций, как Число различных элементов , были утрачены такие функции, как Произведение .
• Отсутствие группировки. В PowerPivot отсутствует возможность группировки сводных таблиц. В частности, вы не сможете группировать ежедневные данные по месяцам, кварталам и годам. Можно, конечно, выполнить группирование путем добавления соответствующих вычислений в исходный набор данных, но все же проще было бы воспользоваться специальной функцией группирования.
• Необычный просмотр таблицы. Для просмотра строк классической сводной таблицы было достаточно дважды щелкнуть на ячейке этой таблицы. При работе с моделью данных это действие приведет к отображению первой тысячи строк.
• Отсутствие вычисляемых попей или вычисляемых элементов. В модели данных не поддерживаются вычисляемые поля либо вычисляемые элементы. Если установлена надстройка PowerPivot, меры DAX позволяют выполнять подобные вычисления. Если же надстройка PowerPivot не установлена, вы не сможете применять вычисляемые поля либо вычисляемые элементы.
• Поддержка только в версии Excel 2013. Рабочие книги, использующие модель данных, не могут загружаться в более ранних версиях Excel.
• Плохо реализованная сортировка. При использовании обычных сводных таблиц январь всегда следует перед февралем. Корректная сортировка в Excel основана на использовании пользовательских списков, составленных для названий месяцев и дней недели. В сводных таблицах, основанных на модели данных, по умолчанию не используются пользовательские списки. Чтобы устранить эту проблему, потребуются восемь дополнительных щелчков мышью в каждом поле сводной таблицы.

Объединение нескольких таблиц с помощью модели данных в Excel 2013

Специалисты из компании Microsoft внедрили лучшие инструменты PowerPivot в Excel 2013. Также доступна версия Office Pro Plus с полнофункциональными модулями PowerPivot, Power View и Inquire. В стандартных версиях Excel 2013 базовые возможности PowerPivot реализованы с помощью модели данных. На рис. 1 представлено диалоговое окно Создание сводной таблицы . Наличие в этом окне флажка означает, что вы имеете дело с PowerPivot. Подробнее об объединении нескольких таблиц в одной сводной с помощью модели данных см. . Эта функция работает как в том случае, когда у вас установлен модуль PowerPivot, так и в случае использования встроенных возможностях Excel.

Использование экспресс-просмотра. После выбора произвольной ячейки сводной таблицы, созданной на основе модели данных, появится значок экспресс-просмотра (рис. 3). Этот значок могут видеть только пользователи Excel 2013, и он отображается только в том случае, если сводная таблица создана на основе модели данных. Экспресс-просмотр предназначен для оказания помощи пользователю, который собирается изменить сводную таблицу. После щелчка на этом значке появится окно, в котором отображаются подсказки по изменению сводной таблицы.

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

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

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

1. Выберите команду Вставка — Сводная таблица .
2. В диалоговом окне Создание сводной таблицы установите переключатель Использовать внешний источник данных (рис. 5а). Даже если данные хранятся в таблице PowerPivot, находящейся в рабочей книге, не забывайте о том, что PowerPivot изначально была внешней надстройкой.
3. Щелкните на кнопке Выбрать подключение Существующие подключения .
4. Выберите вкладку Таблицы .
5. Выберите пункт Таблицы в модели данных книги и щелкните Открыть (рис. 5б).

Подсчет количества различных элементов. Одна из таких возможностей сводных таблиц - подсчет количества уникальных записей. В сводных таблицах Excel могут подсчитываться текстовые значения. Типичный пример подобной сводной таблицы показан на рис. 6. В этой таблице поле Сектор находится в области СТРОКИ, а поля Заказчик и Доход - в области ЗНАЧЕНИЯ. Итоговое количество заказчиков, отображаемое этой сводной таблицей, равно 563, что не соответствует действительности. На самом деле в данном случае речь идет о 563 непустых записях, соответствующим заказчикам, которые могут повторяться. Подобная накладка связана с ограничениями обычных сводных таблиц.

Если сводная таблица основана на модели данных, выполните следующие действия.

1. Кликните правой кнопкой мыши на любой из ячеек в области Число элементов в столбце Заказчик (диапазон В1:В9). Выберите пункт Параметры полей значений .
2. В окне Параметры поля значений на вкладке Операция выберите Число различных элементов (обратите внимание, что по сравнению с обычными сводными таблицами отсутствуют функции Произведение и Индекс ). Щелкните ОК.
3. Сводная таблица отображает список, состоящий из 27 уникальных заказчиков, 11 из которых относятся к сектору Оборудование (рис. 7).

Использование надстройки PowerPivot в Excel 2013 Pro Plus

Если ваша версия Excel 2013 поддерживает надстройку PowerPivot, вы сможете:

• Загрузить данные в PowerPivot можно различными способами. В вашем распоряжении имеется множество источников данных, связанные таблицы, возможность копирования и вставки данных, а также возможность создания подписок.
• В таблицах PowerPivot можно просматривать, сортировать и фильтровать данные.
• Можно импортировать миллионы строк в единственный рабочий лист таблицы PowerPivot.
• Можно создавать формулы DAX как в таблице, так и в виде нового вычисляемого поля, называемого мерой . Аббревиатура DAX расшифровывается как Data Analysis Expressions (Выражения анализа данных). В состав DAX входят 117 функций, которые позволяют выполнять две разновидности вычислений. 81 стандартную функцию Excel можно применять для добавления вычисляемых столбцов в таблицу, находящуюся в окне PowerPoint. А с помощью 54 функций можно создавать новые меры в сводных таблицах. Благодаря этим функциям сводные таблицы получают невиданные ранее возможности.
• В вашем распоряжении имеются дополнительные способы создания связей, включая представление диаграмм, применяемое для отображения связей.
• Можно скрывать или переименовывать столбцы.
• Перед созданием сводной таблицы можно задать числовое форматирование для столбца.
• Появилась возможность назначать категории полям, например, география , URL ссылка на изображение или веб-ссылка .
• Можно определять ключевые показатели эффективности либо иерархии.
• Если в вашей компании установлен сервер PowerPivot Server, вы получите возможность публиковать интерактивные отчеты PowerPivot на сайте SharePoint.

Если вы планируете обрабатывать миллионы записей, установите 64-разрядные версии Office и PowerPivot. В этом случае останутся в силе ограничения, связанные с недостатком оперативной памяти, но они частично нивелируются благодаря тому, что PowerPivot может практически в 10 раз сжимать данные, хранящиеся в файле PowerPivot. В 64-разрядной версии Office 2013 обеспечивается доступ к памяти, превышающей предел в 4 Гбайт, заданный для 32-разрядной версии Windows.

Активизация надстройки PowerPivot. Если вы имеете дело с Office 365, Office 2013 Pro Plus, Office 2013 Enterprise либо коробочной версией Excel 2013, вы сможете получить доступ к надстройке PowerPivot. Чтобы активизировать эту надстройку, выполните следующие действия:

1. Откройте Excel 2013. Видите вкладку ленты PowerPivot (рис. 8)? Если да, можете не выполнять следующие действия.
2. Выполните команду Файл — Параметры , выберите пункт Надстройки. В раскрывающемся списке Управление , находящемся в нижней части окна, выберите пункт Надстройки COM и щелкните на кнопке Перейти (рис. 9а).
3. В списке доступных надстроек СОМ найдите надстройку Microsoft Office PowerPivot for Excel 2013. Установите соответствующий флажок и щелкните на кнопке ОК (рис. 9б). (Учтите, что вам нужна надстройка PowerPivot for Excel 2013. Устаревшая надстройка «PowerPivot for Excel» использовавшаяся в Excel 2010, в Excel 2013 не поддерживается.)
4. Если на ленте не отображается вкладка PowerPivot, завершите выполнение программы Excel 2013 и снова запустите ее.

После установки надстройки на ленте Excel 2013 появится вкладка PowerPivot (рис. 8).

Импорт текстового файла. Исходная таблица включает 1,8 млн. записей, находящихся в файле BigDatal.txt (часть файла в окне программы Блокнот показана на рис. 10). Заголовки столбцов находятся в первой строке файла. Может потребоваться удаление нестандартных строк, находящихся в верхней части файла, дабы избежать проблем при их обработке с помощью надстройки PowerPivot. К сожалению, даты в файле имеют американский формат, поэтому в дальнейшем обрабатываются некорректно.

Для импорта файла, содержащего 1,8 млн. строк, в PowerPivot выполните следующие действия.

1. Перейдите вкладку PowerPivot. Щелкните на значке Управление . На экране появится окно приложения PowerPivot, в котором отображается собственная лента (рис. 11). В этом окне центральное место занимает таблица, с помощью которой можно просматривать данные в модели PowerPivot. В окне PowerPivot находятся следующие три вкладки: В начало , Конструктор и Дополнительно .

Рис. 11. Щелкните на значке Управление, находящемся на вкладке PowerPivot ленты Excel, чтобы открыть окно надстройки PowerPivot, в котором отображается собственная лента

2. Импортируйте таблицу из файла BigDatal.txt. Для этого в группе Получение внешних данных щелкните на кнопке Из других источников . На экране появится диалоговое окно Мастер импорта таблиц .

3. В окне Мастер импорта таблиц выберите самый нижний пункт Текстовый файл . Щелкните на кнопке Далее .

4. Введите в поле Понятное имя соединения имя для своего соединения.

5. Щелкните на кнопке Обзор и найдите текстовый файл. Если данные включают заголовки, PowerPivot обнаружит их.

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

7. Если хотите отказаться от импорта какого-либо столбца, отмените установку соответствующих флажков. Текстовый файл готов к загрузке в оперативную память. Учтите, что можно существенно уменьшить объем используемой оперативной памяти, если исключить лишние столбцы, особенно когда они включают длинные текстовые значения. Для этого сбросьте флажки, соответствующие скрываемым столбцам (рис. 12).

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

9. После щелчка на кнопке Готово PowerPivot начинает загружать файл в память. При этом отображается количество строк, загруженное в настоящий момент (рис. 13).

10. После завершения импорта файла отображается количество загруженных строк. Щелкните на кнопке Закрыть , чтобы вернуться в окно PowerPivot.

11. В окне PowerPivot отображается 1,8 млн. записей. Для их просмотра можно воспользоваться вертикальной полосой прокрутки. Можно также выполнить сортировку, изменить числовой формат либо применить фильтр (рис. 14).

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

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

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

Чтобы изменить ширину столбцов, перетащите границу между названиями столбцов (как в Excel).

Итак, у вас есть 1,8 млн. записей, которые можно сортировать, фильтровать и объединять в сводные таблицы. Обратите внимание на то, что 1,8 млн. строк, импортированных из текстового файла, хранятся в книге Excel. Можно скопировать файл.xlsx на новый компьютер, после чего все строки окажутся на новом компьютере. Исходный текстовый файл имеет размер 58 Мбайт, а сжатый файл Excel имеет размер всего лишь 4 Мбайт.

Добавление данных Excel методом копирования и вставки. В нашем примере таблицы PowerPivot информация о магазине ограничивалась лишь полем Код [магазина]. Сведения о названии либо местоположении магазина отсутствовали. Проблему можно устранить с помощью небольшого файла Excel, в котором коды магазинов сопоставляются с названиями и другой идентификационной информацией. Эти данные можно добавить на новую вкладку в окне PowerPivot. Можно воспользоваться копированием и вставкой, как описано ниже, либо создать связанную таблицу (см. следующий раздел), которая удобнее в применении.

Чтобы воспользоваться операциями копирования и вставки, выполните следующие действия:

1. Откройте рабочую книгу, содержащую диапазон, который связывает идентификаторы магазинов с названиями (рис. 15).
2. Выделите данные с помощью комбинации клавиш Ctrl+Shift*.
3. Скопируйте данные, нажав комбинацию клавиш Ctrl+C.
4. Перейдите на вкладку PowerPivot в Excel.
5. Щелкните на кнопке Управление , чтобы открыть окно PowerPivot. Теперь вы сможете увидеть предварительно импортированный набор данных, включающий 1,8 млн. строк.
6. В левой части вкладки В начало окна PowerPivot щелкните на значке Вставить . На экране появится диалоговое окно Просмотр вставки .
7. Присвойте новой таблице более понятное имя, чем заданное по умолчанию имя Таблица, например, Магазины (рис. 16). Щелкните на кнопке ОК.

Рис. 16. Окно Просмотр вставки

В окне PowerPivot появилась новая вкладка Магазины , на которой находятся дополнительные сведения о магазинах. Обратите внимание: в нижней части окна PowerPivot находятся ярлычки рабочих листов (рис. 17). Данные, вставленные из буфера обмена, представляют собой статическую копию данных Excel. Если данные Excel изменяются, скопируйте их и выполните команду PowerPivot Вставить с заменой .

Добавление данных Excel с помощью связывания. В предыдущем разделе была добавлена таблица Магазины путем выполнения операций копирования и вставки. При этом фактически создаются две копии данных. Одна из них хранится на рабочем листе Excel, а вторая - в окне PowerPivot. Если изменяется исходный лист, содержимое окна PowerPivot остается неизменным. Если подобная ситуация вас не устраивает, свяжите данные из таблицы Excel с данными в окне PowerPivot:

1. Для начала, преобразуйте данные (как на рис. 15) в таблицу, встав на любую ячейку диапазона и нажав Ctrl+T.
2. Выберите контекстную вкладку Конструктор . В левой части ленты отображается название только что созданной таблицы - Таблица1. В этом поле введите новое имя, например, Код_магазина.
3. Перейдите на вкладку PowerPivot и в группе Таблицы кликните Добавить в модель данных . Создастся копия таблицы, которая отображается в окне PowerPivot (рис. 18).

Рис. 18. Новая таблица в PowerPivot – Код_магазина

Определение связей. Обычно для связывания двух таблиц в Excel используется функция ВПР. В PowerPivot эта задача решается гораздо проще:

1. В окне PowerPivot перейдите на вкладку В начало и щелкните на кнопке Представление диаграммы . Отобразятся две таблицы, находящиеся рядом друг с другом (рис. 19).

2. Щелкните в поле Код в основной таблице (BigData) перетащите, и отпустите его, находясь над полем Код магазина области Магазины . Появятся стрелки, соответствующие установленной связи.

3. Чтобы вернуться к таблице PowerPivot, щелкните на значке Представление данных , находящемся на вкладке В начало окна PowerPivot .

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

1. Перейдите на закладку PowerPivot . Щелкните на кнопке Управление и в открывшемся окне щелкните на ярлычке первого рабочего листа – Продажи , находящемся в нижней части окна PowerPivot .
2. Крайний правый столбец называется Добавление столбца . Щелкните на первой ячейке этого пустого столбца.
3. Щелкните на значке fx , находящемся в левой части строки формул. На экране появится диалоговое окно Вставить функцию , включающее целый ряд категорий. Выберите категорию Дата и время . Обратите внимание: отображаемые в этом списке функции отличаются от функций из категории Дата и время в Excel.
4. Прокрутите список и выберите функцию YEAR и нажмите OK (рис. 20). Щелкните на заголовке столбца Дата заказа . Надстройка PowerPivot предлагает формулу =YEAR([Дата заказа]. Завершите создание формулы путем ввода закрывающей скобки и нажатия клавиши Enter . Excel заполняет столбец значениями года, связанного с датой.
5. Щелкните правой кнопкой мыши на столбце и в контекстном меню выберите параметр Переименовать столбец . Введите новое имя столбца, например, Год (рис. 21).

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

1. Перейдите навкладку В начало ленты PowerPivot. Раскройте список, находящийся под кнопкой Сводная таблица и выберите пункт Сводная таблица (рис. 22).
2. В открывшемся окне выберите переключатель, определяющий вставку сводной таблицы на новый лист. Вы снова вернетесь в окно Excel. В списке полей сводной таблицы отобразятся все таблицы, содержащиеся в PowerPivot. Чтобы просмотреть поля, входящие в каждую таблицы, раскройте соответствующий таблице список (нажав на «треугольник»).
3. В списке полей PowerPivot откройте таблицу BigData и выберите поле Доход . Разверните таблицу Магазины о и выберите в ней поле Регион . Excel сформирует сводную таблицу, отображающую продажи по регионам (рис. 23). Итак, в вашем распоряжении оказалась сводная таблица, которая построена на основе 1,8 млн. строк данных и содержит виртуальную ссылку на связанную таблицу.

Рис. 22. Создание сводной таблицы в окне PowerPivot

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

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

• Отсутствует автоматическая сортировка по дням недели (понедельник, вторник, среда и т.д.). Для выполнения корректной сортировки выберите команду Дополнительные параметры сортировки — По возрастанию — Дополнительно . Отмените установку флажка Автосортировка , раскройте список Сортировка по первому ключу и выберите последовательность Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье.
• Для сортировки полей в обычных сводных таблицах можно воспользоваться следующим приемом (вместо перетаскивания). Выберите ячейку, содержащую, например, слово Пятница , и введите в эту ячейку слово Понедельник . После нажатия клавиши Enter данные из столбца Понедельник переместятся в новый столбец. Учтите, что этот прием неприменим при работе со сводными таблицами PowerPivot .
• В процессе ввода формул с помощью интерфейса Excel включаемые в них ячейки можно выбрать щелчком мыши либо с помощью клавиш управления курсором. Вероятно, надстройка PowerPivot создавалась фанатами мыши, поэтому при создании формул PowerPivot можно использовать только мышь.
• После щелчка на кнопке Обновить , находящейся на контекстной вкладке Анализ , Excel обновляет данные в сводной таблице. Подумайте, прежде чем делать это в Excel 2013. В рассматриваемом примере выполняется повторный импорт 1,8 млн. строк.

Два вида вычислений DAX

Только что мы рассматрели пример использования функции DAX (YEAR) для объявления вычисляемого столбца в таблице, которая отображается в окне PowerPivot . Для создания подобных столбцов используется 81 функция, большинство из которых копируются непосредственно из Excel. Многие из функций DAX аналогичны соответствующим функциям Excel за некоторыми исключениями, которые будут рассмотрены ниже. С помощью DAX также можно создавать новые вычисляемые поля в сводной таблице. Эти функции предназначены не для вычисления единственного значения ячейки, а для определения значений отфильтрованных строк, связанных с ячейками сводной таблицы (агрегирующие функции). В DAX имеется 54 таких функций. Реальная мощь PowerPivot заключается именно в этих функциях.

Использование функций DAX в вычисляемых столбцах. Такие функции весьма напоминают обычные функции Excel, поэтому для большинства из них не требуются дополнительные объяснения. Но некоторые функции DAX отличаются от функций Excel:

• Редко упоминаемая функция Excel РАЗНДАТ переименована в YEARFRAC, а ее код переписан. Собственно говоря, самой функции РАЗНДАТ нет в справке Excel. Более того нет ее и в мастере функций. И при наборе вручную первых букв названия функции Excel тоже не покажет подсказку в выпадающем списке. Объясняется это довольно просто. Эта функция изначально не является функцией Excel. Она всего лишь поддерживается Excel для совместимости с другими системами электронных таблиц. В Excel эта функция попала из электронных таблиц Lotus 1-2-3. Подробнее см. .
• Функция Excel ТЕКСТ переименована в FORMAT.
• Функция СУММЕСЛИМН заменена усовершенствованной функцией CALCULATE.
• Вместо функции ВПР применяется более простая функция RELATED.
• В DAX появилась функция BLANK(). Поскольку некоторые из агрегирующих функций могут основывать вычисления на параметрах ALLN0NBLANKR0W либо FIRSTNONBLANK, функция BLANK() может применяться в качестве аргумента функции IF() для исключения некоторых строк при вычислениях мер.
• Функция ВЫБОР переименована в SWITCH. В то время как в качестве аргументов функции ВЫБОР используются числовые значения от 1 до 255, функцию SWITCH можно запрограммировать для работы с другими значениями.

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

В рассматриваемом примере используется таблица BigData, включающая поле Код [магазина] и Доход (сумма продаж за день). В отчетах о продажах часто используется такой показатель, как величина продаж на квадратный метр площади. В связанной таблице Магазины находятся поля Код магазина и Торговая площадь . В наборе этих полей находятся все данные, требуемые для выполнения вычислений.

Чтобы начать создание новой формулы, перейдите в таблицу PowerPivot и щелкните в пустой ячейке колонки Добавление столбца . Введите знак равенства и щелкните в ячейке, находящейся в столбце Доход . Начните вводить формулу в PowerPivot : = [Доход]. Введите знак косой черты, обозначающий деление. Теперь нужно получить доступ к полю Торговая площадь , находящемуся в таблице Магазины . Начните ввод функции RELATED(. Укажите несколько первых букв названия таблицы – Ма . Отобразится список полей в таблице Магазины (рис. 24). Дважды кликните в поле Торговая площадь . Завершите создание формулы вводом закрывающей круглой скобки и нажатием клавиши Enter. Щелкните правой кнопкой мыши на столбце и в контекстном меню выберите параметр Переименовать столбец . Присвойте новое имя, например, ПродажиНаКвМ .

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

Создание вычисляемого поля в сводной таблице с помощью формул DAX. Вычисляемые поля DAX обладают рядом преимуществ по сравнению с обычными вычисляемыми полями. Одно из преимуществ заключается в том, что при использовании подобного поля вычисления выполняются один раз в каждой ячейке результирующей сводной таблицы. На рис. 25 показана сводная таблица, в ячейках В5:С12 которой находятся числовые значения. Созданное вычисляемое поле DAX будет вычисляться лишь для 16 числовых ячеек сводной таблицы. Это намного быстрее, чем вычисление значений, находящихся в 1,8 млн. ячеек, с последующим суммированием. Из-за американского формата дат в исходном файле BigData.txtряд дат в модели PowerPivot отображается некорректно, что приводит к полю (пусто) в срезе дат. Прежде чем приступить к созданию первого вычисляемого поля, следует получить представление о фильтрах.

Рис. 25. Сводная таблица на основе модели PowerPivot

Применение заранее созданных фильтров в вычисляемых полях DAX. В вычисляемых полях DAX автоматически применяются фильтры, предварительно заданные в соответствующих ячейках. Сначала применяются все фильтры, а затем выполняются вычисления в полях DAX. Рассмотрим ячейку В6, представленную на рис. 25. Попробуем ответить на вопрос о том, сколько фильтров задано в ячейке В6. Вы скажете, что определено два фильтра. Я же думаю, что в этой ячейке определено четыре фильтра.

Наравне с обычными автофильтрами данные в ячейках могут фильтроваться с помощью срезов. После создания первого среза отображаются записи, находящиеся в ячейке В6, которые соответствуют 2006 году. Можно также просмотреть данные о продажах в магазинах Bellevue Square Managers, добавив второй срез. Эти два среза и формируют первые два фильтра. Также в качестве фильтра выступает заголовок строки Eyewear. Это будет третий фильтр. И наконец, четвертым фильтром выступает штат Вашингтон. В вычисляемых полях DAX сначала применяются ранее созданные фильтры, а затем вычисляется результат применения формулы DAX.

Создание вычисляемого поля DAX. Для создания вычисляемого поля перейдите на ленту Excel, выберите вкладку PowerPivot и выполните команду Вычисляемые поля — Создание вычисляемого поля . На экране появится диалоговое окно Вычисляемое поле . В поле Имя таблицы укажите название таблицы. Присвойте вычисляемому полю имя, например, КоличествоМагазинов . На панели ввода формул введите формулу. Для вставки названий функций щелкните на значке fх . В процессе ввода названий полей начните вводить несколько символов имени таблицы, а затем с помощью функции автозавершения выберите нужное поле.

После завершения ввода формулы щелкните на кнопке Проверить формулу , чтобы протестировать синтаксис формулы. Обратите внимание на подсказку формулы, которая отображается над панелью, показывающей результаты проверки формулы. Щелкните в поле Описание , чтобы скрыть подсказку. На экране появятся результаты проверки формулы. Если проверка формулы завершилась успешно, отобразится сообщение Формула не содержит ошибок (рис. 26). Щелкните ОК , чтобы добавить вычисляемое поле в список полей сводной таблицы. В таблице Магазины модели PowerPivot новое поле не появилось, а вот в сводной таблице оно есть (рис. 27). Совсем, как в обычной сводной таблице!

Однажды созданное вычисляемое поле можно использовать для выполнения вычислений в будущем. Вычисляемое поле ПродажиМагазина (рис. 28) использует следующую формулу:

SUMX(BigData;BigData[Доход])/[КоличествоМагазинов]

Рис. 28. Поле ПродажиМагазина вычисляется на основе ранее созданного поля КоличествоМагазинов

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

Как отменить стандартную фильтрацию. А теперь рассмотрим следующую проблему. На рис. 29 выделена ячейка С5. В результате применения фильтров к этой ячейке отображаются записи, соответствующие торговцу по имени Амбер и дате продажи 01.06.2014. В этом и следующих разделах (вплоть до Практикума) используется Excel-файл Пример фильтра. xlsx

Как уже упоминалось ранее, все фильтры, заданные к ячейке сводной таблицы, автоматически применяются к вычисляемому полю DAX. Во многих случаях это неприемлемо. В ячейке G6 отображается сумма проданных товаров, равная 165 долларам, которая не связана с каким-либо продавцом. Применяемая в данном случае политика заключается в том, чтобы сумма, равная 165 долларам, была назначена другим людям на основе их доли (в процентах) от суммы проданных товаров в этот же день, причем продавец Хьюз не участвует в продажах. Можно ли реализовать эту политику в данной сводной таблице?

Обратите внимание на то, что вычисления, выполняемые в ячейке С5, неявно фильтруются таким образом, чтобы отображать только продажи Амбера. Нужно создать формулу DAX, которая будет отменять фильтрацию данных. Эта формула будет отменять фильтр, отображающий соответствующие Амберу записи, и отображать записи, которые не связаны с Амбером. Функция DAX CALCULATE может удалять установленные фильтры и применять другие фильтры. Эта функция напоминает функцию СУММЕСЛИМН, но предоставляет пользователям больше возможностей.

Функция CALCULATE применяется для вычисления столбца итогов с учетом одного либо нескольких фильтров. Если для поля Продавец задать фильтр, он заменит фильтр, ранее заданный для этого поля. Если создать фильтр Продавец =Хьюз , DAX автоматически проигнорирует неявный фильтр Продавец =Амбер , что приведет к отображению всех записей, соответствующих Хьюзу. Если вам сложно с ходу научиться создавать формулы DAX, попробуйте поэтапную методику. Вместо многоэтажных формул сформируйте набор, состоящий из небольших формул. Формула, вычисляющая объем продаж Хьюза (рис. 30):

CALCULATE([Сумма по столбцу Продажи];Sls[Продавец]= " Хьюз ")

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

Задайте следующие вычисляемые поля:

Теперь пришла очередь применить фильтр к столбцу Продавец , чтобы скрыть долю Хьюза из сводной таблицы. В результате получим сумму продаж 911 долларов за 2 июня (значение в ячейке Н6, рис. 31), при том что доля Хьюза распределена между другими продавцами. Сравните значения по каждому отдельному продавцу и итоги по дням в таблицах на рис. 30 и 31. Подобного результата невозможно добиться с помощью вычисляемых полей, создаваемых в обычной сводной таблице.

ПРАКТИКУМ: Вычисление диапазона цен на товары

Вычисляемые поля, заданные в обычной сводной таблице, всегда просчитываются по строкам исходного набора данных. Один из подписчиков сайта MrExcel попытался вычислить диапазон цен на товары с помощью формулы МАКС(Цена) – МИН(Цена) , заданной в обычной сводной таблице. При создании вычисляемого поля в сводной таблице Excel просматривает каждую строку исходных данных, и вычисляет максимальное значение в этой строке (рис. 32). Поскольку в каждой строке отображается лишь единственное значение цены, максимальное значение в каждой строке исходных данных будет соответствовать единственному значению цены. То же самое можно сказать и о минимальном значении. Другими словами, МАКС – МИН = Цена – Цена , то бишь 0. В результате для каждого товара, отображаемого в сводной таблице, разница между наибольшей и наименьшей ценами будет равно нулю. Этот абсолютно некорректный результат получен из-за правил, присущих вычисляемым полям в обычных сводных таблицах (рис. 33).

Воспользуйтесь возможностями DAX для решения этой задачи.

1. Выделите исходный набор данных. Нажмите комбинацию клавиш Ctrl+T, чтобы преобразовать выделенный набор данных в таблицу.
2. С помощью набора контекстных вкладок Работа с таблицами (Конструктор) присвойте новой таблице имя Продажи .
3. Выполните команду Вставка — Сводная таблица и установите флажок Добавить эти данные в модель данных .
4. Добавьте поле Товар в область СТРОКИ.
5. Дважды добавьте поле Цена в область ЗНАЧЕНИЯ.
6. В нижней части списка полей сводной таблицы раскройте список для первого поля Цена и выберите пункт Параметры полей значений . Измените итоговую функцию на Минимум.
7. Аналогично для второго поля Цена выберите итоговую функцию Максимум.
8. При выполнении пп. 6 и 7 фактически создается неявно заданное вычисляемое поле. Эти поля подробно рассматриваются в следующем разделе.
9. Выполните команду PowerPivot — Вычисляемые поля — Создание вычисляемого поля , чтобы создать вычисляемое поле. Присвойте этому полю имя Диапазон . В окне ввода формулы введите имя таблицы Продажи . В списке полей найдите поле Продажи [Максимум в столбце Цена] и нажмите клавишу <Таb>. Введите знак минуса. Снова введите Продажи. Найдите поле Продажи [Минимум в столбце Цена] и нажмите клавишу <Таb>, чтобы вставить его в формулу. Теперь формула Диапазон принимает вид =[Максимум в столбце Цена]-[Минимум в столбце Цена].
10. Чтобы добавить формулу в модель данных, щелкните на кнопке ОК. После возврата в окно Excel найдите новое поле Диапазон в таблице Продажи . Кликните на этом поле. Теперь диапазон цен вычисляется корректно (рис. 34).

Создание вычисляемых полей DAX путем добавления полей в область ЗНАЧЕНИЯ. После добавления поля Цена в область ЗНАЧЕНИЯ и выбора функции Минимум PowerPivot автоматически создает вычисляемое поле DAX, отображающее полученный результат. Благодаря этому вам не придется использовать формулу =MINX(‘Продажи’;’Продажи"[Цена]). Можно просмотреть неявные вычисляемые поля в окне PowerPivot. Выполните команду PowerPivot — Управление и на вкладке Дополнительно щелкните на кнопке Показать неявные вычисляемые поля . Новые поля появятся в строках, отображенных в нижней части окна (рис. 35).

Если установить указатель мыши над нижней частью окна, появится подсказка «PowerPivot автоматически сформировал это вычисляемое поле путем добавления поля к области значений в списке полей Excel. Поле доступно только для чтения и будет автоматически удалено при удалении столбца».

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

Чтобы вычислить диапазон цен, можно создать следующую формулу DAX:

Мах(‘Продажи’; ‘Продажи"[Цена])-Minx(‘Продажи’; ‘Продажи"[Цена])

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

В DAX существует набор функций работы со временем. Для использования этих функций требуется специальная таблица, включающая даты (календарная таблица). Любая таблица может включать поле даты. Дата, отображаемая в исходном наборе данных, будет появляться в единственной строке таблицы дат. Если нужны дополнительные столбцы, идентифицирующие дату как относящуюся к определенному году, месяцу или дню недели, добавьте эти столбцы в календарную таблицу. Существует множество способов формирования подобных таблиц, но пользователи Excel обычно создают их в среде Excel:

1. Перейдите к исходному набору данных. Выберите столбец, включающий поле даты, и скопируйте его.
2. Перейдите к пустому листу и вставьте даты в столбец А.
3. Выделив столбец А, выполните команду Данные — Удалить дубликаты и щелкните на кнопке ОК .
4. При необходимости добавьте дополнительные столбцы, например, Год, Номер дня недели, День недели, Номер месяца и Месяц (рис. 36). Формулы, показанные в верхней части рисунка, демонстрируют, каким образом вычисляется каждый столбец.
5. Преобразуйте диапазон данных в таблицу, нажав комбинацию клавиш Ctrl+T.
6. С помощью контекстной вкладки Конструктор , относящейся к набору контекстных вкладок Работа с таблицами , выберите для создаваемой таблицы название Календарь.

Начиная с этого раздела и до конца заметки используются данные из Excel-файла Пример календарь.xlsx.

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

1. Выделите ячейку в таблице Продажи .
2. На вкладке PowerPivot в Excel щелкните на значке Добавить в модель данных.
3. Выберите ячейку в таблице Календарь .
4. Добавить в модель данных .
5. На вкладке PowerPivot в Excel щелкните на значке Управление ; перейдите в окно PowerPivot .

Теперь выполним форматирование этих двух таблиц в окне PowerPivot:

1. В таблице Продажи выберите столбец Дата .
2. В окне PowerPivot выберите вкладку В начало . Обратите внимание на группу Форматирование . По умолчанию уже выбран правильный формат Дата , но само форматирование даты некорректно. Раскройте список Формат и выберите значение *14.03.2001. (Это не ошибка; именно так выглядит пункт раскрывающегося списка.)
3. Выберите столбец Продажи в таблице Продажи .
4. На вкладке В начало выберите формат Валюта . Чтобы устранить знаки после запятой, дважды щелкните на значке Уменьшить число десятичных разрядов .
5. Снова выберите столбец Дата в таблице Продажи .
6. На вкладке Конструктор щелкните на значке Создание связи .
7. В диалоговом окне Создание связи первые два поля заполнены значениями Продажи и Дата . Раскройте список Связанная таблица подстановки и выберите пункт Календарь . В раскрывающемся списке Связанный столбец подстановки автоматически появится значение Дата (рис. 37). Щелкните ОК для создания связи.
8. Выберите рабочий лист Календарь в окне PowerPivot .
9. Выделите столбец Дата .
10. Перейдите на вкладку В Начало и измените формат даты на *14.03.2001.
11. Выделив столбец Дата , перейдите на вкладку Конструктор . Раскройте список Пометить как таблицу дат и выберите пункт Пометить как таблицу дат . На экране появится диалоговое окно Пометить как таблицу данных . В этом окне уже выбрано корректное поле Дата . Щелкните на кнопке ОК . Данное действие нужно для создания фильтров по дате в списке полей сводной таблицы. Результат показан на рис. 39.

Рис. 37. Создание связи между таблицами Продажи и Календарь

В PowerPivot не поддерживается автоматическая сортировка по пользовательским спискам. Я большой поклонник PowerPivot и пишу книги соответствующей тематики с 2009 года. И в первой книге по PowerPivot я несколько раз упоминал о том, что в PowerPivot не выполняется автоматическая сортировка по названиям месяцев (в последовательности «январь, февраль, март…»). В сводных таблицах PowerPivot названия месяцев сортируются по алфавиту (апрель, август, июль…), и эта проблема не устранена даже в версии PowerPivot for Excel 2013.

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

1. В окне PowerPivot выберите таблицу Календарь .
2. Выделите одну из ячеек в столбце День недели .
3. На вкладке В начало в окне PowerPivot в группе Сортировка и фильтрация щелкните на значке Сортировка по столбцам .
4. В диалоговом окне Сортировка по столбцу выберите сортировку дня недели по номеру дня недели (рис. 38). Щелкните ОК .
5. Выделите ячейку в столбце Месяц . Повторите пп. 3 и 4, но на сей раз выберите сортировку столбца Месяц по номеру месяца .

Создание сводной таблицы с расширенными возможностями. В окне PowerPivot выберите таблицу Продажи . На вкладке В начало раскройте список Сводная таблица и выберите пункт Сводная таблица . Вы вернетесь в Excel и в списке полей сводной таблицы отобразятся поля таблицы Календарь и таблицы Продажи . Установите указатель мыши над полем Дата и раскройте список. Выберите пункт Фильтры по дате , позволяющий получить доступ ко всем фильтрам по дате, которые используются в обычных сводных таблицах (рис. 39).

Рис. 39. Благодаря тому, что таблица Календарь была объявлена как таблица дат, появилась возможность получать доступ ко всем фильтрам по дате

Выполните следующие действия, чтобы добавить поля в сводную таблицу:

1. Перетащите поле День недели из таблицы Календарь в область КОЛОННЫ.
2. Перетащите поле Продажи из таблицы Продажи в область ЗНАЧЕНИЯ.
3. Выберите контекстную вкладку Анализ и щелкните на значке Вставить срез .
4. Добавьте срез для поля День недели .
5. На контекстной вкладке Параметры набора контекстных вкладок Инструменты для среза выберите для параметра Столбцы значение 7.
6. На срезе выберите только рабочие дни.

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

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

«Умные» функции времени. После выполнения предварительной работы пришло время вплотную заняться «умными» функциями времени. Начните с реорганизации сводной таблицы, изображенной на рис. 40:

1. На контекстной вкладке Анализ выполните команду Очистить — Очистить всё .
2. Откройте таблицу Календарь и перетащите поле Дата в область СТРОКИ.
3. Откройте таблицу Продажи и перетащите поле Продажи в область ЗНАЧЕНИЯ.
4. Добавьте срез для поля Год .
5. В окне среза Год выберите значение 2015.

В результате будет получена сводная таблица, показанная на рис. 41. Объем продаж за 2 июня 2015 года составил 937 долларов.

А сейчас мы создадим вычисляемые поля DAX, с помощью которых сравним объем продаж текущего дня с объемом продаж такого же дня предыдущего года. Ранее уже упоминалось, что эта задача не столь уж проста, поскольку к ячейке С9 применен фильтр, отображающий записи с датой 2.06.2015. Поэтому нужно отменить фильтр по полю даты, а затем применить новый фильтр, который позволит отобразить записи прошлого года.

Чтобы отменить фильтр, примененный к полю даты, необходимо воспользоваться функцией CALCULATE. Но что делать, если нужно отобразить записи, соответствующие прошлому году? Воспользуйтесь одной из более чем 30 «умных» функций времени, поддерживаемых в DAX, а именно - функцией DATEADD.

Чтобы выбрать точно такой же день ровно год назад, воспользуйтесь формулой DATEADD (‘Календарь"[Дата];–1;year). В качестве третьего аргумента может использоваться day, month либо year. Учтите, что эти аргументы являются перечисляемыми (т.е. при программировании в Excel определяются в качестве глобальных переменных, которые при выполнении программы преобразуется в числовой код), поэтому не заключаются в кавычки подобно текстовым аргументам.

Функция DATEADD используется при создании многих формул, вычисляющих периоды времени. Например, чтобы просмотреть дневной объем продаж, который имел место три месяца назад, воспользуйтесь формулой =DATEADD(‘Календарь"[Дата];–3;month). И не забывайте о том, что функция DATEADD - всего лишь одна из 34 «умных» функций, предназначенных для работы со временем. Например, еще одна функция из этой категории используется в формуле DATESMTD (‘Календарь"[Дата]), которая отображает все даты вплоть до текущего дня месяца.

Предположим, что в вашей модели данных столбец даты находится в таблице Продажи и в таблице Календарь . «Умные» функции времени будут всегда корректно работать, если используется ссылка на поле ‘Календарь"[Дата]. И они же будут работоспособны лишь в 10% случаев, если сослаться на поле ‘Продажи"[Дата]. Если хотите поломать голову над сложной задачей, попробуйте создать формулу DAX со ссылкой на поле ‘Продажи"[Дата]. После проверки корректности синтаксиса на экране появится бодрое сообщение об отсутствии ошибок, и тем не менее сводная таблица отображает некорректные результаты. Я не могу обнаружить причину подобного странного поведения формулы. Но если формула будет применена к полю ‘Календарь"[Дата], будут получены корректные результаты.

Итак, в вашем распоряжении имеется функция DATEADD, которая позволяет идентифицировать дату, которая ровно на год отстоит от даты, указанной в строке сводной таблицы. Чтобы отобразить объем продаж на эту дату, воспользуйтесь функцией CALCULATE, которая переопределяет существующие неявные фильтры. Поскольку в строке 9 сводной таблицы используется неявный фильтр, отображающий записи, соответствующие дате 2 июня 2015 года, воспользуйтесь следующей формулой, включающей функцию CALCULATE:

CALCULATE([Сумма по столбцу Продажи];DATEADD(‘Календарь"[Дата];-1;year))

Выполните следующие действия:

1. PowerPivot и выполните команду Вычисляемые поля — Создание вычисляемого поля . Откроется окно Вычисляемое поле (рис. 42).
2. Присвойте полю имя ПродажиЗаПоследнийГод .
3. Введите формулу
   =CALCULATE([Сумма по столбцу Продажи];DATEADD(‘Календарь"[Дата];-1;year))
4. В группе Категория выберите Валюта. Установите Десятичные разряды 0. Выберите Символ – $ Английский (США).
5. Щелкните на кнопке Проверить формулу , чтобы убедиться в корректности формулы. Если из-за отображаемой на экране подсказки результаты проверки формулы не видны, щелкните в поле Описание . Щелкните ОК , чтобы завершить создание вычисляемого поля.
6. Вы вернетесь в окно Excel. Кликните в Списке полей сводной таблицы в таблице Календарь на поле ПродажиЗаПоследнийГод . Поле добавиться в область ЗНАЧЕНИЯ.

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

1. В окне Excel выберите вкладку PowerPivot и выполните команду Вычисляемые поля — Создание вычисляемого поля .
2. Присвойте полю имя ПроцентноеИзменение .
3. Введите формулу =[Сумма по столбцу Продажи]/[ПродажиЗаПоследнийГод]–1.
4. В группе Категория выберите Число. Установите Десятичные разряды 1. Выберите Формат – Процент.
5. Повторите пп. 5 и 6 предыдущей инструкции.

Получившаяся сводная таблица показана на рис. 43.

Теперь можно удалить из сводной таблицы поля Продажи и ПродажиЗаПоследнийГод , оставив поле ПроцентноеИзменение и добавить имя продавцов (поле Торговец ) в область КОЛОННЫ (рис. 44). В каждой ячейке сводной таблицы показана динамика продаж по сравнению с прошлым годом (странно, но мне не встречалось, чтобы анализировали ежедневную динамику).

Рис. 44. Сводная таблица с вычисляемым полем ПроцентноеИзменение

Чтобы получить таблицу, показанную на рис. 44, выполните следующие действия:

1. В ячейке G9 (и в некоторых других) отображается сообщение об ошибке, причиной появления которого является отсутствие продаж в предыдущем году (и, следовательно, деление на 0). Кликните правой кнопкой мыши на любой ячейке сводной таблицы и в контекстном меню выберите Параметры сводной таблицы (рис. 45). Перейдите на вкладку Макет и формат и установите флажок Для ошибок отображать и в соответствующее поле введите символы - - .
2. Выделите числовые значения в сводной таблице (диапазон С9:Н38). Перейдите на вкладку Главная , и выполните команду Условное форматирование — Наборы значков и выберите набор из двух треугольников и одного прямоугольника. Если хотите, отредактируйте правила форматирования, установленные Excel по умолчанию.

Чтобы получить дополнительные сведения о DAX, посетите блог Роба Колли http://www.powerpivotpro.com/ (я купил книжку Колли, так что предвижу замечательное чтение).

Использование ключевых показателей эффективности

Ключевые показатели эффективности (КПЭ) появились в текущей версии PowerPivot . В настоящее время они функционируют с ошибками, поскольку реализованы не идеально. Чтобы воспользоваться КПЭ, следует создать хотя бы одно вычисляемое поле DAX, которое будет использоваться в качестве базового. Дополнительно можно также создать второе вычисляемое поле DAX, которое послужит в качестве целевого значения.

Настройка КПЭ для абсолютного целевого значения. Чтобы задать и отобразить КПЭ в сводной таблице, выполните следующие действия.

1. На вкладке PowerPivot выберите команду Ключевые показатели эффективности — Создать ключевой показатель эффективности . На экране появится диалоговое окно Ключевой показатель эффективности .
2. Раскройте список Базовое поле ключевого показателя эффективности (значение) и выберите одну из вычисляемых мер DAX.
3. Для задания целевого значения установите переключатель Абсолютное значение и введите значение.
4. Выберите один из четырех стилей пороговых значений состояния (область 1 на рис. 46).
5. Выберите стиль значка (область 2 на рис. 46).
6. Обратите внимание на то, что нельзя перетаскивать текстовые поля, хотя на первый взгляд кажется, будто эта операция возможна. Вводите новые значения в текстовые поля, находящиеся над пороговыми значениями. После ввода нового значения и нажатия клавиши Таb текстовое поле переместится в нужное место.
7. Щелкните на пункте Описания , находящемся в нижней части диалогового окна, чтобы изменить названия полей, которые появятся в п. 10.
8. Щелкните ОК .
9. Откройте таблицу Календарь в списке полей сводной таблицы. Возле поля ПроцентноеИзменение отобразится пиктограмма светофора.
10. Щелкните на значке «плюс», чтобы отобразить три поля: Значение , Цель и Состояние . Перетащите поле Состояние в область ЗНАЧЕНИЯ. В сводной таблице отобразится значок. Теперь независимо от значка, выбранного в диалоговом окне, в сводной таблице отображаются три круглых значка: красный, желтый и зеленый (рис. 47).

Заметка написана на основе книги Джелен, Александер. . Глава 10.