So zählen Sie FARBIGE Zellen in Excel (Schritt-für-Schritt-Anleitung + VIDEO)

Video ansehen - Wie man farbige Zellen in Excel zählt

Wäre es nicht toll, wenn es eine Funktion gäbe, die farbige Zellen in Excel zählen könnte?

Leider gibt es dafür keine eingebaute Funktion.

ABER…

Es kann leicht gemacht werden.

So zählen Sie farbige Zellen in Excel

In diesem Tutorial zeige ich Ihnen drei Möglichkeiten, farbige Zellen in Excel (mit und ohne VBA) zu zählen:

1. Verwenden der Filter- und ZWISCHENSUMME-Funktion
2. Verwenden der GET.CELL-Funktion
3. Verwenden einer benutzerdefinierten Funktion, die mit VBA erstellt wurde

#1 Farbige Zellen mit Filter und ZWISCHENSUMME zählen

Um farbige Zellen in Excel zu zählen, müssen Sie die folgenden zwei Schritte ausführen:

• Farbige Zellen filtern
• Verwenden Sie die Funktion ZWISCHENSUMME, um farbige Zellen zu zählen, die sichtbar sind (nach dem Filtern).

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

In diesem Datensatz werden zwei Hintergrundfarben verwendet (grün und orange).

Hier sind die Schritte zum Zählen farbiger Zellen in Excel:

1. Verwenden Sie in einer beliebigen Zelle unterhalb des Datensatzes die folgende Formel: =ZWISCHENSUMME(102,E1:E20)
   
2. Wählen Sie die Überschriften aus.
3. Gehen Sie zu Daten -> Sortieren und Filtern -> Filtern. Dadurch wird ein Filter auf alle Header angewendet.
4. Klicken Sie auf eines der Filter-Dropdowns.
5. Gehen Sie zu „Nach Farbe filtern“ und wählen Sie die Farbe aus. Da im obigen Datensatz zwei Farben zum Hervorheben der Zellen verwendet werden, zeigt der Filter zwei Farben an, um diese Zellen zu filtern.

Sobald Sie die Zellen filtern, werden Sie feststellen, dass sich der Wert in der Funktion ZWISCHENSUMME ändert und nur die Anzahl der Zellen zurückgibt, die nach dem Filtern sichtbar sind.

Wie funktioniert das?

Die Funktion ZWISCHENSUMME verwendet 102 als erstes Argument, das verwendet wird, um sichtbare Zellen (ausgeblendete Zeilen werden nicht gezählt) im angegebenen Bereich zu zählen.

Wenn die Daten nicht gefiltert sind, werden 19 zurückgegeben, aber wenn sie gefiltert sind, wird nur die Anzahl der sichtbaren Zellen zurückgegeben.

Probieren Sie es selbst aus… Laden Sie die Beispieldatei herunter

#2 Farbige Zellen mit der GET.CELL-Funktion zählen

GET.CELL ist eine Macro4-Funktion, die aus Kompatibilitätsgründen beibehalten wurde.

Es funktioniert nicht, wenn es als reguläre Funktionen im Arbeitsblatt verwendet wird.

Es funktioniert jedoch in Excel benannten Bereichen.

Siehe auch: Erfahren Sie mehr über die GET.CELL-Funktion.

Hier sind die drei Schritte, um mit GET.CELL farbige Zellen in Excel zu zählen:

• Erstellen Sie einen benannten Bereich mit der Funktion GET.CELL
• Verwenden Sie den benannten Bereich, um den Farbcode in einer Spalte zu erhalten
• Verwenden der Farbnummer zum Zählen der Anzahl der farbigen Zellen (nach Farbe)

Lassen Sie uns tief eintauchen und sehen, was in jedem der drei genannten Schritte zu tun ist.

Erstellen eines benannten Bereichs

• Gehen Sie zu Formeln -> Namen definieren.
• Geben Sie im Dialogfeld Neuer Name Folgendes ein:
  • Name: GetColor
  • Geltungsbereich: Arbeitsbuch
  • Bezieht sich auf: =GET.CELL(38,Sheet1!$A2)
    In der obigen Formel habe ich verwendet Blatt1!$A2 als zweites Argument. Sie müssen die Referenz der Spalte verwenden, in der sich die Zellen mit der Hintergrundfarbe befinden.

Abrufen des Farbcodes für jede Zelle

Verwenden Sie in der Zelle neben den Daten die Formel =GetColor

Diese Formel würde 0 zurückgeben, wenn es KEINE Hintergrundfarbe in einer Zelle gibt, und würde eine bestimmte Zahl zurückgeben, wenn eine Hintergrundfarbe vorhanden ist.

Diese Zahl ist spezifisch für eine Farbe, daher erhalten alle Zellen mit derselben Hintergrundfarbe dieselbe Zahl.

Zählen Sie farbige Zellen mit dem Farbcode

Wenn Sie den obigen Vorgang befolgen, erhalten Sie eine Spalte mit Zahlen, die der Hintergrundfarbe entsprechen.

So erhalten Sie die Anzahl einer bestimmten Farbe:

• Geben Sie einer Zelle, die Sie zählen möchten, irgendwo unterhalb des Datasets dieselbe Hintergrundfarbe. Stellen Sie sicher, dass Sie dies in derselben Spalte tun, die Sie beim Erstellen des benannten Bereichs verwendet haben. Zum Beispiel habe ich Spalte A verwendet und werde daher nur die Zellen in Spalte "A" verwenden.
• Verwenden Sie in der angrenzenden Zelle die folgende Formel:

=ZÄHLENWENN($F$2:$F$20,Farbe abrufen)

Diese Formel gibt Ihnen die Anzahl aller Zellen mit der angegebenen Hintergrundfarbe.

Wie funktioniert es?

Die ZÄHLENWENN-Funktion verwendet den benannten Bereich (GetColor) als Kriterium. Der benannte Bereich in der Formel bezieht sich auf die benachbarte Zelle links (in Spalte A) und gibt den Farbcode für diese Zelle zurück. Daher ist diese Farbcodenummer das Kriterium.

Die ZÄHLENWENN-Funktion verwendet den Bereich ($F$2:$F$18), der die Farbcodenummern aller Zellen enthält und die Anzahl basierend auf der Kriteriennummer zurückgibt.

Probieren Sie es selbst aus… Laden Sie die Beispieldatei herunter

# 3 Mit VBA farbig zählen (durch Erstellen einer benutzerdefinierten Funktion)

In den beiden obigen Methoden haben Sie gelernt, wie man farbige Zellen ohne VBA zählt.

Wenn Sie jedoch mit VBA zurechtkommen, ist dies die einfachste der drei Methoden.

Mit VBA würden wir eine benutzerdefinierte Funktion erstellen, die wie eine COUNTIF-Funktion funktioniert und die Anzahl der Zellen mit der spezifischen Hintergrundfarbe zurückgibt.

Hier ist der Code:

'Code erstellt von Sumit Bansal von https://trumpexcel.com Funktion GetColorCount(CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange Für jede rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

So erstellen Sie diese benutzerdefinierte Funktion:

• Drücken Sie bei aktiver Arbeitsmappe Alt + F11 (oder klicken Sie mit der rechten Maustaste auf die Registerkarte Arbeitsblatt und wählen Sie Code anzeigen). Dies würde den VB-Editor öffnen.
• Klicken Sie im linken Bereich unter der Arbeitsmappe, in der Sie arbeiten, mit der rechten Maustaste auf eines der Arbeitsblätter und wählen Sie Einfügen -> Modul. Dies würde ein neues Modul einfügen. Kopieren Sie den Code und fügen Sie ihn in das Modulcodefenster ein.
• Doppelklicken Sie auf den Modulnamen (standardmäßig den Namen des Moduls in Module1) und fügen Sie den Code in das Codefenster ein.
• Schließen Sie den VB-Editor.
• Das ist es! Sie haben jetzt eine benutzerdefinierte Funktion im Arbeitsblatt namens GetColorCount.

Um diese Funktion zu verwenden, verwenden Sie sie einfach wie eine normale Excel-Funktion.

Syntax: =GetColorCount(CountRange, CountColor)

• Zählbereich: der Bereich, in dem Sie die Zellen mit der angegebenen Hintergrundfarbe zählen möchten.
• AnzahlFarbe: die Farbe, für die Sie die Zellen zählen möchten.

Um diese Formel zu verwenden, verwenden Sie dieselbe Hintergrundfarbe (die Sie zählen möchten) in einer Zelle und verwenden Sie die Formel. Das Argument CountColor wäre dieselbe Zelle, in die Sie die Formel eingeben (wie unten gezeigt):

Notiz: Da die Arbeitsmappe einen Code enthält, speichern Sie ihn mit der Erweiterung .xls oder .xlsm.

Probieren Sie es selbst aus… Laden Sie die Beispieldatei herunter

Kennen Sie eine andere Möglichkeit, farbige Zellen in Excel zu zählen?

Wenn ja, teilen Sie es mir mit, indem Sie einen Kommentar hinterlassen.