Что такое pivot в excel

Запуск надстройки Power Pivot для Excel

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

Список версий Office, которые включают Power Pivot, а также список версий, в которых их нет, см. в вопросе Где Power Pivot?

Вот как можно включить Power Pivot перед первым использованием.

1. Перейдите на вкладку Файл >Параметры >Надстройки.
2. В поле Управление выберите Надстройки COM и нажмите Перейти.
3. Установите флажок Microsoft Office Power Pivot и нажмите кнопку ОК. Если установлены другие версии Power Pivot, то они будут также перечислены в списке надстроек COM. Выберите надстройку Power Pivot для Excel.

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

Откройте окно Power Pivot.

1. Щелкните Power Pivot. На этой вкладке можно работать со сводными таблицами, вычисляемыми полями и ключевыми показателями эффективности Power Pivot, а также создавать связанные таблицы.
2. Нажмите кнопку Управление.

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

Устранение неполадок: исчезновение ленты Power Pivot

В редких случаях лента Power Pivot исчезает из меню, когда Excel определяет, что надстройка нарушает его работу. Это может произойти, если Excel неожиданно завершает работу при открытом окне Power Pivot. Чтобы восстановить меню Power Pivot:

1. Выберите Файл >Параметры >Надстройки.
2. В поле Управление выберите Отключенные объекты >Перейти.
3. Выберите Microsoft OfficePower Pivot и нажмите кнопку Включить.

Если не удается восстановить ленту Power Pivot, выполнив указанные выше действия, или лента исчезает, когда вы закрываете и снова открываете Excel, сделайте следующее:

1. закройте Excel;
2. откройте меню Пуск >Выполнить и введите команду regedit;
3. В редакторе реестра разверните следующий раздел: Для Excel 2013: HKEY_CURRENT_USER >Software >Microsoft >Office >15.0 >User Settings. Для Excel 2016: HKEY_CURRENT_USER >Software >Microsoft >Office >16.0 >User Settings
4. щелкните правой кнопкой мыши PowerPivotExcelAddin, а затем нажмите Удалить;
5. вернитесь в верхнюю часть редактора реестра;
6. разверните раздел HKEY_CURRENT_USER >Software >Microsoft >Office >Excel >Addins;
7. щелкните правой кнопкой мыши PowerPivotExcelClientAddIn.NativeEntry.1, а затем нажмите Удалить;
8. закройте редактор реестра;
9. откройте Excel;
10. включите надстройку, выполнив действия, описанные в начале этой статьи.

Power Pivot — обзор и обучение

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

Power Pivot — одно из трех средств анализа данных, доступных в Excel:

Ресурсы по Power Pivot

Приведенные ниже ссылки и сведения помогут вам освоиться с Power Pivot (в том числе узнать, как включить Power Query в Excel и как начать работу с Power Pivot), а также найти учебники и подключиться к сообществу.

Как получить Power Pivot?

Power Pivot можно использовать в качестве надстройки для Excel, которую можно включить, выполнив несколько простых действий. Базовая технология моделирования Power Pivot используется также в конструкторе Power BI Designer, который является частью службы Power BI, предлагаемой корпорацией Майкрософт.

Начало работы с Power Pivot

Когда надстройка Power Pivot включена, на ленте появляется вкладка Power Pivot, которая показана на следующем изображении.

На вкладке ленты Power Pivot в разделе Модель данных выберите управление.

После выбора элемента Управление появляется окно Power Pivot, в котором вы можете просматривать модель данных и управлять ею, добавлять вычисления, устанавливать отношения и видеть элементы своей модели данных Power Pivot. Модель данных — это коллекция таблиц или других данных, между которыми зачастую установлены отношения. На рисунке ниже показано окно Power Pivot с таблицей.

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

Краткое руководство по использованию Power Pivot вы найдете в следующей статье:

В дополнение к этому руководству по следующей ссылке вы найдете исчерпывающую подборку ссылок, ресурсов и дополнительных сведений о Power Pivot:

В последующих разделах перечислены дополнительные ресурсы и руководства, в которых подробнее рассказывается о том, как использовать Power Pivot, в том числе в сочетании с Power Query и Power View, для самостоятельного выполнения комплексных, интуитивно понятных задач бизнес-аналитики в Excel.

Учебники по PowerPivot

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

• Создание модели данных в Excel (начинается с базовой модели данных, которая затем настраивается с помощью Power Pivot)
• Импорт данных в Excel и создание модели данных (первый из шести конечных рядов учебников)
• Оптимизация модели данных для отчетов Power View
• Краткое руководство. Обучение основам DAX за 30 минут

Дополнительные сведения о Power Pivot

Power Pivot проста в использовании и быстро работает. Эта надстройка позволяет создавать многофункциональные и сложные вычисления, индикаторы и формулы. Приведенные ниже ссылки помогут вам определиться со многими задачами, которые можно выполнять с помощью Power Pivot.

• Создание модели данных с эффективным использованием памяти с помощью Excel 2013 и надстройки Power Pivot
• Общие сведения о вычислениях в PowerPivot
• Выражения анализа данных (DAX) в PowerPivot
• Иерархии в PowerPivot
• Агрегатные функции в PowerPivot
• Power Pivot: мощные средства анализа и моделирования данных в Excel (сравнение моделей данных)
• Оптимизатор размера книги (скачивание)

По следующей ссылке вы найдете исчерпывающий набор ссылок и полезных сведений (еще раз):

Ссылки на форумы и связанные темы

Множество разных людей используют Power Query, и они с удовольствием делятся тем, что узнали. С помощью указанных ниже ресурсов вы можете пообщаться с другими пользователями в сообществе Power Query.

• Форум Power Pivot.
• Выражения анализа данных (DAX) в PowerPivot

Power Pivot: мощные средства анализа и моделирования данных в Excel

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

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

Основные возможности Power Pivot для Excel

• Импорт миллионов строк данных из нескольких источников данных С помощью Power Pivot для Excel можно импортировать миллионы строк данных из нескольких источников данных в одну книгу Excel, создавать связи между разнородными данными, создавать вычисляемые столбцы и меры с помощью формул, создавать сводные таблицы и сводные диаграммы, а затем дополнительно анализировать данные, чтобы принимать своевременные бизнес-решения без необходимости в ИТ-помощи.
• Быстрые вычисления и анализ Обработайте миллионы строк примерно за то же время, что и тысячи, и максимально эффективно использовать многоядерные процессоры и гигабайты памяти для максимально быстрой обработки вычислений. Преодолевает существующие ограничения для массового анализа данных на рабочем столе с помощью эффективных алгоритмов сжатия, чтобы загружать в память даже самые большие наборы данных.
• Практически неограниченная поддержка источников данных Предоставляет основу для импорта и объединения исходных данных из любого расположения для массового анализа данных на рабочем столе, включая реляционные базы данных, многомерные источники, облачные службы, веб-каналы данных, файлы Excel, текстовые файлы и данные из Интернета.
• Панель мониторинга управления безопасностью и управлением Power Pivot позволяет ИТ-администраторам отслеживать общие приложения и управлять ими, чтобы обеспечить безопасность, высокий уровень доступности и производительность.
• Выражения анализа данных (DAX) DAX — это язык формул, который расширяет возможности управления данными в Excel, обеспечивая более сложные и сложные группировки, вычисления и анализа. Синтаксис формул DAX очень похож на синтаксис формул Excel.

Задачи в Power Pivot или Excel

Основное различие между Power Pivot и Excel заключается в том, что в окне Power Pivot можно создавать более сложные модели данных. Вот некоторые задачи для сравнения.

Импорт данных из различных источников, таких как большие корпоративные базы данных, публичные ленты новостей, электронные таблицы и текстовые файлы на локальном компьютере.

Импорт всех данных из источника данных.

Фильтрация данных и переименование столбцов и таблиц при импорте.

Таблицы могут находиться на любом листе книги. Листы могут содержать несколько таблиц.

Таблицы организованы в виде отдельных страниц с вкладками в окне Power Pivot.

Редактирование данных в таблице

Можно изменять значения в отдельных ячейках таблицы.

Нельзя изменять отдельные ячейки.

