In einer Tabelle kennzeichne ich bestimmte Werte, die nicht dem inhaltlichen Standard entsprechend mit einer bestimmten Hintergrundfarbe. Da die Position und auch die Anzahl pro Monat wechseln, wollte ich nur diese Werte in der Liste addieren.
Leider kenne ich keine Funktion in Excel, mit der ich Farbhintergründe abfragen kann, weshalb ich mir einfach schnell eine eigene Funktion erstellt habe.
Es gibt nur Indirekt (im wahrsten Sinne des Wortes) die Möglichkeit in Excel die Hintergrundfarbe abzufragen. Und selbst damit ist es erforderlich letztlich eine neue Spalte einzufügen, um dann die Zahlen gezielt zu summieren. Ein sehr schönes Video dazu gibt es von Andreas Thehos auf YouTube:
Andreas Theos: Farben der Zelle auswerten
Meine Funktion geht dabei anders vor. Der zu summierende Bereich kann individuell übergeben werden und wird direkt summiert. Ohne das erst eine neue Spalte eingebaut werden muss.
Code und weitere Erklärung im Tab "Code". Wer nicht weiß, wie und wo man den Code einbauen muss, schau bitte hier nach. Wobei es sich hier um eine Funktion und kein Makro handelt. Der ganze Teil mit dem Menüband anpassen entfällt somit, da eine Funktion direkt von Excel aus aufgerufen wird.
Function HintergrundSumme(y As Range, Farbuebergabe As Range) As Currency On Error Resume Next
Dim Zelle As Range Dim Zahl As CurrencyFor Each Zelle In y If Zelle.Interior.Color = Farbuebergabe.Interior.Color Then Zahl = Zahl + Zelle.Value Next HintergrundSumme = ZahlEnd Function
Die erste Zeile gibt den Namen meiner Funktion an. Ich übergebe eine Range und erhalte einen Wert als Währungstyp (Currency) zurück. Und ich übergebe eine Zelle, in der die Hintergrundfarbe steht, die ich abprüfen und summieren möchte.
Der Code ist letztlich sehr einfach gehalten. Ich frage mit einer For Each Schleife ab, ob ein Feld die Hintergrundfarbe besitzt (Zelle.Interior.Color). Alles, was ich nun tun muss, ist die Zahl zur bisherigen Summe zu addieren und am Ende per Funktionsvariable zurückzugeben.
In Farbübergabe ist letztlich eine Zelle gespeichert, weshalb hier einfach der Farbhintergrund abgefragt werden kann.
Klein, aber fein - wie es immer so schön heißt.
In Excel wird die Funktion folgendermaßen aufgerufen:
=HintergrundSumme(A1:A3;A1)
Wobei die Werte in den Klammern durch Ziehen mit der Maus über die Spalte ermittelt werden. In meinem Beispiel steht u.a. in A1 der gelbe Farbhintergrund. Die beiden Zahlen mit gelben Hintergrund werden ordnungsgemäß summiert.
Wenn Sie die Funktion durch Ziehen kopieren möchten (also beispielsweise nach rechts ziehen), dann setzen Sie die Zelle mit der übergebenen Hintergrundfarbe als absoluten Zellbezug:
=HintergrundSumme(A1:A3;$A$1)
Leider muss die Zeile, obwohl Currency zurückgegeben wird, dennoch als Währungsfeld formatiert werden!
Wer die Vordergrundfarbe abfragen möchte, kann das mit nachfolgenden Befehl machen:
Zelle.Font.Color
Wer mit festen Werten in den Farben arbeiten möchte, findet u.a. hier eine Liste mit Farbwerten für unterschiedlichen Anwendungen:
Color Palette and the 56 Excel ColorIndex Colors
Dann wäre der Aufruf beispielsweise in dieser Art:
If Zelle.Interior.Color = 15773696 Then Zahl = Zahl + Zelle.Value
Hier noch ein Beispiel, wo ich verschiedene Hintergründe in einer Tabelle habe. Der schwarze Hintergrund ist die Gesamtsumme über alle Posten, die Zeilen darunter dann die jeweiligen Farbsummierungen. Die Hintergrundfarben greife ich oben bei den "Rubriken" ab und übergebe diesen Farbwert per absoluten Zellbezug. So kann ich die Formel bequem über alle Monate ziehen.
Der Funktionsaufruf sieht folgendermaßen aus:
=HintergrundSumme(F20:F47;$D$16)