Ergebnis 1 bis 1 von 1
  1. #1

    Excel - Auswertung von farbigen Zellen

    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:
    Angehängte Grafiken Angehängte Grafiken

    Viele Grüße vom Schatzi

    ------------------------
    Dir wurde geholfen? Dann gib doch bitte ein Feedback! Du hilfst damit automatisch anderen Usern, die ein ähnliches Problem haben!

    Ich arbeite mit: Excel 2010, Windows 7
    Ein Tool, um Tabellen(ausschnitte) in diesem Forum posten zu können, findet ihr z.B.
    hier
    .

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  
SMS kostenlos versenden | Krankenversicherungsvergleich | Wii Modchip Xbox 360 Konsole | VLC Download | Forenuser - Die Foren Findmaschine