Power Pivot в Excel: как выгрузить данные из внешних источников и собрать их в одну базу

Power Pivot — встроенный инструмент Excel для обработки и анализа больших объёмов данных. С помощью неё в Excel загружают данные из нескольких источников разных форматов, моделируют их в одну базу и работают с ней дальше.

В Power Pivot нет ограничений по количеству строк. Excel позволяет работать только с 1 048 000 строк, а в Power Pivot их может быть гораздо больше. При этом производительность программы не уменьшается.

Поэтому Power Pivot точно пригодится в случаях, когда стандартный Excel не справляется с количеством данных и их форматами.

В статье разберёмся:

• для чего нужна и как работает надстройка Power Pivot;
• как загрузить данные из внешних источников в Power Pivot;
• как смоделировать данные в Power Pivot и создать базу данных;
• как узнать больше о работе в Excel.

Для чего нужна надстройка Power Pivot и как она работает

Как мы сказали выше, Power Pivot расширяет стандартные возможности Excel и позволяет обрабатывать большое количество данных из разных источников.

Power Pivot — бесплатная надстройка для Excel. Для Excel 2010 года её нужно загружать отдельно с сайта Microsoft. Для версий после 2013 года она встроена в стандартную функциональность программы. К сожалению, Power Pivot не предусмотрен для macOS-версии Excel.

Работа в Power Pivot проходит в таком порядке:

• Загружаем данные из разных источников — например, из базы данных Microsoft Access, «1C», из текстовых файлов или электронных таблиц, из интернета.
• Настраиваем связи между загруженными данными — создаём модель данных. Для этого не нужна функция ВПР или другие поисковые функции Excel — в Power Pivot свои инструменты для объединения данных.
• Проводим дополнительные вычисления — при необходимости.
• Power Pivot строит на основе моделей данных необходимые отчёты — в виде сводных таблиц или диаграмм.

В следующих разделах разберём на примере, как загружать данные из внешних источников в Power Pivot, как их моделировать. А также покажем, как на основании этих данных создать отчёт в форме сводной таблицы.

Загружаем данные из внешних источников в Power Pivot

Предположим, у нас есть четыре файла разных форматов:

• данные о продажах книжного издательства → в формате TXT;
• справочник регионов → в виде базы данных в Access;
• справочник товаров → в XLS;
• ценовая политика → на слайде Power Point.

Данные о продажах. Это таблица с четырьмя столбцами — город, ID товара, дата продажи и количество проданных единиц — и более чем полутора миллионами строк.

Справочник регионов. В файле одна таблица, в которой перечислены все города России, субъекты и округа.

Справочник товаров. В этой таблице перечислены названия книг, их формат и ID‑номер.

Ценовая политика. Это слайд, где указаны актуальные цены на книги разных форматов.

Наша задача — связать данные из этих источников в одну базу. Для начала нужно загрузить эти данные в Power Pivot.

Шаг 1

Открываем пустой файл Excel и активируем надстройку Power Pivot

Для этого потребуется разблокировать вкладку «Разработчик». Переходим во вкладку «Файл» и выбираем пункты «Параметры» → «Настройка ленты». В открывшемся окне в разделе «Основные вкладки» находим пункт «Разработчик», отмечаем его галочкой и нажимаем кнопку «ОК» → в основном меню Excel появляется новая вкладка «Разработчик».

Теперь на этой вкладке нажимаем кнопку «Настройки COM», в появившемся окне выбираем Microsoft Power Pivot for Excel и жмём «ОК».

Готово — на панели появилась отдельная вкладка Power Pivot.

Шаг 2

Открываем окно Power Pivot

Для этого переходим на вкладку Power Pivot и нажимаем на кнопку «Управление».

Готово — открылось окно Power Pivot. Оно относится к файлу Excel, через который мы его открыли.

В этом окне нам нужно собрать данные из наших четырёх источников и настроить связи между ними.

Шаг 3

Выгружаем данные из баз данных

На вкладке «Главная» нажимаем кнопку «Из базы данных» и выбираем «Из Access».

В появившемся окне в поле «Имя базы данных» прописываем адрес, где хранится файл Access со справочником регионов, — адрес можно найти через кнопку «Обзор».

Если база данных зашифрована, в этом же окне нужно ввести имя пользователя и пароль.

Нажимаем «Далее».

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

В нашем случае она одна — «Справочник регионов». Выбираем её и нажимаем «Готово».

Начинается импорт выбранной таблицы в Power Pivot. После этого появляется окно с результатом. Проверяем информацию и нажимаем «Закрыть».

Готово — первые данные загрузились в Power Pivot. В окне появился отдельный лист «Справочник регионов», на нём отражена та же таблица, что была и во внешнем источнике.

Шаг 4

Выгружаем данные из текстового файла

На вкладке «Главная» нажимаем кнопку «Из других источников». В появившемся окне выбираем «Текстовый файл» и нажимаем «Далее».

В появившемся окне в поле «Имя базы данных» прописываем адрес, где хранится текстовый файл с данными о продажах.

После этого в нижней части окна появляется предпросмотр таблицы из источника данных.

При необходимости корректируем настройки — например, меняем вид разделителя столбцов или отключаем ненужные графы таблицы. Затем нажимаем «Готово».

