Сводная таблица на основе внутренней модели данных

Рубрика: 7. Полезняшки Excel

В Excel 2013 появился новый аналитический механизм: модель данных. Каждая рабочая книга располагает собственной внутренней моделью данных, упрощающей анализ разрозненных источников данных. [1] Идея, заложенная в основу модели данных, проста. Предположим, что в вашем распоряжении имеются две таблицы: Заказы (рис. 1) и Сотрудники (рис. 2). В таблице Заказы содержится информация о сделках (код сотрудника, дата и сумма счета-фактуры, период продаж). В таблице Сотрудники находится информация о сотрудниках: код, фамилия, имя и должность. Если нужно проанализировать суммы продаж в зависимости от должности сотрудника, следует объединить информацию, содержащуюся в двух таблицах. Чтобы ваши данные имели вид Таблицы – инструмента Excel(поэтому пишется с заглавной буквы) – кликните на любой ячейке таблицы данных и выполните команду Создать Таблицу: Ctrl+T.

Рис. 1. Информация о сделках

Рис. 1. Информация о сделках

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Информация о сотрудниках

Рис. 2. Информация о сотрудниках

Выполнение подобной задачи в прошлом потребовало бы использования множества формул ВПР, СУММЕСЛИ и других. После появления модели данных в Excel 2013 достаточно объявить обе таблицы связанными (по коду сотрудника) и включить их в модель данных. Модель данных Excel создает куб аналитики на основе связи между кодами сотрудников и передает нужные данные в сводную таблицу.

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

1. Щелкните в области таблицы Заказы и начните создавать новую сводную таблицу, выбрав команду ВставкаСводная таблица.

2. В диалоговом окне Создание сводной таблицы установите флажок Добавить данные в модель данных (рис. 3). Обратите внимание на то, что в окне Создание сводной таблицы находится ссылка на именованный диапазон (Таблицу данных); в нашем примере – Заказы. Рекомендуется присваивать говорящие имена Таблицам данных. Это облегчит распознавание таблиц, находящихся в модели данных. Если не присваивать названия Таблицам данных, модель данных отобразит их под названиями Таблица 1, Таблица2 и т.д. Чтобы назначить Таблице данных имя, кликните на Таблице, перейдите на контекстную вкладку Работа с даннымиКонструктор и в поле Имя таблицы введите говорящее имя. Повторите эту операцию для всех остальных Таблиц.

Рис. 3. Создайте сводную таблицу на основе таблицы данных Заказы

Рис. 3. Создайте сводную таблицу на основе таблицы данных Заказы

3. Щелкните в области таблицы данных Сотрудники и начните создавать новую сводную таблицу. Не забудьте установить флажок Добавить эти данные в модель данных.

4. После добавления таблиц в модель данных перейдите к списку полей сводной таблицы и выберите переключатель ВСЕ. В списке полей появятся два диапазона, соответствующие таблицам данных (рис. 4).

Рис. 4. В списке полей сводной таблицы выберите параметр ВСЕ

Рис. 4. В списке полей сводной таблицы выберите параметр ВСЕ, чтобы получить доступ ко всем таблицам, находящимся в модели данных

5. Создайте сводную таблицу обычным образом. В новой таблице поле Должность появится в области СТРОКИ, а поле Сумма по столбцу Объем продаж — в области ЗНАЧЕНИЯ. Программа тут же распознает, что используются две таблицы из модели данных, и предлагает создать связь между ними (рис. 5). Щелкните на кнопке Создать.

Рис. 5. Выберите создание связи между таблицами после отображения соответствующего запроса

Рис. 5. Выберите создание связи между таблицами после отображения соответствующего запроса

6. На экране появится диалоговое окно Создание связи (рис. 6). В этом окне можно выбрать таблицы и поля, для которых создается связь. На этом рисунке показана таблица Заказы с полем Код сотрудника. Эта таблица связана с таблицей Сотрудники с помощью поля Код сотрудника.

Рис. 6. Создайте подходящую связь, используя раскрывающиеся списки Таблица и Столбец

Рис. 6. Создайте подходящую связь, используя раскрывающиеся списки

7. После создания связи можно использовать данные из двух таблиц для отображения требуемых результатов. Применение модели данных проиллюстрировано на рис. 7.

Рис. 7. Отображение объема продаж по должностям сотрудников

Рис. 7. Отображение объема продаж по должностям сотрудников

Первичные ключи. Обратите внимание на рис. 6 на раскрывающийся список Связанный столбец (первичный ключ). Это означает, что модель данных использует поле, относящееся к связанной таблице, в качестве первичного ключа. Первичный ключ — это поле, содержащее лишь уникальные ненулевые значения (без дубликатов и пустых значений). Первичные ключи применяются в модели данных в целях предотвращения ошибок суммирования и дубликатов. В каждой связи должен быть хотя бы один первичный ключ. Поскольку поле Код сотрудника, находящееся в таблице Сотрудники, является первичным ключом, оно должно включать только уникальные значения (без пробелов и нулевых значений). Первичный ключ обеспечивает единственный способ сохранения целостности данных при объединении нескольких таблиц.

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

После включения таблиц во внутреннюю модель данных может потребоваться настройка связей, установленных между ними. Чтобы изменить связи модели данных, отобразите диалоговое окно Управление связями. Для этого выберите вкладку ленты Данные и в области Работа с данными щелкните на кнопке Отношения. Требуемое окно появится на экране (рис. 8).

Рис. 8. В диалоговом окне Управление связями можно изменить связи, заданные в модели данных

Рис. 8. В диалоговом окне Управление связями можно изменить связи, заданные в модели данных

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

  • Создать. Создание новой связи между таблицами модели данных.
  • Изменить. Изменение выделенной связи.
  • Активировать. Активизация выбранной связи, вследствие чего Excel будет рассматривать данную связь при агрегировании и анализе данных в модели.
  • Деактивировать. Отключение выбранной связи. Это приведет к тому, что Excel будет игнорировать связь при агрегировании и анализе данных в модели.
  • Удалить. Удаление выбранной связи.

Добавление новой таблицы в модель данных. Чтобы добавить новую таблицу в модель данных, воспользуйтесь одним из следующих двух способов.

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

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

1. Если вы еще не создали Таблицу, поместите курсор в таблицу данных (лист ExcelМестоположения, любую ячейку в диапазоне А1:С55), перейдите на вкладку Вставка щелкните на кнопке Таблица (рис. 9). Можно, встав в ячейку таблицы данных, нажать Ctrl+T. На экране появится диалоговое окно (рис. 10), в котором задается диапазон данных. Нажмите Ok. Программа преобразует этот диапазон в Таблицу, которая может распознаваться внутренней моделью данных.

Рис. 9. Создание Таблицы на основе исходных данных

Рис. 9. Создание Таблицы на основе исходных данных

Рис. 10. Преобразование диапазона в таблицу

Рис. 10. Преобразование диапазона в таблицу

2. На контекстной вкладке Работа с таблицамиКонструктор, измените значение поля Имя таблицы, выбрав легко запоминаемое имя; в нашем примере – Отделения.

3. Перейдите на вкладку ленты Данные и в области Подключения щелкните на кнопке Подключения. На экране появится диалоговое окно Подключения к книге (рис. 11). Щелкните на стрелке раскрывающегося списка, находящейся справа от кнопки Добавить, и выберите пункт Добавить в модель данных.

Рис. 11. Откройте диалоговое окно Подключения к книге и выберите пункт меню Добавить в модель данных

Рис. 11. Откройте диалоговое окно Подключения к книге и выберите пункт меню Добавить в модель данных

4. На экране появится диалоговое окно Существующие подключения (рис. 12). Перейдите на вкладку Таблицы и выделите только что созданную Таблицу. Щелкните на кнопке Открыть, чтобы добавить выбранную таблицу в модель данных.

Рис. 12. Окно Существующие подключения

Рис. 12. В окне Существующие подключения перейдите на вкладку Таблицы, выделите только что созданную Таблицу и щелкните Открыть

5. Все сводные таблицы, созданные на основе модели данных, обновляются с учетом добавления новой таблицы. Не забудьте открыть диалоговое окно Управление связями и создать требуемую связь.

Удаление таблицы из модели данных. Иногда возникает необходимость в удалении Таблицы или источника из модели данных. Чтобы выполнить эту задачу, выберите вкладку ленты Данные и щелкните на кнопке Подключения. На экране появится диалоговое окно Подключения к книге (рис. 13). Выберите таблицу, а затем кликните на кнопке Удалить.

Рис. 13. С помощью окна Подключения к книге можно удалить любую таблицу из внутренней модели данных

Рис. 13. С помощью окна Подключения к книге можно удалить любую таблицу из внутренней модели данных

Создание новой сводной таблицы с помощью модели данных. Иногда приходится создавать сводные таблицы «с нуля», используя в качестве источника данных существующую внутреннюю модель данных. Для этого выполните следующие действия.

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

Рис. 14. Окно Создание сводной таблицы

Рис. 14. Окно Создание сводной таблицы

2. На экране появится диалоговое окно Существующие подключения (рис. 15). На вкладке Таблицы выберите параметр Таблицы в модели данных книги и щелкните на кнопке Открыть.

Рис. 15. Окно Существующие подключения

Рис. 15. Окно Существующие подключения

3. Вы вернитесь обратно в диалоговое окно Создание сводной таблицы. Щелкните ОК.

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

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

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

Ограничения внутренней модели данных

Как и все остальное в Excel, внутренняя модель данных имеет определенные ограничения:

Рис. 17. Ограничения внутренней модели данных

[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 7.

Комментарии: 2 комментария

Сергей, добрый день.
Важно отметить, что при таком способе создания сводной таблицы появляется в «Параметры полей значений…» возможность подсчета количества уникальных значений в случае выбора «Число различных элементов».

Спасибо, отличное дополнение! Речь идет о том, что помимо традиционных операций, доступных в полях значений — сумма, количество, минимум и др. (подробнее см. Вычисления в сводной таблице (в области значений) в Excel 2013) — становится доступна еще одна операция: подсчет числа различных элементов.


Прокомментировать