У вас есть данные и много их. Если вы хотите проанализировать все эти данные, узнайте, как использовать надстройку Power Pivot с Excel для импорта наборов данных, определения связей, создания сводных таблиц и создания сводных диаграмм.
Инструкции в этой статье применяются к Excel 2019, 2016, 2013 и Excel для Office 365.
Как получить надстройку Excel Power Pivot
Power Pivot дает вам возможность бизнес-аналитики и аналитических приложений. Вам не нужно специализированное обучение для разработки моделей данных и выполнения расчетов . Вам просто нужно включить его, прежде чем вы сможете его использовать.
-
Откройте Excel .
-
Выберите « Файл» > « Параметры» .
-
Выберите Надстройки .
-
Выберите раскрывающееся меню « Управление» , затем выберите « Надстройки COM» .
-
Выберите Go .
-
Выберите Microsoft Power Pivot для Excel .
-
Выберите ОК . Вкладка Power Pivot добавлена в Excel.
Следуйте вместе с учебником
Если вы хотите быстро приступить к работе с Power Pivot, учитесь на примере. У Microsoft есть несколько примеров наборов данных, доступных для бесплатной загрузки, которые содержат необработанные данные, модель данных и примеры анализа данных. Это отличные инструменты обучения, которые позволяют понять, как профессионалы анализируют большие данные.
В этом руководстве используется образец рабочей книги Microsoft Student Data Model . В первой заметке на странице вы найдете ссылку для загрузки учебного пособия и заполненной модели данных.
Данные в этом образце книги Excel имеют следующее:
- Рабочая тетрадь содержит четыре рабочих листа.
- Каждый лист содержит связанные данные, то есть, по крайней мере, один заголовок столбца на листе соответствует заголовку столбца в другом листе.
- Данные в каждом рабочем листе форматируются в виде таблицы .
- Каждая ячейка в таблице содержит данные. В таблицах нет пустых ячеек, строк или столбцов.
Есть другие примеры наборов данных на веб-сайте Microsoft. Изучите эти учебные ресурсы:
- Загрузите данные из базы данных Microsoft Access, которая описывает Олимпийские медали .
- Загрузите три образца Business Intelligence, которые показывают, как использовать Power Pivot для импорта данных, создания отношений, создания сводных таблиц и проектирования сводных диаграмм.
Перед использованием любого набора данных очистите его. Используйте функцию CLEAN в Excel, чтобы избавиться от непечатаемых символов, выполнить проверку орфографии, удалить дублирующиеся строки данных, преобразовать числа и даты в нужный формат и изменить порядок данных.
Как добавить данные в файл Excel и построить модель данных
Вы собрали данные, которые вам нужны. Теперь пришло время импортировать ваши наборы данных в Excel и автоматически создать модель данных. Модель данных похожа на реляционную базу данных и предоставляет табличные данные, используемые в сводных таблицах и сводных диаграммах.
Если вам нужны данные для школьного задания, рабочего проекта или для выполнения этого урока, вы найдете отличные общедоступные наборы данных на GitHub .
Чтобы импортировать данные Excel в модель данных Power Pivot:
-
Откройте пустой лист и сохраните файл с уникальным именем.
-
Выберите « Данные» , затем выберите « Получить данные» > « Из файла» > « Из рабочей книги», чтобы открыть диалоговое окно « Импорт данных ».
В Excel 2013 выберите Power Query > Получить внешние данные и выберите источник данных.
-
Перейдите к папке, содержащей файл Excel, выберите файл, затем выберите « Импорт», чтобы открыть навигатор.
-
Установите флажок для Выбрать несколько элементов .
-
Выберите таблицы, которые вы хотите импортировать.
При импорте двух или более таблиц Excel автоматически создает модель данных.
-
Выберите Загрузить, чтобы импортировать таблицы данных в модель данных.
-
Чтобы убедиться, что импорт прошел успешно и модель данных была создана, перейдите в раздел «Данные» и в группе « Инструменты данных » выберите « Перейти к окну Power Pivot» .
-
Окно Power Pivot отображает ваши данные в формате рабочего листа и состоит из трех основных областей: таблица данных, область расчета и вкладки таблицы данных.
-
Вкладки в нижней части окна Power Pivot соответствуют каждой из импортированных таблиц.
-
Закройте окно Power Pivot.
Если вы хотите добавить новые данные в модель данных, в окне Excel перейдите в Power Pivot и выберите Добавить в модель данных . Данные отображаются в виде новой вкладки в окне Power Pivot.
Создание связей между таблицами с помощью Power Pivot Excel
Теперь, когда у вас есть модель данных, пришло время создать отношения между каждой из таблиц данных.
-
Выберите Power Pivot , затем выберите Manage, чтобы открыть окно Power Pivot.
-
Выберите Home , затем выберите Diagram View .
-
Импортированные таблицы отображаются в виде отдельных блоков в представлении схемы. Перетащите, чтобы переместить таблицы в другое место. Перетащите угол поля, чтобы изменить его размер.
-
Перетащите заголовок столбца из одной таблицы в другую или в таблицы, содержащие одинаковый заголовок столбца.
-
Продолжайте сопоставлять заголовки столбцов.
-
Выберите Дом , затем выберите Просмотр данных .
Как создавать сводные таблицы
Когда вы используете Power Pivot для создания модели данных, большая часть тяжелой работы, включающей сводные таблицы и сводные диаграммы, была выполнена для вас. Отношения, которые вы создали между таблицами в вашем наборе данных, используются для добавления полей, которые вы будете использовать для создания сводных таблиц и сводных диаграмм.
-
В окне Power Pivot выберите « Домой» , затем выберите « Сводная таблица» .
-
В диалоговом окне « Создать сводную таблицу» выберите « Новая рабочая таблица» , затем нажмите « ОК» .
-
На панели Поля сводной таблицы выберите поля, которые нужно добавить в сводную таблицу. В этом примере создается сводная таблица, которая содержит имя учащегося и его среднюю оценку.
-
Чтобы отсортировать данные сводной таблицы, перетащите поле в область «Фильтры». В этом примере поле «Имя класса» добавляется в область «Фильтры», поэтому список можно отфильтровать, чтобы показать среднюю оценку ученика для класса.
Чтобы изменить метод расчета, используемый полем в области «Значения», выберите раскрывающийся список рядом с именем поля и выберите « Настройки поля значения» . В этом примере сумма оценки была изменена на среднее значение оценки.
-
Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и сортируйте данные, используя стрелки раскрывающегося списка заголовков столбцов.
Преобразовать сводную таблицу в сводную диаграмму
Если вы хотите визуализировать данные сводной таблицы, превратите сводную таблицу в сводную диаграмму.
- Выберите сводную таблицу, затем перейдите в раздел Анализ инструментов сводной таблицы .
- Выберите Сводная диаграмма, чтобы открыть диалоговое окно Вставить диаграмму .
- Выберите диаграмму, затем нажмите ОК .
Создать сводные диаграммы
Если вы предпочитаете анализировать данные в визуальном формате, создайте сводную диаграмму.
-
В окне Power Pivot выберите « Домой» , затем выберите стрелку раскрывающегося списка «Сводная таблица» . Появится список параметров.
-
Выберите сводную диаграмму .
-
Выберите « Новый лист» и нажмите « ОК» . Заполнитель PivotChart появится на новом листе.
-
Перейдите в Инструменты анализа сводной диаграммы и выберите Список полей, чтобы отобразить панель Поля сводной диаграммы .
-
Перетащите поля для добавления в сводную диаграмму. В этом примере создается сводная диаграмма, показывающая среднюю оценку для классов, отфильтрованных по семестрам.
-
Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и отсортируйте данные с помощью стрелок раскрывающегося списка заголовков столбцов.
