Power pivot excel как пользоваться

Запуск надстройки 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, а затем нажмите Удалить;

закройте редактор реестра;

включите надстройку, выполнив действия, описанные в начале этой статьи.

Источник

Узнайте, как использовать Power Query и Power Pivot в Excel

Быстрое получение аналитики

Благодаря Power Query (преобразованию Get & в Excel) и Power Pivot в Excel вы будете тратить меньше времени на управление данными и больше времени на влияние на производительность. Для ознакомления посмотрите это 2-минутное видео.

Попробуйте сами

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

Вопросы и ответы

С Приложения Microsoft 365 для предприятий приложение Excel на Windows для компьютеров предлагает полные функции Power Query и Power Pivot, которые улучшают возможности аналитики и моделирования, используют расширенные возможности подключения к данным и эффективно совместно используют данные в Организации. Дополнительные сведения о доступности Power Query и Power Pivot см. в приведенных ниже сведениях и узнайте у ИТ-отдела, поддерживает ли ваш текущий план Office эти функции.

Основные функции бизнес-аналитики

Базовые функции аналитики доступны в любой Microsoft 365 подписке или Office 2016. К таким функциям относятся сводные таблицы, срезы, диаграммы и возможности модели данных. С Power Query вы можете импортировать, формировать и объединять данные из файлов, баз данных и веб-сайтов. Поддержка базовой модели данных позволяет загружать данные в модель данных, автоматически обнаруживать связи, добавлять группирование времени и создавать меры DAX.

Дополнительные сведения о получении наилучшей производительности см. в разделе «Выбор между 64-разрядной или 32-разрядной версией Office.

Расширенные функции бизнес-аналитики

Функции расширенной аналитики доступны в Приложения Microsoft 365 для предприятий, Office 2019 Professional, Office 2019 Professional Plus и Excel 2019. Помимо базовых функций аналитики, вы можете использовать Power Query для импорта, формирования и объединения данных из корпоративных, больших данных и облачных источников данных, а также для совместного использования запросов. Поддержка расширенных моделей данных в Power Pivot включает специальный редактор моделирования данных, представления данных, вычисляемые столбцы DAX, ключевые показатели эффективности и иерархии.

Дополнительные сведения см. Power Query источникахданных в Excel версиях и где находится Power Pivot?

Excel 2016 для Mac и Excel 2019 для Mac имеют множество основных функций анализа, перечисленных выше: сводные таблицы, срезы, диаграммы и основные возможности импорта. У них еще нет Power Query Power Pivot, но мы постоянно улучшаем Excel mac. Excel для Microsoft 365 для Mac предлагает ограниченную поддержку Power Query.

Дополнительные сведения см. в Power Query в Excel для Mac.

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

Подключение Импортируйте данные и установите подключения к данным в облаке, в службе или локально.

Преобразовать Формирование данных в соответствии со своими потребностями, в то время как исходный источник остается неизменным.

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

Нагрузки Выполните запрос и сохраните его на листе или в модели данных.

Дополнительные сведения см. в Power Query Excel Power Query справке Excel справке.

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

Power BI — это набор инструментов бизнес-аналитики для предоставления подробных сведений обо всей вашей организации. С помощью Power BI Desktop вы можете подключаться к сотням источников данных, упрощать подготовку данных и выполнять специальный анализ. Вы также можете создавать отчеты и публиковать их в организации для использования в Интернете и на мобильных устройствах. Все пользователи могут создавать персонализированные информационные панели с использованием всестороннего представления организации. Имеются встроенные средства управления и обеспечения безопасности.

Дополнительные сведения см. в Power BI.

Excel 2016 и Power BI Desktop предоставляют уникальный для отрасли набор инструментов. Вместе они позволяют бизнес-аналитикам легко собирать, формировать, анализировать и наглядно изучать данные. Power BI — это набор инструментов бизнес-аналитики для предоставления подробных сведений обо всей вашей организации. Он помогает всем сотрудникам организации объединять данные из различных источников: книг Excel, локальных корпоративных данных, служб Azure и других популярных облачных решений, таких как Salesforce.com или Marketo, чтобы просматривать и совместно использовать информационные панели и интерактивные отчеты.

Дополнительные сведения см. в разделе «Анализ Excel для Power BI».

Для Microsoft 365 подписчиков и Excel 2019 добавлены улучшения функций и функций, которые вы уже использовали.

Дополнительные сведения см. в следующих статьях:

Источник

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 Pivot

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

Источник

Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel

Термины «Power Query», «Power Pivot», «Power BI» и прочие «пауэры» все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel.

Давайте проясним ситуацию.

Power Query

Power Query встречается в двух вариантах: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft и как часть Excel 2016. В первом случае после установки в Excel появляется отдельная вкладка:

В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Data) в виде группы Получить и преобразовать (Get & Transform) :

Возможности этих вариантов совершенно идентичны.

Основное окно Power Query обычно выглядит примерно так:

Power Pivot

Общие принципы работы в Power Pivot следующие:

Главное окно Power Pivot выглядит примерно так:

А так выглядит Модель Данных, т.е. все загруженные таблицы с созданными связями:

У Power Pivot есть ряд особенностей, делающих её уникальным инструментом для некоторых задач:

К сожалению, пока что Power Pivot входит не во все версии Excel. Если у вас Excel 2010, то скачать её можно бесплатно с сайта Microsoft. А вот если у вас Excel 2013-2016, то всё зависит от вашей лицензии, т.к. в некоторых вариантах она включена (Office Pro Plus, например), а в некоторых нет (Office 365 Home, Office 365 Personal и т.д.) Подробнее об этом можно почитать тут.

Power Maps

Эта надстройка впервые появилась в 2013 году и первоначально называлась GeoFlow. Она предназначена для визуализации гео-данных, т.е. числовой информации на географических картах. Исходные данные для отображения берутся все из той же Модели Данных Power Pivot (см. предыдущий пункт).

Ключевые особенности Power Map:

Power View

Выглядеть это будет примерно так:

Нюансы тут такие:

На сайте Microsoft, кстати, есть весьма приличный обучающий курс по Power View на русском языке.

Power BI

В Power BI Desktop можно:

Выглядит это примерно так:

На iPhone, например, созданный выше отчет выглядит так:

Источник

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

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

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

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

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

Практически неограниченная поддержка источников данных Служит основой для импорта и объединения исходных данных из любого места для масштабного анализа на компьютере, включая реляционные базы данных, многомерные источники, облачные службы, веб-каналы данных, Excel файлы, текстовые файлы и данные из Интернета.

Панель управленияPower Pivot безопасности и управления позволяет ИТ-администраторам отслеживать общие приложения и управлять ими для обеспечения безопасности, высокой доступности и производительности.

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

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

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

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

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

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

Прочитайте о том, как получать данные с помощью надстройки Power Pivot

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

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

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

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

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

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

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

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

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

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

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

Создавайте KPIS для использования в свиттах и отчетах 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 можно узнать в справке По Power Pivot.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Надстройка для 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 неявные меры, выберите на вкладке Главная → Показать скрытые.

Источник

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 поддерживает следующие источники данных –

Источник

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

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

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

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

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

Практически неограниченная поддержка источников данных Служит основой для импорта и объединения исходных данных из любого места для масштабного анализа на компьютере, включая реляционные базы данных, многомерные источники, облачные службы, веб-каналы данных, Excel файлы, текстовые файлы и данные из Интернета.

Панель управленияPower Pivot безопасности и управления позволяет ИТ-администраторам отслеживать общие приложения и управлять ими для обеспечения безопасности, высокой доступности и производительности.

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

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

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

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

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

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

Прочитайте о том, как получать данные с помощью надстройки Power Pivot

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

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

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

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

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

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

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

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

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

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

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

Создавайте KPIS для использования в свиттах и отчетах 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 можно узнать в справке По Power Pivot.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

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

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

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

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

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

Практически неограниченная поддержка источников данных Служит основой для импорта и объединения исходных данных из любого места для масштабного анализа на компьютере, включая реляционные базы данных, многомерные источники, облачные службы, веб-каналы данных, Excel файлы, текстовые файлы и данные из Интернета.

Панель управленияPower Pivot безопасности и управления позволяет ИТ-администраторам отслеживать общие приложения и управлять ими для обеспечения безопасности, высокой доступности и производительности.

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

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

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

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

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

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

Прочитайте о том, как получать данные с помощью надстройки Power Pivot

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

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

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

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

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

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

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

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

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

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

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

Создавайте KPIS для использования в свиттах и отчетах 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 можно узнать в справке По Power Pivot.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Power Pivot для Excel: что это такое и как его использовать

Добавьте таблицы поиска в свои наборы данных с помощью этого изобретательного дополнения

У вас есть данные и их много. Если вы хотите проанализировать все эти данные, узнайте, как использовать надстройку Power Pivot с Excel для импорта наборов данных, определения связей, создания сводных таблиц и создания сводных диаграмм.

Инструкции в этой статье применяются к Excel 2019, 2016, 2013 и Excel для Office 365.

Как получить надстройку Excel Power Pivot

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

Следуйте вместе с учебником

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

В этом руководстве используется образец рабочей книги Microsoft Student Data Model. В первой заметке на странице вы найдете ссылку для загрузки учебного пособия и заполненной модели данных.

Данные в этом образце книги Excel имеют следующее:

Есть другие примеры наборов данных на веб-сайте Microsoft. Изучите эти учебные ресурсы:

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

Как добавить данные в файл Excel и построить модель данных

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

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

Чтобы импортировать данные Excel в модель данных Power Pivot:

Откройте пустой лист и сохраните файл с уникальным именем.

В Excel 2013 выберите Power Query > Получить внешние данные и выберите источник данных.

Выберите таблицы, которые вы хотите импортировать.

При импорте двух или более таблиц Excel автоматически создает модель данных.

Окно Power Pivot отображает ваши данные в формате рабочего листа и состоит из трех основных областей: таблица данных, область расчета и вкладки таблицы данных.

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

Закройте окно Power Pivot.

Создание связей между таблицами с помощью Power Pivot Excel

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

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

Перетащите заголовок столбца из одной таблицы в другую или в таблицы, содержащие одинаковый заголовок столбца.

Продолжайте сопоставлять заголовки столбцов.

Как создавать сводные таблицы

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

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

Чтобы отсортировать данные сводной таблицы, перетащите поле в область «Фильтры». В этом примере поле «Имя класса» добавляется в область «Фильтры», поэтому список можно отфильтровать, чтобы показать среднюю оценку ученика для класса.

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

