Як просто візуалізувати дані в Google-таблицях | Бізнес-школа Laba (Лаба)
Для відстеження статусу замовлення - авторизуйтесь
Введіть код, який був надісланий на пошту Введіть код із SMS, який був надісланий на номер
anastasiiasytar@gmail.com
Код дійсний протягом 2 хвилин Код з SMS дійсний протягом 2 хвилин
Ви впевнені, що хочете вийти?
Сеанс завершено
На головну

Пошук

Зміст

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

Детальний гайд з функції Sparkline у гугл-таблицях із прикладами.

cover-64ec7a6b2ca77597447107.jpg

Sparkline — це візуалізація даних у гугл-таблицях в одній клітинці. Ось декілька прикладів використання формули:

google sheets

Розглянемо синтаксис функції:

=SPARKLINE(data, [options])
де data — діапазон даних, а options — задані параметри.

Задані параметри options є опціональним елементом формули. Якщо їх не задати — за замовчуванням буде намальований графік із чорною лінією типу Line.

гугл таблиці

Всього для побудови чартів (діаграм) є 4 типи charttype:

charttype

До речі, в цьому прикладі ми використовували charttype типу bar:

bar

Параметри всередині [options] відповідають за задання всіх деталей нашої майбутньої діаграми: тип, кольори, довжина, максимальне/мінімальне значення, обробка текстових значень або пустих колонок та інше. Задаються вони у зв’язці «ключ»\«значення». Наприклад, щоби побудувати ось такий графік без задання параметрів кольору:

data

ми будемо використовувати формулу:

=sparkline(A27:F27;{"charttype"\"column"})

З іншими діаграмами ми маємо такий самий синтаксис:

=sparkline(A28:F28;{"charttype"\"winloss"})
=sparkline(A30:F30;{"charttype"\"bar"})

options

1. Змінимо кольори діаграм

#1. Кольори для charttype line:

Шаблон заповнення такий самий, як і раніше, і виглядає як «ключ»\«значення».
У випадку з charttype line ми маємо тільки 1 ключ — color. Значення можна показати або у форматі hex (#000000 — чорний колір), або текстом (“color”\”black”). В нашій формулі використаємо фіолетовий колір у форматі hex: 

"color"\"#b045eb"

Формула

=sparkline(A24:F24;{"charttype"\"line";"color"\"#b045eb"})

буде мати такий вигляд

sparkline

#2. Кольори для charttype column & winloss:

Ключ при заданні кольорів

Для позначення стовпців, яким ми можемо задати колір, використовуємо такі ключі:

"firstcolor" — перший стовпець

"lastcolor" — останній стовпець

"highcolor" — найбільше значення

"lowcolor" — найменше значення

“negcolor" — від’ємні колонки

"color" — для позначення всіх невизначених стовпців

"highcolor" та "lowcolor" є найбільш пріоритетними параметрами, "firstcolor", "lastcolor", “negcolor" — менш пріоритетні. Тобто якщо "highcolor" та "firstcolor" — це одна і та сама колонка, то відображатиметься колір, заданий значенням у "highcolor".
"color" спрацює тільки в тому випадку, якщо колонка не підпадає під жоден із вищезазначених параметрів

Значення при заданні кольорів

При заданні кольору стовпцям ми можемо використовувати формулювання у форматі hex, де #000000 означає чорний колір, а можемо використовувати стандартне black.

Наприклад, формула

=sparkline(A27:F27;{"charttype"\"column";"color"\"#5e8c6a";"firstcolor"\"#c2d4c7";"lastcolor"\"#e9bd9a";"highcolor"\"black";"lowcolor"\"red";"negcolor"\"green"})

буде мати ось такий вигляд

column

#3. Кольори для charttype bar:

Для позначення кольорів у наступному чарті ми маємо лише два різних ключі "color1" та "color2".

Формула

=sparkline(A24:F24;{"charttype"\"bar";"color1"\"#b045eb";"color2"\"#85ee7b"})

буде мати такий вигляд

bar

2. Додамо вісь та змінимо її колір 

Для додання осі використовуємо «ключ»\«значення» 

"axis"\true

Для задання кольору осі 

"axiscolor"\"колір"

Наприклад, формула

=sparkline(A27:F27;{"charttype"\"column";"color"\"#85ee7b";"negcolor"\"#b045eb";"axis"\true;"axiscolor"\"#b045eb"})

буде мати ось такий вигляд

візуалізація даних

3. Задаємо мінімальне та максимальне значення для колонки 

Задаємо у форматі

“ymin”\число
“ymax”\число

Наприклад, формула

=sparkline(A27:F27;{"charttype"\"column";"color"\"#85ee7b";"ymin"\0;"ymax"\1000})

буде мати такий вигляд

діаграма

4. Обробляємо пусті значення

За замовченням, якщо серед значень є нульове, воно відображається на діаграмі як пробіл:

дашборд

Якщо значення в клітинці відсутнє або замість нього стоїть текст, то клітинка пропускається:

діаграма онлайн

електронна таблиця гугл

Серед можливостей обробки даних для цього типу діаграм є змога перетворити пусте значення клітинки на нульове за допомогою «ключа»\«значення»

"empty"\”zero”

Наприклад, формула 

=sparkline(A27:F27;{"charttype"\"column";"color"\"#85ee7b";"empty"\"zero"})

буде мати ось такий вигляд:

гугл таблиця онлайн

5. Зворотний порядок 

За замовченням ми маємо діаграму, в якій перша колонка є значенням відповідно до стовпця А, а остання — відповідно до стовпця F нашого прикладу. 

Формула, яка використана на скріні нижче:

=sparkline(A27:F27;{"charttype"\"column";"color"\"#85ee7b"})

робота з гугл таблицями

Функціонал sparkline дозволяє змінювати порядок відображення стовпців в іншому напрямку за допомогою ключа "rtl" зі значенням true.

Тобто, формула

=sparkline(A27:F27;{"charttype"\"column";"color"\"#85ee7b";"rtl"\true})

буде мати ось такий розгорнутий вигляд:

google sheets це

6. Різниця між "charttype"\"column" та "charttype"\"winloss"

Ці діаграми дуже схожі між собою, але мають одну різницю. Charttype column є чутливим до змін як серед значень більше нуля, так і менше. Тобто для значення 400 та значення 100 колонки будуть мати різний розмір. 

Тоді як charttype winloss не підтримує таку опцію. Для показників 400 та 100 колонки будуть мати однаковий розмір.

Розглянемо на прикладі.

Маємо дві однакові формули з єдиною різницею в значенні “charttype”.

“column”:
=sparkline(A27:F27;{"charttype"\"column";"color"\"#85ee7b";"negcolor"\"#b045eb";"axis"\true;"axiscolor"\"#b045eb"})

“winloss”:
=sparkline(A28:F28;{"charttype"\"winloss";"color"\"#85ee7b";"negcolor"\"#b045eb";"axis"\true;"axiscolor"\"#b045eb"})

winloss

7. Функціональні особливості charttype bar

Зручність використання charttype bar — у тому, що можна задати у формулі максимальне значення.

За замовчуванням формула

=sparkline(A35;{"charttype"\"bar"})

буде мати такий вигляд:

гугл таблиці це

Якщо ми не задаємо максимального значення для нашого чарту, то значення з діапазону (в нашому випадку це A35 = 100) буде вважатися максимальним. Щоб змінити це, використовуйте ключ “max”.

Формула

=sparkline(A35;{"charttype"\"bar";"max"\500})

вже буде мати такий вигляд:

гугл таблиці онлайн

Замість окремого числа (500 в нашому попередньому прикладі) можна вказати діапазон значень і з нього вибрати максимальне за допомогою формули MAX(діапазон значень)

Формула

=sparkline(A35;{"charttype"\"bar";"max"\MAX($A$35:$A$40)})

буде виглядати ось так в гугл-таблицях:

гугл таблиця

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

excel google

Функціонал SPARKLINE дозволяє використовувати замість діапазону числові значення. Тобто, наприклад, якщо по черзі задати такі формули у клітинки

=sparkline(1;{"charttype"\"bar";"max"\4})
=sparkline(2;{"charttype"\"bar";"max"\4})
=sparkline(3;{"charttype"\"bar";"max"\4})
=sparkline(4;{"charttype"\"bar";"max"\4})

то це буде мати ось такий вигляд:

таблиці гугл

Тобто для реалізації нашого прикладу з галочками необхідно порахувати кількість значень TRUE (коли галочка поставлена) і вписати їх у діапазон.

Формула така:

=sparkline(countif(B4:E4;TRUE);{"charttype"\"bar";"max"\4})

data sheet

Бажаєте отримувати дайджест статей?

Один лист з найкращими матеріалами за місяць. Підписуйтесь, аби нічого не проґавити.
Дякуємо за вашу підписку!
Курс з теми:
«Excel для бізнесу»
Програми
Веде Олексій Вощак
16 квітня 14 травня
Олексій Вощак