Создание связей между таблицами

В диалоговом окне «Связи».

В представлении диаграммы или диалоговом окне «Создание связей».

Расширенные формулы на языке выражений анализа данных (DAX).

Иерархии можно создавать и использовать везде в книге, в том числе в Power View.

Создание ключевых показателей эффективности

Создание ключевых показателей эффективности для использования в сводных таблицах и отчетах Power View.

Создаваемые перспективы позволяют ограничить число столбцов и таблиц, которые видны пользователям книги.

Создание сводных таблиц и сводных диаграмм

Нажмите кнопку PivotTable в окне Power Pivot.

Расширение модели для Power View

Создается базовая модель данных.

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

Использование Visual Basic для Applications (VBA)

VBA не поддерживается в окне Power Pivot.

Используйте DAX в вычисляемых столбцах и вычисляемых полях.

Способ хранения данных

Данные, с которыми вы работаете в Excel и в окне Power Pivot, хранятся в аналитической базе данных в книге Excel, а мощный локальный модуль загружает, запрашивает и обновляет данные в этой базе данных. Так как данные хранятся в Excel, они сразу же доступны для сводных таблиц, сводных диаграмм, Power View и других функций Excel, которые используются для агрегирования данных и взаимодействия с ними. Все представление данных и интерактивность обеспечиваются Excel; и данные и объекты презентации Excel содержатся в одном файле книги. Power Pivot поддерживает файлы размером до 2 ГБ и позволяет работать с 4 ГБ данных в памяти.

Сохранение в SharePoint

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

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

Получение справочной информации

Все сведения о Power Pivot см. в справке по PowerPivot.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Меры в Power Pivot

Меры, также называемые мерами в Power Pivot в Excel 2013, являются вычислениями, используемыми при анализе данных. К примерам, часто встречающимся в бизнес-отчетах, относятся суммы, средние, минимальные и максимальные значения, счетчики или более сложные вычисления, создаваемые с использованием формулы выражений анализа данных (DAX).

В сводной таблице, сводной диаграмме или отчете мера помещается в область «Значения», в которой метки строки и столбца, окружающие ее, определяют контекст значения. Например, при измерении продаж по годам (в столбцах) и регионам (в строках) значение меры вычисляется в зависимости от данного года и региона. Значение меры всегда изменяется в ответ на выбор строк, столбцов и фильтров, обеспечивая нерегламентированный просмотр данных.

Хотя меры и вычисляемые столбцы похожи тем, что основаны на формуле, они отличаются в использовании. Меры чаще всего используются в области Значения сводной таблицы или сводной диаграммы. Вычисляемые столбцы применяются, когда необходимо разместить результаты вычислений в другой области сводной таблицы (например, в строке или столбце сводной таблицы или на оси сводной диаграммы). Очень важно понимать, когда следует использовать меры, а когда — вычисляемые столбцы. Дополнительные сведения см. в статьях Вычисления в Power Pivot и Вычисляются столбцы в Power Pivot.

Общие сведения о мерах

Меры бывают явными и неявными. Это влияет на способы их использования в сводной таблице или сводной диаграмме и в других приложениях, использующих в качестве источника данных модель данных Power Pivot.

Неявная мера создается приложением Excel при перетаскивании такого поля, как Сумма продаж, в область Значения списка полей сводной таблицы. Так как неявные меры создаются Excel, вы можете не знать о том, что создана новая мера. Однако при внимательном изучении списка «Значения» можно заметить, что поле Сумма продаж в действительности является мерой с именем Сумма «Сумма продаж», отображаемой с этим именем в области «Значения» списка полей сводной таблицы, а также в самой сводной таблице.

Неявная мера, созданная в сводной таблице

Неявные меры могут использовать только стандартные агрегатные функции (SUM, COUNT, MIN, MAX, DISTINCTCOUNT или AVG) и формат данных, заданный для этого агрегирования. Кроме того, неявные меры могут использоваться только в сводной таблице или диаграмме, для которой они были созданы.

Неявная мера тесно связана с полем, на котором она основана, что влияет впоследствии на способ удаления или изменения меры.

Явное вычисляемое поле

Явная мера создается пользователем при вводе или выборе формулы в ячейке «Область вычисления» или использовании в окне Power Pivot функции «Автосуммирование». Большинство создаваемых мер будут явными.

Явная мера, созданная в области вычислений Power Pivot

Явные меры можно использовать в любой сводной таблице или сводной диаграмме, в книге и отчетах Power View. Более того, их можно превратить в ключевые показатели эффективности или отформатировать, используя одну из множества строк форматирования, доступных для числовых данных. Команды контекстного меню для функций Создать ключевой показатель эффективности и Формат доступны только при использовании явной меры.

Примечание: После использования меры в качестве KPI ее нельзя использовать для других вычислений. Если вы хотите использовать формулу в вычислениях, необходимо скопировать ее. Дополнительные сведения о ключевых показателях эффективности см. в статье Ключевые показатели эффективности в Power Pivot.

Пример

Менеджер по продажам компании Adventure Works получил задание предоставить прогнозы товарооборота торговых посредников на следующий финансовый год. Он принимает решение базировать свои оценки на объемах продаж предыдущего года, в котором ежегодный прирост достиг 6 % благодаря различным рекламным акциям, проводимым в течение следующих шести месяцев.

Чтобы получить эти данные оценки, он импортирует прошлогодние данные по торговым посредникам и добавляет их в сводную таблицу. Он нашел поле Сумма продаж в таблице «Товарооборот торговых посредников» и перетащил его в область «Значения» списка полей сводной таблицы. Это поле отображается в сводной таблице в виде единичного значения, представляющего суммарный товарооборот торговых посредников начиная с прошлого года. Менеджер обращает внимание, что, даже если он не указал поле, оно было автоматически предоставлено и переименовано в списке полей сводной таблицы в Сумма Сумма продаж. Встроенное агрегатное выражение, добавленное Excel, =SUM(‘FactResellerSales'[SalesAmount]) , выполняет это вычисление. Менеджер переименовал неявную меру Продажи за прошлый год.

Следующий расчет — это прогноз продаж на следующий год, который будет основан на продажах за прошлый год, умноженных на 1,06 с учетом ожидаемого 6-процентного увеличения продаж через торговых посредников. Для этого вычисления необходимо явно создать меру с помощью кнопки Создать вычисляемого поля, чтобы создать вычисление с именем «Прогнозируемые продажи». Она заполнит следующую формулу: =SUM(‘FactResellerSales'[SalesAmount])*1.06 .

Новая мера добавляется в область «Значения» списка полей сводной таблицы. Она также добавляется в таблицу, активную на настоящий момент в списке полей сводной таблицы. В таблице указывается местонахождение меры в книге. Так как менеджер предпочитает, чтобы мера находилась в другой таблице, он отредактировал ее, изменив ее взаимосвязь с таблицей.

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

Именование мер

После создания мер можно изменить их порядок и переименовать их. Тем не менее существуют некоторые ограничения на изменение мер.

• Меры отображаются в списке полей сводной таблицы (если они не скрыты) вместе с другими объектами. Рекомендуется именовать их таким образом, чтобы они легко ассоциировались с выполняемым действием.
• Имя каждой меры должно быть уникальным в пределах таблицы.
• Избегайте использования имен, ранее использовавшихся для вычисляемых столбцов внутри той же книги. Меры и вычисляемые столбцы могут иметь одинаковые имена, но если они не будут уникальными, то возможно появление ошибок вычисления.
• При переименовании меры любые формулы, использующие ее в формуле, также должны быть обновлены. Обновление результатов формул происходит автоматически, если не включен режим ручного обновления. Однако эта операция может занять некоторое время.
• Так как имя является частью формулы меры, в нем нельзя использовать некоторые символы. Дополнительные сведения см. в подзадаче «Требования к именоимингу» в синтаксис DAX.

Совет: Вы можете объединить меры из нескольких таблиц в одну, создав пустую таблицу, а затем переместив в нее существующие меры или создав новые. Учтите, что при обращении к столбцам в других таблицах может потребоваться включить имена этих таблиц в формулы DAX.

Задачи

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