Power Pivot: пошаговая инструкция по включению в Excel

Power Pivot — это расширение для Microsoft Excel. Модуль содержит инструменты для систематизации и анализа данных. Расширение входит в базовую лицензию Excel, но его нужно включить в настройках. Рассказываем, как активировать Power Pivot и создавать модели данных и диаграммы.

Что такое Power Pivot

Power Pivot («пауэр пивот») — полезный инструмент для работы с данными. Модуль расширяет базовые функции Excel и ускоряет обработку информации. Аналитический инструмент помогает создавать модели данных, обрабатывать их и строить отчеты на основе разнородных исходников. В нем можно настроить связи между разобщенными массивами, чтобы работать с единой базой.

Простыми словами, Power Pivot — это расширение, в котором можно загружать данные из разных источников, систематизировать их и строить сводные диаграммы.

Какими источниками можно пользоваться:

• текстовые файлы TXT, JSON;
• данные из модуля Power Query;
• книги XLS, CSV;
• презентации PDF;
• базы Microsoft Access;
• файлы HTML и XML;
• базы «1С».

Загруженная информация сохраняется в Excel как база данных. Содержание можно обновлять с помощью встроенной функции. Если адреса исходных файлов не изменились, данные обновятся автоматически. Изменения будут перенесены в аналитическую базу.

У надстройки простой интерфейс, понятный для начинающих. Пользователям не нужно учить язык для работы с запросами и массивами. Каждый человек быстро может загрузить исходники в книгу Excel, установить связи между ними и создать сводный отчет.

Источник: ru.freepik.com

Почему он важен для анализа данных

Надстройка Power Pivot используется, если стандартных функций Excel не хватает для обработки данных. Она помогает быстрее анализировать большие объемы информации.

Преимущества модуля:

• Неограниченное количество строк. В базовой версии Excel длина таблицы не превышает 1 048 000 строк.
• Сжатие данных. Например, текстовый файл 50 Мб превращается в книгу Excel на 3 Мб.
• Большой объем памяти. Модуль работает с файлами до 2 Гб и сохраняет в оперативной памяти до 4 Гб информации.
• Быстрая работа. Модуль за несколько минут обрабатывает файлы с 10−15 миллионами записей.
• Быстрые обновления. Расширение обновляет файлы с 1,5 миллионами строчек за 10−15 секунд.

Как включить Power Pivot в Excel

Модуль Power Pivot уже установлен в Excel версии 2013 и выше. Если вы пользуетесь Excel 2010, скачайте расширение с сайта Microsoft и установите его вручную.

Шаг 1. Активируйте модуль

Чтобы подключить Power Pivot, разблокируйте вкладку «Разработчик». Пошаговая инструкция, как это сделать:

1. Откройте вкладку «Файл» в меню.
2. Выбирайте пункт «Параметры».
3. Выбирайте подпункт «Настройка ленты».
4. В новом окне перейдите в раздел «Основные вкладки».
5. Поставьте галочку напротив пункта «Разработчик» в списке.
6. Подтвердите действие кнопкой «ОК».
7. Вернитесь в основное меню.
8. Найдите новую вкладку «Разработчик».
9. Выбирайте функцию «Настройки COM».
10. В новом окне выбирайте Microsoft Power Pivot.
11. Нажмите «ОК», чтобы подключить расширение.

Если все сделать правильно, на верхней панели Excel появятся инструменты Power Pivot. Панель с настройками разработчика останется доступной.

Шаг 2. Откройте окно Power Pivot

Чтобы запустить надстройку, откройте вкладку Power Pivot. Для начала работы нажмите на кнопку «Управление». Откроется новое окно, где можно загружать информацию и устанавливать связи. Новая база будет привязана к книге Excel, через которую вы откроете окно.

Как создать модель данных

Моделью данных в Power Pivot называется группа связанных таблиц. Для создания новой модели загрузите исходные файлы в Excel. Для загрузки перейдите в меню «Главная» и укажите источник. Пользователям доступно 15 видов источников для импорта данных.

Можно загружать данные напрямую из Excel:

1. Откройте исходную таблицу.
2. Выделите любую ячейку.
3. Нажмите кнопку «Добавить в модель».
4. Откроется окно с заголовком «Создание таблицы».
5. Проверьте, что диапазон значений выделен полностью, и в пункте «Таблица имеет верхние колонтитулы» стоит галочка.
6. Нажмите кнопку «ОК».

