Гид по SPARKLINE в Google Таблицах и Excel

SPARKLINE — функция в Google Таблицах и Excel, с помощью которой можно построить в одной ячейке мини-диаграмму: линию или столбики. Она помогает быстро оценить динамику показателей — например, рост трафика или эффективность рекламы. Разбираем, как работает инструмент и как с его помощью создать наглядные мини-графики внутри таблицы.

Что умеет SPARKLINE

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

С помощью функции можно наглядно отобразить тренды по продажам, трафику и ключевым метрикам в виде линейного графика, гистограммы, столбчатой диаграммы или диаграммы «выигрыш/проигрыш».

Синтаксис функции SPARKLINE в Google Таблицах

Как создать мини-диаграмму в Google Таблицах

SPARKLINE в Google Таблицах может построить четыре типа диаграмм:

• line — линейный график;
• bar — гистограмма или диаграмма прогресса;
• column — столбчатая диаграмма;
• winloss — диаграмма «выигрыш/проигрыш» или диаграмма «да/нет», когда есть только два результата.

Выбор типа диаграммы зависит от задачи: показать динамику, визуализировать прогресс или отразить бинарный результат. Ниже — краткая подсказка по выбору диаграмм для разных случаев.

Как построить линейный график

Линейный график строится в Google Таблицах по умолчанию:

=SPARKLINE(B2:G2)

Также его можно построить с помощью формулы с указанием типа графика, например:

=SPARKLINE(B2:G2;{«charttype»\»line»})

Функция строит линейный график по значениям в указанном диапазоне ячеек. График автоматически обновляется при изменении данных.

По умолчанию диаграмма отображается в чёрном цвете. Это можно изменить с помощью параметра “color”. Для смены цвета рядом с параметром нужно указать цвет на английском языке, например, «red»:

=SPARKLINE(B2:G2; {«charttype»\»line»;»color»\»red»})

или шестнадцатеричный код цвета, для красного это — “#FFA500”:

=SPARKLINE(B2:G2; {«charttype»\»line»;»color»\»#FFA500″})

Линейный график поменяет цвет.

Чтобы поменять толщину линии графика, нужно добавить в формулу параметр “linewidth” и указать его числовое значение. Обычно в диапазоне от 1 до 4 можно увидеть заметное увеличение в толщине линии, например:

=SPARKLINE(B2:G2;{«charttype»\»line»;»linewidth»\4})

Если есть два столбца значений, то для построения линейного графика можно настроить отображение только определенного диапазона по горизонтальной оси Х. 

Например, есть таблица с днями (столбец A) и количеством посещений сайта (столбец B). Чтобы построить график только с 5 по 11 дни, используются параметры xmin и xmax:

=SPARKLINE(A2:B16;{«xmin»\5;»xmax»\11})

В результате в одной ячейке создаётся мини-график, который наглядно показывает динамику посещений только за выбранный интервал.

Аналогично оси X, в функции SPARKLINE можно ограничить диапазон значений по вертикальной оси Y. Для этого используются параметры ymin и ymax.

Например, чтобы построить график только для значений от 100 до 300 посещений сайта, используется формула:

=SPARKLINE(A2:B16;{«ymin»\100;»ymax»\300})

Это помогает визуально определить, в какие дни показатели выходили за пределы указанного диапазона.

Как работать с пустыми ячейками

Иногда в диапазоне данных встречаются пустые ячейки — например, если данные ещё не собраны полностью. В SPARKLINE с помощью параметра «empty» можно настроить, как эти пустые значения будут отображаться на графике: как ноль или игнорироваться.

Допустим, нужно показать на графике, сколько лидов пришло с нового лендинга. Но из-за проблем с формой для отклика, в некоторые дни лиды не поступали и ячейки со значениями остались пустыми. Чтобы показать или скрыть эти пустые значения на графике используются параметры «zero» или «ignore».

Формула, чтобы отобразить пустую ячейку как 0 на графике:

=SPARKLINE(B2:B14;{«empty»\»zero»})

На мини-графике видно, где были провалы в получении лидов — линия графика опускается до нуля.

А вот формула, чтобы пропускать пустые ячейки без отображения на линии графика:

=SPARKLINE(B2:B21;{«empty»;»ignore»})

Линия графика соединяет только существующие значения, пропускает «провалы» без резких спадов к нулю.

Как выбрать между «zero» или «ignore»:

• Используйте «zero», если важно показать, что в определённые дни значений действительно не было. Например, чтобы увидеть дни без заявок, заказов или активности.
• Используйте «ignore», если пустые ячейки означают отсутствие данных, а не нулевое значение. Например, если данные ещё не собраны или лиды просто не должны были поступать в этот период.

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

Как обрабатывать текстовые ячейки в SPARKLINE

Иногда в таблицах встречаются ячейки с текстом. Благодаря параметру «nan» их можно преобразовать в ноль или игнорировать — для этого соответственно используются опции “convert» или “ignore”.

Например, если в отчёте по лидам с лендинга стоят пометки с текстом «ожидаем» или «нет данных» — параметр “nan” помогает отобразить эти дни как ноль или просто игнорировать их. Так график будет показывать только реальные заявки.

Формула для преобразования ячеек с текстом в ноль:

=SPARKLINE(B2:B14;{«nan»\»convert»})

На графике текстовые ячейки отобразились как нулевые значения — линия графика при некоторых значениях опустилась до нуля.

Если нужно, чтобы текстовые ячейки вообще не влияли на график, используется формула:

=SPARKLINE(B2:B21;{«nan»;»ignore»})

В результате линия графика соединяет только реальные числовые значения и пропускает текст без отображения провалов.

Как отобразить график справа налево в SPARKLINE

Параметр rtl в SPARKLINE позволяет изменить направление построения графика — отображать данные не слева направо, как обычно, а справа налево. Для этого нужно добавить в формулу параметр “rtl” и установить значение “TRUE” или “ИСТИНА”. Чтобы построить график по стандартному направление, нужно ввести значение — “FALSE” или “ЛОЖЬ”.

Параметр “rtl” можно использовать, например, чтобы акцентировать внимание на свежих результатах рекламной кампании — отображать сначала новые данные, а потом старые.

Формула для отображения графика справа налево:

=SPARKLINE(B3:B10; {«rtl»\ ИСТИНА})

На мини-графике новые данные отображаются первыми. Чтобы построить график в обратном направлении, слева направо — используется формула:

=SPARKLINE(B3:B10; {«rtl»\ ЛОЖЬ})

График отображает данные в обычном порядке: сначала идут старые значения, затем новые.

Как составить гистограмму

Гистограмма или тип спарклайна “bar” визуализирует данные в виде горизонтальной полоски в ячейке. Такой формат подходит для сравнения показателей, например, результатов А/В теста или выполнения KPI.

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

Для этого нужно указать тип графика “bar” и использовать параметр “max”, который задает максимальное значение — предел шкалы. Все показатели будут сравниваться с этим значением, и соответственно будет заполняться цветовая шкала.

Для данного примера установили максимум шкалы на уровне 200 кликов:

=SPARKLINE(B2; {«charttype»\»bar»; «max»\200})

После растягивания формулы на остальные ячейки, формируется несколько гистограмм. Они иллюстрируют насколько показатели кликов близки к максимальному значению.

С помощью гистограммы в SPARKLINE легко понять, какие посты приносят больше кликов. В примере видно, что объявления с видео и текстом оказались самыми эффективными.

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

Например, нужно сравнить, сколько пользователей пришло из платного трафика (PPC) и сколько из органического поиска (SEO) для разных посадочных страниц. В этом случае в SPARKLINE можно задать общий максимум равный сумме двух показателей — количества посещений из платного и органического трафика — а также настроить отдельные цвета.

=SPARKLINE(B2:C2; {«charttype»\»bar»; «max»\ СУММ(B2:C2); «color1″\ «#FFA500»; «color2″\ «#228B22»})

В ячейке строится гистограмма, которая показывает, какая доля пользователей пришла с платного трафика, а какая — с органического.

При необходимости можно сравнивать и больше компонентов — главное задать для каждого параметра свой цвет. Если среди значений попадаются пустые ячейки или текст, можно задать параметры “empty” или “nan”, аналогично линейным графикам.

Как создать столбчатую диаграмму

Столбчатая диаграмма типа «column» в SPARKLINE помогает быстро отобразить данные как серию вертикальных столбцов внутри одной ячейки. Это удобно для показа динамики по неделям, месяцам или другим периодам прямо в компактном формате таблицы.

Чтобы построить столбчатую диаграмму — нужно ввести в ячейку формулу:

=SPARKLINE(B2:G2; {«charttype»\»column»})

После этого в ячейке появляется диаграмма по заданным значениям.

По умолчанию диаграмма отображается в чёрном цвете. Это можно изменить с помощью параметра “color”. Рядом с ним нужно указать нужный цвет на английском языке, например, «red»:

=SPARKLINE(B2:G2; {«charttype»\»column»;»color»\»red»})

или шестнадцатеричный код цвета, для красного это  — “#FFA500”:

=SPARKLINE(B2:G2; {«charttype»\»column»;»color»\»#FFA500″})

В результате столбики диаграммы будут отображаться в выбранном цвете.

Дополнительно можно задать отдельный цвет для столбцов с максимальным и/или минимальным значением. Это можно сделать с помощью параметров:

• lowcolor — задает цвет для минимального значения на графике;
• highcolor — задает цвет для максимального значения на графике.

Например, все показатели должны быть красного цвета, минимальные значения нужно выделить синим, а максимальные — зелёным. Формула будет выглядеть так:

=SPARKLINE(B2:G2;{«charttype»\»column»;»color»\»red»;»lowcolor»\»blue»;»highcolor»\»green»})

Цветовые акценты на минимальных и максимальных значениях помогают быстро увидеть на диаграмме, где были достигнуты худшие и лучшие результаты.

Для столбчатой диаграммы можно дополнительно настроить цвета отдельных столбцов:

• firstcolor — цвет первого столбца;
• lastcolor — цвет последнего столбца;
• negcolor — цвет всех столбцов с отрицательными значениями.

Например, компания анализирует чистую прибыль по месяцам за первое полугодие. Чтобы видеть не только убытки, но и быстро выделить начало и конец периода, можно использовать цветовые акценты:

• первый месяц (январь) выделяется оранжевым цветом;
• последний месяц (июнь) выделяется зелёным;
• отрицательные значения (убытки) выделяются красным.

Остальные столбцы остаются по умолчанию чёрного цвета. Формула будет выглядеть так:

=SPARKLINE(B2:G2; {«charttype»\»column»; «firstcolor»\»orange»; «lastcolor»\»green»; «negcolor»\»red»})

В контексте нашего пример строится столбчатая диаграмма, где первый столбец выделяется оранжевым цветом, последний — зелёным, а все отрицательные значения отображаются красным. Все остальные значения остаются в стандартном цвете (чёрном). 

Цветовые акценты помогают быстрее ориентироваться в данных: сразу видно, с каких результатов всё началось, чем период закончился и в какие месяцы были убытки.

Как отобразить горизонтальную ось и задать ей цвет

По умолчанию столбчатая диаграмма не показывает горизонтальную ось, которая обозначает уровень нуля. Ее можно включить с помощью параметра «axis» и дополнительно задать цвет через «axiscolor».

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

Чтобы добавить ось на столбчатую диаграмму, нужно указать значение параметра «axis» ИСТИНА (TRUE). Если нужно скрыть ось, указывается значение ЛОЖЬ (FALSE). Параметр «axiscolor», который задает цвет оси, действует только при значении «axis»\»TRUE». Если ось не включена, ее цвет не изменится.

Чтобы добавить горизонтальную ось и выделить ее, к примеру, красным цветом — нужно задать формулу:

=SPARKLINE(B2:H2; {«charttype»\»column»; «axis»\ИСТИНА; «axiscolor»\»red»})

С помощью такой мини-диаграммы можно быстро отследить положительные и отрицательные значения показателей в течение недели.

Как создать диаграмму «выигрыш/проигрыш»

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

Диаграмма «выигрыш/проигрыш» строит серию вертикальных столбиков одинаковой высоты. Для всех положительных значений в заданном диапазоне данных столбики строятся вверх, а для отрицательных — вниз. Нулевые значения обычно отображаются как пробел или маленькая отметка на оси.

Допустим, нужно отследить изменение количества подписчиков в Telegram-канале по дням недели. Подписки характеризуются положительными значениями, а отписки — отрицательными. Для построения диаграммы «выигрыш/проигрыш» используется формула:

=SPARKLINE(B2:H2; {«charttype»\»winloss»})

По умолчанию столбцы диаграммы чёрного цвета. Это можно скорректировать и задать цвета как для всей диаграммы, так и отдельно для «выигрыша» — положительных значений, и «проигрыша» — отрицательных значений. Также можно отобразить ось и задать ей цвет:

=SPARKLINE(B2:G2; {«charttype»\ «winloss»; «axis»\ ИСТИНА; «color»\ «blue»; «negcolor»\ «red»; «axiscolor»\ «grey»})

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

Такой вариант диаграммы удобно использовать для быстрой оценки выполнения планов и отслеживания динамики показателей.

Как построить спарклайн по рваному диапазону

Обычные диапазоны строятся подряд по строке или по столбцу. Их можно выделить одним движением мыши. Но иногда приходится  строить диаграммы по ячейкам, которые находятся в разных столбцах или строках.

Допустим, нужно построить диаграмму по показателям подписок и отписок на канал не за всю неделю, а за конкретные дни: понедельник, среду и пятницу. Для этого можно вручную собрать эти ячейки в отдельном месте и задать формулу построения SPARKLINE. Либо использовать фигурные скобки и через точку с запятой перечислить конкретные ячейки, по которым нужно построить диаграмму:

=SPARKLINE({B2;D2;F2}; {«charttype»\»column»})

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

Как сделать мини-график в Excel

Чтобы создать миниатюрные графики в Excel, формулы не нужны. Для этого используют настройки интерфейса. Нужно выделить диапазон ячеек с данными, нажать на быстрое меню и выбрать «Спарклайны». На выбор откроются варианты диаграмм, которые после нажатия будут построены в соседней ячейке.   

В меню можно поменять график на другой тип, например, на гистограмму. Нажмите на ячейку с графиком и в меню «Спарклайн» в подразделе «Тип» выберите гистограмму. График автоматически заменится.  

При желании можно выделить цветом максимальные, минимальные, промежуточные и отрицательные значения. В отличие от Google Таблиц здесь не нужно вводить формулу, чтобы указать акцентные точки и задать им цвет. График автоматически подсвечивает выбранные маркеры цветом в зависимости от стиля таблицы.

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

В настройках можно изменить стиль, цвет линии, отметить на графике или диаграмме.

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

Разгруппировка понадобится в случае, когда нужно поработать с каждой ячейкой отдельно. Например, применить к разным графикам разные стили или удалить конкретный мини-график.

Чтобы разгруппировать ячейки, нужно выделить графики, перейти в раздел меню «Спарклайн» и выбрать «Разгруппировать». В том же разделе при необходимости можно снова сгруппировать ячейки.

Мини-графики могут быть более информативными, если в них добавить оси. Оси помогут оценить реальные пропорции графика и определить, где находятся нулевые значения.

Чтобы добавить ось, нужно выделить мини-график, зайти в раздел «Спарклайн» в меню, выбрать опцию «Ось» и поставить галочку на «Показать ось».      

Ось нужна, если данные содержат положительные и отрицательные значения — горизонтальная линия помогает отследить их на графике.

Если на рабочем листе есть скрытые или пустые ячейки, то Excel по умолчанию их игнорирует — мини-график строится только на видимых данных. Иногда нужно, чтобы график учитывал все значения, даже если часть ячеек скрыта фильтрами или вручную. Чтобы учесть эти значения, нужно выделить ячейку с мини-графиком, зайти в меню «Спарклайн», выбрать пункт «Скрытые и пустые ячейки» и настроить, как именно учитывать скрытые данные: пропускать их или строить график, как если бы данные были видимыми.

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

Так можно контролировать, как пустые и скрытые строки будут отображаться на мини-графике.

Дополнительные возможности SPARKLINE

SPARKLINE можно комбинировать с другими функциями Google Таблиц. Например, можно создать спарклайны для отображения динамики цен акций крупных технологических компаний за последние 30 дней. 

Для этого используется функция GOOGLEFINANCE, которая извлекает данные о ценных бумагах. Формула выглядит так:

=SPARKLINE(QUERY(GOOGLEFINANCE($B2; «price»; СЕГОДНЯ()-30; СЕГОДНЯ(); «DAILY»); «select Col2»; -1); {«charttype»\ «column»; «highcolor»\ «red»})

Из каких функций состоит формула помимо SPARKLINE и GOOGLEFINANCE:

• «price» — указывает, что мы запрашиваем данные о цене;
• TODAY()-30 — вычисляет дату 30 дней назад от сегодняшней;
• TODAY() — возвращает текущую дату;
• «DAILY» — запрашивает ежедневные данные;
• QUERY — эта часть формулы возвращает таблицу с датами и ценами акций за последние 30 дней;
• «select Col2». По умолчанию формируется две колонки: с датами и ценами акции. А эта функция выбирает второй столбец, который содержит только цены акций;
• -1 — указывает, что заголовок не включается в выборку.

Красным цветом в диаграмме выделена максимальная цена за акцию в этот периоде.

Есть и другие полезные функции, которые можно комбинировать со SPARKLINE:

IMPORTRANGE. Позволяет импортировать диапазон ячеек из другой таблицы Google. Это полезно, когда данные для SPARKLINE находятся в другом файле. 

Пример формулы: =SPARKLINE(IMPORTRANGE(«https://docs.google.com/spreadsheets/d/…/edit#gid=0»; «Лист1!B2:B10»); {«charttype»\»line»})

Эта формула импортирует данные из диапазона B2:B10 листа «Лист1» другой таблицы и строит по ним линейный SPARKLINE.

SORT. Сортирует значения в диапазоне ячеек по возрастанию или убыванию. Например, можно отсортировать диапазон A2:B16 по второму столбцу (B) в убывающем порядке и построить столбчатую диаграмму SPARKLINE:

=SPARKLINE(SORT(B2:B16; 1; ЛОЖЬ); {«charttype»\»column»})

Значение 1 указывает на первый и в данном случае единственный столбец диапазона B2:B16, а функция ЛОЖЬ выстраивает значения в убывающем порядке.

FILTER. Фильтрует значения в диапазоне ячеек на основе заданных условий. Например, по этой формуле можно отсортировать диапазон B2:B10, оставить только значения, соответствующие значению «Январь» в столбце A и построить линейный SPARKLINE:

=SPARKLINE(FILTER(B2:B10; A2:A10 = «Январь»); {«charttype»\»line»})

GROWTH. Превращает последовательность в экспоненциально растущий ряд. Например, GROWTH(B2:B10; A2:A10) анализирует фактические данные посещаемости (B2:B10) по неделям (A2:A10). На основе этих данных функция рассчитывает идеальную экспоненциальную кривую, которая наилучшим образом соответствует этим точкам. По этой формуле можно построить линейный SPARKLINE, который показывает экспоненциальный тренд на основе данных из B2:B10 (y) и A2:A10 (x):

=SPARKLINE(GROWTH(B2:B10; A2:A10); {«charttype»\»line»})

По этой формуле SPARKLINE строит график не по реальным, немного неровным данным из B2:B10, а по идеальным, сглаженным точкам, которые рассчитала функция. Это помогает оценить общий тренд и не отвлекаться на мелкие колебания значений.

SEQUENCE. Генерирует последовательность чисел с растущим шагом. Это помогает создавать набор данных для визуализации без необходимости вручную вводить все значения.

Допустим, маркетолог планирует бюджет на контекстную рекламу на следующий год — 12 месяцев. Он решает начать с бюджета в 100 000 рублей в первом месяце и планирует увеличивать его на 5 000 рублей каждый последующий месяц для масштабирования кампаний:

=SPARKLINE(SEQUENCE(12; 1; 100; 5); {«charttype»\»column»})

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

ARRAYFORMULA. Позволяет применять формулы к диапазонам ячеек, а не к отдельным ячейкам. Например, по этой формуле можно умножить значения из ячеек диапазонов A2:A10 на B2:B10 и построить линейный SPARKLINE на основе результатов:

=ARRAYFORMULA(SPARKLINE(A2:A10 * B2:B10; {«charttype»\»line»}))

UMINUS. Меняет знак числа на противоположный — делает положительное число отрицательным и наоборот. К примеру, с помощью этой функции можно инвертировать данные о прибылях и убытках в диапазоне A2:A10 и построить столбчатый SPARKLINE. Благодаря UMINUS и параметрам цвета, исходные убытки (ставшие положительными) будут показаны красным над осью, а прибыль (ставшая отрицательной) — зеленым под осью:

=SPARKLINE(ARRAYFORMULA(UMINUS(A2:A10)); {«charttype»\»column»; «color»\»red»})

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

В каких ситуациях SPARKLINE не подходит

Сложные диаграммы. Если нужно построить объёмный график с подписями осей, легендами, числовыми значениями — лучше использовать стандартные диаграммы Google Таблиц или Excel. SPARKLINE нужен для быстрой визуализации, а не для глубокого анализа.

Мобильные устройства. В мобильных версиях Google Таблиц и Excel нельзя создавать или редактировать мини-графики. Но они корректно отображаются — можно просматривать уже готовые диаграммы на смартфоне или планшете.

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

Все функции для работы со SPARKLINE в Google Таблицах

Вместо заключения — список всех функций, которые доступны в SPARKLINE.