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

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

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

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

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

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

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

Модель управляется и рассчитывается с помощью специальной надстройки, встроенной в Microsoft Excel – PowerPivotо чем я уже писал. Чтобы включить его, на вкладке разработчик нажмите на COM-надстройки (Разработчик — надстройки COM) и установите соответствующий флажок:

Если вкладки разработчик (Разработчик)на ленте его не видно, можно включить через Файл – Параметры – Настройка ленты. (Файл — Параметры — Настроить ленту). Если в показанном выше окне в списке надстроек COM у вас нет Power Pivot, значит, он не включен в вашу версию Microsoft Office 🙁

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

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

Загрузите таблицы в модель данных

Чтобы загрузить данные в Модель, сначала превратим таблицу в динамическое «умное» сочетание клавиш. Ctrl+T и дайте ему понятное имя на вкладке Конструктор (Дизайн). Это обязательный шаг.

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

• Нажимать кнопку Добавить в модель (Добавить в модель данных) таб PowerPivot таб Главная (Главная).
  
• Выбор команд Вставка — сводная таблица (Вставка — сводная таблица) и поставьте галочку Добавьте эти данные в модель данных (Добавьте эти данные в модель данных). В этом случае по загруженным в Модель данным также сразу строится сводная таблица.
  
• На вкладке Дополнительно Данные (Дата) нажмите на кнопку Из таблицы/диапазона (Из таблицы/диапазона)чтобы загрузить нашу таблицу в редактор Power Query. Этот путь самый длинный, но при желании здесь можно выполнить дополнительную очистку данных, редактирование и всевозможные преобразования, в которых Power Query очень силен.

  Затем гребённые данные загружаются в Модель командой На главную — Закрыть и загрузить — Закрыть и загрузить… (Главная страница — Закрыть и загрузить — Закрыть и загрузить в…). В открывшемся окне выберите опцию Просто создайте соединение (Только создать соединение) и самое главное поставить галочку Добавьте эти данные в модель данных (Добавьте эти данные в модель данных).
  

Строим сводную информацию о модели данных

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

• нажмите кнопку Таблица результатов (Сводная таблица) в окне Power Pivot.
• Выбор команд в Excel Вставка — сводная таблица и переключитесь в режим Используйте модель данных из этой книги (Вставка — сводная таблица — использовать модель данных этой книги).
• Выбор команд Вставка — сводная таблица (Вставка — сводная таблица) и поставьте галочку Добавьте эти данные в модель данных (Добавьте эти данные в модель данных). Текущая «умная» таблица будет загружена в Модель и будет построена сводная таблица для всей Модели.

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

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

Обычную сводку можно построить только с использованием данных из одной исходной таблицы. Если у вас их несколько, например, продажи, прайс-лист, справочник клиентов, реестр договоров и т. д., то вам сначала придется собрать данные из всех таблиц в одну с помощью таких функций, как ВПР. (ВПР), ИНДЕКС (ПОКАЗАТЕЛЬ), БОЛЬШЕ ОТКРЫТОГО (СООТВЕТСТВОВАТЬ), СУММЕСЛИМН (СУМИФС) и тому подобное. Это долго, утомительно и загоняет ваш Excel в «думку» с большим объёмом данных.

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

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

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

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

Щелкните правой кнопкой мыши по полю — команда Параметры поля значения и на вкладке Эксплуатация Выберите Количество различных элементов (Разное количество):

Преимущество 3: пользовательские формулы DAX

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

Чтобы создать меру, выберите на вкладке PowerPivot Command Меры – Создать меру (Меры — Новая мера) или просто щелкните правой кнопкой мыши таблицу в списке сводных полей и выберите Добавить меру (Добавить меру) в контекстном меню:

В открывшемся окне установите:

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

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

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

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

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

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

Преимущество 5: индивидуальные трафареты

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

Для этого на вкладке Анализ сводной таблицы в раскрывающемся списке Поля, элементы и наборы есть соответствующие команды (Анализ — Филдс, яtems & Sets — создание набора на основе элементов строки/столбца):

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

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

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

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

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

Преимущество 7. Расширенная детализация

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

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

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

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

Преимущество 8: Преобразование Pivot в функции куба

Если вы выберете любую ячейку в сводке для модели данных, а затем выберите на вкладке Анализ сводной таблицы Command Инструменты OLAP — преобразование в формулы (Анализ — Инструменты OLAP — Преобразование в формулы), то вся сводка будет автоматически преобразована в формулы. Теперь значения полей в области строк-столбцов и результаты в области значений будут извлекаться из Модели Данных с помощью специальных функций куба: CUBEVALUE и CUBEMEMBER:

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

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

• Анализ плана-факта в сводной таблице с помощью Power Pivot и Power Query.
• Сводная таблица с многострочным заголовком
• Создайте базу данных в Excel с помощью Power Pivot.