Excel - Auswertung von farbigen Zellen :: Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office

Einloggen, um private Nachrichten zu lesen

Downloads

Excel - Auswertung von farbigen Zellen

Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office Foren-Übersicht » Microsoft Excel Tutorials

Autor

Nachricht

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 4431

Verfasst am: 05.12.2007, 16:41

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:

Code:

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):

Code:

=HFarbeZählen(A1:A10;3)

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

Code:

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):

Code:

=SFarbeZählen(B1:B10;4)

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

Code:

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):

Code:

=HFarbeSumme(C1:C10;5)

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

Code:

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):

Code:

=SFarbeSumme(D1:D10;6)

Eine Übersicht über alle Colorindizes seht ihr hier:

Colorindex.jpg

Beschreibung:

Dateigröße:

76.74 KB

Angeschaut:

1045 mal

_________________

Viele Grüße vom Schatzi
------------------------
Bitte nimm dir die Zeit für eine (kurze) Rückmeldung, ob dir mein Beitrag geholfen hat oder nicht.
Andere User, die das gleiche (oder ein ähnliches) Problem haben, werden dir dankbar sein.

Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office Foren-Übersicht » Microsoft Excel Tutorials

Thema		Autor	Forum	Antworten	Verfasst am
Ähnliche Beiträge
	Zellen per Makro Formel zuweisen	timi	Microsoft Excel Hilfe	11	16.05.2008, 23:36
	Tägliche Sicherung einer Excel Datei	Ironman2020	Microsoft Excel Hilfe	3	14.05.2008, 16:16
	CashFlow Controllign mit Excel	controlling1984	Microsoft Excel Hilfe	0	13.05.2008, 17:53
	nur Bestimmte Zellen zur Ausgabe benu...	hallolledu	Microsoft Excel Hilfe	3	09.05.2008, 14:42
	Telefonnummern in Excel formatieren	ersguterjunge	Microsoft Excel Hilfe	5	08.05.2008, 15:29

Gehe zu:

Du kannst keine Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum nicht antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum posten
Du kannst Dateien in diesem Forum herunterladen

Powered by phpBB © 2001, 2002 phpBB Group :: FI Theme :: Alle Zeiten sind GMT + 1 Stunde
Deutsche Übersetzung von phpBB.de