Bazaprogram.ru

Новости из мира ПК
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Учебник powerpivot для excel

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

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

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

Power Query

Еще в 2013 году специально созданная группа разработчиков внутри Microsoft выпустила для Excel бесплатную надстройку Power Query (другие названия — Data Explorer, Get&Transform), которая умеет массу полезных для повседневной работы вещей:

  • Загружать данные в Excel из почти 40 различных источников, среди которых базы данных (SQL, Oracle, Access, Teradata. ), корпоративные ERP-системы (SAP, Microsoft Dynamics, 1C. ), интернет-сервисы (Facebook, Google Analytics, почти любые сайты).
  • Собирать данные из файлов всех основных типов данных (XLSX, TXT, CSV, JSON, HTML, XML. ), как поодиночке, так и сразу оптом — из всех файлов указанной папки. Из книг Excel можно автоматически загружать данные сразу со всех листов.
  • Зачищать полученные данные от «мусора»: лишних столбцов или строк, повторов, служебной информации в «шапке», лишних пробелов или непечатаемых символов и т.п.
  • Приводить данные в порядок: исправлять регистр, числа-как-текст, заполнять пробелы, добавлять правильную «шапку» таблицы, разбирать «слипшийся» текст на столбцы и склеивать обратно, делить дату на составляющие и т.д.
  • Всячески трансформировать таблицы, приводя их в желаемый вид (фильтровать, сортировать, менять порядок столбцов, транспонировать, добавлять итоги, разворачивать кросс-таблицы в плоские и сворачивать обратно).
  • Подставлять данные из одной таблицы в другую по совпадению одного или нескольких параметров, т.е. прекрасно заменяет функцию ВПР (VLOOKUP) и ее аналоги.

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

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

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

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

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

По моему мнению, это самая полезная для широкого круга пользователей надстройка из всех перечисленных в этой статье. Очень много задач, для которых раньше приходилось либо жутко извращаться с формулами, либо писать макросы — теперь легко и красиво делаются в Power Query. Да еще и с последующим автоматическим обновлением результатов. А учитывая бесплатность, по соотношению «цена-качество» Power Query просто вне конкуренции и абсолютный must have для любого средне-продвинутого пользователя Excel в наши дни.

Power Pivot

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

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

  1. Сначала мы загружаем данные в Power Pivot — поддерживается 15 различных источников: распространенные БД (SQL, Oracle, Access. ), файлы Excel, текстовые файлы, веб-каналы данных. Кроме того, можно использовать Power Query как источник данных, что делает анализ почти всеядным.
  2. Затем между загруженными таблицами настраиваются связи или, как еще говорят, создается Модель Данных. Это позволит в будущем строить отчеты по любым полям из имеющихся таблиц так, будто это одна таблица. И никаких ВПР опять же.
  3. При необходимости, в Модель Данных добавляют дополнительные вычисления с помощью вычисляемых столбцов (аналог столбца с формулами в «умной таблице») и мер (аналог вычисляемого поля в сводной). Всё это пишется на специальном внутреннем языке Power Pivot, который называется DAX (Data Analysis eXpressions).
  4. На листе Excel по Модели Данных строятся интересующие нас отчеты в виде сводных таблиц и диаграмм.

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

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

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

  • В Power Pivot нет предела по количеству строк (как в Excel). Можно грузить таблицы любого размера и спокойно работать с ними.
  • Power Pivot очень хорошо умеет сжимать данные при загрузке их в Модель. 50 Мб исходный текстовый файл может легко превратиться в 3-5 Мб после загрузки.
  • Поскольку «под капотом» у Power Pivot, по сути, полноценный движок базы данных, то с большими объемами информации он справляется очень быстро. Нужно проанализировать 10-15 млн. записей и построить сводную? И все это на стареньком ноутбуке? Без проблем!

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

Читать еще:  Пивот таблицы excel

Power Maps

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

