Pliki cookie pomagają nam dostarczać nasze usługi. Korzystając z naszych usług, zgadzasz się na nasze pomocą plików cookie.
Wskazówka: inne języki są tłumaczone przez Google. Możesz odwiedzić English wersja tego linku.
Zaloguj Się
x
or
x
x
Zarejestruj
x

or

Jak liczyć i sumować komórki na podstawie koloru tła w Excelu?

Przypuśćmy, że masz zakres komórek o różnych kolorach tła, takich jak czerwony, zielony, niebieski itd., Ale teraz musisz zliczyć, ile komórek w tym zakresie ma określony kolor tła i zsumować kolorowe komórki o tym samym kolorze. . W programie Excel nie ma bezpośredniej formuły obliczania sumy i liczby komórek kolorów, tutaj przedstawię Wam kilka sposobów rozwiązania tego problemu.

Komórki Count i Sum oparte na określonym kolorze wypełnienia przez Filtr i SUBTOTAL

Komórki Count i Sum oparte na określonym kolorze wypełnienia przez funkcję GET.CELL

Policz i podsumuj komórki na podstawie określonego koloru wypełnienia za pomocą funkcji zdefiniowanej przez użytkownika

Komórki Count i Sum oparte na określonym kolorze wypełnienia (lub kolorowaniu formatowania warunkowego) z Kutools for Excel

Jedno kliknięcie, aby policzyć, wyliczyć i ubarwić komórki w Excelu

Z doskonałymi Policz według koloru cechą Kutools dla programu Excelmożna szybko liczyć, sumować i uśredniać komórki według określonego koloru wypełnienia lub koloru czcionki za pomocą jednego kliknięcia w programie Excel. Poza tym funkcja ta będzie również znajdować wartości maksymalne i minimalne komórek według koloru wypełnienia lub koloru czcionki. Pełna wersja darmowej wersji próbnej 60-day!
liczba reklam według koloru 2


Zliczaj i sumuj kolorowe komórki według filtru i SUBTOTAL

Przypuśćmy, że mamy tabelę sprzedaży owoców pokazaną na poniższym zrzucie ekranu, a my policzymy lub zsumujemy kolorowe komórki w kolumnie Kwota. W tej sytuacji możemy przefiltrować kolumnę Ilość według koloru, a następnie zliczyć lub zsumować filtrowane kolorowe komórki przez funkcję SUBTOTAL łatwo w Excelu.

1. Wybierz puste komórki, aby wprowadzić funkcję SUBTOTAL.

  1. Aby policzyć wszystkie komórki o tym samym kolorze tła, wprowadź formułę = SUBTOTAL (102, E2: E20);
  2. Aby zsumować wszystkie komórki o tym samym kolorze tła, wprowadź formułę = SUBTOTAL (109, E2: E20);


Uwagi: W obu formułach E2: E20 jest kolumną Ilość zawierającą kolorowe komórki i można je zmienić w razie potrzeby.

2. Wybierz nagłówek tabeli i kliknij Dane > Filtr. Zobacz zrzut ekranu:

3. Kliknij ikonę Filtruj w komórce nagłówka kolumny Kwota i kliknij Filtr według koloru a określony kolor będzie liczony kolejno. Zobacz zrzut ekranu:

Po filtrowaniu automatycznie przeliczane są obydwie formuły SUBTOTAL zliczające i sumujące wszystkie przefiltrowane komórki kolorów w kolumnie Kwota. Zobacz zrzut ekranu:

Uwagi: Ta metoda wymaga kolorowych komórek, które zliczysz lub sumy znajdują się w tej samej kolumnie.


Zliczaj lub sumuj kolorowe komórki za pomocą funkcji GET.CELL

W tej metodzie utworzymy nazwany zakres za pomocą funkcji GET.CELL, otrzymamy kod koloru komórek, a następnie zliczymy lub zsumujemy kod koloru w Excelu. Wykonaj następujące czynności:

1, Kliknij Wzory > Zdefiniuj nazwę. Zobacz zrzut ekranu:

2. W oknie dialogowym Nowa nazwa wykonaj poniższy zrzut ekranu:
(1) Wpisz nazwę w polu Nazwa;
(2) Wprowadź formułę = GET.CELL (38, Sheet4! $ E2) w polu Odnosi się do pudełka (noty: w formule, 38 oznacza zwrócenie kodu komórki, i Sheet4! $ E2 jest pierwszą komórką w kolumnie Kwota, z wyjątkiem nagłówka kolumny, który należy zmienić na podstawie danych tabeli).
(3) Kliknij OK przycisk.

3. Teraz dodaj nową kolumnę koloru bezpośrednio do oryginalnej tabeli. Następnie wpisz formułę = NumColor i przeciągnij uchwyt Autouzupełniania, aby zastosować formułę do innych komórek w kolumnie Kolor. Zobacz zrzut ekranu:
Uwagi: W formule, NumColor to nazwany zakres określony w pierwszych krokach 2. Musisz zmienić go na określoną nazwę, którą ustawiłeś.

Teraz kod koloru każdej komórki w kolumnie Kwota jest zwracany w kolumnie Kolor. Zobacz zrzut ekranu:

4. Skopiuj i wypełnij kolor wypełnienia w pustym zakresie w aktywnym arkuszu, a następnie wpisz formuły obok niego, jak na poniższym zrzucie ekranu:
A. Aby zliczyć komórki według kolorów, wprowadź formułę = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Aby podsumować komórki według kolorów, wprowadź formułę = SUMAF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Uwagi: W obu formułach $ F $ 2: $ F $ 20 jest kolumną kolorów, NumColor jest określonym nazwanym zakresem, $ E $ 2: $ E $ 20 to kolumna Ilość, którą możesz zmienić według potrzeb.

