Объединение нескольких таблиц с помощью модели данных в Excel 2013
Специалисты из компании Microsoft внедрили лучшие инструменты PowerPivot в Excel 2013. Также доступна версия Office Pro Plus с полнофункциональными модулями PowerPivot, Power View и Inquire. В стандартных версиях Excel 2013 базовые возможности PowerPivot реализованы с помощью модели данных. На рис. 1 представлено диалоговое окно Создание сводной таблицы. Наличие в этом окне флажка Добавить эти данные в модель данных означает, что вы имеете дело с PowerPivot. Подробнее об объединении нескольких таблиц в одной сводной с помощью модели данных см. Сводная таблица на основе внутренней модели данных. Эта функция работает как в том случае, когда у вас установлен модуль PowerPivot, так и в случае использования встроенных возможностях Excel.
Использование экспресс-просмотра.После выбора произвольной ячейки сводной таблицы, созданной на основе модели данных, появится значок экспресс-просмотра (рис. 3). Этот значок могут видеть только пользователи Excel 2013, и он отображается только в том случае, если сводная таблица создана на основе модели данных. Экспресс-просмотр предназначен для оказания помощи пользователю, который собирается изменить сводную таблицу. После щелчка на этом значке появится окно, в котором отображаются подсказки по изменению сводной таблицы.
Рис. 3. Значок экспресс-просмотра и окно просмотра
В данном случае подсказки, отображаемые в окне экспресс-просмотра, не слишком полезны. На рис. 4 показан результат детализации дохода по регионам.
Рис. 4. Получившаяся сводная таблица не слишком полезна для менеджеров, но во многих случаях экспресс-просмотр поможет сформировать более информативную сводную таблицу
Создание новой сводной таблицы на основе существующей модели данных.Эта операция немного сложнее, чем при работе с обычными сводными таблицами:
1. Выберите команду Вставка g Сводная таблица.
2. В диалоговом окне Создание сводной таблицы установите переключатель Использовать внешний источник данных (рис. 5а). Даже если данные хранятся в таблице PowerPivot, находящейся в рабочей книге, не забывайте о том, что PowerPivot изначально была внешней надстройкой.
3. Щелкните на кнопке Выбрать подключение. На экране появится диалоговое окно Существующие подключения.
4. Выберите вкладку Таблицы.
5. Выберите пункт Таблицы в модели данных книги и щелкните Открыть (рис. 5б).
Рис. 5. Создание новой сводной на основе модели данных
Подсчет количества различных элементов.Одна из таких возможностей сводных таблиц — подсчет количества уникальных записей. В сводных таблицах Excel могут подсчитываться текстовые значения. Типичный пример подобной сводной таблицы показан на рис. 6. В этой таблице поле Сектор находится в области СТРОКИ, а поля Заказчик и Доход — в области ЗНАЧЕНИЯ. Итоговое количество заказчиков, отображаемое этой сводной таблицей, равно 563, что не соответствует действительности. На самом деле в данном случае речь идет о 563 непустых записях, соответствующим заказчикам, которые могут повторяться. Подобная накладка связана с ограничениями обычных сводных таблиц.
Рис. 6. Величина 563 соответствует количеству строк, а не числу уникальных заказчиков
Если сводная таблица основана на модели данных, выполните следующие действия.
1. Кликните правой кнопкой мыши на любой из ячеек в области Число элементов в столбце Заказчик (диапазон В1:В9). Выберите пункт Параметры полей значений.
2. В окне Параметры поля значений на вкладке Операция выберите Число различных элементов (обратите внимание, что по сравнению с обычными сводными таблицами отсутствуют функции Произведение и Индекс). Щелкните ОК.
3. Сводная таблица отображает список, состоящий из 27 уникальных заказчиков, 11 из которых относятся к сектору Оборудование (рис. 7).
Рис. 7. Подсчет количества уникальных заказчиков