Przejdź do głównej zawartości

Porady Excela: Licz/sumuj komórki według koloru (tło, czcionka, formatowanie warunkowe)

W codziennych zadaniach oznaczanie kolorami jest popularną metodą szybkiego rozróżnienia i podkreślenia kluczowych danych. Ale jak liczyć lub sumować dane komórek na podstawie określonego koloru (kolor wypełnienia, kolor czcionki, formatowanie warunkowe)? Domyślnie program Excel nie oferuje bezpośredniej funkcji liczenia ani sumowania według kolorów. Niemniej jednak, stosując pewne sztuczki i metody pośrednie, nadal możemy to osiągnąć. W tym artykule opisano, jak liczyć lub sumować dane według kolorów.

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

Licz i sumuj komórki na podstawie koloru czcionki

Licz i sumuj komórki na podstawie koloru formatowania warunkowego


Wideo: Liczenie i sumowanie komórek na podstawie koloru


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

Na przykład, jeśli masz zakres danych, w którym wartości są wypełnione różnymi kolorami tła, jak pokazano na zrzucie ekranu poniżej. Aby policzyć lub zsumować komórki na podstawie określonego koloru, Excel nie oferuje bezpośredniej funkcji zliczania lub sumowania komórek na podstawie ich koloru tła. Jednak przy odrobinie pomysłowości i kilku przydatnych technik możesz wykonać to zadanie. Przyjrzyjmy się kilku przydatnym metodom w tej sekcji.


Licz i sumuj komórki według koloru tła za pomocą funkcji zdefiniowanej przez użytkownika

Tutaj pokażemy, jak utworzyć i używać takiej funkcji zdefiniowanej przez użytkownika, aby rozwiązać to zadanie w programie Excel. Wykonaj następujące czynności:

Krok 1: Otwórz edytor modułów VBA i skopiuj kod

  1. Naciśnij przycisk Alt + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
  2. W otwartym oknie kliknij wstawka > Moduł aby utworzyć nowy pusty moduł.
  3. Następnie skopiuj i wklej poniższy kod do pustego modułu.
    Kod VBA: Licz i sumuj komórki na podstawie koloru tła
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
    'Updateby Extendoffice
        Dim rCell As Range
        Dim lCol As Long
        Dim vResult As Double
        lCol = rColor.Interior.ColorIndex
        vResult = 0
        If SUM Then
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + rCell.Value
                End If
            Next rCell
        Else
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + 1
                End If
            Next rCell
        End If
        ColorFunction = vResult
    End Function
    

Krok 2: Utwórz formuły do ​​zliczania i sumowania komórek według koloru tła

Po wklejeniu powyższego kodu zamknij okno modułu, a następnie zastosuj poniższe formuły:

  • Policz komórki na podstawie określonego koloru tła:
    Skopiuj lub wpisz poniższą formułę do wybranej komórki, aby uzyskać wynik. Następnie przeciągnij uchwyt wypełniania w dół, aby uzyskać inne wyniki. Zobacz zrzut ekranu:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Note: W tym wzorze G2 to komórka referencyjna z określonym kolorem tła, który chcesz dopasować; 2 $ B $: 12 $ E $ to zakres, w którym chcesz policzyć liczbę komórek koloru G2; FAŁSZYWY służy do liczenia komórek o pasującym kolorze.
  • Sumuj komórki na podstawie określonego koloru tła:
    Skopiuj lub wpisz poniższą formułę do wybranej komórki, aby uzyskać wynik. Następnie przeciągnij uchwyt wypełniania w dół, aby uzyskać inne wyniki. Zobacz zrzut ekranu:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Note: W tym wzorze G2 to komórka referencyjna z określonym kolorem tła, który chcesz dopasować; 2 $ B $: 12 $ E $ to zakres, w którym chcesz policzyć liczbę komórek koloru G2; TRUE służy do sumowania komórek o pasującym kolorze.

Licz i sumuj komórki według koloru tła za pomocą zaawansowanej funkcji

