Менее чем за час вы научитесь создавать в Excel отчеты сводной таблицы, объединяющие данные из нескольких таблиц. Первая часть этого учебника поможет вам импортировать данные и изучить их. Во второй части вы научитесь уточнять модель данных, лежащую в основе отчета, добавлять в отчеты Power View новые вычисления и иерархии, а также оптимизировать их с помощью надстройки Power Pivot.
Начнем с импорта данных.
- Скачайте образцы данных (ContosoV2) для этого учебника. Извлеките файлы данных и сохраните их в удобном месте, например в папке "Загрузки" или "Мои документы".
- Откройте в Excel пустую книгу.
- Выберите Данные > Получение внешних данных > Из Access.
- Перейдите в папку, содержащую файлы образцов данных, и выберите ContosoSales.
- Нажмите Открыть. Поскольку вы подключаетесь к файлу базы данных, который содержит несколько таблиц, появится диалоговое окно Выбор таблицы, где вы можете выбрать таблицы, которые нужно импортировать.
- В диалоговом окне "Выбор таблицы" установите флажок Разрешить выбор нескольких таблиц.
- Выберите все таблицы и нажмите кнопку ОК.
- На вкладке "Импорт данных" выберите Отчет сводной таблицы и нажмите кнопку ОК.
ПРИМЕЧАНИЕ. Поздравляем! Вы только что создали модель данных. Модель - это уровень интеграции данных, который создается автоматически, когда вы импортируете несколько таблиц или работаете с ними одновременно в одном отчете сводной таблицы.
Модель практически не видна в Excel, но вы можете просматривать и изменять ее напрямую с помощью надстройки Power Pivot . В Excel наличие модели данных прослеживается тогда, когда вы видите набор таблиц в списке полей сводной таблицы. Существует несколько способов создать модель.
Просмотр данных в сводной таблице
Чтобы просмотреть данные в понятном виде, вы можете перетащить поля в области Значения, Столбцыи Строки в списке полей сводной таблицы.
- Прокрутите список полей и найдите в нем таблицу FactSales.
- Выберите столбец SalesAmount. Поскольку он содержит числовые данные, Excel автоматически поместит SalesAmount в область "Значения".
- Из таблицы DimDate перетащите столбец CalendarYear в область "Столбцы".
- Из таблицы DimProductSubcategory перетащите столбец ProductSubcategoryName в область "Строки".
- Из таблицы DimProduct перетащите столбец BrandName в область "Строки", поместив его под подкатегорией.
Сводная таблица должна быть похожа на изображенную ниже.
Не затрачивая особых усилий, вы создали сводную таблицу, которая содержит поля из четырех разных таблиц. Эта задача оказалась настолько легкой благодаря заранее созданным связям между таблицами. Так как связи между таблицами существовали в источнике данных и вы импортировали все таблицы сразу, приложение Excel смогло воссоздать эти связи в модели.
Но что делать, если данные происходят из разных источников или импортируются не одновременно? В общих случаях вы сможете внести новые данные, создав связи на основе совпадающих столбцов. На следующем этапе вы импортируете дополнительные таблицы и узнаете о требованиях к новым связям и этапах их создания.
Добавление дополнительных таблиц
Чтобы научиться устанавливать связи, вам нужны дополнительные несвязанные таблицы. На этом этапе вы получите оставшиеся данные, используемые в этом учебнике, импортировав дополнительную базу данных и вставив данные из двух других книг.
Добавление категорий продуктов
- Откройте в книге новый лист. В нем будут храниться дополнительные данные.
- Выберите Данные > Получение внешних данных > Из Access.
- Перейдите к папке, содержащей файлы образцов данных, и выберите ProductCategories. Нажмите кнопку Открыть.
- На вкладке "Импорт данных" выберите пункт Таблица и нажмите кнопку ОК.
Добавление географических данных
- Добавьте еще один лист.
- Из файлов образцов данных откройте Geography.xlsx, поместите курсор в поле A1, затем нажмите клавиши CTRL+SHIFT+END, чтобы выбрать все данные.
- Скопируйте данные в буфер обмена.
- Вставьте данные в только что добавленный пустой лист.
- Выберите Форматировать как таблицу с любым стилем. Форматирование данных в виде таблицы позволяет присвоить им имя, что очень удобно при задании связей в следующем шаге.
- Убедитесь, что в окне "Форматирование таблицы" установлен флажок Таблица с заголовками. Нажмите кнопку ОК.
- Присвойте таблице имя Geography. Выберите вкладку Работа с таблицами > Конструктор и введите название Geography в поле "Имя таблицы".
- Закройте файл Geography.xlsx, чтобы убрать его из рабочей области.
Добавление данных по магазинам
- Повторите предыдущие шаги для файла Stores.xlsx - вставьте его содержимое в пустой лист. Присвойте таблице имя Stores.
Теперь получилось четыре листа. Лист1 содержит сводную таблицу, лист Лист2 содержит ProductCategories, Лист3 содержит Geography, а Лист4 - Stores. Поскольку вы уже присвоили имена всем таблицам, следующий шаг - создание связей - будет гораздо проще.
Использование полей из новых импортированных таблиц
Вы можете прямо сейчас использовать поля из только что импортированных таблиц. Если приложению Excel не удается определить, как внести поле в отчет сводной таблицы, появится запрос на создание связи между таблицами, с помощью которого новая таблица будет сопоставлена с той, которая уже является частью модели.
- Выберите пункт Все в верхней части списка полей сводной таблицы, чтобы просмотреть полный список доступных таблиц.
- Прокрутите список вниз. Там находятся новые таблицы, которые вы только что добавили.
- Разверните Stores.
- Перетащите столбец StoreName в область "Фильтры".
- Обратите внимание, что Excel выдаст запрос на создание связи. Это уведомление появляется по той причине, что вы использовали поля таблицы, которая не связана с моделью.
- Нажмите Создать, чтобы открыть диалоговое окно "Создание связи".
- В области "Таблица" выберите FactSales. Таблица FactSales из образца данных содержит подробные сведения о продажах и стоимости для компании Contoso, а также ключи других таблиц, в том числе коды магазинов, присутствующие в файле Stores.xlsx, импортированном на предыдущем этапе.
- В области "Столбец (внешний)" выберите StoreKey.
- В области "Связанная таблица" выберите Stores.
- В области "Связанный столбец (основной)" выберите StoreKey.
- Нажмите кнопку ОК.
На самом деле Excel создает модель данных, которую можно использовать глобально во всей книге в любом количестве сводных таблиц и диаграмм, а также отчетов Power View. Для этой модели фундаментальными являются связи между таблицами, определяющие пути навигации и вычисления данных в отчете сводной таблицы. Выполняя следующую задачу, вы вручную создадите связи для подключения к только что импортированным данным.
Добавление связей
Вы можете систематически создавать связи для всех новых таблиц, которые вы импортируете. Если вы используете книгу совместно с сотрудниками, следует заранее определить связи на случай, если сотрудники не ориентируются в данных так же хорошо, как вы.
Создавая связи вручную, вы будете работать с двумя таблицами одновременно. Для каждой таблицы можно выбрать столбцы, которые указывают приложению Excel метод поиска связанных строк в другой таблице.
Связывание ProductSubcategory с ProductCategory
- В Excel выберите Данные > Отношения > Создать.
- В области "Таблица" выберите DimProductSubcategory.
- В области "Столбец (внешний)" выберите ProductCategoryKey.
- В области "Связанная таблица" выберите Table_ProductCategory.accdb.
- В области "Связанный столбец (основной)" выберите ProductCategoryKey.
- Нажмите кнопку ОК.
- Закройте диалоговое окно Управление связями.
Добавление категорий в сводную таблицу
Хотя в модель данных были добавлены дополнительные таблицы и связи, они еще не используются в сводной таблице. В этой задаче в список полей сводной таблицы добавляется ProductCategory.
- В области полей сводной таблицы выберите пункт Все, чтобы отобразить таблицы, присутствующие в модели данных.
- Прокрутите список вниз.
- В области "Строки" удалите BrandName.
- Разверните узел Table_DimProductCategories.accdb.
- Перетащите ProductCategoryName в область "Строки", поместив его над ProductSubcategory.
- В области полей сводной таблицы выберите пункт Активны, чтобы только что использованные таблицы стали использоваться в сводной таблице.
Контрольная точка: повторите изученный материал
Вы создали сводную таблицу, которая содержит данные из нескольких таблиц, импортированных на предыдущем этапе. Чтобы свести данные воедино, потребовалось создать связи таблиц, которые Excel использует для согласования строк. Вы узнали, что наличие столбцов с данными о сопоставлении необходимо для поиска связанных строк. В образце данных все таблицы содержат столбец, который можно использовать с этой целью.
Хотя сводная таблица работает, вы, возможно, заметили некоторые оставшиеся недочеты. Список полей сводной таблицы выглядит так, будто в нем есть лишние таблицы (DimEntity) и столбцы (ETLLoadID), никак не связанные с компанией Contoso. Кроме того, данные из таблицы Geography все еще не интегрированы.
Далее: просмотр и расширение модели с помощью Power Pivot
В следующей серии задач вы расширите модель с помощью надстройки Microsoft Office Power Pivot в Microsoft Excel 2013. Вы узнаете, что проще всего создавать связи с помощью представления диаграммы, предусмотренного в этой надстройке. Кроме того, вам предстоит использовать эту надстройку для создания вычислений и иерархий, скрытия элементов, которые не должны отображаться в списке полей, и оптимизации данных для дополнительных отчетов.
ПРИМЕЧАНИЕ. Надстройка Power Pivot в Microsoft Excel 2013 доступна в Office профессиональный плюс.
Добавьте Power Pivot на ленту Excel, включив надстройку Power Pivot.
- Перейдите на вкладку Файл > Параметры > Надстройки.
- В поле Управление выберите Надстройки COM> Перейти.
- Установите флажок Microsoft Office Power Pivot в Microsoft Excel 2013, а затем нажмите кнопкуОК.
На ленте появится вкладка Power Pivot.
Добавление связи с помощью представления диаграммы в Power Pivot
- В Excel выберите Лист3, чтобы сделать его активным. Лист3 содержит импортированную ранее таблицу Geography.
- На ленте выберите Power Pivot > Добавить в модель данных. На этом этапе таблица Geography будет добавлена в модель. Также откроется надстройка Power Pivot, которую можно использовать для выполнения оставшихся этапов задачи.
- Обратите внимание, что в окне Power Pivot отображаются все таблицы модели, включая таблицу Geography. Просмотрите несколько таблиц. В этой надстройке вы можете просматривать все данные, содержащиеся в модели.
- В окне Power Pivot в разделе "Вид" выберите Представление диаграммы.
- С помощью полосы прокрутки измените размер диаграммы таким образом, чтобы видеть все объекты в диаграмме. Обратите внимание, что две таблицы не связаны с остальной диаграммой: DimEntity и Geography.
- Щелкните правой кнопкой мыши DimEntity, а затем нажмите Удалить. Эта таблица является частью исходной базы данных и не нужна в модели.
- Настройте масштаб в таблице Geography таким образом, чтобы было видно все ее поля. Можно увеличить диаграмму таблицы с помощью ползунка.
- Обратите внимание, что таблица Geography содержит столбец GeographyKey. В этом столбце находятся значения, которые являются уникальными идентификаторами каждой строки таблицы Geography. Давайте определим, используют ли другие таблицы в этой модели такой же ключ. Если это так, мы сможем создать связь, которая соединит таблицу с остальной частью модели.
- Выберите Найти.
- В поле "Поиск метаданных" введите GeographyKey.
- Несколько раз нажмите кнопку Найти далее. Значение GeographyKey будет найдено в таблицах Geography и Stores.
- Перетащите таблицу Geography к таблице Stores.
- Перетащите столбец GeographyKey в таблице Stores на столбец GeographyKey в таблице Geography. Power Pivot проведет черту между двумя столбцами, обозначающую связь.
В этой задаче вы узнали новый способ добавления таблицы и создания связей. Теперь у вас полностью интегрированная модель, в которой все таблицы соединены и доступны для сводной таблицы на листе Лист1.
СОВЕТ. В представлении схемы некоторые схемы таблиц полностью расширены и отображают столбцы ETLLoadID, LoadDate и UpdateDate. Эти конкретные поля являются частью исходного хранилища данных Contoso и добавлены для поддержки операций извлечения и загрузки данных. В вашей модели они не нужны. Чтобы избавиться от них, выделите и щелкните поля правой кнопкой мыши, а затем нажмитеУдалить .
Создание вычисляемого столбца
В надстройке Power Pivot вы можете использовать формулы DAX, чтобы добавить вычисления. В этой задаче вы вычислите итоговую прибыль и добавите вычисляемый столбец, который ссылается на значения данных из других таблиц. Позже вы узнаете, как использовать ссылочные столбцы для упрощения модели.
- В окне Power Pivot вернитесь к представлению данных.
- Дайте таблице Table_ProductCategories accdb более понятное имя. Вы будете ссылаться на эту таблицу на следующих этапах, и более короткое имя упростит чтение вычислений. Щелкните правой кнопкой мыши имя таблицы, а затем нажмите Переименовать, введите имя ProductCategories и нажмите клавишу ВВОД.
- Выберите таблицу FactSales.
- Выберите Конструктор > Столбцы > Добавить.
- В строке формул над таблицей введите следующую формулу. Функция автозаполнения поможет ввести полные имена столбцов и таблиц и покажет доступные функции. Вы также можете просто щелкнуть столбец, и Power Pivot добавит его имя в формулу.
= [SalesAmount] - [TotalCost] - [ReturnAmount]
- Когда вы закончите вводить формулу, нажмите клавишу ВВОД, чтобы подтвердить ее.
Все строки вычисляемого столбца будут заполнены значениями. При прокрутке таблицы вниз будет видно, что строки могут содержать различные значения для данного столбца, на основе данных, которые содержатся в каждой строке.
- Переименуйте столбец, щелкнув правой кнопкой CalculatedColumn1 и выбрав Переименовать столбец. Введите Profit и нажмите клавишу ВВОД.
- Теперь выберите таблицу DimProduct.
- Выберите Конструктор > Столбцы > Добавить.
- В строке формул над таблицей введите следующую формулу.
= RELATED(ProductCategories[ProductCategoryName])
Функция RELATED возвращает значение из связанной таблицы. В нашем случае таблица ProductCategories содержит названия категорий продуктов, которые потребуется добавить в таблицу DimProduct, когда вы построите иерархию, которая включает сведения о категориях.
- Когда вы закончите вводить формулу, нажмите клавишу ВВОД, чтобы подтвердить ее.
Все строки вычисляемого столбца будут заполнены значениями. При прокрутке таблицы вниз будет видно, что в каждой строке содержится имя категории продукта.
- Переименуйте столбец, щелкнув правой кнопкой мыши CalculatedColumn1 и выбрав Переименовать столбец. Введите ProductCategory и нажмите клавишу ВВОД.
- Выберите Конструктор > Столбцы > Добавить.
- В строке формул над таблицей введите следующую формулу, а затем нажмите клавишу ВВОД, чтобы подтвердить ее.
= RELATED(DimProductSubcategory[ProductSubcategoryName])
- Переименуйте столбец, щелкнув правой кнопкой мыши CalculatedColumn1 и выбрав Переименовать столбец. Введите ProductSubcategory и нажмите клавишу ВВОД.
Создание иерархии
Большинство моделей содержат данные, являющиеся по своей природе иерархическими. Распространенные примеры: данные календаря, географические данные и категории продуктов. Создание иерархий полезно тем, что позволяет перетаскивать элемент (иерархию) в отчет, а не собирать и упорядочивать одни и те же поля каждый раз заново.
- В Power Pivot перейдите в представление диаграммы. Разверните таблицу DimDate, чтобы было проще работать с ее полями.
- Нажмите и удерживайте клавишу CTRL и щелкните столбцы CalendarYear, CalendarQuarter и CalendarMonth (потребуется выполнить прокрутку вниз в таблице).
- Выбрав три столбца, щелкните правой кнопкой мыши один из них и нажмите кнопку Создать иерархию. В нижней части таблицы будет создан родительский узел иерархии Hierarchy 1, а выбранные столбцы будут скопированы в иерархию в качестве дочерних узлов.
- Введите в качестве имени новой иерархии Dates.
- Добавьте в иерархию столбец FullDateLabel. Щелкните правой кнопкой мыши FullDateLabel и выберите пункт Добавить в иерархию. Выберите тип Дата. Столбец FullDateLabel содержит дату в полном формате, включая год, месяц и день. Убедитесь, что столбец FullDateLabel появился в иерархии в самом низу. Теперь у вас есть многоуровневая иерархия, которая включает год, квартал, месяц и отдельные календарные дни.
- Оставаясь в представлении диаграммы, выберите таблицу DimProduct и нажмите кнопку Создать иерархию в заголовке таблицы. В нижней части таблицы появится пустой родительский узел иерархии.
- Введите в качестве имени новой иерархии Product Categories.
- Чтобы создать дочерние узлы иерархии, перетащите в иерархию столбцы ProductCategory и ProductSubcategory.
- Щелкните правой кнопкой мыши ProductName и выберите пункт Добавить в иерархию. Выберите Product Categories.
Теперь, когда вы знаете несколько способов создания иерархии, воспользуемся ими в сводной таблице.
- Вернитесь назад в Excel.
- На Листе1 (этот лист содержит сводную таблицу) удалите поля в области "Строки".
- Замените их в новой иерархией Product Categories в DimProduct.
- Аналогичным образом замените CalendarYear в области "Столбцы" иерархией Dates из DimDate.
Теперь при просмотре данных легко заметны преимущества иерархий. Вы можете независимо разворачивать и закрывать различные области сводной таблицы, что обеспечивает более полный контроль над использованием доступного места. Кроме того, добавление единой иерархии для областей "Строки" и "Столбцы" обеспечивает возможность мгновенной детализации без необходимости нагромождать несколько полей для получения аналогичного эффекта.
Скрытие столбцов
После создания иерархии Product Categories и ее размещения в DimProductDimProductCategory или DimProductSubcategory в списке полей сводной таблицы больше не нужны. В этой задаче вы узнаете, как скрыть лишние таблицы и столбцы, которые занимают место в списке полей сводной таблицы. Скрытие таблиц и столбцов позволяет оптимизировать работу с отчетами, не затрагивая модель, которая предоставляет связи и вычисления данных.
Можно скрыть как отдельные столбцы, так и их диапазон, а также всю таблицу. Имена столбцов и таблиц становятся неактивными, что отражает их скрытое состояние для клиентских средств подготовки отчетов, использующих модель. Скрытые столбцы отображаются в модели серым, но остаются видимыми в представлении данных, чтобы с ними можно было работать.
- Убедитесь, что в Power Pivot выбрано представление данных.
- На вкладках в нижней части экрана щелкните правой кнопкой мыши DimProductSubcategory и выберитеСкрыть в клиентских средствах.
- Повторите для ProductCategories.
- Откройте среду DimProduct.
- Щелкните правой кнопкой мыши следующие столбцы и выберите пункт Скрыть в клиентских средствах.
- ProductKey
- ProductLabel
- ProductSubcategory
- Выделите несколько смежных столбцов, начиная с ClassID и заканчивая ProductSubcategory. Щелкните правой кнопкой мыши, чтобы скрыть их.
- Повторите это действие с другими таблицами, удалив идентификаторы, ключи и другие подробные сведения, которых не должно быть в отчете.
Перейдите обратно в Excel в список полей сводной таблицы на Листе1, чтобы увидеть различия. Количество таблиц сократится, и в DimProduct будут только те элементы, которые, скорее всего, пригодятся при анализе продаж.
Создание отчета Power View
Отчеты сводной таблицы - это не единственный тип отчетов, с которым удобно работать с помощью модели данных. Пользуясь только что построенной моделью, можно добавить лист Power View, чтобы испробовать некоторые из макетов, предоставляемых этим средством.
- В Excel выберите Вставка > Power View.
ПРИМЕЧАНИЕ. Если вы впервые используете Power View на этом компьютере, сначала отобразится запрос на включение надстройки и установку Silverlight.
- В области полей Power View щелкните стрелку возле таблицы FactSales, а затем щелкните SalesAmount.
- Разверните таблицу Geography и выберите столбец RegionCountryName.
- На ленте нажмите кнопку Карта.
- Появится отчет карты. Перетащите угол, чтобы изменить его размер. На карте синими кругами разного размера отмечены объемы продаж для различных стран и регионов.
Оптимизация для отчетов Power View
Если вы внесете в свою модель несколько небольших изменений, отчет Power View получится более понятным. Выполняя эту задачу, вы добавите URL-адреса веб-сайтов нескольких производителей, а затем отнесете эти данные к категории "URL-адрес веб-сайта", чтобы они отображались в виде ссылок.
Сперва добавьте в книгу URL-адреса.
- В Excel откройте новый лист и скопируйте следующие значения:
| MANUFACTURERURL | MANUFACTURERID |
|---|---|
| http://www.contoso.com | Contoso, LTD |
| http://www.adventure-works.com | Adventure Works |
| http://www.fabrikam.com | Fabrikam, Inc. |
- Сформируйте таблицу, отформатировав ячейки, и задайте таблице имя URL.
- Создайте связь между таблицей URL и таблицей, которая содержит названия производителей, DimProduct.
- Выберите Данные > Отношения. Появится диалоговое окно "Создание связи".
- Нажмите Создать.
- В области "Таблица" выберите DimProduct.
- В области "Столбец" выберите Manufacturer.
- В области "Связанная таблица" выберите URL.
- В области "Связанный столбец (основной)" выберите ManufacturerID.
Чтобы сравнить предыдущий вариант с результатом, создайте новый отчет Power View и добавьте туда поля SalesAmount из таблицы FactSales, Manufacturer из таблицы dimProduct и ManufacturerURL из таблицы URL. Обратите внимание, что URL-адреса отображаются как обычный текст.
Чтобы URL-адреса отображались как активные гиперссылки, нужно отнести их к соответствующей категории. Используйте для этого Power Pivot.
- В Power Pivot откройте таблицу URL.
- Выберите столбец ManufacturerURL.
- Нажмите кнопку Дополнительно > Свойства отчетов > Категория данных: без категорий.
- Щелкните стрелку вниз.
- Выберите вариант URL-адрес веб-сайта.
- В Excel выберите Вставка > Power View.
- В области полей Power View выберите поля SalesAmount из таблицы FactSales, Manufacturer из таблицы dimProduct и ManufacturerURL из таблицы URL. Теперь URL-адреса будут отображаться как гиперссылки.
К другим мерам по оптимизации Power View относятся определение набора полей по умолчанию для каждой таблицы и свойств параметров, определяющих способ обработки повторяющихся данных: статистическая или независимая обработка.
Создание вычисляемых полей
В ходе второй задачи ("Просмотр данных в сводной таблице") вы выбрали поле SalesAmount из списка полей сводной таблицы. Поскольку SalesAmount является числовым столбцом, он был автоматически добавлен в область "Значения" сводной таблицы. На том этапе по столбцу SalesAmount можно было рассчитать суммы продаж, применяя любые фильтры. В этом случае фильтры сначала не применялись, но затем использовались CalendarYear, ProductSubcategoryName и BrandName.
На самом деле на том этапе вы создали неявное вычисляемое поле, упростив анализ сумм продаж из таблицы FactSales относительно других полей, содержащих категории продуктов, регионы и даты. Неявные вычисляемые поля создаются в Excel, когда вы перетаскиваете поле в область "Значения" или когда выбираете числовое поле, как в случае с полем SalesAmount. Неявные вычисляемые поля - это формулы, которые используют такие стандартные статистические функции, как СУММ, СЧЁТ и СРЗНАЧ, и создаются для вас автоматически.
Существуют и другие типы вычисляемых полей. В Power Pivot вы можете создавать явные вычисляемые поля. В отличие от неявных вычисляемых полей, которые можно использовать только в той сводной таблице, где они были созданы, явные вычисляемые поля можно использовать в любой сводной таблице в книге, а также в любом отчете, который использует в качестве источника данных модель данных. С помощью явных вычисляемых полей, созданных в Power Pivot, вы можете использовать автосуммирование, чтобы автоматически получать вычисляемые поля со стандартными агрегатными функциями или создавать собственные вычисляемые поля при помощи формул с выражениями анализа данных (DAX).
Теперь вы понимаете, что, создавая вычисляемые поля, вы сможете анализировать данные бесчисленным количеством действенных способов. Давайте узнаем, как создавать эти поля.
Создавать вычисляемые поля в Power Pivot легко, если воспользоваться функцией Автосумма.
- В таблице FactSales выберите столбец Profit.
- Выберите Вычисления > Автосумма. Обратите внимание, что было создано новое вычисляемое поле с названием Sum of Profit в ячейке области вычислений прямо под столбцом Profit.
- В Excel на Листе1 в списке полей выберите в таблице FactSales вычисляемое поле Sum of Profit.
Готово! Как видите, с помощью стандартных агрегатных функций мы всего за несколько минут создали в Power Pivot вычисляемое поле Sum of Profit и добавили его в сводную таблицу. Теперь можно быстро анализировать прибыль, применяя различные фильтры. В этом случае вы видите столбец Sum of Profit, значения которого отфильтрованы согласно иерархиям Product Category и Dates.
Но что делать, если вам необходимо провести более углубленный анализ, например подсчитать продажи по определенному каналу, продукту или категории? Для этого вам нужно создать другое вычисляемое поле, которое вычисляет количество строк, по одной для каждой продажи из таблицы FactSales, в зависимости от используемых фильтров.
- В таблице FactSales выберите столбец SalesKey.
- В области Вычисления щелкните стрелку вниз под кнопкой Автосумма > СЧЁТ.
- Переименуйте новое вычисляемое поле, щелкнув правой кнопкой мыши столбец Count of SalesKey в области вычислений и выбрав команду Переименовать. Введите Count и нажмите клавишу ВВОД.
- В Excel на Листе1 в списке полей выберите FactSales и нажмите Count.
Обратите внимание, что в сводную таблицу был добавлен новый столбец Count, который отображает количество продаж в зависимости от применяемых фильтров. Как и в случае с вычисляемым столбцом Sum of Profit, вычисляемое поле Count отфильтровано согласно иерархиям Product Category и Dates.
Давайте потренируемся еще. На этот раз вы создадите вычисляемое поле, которое вычисляет процент от суммы продаж в соответствии с конкретным контекстом или фильтром. Однако в отличие от предыдущих вычисляемых полей, которые вы создавали с помощью функции "Автосумма", на этот раз необходимо будет ввести формулу вручную.
- В таблице FactSales в области вычислений выберите пустую ячейку. Совет: удобнее всего будет размещать вычисляемые поля, начиная с ячейки в левом верхнем углу. Таким образом их будет легче найти. Вы можете перемещаться в любом вычисляемом поле в области вычислений.
- В строке формул введите следующую формулу, используя IntelliSense:
Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct)) - Нажмите клавишу ВВОД, чтобы подтвердить формулу.
- В Excel на Листе1 в списке полей в таблице FactSales выберите Percentage of All Products.
- В сводной таблице выберите несколько столбцов Percentage of All Products.
- На вкладке Главная выберите Число > Процентный формат. Для форматирования новых столбцов используйте два десятичных знака после запятой.
Это новое вычисляемое поле вычисляет процент от суммы продаж для заданного контекста фильтра. В нашем случае фильтрами по-прежнему выступают иерархии Product Category и Dates. Среди прочего вы можете увидеть, что процент компьютеров от суммы продаж продуктов со временем увеличился.
Для вас не составит труда создавать формулы для вычисляемых столбцов и полей, если вы знакомы с процедурой создания формул Excel. Но, независимо от того, знакомы вы с формулами Excel или нет, у вас есть отличная возможность изучить основные формулы DAX, пройдя уроки из электронной книги Краткое руководство: основы DAX за 30 минут.
Сохранение работы
Сохраните книгу, чтобы можно было использовать ее с другими учебниками или для дальнейшего изучения.
Следующие шаги
Хотя можно легко импортировать данные из Excel, часто быстрее и эффективнее оказывается импорт с помощью надстройки Power Pivot. Вы можете отфильтровать импортируемые данные, исключив ненужные столбцы. Вы также можете решить, будет ли выполняться извлечение данных с помощью построителя запросов или команды запроса. В качестве следующего шага изучите следующие альтернативные способы:Получение данных из веб-канала данных в Power Pivot и Импорт данных из служб Analysis Services или Power Pivot.
Отчеты Power View предназначены для работы с моделями данных, аналогичными только что построенной вами. Дополнительные материалы о расширенном представлении, которое Power View обеспечивает в Excel:Включение Power View в приложении Excel 2013 и Power View: исследование, визуализация и представление данных.
Попробуйте усовершенствовать свою модель данных, чтобы в дальнейшем создавать более наглядные отчеты Power View, изучив следующий Учебник. Оптимизация модели данных для отчетов Power View.