Andreas Unkelbach
Logo Andreas Unkelbach Blog

Andreas Unkelbach Blog

ISSN 2701-6242

Artikel über Controlling und Berichtswesen mit SAP, insbesondere im Bereich des Hochschulcontrolling, aber auch zu anderen oft it-nahen Themen.


Werbung


Samstag, 17. September 2016
11:18 Uhr

Excel rechnet mit Farben oder ZÄHLENWENN bzw. SUMMEWENN anhand der Hintergrundfarbe der Zelle dank ZELLE.ZUORDNEN ohne VBA

Nachdem Einladungen per Serienmail versandt worden sind (siehe Artikel "Serienmails über Serienbrieffunktion in Winword per Outlook, Thunderbird oder anderen Mailprogramm versenden") kommt es an der Erfassung von Rückmeldungen.

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.
In beiden Fällen sind die Formatvorlagen Gut (grüne Hintergrundfarbe), Neutral (gelbe Hintergrundfarbe) oder auch Schlecht (rot/rosa Hintergrund) genutzt worden um einen Zustand zu definieren. Nun liegen zwar wunderschöne Farben in der Tabelle vor, aber eventuell sollen nun ja auch die Teilnehmenden gezählt werden, oder aber auch die gezahlten oder noch offenen Beträge zusammengefasst werden.

Nehmen wir als Beispiel einmal folgende Ausgangstabellen, wie in der Abbildung zu sehen ist.

Tabelle mit R�ckmeldungen zur Teilnahme oder Rechnungen die farblich markiert sind

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.

Namensmanager mit EXCEL4-Makrofunktion zur Bestimmung der Farbwerte der Hintergrundfarbe

Zur besseren Lesbarkeit noch einmal beide definierte Namensfunktionen:
 
Name bezieht sich auf
Namensmanager definierte Funktionen
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.

Formel farbe_L1 zur Berechnung der Hintergrundfarbe

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.

Hintergrundfarbe als Zahl dargestellt

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 Formel

=ZÄHLENWENN($D$9:$D$16;Farbe_L1)

in den Zellen C3 bis C6 eingetragen, so dass hier die übereinstimmende Hintergrundfarben gezählt werden.

Z�HLEWENN Hintergrundfarbe �bereinstimmt

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:G6

=SUMMEWENN($H$9:$H$16;Farbe_L1;$G$9:$G$16)

wie auch in der Abbildung zu sehen ist.

SUMMEWENN  Hintergrundfarbe �bereinstimmt

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;

=SUMMEWENN($H$9:$H$16;Farbe_Zelle;$G$9:$G$16)

und das gewünschte Ergebnis sieht dann wie folgt aus, wobei ich hier die Hilfsspalten D und H entsprechend ausgeblendet (bzw. Gruppiert) habe.

Ergebnis mit der Hintergrundfarbe einer Tabellenzelle rechnen
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:


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…?"

Darauf hatte ich damals auf folgenden Umstand hingewiesen:

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
 

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.

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:

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."

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....

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.



Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
SAP Weiterbildung
ein Angebot von Espresso Tutorials
SAP Weiterbildung - so wirksam wie eine gute Tasse Espresso

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 19.3.2024)

Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Kommentare

Anonym am 24.3.2020 um 23:10 Uhr
Hat geklappt!


Auch kommentieren?


Beim Versenden eines Kommentars wird mir ihre IP mitgeteilt. Diese wird jedoch nicht dauerhaft gespeichert; die angegebene E-Mail wird nicht veröffentlicht: beim Versenden als "Normaler Kommentar" ist die Angabe eines Namen erforderlich, gerne kann hier auch ein Pseudonyme oder anonyme Angaben gemacht werden (siehe auch Kommentare und Beiträge in der Datenschutzerklärung).

Eine Rückmeldung ist entweder per Schnellkommentar oder (weiter unten) als normalen Kommentar möglich. Eine persönliche Rückmeldung (gerne auch Fragen zum Thema) würde mich sehr freuen.

Schnellkommentar (Kurzes Feedback, ausführliche Kommentare bitte unten als normaler Kommentar)





Ich nutze zum Schutz vor Spam-Kommentaren (reine Werbeeinträge) eine Wortliste, so dass diese Kommentare nicht veröffentlicht werden. Sollte ihr Kommentar nicht direkt veröffentlicht werden, kann dieses an einen entsprechenden Filter liegen.

Im Zweifel besteht auch immer die Möglichkeit eine Mail zu schreiben oder die sozialen Medien zu nutzen. Meine Kontaktdaten finden Sie auf »Über mich« oder unter »Kontakt«. Ansonsten antworte ich tatsächlich sehr gerne auf Kommentare und freue mich auf einen spannenden Austausch.












* Amazon Partnerlink/Affiliatelinks/Werbelinks
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
Weitere Partnerschaften sind unter Onlineshop und unter Finanzierung und Transparenz aufgeführt. Hinauf






Logo Andreas-Unkelbach.de
Andreas Unkelbach Blog
ISSN 2701-6242

© 2004 - 2024 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de

UStID-Nr: DE348450326 - Kleinunternehmer im Sinne von § 19 Abs. 1 UStG

Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter XING

Linkedin Mastodon Bluesky

Amazon Autorenwelt Librarything

Buchempfehlung
SAP S/4HANA Migration Cockpit - Datenmigration mit LTMC und LTMOM

29,95 € Amazon* Autorenwelt

Espresso Tutorials

unkelbach.link/et.reportpainter/

unkelbach.link/et.migrationscockpit/

Privates

Kaffeekasse 📖 Wunschliste