В Power Pivot появится новое окно, где будет выбранная таблица. Так можно загрузить несколько таблиц, каждая из них будет на отдельной вкладке. Чтобы изменить названия вкладки, нажмите на нее правой кнопкой и выберите функцию «Переименовать».

Модель данных собирается из нескольких таблиц, в которых часть значений повторяется, а часть уникальная. Для примера представим базу интернет-магазина:

• Первая таблица выгружена из CRM системы, в ней содержатся имена, фамилии, телефоны клиентов и статистика покупок.
• Вторая таблица выгружена с сайта, в ней есть телефоны, email и логины клиентов.
• Третью таблицу использовали для доставки заказов. Она содержит почтовые адреса, ФИО клиентов и телефоны для связи

В Power Pivot можно объединить исходные базы в модель данных. Установить параллели между ними и создать единую базу поможет поле «телефон», которое есть во всех трех таблицах. Для настройки отношений откройте меню «Главная» и раздел «Представление диаграммы».

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

Между квадратами появятся связи. Они будут выглядеть как линии с «1» на одном конце и «*" на другом. Такие отношения называются «1 ко многим». В таблице «1» все значения уникальные, а в таблице «*" информация может повторяться.

Если дважды кликнуть по линии, откроется меню с настройками. В нем можно менять или удалять связи. Аналогичные функции есть в меню «Конструктор» в разделе «Управление связями». Чтобы вернуться к исходникам, откройте подраздел «Представление данных» на вкладке «Главная».

Как анализировать данные в Power Pivot

В расширении можно создавать сводные таблицы, графики и диаграммы. Для работы с моделями данных можно подключить Power View. Это встроенный модуль Excel, где можно визуализировать интерактивные диаграммы, графики и карты. Он подключается по тому же алгоритму, что и Power Pivot.

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

Как создать сводный документ:

1. Выберите пункт «Сводная таблица» в меню.
2. В появившемся окне выберите функцию «Новый лист».
3. Нажмите кнопку «ОК» и переходите к следующему окну.
4. На вкладке «Поля сводной таблицы» укажите, какие значения отражать в диаграмме.
5. На вкладке «Фильтры» укажите поля, по которым можно проводить сортировку значений.
6. На вкладке «Значения» укажите методы расчета параметров. Например, если перечислены заказы каждого клиента, можно рассчитать средний чек и общую стоимость покупок.

Как создать сводную диаграмму:

1. Откройте раздел меню «Анализ инструментов сводной таблицы».
2. Выберите функцию «Сводная диаграмма».
3. В новом окне нажмите кнопку «Вставить диаграмму».
4. Выберите, как будет выглядеть диаграмма или график.
5. Выберите функцию «Список полей».
6. Перетащите поля для диаграммы на панель.
7. Нажмите «ОК».

Готовую диаграмму можно редактировать, добавляя новые поля и фильтры.

Источник: ru.freepik.com

Типичные ошибки и как их исправить

• Ошибка обработки данных возникает, если связи настроены неправильно. Например, при настройке диаграммы задано деление на ноль или на текстовое поле. Чтобы ее исправить, проверьте зависимости между значениями.
• Ошибка «Класс не зарегистрирован» появляется, если источник для загрузки данных не поддерживается в активной версии Power Pivot. Чтобы исправить ошибку, нужно перевести исходник в другой формат.
• Ошибка «Не удалось выполнить инициализацию источника данных» возникает при попытке открыть файл, созданный в более поздней версии. Например, когда пользователь пробует открыть модель из Excel 2013 через Excel 2010. Ошибка не будет появляться, если установить совместимую версию программы.
• Неактивное меню. Элементы управления окна Power Pivot могут стать недоступными. Это происходит, когда формат сохранения файлов по умолчанию отличается от совместимых форматов. Чтобы меню стало активным, нужно выбрать формат сохранения «Книга Excel» (XLSX, XLSM, XLS, XLTX, XLTM).

Главное, что нужно знать

• Power Pivot — это надстройка для Microsoft Excel. Расширение может быстро обрабатывать большое число строчек. Модуль применяется для систематизации и аналитики данных, он упрощает работу с информацией.
• Модуль входит в лицензию Excel 2013 и выше, но сразу после установки программы он не активен. Чтобы начать работать с расширением, нужно подключить его настройках. Для Excel 2010 требуется скачивание и установка модуля.
• В Power Pivot можно создавать модели данных, создавая зависимости между строчками из разных источников. На основе моделей можно строить сводные диаграммы и таблицы для аналитики.