Excel - Auswertung von farbigen Zellen

[schatzi]

Hallo!

(Eventuelle Fragen zu diesem Tutorial stellt ihr bitte im Excel-Forum.)

Wichtig: Dieses Tutorial bezieht sich NICHT(!) auf Zellen, die mit der bedingten Formatierung gefärbt wurden!

Immer wieder taucht die Frage auf, wie man die Zellfarbe als Kriterium in einer Formel anwenden kann.
Beispiel: Wie lautet die Formel, wenn ich alle Zellen mit rotem Hintergrund zählen möchte?
Oder: Wie summiere ich alle Zellinhalte von Zellen mit grüner Schrift?

Die schlechte Nachricht vorweg:
Eine solche Funktion stellt Excel von Haus aus nicht zur Verfügung.
Daher ist auch grundsätzlich von einer solchen Vorgehensweise abzuraten!

Sollte es sich dennoch nicht vermeiden lassen, gibt es trotzdem Möglichkeiten, ans gewünschte Ziel zu gelangen. Alle vorgestellten Lösungen haben jedoch den Nachteil, dass sie sich nicht automatisch aktualisieren, da eine Farbänderung keine Neuberechnung der Tabelle auslöst. Diese Neuberechnung kann jeweils durch das Drücken von [F9] erzwungen werden, wenn in der Tabelle mindestens eine einzige Formel auftaucht (was sollte sonst auch berechnet werden...?).

Nun aber die Möglichkeiten:

Die VBA-freie Variante:

Definiere einen Namen (Menü Einfügen > Namen > Definieren):
Name: Zellfarbe
Bezieht sich auf: =ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))

Um hiermit den Colorindex einer Zelle auszulesen, schreibe in die Zelle rechts daneben die Formel
=Zellfarbe

Geht es dir nicht um die Zellfarbe sondern um die Schriftfarbe, dann verwende diese Namensdefinition:
Name: Schriftfarbe
Bezieht sich auf: =ZELLE.ZUORDNEN(24;INDIREKT("ZS(-1)";))

Die weitere Auswertung dieser Ergebnisse erfolgt dann über die Funktionen ZÄHLENWENN, SUMMEWENN oder auch SUMMENPRODUKT.


Sollte aus irgendeinem Grund kein Platz für diese Extra-Zellen bleiben, dann existiert auch noch die Möglichkeit, eine selbstdefinierte Funktion für diese Auswertung einzusetzen. Dies ist

Die VBA-Variante:

Öffne aus der Tabelle heraus den VBA-Editor mit Alt+F11.
Erstelle ein Modul: Menü Einfügen > Modul

Je nachdem, welchem Zweck die Funktion dienen soll, kopiere einen der folgenden Codes in das Codefenster rechts.

1. Zellen mit einer bestimmten Hintergrundfarbe sollen gezählt werden:

Public Function HFarbeZählen(Bereich As Range, Farbe As Integer)
Dim Zelle As Range
Application.Volatile
For Each Zelle In Bereich
If Zelle.Interior.ColorIndex = Farbe Then
HFarbeZählen = HFarbeZählen + 1
End If
Next Zelle
End Function

Der Aufruf in der Tabelle erfolgt dann z.B. so (zählt alle Zellen in A1:A10 mit rotem Hintergrund):

=HFarbeZählen(A1:A10;3)

2. Zellen mit einer bestimmten Schriftfarbe sollen gezählt werden:

Public Function SFarbeZählen(Bereich As Range, Farbe As Integer)
Dim Zelle As Range
Application.Volatile
For Each Zelle In Bereich
If Zelle.Font.ColorIndex = Farbe Then
SFarbeZählen = SFarbeZählen + 1
End If
Next Zelle
End Function

Der Aufruf in der Tabelle erfolgt dann z.B. so (zählt alle Zellen in B1:B10 mit grüner Schriftfarbe):

=SFarbeZählen(B1:B10;4)

3. Zellinhalte von Zellen mit bestimmter Hintergrundfarbe sollen summiert werden:

Public Function HFarbeSumme(Bereich As Range, Farbe As Integer)
Dim Zelle As Range
Application.Volatile
For Each Zelle In Bereich
If Zelle.Interior.ColorIndex = Farbe Then
HFarbeSumme = HFarbeSumme + Zelle.Value
End If
Next Zelle
End Function

Der Aufruf in der Tabelle erfolgt dann z.B. so (summiert alle Zellinhalte von Zellen in C1:C10 mit blauer Hintergrundfarbe):

=HFarbeSumme(C1:C10;5)

4. Zellinhalte von Zellen mit bestimmter Schriftfarbe sollen summiert werden:

Public Function SFarbeSumme(Bereich As Range, Farbe As Integer)
Dim Zelle As Range
Application.Volatile
For Each Zelle In Bereich
If Zelle.Font.ColorIndex = Farbe Then
SFarbeSumme = SFarbeSumme + Zelle.Value
End If
Next Zelle
End Function

Der Aufruf in der Tabelle erfolgt dann z.B. so (Summiert alle Zellinhalte von Zellen in D1:D10 mit gelber Schriftfarbe):

=SFarbeSumme(D1:D10;6)

Eine Übersicht über alle Colorindizes seht ihr hier: