Excel vba zu erstellen, jede mögliche Kombination von einer Reihe

Da habe ich angeboten, eine ODBC-Ansatz, den ich dachte, ich sollte erläutern, es ist nicht sofort offensichtlich, wie dies zu tun. Und, in der Ehrlichkeit, ich musste erst wieder lernen der Prozess und dokumentieren es für mich.

Dies ist ein Weg zur Erzeugung von Kartesisches Produkt zwei oder mehrere eindimensionale arrays Benutzung von Excel und Microsoft Query.

Diese Instruktionen geschrieben wurden, mit XL2007 aber sollte mit geringem (wenn überhaupt) änderungen in jeder version.

Schritt 1

Organisieren die arrays in Spalten.

Wichtig: Jede Spalte sollte mit zwei "header" - Namen, wie gezeigt, in Fett unten. Der oberste name wird später interpretiert werden als "table-name". Der zweite name wird interpretiert als "Spaltenname". Das wird ersichtlich, wenige Schritte später.

Wählen Sie die einzelnen Daten-Bereich, in turn, einschließlich der beiden "headers", und drücken Sie Ctrl+Shift+F3. Klicken Sie nur auf Top row in der "Namen Erstellen" - dialog und klicken Sie auf OK.

Einmal alle benannten Bereiche sind eingerichtet, speichern Sie die Datei.

Schritt 2

Daten | Externe Daten Abrufen | Aus Anderen Quellen | Von Microsoft Query

Wählen Sie <New Data Source>. In der Choose New Data Source dialog:

1. Einen Anzeigenamen für die Verbindung

2. wählen Sie die entsprechenden Microsoft Excel-Treiber

... dann Connect

Schritt 3

Select Workbook... dann suchen Sie nach Ihrer Datei.

Schritt 4

Fügen Sie die "Spalten" aus dem "Tabellen". Sie können jetzt sehen, warum die "zwei-header" layout in Schritt 1 ist wichtig-es tricks, die der Fahrer in das Verständnis der Daten ordnungsgemäß.

Nächstes klicken Sie Cancel (wirklich!). Eventuell werden Sie aufgefordert, an dieser Stelle auf "weiter Bearbeiten in Microsoft Query?" (Antwort Yes), oder eine Beschwerde, dass die Verknüpfungen nicht dargestellt werden können, die im grafischen editor. Ignorieren Sie diese und Schmieden an...

Schritt 5

Microsoft Query wird geöffnet, und standardmäßig die Tabellen, die Sie Hinzugefügt, wird sein Kreuz beigetreten. Dies erzeugt ein Kartesisches Produkt, das ist, was wir wollen.

Nun schließen MSQuery insgesamt.

Schritt 6

Sind Sie in das Arbeitsblatt zurückgegeben. Fast fertig, das verspreche ich! Tick New worksheet und OK.

Schritt 7

Cross-verknüpften Ergebnisse werden zurückgegeben.

InformationsquelleAutor der Antwort andy holaday

Ich brauchte, das selbst mehrere Male, und schließlich gebaut.

Glaube ich, dass der code skaliert werden für insgesamt Anzahl der Spalten und eine beliebige Anzahl von unterschiedlichen Werten in Spalten (z.B. jede Spalte kann eine beliebige Anzahl von Werten)

Es übernimmt alle Werte in jeder Spalte eindeutig ist (wenn dies nicht der Fall, erhalten Sie doppelte Zeilen)

Es wird davon ausgegangen, Sie wollen cross-join-output, basierend auf was auch immer Zellen, die Sie aktuell ausgewählt haben (stellen Sie sicher, dass Sie Ihnen allen)

Es wird vorausgesetzt, Sie möchten die Ausgabe zu starten, eine Spalte nach der aktuellen Auswahl.

Wie es funktioniert (kurz):
ersten für jede Spalte und für jede Zeile: Es berechnet die Anzahl der Zeilen benötigt, um alle combos in N Spalten (Elemente in Spalte 1 * Elemente in der Spalte 2 ... * Elemente in der Spalte N)

Sekunde für jede Spalte: bezogen auf den Gesamt-combos, und die Gesamt-combos der vorherigen Spalten berechnet es zwei Schleifen.

ValueCycles (wie viele Male Sie haben, um durch alle Werte in der aktuellen Spalte)
ValueRepeats (wie viele Male zu wiederholen, jeder Wert in der Spalte nacheinander)

Sub sub_CrossJoin()

Dim rg_Selection As Range
Dim rg_Col As Range
Dim rg_Row As Range
Dim rg_Cell As Range
Dim rg_DestinationCol As Range
Dim rg_DestinationCell As Range
Dim int_PriorCombos As Long
Dim int_TotalCombos As Long
Dim int_ValueRowCount As Long
Dim int_ValueRepeats As Long
Dim int_ValueRepeater As Long
Dim int_ValueCycles As Long
Dim int_ValueCycler As Long

int_TotalCombos = 1
int_PriorCombos = 1
int_ValueRowCount = 0
int_ValueCycler = 0
int_ValueRepeater = 0

Set rg_Selection = Selection
Set rg_DestinationCol = rg_Selection.Cells(1, 1)
Set rg_DestinationCol = rg_DestinationCol.Offset(0, rg_Selection.Columns.Count)

'get total combos
For Each rg_Col In rg_Selection.Columns
    int_ValueRowCount = 0
    For Each rg_Row In rg_Col.Cells
        If rg_Row.Value = "" Then
            Exit For
        End If
        int_ValueRowCount = int_ValueRowCount + 1
    Next rg_Row
    int_TotalCombos = int_TotalCombos * int_ValueRowCount
Next rg_Col

int_ValueRowCount = 0

'for each column, calculate the repeats needed for each row value and then populate the destination
For Each rg_Col In rg_Selection.Columns
    int_ValueRowCount = 0
    For Each rg_Row In rg_Col.Cells
        If rg_Row.Value = "" Then
            Exit For
        End If
        int_ValueRowCount = int_ValueRowCount + 1
    Next rg_Row
    int_PriorCombos = int_PriorCombos * int_ValueRowCount
    int_ValueRepeats = int_TotalCombos / int_PriorCombos


    int_ValueCycles = (int_TotalCombos / int_ValueRepeats) / int_ValueRowCount
    int_ValueCycler = 0

    int_ValueRepeater = 0

    Set rg_DestinationCell = rg_DestinationCol

    For int_ValueCycler = 1 To int_ValueCycles
        For Each rg_Row In rg_Col.Cells
            If rg_Row.Value = "" Then
                Exit For
            End If

                For int_ValueRepeater = 1 To int_ValueRepeats
                    rg_DestinationCell.Value = rg_Row.Value
                    Set rg_DestinationCell = rg_DestinationCell.Offset(1, 0)
                Next int_ValueRepeater

        Next rg_Row
    Next int_ValueCycler

    Set rg_DestinationCol = rg_DestinationCol.Offset(0, 1)
Next rg_Col
End Sub

InformationsquelleAutor der Antwort spioter

rufen Sie die Methode und in die aktuelle Ebene, die dekrementiert wird in der Methode (sorry für die DEU)

Beispiel:

    sub MyAdd(i as integer)
      if i > 1 then
        MyAdd = i + MyAdd(i-1)
      else
        MyAdd = 1
      end if
    end sub

InformationsquelleAutor der Antwort NDavid RU