Пользовательские функции Google Sheets для подсчета цветных ячеек: CELLCOLOR и VALUESBYCOLORALL

В этом руководстве представлены две новые функции из нашего дополнения «Функция по цвету» для Google Таблиц: CELLCOLOR и VALUESBYCOLORALL. Используйте их для суммирования и подсчета ячеек не только по их цветам, но и по общему содержимому. Включены готовые формулы СУММЕСЛИМН и СЧЁТЕСЛИМН 😉

Если вы много работаете с цветными ячейками в Google Sheets, возможно, вы пробовали наш Надстройка «Функция по цвету». Мало ли вы знаете, что теперь у него есть еще 2 функции, которые еще больше расширяют ваши операции с цветными ячейками: CELLCOLOR и VALUESBYCOLORALL. В этом уроке я познакомлю вас с обеими функциями и предоставлю несколько готовых формул.

Суммируйте и подсчитайте цветные ячейки с помощью функции по цвету

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

Это дополнение для Google Sheets проверяет шрифт и/или цвета заливки в выбранных ячейках и:

• суммирует числа с общим оттенком
• считает цветные клетки и даже пробелы
• находит средние/минимальные/максимальные значения среди выделенных ячеек
• и более

Всего имеется 13 функций для расчета цветных ячеек.

Вот как это работает:

1. Вы выбираете диапазон для обработки.
2. Выберите шрифт и / или оттенки заливки, которые вы хотите рассмотреть, и выберите функцию в соответствии с вашей задачей.
3. Выберите расчет записей в каждой строке/столбце или во всем диапазоне.
4. Выберите ячейку (ячейки), где вы хотите увидеть результат.
5. Нажмите Вставить функцию.

Например, здесь в каждой строке я суммирую все предметы, которые «в пути» — с синим фоном:

=СУММ(VALUESBYCOLOR(“светло-васильковый 3”, “”, B2:E2))

Кончик. Подробное руководство по надстройке доступно здесь, а запись в блоге с примерами — здесь.

Как видите, надстройка использует стандартную функцию SUM вместе со специальной функцией внутри: VALUESBYCOLOR.

Функция ЗНАЧ.ПОЦВЕТУ

VALUESBYCOLOR — это наша пользовательская функция.

Примечание. Вы не найдете его в электронных таблицах без надстройки.

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

=VALUESBYCOLOR(“светло-васильковый 3”, “”, B2:E2)

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

Довольно круто, да? 😉

Что ж, дополнение упустило кое-что. Эту формулу нельзя было использовать в СУММЕСЛИМН и СЧЁТЕСЛИМН, поэтому вы по-прежнему не могли считать по нескольким условиям, таким как общий оттенок и содержимое ячеек одновременно. И нас об этом много спрашивали!

Я рад сообщить вам, что мы сделали это возможным благодаря последнему обновлению (октябрь 2021 г.)! Теперь Функция по цвету содержит еще 2 пользовательские функции, которые вам в этом помогут 🙂

Дополнительные функции функции по цвету

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

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

Кончик. Посмотрите это видео или продолжите чтение. Или сделайте и то, и другое для лучшего понимания 😉 Там даже есть Практическая таблица доступна в конце сообщения в блоге. 😉

VALUESBYCOLORALL

Эта пользовательская функция требует 3 аргумента:

VALUESBYCOLORALL(fill_color, font_color, диапазон)

• Цвет заливки — Код RGB или название цвета (в соответствии с цветовой палитрой Google Sheets) для цвета фона.

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

• Цвет шрифта — Код RGB или название цвета (в соответствии с цветовой палитрой Google Sheets) для цвета текста.

  Кончик. Аргумент также является обязательным, но также требует пары двойных кавычек “”, когда вам нужно игнорировать цвет шрифта.

• диапазон — здесь ничего особенного, просто диапазон ячеек, которые вы хотите обработать.

Вы заметили, что VALUESBYCOLORALL можно легко принять за функцию VALUESBYCOLOR, используемую дополнением? Будьте осторожны, разница огромная. Взгляните на этот скриншот:

Формулы написаны в B2 и C2, но вы можете посмотреть, как они выглядят в B8 и C8 соответственно:

