Excel vba pour créer toutes les combinaisons possibles d'une gamme

Depuis que je lui ai offert un ODBC approche que j'ai pensé que je devrais la faire évoluer, comme il n'est pas immédiatement évident pour ce faire. Et, dans l'honnêteté, j'avais besoin de réapprendre les processus et les documenter pour moi-même.

C'est une façon de générer un Produit cartésien de deux ou plus de la dimension des données des tableaux à l'aide d'Excel et Microsoft Query.

Ces instructions ont été écrites avec XL2007 mais devrait fonctionner avec mineur (le cas échéant) modifications dans n'importe quelle version.

Étape 1

Organiser des groupes de colonnes.

Important: Chaque colonne doit avoir deux "en-tête" des noms comme indiqué en gras ci-dessous. Le premier nom sera par la suite être interprété comme un "nom de table". Le deuxième nom est interprété comme un "nom de colonne". Cela deviendra évident quelques étapes plus loin.

Sélectionnez chaque plage de données à leur tour, y compris les en-têtes", et a frappé Ctrl+Shift+F3. Cochez uniquement Top row dans le "Créer les Noms de" boîte de dialogue et cliquez sur OK.

Une fois toutes les plages nommées sont établis, enregistrez le fichier.

Étape 2

Données | Données Externes | À Partir D'Autres Sources Et À Partir De Microsoft Query

Choisir <New Data Source>. Dans le Choose New Data Source dialogue:

1. Un nom convivial pour votre connexion

2. choisir le pilote Microsoft Excel

... puis Connect

Étape 3

Select Workbook... puis recherchez votre fichier.

Étape 4

Ajouter les "colonnes" de votre "tables". Vous pouvez maintenant voir pourquoi le "deux en-tête de" mise à l'étape 1 c'est important-il des astuces au conducteur de les comprendre correctement les données.

Ensuite, cliquez sur Cancel (vraiment!). Vous pouvez être invité à ce stade, de "continuer à modifier dans Microsoft Query?" (réponse Yes), ou d'une plainte qui se joint ne peut pas être représenté dans l'éditeur graphique. Ignorer cela, et de forger...

Étape 5

Microsoft Requête s'ouvre, et par défaut, les tables vous seront croisés. Cela va générer un produit Cartésien, qui est ce que nous voulons.

Maintenant fermer MSQuery tout à fait.

Étape 6

Vous revenez à la feuille de calcul. Presque terminé, je le promets! Tique New worksheet et OK.

Étape 7

La croix-joint les résultats sont renvoyés.

J'avais besoin de ce moi-même à plusieurs reprises et finalement construit.

Je crois que le code des échelles pour n'importe quel nombre total de colonnes et le nombre de valeurs distinctes dans les colonnes (par exemple, chaque colonne peut contenir un nombre quelconque de valeurs)

Il suppose que toutes les valeurs dans chaque colonne unique (si ce n'est pas vrai, vous obtiendrez des lignes en double)

Il suppose que vous voulez de la croix-rejoindre une sortie basée sur ce que les cellules que vous avez actuellement sélectionné (assurez-vous de sélectionner tous)

Il suppose que vous voulez que le résultat commence une colonne après la sélection en cours.

Comment il fonctionne (brièvement):
d'abord pour chaque colonne et pour chaque ligne: Il calcule le nombre total de lignes nécessaires à l'appui de tous les combos en N colonnes (les éléments de la colonne 1 * les éléments de la colonne 2 ... * les éléments de la colonne N)

seconde pour chaque colonne: sur la Base du total des combos, et le total des combos des colonnes précédentes il calcule deux boucles.

ValueCycles (combien de fois vous avez à faire défiler toutes les valeurs dans la colonne en cours)
ValueRepeats (combien de fois répéter chaque valeur de la colonne consécutivement)

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

appel de la méthode et de mettre dans le niveau actuel, qui sera décrémenté dans la méthode (désolé pour l'anglais)

exemple:

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