Управление
#Руководства

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

Инструкция со скриншотами. Разбираемся, для чего нужна и как работает надстройка Power Pivot для Excel.

Иллюстрация: Meery Mary для Skillbox Media

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

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

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

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

При подготовке статьи мы использовали материалы курса Skillbox «Excel + Google Таблицы с нуля до PRO».

Для чего нужна надстройка 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.
Файлы с источниками данных
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Так выглядят данные о продажах
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Так выглядит справочник регионов
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Так выглядит справочник товаров
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Так выглядят данные о ценовой политике издательства
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Шаг 1


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

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

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

Активируем настройку Microsoft Power Pivot for Excel
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Шаг 2


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

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

Переходим в отдельное окно Power Pivot
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Так выглядит окно Power Pivot
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Шаг 3


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

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

Выгружаем данные из базы данных
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

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

Вводим адрес, где хранится нужная база данных
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

Выбираем таблицу, которую нужно выгрузить из базы данных
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Результат импорта таблицы из базы данных в Power Pivot
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Таблица из базы данных Access появилась в Power Pivot
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

Шаг 4


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

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

Выгружаем данные из текстового файла
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

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

Вводим адрес, где хранится текстовый файл, и выставляем нужные настройки
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Результат импорта таблицы из текстового файла в Power Pivot
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Таблица из текстового файла появилась в Power Pivot
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

Шаг 5


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

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

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

Вводим адрес, по которому хранится нужный файл Excel
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

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

Результат импорта таблицы из файла Excel в Power Pivot
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Таблица из файла Excel появилась в Power Pivot
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

Шаг 6


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

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

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

Копируем таблицу из файла Power Point
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Нажимаем кнопку «Вставить», чтобы получить данные из Power Point
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Даём имя таблице из Power Point
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Таблица из файла Power Point появилась в Power Pivot
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

Шаг 7


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

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

Нажимаем сюда, чтобы обновить данные из внешних источников
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

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

Power Pivot обновляет данные из внешних источников
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

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

Шаг 1


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

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

Меняем режим представления данных в Power Pivot
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

Шаг 2


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

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

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

Чтобы создать связь между таблицами, нужно столбец из одной таблицы притянуть к столбцу из другой
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Между таблицами появилась связь по одному столбцу
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

Строим связь по столбцам с ID-номерами товаров
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

Названия столбцов, по которым строят связи между таблицами, не обязательно должны быть одинаковыми. Главное, чтобы в таких столбцах хранилась аналогичная информация. В нашем случае это ID-номера книг.

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

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

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

Строим связь по столбцам с форматами книг
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Шаг 3


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

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

Нажимаем сюда, чтобы создать сводную таблицу
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Выбираем, на каком листе создать сводную таблицу
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

Появился новый лист для отчёта сводной таблицы
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

Подробнее о назначении областей, а также о том, как строить и настраивать сводные таблицы в стандартной версии Excel, говорили в этой статье Skillbox Media.

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

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

Так выглядит полученный отчёт сводной таблицы
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»

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

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

Как узнать больше о работе в Excel

  • В этой статье Skillbox Media собрали в одном месте 15 статей и видео об инструментах Excel, которые ускорят и упростят работу с электронными таблицами.
  • В Skillbox есть курс «Excel + Google Таблицы с нуля до PRO». Он подойдёт как новичкам, которые хотят научиться работать в Excel с нуля, так и уверенным пользователям, которые хотят улучшить свои навыки. На курсе учат быстро делать сложные расчёты, визуализировать данные, строить прогнозы, работать с внешними источниками данных, создавать макросы и скрипты.
  • Кроме того, Skillbox даёт бесплатный доступ к записи онлайн-интенсива «Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня». Он подходит для начинающих пользователей. На нём можно научиться создавать и оформлять листы, вводить данные, использовать формулы и функции для базовых вычислений, настраивать пользовательские форматы и создавать формулы с абсолютными и относительными ссылками.

Другие материалы Skillbox Media по Excel

Идите в бухгалтерию!
Вы с нуля научитесь вести бухучёт по российским стандартам и работать в «1С», готовить налоговую отчётность и рассчитывать зарплату. Трудоустройство гарантировано договором.
Узнать больше
Понравилась статья?
Да

Пользуясь нашим сайтом, вы соглашаетесь с тем, что мы используем cookies 🍪

Ссылка скопирована