Creare un Crossjoin (tutte le combinazioni possibili) da 2 colonne in Excel — Guida completa
Quando lavori con due elenchi in Excel—come nomi di prodotti e taglie, regioni e rappresentanti di vendita, oppure studenti e corsi—potresti aver bisogno di generare tutte le combinazioni possibili tra di essi. Questa operazione è nota come Crossjoin (o Prodotto Cartesiano). Questo tutorial ti offre istruzioni dettagliate passo passo, vantaggi e svantaggi, ed esempi pratici per aiutarti a scegliere il metodo più adatto al tuo flusso di lavoro.
Cos’è un Crossjoin?
Un crossjoin (noto anche come prodotto cartesiano) è un’operazione che genera tutte le possibili combinazioni tra due elenchi. In Excel, significa abbinare ogni elemento dell’elenco A con ogni elemento dell’elenco B, creando una matrice completa di combinazioni.
I Crossjoin sono estremamente utili in molti scenari reali, ad esempio:
Varianti di prodotto
Combina colori, taglie e stili per creare un catalogo completo di prodotti.
Analisi delle vendite
Crea combinazioni tra regioni, rappresentanti e trimestri.
Pianificazione e organizzazione
Genera automaticamente tutte le combinazioni possibili tra dipendenti e turni o tra studenti e corsi.
Test e simulazioni
Crea combinazioni di scenari per modellazione, previsione o validazione.
Esempio:
Se hai:
Il risultato del Crossjoin sarà:
Eseguire un Crossjoin in Excel
Excel offre diversi metodi per creare un crossjoin; la scelta migliore dipende dalla versione di Excel che utilizzi, dal tuo livello di familiarità con formule e strumenti e dalle dimensioni del set di dati. Di seguito ti presentiamo quattro metodi pratici ed efficienti, che spaziano da semplici formule a soluzioni più avanzate come Power Query e VBA. Ogni approccio presenta vantaggi specifici, così puoi selezionare quello più adatto al tuo flusso di lavoro, alle dimensioni dei tuoi dati e alle tue esigenze di automazione.
Metodo 1: Cross Join tramite formula (Excel 365)
1.Prepara i tuoi dati: inserisci il primo elenco in una colonna (ad esempio A2:A5 per Prodotti) e il secondo elenco in un’altra colonna (ad esempio C2:C5 per Colori).
2.Inserisci la seguente formula in una cella vuota dove desideri visualizzare il risultato, quindi premi Invio: la formula si espanderà automaticamente, restituendo tutte le combinazioni generate. Vedi screenshot:
=TEXTSPLIT(TEXTJOIN(",", TRUE, A2:A5 & "|" & TRANSPOSE(C2:C5)), "|", ",")
Spiegazione di questa formula:
- A2:A5 & «|» & TRASPOSTA(C2:C5): Combina ogni valore della colonna A con ciascun valore della colonna C.
- TESTO.UNISCI(",", VERO, …): Unisce tutte le coppie in un’unica stringa di testo, separata da virgole.
- TESTO.SEPARA(…, «|», ","): Suddivide nuovamente il testo in una tabella a due colonne.
In sintesi, la formula crea coppie A|C, le unisce in un’unica stringa di testo e poi le suddivide nuovamente in una tabella strutturata a due colonne, generando così tutte le combinazioni possibili.
Suggerimenti:
Oltre alla formula illustrata in precedenza, puoi utilizzare anche la seguente per ottenere lo stesso risultato.
=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))))
)
))
Vantaggi
- Completamente dinamico
- Nessuna colonna di appoggio
- Si aggiorna automaticamente quando le liste di origine cambiano
Svantaggi
- Richiede Excel 365
- La formula appare complessa per i principianti
✨ Elenca tutte le combinazioni: genera ogni possibile combinazione con un solo clic!
Stanco di scrivere formule complesse per ottenere tutte le combinazioni possibili? Con Kutools per Excel puoi elencare immediatamente ogni combinazione di più colonne o valori — niente formule, niente Power Query, solo pochi clic!
Metodo 2: Cross Join tramite Power Query
Power Query offre un approccio pulito e senza codice, disponibile in tutte le versioni moderne di Excel. Con pochi clic, ti permette di generare un cross join senza dover scrivere alcuna formula. Segui le istruzioni passo-passo qui sotto.
Passaggio 1: Crea tabelle per ciascuna colonna di dati
1.Seleziona il primo elenco di dati, fai clic su Inserisci > Tabella. Nella finestra di dialogo Crea tabella, fai clic su OK. Otterrai la prima tabella.
2. Dalla scheda Progettazione tabella, rinomina la tabella con un nome significativo per facilitarne il riferimento in seguito.
3.Ripeti gli stessi passaggi per convertire un altro elenco di dati in una tabella e assegnarle un nome.
Passaggio 2: Importa le tabelle e carica come connessioni
1.Seleziona la prima tabella e fai clic su Dati>Da tabella/intervallo, vedi screenshot:
2. Nella finestra dell’Editor di Power Query aperta, fai clic su Chiudi e carica > Chiudi e carica dalla scheda Home.
3. Si aprirà la finestra di dialogo Importa dati. Seleziona l’opzione Crea solo connessione e fai clic su OK.
4. Nel riquadro Query e connessioni a destra, vedrai che una query è stata aggiunta esclusivamente come connessione.
5. Ripeti gli stessi passaggi per caricare la seconda tabella come query in sola connessione; la vedrai elencata accanto alla prima nel riquadro Query e connessioni.
Passaggio 3: Crea una query di riferimento e una colonna personalizzata
1.Fai clic con il pulsante destro del mouse sulla query che vuoi usare come prima tabella nel cross join e seleziona Riferimento.
2.Nella finestra dell’Editor di Power Query, vai alla scheda Aggiungi colonnae fai clic su Colonna personalizzata, vedi screenshot:
3. Nella finestra di dialogo Colonna personalizzata, nella casella Formula colonna personalizzata digita il nome dell’altra tabella che vuoi usare per il cross join. Quindi, fai clic sul pulsante OK.
Nota:
Se il nome della tua query contiene spazi (ad esempio, Colore prodotto), devi racchiuderlo nella sintassi #«Nome query» quando lo inserisci nella casella della formula della colonna personalizzata. Ad esempio, per «Colore prodotto», dovrai digitare #«Colore prodotto».
4. Apparirà una nuova colonna personalizzata; fai clic sul pulsante Espandi per visualizzarne il contenuto.
5.Nel riquadro espanso, assicurati che sia selezionato il nome della colonna che vuoi unire, quindi fai clic su OK.
6.Ora vedrai tutte le combinazioni generate dalle due tabelle.
Passaggio 4: Carica i dati nel foglio di lavoro
Vai alla scheda Home, fai clic su Chiudi e carica > Chiudi e carica. La tabella con tutte le combinazioni verrà caricata in un nuovo foglio di lavoro.
→
Vantaggi
- Gestisce dati voluminosi: prestazioni eccellenti anche con migliaia di righe.
- Riutilizzabile e aggiornabile: aggiungi altri dati all’intervallo di origine, aggiorna la query e i risultati si aggiorneranno automaticamente.
Svantaggi
- Leggermente più passaggi
- Richiede conoscenze di base di Power Query
Metodo 3: Cross Join tramite Tabella pivot
Questo metodo è indiretto, ma si rivela sorprendentemente efficace quando devi generare tutte le combinazioni senza ricorrere a formule. È ideale per chi preferisce un approccio visivo basato sui clic o per chi utilizza versioni di Excel che non supportano le formule a matrice dinamica.
1. Crea due tabelle distinte per gli elenchi di dati e assegnagli un nome seguendo gli stessi passaggi descritti al Passaggio 1 del Metodo 2.
2. Seleziona la tabella che desideri utilizzare come prima colonna. Poi, vai alla scheda Inserisci e fai clic su Tabella pivot. Vedi screenshot:
3. Nella finestra di dialogo Tabella pivot da tabella o intervallo, seleziona Foglio di lavoro esistente, scegli la posizione per la tabella pivot, spunta l’opzione Aggiungi questi dati al modello dati e poi fai clic su OK.
4.Quando compare il riquadro Campi tabella pivot a destra, seleziona il nome della colonna della tabella: verrà aggiunto automaticamente all’area Righe.
5.Passa quindi alla scheda Tutto nel riquadro Campi tabella pivot, seleziona la tabella che desideri utilizzare come seconda colonna del tuo Crossjoin e spunta il nome della relativa colonna per aggiungerla all’area Righe. Otterrai la tabella pivot mostrata nello screenshot seguente:
6. Fai clic su una cella della tabella pivot, vai alla scheda Progettazione, seleziona Layout report > Mostra in forma tabellare e otterrai la tabella pivot in formato tabellare. Vedi screenshot:
7. Prosegui facendo clic su Layout report > Ripeti tutte le etichette elemento per visualizzare tutti gli elementi in ogni riga.
8. Infine, fai clic su Totali complessivi > Disattivati per righe e colonne.
Ora la tabella pivot presenta un elenco pulito di tutte le combinazioni, senza righe o colonne di riepilogo.
Vantaggi
- Non richiede formule né Power Query
- Molto semplice e visivo
- Ideale per analisi rapide
Svantaggi
- Non dinamico
- Richiede azioni manuali
- L’output non è collegato ai dati originali
Metodo 4: Cross Join tramite funzione definita dall'utente (Excel 365 / Excel 2021 e versioni successive)
Se hai spesso bisogno di generare tutte le combinazioni possibili tra due elenchi, una funzione definita dall’utente (UDF) ti offre una soluzione pulita, riutilizzabile e altamente flessibile. Con Excel 365 o Excel 2021, l’output a matrice espansa rende i risultati completamente dinamici, fornendoti un modo potente e conveniente per automatizzare le operazioni di Cross Join.
1.Premi Alt + F11 per aprire l'editor VBA.
2.Quindi, fai clic su Inserisci > Modulo e copia e incolla il codice seguente nel modulo vuoto.
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. Torna al foglio di lavoro di Excel, inserisci la formula seguente e premi Invio: Excel riempirà automaticamente tutte le combinazioni.
=CrossJoin(A2:A5, C2:C5)
Conclusione
In sintesi, eseguire un Crossjoin in Excel offre diverse soluzioni flessibili ed efficienti, consentendoti di scegliere il metodo più adatto alle tue esigenze specifiche e al tuo ambiente di lavoro:
- La formula sfrutta le formule a matrice dinamica di Excel 365 per generare rapidamente i risultati senza alcuna programmazione, risultando perfetta per gli utenti occasionali che preferiscono soluzioni basate su formule native.
- Power Query offre un processo chiaro e riutilizzabile, perfetto per gestire grandi set di dati: la scelta ideale per pulire i dati e creare report in modo automatico.
- Il metodo Tabella pivot potrebbe essere meno diretto, ma si rivela molto efficace e intuitivo per un pubblico esperto.
- La funzione definita dall'utente (UDF) in VBA offre il massimo livello di personalizzazione ed è ideale per scenari che richiedono l'integrazione in codice macro complesso.
Migliori Strumenti per la Produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un’efficienza mai vista prima.Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi