Настройка модели данных
Чтобы посмотреть на существующие в модели таблицы, их поля (столбцы) и настроить связи между ними, нужно перейти в режим «Представление диаграммы».
«Главная» → группа «Просмотр» → «Представление диаграммы» (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, а затем и данные в сводной таблице.
Визуализация данных
Спарклайны
Файл с примерами: Спарклайны.xlsx
Спарклайны (Sparklines) — маленькие графики в ячейках. Благодаря тому что они не занимают много места и размещаются в пределах ячейки, их можно использовать, чтобы оценить динамику большого количества рядов данных, в отличие от диаграмм.
При этом они намного проще диаграмм: у них нет подписей данных, возможности комбинировать типы (и типов всего три в Excel).
Спарклайны появились в Excel 2010. Вставляются через вкладку «Вставка» (Insert) на ленте.
В Excel всего три типа спарклайнов: график и гистограмма (подходят для оценки динамики показателей) и «выигрыш/проигрыш» (показывает только положительные, отрицательные значения и нули/пустоту, но не относительную величину этих значений, в отличие от гистограммы).
При выделении ячеек со спарклайнами на ленте появляется одноименная контекстная вкладка, на ней можно изменить стиль спарклайна, задать разные цвета для минимальных и максимальных значений, изменить тип спарклайна и удалить их (это команда «Очистить» на ленте — с помощью Delete спарклайны не удаляются).
Когда вы строите спарклайны по нескольким рядам данных, каждый спарклайн будет со своим масштабом, вертикальная ось (она не отображается) будет своя. Это значит, что не будут видны масштабы рядов относительно друг друга.
Если нужно сравнивать не только динамику в рамках каждой строки, но и значения данных относительно друг друга (в данном примере в строке 5 числа значительно меньше, чем в других), нужно включить общие для всех спарклайнов минимальное и максимальное значение по оси.
В Google Таблицах четыре типа спарклайнов (https://mif.to/x8SYx): график, гистограмма и выигрыш-проигрыш, как в Excel, а также bar (горизонтальный столбик, как гистограмма в условном форматировании Excel).
В Google Таблицах спарклайны не вставляются как диаграммы, а возвращаются функцией SPARKLINE (у нее нет названия на русском).
Дополнительные примеры в Google Таблицах:
Спарклайн с условием https://t.me/google_sheets/519
Динамический спарклайн
https://t.me/google_sheets/528: выбираем период, точку отсчета и цвет спарклайна и максимальной точки (цвет — на русском языке)
Спарклайн с расчетом данных прямо в формуле
https://t.me/google_sheets/610
Динамический спарклайн
Чтобы построить динамический спарклайн, который будет меняться автоматически при изменении одного или нескольких параметров (например, продолжительности периода, за который необходимо визуализировать данные), можно воспользоваться функцией СМЕЩ / OFFSET, с которой мы уже знакомы и которая позволяет формировать ссылки на диапазоны по их параметрам (напомним: начальная точка, отступ от нее, высота и ширина).
Необходимо сформировать такую функцию и затем сохранить ее в виде имени в книге Excel, чтобы затем использовать это имя как источник данных для спарклайна.
Шаг 1. Вводим функцию СМЕЩ, которая будет формировать динамический диапазон (например, с переменной продолжительностью).
Шаг 2. Копируем эту функцию, заходим в «Диспетчер имен» (Name Manager, Ctrl + F3), создаем новое имя и вводим в поле «Диапазон» (Refers To) функцию. Имя задаем на свой вкус (в примере — «Спарклайн»). Теперь за этим именем в этой книге Excel скрывается наша функция, которая, в свою очередь, выдает диапазон переменной высоты (в зависимости от того, что введено в ячейку H1 с количеством дней).
Шаг 3. Создаем спарклайн и в качестве диапазона данных (Data Range) указываем созданное ранее имя.
Теперь спарклайн будет автоматически перестраиваться при изменении продолжительности периода в ячейке H1.
Диаграммы
Файл с примерами: Диаграммы.xlsx
Data-Ink — меньше визуального мусора
Эдвард Тафти (кстати, термин «спарклайн» принадлежит ему), главный эксперт в мире информационного дизайна и автор классических книг по теме, предложил коэффициент Data-Ink (данные-чернила): соотношение «чернил», затраченных на график в целом, и «чернил», которые действительно отображают данные. Чем он выше, тем лучше (меньше «чернил» потрачено впустую на то, что Тафти называет chartjunk — мусором). Иначе говоря, лишние элементы диаграмм их совсем не украшают, а только усложняют восприятие данных.
В подавляющем большинстве книг по Excel и визуализации данных авторы сходятся в том, что объем — главное зло в диаграммах: он уже никого не впечатлит из ваших слушателей и читателей, а вот исказить данные вполне может. Используйте двумерные диаграммы, чтобы доносить информацию точно.
Стремитесь к тому, чтобы читатель вашего отчета/диаграммы мог сразу понять, что имеется в виду и о чем говорит ваша визуализация. Сложность вводит нас в ступор (и к тому же сложность вокруг нас только возрастает во всем), так что иначе вы рискуете потерять вашего читателя. Здорово, если у каждой диаграммы есть один посыл, одна идея, которую вы хотите донести до слушателя, и надписи / элементы / сам макет диаграммы позволяют ее быстро и однозначно считать.
Диаграмма — визуальное представление числовых данных в Excel. Диаграммы бывают внедренными и расположенными на отдельном листе.
Внедренные диаграммы находятся на графическом слое обычного листа Excel — поверх ячеек. Диаграммы можно передвигать (напомню, что с зажатой клавишей Alt диаграммы и другие объекты графического слоя «приклеиваются» к границам ячеек), можно менять их размеры.
Быстро создать внедренную диаграмму можно с помощью клавиш Alt + F1 (Fn + + F1). Правда, создается с помощью горячих клавиш определенный тип — гистограмма.