Summe nach Zellfarbe bilden

Eine SUMMEWENN-Funktion berücksichtigt zwar Kriterien und bildet bei Erfüllen dieser die Summe der jeweiligen Werte, beispielsweise die Zellfarbe kann allerdings nicht ohne weiteres als Kriterium erkannt und berücksichtigt werden. Dazu benötigt es einen zusätzlichen Schritt, welchen wir in diesem Beitrag betrachten:

Im Menüband gehst Du unter "Formeln" auf "Name definieren". Gib dort als Name "Farbe" ein - dies ist später der Name einer Formel, welche wir in eine Hilfsspalte eingeben werden (Du kannst daraufhin "=Farbe" in eine Zelle eingeben). Bei "Bezieht sich auf" gibst Du folgendes ein:

=ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))

Zusammengefasst sieht Dein definierter Name wie folgt aus:

ZellfarbeName

[Der Bezug ZS(-1) ist ein Referenzbezug: Dieser bedeutet, dass Du auf die selbe Zeile (Z), allerdings auf eine Spalte (S) nach links (-1) zugreifst.]

Wenn Du nun eine Spalte rechts Deiner farbigen Zelle "=Farbe" eingibst, wirst Du eine Zahl erhalten - diese bezieht sich auf die Zellfarbe. Wenn Du die Zellfarbe änderst, wird sich auch das Resultat in der Zelle nebenan verändern, sobald Du die "=Farbe"-Formel aktualisierst (oder F9 auf der Tastatur zur Kalkulation des gesamten Tabellenblatts drückst). Nun kannst Du die bekannte SUMMEWENN-Formel verwenden und alle Werte summieren, welche in der Spalte nebenan den entsprechenden Farbcode aufweisen. In diesem Beispiel erstelle ich die Summe aller orangen Zellen (mit dem Farbcode 44):

SummewennZellfarbe

(10 + 2 + 3 + 5 + 10 = 30)

Wichtig zu beachten ist, dass Du Deine Datei daraufhin als .xlsm (Makro-Datei) abspeichern musst, damit die "=Farbe"-Funktion auch zu einem späteren Zeitpunkt wieder funktionsfähig ist.

Dynamische Ergebnisse (Formel "Teilergebnis")

Deine Excel-Tabellen müssen dynamisch sein. Was bedeutet das? Und weshalb und vor allem wie kann man das bewerkstelligen?

Hast Du schon einmal einen Bericht oder Daten erhalten, darauf einen Filter angewandt und das angezeigte Ergebnis konnte einfach nicht stimmen (sprich es hat sich nicht dem Filter entsprechend angepasst)? Schlimmer sind jedoch Fälle, bei denen man nicht einmal bemerkt, dass von einer falschen Datenbasis ausgegangen wird und man deshalb das angezeigte Total weiterverwendet. In diesem Beitrag schauen wir an, wie Du Deine Ergebnisse dynamisch gestalten kannst - es soll also stets von den angezeigten Daten ausgegangen werden. Dazu verwenden wir die Formel "TEILERGEBNIS".

Die Formel TEILERGEBNIS im Vergleich

Damit Du siehst, was ich genau mit meiner Einleitung meinte, schauen wir uns nachfolgende Grafik an.

Teilergebnis1

Es sind Verkaufsdaten zu den jeweiligen Tagen erfasst. Unterhalb wird das Total mittels der normalen Formel "SUMME" ermittelt. Wendet nun der Empfänger dieser Daten den Filter an und lässt sich beispielsweise nur den Kunden "30001" anzeigen, wird die Formel nach wie vor dasselbe Ergebnis wie in der oberen Grafik darstellen. Die "SUMME"-Formel erkennt nicht, dass ein Filter gesetzt worden ist und hält eisern am angegebenen Bereich (C2:C8) fest.

Teilergebnis2

Verwendest Du hingegen die intelligentere Formel "TEILERGEBNIS", wird der Filter ebenfalls berücksichtigt. Die Formelsyntax beinhaltet eine Funktion und einen Bezug. Die Funktion ist eine Zahl, stellvertretend für die jeweilige Formel (ANZAHL, MAX, MIN etc.) - SUMME hat die Zahl 9. Sobald Du "=TEILERGEBNIS(" eingibst, werden die verfügbaren Formeln angezeigt. Desweiteren ist der Bereich anzugeben, welcher gleich ist wie bei der herkömmlichen SUMME-Formel. Für die Spalte "Umsatz" (C) lautet die Formel demnach:

=TEILERGEBNIS(9;C2:C8)

Teilergebnis3

Nun kannst Du oder der Empfänger Deiner Daten willkürlich filtern - und es wird stets die richtige Summe gezogen werden! Solche Absicherungen sind wichtig, denn es stellt die Richtigkeit der Angaben sicher und stärkt das Vertrauen des Empfängers der Daten!

Dynamische Titel und Weiterführendes

Im Beitrag Dynamische Titel (Formel "TEXT") ist die Formel "TEXT" erläutert und es wird Dir gezeigt, wie Du dynamische Titel setzen kannst. Da wird - auf den Filter reagierend - ein Titel definiert, sodass jeweils das Von- und Bis-Datum stets korrekt angezeigt wird.