Ich habe eine Spalte voller Daten und es gibt kein bestimmtes Muster, dass ich nach 10 oder 20 Zeilen eine neue Zeile wiederholen sollte. Daher können diese Index- und Offset-Lösungen nur funktionieren, wenn die Formel den Anfang und das Ende jedes Abschnitts definieren kann.
Um es richtig zu verstehen, ich habe eine Spalte wie:
A
B
C
D
– (dies unterbricht einen Abschnitt)
A
C
E
F
G
– (dies unterbricht eine weitere Reihe)
B
C
F
G
– (noch einmal unterbrechen)
Für mich wäre es also in Ordnung, wenn jedes "-"-Zeichen eine neue Zeile in einer Tabelle beginnen würde. Ist das in Excel oder Access mit einer Formel oder VBA möglich?
Danke schön!
Antwort1
Verwenden:
=FILTERXML("<Group><Element>"&SUBSTITUTE(TEXTJOIN("",FALSE,A1:A15),"-","</Element><Element>")&"</Element></Group>","/Group/Element")
Dies wird verwendet TEXTJOIN(), um alle Werte in der Spalte (A1:A15 in der Formel) zu verbinden. Es wird angenommen, dass kein "-" als Endzelle vorhanden ist. Dies kann erreicht werden, indem einfach die Zelle über einem solchen letzten Eintrag als Ende des Bereichs ausgewählt wird, wenn immer abschließende "-"-Zeichen erscheinen.) Es werden keine Zellen übersprungen, auch wenn sie leer sind. Wenn leere Zellen jedoch übersprungen werden sollen, verwenden Sie dies TRUEfür den zweiten Parameter in der Funktion.
Anschließend wandeln Sie das TEXTJOIN()Array mit einem Element, das durch die Zeichen "-" in Teile unterteilt wurde, in dieselbe Zeichenfolge um, jedoch mit "" anstelle der Zeichen "-". Dadurch wird die Zeichenfolge teilweise für die Verwendung in der FILTERXML()angezeigten Funktion bereit gemacht. Beachten Sie, dass die Ersetzungen alle intern in der Zeichenfolge erfolgen oder mit anderen Worten nur darin und nicht an einem der Enden erscheinen. Wenn Sie mit HTML vertraut sind, wissen Sie, dass die Form "</tag name>" ein Tag schließt und "" ein Tag öffnet.
Sie fügen dann das öffnende Tag am Anfang hinzu (stellen es vor das SUBSTITUTE()Vorkommen) und das schließende Tag wird an das Ende angehängt. Sie haben also ein öffnendes und ein schließendes Tagpaar um jede der gewünschten Gruppen, die die Zeichen „-“ früher voneinander trennten.
Schließlich müssen alle einzeln getaggten Elemente (sehen Sie, woher ich den Tag-Namen "" habe?) in einer einzigen Gruppe zusammengefasst werden (sehen Sie, woher das Tag stammt?).
Jetzt haben Sie eine HTML-Zeichenfolge, die FILTERXML()Sie verstehen und in Stücke zerlegen können. Diese Zeichenfolge wird als erster Parameter verwendet und als zweiter Parameter teilen Sie ihm über den sogenannten „Pfad“ mit, wie die Tags zusammenhängen... das ist das „/Group/Element“-Bit am Ende.
Schwer zu verstehen, warum Excel das einrichten muss, wenn eine solche Zeichenfolge ganz offensichtlich auf eine bestimmte Weise organisiert ist, oder? Aber es war beabsichtigt, tatsächliches Live-HTML von Live-Websites zu sezieren, nicht „gewöhnliche“ Zeichenfolgen auseinanderzunehmen, und es hätte viele andere Tags in einer solchen HTML-Zeichenfolge geben können, und dann wäre es nicht so offensichtlich! Interessanterweise deutet es jedoch darauf hin, dass Sie, wenn Sie eine Zeichenfolge mit mehreren Tags erstellen, Excel verschiedene Sets aus derselben Zeichenfolge mit unterschiedlichen Pfadwerten erstellen lassen könnten.
Wie auch immer, FILTERXML()es gibt seine Ergebnisse gerne in einem vertikalen Layout, Zeilen in einer Spalte. Ich glaube, Sie würden dies bevorzugen[ja, ich sehe in den Kommentaren, dass Sie explizit nach Zeilen fragen], aber wenn nicht, wenn Sie ein horizontales Layout mit Spalten in einer Zeile wünschen, packen Sie das Ganze einfach in eine TRANSPOSE()Funktion.
Eine weitere interessante Verwendung FILTERXML()besteht darin, dass Sie genau angeben können, welches Element einer solchen Gruppe Sie möchten, und zwar per Nummer (1, 2, 3, … usw.) UND das letzte per Nummer (sofern bekannt) ODER per „letztem“, was praktisch sein kann.
Es kann eine größere Bandbreite an Dingen mit Zeichenfolgen tun, wenn Sie über eine funktionierende Möglichkeit verfügen, die internen Tag-Paare () an den richtigen Stellen einzufügen.