=INDIRETTO(E2) legge la cella il cui indirizzo è scritto come testo in E2. Se E2 dice C4, la formula restituisce il valore di C4. L'indirizzo si può anche costruire a pezzi: =INDIRETTO("C"&E3) legge la colonna C alla riga indicata in E3. INDIRETTO si chiama INDIRECT in Excel inglese, ed è così che la mostra la tabella; puoi scrivere le formule anche in italiano, con il punto e virgola.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Address | Value | |
| 2 | Apple | Fruit | $1.20 | C4 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | 6 | $1.10 | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
=INDIRETTO(E2)F2 legge C4, il prezzo di Carrot, $0.80. Cambia E2 in C3 o B5 e F2 segue. F3 unisce "C" e il 6 di E3 nell'indirizzo C6 e restituisce $1.10. Cambia E3 in 2 per il prezzo di Apple.
Sintassi di INDIRETTO
=INDIRECT(ref_text, [a1])
ref_text(rif): un testo che scrive un riferimento:"C4","B2:B6","Prices!A2","'Price list'!A2:B9".a1:VEROoppure omesso per gli indirizzi in stile A1.FALSOlegge lo stile R1C1, in cui"R4C3"significa riga 4, colonna 3, adatto quando riga e colonna sono entrambe numeri.
Se il testo non è un indirizzo valido, il risultato è #RIF! (in inglese #REF!). INDIRETTO restituisce un riferimento vero, quindi funziona dentro SOMMA, CONTA.SE, CERCA.VERT e ogni funzione che accetta un intervallo.
Costruire un intervallo a partire da numeri
L'indirizzo può essere un intero intervallo. Unendoci un numero si ottiene un intervallo la cui dimensione viene da una cella.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Rows | Total | ||
| 2 | Jan | 4,200 | 3 | 12,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 |
=SOMMA(INDIRETTO("B2:B"&(1+E2)))Con 3 in E2 il testo diventa B2:B4, e F2 somma da Jan a Mar: 12,900. Imposta E2 a 6 per il semestre, 27,900. 1+E2 c'è perché i dati partono dalla riga 2. In Excel italiano la formula è =SOMMA(INDIRETTO("B2:B"&(1+E2))). Lo stesso totale si può scrivere senza INDIRETTO, =SOMMA(B2:INDICE(B2:B7;E2)), che non è volatile; la pagina su SCARTO confronta le opzioni.
Fare riferimento a un foglio nominato in una cella
Anche il nome del foglio può venire da una cella. Così una sola formula di riepilogo diventa una ricerca tra fogli: ogni riga legge il foglio nominato nella colonna A. Gli apici singoli attorno al nome la fanno funzionare anche con i nomi che contengono spazi.
| A | B | |
|---|---|---|
| 1 | Month | Total |
| 2 | Jan | 12,500 |
| 3 | Feb | 12,200 |
| 4 | Mar | 13,700 |
=SOMMA(INDIRETTO("'"&A2&"'!B2:B4"))B2 costruisce il testo 'Jan'!B2:B4 e lo somma: 12,500. B3 e B4 sono la stessa formula copiata verso il basso, quindi leggono Feb (12,200) e Mar (13,700). Apri la scheda Feb e cambia un numero: il riepilogo segue. Scrivi Feb al posto di Jan in A2 e B2 ora somma Feb. Il B2:B4 tra virgolette è testo, quindi non cambia quando la formula viene copiata verso il basso; cambia solo il riferimento ad A2.
Elenchi a discesa dipendenti
Un secondo elenco a discesa le cui voci dipendono dal primo è il lavoro classico di INDIRETTO. In Excel la preparazione abituale è:
- Metti le voci di ogni categoria in una colonna e dai a ogni intervallo il nome della sua categoria: seleziona le colonne con le intestazioni e usa Formule > Crea da selezione > Riga superiore. Così si creano i nomi
Fruit,VegetableeDairy. - Dai ad A2 un elenco delle categorie: Dati > Convalida dati > Consenti: Elenco, Origine
Fruit;Vegetable;Dairy. - Dai a B2 un elenco con Origine
=INDIRETTO(A2). Quando A2 dice Fruit, l'elenco legge l'intervallo chiamato Fruit.
La tabella qui sotto costruisce la stessa cosa con un foglio per categoria invece di un intervallo denominato. D2 usa INDIRETTO per espandere le voci del foglio nominato in A2, e l'elenco di B2 legge D2:D4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Item | Items for the category | |
| 2 | Fruit | Apple | Apple | |
| 3 | Pear | |||
| 4 | Plum |
=INDIRETTO("'"&A2&"'!A2:A4")Scegli Dairy in A2: D2:D4 passa a Milk, Butter, Cheese, e lo stesso fanno le scelte in B2. B2 mantiene il suo vecchio valore finché non ne scegli uno nuovo; Excel si comporta allo stesso modo, ed è per questo che i moduli aggiungono spesso un controllo come =CONTA.SE(D2:D4;B2)>0 accanto alla voce. In Excel 365 puoi fare a meno degli intervalli denominati e puntare il secondo elenco su una formula espansa, per esempio =INDIRETTO("'"&A2&"'!A2:A4") in una cella di appoggio e =D2# come Origine. La pagina sull'elenco a discesa spiega il resto della preparazione.
INDIRETTO è volatile, e ignora le righe inserite
Due effetti collaterali derivano dal fatto che INDIRETTO legge un testo invece di un riferimento:
- Si ricalcola a ogni modifica. Excel non può sapere a quali celle punterà un testo, quindi ricalcola ogni INDIRETTO dopo qualsiasi modifica in qualsiasi punto della cartella di lavoro. Qualche decina non fa danni; decine di migliaia rendono lento ogni tasto premuto. INDICE con un numero di riga (
=INDICE(C:C;E3)) dà lo stesso risultato di=INDIRETTO("C"&E3)e si ricalcola solo quando cambiano i suoi input. - L'indirizzo non si sposta. Inserisci una riga sopra la riga 4 e
=C4diventa=C5, ma=INDIRETTO("C4")legge ancora C4, che ora è un'altra riga. A volte è proprio lo scopo, un riferimento che deve restare su una cella fissa qualunque cosa succeda al foglio. Più spesso è un errore che aspetta solo che qualcuno inserisca una riga.
INDIRETTO verso un'altra cartella di lavoro funziona solo mentre quella cartella è aperta; chiusa, restituisce #RIF!.
Esercizio: un prezzo da un numero di riga
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Price | |
| 2 | Apple | Fruit | $1.20 | 5 | ||
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Tocca a te: In F2, usa INDIRETTO per restituire il prezzo nella colonna C alla riga il cui numero è scritto in E2.
Domande frequenti
Cosa fa INDIRETTO in Excel?
Trasforma un testo in un riferimento. =INDIRETTO("C4") restituisce il valore di C4, e =INDIRETTO(E2) restituisce il valore di qualunque indirizzo di cella sia scritto in E2. L'indirizzo si può costruire con &, quindi =INDIRETTO("C"&E2) legge la colonna C alla riga indicata in E2.
Come si fa riferimento a un altro foglio il cui nome è in una cella?
Costruisci l'indirizzo con il nome del foglio tra apici singoli: =INDIRETTO("'"&A2&"'!B2"). Gli apici la fanno funzionare anche con i nomi che contengono spazi. =SOMMA(INDIRETTO("'"&A2&"'!B2:B4")) somma un intervallo su quel foglio.
Perché INDIRETTO restituisce #RIF!?
Il testo non è un indirizzo valido, oppure nomina un foglio che non esiste, oppure punta a un'altra cartella di lavoro che è chiusa. Controlla il testo che la formula costruisce mettendo la stessa espressione da sola in una cella, senza INDIRETTO.
INDIRETTO è volatile?
Sì. Excel ricalcola ogni INDIRETTO a ogni modifica in qualsiasi punto della cartella di lavoro, perché non può sapere in anticipo a quali celle punterà il testo. Poche formule non fanno danni; migliaia rallentano la cartella di lavoro. Spesso INDICE può fare lo stesso lavoro senza essere volatile.
