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