Come utilizzare le funzioni di ricerca in excel?

file di esempio

1. Funzione INDIRIZZO

La funzione INDIRIZZO restituisce un riferimento di cella valido in base all’indirizzo di colonna e riga. In termini semplici, puoi creare l’indirizzo di una cella utilizzando il numero di riga e il numero di colonna.

Sintassi

INDIRIZZO(num_riga,num_colonna,num_abs,A1,testo_foglio)

argomenti

• row_num: un numero per specificare il numero di riga.
• num_colonna: un numero per specificare il numero di colonna.
• [abs_num]: tipo di riferimento.
• [A1]: stile di riferimento.
• [sheet_text]: un valore di testo come nome del foglio.

Commenti

• Per impostazione predefinita, la funzione INDIRIZZO restituisce un riferimento assoluto nel risultato.

Esempio

Nell’esempio seguente abbiamo utilizzato argomenti diversi per ottenere tutti i tipi di risultati.

Con stile di riferimento R1C1 :

• Riferimento relativo.
• Riferimento di riga relativo e riferimento di colonna assoluto.
• Riferimento assoluto alla riga e relativo colonna.
• Riferimento assoluto.

Con stile di riferimento A1:

• Riferimento relativo.
• Riferimento di riga relativo e riferimento di colonna assoluto.
• Riferimento assoluto alla riga e relativo colonna.
• Riferimento assoluto.

2. Funzione ZONE

La funzione AREE restituisce un numero che rappresenta il numero di intervalli nel riferimento specificato. In termini semplici, conta effettivamente le diverse aree del foglio di lavoro a cui hai fatto riferimento nella funzione.

sintassi

CAMPI (riferimento)

argomenti

• riferimento: un riferimento a una cella o a un intervallo di celle.

Commenti

• Il riferimento può essere una cella, un intervallo di celle o un intervallo denominato.
• Se desideri fare riferimento a più riferimenti di cella, devi racchiudere tutti questi riferimenti tra più serie di parentesi e utilizzare le virgole per separare ciascun riferimento dagli altri.

Esempio

Nell’esempio seguente abbiamo utilizzato la funzione zone per ottenere il riferimento numerico in un intervallo denominato.

Come puoi vedere, ci sono tre colonne nell’intervallo e il risultato è 3.

3. SCEGLI la funzione

La funzione SELECT restituisce un valore dall’elenco di valori in base al numero di posizione specificato. In termini semplici, cerca un valore in un elenco in base alla sua posizione e lo restituisce nel risultato.

Sintassi

SELECT(num_indice,valore1,valore2,…)

argomenti

• indice_num: numero utilizzato per specificare la posizione del valore nell’elenco.
• valore1: un intervallo di celle o un valore di input da cui è possibile scegliere.
• [valore2]: un intervallo di celle o valore di input da cui è possibile scegliere.

Commenti

• Puoi fare riferimento a una cella oppure puoi anche inserire valori direttamente nella funzione.

Esempio

Nell’esempio seguente, abbiamo utilizzato la funzione SCEGLI con un elenco a discesa per calcolare quattro cose diverse (somma, media, massimo e mix). Quindi abbiamo utilizzato la formula seguente per calcolare le quattro cose:

=SCEGLI(CERCA.VERT(K2,Q1:R4,2,FALSO),SOMMA(O2:O9),MEDIA(O2:O9),MAX(O2:O9),MIN(O2:O9))

Abbiamo questa piccola tabella con i nomi dei quattro calcoli che vogliamo e un numero di serie per ciascuno nella cella corrispondente.

Successivamente abbiamo un elenco a discesa per tutti e quattro i calcoli. Ora per ottenere il numero di indice nella funzione di scelta da questa piccola tabella, abbiamo una formula di ricerca che restituirà il numero di serie in base al valore selezionato dall’elenco a discesa.

E invece dei valori abbiamo utilizzato quattro formule per quattro calcoli diversi.

4. Funzione COLONNA

