Учебник. Анализ данных сводных таблиц с помощью модели данных в Excel 2013

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

Импорт данных

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

  1. Скачайте образец данных (ContosoV2) для этого учебника. Подробнее об этом можно узнать в разделе Получение образцов данных для DAX и учебников Модели данных. Извлеките файлы данных и сохраните их в удобном месте, например в папке "Загрузки" или "Мои документы".

  2. Откройте в Excel пустую книгу.

  3. Выберите Данные > Получение внешних данных > Из Access.

  4. Перейдите в папку, содержащую файлы с образцом данных, и выберите ContosoSales.

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

    Диалоговое окно выбора таблицы

  6. В диалоговом окне "Выбор таблицы" установите флажок Разрешить выбор нескольких таблиц.

  7. Выберите все таблицы и нажмите кнопку ОК.

  8. На вкладке "Импорт данных" выберите Отчет сводной таблицы и нажмите кнопку ОК.

Что произошло?

Возможно, вы еще не заметили этого, но вы создали модель данных. Она автоматически создается при одновременном импорте нескольких таблиц или работе с ними в одном отчете сводной таблицы. Модель практически не заметна в Excel, но вы можете просматривать и изменять ее с помощью надстройки Power Pivot. В Excel на наличие модели данных указывает отображение набора таблиц в списке полей сводной таблицы. Существует несколько способов создать модель. Дополнительные сведения см. в статье Создание модели данных в 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. Нажмите кнопку ОК.

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

Добавление связей

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

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

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

Связывание 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 в Microsoft Excel 2013.

Добавьте 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 добавит его имя в формулу.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

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

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

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

  8. Теперь выберите таблицу DimProduct.

  9. Выберите Конструктор > Столбцы > Добавить.

  10. В строке формул над таблицей введите следующую формулу.

    = RELATED(ProductCategories[ProductCategoryName])

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

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

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

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

  13. Выберите Конструктор > Столбцы > Добавить.

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

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Переименуйте столбец, щелкнув правой кнопкой мыши 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 и ее размещения в DimProduct поля DimProductCategory или DimProductSubcategory в списке полей сводной таблицы больше не нужны. В этом разделе вы узнаете, как скрыть лишние таблицы и столбцы, которые занимают место в списке полей сводной таблицы. Скрытие таблиц и столбцов позволяет оптимизировать работу с отчетами, не затрагивая модель, которая предоставляет связи и вычисления данных.

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

  1. Убедитесь, что в Power Pivot выбрано представление данных.

  2. На вкладках в нижней части экрана щелкните правой кнопкой мыши DimProductSubcategory и выберите команду Скрыть из набора клиентских средств.

  3. Повторите для ProductCategories.

  4. Откройте таблицу DimProduct.

  5. Щелкните правой кнопкой мыши следующие столбцы и выберите команду Скрыть из набора клиентских средств:

    • ProductKey

    • ProductLabel

    • ProductSubcategory

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

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

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

Создание отчета Power View

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

  1. В Excel выберите Вставка > Power View.

    Примечание :  Если вы впервые используете Power View на этом компьютере, сначала отобразится запрос на включение надстройки и установку Silverlight.

  2. В области полей Power View щелкните стрелку возле таблицы FactSales и выберите SalesAmount.

  3. Разверните таблицу Geography и выберите столбец RegionCountryName.

  4. На ленте нажмите кнопку Карта.

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

Оптимизация для отчетов Power View

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

Сперва добавьте в книгу URL-адреса.

  1. В Excel откройте новый лист и скопируйте следующие значения:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Отформатируйте ячейки в виде таблицы и присвойте ей имя URL.

  2. Создайте связь между таблицей URL и таблицей с названиями производителей, DimProduct.

    1. Выберите Данные > Отношения. Появится диалоговое окно "Создание связи".

    2. Нажмите Создать.

    3. В области "Таблица" выберите DimProduct.

    4. В области "Столбец" выберите Manufacturer.

    5. В области "Связанная таблица" выберите URL.

    6. В области "Связанный столбец (основной)" выберите ManufacturerID.

Чтобы сравнить предыдущий вариант с результатом, создайте новый отчет Power View и добавьте туда поля SalesAmount из таблицы FactSales, Manufacturer из таблицы dimProduct и ManufacturerURL из таблицы URL. Обратите внимание, что URL-адреса отображаются как обычный текст.

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

  1. Откройте URL-адрес в Power Pivot.

  2. Выберите столбец ManufacturerURL.

  3. Нажмите кнопку Дополнительно > Свойства отчетов > Категория данных: Без категории.

  4. Щелкните стрелку вниз.

  5. Выберите пункт "URL-адрес веб-сайта".

  6. В Excel выберите Вставка > Power View.

  7. В области полей Power View выберите поля SalesAmount из таблицы FactSales, Manufacturer из таблицы dimProduct и ManufacturerURL из таблицы URL. Теперь URL-адреса будут отображаться как гиперссылки.

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

Создание вычисляемых полей

В ходе второй задачи ("Просмотр данных в сводной таблице") вы выбрали поле SalesAmount из списка полей сводной таблицы. Поскольку SalesAmount является числовым столбцом, он был автоматически добавлен в область "Значения" сводной таблицы. На том этапе по столбцу SalesAmount можно было рассчитать суммы продаж, применяя любые фильтры. В этом случае фильтры сначала не применялись, но затем использовались CalendarYear, ProductSubcategoryName и BrandName.

На самом деле вы создали неявное вычисляемое поле, упростив анализ сумм продаж из таблицы FactSales относительно других полей, содержащих категории продуктов, регионы и даты. Неявные вычисляемые поля создаются в Excel, когда вы перетаскиваете поле в область "Значения" или выбираете числовое поле, как в случае с полем SalesAmount. Неявные вычисляемые поля — это формулы, которые используют такие стандартные статистические функции, как СУММ, СЧЁТ и СРЗНАЧ, и создаются для вас автоматически.

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

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

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

  1. В таблице FactSales выберите столбец Profit.

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

  3. В Excel на Листе1 в списке полей выберите в таблице FactSales вычисляемое поле Сумма Profit.

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

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

  1. В таблице FactSales выберите столбец SalesKey.

  2. В области Вычисления щелкните стрелку вниз рядом с кнопкой Автосумма и выберите Число.

  3. Переименуйте новое вычисляемое поле, щелкнув столбец Число SalesKey в области вычислений, изменив Число SalesKey на Число и нажав клавишу ВВОД. В отличие от вычисляемых столбцов, имена вычисляемых полей являются частью формулы DAX.

  4. В Excel на Листе1 в списке полей выберите FactSales и нажмите Count.

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

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

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

  2. В строке формул введите следующую формулу, используя IntelliSense: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

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

  4. В Excel на Листе1 в списке полей таблицы FactSales выберите Percentage of All Products.

  5. В сводной таблице выберите несколько столбцов Percentage of All Products.

  6. На вкладке Главная выберите Число > Процентный. Для форматирования новых столбцов используйте два десятичных знака после запятой.

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

Для вас не составит труда создавать формулы для вычисляемых столбцов и полей, если вы знакомы с процедурой создания формул 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.

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×