Summieren nach Farbe mit ZELLEN.ZUORDNEN (ohne VBA)

Wolltet Ihr auch schon einmal Zellen Summieren basierend auf deren Hintergrundfarbe? Dann habt Ihr sicherlich bemerkt dass es hierfür in Excel keine normale Lösung gibt.

Dachte ich lange Zeit auch… aber es gibt Sie!

Als erstes werde ich wohl selber Farbe bekennen müssen. Im Titel schreibe ich dass es in diesem Beispiel kein VBA Code geben wird. Streng genommen stimmt das auch, denn wir werden keinen Code in einem Modul eingeben, und trotzdem ist es so dass wir Makros (sprich Code) verwenden werden.

Aber keine Aufregung wenn Euch Makros nicht so behagen… der Code in diesem Beispiel ist sehr einfach und Ihr braucht dafür nicht mal den VBE (Visual Basic Editor)!

Summieren nach Farbe in zwei Schritten

Schritt 1: Die Farbe ausfindig machen

Das Problem, mit dem wir konfrontiert sind bei dieser Aufgabe, ist dass wir scheinbar keine Methode haben um die Hintergrundfarbe einer Zelle ausfindig zu machen.

Excel hat zwar die Funktion ZELLE() mit der wir “Farbe” auswerten können, jedoch liefert uns dies lediglich die Information ob die Zelle für negative Werte farbig formatiert ist (wenn ja, liefert es eine 1, sonst 0). Also nicht das nach dem wir suchen.

Es gibt aber eine verwandte Funktion von ZELLE! Man könnte es auch den Vorahnen dieser Funktion nennen…

Excel4-Makrofunktion ZELLE.ZUORDNEN()

ZELLE.ZUORDNEN ist eine Excel4 Makrofunktion, welche ursprünglich in einer vorsintflutlichen Version von Excel existierte (eben Excel4 und vorher… also, 1992 als wir alle noch jung und naive waren), die dann von Microsoft aus den Formeln die man im Ribbon findet entfernt wurde, die aber in den tiefen von Excel weiterlebt!

Diese Funktion kann nicht direkt in einer Zelle auf einem Arbeitsblatt eingegeben werden, sondern muss über die Erstellung eines benannten Bereiches im Namens-Manager eingegeben werden.

Beispiel

Nach-Farbe-Summieren.xlsmDownload

In unserem Beispiel, platzieren wir den Kursor in die Zelle C2, und erstellen im Namensmanager eine neue Benannte-Formel (Benannter-Bereich) die wir HintergrundFarbe nenne und mit folgender Formel hinterlegen:

Syntax von ZELLE.ZUORDNEN()

=ZELLE.ZUORDNEN(Typ, Bezug)
=ZELLE.ZUORDNEN(38;Tabelle1!B2)

Typ: Wir geben hier die Nummer 38 ein. Wieso 38? Weil dies die Zahl ist, die angibt, welche Information die Funktion zurückliefern soll. In unserem Fall liefert es die Farbe des Hintergrundes als Zahl von 1 bis 56; 0, wenn die Farbe auf Automatisch eingestellt ist.

Natürlich gibt es noch weitere Informationen welche diese Formel zurückliefern kann. Eine Liste dieser, habe ich unten in diesem Beitrag angefügt.

Bezug: Wichtig bei der Eingabe ist dass wir die Zellenreferenz auf relativ stellen. Standardmäßig werden Zellen im Namens-Manager gleich auf eine Absolutereferenz gestellt… also nicht vergessen umzustellen!

Schritt 2: Benannte-Formel einsetzen und danach aufsummieren

Als nächstes setzen wir die Benannte-Formel in die Zelle rechts von derjenigen Zelle bei der wir den Hintergrund auswerten möchten.

Wenn wir die Formel nach unten ziehen, sehen wir dass wir die Farben nun als Werte ausgewertet bekommen.

Mit Hilfe einer Farblegende, können wir nun mit einer Standard-Formel, z.B. SUMMEWENN(), diese aufsummieren.

Hier noch Beispielhaft die Formel in Zelle F2:

=SUMMEWENNS($B$2:$B$12;$C$2:$C$12;E2)

Das aktualisierung Problem (auf was Ihr achten müsst):

Weil diese Formel im Hintergrund tatsächlich ein Makro ausführt, aktualisiert Sie sich nicht dynamisch, wenn eine Hintergrundfarbe geändert wird. Auch dann nicht wenn man auf aktualisiert mit “Neu Berechnen” (F9) klickt!

Aus dem selben Grund muss die Datei im .xlsm Format gespeichert werden!

Die Lösung zum aktualisierung Problem:

Ändert die Farbe nur mit dem “Format Übertragen” Pinsel… dies scheint die Neuberechnung der Excel4-Makros auszulösen. Die Formeln berechnen sich auch durch das schliessen und neue öffnen der Datei!!!

Hintergrundfarben welche durch Bedingte Formatierungen gesetzt werden, erkennt die ZELLE.ZUORDNEN Formel nicht!

ZELLE.ZUORDNEN Arguments Typ Liste

Diese Liste wurde ursprünglich im Mr.Excel Forum publiziert und via maschineller Übersetzung auf Deutsch übersetzt. Ich habe die Auflistung hier nochmals aufgeführt, da diese nahezu unauffindbar ist auf dem Netz.

(Diese Liste könnt Ihr übrigens auch im Beispiel Download File finden!)

1 Absolute Referenz der linken oberen Zelle in Referenz, als Text im aktuellen Arbeitsbereichsreferenzstil.
2 Zeilennummer der oberen Zelle in der Referenz.
3 Spaltennummer der Zelle ganz links in der Referenz.
4 Gleich wie TYP(Verweis).
5 Inhalt des Verweises.
6 Formel in Referenz, als Text, entweder im Stil A1 oder R1C1, je nach Einstellung des Arbeitsbereichs.
7 Zahlenformat der Zelle als Text (z.B. “m/d/yy” oder “Allgemein”).
8 Zahl, die die horizontale Ausrichtung der Zelle angibt:
1 = Allgemein
2 = Links
3 = Mitte
4 = Recht
5 = Füllen
6 = Begründen
7 = Mitte über Zellen
9 Zahl, die den der Zelle zugewiesenen linken Rand angibt:
0 = kein Rand
1 = Dünne Linie
2 = Mittlere Linie
3 = Gestrichelte Linie
4 = Gestrichelte Linie
5 = Dicke Linie
6 = Doppelte Linie
7 = Haarlinie
10 Zahl, die den der Zelle zugewiesenen rechten Randstil angibt.
Siehe type_num 9 für Beschreibungen der zurückgegebenen Zahlen.
11 Zahl, die den der Zelle zugewiesenen Stil für den oberen Rand angibt.
Siehe type_num 9 für Beschreibungen der zurückgegebenen Zahlen.
12 Zahl, die den der Zelle zugewiesenen unteren Randstil angibt.
Siehe type_num 9 für Beschreibungen der zurückgegebenen Zahlen.
13 Zahl von 0 bis 18, die das Muster der ausgewählten Zelle angibt
wie auf der Registerkarte Muster im Dialogfeld Zellen formatieren angezeigt.
die angezeigt wird, wenn Sie den Befehl Zellen aus dem Menü Format wählen.
Wenn kein Muster ausgewählt ist, wird 0 zurückgegeben.
14 Wenn die Zelle gesperrt ist, wird WAHR zurückgegeben, andernfalls FALSCH.
15 Wenn die Formel der Zelle ausgeblendet ist, wird WAHR zurückgegeben, andernfalls FALSCH.
16 Ein horizontales Array mit zwei Elementen, das die Breite der aktiven Zelle und einen logischen Wert enthält
Angabe, ob die Breite der Zelle so eingestellt ist, dass sie sich bei Änderung der Standardbreite ändert (WAHR)
oder ist eine benutzerdefinierte Breite (FALSE).
17 Zeilenhöhe der Zelle, in Punkten.
18 Name der Schriftart, als Text.
19 Größe der Schriftart, in Punkten.
20 Wenn alle Zeichen in der Zelle oder nur das erste Zeichen fett gedruckt sind, wird WAHR zurückgegeben, andernfalls FALSCH.
21 Wenn alle Zeichen in der Zelle oder nur das erste Zeichen kursiv sind, wird WAHR zurückgegeben, andernfalls FALSCH.
22 Wenn alle Zeichen in der Zelle oder nur das erste Zeichen unterstrichen sind, wird WAHR zurückgegeben, andernfalls FALSCH.
23 Wenn alle Zeichen in der Zelle oder nur das erste Zeichen durchgestrichen sind, wird WAHR zurückgegeben, andernfalls FALSCH.
24 Schriftfarbe des ersten Zeichens in der Zelle, als Zahl im Bereich 1 bis 56. Wenn die Schriftfarbe automatisch ist, wird 0 zurückgegeben.
25 Wenn alle Zeichen in der Zelle oder nur das erste Zeichen durchgestrichen sind, wird WAHR zurückgegeben, andernfalls FALSCH.
Das Format der Umrissschrift wird von Microsoft Excel für Windows nicht unterstützt.
26 Wenn alle Zeichen in der Zelle oder nur das erste Zeichen verdunkelt sind, wird WAHR zurückgegeben, andernfalls FALSCH.
Das Format der Schattenschrift wird von Microsoft Excel für Windows nicht unterstützt.
27 Zahl, die angibt, ob an der Zelle ein manueller Seitenumbruch erfolgt:
0 = kein Umbruch
1 = Reihe
2 = Spalte
3 = Sowohl Zeile als auch Spalte
28 Zeilenebene (Umriss).
29 Spaltenebene (Umriss).
30 Wenn die Zeile mit der aktiven Zelle eine Summenzeile ist, wird WAHR zurückgegeben, andernfalls FALSCH.
31 Wenn die Spalte, die die aktive Zelle enthält, eine Summenspalte ist, wird WAHR zurückgegeben; andernfalls FALSCH.
32 Name der Arbeitsmappe und des Blattes, das die Zelle enthält Wenn das Fenster nur ein einzelnes Blatt enthält, das die gleiche Zelle enthält, gibt
name als Arbeitsmappe ohne ihre Erweiterung, gibt nur den Namen des Buches zurück, in der Form BOOK1.XLS.
Andernfalls wird der Name des Blattes in der Form “[Buch1]Blatt1” zurückgegeben.
33 Wenn die Zelle für den Zeilenumbruch formatiert ist, wird TRUE zurückgegeben; andernfalls wird FALSE zurückgegeben.
34 Farbe am linken Rand als Zahl im Bereich 1 bis 56. Wenn die Farbe automatisch ist, wird 0 zurückgegeben.
35 Farbe am rechten Rand als Zahl im Bereich 1 bis 56. Wenn die Farbe automatisch ist, wird 0 zurückgegeben.
36 Farbe des oberen Randes als Zahl im Bereich von 1 bis 56. Wenn die Farbe automatisch ist, wird 0 zurückgegeben.
37 Farbe am unteren Rand als Zahl im Bereich von 1 bis 56. Wenn die Farbe automatisch ist, wird 0 zurückgegeben.
38 Vordergrundfarbe als Zahl im Bereich 1 bis 56 schattieren. Wenn die Farbe automatisch ist, wird 0 zurückgegeben.
39 Schattieren Sie die Hintergrundfarbe als Zahl im Bereich 1 bis 56. Wenn die Farbe automatisch ist, wird 0 zurückgegeben.
40 Stil der Zelle, als Text.
41 Gibt die Formel in der aktiven Zelle zurück, ohne sie zu übersetzen (nützlich für internationale Makroblätter).
42 Der horizontale Abstand, gemessen in Punkten, vom linken Rand des aktiven Fensters bis zum linken Rand der Zelle.
Kann eine negative Zahl sein, wenn das Fenster über die Zelle hinaus gescrollt wird.
43 Der vertikale Abstand, gemessen in Punkten, von der Oberkante des aktiven Fensters bis zur Oberkante der Zelle.
Kann eine negative Zahl sein, wenn das Fenster über die Zelle hinaus gescrollt wird.
44 Der horizontale Abstand, gemessen in Punkten, vom linken Rand des aktiven Fensters bis zum rechten Rand der Zelle.
Kann eine negative Zahl sein, wenn das Fenster über die Zelle hinaus gescrollt wird.
45 Der vertikale Abstand, gemessen in Punkten, von der Oberkante des aktiven Fensters bis zur Unterkante der Zelle.
Kann eine negative Zahl sein, wenn das Fenster über die Zelle hinaus gescrollt wird.
46 Wenn die Zelle eine Textnotiz enthält, wird WAHR zurückgegeben, andernfalls FALSCH.
47 Wenn die Zelle eine Tonnote enthält, wird WAHR zurückgegeben, andernfalls FALSCH.
48 Wenn die Zelle eine Formel enthält, wird WAHR zurückgegeben; wenn die Zelle eine Konstante enthält, wird FALSCH zurückgegeben.
49 Wenn die Zelle Teil eines Arrays ist, wird WAHR zurückgegeben, andernfalls FALSCH.
50 Zahl, die die vertikale Ausrichtung der Zelle angibt:
1 = Oben
2 = Mitte
3 = Unten
4 = gerechtfertigt
51 Zahl, die die vertikale Ausrichtung der Zelle angibt:
0 = Horizontal
1 = Vertikal
2 = Aufwärts
3 = Abwärts
52 Das Zeichen für das Zellpräfix (oder die Textausrichtung) oder leerer Text (“””), wenn die Zelle kein Zeichen enthält.
53 Der Inhalt der Zelle, wie er gegenwärtig als Text angezeigt wird, einschließlich aller zusätzlichen Zahlen oder Symbole, die sich aus der Formatierung der Zelle ergeben.
54 Gibt den Namen der PivotTable-Ansicht zurück, die die aktive Zelle enthält.
55 Gibt die Position einer Zelle innerhalb der PivotTable-Ansicht zurück.
56 Gibt den Namen des Feldes zurück, das den aktiven Zellbezug enthält, wenn es sich innerhalb einer PivotTable-Ansicht befindet.
57 Gibt TRUE zurück, wenn alle Zeichen in der Zelle oder nur das erste Zeichen mit einer hochgestellten Schriftart formatiert sind; andernfalls wird FALSE zurückgegeben.
58 Gibt die Schriftart als Text aller Zeichen in der Zelle oder nur des ersten Zeichens zurück, wie es im Register Schriftart des Dialogfelds “Zellen formatieren” angezeigt wird: z. B. “Fett kursiv”.
59 Gibt die Zahl für den Unterstreichungsstil zurück:
1 = keine
2 = einzeln
3 = doppelt
4 = einzelne Buchhaltung
5 = doppelte Buchhaltung
60 Gibt TRUE zurück, wenn alle Zeichen in der Zelle oder nur das erste Zeichen mit einer tiefgestellten Schriftart formatiert sind; andernfalls wird FALSE zurückgegeben.
61 Gibt den Namen des PivotTable-Elements für die aktive Zelle als Text zurück.
62 Gibt den Namen der Arbeitsmappe und des aktuellen Blattes in der Form “[Buch1]Blatt1” zurück.
63 Gibt die Füllfarbe (Hintergrundfarbe) der Zelle zurück.
64 Gibt die Musterfarbe (Vordergrundfarbe) der Zelle zurück.
65 Gibt TRUE zurück, wenn die Option Einrückungsausrichtung hinzufügen aktiviert ist (nur fernöstliche Versionen von Microsoft Excel); andernfalls wird FALSE zurückgegeben.
66 Gibt den Buchnamen der Arbeitsmappe zurück, die die Zelle in der Form BOOK1.XLS enthält.

January 23, 2020
 |  7 Comments
 |  Category: Tipps, VBA
Tags: , , ,