Direkt zum Inhalt

Excel-Tipps: Zellen nach Farbe zählen/summieren (Hintergrund, Schriftart, bedingte Formatierung)

Bei alltäglichen Aufgaben ist die Farbmarkierung eine beliebte Methode, um wichtige Daten schnell zu unterscheiden und hervorzuheben. Aber wie zählen oder summieren wir Zelldaten basierend auf einer bestimmten Farbe (Füllfarbe, Schriftfarbe, bedingte Formatierung)? Standardmäßig bietet Excel keine direkte Funktion zum Zählen oder Summieren nach Farbe. Mit einigen Tricks und indirekten Methoden können wir dies jedoch dennoch erreichen. In diesem Artikel erfahren Sie, wie Sie Daten nach Farbe zählen oder summieren.

Zählen und summieren Sie Zellen basierend auf der Hintergrundfarbe

Zählen und summieren Sie Zellen basierend auf der Schriftfarbe

Zählen und summieren Sie Zellen basierend auf der Farbe der bedingten Formatierung


Video: Zellen basierend auf der Farbe zählen und summieren


Zählen und summieren Sie Zellen basierend auf der Hintergrundfarbe

Wenn Sie beispielsweise über einen Datenbereich verfügen, in dem die Werte mit unterschiedlichen Hintergrundfarben gefüllt sind, wie im Screenshot unten gezeigt. Um die Zellen basierend auf einer bestimmten Farbe zu zählen oder zu summieren, bietet Excel keine direkte Funktion zum Zählen oder Summieren von Zellen basierend auf ihrer Hintergrundfarbe. Mit ein wenig Einfallsreichtum und einigen praktischen Techniken können Sie diese Aufgabe jedoch bewältigen. Lassen Sie uns in diesem Abschnitt einige nützliche Methoden untersuchen.


Zählen und summieren Sie Zellen nach Hintergrundfarbe mit der benutzerdefinierten Funktion

Hier zeigen wir Ihnen, wie Sie eine solche benutzerdefinierte Funktion erstellen und verwenden, um diese Aufgabe in Excel zu lösen. Gehen Sie bitte wie folgt vor:

Schritt 1: Öffnen Sie den VBA-Modul-Editor und kopieren Sie den Code

  1. Presse- Alt + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.
  2. Klicken Sie im geöffneten Fenster auf Insert > Modul um ein neues leeres Modul zu erstellen.
  3. Kopieren Sie dann den folgenden Code und fügen Sie ihn in das leere Modul ein.
    VBA-Code: Zellen basierend auf der Hintergrundfarbe zählen und summieren
    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
    

Schritt 2: Erstellen Sie Formeln zum Zählen und Summieren von Zellen anhand der Hintergrundfarbe

Schließen Sie nach dem Einfügen des obigen Codes das Modulfenster und wenden Sie dann die folgenden Formeln an:

  • Zählen Sie Zellen basierend auf der spezifischen Hintergrundfarbe:
    Kopieren oder geben Sie die unten angegebene Formel in die gewünschte Zelle ein, um das Ergebnis zu erhalten. Ziehen Sie dann den Füllpunkt nach unten, um weitere Ergebnisse zu erhalten. Siehe Screenshot:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Note: In dieser Formel G2 ist die Referenzzelle mit der spezifischen Hintergrundfarbe, die Sie anpassen möchten; $B$2:$E$12 ist der Bereich, in dem Sie die Anzahl der Zellen der Farbe von G2 zählen möchten; FALSCH wird verwendet, um Zellen mit passender Farbe zu zählen.
  • Summieren Sie Zellen basierend auf der spezifischen Hintergrundfarbe:
    Kopieren oder geben Sie die unten angegebene Formel in die gewünschte Zelle ein, um das Ergebnis zu erhalten. Ziehen Sie dann den Füllpunkt nach unten, um weitere Ergebnisse zu erhalten. Siehe Screenshot:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Note: In dieser Formel G2 ist die Referenzzelle mit der spezifischen Hintergrundfarbe, die Sie anpassen möchten; $B$2:$E$12 ist der Bereich, in dem Sie die Anzahl der Zellen der Farbe von G2 zählen möchten; TRUE wird verwendet, um Zellen mit passender Farbe zu summieren.

Zählen und summieren Sie Zellen anhand der Hintergrundfarbe mit einer leistungsstarken Funktion

Für diejenigen, die mit Programmierung nicht vertraut sind, kann VBA recht komplex erscheinen. Hier stellen wir ein leistungsstarkes Tool vor - Kutool für Excel, seine Nach Farbe zählen Mit dieser Funktion können Sie mit nur wenigen Klicks ganz einfach Berechnungen (Zählung, Summe, Durchschnitt usw.) basierend auf der Hintergrundfarbe durchführen. Eindrucksvoll, Nach Farbe zählen Die Funktion geht über bloße Hintergrundfarben hinaus – sie kann auch anhand von Schriftfarben und bedingter Formatierung unterscheiden und berechnen.

Nach der Kutools für Excel herunterladen und installierenWählen Sie zunächst den Datenbereich aus, den Sie basierend auf einer bestimmten Hintergrundfarbe zählen oder summieren möchten. Navigieren Sie als Nächstes zu Kutoolen Plus und wählen Sie Nach Farbe zählen.

Im Nach Farbe zählen Geben Sie im Dialogfeld bitte die Vorgänge an:

  1. Auswählen Standardformatierung von dem Farbmethode Dropdown-Liste;
  2. Angeben Hintergrund von dem Zählungstyp Dropdown-Liste, und Sie können im Dialogfeld eine Vorschau der statistischen Ergebnisse für jede Hintergrundfarbe anzeigen;
  3. Endlich, klick Bericht generieren um die berechneten Ergebnisse in eine neue Arbeitsmappe zu exportieren.

Ergebnis:

Jetzt erhalten Sie eine neue Arbeitsmappe mit den Statistiken. Siehe Screenshot:

Tips:
  1. Das Nach Farbe zählen Die Funktion unterstützt auch das Zählen und Summieren von Zellen basierend auf der Standardschriftfarbe, dem Hintergrund oder der Schriftfarbe aus der bedingten Formatierung sowie einer Kombination aus Füll- und bedingten Formatierungsfarben.
  2. Bitte interessieren Sie sich für diese Funktion Klicken Sie zum Herunterladen, um eine kostenlose Testversion für 30 Tage zu erhalten.

Zählen und summieren Sie Zellen nach Hintergrundfarbe mit der Filter- und ZWISCHENSUMME-Funktion

Angenommen, wir haben eine Fruchtverkaufstabelle wie im folgenden Screenshot gezeigt, und wir zählen oder summieren die farbigen Zellen darin Summe Spalte.

Schritt 1: Wenden Sie die ZWISCHENSUMME-Funktion an

Wählen Sie leere Zellen aus, um die ZWISCHENSUMME-Funktion aufzurufen.

  • Um alle Zellen mit derselben Hintergrundfarbe zu zählen, geben Sie bitte die Formel ein:
    =SUBTOTAL(102, F2:F16)
  • Um alle Zellen mit derselben Hintergrundfarbe zu summieren, geben Sie bitte die Formel ein;
    =SUBTOTAL(109, F2:F16)
  • Note: in den obigen Formeln, 102 bedeutet, numerische Werte in einer gefilterten Liste zu zählen und dabei ausgeblendete Zellen auszuschließen; 109 stellt dar, Werte in einer gefilterten Liste ohne ausgeblendete Zellen zusammenzufassen; F2: F16 ist der Bereich, über den entweder die Anzahl oder die Summe berechnet wird.

Schritt 2: Filtern Sie Zellen nach einer bestimmten Farbe

  1. Wählen Sie die Kopfzeile der Tabelle aus und klicken Sie Datum > Filter. Siehe Screenshot:
  2. Klicken Sie auf die Filter am linken Bildschirmrand.  in der Kopfzelle des Summe Spalte und klicken Sie auf Filter nach Farbe und die angegebene Farbe, nach der Sie nacheinander zählen. Siehe Screenshot:

Ergebnis:

Nach dem Filtern zählen und summieren die ZWISCHENSUMME-Formeln automatisch die farbigen Zellen im Summe Säule. Siehe Screenshot:

Note: Für diese Methode müssen sich die farbigen Zellen, die Sie zählen oder summieren, in derselben Spalte befinden.

Zählen und summieren Sie Zellen basierend auf der Schriftfarbe

Möchten Sie Zellen basierend auf ihrer Schriftfarbe in Excel zählen oder summieren? Nehmen wir an, Sie haben die Daten, wie im Screenshot, mit Zellen, die Texte in den Farben Rot, Blau, Orange und Schwarz enthalten. Excel macht dies standardmäßig nicht einfach. Aber keine Sorge! In diesem Abschnitt zeigen wir Ihnen einige einfache Tricks, um genau das zu erreichen.


Zählen und summieren Sie Zellen basierend auf der Schriftfarbe mit der benutzerdefinierten Funktion

Um Zellen mit bestimmten Schriftfarben zu zählen und zu summieren, kann Ihnen die folgende benutzerdefinierte Funktion bei der Lösung dieser Aufgabe helfen. Gehen Sie bitte wie folgt vor:

