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

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

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

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

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

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

  1. Сначала выделите горизонтальную последовательность ячеек и откройте меню «Формат».Переход к объединению ячеек для создания графической шкалы выполнения задач в Google Таблицах
  2. Наведите курсор на пункт «Объединить ячейки» и выберите вариант «Объединить все».Настройка для объединения ячеек для создания графической шкалы выполнения задач в Google Таблицах
  3. Вернитесь к таблице и убедитесь в том, что настройка выполнена успешно.Результат объединения ячеек для создания графической шкалы выполнения задач в Google Таблицах

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

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

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

  1. Выберите ту самую объединенную ячейку, объявите функцию =SPARKLINE(), внутри скобок укажите номер ячейки, в которой отображается сумма всех процентов. Соответственно, не забудьте поставить точку с запятой.Объявление функции для создания графической шкалы выполнения задач в Google Таблицах
  2. Заполните остальную часть функции, которая выглядит так: {«charttype»»bar»;»max»СЧЁТЗ(A3:A7)*100%}. О каждом из этих параметров более детально поговорим ниже. Номера ячеек для подсчета замените на свои (указывайте ячейки не с процентами, а с названиями пунктов, что видно на следующем изображении).Написание полной функции для создания графической шкалы выполнения задач в Google Таблицах
  3. Нажмите Enter для применения изменений и посмотрите, появилась ли на экране графическая шкала. Если нет, скорее всего, у вас еще не указаны проценты в соответствующих полях. В противном случае внимательно проверьте то, как именно вы написали формулу.Просмотр результата функции для создания графической шкалы выполнения задач в Google Таблицах
  4. Измените любое из значений на большее или меньшее и посмотрите, как изменяется длина графической шкалы.Изменение функции после создания графической шкалы выполнения задач в Google Таблицах

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

  • «charttype»»bar» – тип используемой диаграммы, в данном случае это шкала.
  • «max»СЧЁТЗ(A3:A7)*100%} – максимально допустимое значение в наборе данных. Набором данных являются пункты с процентами.
  • «color1″»red» – отсутствует в данном представлении формулы, поскольку в шкале используется только один цвет для любого состояния. Понятно, что этот параметр отвечает за цвет шкалы, о его применении я расскажу далее.

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

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

  1. Для начала перед функцией диаграммы в ячейке объявите IF(, поставив только открывающуюся скобку.Добавление условия ЕСЛИ для создания графической шкалы выполнения задач в Google Таблицах
  2. После этого введите B1<250%;, где B1 – номер ячейки с общим подсчетом процентов. 250% вы можете заменить на любое значение.Настройка первого условия ЕСЛИ для создания графической шкалы выполнения задач в Google Таблицах
  3. Модернизируйте формулу, поставив после типа графика часть «color1″»red», о которой уже шла речь выше. Цвет тоже замените на любой, но обязательно держите его в двойных кавычках.Выбор цвета для первого условия для создания графической шкалы выполнения задач в Google Таблицах
  4. Переместитесь в конец формулы, поставьте точку с запятой и пропишите IF(AND(B1>250%;B1<400%); для среднего значения.Добавление второго условия для ЕСЛИ для создания графической шкалы выполнения задач в Google Таблицах
  5. После этого вставьте ту же часть функции, начиная со SPARKLINE, но замените цвет уже на другой.Вставка второй части условия ЕСЛИ для создания графической шкалы выполнения задач в Google Таблицах
  6. Поставьте точку с запятой в конце, снова вставьте формулу графика и поставьте для него третий цвет. Этот вариант сработает в том случае, если значение не равняется ни одному из перечисленных выше условий.Вставка третьей части условия ЕСЛИ для создания графической шкалы выполнения задач в Google Таблицах
  7. Нажмите Enter для применения изменений и посмотрите, стал ли цвет графика другим.Проверка функции после добавления ЕСЛИ для создания графической шкалы выполнения задач в Google Таблицах
  8. Поменяйте проценты и продолжайте отслеживать изменения.Изменение значений после добавления ЕСЛИ для создания графической шкалы выполнения задач в Google Таблицах

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

=ЕСЛИ(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%}))))

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

источник

Related Posts
AllEscortAllEscort