Подсчет ячеек по цвету в Google Sheets

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

Вы можете найти это среди умных функций нашего Электроинструменты дополнение или как отдельный инструмент: Функция по цвету. Оба предлагают 30-дневный пробный период и два плана подписки: 12-месячный и пожизненный.

Кончик. Посмотрите это короткое демонстрационное видео или просмотрите подробное описание со скриншотами ниже.

Как считать цветные клетки

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

Если вы работаете с похожими диапазонами, которые содержат разные типы записей: числа, текст или значения дат, воспользуйтесь функцией СЧЕТЧИК с инструментом Функция по цвету.

Для начала откройте инструмент суммирования по цвету. В Power Tools вы увидите это прямо на умной панели инструментов:

Вот шесть простых настроек, которые необходимо указать, чтобы получить результаты:

1. диапазон с выделенными цветом данными, которые вы хотите проверить: я выбираю столбцы с результатами теста.
2. ячейка шаблона. Функция возьмет свой шрифт и/или цвет фона и будет искать то же самое в других ячейках. В качестве примера я выбираю цвет заливки B2.
3. функция. Если вы хотите подсчитать ячейки с определенным форматированием, лучше всего выбрать функцию СЧЕТЧИК, потому что это единственная функция, которая работает с нечисловыми значениями. Таким образом, мы можем быть уверены, что формат данных в ячейках не помешает получению результатов.
4. место для результатов. Я хочу считать в каждом столбце, поэтому для меня это три ячейки.
5. Как это применять. Мы можем выбрать подсчет ячеек в каждой строке, столбце или во всех выбранных ячейках. Поскольку я хочу подсчитать зеленые ячейки для каждого теста, я выбираю «Рассчитать» в каждом столбце. Если бы мне нужен был общий результат для всех трех тестов, я бы выбрал «весь диапазон».
6. форматирование ячеек с результатом. Вы можете заполнить их теми же цветами, которые считаете, установив только один флажок.

Как только я нажму «Вставить функцию», инструмент добавит формулу в каждый столбец, поэтому я увижу результаты для каждого теста:

Позвольте мне разобрать его синтаксис и объяснить каждую часть:

• =COUNTA — это одна из стандартных функций Google Sheets, которую вы выбираете для использования с цветными ячейками.
• светло-зеленый 3 — цвет ячейки, учитываемый при расчетах, т. е. цвет фона ячейки шаблона.
• “” пусто, так как я не учитываю цвет шрифта ячейки шаблона.
• B2:B18 — это диапазон для проверки цветных ячеек. В моем примере он смотрит на ячейки в столбце B.

Как суммировать значения по цвету

Скажем, я отслеживаю заказы на оборудование для занятий. Желтым цветом фона я обозначаю заказанные вещи, синим — вещи в пути, зеленым — доставленное оборудование:

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

1. Я выбираю весь диапазон со своими данными, чтобы проверить его.
2. Я выбираю C2 в качестве ячейки шаблона, чтобы указать формат элементов, которые я хочу вычислить.
3. Выберите функцию СУММ, чтобы сложить числа из зеленых ячеек.
4. Выберите ячейки, чтобы разместить полученные вычисления для каждого элемента.
5. Чтобы увидеть количество отправленных товаров для каждого продукта, примените функцию к каждой строке.

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

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

Обновить результаты

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

1. Вы можете изменить любое значение в расчетном диапазоне. Скажем, вы считаете зеленые ячейки в A1:C254. Вы можете просто добавить символ в любую из ячеек этого диапазона, а затем удалить его, чтобы получить обновленные результаты.
2. Если у вас много значения по цвету формул на вашем листе, щелкните параметр «Обновить», который находится прямо под функцией по цвету в Power Tools, чтобы обновить все формулы за один раз.
   

Изменить формулы

Другой способ изменить результат — отредактировать всю формулу. Но если вы хотите выбрать только пару параметров, вам не нужно создавать всю формулу заново. Достаточно щелкнуть ячейку, которая уже содержит формулу, и выбрать вариант ее редактирования:

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

Исправление проблем

Если вы видите ошибку вместо формулы, возможно, вы работаете с файлом, для которого не задана локаль. Когда это происходит, наша функция не знает, какие разделители ей следует использовать, поэтому в итоге выдает ошибку. Если вы видите это, пожалуйста, перейдите к Файл > Параметры электронной таблицы… в Google Таблицах и убедитесь, что у вас установлен языковой стандарт.

Смотрите также