Сумма по цвету ячейки в Excel: формула

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

Инструкция

Чтобы вы поняли, как использовать формулу суммы ячеек по цвету в Excel программе – я придумал простой пример. У нас есть 10 ячеек, где желтым подсвечены те, числа которых больше 50. Остальные не имеют заливку.

Советую также глянуть урок по выделению ячеек цветом по выбранному условию в Excel.

Наша задача эту сумму посчитать:

Заходим в «Формулы».

Выбираем кнопку «Диспетчер имен».

Кликаем по кнопке «Создать».

В имя вводим любое наименование – например:

ЦветЯчейки

ПРИМЕЧАНИЕ! Неважно что именно вы введете, это имя мы будем использовать в качестве подсчетов.

В «Диапазон» просто впишите формулу:

=ПОЛУЧИТЬ.ЯЧЕЙКУ(63;ДВССЫЛ(«RC[-1]»;0))

Если у вас англоязычная программа, то используем:

=GET.CELL(63;INDIRECT(«RC[-1]»;0))

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

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

=ЦветЯчейки (или то, что вы ввели в поле «Имя»)

Используем маркер автозаполнения и копируем этот расчет во все соседние ячейки.

Читаем – что такое маркер автозаполнения.

Числа 6 – это по сути код заливки желтым цветом. 0 – значит заливки нет. Давайте для примера попробуем сделать заливку соседних ячеек красным цветом. Не важно по какому условию, наша задача сейчас посмотреть, как вообще работает эта штука.

ВНИМАНИЕ! Если вы измените заливку, то во втором столбце числа не поменяются. Поэтому нужно заново делать расчет, а именно шаги 6-7. Или вы можете использовать для перерасчета горячие клавиши: Ctl + Alt + F.

Как видите, у красного цвета – код другой.

Ну и теперь давайте посчитаем сумму желтых ячеек с помощью формулы:

=СУММЕСЛИ(B2:B10;6;A2:A10)

Или

=SUMIF(B2:B10;6;A2:A10)

Кратко рассмотрим логику самой формулы. Мы берем сначала диапазон ячеек с выбранными кодами. В нашем случае 6 и 3. Далее выбираем только выбранный код – то есть 6. Ну и дальше считаем сумму уже ячеек с числами.

Повторяем ту же самую процедуру, но уже для красных ячеек.

ВНИМАНИЕ! Обязательно сохраните документ в формате с поддержкой макросов.

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

Видео

YouTube