Funzione INDIRETTA in Excel
La funzione indiretta in Excel è una funzione incorporata che viene utilizzata per fare riferimento e ottenere valori di cella da una stringa di testo, questa formula prende riferimento dalla cella di riferimento, ha due argomenti il primo argomento non è facoltativo mentre il secondo argomento è facoltativo a cui si riferisce il tipo di corrispondenza, questa funzione può essere utilizzata anche con un'altra formula.
La funzione INDIRETTA in Excel ha molteplici utilizzi. Restituisce il riferimento di cella specificato da una stringa di testo. Ad esempio, se la cella B1 contiene una stringa, supponiamo che l'intervallo c4 e c4 abbia il valore "Jessica". Ora, se useremo la funzione INDIRETTO e passeremo il testo di riferimento come B1, restituirà il valore contenuto dall'intervallo di celle c4 che è "Jessica".
La funzione INDIRETTO di Excel ti consente di specificare un indirizzo di cella INDIRETTAMENTE. Come puoi nell'esempio di Excel INDIRETTO sopra, se la cella B1 contiene il testo C4, questa formula Excel indiretta restituisce il contenuto della cella C4 ( Jessica ). Questa funzione è una funzione estremamente utile. È possibile utilizzare la funzione INDIRETTO ogni volta che si desidera modificare il riferimento a una cella all'interno di una formula Excel indiretta senza modificare la formula stessa.
Formula Excel INDIRETTA
Spiegazione
Ref_text è un riferimento a una cella che contiene un riferimento in stile A1, un riferimento in stile R1C1, un nome definito come riferimento o un riferimento a una cella come stringa di testo.
a1 argomento è facoltativo
| a1 | Falso o 0, quando ref_text è in stile R1C1 |
| a1 | Omissis o True (1) quando ref_text è in stile A1 |
Come utilizzare la funzione INDIRETTO in Excel?
Facciamo un esempio di Excel INDIRETTO prima di utilizzare la funzione indirizzo nella cartella di lavoro di Excel:
Supponiamo che per un sito Web www.xyz.com disponiamo dei dati di Google Analytics per i visitatori del sito Web da diversi canali di pubblico per cinque diversi paesi, come mostrato di seguito nella tabella:
Possiamo utilizzare la funzione INDIRETTO in Excel allo scopo di convertire una stringa di testo in un riferimento, quindi facendo riferimento alla cella H4 e utilizzando quel nome come riferimento. L'uso della funzione INDIRETTO in Excel, in questo caso, consiste nel fare riferimento a intervalli di nomi utilizzando i valori di una cella, quindi nella cella I4, potremmo semplicemente utilizzare la funzione somma per calcolare il numero totale di visitatori da un paese Canada.
Ora, possiamo calcolare direttamente la somma dei visitatori di ciascun paese utilizzando la funzione somma, ma utilizzando la funzione INDIRETTO in Excel, possiamo calcolare la somma dei visitatori di diversi paesi modificando il riferimento del nome piuttosto che cambiare la stessa formula Excel indiretta.
Abbiamo creato l'intervallo di nomi per ogni visitatore del paese; questo può essere fatto facilmente digitando l'intervallo di nomi nello spazio appena sotto gli appunti, come mostrato di seguito.
Un altro metodo è che puoi andare a formule-> gestore dei nomi -> seleziona l'intervallo che desideri nominare -> fai clic su gestore dei nomi -> digita l'intervallo di nomi.
Allo stesso modo, ha creato un intervallo denominato in Excel anche per i visitatori di altri paesi.
Ora, in I4, calcolando la somma del visitatore del paese Canada utilizzando la funzione INDIRETTO e utilizzando l'intervallo di nomi in H4 (Canada, un nome sopra per F3: F7), otteniamo la somma totale di un visitatore.
Se creiamo un elenco di dati utilizzando la convalida dei dati per i paesi e applichiamo tale elenco a H4
E cambiando i nomi dei diversi paesi dal Canada all'India, agli Stati Uniti, all'Australia o a Singapore, otteniamo la somma totale del visitatore in I4 utilizzando la funzione INDIRETTO in Excel.
Quindi, per ogni caso, non stiamo cambiando la formula Excel indiretta, che si trova nella cella I4; stiamo cambiando il riferimento del nome in H4 e calcolando la somma dei visitatori per ogni paese.
Possiamo fare un ulteriore passo avanti e possiamo fare riferimento a nomi a livello di foglio di lavoro anche utilizzando la funzione INDIRETTO in Excel. Ad esempio, ora utilizzeremo la funzione INDIRETTO in riferimento a diversi fogli nella cartella di lavoro.
Abbiamo creato diversi fogli con i nomi dei paesi per il numero totale di vendite effettuate con ciascun canale di ricerca per diversi paesi.
Per l'India,
Per gli USA,
Allo stesso modo, per altri paesi.
Anche in questo caso, calcoleremo le vendite totali effettuate attraverso ciascun canale utilizzando la funzione INDIRETTO in Excel per diversi fogli con i nomi dei paesi come riferimento.
In questo caso, poiché abbiamo dati di vendita in fogli diversi, dovremo concatenare diversi elementi che sono l'intervallo di dati, quindi il ref_text inizierà con il nome del foglio, che è India in questo caso in B4, quindi lo faremo usa l'operatore & chiamato e commerciale che viene utilizzato per la concatenazione.
Quindi, otteniamo la somma totale delle vendite per l'India; ora, se cambiamo il nome del paese del foglio in B4 dall'India ad altri paesi come USA, Singapore o Canada, otterremo il valore di vendita totale di quei paesi.
Ancora una volta, in questo caso, abbiamo semplicemente modificato i valori ref_text nella cella B4 senza modificare la principale formula Excel indiretta, che è in C4, per ottenere la somma delle vendite per diversi paesi che si trovano in fogli diversi utilizzando la funzione INDIRETTO.
Ora useremo la funzione INDIRETTO usando il riferimento R1C1 per ottenere il valore di vendita dell'ultimo mese. Abbiamo vendite per mesi diversi nelle colonne B, C e D per tre mesi gennaio, febbraio e marzo che possono aumentare fino a mesi come aprile, maggio, giugno e così via. Quindi, la tabella di vendita crescerà sempre a seconda dell'aggiunta della vendita del mese imminente. Quindi, in questo esempio di Excel INDIRETTO, vogliamo calcolare la vendita dell'ultimo mese.
Quindi, qui l'ultimo mese è marzo e vogliamo calcolare la vendita totale del mese di marzo, che è $ 249.344, utilizzando la funzione INDIRETTO.
In questo esempio di Excel INDIRETTO, troveremo l'uso del secondo argomento della funzione INDIRETTO
INDIRETTO (ref_text, a1)
a1: che è un argomento opzionale, è un valore logico (un valore booleano) che specifica quale tipo di riferimento (ref_text) è contenuto nella cella
se a1 è vero (1) o omesso, ref_text è considerato un riferimento in stile A1
se a1 è falso (0), ref_text viene interpretato come uno stile R1C1
In che modo lo stile A1 è diverso dallo stile R1C1?
Normalmente, la funzione INDIRETTO in Excel utilizza la notazione A1. Ogni indirizzo di cella è costituito da una lettera di colonna e un numero di riga. Tuttavia, la funzione INDIRETTO in Excel supporta anche la notazione R1C1. In questo sistema, la cella A1 è indicata come cella R1C1, cella A2 come R2C1 e così via.
Per passare alla notazione R1C1, scegliere File-> opzione per aprire la finestra di dialogo delle opzioni di Excel, fare clic sulla scheda della formula e un segno di spunta sull'opzione dello stile di riferimento R1C1. Ora noterai che le lettere delle colonne cambiano tutte in numeri. E anche tutti i riferimenti di cella nelle tue formule si adattano.
Quindi, quando usiamo lo stile R1C1 nella funzione INDIRETTO in Excel, passiamo il valore a1 come falso e, se usiamo lo stile A1, passiamo il valore true ad a1.
Ora, in questo esempio di Excel INDIRETTO, utilizzeremo lo stile R1C1 a causa del requisito in quanto le colonne con il mese sarebbero in crescita.
Utilizzando lo stile R1C1, il valore della vendita totale è nella riga 10 e l'ultimo valore della colonna, che si trova nella colonna D, verrà calcolato utilizzando la funzione CONTA.VALORI , che fornirà il numero dell'ultima colonna contenente un valore. Quindi, count (10:10) in questo caso darà 4, che è il numero di colonna nello stile R1C1. Quindi, abbiamo contattato quel valore per ottenere R10C4 che contiene il valore desiderato che è il valore di vendita totale dell'ultimo mese ($ 249.344).
E, poiché qui stiamo usando lo stile R1C1, passeremo un valore falso per l'argomento a1.
Ora, aggiungiamo un'altra colonna con i saldi del mese di aprile.
Quindi, ottieni nuovi valori di vendita del mese scorso corrispondenti a quelli dell'ultimo mese di aprile. Allo stesso modo, se aggiungeremo più mesi, otterremo sempre un nuovo valore di vendita del mese scorso senza modificare la formula Excel indiretta per ogni mese. Questa è la specialità della funzione INDIRETTA in Excel.
Come personalizzare l'array della tabella nella funzione CERCA.VERT INDIRETTO di Excel?
Abbiamo cinque paesi e un canale di ricerca e la vendita che il sito web ha effettuato attraverso questi diversi canali di diversi paesi. Ora, nella cella B3, vogliamo ottenere la vendita effettuata tramite ricerche organiche dal paese degli Stati Uniti.
Per ogni tabella per diversi paesi, abbiamo denominato ogni tabella con un nome breve per il paese, Ind per l'India, Stati per gli Stati Uniti, Can per il Canada e così via .
Se usiamo direttamente la funzione vlookup di Excel INDIRETTO per ottenere il valore, come fatto di seguito
Otteniamo un errore perché la funzione vlookup di Excel INDIRETTO non è in grado di riconoscere l'array di tabella passato come valore della cella B2, quindi useremo la funzione INDIRETTO in Excel per fare in modo che vlookup ottenga il table_array riconosciuto come argomento valido.
Se modifichiamo i valori di B1 e B2, otterremo valori di vendita diversi in B3, come richiesto.
Nota: gli intervalli di nomi non fanno distinzione tra maiuscole e minuscole. Quindi - Ind, IND e InD sono tutti uguali per Excel.
Nel prossimo esempio INDIRETTO di Excel, utilizzeremo la funzione INDIRETTO in Excel per creare un elenco dipendente. Abbiamo un elenco della posizione di un dipendente in un'azienda; quindi, per ogni posizione, abbiamo i nomi dei dipendenti.
Ora, nella cella I2, creeremo un elenco per i diversi ranghi dei dipendenti come loro posizioni, e in I3, mostreremo un elenco che dipenderà dal valore in I2. Se I2 è un regista, I3 dovrebbe riflettere i nomi dei registi che Andrew e Micheal; se I2 è Manager, I3 dovrebbe riflettere i nomi del manager che è Camille, David, Davis e William e allo stesso modo per il supervisore.
In questo caso, ancora una volta, useremo la funzione INDIRETTO in Excel per creare l'elenco dipendente. Convalida dei dati, nelle impostazioni che selezionano i criteri di convalida come un elenco e nel campo sorgente, utilizzeremo la funzione INDIRETTO in Excel come segue.
= INDIRETTO ($ I $ 2)
Quando selezioniamo i nomi dei direttori in I3 si riflette come un elenco con i nomi Andrew e Micheal, quando I2 è Manager, I3 riflette i nomi del manager Camille, David, Davis e William e in modo simile per il supervisore come mostrato di seguito nella figura.
Cose da ricordare
- L'argomento ref_text deve essere un riferimento di cella valido, altrimenti INDIRETTO restituirà #REF! Errore. L'argomento ref_text può fare riferimento a un'altra cartella di lavoro.
- Se ref_text fa riferimento a un'altra cartella di lavoro (un riferimento esterno), l'altra cartella di lavoro deve essere aperta. Se la cartella di lavoro di origine non è aperta, INDIRETTO restituisce #RIF! Valore di errore.
- Se ref_text fa riferimento a un intervallo di celle al di fuori del limite di riga di 1.048.576 o del limite di colonna di 16.384 (XFD), INDIRETTO restituisce un #RIF! Errore.
