Нужна доработка таблицы?
Нужна доработка таблицы?
ПРОЕКТ РАЗРАБОТЧИКОВ БИЗНЕС АНАЛИТИКИ
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, не правда ли?
На этом все!
Хотите обсудить свой проект?
Если Вам понравился наш кейс и Вы бы хотели проконсультироваться с нашей командой по поводу внедрения подобного решения в Вашу компанию, оставьте заявку и мы свяжемся с Вами в ближайшее время.
Для обсуждения вашей задачи напишите нам в WhatsApp. Проведем аудит и предложим решение.
Чтобы вставить мини-график прямо в ячейку Google Таблиц, введите формулу =SPARKLINE(диапазон) — например =SPARKLINE(A2:F2). По умолчанию строится линия; тип меняется вторым аргументом-массивом: {"charttype"\"column"} — столбцы, {"charttype"\"bar"} — полоса-шкала, {"charttype"\"winloss"} — «выигрыш/проигрыш». В том же массиве парами «имя-значение» настраиваются цвет («color», «negcolor»), пределы оси («max», «ymax», «ymin») и толщина линии («linewidth»). Важно: в русской локали внутри фигурных скобок пары разделяются «;», а имя и значение — обратной косой чертой «\» (в английской локали — запятыми).
Частые вопросы
Как сделать мини-график внутри одной ячейки в Google Таблицах?
Встаньте в пустую ячейку и введите =SPARKLINE(диапазон с числами), например =SPARKLINE(B2:B13). Функция нарисует компактный график прямо в ячейке без вставки объекта «Диаграмма». Чтобы график был виден целиком, увеличьте высоту строки и ширину столбца.
Как поменять тип спарклайна на столбцы или линию?
Тип задаётся параметром «charttype» во втором аргументе: =SPARKLINE(A2:F2; {"charttype"\"line"}) — линия (по умолчанию), «column» — вертикальные столбцы, «bar» — горизонтальная полоса-шкала, «winloss» — одинаковые столбики вверх для плюсов и вниз для минусов. В английской локали разделители внутри фигурных скобок — запятые: {"charttype","column"}.
Как изменить цвет мини-графика в SPARKLINE?
Добавьте пару с именем цвета в массив параметров. Для линии и столбцов — «color»: =SPARKLINE(A2:F2; {"charttype"\"column";"color"\"green"}). Отрицательные значения красят через «negcolor», крайние точки — «firstcolor»/«lastcolor», минимум и максимум — «lowcolor»/«highcolor». Для типа «bar» две части полосы задают «color1» и «color2».
Почему SPARKLINE не строит график или выдаёт ошибку?
Самая частая причина в русской локали — неверные разделители: внутри {} нужно ставить «;» между парами и «\» между именем и значением, иначе будет #ERROR!. Также проверьте, что в диапазоне числа, а не текст, и что имена параметров написаны точно («charttype», «color», «ymin», «ymax»). Если график «не помещается» — просто расширьте ячейку: сама формула при этом рабочая.