Excel bietet bei den Autofiltern ja die ungemein praktische Möglichkeit, nach Text- oder Schriftfarbe zu filtern.
Nun stellt sich manch einer die Frage, ob man nicht auch auf Basis von Zellenfarben rechnen kann. Also zum Beispiel die Summe über alle rot eingefärbten Zellen oder die Anzahl aller gelben Zellen berechnen.
Das ist in Excel leider (oder zum Glück?) nicht vorgesehen. Wer jedoch partout nicht darauf verzichten will, kommt mit einem kleinen Trick doch auf das gewünschte Ergebnis.
Und so geht’s:
Filtern nach Farben
Wem es bisher entgangen sein sollte: Tabellen lassen sich in Excel auch nach Textfarbe oder Schriftfarbe filtern
(übrigens: Man kann nicht nur nach Farbe filtern, sondern auch sortieren).
In der folgenden Tabelle habe ich die drei höchsten und die drei niedrigsten Umsätze farbig markiert. Zusätzlich zeigen die Funktionen ANZAHL2 (in Zelle C26) und SUMME (in Zelle D26) die Gesamtwerte:
Ausgangstabelle mit farbigen Markierungen
Im Autofilter bietet Excel automatisch alle in der jeweiligen Spalte verwendeten Farben an, aus denen man sich dann die gewünschte auswählen kann:
Filtern nach Farben
Schon ganz praktisch, hat aber einen entscheidenden Haken: Die Zeile mit den Gesamtwerten verschwindet leider.
Gefiltert, aber ohne Ergebnis-Zeile
Die Lösung: TEILERGEBNIS
Das Problem lässt sich ganz leicht mit der vielseitigen TEILERGEBNIS-Funktion lösen. Sie vereint nämlich viele Funktionen in einer einzigen. Dabei muss lediglich als erster Parameter die gewünschte Funktion angegeben werden:
| Parameter 1 | Entspricht der Funktion |
|---|---|
| 1 | MITTELWERT |
| 2 | ANZAHL |
| 3 | ANZAHL2 |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUKT |
| 7 | STABW |
| 8 | STABWN |
| 9 | SUMME |
| 10 | VARIANZ |
| 11 | VARIANZEN |
| 101 | MITTELWERT |
| 102 | ANZAHL |
| 103 | ANZAHL2 |
| 104 | MAX |
| 105 | MIN |
| 106 | PRODUKT |
| 107 | STABW |
| 108 | STABWN |
| 109 | SUMME |
| 110 | VARIANZ |
| 111 | VARIANZEN |
Die Werte 1 bis 11 beziehen ausgeblendete Werte ein, 101 bis 111 ignorieren ausgeblendete Werte. Mit „ausgeblendet“ sind aber nur manuell ausgeblendete Zeilen gemeint. Alles, was über die Filterfunktion ausgeblendet wird, ignoriert die TEILERGEBNIS-Funktion auf jeden Fall.
Somit können wir anstelle der ANZAHL2-Funktion in Zelle C26 folgendes verwenden:
=TEILERGEBNIS(3;C2:C25)
Und anstelle der SUMME-Funktion in Zelle D26 die TEILERGEBNIS-Variante:
=TEILERGEBNIS(9;C2:C25)
Wenn wir jetzt wieder nach der Farbe filtern, liefert Excel brav die gewünschten Ergebnisse:
Gefiltert mit TEILERGEBNIS
Mit diesem kleinen Trick lässt sich also doch noch mit Farben rechnen. Und ganz nebenbei hast du auch die TEILERGENIS-Funktion kennengelernt.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo,
bei mir verschwindet die Zeile mit den Gesamtwerten trotzdem wenn ich nach Farben filter (Zeile mit Gesamtwerten ist ja nicht farbig markiert). Muss ich die Zeile noch irgendwie kennzeichnen?
Hallo Michael,
Der Datenfilter darf die Gesamtzeile nicht mit einschließen.
SG
Marcel
Genau. Deshalb lege ich die Teilergebnisfunktionen gern oberhalb der Liste ab. Man weiss ja nie, wie lang die Liste wird und muss dann nicht die Ergebnisse irgendwo unten suchen.
Euch eine schöne Woche!
Hallo Michael.
Alternativ kann die Tabelle auch als Datentabelle formatiert werden.
Hierzu kann im Ribbon START in der Befehlsgruppe Formatvorlagen die Schaltfläche „Als Tbaelle formatieren“ gewählt werden.
Danach kann unter Tabellentools Ribbon „Entwurf“ die „Ergebniszeile“ aktiivert werden. So ist automatisch ein Teilergebnis ausgewählt und es können auch weitere Zellen hinzugefügt werden bzw. die Daten gefiltert werden.
Viele Grüße
Andreas
Hallo Martin,
hier zeigt sich einmal wieder, wie unterschiedlich in Excel eine Fragestellung beantwortet werden kann. Ich hatte vor ein paar Tagen noch im Artikel „Excel rechnet mit Farben oder ZÄHLENWENN bzw. SUMMEWENN anhand der Hintergrundfarbe der Zelle dank ZELLE.ZUORDNEN ohne VBA“ die gleiche Fragestellung beackert allerdings ohne Filter gelöst.
Die Funktion Teilergebnis leigt ebenfalls noch im Entwurfmodus vor, so dass ich hier gerne auf deinen Artikel verweisen mag, wenn ich doch noch dazu komme den Beitrag zu veröffentlichen :-). Immerhin ist dieses ein schöner Anlass hier einmal wieder daran erinnert zu werden. Besonders der Unterschied zwischen 1 und 101 ist gerade bei umfangreichen Listen ein wertvoller Hinweis (und hat mir schon manch Nerv gekostet, wenn in einer ausgblendeten Zelle ein davon Vermerk versteckt war und dann der Mittelwert nicht mehr plausibel erscheint).. ;-).
Viele Grüße
Andreas
Hallo Andreas,
die von Dir vorgestellte Lösung mit der ZELLE.ZUORDNEN-Funktion gefällt mir auch sehr gut. In den alten Makrofunktionen schlummern nach einige Perlen und man braucht dabei nicht mal wirklich auf Makros oder VBA zurückgreifen. Der von Dir beschriebenen Nachteil, dass man die Excel-Datei dann auch im Format XLSM (also dem Makro-Format) speichern muss, können die meisten Anwender vermutlich verschmerzen.
Was mich jedoch gerade viel mehr irritiert: Die Lösung mit der ZELLE.ZUORDNEN-Funktion scheint unter der aktuellsten Excel-2016-Version nicht mehr korrekt zu arbeiten. Es wird nur noch ein #BEZUG!-Fehler ausgespuckt. In der exakt gleichen Variante unter Excel 2007 läuft alles einwandfrei. Da wird doch nicht etwa Microsoft diese schöne Funktion eingestampft haben…?
Kann dieses Problem vielleicht noch irgendein anderer Leser hier bestätigen?
Ratlose Grüße,
Martin
Hallo Martin,
es scheint tatsächlich so zu sein, dass die Formel ZELLE.ZUORDNEN(63, ZELLE) noch funktioniert, so liefert mir zum Beispiel der Namensmanager mit ZELLE.ZUORDNEN(63, A2) die Hintergrundfarbe der Zelle A2. Allerdings scheint der indirekte Bezug mit =ZELLE.ZUORDNEN(63;INDIREKT(„ZS“;)) nicht mehr zu klappen…. was extrem schade ist.
Von daher könnte man zwar für die einzelnen Zellen eine Hintergrundfarbe ermitteln, aber es ist nicht mehr möglich bezogen auf die aktuelle Zelle die Hintergrundfarbe der versetzten Zelle auszulesen.
Vielleicht gibt es ja eine andere Bezugsformel, die hier ab Excel 2016 in Verbindung zur ZELLE.ZUORDNEN genutzt werden kann.
In Office 2013 scheint die Formel noch funktioniert zu haben siehe:
http://answers.microsoft.com/de-de/msoffice/wiki/msoffice_excel-mso_other/die-excel4-makrofunktion-zellezuordnen/6ee8af02-b52c-45b7-94ef-7f7bb7e45d88
Vielleicht hat es durchaus Vorteile nicht immer die aktuellste Excelversion zu nutzen 😉
Verwirrte Grüße
Andreas
Hallo Martin,
vielen Dank für den Tipp. Ich habe eine Lösung gefunden, mit der man verschiedene Schriftfarben z.B. einer Zeile addieren kann:
=Farbsummes((Bezug);1) 1 ist hier die Schriftfarbe schwarz (nicht die automatische schwarz)
=Farbsummes((Bezug);3) 3 ist rote Schriftfarbe
Viele Grüße
Alexander
Hallo Alexander.
FARBSUMMES ist aber keine Standardfunktion in Excel. Die Funktion ist das Werk eines anderen Excel-Experten. Sie muss erst mittels Add-In installiert oder mittels VBA-Code selbst „eingebaut“ werden.
Gruß
Stefan
Hallo Martin,
ich arbeite schon viele Jahre mit Excel, aber viele Deiner Tipps sind für mich eine echte Hilfe.
Gerade hab ich folgendes Problem mit eine Vereins-Jubiläumstabelle:
Name Geburtstag Eintritt Hochzeit
Müller 50
Meier
Muster 25
Vogel
Baum 25
Wie kann ich die Spalten in denen kein Jubiläum steht unterdrücken?
Danke für die Tipps und viele Grüße
Richard
Nachtrag: die Jubiläumsjahre sollten in den drei Spalten „Geburtstag Eintritt Hochzeit“ stehen
Hallo Richard,
ich vermute mal, Du möchtest nicht die Spalten, sondern die Zeilen ohne Jubiläum unterdrücken, richtig? Dazu würde ich rechts eine weitere Spalte anfügen (Jubiläum ja/nein) und eine Formel reinsetzen, die prüft, ob die drei vorausgegangenen Spalten leer sind. Beispiel:
Die Namen stehen in Spalte A (beginnend ab Zeile 2), die drei Jubiläumsspalten sind B, C und D. In Zelle E2 kommt folgende Formel:
=ANZAHL(B2:D2)<>0
Danach kannst Du auf alle Zeilen filtern, bei denen in Spalte D „FALSCH“ steht.
Schöne Grüße,
Martin
Die
einen Autofilter setzen (Register Daten – Filter)
Ich möchte die Summe aller Zell-Werte, die z.B. mit einem Unterstrich versehen sind.
Kann Excel das überhaupt?
Ich habe eine Besuchsstatistik erstellt, wobei ich den/die frühesten Besucher (Anzahl) mit einem Unterstrich versehe.
Wenn ich die Ziffer kursiv schreibe, und unterstreiche, wird erkenntlich, daß der/die frühesten Besucher zumindest aus einem Pärchen bestand.
Und wenn ich die rote Schriftfarbe wähle, dann geht daraus hervor, daß der/die Besucher schon lange nicht mehr anwesend waren.
Das Ganze ist eine Vogel-Besuchs-Statistik. Wenn ich die Anzahl in nur 1 Schriftart schreibe und die Füll-Farbe verwende, kann ich nur max. 2 Eigenarten damit ausdrücken. Denn in einer Zelle kann ich mich nur für 1 Füllfarbe entscheiden. Mit dem Schrift-Format sind jedoch 3 Kriterien abbildbar und es lassen sich sogar Kombinationen ausdrücken.
(Beispiel: wenn ein o mehrere Vogelpaar/e lange nicht mehr hier war, dann schreibe ich die Anzahl in rot und kursiv. Und wenn diese Art die erste im Beobachtungszeitraum war, dann wird die Anzahl auch noch unterstrichen.
Oh Gott, wie kann man das bloß auszählen.
Ich habe schon Stunden verbraucht und schaffe es nicht.
Daß ich daher für eine Idee endlos dankbar wäre, ist klar.
.
Hallo Günter,
ich kenne jetzt den Aufbei deiner Tabelle nicht, aber ich empfehle grundsätzlich, nicht zuviel „Intelligenz“ in einzelne Zellen zu packen. Denn das lässt sich hinterher nur schwer bis gar nicht auswerten. Stattdessen sollten unterschiedliche Informationen in separate Spalten (in deinem Fall z.B. der besagte Unterstrich, der dann ja auch einfach ein Kreuzchen sein könnte).
Die Verwendung von Farb- und Schriftattributen ist zwar gut für den Anwender, um auf einen Blick Sachverhalte erkennen zu können. Aber absolut ungeeignet für Auswertungen. Auch hier gilt: unterschiedliche Informationen oder Eigenschaften voneinander trennen und in eigene Spalten packen. So etwas lässt sich später mit SUMMEWENN, SUMMEWENNS, SUMMENPRODUKT und ähnlichen Funktionen besser analysieren.
Schöne Grüße,
Martin
Hi Martin,
na super. Leider keine Lösung für mein Problem, das da wäre:
Ich habe bereits eine Tabelle mit Namen unterschiedlicher Schriftfarbe (Schwarz und Rot, Fett) . Nun möchte ich aber in einem weiteren Tabellenblatt die Anzahl der rot markierten Namen, also nach Schriftfarbe, zählen. Wie geht das ?
Gruß Lars
Hallo Lars,
die einfache Antwort: Das ist in Excel nicht vorgesehen.
Die etwas längere Antwort: Es geht über Umwege mit der alten Excel-4-Makrofunktion ZELLE.ZUORDNEN. Weiter oben in den Kommentaren hat Andreas Unkelbach etwas dazu geschrieben. Daher verweise ich hier nur auf seinen Kommentar und Artikel. Für die Schriftfarbe musst du den Infotyp 24 verwenden, also beispielsweise =ZELLE.ZUORDNEN(24;A1)
Aber ich persönlich rate davon ab, irgendwelche Berechnungen auf Basis von Farben oder sonstigen Schriftattributen durchzuführen.
Schöne Grüße,
Martin
Lieber Martin
Gibt es deines Wissens nach auch eine Möglichkeit, numerische Zellinhalte, deren Hintergrund per bedingter Formatierung gefärbt wurde, zu addieren? Die Funktion ZELLE.ZUORDNEN funktioniert da leider nicht. Ich nehme an, dazu bedarf es eines speziellen Makros …
Über Tipps und Hilfe wäre ich sehr dankbar.
SG
Martin
Hallo Martin,
nein, es gibt keine Möglichkeit, mit normalen Mitteln Berechnungen aufgrund von Zellfarben anzustellen. Es spielt dabei auch keine Rolle, ob es sich um eine manuelle oder eine bedingte Formatierung handelt. Wenn man so etwas umsetzen möchte, müsste zwingend auf VBA zurückgegriffen werden.
Schöne Grüße,
Martin