Преимущества сводной по Модели Данных

При построении сводной таблицы в Excel в первом же диалоговом окне, где нас просят задать исходный диапазон и выбрать место для вставки сводной, есть внизу неприметная, но очень важная галочка - Добавить эти данные в Модель Данных (Add this data to Data Model) и, чуть выше, переключатель Использовать модель данных этой книги (Use Data Model of this workbook):

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

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

Однако, перед тем, как рассматривать эти "плюшки" вблизи, давайте сначала разберёмся с тем, что такое, собственно, эта Модель Данных?

Что такое Модель Данных

Модель Данных (сокращенно - МД или DM=Data Model) - это специальная область внутри файла Excel, куда можно где можно хранить табличные данные - одну или несколько таблиц связанных, при желании, между собой. По сути, это маленькая база данных (OLAP-куб), встроенная внутрь книги Excel. По сравнению с классическим хранением данных в виде обычных (или умных) таблиц на листах самого Excel, у Модели Данных есть несколько серьезных преимуществ:

  • Размер таблиц может достигать 2 млрд. строк, а на лист Excel вмещается чуть больше 1 млн.
  • Не смотря на гигантские размеры, обработка таких таблиц (фильтрация, сортировка, вычисления по ним, построение сводных и т.д.) выполняются очень быстро - гораздо быстрее, чем в самом Excel.
  • С данными в Модели можно производить дополнительные (при желании - весьма сложные) вычисления с помощью встроенного языка DAX.
  • Вся информация, загруженная в Модель Данных, очень сильно сжимается с помощью специального встроенного архиватора и весьма умеренно увеличивает размер исходного Excel-файла.

Управлением Моделью и вычислениями по ней занимается специальная встроенная в Microsoft Excel надстройка - Power Pivot, о которой я уже писал. Чтобы её включить, на вкладке Разработчик нажмите кнопку Надстройки COM (Developer - COM Add-ins) и поставьте соответствующую галочку:

Подключаем надстройку Power Pivot

Если вкладки Разработчик (Developer) у вас на ленте не видно, то включить её можно через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon). Если же в показанном выше окне в списке COM-надстроек у вас нет Power Pivot, то значит она не входит в вашу версию Microsoft Office :(

На появившейся вкладке Power Pivot будет большая салатового цвета кнопка Управление (Manage), нажатие на которую и откроет поверх Excel окно Power Pivot, где мы и увидим содержимое Модели Данных текущей книги:

Главное окно Power Pivot

Важное замечание по ходу: книга Excel может содержать только одну Модель Данных.

Грузим таблицы в Модель Данных

Для загрузки данных в Модель сначала превращаем таблицу в динамическую "умную" сочетанием клавиш Ctrl+T и даём ей понятное имя на вкладке Конструктор (Design). Это обязательный этап.

Затем можно использовать любой из трех способов, на выбор:

  • Жмём кнопку Добавить в модель (Add to Data Model) на вкладке Power Pivot на вкладке Главная (Home).
  • Выбираем команды Вставка - Сводная таблица (Insert - Pivot Table) и включаем флажок Добавить эти данные в Модель данных (Add this data to Data Model). В этом случае по загруженным в Модель данным сразу строится ещё и сводная таблица.
  • На вкладке Данные (Data) жмём на кнопку Из таблицы/диапазона (From Table/Range), чтобы загрузить нашу таблицу в редактор Power Query. Этот путь самый долгий, но, при желании, здесь можно произвести дополнительную зачистку данных, правки и всяческие трансформации, в которых Power Query очень силён.
    Затем причёсанные данные выгружаются в Модель командой Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...). В открывшемся окне выбираем вариант Только создать подключение (Only create connection) и, главное, ставим галочку Добавить эти данные в Модель данных (Add this data to Data Model).

Строим сводную по Модели Данных

Чтобы построить сводную Модели Данных можно использовать любой из трёх подходов:

  • Нажать кнопку Сводная таблица (Pivot Table) в окне Power Pivot.
  • Выбрать в Excel команды Вставка - Сводная таблица и переключиться в режим Использовать модель данных этой книги (Insert - Pivot Table - Use this workbook's Data Model).
  • Выбираем команды Вставка - Сводная таблица (Insert - Pivot Table) и включаем флажок Добавить эти данные в Модель данных (Add this data to Data Model). Текущая "умная" таблица будет загружена в Модель и по всей Модели будет построена сводная таблица.

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

Преимущество 1. Связи между таблицами без помощи формул

Обычная сводная может быть построена только по данным из одной исходной таблицы. Если же у вас их несколько, например, продажи, прайс, справочник по клиентам, реестр договоров и т.д., то сначала придется собирать данные из всех таблиц в одну с помощью функций типа ВПР (VLOOKUP), ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH), СУММЕСЛИМН (SUMIFS) и им подобных. Это долго, муторно и вгоняет ваш Excel в "задумчивость" при большом количестве данных.

