Google Таблицы

Динамический спарклайн: выбираем период, точку отсчета и цвет спарклайна и максимальной точки (цвет - на русском языке)

Друзья, привет. Астрологи провозгласили месяц спарклайнов – продолжим развлекаться с маленькими графиками.

Сегодня рассмотрим такой пример: данные по выручке за несколько лет по месяцам.

Создадим формулу, которая будет строить спарклайн по выбранному количеству месяцев и начиная с выбранного месяца.
Для этого нужна будет функция СМЕЩ, формирующая ссылку на динамический диапазон. Зададим точку отсчета - первый месяц с данными; высотой диапазона будет выбранная пользователем в ячейке длительность периода, точкой отсчета (отступом по строкам) – позиция выбранного месяца.

=СМЕЩ(первая ячейка с данными; ПОИСКПОЗ(выбранный в выпадающем списке месяц - точка отсчета; диапазон с месяцами в наших данных;0)-1;0; выбранная длительность периода; 1)

Вычитаем единицу из позиции выбранного месяца, т.к. для января , который первый в диапазоне, нам отступать никуда не нужно.
Последний аргумент- это ширина, она равна единице.
0 в функции СМЕЩ - это отступ по столбцам, он здесь не нужен. Берем данные из одного и того же столбца с показателем.

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

Помня о том, что в случае со спарклайнами нельзя добавить ось и подписи данных, выведем отдельно минимальное и максимальное значение за выбранный период - просто будем использовать СМЕЩ как аргумент функций МИН и МАКС. Так мы будем понимать масштаб, понимать, какому значению соответствует минимальный и максимальный столбцы.

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

Напомним параметры такого спарклайна:
charrtype (тип) – column (столбчатый)
color – цвет спарклайна
highcolor – цвет максимального значения

Ссылка на файл с примером

Другие посты про спарклайны:
– Меняем цвет у отрицательных, максимальных и минимальных, первых и последних значений.
– Спарклайн с условием
– Видеоурок: Синтаксис и примеры
– Что такое спарклайны?

www.tgoop.com/google_sheets/528

12.6K viewsedited  May 19, 2020 at 08:07

Динамический спарклайн: выбираем период, точку отсчета и цвет спарклайна и максимальной точки (цвет - на русском языке)

Друзья, привет. Астрологи провозгласили месяц спарклайнов – продолжим развлекаться с маленькими графиками.

Сегодня рассмотрим такой пример: данные по выручке за несколько лет по месяцам.

Создадим формулу, которая будет строить спарклайн по выбранному количеству месяцев и начиная с выбранного месяца.
Для этого нужна будет функция СМЕЩ, формирующая ссылку на динамический диапазон. Зададим точку отсчета - первый месяц с данными; высотой диапазона будет выбранная пользователем в ячейке длительность периода, точкой отсчета (отступом по строкам) – позиция выбранного месяца.

=СМЕЩ(первая ячейка с данными; ПОИСКПОЗ(выбранный в выпадающем списке месяц - точка отсчета; диапазон с месяцами в наших данных;0)-1;0; выбранная длительность периода; 1)

Вычитаем единицу из позиции выбранного месяца, т.к. для января , который первый в диапазоне, нам отступать никуда не нужно.
Последний аргумент- это ширина, она равна единице.
0 в функции СМЕЩ - это отступ по столбцам, он здесь не нужен. Берем данные из одного и того же столбца с показателем.

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

Помня о том, что в случае со спарклайнами нельзя добавить ось и подписи данных, выведем отдельно минимальное и максимальное значение за выбранный период - просто будем использовать СМЕЩ как аргумент функций МИН и МАКС. Так мы будем понимать масштаб, понимать, какому значению соответствует минимальный и максимальный столбцы.

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

Напомним параметры такого спарклайна:
charrtype (тип) – column (столбчатый)
color – цвет спарклайна
highcolor – цвет максимального значения

Ссылка на файл с примером

Другие посты про спарклайны:
– Меняем цвет у отрицательных, максимальных и минимальных, первых и последних значений.
– Спарклайн с условием
– Видеоурок: Синтаксис и примеры
– Что такое спарклайны?

BY Google Таблицы