Печиво допомогти нам доставити наші послуги. Користуючись нашими послугами, Ви погоджуєтесь на використання нами куки.
Порада. Інші мови - перекладена Google. Ви можете відвідати English версія цього посилання.
увійти
x
or
x
x
Реєстрація
x

or

Як розраховувати та сумувати клітинки на фоні кольору в Excel?

Якщо ви маєте діапазон клітин з різними фоновими кольорами, наприклад, червоний, зелений, синій і так далі, але тепер вам потрібно підрахувати, скільки осередків у цьому діапазоні мають певний колір фону і підсумувати кольорові клітини з однаковим кольором . У Excel немає прямої формули для обчислення суми і графа кольорових клітин, тут я познайомлюся з деякими способами вирішення цієї проблеми.

Обчислюйте та підсумовуйте клітинки на основі конкретного кольору заливки за допомогою фільтра та SUBTOTAL

Обчислюйте і підсумовуйте клітини на основі конкретного кольору заливки за допомогою функції GET.CELL

Кількість і суми клітин на основі конкретного кольору заповнення за допомогою функції, призначеної користувачем

Обчислюйте та підсумовуйте клітини на основі конкретного кольору заливки (або кольору умовного форматування) з Kutools для Excel

Один клік для підрахунку, суми та середніх кольорових клітин у Excel

З відмінним Граф за кольором особливість Kutools для Excel, Ви можете швидко підрахувати, підсумувати та перерахувати клітинки за вказаним кольором заливки або кольором шрифту лише одним клацанням миші в Excel. Крім того, ця функція також виявить максимальне та мінімальне значення осередків за кольором заливки або кольором шрифту. Повна функціональна безкоштовна пробна версія 60-день!
кількість оголошень за кольором 2


Обчислюйте та сумите кольорові клітинки за допомогою фільтра та SUBTOTAL

Припустимо, що у нас є таблиця продажів фруктів, як показано нижче, і ми підрахуємо або підрахуємо кольорові клітинки в колонці Сума. У цій ситуації ми можемо фільтрувати колонку "Сума" за кольором, а потім підраховувати або сумарно відфільтровувати кольорові клітини функцією SUBTOTAL у Excel.

1. Виберіть порожні клітинки, щоб увійти до функції SUBTOTAL.

  1. Щоб підрахувати всі клітинки з однаковим фоновим кольором, введіть формулу = SUBTOTAL (102, E2: E20);
  2. Щоб підсумувати всі клітинки з однаковим фоновим кольором, введіть формулу = SUBTOTAL (109, E2: E20);


примітки: В обох формулах E2: E20 - це стовпчик Сума, що містить кольорові клітинки, і ви можете змінити їх, як вам потрібно.

2. Виберіть заголовок таблиці та натисніть дані > фільтр. Див. Знімок екрана:

3. Натисніть піктограму Фільтр у колонці заголовка стовпця Сума і натисніть Фільтр за кольором і вказаний колір підраховується послідовно. Переглянути скріншот:

Після фільтрування обидві формули SUBTOTAL підраховують і підсумовують усі відфільтровані кольорові клітинки у стовпці Сума автоматично. Переглянути скріншот:

примітки: Для цього методу потрібні кольорові клітинки, які ви підраховуєте або сума у ​​одному стовпці.


Обчислюйте або сумміруйте клітини за допомогою функції GET.CELL

У цьому методі ми створимо іменований діапазон з функцією GET.CELL, отримаємо колірний код комірок, а потім легко розраховуємо або підраховуємо код кольору в Excel. Будь ласка, зробіть наступне:

1. Клацання Формули > Визначте ім'я. Див. Знімок екрана:

2. У діалоговому вікні "Нове ім'я" зробіть, як показано нижче:
(1) Введіть ім'я в полі Ім'я;
(2) Введіть формулу = GET.CELL (38, Sheet4! $ E2) у полі "Відноситься до" (увагу: у формулі 38 означає повернення коду комірки, і $ E4 є першою коміркою у стовпці Сума, окрім заголовка стовпця, який потрібно змінити на основі даних таблиці.)
(3) Натисніть кнопку OK кнопки.

