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