11:18 Uhr
Excel rechnet mit Farben oder ZÄHLENWENN bzw. SUMMEWENN anhand der Hintergrundfarbe der Zelle dank ZELLE.ZUORDNEN ohne VBA
Hierbei werden mit Farben folgende Rückmeldungen festgehalten worden:
- Liste mit Teilnahmegebühren und farbliche Hervorhebung ob die Gebühr bezahlt worden ist oder nicht.
- Liste mit Rückmeldungen ob an einer Veranstaltung teilgenommen wird oder eben nicht.
Nehmen wir als Beispiel einmal folgende Ausgangstabellen, wie in der Abbildung zu sehen ist.

Nun könnte man natürlich auf die Idee kommen die einzelnen Rückmeldungen oder auch Rechnungsbeträge per Filter, wie im Artikel "Vorteil von Excel Formatvorlagen und Filter nach Farben oder Zellensymbolen aus bedingter Formatierung" zu sortieren um dann ein entsprechendes Teilergebnis zu berechnen. Dieses ist dann allerdings unschön, da sich ja die Farben auch ändern können. Zum Beispiel könnte die Rechnung Kunde-A-02 ja doch noch bezahlt werden und der Rechnungsbetrag ist einfach untergegangen.
Hier hat mich ein Kollege auf die Excel4-Makrofunktionen ZELLE.ZUORDNEN hingewiesen. Eine solche Makrofunktion kann nicht direkt im Tabellenblatt genutzt werden sondern muss im Namensmanager als benannte Formel eingerichtet werden. Eine sehr gute umfassende Beschreibung ist von Frank Arendt-Theilen im Artikel "Die Funktion ZELLE.ZUORDNEN() " veröffentlicht worden (der Artikel ist auch in der Microsoft Answers zu finden, aber mir ist die Verlinkung auf ein persönliches Blog immer lieber).
Frank Arendt-Theilen ist auch als Dozent unter anderen bei Video2brain als Autor tätig. Auf einige gute Schulungsvideos zu Excel (und andere Office Produkte) sind im Artikel "Video2brain - Onlineschulung per Videostreaming unter Android, Windows, iOS und Web" zu finden.
Das Schöne an dieser Funktion ist, dass hier nicht VBA aktiviert werden muss sondern diese direkt funktioniert. Allerdings muss in "neueren" Excelversionen (ab 2007) zur Nutzung der Makrofunktion die Datei als "Excel Arbeitsmappe mit Makros" (Dateiendung XLSM) gespeichert werden.
Nun aber zur tatsächlichen Lösung. Für Facebook Abonenten (siehe facebook.com/Unkelbach) ist dieses schon vor einigen Tagen angesprochen worden. Nun möchte ich aber die Lösung etwas ausführlicher beschreiben und zum Ende eine offene Frage stellen in der Hoffnung, dass vielleicht andere Excelblogs eine Lösung für diese Frage haben.
Über den Ribbon FORMELN kann in der Befehlsgruppe "Definierte Namen" der Namensmanager aufgerufen werden und hier zwei neue Namen definiert werden, wie in der folgende Abbildung schon dargestellt worden ist.

Zur besseren Lesbarkeit noch einmal beide definierte Namensfunktionen:
Name | bezieht sich auf |
---|---|
Farbe_L1 | =ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";)) |
Farbe_Zelle | =ZELLE.ZUORDNEN(63;INDIREKT("ZS";)) |
Dabei wird über Farbe_L1 der Wert der Hintergrundfarbe der linken Zelle und über Farbe_Zelle der Wert der Hintergrundfarbe der aktuellen Zelle ausgegeben. Die indirekte Zuordnung von Zellen und Spalten anstatt direkt mit Werten zu arbeiten ist sehr verständlich (inklusive Hinweis auf internationale Excelversionen) im Artikel "#INDIREKT mit #Nummerierung" von Katharina Schwarzer auf soprani.at (Soprani Software @KatharinaKanns) beschrieben. Insgesamt sind nebenbei einige Twitteraccounts zu Excel sehr lesenswert, aber das nur am Rande.
Beschreibung ZELLE.ZUORDNEN
Die EXCEL4-Makroformel ZELLE.ZUORDNEN ist dabei wie folgt aufgebaut:
ZELLE.ZUORDNEN(Typ;Bezug)
Als Typ ist im oberen Beispiel 63 (Wert der Farbe für die Füllung (Hintergrund) einer Zelle und als Bezug ist wie beschrieben die Zelle eine Spalte (-1) der aktuellen Zelle oder aber die aktuelle Zelle selbst festgehalten. Näheres dazu ist auch im Artikel von soprani.at zu finden.
Sofern die Vordergrundfarbe (Muster) abgefragt werden soll kann hier als Typ 64 gewählt werden. Für die weiteren Argumente und weitere Typen verweise ich auf den Artikel von Frank Arendt-Theilen.
Jetzt können wir über =Farbe_L1 den Wert der Hintergrundfarbe einer Zelle links von der Formeleingabe ausgeben lassen.
Formeln automatisch auf markierte Zellen übertragen lassen (STRG + ENTER)
Hierzu markiere ich die Zellen D9:D16 sowie H9:H16 und trage als Wert in der Eingabezeile die Anweisung ein, dass hier die Hintergrundfarbe ausgegeben werden soll. Durch die Tastenkombination STRG und ENTER schliesse ich die Eingabe ab.
Auch wenn ich die Formel eigentlich in der Zelle H9 eingetragen habe, wird diese Formel automatisch auch in den anderen Zellen eingetragen.
Dieses ist eine Form des automatischen Ausfüllens durch das nicht Formatierungen überschrieben werden und gerade bei umfangreicher formatierten Tabellen für mich mittlerweile eine der Lieblingstastenkombinationen in Excel ist.
Im Ergebnis ist nun in der Spalte neben der Eingabe die Hintergrundfarbe als Zahlenwert stehen.

Hier kann nun per Zählenwenn oder SummeWenn im oberen Abschnitt die Teilnehmenden oder die Rechnungsbeträge ausgewiesen werden. Dabei kann natürlich auch in der Formel selbst ein Bezug auf die Zelle in der Legende per Farbe_L1 genommen werden.
ZÄHLEWENN Hintergrundfarbe der Zelle übereinstimmt
Um die jeweiligen Rückmeldungen zu zählen wird die Formelin den Zellen C3 bis C6 eingetragen, so dass hier die übereinstimmende Hintergrundfarben gezählt werden.=ZÄHLENWENN($D$9:$D$16;Farbe_L1)

So hat als Beispiel GUT den Hintergrundfarbwert 35, so dass hier die beiden Rückmeldungen von Andreas und Claudia gezählt werden. Die beiden negativen Rückmeldungen von Gustav und Heinrich werden natürlich ebenso gezählt.
SUMMEWENN Hintergrundfarbe
Ebenso kann natürlich auch eine Summe gebildet werden, wobei der Suchbereich die Zellen H9:H16 und der Summenbereich die Zellen G9:G16 sind (elegant wäre es natürlich hier ebenfalls mit Namen zu arbeiten ;-)).Hier lautet die Formel demnach in den Zellen G3:G6wie auch in der Abbildung zu sehen ist.=SUMMEWENN($H$9:$H$16;Farbe_L1;$G$9:$G$16)

Farbergebnis mit direkten Bezug auf Zelle
Wir hatten ja eingangs auch per Namensmanager Farbe_Zelle definiert. Dieses kommt in der Summenzeile nun im Einsatz und gibt direkt in der Zelle selbst die Summen bzw. Teilnehmenden aus.So lautet die Formel für die Teilnehmende:
=ZÄHLENWENN($D$9:$D$16;Farbe_Zelle)
und für die Summe der Rechnungen;
und das gewünschte Ergebnis sieht dann wie folgt aus, wobei ich hier die Hilfsspalten D und H entsprechend ausgeblendet (bzw. Gruppiert) habe.=SUMMEWENN($H$9:$H$16;Farbe_Zelle;$G$9:$G$16)

