powerpivot excel что это
Надстройка для Excel — Power Pivot, или жизнь после 1 048 576 строк
Как показывает практика, если в файле Excel больше 50 тысяч строк, да еще формулы типа ВПР, он падает и умирает. Потом восстает, как зомби, чтобы выпить нашу кровь и нервы. Ведет он себя тоже как зомби — еле двигается и «ни черта» не соображает.
Что же делать? Ответ простой: начать работать с надстройкой для Excel — Power Pivot. Этот инструмент создан для работы с данными. Он может легко обрабатывать миллионы строк!
Надстройка Power Pivot в Excel
Power Pivot – это надстройка Excel, с помощью которой можно работать с данными в несколько миллионов строк, объединять таблицы в модель данных и создавать аналитические вычисления.
В «обычном» Excel пользователи ограничены количеством строк в таблице – не более размера листа в 1 048 тысяч строк, но в Power Pivot такого ограничения нет. Надстройка может подключаться к данным из внешних источников и работать с большими объемами информации в миллионы строк.
Открыть надстройку Power Pivot можно, нажав на вкладке меню Power Pivot кнопку Управление. Эта вкладка выглядит одинаково во всех версиях Excel.
Хорошая новость: начиная c версий после 2019 года компания Microsoft анонсировала включение Power Pivot во все версии Excel.
Работа с данными в Power Pivot
Как правило, разработка отчетов в Power Pivot происходит в следующем порядке:
Подключения к источникам, связи и вычисления настраиваются в отчете один раз. При изменении исходных данных отчеты можно обновить в меню Данные → Обновить все. Давайте разберем подробнее, как это работает.
Добавление данных в Power Pivot
Чтобы начать работать с Power Pivot, перейдите на вкладку меню Power Pivot → нажмите Управление. Добавить данные в открывшейся надстройке можно несколькими способами:
Способ 1. Подключение к данным с помощью встроенных инструментов импорта.
В Power Pivot есть свои инструменты для импорта внешних данных, которые можно найти на вкладке Главная → кнопки Из базы данных, Из службы данных, Из других источников.
С помощью встроенных инструментов настраивается подключение к 15 видам источников данных.
Увидеть весь список можно в окне «Мастер импорта таблиц», которое открывается в меню Главная → Из других источников.
Настроим подключение к данным на примере файла Excel. Укажите путь к файлу, поставьте галочку «Использовать первую строку в качестве заголовков столбцов», выберите таблицы, жмем «Готово». У вас в окне включится счетчик импорта строк — работает довольно быстро. В результате импорта в окне Power Pivot появятся вкладки с таблицами.
Способ 2. Добавить данные из Power Query.
Загрузка данных с помощью инструментов Power Pivot делается легко, но Power Query лучше подходит для импорта и значительно расширяет возможности аналитики. В нем намного больше доступных источников и возможностей для обработки таблиц произвольного вида.
Чтобы настроить подключение с помощью Power Query, вам нужно создать запрос к источнику данных. Список ранее созданных запросов находится на вкладке «Запросы и подключения». Нажмите на запрос правой кнопкой мышки и выберите Загрузить в… В открывшемся окне доступных вариантов импорта поставьте галочку «Добавить эти данные в модель данных». Задать настройки импорта также можно в самом редакторе Power Query.
К сожалению, в Excel 2010 Power Pivot почти невозможно «подружить» с Power Query и этот новый функционал в старом Excel сильно ограничен.
Интерфейс Power Pivot
Разберем подробнее интерфейс Power Pivot.
В окне Power Pivot есть:
Модель данных и связи
Чтобы перейти к настройке связей между таблицами, выберите в меню Главная → Представление диаграммы (вернутся обратно к просмотру таблиц можно, нажав Представление данных).
Модель данных в Power Pivot – это набор таблиц, объединенных связями.
Графически связь таблиц обозначается линией между ними, как в примере на рисунке. Чтобы создать связь, выделите мышкой поле в одной таблице и «перетащите» его на соответствующее ему поле в области другой таблицы.
Power Pivot поддерживает типы связей «один к одному», «один ко многим».
Если выделить мышкой линию связи в модели данных, то можно увидеть, с помощью каких полей задана связь. Выделенные линии можно удалять. Или, щелкнув по ним дважды, менять связи в открывшемся окне. Также управление связями доступно в окне, которое открывается в меню Конструктор → Управление связями.
Вычисления в Power Pivot
Формулы Power Pivot пишут на языке DAX (Data Analysis Expressions, выражения для анализа данных). DAX-формулы позволяют, по аналогии с формулами Excel, выполнять вычисления и/или настраивать произвольную фильтрацию и представление данных в таблицах.
Язык DAX впервые появился в 2010 году вместе с надстройкой Power Pivot. В этом языке сотни функций, с помощью которых можно создавать аналитические расчеты. Кроме Power Pivot в Excel, DAX-формулы также доступны в Power BI и Analysis Services. То есть эти формулы вам точно пригодятся.
Вычисления с помощью DAX-формул создаются в виде:
Вычисляемые столбцы представляют собой столбцы в таблицах данных, созданные с помощью формул. Чтобы добавить такой столбец, щелкните мышкой дважды по столбцу слева «Добавление столбца», введите название вычисления, а затем знак «=» и формулу в строке формул.
Вычисляемый столбец похож на любой другой столбец в таблице. Также с помощью таких столбцов можно создавать связи в модели. Вычисляемые столбцы в Excel рассчитываются в момент загрузки (обновления) данных, а значения в самом столбце сохраняются, увеличивая размер файла. То есть столбцы – это статические вычисления.
Как и вычисляемые столбцы, меры тоже создаются с помощью DAX-формул. В отличие от столбцов, меры – это динамические вычисления, результат которых зависит от контекста – его можно увидеть в отчете, где мы задаем в разрезе каких полей, фильтров и др. вычислить меру. Меры записываются под таблицей в области вычислений.
Меры в Power Pivot можно превратить в KPI – ключевые показатели эффективности. Для этого выделите меру и нажмите на кнопку Создать KPI в меню Главная. Кроме мер, созданных пользователями, в Excel также есть неявные меры. Они создаются автоматически при формировании сводной таблицы, когда пользователь помещает данные в область значений. Чтобы посмотреть, есть ли у вас в Power Pivot неявные меры, выберите на вкладке Главная → Показать скрытые.
Запуск надстройки Power Pivot для Excel
Power Pivot — это надстройка, с помощью которую можно выполнять мощный анализ данных в Excel. Надстройка встроена в определенные версии Office, но по умолчанию она не включена.
Список версий Office, включа которых есть Power Pivot, а также список неохватных версий см. в вопросе Где Power Pivot?
Вот как можно включить Power Pivot перед первым использованием.
Перейдите на вкладку Файл > Параметры > Надстройки.
В поле Управление выберите Надстройки COM и нажмите Перейти.
Установите флажок Microsoft Office Power Pivot и нажмите кнопку ОК. Если установлены другие версии Power Pivot, то они будут также перечислены в списке надстроек COM. Выберите надстройку Power Pivot для Excel.
На ленте появится вкладка Power Pivot.
Откройте окно Power Pivot.
Щелкните Power Pivot.
На этой вкладке можно работать со сводными таблицами, вычисляемыми полями и ключевыми показателями эффективности Power Pivot, а также создавать связанные таблицы.
Нажмите кнопку Управление.
Откроется окно Power Pivot. Здесь вы можете нажать кнопку «Внешние данные», чтобы использовать мастер импорта таблиц для фильтрации данных при их добавлении в файл, создания связей между таблицами, обогащения данных вычислениями и выражениями и создании сводных таблиц и сводных диаграмм на их основе.
Устранение неполадок: исчезновение ленты Power Pivot
В редких случаях лента Power Pivot исчезает из меню, когда Excel определяет, что надстройка нарушает его работу. Это может произойти, если Excel неожиданно завершает работу при открытом окне Power Pivot. Чтобы восстановить меню Power Pivot:
Выберите Файл > Параметры > Надстройки.
В поле Управление выберите Отключенные объекты > Перейти.
Выберите Microsoft OfficePower Pivot и нажмите кнопку Включить.
Если не удается восстановить ленту Power Pivot, выполнив указанные выше действия, или лента исчезает, когда вы закрываете и снова открываете Excel, сделайте следующее:
откройте меню Пуск > Выполнить и введите команду regedit;
В редакторе реестра разверните следующий раздел:
Для Excel 2013: HKEY_CURRENT_USER > Software > Microsoft > Office > 15.0 > User Settings.
Для Excel 2016: HKEY_CURRENT_USER > Software > Microsoft > Office > 16.0 > User Settings
щелкните правой кнопкой мыши PowerPivotExcelAddin, а затем нажмите Удалить;
вернитесь в верхнюю часть редактора реестра;
разверните раздел HKEY_CURRENT_USER > Software > Microsoft > Office > Excel > Addins;
щелкните правой кнопкой мыши PowerPivotExcelClientAddIn.NativeEntry.1, а затем нажмите Удалить;
закройте редактор реестра;
включите надстройку, выполнив действия, описанные в начале этой статьи.
Excel Power Pivot — Краткое руководство
Excel Power Pivot — это эффективный и мощный инструмент, который поставляется вместе с Excel в качестве надстройки. С Power Pivot вы можете загружать сотни миллионов строк данных из внешних источников и эффективно управлять данными с помощью мощного механизма xVelocity в сильно сжатой форме. Это позволяет выполнять расчеты, анализировать данные и получать отчет для выработки выводов и решений. Таким образом, человек, имеющий практический опыт работы с Excel, сможет выполнить анализ данных высокого уровня и принять решение за считанные минуты.
Этот урок будет охватывать следующее —
Power Pivot Особенности
Что делает Power Pivot сильным инструментом, так это набор его функций. Вы узнаете о различных функциях Power Pivot в главе «Функции Power Pivot».
Данные Power Pivot из различных источников
Power Pivot может сопоставлять данные из различных источников данных для выполнения необходимых расчетов. Вы узнаете, как получить данные в Power Pivot, в главе «Загрузка данных в Power Pivot».
Модель данных Power Pivot
Сила Power Pivot заключается в его базе данных — модели данных. Данные хранятся в виде таблиц данных в модели данных. Вы можете создать отношения между таблицами данных, чтобы объединить данные из разных таблиц данных для анализа и составления отчетов. Глава — Понимание модели данных (Power Pivot Database) дает вам подробную информацию о модели данных.
Управление моделью данных и отношениями
Вам необходимо знать, как вы можете управлять таблицами данных в модели данных и связями между ними. Подробнее об этом вы узнаете в главе «Управление моделью данных Power Pivot».
Создание Power Pivot Tables и Power Pivot Charts
Power PivotTables и Power Pivot Chart предоставляют вам возможность проанализировать данные, чтобы прийти к выводам и / или решениям.
Вы узнаете, как создавать сводные таблицы Power в главах — Создание сводных таблиц Power и плоских сводных таблиц.
Вы узнаете, как создавать Power PivotCharts в главе — Power PivotCharts.
Основы DAX
DAX — это язык, используемый в Power Pivot для выполнения вычислений. Формулы в DAX аналогичны формулам Excel, но с одним отличием: формулы Excel основаны на отдельных ячейках, а формулы DAX — на столбцах (полях).
Вы поймете основы DAX в главе — Основы DAX.
Изучение и представление данных Power Pivot
Вы можете изучить данные Power Pivot, которые находятся в модели данных, с помощью Power PivotTables и Power Pivot Charts. В этом уроке вы узнаете, как вы можете исследовать данные и сообщать о них.
Иерархии
Вы можете определить иерархии данных в таблице данных, чтобы было легко обрабатывать связанные поля данных вместе в Power PivotTables. Вы узнаете подробности создания и использования Иерархий в главе «Иерархии в Power Pivot».
Эстетические отчеты
Вы можете создавать эстетические отчеты о своем анализе данных с помощью Power Pivot Charts и / или Power Pivot Charts. У вас есть несколько вариантов форматирования, чтобы выделить важные данные в отчетах. Отчеты являются интерактивными по своей природе, что позволяет человеку, просматривающему компактный отчет, быстро и легко просматривать любую необходимую информацию.
Эти подробности вы узнаете в главе «Эстетические отчеты с данными Power Pivot».
Excel Power Pivot — Установка
В этой главе вы узнаете, что делает Power Pivot сильным и востребованным инструментом для аналитиков и лиц, принимающих решения.
Power Pivot на ленте
Первый шаг для продолжения Power Pivot — убедиться, что на ленте доступна вкладка POWERPIVOT. Если у вас Excel 2013 или более поздние версии, на ленте появится вкладка POWERPIVOT.
Если у вас Excel 2010, вкладка POWERPIVOT может не отображаться на ленте, если вы еще не включили надстройку Power Pivot.
Надстройка Power Pivot
Надстройка Power Pivot — это надстройка COM, которую необходимо включить, чтобы получить все возможности Power Pivot в Excel. Даже когда на ленте появляется вкладка POWERPIVOT, необходимо убедиться, что надстройка включена для доступа ко всем функциям Power Pivot.
Шаг 1 — Перейдите на вкладку ФАЙЛ на ленте.
Шаг 2 — Нажмите Параметры в раскрывающемся списке. Откроется диалоговое окно «Параметры Excel».
Шаг 3 — Следуйте инструкциям следующим образом.
В поле Управление выберите Надстройки COM из раскрывающегося списка.
Нажмите кнопку «Перейти». Откроется диалоговое окно «Надстройки COM».
Проверьте Power Pivot и нажмите ОК.
В поле Управление выберите Надстройки COM из раскрывающегося списка.
Нажмите кнопку «Перейти». Откроется диалоговое окно «Надстройки COM».
Проверьте Power Pivot и нажмите ОК.
Что такое Power Pivot?
Excel Power Pivot — это инструмент для интеграции и обработки больших объемов данных. С Power Pivot вы можете легко загружать, сортировать и фильтровать наборы данных, которые содержат миллионы строк, и выполнять необходимые вычисления. Вы можете использовать Power Pivot в качестве специального решения для отчетности и аналитики.
Лента Power Pivot, как показано ниже, содержит различные команды — от управления моделью данных до создания отчетов.
Окно Power Pivot будет иметь ленту, как показано ниже —
Почему Power Pivot — сильный инструмент?
Когда вы вызываете Power Pivot, Power Pivot создает определения данных и соединения, которые сохраняются вместе с вашим файлом Excel в сжатой форме. Когда данные в источнике обновляются, они автоматически обновляются в вашем файле Excel. Это облегчает использование данных, хранящихся в других местах, но требуется для изучения времени от времени и принятия решений. Исходные данные могут быть в любой форме — от текстового файла или веб-страницы до различных реляционных баз данных.
Удобный интерфейс Power Pivot в окне PowerPivot позволяет выполнять операции с данными без знания какого-либо языка запросов к базе данных. Затем вы можете создать отчет о вашем анализе в течение нескольких секунд. Отчеты являются универсальными, динамичными и интерактивными и позволяют вам дополнительно исследовать данные, чтобы получить представление и прийти к выводам / решениям.
Данные, с которыми вы работаете в Excel и в окне Power Pivot, хранятся в аналитической базе данных внутри книги Excel, а мощный локальный механизм загружает, запрашивает и обновляет данные в этой базе данных. Поскольку данные находятся в Excel, они сразу доступны для сводных таблиц, сводных диаграмм, Power View и других функций Excel, которые вы используете для агрегирования и взаимодействия с данными. Представление данных и интерактивность обеспечиваются Excel, а объекты данных и презентации Excel содержатся в одном файле рабочей книги. Power Pivot поддерживает файлы размером до 2 ГБ и позволяет работать с 4 ГБ данных в памяти.
Мощные функции в Excel с Power Pivot
Функции Power Pivot бесплатны в Excel. Power Pivot повысил производительность Excel благодаря мощным функциям, которые включают следующее:
Способность обрабатывать большие объемы данных, сжатые в небольшие файлы, с удивительной скоростью.
Фильтруйте данные и переименовывайте столбцы и таблицы при импорте.
Организовать таблицы в отдельные страницы с вкладками в окне Power Pivot по сравнению с таблицами Excel, распределенными по всей книге или по нескольким таблицам в одной рабочей таблице.
Создайте связи между таблицами, чтобы совместно анализировать данные в таблицах. До Power Pivot приходилось полагаться на интенсивное использование функции VLOOKUP, чтобы объединить данные в одну таблицу перед таким анализом. Раньше это было трудоемким и подверженным ошибкам.
Добавьте мощность в простую сводную таблицу со многими дополнительными функциями.
Предоставить язык выражений анализа данных (DAX) для написания расширенных формул.
Добавьте вычисляемые поля и вычисляемые столбцы в таблицы данных.
Создайте KPI для использования в сводных таблицах и отчетах Power View.
Способность обрабатывать большие объемы данных, сжатые в небольшие файлы, с удивительной скоростью.
Фильтруйте данные и переименовывайте столбцы и таблицы при импорте.
Организовать таблицы в отдельные страницы с вкладками в окне Power Pivot по сравнению с таблицами Excel, распределенными по всей книге или по нескольким таблицам в одной рабочей таблице.
Создайте связи между таблицами, чтобы совместно анализировать данные в таблицах. До Power Pivot приходилось полагаться на интенсивное использование функции VLOOKUP, чтобы объединить данные в одну таблицу перед таким анализом. Раньше это было трудоемким и подверженным ошибкам.
Добавьте мощность в простую сводную таблицу со многими дополнительными функциями.
Предоставить язык выражений анализа данных (DAX) для написания расширенных формул.
Добавьте вычисляемые поля и вычисляемые столбцы в таблицы данных.
Создайте KPI для использования в сводных таблицах и отчетах Power View.
Вы поймете особенности Power Pivot подробно в следующей главе.
Использование Power Pivot
Вы можете использовать Power Pivot для следующих целей:
Для выполнения мощного анализа данных и создания сложных моделей данных.
Быстрое объединение больших объемов данных из нескольких разных источников.
Для анализа информации и обмена знаниями в интерактивном режиме.
Для написания расширенных формул на языке выражений анализа данных (DAX).
Создать ключевые показатели эффективности (KPI).
Для выполнения мощного анализа данных и создания сложных моделей данных.
Быстрое объединение больших объемов данных из нескольких разных источников.
Для анализа информации и обмена знаниями в интерактивном режиме.
Для написания расширенных формул на языке выражений анализа данных (DAX).
Создать ключевые показатели эффективности (KPI).
Моделирование данных с помощью Power Pivot
Power Pivot предоставляет расширенные возможности моделирования данных в Excel. Данные в Power Pivot управляются в модели данных, которая также называется базой данных Power Pivot. Вы можете использовать Power Pivot, чтобы получить новое представление о ваших данных.
Вы можете создавать отношения между таблицами данных, чтобы вы могли выполнять общий анализ данных в таблицах. С DAX вы можете писать расширенные формулы. Вы можете создавать вычисляемые поля и вычисляемые столбцы в таблицах данных в модели данных.
Вы можете определить иерархии в данных, чтобы использовать их везде в книге, включая Power View. Вы можете создавать KPI для использования в сводных таблицах и отчетах Power View, чтобы сразу увидеть, является ли производительность включенной или выключенной для одной или нескольких метрик.
Бизнес-аналитика с Power Pivot
Бизнес-аналитика (BI) — это, по сути, набор инструментов и процессов, которые люди используют для сбора данных, превращения их в значимую информацию и принятия более эффективных решений. Возможности BI Power Pivot в Excel позволяют собирать данные, визуализировать их и обмениваться информацией с людьми в вашей организации на нескольких устройствах.
Вы можете предоставить доступ к своей книге в среду SharePoint, в которой включены службы Excel. На сервере SharePoint службы Excel обрабатывают и отображают данные в окне браузера, где другие могут анализировать данные.
Excel Power Pivot — Возможности
Наиболее важной и мощной особенностью Power Pivot является его база данных — модель данных. Следующей важной особенностью является механизм аналитики в памяти xVelocity, который позволяет работать с несколькими большими базами данных в течение нескольких минут. Есть несколько более важных функций, которые поставляются с надстройкой PowerPivot.
В этой главе вы получите краткий обзор функций Power Pivot, которые подробно будут показаны позже.
Загрузка данных из внешних источников
Вы можете загрузить данные в модель данных из внешних источников двумя способами:
Загрузите данные в Excel, а затем создайте модель данных Power Pivot.
Загрузка данных непосредственно в модель данных Power Pivot.
Загрузите данные в Excel, а затем создайте модель данных Power Pivot.
Загрузка данных непосредственно в модель данных Power Pivot.
Второй способ более эффективен благодаря эффективному способу обработки данных в памяти Power Pivot.
Для получения более подробной информации см. Главу «Загрузка данных в Power Pivot».
Окно Excel и Power Pivot Window
Когда вы начнете работать с Power Pivot, одновременно откроются два окна — окно Excel и окно Power Pivot. Именно через окно PowerPivot вы можете напрямую загружать данные в модель данных, просматривать данные в представлениях данных и представлениях диаграмм, создавать связи между таблицами, управлять связями и создавать отчеты Power PivotTable и / или PowerPivot Chart.
Вам не нужно иметь данные в таблицах Excel при импорте данных из внешних источников. Если у вас есть данные в виде таблиц Excel в книге, вы можете добавить их в модель данных, создавая таблицы данных в модели данных, которые связаны с таблицами Excel.
При создании сводной таблицы или сводной диаграммы из окна Power Pivot они создаются в окне Excel. Однако данные по-прежнему управляются из модели данных.
Вы всегда можете легко переключаться между окном Excel и окном Power Pivot в любое время.
Модель данных
Модель данных — самая мощная функция Power Pivot. Данные, полученные из различных источников данных, поддерживаются в модели данных в виде таблиц данных. Вы можете создать отношения между таблицами данных, чтобы объединить данные в таблицах для анализа и составления отчетов.
Подробнее о модели данных вы узнаете в главе «Общие сведения о модели данных (Power Pivot Database)».
Оптимизация памяти
Модель данных Power Pivot использует хранилище xVelocity, которое сильно сжимается при загрузке данных в память, что позволяет хранить в памяти сотни миллионов строк.
Таким образом, если вы загружаете данные непосредственно в модель данных, вы будете делать это в эффективной сильно сжатой форме.
Компактный размер файла
Если данные загружаются непосредственно в модель данных, при сохранении файла Excel они занимают очень мало места на жестком диске. Вы можете сравнить размеры файлов Excel, первый с загрузкой данных в Excel, а затем с созданием модели данных, а второй с загрузкой данных непосредственно в модель данных, пропустив первый шаг. Второй будет в 10 раз меньше первого.
Power PivotTables
Вы можете создать Power PivotTables из окна Power Pivot. Созданные таким образом сводные таблицы основаны на таблицах данных в модели данных, что позволяет объединять данные из связанных таблиц для анализа и составления отчетов.
Power PivotCharts
Вы можете создать Power PivotCharts из окна Power Pivot. Созданные таким образом сводные диаграммы основаны на таблицах данных в модели данных, что позволяет объединять данные из связанных таблиц для анализа и составления отчетов. Power PivotCharts обладает всеми функциями сводных диаграмм Excel и многими другими, такими как кнопки полей.
Вы также можете иметь комбинации Power PivotTable и Power PivotChart.
DAX Language
Преимущество Power Pivot заключается в языке DAX, который можно эффективно использовать в модели данных для выполнения расчетов с данными в таблицах данных. У вас могут быть рассчитанные столбцы и вычисляемые поля, определенные DAX, которые можно использовать в сводных таблицах и сводных диаграммах питания.
Excel Power Pivot — загрузка данных
В этой главе мы научимся загружать данные в Power Pivot.
Вы можете загрузить данные в Power Pivot двумя способами:
Загрузить данные в Excel и добавить их в модель данных
Загружайте данные непосредственно в PowerPivot, заполняя модель данных, которая является базой данных PowerPivot.
Загрузить данные в Excel и добавить их в модель данных
Загружайте данные непосредственно в PowerPivot, заполняя модель данных, которая является базой данных PowerPivot.
Если вам нужны данные для Power Pivot, сделайте это вторым способом, даже если Excel даже не знает об этом. Это потому, что вы будете загружать данные только один раз, в сильно сжатом формате. Чтобы понять разницу, предположим, что вы загружаете данные в Excel, сначала добавляя их в модель данных, размер файла, скажем, 10 МБ.
Если вы загружаете данные в PowerPivot и, следовательно, в модель данных, пропуская дополнительный шаг Excel, размер вашего файла может составлять всего 1 МБ.
Источники данных, поддерживаемые Power Pivot
Вы можете либо импортировать данные в модель данных Power Pivot из различных источников данных, либо устанавливать соединения и / или использовать существующие соединения. Power Pivot поддерживает следующие источники данных —