Jak liczyć kolorowe komórki w programie Excel (przewodnik krok po kroku + WIDEO)

Obejrzyj wideo - Jak liczyć kolorowe komórki w programie Excel

Czy nie byłoby wspaniale, gdyby istniała funkcja, która mogłaby zliczać kolorowe komórki w programie Excel?

Niestety nie ma żadnej wbudowanej funkcji, aby to zrobić.

ALE…

Można to łatwo zrobić.

Jak liczyć kolorowe komórki w programie Excel

W tym samouczku pokażę ci trzy sposoby liczenia kolorowych komórek w Excelu (z VBA i bez):

1. Korzystanie z funkcji Filtr i SUMA CZĘŚCIOWA
2. Korzystanie z funkcji GET.CELL
3. Korzystanie z funkcji niestandardowej utworzonej za pomocą VBA

#1 Policz kolorowe komórki za pomocą filtra i SUMY CZĘŚCIOWEJ

Aby policzyć kolorowe komórki w programie Excel, musisz wykonać następujące dwa kroki:

• Filtruj kolorowe komórki
• Użyj funkcji SUMA CZĘŚCIOWA, aby zliczyć widoczne kolorowe komórki (po przefiltrowaniu).

Załóżmy, że masz zbiór danych, jak pokazano poniżej:

W tym zestawie danych używane są dwa kolory tła (zielony i pomarańczowy).

Oto kroki liczą kolorowe komórki w programie Excel:

1. W dowolnej komórce poniżej zestawu danych użyj następującej formuły: = SUMA.CZĘŚCIOWA(102,E1:E20)
   
2. Wybierz nagłówki.
3. Przejdź do Dane -> Sortuj i filtruj -> Filtruj. Spowoduje to zastosowanie filtru do wszystkich nagłówków.
4. Kliknij dowolne menu filtrów.
5. Przejdź do „Filtruj według koloru” i wybierz kolor. W powyższym zestawie danych, ponieważ do podświetlania komórek używane są dwa kolory, filtr pokazuje dwa kolory do filtrowania tych komórek.

Jak tylko przefiltrujesz komórki, zauważysz, że wartość w funkcji SUMA CZĘŚCIOWA zmienia się i zwraca tylko liczbę komórek, które są widoczne po przefiltrowaniu.

Jak to działa?

Funkcja SUMY.CZĘŚCIOWE używa 102 jako pierwszego argumentu, który służy do zliczania widocznych komórek (ukryte wiersze nie są liczone) w określonym zakresie.

Jeśli dane nie są filtrowane, zwracają 19, ale jeśli są filtrowane, zwracają tylko liczbę widocznych komórek.

Spróbuj sam… Pobierz przykładowy plik

#2 Policz kolorowe komórki za pomocą funkcji GET.CELL

GET.CELL to funkcja Macro4, która została zachowana ze względu na kompatybilność.

Nie działa, jeśli jest używany jako zwykłe funkcje w arkuszu.

Działa jednak w nazwanych zakresach programu Excel.

Zobacz też: Dowiedz się więcej o funkcji GET.CELL.

Oto trzy kroki, aby użyć GET.CELL do zliczania kolorowych komórek w programie Excel:

• Utwórz nazwany zakres za pomocą funkcji GET.CELL
• Użyj nazwanego zakresu, aby uzyskać kod koloru w kolumnie
• Używanie numeru koloru do liczenia liczby kolorowych komórek (według koloru)

Zanurzmy się głęboko i zobaczmy, co zrobić w każdym z trzech wymienionych kroków.

Tworzenie nazwanego zakresu

• Przejdź do Formuły -> Zdefiniuj nazwę.
• W oknie dialogowym Nowa nazwa wprowadź:
  • Nazwa: GetColor
  • Zakres: skoroszyt
  • Odnosi się do: =GET.CELL(38;Arkusz1!$A2)
    W powyższym wzorze użyłem Arkusz1!$A2 jako drugi argument. Musisz użyć odniesienia do kolumny, w której masz komórki z kolorem tła.

Uzyskiwanie kodu koloru dla każdej komórki

W komórce sąsiadującej z danymi użyj formuły = GetColor

Ta formuła zwróci 0, jeśli w komórce NIE ma koloru tła, i zwróci określoną liczbę, jeśli jest kolor tła.

Ta liczba jest specyficzna dla koloru, więc wszystkie komórki z tym samym kolorem tła otrzymują ten sam numer.

Policz kolorowe komórki za pomocą kodu koloru

Jeśli zastosujesz się do powyższego procesu, będziesz miał kolumnę z liczbami odpowiadającymi kolorowi tła.

Aby uzyskać liczbę określonego koloru:

• Gdzieś poniżej zbioru danych nadaj ten sam kolor tła komórce, którą chcesz policzyć. Upewnij się, że robisz to w tej samej kolumnie, której użyłeś podczas tworzenia nazwanego zakresu. Na przykład użyłem kolumny A, dlatego użyję tylko komórek w kolumnie „A”.
• W sąsiedniej komórce użyj następującej formuły:

=LICZ.JEŻELI($F$2:$F$20,Pobierz kolor)

Ta formuła da ci liczbę wszystkich komórek o określonym kolorze tła.

Jak to działa?

Funkcja LICZ.JEŻELI używa nazwanego zakresu (GetColor) jako kryterium. Nazwany zakres w formule odwołuje się do sąsiedniej komórki po lewej stronie (w kolumnie A) i zwraca kod koloru tej komórki. Stąd ten numer kodu koloru jest kryterium.

Funkcja LICZ.JEŻELI używa zakresu ($ F 2: $ F $ 18), który przechowuje numery kodów kolorów wszystkich komórek i zwraca liczbę na podstawie liczby kryteriów.

Spróbuj sam… Pobierz przykładowy plik

#3 Policz kolorowe za pomocą VBA (poprzez utworzenie funkcji niestandardowej)

W powyższych dwóch metodach nauczyłeś się liczyć kolorowe komórki bez użycia VBA.

Ale jeśli nie masz nic przeciwko używaniu VBA, jest to najłatwiejsza z trzech metod.

Korzystając z VBA, stworzylibyśmy niestandardową funkcję, która działałaby jak funkcja LICZ.JEŻELI i zwracała liczbę komórek z określonym kolorem tła.

Oto kod:

'Kod utworzony przez Sumit Bansal z https://trumpexcel.com Funkcja GetColorCount(CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Each rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

Aby utworzyć tę funkcję niestandardową:

• Przy aktywnym skoroszycie naciśnij Alt + F11 (lub kliknij prawym przyciskiem myszy kartę arkusza roboczego i wybierz Wyświetl kod). Otworzyłoby to edytor VB.
• W lewym okienku, pod skoroszytem, ​​w którym pracujesz, kliknij prawym przyciskiem myszy dowolny arkusz i wybierz Wstaw -> Moduł. Spowodowałoby to wstawienie nowego modułu. Skopiuj i wklej kod w oknie kodu modułu.
• Kliknij dwukrotnie nazwę modułu (domyślnie nazwa modułu w Module1) i wklej kod w oknie kodu.
• Zamknij edytor VB.
• Otóż ​​to! Masz teraz w arkuszu funkcję niestandardową o nazwie GetColorCount.

Aby skorzystać z tej funkcji, po prostu użyj jej jak zwykłej funkcji programu Excel.

Składnia: =GetColorCount(CountRange,CountColor)

• LiczbaZakres: zakres, w którym chcesz policzyć komórki o określonym kolorze tła.
• Licz Kolor: kolor, dla którego chcesz policzyć komórki.

Aby użyć tej formuły, użyj tego samego koloru tła (który chcesz policzyć) w komórce i użyj formuły. Argument CountColor byłby tą samą komórką, w której wprowadzasz formułę (jak pokazano poniżej):

Notatka: Ponieważ w skoroszycie znajduje się kod, zapisz go z rozszerzeniem .xls lub .xlsm.

Spróbuj sam… Pobierz przykładowy plik

Czy znasz inny sposób liczenia kolorowych komórek w programie Excel?

Jeśli tak, podziel się tym ze mną, zostawiając komentarz.