=VALUESBYCOLOR(“светло-зеленый 3”, “”, A2:A7)

а также

=VALUESBYCOLORALL(“светло-зеленый 3”, “”, A2:A7)

Кончик. Названия цветов взяты из палитры Google Sheets:

У этих двух функций одни и те же аргументы, и даже их имена очень похожи!

Тем не менее, они возвращают разные наборы данных:

• VALUESBYCOLOR возвращает список только тех записей, которые отображаются с зеленым цветом заливки в столбце A. Результат этой формулы занимает всего 3 ячейки: B2:B4.
• VALUESBYCOLORALL, в свою очередь, возвращает диапазон того же размера, что и исходный (6 ячеек) — C2:C7. Но ячейки в этом диапазоне содержат записи только в том случае, если соответствующая ячейка в столбце А имеет требуемый цвет заливки. Остальные ячейки остаются пустыми.

Даже если это может показаться вам одним и тем же, это имеет огромное значение в сочетании с другими функциями. И именно это позволяет вам проверять цвета вместе с содержимым ячеек с помощью таких функций, как СЧЁТЕСЛИМН или же СУММЕСЛИМН.

CELLCOLOR

Следующая функция довольно проста: она проверяет цвета ячеек и возвращает список названий цветов или кодов RGB (по вашему выбору), используемых в каждой ячейке. Он даже называется так же: CELLCOLOR.

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

Эта функция также требует 3 аргумента:

CELLCOLOR(диапазон, источник_цвета, имя_цвета)

• диапазон — те ячейки, которые вы хотите проверить на цвета.
• цвет_источник — сообщает функции, где смотреть:
  • использовать слово “наполнять” в двойных кавычках для проверки цвета фона
  • “шрифт” — для цветов текста
  • “оба” — для цветов заливки и текста
• имя_цвета — ваш способ сказать, какое имя вернуть:
  • ПРАВДА дает вам имена, которые вы видите в палитре Google Sheets, например красный или же темно-синий 1
  • ЛОЖНЫЙ получает RGB-коды цветов, например #ff0000 или же #3d85c6

Например, приведенная ниже формула возвращает список цветов заливки и шрифта, используемых в каждой ячейке формата A2:A7:

=CELLCOLOR(A2:A7, “оба”, TRUE)

Так как же эти функции можно использовать с ЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИМН? Как вы настраиваете критерии поиска на основе цветов?

Суммировать и считать ячейки по цвету и содержимому — примеры формул

Давайте попробуем использовать VALUESBYCOLORALL и CELLCOLOR в нескольких простых случаях.

ЕСЛИ цвет, то…

Здесь у меня есть краткий список студентов, прошедших 3 теста:

Я хочу пометить строку PASS в столбце E, только если все ячейки в строке зеленые (студенты, сдавшие все экзамены). Я буду использовать наш CELLCOLOR в функции ЕСЛИ, чтобы проверить цвета и вернуть нужную строку:

=ЕСЛИ(СЧЁТЕСЛИ(ЦВЕТ КЛЕТКИ(B2:D2,”заполнить”,ИСТИНА),”светло-зелёный 3″)=3,”ПРОШЕЛ”,””)

Вот что он делает:

1. CELLCOLOR(B2:D2,”заливка”,ИСТИНА) возвращает все цвета заливки, используемые в строке.
2. СЧЁТЕСЛИ(CELLCOLOR(B2:D2,”заливка”,ИСТИНА),”светло-зелёный 3″)=3 берет эти цвета и проверяет, появляется ли «светло-зеленый 3» (который я использую в своих ячейках) ровно 3 раза подряд.
3. Если это так, ЕСЛИ возвращает «ПРОШЕЛ», в противном случае ячейка остается пустой.

СЧЁТЕСЛИМН: подсчитайте по цветам и значениям с помощью 1 формулы

СЧЁТЕСЛИМН — это ещё одна функция, которая, наконец, может считать по нескольким критериям, даже если один из них — цвет.

Допустим, есть записи прибыли за смену и на одного работника:

Используя две наши пользовательские функции внутри COUNTIFS, я могу подсчитать, сколько раз каждый сотрудник реализовал план продаж (зеленые ячейки).

