Excel vba per creare ogni ansible combinazione di un intervallo

Ho un problema che non sono stato in grado di trovare da nessuna parte sul web (potrebbe essere lì, ma non riesco a trovarlo, heh).

Ho un foglio di calcolo con 13 colonne di dati. Ciascuna colonna contiene variazioni di un parametro che deve essere inserito in un caso di test globale.

Tutti loro differiscono, come

E:
101%
105%
110%
120%

J:
S superiore
Upside L
Lato negativo B
V premium

Ho visto diverse soluzioni al problema della combinazione che utilizza cicli annidati. Mi piacerebbe evitare 13 anelli annidati (ma questa è la mia migliore scommessa al momento). Sono un po ‘in perdita su come generare ogni combinazione unica in ogni colonna.

Non sono sicuro se questo abbia abbastanza senso per voi ragazzi. Speravo che qualcuno potesse almeno indicarmi la giusta direzione con un algoritmo ricorsivo. Mi piacerebbe renderlo abbastanza dinamico da prendere diversi numeri di colonne e righe.

Grazie per l’aiuto che puoi darmi.

Dal momento che ho offerto un approccio ODBC ho pensato di approfondirlo, poiché non è immediatamente ovvio come farlo. E, onestamente, avevo bisogno di imparare di nuovo il processo e documentarlo da solo.

Questo è un modo per generare un prodotto cartesiano di due o più array di dati unidimensionali utilizzando Excel e Microsoft Query.

Queste istruzioni sono state scritte con XL2007, ma dovrebbero funzionare con modifiche minori (se presenti) in qualsiasi versione.

Passo 1

Organizza gli array in colonne.

Importante: ogni colonna dovrebbe avere due nomi di “intestazione” come mostrato in grassetto sotto. Il nome più in alto verrà in seguito interpretato come un “nome tabella”. Il secondo nome verrà interpretato come “nome colonna”. Questo diventerà evidente qualche passo dopo.

Seleziona ciascun intervallo di dati a turno, includendo entrambe le “intestazioni” e premi Ctrl+Shift+F3 . Seleziona solo la Top row nella finestra di dialogo “Crea nomi” e fai OK su OK .

Una volta stabiliti tutti gli intervalli con nome, salvare il file.

inserisci la descrizione dell'immagine qui

Passo 2

Dati | Ottieni dati esterni | Da altre fonti | Da Microsoft Query

Scegli . Nella finestra di dialogo Choose New Data Source :

  1. Un nome descrittivo per la tua connessione

  2. scegliere il driver Microsoft Excel appropriato

… quindi Connect

inserisci la descrizione dell'immagine qui

Passaggio 3

Select Workbook... quindi cerca il tuo file.

inserisci la descrizione dell'immagine qui

Passaggio 4

Aggiungi le “colonne” dalle tue “tabelle”. Ora puoi vedere perché il layout “two header” nel passaggio 1 è importante, perché induce il guidatore a comprendere correttamente i dati.

Quindi fai clic su Cancel (davvero!). A questo punto potrebbe essere richiesto di “continuare a modificare in Microsoft Query?” (rispondere Yes ) o un reclamo che i join non possono essere rappresentati nell’editor grafico. Ignora questo e forgia su …

inserisci la descrizione dell'immagine qui

Passaggio 5

Si apre Microsoft Query e, per impostazione predefinita, le tabelle aggiunte verranno incrociate. Questo genererà un prodotto cartesiano, che è ciò che vogliamo.

Ora chiudere MSQuery del tutto.

inserisci la descrizione dell'immagine qui

Passaggio 6

Si ritorna al foglio di lavoro. Quasi finito, lo prometto! Spunta New worksheet e OK .

inserisci la descrizione dell'immagine qui

Passaggio 7

I risultati incrociati vengono restituiti.

inserisci la descrizione dell'immagine qui

