Совместное использование Power Query и Power Pivot

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

Содержание

Как добавить Power Query и Power Pivot в меню

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

Если в меню Excel у вас нет надстроек, то нужно их добавить в окне Управления надстройками:
Файл → Параметры → Надстройки → в Выпадающем меню выбрать → Надстройки COM → Перейти → в открывшемся окне выбрать надстройки.

Как надстройки помогают в создании отчетов Excel

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

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

pp5

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

Надстройки в Excel и Power BI

Огромное преимущество надстроек Power Query и Power Pivot – они есть не только в Excel, но и в Power BI. Поэтому на тренингах я обычно говорю слушателям, что мы изучаем сразу две программы.

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

Для сравнения – интерфейс Power Query в обеих программах выглядит практически одинаково:

Окно редактора запросов

Power Query в Excel

Редактор запросов в Power BI

Power Query в Power BI

Power Pivot в целом также работает одинаково в Excel и Power BI, кроме ряда некоторых нюансов. Например, так выглядит окно настройки модели данных:

Модель данных в Excel

Модель данных в Excel

Модель данных в Power BI

Модель данных в Power BI

Кстати, если у вас есть файл Excel, в котором вы настраивали аналитику с помощью Power Query и Power Pivot, то его содержимое можно перенести в Power BI. Это делается в Power BI: меню Файл → Импортировать → выберите «Power Query, Power Pivot, Power View». После пары вопросов и некоторых «размышлений» запросы и вычисления из Excel будут перенесены в Power BI.

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

Автор: Станислав Салостей

Для чего нужна и что может надстройка «Power Query»?

«Power Query» помогает пользователям формировать запросы по аналогии с базами данных MS «Access». Что в конечном итоге дает возможность формирования таблиц, в котороые информация загружается из разных источников и выстраивается в нужном порядке.

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

Еще одно значительное преимущество «Power Query» в том, что таблицы, сформированные в этой надстройке, менее требовательны к ресурсам ПК за счет формирования таблиц на ссылках — принцип действия похож на связанные таблицы «Excel».

Архитектура Power Query

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

  1. Получение данных;
  2. Преобразование данных;
  3. Загрузка данных;
  4. Обновление данных.

Получение данных

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

Пользователю предлагается выбрать из четырёх групп источников:

  • Из файла;
  • Из базы данных;
  • Из Azure;
  • Из других источников.
2.png

На практике, чаще всего используется группа Из файла, позволяющая получать данные из книги Excel, из файлов CSV и XML, из текстовых файлов и (целым скопом) из папки.

3.png

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

4.png

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

5.png

Для примера, рассмотрим получение данных из следующего текстового файла:

6.png

На вкладке Данные выбираем Создать запрос -> Из файла -> Из текста. В открывшемся окне Импорт данных находим нужный нам файл и жмём на кнопку Импорт.

7.png

Преобразование данных

Откроется отдельное окно Редактора запросов, с собственной лентой, состоящей из четырёх вкладок:

  • Главная;
  • Преобразование;
  • Добавить столбец;
  • Просмотр.

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

8.png

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

9.png

Прежде всего избавимся от четырёх верхних строк, которые не представляют для нас никакой ценности. Для этого, на вкладке Главная выбираем Сократить строки -> Удалить строки -> Удаление верхних строк. Далее, в появившемся окне указываем количество строк, которые необходимо удалить и жмём ОК.

pq%2B%25D1%2583%25D0%25B4%25D0%25B0%25D0%25BB%25D0%25B5%25D0%25BD%25D0%25B8%25D0%25B5%2B%25D0%25B2%25D0%25B5%25D1%2580%25D1%2585%25D0%25BD%25D0%25B8%25D1%2585%2B%25D1%2581%25D1%2582%25D1%2580%25D0%25BE%25D0%25BA.gif

Теперь нам нужно разделить данные по столбцам. Опять же, на вкладке Главная выбираем Разделить столбец -> По разделителю и в появившемся окне в качестве разделителя указываем табуляцию, после чего жмём ОК.

pq%2B%25D1%2580%25D0%25B0%25D0%25B7%25D0%25B4%25D0%25B5%25D0%25BB%25D0%25B5%25D0%25BD%25D0%25B8%25D0%25B5%2B%25D1%2581%25D1%2582%25D0%25BE%25D0%25BB%25D0%25B1%25D1%2586%25D0%25B0.gif

Далее, на вкладке

Главная

выбираем команду