La funzione COLONNA restituisce il numero di colonna per il riferimento di cella specificato. Come sai, ogni riferimento di cella è costituito da un numero di colonna e da un numero di riga. Quindi prende il numero della colonna e lo restituisce nel risultato.

Sintassi

COLONNA([riferimento])

argomenti

• riferimento: un riferimento di cella per il quale si desidera ottenere il numero di colonna.

Commenti

• Non è possibile fare riferimento a più riferimenti.
• Se ti riferisci a un array, la funzione colonna restituirà anche i numeri di colonna in un array.
• Se ti riferisci a un intervallo di più celle, restituirà il numero di colonna della cella più a sinistra. Ad esempio, se fai riferimento all’intervallo A1:C10, restituirà il numero di colonna della cella A1.
• Se salti la specifica di un riferimento, verrà restituito il numero di colonna della cella corrente.

Esempio

Nell’esempio seguente, abbiamo utilizzato COLONNA per ottenere il numero di colonna della cella A1.

Come ho già detto, se ignori il riferimento alla cella, verrà restituito il numero di colonna della cella corrente. Nell’esempio seguente, abbiamo utilizzato COLUMN per creare un’intestazione con numeri di serie.

5. Funzione COLONNE

La funzione COLONNE restituisce il numero di colonne a cui si fa riferimento nel riferimento specificato. In termini semplici, conta il numero di colonne nell’intervallo fornito e restituisce quel numero.

Sintassi

COLONNE(tabella)

argomenti

• tabella: una tabella o un intervallo di celle da cui desideri ottenere il numero di colonne.

Commenti

• Puoi anche utilizzare un intervallo denominato.
• La funzione COLONNE non riguarda i valori nelle celle, restituirà semplicemente il numero di colonne in un riferimento.

Esempio

Nell’esempio seguente abbiamo utilizzato COLUMN per ottenere il numero di colonne nell’intervallo A1:F1.

6. Funzione FORMULA TESTO

La funzione FORMULATEXT restituisce la formula per la cella di riferimento. E se non è presente alcuna formula nella cella di riferimento, un valore o uno spazio vuoto, verrà restituito un #N/A.

Sintassi

TESTOFORMULA (riferimento)

argomenti

• riferimento: il riferimento della cella da cui desideri ottenere la formula come testo.

Commenti

• Se fai riferimento a un’altra cartella di lavoro, tale cartella di lavoro deve essere aperta, altrimenti non visualizzerà la formula.
• Se ti riferisci a un intervallo composto da più di una singola cella, verrà restituita la formula per la cella in alto a sinistra dell’intervallo specificato.
• Restituirà un valore di errore “#N/D” se la cella che stai utilizzando come riferimento non contiene formule, ha una formula con più di 8192 caratteri, una cella è protetta o una cartella di lavoro esterna non è aperta.
• Se si fa riferimento a due celle in un riferimento circolare, verranno restituiti i risultati di entrambi.

Esempio

Nell’esempio seguente abbiamo utilizzato il testo della formula con diversi tipi di riferimento. Quando fai riferimento a una cella che non ha una formula, verrà restituito il valore di errore “#N/A”.

7. Funzione HSEARCH

La funzione CERCA.ORIZZ cerca un valore nella riga superiore di una tabella e restituisce il valore nella stessa colonna del valore corrispondente utilizzando il numero di indice. In termini semplici, la ricerca avviene in orizzontale.

Sintassi

CERCA.ORIZZ(valore_ricerca, matrice_tabella, numero_indice_riga, [ricerca_intervallo])

argomenti

• lookup_value: il valore che vuoi cercare.
• table_array: la tabella o l’array di dati da cui vuoi trovare il valore.
• row_index_num: un valore numerico che rappresenta un numero di righe a partire dalla riga superiore da cui si desidera ottenere il valore. Ad esempio, se specifichi 2 e il valore di ricerca è in A10 nella tabella dati, verrà restituito il valore nella cella B10.
• [range_lookup]: un valore logico per specificare il tipo di ricerca. Se vuoi fare una ricerca con corrispondenza esatta, usa FALSE e se vuoi fare una ricerca con corrispondenza non esatta, usa TRUE (predefinito).