Преобразовать сводную таблицу в сводную диаграмму

Если вы хотите визуализировать данные сводной таблицы, превратите сводную таблицу в сводную диаграмму.

Создать сводные диаграммы

Если вы предпочитаете анализировать данные в визуальном формате, создайте сводную диаграмму.

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

Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и отсортируйте данные с помощью стрелок раскрывающегося списка заголовков столбцов.

Источник

План-факт анализ в сводной таблице с Power Pivot и Power Query

Постановка задачи

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

И «красивую» таблицу с плановыми помесячными показателями от руководства:

Задача: каким-то образом объединить обе таблицы в одну, чтобы наглядно отобразить выполнение плана по каждому товару, региону, месяцу, кварталу и т.д.

Необходимая оговорка

Можно, конечно, не напрягаться, и решать это дело привычным образом «в лоб». Т.е. с помощью 144 функций СУММЕСЛИМН (SUMIFS) вычислять суммарные продажи по каждому месяцу, товару и городу, а потом с помощью еще 144 формул вручную считать процент выполнения плана.

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

И в нашем примере всего 3 города и 4 товара. А если будет больше?

Что мы будем делать

Думаю, никто не будет спорить, что самым удобным, гибким и мощным инструментом для анализа данных в Microsoft Excel являются сводные таблицы. Так что, в идеале, надо бы свести решение нашей задачи именно к ним.

Но как объединить в одной сводной две наших исходных таблицы? Плоскую таблицу продаж по дням и трехмерную таблицу плановых значений с детализацией по месяцам? Тут нам помогут 2 мастхэв надстройки для Excel:

Поехали, по шагам.

Шаг 1. Добавляем соединительные таблицы-справочники

Шаг 2. Превращаем все таблицы в «умные» и даём им имена

Для загрузки таблиц в Power Pivot они должны быть «умными» (динамическими). Для этого с каждой таблицей проделываем следующее:

Я назвал наши таблицы, соответственно:

Шаг 3. Грузим первые 4 таблицы в Power Pivot

Теперь по очереди для каждой из первых четырёх таблиц, установив в неё активную ячейку, жмём на кнопку Добавить в модель данных (Add to Data Model) :

В итоге все наши таблички должны загрузиться в открывшееся окно Power Pivot на отдельные вкладки:

Шаг 4. Доводим до ума таблицу План

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

Сначала загрузим таблицу с планами в редактор запросов Power Query, используя кнопку Из таблицы/диапазона (From Table/Range) на вкладке Данные (Data) или на вкладке Power Query (если у вас старая версия Excel 2010-2013 и вы установили Power Query как отдельную надстройку):

Затем в открывшемся окне Power Query делаем следующее:

После этого наша последняя таблица таблПлан должна загрузиться в окошко Power Pivot.

Шаг 5. Связываем таблицы

Связь делается очень просто: хватаем мышью столбец в одной из соединительных таблиц (таблТовары, таблГеография, таблКалендарь), тянем и бросаем на соответствующий столбец в таблицах таблПродажи и таблПлан2:

Шаг 6. Строим сводную

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

Главные принципы здесь такие:

Например, можно накидать так:

В итоге должно получиться что-то уже очень похожее на то, что нам требуется:

Шаг 7. Добавляем меры для вычислений

Сумма по полю Выручка := SUM( таблПродажи[Выручка] )

Повторяем процесс еще два раза:

Добавленные меры появятся в правой панели сводной таблицы с характерным значком:

Теперь их можно смело закидывать мышкой в нашу сводную и выполнять план-факт анализ в любых разрезах за считанные секунды:

Возможные проблемы и их решения

В процессе реализации вы можете нарваться на несколько типичных «граблей»:

Источник

План-факт анализ в сводной таблице с Power Pivot и Power Query

Постановка задачи

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

И «красивую» таблицу с плановыми помесячными показателями от руководства:

Задача: каким-то образом объединить обе таблицы в одну, чтобы наглядно отобразить выполнение плана по каждому товару, региону, месяцу, кварталу и т.д.

Необходимая оговорка

Можно, конечно, не напрягаться, и решать это дело привычным образом «в лоб». Т.е. с помощью 144 функций СУММЕСЛИМН (SUMIFS) вычислять суммарные продажи по каждому месяцу, товару и городу, а потом с помощью еще 144 формул вручную считать процент выполнения плана.

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

И в нашем примере всего 3 города и 4 товара. А если будет больше?

Что мы будем делать

Думаю, никто не будет спорить, что самым удобным, гибким и мощным инструментом для анализа данных в Microsoft Excel являются сводные таблицы. Так что, в идеале, надо бы свести решение нашей задачи именно к ним.

Но как объединить в одной сводной две наших исходных таблицы? Плоскую таблицу продаж по дням и трехмерную таблицу плановых значений с детализацией по месяцам? Тут нам помогут 2 мастхэв надстройки для Excel:

Поехали, по шагам.

Шаг 1. Добавляем соединительные таблицы-справочники

Шаг 2. Превращаем все таблицы в «умные» и даём им имена

Для загрузки таблиц в Power Pivot они должны быть «умными» (динамическими). Для этого с каждой таблицей проделываем следующее:

Я назвал наши таблицы, соответственно:

