Как сделать динамическую шкалу выполнения задач в Google Таблицах

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

Исходные материалы

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

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

2. Далее – свободная ячейка, в которой будет отображаться общее значение в процентах. Вам требуется простая формула суммирования. Формат ячейки обязательно должен быть в процентах, чтобы значение соответствовало всем остальным в таблице.

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

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

1. Сначала выделите горизонтальную последовательность ячеек и откройте меню «Формат».

2. Наведите курсор на пункт «Объединить ячейки» и выберите вариант «Объединить все».

3. Вернитесь к таблице и убедитесь в том, что настройка выполнена успешно.

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

Создание графической шкалы выполнения задач

Функция SPARKLINE представляет собой миниатюрную диаграмму, расположенную внутри одной ячейки. Мы объединили несколько ячеек в одну, поэтому диаграмма будет растягиваться. Понадобится только выбрать правильный тип и установить, что именно она будет считать. Все это осуществляется при помощи небольшой формулы:

1. Выберите ту самую объединенную ячейку, объявите функцию =SPARKLINE(), внутри скобок укажите номер ячейки, в которой отображается сумма всех процентов. Соответственно, не забудьте поставить точку с запятой.

2. Заполните остальную часть функции, которая выглядит так: {"charttype"\"bar";"max"\СЧЁТЗ(A3:A7)*100%}. О каждом из этих параметров более детально поговорим ниже. Номера ячеек для подсчета замените на свои (указывайте ячейки не с процентами, а с названиями пунктов, что видно на следующем изображении).

3. Нажмите Enter для применения изменений и посмотрите, появилась ли на экране графическая шкала. Если нет, скорее всего, у вас еще не указаны проценты в соответствующих полях. В противном случае внимательно проверьте то, как именно вы написали формулу.

4. Измените любое из значений на большее или меньшее и посмотрите, как изменяется длина графической шкалы.

Я перечислю все используемые в формуле =SPARKLINE(B1;{"charttype"\"bar";"max"\СЧЁТЗ(A3:A7)*100%}) параметры, чтобы у вас не возникло вопросов по содержимому. Кстати, саму формулу вы можете просто скопировать и вставить с заменой номеров ячеек.

• "charttype"\"bar" – тип используемой диаграммы, в данном случае это шкала.

• "max"\СЧЁТЗ(A3:A7)*100%} – максимально допустимое значение в наборе данных. Набором данных являются пункты с процентами.

• "color1"\"red" – отсутствует в данном представлении формулы, поскольку в шкале используется только один цвет для любого состояния. Понятно, что этот параметр отвечает за цвет шкалы, о его применении я расскажу далее.

Создание меняющегося цвета для графической шкалы

Простая одноцветная шкала для отображения процентов выполнения создается очень быстро и просто, в чем вы уже убедились. Немного сложнее будет ее модернизировать так, чтобы цвет менялся в зависимости от количества процентов. Для этого понадобится функция ЕСЛИ (она же IF), но сама формула по размеру будет большой.

1. Для начала перед функцией диаграммы в ячейке объявите IF(, поставив только открывающуюся скобку.

2. После этого введите B1<250%;, где B1 – номер ячейки с общим подсчетом процентов. 250% вы можете заменить на любое значение.

3. Модернизируйте формулу, поставив после типа графика часть "color1"\"red", о которой уже шла речь выше. Цвет тоже замените на любой, но обязательно держите его в двойных кавычках.

4. Переместитесь в конец формулы, поставьте точку с запятой и пропишите IF(AND(B1>250%;B1<400%); для среднего значения.

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

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

7. Нажмите Enter для применения изменений и посмотрите, стал ли цвет графика другим.

8. Поменяйте проценты и продолжайте отслеживать изменения.

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

=ЕСЛИ(B1<250%;SPARKLINE(B1;{"charttype"\"bar";"color1"\"red";"max"\СЧЁТЗ(A3:A7)*100%});ЕСЛИ(И(B1>250%;B1<400%);(SPARKLINE(B1;{"charttype"\"bar";"color1"\"yellow";"max"\СЧЁТЗ(A3:A7)*100%}));(SPARKLINE(B1;{"charttype"\"bar";"color1"\"green";"max"\СЧЁТЗ(A3:A7)*100%}))))

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