Пример 1. COUNTIFS + CELLCOLOR

Я перечислю всех руководителей рядом с таблицей с данными и введу отдельную формулу для каждого сотрудника. Начну с CELLCOLOR:

=СЧЁТЕСЛИМН($A$2:$A$10,E2,CELLCOLOR($C$2:$C$10,”заполнить”,ИСТИНА),”светло-зелёный 3″)

1. Первое, что проверяет формула, — это столбец A: если там есть «Лила» (имя из E2), она принимает во внимание запись.
2. Во-вторых, мне нужно проверить, окрашены ли ячейки в столбце C светло-зеленым цветом 3.

   Кончик. Проверьте цвет ячейки с помощью палитры Google Sheets:
   

Поскольку СЧЁТЕСЛИМН сам по себе не может просто подобрать цвет, я использую наш CELLCOLOR в качестве диапазона условия.

Запомнить, CELLCOLOR возвращает список цветов, используемых в каждой ячейке.. Когда я вставляю его в COUNTIFS, последний сканирует этот список в поисках всех вхождений «светло-зеленого 3». Это в сочетании с именем из столбца E дает требуемый результат. Очень просто 🙂

Пример 2. COUNTIFS + VALUESBYCOLORALL

То же самое произойдет, если вместо этого вы выберете VALUESBYCOLORALL. Введите его как диапазон для второго условия:

=СЧЁТЕСЛИМН($A$2:$A$10,E2,VALUESBYCOLORALL(“светло-зелёный 3″,””,$C$2:C$10),”<>“)

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

Поэтому, когда VALUESBYCOLORALL ставится в COUNTIFS, формула считает только те ячейки, которые не пусты: «<>» (или, другими словами, соответствуют требуемому цвету).

СУММЕСЛИМН: суммирование ячеек по цветам и значениям с помощью 1 формулы

История с СУММЕСЛИМН похожа на с СЧЁТЕСЛИМН:

1. Возьмите одну из наших пользовательских функций: CELLCOLOR или же VALUESBYCOLORALL.
2. Поместите это как диапазон, который должен быть проверен на цвета.
3. Введите условие в зависимости от выбранной вами функции: название цвета для CELLCOLOR и «не пустой» («<>») для VALUESBYCOLORALL.

Примечание. СУММЕСЛИМН не принимает в качестве первого аргумента ничего, кроме простого диапазона — сумма_диапазон. Если вы попытаетесь внедрить туда одну из наших пользовательских функций, формула просто не сработает. Так что имейте это в виду и не забудьте ввести CELLCOLOR и VALUESBYCOLORALL как критерий вместо.

Вот несколько примеров.

Пример 1. СУММЕСЛИМН + ЦВЕТ КЛЕТКИ

Посмотрите на эту формулу:

=СУММЕСЛИМН($C$2:$C$10,A$2:A$10,E2,CELLCOLOR($C$2:$C$10,”заполнить”,ИСТИНА),”светло-зеленый 3″)

1. CELLCOLOR получает все цвета заливки из C2:C10, а СУММЕСЛИМН проверяет, является ли какой-либо из них «светло-зеленым 3».
2. СУММЕСЛИМН также сканирует A2:A10 в поисках имени из E2 — Лила.
3. Как только оба условия соблюдены, количество C2:C10 добавляется к общему количеству.

Пример 2. СУММЕСЛИМН + ЗНАЧ. ПО ЦВЕТУ ВСЕ

То же самое происходит с VALUESBYCOLORALL:

=СУММЕСЛИМН($C$2:$C$10,$A$2:$A$10,E2,VALUESBYCOLORALL(“светло-зеленый 3″,””,$C$2:$C$10),”<>“)

1. VALUESBYCOLORALL возвращает диапазон, в котором значения содержатся только в ячейках требуемого цвета заливки. СУММЕСЛИМН учитывает все непустые ячейки.
2. СУММЕСЛИМН также сканирует A2: A10 в поисках «Лилы» из E2.
3. Как только оба условия выполнены, соответствующее количество из C2:C10 суммируется.

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

Таблица для практики

Функция по цвету – пользовательские функции – примеры (сделать копию таблицы)

Вас также могут заинтересовать