Commenti

• Puoi usare i caratteri jolly.
• Puoi eseguire la corrispondenza esatta e la corrispondenza fuzzy.
• Quando esegui una corrispondenza fuzzy, assicurati di ordinare i dati in ordine crescente da sinistra a destra e, se i dati non sono in ordine crescente, restituirebbero un risultato impreciso.
• Se range_lookup è vero o omesso, eseguirà una corrispondenza non esatta ma restituirà una corrispondenza esatta se il valore di ricerca esiste nell’intervallo di ricerca.
• Se range_lookup è vero o omesso e il valore di ricerca non è nell’intervallo di ricerca, restituirà il valore più vicino che è inferiore al valore di ricerca.
• Se range_lookup è false, non è necessario ordinare l’intervallo di dati.

Esempio

Nell’esempio seguente, abbiamo utilizzato la funzione CERCA.ORIZZ con CONFRONTA per creare una formula dinamica, quindi abbiamo utilizzato un elenco a discesa per modificare il valore di ricerca della cella.

Il nome del campo della cella C7 viene utilizzato come valore di ricerca. Intervallo B1: F5 come array di tabelle e per row_index_num abbiamo utilizzato la funzione di corrispondenza per ottenere il numero di riga.

Ogni volta che modifichi il valore nella cella C9, restituirà il numero di riga della tabella. Non è necessario cambiare la formula più e più volte. Basta modificare i valori con il menu a discesa e otterrai un valore.

8. Funzione COLLEGAMENTO IPERTESTUALE

La funzione HYPERLINK restituisce una stringa a cui è allegato un collegamento ipertestuale. In parole semplici, come l’opzione HYPERLINK che hai in Excel, la funzione HYPERLINK ti aiuta a creare un collegamento ipertestuale.

Sintassi

HYPERLINK(posizione_link,[nome_friendly])

argomenti

• Link_Location: la posizione per la quale desideri aggiungere un HYPERLINK. Può essere ulteriormente suddiviso in due termini.
  1. collegamento: può essere l’indirizzo di una cella o di un intervallo di celle nello stesso foglio di lavoro o in qualsiasi altro foglio di lavoro o cartella di lavoro. Possiamo anche collegare un segnalibro da un documento Word.
  2. posizione: può essere un collegamento a un disco rigido, un server che utilizza il percorso UNC o qualsiasi URL da Internet o Intranet. (In Excel online è possibile utilizzare l’indirizzo Web solo per la funzione COLLEGAMENTO IPERTESTUALE). È possibile inserire un collegamento alla funzione inserendolo come testo tra virgolette o facendo riferimento a una cella che contiene il collegamento come testo. Assicurati di utilizzare “HTTPS://” prima di un indirizzo web.
• [friendly_name]: questa è una parte opzionale di questa funzionalità. Funziona come il volto del collegamento di connessione.
  1. Puoi utilizzare qualsiasi tipo di testo, numero o entrambi.
  2. Ti riferisci anche a una cella che contiene friendly_name.
  3. Se lo ignori, la funzione utilizzerà l’indirizzo del collegamento da visualizzare.
  4. Se friendly_name restituisce un errore, la funzione visualizzerà un errore.

Commenti

• Collegare un file salvato a un indirizzo web: puoi utilizzare un file salvato a un indirizzo web. Questo ci aiuta a condividere il file in modo efficiente.
• Collega un file salvato su un disco rigido: puoi utilizzare questa funzione anche quando lavori offline. Puoi collegare un file memorizzato sul tuo disco rigido e accedervi tramite il tuo unico foglio Excel, senza bisogno di andare in ciascuna cartella per aprirli.
• Collega file di documenti Word: anche questa è una fantastica funzionalità della funzione HYPERLINK. È possibile collegare un file di documento Word o una posizione specifica in un file di documento Word utilizzando un segnalibro.
• Collega un file senza utilizzare un nome descrittivo: se desideri visualizzare all’utente il collegamento effettivo al file o al percorso. In questa situazione, devi semplicemente ignorare la dichiarazione del nome descrittivo nella funzione HYPERLINK.

