Как сделать спарклайн в гугл таблицах

Обновлено: 07.07.2024

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

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

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

Прикрепляю небольшой видеообзор итогового решения, что бы было понятней о чем речь)

Вот ссылка на папку с таблицами, которые будет описаны далее.

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

И есть таблица руководителя, содержащая форму отчета.

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

Для начала нужно сделать так, что бы данные, которые водятся в таблицы сотрудников появлялись в таблице руководителя.

Для этого создаем в таблице руководителя ровно столько листов, сколько таблиц сотрудников. Данные будем подтягивать формулой IMPORTRANGE()

Посмотрите короткий обзор применения формулы в данной ситуации.

Подробнее о формуле:

Обязательно заключайте аргументы в кавычки!

Пример функции:

Для самопроверки воспользуйтесь готовой формулой

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

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

Для этого хорошо подходить формула СУММЕСЛИМН()

Подробнее о формуле:

=СУММЕСЛИМН(диапазон суммирования;диапазон критерия 1; критерий 1; диапазон критерия 2; критерий 2;…)

И эту формулу нужно приписать относительно каждой таблицы.

Вид формулы в ячейке отчета:

Про формулу СУММЕСЛИМН и про то как ее использовать для разных вычислений напишу статью немного позже.

А пока прикрепляю запись вебинара, где мы очень подробно разбираем IMPORTRANGE, СУММЕСЛИМН, ЕСЛИ, FILTER для создания модели отчета.

Если хотите получить записи курса по Гугл таблицам, то можно посмотреть информацию на этой страничке.

Какой-то велосипед из костылей и желудей. Вы про СУБД слышали, не?

Конечно слышал) мы этим и занимаемся!
Суть статьи в том, что бы каждый предприниматель смог простым способом настроить себе отчетность. Практика показывает, что большинство пользователей не знают элементарных вещей.

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

Не все могут и хотят делать что то сложное. Да возможно это нецелесообразно. Есть простые и общедоступные инструменты.

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

Например? Возможно читателям статьи будет интересно.

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

Тут очевидно, что без разработчиков не обойтись! Я писал выше о цели статьи.

query builder
база данных
Специалист/10
Для того, что бы получить что то нужна функциональность, которая будет отправлять запросы в базу.
Люди это интерфейсом называют.
Тут очевидно, что без разработчиков не обойтись! Я писал выше о цели статьи.
Кому очевидно? Специалистам хелпэксел.про? Ну такое. Мне вот для нажатия далее-согласен-далее-далее-окей разработчик не нужен, и это всё ещё проще костылей в табличном редакторе с очень условной реляционностью.
Да в целом вы можете к нам на сайт перейти и будет понятно вообще чем
мы занимаемся
Пытаетесь продавать таблицы в экселе и на 40% не работающих чат-ботов.
и почему имеем в этом экспертность
Скорее экспертность вас имеет.

Это рассуждения человека далекого от реальности) спасибо за то, что уделили столько времени! Любая критика очень даже приемлема.

Практического применения, тем более массового, инструментов, которые вы привели нет!

Загрузка отчета

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

Пример отчета Яндекс.Метрики по источникам трафика с визитами и достижениями целей.

Пример отчета Яндекс.Метрики по источникам трафика с визитами и достижениями целей.

Так уже выглядит отчет, выгруженный из Яндекс.Метрики и загруженный в Google Spreadsheets

Так уже выглядит отчет, выгруженный из Яндекс.Метрики и загруженный в Google Spreadsheets

Подобные отчеты стандартизированные, создаются по одинаковому шаблону, поэтому при обновлении (загрузке данных за новый период) все останется на своих местах. И правильно настроенные формулы/диаграммы на соседнем листе будут правильно срабатывать каждый раз, когда нужно актуализировать информацию.

К сожалению, нет прямой интеграции между Google Таблицами и Яндекс.Метрикой, приходится работать руками. Но для работы с GA есть очень удобное расширение — Google Analytics Spreadsheet Add-on. С ним можно строить и актуализировать отчеты прямо в гугл-таблицах.

