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

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

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

Введение в PowerPivot надстройку

После установки на ленте появится новая вкладка PowerPivot.

Щелчок на Окно PowerPiwot запустит новую вкладку PowerPivot для Excel.

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

PowerPivot обладает встроенным алгоритмом обработки данных, который позволяет 1) хранить и обрабатывать бо льшие объемы данных, чем доступно в Excel, 2) импортировать данные с различных источников данных, 3) моделировать операции, такие как определение связей между таблицами и создание DAX формул.

Надстройка PowerPivot для Excel (см. выше) имеет Excel-ориентированный пользовательский интерфейс.

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

Excel документ является отличным способом упаковки данных и визуализации. Это означает, что все, что вы видите в окне надстройки хранится, внутри вашей книги. Что облегчает управление и обмен данными.

Импорт данных

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

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

Копировать/вставить

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

Работа с таблицами

После того, как данные были импортированы, они отображаются в окне PowerPivot для Excel.

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

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

Расчеты

Конечная цель PowerPivot для Excel сделать анализ данных простым. В отличие от продуктов анализа данных, которые предназначены для IT-специалистов (например, SQL Server Analysis Services), PowerPivot предназначен для людей, которые используют сводные таблицы в своей ежедневной работе. Идея заключается в том, чтобы пользователи могли применять имеющиеся навыки Excel, без необходимости изучения специализированных языков.

If(>100000000, “Отлично”, if(>10000000, “Хорошо”, “Плохо”))

Поэтому во время работы можно встретить знакомые формулы, такие как ABS, AVERAGE, AVERAGEA (думаю, со временем их тоже переведут на русский язык). Так же есть совершенно новые: ALL, ALLEXEPT, AVERAGEX.

Визуализация

Для того чтобы создавать многофункциональные , PowerPivot использует инструменты Excel для визуализации. К счастью у Excel этого добра достаточно: сводные таблицы, сводные диаграммы и срезы.

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

Итог

Итак, что мы имеем? PowerPivot для Excel – это надстройка, используемая для добавления и анализа больших объемов данных. В Excel книга предоставляет все возможности визуализации данных и взаимодействия с ними. Навыки составления отчетов с помощью сводных таблиц позволят создавать сложные книги, основывающиеся на сложных аналитических данных Excel.

[в связи со спорным переносом 1 части поста на geektimes (при том что 2-я часть осталась на хабре) возвращаю 1-ю часть на место]

Работая в сфере аналитики и мониторя различные инструменты BI рано или поздно наталкиваешься на обзор или упоминание надстройки Power Pivot Excel. В моем случае знакомство с ним произошло на конференции Microsoft Data Day.

Особых впечатлений после презентации инструмент не оставил: Да, бесплатен (в рамках лицензии Office), да - есть некий ETL функционал в части получения данных с разрозненных источников (БД,csv,xls, и т.д.), Join-ов этих источников и скармливания в оперативку записей на порядки выше 1 млн.строк в Excel. Короче, посмотрел и забыл.

А вспомнить пришлось, когда появилась необходимость идентификации определённых явлений в данных

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

Собственно, постановка задачи (на обезличенном примере) следующая:

В исходных данных csv файла:

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

Поиску и очистке данных штатными средствами office мешают следующие обстоятельства:

Детализация данных до строк накладной
Количество записей в несколько миллионов строк
Отсутствие sql инструментария (К примеру: Access - не в комплекте)

Конечно можно залить любую бесплатную СУБД (хоть десктоп версию, хоть серверную) но для этого во-первых нужны админские права, во-вторых статья была бы уже не про Power Pivot.

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

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

Шаг 1. Чем отличается вычисляемый столбец от вычисляемой меры?
Вот пример вычисляемого столбца для выделения НДС из поля отгрузки с НДС используя встроенные формулы DAX:

ROUND([Отгрузка с НДС]*POWER(1,18;-1)*0,18;2)