9. Funzione INDIRETTA

La funzione INDIRETTO restituisce un riferimento valido da una stringa di testo che rappresenta un riferimento di cella. In termini semplici, puoi fare riferimento a un intervallo di celle utilizzando l’indirizzo della cella come valore di testo.

Sintassi

INDIRETTO(testo_rif, [a1])

argomenti

• ref_text: testo che rappresenta l’indirizzo di una cella, l’indirizzo di un intervallo di celle, un intervallo denominato o il nome di una tabella. Ad esempio, A1, B10:B20 o MyRange.
• [a1]: un numero o un valore booleano per rappresentare il tipo di riferimento di cella specificato in ref_text. Ad esempio, se desideri utilizzare lo stile di riferimento A1, utilizza TRUE o 1, mentre se desideri utilizzare lo stile di riferimento R1C1, utilizza FALSE o 0 per lo stile di riferimento R1C. E se non specifichi il tipo di riferimento della cella, utilizzerà lo stile A1 per impostazione predefinita.

Commenti

• Dopo aver fatto riferimento a un’altra cartella di lavoro, è necessario aprire quella cartella di lavoro.
• Se inserisci una riga o una colonna nell’intervallo a cui fai riferimento, INDIRECT non aggiornerà quel riferimento.
• Se vuoi inserire del testo direttamente nella funzione, devi racchiuderlo tra virgolette doppie oppure puoi anche fare riferimento a una cella contenente il testo che vuoi usare come riferimento.

Esempio

1. Riferimento a un altro foglio di lavoro

Puoi anche fare riferimento a un altro foglio di lavoro utilizzando INDIRETTO e devi inserire lì il nome del foglio di lavoro. Nell’esempio seguente, abbiamo utilizzato la funzione indiretta per fare riferimento a un altro foglio di lavoro e avere il nome del foglio nella cella A2 e il riferimento alla cella nella cella B2.

Nella cella C2 abbiamo utilizzato la seguente formula per combinare il testo.

=INDIRETTO(“’”&A2&”’!”&B2)

Questa combinazione crea testo utilizzato dalla funzione INDIRETTO per fare riferimento alla cella A1 nel foglio1 e la parte migliore è che quando si modifica il nome del foglio di lavoro o l’indirizzo della cella, il riferimento cambierà automaticamente.

La cella A1 nel “Foglio1” ha il valore “Sì” ed è per questo che l’indiretto restituisce il valore “Sì”.

2. Riferimento a un’altra cartella di lavoro

Puoi anche fare riferimento a un’altra cartella di lavoro, nello stesso modo in cui abbiamo fatto per un altro foglio di lavoro. Tutto quello che devi fare è semplicemente aggiungere il nome di una cartella di lavoro nel testo da utilizzare come riferimento.

Nell’esempio sopra, abbiamo utilizzato la seguente formula per ottenere il valore della cella A1 della cartella di lavoro “Libro1”.

=INDIRETTO(“[“&A2&”]”&B2&”!”&C2)

Poiché abbiamo il nome della cartella di lavoro nella cella “A2”, il nome del foglio di lavoro nella cella “B2” e il nome della cella nella cella “C2”. Li combiniamo per utilizzarli come testo di input in una funzione indiretta.

Nota: quando combini un riferimento di cella come testo, assicurati di seguire la struttura di riferimento corretta.

3. Utilizzo di intervalli denominati

Sì, puoi anche fare riferimento a un intervallo denominato utilizzando la funzione indiretta. È semplice. Dopo aver creato un intervallo denominato, è necessario inserirlo come testo in INDIRETTO.

