Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

Загрузка данных в Power Pivot с помощью Power Query

Чтобы использовать загруженные через Power Query данные в Power Pivot, после импорта (и при необходимости преобразования в Power Query) нажмите «Загрузить в» (Load To) и активируйте галочку «Добавить эти данные в модель данных» (Add this data to the Data Model).

Если при этом вам не нужно выводить эти данные на лист Excel как сводную таблицу, а только загрузить в Power Pivot для дальнейшей обработки в модели данных, выберите способ «Только создать подключение» (Only Create Connection). Тогда вы загрузите данные в модель Power Pivot, а на листах книги Excel они никак не отобразятся.

Настройка модели данных

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

«Главная» → группа «Просмотр» → «Представление диаграммы» (Home → View → Diagram View).

В этом режиме отображаются все загруженные в Power Pivot источники данных со списком их полей (столбцов). Здесь можно установить связь между ними, для этого достаточно потянуть левой кнопкой мыши за поле в одной таблице и соединить его линией с другой. Разумеется, нужно, чтобы в обеих таблицах был столбец с ключом, по которому можно установить связь (проводя аналогии с ВПР / VLOOKUP и другими функциями поиска, это искомое значение, по которому мы можем связать две таблицы просто на листе Excel с помощью формул).

Вычисляемые столбцы в Power Pivot

В Power Pivot нельзя редактировать отдельные ячейки (здесь нет уровня ячеек, как в Excel), но есть возможность создавать свои вычисляемые столбцы (с формулами).

В таких столбцах ссылаться можно на другие столбцы. В каком-то смысле это похоже на ссылки в рамках таблиц в Excel.

Язык функций здесь свой — DAX (Data Analysis Expressions, выражения для анализа данных). Впрочем, большинство функций будут знакомы вам по Excel — как по логике и синтаксису, так и по названиям (но в DAX они всегда на английском). Здесь есть и логические, и текстовые функции, и функции для работы с датой. Для ссылок на столбцы в других таблицах в рамках модели данных используется функция RELATED.

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

Формулы, как и в Excel, вводятся со знака «равно».

Меры в Power Pivot

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

Если в обычных значениях сводной есть совсем небольшой набор операций агрегирования — всего 11 (напомним, по умолчанию это суммирование для чисел и количество для текстовых значений), то в мерах в Power Pivot используются функции DAX, а это означает намного большее количество операций. Например, в обычной сводной нельзя посчитать количество уникальных значений или медианное значение, а в Power Pivot можно — с помощью функций DISCTINCTCOUNT и MEDIAN соответственно.

Меры вводятся в нижней части окна Power Pivot.

Достаточно поставить знак «равно» и начать вводить функцию в любой ячейке.

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

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

После создания меры она будет доступна в сводной, построенной по модели данных.

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

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

Создать сводную можно с помощью соответствующей кнопки на ленте Power Pivot.

Эту сводную, как и обычную в Excel, можно разместить на отдельном или на существующем листе.

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

Визуализация данных

Спарклайны

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

При этом они намного проще диаграмм: у них нет подписей данных, возможности комбинировать типы (и типов всего три в Excel).

Спарклайны появились в Excel 2010. Вставляются через вкладку «Вставка» (Insert) на ленте.

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

При выделении ячеек со спарклайнами на ленте появляется одноименная контекстная вкладка, на ней можно изменить стиль спарклайна, задать разные цвета для минимальных и максимальных значений, изменить тип спарклайна и удалить их (это команда «Очистить» на ленте — с помощью Delete спарклайны не удаляются).

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

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