Как видно из примера вычисляемый столбец (Назовем его НДС) работает с каждой атомарной записью по горизонтали.
Теперь добавим вычисляемое поле для цены за штуку без НДС:

ROUND([Отгрузка с НДС]*POWER(1,18;-1)/[Отгрузка шт];2)

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

Средняя цена за штуку без НДС: =ROUND(AVERAGE([Поле_Цена за штуку без НДС]);2)

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

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

Обратите внимание, если вычисляемое поле НДС на каждом уровне данных (зеленая обводка на уровне торговой точки, города или итого по таблице) показывает сумму, что в принципе – корректно, то сумма цен вычисляемого поля «Цена за штуку без НДС» (красная обводка) вызывает вопросы.
А вот вычисляемая мера «Средняя цена за штуку без НДС» вполне имеет право на жизнь в рамках данного аналитического куба.

Отсюда делаем вывод, что вычисляемое поле «Цена за штуку без НДС» является вспомогательным инструментом для расчета меры «Средняя цена за штуку без НДС» и дабы не смущать пользователя этим полем мы скроем его из списка клиентских средств, оставив меру средней цены.

Еще одно отличие меры от столбца – она позволяет добавить визуализацию:

К примеру, построим KPI степени разброса цен с целевой границей 35% путем деления корня из дисперсии на среднюю арифметическую.

К_вар:=STDEV.P([Поле_Цена за штуку без НДС])/AVERAGE([Поле_Цена за штуку без НДС])

В итоге видим такую таблицу в Excel (кстати расчетное вспомогательное поле цен уже не в списке доступных полей справа):

Двойной клик на 80%-м коэффициенте показывает, что цены действительно колбасит вокруг средней:

Cильнее чем при коэффициенте 15%:

Итак, на данном шаге мы рассмотрели основные отличия мер от полей в рамках PowerPivot.

Шаг 2. Усложняем: Посчитаем долю каждой записи в общих продажах.
Вот первый пример сравнения подходов оконных функций MS SQL Server и DAX:

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

На sql я бы это написал так (за огрехи не пинать, ибо Word синтаксис SQL Server не проверяет):

Begin Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Отгрузка, шт"/sum("t1.Отгрузка, шт") over () as share from Table as t1 order by "t1.Отгрузка, шт"/sum("t1.Отгрузка, шт") desc

Здесь, как можно заметить окно открывается через все записи датасэта, попробуем аналогичную вещь в PowerPivot:

=[Отгрузка шт]/CALCULATE(SUM([Отгрузка шт]);ALL("Таблица1"))

Основное внимание обратим к знаменателю: Я уже упоминал выше что основное отличие вычисляемого поля от меры заключается в том что в поле формулы считают по горизонтали (в рамках одной записи) а меры – по вертикали (в рамках одного атрибута). Здесь мы смогли скрестить свойства поля и свойство меры через метод CALCULATE. И если ширину окна в SQL мы отрегулировали через Over() то здесь мы сделали это через All().

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

Оконные функции на sql будут смотреться так:

Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Цена за шт без НДС", CASE WHEN ABS("t1.Цена за шт без НДС" - AVG("t1.Цена за шт без НДС") OVER()) > 3 * STDEV("t1.Цена за шт без НДС") OVER() THEN 1 ELSE 0 END as Outlier from Table as t1 Go

А вот то же самое в DAX:

If(ABS([Поле_Цена за штуку без НДС]-CALCULATE(AVERAGE([Поле_Цена за штуку без НДС]);ALL("Таблица1")))>(3*CALCULATE(STDEV.P([Поле_Цена за штуку без НДС]);all("Таблица1")));1;0)

Как видите, цена несколько высоковата при средней арифметической 40,03 руб.

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

Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Цена за шт без НДС", count("t1.*) OVER(partition by "t1.Город") as cnt from Table as t1 Go

В DAX:
=CALCULATE(COUNTROWS("Таблица1");ALLEXCEPT("Таблица1";"Таблица1"[Город]))

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

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

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

Запрос на SQL Server:

With a1 as (Select "t1.Имя ТТ", "t1.Город", "t1.Адрес", "t1.Продукт", "t1.№ ТТН", "t1.Дата ТТН", "t1.Отгрузка, шт", "t1.Отгрузка с НДС", "t1.Цена за шт без НДС", count(Distinct "t1.Адрес") OVER(partition by "t1.Город", "t1.Имя ТТ") as adrcnt from Table as t1) Select * from a1 where adrcnt>1

Теперь нам ничего не мешает это сделать и в DAX:

CALCULATE(DISTINCTCOUNT("Таблица1"[Адрес]);ALLEXCEPT("Таблица1";"Таблица1"[Город];"Таблица1"[Имя ТТ]))

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

Конечно в процессе изучения (пробежавшись взглядом на другие формулы) становится понятно что DAX в PowerPivot гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.

Надеюсь было интересно.
Продолжение статьи

/ Бизнес-аналитика с помощью PowerPivot в Microsoft Excel

Вконтакте

Одноклассники

Кулешова Ольга , МСТ, старший преподаватель Центра компьютерного обучения «Специалист» при МГТУ им. Н.Э. Баумана

Бизнес-аналитика
с помощью PowerPivot в Microsoft Excel

Чем новее версия Excel, тем больше инструментов для анализа данных. Рассмотрим возможности анализа больших объемов данных с помощью модели PowerPivot и отчетов PowerView

Такие инструменты, как PowerPivot сам по себе и особенно в совокупности с PowerView, представляют собой современные средства бизнес-аналитики (BI – Business Intelligence), способные хранить, обрабатывать и визуализировать огромное количество данных как из одного, так и из множества источников.

Где PowerPivot в Excel?

Возможность анализа данных с помощью PowerPivot доступна:

• пользователям Microsoft Office 2013 Professional Plus или Office 365 ProPlus;
• а также пользователям версии Microsoft Excel 2010.

Power Pivot в Microsoft Excel 2013 – это надстройка Microsoft Office PowerPivot for Excel 2013, которая встроена в Excel 2013, но не включена, поэтому для дальнейшей работы с PowerPivot необходимо ее включить в списке «Надстройки Com».

Обладателям Excel 2010 для того, чтобы пользоваться PowerPivot, необходимо предварительно скачать компонент PowerPivot (PowerPivot_for_Excel.msi) из Центра Загрузки Microsoft Download Center. Следует обратить внимание на разрядность версии надстройки – 32 или 64 разряда – и выбрать нужную.

Также надстройку нельзя будет использовать, если установлено приложение Excel 2010, но при этом не установлены общие средства Office.

Надстройка PowerPivot – это вкладка с соответствующим названием (см. рис. 1). Начало работы с моделью начинается с нажатия кнопки «Управление».

Источники модели PowerPivot

Источником для PowerPivot могут служить:

• Реляционные базы данных: Microsoft SQL Server, Microsoft SQL Azure, хранилище данных Microsoft SQL Server Parallel Data, Microsoft Access, Oracle, Teradata, Sybase, Informix, IBM DB2, другие (OLEDB/ODBC).
• Многомерные источники: службы Microsoft Analysis Services.
• Веб-каналы данных.
• Текстовые файлы: файлы Excel, текстовые файлы (*.csv или *.txt).

В процессе получения внешних данных у пользователя всегда есть возможность выбрать таблицы, из которых нужно импортировать данные. Причем это можно сделать как самостоятельно, так и прибегнуть к помощи программы – для выбора связанных таблиц нужно выбрать одну таблицу, а затем воспользоваться командой «Выбрать связанные таблицы» (см. рис. 2).

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

Процесс импорта завершен. Результат – окно PowerPivot для Excel, которое содержит вкладки с названиями импортируемых таблиц: «Заказы» и «Сотрудники». Каждый из источников может иметь количество записей, значительно превышающее количество строк на листе Excel.

Особенности и возможности PowerPivot

• При выполнении импорта можно выполнять фильтрацию, переименовывать столбцы и таблицы. Каждая таблица располагается на отдельной странице (вкладке) окна PowerPivot.
• В PowerPivot нельзя редактировать данные отдельных ячеек.
• Таблицы из других источников могут быть импортированы сразу с сохранением связей, или связи могут быть созданы непосредственно в PowerPivot в двух местах: в представлении диаграммы или окне «Создание связей».
• Объемы анализируемых данных могут быть ограничены только объемом оперативной памяти.
• В PowerPivot расширенные формулы на языке выражений анализа данных (DAX).
• Для удобства работы можно выстроить подчинение данных в виде иерархий, чтобы в дальнейшем их использовать в отчетах.
• Для анализа состояния определенных значений (полей) в PowerPivot создаются ключевые показатели эффективности (KPI), которые можно использовать как в отчетах PowerView, так и в сводных таблицах.

Вычисления в источниках PowerPivot

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

Можно встретить следующие категории функций DAX: Дата и время, Фильтр, Логические, Арифметические и Тригонометрические функции, Статистические, Текст, Информация, Родители-потомки. В большинстве своем они полностью повторяют возможности аналогичных функций Excel, что не требует их повторного изучения.

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

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

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

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

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

При работе с данными в PowerPivot надо знать о неявных и явных вычисляемых полях.

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

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

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

При создании вычисляемого поля в модели PowerPivot в одной формуле, как и в формулах Excel, допустимо использование до 64 уровней вложенности функций, все строится по принципу:

Имя_вычисляемого_поля:=Формула

Отчеты по модели PowerPivot

Исходные данные модели содержатся в окне PowerPivot для Excel, а сами отчеты строятся в окне Microsoft Excel.

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

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

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

Во всех создаваемых отчетах для удобства фильтрации можно использовать срезы – быстро, удобно, наглядно. Срезы можно форматировать, и их можно упорядочивать как вручную, так и с помощью команд «Выровнять вертикально» или «Выровнять горизонтально». Работа со срезами аналогична работе с ними в обычных сводных таблицах начиная с Excel 2010.

Отчеты PowerView

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

Для построения отчета необходимо в окне Excel выбрать на вкладке «Вставка» команду PowerView. Чтобы добавить нужные поля в отчет, их следует переместить в область отчета или в область поля внизу области «Поля Power View» (аналог области СТРОКИ в отчетах сводных таблиц). Если перетащить поле из списка полей в область ПРАВИЛО ФРАГМЕНТИРОВАНИЯ, то затем можно будет быстро осуществлять выбор данных этого поля (фильтрация). Любые данные в отчете могут быть представлены как в табличном, так и графическом виде, как по отдельности, так и вместе.

Чтобы одновременно показать данные в двух представлениях, следует скопировать таблицу в свободное место отчета, а затем выбрать нужный вариант отображения из предлагаемых: Таблица, Линейчатая диаграмма, Гистограмма или Другая диаграмма (Круговая, Точечная). Щелкнув по элементу диаграммы, устанавливаете фильтр по этому значению, а также и в других объектах отчета (см. рис. 6).

Отчеты PowerView – это динамические отчеты. Фильтрация в одном элементе отчета сразу перекликается с данными другого элемента отчета, если они построены по данным одной или нескольких связанных таблиц. Можно также выполнять построение в одном отчете по данным нескольких таблиц, абсолютно не связанных друг с другом, но позволяющих анализировать данные одновременно.

Отчеты PowerView – это быстрый интерактивный просмотр данных, наглядная визуализация, различные наглядные способы предоставления данных.

В чем сходство модели данных PowerPivot и отчетов PowerView?

