Преимущества сводной по Модели Данных
При построении сводной таблицы в 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) и поставьте соответствующую галочку:
Если вкладки Разработчик (Developer) у вас на ленте не видно, то включить её можно через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon). Если же в показанном выше окне в списке COM-надстроек у вас нет Power Pivot, то значит она не входит в вашу версию Microsoft Office :(
На появившейся вкладке Power Pivot будет большая салатового цвета кнопка Управление (Manage), нажатие на которую и откроет поверх Excel окно 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) уйдет в область фильтра, а сводная будет построена уже по офисам:
Само-собой, такую процедуру можно повторять многократно, последовательно углубляясь в ваши данные в интересующем вас направлении.
Преимущество 8. Преобразование сводной в функции кубов
Если выделить любую ячейку в сводной по Модели Данных и выбрать затем на вкладке Анализ сводной таблицы команду Средства OLAP - Преобразовать в формулы (Analyze - OLAP Tools - Convert to formulas), то вся сводная будет автоматически преобразована в формулы. Теперь значения полей в области строк-столбцов и результаты в области значений будут извлекаться из Модели Данных с помощью специальных функций кубов: КУБЗНАЧЕНИЕ и КУБЭЛЕМЕНТ:
Технически, это означает, что теперь мы имеем дело не со сводной, а с несколькими ячейками с формулами, т.е. спокойно можем делать с нашим отчетом любые преобразования недоступные в сводных, например, вставлять в середину отчета новые строки или столбцы, делать внутри сводной любые доп.вычисления, оформлять их любым желаемым образом и т.д.
При этом связь с исходными данными, само-собой, остается и в будущем эти формулы будут обновляться при изменении источников. Красота!
Ссылки по теме
- План-факт анализ в сводной таблице с Power Pivot и Power Query
- Сводная по таблице с многострочной шапкой
- Создание базы данных в Excel с помощью Power Pivot
В 2016 как отдельную надстройку его установить нельзя, нужно устанавливать версию продукта, который её содержит. Иначе никак.
В общем если будет конкретная задача, приходите на форум, поможем, чем сможем.
Но есть вопрос.
Кода преобразовал сводную в обычную таблицу функцией кубов, данные подгружаются из папок. В названия столбцов засунул даты предварительно ограничил фильтром в PQ "в предыдущие 30 дней".
Но итоговая таблица не обновила последний день, а вывела в столбцы #Н/Д. Как этого избежать?
У Вам получилось решить проблему? Если да, то прошу поделиться решением. Спасибо
У меня установлен Офис 365. При работе со Сводными, построенными на основании Модели Данных, не появляется иконка "Quick Explore". Я скачал Ваш пример из этого урока. То же самое - нет иконки. При двойном нажатии на данные я проваливаюсь дальше, но всех преимуществ, о которых Вы говорили, получить не выходит. Офис переустанавливал. Проблема осталась.
В чем может быть проблема?
Спасибо.