Вариант без формул — простая диаграмма

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

Создание и настройка линейчатой диаграммы в Google Spreadsheets, которая может заменить классическую воронку

Создание и настройка линейчатой диаграммы в Google Spreadsheets, которая может заменить классическую воронку

Вариант сложнее, но интереснее — спарклайны

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

Функция SPARKLINE принимает два аргумента:

Массив — это как бы виртуальная таблица. В данном случае у нее один столбец: точка с запятой ‘;’ показывает, что значения располагаются сверху вниз, вертикально. А если обратный слеш ‘\’, то наоборот — значения идут по горизонтали, в строку. То есть массивы могут быть и многомерными вроде \>, где в первой строке идут 1 и 3, во второй — 2 и 4.

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

Самих опций довольно много. Ключевой параметр charttype устанавливает тип диаграммы: line — для графика (стоит по умолчанию, если не задавать этот параметр), bar — для гистограммы, column — для столбчатой диаграммы, winloss — для столбчатой диаграммы с отрицательными и положительными результатами. (В данном случае нужен именно bar.)

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

  • max — устанавливает максимальное значение по горизонтальной оси;
  • color1 — задает первый цвет столбцов;
  • color2 — соответственно, второй;
  • empty — задает значения пустых ячеек (возможные варианты: zero – ноль, ignore – игнорировать);
  • nan — устанавливает значения для ячеек с нечисловыми данными (возможные варианты: convert – конвертировать, ignore – игнорировать);
  • rtl — указывает, если нужно отображать данные на диаграмме справа налево (true – да, false – нет).

Для построения воронки хватит параметров color1 и color2. Максимальное значение тоже нужно, но это можно сделать с помощью функции max, а не параметра, или еще проще — через ссылку на соответствующую ячейку, так как в данном примере всегда известно, где находится то самое максимальное значение.

Итак, для визуализации — вышеуказанного примера отчета из Яндекс.Метрики — понадобятся четыре спарклайна: по одному на каждый шаг. (Для удобства лучше создать их на соседнем листе, просто ссылаясь на данные первого листа, где располагается выгруженный отчет.)

Для первой диаграммы можно написать такую формулу: =SPARKLINE(;), а для последующих второе значение будет просто меняться с B7 на C7, затем D7 и E7. (То есть в четвертом, например, так: =SPARKLINE(;)).

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

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

Пример воронки с расчетными показателями конверсии рядом. А чтобы при копировании-вставке ячеек с формулами ссылки не менялись, можно надежно закрепить их такими значками доллара ‘$’.

Пример воронки с расчетными показателями конверсии рядом. А чтобы при копировании-вставке ячеек с формулами ссылки не менялись, можно надежно закрепить их такими значками доллара ‘$’.

Воронка получилась, но не очень красивая, верно? Из-за маленькой конверсии последних шагов диаграммы очень сужаются, буквально стремятся к нулю. Решение: выбрать в качестве максимального значения (которое, напомним, не отображается, а служит буфером с двух сторон) не число визитов, а количество открытий формы, то есть второе по величине значение. В данном кейсе — данные из ячейки ‘Лист1’!C7. То есть поменять, например, последнюю формулу на: =SPARKLINE(;). В итоге воронка получается более плавной и наглядной.

Так воронка преображается, если отталкиваться не от максимального значения в диапазоне, а от №2 по величине.

Так воронка преображается, если отталкиваться не от максимального значения в диапазоне, а от №2 по величине.

Бонус: выбор данных для построения воронки

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

  1. Запрос, по которому нужно искать. В данном случае это значение, которое будет выбрано в ячейке B1.
  2. Диапазон, в котором нужно искать. Чтобы не путаться в формулах, лучше сразу взять весь диапазон, где могут лежать искомые данные — то есть ‘Лист1’!A7:E13.
  3. Номер столбца, из которого нужно извлечь данные. Для первой диаграммы это будет цифра 2, для второй — 3 и так далее.
  4. Режим поиска. Лучше всегда ставить 0 или FALSE, так как в большинстве случаев нужно именно точное совпадение. (1 или TRUE будет возвращать значение, ближайшее к запрошенному (меньшее либо равное) — сложно сказать, когда на практике такое может понадобиться.)