Osobom niezaznajomionym z programowaniem VBA może wydawać się dość skomplikowany. Tutaj przedstawimy potężne narzędzie - Kutool dla programu Excel, jego Policz według koloru funkcja umożliwia łatwe obliczenia (liczenie, suma, średnia itp.) na podstawie koloru tła za pomocą zaledwie kilku kliknięć. Imponujący, Policz według koloru funkcja wykracza poza zwykłe kolory tła – może także różnicować i obliczać na podstawie kolorów czcionek i formatowania warunkowego.

Po pobieranie i instalowanie Kutools dla Excelanajpierw wybierz zakres danych, który chcesz policzyć, lub zsumuj komórki na podstawie określonego koloru tła. Następnie przejdź do Kutools Plus i wybierz Policz według koloru.

W Policz według koloru oknie dialogowym, proszę określić operacje:

  1. Wybierz Standardowe formatowanie z Metoda kolorystyczna Lista rozwijana;
  2. Sprecyzować Tło z Typ zliczania rozwijana lista, z której w oknie dialogowym możesz wyświetlić podgląd wyników statystycznych dla każdego koloru tła;
  3. Nareszcie kliknij Generuj raport aby wyeksportować obliczone wyniki do nowego skoroszytu.

Wynik:

Teraz otrzymasz nowy skoroszyt ze statystykami. Zobacz zrzut ekranu:

Tips:
  1. Połączenia Policz według koloru funkcja obsługuje także zliczanie i sumowanie komórek w oparciu o standardowy kolor czcionki, tło lub kolor czcionki z formatowania warunkowego oraz kombinację kolorów wypełnienia i formatowania warunkowego.
  2. Zainteresuj się tą funkcją kliknij, aby pobrać, aby uzyskać bezpłatną wersję próbną na 30 dni.

Zliczaj i sumuj komórki według koloru tła za pomocą funkcji Filtr i SUMA CZĘŚCIOWA

Załóżmy, że mamy tabelę sprzedaży owoców, jak pokazano na zrzucie ekranu, i policzymy lub zsumujemy kolorowe komórki w ilość Kolumna.

Krok 1: Zastosuj funkcję PODSUMA

Wybierz puste komórki, aby wprowadzić funkcję SUMA CZĘŚCIOWA.

  • Aby policzyć wszystkie komórki o tym samym kolorze tła należy wpisać formułę:
    =SUBTOTAL(102, F2:F16)
  • Aby zsumować wszystkie komórki o tym samym kolorze tła należy wpisać formułę;
    =SUBTOTAL(109, F2:F16)
  • Note: w powyższych wzorach, 102 reprezentuje zliczanie wartości numerycznych na filtrowanej liście z wyłączeniem ukrytych komórek; 109 reprezentuje sumowanie wartości na przefiltrowanej liście z wyłączeniem ukrytych komórek; F2: F16 to zakres, dla którego zostanie obliczona liczba lub suma.

Krok 2: Filtruj komórki na podstawie określonego koloru

  1. Wybierz nagłówek tabeli i kliknij Dane > FILTRY. Zobacz zrzut ekranu:
  2. Kliknij FILTRY ikona  w komórce nagłówka ilość kolumnę i kliknij Filtr według koloru a określony kolor będziesz liczyć sukcesywnie. Zobacz zrzut ekranu:

Wynik:

Po przefiltrowaniu formuły SUMA CZĘŚCIOWA automatycznie zliczają i sumują kolorowe komórki w pliku ilość kolumna. Zobacz zrzut ekranu:

Note: Ta metoda wymaga, aby kolorowe komórki, które będziesz policzyć lub zsumować, znajdują się w tej samej kolumnie.

Licz i sumuj komórki na podstawie koloru czcionki

Chcesz policzyć lub zsumować komórki na podstawie koloru czcionki w programie Excel? Załóżmy, że masz dane, jak na podanym zrzucie ekranu, z komórkami zawierającymi teksty w kolorze czerwonym, niebieskim, pomarańczowym i czarnym. Excel domyślnie nie ułatwia tego. Ale nie martw się! W tej sekcji pokażemy Ci kilka prostych sztuczek, które pozwolą Ci to zrobić.


Licz i sumuj komórki na podstawie koloru czcionki za pomocą funkcji zdefiniowanej przez użytkownika

Aby zliczyć i zsumować komórki z określonymi kolorami czcionek, poniższa funkcja zdefiniowana przez użytkownika może pomóc w rozwiązaniu tego zadania. Wykonaj następujące kroki:

Krok 1: Otwórz edytor modułów VBA i skopiuj kod

  1. Naciśnij przycisk Alt + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
  2. W otwartym oknie kliknij wstawka > Moduł aby utworzyć nowy pusty moduł.
  3. Następnie skopiuj i wklej poniższy kod do pustego modułu.
    Kod VBA: Licz i sumuj komórki na podstawie koloru czcionki
    Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
    'Updateby Extendoffice
        Application.Volatile
        Dim rng As Range
        Dim xTotal As Double
        Dim xCount As Double
        xTotal = 0
        xCount = 0
        For Each rng In pRange1
            If rng.Font.Color = pRange2.Font.Color Then
                If UCase(FunctionType) = "SUM" Then
                    xTotal = xTotal + rng.Value
                ElseIf UCase(FunctionType) = "COUNT" Then
                    xCount = xCount + 1
                End If
            End If
        Next
        If UCase(FunctionType) = "SUM" Then
            ProcessByFontColor = xTotal
        ElseIf UCase(FunctionType) = "COUNT" Then
            ProcessByFontColor = xCount
        Else
            ProcessByFontColor = CVErr(xlErrValue)
        End If
    End Function
    

Krok 2: Utwórz formuły do ​​zliczania i sumowania komórek według koloru czcionki

Po wklejeniu powyższego kodu zamknij okno modułu, a następnie zastosuj poniższe formuły:

  • Policz komórki na podstawie określonego koloru czcionki:
    Skopiuj lub wpisz poniższą formułę do wybranej komórki, aby uzyskać wynik. Następnie przeciągnij uchwyt wypełniania w dół, aby uzyskać inne wyniki. Zobacz zrzut ekranu:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Note: W tym wzorze G2 to komórka referencyjna z określonym kolorem czcionki, który chcesz dopasować; 2 $ B $: 12 $ E $ to zakres, w którym chcesz policzyć liczbę komórek koloru G2.
  • Sumuj komórki na podstawie określonego koloru czcionki:
    Skopiuj lub wpisz poniższą formułę do wybranej komórki, aby uzyskać wynik. Następnie przeciągnij uchwyt wypełniania w dół, aby uzyskać inne wyniki. Zobacz zrzut ekranu:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Note: W tym wzorze G2 to komórka referencyjna z określonym kolorem czcionki, który chcesz dopasować; 2 $ B $: 12 $ E $ to zakres, w którym chcesz policzyć liczbę komórek koloru G2.

Zliczaj i sumuj komórki na podstawie koloru czcionki za pomocą łatwej funkcji

Chcesz bez wysiłku liczyć lub sumować wartości komórek w programie Excel na podstawie koloru czcionki? Nurkować Kutools dla programu Excel's Policz według koloru funkcja! Dzięki temu inteligentnemu narzędziu liczenie i sumowanie komórek według określonego koloru czcionki staje się dziecinnie proste. Odkryj jak Kutools może zmienić Twoje doświadczenie z Excelem.

Po pobieranie i instalowanie Kutools dla Excelanajpierw wybierz zakres danych, który chcesz policzyć, lub zsumuj komórki na podstawie określonego koloru czcionki. Następnie kliknij Kutools Plus > Policz według koloru otworzyć Policz według koloru okno dialogowe.

W Policz według koloru oknie dialogowym, proszę określić operacje:

  1. Wybierz Standardowe formatowanie z Metoda kolorystyczna Lista rozwijana;
  2. Sprecyzować chrzcielnica z Typ zliczania rozwijanej listy, z której w oknie dialogowym można wyświetlić podgląd wyników statystycznych dla każdego koloru czcionki;
  3. Nareszcie kliknij Generuj raport aby wyeksportować obliczone wyniki do nowego skoroszytu.

Wynik:

Teraz masz nowy skoroszyt wyświetlający szczegółowe statystyki na podstawie koloru czcionki. Zobacz zrzut ekranu:


Licz i sumuj komórki na podstawie koloru formatowania warunkowego

W programie Excel często można używać formatowania warunkowego, aby zastosować określony kolor do komórek spełniających określone kryteria, dzięki czemu wizualizacja danych będzie intuicyjna. Ale co, jeśli musisz policzyć lub zsumować te specjalnie sformatowane komórki? Chociaż Excel nie oferuje bezpośredniego sposobu, aby to zrobić, oto sposoby obejścia tego ograniczenia.


