Vor kurzem haben wir die Verwendung der Funktion FILTER.XML zum Importieren von XML-Daten aus dem Internet besprochen – die Hauptaufgabe, für die diese Funktion eigentlich gedacht ist. Im Laufe der Zeit ist jedoch eine weitere unerwartete und schöne Verwendung dieser Funktion aufgetaucht – zum schnellen Teilen von klebrigem Text in Zellen.
Nehmen wir an, wir haben eine Datenspalte wie diese:
Der Einfachheit halber möchte ich es natürlich in separate Spalten unterteilen: Firmenname, Stadt, Straße, Haus. Sie können dies auf verschiedene Arten tun:
- Verwenden Sie die Text nach Spalten aus der Registerkarte Datum (Daten — Text in Spalten) und gehe drei Schritte Text-Parser. Aber wenn sich die Daten morgen ändern, müssen Sie den ganzen Vorgang noch einmal wiederholen.
- Laden Sie diese Daten in Power Query und teilen Sie sie dort auf, und laden Sie sie dann wieder in das Blatt hoch und aktualisieren Sie dann die Abfrage, wenn sich die Daten ändern (was bereits einfacher ist).
- Wenn Sie spontan aktualisieren müssen, können Sie einige sehr komplexe Formeln schreiben, um Kommas zu finden und den Text zwischen ihnen zu extrahieren.
Und Sie können es eleganter machen und die Funktion FILTER.XML verwenden, aber was hat das damit zu tun?
Die Funktion FILTER.XML erhält als Anfangsargument einen XML-Code – Text, der mit speziellen Tags und Attributen gekennzeichnet ist, und zerlegt ihn dann in seine Komponenten, wobei die benötigten Datenfragmente extrahiert werden. Der XML-Code sieht normalerweise etwa so aus:
In XML muss jedes Datenelement in Tags eingeschlossen werden. Ein Tag ist ein Text (im obigen Beispiel Manager, Name, Gewinn), der in spitze Klammern eingeschlossen ist. Tags kommen immer paarweise vor – öffnend und schließend (mit einem Schrägstrich am Anfang).
Die Funktion FILTER.XML kann problemlos den Inhalt aller benötigten Tags extrahieren, beispielsweise die Namen aller Manager, und (am wichtigsten) alle auf einmal in einer Liste anzeigen. Unsere Aufgabe besteht also darin, dem Quelltext Tags hinzuzufügen und ihn in XML-Code umzuwandeln, der für die anschließende Analyse durch die Funktion FILTER.XML geeignet ist.
Wenn wir die erste Adresse aus unserer Liste als Beispiel nehmen, müssen wir sie in diese Konstruktion umwandeln:
Ich habe das globale Öffnen und Schließen aller Text-Tags aufgerufen t, und die Tags, die jedes Element umrahmen, sind s., aber Sie können auch beliebige andere Bezeichnungen verwenden – das spielt keine Rolle.
Wenn wir aus diesem Code Einzüge und Zeilenumbrüche entfernen – übrigens komplett optional und nur der Übersichtlichkeit halber hinzugefügt – dann wird das alles zu einer Zeile:
Und sie lässt sich bereits relativ einfach aus der Quelladresse ermitteln, indem man darin Kommas durch ein paar Tags ersetzt mit der Funktion ERSATZ (ERSATZ) und Kleben mit dem Symbol & am Anfang und Ende der öffnenden und schließenden Tags:
Um den resultierenden Bereich horizontal zu erweitern, verwenden wir die Standardfunktion TRANSP (TRANSPOSIEREN), unsere Formel darin verpacken:
Ein wichtiges Merkmal dieses ganzen Designs ist, dass in der neuen Version von Office 2021 und Office 365 mit Unterstützung für dynamische Arrays keine speziellen Gesten für die Eingabe erforderlich sind – einfach eingeben und klicken Enter – Die Formel selbst belegt die Anzahl der Zellen, die sie benötigt, und alles funktioniert mit einem Paukenschlag. In früheren Versionen, in denen es noch keine dynamischen Arrays gab, müssen Sie zunächst eine ausreichende Anzahl leerer Zellen auswählen, bevor Sie die Formel eingeben (das können Sie mit einem Rand), und nach dem Erstellen der Formel die Tastenkombination drücken Ctrl+Shift+Enterals Matrixformel einzugeben.
Ein ähnlicher Trick kann verwendet werden, um zusammengeklebten Text in einer Zelle durch einen Zeilenumbruch zu trennen:
Der einzige Unterschied zum vorherigen Beispiel besteht darin, dass wir hier anstelle eines Kommas das unsichtbare Alt + Enter-Zeilenumbruchzeichen ersetzen, das in der Formel mithilfe der CHAR-Funktion mit dem Code 10 angegeben werden kann.
- Die Feinheiten beim Arbeiten mit Zeilenumbrüchen (Alt + Enter) in Excel
- Teilen Sie Text in Excel nach Spalten
- Ersetzen von Text durch ERSATZ