Power Pivot в Excel: как выгрузить данные из внешних источников и собрать их в одну базу
Инструкция со скриншотами. Разбираемся, для чего нужна и как работает надстройка Power Pivot для Excel.
![](https://248006.selcdn.ru/main/iblock/12e/12e057bc002fbb7901c0a749d1459b6e/42bce666dccec52a0334b70916a11b19.png)
![](https://248006.selcdn.ru/main/iblock/12e/12e057bc002fbb7901c0a749d1459b6e/42bce666dccec52a0334b70916a11b19.png)
Иллюстрация: Meery Mary для Skillbox Media
Power Pivot — встроенный инструмент Excel для обработки и анализа больших объёмов данных. С помощью неё в Excel загружают данные из нескольких источников разных форматов, моделируют их в одну базу и работают с ней дальше.
В Power Pivot нет ограничений по количеству строк. Excel позволяет работать только с 1 048 000 строк, а в Power Pivot их может быть гораздо больше. При этом производительность программы не уменьшается.
Поэтому Power Pivot точно пригодится в случаях, когда стандартный Excel не справляется с количеством данных и их форматами.
В статье разберёмся:
- для чего нужна и как работает надстройка Power Pivot;
- как загрузить данные из внешних источников в 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.
![](/upload/setka_images/14422420122022_083f15b158a17e94da4c6feb839db74a42e2bc7e.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Данные о продажах. Это таблица с четырьмя столбцами — город, ID товара, дата продажи и количество проданных единиц — и более чем полутора миллионами строк.
![](/upload/setka_images/14441620122022_b8ecb6cdb908a0831723e4903e158e0c999f10e6.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Справочник регионов. В файле одна таблица, в которой перечислены все города России, субъекты и округа.
![](/upload/setka_images/14441820122022_1064345c817c93b5e82dde24de7fb282e60d46ef.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Справочник товаров. В этой таблице перечислены названия книг, их формат и ID‑номер.
![](/upload/setka_images/14442220122022_013f0a20ea3a10c81c42880e3021c0deda6a3756.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Ценовая политика. Это слайд, где указаны актуальные цены на книги разных форматов.
![](/upload/setka_images/14442120122022_4ac974507cf921607ada2eeff1670b7aac135bd2.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Наша задача — связать данные из этих источников в одну базу. Для начала нужно загрузить эти данные в Power Pivot.
Шаг 1
Открываем пустой файл Excel и активируем надстройку Power Pivot
Для этого потребуется разблокировать вкладку «Разработчик». Переходим во вкладку «Файл» и выбираем пункты «Параметры» → «Настройка ленты». В открывшемся окне в разделе «Основные вкладки» находим пункт «Разработчик», отмечаем его галочкой и нажимаем кнопку «ОК» → в основном меню Excel появляется новая вкладка «Разработчик».
Теперь на этой вкладке нажимаем кнопку «Настройки COM», в появившемся окне выбираем Microsoft Power Pivot for Excel и жмём «ОК».
![](/upload/setka_images/14441620122022_c6dd8eec548befd472c046e0abd19b13b7574ff7.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Готово — на панели появилась отдельная вкладка Power Pivot.
Шаг 2
Открываем окно Power Pivot
Для этого переходим на вкладку Power Pivot и нажимаем на кнопку «Управление».
![](/upload/setka_images/14441920122022_f670d2b1eb99a93e60fbc29d160ff587411ea8f9.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Готово — открылось окно Power Pivot. Оно относится к файлу Excel, через который мы его открыли.
![](/upload/setka_images/14441620122022_a611bd2592e034c71645545451739790895d62c6.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
В этом окне нам нужно собрать данные из наших четырёх источников и настроить связи между ними.
Шаг 3
Выгружаем данные из баз данных
На вкладке «Главная» нажимаем кнопку «Из базы данных» и выбираем «Из Access».
![](/upload/setka_images/14441720122022_9652bf6b807d8ba83980025c86e25ca84d5fa737.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
В появившемся окне в поле «Имя базы данных» прописываем адрес, где хранится файл Access со справочником регионов, — адрес можно найти через кнопку «Обзор».
Если база данных зашифрована, в этом же окне нужно ввести имя пользователя и пароль.
Нажимаем «Далее».
![](/upload/setka_images/14441820122022_7604d7e5b33d874644c75f6e623731bfaf1e772b.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
В следующем окне появляется список всех таблиц, которые хранятся в выбранной базе данных.
В нашем случае она одна — «Справочник регионов». Выбираем её и нажимаем «Готово».
![](/upload/setka_images/14441720122022_31d2d8f5299a16535997a796d00f7e71843e513a.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Начинается импорт выбранной таблицы в Power Pivot. После этого появляется окно с результатом. Проверяем информацию и нажимаем «Закрыть».
![](/upload/setka_images/14441920122022_1f48be97561a3e9639fb16fc04a4fc881890d18d.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Готово — первые данные загрузились в Power Pivot. В окне появился отдельный лист «Справочник регионов», на нём отражена та же таблица, что была и во внешнем источнике.
![](/upload/setka_images/14441620122022_c854d5d97181d0119b28197642c195d87dc49c73.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Шаг 4
Выгружаем данные из текстового файла
На вкладке «Главная» нажимаем кнопку «Из других источников». В появившемся окне выбираем «Текстовый файл» и нажимаем «Далее».
![](/upload/setka_images/14441720122022_3b7177ca8b0a4e869aa9cfaa7ca8de81d283933f.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
В появившемся окне в поле «Имя базы данных» прописываем адрес, где хранится текстовый файл с данными о продажах.
После этого в нижней части окна появляется предпросмотр таблицы из источника данных.
При необходимости корректируем настройки — например, меняем вид разделителя столбцов или отключаем ненужные графы таблицы. Затем нажимаем «Готово».
![](/upload/setka_images/14441820122022_4b007dd12a91043952841390fe80c03939eb24c7.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Начинается импорт выбранной таблицы из текстового файла в Power Pivot. После этого появляется окно с результатом. Проверяем информацию и нажимаем «Закрыть».
![](/upload/setka_images/14441620122022_d5b6f6b34d3531ed785f38f660ab83f1db4fc552.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Готово — в окне Power Pivot появилась вторая вкладка «Продажи» — на ней более полутора миллионов строк. Напомним, в Excel без надстройки могло бы поместиться около миллиона.
![](/upload/setka_images/14441920122022_1992edc13ca43b96b18e0af3256a3aa6eb60d96f.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Шаг 5
Выгружаем данные из файла Excel
По аналогии с предыдущим шагом на вкладке «Главная» нажимаем кнопку «Из других источников». Во всплывшем окне выбираем «Файл Excel» и нажимаем «Далее».
В появившемся окне в поле «Имя базы данных» прописываем адрес, по которому хранится файл Excel со справочником товаров. Нажимаем «Далее»,
![](/upload/setka_images/14441620122022_045d7802fc05a3852c9e6a5f6b8ec30011b3e9bf.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
В следующем окне появляется список всех таблиц, которые хранятся в выбранной базе данных.
В нашем случае она одна — «Справочник товаров». Выбираем её и нажимаем «Готово».
Снова происходит импорт и появляется окно с результатом.
![](/upload/setka_images/14441620122022_65b5f9bd30cc7486321c7e7f4a416f12225c5c7a.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
В окне Power Pivot появилась третья вкладка «Справочник товаров». Также со всеми данными, которые хранились в первоисточнике — внешнем файле Excel.
![](/upload/setka_images/14441720122022_b48e6246b7bd7b9fb199e1bce690425314761faf.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Шаг 6
Выгружаем данные из файла Power Point
В этом случае выгрузить данные можно только методом «копировать — вставить».
Открываем файл Power Point, содержащий ценовую политику. Выделяем таблицу, нажимаем правую кнопку мыши и выбираем «Скопировать».
![](/upload/setka_images/14441720122022_d27eea02ec0ba0882352d5a44ea3581bb6e91ba5.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Затем возвращаемся в окно Power Pivot и на любой вкладке нажимаем кнопку «Вставить» на главной панели.
![](/upload/setka_images/14441620122022_25c4ffd8c648e33300b618cab62dacf3becc5ae8.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
В появившемся окне вводим имя таблицы — «Цены» — и нажимаем «ОК».
![](/upload/setka_images/14441720122022_bb7da7f91e910bdfd9b37c61ec491ca0c8cbf4c0.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Готово — в окне Power Pivot появилась четвёртая вкладка «Цены».
![](/upload/setka_images/14441820122022_a015575029a3aa4b98b30c237b20db3387361de4.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Важно понимать, что при таком варианте выгрузки данных — копировании и вставке — таблица не будет обновляться, если внести изменения в исходный файл Power Point.
В предыдущих трёх вариантах — при загрузке таблиц из базы данных, текстового файла и файла Excel — в случае изменения данных в исходных файлах они также изменятся и в Power Pivot. Ниже показываем, как это сделать.
Шаг 7
Обновляем данные из внешних источников в Power Pivot
Для этого на главной панели любой вкладки нужно нажать кнопку «Обновить», затем «Обновить все».
![](/upload/setka_images/14441720122022_16d86127010ce02fa927d9826109928ae3661331.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Появится окно, где будет видно, какие вкладки обновляются. В нашем случае это только первые три вкладки.
Обновление проходит быстро — примерно за 10–15 секунд обновляются три вкладки, в одной из которых более полутора миллионов строк.
Файл из Power Point придётся обновлять вручную.
![](/upload/setka_images/14441720122022_bd38b7024e8662104b8bf2939fc16d53ce27a8d8.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Моделируем данные в Power Pivot
В предыдущем разделе мы загрузили данные из внешних источников в Power Pivot. Сейчас у нас есть четыре вкладки, в которых хранятся четыре таблицы: «Справочник регионов», «Продажи», «Справочник товаров», «Цены».
Задача этого этапа — связать данные всех таблиц так, чтобы можно было анализировать информацию одновременно по всем столбцам.
Шаг 1
Меняем режим представления данных в Power Pivot
Для этого на главной вкладке окна Power Pivot нажмём кнопку «Представление диаграммы».
![](/upload/setka_images/14441620122022_55829eb5559b90524549345ff378b36b0fd71c29.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
В этом режиме отображения каждая таблица показана в виде прямоугольника, в котором перечислены её столбцы. Для удобства эти прямоугольники можно двигать и менять местами.
В этом же режиме отображения в Power Pivot настраивают связи между таблицами.
Шаг 2
Настраиваем связи между данными таблиц
Первая связь. В таблице «Продажи» есть столбец «Город», но нет столбцов «Субъекты» и «Округ». Все эти столбцы, включая столбец «Город», есть в таблице «Справочник регионов».
Соответственно, чтобы объединить данные этих двух таблиц, нужно создать связь по столбцу «Город». Для этого нужно зажать мышкой такой столбец в одной из таблиц и перетянуть его во вторую таблицу. Между таблицами появится линия.
![](/upload/setka_images/14441720122022_19c7d4c5a77edc1b2d115265f04a87f26646152a.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Готово — между таблицами «Справочник регионов» и «Продажи» появилась связь по столбцу «Город». Теперь у каждой строки продаж будет указан не только город, но также субъект и округ.
![](/upload/setka_images/14441720122022_155dfe08ba186073e613dc3cb4fc5780b71a9ca2.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Вторая связь. В таблице «Продажи» есть столбец «Товар», где перечислены ID‑номера книг, но нет названий книг и их форматов. Все эти данные находятся в таблице «Справочник товаров».
По аналогии с предыдущей связью построим связь между столбцом «Товар» в таблице «Продажи» и столбцом «ID товара» в таблице «Справочник товаров».
![](/upload/setka_images/14441720122022_07e7f45a634168b52c8eb8fe5a5999a17375993a.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Названия столбцов, по которым строят связи между таблицами, не обязательно должны быть одинаковыми. Главное, чтобы в таких столбцах хранилась аналогичная информация. В нашем случае это ID-номера книг.
При этом Power Pivot не проверяет самостоятельно, правильно ли настроена связь — совпадают ли данные столбцов двух таблиц, — поэтому проводить связи между таблицами нужно внимательно.
Третья связь. Теперь к уже объединённым данным нужно добавить цены. Они хранятся в четвёртой таблице. Настроим связь между ней и таблицей «Справочник товаров» по столбцу «Формат».
Благодаря этой связи в «Справочнике товаров» появится информация о ценах на книги.
![](/upload/setka_images/14441620122022_1b6a3afa0f440397233f7b9c6c033611ca8f3ec7.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Готово. Мы построили модель из четырёх таблиц из разных источников и связали данные этих таблиц по идентичным столбцам. Чтобы увидеть результат, нужно создать сводную таблицу.
Шаг 3
Создаём сводную таблицу.
На главной вкладке нажимаем кнопку «Сводная таблица».
![](/upload/setka_images/14441620122022_ef250ba77342cfb0f3a761854cbd09a62d7b0bc2.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Дальше выбираем, на каком листе нужно создать сводную таблицу — на новом или на существующем.
![](/upload/setka_images/14441720122022_26f1c7108b3a6411cb0f1d97dc5e07ac543013ad.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Готово — появился новый лист для сводной таблицы. Слева на листе расположена область, где появится отчёт сводной таблицы после настроек. Справа — панель «Поля сводной таблицы», в которой мы будем работать с этими настройками.
![](/upload/setka_images/14441620122022_fcf5f1adb4ed14bf0846b927c03ccbc1a836d642.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Панель «Поля сводной таблицы» состоит из двух частей. В верхней части находится список полей — в нашем случае Power Pivot перенёс в список полей данные четырёх таблиц, которые мы связали между собой. В нижней части — четыре области: «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своё назначение.
Чтобы создать отчёт сводной таблицы, нужно выбрать необходимые поля из списка полей и перенести их в нужную область.
Подробнее о назначении областей, а также о том, как строить и настраивать сводные таблицы в стандартной версии Excel, говорили в этой статье Skillbox Media.
Для примера построим отчёт в форме сводной таблицы, который покажет количество продаж издательства в субъектах России с детализацией по формату книг.
Для этого в область «Строки» перенесём поля «Округ» и «Субъект» из «Справочника регионов», в область «Значения» — поле «Количество» из таблицы «Продажи», в область «Столбцы» — поле «Формат» из «Справочника товаров».
![](/upload/setka_images/14441620122022_2a4cd2fc89429f9a747432d1aeb0380caffc470b.png)
Скриншот: курс Skillbox «Excel + Google Таблицы с нуля до PRO»
Готово. Мы получили таблицу, где по вертикали расположены все субъекты и округа страны, по горизонтали — количество проданных книг с разбивкой по форматам.
По такому же принципу можно строить другие отчёты — в зависимости от того, какую информацию для анализа нужно получить. Например, в отчёте можно показать количество продаж с детализацией по названиям книг и датам их продажи.
Как узнать больше о работе в Excel
- В этой статье Skillbox Media собрали в одном месте 15 статей и видео об инструментах Excel, которые ускорят и упростят работу с электронными таблицами.
- В Skillbox есть курс «Excel + Google Таблицы с нуля до PRO». Он подойдёт как новичкам, которые хотят научиться работать в Excel с нуля, так и уверенным пользователям, которые хотят улучшить свои навыки. На курсе учат быстро делать сложные расчёты, визуализировать данные, строить прогнозы, работать с внешними источниками данных, создавать макросы и скрипты.
- Кроме того, Skillbox даёт бесплатный доступ к записи онлайн-интенсива «Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня». Он подходит для начинающих пользователей. На нём можно научиться создавать и оформлять листы, вводить данные, использовать формулы и функции для базовых вычислений, настраивать пользовательские форматы и создавать формулы с абсолютными и относительными ссылками.
Другие материалы Skillbox Media по Excel
- Как сделать сводные таблицы в Excel: пошаговая инструкция со скриншотами
- Как сделать ВПР в Excel: пошаговая инструкция со скриншотами
- Основы Excel: работаем с выпадающим списком. Пошаговая инструкция со скриншотами
- Как сделать сортировку в Excel: детальная инструкция со скриншотами
- Как установить фильтр и расширенный фильтр в Excel: детальные инструкции со скриншотами
- Для чего нужны макросы в Excel и как их сделать: инструкция со скриншотами