La formula INDIRETTO in Excel: costruire riferimenti dinamici tra fogli

Bentrovato sul mio blog, io sono Marco in arte Macro Braglia e, se non mi conosci, sono un esperto di Excel e di automazione aziendale, con le macro e con tante altre cose.

Quante volte ti sei trovato con dodici fogli mensili costruiti allo stesso modo e un tredicesimo foglio da compilare collegando una cella alla volta??? Gennaio punta a una cella, febbraio alla stessa cella di un altro foglio, marzo idem e così via fino a dicembre. Il risultato si ottiene, ma stai riscrivendo dodici volte una logica che è sempre la stessa.

La formula INDIRETTO serve proprio a costruire un riferimento partendo da una stringa di testo. In questo articolo vediamo come ragionare su questa funzione e come usarla per:

1. trasformare il testo E5 in un riferimento alla cella E5;
2. leggere una cella che si trova in un altro foglio;
3. passare a una formula un intero intervallo;
4. lavorare con nomi e riferimenti strutturati delle tabelle;
5. raccogliere in un riepilogo i valori di dodici fogli mensili.

Se preferisci la versione video di questo tutorial, eccola qui sotto.

TL;DR

INDIRETTO chiede a Excel di interpretare una stringa come un riferimento. La formula =INDIRETTO("E5"), per esempio, restituisce il contenuto della cella E5. Il vantaggio arriva quando quella stringa non è fissa, ma viene composta usando il nome di un foglio, un'intestazione o un indirizzo scritto in un'altra cella.

Nel riepilogo annuale costruiremo il riferimento con:

=INDIRETTO(D$2&"!"&$B3)

In D2 avremo il nome del mese, mentre in B3 avremo l'indirizzo della cella da leggere. Trascinando la formula verso destra, cambierà il mese senza dover riscrivere il riferimento a mano.

Prima della formula: valore, testo e riferimento

Per capire INDIRETTO dobbiamo mettere ordine tra tre elementi che in Excel possono sembrare molto simili:

• E5 scritto dentro una formula è un riferimento;
• "E5", racchiuso tra virgolette, è una stringa di testo;
• il contenuto della cella E5 è il valore che Excel ci restituisce quando valuta quel riferimento.

Se scrivi:

=E5

stai chiedendo direttamente a Excel il valore contenuto in E5.

Se invece scrivi:

=INDIRETTO("E5")

prima consegni a Excel il testo E5, poi INDIRETTO lo trasforma nel riferimento alla cella. Il risultato è lo stesso, quindi in questo caso abbiamo soltanto fatto un giro più lungo. L'esempio serve a capire il meccanismo.

Puoi anche scrivere E5, senza il segno uguale, in una cella di appoggio, per esempio B2, e usare:

=INDIRETTO(B2)

A questo punto la formula legge il testo contenuto in B2 e lo usa come indirizzo. Se cambi B2 da E5 a E6, cambia anche la cella letta dalla formula. Qui comincia a esserci qualcosa di utile.

Occhio soltanto a dove punti: se costruisci un riferimento alla stessa cella che contiene la formula, Excel segnala un riferimento circolare.

Leggere una cella da un altro foglio

Un riferimento a una cella di un altro foglio contiene tre pezzi:

1. il nome del foglio;
2. il punto esclamativo !;
3. l'indirizzo della cella.

La stringa:

Dipendenti!E5

significa quindi: cella E5 del foglio Dipendenti.

Possiamo inserirla direttamente nella formula:

=INDIRETTO("Dipendenti!E5")

Oppure possiamo scrivere Dipendenti!E5 in una cella, per esempio B7, e lasciare che sia la formula a leggerla:

=INDIRETTO(B7)

Il secondo caso è più interessante perché separa l'indirizzo dalla formula. Il riferimento rimane visibile in una normale cella e puoi cambiarlo senza smontare una formula più lunga.

Il lavoro si divide così: prima componi la stringa con il nome del foglio e l'indirizzo, poi chiedi a INDIRETTO di interpretarla.

Usare INDIRETTO con un intervallo

Un riferimento non deve per forza identificare una sola cella. Anche F3:F11 è un riferimento, in questo caso a un intervallo di nove celle.

Con Excel 365 puoi scrivere:

=INDIRETTO("F3:F11")

e vedere i valori espandersi nelle celle sottostanti. È il comportamento delle matrici espanse: una sola formula restituisce più risultati.

Se vuoi usare l'intervallo come argomento di SOMMA e sommare i valori da F3 a F11, puoi scrivere:

=SOMMA(INDIRETTO("F3:F11"))

Leggila dall'interno verso l'esterno:

1. "F3:F11" è la stringa che descrive l'intervallo;
2. INDIRETTO la trasforma in un riferimento;
3. SOMMA riceve quel riferimento e somma le celle.

È un buon modo per controllare il ragionamento anche quando non ti interessa visualizzare l'intera matrice. Invece di fissarti sulla formula completa, chiediti sempre che cosa restituisce ogni pezzo.

Nomi e tabelle: riferimenti che si capiscono

Gli indirizzi come F3:F11 funzionano, ma da soli non dicono quali dati contengono. Se riapri il file tra sei mesi, devi andare a vedere quali dati vivono in quelle celle.

