​​Прямой доступ к внутренней модели данных

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

Для того чтобы создать сводную таблицу на основе внутренней модели данных выполните следующие действия:
1.  На ленте приложения выберите команду Данные => Получить и преобразовать данные => Получить данные с помощью существующего подключения;
2.  В окне Существующие подключения перейдите на вкладку Таблицы и дважды щелкните на строке Таблицы в модели данных книги;
3.  В диалоговом окне Импорт данных установите переключатель Выберите способ представления данных в книге в положение Отчет сводной таблицы, переключатель Куда следует поместить данные? в положение Новый лист и щелкните на кнопке ОК;
4.  Перейдите на появившийся новый рабочий лист и, если панель задач Поля сводной таблицы еще отсутствует на экране, дважды щелкните мышью в любом месте только что созданной сводной таблицы, чтобы открыть ее. На панели задач Поля сводной таблицы должна быть открыта вкладка Все; в этом случае в списке полей будут представлены все таблицы, доступные во внутренней модели данных книги;
5.  Создайте требуемую сводную таблицу таким образом, как это обычно делается. В нашем случае поле Должность следует поместить в область Строки, а поле Стоимость_работ - в область Значения. Программа Excel немедленно обнаружила, что в сводной таблице используются две таблицы из внутренней модели данных, и предложила создать между ними необходимые связи. Вы можете позволить Excel автоматически выявить и установить требуемые отношения между таблицами либо вы можете щелкнуть на кнопке Создать и выполнить эту работу вручную. В общем случае связи между таблицами во внутренней модели лучше устанавливать вручную, что позволит избежать любых возможных ошибок, если программа поймет что-либо неправильно, поэтому щелкните на кнопке Создать - и программа откроет диалоговое окно Создание отношения;
6.  В диалоговом окне Создание отношения необходимо указать таблицы и их поля, которые будут использованы для создания отношения. Как видно в нашем случае в таблице Обслуживание поле Исполнитель связывается с полем Таб_номер в таблице Персонал.

#PowerPivot

Хотите освоить визуализацию данных на реальных задачах — попробуйте бесплатный курс-симулятор от Нетологии и команды DataLens.

Ваш персонаж — аналитик в крупной ритейл-компании, а каждое занятие — история с прописанным сюжетом. Вы построите интерактивную карту клиентов в DataLens, соберёте отчёт-конструктор расходов в Excel, сравните показатели в Power BI, а в конце наведёте красоту в Tableau.

Узнаете много нового и познакомитесь с основными инструментами визуализации данных. А ещё ваш дашборд может выиграть приз, подробнее →
https://netolo.gy/gAR

​​Управление связями во внутренней модели данных

Чтобы внести изменения в отношения между таблицами во внутренней модели данных, необходимо воспользоваться диалоговым окном Управление отношениями, которое открывается при выборе команды Данные => Работа с данными => Отношения.

В окне Управление отношениями присутствуют следующие командные кнопки, предназначенные для управления отношениями:
▪️ Создать. Создается новое отношение между двумя таблицами во внутренней модели данных.
▪️ Автоматическое обнаружение. Надстройке Power Pivot предоставляется возможность автоматически выявить и создать отношения между таблицами, исходя из сохраняемых в них данных.
▪️ Изменить. Предоставляется возможность внести изменения в выбранное отношение.
▪️ Активировать. Выбранное отношение вводится в силу, т.е. программе Excel указывается на необходимость принимать это отношение во внимание при обобщении и анализе информации с использованием внутренней модели данных рабочей книги.
▪️ Деактивировать Выбранное отношение отключается, т.е. программе Excel предписывается игнорировать это отношение при обобщении и анализе информации с использованием внутренней модели данных рабочей книги.
▪️ Удалить. Выбранное отношение удаляется.

#PowerPivot

​​Удаление таблицы из внутренней модели данных

В некоторых случаях может потребоваться удалить таблицу или источник данных из внутренней модели данных рабочей книги. Чтобы сделать это, на ленте программы выберите команду Данные => Запросы и подключения => Запросы и подключения. Откроется панель задач Запросы и подключения.
На этой панели задач щелкните правой кнопкой мыши на той таблице, которую следует удалить из внутренней модели данных (в нашем примере это таблица Обслуживание), и выберите в открывшемся контекстном меню команду Удалить. В открывшемся окне с запросом на подтверждение необходимости удаления щелкните на кнопке ОК для завершения операции.

#PowerPivot

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