Работать с моделью данных PowerPivot может каждый пользователь: не требуется установки и настройки множества специальных программ – все сразу в одной надстройке; данные в модель добавляются простыми операциями импорта с возможностью обновления; без дополнительных знаний о реляционных базах данных можно создавать связи между таблицами; используя уже знакомые формулы, можно создавать вычисления в модели.

Отчеты PowerView: быстро строятся, интуитивно понятны, динамичны. Это те качества, которые крайне необходимы для пользователя, который постоянно сталкивается с большими объемами данных.

Вконтакте

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

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

Примечание: перед освоение сведения,

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

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

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

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

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

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

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

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

Импорт данных из различных источников, таких как большие корпоративные базы данных, публичные ленты новостей, электронные таблицы и текстовые файлы на локальном компьютере.	Импорт всех данных из источника данных.	Фильтрация данных и переименование столбцов и таблиц при импорте.
Создание таблиц	Таблицы могут находиться на любом листе книги. Листы могут содержать несколько таблиц.	Таблицы организованы в виде отдельных страниц с вкладками в окне Power Pivot.
Редактирование данных в таблице	Можно изменять значения в отдельных ячейках таблицы.	Нельзя изменять отдельные ячейки.
Создание связей между таблицами	В диалоговом окне «Связи».	В представлении диаграммы или диалоговом окне «Создание связей».
Создание вычислений	Формулы Excel.	Расширенные формулы на языке выражений анализа данных (DAX).
Создание иерархий		Нажмите кнопку PivotTable в окне Power Pivot.
Расширение модели для Power View	Создается базовая модель данных.	Доступны расширения, например определение полей по умолчанию, изображений и уникальных значений.
Использование Visual Basic для Applications (VBA)		Не используйте VBA в окне Power Pivot.
Группировка данных		Получение справочной информации Дополнительные сведения Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте Excel User Voice .

Те, кто часто в работе пользуются Сводными таблицами , уже смогли оценить насколько мощным инструментом они являются. Однако, к сожалению, сводные таблицы не могут делать отчеты из разных источников данных, будь то базы данных или файлы. Также, когда мы оперируем большими объемами данных, наши обычные компьютеры начинают не справляться с обработкой этих данных, и начинают "тормозить" или вообще зависают. Это касается таблиц, которые имеют более 300 тыс. строк. Итак, чтобы решить все эти проблемы, корпорация Microsoft разработала бесплатное приложение к Excel , которое расширяет возможности обычных Сводных таблиц .

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

Так что такое PowerPivot?

Power Pivot - это дополнительная надстройка для Excel , которая расширяет функционал Сводных таблиц (Pivot Tables ). Используется только для MS Office 2010. В новой версии Office 2013 PowerPivot уже является неотъемлемым элементом Excel .

Как установить PowerPivot?

Перед установкой PowerPivot , вам необходимо (для Windows XP ):

деинсталлировать MS Office 2010

установить SP3 для Windows XP

установить .NET Framework 4.0 та Visual Studio 2010 Tools for Office Runtime

установить полностью MS Office 2010 (со всеми программами и общими средствами)

установить PowerPivot

После установки PowerPivot в Excel появляется дополнительная вкладка:

Нажав PowerPivot Window откроется отдельное окно, в которое можно загружать данные из разных источников.

После закачки данных из различных файлов Excel в вкладку PowerPivot Window , вы сможете работать с ними, и на обычном листе Excel , т.е. добавлять колонки, производить вычисления, используя формулы. Также, появляется уникальная как для Excel "я возможность, делать связи между загруженными таблицами как в Access . Используя таблицы со связями, мы можем делать Сводную таблицу , которая будет брать данные из разных таблиц, без предварительного их ручного объединения, без многократного использования VLOOKUP (ВПР ) , просто и быстро.

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

Также, как мы видим, в Сводной таблице появляются новые дополнительные поля, которые называются Визуальными фильтрами (Slicers ) .

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

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