Подсчет ячеек по цвету в Google Sheets
Если вы кодируете данные цветом в Google Таблицах, вы, вероятно, заметили отсутствие стандартных формул, обобщающих такую информацию. Вот почему мы добавили пользовательскую функцию, которая учитывает шрифт и цвет фона ячеек при выполнении всех основных операций.
Вы можете найти это среди умных функций нашего Электроинструменты дополнение или как отдельный инструмент: Функция по цвету. Оба предлагают 30-дневный пробный период и два плана подписки: 12-месячный и пожизненный.
Кончик. Посмотрите это короткое демонстрационное видео или просмотрите подробное описание со скриншотами ниже.
Как считать цветные клетки
Возьмем одну из самых распространенных задач с цветными ячейками в электронной таблице: подсчет ячеек с одинаковым форматированием. Скажем, вот у меня есть список оценок и я хочу посмотреть, сколько раз был пройден каждый тест, т.е. посчитать все зеленые клетки в столбце:
Если вы работаете с похожими диапазонами, которые содержат разные типы записей: числа, текст или значения дат, воспользуйтесь функцией СЧЕТЧИК с инструментом Функция по цвету.
Для начала откройте инструмент суммирования по цвету. В Power Tools вы увидите это прямо на умной панели инструментов:
Вот шесть простых настроек, которые необходимо указать, чтобы получить результаты:
- диапазон с выделенными цветом данными, которые вы хотите проверить: я выбираю столбцы с результатами теста.
- ячейка шаблона. Функция возьмет свой шрифт и/или цвет фона и будет искать то же самое в других ячейках. В качестве примера я выбираю цвет заливки B2.
- функция. Если вы хотите подсчитать ячейки с определенным форматированием, лучше всего выбрать функцию СЧЕТЧИК, потому что это единственная функция, которая работает с нечисловыми значениями. Таким образом, мы можем быть уверены, что формат данных в ячейках не помешает получению результатов.
- место для результатов. Я хочу считать в каждом столбце, поэтому для меня это три ячейки.
- Как это применять. Мы можем выбрать подсчет ячеек в каждой строке, столбце или во всех выбранных ячейках. Поскольку я хочу подсчитать зеленые ячейки для каждого теста, я выбираю «Рассчитать» в каждом столбце. Если бы мне нужен был общий результат для всех трех тестов, я бы выбрал «весь диапазон».
- форматирование ячеек с результатом. Вы можете заполнить их теми же цветами, которые считаете, установив только один флажок.
Как только я нажму «Вставить функцию», инструмент добавит формулу в каждый столбец, поэтому я увижу результаты для каждого теста:
Позвольте мне разобрать его синтаксис и объяснить каждую часть:
- =COUNTA — это одна из стандартных функций Google Sheets, которую вы выбираете для использования с цветными ячейками.
- светло-зеленый 3 — цвет ячейки, учитываемый при расчетах, т. е. цвет фона ячейки шаблона.
- “” пусто, так как я не учитываю цвет шрифта ячейки шаблона.
- B2:B18 — это диапазон для проверки цветных ячеек. В моем примере он смотрит на ячейки в столбце B.
Как суммировать значения по цвету
Скажем, я отслеживаю заказы на оборудование для занятий. Желтым цветом фона я обозначаю заказанные вещи, синим — вещи в пути, зеленым — доставленное оборудование:
Моя задача — посмотреть, сколько столов, компьютеров и других расходных материалов находится в пути в данный момент. Цвет — единственное отличие этих чисел в моей таблице. Поэтому я открываю инструмент для суммирования по цвету и использую следующие настройки:
- Я выбираю весь диапазон со своими данными, чтобы проверить его.
- Я выбираю C2 в качестве ячейки шаблона, чтобы указать формат элементов, которые я хочу вычислить.
- Выберите функцию СУММ, чтобы сложить числа из зеленых ячеек.
- Выберите ячейки, чтобы разместить полученные вычисления для каждого элемента.
- Чтобы увидеть количество отправленных товаров для каждого продукта, примените функцию к каждой строке.
Щелкните Вставить функцию, чтобы получить формулу после каждой строки в таблице.
Удобство получения формулы заключается в том, что вы можете изменить любую ее часть и вставить в нужное место в своих таблицах Google.
Обновить результаты
Вы помните, что в Google Таблицах нет функций для работы с цветом? Это означает, что изменения в форматировании ячеек не считаются причиной для повторного расчета результатов формулы. Хорошая новость заключается в том, что оба обходных пути, которые вы можете использовать, очень просты.
- Вы можете изменить любое значение в расчетном диапазоне. Скажем, вы считаете зеленые ячейки в A1:C254. Вы можете просто добавить символ в любую из ячеек этого диапазона, а затем удалить его, чтобы получить обновленные результаты.
- Если у вас много значения по цвету формул на вашем листе, щелкните параметр «Обновить», который находится прямо под функцией по цвету в Power Tools, чтобы обновить все формулы за один раз.
Изменить формулы
Другой способ изменить результат — отредактировать всю формулу. Но если вы хотите выбрать только пару параметров, вам не нужно создавать всю формулу заново. Достаточно щелкнуть ячейку, которая уже содержит формулу, и выбрать вариант ее редактирования:
Надстройка откроется со всеми настройками, которые использовались для создания выбранной формулы. Вы можете настроить некоторые или все из них — диапазоны, цвета, ячейку шаблона, функцию — и вставить все обратно.
Исправление проблем
Если вы видите ошибку вместо формулы, возможно, вы работаете с файлом, для которого не задана локаль. Когда это происходит, наша функция не знает, какие разделители ей следует использовать, поэтому в итоге выдает ошибку. Если вы видите это, пожалуйста, перейдите к Файл > Параметры электронной таблицы… в Google Таблицах и убедитесь, что у вас установлен языковой стандарт.
