Przypuśćmy, że mamy tabelę sprzedaży owoców, jak pokazano na zrzucie ekranu, i policzymy lub zsumujemy kolorowe komórki w kolumnie Kwota. W tej sytuacji możemy filtrować kolumnę Kwota według koloru, a następnie łatwo policzyć lub zsumować przefiltrowane kolorowe komórki za pomocą funkcji SUMA CZĘŚCIOWA w programie Excel.

1. Wybierz puste komórki, aby przejść do funkcji SUMA CZĘŚCIOWA.

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

Uwagi: W obu formułach E2: E20 to kolumna Kwota zawierająca kolorowe komórki i możesz je zmieniać według potrzeb.

2. Wybierz nagłówek tabeli i kliknij Dane > Filtruj. 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ędziesz liczyć sukcesywnie. Zobacz zrzut ekranu:

Po przefiltrowaniu obie formuły SUMA CZĘŚCIOWA automatycznie zliczają i sumują wszystkie przefiltrowane kolorowe komórki w kolumnie Kwota. Zobacz zrzut ekranu:

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

Jedno kliknięcie, aby policzyć, zsumować i uśrednić kolorowe komórki w programie Excel

Z doskonałymi Policz według koloru cechą Kutools dla programu Excel, możesz szybko policzyć, podsumować i uśrednić komórki według określonego koloru wypełnienia lub koloru czcionki za pomocą jednego kliknięcia w programie Excel. Poza tym ta funkcja będzie również określać maksymalne i minimalne wartości komórek według koloru wypełnienia lub koloru czcionki.

W tej metodzie utworzymy nazwany zakres za pomocą funkcji GET.CELL, pobierzemy kod koloru komórek, a następnie z łatwością policzymy lub zsumujemy według kodu koloru w programie Excel. Wykonaj następujące czynności:

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

2. W oknie dialogowym Nowa nazwa wykonaj poniższe zrzuty ekranu:
(1) Wpisz nazwę w polu Nazwa;
(2) Wprowadź wzór = GET.CELL (38; Sheet4! $ E2) w polu Odnosi się do (noty: we wzorze, 38 oznacza zwrot kodu komórki i Arkusz4! $ E2 to pierwsza komórka w kolumnie Kwota, z wyjątkiem nagłówka kolumny, który należy zmienić na podstawie danych w tabeli).
(3) Kliknij OK przycisk.

3. Teraz dodaj nową kolumnę Kolor bezpośrednio do oryginalnej tabeli. Następnie wpisz formułę = NumColor i przeciągnij uchwyt Autouzupełnianie, aby zastosować formułę do innych komórek w kolumnie Kolor. Zobacz zrzut ekranu:
Uwagi: We wzorze NumerKolor to nazwany zakres, który określiliśmy w pierwszych 2 krokach. Musisz zmienić go na określoną przez siebie nazwę.

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

4. Skopiuj i wypisz kolor wypełnienia w pustym zakresie w aktywnym arkuszu i wpisz formuły obok niego, jak pokazano na poniższym zrzucie ekranu:
A. Aby policzyć komórki według koloru, wprowadź formułę = LICZ.JEŻELI ($ F $ 2: $ F $ 20, NumColor);
B. Aby zsumować komórki według koloru, wprowadź wzór = SUMA.JEŻELI ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Uwagi: W obu formułach 2 $ F $: 20 $ F $ to kolumna Kolor, NumerKolor to określony nazwany zakres, 2 E $: 20 E $ to kolumna Kwota i możesz ją zmienić w razie potrzeby.

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

Zakładając, że kolorowe komórki rozpraszają się w zakresie, jak pokazano na zrzucie ekranu, obie powyższe metody nie mogą liczyć ani sumować kolorowych komórek. Tutaj ta metoda wprowadzi VBA do rozwiązania problemu.

1. Przytrzymaj ALT + F11 klucze i otwiera plik 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. Zsumuj kolorowe komórki: = funkcja koloru (A, B: C, PRAWDA)

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

4. Weź na przykład poniższy zrzut ekranu, wprowadź wzór= funkcja koloru (A1, A1: D11, FALSE) policzyć żółte komórki. I użyj wzoru = funkcja koloru (A1, A1: D11, TRUE) podsumować żół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:

Kutools dla programu Excel obsługuje również kilka przydatnych funkcji, które pomagają użytkownikom programu Excel w wykonywaniu specjalnych obliczeń, mówi liczenie według koloru tła komórki, suma według koloru czcionki itp.

1. Wybierz pustą komórkę, w której umieszczasz wyniki liczenia, i kliknij Kutools > Funkcje Kutools > Statystyka i matematyka > LICZ.BYKOWÓRKI. Zobacz zrzut ekranu:

2. W oknie dialogowym Argumenty funkcji określ zakres, w którym będą zliczane kolorowe komórki w Numer Referencyjny wybierz komórkę wypełnioną określonym kolorem tła w pliku Kolor_indeks_nr i kliknij przycisk OK przycisk. Zobacz zrzut ekranu:

Uwagi:
(1) Możesz również wpisać określoną funkcję Kutools = COUNTBYCELLCOLOR (1 $ A $: 20 $ E, G2)  w pustej komórce lub pasku formuły bezpośrednio, aby uzyskać wyniki zliczania;
(2) Kliknij Kutools > Funkcje Kutools > Statystyka i matematyka > KOLOR KOMÓRKI lub typ = SUMBYCELLCOLOR (1 $ A $: 20 $ E, G2) w pustej komórce bezpośrednio, aby zsumować komórki na podstawie określonego koloru tła.
Zastosuj LICZ.BYKOWÓRKI i KOLOR KOMÓRKI funkcje dla każdego koloru tła osobno, a otrzymasz wyniki jak na poniższym zrzucie ekranu:

Funkcje Kutools zawierają szereg wbudowanych funkcji ułatwiających użytkownikom programu Excel wykonywanie obliczeń, w tym Liczba / suma / średnia widocznych komórek, Liczba / suma według koloru komórki, Liczba / suma według koloru czcionki, Policz znaki, Policz pogrubioną czcionkąItp Bezpłatna wersja próbna!

Dzięki powyższej funkcji zdefiniowanej przez użytkownika musisz wprowadzić formułę jeden po drugim, jeśli jest 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 z kolorowych komórek. Możesz nie tylko liczyć i sumować kolorowe komórki, ale także uzyskać średnią, maksymalną i minimalną wartość kolorowego zakresu.

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

2. I w Policz według koloru okno dialogowe, wykonaj poniższe zrzuty ekranu:
(1) Wybierz Standardowe formatowanie z Metoda kolorystyczna Lista rozwijana;
(2) Wybierz tło z Typ zliczania Lista rozwijana.
(3) Kliknij przycisk Generuj raport.

Uwagi: Aby policzyć i zsumować kolorowe komórki według określonego koloru formatowania warunkowego, wybierz Formatowanie warunkowe z Metoda kolorystyczna rozwijana lista w powyższym oknie dialogowym lub wybierz Formatowanie standardowe i warunkowe z listy rozwijanej, aby policzyć wszystkie komórki wypełnione określonym kolorem.

Teraz otrzymasz nowy skoroszyt ze statystykami. Zobacz zrzut ekranu:

Podróż Ruta de la Plata w liczbach Policz według koloru funkcja oblicza komórki (liczba, suma, średnia, maks. itp.) według koloru tła lub koloru czcionki. Bezpłatna wersja próbna!

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