В надстройке Power Pivot процедура создания вычисляемого столбца очень напоминает процедуру внесения формул в таблицу Excel. Проще всего познакомиться с этой процедурой на конкретном примере.
Чтобы создать в таблице надстройки Power Pivot вычисляемый столбец, выполните следующие действия:
1.  Выберите на ленте команду Power Pivot => Модель данных => Управление, чтобы открыть окно надстройки Power Pivot. В этом окне перейдите на вкладку Детали_счета.
2.  В таблице на этой вкладке крайним справа будет пустой столбец с названием Добавление столбца; щелкните на его верхней пустой ячейке.
3.  В строке формул окна Power Pivot введите следующую формулу:

=[Цена_единицы]*[Количество]

4.  Нажмите клавишу Enter - и введенная формула будет распространена по всем ячейкам этого столбца.
5.  Надстройка Power Pivot автоматически присвоит столбцу название Вычисляемый столбец 1, поэтому дважды щелкните на его заголовке и переименуйте его, - пусть он будет называться Общий_доход.

#PowerPivot

​Форматирование вычисляемых столбцов

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

1.  В окне приложения Power Pivot щелкните на любой ячейке того столбца, который требуется отформатировать;
2.  На ленте надстройки на вкладке Главная найдите группу команд Форматирование;
3.  Воспользуйтесь элементами управления в этой группе, чтобы придать значениям в столбце нужный вид.

#PowerPivot

​Ссылки на вычисляемые столбцы в других вычислениях

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

=[Общий_доход]-([Себестоимость_единицы]*[Количество])

В формуле используется ссылка на созданный ранее вычисляемый столбец Общий_доход.

#PowerPivot

​Сокрытие промежуточных вычисляемых столбцов

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

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

В надстройке Power Pivot столбцы таблиц в ее окне окрашиваются в зависимости от их характеристик. Так, скрытые столбцы отображаются затененными, в сером цвете, тогда как вычисляемые столбцы выделены более светлым оттенком серого и имеют темные (черные) заголовки.

#PowerPivot

Использование языка DAX при создании вычисляемых столбцов

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

#PowerPivot

​Функции языка DAX, используемые для создания вычисляемых столбцов

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

=SUM([Валовая_прибыль])

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

#PowerPivot

​Создание вычисляемых столбцов с использованием функций языка DAX

Чтобы продемонстрировать полезность использования функций языка DAX для расширения возможностей создания вычисляемых столбцов, давайте вернемся к нашему примеру. Перейдите в окно надстройки Power Pivot и щелкните на вкладке Заголовок_счета.
На вкладке Заголовок_счета, присутствует столбец Дата_счета. Хотя этот столбец достаточно важен и в своем исходном виде, в ходе анализа данных с использованием сводной таблицы работать с конкретными датами не всегда удобно. Будет гораздо удобнее создать отдельные столбцы для значений года и месяца, извлеченных из поля Дата_счета. Поступив так, мы получим возможность обобщать и анализировать данные по месяцам и годам.
Для этих целей воспользуемся функциями YEAR(), MONTH() и FORMAT() языка DAX. Чтобы добавить в модель данных вычисляемые столбцы с требуемыми размерностями времени, выполните следующие действия.
1.  В таблице 3аголовок_счета щелкните на верхней пустой ячейке в крайнем справа столбце с заголовком Добавление столбца.
2. В строке формул введите выражение =YEAR([Дата_счета]) и нажмите клавишу Enter. Надстройка Power Pivot автоматически переименует столбец, присвоив ему название Вычисляемый столбец 1 и заполнит его вычисленными функцией YEAR() значениями. Присвойте столбцу название Год
3.  Еще раз щелкните на верхней пустой ячейке в следующем (крайнем справа) столбце, который теперь имеет название Добавление столбца.
4.  В строке формул введите выражение =MONTH([Дата_счета]) и нажмите клавишу Enter. Надстройка Power Pivot автоматически переименует столбец, присвоив ему название Вычисляемый столбец 1 и заполнит его вычисленными функцией MONTH() значениями. Присвойте этому столбцу название №_месяца.
5. И вновь щелкните на верхней пустой ячейке в следующем (крайнем справа) столбце, который теперь имеет название Добавление столбца.
6.  В строке формул введите выражение =FORMAT([Дата_счета]; "mmmm") и нажмите клавишу Enter. Надстройка Power Pivot автоматически переименует столбец, присвоив ему название Вычисляемый столбец 1 и заполнит его вычисленными функцией FORMAT() значениями. Присвойте этому столбцу название Месяц

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

#PowerPivot

Power Pivot. Ссылки на поля в других таблицах

https://telegra.ph/Ssylki-na-polya-v-drugih-tablicah-06-03-4