Шаг 3. Грузим первые 4 таблицы в Power Pivot

Теперь по очереди для каждой из первых четырёх таблиц, установив в неё активную ячейку, жмём на кнопку Добавить в модель данных (Add to Data Model) :

В итоге все наши таблички должны загрузиться в открывшееся окно Power Pivot на отдельные вкладки:

Шаг 4. Доводим до ума таблицу План

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

Сначала загрузим таблицу с планами в редактор запросов Power Query, используя кнопку Из таблицы/диапазона (From Table/Range) на вкладке Данные (Data) или на вкладке Power Query (если у вас старая версия Excel 2010-2013 и вы установили Power Query как отдельную надстройку):

Затем в открывшемся окне Power Query делаем следующее:

После этого наша последняя таблица таблПлан должна загрузиться в окошко Power Pivot.

Шаг 5. Связываем таблицы

Связь делается очень просто: хватаем мышью столбец в одной из соединительных таблиц (таблТовары, таблГеография, таблКалендарь), тянем и бросаем на соответствующий столбец в таблицах таблПродажи и таблПлан2:

Шаг 6. Строим сводную

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

Главные принципы здесь такие:

Например, можно накидать так:

В итоге должно получиться что-то уже очень похожее на то, что нам требуется:

Шаг 7. Добавляем меры для вычислений

Сумма по полю Выручка := SUM( таблПродажи[Выручка] )

Повторяем процесс еще два раза:

Добавленные меры появятся в правой панели сводной таблицы с характерным значком:

Теперь их можно смело закидывать мышкой в нашу сводную и выполнять план-факт анализ в любых разрезах за считанные секунды:

Возможные проблемы и их решения

В процессе реализации вы можете нарваться на несколько типичных «граблей»:

Источник

Создание модели данных с эффективным использованием памяти с Excel и надстройки Power Pivot

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

Примечание: В этой статье описаны модели данных Excel 2013. Тем не менее те же функции моделирования данных и Power Pivot, Excel 2013, также относятся к Excel 2016. Между этими версиями Excel фактически нет Excel.

Хотя вы можете легко создавать большие модели данных в Excel, есть несколько причин не делать этого. Во-первых, большие модели, содержащие большое количество таблиц и столбцов, являются неавтоматиченными для большинства анализа и являются утомительным списком полей. Во-вторых, большие модели используют ценную память, что отрицательно влияет на другие приложения и отчеты, в которые используются те же ресурсы системы. Наконец, Microsoft 365, как SharePoint Online, так и Excel Web App, ограничивают размер файла Excel 10 МБ. Для моделей данных книги, содержащих миллионы строк, достаточно быстро вы получите ограничение в 10 МБ. См. спецификацию и ограничения модели данных.

Из этой статьи вы узнаете, как создать тщательно продуманную модель, которая упрощает работу с данными и требует мало памяти. Время, необходимое для того, чтобы изучить методики эффективного проектирования модели, окупится при создании и использовании любой модели независимо от того, просматриваете ли вы ее в Excel 2013, Microsoft 365 SharePoint Online, на сервере Office Web Apps Server или в SharePoint 2013.

Также рекомендуем запустить средство Workbook Size Optimizer. Оно анализирует ваши книги Excel и при возможности дополнительно сжимает их. Скачайте оптимизатор размера книги.

В этой статье

Степень сжатия и аналитический обработчик в памяти

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

Вы можете получить модель данных, которая в среднем в 7–10 раз меньше исходной. Например, при импорте данных размером 7 МБ из базы данных SQL Server размер модели данных в Excel может составлять 1 МБ или меньше. Степень сжатия зависит главным образом от количества уникальных значений в каждом столбце. Чем больше уникальных значений, тем больше памяти понадобится для их хранения.

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

Примечание: Объем памяти, необходимый для хранения отдельных столбцов, может существенно отличаться. Иногда лучше иметь несколько столбцов с небольшим количеством уникальных значений, чем один столбец, в котором их много. Данный способ подробно описан в разделе об оптимизации столбцов даты и времени.

Сокращение количества столбцов для эффективного использования памяти

Меньше всего памяти занимает столбец, который вы не импортировали. Чтобы построить эффективную модель, определите значимость каждого столбца для необходимого вам анализа. Если столбец не играет большой роли, не импортируйте его. В случае необходимости вы всегда сможете добавить столбцы в дальнейшем.

Два примера столбцов, которые не нужно импортировать

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

Второй пример касается исключения столбца первичного ключа при импорте фактической таблицы.

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

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

Примечание: В хранилищах и многомерных базах данных большие таблицы, содержащие преимущественно числовые данные, часто называют таблицами фактов. Как правило, таблицы фактов содержат данные о производительности предприятия или финансовых операциях (например, точки данных о продажах и затратах, которые группируются по подразделениям, продуктам, рыночным сегментам, географическим регионам и т. д.). Для поддержки анализа данных в модель следует включать все столбцы таблицы фактов, которые содержат бизнес-данные или могут использоваться для создания перекрестных ссылок на данные других таблиц. Исключается лишь столбец первичного ключа, который содержит уникальные значения, существующие только в данной таблице фактов. Из-за большого размера таблиц фактов исключение из них строк или столбцов способствует созданию эффективной модели данных.

Исключение ненужных столбцов

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

Запустив его, вы сможете выбирать таблицы для импорта.