3. Тепер додайте новий стовпець Колір прямо до початкової таблиці. Далі введіть формулу = NumColor і перетягніть ручку автозаповнення, щоб застосувати формулу до інших комірок у стовпці Колір. Переглянути скріншот:
примітки: У формулі NumColor є названий діапазон, який ми вказали в перших кроках 2. Потрібно змінити вказане ім'я.

Тепер колірний код кожної клітинки у стовпчику Сума повертається у стовпці Колір. Переглянути скріншот:

4. Скопіюйте та вкажіть колір заливки в порожньому діапазоні активного аркуша та введіть формули поруч із ним, як показано нижче:
A. Для підрахунку клітин за кольором введіть формулу = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Щоб підсумувати клітинки за кольором, введіть формулу = SUMIF ($ 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. Потім збережіть код і застосуйте наступну формулу:
A. Перерахуйте кольорові клітини: = кольоровафункція (A, B: C, FALSE)
B. Сума кольорових клітин: = кольоровафункція (A, B: C, TRUE)

Примітка: У наведених вище формулах A є клітинка з певним кольором тла, який потрібно обчислити підрахунок і суму, і B: C - це діапазон клітин, у якому потрібно обчислити кількість і суму.

4. Зробіть наступний скріншот, наприклад, введіть формулу= кольоровафункція (A1, A1: D11, FALSE) для підрахунку жовтих клітин. І використовуйте формулу = кольоровафункція (A1, A1: D11, TRUE) щоб підсумувати жовті клітини. Див. Знімок екрана:

5. Якщо ви хочете порахувати та підрахувати інші кольорові клітинки, будь ласка, повторіть крок 4. Тоді ви отримаєте наступні результати:


Кількість і кількість комірок на основі конкретного кольору заповнення з Kutools для Excel

З наведеною вище визначеною користувачем функцією, вам потрібно ввести формулу по одному, якщо є багато різних кольорів, цей спосіб буде нудним і трудомістким. Але якщо у вас є Kutools для ExcelАвтора Граф за кольором Утиліта, ви можете швидко створити звіт про кольорові клітинки. Ви можете не тільки розраховувати та сумувати кольорові клітини, але також отримати середні значення, макс і мінімум кольорового діапазону.

1. Виберіть діапазон, який потрібно використовувати, і натисніть Kutools More > Граф за кольором, див. скріншот:

2. І в Граф за кольором діалогове вікно, будь ласка, зробіть, як показано нижче:
(1) Виберіть Стандартне форматування від Колірний метод випадаючий список;
(2) Виберіть фон від Граф типу випадаючий список.
(3) Натисніть кнопку Створити звіт.

Kutools для Excel - Включає більше 300 зручних інструментів Excel. Повна функціональна безкоштовна пробна версія 60-день, не потрібна кредитна карта Отримати зараз!

примітки: Щоб підрахувати та підсумувати кольорові клітинки за певним кольором умовного форматування, виберіть Умовне форматування від Колірний метод випадаючого списку у діалоговому вікні вище, або виберіть Стандартне та умовне форматування зі спадного списку підрахувати всі клітинки, заповнені вказаним кольором.

Тепер ви отримаєте нову книгу зі статистикою. Переглянути скріншот:


Пов'язана стаття:

Як підрахувати / сумувати клітинки на основі кольорів шрифтів в Excel?


Демо: кількість і кількість елементів на фоні, умовне форматування:

У цьому відео Кутулс та вкладку Kutools More вкладка додана Kutools для Excel. Якщо потрібно, натисніть тут мати безкоштовну пробну версію 60 без обмеження!



Рекомендовані інструменти для роботи з продуктивністю

Вкладка Office

золота зірка1 Принесіть зручні вкладки для Excel та іншого програмного забезпечення Office, як і Chrome, Firefox та нового Internet Explorer.

Kutools для Excel

золота зірка1 Дивовижний! Збільште свою продуктивність за 5 хв. Не потрібно спеціальних навичок, заощаджуйте дві години щодня!

золота зірка1 Нові можливості 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