ПРОЕКТ РАЗРАБОТЧИКОВ БИЗНЕС АНАЛИТИКИ
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 задает максимальное значение, предел шкалы.
Сэкономьте время и организуйте учет в компании на индивидуальной консультации с нашим специалистом.
Хотите обсудить свой проект?
Если вы задумываетесь об оптимизации процессов в компании и внедрении IT-систем, оставьте заявку на консультацию и мы свяжемся с вами для обсуждения деталей.
Диаграмма ДА/НЕТ (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-канале:
Хотите обсудить свой проект?