Im Ergebnis kann so also tatsächlich mit der Hintergrundfarbe in Excel gerechnet werden.
Natürlich ist der umgekehrte Weg vorhandene Zellen bedingt zu formatieren (siehe "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)") etwas pflegeleichter aber hier kann direkt mit entsprechend vorhandenen Formatierungen gearbeitet werden. Ausserdem sind Farben ja auch sprechende Informationen ;-) Ebenfalls ein Vorteil ist, dass fehlende Teilnehmende zum Beispiel Elisabeth oder Emil ebenfalls in der Liste ergänzt werden können und natürlich auch weitere Rechnungen in der Liste als bezahlt markiert oder auch andere Positionen ergänzt werden.
Offene Frage an andere Excelexperten
Leider habe ich es ohne Hilfsspalten nicht geschafft eine solche Berechnung hinzubekommen, würde mich aber sehr freuen, wenn als Kommentar eine entsprechende Lösung (gerne auch durch einen anderen Blogartikel auf den ich dann verlinken würde) ergänzt werden könnte. Ein variabler Index über die Hintergrundfarbwerte in Form einer Matrixfunktion wäre hier natürlich ein absoluter Königsweg, den ich aber leider nicht geschafft habe zu beschreiten.Aber auch mit der Hilfsspalte selbst ist die Lösung für manche Anwendungsfälle schon sehr hilfreich. Besonders elegant ist diese Lösung auch für Einrichtungen bei denen VBA per Gruppenrichtlinie in Excel deaktiviert ist... wobei dadurch auch die Excelansicht in SAP nicht mehr funktioniert was dann aber ein anderes Problemfeld ist.
Nachtrag Makrofunktionen und Excel 2016:
Ein Kollege hat mich an dieser Stelle auf einen Artikel zum Thema "In Excel mit Farben rechnen" von Martin (tabellenexperte.de) hingewiesen.Martin Weiß weist mich im Artikel zu den Kommentaren auf folgenden Sachverhalt aufmerksam gemacht:
Darauf hatte ich damals auf folgenden Umstand hingewiesen:
Martin Weiß (tabellenexperte.de)"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…?"
Vielen Dank an dieser Stelle an meinen Kollegen für den Hinweis auf obigen Artikel den ich gerne zum Anlass nehme um auf die Filterfunktion nach Farben und Teilergebnis, das beides auf elegante Weise ebenfalls ein Rechnen nach Farben ermöglicht.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
Manchmal ist Excel wirklich spannend. im Artikel "Summieren nach Farbe mit ZELLEN.ZUORDNEN (ohne VBA)" ist Lukas Rohr (excelnova.org) ebenfalls auf diese Formel eingegangen aber unter Excel 2019 scheint diese wieder zu funktionieren:
Vielen Dank für diesen Hinweis..eigentlich müssste ich diese Formel nun auch unter Excel 2016 erneut testen in der Hoffnung, dass dank Update die Formel vielleicht doch wieder funktioniert....Lukas Rohr "Mensch, ist ja spannend! Es scheint der Fehler wurde wieder behoben. Bei mir Excel 2019 (Office 365 Abo) Version funktioniert das jetzt wieder (also auch mit Argument 63)! Ich habe jetzt natürlich nicht jede Variante der Argumente durchprobiert, aber ich bin bis jetzt noch über kein solches Problem gestolpert."
Update 2020:
Unter Excel 2016 funktioniert die Formel wieder :-))) Offensichtlich ist hier durch ein Update die Makroformel wieder funktionierend.
An dieser Stelle muss ich übrigens meiner Frau zustimmen, die meinte als ich ihr erzählte, dass mich ein Kollege auf einen anderen Blogartikel hingewiesen hatte, den ich auch schon kommentiert hatte "Dein Internet ist ganz schön klein.. "
Berichtswesen nicht nur mit Excel
Beruflich ist ein Schwerpunkt meiner Arbeit das Controlling und Berichtswesen. Neben Excel arbeite ich hier auch besonders gerne mit SAP. Schon bei der Konzeption eines umfangreichen Berichtes und etwaiger Dashboards ist es hier hilfreich sich im Vorfeld passende Gedanken zu machen. Hier habe ich im Buch »Berichtswesen im SAP®-Controlling« (Buchvorstellung, für 19,95 EUR bestellen) einige Punkte festgehalten.
Im Blog finden Sie aber auch regelmäßig Praxisbeispiele rund um die Themen SAP, Berichtswesen und Controlling. Viele Beispiele sind dabei mit Bezug zur Hochschule aber können, wie der Artikel "Statistische Kennzahlen für Verrechnung in SAP - Umlage und Verteilung nicht nur im Hochschulcontrolling und Hochschulberichtswesen" auch für andere Branchen genutzt und als Grundlage zum Aufbau eines eigenen Berichtswesens genutzt werden.
Ich würde mich freuen, wenn meine Bücher (Publikationen) aber auch Schulungen (Workshop & Seminare) auch für Sie interessant wären. Weitere Partnerangebote, wie auch eine Excel Schulung zu Pivot finden Sie ebenfalls unter der Rubrik Onlineshop.
ein Angebot von Espresso Tutorials

unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Excel rechnet mit Farben oder ZÄHLENWENN bzw. SUMMEWENN anhand der Hintergrundfarbe der Zelle dank ZELLE.ZUORDNEN ohne VBA« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 17.9.2016, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=754 (Abgerufen am 13.2.2025)
Ein Kommentar - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Bluesky 🌎 LinkedIn