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
- Korzystając z funkcji zdefiniowanej przez użytkownika
- Korzystając z zaawansowanej funkcji – Kutools dla Excela
- Używając Filtra i SUMY CZĘŚCIOWEJ
Licz i sumuj komórki na podstawie koloru czcionki
- Korzystając z funkcji zdefiniowanej przez użytkownika
- Korzystając z łatwej funkcji – Kutools dla Excela
Licz i sumuj komórki na podstawie koloru formatowania warunkowego
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
- Naciśnij przycisk Alt + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
- W otwartym oknie kliknij wstawka > Moduł aby utworzyć nowy pusty moduł.
- Następnie skopiuj i wklej poniższy kod do pustego modułu.
Kod VBA: Licz i sumuj komórki na podstawie koloru tłaFunction 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:
- Wybierz Standardowe formatowanie z Metoda kolorystyczna Lista rozwijana;
- 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;
- Nareszcie kliknij Generuj raport aby wyeksportować obliczone wyniki do nowego skoroszytu.
Wynik:
Teraz otrzymasz nowy skoroszyt ze statystykami. Zobacz zrzut ekranu:
- 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.
- 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
- Wybierz nagłówek tabeli i kliknij Dane > FILTRY. Zobacz zrzut ekranu:
- 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:
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
- Naciśnij przycisk Alt + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
- W otwartym oknie kliknij wstawka > Moduł aby utworzyć nowy pusty moduł.
- Następnie skopiuj i wklej poniższy kod do pustego modułu.
Kod VBA: Licz i sumuj komórki na podstawie koloru czcionkiFunction 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:
- Wybierz Standardowe formatowanie z Metoda kolorystyczna Lista rozwijana;
- 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;
- 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
- Naciśnij przycisk Alt + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
- W otwartym oknie kliknij wstawka > Moduł aby utworzyć nowy pusty moduł.
- Następnie skopiuj i wklej poniższy kod do pustego modułu.
Kod VBA: Licz i sumuj komórki na podstawie koloru formatowania warunkowegoSub 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
- 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:
- 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:
- Wybierz Formatowanie warunkowe z Metoda kolorystyczna Lista rozwijana;
- 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;
- 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?
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
Spis treści
- Wideo
- Policz i podsumuj komórki na podstawie koloru tła
- Korzystając z funkcji zdefiniowanej przez użytkownika
- Korzystając z zaawansowanej funkcji – Kutools dla Excela
- Używając Filtra i SUMY CZĘŚCIOWEJ
- Licz i sumuj komórki na podstawie koloru czcionki
- Korzystając z funkcji zdefiniowanej przez użytkownika
- Korzystając z łatwej funkcji – Kutools dla Excela
- Licz i sumuj komórki na podstawie koloru formatowania warunkowego
- Za pomocą kodu VBA
- Korzystając z inteligentnej funkcji – Kutools dla Excela
- Powiązane artykuły
- Najlepsze narzędzia biurowe
- Komentarze