Schritt 1: Öffnen Sie den VBA-Modul-Editor und kopieren Sie den Code

  1. Presse- Alt + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.
  2. Klicken Sie im geöffneten Fenster auf Insert > Modul um ein neues leeres Modul zu erstellen.
  3. Kopieren Sie dann den folgenden Code und fügen Sie ihn in das leere Modul ein.
    VBA-Code: Zellen basierend auf der Schriftfarbe zählen und summieren
    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
    

Schritt 2: Erstellen Sie Formeln zum Zählen und Summieren von Zellen nach Schriftfarbe

Schließen Sie nach dem Einfügen des obigen Codes das Modulfenster und wenden Sie dann die folgenden Formeln an:

  • Zählen Sie Zellen basierend auf der spezifischen Schriftfarbe:
    Kopieren oder geben Sie die unten angegebene Formel in die gewünschte Zelle ein, um das Ergebnis zu erhalten. Ziehen Sie dann den Füllpunkt nach unten, um weitere Ergebnisse zu erhalten. Siehe Screenshot:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Note: In dieser Formel G2 ist die Referenzzelle mit der spezifischen Schriftfarbe, die Sie anpassen möchten; $B$2:$E$12 ist der Bereich, in dem Sie die Anzahl der Zellen der Farbe von G2 zählen möchten.
  • Summieren Sie Zellen basierend auf der spezifischen Schriftfarbe:
    Kopieren oder geben Sie die unten angegebene Formel in die gewünschte Zelle ein, um das Ergebnis zu erhalten. Ziehen Sie dann den Füllpunkt nach unten, um weitere Ergebnisse zu erhalten. Siehe Screenshot:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Note: In dieser Formel G2 ist die Referenzzelle mit der spezifischen Schriftfarbe, die Sie anpassen möchten; $B$2:$E$12 ist der Bereich, in dem Sie die Anzahl der Zellen der Farbe von G2 zählen möchten.

Zählen und summieren Sie Zellen basierend auf der Schriftfarbe mit einer einfachen Funktion

Möchten Sie Zellwerte in Excel mühelos anhand der Schriftfarbe zählen oder summieren? Eintauchen in Kutools for Excel's Nach Farbe zählen Besonderheit! Mit diesem intelligenten Tool wird das Zählen und Summieren von Zellen nach einer bestimmten Schriftfarbe zum Kinderspiel. Entdecken Sie wie Kutoolen kann Ihr Excel-Erlebnis verändern.

Nach der Kutools für Excel herunterladen und installierenWählen Sie zunächst den Datenbereich aus, den Sie basierend auf einer bestimmten Schriftfarbe zählen oder summieren möchten. Dann klick Kutoolen Plus > Nach Farbe zählen öffnen Nach Farbe zählen Dialogbox.

Im Nach Farbe zählen Geben Sie im Dialogfeld bitte die Vorgänge an:

  1. Auswählen Standardformatierung von dem Farbmethode Dropdown-Liste;
  2. Angeben Schriftart von dem Zählungstyp Dropdown-Liste, und Sie können im Dialogfeld eine Vorschau der statistischen Ergebnisse für jede Schriftfarbe anzeigen;
  3. Endlich, klick Bericht generieren um die berechneten Ergebnisse in eine neue Arbeitsmappe zu exportieren.

Ergebnis:

Jetzt verfügen Sie über eine neue Arbeitsmappe, in der detaillierte Statistiken basierend auf der Schriftfarbe angezeigt werden. Siehe Screenshot:


Zählen und summieren Sie Zellen basierend auf der Farbe der bedingten Formatierung

In Excel können Sie häufig die bedingte Formatierung verwenden, um Zellen, die bestimmte Kriterien erfüllen, eine bestimmte Farbe zuzuweisen und so die Datenvisualisierung intuitiv zu gestalten. Was aber, wenn Sie diese speziell formatierten Zellen zählen oder summieren müssen? Obwohl Excel hierfür keine direkte Möglichkeit bietet, finden Sie hier Möglichkeiten, diese Einschränkung zu umgehen.


Zählen und summieren Sie bedingt formatierte Zellen mit VBA-Code

Das Zählen und Summieren bedingt formatierter Zellen in Excel ist mit integrierten Funktionen nicht einfach. Sie können diese Aufgabe jedoch mit VBA-Code ausführen. Sehen wir uns an, wie Sie VBA dafür verwenden können:

Schritt 1: Öffnen Sie den VBA-Modul-Editor und kopieren Sie den Code

  1. Presse- Alt + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.
  2. Klicken Sie im geöffneten Fenster auf Insert > Modul um ein neues leeres Modul zu erstellen.
  3. Kopieren Sie dann den folgenden Code und fügen Sie ihn in das leere Modul ein.
    VBA-Code: Zählen und summieren Sie Zellen basierend auf der Farbe der bedingten Formatierung
    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
    

Schritt 2: Führen Sie diesen VBA-Code aus

  1. Drücken Sie nach dem Einfügen des Codes F5 Wenn Sie die Taste drücken, um diesen Code auszuführen, wird ein Eingabeaufforderungsfeld angezeigt. Wählen Sie bitte den Datenbereich aus, in dem Sie Zellen basierend auf der bedingten Formatierung zählen und summieren möchten. Dann klick OK, Siehe Screenshot:
  2. Wählen Sie in einem weiteren Eingabeaufforderungsfeld eine bestimmte Farbe für die bedingte Formatierung aus, die Sie zählen und summieren möchten, und klicken Sie auf OK Schaltfläche, siehe Screenshot:

Ergebnis:

Nun wird das Ergebnis, das sowohl die Anzahl als auch die Summe der Zellen mit der angegebenen Farbe für die bedingte Formatierung enthält, im Popup-Feld angezeigt. Siehe Screenshot:


Zählen und summieren Sie bedingt formatierte Zellen mit einer intelligenten Funktion

Wenn Sie nach anderen schnellen und einfachen Methoden zum Zählen und Summieren bedingt formatierter Zellen suchen, Kutools for Excel ist Ihre Lösung. Es ist Nach Farbe zählen Diese Funktion kann diese Aufgabe mit nur wenigen Klicks lösen. Tauchen Sie ein und entdecken Sie die Effizienz und Präzision, die Kutools in Ihren Arbeitsablauf bringen kann.

Nach der Kutools für Excel herunterladen und installierenWählen Sie zunächst den Datenbereich aus, den Sie basierend auf einer bestimmten bedingten Formatierungsfarbe zählen oder summieren möchten. Dann klick Kutoolen Plus > Nach Farbe zählen öffnen Nach Farbe zählen Dialogbox.

Im Nach Farbe zählen Geben Sie im Dialogfeld bitte die Vorgänge an:

  1. Auswählen Bedingte Formatierung von dem Farbmethode Dropdown-Liste;
  2. Angeben Hintergrund von dem Zählungstyp Dropdown-Liste, und Sie können eine Vorschau der statistischen Ergebnisse für jede bedingte Formatierungsfarbe im Dialogfeld anzeigen;
  3. Endlich, klick Bericht generieren um die berechneten Ergebnisse in eine neue Arbeitsmappe zu exportieren.

Ergebnis:

Jetzt verfügen Sie über eine neue Arbeitsmappe, in der detaillierte Statistiken basierend auf der Farbe der bedingten Formatierung angezeigt werden. Siehe Screenshot:


Weitere Artikel:

  • Wenn die Schriftfarbe Rot ist, wird ein bestimmter Text zurückgegeben
  • Wie können Sie einen bestimmten Text zurückgeben, wenn die Schriftfarbe in einer anderen Zelle rot ist, wie im folgenden Screenshot gezeigt? In diesem Artikel werde ich einige Tricks vorstellen, um einige Operationen basierend auf dem Text in roter Schrift in Excel durchzuführen.
  • Filtern Sie Daten nach mehreren Farben
  • Normalerweise können Sie in Excel schnell Zeilen mit nur einer Farbe filtern. Haben Sie jemals darüber nachgedacht, Zeilen mit mehreren Farben gleichzeitig zu filtern? In diesem Artikel werde ich über einen schnellen Trick sprechen, mit dem Sie dieses Problem lösen können.
  • Fügen Sie der Dropdown-Liste Farbe hinzu
  • In Excel kann das Erstellen einer Dropdown-Liste sehr hilfreich sein, und manchmal müssen Sie die Werte der Dropdown-Liste abhängig von der entsprechenden Auswahl farblich kennzeichnen. Ich habe zum Beispiel eine Dropdown-Liste mit den Fruchtnamen erstellt. Wenn ich Apfel auswähle, muss die Zelle automatisch rot gefärbt werden, und wenn ich Orange wähle, kann die Zelle orange gefärbt werden.
  • Färben Sie alternative Zeilen für verbundene Zellen
  • Es ist sehr hilfreich, abwechselnde Zeilen mit einer anderen Farbe in großen Daten zu formatieren, damit wir die Daten scannen können, aber manchmal können einige verbundene Zellen in Ihren Daten vorhanden sein. Wie können Sie dieses Problem in Excel lösen, um die Zeilen abwechselnd mit einer anderen Farbe für die verbundenen Zellen hervorzuheben, wie im folgenden Screenshot gezeigt?