Более простое решение — загружать данные в строку выше воронки, справа от селектора, а формулы просто немного поменять, чтобы они брали значения с этого же листа. Тогда в C1 будет такая формула: =VLOOKUP(B1;’Лист1′!A7:E13;2;0), в С2 — =VLOOKUP(B1;’Лист1′!A7:E13;3;0) и так далее. Меняется только номер столбца, так как нужна та же строка в отчете, просто цифры нужно брать все правее.

После остается только изменить формулы для создания диаграмм — например, первую на =SPARKLINE(;); вторую на =SPARKLINE(;) и так далее.

Формулы для подсчета CTR тоже соответственно нужно поменять: =D1/C1; =E1/C1 и так далее.

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

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

Как видно, формула усложнилась, тем не менее работает нормально — извлекает все нужные данные без необходимости копирования на этот второй лист

Как видно, формула усложнилась, тем не менее работает нормально — извлекает все нужные данные без необходимости копирования на этот второй лист

Плагин jQuery, который генерирует спарклайны (маленькие встроенные графики).

Я хочу, чтобы эта ячейка выводила среднее значение в соответствии с выбранным периодом времени. Если я выберу 7 дней, в ячейке должно отображаться среднее значение за последнюю неделю / 7 дней. Если я выберу 6 месяцев, отобразится среднее значение за последние 6 месяцев и так далее. Это возможно.

Как заставить график SPARKLINE Line динамически отображать только последние 7 дней? То есть по мере того, как данные или новые строки продолжают добавляться на лист HISTORY, график SPARKLINE продвигается вперед, чтобы всегда отображать только последние 7 дней на графике. Я полагаю, есть способы сд.

У меня 2 спарклайна. Оба они созданы на основе исторических цен на акции с года назад по сегодняшний день. Я хочу создать новый спарклайн, представляющий собой сумму этих двух диаграмм. Возможно ли это, учитывая .

Я бы хотел удалить запятую из даты в всплывающей подсказке, но мне нужно сохранить ее в значении. Я не совсем понимаю форматирование jQuery Sparkline. Пример всплывающей подсказки library(shiny) library(dplyr) ui 1 -->

У меня возникли проблемы с отображением значков со спарклайнами в столбце DT::datatable в приложении Shiny, хотя я экранировал HTML. Изменить: удален 2-й вопрос. library(shiny) library(dplyr) ui 1 -->

Я хочу, чтобы мой спарклайн менял цвет на основе значений, чтобы отразить срок годности продукта, а также показать, когда продукт был доставлен: Дата выдачи (C10), срок действия (D10), спарклайн (J10), статус сертификата (K10): В идеале я бы хотел, чтобы ячейка была зеленой, а по мере приближения д.

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

С помощью =SPARKLINE(myFun()) я могу нарисовать один график. Однако мне нужно нарисовать 10 графиков. Я попытался вернуться в многомерный массив myFun, но получил: Ошибка Функция SPARKLINE строка параметра 1 диапазон должна быть одной строкой, одним столбцом, двумя строками или двумя столбцами. В.

Я пытаюсь добавить спарклайны к простому реагируемому примеру, но когда я пытаюсь это сделать, таблица оказывается пустой. Я следил за этим примером из документации . Вот пример данных, которые я использую: PartName 1 -->

У меня есть некоторые значения в ячейках, которые не следуют друг за другом и разбросаны по ячейкам, где некоторые значения для ячеек пусты или я хочу исключить их отображение в спарклайне. Пример: я хочу отобразить спарклайн для значений A1: E1, но B1 содержит значение, которое я не хочу отображать.

