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" } )
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.
Проведем аудит и предложим решение.
Обзоры наших работ
На основе настоящих кейсов мы создаем готовые шаблоны таблиц
и публикуем в открытый доступ.
и публикуем в открытый доступ.
Обзоры наших работ
На основе настоящих кейсов мы создаем готовые шаблоны таблиц
и публикуем в открытый доступ.
и публикуем в открытый доступ.
© 2016-2022 Бизнес Аналитика
Используя наш сайт, вы принимаете Политику конфиденциальности персональных данных.
Использование материалов сайта возможно только с предварительного согласия правообладателей и обязательным указанием ссылки на helpexcel.pro
Используя наш сайт, вы принимаете Политику конфиденциальности персональных данных.
Использование материалов сайта возможно только с предварительного согласия правообладателей и обязательным указанием ссылки на helpexcel.pro
НАПРАВЛЕНИЯ
КЕЙСЫ И СТАТЬИ
ДРУГИЕ РЕСУРСЫ
НАПРАВЛЕНИЯ
КЕЙСЫ И СТАТЬИ
ДРУГИЕ РЕСУРСЫ
БИЗНЕС АНАЛИТИКА
© 2016-2022 Бизнес Аналитика
Используя наш сайт, вы принимаете Политику конфиденциальности персональных данных.
Использование материалов сайта возможно только с предварительного согласия правообладателей и обязательным указанием ссылки на helpexcel.pro
Используя наш сайт, вы принимаете Политику конфиденциальности персональных данных.
Использование материалов сайта возможно только с предварительного согласия правообладателей и обязательным указанием ссылки на helpexcel.pro