Erstellen Sie einen Crossjoin (alle Kombinationen) aus 2 Spalten in Excel – vollständige Anleitung
Wenn Sie in Excel mit zwei Listen arbeiten – etwa Produktnamen und Größen, Regionen und Vertriebsmitarbeiter oder Studierende und Kurse –, möchten Sie unter Umständen jede mögliche Kombination dieser Listen erstellen. Dieser Vorgang wird als Crossjoin (auch kartesisches Produkt genannt) bezeichnet. In diesem Tutorial erhalten Sie Schritt-für-Schritt-Anleitungen, Vor- und Nachteile sowie praxisnahe Beispiele, damit Sie die Methode wählen können, die optimal zu Ihrem Arbeitsablauf passt.
Was ist ein Crossjoin?
Ein Crossjoin – auch kartesisches Produkt genannt – ist eine Operation, die jede mögliche Kombination zwischen zwei Listen erzeugt. In Excel bedeutet das: Jedes Element aus Liste A wird mit jedem Element aus Liste B gepaart, sodass eine vollständige Matrix aller Kombinationen entsteht.
Crossjoins sind in vielen realen Datenszenarien äußerst hilfreich, beispielsweise:
Produktvarianten
Kombinieren Sie Farben × Größen × Stile, um einen umfassenden Produktkatalog zu erstellen.
Verkaufsanalyse
Kombinieren Sie Regionen, Vertriebsmitarbeiter und Quartale für präzise Analysen.
Planung & Terminierung
Generieren Sie alle möglichen Kombinationen aus Mitarbeitern × Schichten oder Studierenden × Kursen.
Tests & Simulationen
Erstellen Sie Szenarienkombinationen für Modellierung, Prognosen oder Validierung.
Beispiel:
Wenn Sie Folgendes haben:
Lautet das Crossjoin-Ergebnis:
Führen Sie einen Crossjoin in Excel durch
Excel bietet gleich mehrere Möglichkeiten, einen Crossjoin zu erstellen – und die beste Methode hängt von Ihrer Excel-Version, Ihrer Vertrautheit mit Formeln oder Tools sowie der Größe Ihres Datensatzes ab. Im Folgenden stellen wir Ihnen vier praktische und effiziente Ansätze vor: von einfachen Formeln bis hin zu fortgeschrittenen Lösungen wie Power Query und VBA. Jede Methode hat ihre eigenen Stärken, sodass Sie genau die wählen können, die optimal zu Ihrem Arbeitsablauf, Ihrer Datenmenge und Ihren Automatisierungsanforderungen passt.
Methode 1: Cross Join mithilfe einer Formel (Excel 365)
1.Bereiten Sie Ihre Daten vor: Platzieren Sie Ihre erste Liste in einer Spalte (z. B. A2:A5 für Produkte) und Ihre zweite Liste in einer anderen Spalte (z. B. C2:C5 für Farben).
2.Geben Sie die folgende Formel in eine leere Zelle ein, in der das Ergebnis erscheinen soll, und drücken Sie die Eingabetaste. Die Formel füllt automatisch alle generierten Kombinationen auf einmal aus. Siehe Screenshot:
=TEXTSPLIT(TEXTJOIN(",", TRUE, A2:A5 & "|" & TRANSPOSE(C2:C5)), "|", ",")
Erläuterung dieser Formel:
- A2:A5 & „|" & TRANSPOSE(C2:C5): Kombiniert jeden Wert aus Spalte A mit jedem Wert aus Spalte C.
- TEXTJOIN(",", WAHR, …): Fügt alle Elemente zu einer langen Textzeichenfolge zusammen – getrennt durch Kommas.
- TEXTSPLIT(…, "|", ","): Teilt den Text erneut in eine Tabelle mit zwei Spalten auf.
Kurz gesagt: Die Formel erstellt A|C-Paare, fügt sie zu einer einzigen Textzeichenfolge zusammen und teilt sie anschließend wieder in eine strukturierte Tabelle mit zwei Spalten auf – wodurch effektiv alle möglichen Kombinationen entstehen.
Tipps:
Neben der zuvor gezeigten Formel können Sie auch die folgende nutzen, um das gleiche Ergebnis zu erzielen.
=LET(a,A2:A5,b,C2:C5,
MAKEARRAY(ROWS(a)*ROWS(b),2,
LAMBDA(r,c,
IF(c=1, INDEX(a, 1+INT((r-1)/ROWS(b))), INDEX(b, 1+MOD(r-1, ROWS(b))))
)
))
Vorteile
- Vollständig dynamisch
- Keine Hilfsspalten erforderlich
- Aktualisiert sich automatisch, wenn sich die Quelllisten ändern
Nachteile
- Erfordert Excel 365
- Formel wirkt für Anfänger komplex
✨ Alle Kombinationen auflisten – Mit einem Klick sämtliche Kombinationen generieren!
Müde davon, komplexe Formeln zu schreiben, um alle möglichen Kombinationen zu erhalten? Mit Kutools für Excel können Sie sofort jede Kombination mehrerer Spalten oder Werte auflisten – ohne Formeln, ohne Power Query, nur mit wenigen Klicks!
Methode 2: Cross Join mithilfe von Power Query
Power Query bietet einen übersichtlichen, codefreien Ansatz, der in allen modernen Excel-Versionen nahtlos funktioniert. Erstellen Sie einen Cross Join ganz einfach per Klick – ohne eine einzige Formel schreiben zu müssen. Folgen Sie einfach den nachstehenden Schritt-für-Schritt-Anweisungen.
Schritt 1: Erstellen Sie Tabellen für jede Spaltendaten
1. Markieren Sie die erste Datenliste, klicken Sie auf Einfügen > Tabelle, wählen Sie im Dialogfeld Tabelle erstellen die Option OK. So erhalten Sie Ihre erste Tabelle.
2. Benennen Sie die Tabelle über die Registerkarte Tabellenentwurf sinnvoll um, damit Sie sie später leichter referenzieren können.
3. Wiederholen Sie dieselben Schritte, um eine weitere Spalte in eine Tabelle umzuwandeln, und vergeben Sie dafür einen Namen.
Schritt 2: Importieren Sie die Tabellen und laden Sie sie als Verbindungen
1.Markieren Sie die erste Tabelle und klicken Sie auf Daten>Aus Tabelle/Bereich, siehe Screenshot:
2. Klicken Sie im geöffneten Fenster Power Query-Editor auf der Registerkarte Schließen & laden auf Schließen & laden, um aus der Registerkarte Start zu schließen.
3. Es wird ein Daten importieren-Dialogfeld angezeigt. Wählen Sie die Option Nur Verbindung erstellen und klicken Sie auf OK.
4. Der Bereich Abfragen & Verbindungen wird rechts angezeigt und zeigt an, dass eine Abfrage ausschließlich als Verbindung hinzugefügt wurde.
5. Wiederholen Sie dieselben Schritte, um auch die zweite Tabelle als reine Verbindungsabfrage zu laden. Anschließend wird sie im Bereich Abfragen & Verbindungen direkt neben der ersten angezeigt.
Schritt 3: Erstellen Sie eine Referenzabfrage und eine benutzerdefinierte Spalte
1. Klicken Sie mit der rechten Maustaste auf die Abfrage, die Sie als erste Tabelle im Crossjoin verwenden möchten, und wählen Sie Referenz.
2.Wechseln Sie im Fenster Power Query-Editorzur Registerkarte Spalte hinzufügenund klicken Sie auf Benutzerdefinierte Spalte, siehe Screenshot:
3. Geben Sie im Dialogfeld Benutzerdefinierte Spalte im Feld Formel für benutzerdefinierte Spalte den Namen der anderen Tabelle ein, die Sie für den Crossjoin verwenden möchten, und klicken Sie anschließend auf die Schaltfläche OK.
Hinweis:
Enthält der Abfragename Leerzeichen (z. B. „Produktfarbe“), müssen Sie ihn in der Syntax #„Abfragename" eingeben, wenn Sie ihn in das Feld „Formel für benutzerdefinierte Spalte“ eintragen. Geben Sie für „Produktfarbe“ beispielsweise #„Produktfarben" ein.
4. Eine neue benutzerdefinierte Spalte wird angezeigt. Klicken Sie auf die Schaltfläche Erweitern, um ihren Inhalt anzuzeigen.
5. Stellen Sie im Erweiterungsbereich sicher, dass der gewünschte Spaltenname aktiviert ist, und klicken Sie anschließend auf OK.
6. Jetzt sehen Sie alle Kombinationen, die aus den beiden Tabellen generiert wurden.
Schritt 4: Laden Sie die Daten in das Arbeitsblatt
Wechseln Sie zur Start-Registerkarte und klicken Sie auf Schließen & Laden > Schließen & Laden. Die Tabelle mit allen Kombinationen wird in ein neues Arbeitsblatt geladen.
→
Vorteile
- Verarbeitet große Datenmengen: Überzeugende Leistung bei tausenden von Zeilen.
- Wiederverwendbar und aktualisierbar: Fügen Sie einfach weitere Daten zum Quellbereich hinzu und aktualisieren Sie die Abfrage – die Ergebnisse werden automatisch angepasst.
Nachteile
- Geringfügig mehr Schritte
- Erfordert grundlegende Power-Query-Kenntnisse
Methode 3: Cross Join mithilfe von PivotTable
Diese Methode ist indirekt, erweist sich aber als überraschend effektiv, wenn Sie alle Kombinationen generieren möchten – ganz ohne Formeln. Sie ist ideal für Anwender, die einen visuelleren, klickbasierten Ansatz bevorzugen oder mit Excel-Versionen arbeiten, die keine dynamischen Arrayformeln unterstützen.
1. Erstellen Sie zwei separate Tabellen für die Datenlisten und benennen Sie diese gemäß den in Schritt 1 der Methode 2 beschriebenen Schritten.
2. Markieren Sie die Tabelle, die Sie als erste Spalte verwenden möchten. Wechseln Sie dann zur Registerkarte Einfügen und klicken Sie auf PivotTable. Siehe Screenshot:
3. Wählen Sie im Dialogfeld PivotTable aus Tabelle oder Bereich die Option Vorhandenes Arbeitsblatt, legen Sie den Speicherort für die PivotTable fest, aktivieren Sie das Kontrollkästchen Diese Daten zum Datenmodell hinzufügen und klicken Sie auf OK.
4. Sobald der Bereich PivotTable-Felder rechts angezeigt wird, aktivieren Sie den Spaltennamen der Tabelle. Dieser wird automatisch dem Bereich Zeilen hinzugefügt.
5. Wechseln Sie anschließend zur Registerkarte Alle im Bereich PivotTable-Felder, wählen Sie die Tabelle aus, die Sie als zweite Spalte Ihres Crossjoins verwenden möchten, und aktivieren Sie deren Spaltennamen, um ihn dem Bereich Zeilen hinzuzufügen. Sie erhalten die PivotTable, wie im folgenden Screenshot dargestellt:
6. Klicken Sie auf eine Zelle der PivotTable, wechseln Sie zur Registerkarte Entwurf, wählen Sie Berichtslayout > In Tabellenform anzeigen, und schon sehen Sie Ihre PivotTable in übersichtlicher Tabellenform. Siehe Screenshot:
7. Klicken Sie weiterhin auf Berichtslayout > Alle Elementbezeichnungen wiederholen, um alle Elemente in jeder Zeile anzuzeigen.
8. Klicken Sie abschließend auf Gesamtsummen > Aus für Zeilen und Spalten.
Die PivotTable zeigt nun eine übersichtliche Liste aller Kombinationen – ohne Zusammenfassungszeilen oder -spalten.
Vorteile
- Keine Formel oder Power Query erforderlich
- Sehr einfach und visuell
- Gut geeignet für schnelle Analysen
Nachteile
- Nicht dynamisch
- Erfordert manuelle Aktionen
- Die Ausgabe ist nicht mit den Originaldaten verknüpft
Methode 4: Cross Join mithilfe einer benutzerdefinierten Funktion (Excel 365 / Excel 2021 und neuer)
Wenn Sie regelmäßig alle möglichen Kombinationen zwischen zwei Listen generieren müssen, bietet eine benutzerdefinierte Funktion (UDF) eine saubere, wiederverwendbare und äußerst flexible Lösung. Mit Excel 365 oder Excel 2021 sorgt die automatische Array-Ausgabe dafür, dass die Ergebnisse vollständig dynamisch sind – eine leistungsstarke und komfortable Methode, um Cross-Join-Vorgänge zu automatisieren.
1. Drücken Sie Alt + F11, um den VBA-Editor zu öffnen.
2. Klicken Sie anschließend auf Einfügen > Modul, und fügen Sie den folgenden Code in das leere Modul ein.
Function CrossJoin(list1 As Range, list2 As Range)
'Updateby Extendoffice
Dim arr1, arr2, result()
Dim i As Long, j As Long, r As Long
arr1 = list1.Value
arr2 = list2.Value
ReDim result(1 To UBound(arr1, 1) * UBound(arr2, 1), 1 To 2)
r = 1
For i = 1 To UBound(arr1, 1)
For j = 1 To UBound(arr2, 1)
result(r, 1) = arr1(i, 1)
result(r, 2) = arr2(j, 1)
r = r + 1
Next j
Next i
CrossJoin = result
End Function
3.Wechseln Sie zum Excel-Arbeitsblatt, geben Sie die folgende Formel ein, drücken Sie Enter, und Excel füllt automatisch alle Kombinationen aus.
=CrossJoin(A2:A5, C2:C5)
Fazit
Zusammenfassend bietet die Durchführung eines Crossjoins in Excel mehrere flexible und effiziente Lösungen, sodass Sie die am besten geeignete Methode entsprechend Ihren spezifischen Anforderungen und Ihrer Arbeitsumgebung auswählen können:
- Formel nutzt dynamische Arrayformeln in Excel 365, um schnell Ergebnisse zu liefern – ganz ohne Programmierung. Ideal für Anwender, die native Formeln bevorzugen.
- Power Query bietet einen klaren, wiederverwendbaren Prozess zur Verarbeitung großer Datensätze und ist damit die ideale Lösung für Datenbereinigung und automatisierte Berichterstellung.
- Die PivotTable-Methode mag weniger direkt sein, erweist sich für ein erfahrenes Publikum jedoch als äußerst effektiv und intuitiv.
- Die VBA-benutzerdefinierte Funktion bietet das höchste Maß an Anpassbarkeit und eignet sich ideal für Szenarien, die eine Integration in komplexen Makrocode erfordern.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins