Excel Tipps: Zählen/summieren von Zellen nach Farbe (Hintergrund, Schrift, bedingte Formatierung)

Im täglichen Arbeitsablauf ist die Farbmarkierung eine beliebte Methode, um wichtige Daten schnell zu unterscheiden und hervorzuheben. Doch 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 dieses Ziel jedoch dennoch erreichen. Dieser Artikel wird untersuchen, wie man Daten nach Farbe zählt oder summiert.

Zählen und Summieren von Zellen basierend auf der Hintergrundfarbe

• Durch Verwendung einer benutzerdefinierten Funktion
• Durch Verwendung eines leistungsstarken Features – Kutools für Excel
• Durch Verwendung von Filtern und TEILSUMME

Zählen und Summieren von Zellen basierend auf der Schriftfarbe

• Durch Verwendung einer benutzerdefinierten Funktion
• Durch Verwendung einer einfachen Funktion – Kutools für Excel

Zählen und Summieren von Zellen basierend auf der bedingten Formatierungsfarbe

• Durch Verwendung von VBA-Code
• Durch Verwendung einer intelligenten Funktion – Kutools für Excel

Video: Zählen und Summieren von Zellen basierend auf Farbe

Zählen und Summieren von Zellen basierend auf der Hintergrundfarbe

Angenommen, Sie haben einen Datenbereich, in dem die Werte mit unterschiedlichen Hintergrundfarben versehen sind, wie im folgenden Screenshot gezeigt. Um 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 etwas Einfallsreichtum und praktischen Techniken können Sie diese Aufgabe jedoch erledigen. Lassen Sie uns einige nützliche Methoden in diesem Abschnitt erkunden.

Zählen und Summieren von Zellen nach Hintergrundfarbe mit einer benutzerdefinierten Funktion

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

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

1. Drücken Sie Alt + F11, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie im geöffneten Fenster auf Einfügen > Modul, um ein neues leeres Modul zu erstellen.
3. Kopieren und fügen Sie dann den folgenden Code in das leere Modul ein.
   VBA-Code: Zählen und Summieren von Zellen basierend auf der Hintergrundfarbe

   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, um Zellen nach Hintergrundfarbe zu zählen und zu summieren

Nachdem Sie den obigen Code eingefügt haben, schließen Sie das Modulfenster und wenden Sie dann die folgenden Formeln an:

• Zählen von Zellen basierend auf der spezifischen Hintergrundfarbe:
  Kopieren oder geben Sie die unten stehende Formel in die gewünschte Zelle für das Ergebnis ein. Ziehen Sie dann den Ausfüllkursor nach unten, um weitere Ergebnisse zu erhalten. Siehe Screenshot:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  Hinweis: In dieser Formel ist G2 die Referenzzelle mit der spezifischen Hintergrundfarbe, die Sie abgleichen möchten; $B$2:$E$12 ist der Bereich, in dem Sie die Anzahl der Zellen der Farbe von G2 zählen möchten; FALSE wird verwendet, um Zellen mit übereinstimmender Farbe zu zählen.
  
• Summieren von Zellen basierend auf der spezifischen Hintergrundfarbe:
  Kopieren oder geben Sie die unten stehende Formel in die gewünschte Zelle für das Ergebnis ein. Ziehen Sie dann den Ausfüllkursor nach unten, um weitere Ergebnisse zu erhalten. Siehe Screenshot:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  Hinweis: In dieser Formel ist G2 die Referenzzelle mit der spezifischen Hintergrundfarbe, die Sie abgleichen 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 übereinstimmender Farbe zu summieren.
  

Zählen und Summieren von Zellen nach Hintergrundfarbe mit einem leistungsstarken Feature

Für diejenigen, die nicht mit Programmierung vertraut sind, kann VBA ziemlich komplex erscheinen. Hier stellen wir ein leistungsstarkes Tool vor - Kutools für Excel, dessen Funktion Nach Farbe zählen es Ihnen ermöglicht, ganz einfach (Zählen, Summieren, Durchschnitt usw.) basierend auf der Hintergrundfarbe mit nur wenigen Klicks durchzuführen. Beeindruckend ist, dass die Funktion Nach Farbe zählen über die bloße Hintergrundfarbe hinausgeht – sie kann auch basierend auf Schriftfarben und bedingter Formatierung differenzieren und berechnen.

Nach dem Herunterladen und Installieren von Kutools für Excel wä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 Kutools Plus und wählen Sie Nach Farbe zählen.

Geben Sie im Dialogfeld Nach Farbe zählen die Operationen an:

1. Wählen Sie Standardformatierung aus der Dropdown-Liste Farbmodus aus;
2. Geben Sie Hintergrund aus der Dropdown-Liste Zähltyp an, und Sie können die statistischen Ergebnisse für jede Hintergrundfarbe im Dialogfeld anzeigen;
3. Klicken Sie abschließend auf Bericht generieren, um die berechneten Ergebnisse in eine neue Arbeitsmappe zu exportieren.

Ergebnis:

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

Zählen und Summieren von Zellen nach Hintergrundfarbe mit Filter und TEILSUMME-Funktion

Angenommen, wir haben eine Fruchtverkaufs-Tabelle wie im folgenden Screenshot gezeigt, und wir werden die farbigen Zellen in der Betrag Spalte zählen oder summieren.

Schritt 1: Wenden Sie die TEILSUMME-Funktion an

Wählen Sie leere Zellen aus, um die TEILSUMME-Funktion einzugeben.

• Um alle Zellen mit der gleichen Hintergrundfarbe zu zählen, geben Sie bitte die folgende Formel ein:

  =SUBTOTAL(102, F2:F16)

• Um alle Zellen mit der gleichen Hintergrundfarbe zu summieren, geben Sie bitte die folgende Formel ein:

  =SUBTOTAL(109, F2:F16)

• Hinweis: In den oben genannten Formeln steht 102 für das Zählen numerischer Werte in einer gefilterten Liste, wobei ausgeblendete Zellen ausgeschlossen werden; 109 steht für die Summe der Werte in einer gefilterten Liste, wobei ausgeblendete Zellen ausgeschlossen werden; F2:F16 ist der Bereich, über den entweder gezählt oder summiert wird.
• 

Schritt 2: Filtern Sie Zellen basierend auf einer bestimmten Farbe

1. Wählen Sie den Kopf der Tabelle aus und klicken Sie auf Daten > Filter. Siehe Screenshot:
   
2. Klicken Sie auf das Filter Symbol  in der Kopfzeile der Betrag Spalte und klicken Sie auf Nach Farbe filtern und die angegebene Farbe, nach der Sie nacheinander zählen möchten. Siehe Screenshot:
   

Ergebnis:

Nach dem Filtern zählen und summieren die TEILSUMME-Formeln automatisch die farbigen Zellen in der Betrag Spalte. Siehe Screenshot:

Zählen und Summieren von Zellen basierend auf der Schriftfarbe

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

Zählen und Summieren von Zellen basierend auf der Schriftfarbe mit einer benutzerdefinierten Funktion

Um Zellen mit spezifischen Schriftfarben zu zählen und zu summieren, kann die folgende benutzerdefinierte Funktion Ihnen helfen, diese Aufgabe zu lösen. Bitte gehen Sie wie folgt vor:

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

1. Drücken Sie Alt + F11, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie im geöffneten Fenster auf Einfügen > Modul, um ein neues leeres Modul zu erstellen.
3. Kopieren und fügen Sie dann den folgenden Code in das leere Modul ein.
   VBA-Code: Zählen und Summieren von Zellen basierend auf der Schriftfarbe

   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, um Zellen nach Schriftfarbe zu zählen und zu summieren

Nachdem Sie den obigen Code eingefügt haben, schließen Sie das Modulfenster und wenden Sie dann die folgenden Formeln an:

• Zählen von Zellen basierend auf der spezifischen Schriftfarbe:
  Kopieren oder geben Sie die unten stehende Formel in die gewünschte Zelle für das Ergebnis ein. Ziehen Sie dann den Ausfüllkursor nach unten, um weitere Ergebnisse zu erhalten. Siehe Screenshot:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  Hinweis: In dieser Formel ist G2 die Referenzzelle mit der spezifischen Schriftfarbe, die Sie abgleichen 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 von Zellen basierend auf der spezifischen Schriftfarbe:
  Kopieren oder geben Sie die unten stehende Formel in die gewünschte Zelle für das Ergebnis ein. Ziehen Sie dann den Ausfüllkursor nach unten, um weitere Ergebnisse zu erhalten. Siehe Screenshot:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Hinweis: In dieser Formel ist G2 die Referenzzelle mit der spezifischen Schriftfarbe, die Sie abgleichen 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 von Zellen basierend auf der Schriftfarbe mit einer einfachen Funktion

Möchten Sie Zellwerte in Excel mühelos basierend auf der Schriftfarbe zählen oder summieren? Tauchen Sie ein in Kutools für Excels Nach Farbe zählen Funktion! Mit diesem intelligenten Tool wird das Zählen und Summieren von Zellen nach spezifischer Schriftfarbe zum Kinderspiel. Entdecken Sie, wie Kutools Ihre Excel-Erfahrung transformieren kann.

Nach dem Herunterladen und Installieren von Kutools für Excel wählen Sie zunächst den Datenbereich aus, den Sie basierend auf einer bestimmten Schriftfarbe zählen oder summieren möchten. Klicken Sie dann auf Kutools Plus > Nach Farbe zählen, um das Dialogfeld Nach Farbe zählen zu öffnen.

Geben Sie im Dialogfeld Nach Farbe zählen die Operationen an:

1. Wählen Sie Standardformatierung aus der Dropdown-Liste Farbmodus aus;
2. Geben Sie Schriftart aus der Dropdown-Liste Zähltyp an, und Sie können die statistischen Ergebnisse für jede Schriftfarbe im Dialogfeld anzeigen;
3. Klicken Sie abschließend auf Bericht generieren, um die berechneten Ergebnisse in eine neue Arbeitsmappe zu exportieren.

Ergebnis:

Jetzt haben Sie eine neue Arbeitsmappe, die die detaillierten Statistiken basierend auf der Schriftfarbe anzeigt. Siehe Screenshot:

Zählen und Summieren von Zellen basierend auf der bedingten Formatierungsfarbe

In Excel verwenden Sie möglicherweise häufig die bedingte Formatierung, um Zellen, die bestimmte Kriterien erfüllen, eine bestimmte Farbe zuzuweisen, was die Datenvisualisierung intuitiv macht. Was aber, wenn Sie diese speziell formatierten Zellen zählen oder summieren müssen? Während Excel keine direkte Möglichkeit dafür bietet, gibt es Wege, diese Einschränkung zu umgehen.

Zählen und Summieren bedingt formatierter Zellen mit VBA-Code

Das Zählen und Summieren bedingt formatierter Zellen in Excel ist mit integrierten Funktionen nicht unkompliziert. Sie können diese Aufgabe jedoch mit VBA-Code erledigen. Lassen Sie uns sehen, wie Sie VBA dafür verwenden können:

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

1. Drücken Sie Alt + F11, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie im geöffneten Fenster auf Einfügen > Modul, um ein neues leeres Modul zu erstellen.
3. Kopieren und fügen Sie dann den folgenden Code in das leere Modul ein.
   VBA-Code: Zählen und Summieren von Zellen basierend auf der bedingten Formatierungsfarbe

   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. Nachdem Sie den Code eingefügt haben, drücken Sie die F5 Taste, um diesen Code auszuführen. Ein Eingabefeld wird angezeigt, wählen Sie bitte den Datenbereich aus, in dem Sie Zellen basierend auf der bedingten Formatierung zählen und summieren möchten. Klicken Sie dann auf OK, Siehe Screenshot:
   
2. In einem weiteren Eingabefeld wählen Sie eine spezifische bedingte Formatierungsfarbe aus, die Sie zählen und summieren möchten, und klicken Sie auf OK Button, siehe Screenshot:
   

Ergebnis:

Jetzt wird das Ergebnis, das sowohl die Anzahl als auch die Summe der Zellen mit der spezifischen bedingten Formatierungsfarbe enthält, im aufgepoppten Feld angezeigt. Siehe Screenshot:

Zählen und Summieren bedingt formatierter Zellen mit einer intelligenten Funktion

Wenn Sie nach anderen schnellen und einfachen Methoden suchen, um bedingt formatierte Zellen zu zählen und zu summieren, ist Kutools für Excel Ihre bevorzugte Lösung. Seine Funktion Nach Farbe zählen kann diese Aufgabe mit nur wenigen Klicks lösen. Tauchen Sie ein, um die Effizienz und Präzision zu entdecken, die Kutools in Ihren Workflow bringen kann.

Nach dem Herunterladen und Installieren von Kutools für Excel wählen Sie zunächst den Datenbereich aus, den Sie basierend auf einer bestimmten bedingten Formatierungsfarbe zählen oder summieren möchten. Klicken Sie dann auf Kutools Plus > Nach Farbe zählen, um das Dialogfeld Nach Farbe zählen zu öffnen.

Geben Sie im Dialogfeld Nach Farbe zählen die Operationen an:

1. Wählen Sie Bedingte Formatierung aus der Dropdown-Liste Farbmodus aus;
2. Geben Sie Hintergrund aus der Dropdown-Liste Zähltyp an, und Sie können die statistischen Ergebnisse für jede bedingte Formatierungsfarbe im Dialogfeld anzeigen;
3. Klicken Sie abschließend auf Bericht generieren, um die berechneten Ergebnisse in eine neue Arbeitsmappe zu exportieren.

Ergebnis:

Jetzt haben Sie eine neue Arbeitsmappe, die die detaillierten Statistiken basierend auf der bedingten Formatierungsfarbe anzeigt. Siehe Screenshot:

Verwandte Artikel: