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

Спарклайны (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 — мусором). Иначе говоря, лишние элементы диаграмм их совсем не украшают, а только усложняют восприятие данных.

Никаких 3D-диаграмм