Nell’esempio sopra, abbiamo un elenco a discesa nella cella E1 che contiene un elenco di intervalli denominati e nella cella E2 abbiamo utilizzato quel nome. Poiché l’intervallo B2:B5 è denominato “Quantità” e l’intervallo C2:C5 è denominato “Importo”.

Quando selezioni una quantità dall’elenco a discesa, la funzione indiretta fa immediatamente riferimento all’intervallo denominato. E quando selezioni l’importo dal menu a discesa, avrai la somma dell’intervallo di celle C2:C5.

11. Funzione RICERCA

La funzione RICERCA restituisce un valore (che stai cercando) da una riga, colonna o tabella . In termini semplici, puoi cercare un valore e SEARCH restituirà quel valore se si trova in quella riga, colonna o tabella.

Sintassi

RICERCA(valore, intervallo_ricerca, [intervallo_risultato])

Esistono due tipi di funzioni di RICERCA.

• forma vettoriale
• Modulo tabella

argomenti

• valore: il valore che vuoi cercare in una colonna o riga.
• lookup_range: la colonna o la riga da cui vuoi cercare il valore.
• [intervallo_risultato]: la colonna o la riga da cui si desidera restituire un valore. Questo è un argomento facoltativo.

Commenti

• Invece di utilizzare il formato array, è meglio utilizzare CERCA.VERT o CERCA.ORIZZ.

12.Funzione CONFRONTA

La funzione CONFRONTA restituisce il numero di indice di un valore di matrice. In termini semplici, la funzione CONFRONTA cerca un valore nell’elenco e restituisce il numero di posizione di quel valore nell’elenco.

Sintassi

CORRISPONDENZA (valore_ricerca, matrice_ricerca, [tipo_corrispondenza])

argomenti

• lookup_value : il valore di cui si desidera ottenere la posizione da un elenco di valori.
• lookup_array : l’intervallo di celle o l’array contiene valori.
• [match_type] : il numero (-1, 0 e 1) per specificare il modo in cui Excel cerca il valore nell’elenco dei valori.
  1. Se usi 1, restituirà il valore più grande uguale o inferiore al valore di ricerca. I valori nell’elenco devono essere ordinati in ordine crescente.
  2. Se usi -1, restituirà il valore più piccolo uguale o maggiore del valore di ricerca. I valori nell’elenco devono essere ordinati in ordine crescente.
  3. Se usi 0 restituirà la corrispondenza esatta dell’elenco.

Commenti

• Puoi usare i caratteri jolly .
• Se non è presente alcun valore corrispondente nell’elenco, se restituirà #N/A.
• La funzione di corrispondenza non distingue tra maiuscole e minuscole.

Esempio

Nell’esempio seguente abbiamo utilizzato 1 come tipo di corrispondenza e stiamo cercando il valore 5.

Come ho già detto, se utilizzi 1 nel tipo di corrispondenza, restituisce il valore più grande uguale o inferiore al valore di ricerca. In tutta la lista ci sono 3 valori inferiori a 5 e 4 è il più alto.

Ecco perché nel risultato è tornato 3 che è la posizione del valore 4.

13. Funzione di cambio

La funzione OFFSET restituisce un riferimento a un intervallo che corrisponde a un numero specifico di righe e colonne di distanza da una cella o da un intervallo di celle. In termini semplici, puoi fare riferimento a una cella o a un intervallo di celle utilizzando righe e colonne a partire da una cella iniziale.

Sintassi

OFFSET(riferimento, righe, colonne, [altezza], [larghezza])

argomenti

• riferimento : il riferimento da cui si desidera eseguire l’offset per iniziare. Può essere una cella o un intervallo di celle adiacenti.
• righe : il numero di righe che indicano a OFFSET di spostarsi verso l’alto o verso il basso rispetto al riferimento. Per scendere è necessario un numero positivo, per salire è necessario un numero negativo.
• cols : il numero di colonne indica a OFFSET di spostarsi a sinistra o a destra dal riferimento. Per andare a destra è necessario un numero positivo e per andare a sinistra è necessario un numero negativo.
• [altezza] : un numero per specificare quali righe includere nel riferimento.
• [larghezza]: un numero per specificare quali colonne includere nel riferimento.

