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

Вам не нужно писать функцию самостоятельно. Для этой утилиты можно использовать плагины, например PowerTools или Function by Color. У них есть пробная версия на месяц с возможностью подписки — на 12 месяцев или на всю жизнь. Оба они выходят за рамки данного руководства.

Возьмем пример.

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

И именно здесь подсчет ячеек по их цвету может помочь мне сэкономить время.

Реальный пример подсчета клеток по цвету клеток

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

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

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

 

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

Итак, как написать свою собственную функцию в Google Таблицах? Рассмотрим подробнее на примере.

Как подсчитать ячейки на основе цвета ячеек в Google Таблицах

  • Я перечислил оценки, полученные старшеклассниками соседней школы на их ежегодных экзаменах, по предметам. Если вы внимательно заметили, я разделил их характеристики по цвету на четыре категории — «Неудачно» (красный), «Удовлетворительно» (оранжевый), «Хорошо» (светло-зеленый) и «Отлично» (темно-зеленый). Цель состоит в том, чтобы выяснить распределение студентов по четырем категориям по всем предметам.

         В Google Таблицах есть положение под названием «Редактор скриптов». Перейдите в Инструменты → Редактор скриптов.

      Щелчок по «Редактору сценариев» должен перенаправить вас в новое окно, которое позволит вам написать свой код, как показано ниже.

  • Как только вы окажетесь здесь, напишите сценарий, который даст вам желаемую функциональную возможность — в данном случае подсчет ячеек на основе их цвета. После завершения нажмите кнопку сохранения, и теперь функция будет доступна в ваших таблицах Google для дальнейшего использования.

 

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

        Позвольте мне попытаться объяснить логику построчно. 

    • Строка 1: обозначает имя, данное функции, и входные данные, необходимые для ее выполнения. В данном случае заданное имя — «countColoredCells», что требует ввода двух параметров — countRange и colorRef.
    • Строка 2: команда .getActiveRange () вернет диапазон, выбранный пользователем.
    • Строка 3: команда .getSheet () вернет активный лист.
    • Строка 4: .getFormula () возвращает формулу, примененную к верхней левой ячейке выбранного диапазона.
    • Строки 6-9: обозначают выбор диапазона и цвета фона соответственно. .GetBackgrounds () возвращает значение цвета фона.
    • Строки 11-13: обозначают получение эталонного цвета фона, который необходимо сопоставить.
    • Строка 15: инициализирует счетчик. Эта переменная будет обновляться в цикле для каждого случая, когда эталонный цвет совпадает с цветом ячейки.
    • Строки 17-21: обозначают цикл, который вычисляет счетчик для каждого указанного эталонного цвета.

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

      4. Затем просто введите знак равенства ‘ = ‘, чтобы начать функцию, и следуйте за именем функции, которое вы назвали в редакторе скриптов. В этом примере имя функции — countColoredCells.

  • Согласно закодированной мной логике, требуются два входа — диапазон и эталон цвета. Введите оба входа в функцию и закройте скобки, как показано ниже:

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

Это почти все. У вас есть все необходимое, чтобы начать подсчет ячеек на основе цвета ячеек. Вы также можете попытаться суммировать значения на основе цвета. Я рекомендую поэкспериментировать с настраиваемой функцией, объединить ее с многочисленными доступными формулами Google Таблиц и посмотреть, что вы можете придумать.🙂