Come trasporre i riferimenti durante il riempimento automatico verso il basso/destra in Excel?

Quando si lavora con le formule e il riempimento automatico in Excel, il comportamento predefinito è che i riferimenti di colonna aumentano quando si trascina la maniglia di riempimento orizzontalmente verso destra, e i riferimenti di riga aumentano quando si trascina verticalmente verso il basso. Tuttavia, a volte potrebbe essere necessario trasporre i riferimenti — ad esempio, fare riferimento a un intervallo verticale ma riempire orizzontalmente, o viceversa. Questa operazione è particolarmente utile quando si riorganizzano layout di dati o si creano report dinamici che richiedono incroci di dati in modo trasposto. Questo articolo esplorerà vari metodi pratici per ottenere questo riempimento automatico trasposto, inclusi formule, macro VBA e strumenti convenienti come Kutools. Vedere l'illustrazione sottostante per uno scenario tipico di riempimento trasposto:

Trasponi riferimenti durante il riempimento verso il basso o a destra

Formula Excel – Usa INDICE o SCOSTAMENTO per costruire manualmente riferimenti trasposti

Trasponi riferimenti durante il riempimento verso il basso o a destra

Per trasporre un riferimento mentre si riempie attraverso righe o colonne, può essere utilizzata la funzione TRASPOSTA in Excel. Questo metodo è adatto quando si desidera convertire dati disposti in una colonna in una riga (o viceversa), con la formula che regola automaticamente la direzione del riferimento durante il riempimento automatico.

Seleziona l'intervallo dove desideri applicare il riferimento trasposto. Inserisci la seguente formula nella barra delle formule: =TRASPOSTA(Foglio2!$B$1:B12) e poi premi Ctrl + Maiusc + Invio simultaneamente (per le versioni precedenti a Microsoft 365 / Excel 2021). Nelle nuove versioni che supportano gli array dinamici, basta premere Invio è sufficiente. Vedi lo screenshot qui sotto:

In questa formula, B1:B12 rappresenta l'intervallo che desideri trasporre da un intervallo verticale a una disposizione orizzontale. Puoi regolare il nome del foglio e l'intervallo specifico per adattarlo alla tua fonte di dati effettiva.
Suggerimento: Per evitare errori di formula, assicurati che l'intervallo di destinazione per i dati trasposti sia dimensionato correttamente (ad esempio, se la tua fonte è una colonna con 12 righe, l'intervallo di destinazione dovrebbe essere una riga con 12 colonne).

Suggerimento. Se vuoi convertire rapidamente un intervallo in una singola colonna o riga (o viceversa) molte volte, oppure se gli intervalli sono grandi e le formule diventano difficili da gestire, puoi farlo in modo efficiente usando l'utilità Trasforma Intervallo di Kutools per Excel. Questo strumento offre un'interfaccia semplice e flessibilità per trasformare intervalli, riducendo al minimo il rischio di errori di formula o discrepanze. La funzionalità è completamente funzionante per 60 giorni. Scarica e prova gratuitamente.

Formula Excel – Usa INDICE o SCOSTAMENTO per costruire manualmente riferimenti trasposti

A volte usare formule di array come TRASPOSTA non è preferito, o si richiede più controllo su come vengono trasposti i riferimenti, specialmente per dati non contigui o quando si lavora con versioni precedenti di Excel. Con le funzioni INDICE e SCOSTAMENTO, puoi fare riferimento direttamente ai dati trasposti utilizzando operazioni di riempimento standard, senza bisogno di Ctrl + Maiusc + Invio.

Scenari applicabili: Preferibile quando vuoi un riferimento trasposto uno-a-uno per cella, ad esempio, fare riferimento alla cella A1 verticalmente a B1 orizzontalmente (o viceversa). Utile per evitare inserimenti di array, o per riferimenti dinamici attraverso fogli di lavoro complessi.

Vantaggi: Nessun input di array richiesto, le formule sono semplici da trascinare e riempire.
Potenziali svantaggi: Può essere un po' più complicato impostare la formula iniziale; prestare attenzione a come si regolano gli indici di riga e colonna.

Come usare INDICE per il riferimento trasposto:

1. Supponiamo di avere dei dati in A1:A5 (verticale), e vuoi riempirli trasposti in B1:F1 (orizzontale). Seleziona la cella B1 e inserisci la seguente formula:

=INDEX($A$1:$A$5,COLUMN(A1))

2. Premi Invio, quindi trascina la formula verso destra fino alla cella F1. La formula usa COLONNA(A1) per incrementare man mano che ti sposti a destra, facendo riferimento ad A1, A2, ..., A5 attraverso B1:F1.

Come usare SCOSTAMENTO per il riferimento trasposto:

1. Supponiamo che i tuoi dati siano nella riga B1:F1 (orizzontale), e vuoi riempirli verticalmente lungo la colonna G1:G5. Nella cella G1, inserisci:

=OFFSET($B$1,0,ROW(A1)-1)

2. Premi Invio, quindi trascina la formula verso il basso fino a G5. Man mano che riempi verso il basso, RIGA(A1)-1 si incrementerà da 0, spostando l'indice della colonna della funzione SCOSTAMENTO per recuperare B1, C1, D1, ecc.

Regola l'intervallo della formula o la posizione iniziale secondo necessità per adattarla al tuo layout di dati. Verifica sempre il corretto riferimento, soprattutto quando copi o sposti formule, per evitare discrepanze nei riferimenti.

Risoluzione dei problemi: Se ottieni un errore #RIF!, verifica nuovamente gli intervalli di riferimento e assicurati che la formula non stia cercando di riferirsi all'esterno del set di dati di destinazione. Inoltre, per set di dati di grandi dimensioni, queste formule possono aumentare il carico di calcolo del foglio di lavoro.

In sintesi, scegliere se usare formule, VBA o un componente aggiuntivo di Excel dipende dalla struttura dei dati, dalla frequenza con cui è necessario eseguire questa operazione e dal tuo livello di comfort con le funzionalità avanzate di Excel. Se un approccio non soddisfa le tue esigenze, prova un altro metodo come descritto sopra. Se incontri problemi persistenti di formule o riferimenti, verifica attentamente le formule per i riferimenti di celle bloccati (uso del simbolo $) e regolali se necessario per la corretta direzionalità durante la trasposizione.

Sblocca la Magia di Excel con Kutools AI

• Esecuzione Intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici — tutto guidato da semplici comandi.
• Formule Personalizzate: Genera formule su misura per ottimizzare i tuoi flussi di lavoro.
• Codifica VBA: Scrivi e implementa codice VBA senza sforzo.
• Interpretazione delle Formule: Comprendi facilmente formule complesse.
• Traduzione del Testo: Superare le barriere linguistiche all'interno dei tuoi fogli di calcolo.

Potenzia le tue capacità di Excel con strumenti alimentati dall'IA. Scarica Ora ed esperisci un'efficienza mai vista prima!