Счет если по цвету ячейки. Закрасить ячейку по условию или формуле

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

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

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

Итак, приступим. Зайдите в редактор Visual Basic, для этого:

в Excel 2003 нажмите на Сервис , далее Макрос и затем Редактор Visual Basic .

в Excel 2007, 2010 и 2013 это делается по-другому. Зайдите в раздел Разработчик , далее выберите Visual Basic

Внимание! Раздел панели инструментов Разработчик в Excel 2007 доступен по умолчанию, а в Excel 2010 и 2013 его необходимо включить. Это особенно полезно сделать тем пользователям, которые будут часто работать с макросами. Чтобы включить панель инструментов Разработчик в Excel 2010 или 2013 необходимо запустить Файл | Параметры | Настройка ленты после этого необходимо с правой стороны необходимо поставить галочку напротив надписи Разработчик

После того как откроется редактор Visual Basic, вставьте пустой модуль, для этого выберите меню Insert и далее Module

Public Function ColorNom (Cell As Range)
ColorNom = Cell.Font.ColorIndex
End Function

Важно! Вы не сможете находить с помощью данной функции номер цвета ячейки при использовании условного форматирования. Кроме того, при изменении цвета ячейки Excel не пересчитывает значения, необходимо это делать в ручную, нажимая Ctrl+Alt+F9, либо изменения будут происходить при новом открытии данного файла. Это происходит из-за того, что Excel не считает изменение цвета ячейки редактированием формулы. В связи с этим, если это критично, то можно внести изменение в саму формулу, просто добавив функцию, которая постоянно пересчитывается и при этом не повлияет на определение цвета ячейки. Например, указать функцию определения текущей даты, умноженную на ноль.
В нашем случае функция будет выглядеть следующем образом.

=ColorNom (A1)+Сегодня()*0

Пример подсчета количества значений по цвету цвету заливки ячеек в Excel

Рассмотрим вышеуказанный пример с перечнем фруктов. Мы определили код ячеек и отобразили его напротив каждой ячейки.

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

СЧЁТЕСЛИ(диапазон ;критерий )

Пропишем формулу:

СЧЁТЕСЛИ($B$1:$B$8 ;E2 )

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

Скачать пример файла: (файл с поддержкой макросов)

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

Один щелчок для подсчета, суммирования и усреднения цветных ячеек в Excel

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

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

1 , Выберите пустые ячейки, чтобы войти в функцию SUBTOTAL.

1. Чтобы подсчитать все ячейки с одинаковым цветом фона, введите формулу = ВСЕГО (102, E2: E20) ;
2. Чтобы сложить все ячейки с одинаковым цветом фона, введите формулу = ВСЕГО (109, E2: E20) ;

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

2 , Выберите заголовок таблицы и нажмите дата > Фильтр , Смотрите скриншот:

3 , Нажмите значок фильтра в ячейке заголовка столбца Сумма и нажмите Фильтр по цвету и указанный цвет вы будете считать последовательно. Смотрите скриншот:

После фильтрации обе СУБТОТАЛЬНЫЕ формулы автоматически подсчитывают и суммируют все отфильтрованные цветовые ячейки в столбце Количество. Смотрите скриншот:

Внимание : Этот метод требует, чтобы цветные ячейки, которые вы будете считать или суммировали, находились в одном столбце.

Подсчет или суммирование цветных ячеек с помощью функции GET.CELL

В этом методе мы создадим именованный диапазон с помощью функции GET.CELL, получим код цвета ячеек, а затем легко посчитаем или суммируем по коду цвета в Excel. Пожалуйста, сделайте следующее:

1 . Щелчок Формулы > Определить имя , Смотрите скриншот:

2 , В диалоговом окне «Новое имя» сделайте, как показано ниже:
(1) Введите имя в поле «Имя»;
(2) Введите формулу = GET.CELL (38, Sheet4! $ E2) в поле Относится к (внимание : в формуле, 38 означает вернуть код ячейки, и Sheet4! $ E2 является первой ячейкой в ​​столбце Сумма, кроме заголовка столбца, который необходимо изменить на основе данных таблицы.)
(3) Нажмите OK Кнопка.