Non sei sicuro del motivo per cui sei contrario al looping. Vedi questo esempio. Ci è voluto meno di un secondo.

 Option Explicit Sub Sample() Dim i As Long, j As Long, k As Long, l As Long Dim CountComb As Long, lastrow As Long Range("G2").Value = Now Application.ScreenUpdating = False CountComb = 0: lastrow = 6 For i = 1 To 4: For j = 1 To 4 For k = 1 To 8: For l = 1 To 12 Range("G" & lastrow).Value = Range("A" & i).Value & "/" & _ Range("B" & j).Value & "/" & _ Range("C" & k).Value & "/" & _ Range("D" & l).Value lastrow = lastrow + 1 CountComb = CountComb + 1 Next: Next Next: Next Range("G1").Value = CountComb Range("G3").Value = Now Application.ScreenUpdating = True End Sub 

ISTANTANEA

inserisci la descrizione dell'immagine qui

NOTA : quanto sopra è un piccolo esempio. Ho fatto un test su 4 colonne con 200 righe ciascuna. La combinazione totale ansible in un tale scenario è 1600000000 e ci sono voluti 16 secondi.

In tal caso, supera il limite delle righe di Excel. Un’altra opzione che posso pensare è scrivere l’output in un file di testo in uno scenario del genere. Se i tuoi dati sono piccoli, puoi andare via senza usare array e scrivere direttamente alle celle. 🙂 Ma in caso di dati di grandi dimensioni, vorrei raccomandare l’utilizzo di matrici.

Ne avevo bisogno diverse volte e alla fine l’ho costruito.

Credo che il codice sia scalabile per qualsiasi numero totale di colonne e qualsiasi numero di valori distinti all’interno delle colonne (ad es. Ogni colonna può contenere un numero qualsiasi di valori)

Si presuppone che tutti i valori in ogni colonna siano univoci (se ciò non è vero, si otterranno righe duplicate)

Si presuppone che tu desideri eseguire il cross-join dell’output in base alle celle che hai selezionato al momento (assicurati di selezionarle tutte)

Si presuppone che si desideri che l’output inizi una colonna dopo la selezione corrente.

Come funziona (in breve): prima per ogni colonna e per ogni riga: calcola il numero di righe totali necessarie per supportare tutte le combo in N colonne (voci nella colonna 1 * voci nella colonna 2 … * voci nella colonna N)

secondo per ogni colonna: in base alle combo totali e alle combo totali delle colonne precedenti, calcola due anelli.

ValueCycles (quante volte è necessario scorrere tutti i valori nella colonna corrente) ValueRepeats (quante volte è necessario ripetere ogni valore nella colonna consecutivamente)

 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 

Soluzione basata sul mio secondo commento. Questo esempio presuppone che tu abbia tre colonne di dati ma che possano essere adattate per gestirne di più.

Comincio con i tuoi dati di esempio. Ho aggiunto i conteggi nella riga superiore per comodità. Ho anche aggiunto il numero totale di combinazioni (prodotto dei conteggi). Questo è Sheet1 :

inserisci la descrizione dell'immagine qui

Sul Sheet2 :

inserisci la descrizione dell'immagine qui

formule:

A2:C2 (celle arancioni) sono hard coded =0

 A3=IF(SUM(B3:C3)=0,MOD(A2+1,Sheet1!$E$1),A2) B3=IF(C3=0,MOD(B2+1,Sheet1!$G$1),B2) C3=MOD(C2+1,Sheet1!$J$1) D2=INDEX(Sheet1!$E$2:$E$5,Sheet2!A2+1) E2=INDEX(Sheet1!$G$2:$G$6,Sheet2!B2+1) F2=INDEX(Sheet1!$J$2:$J$5,Sheet2!C2+1) 

Riempi dalla riga 3 verso il basso tante righe quante Total mostra su Sheet1

chiama il metodo e inserisci il livello corrente, che verrà decrementato nel metodo (scusa per l’inglese)

campione:

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