Начинается импорт выбранной таблицы из текстового файла в Power Pivot. После этого появляется окно с результатом. Проверяем информацию и нажимаем «Закрыть».

Готово — в окне Power Pivot появилась вторая вкладка «Продажи» — на ней более полутора миллионов строк. Напомним, в Excel без надстройки могло бы поместиться около миллиона.

Шаг 5

Выгружаем данные из файла Excel

По аналогии с предыдущим шагом на вкладке «Главная» нажимаем кнопку «Из других источников». Во всплывшем окне выбираем «Файл Excel» и нажимаем «Далее».

В появившемся окне в поле «Имя базы данных» прописываем адрес, по которому хранится файл Excel со справочником товаров. Нажимаем «Далее»,

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

В нашем случае она одна — «Справочник товаров». Выбираем её и нажимаем «Готово».

Снова происходит импорт и появляется окно с результатом.

В окне Power Pivot появилась третья вкладка «Справочник товаров». Также со всеми данными, которые хранились в первоисточнике — внешнем файле Excel.

Шаг 6

Выгружаем данные из файла Power Point

В этом случае выгрузить данные можно только методом «копировать — вставить».

Открываем файл Power Point, содержащий ценовую политику. Выделяем таблицу, нажимаем правую кнопку мыши и выбираем «Скопировать».

Затем возвращаемся в окно Power Pivot и на любой вкладке нажимаем кнопку «Вставить» на главной панели.

В появившемся окне вводим имя таблицы — «Цены» — и нажимаем «ОК».

Готово — в окне Power Pivot появилась четвёртая вкладка «Цены».

Важно понимать, что при таком варианте выгрузки данных — копировании и вставке — таблица не будет обновляться, если внести изменения в исходный файл Power Point.

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

Шаг 7

Обновляем данные из внешних источников в Power Pivot

Для этого на главной панели любой вкладки нужно нажать кнопку «Обновить», затем «Обновить все».

Появится окно, где будет видно, какие вкладки обновляются. В нашем случае это только первые три вкладки.

Обновление проходит быстро — примерно за 10–15 секунд обновляются три вкладки, в одной из которых более полутора миллионов строк.

Файл из Power Point придётся обновлять вручную.

Моделируем данные в Power Pivot

В предыдущем разделе мы загрузили данные из внешних источников в Power Pivot. Сейчас у нас есть четыре вкладки, в которых хранятся четыре таблицы: «Справочник регионов», «Продажи», «Справочник товаров», «Цены».

Задача этого этапа — связать данные всех таблиц так, чтобы можно было анализировать информацию одновременно по всем столбцам.

Шаг 1

Меняем режим представления данных в Power Pivot

Для этого на главной вкладке окна Power Pivot нажмём кнопку «Представление диаграммы».

В этом режиме отображения каждая таблица показана в виде прямоугольника, в котором перечислены её столбцы. Для удобства эти прямоугольники можно двигать и менять местами.

В этом же режиме отображения в Power Pivot настраивают связи между таблицами.

Шаг 2

Настраиваем связи между данными таблиц

Первая связь. В таблице «Продажи» есть столбец «Город», но нет столбцов «Субъекты» и «Округ». Все эти столбцы, включая столбец «Город», есть в таблице «Справочник регионов».

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

Готово — между таблицами «Справочник регионов» и «Продажи» появилась связь по столбцу «Город». Теперь у каждой строки продаж будет указан не только город, но также субъект и округ.

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

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

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

Третья связь. Теперь к уже объединённым данным нужно добавить цены. Они хранятся в четвёртой таблице. Настроим связь между ней и таблицей «Справочник товаров» по столбцу «Формат».

Благодаря этой связи в «Справочнике товаров» появится информация о ценах на книги.

Готово. Мы построили модель из четырёх таблиц из разных источников и связали данные этих таблиц по идентичным столбцам. Чтобы увидеть результат, нужно создать сводную таблицу.

Шаг 3

Создаём сводную таблицу.

На главной вкладке нажимаем кнопку «Сводная таблица».

Дальше выбираем, на каком листе нужно создать сводную таблицу — на новом или на существующем.

Готово — появился новый лист для сводной таблицы. Слева на листе расположена область, где появится отчёт сводной таблицы после настроек. Справа — панель «Поля сводной таблицы», в которой мы будем работать с этими настройками.

Панель «Поля сводной таблицы» состоит из двух частей. В верхней части находится список полей — в нашем случае Power Pivot перенёс в список полей данные четырёх таблиц, которые мы связали между собой. В нижней части — четыре области: «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своё назначение.

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

Для примера построим отчёт в форме сводной таблицы, который покажет количество продаж издательства в субъектах России с детализацией по формату книг.

Для этого в область «Строки» перенесём поля «Округ» и «Субъект» из «Справочника регионов», в область «Значения» — поле «Количество» из таблицы «Продажи», в область «Столбцы» — поле «Формат» из «Справочника товаров».

Готово. Мы получили таблицу, где по вертикали расположены все субъекты и округа страны, по горизонтали — количество проданных книг с разбивкой по форматам.

По такому же принципу можно строить другие отчёты — в зависимости от того, какую информацию для анализа нужно получить. Например, в отчёте можно показать количество продаж с детализацией по названиям книг и датам их продажи.