3 , Теперь добавьте новый столбец Color прямо к исходной таблице. Далее введите формулу = NumColor и перетащите маркер автозаполнения, чтобы применить формулу к другим ячейкам в столбце «Цвет». Смотрите скриншот:
Внимание : В формуле, NumColor это именованный диапазон, который мы указали в первых шагах 2. Вам нужно изменить его на указанное вами имя.

Теперь код цвета каждой ячейки в столбце Количество возвращается в столбце Цвет. Смотрите скриншот:

4 , Скопируйте и укажите цвет заливки в пустом диапазоне на активном листе и введите формулы рядом с ним, как показано на снимке экрана ниже:
А. Для подсчета клеток по цвету, пожалуйста, введите формулу = COUNTIF ($ F $ 2: $ F $ 20, NumColor) ;
B. Для суммирования ячеек по цвету, пожалуйста, введите формулу = СУММЕСЛИ ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20) .

Внимание : В обеих формулах $ F $ 2: $ F $ 20 столбец Цвет, NumColor указанный именованный диапазон, $ E $ 2: $ E $ 20 это столбец суммы, и вы можете изменить их, как вам нужно.

Теперь вы увидите, что ячейки в столбце Количество подсчитываются и суммируются по цветам заливки.

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

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

1 , Удерживайте ALT + F11 и открывает Microsoft Visual Basic для приложений окна.

2 . Щелчок Вставить > модуль , и вставьте следующий код в окно модуля.

VBA: подсчет и суммирование ячеек в зависимости от цвета фона:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function

3 , Затем сохраните код и примените следующую формулу:
А. Подсчитайте цветные клетки: = Colorfunction (А, В, С, значение FALSE)
Б. Суммируйте цветные ячейки: = Colorfunction (A, B, C, TRUE),

Примечание: в приведенных выше формулах A является ячейка с конкретным цветом фона, который вы хотите рассчитать, подсчет и сумма, и ДО НАШЕЙ ЭРЫ диапазон ячеек, в котором вы хотите рассчитать количество и сумму.

4 , Например, сделайте следующий скриншот, введите формулу= Colorfunction (A1, A1: D11, FALSE) для подсчета желтых клеток. И используйте формулу = Colorfunction (A1, A1: D11, TRUE) суммировать желтые клетки. Смотрите скриншот:

5 , Если вы хотите подсчитать и суммировать другие цветные ячейки, повторите шаг 4. Затем вы получите следующие результаты:

Подсчет и суммирование ячеек на основе определенного цвета заливки с помощью функций Kutools

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

1 , Выберите пустую ячейку, в которую вы помещаете результаты подсчета, и нажмите Kutools > Функции Kutools > > COUNTBYCELLCOLOR , Смотрите скриншот:

2 , В диалоговом окне «Аргументы функции» укажите диапазон, в котором вы будете считать цветные ячейки в Справка выберите ячейку, которая заполнена указанным цветом фона в Color_index_nr и нажмите OK кнопка. Смотрите скриншот:

Заметки:
(1) Вы также можете ввести указанную функцию Kutools = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) непосредственно в пустой ячейке или строке формул, чтобы получить результаты подсчета;
(2) Нажмите Kutools > Функции Kutools > Статистический и математический > SUMBYCELLCOLOR или типа = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) непосредственно в пустую ячейку для суммирования ячеек на основе заданного цвета фона.
Применить COUNTBYCELLCOLOR и SUMBYCELLCOLOR функции для каждого цвета фона отдельно, и вы получите результаты, как показано на скриншоте ниже:

Функции Kutools содержит ряд встроенных функций, которые помогут пользователям Excel легко рассчитать, в том числе Количество / Сумма / Среднее видимых ячеек , Подсчет / Сумма по цвету ячейки , Подсчет / сумма по цвету шрифта , Считать персонажей , Подсчет шрифтом жирным шрифтом , И т.д. Бесплатная пробная версия!

Ячейки Count и Sum, основанные на конкретном цвете заполнения с помощью Kutools for Excel

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