Демо-версию Power Map (почти не отличающуюся от полной по возможностям, кстати) можно совершенно бесплатно загрузить опять же с сайта Microsoft. Полная же версия включена в некоторые пакеты Microsoft Office 2013-2016 вместе с Power Pivot — в виде кнопки 3D-карта на вкладке Вставка (Insert — 3D-map) :

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

  • Карты могут быть как плоскими, так и объемными (земной шар).
  • Можно использовать несколько разных типов визуализации (гистограммы, пузырьковые диаграммы, тепловые карты, заливку областями).
  • Можно добавлять измерение времени, т.е. анимировать процесс и смотреть на него в развитии.
  • Карты подгружаются из сервиса Bing Maps, т.е. для просмотра нужен весьма шустрый доступ в интернет. Иногда возникают сложности с правильным распознаванием адресов, т.к. названия в данных не всегда совпадают с Bing Maps.
  • В полной (не демо) версии Power Map можно использовать собственные загружаемые карты, например визуализировать посетителей торгового центра или цены на квартиры в жилом доме прямо на строительном плане.
  • На основе созданных гео-визуализаций можно прямо в Power Map создавать видеоролики (пример), чтобы поделиться ими потом с теми, у кого надстройка не установлена или включить в презентацию Power Point.

Power View

Эта надстройка появилась впервые в составе Excel 2013 и предназначена для «оживления» ваших данных — построения интерактивных графиков, диаграмм, карт и таблиц. Иногда для этого используют термины дашборд (dashboard) или панель показателей (scorecard) . Суть в том, что вы можете вставить в ваш файл Excel специальный лист без ячеек — слайд Power View, куда добавить текст, картинки и массу различного типа визуализаций по вашим данным из Модели Данных Power Pivot.

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

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

  • Исходные данные берутся всё оттуда же — из Модели Данных Power Pivot.
  • Для работы с Power View необходимо установить на вашем компьютере Silverlight — майкрософтовский аналог Flash (бесплатный).

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

Power BI

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

1. Power BI Desktop — программа для анализа и визуализации данных, включающая в себя, помимо прочего, весь функционал надстроек Power Query и Power Pivot + улучшенные механизмы визуализации из Power View и Power Map. Скачать и установить её можно совершенно бесплатно с сайта Microsoft.

В Power BI Desktop можно:

  • Загружать данные из более чем 70 различных источников (как в Power Query + дополнительные коннекторы).
  • Связывать таблицы в модель (как в Power Pivot)
  • Добавлять к данным дополнительные вычисления с помощью мер и вычисляемых столбцов на DAX (как в Power Pivot)
  • Создавать на основе данных красивейшие интерактивные отчеты с разного типа визуализациями (очень похоже на Power View, но еще лучше и мощнее).
  • Публиковать созданные отчеты на сайте Power BI Service (см. следующий пункт) и делиться ими с коллегами. Причем есть возможность давать разные права (чтение, редактирование) разным людям.

2. Онлайн-сервис Power BI — упрощенно говоря, это сайт, где у вас и у каждого пользователя в вашей компании будет своя «песочница» (workspace) куда можно загружать созданные в Power BI Desktop отчеты. Помимо просмотра, позволяет их даже редактировать, воспроизводя онлайн почти весь функционал Power BI Desktop. Также сюда можно заимствовать отдельные визуализации из чужих отчетов, собирая из них свои авторские дашборды.

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

3. Power BI Mobile — приложение для iOS / Android / Windows для подключения к Power BI Service и удобного просмотра (не редактирования) созданных отчетов и дашбордов прямо на экране телефона или планшета. Скачать его (совершенно бесплатно) можно тут.

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

Причем всё это с сохранением интерактивностии и анимации + заточенность под тач и рисование по экрану пером. Очень удобно. Таким образом, бизнес-аналитика становится доступной всем ключевым лицам компании в любой момент и в любом месте — нужен только доступ в интернет.

Тарифные планы Power BI. Power BI Desktop и Mobile бесплатны изначально, большинство функций Power BI Service — тоже. Так что для персонального использования или применения в пределах небольшой компании за всё вышеперечисленное не нужно платить ни копейки и можно смело оставаться на плане Free. Если вы хотите делиться отчетами с коллегами и администрировать их права доступа, то придется перейти на Pro (10$ в месяц за пользователя). Есть еще Premium — для больших компаний (>500 пользователей), которым требуются для данных отдельные хранилища и серверные мощности.

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

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

Читать еще:  Sumifs 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)

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

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

Приложение для расширения возможностей Сводных таблиц (Power Pivot)

Те, кто часто в работе пользуются Сводными таблицами, уже смогли оценить насколько мощным инструментом они являются. Однако, к сожалению, сводные таблицы не могут делать отчеты из разных источников данных, будь то базы данных или файлы. Также, когда мы оперируем большими объемами данных, наши обычные компьютеры начинают не справляться с обработкой этих данных, и начинают «тормозить» или вообще зависают. Это касается таблиц, которые имеют более 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 для WindowsXP
  • установить .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:

    PowerPivot преодолевает ограничения на число строк

    С помощью драйвера ODBC я организовал связь PowerPivot с MySQL
    PowerPivot позволяет работать в Excel с гораздо более объемными таблицами
    Средство импортирования данных в PowerPivot позволяет без труда определить, какие таблицы, столбцы и записи надо загружать
    Читать еще:  Чистрабдни межд в excel

    Microsoft PowerPivot представляет собой надстройку к анонсированному корпорацией табличному процессору Excel 2010. Мое внимание программа привлекла прежде всего тем, что с ее помощью можно работать с гораздо большими массивами данных, чем в самом Excel. К примеру, мне удавалось загружать и просматривать таблицы, содержащие более 3,9 млн. строк — это в четыре раза больше предельного для Excel значения. При этом прокрутка таблицы осуществлялась так же легко, как если бы речь шла о документе всего из нескольких сот строк.

    Если вы при работе с электронной таблицей упираетесь в ограничение на число строк, это, как правило, означает, что для решения вашей задачи лучше подошла бы СУБД. Однако для человека, привыкшего иметь дело с редактором таблиц, не так-то просто отказаться от любимого инструмента.

    PowerPivot радует не только скоростью работы с длинными таблицами — этот бесплатный продукт еще и очень эффективно обеспечивает связь между рабочими книгами Excel и одной или несколькими базами данных. Я с помощью драйвера ODBC привязывал таким образом данные из Access, из “плоских” (бесструктурных) файлов и из MySQL.

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

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

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

    PowerPivot в лаборатории

    Тестирование PowerPivot проводилось в бета-версии Excel 2010 на виртуальной машине под управлением 64-разрядной версии Windows 7. Вообще-то и у PowerPivot, и у Excel 2010 существуют 64-разрядные версии, но я использовал при испытаниях 32-разрядные версии обеих программ. Бета-версии этих продуктов можно получить по адресу соответственно microsoft.com/office/2010 и powerpivot.com. Если вы решили установить не весь набор приложений Office 2010, а только Excel, то вам нужно инсталлировать также “Общие средства Office” (Office Shared Features), без которых PowerPivot не сможет нормально функционировать.

    Для испытаний я взял выборку из базы данных Access и таблицы Excel из проекта Codeplex. Кроме того, я взял с сайта OpenSecrets.org данные о финансировании кампании, проводимой Центром за ответственную политику, и импортировал их в MySQL.

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

    В PowerPivot есть удобный мастер для закачки внешних данных из СУБД, плоских файлов и других источников. Я подключал свою тестовую таблицу к базе данных Access, переходя к нужному ACCDB-файлу, и затем либо выбирал объект из списка таблиц и представлений в СУБД, либо использовал SQL-запрос. При работе со списком можно выбирать таблицы вместе с фильтрами, определяющими правила включения записей.

    При осуществлении аналогичных операций с данными из MySQL (это делалось через драйвер ODBC, который был у меня уже установлен и сконфигурирован в Windows) всё поначалу шло примерно так же — надо было указывать мышью и нажимать кнопку. Но когда я задействовал графическое средство для выбора таблиц и фильтров, программа стала выдавать сообщения об ошибке. С помощью SQL-запросов я все же смог задать требуемые условия, но это оказалось несколько сложнее, чем с помощью мастера.

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

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

  • Ссылка на основную публикацию
    Adblock
    detector