В случае сводной по Модели Данных всё гораздо проще. Достаточно один раз настроить связи между таблицами в окне Power Pivot - и дело в шляпе. Для этого на вкладке Power Pivot жмём кнопку Управление (Manage) и затем в появившемся окне - кнопку Представление диаграммы (Diagram View). Останется перетащить общие (ключевые) названия столбцов (поля) между таблицами, чтобы создать связи:

Создание связей между таблицами

После этого в сводной по Модели Данных можно закидывать в области сводной (строки, столбцы, фильтры, значения) любые поля из любых связанных таблиц - всё будет связываться и подсчитываться уже автоматически:

Строим сводну по модели

Преимущество 2. Подсчёт количества уникальных значений

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

Щёлкаем правой кнопкой мыши по полю - команда Параметры полей значений и на вкладке Операция выбираем Число разных элементов (Distinct count):

Посчет количества уникальных элементов

Преимущество 3. Свои формулы на языке DAX

Иногда в сводных таблицах приходится выполнять различные дополнительные вычисления. В обычных сводных это делается с помощью вычисляемых полей и объектов, а сводной по Модели Данных для этого используются меры на специальном языке DAX (DAX = Data Analysis Expressions).

Для создания меры выберите на вкладке Power Pivot команду Меры - Создать меру (Measures - New measure) или просто щёлкните правой кнопкой мыши по таблице в списке полей сводной и выберите Добавить меру (Add measure) в контекстном меню:

Добавляем меру

В открывшемся окне задаём:

Параметры меры

  • Имя таблицы, где созданная мера будет храниться.
  • Название меры - любое понятное вам имя для нового поля.
  • Описание - по желанию.
  • Формула - самое главное, т.к. здесь мы либо вручную вписываем, либо жмём на кнопку fx и выбираем из списка функцию DAX, которая должна вычислять результат, когда мы потом забросим нашу меру в область Значений.
  • В нижней части окна можно сразу задать для меры числовой формат в списке Категория.

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

Преимущество 4. Свои иерархии полей

Часто при создании типовых отчётов приходится забрасывать в сводные таблицы одни и те же комбинации полей в заданной последовательности, например Год-Квартал-Месяц-День, или Категория-Товар, или Страна-Город-Клиент и т.п. В сводной по Модели Данных эта проблема легко решается созданием собственных иерархий - пользовательских наборов полей.

В окне Power Pivot переключитесь в режим диаграммы кнопкой Представление диаграммы на вкладке Главная (Home - Diagram View), выделите с Ctrl нужные поля и щёлкните по ним правой кнопкой мыши. В контекстном меню будет команда Создать иерархию (Create hierarchy):

Создание иерархии полей

Созданную иерархию можно переименовать и перетащить в неё мышью требуемые поля, чтобы потом в одно движение забрасывать их в сводную:

Добавление иерархии в сводную

Преимущество 5. Свои наборы элементов

Продолжая идею предыдущего пункта, в сводной по Модели Данных можно создавать ещё и свои наборы элементов для каждого поля. Например, из всего списка городов можно легко сделать набор только из тех, которые входят в зону вашей ответственности. Или собрать в специальный набор только своих клиентов, свои товары и т.п.

Для этого на вкладке Анализ сводной таблицы в выпадающем списке Поля, элементы и наборы есть соответствующие команды (Analyze - Fields, Items & Sets - Create set based on row/column items):

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

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

Создаем набор

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

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

Преимущество 6. Выборочное скрытие таблиц и столбцов

Это хоть и небольшое, но весьма приятное в некоторых случаях преимущество. Щёлкнув правой кнопкой мыши по названию поля или по ярлычку таблицы в окне Power Pivot, можно выбрать команду Скрыть из набора клиентских средств (Hide from Client Tools):

Скрываем поле или таблицу от пользователя

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

Преимущество 7. Продвинутый drill-down

Если в обычной сводной таблице сделать двойной щелчок левой кнопкой мыши по любой ячейке в области значений, то Excel выводит на отдельном листе копию фрагмента исходных данных, которые участвовали в расчёте этой ячейки. Это очень удобная штука, официально называющаяся Drill-down (на русском обычно говорят "провалиться").

В сводной по Модели Данных этот удобный инструмент работает более тонко. Встав на любую интересующую нас ячейку с результатом, можно щёлкнуть по всплывающему рядом значку с лупой (он называется Экспресс-тенденции) и выбрать затем любое интересующее вас поле в любой связанной таблице:

Экспресс-тенденции

После этого текущее значение (Модель = Explorer) уйдет в область фильтра, а сводная будет построена уже по офисам:

Результат drill-down

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

Преимущество 8. Преобразование сводной в функции кубов

Если выделить любую ячейку в сводной по Модели Данных и выбрать затем на вкладке Анализ сводной таблицы команду Средства OLAP - Преобразовать в формулы (Analyze - OLAP Tools - Convert to formulas), то вся сводная будет автоматически преобразована в формулы. Теперь значения полей в области строк-столбцов и результаты в области значений будут извлекаться из Модели Данных с помощью специальных функций кубов: КУБЗНАЧЕНИЕ и КУБЭЛЕМЕНТ:

Преобразование сводной в функции кубов

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

При этом связь с исходными данными, само-собой, остается и в будущем эти формулы будут обновляться при изменении источников. Красота!

Ссылки по теме

 


26.02.2021 22:47:32
Николай ,а видео будет ? Тема оч интересная.
27.02.2021 12:30:51
Нашел в уроках. Спасибо
01.03.2021 12:03:05
А как обновить в OLAP, через Pivot? только через него или есть отдельная клавиша какая-то? И да, спасибо огромноееееее!!!!!! МИР ПЕРЕВЕРНУЛСЯ В ОЧЕРЕДНОЙ РАЗ!
02.03.2021 12:43:59
Видимо надстройка PowerPivot не во всех версиях офис 2016, можно ссылку для установки надстройки вручную?
02.03.2021 13:38:51
Вот, ознакомьтесь.
В 2016 как отдельную надстройку его установить нельзя, нужно устанавливать версию продукта, который её содержит. Иначе никак.
02.03.2021 14:30:47
Да, к сожалению тогда я не смогу воспользоваться этой фичей.
02.03.2021 14:25:48
Николай, я еще хотела спросить, группировать в сводной по модели, я так понимаю, не получится, как раньше? или это у меня. Вроде на разных проверяла. Нужно создавать наборы данных?
02.03.2021 15:43:29
Группировать можно, но для этого используются другие инструменты: можно создать вычисляемые столбцы для таблицы в модели данных и их уже выводить в сводную. Можно создать вообще отдельную не связанную таблицу, а результат выводить при помощи хитроделанной меры.
В общем если будет конкретная задача, приходите на форум, поможем, чем сможем.
02.03.2021 17:13:45
Спасибо)))) пока метод научного тыка тоже очень помогает))) два дня не могу оторваться, вот такое интересное хобби у людей
04.03.2021 13:17:22
Преобразование сводной в функции кубов  -  супер спасибо.

Но есть вопрос.
Кода преобразовал сводную в обычную таблицу функцией кубов, данные подгружаются из папок. В названия столбцов засунул даты предварительно ограничил фильтром в PQ  "в предыдущие  30 дней".
Но итоговая таблица не обновила последний день, а вывела в столбцы #Н/Д. Как этого избежать?
07.03.2021 11:05:15
У меня версия microsoft365, но не работает drill-down. Даже в скачанном примере. Как это можно подправить?
09.03.2021 18:15:07
Это как понимать? Ошибку выдает или просто игнорирует дабл-клик?
09.03.2021 18:42:22
В статье написано "Встав на любую интересующую нас ячейку с результатом, можно щёлкнуть по всплывающему рядом значку с лупой (он называется Экспресс-тенденции) и выбрать затем любое интересующее вас поле в любой связанной таблице:....."  Не появляется значок с лупой.  
11.03.2021 11:18:26
Странно, у меня и в О2016 и в О365 все отлично фурычит, правда оно лично мне скорее мешает. :D И хотелось бы как-нибудь эту штуку, наоборот отключить.
11.03.2021 18:59:49
Я бы с радостью поменялся бы с Вами своим м365, чтобы избавить от "назойливой и мешающей" функции:D
10.03.2021 13:53:52
Аналогичная проблема. Двойное нажатие работает, но всплывающего окна Экспресс-тенденции нет. Что делать?
11.03.2021 11:22:47
Кстати, преимущество из статьи за номером 6, можно в том числе использовать для блокировки возможности изменения макета сводной таблицы со стороны пользователя. Для этого нужно один раз выставить все измерения (строки/столбцы) сводной, настроить срезы, после чего скрыть, нафиг, все таблицы в модели, а затем защитить книгу. В этом случае доступ в модель данных будет только по паролю, и юзер сможет пользоваться сводной только в том виде, как настроено изначально.
05.04.2021 18:11:27
Коллеги Николай  помогите пжста: нужна работа с текстовыми  многочисленными  таблицами ( каталог сервисов делаю) Все справочники связала в  модель данных Однако сводная таблица по данной МД  не помогает показать по определенному сервису всю связанную инфо из разых (связанных)  справочников Кто ниб решал подобную задачу? подскажите пжста что делаю не так
15.04.2021 08:47:55
Николай, пытаюсь создать связь между таблицами, но связь создается "один ко многим". В итоге к одному значению подтягиваются разные значения из другой таблицы. Можно как-то создать связь "один к одному"?
Наверх