Teraz zobaczysz, że komórki w kolumnie Kwota są zliczane i sumowane według ich kolorów wypełnienia.


Policz i podsumuj komórki na podstawie określonego koloru wypełnienia za pomocą funkcji zdefiniowanej przez użytkownika


Załóżmy, że kolorowe komórki rozproszyły się w zakresie, jak pokazano na zrzucie ekranu, obie powyższe metody nie mogą zliczyć ani zsumować kolorowych komórek. Tutaj ta metoda wprowadzi VBA, aby rozwiązać problem.

1. Przytrzymaj przycisk ALT + F11 klucze i otwiera Microsoft Visual Basic for Applications okno.

2, Kliknij wstawka > Modułi wklej następujący kod w oknie modułu.

VBA: Policz i podsumuj komórki na podstawie koloru tła:

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. Następnie zapisz kod i zastosuj następującą formułę:
A. Policz kolorowe komórki: = funkcja koloru (A, B: C, FALSE)
B. Sumuj kolorowe komórki: = funkcja koloru (A, B: C, PRAWDA)

Uwaga: w powyższych wzorach A jest komórką o określonym kolorze tła, który chcesz obliczyć liczbę i sumę, i PNE to zakres komórek, w którym chcesz obliczyć liczbę i sumę.

4. Zrób następujący zrzut ekranu, na przykład wprowadź formułę= funkcja koloru (A1, A1: D11, FALSE) liczyć żółte komórki. I użyj wzoru = funkcja koloru (A1, A1: D11, TRUE) aby zsumować żółte komórki. Zobacz zrzut ekranu:

5. Jeśli chcesz policzyć i zsumować inne kolorowe komórki, powtórz krok 4. Otrzymasz następujące wyniki:


Komórki Count i Sum oparte na określonym kolorze wypełnienia za pomocą Kutools for Excel

Dzięki powyższej funkcji zdefiniowanej przez użytkownika, musisz wprowadzić formułę jeden po drugim, jeśli istnieje wiele różnych kolorów, ta metoda będzie żmudna i czasochłonna. Ale jeśli masz Kutools dla programu Excel'S Policz według koloru narzędzie, możesz szybko wygenerować raport o kolorowych komórkach. Możesz nie tylko policzyć i zsumować kolorowe komórki, ale także uzyskać średnie, maksymalne i minimalne wartości w zakresie kolorów.

1. Wybierz zakres, którego chcesz użyć, i kliknij Kutools Plus > Policz według koloru, zobacz zrzut ekranu:

2. I w Policz według koloru okno dialogowe, zrób poniższy zrzut ekranu:
(1) Wybierz Standardowe formatowanie z Metoda kolorów Lista rozwijana;
(2) Wybierz tło z Typ liczenia Lista rozwijana.
(3) Kliknij przycisk Generuj raport.

Kutools dla programu Excel - Zawiera więcej niż 300 poręcznych narzędzi Excel. Pełna funkcja bezpłatna wersja próbna 60-dzień, bez karty kredytowej wymagane! Zdobądź to teraz!

Uwagi: Aby policzyć i zsumować kolorowe komórki według określonego kolorowania warunkowego, wybierz Formatowanie warunkowe z Metoda kolorów listę rozwijaną w powyższym oknie dialogowym lub wybierz Formatowanie standardowe i warunkowe z rozwijanej listy, aby zliczyć wszystkie komórki wypełnione określonym kolorem.

Teraz otrzymasz nowy skoroszyt ze statystykami. Zobacz zrzut ekranu:


Podobne artykuły:

Jak liczyć / podsumować komórki na podstawie kolorów czcionek w programie Excel?


Demo: Policz i podsumuj komórki na podstawie tła, kolorowania warunkowego:

W tym filmie wideo Kutools karta i Kutools Plus zakładki są dodawane przez Kutools dla programu Excel. W razie potrzeby kliknij tutaj mieć bezpłatny okres próbny 60 bez ograniczeń!



Zalecane narzędzia zwiększające wydajność

Karta Office

złota star1 Przynieś poręczne zakładki do Excela i innego oprogramowania Office, tak jak Chrome, Firefox i nowy Internet Explorer.

Kutools dla programu Excel

złota star1 Niesamowity! Zwiększ swoją produktywność za 5 minut. Nie potrzebujesz żadnych specjalnych umiejętności, oszczędzając dwie godziny dziennie!

złota star1 300 Nowe funkcje programu Excel, uczynienie z Excela bardzo prostym i wydajnym:

  • Scal komórki / wiersze / kolumny bez utraty danych.
  • Łącz i konsoliduj wiele arkuszy i zeszytów.
  • Porównaj zakresy, skopiuj wiele zakresów, przelicz tekst na datę, jednostkę i walutę.
  • Zliczaj według kolorów, podstrony stronicowania, sortowanie zaawansowane i super filtry,
  • Więcej Wybierz / Wstaw / Usuń / Tekst / Format / Link / Komentarz / Skoroszyty / Arkusze zadań Narzędzia ...

Zrzut ekranu z Kutools dla Excela

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.
    Troy Havard · 26 days ago
    I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
    • To post as a guest, your comment is unpublished.
      Jku · 10 days ago
      #NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
    • To post as a guest, your comment is unpublished.
      Troy Havard · 26 days ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 2 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 1 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 3 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 5 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.
  • To post as a guest, your comment is unpublished.
    Hredoy · 7 months ago
    i want to colour in excel by using if function.