Un nome definito o un riferimento strutturato di tabella rende la formula più leggibile. In una tabella chiamata Dipendenti, per esempio, la colonna delle ferie maturate può essere identificata così:

Dipendenti[GG FERIE MATURATI]

Quella stringa può essere trasformata in un riferimento e passata a SOMMA:

=SOMMA(INDIRETTO("Dipendenti[GG FERIE MATURATI]"))

La logica è identica a quella dell'intervallo F3:F11, ma il riferimento ora dice chiaramente che stiamo lavorando sulla colonna GG FERIE MATURATI della tabella Dipendenti.

Se vuoi approfondire la differenza tra un normale intervallo e una tabella, trovi qui il tutorial dedicato alle tabelle in Excel. Prima di lanciarti in formule furbe, dai un nome decente ai tuoi oggetti: è uno dei regali più economici che puoi fare a chi dovrà capire il file, compreso il te stesso del futuro.

Costruire un riepilogo da dodici fogli mensili

Ora mettiamo insieme i pezzi in un esempio da ufficio vero. Abbiamo dodici fogli, da Gennaio a Dicembre, con la stessa struttura. In ogni foglio:

• la cella C2 contiene le entrate totali;
• la cella C3 contiene le spese totali;
• la cella C5 contiene il cash flow.

Nel foglio RiassuntoAnnuale vogliamo disporre i mesi sulle colonne e le tre voci sulle righe. Possiamo preparare la struttura in questo modo:

• da D2 a O2: i nomi dei mesi;
• in B3: il testo C2;
• in B4: il testo C3;
• in B6: il testo C5.

Per leggere le entrate di gennaio, nella cella D3 scriviamo:

=INDIRETTO(D$2&"!"&$B3)

Prima di premere Invio, leggiamo la formula un pezzo alla volta:

1. D$2 restituisce il testo Gennaio;
2. "!" aggiunge il separatore tra foglio e cella;
3. $B3 restituisce il testo C2;
4. l'operatore & concatena i tre pezzi e costruisce Gennaio!C2;
5. INDIRETTO interpreta Gennaio!C2 come riferimento e restituisce il valore della cella.

I simboli $ non sono decorazioni natalizie. In D$2 bloccano la riga delle intestazioni, così trascinando la formula verso il basso continuiamo a leggere il nome del mese dalla riga 2. In $B3, invece, bloccano la colonna che contiene gli indirizzi, mentre il numero di riga può cambiare.

Puoi quindi trascinare la formula verso destra per passare da gennaio a febbraio, marzo e tutti gli altri mesi. Trascinandola sulle righe previste, il riferimento passa da C2 a C3 o C5. Una sola logica copre l'intero riepilogo perché i fogli mensili rispettano la stessa struttura.

Prima di trascinare la formula, sistema la struttura

INDIRETTO non rimette in ordine un file disordinato: interpreta il testo che gli dai. Prima di usarlo in un riepilogo, controlla tre cose:

1. i fogli che vuoi leggere devono seguire la stessa organizzazione;
2. i nomi usati nelle intestazioni devono corrispondere ai nomi dei fogli;
3. gli indirizzi nelle celle di appoggio devono identificare sempre la stessa voce.

Se gennaio ha le entrate in C2, febbraio in F9 e marzo in una cella scelta lanciando una monetina, questo approccio non funziona più e dovremmo sistemare la struttura dei fogli ( o complicare la formula per andare a cercare la cella giusta in modo dinamico).

Evita anche di usare INDIRETTO quando il riferimento è fisso e resterà fisso. Scrivere =INDIRETTO("E5") al posto di =E5 non rende il file più intelligente, lo rende soltanto più difficile da leggere e più lento da elaborare. La funzione guadagna il suo posto quando la stringa cambia in modo controllato.

Come controllare che il riferimento sia corretto

Quando costruisci una stringa con più pezzi, non partire subito dalla formula completa. Usa una cella libera e verifica prima il testo ottenuto dalla concatenazione:

=D$2&"!"&$B3

Se il risultato è Gennaio!C2, la stringa è pronta per essere passata a INDIRETTO. A quel punto puoi completare la formula:

=INDIRETTO(D$2&"!"&$B3)

Infine confronta almeno un valore del riepilogo con la cella originale. Questo controllo richiede pochi secondi e ti evita di trascinare un errore per dodici colonne con grande efficienza, che non è esattamente il tipo di automazione a cui aspiriamo.

Per ricostruire la formula senza copiarla alla cieca, parti sempre dalla struttura ripetuta, scrivi in chiaro la stringa che identifica la cella e soltanto dopo passala a INDIRETTO. In questo modo una sola formula copre i dodici mesi e sai dove intervenire quando cambia un'intestazione o un indirizzo.

Ora apri un file con fogli ripetuti, scegli una voce comune e prova a costruire il primo riferimento in una cella di appoggio. Quando il testo è corretto, racchiudilo dentro INDIRETTO e confronta il risultato con la cella originale.

Se Excel l'hai imparato a pezzi, cercando su internet la formula che serve quel giorno, Excenziale è il mio corso Excel: dalle basi a Power Query passando per le tabelle pivot, con una progressione didattica studiata nei dettagli.