Commenti

• OFFSET è una funzione “volatile”, si ricalcola ogni volta che si verifica una modifica in un foglio di calcolo.
• Visualizza il #REF! valore di errore se l’offset è esterno al bordo del foglio di lavoro.
• Se l’altezza o la larghezza vengono omesse, vengono utilizzate l’altezza e la larghezza di riferimento.

Esempio

Nell’esempio seguente, abbiamo utilizzato SUM with OFFSET per creare un intervallo dinamico che somma i valori di tutti i mesi per un particolare prodotto.

14. Funzione LINEA

La funzione RIGA restituisce il numero di riga della cella di riferimento. In parole semplici, con la funzione RIGA puoi ottenere il numero di riga di una cella e se non ti riferisci a nessuna cella, restituisce il numero di riga della cella in cui la inserisci.

Sintassi

LINEA([riferimento])

argomenti

• riferimento: un riferimento di cella o un intervallo di celle di cui si desidera controllare il numero di riga.

Commenti

• Comprenderà tutti i tipi di fogli (foglio grafico, foglio di lavoro o foglio macro).
• È possibile fare riferimento ai folio anche se sono visibili, nascosti o molto nascosti.
• Se non specifichi alcun valore nella funzione, ti verrà fornito il numero del foglio in cui hai applicato la funzione.
• Se specifichi un nome di foglio non valido, verrà restituito un #N/A.
• Se specifichi un riferimento al foglio non valido, verrà restituito un #REF!.

Esempio

Nell’esempio seguente, abbiamo utilizzato la funzione riga per verificare il numero di riga della stessa cella in cui abbiamo utilizzato la funzione.

Nell’esempio seguente, abbiamo fatto riferimento a un’altra cella per ottenere il numero di riga di quella cella.

Puoi utilizzare la funzione riga per creare un elenco di numeri di serie nel tuo foglio di calcolo. Tutto quello che devi fare è semplicemente inserire le funzioni di riga in una cella e trascinarla nella cella in cui desideri aggiungere i numeri di serie.

15. Funzione LINEE

La funzione ROWS restituisce il numero di righe nell’intervallo di riferimento. In parole semplici, con la funzione RIGHE, puoi contare il numero di righe nell’intervallo a cui fai riferimento.

Sintassi

LINEE (tabella)

argomenti

• array: un riferimento di cella o un array per verificare il numero di righe.

Commenti

• Puoi anche utilizzare un intervallo denominato.
• Non si preoccupa dei valori nelle celle, restituirà semplicemente il numero di righe in un riferimento.

Esempio

Nell’esempio seguente, abbiamo fatto riferimento a un intervallo verticale di 10 celle e il risultato ha restituito 10 perché l’intervallo ha 10 righe.

16. Funzione TRASPOSIZIONE

La funzione TRANSPOSE modifica l’orientamento di un intervallo. In parole semplici, utilizzando questa funzione puoi modificare i dati da una riga a una colonna e da una colonna a una riga.

Sintassi

TRASPOSIZIONE (tabella)

argomenti

• array: un array o un intervallo che desideri trasporre.

Commenti

• Devi applicare TRANSPOSE come funzione di array, utilizzando lo stesso numero di celle che hai nell’intervallo di origine premendo Ctrl+Shift+Invio.
• Se selezioni celle più piccole dell’intervallo di origine, i dati verranno trasposti solo per quelle celle.

Esempio

Qui dobbiamo trasporre i dati dall’intervallo B2:D4 all’intervallo da G2 a I4:

Per farlo, dobbiamo prima andare nella cella G2 e selezionare l’intervallo di celle fino a I4.

Successivamente, inserisci (=TRANSPOSE(B2:D4)) nella cella G2 e premi Ctrl+Maiusc+Invio.

TRANSPOSE convertirà i dati da righe a colonne e la formula che abbiamo applicato è una formula di matrice, non puoi modificarne una singola cella.