Come convertire una tabella in stile matrice in tre colonne in Excel?
Quando si lavora con i dati in Excel, si possono incontrare frequentemente tabelle in stile matrice che presentano le informazioni in una griglia, dove sia le righe che le colonne fungono da intestazioni. Sebbene questo formato sia visivamente utile per alcune analisi, potrebbe essere necessario trasformare questa matrice in una "lista" o tabella a tre colonne per attività come l'importazione di database, la normalizzazione dei dati, la creazione di grafici o analisi avanzate. La conversione della matrice in una lista a tre colonne (a volte definita come dati "unpivot") permette un filtraggio, un'aggregazione e un'integrazione più facili con altri strumenti di dati. Vedere l'esempio sottostante che illustra la trasformazione:
➤ Convertire la tabella in stile matrice in lista con Tabella pivot
➤ Convertire la tabella in stile matrice in lista con codice VBA
➤ Convertire la tabella in stile matrice in lista con Kutools per Excel
➤ Convertire la tabella in stile matrice in lista con formula Excel
Convertire la tabella in stile matrice in lista con Tabella pivot
In Excel, non esiste un comando incorporato diretto per convertire una tabella in stile matrice in una lista a tre colonne. Tuttavia, utilizzando la Creazione guidata Tabella pivot, è possibile trasformare efficacemente una matrice a tabulazione incrociata in dati piatti e tabulari adatti per ulteriori analisi. Questo approccio è ideale per set di dati di piccole o medie dimensioni ed è particolarmente utile quando si vuole appiattire una struttura di report complessa. È meno adatto per set di dati di grandi dimensioni o per utenti non familiari con le operazioni delle Tabelle pivot.
1. Apri il foglio di lavoro contenente la tua matrice. Premi Alt + D, quindi P per aprire la Creazione guidata Tabella pivot e Grafico pivot. Nella procedura guidata:
- Sotto Dove sono i dati che desideri analizzare, scegli Intervalli di consolidamento multipli.
- Sotto Che tipo di report vuoi creare, seleziona Tabella pivot.
2. Clicca su Avanti. Nel passaggio 2a di 3 della finestra di dialogo, seleziona Creerò i campi di pagina:
3. Clicca Avanti. In Passaggio 2b di 3clicca il 
pulsante e seleziona l'intervallo completo dei dati della matrice, inclusi le intestazioni di riga e colonna. Clicca Aggiungi per inserire l'intervallo in Tutti gli intervalli lista. Conferma che la selezione dell'intervallo copra l'intera matrice.
4. Clicca su Avanti. Nel Passaggio 3 di 3, scegli se posizionare la Tabella pivot in un nuovo foglio di lavoro o in una cella specifica:
5. Clicca su Fine. Excel genera una Tabella pivot che riassume la matrice. Per impostazione predefinita, mostra totali aggregati alle intersezioni. Non è necessario regolare la struttura della Tabella pivot per questo compito—procedi direttamente al passaggio successivo:
6. Fai doppio clic sulla cella dove si intersecano i totali generali di riga e colonna (ad esempio, cella F22). Excel creerà un nuovo foglio contenente una lista a tre colonne, dove ogni riga mostra una combinazione unica di intestazione di riga e colonna con il valore corrispondente.
7. Per finalizzare, seleziona la nuova tabella, fai clic con il pulsante destro del mouse, quindi scegli Tabella > Converti in intervallo. Questo rimuove la formattazione della tabella, lasciando un elenco semplice ed editabile:
Consiglio: Se la tua matrice cambia frequentemente, dovrai ripetere questo processo per aggiornare la lista a tre colonne. Questo metodo è migliore per i dati statici. Inoltre, se la tua matrice contiene celle vuote o unite, potrebbe essere necessaria una pulizia prima di usare questa tecnica.
Convertire la tabella in stile matrice in lista con codice VBA
Se preferisci l'automazione o vuoi applicare questa trasformazione ripetutamente, utilizzando una macro VBA puoi convertire rapidamente qualsiasi tabella in stile matrice in una lista a tre colonne strutturata. Questo metodo è particolarmente efficace per set di dati di grandi dimensioni o layout diversificati ed elimina la necessità di formattazione manuale. È ideale per gli utenti familiari con l'esecuzione di script VBA.
1. Premi Alt + F11 per aprire l'editor Microsoft Visual Basic for Applications.
2. Nell'editor, clicca Inserisci > Modulo per creare un nuovo modulo. Quindi incolla il seguente codice nella finestra del modulo:
📜 Codice VBA: Convertire la matrice in lista
Sub ConvertTable()
' Updated by Extendoffice
Dim Rng As Range
Dim cRng As Range
Dim rRng As Range
Dim xOutRng As Range
xTitleId = "KutoolsforExcel"
Set cRng = Application.InputBox("Select your Column labels", xTitleId, Type:=8)
Set rRng = Application.InputBox("Select Your Row Labels", xTitleId, Type:=8)
Set Rng = Application.InputBox("Select your data", xTitleId, Type:=8)
Set outRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set xWs = Rng.Worksheet
k = 1
xColumns = rRng.Column
xRow = cRng.Row
For i = Rng.Rows(1).Row To Rng.Rows(1).Row + Rng.Rows.Count - 1
For j = Rng.Columns(1).Column To Rng.Columns(1).Column + Rng.Columns.Count - 1
outRng.Cells(k, 1) = xWs.Cells(i, xColumns)
outRng.Cells(k, 2) = xWs.Cells(xRow, j)
outRng.Cells(k, 3) = xWs.Cells(i, j)
k = k + 1
Next j
Next i
End Sub3. Premi F5 o clicca Esegui per eseguire la macro. Una serie di prompt ti guiderà attraverso le selezioni richieste:
Passaggio 1: Seleziona le etichette di colonna (di solito la prima riga della tua matrice):
Passaggio 2: Seleziona le etichette di riga (di solito la prima colonna della tua matrice):
Passaggio 3: Seleziona l'intervallo effettivo dei dati della matrice (escludendo le intestazioni di riga e colonna):
Passaggio 4: Scegli la cella di output dove la lista convertita a tre colonne dovrebbe iniziare. Si consiglia di usare una cella vuota o un nuovo foglio di lavoro:
Passaggio 5: Clicca OK. La tua matrice verrà ora convertita in una lista piatta a tre colonne.
⚠️ Note & Suggerimenti:
• Assicurati di non includere intestazioni di colonna o riga nell'intervallo dei dati della matrice.
• Se la tua matrice ha celle unite, separale prima di eseguire la macro per evitare errori.
• Se incontri errori, rivedi gli intervalli selezionati e assicurati che siano allineati correttamente.
Convertire la tabella in stile matrice in lista con Kutools per Excel
Sebbene i metodi sopra descritti siano efficaci, possono sembrare tediosi o intimidatori per gli utenti meno esperti. Se stai cercando una soluzione rapida e facile da usare, Kutools per Excel offre un'utilità dedicata chiamata Converti dimensioni tabella, specificamente progettata per questo scopo.
Questo strumento è ideale per gli utenti che convertono frequentemente tabelle in stile matrice o devono eseguire l'elaborazione batch. Può preservare la formattazione originale—come carattere, colore di riempimento e formule—se necessario. Uno svantaggio potenziale è che Kutools è un componente aggiuntivo di terze parti che deve essere installato, ma è un'opzione potente per chiunque riorganizzi regolarmente i dati in Excel.
Passaggi:
1. Dopo aver installato Kutools, vai alla scheda Kutools, clicca Intervallo, quindi seleziona Converti dimensioni tabella:
2. Nella finestra di dialogo Converti dimensioni tabella:
- (1) Scegli Tabella bidimensionale in lista sotto la sezione Tipo di conversione.
- (2) Clicca il
pulsante accanto a Intervallo di origine per selezionare la tua tabella matrice. - (3) Clicca il pulsante accanto a Intervallo risultati per specificare dove posizionare l'output.
Assicurati di selezionare l'intera matrice (inclusi sia le intestazioni che i dati) per evitare conversioni parziali o risultati errati.
3. Clicca OK. La matrice verrà convertita istantaneamente in una lista a tre colonne, preservando dove possibile la formattazione delle celle originali:
Suggerimento: Questa funzionalità supporta anche l'operazione inversa—convertire una lista piatta in una matrice bidimensionale. È utile per ricostruire report o preparare i dati per l'analisi incrociata. Vedi: come convertire una lista in una tabella a croce 2D.
➤ Scopri di più sulla funzionalità Converti dimensioni tabella
⏬ Scarica e prova gratuitamente Kutools per Excel ora!
Convertire la tabella in stile matrice in lista con Formula Excel
Se preferisci un approccio basato sulle formule—particolarmente utile se vuoi che la tua lista a tre colonne si aggiorni dinamicamente mentre la matrice cambia—puoi utilizzare una combinazione delle funzioni INDICE, RIF.RIGA, RIF.COLONNA e CONTA.VALORI per annullare manualmente la pivotizzazione dei dati. Questa soluzione non richiede VBA o componenti aggiuntivi ed è ideale per coloro che desiderano evitare di usare macro o strumenti esterni. Tuttavia, richiede attenzione scrupolosa ai riferimenti delle formule poiché le formule devono tipicamente essere riempite come array o trascinate sistematicamente verso il basso/lato. È più pratica per matrici di dimensioni moderate e situazioni in cui l'elenco deve rimanere attivo e reattivo ai cambiamenti dei dati di origine.
Supponiamo che il tuo intervallo di dati sia il seguente:
- Le etichette di riga sono nelle celle A2:A10.
- Le etichette di colonna sono nelle celle B1:J1.
- I valori della matrice sono nelle celle B2:J10.
1. Crea un nuovo foglio di lavoro o inizia in un'area vuota del tuo foglio esistente. Nella cella L2, inserisci la seguente formula per estrarre l'etichetta di riga:
=INDEX($A$2:$A$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1)2. Nella cella M2, inserisci questa formula per estrarre l'etichetta di colonna corrispondente:
=INDEX($B$1:$J$1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)3. Nella cella N2, estrai il valore dalla matrice con:
=INDEX($B$2:$J$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)4. Seleziona le celle L2:N2 e trascina la maniglia di riempimento verso il basso. Il punto di arresto dovrebbe essere numero di righe × numero di colonne (in questo esempio, sono 9 righe × 9 colonne = 81 righe totali).
✅ Suggerimenti:
- Regola tutti gli intervalli per adattarli alla tua struttura dati effettiva.
- Usa
SEeVAL.VUOTOper filtrare le righe vuote se necessario. - Per un comportamento di espansione automatica, considera l'uso di
SCARTOo intervalli denominati dinamici. - Questo metodo è più pratico per la dimensione statica della matrice e volumi moderati di dati.
ℹ️ Note aggiuntive:
- Vantaggi: Le formule rimangono attive e riflettono automaticamente i cambiamenti della matrice. Non è richiesto VBA o componenti aggiuntivi.
- Svantaggi: Potrebbe rallentare le prestazioni della cartella di lavoro per matrici di grandi dimensioni. Richiede una configurazione accurata.
Demo: Convertire la tabella in stile matrice in lista con Kutools per Excel
I 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 oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in