Нажав кнопку «Просмотр и фильтр», можно выбрать необходимые элементы для каждой таблицы. Мы рекомендуем сначала снять флажки со всех столбцов, а затем установить их рядом с теми, которые необходимы для анализа.

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

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

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

Можно ли уменьшить объем памяти, необходимый для хранения столбца?

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

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

В большинстве случаев столбцы даты и времени занимают много места. К счастью, существует несколько способов уменьшения объема памяти, необходимого для хранения этого типа данных. Выбор способа зависит от того, как используется столбец, и удобства построения SQL-запросов.

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

нужны ли вам данные времени;

Нужна ли часть времени на уровне часов? Минут? Секунд? Миллисекунд?

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

Ответы на эти вопросы определят способ изменения столбца даты и времени.

Все эти способы требуют изменения SQL-запроса. Чтобы упростить эту задачу, следует исключить как минимум один столбец из каждой таблицы. Таким образом, конструкция запроса изменится с сокращенного формата (SELECT *) на оператор SELECT с полными именами столбцов, которые гораздо легче изменить.

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

В окне «Свойства таблицы» выберите Редактор запросов.

В редакторе запросов отображается SQL-запрос, используемый для заполнения таблицы. Если вы исключили один из столбцов во время импорта, запрос содержит полные имена столбцов:

Если вы полностью импортировали таблицу, не снимая флажок с какого-либо столбца и не применяя фильтр, запрос будет отображаться в формате «Select * from «, который намного сложнее изменить:

Изменение SQL-запроса

Найдя запрос, вы можете его изменить, чтобы еще уменьшить размер модели.

Если столбец содержит денежные или десятичные данные, используйте такой синтаксис, чтобы исключить ненужные десятичные значения:

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

При наличии столбца даты и времени «dbo.Bigtable.[Date Time]», в котором вам не нужна часть данных времени, используйте такой синтаксис для ее исключения:

«SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) «

При наличии столбца даты и времени «dbo.Bigtable.[Date Time]», в котором вам не нужны части данных даты и времени, вместо данного столбца используйте в SQL-запросе несколько столбцов:

«SELECT CAST (dbo.Bigtable.[Date Time] as date) AS [Date Time],

datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]»

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

Если вы хотите, чтобы часы и минуты отображались в одном столбце времени, используйте такой синтаксис:

Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

Если у вас есть два столбца даты и времени, например [Start Time] и [End Time], и вам нужно получить разницу между ними в секундах в столбце [Duration], удалите оба столбца из списка, добавив следующее:

«datediff(ss,[Start Date],[End Date]) as [Duration]»

Используя ключевое слово «ms» вместо «ss», вы получите разницу в миллисекундах.

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

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

Один из способов эффективного использования памяти предполагает замену обычных или вычисляемых столбцов вычисляемыми мерами. Классическим примером является наличие столбцов «Цена за единицу», «Количество» и «Всего». Имея три столбца, можно освободить дополнительное место, оставив два из них и вычислив третий с помощью DAX.

Каких 2 столбца нужно оставить?

В приведенном выше примере необходимо оставить столбцы «Количество» и «Цена за единицу». Они содержат меньше значений, чем столбец «Всего». Чтобы вычислить столбец «Всего», добавьте следующую вычисляемую меру:

«TotalSales:=sumx(‘Sales Table’,’Sales Table’[Unit Price]*’Sales Table’[Quantity])»

Вычисляются столбцы, как обычные столбцы, которые обеим сторонам требуется место в модели. С другой стороны, вычисляемая мера вычисляется «на лету» и не отбирает место.

Заключение

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

удаление столбцов — лучший способ экономии места. Оставляйте только действительно нужные столбцы;

в некоторых случаях можно удалить столбец из таблицы, заменив его вычисляемой мерой;

Возможно, вам не нужны все строки в таблице. Вы можете отфильтровать строки в мастере импорта таблиц.

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

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

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

Теперь, когда вы сделали все возможное, чтобы уменьшить размер книги, также рекомендуем запустить средство Workbook Size Optimizer. Оно анализирует ваши книги Excel и при возможности дополнительно сжимает их. Скачайте оптимизатор размера книги.

Источник

Учебник. Расширение связей модели данных с использованием Excel, Power Pivot и DAX

Аннотация. Это второй учебник из серии. В первом учебнике «Импортданных в модель данных и создание модели данных» была создана книга Excel с данными, импортируемыми из нескольких источников.

Примечание: В этой статье описаны модели данных в Excel 2013. Тем не менее те же функции моделирования данных и Power Pivot, которые впервые представлены в Excel 2013, также относятся к Excel 2016.

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

Ниже перечислены разделы этого учебника.

В конце учебника есть тест, с помощью которого можно проверить свои знания.

В этой серии учебников используются данные об олимпийских медалях и спортивных состязаниях, а также странах, принимавших Олимпийские игры. Учебники этой серии:

Расширение связей модели данных с помощью Excel, Power Pivot и DAX

Рекомендуется изучить их по порядку.

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

Добавление связи с помощью представления диаграммы в Power Pivot

В этом разделе вы научитесь расширять модель с помощью надстройки Microsoft Office Power Pivot в Excel 2013. Представление диаграммы в Microsoft SQL Server PowerPivot для Excel упрощает создание отношений. Прежде всего вам нужно убедиться, что надстройка Power Pivot включена.

Примечание. Power Pivot в Microsoft Excel 2013 входит в состав Office профессиональный плюс. Дополнительные сведения см. в надстройка «Запуск Power Pivot в Microsoft Excel 2013».

Добавление Power Pivot на ленту Excel путем включения Power Pivot надстройки

Если надстройка Power Pivot включена, в Excel 2013 отображается вкладка ленты под названием POWER PIVOT. Чтобы включить Power Pivot, выполните следующие действия:

Выберите ФАЙЛ > Параметры > Надстройки.

В поле Управление внизу выберите Надстройки COM> Перейти.

Установите флажок Microsoft Office Power Pivot в Microsoft Excel 2013, а затем нажмите кнопку ОК.

На ленте Excel появится вкладка POWER PIVOT.

Добавление связи с помощью представления диаграммы в Power Pivot

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

В Excel щелкните ярлычок Hosts, чтобы сделать этот лист активным.

На ленте выберите POWER PIVOT > Таблицы > Добавить в модель данных. Этот шаг добавляет таблицу Hosts в модель данных. Также откроется надстройка Power Pivot, которую можно использовать для выполнения оставшихся шагов задачи.

Обратите внимание, Power Pivot показывает все таблицы в модели, включая таблицы «Hosts». Просмотрите несколько таблиц. В Power Pivot вы можете просмотреть все данные, которые содержит модель, даже если они не отображаются ни на каких листах Excel, например данные о дисциплинах, соревнованиях и медалях ниже, а также данных S_Teams, W_Teams и sports.

В окне Power Pivot в группе Просмотр выберите команду Представление диаграммы.

С помощью полосы прокрутки измените размер диаграммы таким образом, чтобы видеть все объекты в диаграмме. Перетаскружая заголовок таблицы, можно изменить их положение, чтобы они были видны и расположились рядом друг с другом. Обратите внимание, что четыре таблицы не связаны с остальными таблицами: Hosts, Events, W _Teamsи S _Teams.

Вы заметите, что как в таблице Medals, так и в таблице Events есть поле под названием DisciplineEvent. При дальнейшем изучении вы определяете, что поле DisciplineEvent в таблице Events состоит из уникальных, неповторяющихся значений.

Примечание: Поле DisciplineEvent представляет уникальное сочетание каждой дисциплины и соревнования. С другой стороны, в таблице Medals поле DisciplineEvent повторяется много раз. Это понятно, потому что для каждого сочетания дисциплины и соревнования есть три медали (золото, серебро, бронза), которые присуждаются на каждых Олимпийских играх. Поэтому между этими таблицами существует отношение «один ко многим» (одна уникальная запись, включающая дисциплину и соревнование, в таблице Disciplines, и несколько записей для каждого значения «дисциплина + соревнование»).

Создайте связь между таблицами Medals и Events. В представлении диаграммы перетащите поле DisciplineEvent из таблицы Events на поле DisciplineEvent таблицы Medals. Между ними появится линия, показывающая, что связь была создана.

Щелкните линию, которая соединяет Events и Medals. Выделенные поля определяют связь, как показано на следующем экране.

Чтобы связать Hosts с моделью данных, нам требуется поле со значениями, уникально определяющими каждую строку в таблице Hosts. Затем мы можем выполнить поиск в модели данных, чтобы узнать, есть ли такие же данные в другой таблице. Представление диаграммы не позволяет нам это сделать. Выбрав Hosts, вернитесь в представление данных.

Изучив столбцы, мы понимаем, что в таблице Hosts нет столбца уникальных значений. Мы должны создать его с помощью вычисляемого столбца и выражений анализа данных (DAX).

Приятно, когда в модели данных есть все поля, необходимые для создания отношений, и данные объединения, требуемые для визуализации с помощью Power View или сводных таблиц. Но это не всегда так, поэтому в следующем разделе описывается, как с помощью DAX создать новый столбец, который можно использовать для создания связи между таблицами.

Расширение модели данных с использованием вычисляемых столбцов

Чтобы создать связь между таблицей Hosts и моделью данных и тем самым расширить модель данных, включив в нее таблицу Hosts, в таблице Hosts должно быть поле, которое однозначно идентифицирует каждую строку. Кроме того, это поле должно соответствовать полю в модели данных. Такие соответствующие поля (по одному в каждой таблице) являются основой для связи таблиц.

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

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

В Hosts мы можем создать уникальный вычисляемый столбец, объединив поля Edition (год проведения Олимпийских игр) и Season (лето или зима). В таблице Medals также есть поля Edition и Season, так что если мы создадим в каждой из этих таблиц вычисляемый столбец, которые объединяет поля Edition и Season, мы сможем установить связь между Hosts и Medals. На следующем экране показана таблица Hosts с выбранными полями Edition и Season

Создание вычисляемых столбцов с помощью DAX

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

Чтобы убедиться, что выбрано представление данных, а не представление схемы, на вкладке Power Pivot выберите пункты В начало > Просмотр > Представление данных.

Выберите таблицу Hosts в Power Pivot. Рядом с существующими столбцами есть пустой столбец под названием Добавить столбец. Power Pivot предоставляет этот столбец в качестве заполнителя. В Power Pivot есть много способов добавления нового столбца в таблицу, один из которых — выбрать пустой столбец с названием Добавить столбец.

Введите указанную ниже формулу DAX в строке формул. Функция CONCATENATE объединяет несколько полей в одно. По мере ввода функция автозаполнения поможет ввести полные имена столбцов и таблиц и представит доступные функции. Используйте клавишу TAB для выбора предложений автозаполнения. Вы также можете просто щелкнуть столбец при вводе формулы, и Power Pivot вставит его имя в формулу.

Когда вы закончите вводить формулу, нажмите клавишу ВВОД, чтобы подтвердить ее.

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

Переименуем вычисляемый столбец в EditionID. Любой столбец можно переименовать, дважды щелкнув его или щелкнув его правой кнопкой мыши и выбрав команду Переименовать столбец. После завершения таблица Hosts в PowerPoint будет выглядеть так, как на следующем экране.

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

Для начала создайте новый столбец в таблице Medals (так же, как в таблице Hosts). В Power Pivot выберите таблицу Medals и щелкните Конструктор > Столбцы > Добавить. Обратите внимание, что установлен флажок Добавить столбец. Это дает тот же результат, что и выбор столбца Добавить столбец.

Формат столбца Edition в Medals отличается от формата Edition в таблице Hosts. Прежде чем объединять (сцеплять) столбцы Edition и Season для создания столбца EditionID, нам нужно создать промежуточное поле, которое возвращает данные Edition в правильном формате. В строке формул над таблицей введите следующую формулу DAX.

Когда вы закончите вводить формулу, нажмите клавишу ВВОД. Все строки вычисляемого столбца будут заполнены значениями на основе введенной формулы. Если вы сравните этот столбец со столбцом Edition в таблице Hosts, вы увидите, что они имеют одинаковый формат.

Переименуйте столбец, щелкнув правой кнопкой мыши CalculatedColumn1 и выбрав Переименовать столбец. Введите Year и нажмите клавишу ВВОД.

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

Переименуйте столбец: дважды щелкните CalculatedColumn1 и введите EditionID.

Отсортируйте столбец в порядке возрастания. Таблица Medals в Power Pivot теперь выглядит так, как на следующем экране.

Обратите внимание, что многие значения в поле EditionID таблицы Medals повторяются. Это и ожидалось, так как во время каждых Олимпийских игр (которые теперь представлены значением EditionID) было вручено много медалей. Уникальной в таблице Medals является каждая медаль. Уникальным идентификатором для каждой записи в таблице Medals и ее первичным ключом является поле MedalKey.

Следующим шагом является создание связи между таблицами Hosts и Medals.

Создание связи с помощью вычисляемых столбцов

Теперь мы используем созданные вычисляемые столбцы для установки связи между таблицами Hosts и Medals.

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

Разверните таблицу Hosts, чтобы можно было просмотреть все ее поля. Мы создали столбец EditionID, который является первичным ключом (уникальным, не повторяющимся полем) таблицы Hosts, и столбец EditionID в таблице Medals, чтобы установить связь между ними. Нам нужно найти их и создать связь. На ленте Power Pivot есть функция Найти, которая позволяет искать нужные поля в модели данных. На следующем экране показано окно Поиск метаданных, в поле Найти которого указано «EditionID».

Расположите таблицу Hosts рядом с таблицей Medals.

Перетащите столбец EditionID из таблицы Medals на столбец EditionID в таблице Hosts. Power Pivot создаст между таблицами связь, основанную на столбце EditionID, и нарисует между двумя столбцами линию, которая показывает ее.

В этом разделе вы научились новому способу добавления столбцов, создали вычисляемый столбец с помощью DAX и использовали его для создания связи между таблицами. Теперь таблица Hosts интегрирована в модель данных, а ее данные доступны для сводной таблицы на листе Лист1. Вы также можете использовать связанные данные для создания дополнительных сводных таблиц, сводных диаграмм, отчетов Power View и т. д.

Создание иерархии

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

Данные Olympics также являются иерархическими. Полезно разобраться в иерархии Олимпийских игр с точки зрения видов спорта, дисциплин и соревнований. У каждого вида спорта есть одна или несколько дисциплин (иногда их много). По каждой дисциплине проводится одно или несколько соревнований (опять же, таких соревнований бывает много). На следующем рисунке показана эта иерархия.

В этом разделе вы создадите две иерархии в данных Olympic, которые вы используете в этом учебнике. Затем на их примере мы покажем, как иерархии упрощают организацию данных в сводных таблицах и (в следующем учебнике) в Power View.

Создание иерархии Sport

В Power Pivot выберите Представление диаграммы. Разверните таблицу Events, чтобы было проще работать с ее полями.

Нажмите и удерживайте CTRL, а затем щелкните поля «Спорт», «Дисциплина» и «Соревнования». Выбрав эти три поля, щелкните правой кнопкой мыши и выберите «Создать иерархию». В нижней части таблицы будет создан родительский узел иерархии Hierarchy 1,а выбранные столбцы будут скопированы в иерархию в качестве потомков. Убедитесь, что Sport отображается сначала в иерархии, а затем Discipline, а затем Event.

Дважды щелкните заголовок Иерархия1 и введите SDE, чтобы переименовать новую иерархию. Теперь у вас есть иерархия, которая включает в себя виды спорта, дисциплины и соревнования. Таблица Events теперь выглядит так, как показано на следующем экране.