1 , Выберите диапазон, который вы хотите использовать, и нажмите Kutools Plus > По цвету , см. снимок экрана:

2 . И в По цвету диалоговое окно, пожалуйста, сделайте, как показано на скриншоте ниже:
(1) Выберите Стандартное форматирование из Метод цвета выпадающий список;
(2) Выберите Задний план из Тип счета выпадающий список.
(3) Нажмите кнопку «Создать отчет».


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

Теперь вы получите новую рабочую тетрадь со статистикой. Смотрите скриншот:

При работе с таблицами первоочередное значение имеют выводимые в ней значения. Но немаловажной составляющей является также и её оформление. Некоторые пользователи считают это второстепенным фактором и не обращают на него особого внимания. А зря, ведь красиво оформленная таблица является важным условием для лучшего её восприятия и понимания пользователями. Особенно большую роль в этом играет визуализация данных. Например, с помощью инструментов визуализации можно окрасить ячейки таблицы в зависимости от их содержимого. Давайте узнаем, как это можно сделать в программе Excel.

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

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

Но выход существует. Для ячеек, которые содержат динамические (изменяющиеся) значения применяется условное форматирование, а для статистических данных можно использовать инструмент «Найти и заменить» .

Способ 1: условное форматирование

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

Посмотрим, как этот способ работает на конкретном примере. Имеем таблицу доходов предприятия, в которой данные разбиты помесячно. Нам нужно выделить разными цветами те элементы, в которых величина доходов менее 400000 рублей, от 400000 до 500000 рублей и превышает 500000 рублей.

1. Выделяем столбец, в котором находится информация по доходам предприятия. Затем перемещаемся во вкладку «Главная» . Щелкаем по кнопке «Условное форматирование» , которая располагается на ленте в блоке инструментов «Стили» . В открывшемся списке выбираем пункт «Управления правилами…» .



2. Запускается окошко управления правилами условного форматирования. В поле «Показать правила форматирования для» должно быть установлено значение «Текущий фрагмент» . По умолчанию именно оно и должно быть там указано, но на всякий случай проверьте и в случае несоответствия измените настройки согласно вышеуказанным рекомендациям. После этого следует нажать на кнопку «Создать правило…» .



3. Открывается окно создания правила форматирования. В списке типов правил выбираем позицию . В блоке описания правила в первом поле переключатель должен стоять в позиции «Значения» . Во втором поле устанавливаем переключатель в позицию «Меньше» . В третьем поле указываем значение, элементы листа, содержащие величину меньше которого, будут окрашены определенным цветом. В нашем случае это значение будет 400000 . После этого жмем на кнопку «Формат…» .



4. Открывается окно формата ячеек. Перемещаемся во вкладку «Заливка» . Выбираем тот цвет заливки, которым желаем, чтобы выделялись ячейки, содержащие величину менее 400000 . После этого жмем на кнопку «OK» в нижней части окна.



5. Возвращаемся в окно создания правила форматирования и там тоже жмем на кнопку «OK» .



6. После этого действия мы снова будем перенаправлены в Диспетчер правил условного форматирования . Как видим, одно правило уже добавлено, но нам предстоит добавить ещё два. Поэтому снова жмем на кнопку «Создать правило…» .



7. И опять мы попадаем в окно создания правила. Перемещаемся в раздел «Форматировать только ячейки, которые содержат» . В первом поле данного раздела оставляем параметр «Значение ячейки» , а во втором выставляем переключатель в позицию «Между» . В третьем поле нужно указать начальное значение диапазона, в котором будут форматироваться элементы листа. В нашем случае это число 400000 . В четвертом указываем конечное значение данного диапазона. Оно составит 500000 . После этого щелкаем по кнопке «Формат…» .



8. В окне форматирования снова перемещаемся во вкладку «Заливка» , но на этот раз уже выбираем другой цвет, после чего жмем на кнопку «OK» .



9. После возврата в окно создания правила тоже жмем на кнопку «OK» .



10. Как видим, в Диспетчере правил у нас создано уже два правила. Таким образом, осталось создать третье. Щелкаем по кнопке «Создать правило» .



11. В окне создания правила опять перемещаемся в раздел «Форматировать только ячейки, которые содержат» . В первом поле оставляем вариант «Значение ячейки» . Во втором поле устанавливаем переключатель в полицию «Больше» . В третьем поле вбиваем число 500000 . Затем, как и в предыдущих случаях, жмем на кнопку «Формат…» .



12. В окне «Формат ячеек» опять перемещаемся во вкладку «Заливка» . На этот раз выбираем цвет, который отличается от двух предыдущих случаев. Выполняем щелчок по кнопке «OK» .



13. В окне создания правил повторяем нажатие на кнопку «OK» .



14. Открывается Диспетчер правил . Как видим, все три правила созданы, поэтому жмем на кнопку «OK» .



15. Теперь элементы таблицы окрашены согласно заданным условиям и границам в настройках условного форматирования.



16. Если мы изменим содержимое в одной из ячеек, выходя при этом за границы одного из заданных правил, то при этом данный элемент листа автоматически сменит цвет.

Кроме того, можно использовать условное форматирование несколько по-другому для окраски элементов листа цветом.

Способ 2: использование инструмента «Найти и выделить»

Если в таблице находятся статические данные, которые не планируется со временем изменять, то можно воспользоваться инструментом для изменения цвета ячеек по их содержимому под названием «Найти и выделить» . Указанный инструмент позволит отыскать заданные значения и изменить цвет в этих ячейках на нужный пользователю. Но следует учесть, что при изменении содержимого в элементах листа, цвет автоматически изменяться не будет, а останется прежним. Для того, чтобы сменить цвет на актуальный, придется повторять процедуру заново. Поэтому данный способ не является оптимальным для таблиц с динамическим содержимым.

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

1. Выделяем столбец с данными, которые следует отформатировать цветом. Затем переходим во вкладку «Главная» и жмем на кнопку «Найти и выделить» , которая размещена на ленте в блоке инструментов «Редактирование» . В открывшемся списке кликаем по пункту «Найти» .



2. Запускается окно «Найти и заменить» во вкладке «Найти» . Прежде всего, найдем значения до 400000 рублей. Так как у нас нет ни одной ячейки, где содержалось бы значение менее 300000 рублей, то, по сути, нам нужно выделить все элементы, в которых содержатся числа в диапазоне от 300000 до 400000 . К сожалению, прямо указать данный диапазон, как в случае применения условного форматирования, в данном способе нельзя.

   Но существует возможность поступить несколько по-другому, что нам даст тот же результат. Можно в строке поиска задать следующий шаблон «3?????» . Знак вопроса означает любой символ. Таким образом, программа будет искать все шестизначные числа, которые начинаются с цифры «3» . То есть, в выдачу поиска попадут значения в диапазоне 300000 – 400000 , что нам и требуется. Если бы в таблице были числа меньше 300000 или меньше 200000 , то для каждого диапазона в сотню тысяч поиск пришлось бы производить отдельно.

   Вводим выражение «3?????» в поле «Найти» и жмем на кнопку «Найти все ».



3. После этого в нижней части окошка открываются результаты поисковой выдачи. Кликаем левой кнопкой мыши по любому из них. Затем набираем комбинацию клавиш Ctrl+A . После этого выделяются все результаты поисковой выдачи и одновременно выделяются элементы в столбце, на которые данные результаты ссылаются.



4. После того, как элементы в столбце выделены, не спешим закрывать окно «Найти и заменить» . Находясь во вкладке «Главная» в которую мы переместились ранее, переходим на ленту к блоку инструментов «Шрифт» . Кликаем по треугольнику справа от кнопки «Цвет заливки» . Открывается выбор различных цветов заливки. Выбираем тот цвет, который мы желаем применить к элементам листа, содержащим величины менее 400000 рублей.



5. Как видим, все ячейки столбца, в которых находятся значения менее 400000 рублей, выделены выбранным цветом.



6. Теперь нам нужно окрасить элементы, в которых располагаются величины в диапазоне от 400000 до 500000 рублей. В этот диапазон входят числа, которые соответствуют шаблону «4??????» . Вбиваем его в поле поиска и щелкаем по кнопке «Найти все» , предварительно выделив нужный нам столбец.



7. Аналогично с предыдущим разом в поисковой выдаче производим выделение всего полученного результата нажатием комбинации горячих клавиш CTRL+A . После этого перемещаемся к значку выбора цвета заливки. Кликаем по нему и жмем на пиктограмму нужного нам оттенка, который будет окрашивать элементы листа, где находятся величины в диапазоне от 400000 до 500000 .



8. Как видим, после этого действия все элементы таблицы с данными в интервале с 400000 по 500000 выделены выбранным цветом.



9. Теперь нам осталось выделить последний интервал величин – более 500000 . Тут нам тоже повезло, так как все числа более 500000 находятся в интервале от 500000 до 600000 . Поэтому в поле поиска вводим выражение «5?????» и жмем на кнопку «Найти все» . Если бы были величины, превышающие 600000 , то нам бы пришлось дополнительно производить поиск для выражения «6?????» и т.д.



10. Опять выделяем результаты поиска при помощи комбинации Ctrl+A . Далее, воспользовавшись кнопкой на ленте, выбираем новый цвет для заливки интервала, превышающего 500000 по той же аналогии, как мы это делали ранее.



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



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

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

Для выполнения данной операции необходим пакет утилит Excel под названием .

Вообще, подсчет по цветам удобен в том случае, если вы работаете с цветной таблицей.

Как подсчитать и просуммировать количество ячеек по цвету фона или шрифта в Excel

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

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

Подсчет количества ячеек по цвету фона в Excel

Шаг 1: Откройте лист, содержащий цветные ячейки. Нажмите на любую пустую ячейку, в которую нужно поместить результат.

Шаг 2: Теперь откройте вкладку ASAP Utilities. Из выпадающего списка Формулы выберите 12. Вставить функцию из библиотеки

Шаг 3: После этого появится диалоговое окно, содержащее множество формул. Из того же списка, выберите функцию ASAPCOUNTBYCELLCOLOR и нажмите кнопку OK.

Шаг 4: Теперь появится диалоговое окно с аргументами функции. Укажите их и нажмите кнопку ОК.

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

После нажатия на ОК вам будет показан результат:

Таким образом в Excel легко подсчитать количество ячеек по цвету фона.

Подсчет количества ячеек по цвету шрифта

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

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

Как просуммировать ячейки по цвету фона или шрифта в Excel

Сумма ячеек по цвету фона

Для суммирования ячеек нужно выполнить следующее:

Шаг 1: Откройте лист Excel, имеющий цветные ячейки со значениями в них. Выберите конкретную ячейку, в которой нужно отобразить результат.

Шаг 2: Перейдите на вкладку в разделе формул опять-таки выберите раздел 12. Вставить функцию из библиотеки .

Шаг 3: Перечень функций прокрутите список вниз и выберите функцию ASAPSUMBYCELLCOLOR и нажмите кнопку OK.

Шаг 4: О ткроется диалоговое окно с аргументами функции. Здесь также нужно указать диапазон и адрес ячейки, фон которой учитывается при суммировании остальных ячеек. Нажмите кнопку OK и порадуйтесь результату.

Вот таким образом можно просуммировать ячейки по цвету фона. И весь процесс вряд ли занимает несколько секунд.

Сумма ячеек по цвету шрифта в них

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

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

Вывод

Использование пакета ASAP Utilities позволило быстро произвести подсчеты в ячейках с учетом цвета ячеек и цвета шрифта текста. ASAP Utilities гармонично вливается в интерфейс Excel, что весьма удобно для пользователя. Данный способ работы с цветными ячейки намного проще, нежели написание специальных макросов.

Оставьте свой комментарий!

Профессиональная работа в Excel рано или поздно приведет вас к необходимости написания собственных функций. Довольно часто пользователи «раскрашивают» ячейки в разные цвета. Если потом возникает необходимость просуммировать значения в выделенных ячейках, то, к сожалению, в Excel нет такой стандартной функции. Тем не менее, сумма по цвету ячеек в Excel может быть найдена!

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

1. Убедитесь, что среди вкладок на ленте Excel, присутствует «Разработчик»:

Скачать заметку в формате Word

Скачать заметку в формате pdf

Скачать файл примера в формате Excel с поддержкой макросов . На основании комментариев добавил в Excel-файл код функции КолЦвет — определяет число ячеек выделенного цвета, СумНеЦвет — определяет сумму значений в ячейках выделенных любым цветом (не белого цвета)

2. Если такой закладки вы не видите, щелкните на кнопке Officeв левом верхнем углу и затем на кнопке «Параметры Excel»:

3. В открывшемся окне «Параметры Excel» перейдите на вкладку «Основные» и поставьте галочку в строке «Показывать меню Разработчик на ленте». Нажмите Ok

4. Создайте на листе Excel диапазон со значениями; несколько ячеек раскрасьте:

5. Перейдите на вкладку Разработчик и щелкните на VisualBasic:

6. У вас откроется окно VBA, содержащее окно VBAProject:

7. Если окна VBAProjectнет на экране

щелкните на меню View — Project Explorer:

8. Если вы хотите использовать создаваемый код VBA в любом Excel-файле, вам следует его записать в VBAProject, относящийся к Personal.xlsb. Если вы хотите применять код только в одном файле, с которым вы сейчас работаете, сохраните код в VBAProject`е именно этого файла (в нашем случае Сумма по цвету.xlsm):

9. Допустим, вы решили, что создаваемая функция будет использоваться в дальнейшем в различных файлах. Щелкните правой кнопкой мыши на VBAProject(Personal.xlsb) и выберите Insert — Module

Появится окно нового модуля, в которое следует перенести код:

Function СумЦвет(диапазон As Range, критерий As Range) As Double " Определяет сумму значений в ячейках "диапазона", " цвет которых совпадает с цветом в ячейке "критерий" Application.Volatile True Dim i As Range For Each i In диапазон If i.Interior.Color = критерий.Interior.Color Then СумЦвет = СумЦвет + i End If Next End Function

Номер вашего модуля (у меня он 5) будет зависеть от числа ранее созданных модулей.

Несколько слов о коде:

Function СумЦвет (диапазон AsRange, критерий AsRange) AsDouble/ Задает пользовательскую функция под названием СумЦвет с двумя параметрами: диапазоном суммирования и критерием – ячейкой, по цвету которой определяется, значения в каких ячейках суммировать.

Application.Volatile True/ Заставит нашу функцию пересчитываться при любом изменении значения в любой из ячеек на листе

Dim i AsRange/ Определяет переменную i, как диапазон ячеек

For Eachi In диапазон / Для всех ячеек из выбранного диапазона

If i.Interior.Color = критерий.Interior.Color Then/ Если цвет ячейки совпадает с критерием

СумЦвет = СумЦвет + i.Value/ то добавляем значение, хранящееся в ячейке в сумму

10. Вы создали пользовательскую функцию СумЦвет, которую можно найти в категории «Определенные пользователем»

11. Окно мастера функции выглядит также, как и для стандартной функции Excel

Хочу обратить ваше внимание на две особенности функции СумЦвет:

• При изменении значения в одной из ячеек происходит автоматический пересчет значения функции СумЦвет. Если вы поменяли только цвет ячейки , автоматический пересчет не произойдет. Нажмите F9.
• К сожалению, функция не работает, если ячейки раскрашены с помощью условного форматирования. Это ограничение можно преодолеть, если применить правила, использованные в условном форматировании, в функциях СУММЕСЛИ и СУММЕСЛИМН. Подробнее см.

На основании комментариев, появившихся после первой публикации заметки, добавил в Excel-файл код функций:

• КолЦвет – определяет число ячеек выделенного цвета
• СумНеЦвет – определяет сумму значений в ячейках выделенных любым цветом (не белого цвета)
• КолНеЦвет – определяет число выделенных ячеек (не белых)