Использовать первую строку в качестве заголовков

.

pq%2B%25D0%25BF%25D0%25BE%25D0%25B2%25D1%258B%25D1%2588%25D0%25B5%25D0%25BD%25D0%25B8%25D0%25B5%2B%25D0%25B7%25D0%25B0%25D0%25B3%25D0%25BE%25D0%25BB%25D0%25BE%25D0%25B2%25D0%25BA%25D0%25BE%25D0%25B2.gif

 И последний штрих – если присмотреться к столбцу Сумма, то можно увидеть, что Power Query присвоил ему текстовый тип данных.

11.png

Чтобы превратить эти данные в числовые выделяем столбец Сумма и на вкладке

Главная

изменяем тип данных на Целое число.

12.png

Загрузка данных

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

Доступны две команды: Закрыть и загрузить и Закрыть и загрузить в…

10.png

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

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

14.png

Пользователю предлагается несколько опций:

  • загрузка данных в таблицу с указанием листа (новый либо существующий);
  • только создание соединения – в случае если мы планируем использовать этот запрос для объединения с другими запросами;
  • добавление в модель данных – если мы хотим загрузить полученные данные прямо в модель данных Power Pivot.

Выберем первую команду из выпадающего меню и загрузим данные на новый лист.

13.png

Обновление данных

А теперь главный секрет столь огромной популярности Power Query.

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

15.png

Чтобы загрузить новые данные на лист, на вкладке Данные, в группе Подключения, жмём на кнопку Обновить.

pq%2B%25D0%25BE%25D0%25B1%25D0%25BD%25D0%25BE%25D0%25B2%25D0%25BB%25D0%25B5%25D0%25BD%25D0%25B8%25D0%25B5%2B%25D0%25B4%25D0%25B0%25D0%25BD%25D0%25BD%25D1%258B%25D1%2585.gif

И это всё! Только подумайте об этом – Вам больше не нужно проходить весь процесс заново, как Вы раньше делали при обычной выгрузке данных из текстовых таблиц. Всё, что Вам нужно всего лишь нажать на одну кнопку!

Заключение

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

Power Query

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

Для этого и необходим Power Query. До версии Excel 2013 включительно этот инструмент был в виде надстройки, которую можно было установить бесплатно с сайта Microsoft.

В версии 2016 это уже встроенный в программу инструментарий, находящийся на вкладке “Данные” (Data) в разделе “Скачать и преобразовать” (Get and Transform).

ИНСТРУКЦИЯ

по работе с Excel

Спасибо. Будет интересно!

Перечень источников информации, к которым можно подключаться — огромный: от баз данных (их в последней версии 10) до Facebook и Google таблиц (рис. 1).

Евгений Довженко о том, как можно эффективно работать даже с огромными массивами данных. 1

Рис 1. Выбор источника данных в Power Query

Вот некоторые возможности Power Query по подготовке и преобразованию данных:

иконка 1

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

иконка 2

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

иконка 3

транспонирование таблицы, разворачивание по столбцам (Pivot) и наоборот — сворачивание данных, организованных по столбцам, в построчный вид (Unpivot)

иконка 4

объединение нескольких таблиц: как вниз — одну под другую, так и связывание по общей колонке (единому ключу)

Евгений Довженко о том, как можно эффективно работать даже с огромными массивами данных. 2

Рис 2. Окно редактора Power Query

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

Пример

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

Таблица на сайте непригодна для прямого использования (рисунок 2-1):    

иконка 1

все валюты не нужны

иконка 2

в колонке “Курс” в качестве разделителя целой и дробной частей используется точка (в наших региональных настройках — запятая)

иконка 3

в колонке “Курс” отображается показатель за разное количество единиц валюты: за 100, за 1000 и т. д. (указано в отдельной колонке “Количество единиц”)

Евгений Довженко о том, как можно эффективно работать даже с огромными массивами данных. 3

Рис. 2-1. Так выглядит таблица с курсами валют на сайте Нацбанка.

С помощью Power Query мы подключаемся к таблице текущих курсов валют на сайте НБУ и в этом редакторе готовим запрос на извлечение данных:

иконка 1

В колонке “Курс” меняем точку на запятую (инструмент “Замена значений”).

иконка 2

Создаем вычисляемый столбец, в котором курсы валют в колонке “Курс” делятся на количество единиц валюты из колонки “Количество единиц”.

иконка 3

