Beim Überführen von Daten in ein neues System stand einer meiner Kunden vor dem Problem, Informationen aus Daten auszulesen zu müssen, die per Formatierung hinterlegt waren.
Alle Kundennamen, bei denen eine Kundendatenabfrage im Jahr 2016 durchgeführt wurde, waren per Fettschrift gekennzeichnet. Diese Information sollte nun in eine neue Spalte Aktualisierung mit dem Eintrag 2016 überführt werden.
Bild 1: Alle mit Fettschrift gekennzeichneten Namen sollen in der Spalte Aktualisierung das Jahr 2016 eingetragen bekommen
Niemand wollte dies für mehrere tausend Datensätze per Hand tun. Abhilfe schaffte die alte Excel4-Makrofunktion ZELLE.ZUORDNEN. So ließ sich das Problem auf die Schnelle lösen.
Wie wird die alte Excel4-Makrofunktion ZELLE.ZUORDNEN angewendet?
ZELLE.ZUORDNEN ist eine Funktion, die nicht direkt im Tabellenblatt genutzt werden kann, sondern nur über die Erstellung einer benannten Formel im Namens-Manager.
Das funktioniert wie folgt (siehe Bild 2 und Bild 3):
Bild 2: Im Namens-Manager wird eine benannte Formel unter Benutzung der Makro-Funktion ZELLE.ZUORDNEN erstellt
- Mit Strg+F3 den Namens-Manager öffnen
- Per Klick auf die Schaltfläche Neu [1] das Dialogfenster zum Anlegen eines neuen Bereichsnamens öffnen
- Im Dialogfenster Neuer Name als Name [2] Aktualisiert eintragen und bei Bezieht sich auf [3] die folgende Formel: =ZELLE.ZUORDNEN(20;INDIREKT(„ZS(-5)“;))
- In der Zelle F3 nun diese Formel erfassen [6], die sich auf den Namen Aktualisiert bezieht: =Wenn(Aktualisiert=WAHR;“2016″;““) und die Formel per AutoAusfüllkästchen [7] nach unten ausfüllen
Bild 3: Die Formel in der Zelle bezieht sich auf die benannte Formel und wertet diese mit einer WENN-Funktion aus
Wie funktioniert die benannte Formel mit ZELLE.ZUORDNEN?
Die Syntax der Funktion lautet ZELLE.ZUORDNEN(Typ;Bezug). Dabei steht
- Typ für eine Zahl, die die Art der Information angibt, die zurückgeliefert werden soll
(hier 20 für Fettschrift), - Bezug ist eine Zelle oder ein Zellbereich, auf die sich die Funktion beziehen soll
(hier per Funktion INDIREKT in der ZS-Schreibweise, wobei ZS(-5) auf die Zelle verweist, die sich 5 Zellen weiter links von der aktuellen befindet).
Eine gute Beschreibung der Funktion ZELLE.ZUORDNEN mit allen Typangaben ist im Microsoft Forum unter folgendem Link zu finden:
Beschreibung der Makro-Funktion ZELLE.ZUORDNEN
Wie werden die Daten nun dauerhaft gespeichert?
- Soll die Formel mit ZELLE.ZUORDNEN stets verfügbar sein, muss die Excel-Datei als Makrodatei (.XSLM) gespeichert werden.
- Sollen hingegen die Information nur einmal ausgelesen werden, muss die Formel aus Spalte F durch Kopieren und anschließend Einfügen > Nur Werte aufgehoben werden. Anschließend muss über den Namens-Manager noch der Bereichsname Aktualisiert gelöscht werden.
