In Excel mit Farben rechnen 16

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.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

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.



Schreibe einen Kommentar zu Marcel Antworten abbrechen

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

16 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ß Autor des Beitrags

      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ß Autor des Beitrags

        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)

  • Günter Ziepa

    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.

    .

    • Martin Weiß Autor des Beitrags

      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

  • Lars

    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

    • Martin Weiß Autor des Beitrags

      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