Удаляем лишние столбцы и оставляем только строки валют, с которыми работаем.

иконка 4

Выгружаем полученную таблицу на рабочий лист Excel.

Результат показан на рисунке 2-2.

Евгений Довженко о том, как можно эффективно работать даже с огромными массивами данных. 4

Рис. 2-2. Так выглядит результирующая таблица в нашем Excel файле.

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

Power Pivot

У вас данные находятся в разрозненных источниках? Некоторые таблицы содержат больше 1 млн строк? Вам нужно все это объединить в одну модель данных и анализировать с помощью, например, сводной таблицы Excel? Здесь понадобится Power Pivot — надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).

В Power Pivot вы можете добавлять данные из разных источников, связывать таблицы между собой (рисунок 3). Таблицы при этом не обязательно должны находиться на рабочих листах Excel. Вместо этого они по-прежнему будут храниться в файле Excel, но просматривать их можно в окне Power Pivot (рис. 4). Поэтому нет ограничения на количество строк — в вашем файле Excel могут находиться таблицы и в сотни миллионов строк.

Евгений Довженко о том, как можно эффективно работать даже с огромными массивами данных. 5

Рис. 3. Окно Power Pivot в представлении диаграммы

Евгений Довженко о том, как можно эффективно работать даже с огромными массивами данных. 6

Рис. 4. Окно Power Pivot в представлении данных

Вот некоторые возможности Power Pivot, помимо описанных выше:

иконка 1

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

иконка 2

создавать и мониторить в сводной таблице ключевые показатели эффективности (KPI)

иконка 3

создавать иерархические структуры (например, по географическому признаку — регион, область, город, район)

И обрабатывать все это с помощью сводной таблицы Excel, построенной на модели данных.

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

С помощью Power Pivot:

иконка 1

добавляем все 5 таблиц в модель данных

иконка 2

связываем таблицы по общим ключам (столбцам)

иконка 3

в таблице “Продажи” создаем вычисляемый столбец “Продажи в закупочных ценах”, умножив количество штук из таблицы “Продажи” на закупочную цену из таблицы “Цена закупки”

иконка 4

создаем вычисляемое поле (меру) “Маржа”

иконка 5

с помощью инструмента “Ключевые показатели эффективности” устанавливаем цель по маржинальности и настраиваем визуализацию — как выполнение цели будет визуализироваться в сводной таблице

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

См. также

Справка по Power Query для Excel

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

Нужна дополнительная помощь?

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

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

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

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

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

  1. Откройте Excel .

  2. Выберите Файл > Параметры .

  3. Выберите Надстройки .

  4. Откройте раскрывающееся меню Управление и выберите Надстройки COM .

    image_15989.jpg

  5. Выберите Перейти .

  6. Выберите Microsoft Power Pivot для Excel .

    image_16003.jpg

  7. Выберите ОК . Вкладка Power Pivot добавлена ​​в Excel.

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

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

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

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

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

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

  • Загрузите данные из базы данных Microsoft Access, в которой описаны олимпийские медали.
  • Загрузите три образца Business Intelligence, которые показывают, как использовать Power Pivot для импорта данных, создания отношений, создания сводных таблиц и проектирования сводных диаграмм.

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

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

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

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

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

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

  2. Выберите Данные , затем выберите Получить данные > Из файла > Из рабочей книги , чтобы открыть Импорт данных диалоговое окно.

    image_16042.jpg

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

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

  4. Установите флажок Выбрать несколько элементов .

    image_16054.jpg

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

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

  6. Выберите Загрузить , чтобы импортировать таблицы данных в модель данных.

  7. Чтобы убедиться, что импорт прошел успешно и модель данных была создана, перейдите в раздел Данные и в группе Инструменты данных выберите Перейти в окно Power Pivot .

    image_16065.jpg

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

    image_16080.JPG

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

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

Если вы хотите добавить новые данные в модель данных, в окне Excel перейдите на Power Pivot и выберите Добавить в модель данных . Данные отображаются в виде новой вкладки в окне Power Pivot.

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

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

  1. Выберите Power Pivot , затем выберите Управление , чтобы открыть окно Power Pivot.

    image_16085.jpg

  2. Выберите Главная , затем выберите Вид диаграммы .

    image_16091.jpg

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

    image_16099.jpg

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

    image_16104.jpg

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

    image_16113.jpg

  6. Выберите Главная , затем выберите Просмотр данных .

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

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

  1. В окне Power Pivot выберите Главная , затем выберите Сводная таблица .

    image_16116.jpg

  2. В диалоговом окне Создать сводную таблицу выберите Новый лист , затем выберите ОК .

    image_16119.jpg

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

    image_16120.jpg

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

    image_16188.jpg

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

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

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

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

