Excel: uso dinamico dei Nomi definiti con INDIRECT, INDEX e COLUMN

Vuoi far scorrere una formula di Excel a destra e, senza riscriverla, puntare automaticamente al “Nome definito” corretto (QData1, QData2, …)? In questa guida pratica vedrai come farlo con INDIRECT, come evitarlo nelle versioni più moderne di Excel e come rendere la soluzione robusta e veloce.

Indice

Scenario e obiettivo

Supponiamo di avere molti Nomi definiti che puntano a colonne adiacenti dello stesso blocco dati, ad esempio:

QData1 = Sheet1!$C$5:$C$39
QData2 = Sheet1!$D$5:$D$39
QData3 = Sheet1!$E$5:$E$39
…

L’obiettivo è scrivere una sola formula e riempirla verso destra in modo che selezioni automaticamente il nome giusto in base alla colonna in cui si trova la formula. In altre parole, vogliamo trasformare:

=INDEX(QData1, 1, 1)

in qualcosa come:

=INDEX(QData#, 1, 1)

dove # è calcolato dinamicamente dalla posizione della formula (con COLUMN()).

Soluzione rapida con INDIRECT

La via più semplice è costruire testualmente il nome definito desiderato e convertirlo in riferimento con INDIRECT:

=INDEX(INDIRECT("QData"&amp;COLUMN()-2), 1, 1)

Come funziona:

COLUMN() restituisce l’indice della colonna in cui è scritta la formula (C=3, D=4, …).
-2 riallinea l’indice per far sì che in C il suffisso sia 1, in D sia 2 e così via.
"QData"&COLUMN()-2 produce la stringa QData1, QData2, QData3, ecc.
INDIRECT converte quella stringa nel riferimento reale al Nome definito.
INDEX(..., 1, 1) estrae il primo elemento della colonna.

Nota terminologia (Excel in italiano):

INDEX = INDICE
INDIRECT = INDIRETTO
COLUMN = COLONNA

In molte installazioni italiane il separatore degli argomenti è il ; invece della ,. Per esempio:
=INDICE(INDIRETTO("QData"&COLONNA()-2); 1; 1)

Quando serve l’intera colonna (Dynamic Array)

Se vuoi far “spillare” l’intera colonna associata a QData# (Microsoft 365), usa 0 come numero di righe in INDEX:

=INDEX(INDIRECT("QData"&amp;COLUMN()-2), 0, 1)

Questa variante restituisce tutte le righe della colonna mirata e le fa “spandere” in verticale dal punto della formula in giù.

Rendere l’allineamento robusto

La sottrazione fissa -2 funziona se il blocco inizia dalla colonna C. Per rendere la formula indipendente dalla colonna di partenza, usa l’ancora della prima colonna:

=INDEX(INDIRECT("QData"&amp;(COLUMN()-COLUMN(Sheet1!$C$1)+1)), 1, 1)

In questo modo, se un domani sposti il blocco, ti basterà aggiornare l’ancora Sheet1!$C$1 e l’offset tornerà coerente.

Passo–passo: come costruire la soluzione

Verifica i Nomi definiti: vai su Formule > Gestione nomi e controlla che QData1, QData2, … puntino a colonne contigue dello stesso blocco (stesso numero di righe, stesso foglio).
Scegli l’ancora: individua la cella “di riferimento” corrispondente alla prima colonna (es. Sheet1!$C$1).
Scrivi la formula nella prima cella dove vuoi il risultato, ad esempio: =INDEX(INDIRECT("QData"&(COLUMN()-COLUMN(Sheet1!$C$1)+1)), 1, 1)
Riempimento a destra: trascina o copia a destra. A ogni spostamento la formula incrementerà # in modo automatico (QData1, QData2, …).

Alternative moderne senza INDIRECT (Microsoft 365)

INDIRECT è pratico, ma è volatile (vedi più sotto) e può rallentare cartelle di lavoro grandi. Se usi Microsoft 365, puoi evitare INDIRECT in due modi eleganti.

Alternativa A — Un unico Nome sul blocco e INDEX con colonna variabile

Invece di tanti nomi, definiscine uno solo (ad es. QDataAll = Sheet1!$C$5:$Z$39) sull’intero blocco. Poi usa la colonna variabile in INDEX:

=INDEX(QDataAll, 1, COLUMN()-COLUMN(Sheet1!$C$1)+1)

Per far spillare l’intera colonna:

=INDEX(QDataAll, 0, COLUMN()-COLUMN(Sheet1!$C$1)+1)

Vantaggi: nessuna funzione volatile, una sola definizione da manutenere, massima velocità. Svantaggi: cambi il modello concettuale (da “tanti nomi” a “un nome unico”).

Alternativa B — CHOOSECOLS

Se le colonne sono contigue, puoi selezionare direttamente la n-esima colonna con CHOOSECOLS:

=INDEX(CHOOSECOLS(Sheet1!$C$5:$Z$39, COLUMN()-COLUMN(Sheet1!$C$1)+1), 1, 1)

Per ottenere l’intera colonna:

=CHOOSECOLS(Sheet1!$C$5:$Z$39, COLUMN()-COLUMN(Sheet1!$C$1)+1)

Nota: CHOOSECOLS non è volatile ed è disponibile in Microsoft 365 e nelle versioni più recenti.

Versioni con LET e LAMBDA per chiarezza e performance

La funzione LET consente di nominare sotto-espressioni, migliorando leggibilità e performance (calcola una sola volta i termini ripetuti). Esempio con INDIRECT:

=LET(
  n, COLUMN()-COLUMN(Sheet1!$C$1)+1,
  rng, INDIRECT("QData"&amp;n),
  INDEX(rng, 1, 1)
)

Oppure, su un unico nome di blocco non volatile:

=LET(
  n, COLUMN()-COLUMN(Sheet1!$C$1)+1,
  INDEX(QDataAll, 1, n)
)

Puoi anche creare una LAMBDA (Microsoft 365) per riutilizzare la logica:

=LAMBDA(n, INDEX(INDIRECT("QData"&amp;n), 1, 1))

Dalle Definizioni nome assegnale un nome (es. GetQ) e usala così:

=GetQ(COLUMN()-COLUMN(Sheet1!$C$1)+1)

Controlli, errori e casi limite

Nomina coerente e spazi: verifica che i nomi siano uniformi (QData1, QData2, …) e senza spazi o caratteri speciali.
Stesse dimensioni: tutte le colonne devono avere pari numero di righe; misure diverse possono produrre risultati inattesi (soprattutto con i Dynamic Array).
Gestione errori: se la formula può “spingersi” oltre l’ultima colonna disponibile, avvolgi con IFERROR: =IFERROR(INDEX(INDIRECT("QData"&(COLUMN()-2)), 1, 1), "") Versione non volatile con QDataAll: =LET( n, COLUMN()-COLUMN(Sheet1!$C$1)+1, IF(n>COLUMNS(QDataAll), "", INDEX(QDataAll, 1, n)) )
Rinominare il foglio: la soluzione con INDIRECT non risente del rename se componi solo il nome definito (es. "QData"&n). Se invece componi un indirizzo di cella testuale ("Sheet1!C5:C39"), ricorda che INDIRECT non aggiorna automaticamente i nomi dei fogli rinominati.

Prestazioni: cosa considerare

INDIRECT è volatile: si ricalcola ad ogni cambiamento del file. Con poche formule non c’è problema; con decine di migliaia può diventare un collo di bottiglia. In questi casi:

Preferisci il pattern un solo nome di blocco + INDEX con colonna variabile (non volatile).
Se necessario resta su CHOOSECOLS (Microsoft 365), anch’esso non volatile.
Usa LET per ridurre calcoli duplicati.

Tabella riassuntiva: pro e contro

Approccio	Formula tipo	Pro	Contro	Quando usarlo
INDIRECT + tanti nomi	`=INDEX(INDIRECT("QData"&col), r, c)`	Immediato, non richiede cambiare la struttura esistente	Volatile, scalabilità limitata	Pochi dati, soluzione rapida
Un solo nome di blocco	`=INDEX(QDataAll, r, col)`	Prestazioni ottime, manutenzione minima	Serve creare/accettare un nome unico	Dataset medio-grandi, 365/2019/2021
CHOOSECOLS	`=CHOOSECOLS(rng, col)`	Non volatile, sintassi chiara	Solo Microsoft 365	365, colonne contigue

Ricette pronte (copiaincolla)

Valore della prima riga nella colonna puntata dal nome

=INDEX(INDIRECT("QData"&amp;(COLUMN()-COLUMN(Sheet1!$C$1)+1)), 1, 1)

Intera colonna (spill)

=INDEX(INDIRECT("QData"&amp;(COLUMN()-COLUMN(Sheet1!$C$1)+1)), 0, 1)

Elemento alla riga n

=LET(
  n, ROW()-ROW($A$1)+1,
  INDEX(INDIRECT("QData"&amp;(COLUMN()-COLUMN(Sheet1!$C$1)+1)), n, 1)
)

Somma della colonna corrente

=SUM(INDIRECT("QData"&amp;(COLUMN()-COLUMN(Sheet1!$C$1)+1)))

Media della colonna corrente

=AVERAGE(INDIRECT("QData"&amp;(COLUMN()-COLUMN(Sheet1!$C$1)+1)))

Versioni non volatili equivalenti (nome unico di blocco)

Assumi QDataAll = Sheet1!$C$5:$Z$39.

Valore prima riga: =INDEX(QDataAll, 1, COLUMN()-COLUMN(Sheet1!$C$1)+1)
Intera colonna: =INDEX(QDataAll, 0, COLUMN()-COLUMN(Sheet1!$C$1)+1)
Somma: =LET( n, COLUMN()-COLUMN(Sheet1!$C$1)+1, SUM(INDEX(QDataAll, 0, n)) )

Compatibilità e locali

Separatore di elenco: italiano spesso usa ;. Adatta le formule di conseguenza.
Nomi funzione: valuta se il tuo Excel è impostato in italiano (INDICE, INDIRETTO, COLONNA) o in inglese (INDEX, INDIRECT, COLUMN).
Excel per il Web: supporta CHOOSECOLS e i Dynamic Array; performance molto buone con l’approccio non volatile.

Se vuoi mantenere tanti nomi ma evitare la volatilità

Non esiste una funzione nativa non volatile che accetti un nome costruito a testo e lo risolva (questa è proprio la specialità di INDIRECT). Se devi restare su “molti nomi” e desideri evitare INDIRECT, l’unica alternativa è mappare esplicitamente i nomi tramite CHOOSE o SWITCH, che però scala male:

=CHOOSE(COLUMN()-2, QData1, QData2, QData3, QData4, ...)

È praticabile solo con pochi nomi; oltre diventa ingestibile. Per questo, in chiave prestazioni, conviene quasi sempre migrare a un nome unico di blocco oppure usare CHOOSECOLS sul range originale.

Check-list di qualità

Coerenza range: tutti i QData# devono avere la stessa altezza (stesse righe).
Colonne contigue: se usi CHOOSECOLS, le colonne devono essere attigue. Con nomi discontinui resta su INDIRECT o su un mapping esplicito.
Ancora corretta: verifica che Sheet1!$C$1 rappresenti davvero la tua prima colonna logica.
Gestione overflow: proteggi con IFERROR o controlli su COLUMNS(...) per evitare #RIF!.

Esempio completo commentato

Dataset in Sheet1!$C$5:$F$39 (4 colonne). Nomi: QData1 → $C$5:$C$39, …, QData4 → $F$5:$F$39. In Sheet2!$C$3 inserisci:

=LET(
  an, COLUMN()-COLUMN(Sheet1!$C$1)+1,     / indice colonna "ancorato" /
  nm, "QData"&amp;an,                         / costruisco il nome QData# /
  rng, INDIRECT(nm),                      / lo converto in riferimento /
  IFERROR(INDEX(rng, 1, 1), "")           / prendo la prima riga o "" /
)

Copiando a destra in D3, E3, F3 otterrai i primi valori delle colonne QData2, QData3, QData4. Se incolli la versione con 0 come numero di righe, vedrai spillare l’intera colonna sotto la cella di partenza.

Consigli di manutenzione

Nuove colonne: aggiungi semplicemente QDataN (o estendi QDataAll se usi il nome unico).
Excel Table: se converti il blocco in una Tabella (Ctrl+T), puoi sostituire il riferimento con Table1 e usare CHOOSECOLS(Table1, ...) per soluzioni ancora più stabili nel tempo.
Documenta l’ancora: inserisci un commento sulla cella di ancoraggio o crea un Nome dedicato (es. QAnchor) da usare al posto di Sheet1!$C$1.

Domande frequenti

Posso usare OFFSET al posto di INDIRECT?
Sì, ma OFFSET (SCARTO) è anch’essa volatile. Se l’obiettivo è la performance, preferisci l’approccio con INDEX su un blocco o CHOOSECOLS.

INDIRECT aggiorna i riferimenti quando rinomino il foglio?
Se componi un nome definito (es. "QData"&n) non c’è problema. Se componi un indirizzo testuale (es. "Sheet1!C5:C39") allora no: INDIRECT non “segue” il rinomina-foglio.

Come posso cambiare il punto di partenza senza rieditare tutto?
Usa l’ancora con COLUMN(Sheet1!$C$1) (o un nome come QAnchor) e basa l’offset su quella.

Riepilogo operativo

Vuoi la via più veloce? INDIRECT + concatenazione del nome: INDEX(INDIRECT("QData"&COLONNA()-2); ...).
Vuoi velocità e robustezza? Un solo nome sul blocco + INDEX(..., col) o CHOOSECOLS (Microsoft 365).
Rendi la formula indipendente dalla colonna iniziale con l’ancora COLUMN(...$C$1).
Proteggi con IFERROR e documenta l’ancora per manutenzione a prova di futuro.

Esempi completi (IT vs EN)

Obiettivo	Formula (IT; separatore 😉	Formula (EN; separatore ,)
Primo valore colonna corrente con nomi QData#	`=INDICE(INDIRETTO("QData"&(COLONNA()-COLONNA(Sheet1!$C$1)+1)); 1; 1)`	`=INDEX(INDIRECT("QData"&(COLUMN()-COLUMN(Sheet1!$C$1)+1)), 1, 1)`
Intera colonna (spill)	`=INDICE(INDIRETTO("QData"&(COLONNA()-COLONNA(Sheet1!$C$1)+1)); 0; 1)`	`=INDEX(INDIRECT("QData"&(COLUMN()-COLUMN(Sheet1!$C$1)+1)), 0, 1)`
Alternativa non volatile con nome unico QDataAll	`=INDICE(QDataAll; 1; COLONNA()-COLONNA(Sheet1!$C$1)+1)`	`=INDEX(QDataAll, 1, COLUMN()-COLUMN(Sheet1!$C$1)+1)`
CHOOSECOLS (365) – intera colonna	`=CHOOSECOLS(Sheet1!$C$5:$Z$39; COLONNA()-COLONNA(Sheet1!$C$1)+1)`	`=CHOOSECOLS(Sheet1!$C$5:$Z$39, COLUMN()-COLUMN(Sheet1!$C$1)+1)`

Conclusione

Sì, INDIRECT è la soluzione più semplice per “costruire” il nome definito desiderato e usarlo in una formula che scorre a destra. Tuttavia, se lavori su cartelle di lavoro grandi o su Microsoft 365, vale la pena adottare l’approccio non volatile (nome unico + INDEX o CHOOSECOLS) per ottenere performance e manutenibilità superiori. In ogni caso, ancorare il calcolo della colonna con COLUMN(...$C$1), proteggere gli estremi con IFERROR e, quando possibile, racchiudere la logica in LET/LAMBDA renderà la soluzione stabile e pronta a crescere.

In sintesi operativa: usa INDIRECT se vuoi il massimo della flessibilità immediata con nomi preesistenti; passa a INDEX su un blocco o a CHOOSECOLS se ti servono velocità e scalabilità senza sorprese.