In Excel mit Farben rechnen 12

Mit einem kleinen Trick lässt sich in Excel auch mit Farben rechnen
 

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

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

Filtern nach Farben

Schon ganz praktisch, hat aber einen entscheidenden Haken: Die Zeile mit den Gesamtwerten verschwindet leider.

Gefiltert, aber ohne Ergebnis-Zeile

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 1Entspricht der Funktion
1MITTELWERT
2ANZAHL
3ANZAHL2
4MAX
5MIN
6PRODUKT
7STABW
8STABWN
9SUMME
10VARIANZ
11VARIANZEN
101MITTELWERT
102ANZAHL
103ANZAHL2
104MAX
105MIN
106PRODUKT
107STABW
108STABWN
109SUMME
110VARIANZ
111VARIANZEN

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

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.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

12 Gedanken zu “In Excel mit Farben rechnen

  • Michael

    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?

      • Hannes

        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!

    • Andreas Unkelbach

      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

  • Andreas Unkelbach

    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

    • Martin Weiß Beitragsautor

      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

      • Andreas Unkelbach

        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

  • Alexander

    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

    • Stefan

      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

  • Richard Pfeifer

    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

      • Martin Weiß Beitragsautor

        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)