Scarica la cartella di lavoro di esempio
Questo tutorial mostra come usare il Funzione INDIRETTA di Excel in Excel per creare un riferimento di cella dal testo.
Panoramica della funzione INDIRETTA
La funzione INDIRETTO Crea un riferimento di cella da una stringa di testo.
(Notare come appaiono gli input della formula)
Funzione INDIRETTO Sintassi e ingressi:
| 1 | =INDIRETTO(testo_rif,C1) |
ref_text - Una stringa che rappresenta un riferimento di cella o di intervallo. La stringa può essere in formato R1C1 o A1 oppure può essere un intervallo denominato.
a1 - FACOLTATIVO: Indica se il riferimento è in formato R1C1 o A1. FALSO per R1C1 o VERO / Omesso per A1.
Cos'è la funzione INDIRETTA?
La funzione INDIRETTO consente di fornire una stringa di testo e fare in modo che il computer interpreti quella stringa come un riferimento effettivo. Questo può essere usato per fare riferimento a un intervallo sullo stesso foglio, un foglio diverso o anche una cartella di lavoro diversa.
ATTENZIONE: La funzione INDIRETTA è una delle funzioni volatili. La maggior parte delle volte quando lavori nel tuo foglio di calcolo, il computer ricalcolerà una formula solo se gli input hanno cambiato i loro valori. Una funzione volatile, tuttavia, ricalcola ogni volta che apporti una modifica a qualsiasi cella. È necessario prestare attenzione per assicurarsi di non causare un tempo di ricalcolo elevato a causa dell'uso eccessivo della funzione volatile o della presenza di molte celle dipendenti dal risultato di una funzione volatile.
Crea un riferimento di cella
Dì che vuoi recuperare il valore da A2, ma vuoi assicurarti che la tua formula resta su A2 indipendentemente dall'inserimento/rimozione di nuove righe. Potresti scrivere una formula di
| 1 | =INDIRETTO("A2") |
Nota che l'argomento all'interno della nostra funzione è la stringa di testo "A2" e non un riferimento di cella. Inoltre, poiché si tratta di una stringa di testo, non è necessario indicare un riferimento assoluto come $A$2. Il testo non cambierà mai, e quindi questa formula punterà sempre ad A2, indipendentemente da dove viene spostata.
INDIRETTO numero di riga
Puoi concatenare stringhe di testo e valori dalle celle insieme. Invece di scrivere "A2" come abbiamo fatto in precedenza, possiamo prendere un valore numerico dalla cella B2 e usarlo nella nostra formula. Scriviamo la formula come
| 1 | =INDIRETTO("A" & B2) |
Il simbolo "&" viene utilizzato qui per concatenare la stringa di testo "A" con il valore della cella B2. Quindi, se il valore di B2 fosse attualmente 10, la nostra formula lo leggerebbe come
| 123 | =INDIRETTO("A" & 10)=INDIRETTO("A10")=A10 |
Valore colonna INDIRETTO
Puoi anche concatenare nel riferimento di colonna. Questa volta diciamo che sappiamo di voler prendere un valore dalla riga 10, ma vogliamo essere in grado di cambiare la colonna da cui estrarre. Metteremo la lettera della colonna che vogliamo nella cella B2. La nostra formula potrebbe assomigliare
| 1 | =INDIRETTO(B2 & "10") |
Se il valore di B2 è "G", la nostra formula valuta in questo modo
| 123 | =INDIRETTO("V" & 10)=INDIRETTO("G10")=Sol10 |
INDIRETTO stile r1c1
Nel nostro esempio precedente, dovevamo usare una lettera per indicare il riferimento alla colonna. Questo perché stavamo usando ciò che è noto come riferimento in stile A1. Nello stile A1, le colonne sono date da una lettera e le righe sono date da numeri. I riferimenti assoluti sono indicati utilizzando il "$" prima dell'elemento che vogliamo rimanere assoluto.
In r1c1, sia le righe che le colonne vengono avviate utilizzando il numero. Il riferimento assoluto ad a1 verrebbe scritto come
| 1 | =R1C1 |
Puoi leggerlo come "Riga 1, Colonna 1". I riferimenti relativi sono dati utilizzando le parentesi, ma il numero indica la posizione relativo alla cella con formula. Quindi, se stessimo scrivendo una formula nella cella A10 e abbiamo bisogno di fare riferimento ad A1, scriveremmo la formula
| 1 | =R[-9]C |
Puoi leggerlo come "La cella 9 è in alto, ma nella stessa colonna.
Il motivo per cui questo potrebbe essere utile è che INDIRETTO può supportare l'uso della notazione r1c1. Considera l'esempio precedente in cui stavamo recuperando un valore dalla riga 10 ma volevamo essere in grado di modificare la colonna. Piuttosto che dare una lettera, diciamo di inserire un numero nella cella B2. La nostra formula quindi potrebbe sembrare
| 1 | =INDIRETTO("R10C" & B2, FALSO) |
Abbiamo omesso il 2ns argomento fino ad ora. Se questo argomento viene omesso o True, la funzione valuterà utilizzando lo stile A1. Poiché è False, valuterà in r1c1. Supponiamo che il valore di B2 sia 5. La nostra formula valuterà questo in questo modo
| 12 | =INDIRETTO("R10C5", FALSO)=$E$10 |
Differenze INDIRETTE con A1 vs r1c1
Ricordi che in precedenza abbiamo mostrato che poiché il contenuto di questa formula era una stringa di testo, non è mai cambiato?
| 1 | =INDIRETTO("A2") |
Questa formula guarderà sempre la cella A2, indipendentemente da dove sposti la formula. In r1c1, poiché puoi indicare la posizione relativa usando le parentesi, questa regola non rimane coerente. Se inserisci questa formula nella cella B2
| 1 | =INDIRETTO("RC[-1]") |
Guarderà la cella A2 (poiché la colonna A è una a sinistra della colonna B). Se copi questa formula nella cella B3, il testo all'interno rimarrà lo stesso, ma INDIRETTO ora guarderà la cella A3.
INDIRETTO con nome foglio
Puoi anche combinare un nome di foglio nei tuoi riferimenti INDIRETTI. Una regola importante da ricordare è che dovresti inserire virgolette singole attorno ai nomi e devi separare il nome del foglio dal riferimento di cella con un punto esclamativo.
Diciamo che abbiamo avuto questa configurazione, in cui stiamo affermando il nome del foglio, la riga e la colonna.
La nostra formula per combinare tutti questi elementi in un riferimento sarebbe simile a questa:
| 1 | =INDIRETTO("'" & A2 & "'!" & B2 & C2) |
La nostra formula verrà quindi valutata in questo modo:
| 123 | =INDIRETTO("'" & "Foglio2" & "'!" & "B" & "5")=INDIRETTO("'"Foglio2'!B5")='Foglio2'!B5 |
Tecnicamente, poiché la parola "Foglio2" non contiene spazi, noi non lo facciamo bisogno le virgolette singole. È perfettamente valido scrivere qualcosa come
| 1 | =Foglio2!A2 |
Tuttavia, non fa male mettere le virgolette quando non ne hai bisogno. È consigliabile includerli in modo che la formula possa gestire l'istanza in cui potrebbero essere necessari.
INDIRETTO a un'altra cartella di lavoro
Menzioneremo anche che INDIRETTO può creare un riferimento a una cartella di lavoro diversa. La limitazione è che INDIRETTO non recupererà i valori da una cartella di lavoro chiusa, quindi questo uso particolare ha una praticità limitata. Se la cartella di lavoro a cui punta INDIRETTO non è aperta, la funzione genererà un "#REF!" errore.
La sintassi quando si scrive il nome della cartella di lavoro è che deve essere tra parentesi quadre. Usiamo questa configurazione e proviamo a recuperare un valore dalla cella C7.
La nostra formula sarebbe
| 1 | =INDIRETTO("'[" & A2 & "]" & B2 & "'!C7") |
Ancora una volta, presta attenzione alla posizione delle virgolette singole, delle parentesi quadre e del punto esclamativo. La nostra formula verrà quindi valutata in questo modo:
| 123 | =INDIRETTO("'[" & "Campione.xlsx" & "]" & "Riepilogo" & "'!C7")=INDIRETTO("'[Campione.xslx]Riepilogo'!C7")='[Campione.xlsx]Riepilogo'!C7 |
INDIRETTO per costruire la gamma dinamica
Quando si dispone di un set di dati di grandi dimensioni, è importante cercare di ottimizzare le formule in modo che non svolgano più lavoro del necessario. Ad esempio, invece di fare riferimento a tutta la colonna A, potremmo voler fare riferimento solo al numero esatto di celle nel nostro elenco. Considera il seguente layout:
Nella cella B2, abbiamo inserito la formula
| 1 | =CONTA.VALORI(A:A) |
La funzione CONTA.VALORI è molto facile da calcolare per il computer, poiché controlla semplicemente quante celle in col A hanno un valore, invece di dover eseguire controlli logici o operazioni matematiche.
Ora, costruiamo la nostra formula che sommerà i valori nella colonna A, ma vogliamo assicurarci che guardi solo all'intervallo esatto con i valori (A2: A5). Scriveremo la nostra formula come
| 1 | =SOMMA(INDIRETTO("A2:A" & B2)) |
Il nostro INDIRETTO prenderà il numero 5 dalla cella B2 e creerà un riferimento all'intervallo A2:A5. La SOMMA può quindi utilizzare questo intervallo per il suo calcolo. Se aggiungiamo un altro valore nella cella A6, il numero in B2 verrà aggiornato e anche la nostra formula SUM si aggiornerà automaticamente per includere questo nuovo valore.
ATTENZIONE: con l'introduzione delle tabelle in Office 2007, è molto più efficiente archiviare i dati in una tabella e utilizzare un riferimento strutturale anziché creare la formula utilizzata in questo esempio a causa della natura volatile di INDIRETTO. Tuttavia, potrebbero essere casi in cui è necessario creare un elenco di elementi e non è possibile utilizzare una tabella.
Grafici dinamici con INDIRECT
Prendiamo l'esempio precedente e facciamo un altro passo. Invece di scrivere una formula per darci la somma dei valori, creeremo un intervallo con nome. Potremmo chiamare questo intervallo "MyData" e fare riferimento a
| 1 | =INDIRETTO("A2:A" & CONTA.VALORI($A:$A)) |
Nota che poiché lo stiamo inserendo in un intervallo denominato, abbiamo scambiato il riferimento a B2 e invece abbiamo inserito direttamente la funzione CONTA.VALORI.
Ora che abbiamo questo intervallo denominato, potremmo usarlo in un grafico. Creeremo un grafico a linee vuoto, quindi aggiungeremo una serie di dati. Per i valori della serie, potresti scrivere qualcosa del tipo
| 1 | =Foglio1!MieiDati |
Il grafico ora utilizzerà questo riferimento per tracciare i valori. Man mano che vengono aggiunti più valori alla colonna A, INDIRETTO farà riferimento a un intervallo sempre più ampio e il nostro grafico continuerà a rimanere aggiornato con tutti i nuovi valori aggiunti.
Validazione dinamica dei dati con INDIRECT
Quando si raccolgono input dagli utenti, a volte è necessario fare in modo che le opzioni di una scelta tra cui scegliere dipendano da una scelta precedente. Considera questo layout, in cui la nostra prima colonna consente all'utente di scegliere tra frutta, verdura e carne.
Nel 2ns colonna, non vogliamo avere un elenco lungo che mostra tutte le scelte possibili, poiché abbiamo già ristretto un po' le cose. Quindi, abbiamo creato altri 3 elenchi che assomigliano a questo:
Successivamente, assegneremo ciascuno di queste elenchi a un intervallo denominato. Cioè, tutti i frutti saranno in una gamma chiamata "Frutta", e le verdure in "Verdure", ecc.
Tornati nella nostra tabella, siamo pronti per impostare la convalida dei dati nel 2ns colonna. Creeremo una convalida del tipo di elenco, con un input di:
| 1 | =INDIRETTO(A2) |
L'INDIRETTO va a leggere la scelta fatta in col A e vedere il nome di una categoria. Abbiamo definito intervalli con questi nomi, quindi INDIRETTO prenderà quel nome e creerà un riferimento all'intervallo desiderato.
Note aggiuntive
Utilizzare la funzione INDIRETTO per creare un riferimento di cella dal testo.
Per prima cosa crea la stringa di testo che rappresenta un riferimento di cella. La stringa deve essere nella consueta lettera di colonna in stile A1 e numero di riga (M37) o in stile R1C1 (R37C13). Puoi digitare direttamente il riferimento, ma di solito farai riferimento alle celle che definiscono le righe e le colonne. Infine, inserisci il formato di riferimento della cella che scegli. VERO o omesso per il riferimento in stile A1 o FALSE per lo stile R1C1.
Mentre lavori con le formule INDIRETTE, potresti voler usare il Funzione RIGA per ottenere il numero di riga di un riferimento o il Funzione COLONNA per ottenere il numero di colonna (non lettera) di un riferimento.
Torna all'elenco di tutte le funzioni in Excel
INDIRETTO in Fogli Google
La funzione INDIRETTA funziona esattamente allo stesso modo in Fogli Google come in Excel:
