Печенье помочь нам доставить наши услуги. Пользуясь нашими услугами, Вы соглашаетесь на использование нами куки.
Совет. Другие языки - Google-Translated. Вы можете посетить English версия этой ссылки.
Вход
x
or
x
x
Регистрация
x

or

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

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

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

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

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

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

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

С отличным По цвету особенность Kutools for ExcelВы можете быстро подсчитать, суммировать и усреднить ячейки по заданному цвету заливки или цвету шрифта одним щелчком мыши в Excel. Кроме того, эта функция также определяет максимальные и минимальные значения ячеек по цвету заливки или цвету шрифта. Полнофункциональная бесплатная пробная версия 60-day!
количество объявлений по цвету 2


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

Предположим, у нас есть таблица продаж фруктов, как показано на скриншоте ниже, и мы будем подсчитывать или суммировать цветные ячейки в столбце Количество. В этой ситуации мы можем отфильтровать столбец 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. Затем вы получите следующие результаты:


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

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

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

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

Kutools for Excel - Включает в себя более чем удобные инструменты Excel 300. Полнофункциональная бесплатная пробная версия 60-day, кредитная карта не требуется! Получите его сейчас!

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

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


Связанная статья:

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


Демо: подсчет и суммирование ячеек на основе фона, условное форматирование цвета:

В этом видео Kutools вкладка и Kutools Plus вкладка добавляются Kutools for Excel, Если нужно, пожалуйста, нажмите здесь иметь бесплатную пробную версию 60-дня без ограничений!



Рекомендуемые инструменты производительности

Office Tab

золото star1 Принесите удобные вкладки в Excel и другое программное обеспечение Office, как Chrome, Firefox и новый Internet Explorer.

Kutools for Excel

золото star1 Удивительно! Увеличьте производительность в течение 5 минут. Не нужно никаких специальных навыков, сэкономить два часа каждый день!

золото star1 Новые возможности 300 для Excel, сделать Excel намного проще и мощнее:

  • Объединить ячейки / строки / столбцы без потери данных.
  • Объединение и объединение нескольких листов и книг.
  • Сравнение диапазонов, копирование нескольких диапазонов, преобразование текста в дату, преобразование единиц и валют.
  • Подсчет количества цветов, Пейджинговые субтитры, Расширенный сортировка и Суперфильтр,
  • Подробнее Выбрать / Вставить / Удалить / Текст / Формат / Ссылка / Комментарий / Рабочие книги / Рабочие листы Инструменты ...

Снимок экрана Kutools для Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    AvGravy · 5 years ago
    Thank you so much! This is genius, thanks for the help!
  • To post as a guest, your comment is unpublished.
    RAMON · 5 years ago
    I applied the formula but the cell said NAMES? what I need to fix
    • To post as a guest, your comment is unpublished.
      skyyang · 5 years ago
      Hello RAMON, maybe you didn’t copy the above code into the Module. You must copy the code into the Module first and save it, then apply the formulas. You can try it. If it doesn’t help you, please let me know.
      • To post as a guest, your comment is unpublished.
        Mike F · 2 years ago
        Skyyang,

        If I email you a document can you help me with it?

        Mike
      • To post as a guest, your comment is unpublished.
        Alexandra · 5 years ago
        Hello, It worked the first time, but now, when I have to add a cell to the sum... I tried introducing again the code, saving, writing the formula again... it says "name?"
        • To post as a guest, your comment is unpublished.
          Daria · 5 years ago
          I'm having the same problem -- worked beautifully at first, but getting the NAME error after adding another cell. Have tried to delete module and add again, re-enter the function...no joy.
          • To post as a guest, your comment is unpublished.
            Gary_9991 · 4 years ago
            You might need to enable Marcos again. Should flash up at the top of the screen depending on version of excel. :)
  • To post as a guest, your comment is unpublished.
    Sankar Narayanan · 5 years ago
    Hi, getting error. complie error, ambiguous error is coming.
  • To post as a guest, your comment is unpublished.
    David Coe · 5 years ago
    This is great tool, thanks for sharing! I do have one question: I noticed that the macro formula does not update itself when you change a cell color from one color to another. Is there a way to get it to automatically update when cell colors change? If I double click on the cell with the addition formula and press enter, it updates, but I have a sheet with many of these cells and don't want to have to manually update it each time.
    • To post as a guest, your comment is unpublished.
      Louise · 5 years ago
      try CTL+ALT+f9 it will update all cells with addition formulas at once. Not quite automatic but at least better than having to click on each cell with addition formula individually.
    • To post as a guest, your comment is unpublished.
      Happiqiu · 5 years ago
      Hi, i have tried. and the worksheet just needs an "Enter" No matter which cell. So, after coloring your cell, just type a memo on the colored cells and "enter"
    • To post as a guest, your comment is unpublished.
      skyyang · 5 years ago
      Thank you for your reply, I’m sorry, under the current situation, the VBA code can’t solve the question that you pointed out, you need to update them manually.
      • To post as a guest, your comment is unpublished.
        Jako · 2 years ago
        I have also found that using the format painter to update the cell color will cause the formulas to work as expected. Still not perfect.
  • To post as a guest, your comment is unpublished.
    Shankee · 5 years ago
    Amazing,thanks a lot