Zliczaj i sumuj komórki sformatowane warunkowo za pomocą kodu VBA

Liczenie i sumowanie komórek sformatowanych warunkowo w programie Excel nie jest proste przy użyciu wbudowanych funkcji. Można jednak wykonać to zadanie za pomocą kodu VBA. Przyjrzyjmy się, jak możesz wykorzystać do tego VBA:

Krok 1: Otwórz edytor modułów VBA i skopiuj kod

  1. Naciśnij przycisk Alt + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
  2. W otwartym oknie kliknij wstawka > Moduł aby utworzyć nowy pusty moduł.
  3. Następnie skopiuj i wklej poniższy kod do pustego modułu.
    Kod VBA: Licz i sumuj komórki na podstawie koloru formatowania warunkowego
    Sub SumCountByConditionalFormat()
    'Updateby Extendoffice
        Dim sampleColor As Range
        Dim selectedRange As Range
        Dim cell As Range
        Dim countByColor As Long
        Dim sumByColor As Double
        Dim refColor As Long
        Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                 "Kutools for Excel", _
                                                 Type:=8)
        If selectedRange Is Nothing Then Exit Sub
        Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                               "Kutools for Excel", _
                                               Type:=8)
        If Not sampleColor Is Nothing Then
            refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
            For Each cell In selectedRange
                If cell.DisplayFormat.Interior.color = refColor Then
                    countByColor = countByColor + 1
                    sumByColor = sumByColor + cell.Value
                End If
            Next cell
            MsgBox "Count: " & countByColor & vbCrLf & _
                   "Sum: " & sumByColor, _
                   vbInformation, "Results based on Conditional Format Color"
        End If
    End Sub
    

Krok 2: Wykonaj ten kod VBA

  1. Po wklejeniu kodu naciśnij F5 Aby uruchomić ten kod, pojawi się okno zachęty. Wybierz zakres danych, w którym chcesz policzyć i zsumować komórki w oparciu o formatowanie warunkowe. Następnie kliknij OK, Zobacz zrzut ekranu:
  2. W innym polu zachęty wybierz konkretny kolor formatowania warunkowego, który chcesz policzyć i zsumować, a następnie kliknij OK przycisk, zobacz zrzut ekranu:

Wynik:

Teraz wynik, który zawiera zarówno liczbę, jak i sumę komórek z określonym kolorem formatowania warunkowego, zostanie wyświetlony w wyskakującym okienku. Zobacz zrzut ekranu:


Licz i sumuj komórki sformatowane warunkowo za pomocą inteligentnej funkcji

Jeśli szukasz innych szybkich i łatwych metod liczenia i sumowania komórek sformatowanych warunkowo, Kutools dla programu Excel to najlepsze rozwiązanie. Jego Policz według koloru funkcja może rozwiązać to zadanie za pomocą zaledwie kilku kliknięć. Zanurz się, aby odkryć wydajność i precyzję, jaką Kutools może wnieść do Twojego przepływu pracy.

Po pobieranie i instalowanie Kutools dla Excelanajpierw wybierz zakres danych, który chcesz policzyć, lub zsumuj komórki na podstawie określonego koloru formatowania warunkowego. Następnie kliknij Kutools Plus > Policz według koloru otworzyć Policz według koloru okno dialogowe.

W Policz według koloru oknie dialogowym, proszę określić operacje:

  1. Wybierz Formatowanie warunkowe z Metoda kolorystyczna Lista rozwijana;
  2. Sprecyzować Tło z Typ zliczania rozwijanej listy, z której możesz wyświetlić podgląd wyników statystycznych dla każdego koloru formatowania warunku w oknie dialogowym;
  3. Nareszcie kliknij Generuj raport aby wyeksportować obliczone wyniki do nowego skoroszytu.

Wynik:

Teraz masz nowy skoroszyt wyświetlający szczegółowe statystyki w oparciu o kolor formatowania warunkowego. Zobacz zrzut ekranu:


Podobne artykuły:

  • Jeśli kolor czcionki jest czerwony, zwróć określony tekst
  • Jak możesz zwrócić określony tekst, jeśli kolor czcionki jest czerwony w innej komórce, jak pokazano na zrzucie ekranu? W tym artykule przedstawię kilka sztuczek do wykonywania niektórych operacji w oparciu o tekst czerwonej czcionki w programie Excel.
  • Filtruj dane według wielu kolorów
  • Zwykle w programie Excel można szybko filtrować wiersze za pomocą tylko jednego koloru, ale czy kiedykolwiek rozważałeś filtrowanie wierszy z wieloma kolorami w tym samym czasie? W tym artykule opowiem o szybkiej sztuczce, jak poradzić sobie z tym problemem.
  • Dodaj kolor do listy rozwijanej
  • W programie Excel utworzenie listy rozwijanej może ci bardzo pomóc, a czasami trzeba oznaczyć kolorami wartości listy rozwijanej w zależności od odpowiednich wybranych. Na przykład utworzyłem listę rozwijaną nazw owoców. Kiedy wybiorę Apple, komórka musi zostać automatycznie pokolorowana na czerwono, a kiedy wybiorę Pomarańczowy, komórka może zostać pokolorowana na pomarańczowo.
  • Pokoloruj naprzemienne wiersze dla scalonych komórek
  • Bardzo pomocne jest formatowanie alternatywnych wierszy w innym kolorze w dużych danych, abyśmy mogli je zeskanować, ale czasami w danych mogą znajdować się scalone komórki. Aby podświetlić wiersze naprzemiennie innym kolorem dla scalonych komórek, jak pokazano na poniższym zrzucie ekranu, jak rozwiązać ten problem w programie Excel?
Comments (237)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
This comment was minimized by the moderator on the site
Hi, How to make a diagram based on the colors in the table? For example, I want to count all the red, green and yellow colors in the cells in a table and make a diagram. How to do this? Please
This comment was minimized by the moderator on the site
Is there a way to count different color backgrounds from conditional formatting? The current code as of 7/14/2020 counts them all as default yellow or not at all.
This comment was minimized by the moderator on the site
Hi Dusty,
You can try the Count by Color feature of Kutools for Excel. This feature will help you quickly calculate (count, sum, average, etc.) cells by cell background color or font color, no matter they are formatted by conditional formatting or solidly format.
This comment was minimized by the moderator on the site
Did anyone find a solution to auto-refresh? I have to manually refresh for it to update. Otherwise, it works great!
This comment was minimized by the moderator on the site
Hi Dennis,
By default, formulas are calculated in Excel until you are turning off the Automatic Formula Calculation. You can enable it by clicking Formulas > Calculation Options > Automatic.
This comment was minimized by the moderator on the site
Anyone have tips on a max by color VBA?
This comment was minimized by the moderator on the site
Hi Natasha,VBA is good but hard to apply. But below methods may solve your work easily too.
Method 1: Use Find & Replace feature to select and statistic the color cells(1) Press Ctrl + H keys to open the Find and Replace dialog, and then enable the Find tab.
(2) In the dialog, click Options to show advanced find options.
(3) Then click Format > Choose Format From Cell, and select one of the specified color cells.
(4) Click Find All. Now all cells with the same fill color are found out and listed at the bottom of the dialog.
(5) Select one of found cells, and press Ctrl + A to select all found cells, so that these cells are selected in the worksheet.
(6) Now you can get the count, average, sum, min, max, etc. of these cells in the task bar.
Note: If a certain statistic result cannot be found one the taskbar, you can right click the task bar, and then tick the specified item to show it.

Method 2: Kutools for Excel
Kutools for Excel supports 30-day free trial. Therefore, you can download it and try its Count by color feature to solve your problem with several clicks only.
This comment was minimized by the moderator on the site
awesome fix! count by color over an entire sheet was just what i was looking for and your VBA code was tighter than others that i have looked at. Works like a charm. Thank you, and again, well done.
This comment was minimized by the moderator on the site
I copied and paste but calculation result is "0" why?? I am using Office 2016.
This comment was minimized by the moderator on the site
I try the same you this command =COUNTBYCELLCOLOR is counting only fill color but is not count by condition formating. Please help to improve code thx.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
#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
This comment was minimized by the moderator on the site
I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations