3 401 
Оставить комментарий
Сохранить статью:
Что это? Power Pivot – надстройка в Excel для работы со сводными таблицами, построения диаграмм на основе разобщенных источников информации. Это мощный аналитический инструмент, не имеющий ограничений по строкам (в отличие от Excel) и значительно быстрее обрабатывающий данные.
Как используется? Чтобы запустить Power Pivot, выберите его вкладку в ленте либо следуйте алгоритму надстройки при первом запуске, описанному в нашем материале. После инициализации Power Pivot вы можете приступать к работе.
В статье рассказывается:
- Особенности Power Pivot
- Как работает Power Pivot
- Обучение работе в Power Pivot
- 2 способа добавления данных в Power Pivot
- Модель данных и настройка связей в Power Pivot
- Сводные таблицы и сводные диаграммы в Power Pivot
-
Пройди тест и узнай, какая сфера тебе подходит:
айти, дизайн или маркетинг.Бесплатно от Geekbrains
Особенности Power Pivot
Power Pivot – это дополнительный инструмент Еxcel с мощнейшими функциональными возможностями. Его предназначение – обеспечение связей между разнообразными источниками данных, обработка этих данных, причем с достаточно быстрой скоростью, даже если объёмы информации огромны.
Изначально Power Pivot представлял собой настройку для Еxcel, которую нужно было скачивать и устанавливать. Но с 2013 она уже изначально есть в составе программы. Кроме того, Power Pivot входит и в Power BI наряду с мощнейшим по своим возможностям инструментом Power Query, предназначенным для визуальной обработки данных.
Power Pivot действительно уникальный продукт, и вот почему:
- Количество строк, доступных для обработки, не ограничено (в отличие от Excel). То есть, можно подгружать таблицы каких угодно размеров.
- В Power Pivot есть отличная опция для сжимания данных, загружаемых в Модель. Текстовый файл с исходным размером в 50 Мб по итогу запросто превращается в 3 Мб.
- По сути, Power Pivot обладает полноценным функционалом для работы с базами данных, поэтому программа справляется с поставленными задачами очень быстро. Она даже на не самом новом ноутбуке запросто проштудирует 10-15 млн. записей и соберет результаты анализа в сводной таблице.
входят в ТОП-30 с доходом
от 210 000 ₽/мес
Скачивайте и используйте уже сегодня:
Топ-30 самых востребованных и высокооплачиваемых профессий 2023
Поможет разобраться в актуальной ситуации на рынке труда
Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка
Только проверенные нейросети с доступом из России и свободным использованием
ТОП-100 площадок для поиска работы от GeekBrains
Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽
Интерфейс Power Pivot интуитивно понятен, тут не нужно знать какой-то специальный язык запросов, чтобы получать из базы данных информацию и работать с ней. Потом в считанные секунды вы можете сформировать отчет по проведенному анализу. Отчеты программа строит универсальные, динамичные и интерактивные. По ним можно выполнять дополнительные исследования, получать представления, на основании которых формулировать выводы и решения.
Местоположение данных, запрашиваемых для работы в Excel и в Power Pivot – аналитическая база данных в книге Excel. Они загружаются, запрашиваются и обновляются с помощью мощного встроенного механизма. Хранение данных в Excel позволяет задействовать весь функционал Excel для взаимодействия с ними (сводные таблицы, сводные диаграммы, Power View и проч.).
Excel обеспечивает представление и интерактивность, и в общем файле рабочей книги объединены объекты данных и презентации. Power Pivot умеет работать с файлами размером до 2 ГБ и держать в памяти до 4 ГБ данных.
Именно Power Pivot придет на помощь, когда стандартного функционала Excel будет недостаточно для обработки нужных форматов и объёмов информации.
Как работает Power Pivot
По сути, Power Pivot это надстройка для Excel, позволяющая запрашивать данные из разных источников и выполнять с ними нужные манипуляции.
Power Pivot – бесплатная программа. Тем, у кого стоит Excel 2010 года, придется зайти на сайт Microsoft и загрузить её. В версиях с 2013 года и далее она уже является частью встроенного функционала. В macOS-версии Excel расширения Power Pivot , к сожалению, нет.
Порядок действий при использовании Power Pivot следующий:
- Находите в разных источниках (в базах Microsoft Access, «1С», в текстах или таблицах, в сети) нужные вам данные и загружаете их.
- Формируете модель данных, настроив связи между загруженными сведениями. В Power Pivot для объединения данных есть собственный инструментарий, поисковые опции Excel или ВПР для этого не понадобятся.
- Если нужно, выполняете дополнительные вычисления.
- Проанализировав модели данных, Power Pivot формирует сводные отчеты, представленные в виде диаграмм либо таблиц.
Вкладка может быть не включена, и тогда вы её не увидите. Как включить? В ленте пошагово выберите следующие пункты: Файл ® Параметры ® Настроить ленту. Слева вывалится список полей. Там зайдите в «Выбрать команды» и далее «Основные вкладки». Тут нужно найти и выделить строку Power Pivot, а затем кликнуть Добавить и ОК.
Обучение работе в Power Pivot
Проще и быстрее всего осваивать работу с Power Pivot на примере. Из Microsoft можно бесплатно загрузить несколько примеров наборов данных, включающих в себя необработанные данные, модель и пример анализа этих данных. В качестве инструментов обучения это как раз то, что нужно. Они отлично демонстрируют принципы работы с большими объёмами информации.
В данном руководстве приведен образец из рабочей книги Microsoft Student Data Model. Сразу же на первой странице есть ссылка на загрузку учебных материалов и готовой модели данных.
Вот что есть в этом образце книги Excel:
- Рабочая тетрадь из четырех листов.
- Сведения на каждом из листов взаимосвязаны. Имеется в виду, что хотя бы один из заголовков столбца на листе перекликается с заголовком на следующем листе.
- Информация собрана в виде таблиц.
- Все ячейки таблиц (строки и столбцы) заполнены, пустых нет ни одной.
Кроме описанного, на сайте Microsoft есть еще разные примеры наборов данных. Можно порекомендовать ознакомиться со следующими образовательными ресурсами:
- База данных Microsoft Access с описанием олимпийских медалей.
- Три образца Business Intelligence. Они демонстрируют возможности использования Power Pivot для выгрузки данных, формирования отношений, сводных таблиц и диаграмм.
Прежде чем начать работу с любым набором данных, нужно произвести его очистку. Для этого в Excel запустите функцию CLEAN. В результате из текста уберутся непечатные символы, повторяющиеся данные, будет проверена орфография, цифры и даты представятся в нужном формате, произойдет перегруппировка данных.
2 способа добавления данных в Power Pivot
Работа с Power Pivot: во вкладке меню Power Pivot кликаете Управление. Там открывается надстройка, в которую тем или иным способом можно подгрузить данные:
- Посредством встроенных опций.
- Взять информацию из Power Query.
- Зайти в меню Power Pivot (Главная → Вставить) и тут простым действием «копировать-вставить» перенести таблицу с данными.
Способ 1: применение встроенных опций для импорта
Их местоположение в Power Pivot следующее: заходите во вкладку Главная и там выбираете варианты, где вы будете брать информацию для загрузки: из базы данных, из службы данных, из других источников.
Если вы выбираете именно этот способ (то есть, применение внутренних инструментов), здесь у вас будет доступно 15 видов источников.
Весь список, какой он есть, смотрите в окне «Мастер импорта таблиц». Как в него попасть? Путь простой: меню Главная → Из других источников.
В качестве примера – подключение к экселевскому файлу с некими сведениями. Вам следует прописать путь к файлу, поставить птичку на позиции «Использовать первую строку в качестве заголовков столбцов», указать интересующие вас таблицы и кликнуть «Готово». Тут заработает счетчик импорта строк (процедура совсем не длительная). Как только импорт произойдет, в окне Power Pivot вы увидите вкладки с таблицами.
на курсы от GeekBrains до 05 мая
Вот что есть в этом окне:
- Редакторы для всех вкладок меню Главная, Конструктор, Дополнительно.
- Строка с формулами (язык — DAX).
- Области с данными и вычисляемыми столбцами.
- Возможность добавлять вычисляемые столбцы.
- Области для внесения мер.
- Меню, вызываемое правой кнопкой мыши.
- Ярлыки с названиями таблиц, позволяющие обращаться к нужной из них (как на листах в Excel).
Способ 2: получение информации из Power Query
С Power Pivot данные загружаются абсолютно легко, и всё же Power Query тут более функционален. В его распоряжении больше источников, плюс разнообразнее возможности для анализа и обработки таблиц всевозможных видов.
Как организовать подключение посредством Power Query? Первое, что нужно сделать – сформировать запрос, адресованный источнику данных. Во вкладке «Запросы и подключения» сохраняется список запросов, которые делались раньше. Правой кнопкой мыши кликните по запросу и там нажмите Загрузить в … Появится окно доступных для импорта вариантов, и тут поставьте птичку напротив строки «Добавить эти данные в модель данных». Процедуру импорта можно настраивать и непосредственно в редакторе Power Query.
Модель данных и настройка связей в Power Pivot
Настройка связей в Power Pivot осуществляется через меню Главная и там идёте в Представление диаграммы. Чтобы снова попасть в таблицы, переходите в Представление данных.
Для того чтобы графически показать, как таблицы перекликаются между собой, здесь используют линии. Для отображения связей следует мышкой сделать выделение поля в одной таблице и затем обыкновенным образом перетянуть это поле в другую таблицу (в то место, куда вам нужно).
Типы связей, поддерживаемые в Power Pivot – это «один к одному» и «один ко многим».
- Вид заданной связи понятен по значкам на концах линий. Сторона «один» отмечается единицей «1», а там, где сторона «многие», вносится значок звездочка «*». Связь «один к одному» обозначается единицами на том и на другом конце линии.
- Задействованные в связи поля – это так называемые ключи связи. Таблицы, расположенные на конце линии, обозначенном «1» (то есть, это сторона «один»), могут иметь в ключах связи только уникальные значения. А там, где звездочка (сторона «многие»), в ключевых столбцах стоят те же самые значения, но допускается их многократное повторение.
- По стрелке на линии понятно, в каком направлении идет фильтрация.
ТОП-100 площадок для поиска работы от GeekBrains
20 профессий 2023 года, с доходом от 150 000 рублей
Чек-лист «Как успешно пройти собеседование»
Сделав мышкой выделение по линии связи, вы можете посмотреть, какие поля участвуют в создании связи. Доступно удаление выделенных линий. Если щелкнуть по линии связи два раза, открывается окно, в котором можно корректировать связи. Ещё в меню Конструктор → Управление связями есть окно, тоже позволяющее манипулировать связями.
Сводные таблицы и сводные диаграммы в Power Pivot
При формировании модели данных через Power Pivot самую сложную работу программа берет на себя, в том числе построение сводных таблиц и диаграмм. Вы со своим набором данных создаете между таблицами отношения, которые потом задействуете для выведения сводных таблиц и сводных диаграмм.
- Откройте окно Power Pivot и кликните по пункту Сводная таблица.
- Откроется диалоговое окно, в котором нужно выбрать Новый лист и кликнуть ОК.
- Далее нужно на панели Поля сводной таблицы выбрать интересующие вас поля. К примеру, в сводной таблице вам нужна фамилия ученика и его средний балл.
- Сортировка данных происходит в области «Фильтры», куда нужно перетащить выбранное поле. Например, переносите в область «Фильтры» поле «Имя класса», список фильтруется, и программа выдает средний балл ученика в классе. Метод расчета для поля «Значения» можно поменять. Рядом с именем поля выпадает список, и там нужно выбрать Настройки поля значения. К примеру, сумму оценки меняете на средний балл.
- Оцените, как у вас формируются данные. Попробуйте применять разные фильтры для сортировки с помощью стрелок списка, раскрывающегося у заголовков столбцов.
Для визуализации информации, собранной в сводной таблице, используйте сводную диаграмму.
- Выберите нужную сводную таблицу и зайдите в Анализ инструментов сводной таблицы.
- Кликните по Сводной диаграмме, там в открывшемся диалоговом окне нажмите Вставить диаграмму.
- Выберите нужную диаграмму и кликните ОК.
Если вам удобнее работать с визуализированными данными, отобразите их в виде сводной диаграммы.
- Зайдите в окно Power Pivot, далее – Главная и там в стрелке, где раскрывается список – Сводная таблица. Вывалится список параметров.
- Вам нужен пункт Сводная диаграмма.
- Далее выбираете Новый лист и жмёте ОК. На новом листе увидите заполнитель PivotChart.
- Теперь нужно на странице Анализ инструментов сводной диаграммы кликнуть Список полей, тогда появится панель полей сводной диаграммы.
- Нужные поля в сводную диаграмму добавляются перетаскиванием. Вы получите средний балл для классов, с фильтрацией по семестрам.
- Оцените, как у вас сформировались данные. Попробуйте применять разные фильтры для сортировки с помощью стрелок списка, раскрывающегося у заголовков столбцов.
В общем, работать с Power Pivot несложно. И если вы нормально ориентируетесь в Еxcel, то легко разберетесь и с данным инструментом, более того, сделать это обязательно нужно! Это отличная программа для работы с объёмными базами данных, и в вашем арсенале она точно не будет лишней.
Разберем 11 самых важных жизненных вопросов
Только до 
Эксклюзивные лекции по основам программирования
