Contenuti
Congratulazioni! Sei arrivato all'ultimo giorno della maratona 30 funzioni di Excel in 30 giorni. È stato un viaggio lungo e interessante durante il quale hai imparato molte cose utili sulle funzioni di Excel.
Il 30° giorno della maratona dedicheremo lo studio della funzione INDIRETTI (INDIRETTO), che restituisce il collegamento specificato dalla stringa di testo. Con questa funzione è possibile creare elenchi a discesa dipendenti. Ad esempio, quando si seleziona un paese da un elenco a discesa determina quali opzioni verranno visualizzate nell'elenco a discesa della città.
Quindi, diamo un'occhiata più da vicino alla parte teorica della funzione INDIRETTI (INDIRETTO) ed esplorare esempi pratici della sua applicazione. Se hai ulteriori informazioni o esempi, condividili nei commenti.
Funzione 30: INDIRETTO
Funzione INDIRETTI (INDIRETTO) restituisce il collegamento specificato dalla stringa di testo.
Come puoi usare la funzione INDIRETTO?
Dal momento che la funzione INDIRETTI (INDIRETTO) restituisce un collegamento dato da una stringa di testo, puoi usarlo per:
- Crea un collegamento iniziale non mobile.
- Crea un riferimento a un intervallo denominato statico.
- Crea un collegamento utilizzando le informazioni su foglio, riga e colonna.
- Crea una matrice di numeri non mobile.
Sintassi INDIRETTO (INDIRETTO)
Funzione INDIRETTI (INDIRETTO) ha la seguente sintassi:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_testo (link_to_cell) è il testo del collegamento.
- a1 – se uguale a TRUE (TRUE) o non specificato, verrà utilizzato lo stile del collegamento A1; e se FALSE (FALSE), allora lo stile R1C1.
Trappole INDIRETTO (INDIRETTO)
- Funzione INDIRETTI (INDIRETTO) viene ricalcolato ogni volta che cambiano i valori nel foglio di lavoro di Excel. Ciò può rallentare notevolmente la cartella di lavoro se la funzione viene utilizzata in molte formule.
- Se la funzione INDIRETTI (INDIRETTO) crea un collegamento a un'altra cartella di lavoro di Excel, tale cartella di lavoro deve essere aperta o la formula segnalerà un errore #RIF! (#COLLEGAMENTO!).
- Se la funzione INDIRETTI (INDIRETTO) fa riferimento a un intervallo che supera il limite di righe e colonne, la formula segnalerà un errore #RIF! (#COLLEGAMENTO!).
- Funzione INDIRETTI (INDIRETTO) non può fare riferimento a un intervallo denominato dinamico.
Esempio 1: creare un collegamento iniziale non mobile
Nel primo esempio, le colonne C ed E contengono gli stessi numeri, le loro somme calcolate utilizzando la funzione SUM (SOMMA) sono anche gli stessi. Tuttavia, le formule sono leggermente diverse. Nella cella C8, la formula è:
=SUM(C2:C7)
=СУММ(C2:C7)
Nella cella E8, la funzione INDIRETTI (INDIRETTO) crea un collegamento alla cella iniziale E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Se inserisci una riga nella parte superiore del foglio e aggiungi il valore di gennaio (gennaio), l'importo nella colonna C non cambierà. La formula cambierà, reagendo all'aggiunta di una riga:
=SUM(C3:C8)
=СУММ(C3:C8)
Tuttavia, la funzione INDIRETTI (INDIRETTO) fissa E2 come cella di inizio, quindi gennaio viene automaticamente incluso nel calcolo dei totali della colonna E. La cella finale è stata modificata, ma la cella iniziale non è stata modificata.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Esempio 2: collegamento a un intervallo denominato statico
Funzione INDIRETTI (INDIRETTO) può creare un riferimento a un intervallo denominato. In questo esempio, le celle blu costituiscono l'intervallo Lista Num. Inoltre, viene creato anche un intervallo dinamico dai valori nella colonna B NumListDin, a seconda del numero di numeri in questa colonna.
La somma per entrambi gli intervalli può essere calcolata semplicemente dando il suo nome come argomento alla funzione SUM (SOMMA), come puoi vedere nelle celle E3 ed E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Invece di digitare un nome di intervallo in una funzione SUM (SOMMA), puoi fare riferimento al nome scritto in una delle celle del foglio di lavoro. Ad esempio, se il nome Lista Num è scritto nella cella D7, quindi la formula nella cella E7 sarà così:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Purtroppo la funzione INDIRETTI (INDIRETTO) non può creare un riferimento all'intervallo dinamico, quindi quando copi questa formula nella cella E8, riceverai un errore #RIF! (#COLLEGAMENTO!).
Esempio 3: creare un collegamento utilizzando le informazioni su foglio, riga e colonna
È possibile creare facilmente un collegamento in base ai numeri di riga e colonna, nonché utilizzare il valore FALSO (FALSO) per il secondo argomento della funzione INDIRETTI (INDIRETTO). Questo è il modo in cui viene creato il collegamento di stile R1C1. In questo esempio, abbiamo aggiunto anche il nome del foglio al link: 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Esempio 4: crea una matrice di numeri non mobile
A volte è necessario utilizzare una matrice di numeri nelle formule di Excel. Nell'esempio seguente, vogliamo calcolare la media dei 3 numeri più grandi nella colonna B. I numeri possono essere inseriti in una formula, come si fa nella cella D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Se hai bisogno di un array più grande, è improbabile che tu voglia inserire tutti i numeri nella formula. La seconda opzione è usare la funzione RIGA (ROW), come fatto nella formula di matrice immessa nella cella D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
La terza opzione è usare la funzione RIGA (STRINGA) insieme a INDIRETTI (INDIRETTO), come fatto con la formula della matrice nella cella D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Il risultato per tutte e 3 le formule sarà lo stesso:
Tuttavia, se le righe vengono inserite nella parte superiore del foglio, la seconda formula restituirà un risultato errato poiché i riferimenti nella formula cambieranno insieme allo spostamento di riga. Ora, invece della media dei tre numeri più grandi, la formula restituisce la media del 3°, 4° e 5° numero più grande.
Usare le funzioni INDIRETTI (INDIRETTO), la terza formula mantiene i riferimenti di riga corretti e continua a mostrare il risultato corretto.