Пытаюсь разобраться в ReactJS. А именно, настал момент, когда нужно делать SparkLines. Но почему-то не отображает значение. Что могло быть не так? JSON : [ < "playersCount": "41170", "playersCountToday": "41", "playersCountAll": "45232", "countDailyChecksAll": .

Итак, у меня есть две книги на Google листах. Книга 1 и Книга 2. В настоящее время у меня есть Book1, импортирующий некоторую информацию из Book2, чтобы создать небольшой спарклайн-график. Он извлекает информацию из столбца Book2 от A3 до G3, чтобы создать небольшой график. Мой текущий код (в ячейке.

Я приду сюда сегодня вечером, потому что я чего-то не понимаю в Google Таблицах. Я нашел И теперь та же формула в моем документе, но это не работает .

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

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

У меня есть sparkline, который показывает, насколько завершено мероприятие. Я хочу, чтобы цвет менялся в зависимости от значения в другой ячейке. Если значение меньше 50, я хочу, чтобы он был красным; Если значение больше 51, но меньше 75, я хочу, чтобы оно было желтым; и - Если значение больше 7.

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

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


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

Типы спарклайн

Существует 6 типов спарклайн:

Чтобы создать темплейт-функцию для спарклайна с новым типов, используйте метод webix.Sparklines.getTemplate("type_name").

Тип Line


Тип Area


Тип Bar


Тип Spline


Тип SplineArea


Тип Pie


Тултипы для спарклайна

Вы можете задать тултип для значений спарклайна. Тултип будет появляться когда пользователь перемещает указатель мыши над элементами спарклайна (колонки, сектора).

Тултип для спарклайна задается с помощью темплейта "tooltip". Темплейт-функция получает элемент данных спарклайна как первый параметр.


Настройка спарклайнов

Вы можете настраивать спарклайны с помощью пользовательских опций в их конфигурации:

Чтобы задать доступные опции в зависимости от типа спарклайна:

Настройки Line

  • paddingX - левый и правый отступ
  • paddingY - верхний и нижний отступ
  • radius - радиус точки
  • eventRadius - радиус кликабельной зоны вокруг точки
  • minHeight - минимальная высота
  • css - пользовательский класс CSS для спарклайн
  • origin - масштаб
  • scale - максимальное значение шкалы
  • color - цвет линии

Настройки Bar

  • paddingX - левый и правый отступ
  • paddingY - верхний и нижний отступ
  • width - ширина колонки
  • margin - граница колонки
  • minHeight - минимальная высота
  • origin - масштаб
  • itemCss - css колонки
  • scale - максимальное значение шкалы
  • color - цвет колонки
  • negativeColor - цвет колонок ниже оси X

Настройки Area

  • paddingX - левый и правый отступ
  • paddingY - верхний и нижний отступ
  • radius - радиус точки
  • eventRadius - радиус кликабельной зоны вокруг точки
  • minHeight - минимальная высота
  • css - пользовательский класс CSS для спарклайн
  • color - цвет линии

Настройки Pie

  • paddingY - верхний и нижний отступ
  • css - пользовательский класс CSS для спарклайн
  • color - цвет линии

Настройки Spline

  • paddingX - левый и правый отступ
  • paddingY - верхний и нижний отступ
  • radius - радиус точки
  • eventRadius - радиус кликабельной зоны вокруг точки
  • minHeight - минимальная высота
  • css - пользовательский класс CSS для спарклайн

Задание цвета спарклайн

Существует два варианта настройки цвета спарклайн, используя свойство color:

  • задать разные цвета спарклайн для каждой строки таблицы

Цвет негативных значений для спарклайн с типом Bar

Вы можете установить определенный цвет во время отображения спарклайн с типом Bar. Для этого используется свойство negativeColor:


Спарклайн в других компонентах

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


Для этого, создайте темплейт-функцию для спарклайн и используйте ее в темплейте списка. Как и любой другой темплейт, темплейт-функция спарклайн возвращает строку HTML. Получает два параметра:

  • data - коллекция данных для отображения
  • type - объект содержащий свойства размерности ("width" и "height")

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

Темплейт спарклайн также может быть использован внутри темплейта списка. В этом случае вам нужно передать данные и объект с размерами в темплейт спарклайн:

If you have not checked yet, be sure to visit site of our main product Webix widget library and page of html5 datatable product.

Читайте также: