Kürzlich kam ein Freund von mir mit der Bitte auf mich zu, bei der Generierung aller möglichen Phrasen zu helfen, die aus einer Menge gegebener Wörter bestehen. Probleme dieser Art können beim Zusammenstellen von Listen mit Schlüsselwörtern und Phrasen für Online-Werbung und SEO-Promotion auftreten, wenn Sie alle möglichen Permutationen von Wörtern in einer Suchanfrage durchgehen müssen:
In der Mathematik heißt diese Operation kartesisches Produkt. Die offizielle Definition lautet wie folgt: Das kartesische Produkt der Mengen A und B ist die Menge aller Paare, deren erste Komponente zur Menge A und deren zweite Komponente zur Menge B gehört. Außerdem können die Elemente von Mengen beide sein Zahlen und Text.
Übersetzt in die menschliche Sprache bedeutet dies: Wenn wir in Menge A zum Beispiel die Wörter „weiß“ und „rot“ haben und in Menge B „BMW“ und „Mercedes“, dann nach dem kartesischen Produkt dieser beiden Mengen wir get auf die Ausgabe ist die Menge aller möglichen Phrasenvarianten, die sich aus den Wörtern beider Listen zusammensetzt:
- weißer bmw
- roter bmw
- weißer Mercedes
- roter mercedes
… dh genau das, was wir brauchen. Schauen wir uns ein paar Möglichkeiten an, um diese Aufgabe in Excel zu lösen.
Methode 1. Formeln
Beginnen wir mit Formeln. Nehmen wir an, dass wir als Anfangsdaten drei Listen mit Originalwörtern in den Spalten A, B bzw. C haben und die Anzahl der Elemente in jeder Liste variieren kann:
Lassen Sie uns zuerst drei Spalten mit Indizes erstellen, dh Ordnungszahlen von Wörtern aus jeder Liste in allen möglichen Kombinationen. Die erste Reihe von Einheiten (E2:G2) wird manuell eingegeben, und für den Rest verwenden wir die folgende Formel:
Die Logik dabei ist einfach: wenn der Index in der übergeordneten vorherigen Zelle bereits das Ende der Liste erreicht hat, also gleich der Anzahl der Elemente in der Liste ist, die von der Funktion berechnet wurde ANZAHL (COUNTA), dann starten wir die Nummerierung neu. Ansonsten erhöhen wir den Index um 1. Achten Sie besonders auf die geschickte Fixierung der Bereiche mit Dollarzeichen ($), damit Sie die Formel nach unten und rechts kopieren können.
Nachdem wir nun die Ordnungszahlen der Wörter haben, die wir aus jeder Liste benötigen, können wir die Wörter selbst mit der Funktion extrahieren INDEX (INDEX) in drei getrennte Spalten:
Wenn Sie diese Funktion in Ihrer Arbeit noch nicht kennengelernt haben, dann rate ich Ihnen dringend, sie zumindest diagonal zu studieren – sie hilft in vielen Situationen und ist nicht weniger (und sogar mehr!) VPR (SVERWEIS).
Nun, danach müssen die resultierenden Fragmente nur noch Zeile für Zeile mit dem Verkettungssymbol (&) zusammengefügt werden:
… oder (wenn Sie die neueste Excel-Version haben) mit der praktischen Funktion KOMBINIEREN (TEXTVERBINDUNG), die den gesamten Inhalt der angegebenen Zellen durch ein bestimmtes Trennzeichen (Leerzeichen) kleben kann:
Methode 2. Durch Power Query
Power Query ist ein leistungsstarkes Add-In für Microsoft Excel, das zwei Hauptaufgaben erfüllt: 1. Laden von Daten in Excel aus fast jeder externen Quelle und 2. alle Arten von Transformationen geladener Tabellen. Power Query ist bereits in Excel 2016-2019 integriert und wird für Excel 2010-2013 als separates Add-In installiert (Sie können es kostenlos von der offiziellen Microsoft-Website herunterladen). Wenn Sie Power Query noch nicht in Ihrer Arbeit verwendet haben, sollten Sie darüber nachdenken, denn Transformationen wie die oben beschriebenen werden dort einfach und natürlich in nur wenigen Handgriffen durchgeführt.
Lassen Sie uns zunächst die Quelllisten als separate Abfragen in Power Query laden. Führen Sie dazu für jede Tabelle die folgenden Schritte aus:
- Lassen Sie uns Tische mit einem Knopfdruck in „intelligente“ Tische verwandeln Als Tabelle formatieren Tab Startseite (Home — Als Tabelle formatieren) oder Tastenkürzel Ctrl+T. Jede Tabelle erhält automatisch einen Namen Tabelle1,2,3…, die auf der Registerkarte jedoch bei Bedarf geändert werden kann Bauherr (Design).
- Nachdem Sie die aktive Zelle in der Tabelle eingestellt haben, drücken Sie die Taste Vom Tisch (Aus Tabelle) Tab Datum (Datum) oder auf der Registerkarte Power Query (wenn Sie es als separates Add-In für Excel 2010-2013 installiert haben).
- Wählen Sie im sich öffnenden Fenster des Abfrageeditors den Befehl aus Home — Schließen und laden — Schließen und laden in… (Home — Schließen&Laden — Schließen&Laden nach..) und dann die Option Stellen Sie einfach eine Verbindung her (Nur Verbindung erstellen). Dadurch wird die geladene Tabelle im Arbeitsspeicher belassen und der zukünftige Zugriff darauf ermöglicht.
Wenn Sie alles richtig machen, sollte die Ausgabe im rechten Bereich drei Anforderungen im Modus sein Nur Verbindung mit unseren Tabellennamen:
Klicken Sie nun mit der rechten Maustaste auf die erste Abfrage und wählen Sie den Befehl aus Link (Referenz), um eine aktualisierbare Kopie davon zu erstellen, und fügen Sie dann über den Befehl eine zusätzliche Spalte zu den Daten hinzu Hinzufügen einer Spalte ž – Benutzerdefinierte Spalte (Spalte hinzufügen -ž Benutzerdefinierte Spalte). Geben Sie im Formeleingabefenster den Namen der neuen Spalte (z. B. Fragment2) und einen ganz einfachen Ausdruck als Formel ein:
=Tabelle2
… also mit anderen Worten der Name der zweiten Abfrage:
Nach dem Klicken auf OK Wir werden eine neue Spalte sehen, in der sich in jeder Zelle eine verschachtelte Tabelle mit Phrasen aus der zweiten Tabelle befindet (Sie können den Inhalt dieser Tabellen sehen, wenn Sie in den Hintergrund der Zelle neben dem Wort klicken Tisch):
Es bleibt, den gesamten Inhalt dieser verschachtelten Tabellen zu erweitern, indem Sie die Schaltfläche mit den Doppelpfeilen in der Kopfzeile der resultierenden Spalte verwenden und das Kontrollkästchen deaktivieren Verwenden Sie den ursprünglichen Spaltennamen als Präfix (Originalspaltenname als Präfix verwenden):
… und wir erhalten alle möglichen Kombinationen von Elementen aus den ersten beiden Mengen:
Außerdem ist alles ähnlich. Fügen Sie eine weitere berechnete Spalte mit der Formel hinzu:
=Tabelle3
…, und erweitern Sie dann die verschachtelten Tabellen wieder – und jetzt haben wir bereits alle möglichen Optionen zum Permutieren von Wörtern aus den jeweils drei Sätzen:
Es bleibt, alle drei Spalten von links nach rechts auszuwählen und gedrückt zu halten Ctrl, und verketten Sie ihre durch Leerzeichen getrennten Inhalte mit dem Befehl Spalten zusammenführen (Spalten zusammenführen) aus der Registerkarte Transformation (Verwandeln):
Die resultierenden Ergebnisse können mit dem bereits bekannten Befehl wieder auf das Blatt entladen werden Home — Schließen und laden — Schließen und laden in… (Home — Schließen&Laden — Schließen&Laden nach..):
Wenn sich in Zukunft etwas in unseren Quelltabellen mit Fragmenten ändert, reicht es aus, die generierte Abfrage zu aktualisieren, indem Sie mit der rechten Maustaste auf die resultierende Tabelle klicken und den Befehl auswählen Aktualisieren & Speichern (Aktualisierung) oder durch Drücken der Tastenkombination Ctrl+Andere+F5.
- Was ist Power Query, Power Pivot, Power Map und Power BI und warum brauchen sie einen Excel-Benutzer?
- Erstellen eines Gantt-Diagramms in Power Query
- 5 Möglichkeiten, die INDEX-Funktion zu verwenden