image_16192.jpg

  1. Выберите сводную таблицу, затем перейдите в Анализ инструментов сводной таблицы .
  2. Выберите Сводная диаграмма , чтобы открыть диалоговое окно Вставить диаграмму .
  3. Выберите диаграмму, затем нажмите ОК .

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

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

  1. В окне Power Pivot выберите Главная , затем выберите стрелку раскрывающегося списка Сводная таблица . Появится список параметров.

  2. Выберите Сводная диаграмма .

    image_16195.jpg

  3. Выберите Новый лист и выберите ОК . Заполнитель PivotChart появится на новом листе.

    image_16198.jpg

  4. Перейдите на страницу Анализ инструментов сводной диаграммы и выберите Список полей , чтобы отобразить панель полей сводной диаграммы.

    image_16210.jpg

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

    image_16217.jpg

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

Обработка больших объемов данных. Часть 3. Сводные таблицы

Подробности Создано 24 Октябрь 2011

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

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

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

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

Выборка уникальных значений

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

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

В поле данных необходимо, чтобы стоял вид операции – «количество». Этот параметр позволяет обрабатывать в области данных сводной таблицы нечисловые поля исходных данных. Альтернативой операции подсчета количества служит стандартная функция COUNTIF. Сформировать набор уникальных значения только с помощью формул в принципе тоже возможно (см. часть 1), но это потребует очень сложных формул с вычисляемой адресацией. То есть, использование сводной таблицы в данной задаче – это самый оптимальный способ решения.

Суммирование значений

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

В примере на листе Сумма сформированы итоговые данные по заказам по каждой стране:

Вид операции «Сумма» в поле данных допускает использование только числовых полей. Прочие виды агрегации исходных данных на практике почти не используются.

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

Двухмерный анализ

Описанные ранее примеры демонстрируют анализ данных по одному критерию. Электронные таблицы позволяют наглядно представить данные в двух измерениях: по столбцам и по строкам. Сводные таблицы также имеют эти области отображения данных.

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

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

Многомерный анализ

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

Пример на листе Фильтр демонстрирует возможность просмотра данных по компаниям одной страны с использованием области фильтра сводной таблицы:

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

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

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

Возможности «Power Query» «Excel».

  1. Возможность загрузки данных в таблицу «Excel» из множества различных источников:Power Query загрузка данных

    • Интернет;
    • Файлы форматов: XML, CSV, таблицы Excel, текстовые документы;
    • Из баз данных: SQL, Oracle, Access, IBM DB, Mysql, Sybase и т.д.;
    • Из Azure;
    • Из веб служб и интерфейсов: facebook, MS Exchange Online, облачных хранилищ;
  2. Возможность слияния нескольких таблиц в одну с установкой связей по определенным критериям.Power Query слияние таблиц

  3. Выполнять поиск по каталогам.

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

Power View

Иногда сводная таблица — не лучший вариант визуализации данных. В таком случае можно создавать отчеты Power View. Как и Power Pivot, Power View — это надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).

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

Вот некоторые возможности Power View:

иконка 1

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

иконка 2

организовывать срезы и фильтры

иконка 3

уходить на разные уровни детализации данных

иконка 4

добавлять карты и располагать на них данные

иконка 5

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

Пример отчета Power View — на рисунке 5.

Евгений Довженко о том, как можно эффективно работать даже с огромными массивами данных. 7

Рис. 5. Пример отчета Power View

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

Хотите получать дайджест статей?

Одно письмо с лучшими материалами за неделю. Подписывайтесь, чтобы ничего не упустить.

Спасибо за подписку!

Последние материалы

img-gd-61a62acd00b3e264345207.jpg

Полезные функции, которые сделают вас королем гугл-документов.

img-alkhimovich2-61a54630a075f792406904.jpg

Как внедрить бонусную систему: 5 советов от топ-менеджера Luxoft.

img-mascot-619faf0bec82a355501337.jpg

Как появилась идея говорящих драже M&M’s и в чем успех английского капитана Моргана.

Курс по теме:

“Excel для бізнесу”

Программы

ВедетАлексейВощак
Bridges Consulting

15 декабря24 января

Алексей Вощак
Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...