SPARKLINE. Маленький помощник с большими возможностями
ПРОЕКТ РАЗРАБОТЧИКОВ HELPEXCEL
Олег Коваль
Автор статьи и разработчик HelpExcel
Функция SPARKLINE позволяет выводить небольшие графики в пределах одной ячейки. Это помогает в представлении рядов цифр, а настройки дают возможность изменить её внешний вид и сделать информацию на листе гармоничной и легко воспринимаемой.
В общем виде синтаксис функции выглядит так: =SPARKLINE(data, [options])
data – диапазон или массив, содержащий данные для построения диаграммы. Может содержать одну строку или один столбец для построения графиков, также может принимать два столбца или две строки для построения фигур.
options [НЕОБЯЗАТЕЛЬНО] – диапазон или массив дополнительных параметров и связанных с ними значений, используемых для настройки диаграммы.
Диапазоны options должны занимать по две ячейки. В первой ячейке содержится название параметра, а во второй – его значение.
Пример 1: =SPARKLINE(A1:A) или =SPARKLINE(A1:1) Самый простой способ применения функции, где её единственный параметр - диапазон с числами.
Пример 2: RU: =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" значительно отличается от предыдущих и хорошо подходит для процентов, долей, прогресса и так далее.
Цвет первого значения задается опцией 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
С помощью QUERY
берем второй столбец данных из формулы GOOGLEFINANCE и они становятся данными для SPARKLINE.
Отдельно хочу остановиться на самом первом типе спарклайна, используемом по умолчанию. Дело в том, что тип line в качестве диапазона данных может принимать не только один столбец (или строку), но и два столбца (или две строки), образуя пары чисел, которые становятся координатами, по которым рисуется линия.
Да, это всё еще спарклайн. Вот его формула: =SPARKLINE(U2:V6;{"color"\"green";"linewidth"\2})