ПРОЕКТ РАЗРАБОТЧИКОВ БИЗНЕС АНАЛИТИКИ

SPARKLINE. Маленький помощник с большими возможностями

Олег Коваль

Автор статьи и разработчик HelpExcel

Функция SPARKLINE позволяет выводить небольшие графики в пределах одной ячейки. Это помогает в представлении рядов цифр, а настройки дают возможность изменить её внешний вид и сделать информацию на листе гармоничной и легко воспринимаемой.

Открыть таблицу

Разбираемые функции можно увидеть в таблице:

В общем виде синтаксис функции выглядит так: =SPARKLINE(data, [options])

data – диапазон или массив, содержащий данные для построения диаграммы. Может содержать одну строку или один столбец для построения графиков, также может принимать два столбца или две строки для построения фигур.

options [НЕОБЯЗАТЕЛЬНО] – диапазон или массив дополнительных параметров и связанных с ними значений, используемых для настройки диаграммы.

Диапазоны options должны занимать по две ячейки. В первой ячейке содержится название параметра, а во второй – его значение.

Пример 1: =SPARKLINE(A1:A) или =SPARKLINE(A1:1)
Самый простой способ применения функции, где её единственный параметр - диапазон с числами.

Пример 2:
RU: =SPARKLINE(R3:Z3; { "charttype"\"column"; "highcolor"\"red" } )

ENG: =SPARKLINE(R3:Z3, { "charttype", "column"; "highcolor", "red" } )

Возможно вы сталкивались с ситуацией, когда формулы из интернета отказывались работать в вашем файле. Обратите внимание на эти две записи: в русской и английской версии Гугл таблиц синтаксис формул различается. Это касается всех функций, не только SPARKLINE. Здесь и далее мы будем рассматривать русскоязычную версию.

Где это возможно, цвета можно задавать как с помощью слов, так и в шестнадцатеричном формате: "color"\"#FF0000"

SPARKLINE может иметь четыре разных вида для отображения данных, каждый из которых имеет свои особенности и настройки. Рассмотрим их подробнее.

Линейная диаграмма

Пример 3: =SPARKLINE($B$4:$B23;{"charttype"\"line"})

charttype: Line - линейная диаграмма, используется по умолчанию.

Столбчатая диаграмма

Пример 4: =SPARKLINE($B$2:$B26;{"charttype"\"column";"highcolor"\"red"})

charttype: Column - столбчатая диаграмма. Обладает широким набором функций. Можно задать цвет максимального значения, минимального, первого и последнего, включить нулевую линию и так далее:

lowcolor - цвет минимального значения

highcolor - цвет максимального значения

firstcolor - цвет первого значения

negcolor - цвет отрицательных значений

axis - "axis"\true включает центральную линию

axiscolor - задает цвет центральной линии,
например =SPARKLINE($B$2:$B35;{"charttype"\"column";"axis"\true;"axiscolor"\"red"})

"ymax"\10 - ограничение на отображаемые значения.

"ymin"\0 - ограничение на отображаемые значения.

=SPARKLINE($B$2:$B38;{"charttype"\"column";"ymin"\0;"ymax"\5})

"RTL"\true - инверсия графика.

Некоторые из этих ключей можно использовать и в других типах диаграмм.

Диаграмма прогресса

Тип спарклайна "bar" значительно отличается от предыдущих и хорошо подходит для процентов, долей, прогресса и так далее.

=SPARKLINE(N32:O32;{"charttype"\"bar";"max"\sum(N32;O32);"color1"\"blue";"color2"\"#999999"})

Цвет первого значения задается опцией color1, второго - color2.

=SPARKLINE(B2;{"charttype"\"bar";"max"\30})

Опция Max задает максимальное значение, предел шкалы.

Диаграмма ДА/НЕТ (winloss)

Эта диаграмма показывает только два варианта - положительные и отрицательные значения.

=SPARKLINE(F36:M36;{"charttype"\"winloss";"color"\"blue";"highcolor"\"red"})

Однако этой диаграмме также можно указать цвета, как и в предыдущих примерах. На рисунке видно, что максимальное число выделено красным - в нашем случае это шесть. Можно выделить и минимальное значение опцией lowcolor, или все отрицательные значения: negcolor

Некоторые детали

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

=SPARKLINE(F38:O38;{Q38\R38;Q39\R39})

Тот же пример, но опции и их ключи указаны как диапазоны, которые можно масштабировать до нужных размеров: =SPARKLINE(F38:F;{Q38:Q43\R38:R43})

В качестве входных данных можно указывать не диапазон на листе, но и результат таких формул, как GOOGLEFINANCE или IMPORTRANGE

=SPARKLINE(QUERY(GOOGLEFINANCE("GOOG";"price";TODAY()-60;TODAY();"DAILY");"select Col2";-1);{"charttype"\"column";"color"\"blue";"highcolor"\"red"})

С помощью QUERY берем второй столбец данных из формулы GOOGLEFINANCE и они становятся данными для SPARKLINE.

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

Да, это всё еще спарклайн. Вот его формула:
=SPARKLINE(U2:V6;{"color"\"green";"linewidth"\2})

Или вот еще вариант:

=SPARKLINE(J2:K100;{"color"\"green";"linewidth"\2})

С помощью функций SIN и COS создать координаты для круга, и тогда можно одной формулой сделать диаграмму вида "пирог" или даже часы реального времени:

https://www.`.com/spreadsheets/google-sheets-formula-clock/

Или вот еще вариант необычной формулы
=ArrayFormula(SPARKLINE(UMINUS(GROWTH(SEQUENCE(10;1;1;1000)));{"charttype"\"column";"color"\"red"}))

которая выглядит так:

Интересная формула SPARKLINE, не правда ли?

На этом все!

Еще больше полезных материалов вы можете найти в нашем Telegram-канале:

Telegram-канал

Хотите обсудить свой проект?

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

Для обсуждения вашей задачи напишите нам в WhatsApp. Проведем аудит и предложим решение.

Написать в WhatsApp