Создание иерархии Location

В представлении схемы в Power Pivot выберите таблицу Hosts и нажмите кнопку «Создать иерархию» в заголовке таблицы, как показано на следующем экране.

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

Введите в качестве имени новой иерархии Locations.

Есть много способов добавления столбцов в иерархию. Перетащите поля Season, City и NOC_CountryRegion на имя иерархии (в данном случае — Locations), пока имя иерархии не будет выделено, а затем отпустите, чтобы добавить их.

Щелкните правой кнопкой мыши EditionID и выберите пункт Добавить в иерархию. Выберите Locations.

Убедитесь, что дочерние узлы в иерархии расположены в нужном порядке. Порядок должен быть: Season, NOC, City, EditionID. Если дочерние узлы расположены не по порядку, просто перетащите их в нужное место в иерархии. Ваша таблица должна выглядеть так, как на следующем экране.

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

Использование иерархий в сводных таблицах

Теперь, когда у нас иерархии Sports и Locations, мы можем добавить их в сводные таблицы или Power View и быстро сгруппировать данные или получить другие полезные результаты. Перед созданием иерархий вы добавили отдельные поля в сводную таблицу и упорядочили их так, как вы хотите их просматривать.

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

Вернитесь назад в Excel.

На листе Лист1 удалите поля из области строк полей сводной таблицы, а затем удалите все поля из области столбцов. Убедитесь, что сводная таблица выбрана (сейчас она довольно небольшая, так что вы можете выбрать ячейку A1, чтобы сделать это). В сводной таблице остались только поля Medal в области фильтров и Count of Medal в области значений. Практически пустая сводная таблица должна быть похожа на изображенную на следующем экране.

В области полей сводной таблицы перетащите SDE из таблицы Events в область строк. Затем перетащите Locations из таблицы Hosts в область СТОЛБЦЫ. Просто путем перетаскивания этих двух иерархий можно заполнить сводную таблицу большим количеством данных, все из которых организованы в иерархии, созданные на предыдущих шагах. Ваша таблица должна выглядеть так, как на следующем экране.

Отфильтруем данные, чтобы просмотреть только первые десять строк соревнований. В сводной таблице щелкните стрелку в поле Метки строк, щелкните (Выбрать все), чтобы снять выделение, а затем установите флажки рядом с первыми десятью видами спорта. Сводная таблица теперь выглядит так, как показано на следующем экране.

Вы можете развернуть любой из этих видов спорта, которые являются верхним уровнем в иерархии SDE, и просмотреть информацию на следующем уровне (дисциплины). Если для этой дисциплины существует более низкий уровень в иерархии, то можно развернуть ее для просмотра соревнований. Вы можете сделать то же самое в иерархии Location, где верхним уровнем является Season, который выводится как Summer и Winter в сводной таблице. При развертывании вида спорта Aquatics выводятся все его дочерние элементы дисциплин и их данные. Если развернуть дисциплину Diving спорта Aquatics, появятся соревнования по ней, как показано на следующем экране. Мы можем сделать то же самое для дисциплины Water Polo и увидеть, что у нее только оно соревнование.

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

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

В области полей сводной таблицы удалите Locations из области столбцов. Затем удалите SDE из области строк. Мы вернулись к базовой сводной таблице.

Из таблицы Hosts перетащите Season, City, NOC_CountryRegion и EditionID в область столбцов и расположите их в указанном порядке сверху вниз.

Из таблицы Events перетащите Sport, Discipline и Event в область строк и расположите их в указанном порядке сверху вниз.

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

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

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

Контрольная точка и тест

Повторите изученный материал

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

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

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

Хотите проверить, насколько хорошо вы усвоили пройденный материал? Приступим. Этот тест посвящен функциям, возможностям и требованиям, о которых вы узнали в этом учебнике. Внизу страницы вы найдете ответы на вопросы. Удачи!

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

А. Связи между таблицами создаются в Power View.

Б. Связи между таблицами создаются в представлении конструирования в Power Pivot.

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

Г. Все вышеперечисленные.

Вопрос 2. ИСТИНА или ЛОЖЬ. Связи между таблицами можно устанавливать на основе уникального идентификатора, который создается с помощью формул DAX.

Вопрос 3. Где можно создать формулу DAX?

А. В области вычислений Power Pivot.

Б. В новом столбце в Power Pivot.

В. В любой ячейке Excel 2013.

Вопрос 4. Какое из приведенных утверждений об иерархиях является верным?

А. После создания иерархии поля, включенные в нее, больше не доступны по отдельности.

Б. После создания иерархии поля, включенные в нее, можно использовать в клиентских средствах, просто перетащив иерархию в Power View или область сводной таблицы.

В. После создания иерархии соответствующие данные в модели данных объединяются в одно поле.

Г. В Power Pivot нельзя создавать иерархии.

Ответы на вопросы теста

Правильный ответ: D

Правильный ответ: А

Правильный ответ: D

Правильный ответ: Б

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

Набор данных об Олимпийских играх © Guardian News & Media Ltd.

Изображения флагов из справочника CIA Factbook ( cia.gov ).

Данные о населении из документов Всемирного банка ( worldbank.org ).

Авторы эмблем олимпийских видов спорта Thadius856 и Parutakupiu.

Источник