Funzioni di ricerca

     

In Excel esistono diverse funzioni che possono essere usate per cercare un dato in un foglio di calcolo in base a una o molteplici condizioni, come ad esempio:
↓ INDICE
↓ CONFRONTA
↓ SCEGLI
↓ CERCA.VERT
↓ CERCA.X
Queste funzioni vengono in genere impiegate in unione tra loro ottenendo formule di una certa lunghezza e complessità. In questa pagina si vogliamo mostrare la potenzialità di queste funzioni di ricerca e riferimento, la maggior parte delle quali esistono già da molti anni, anche nelle versioni precedenti all’ultima versione del programma.

Funzione INDICE

     

La funzione INDICE restituisce il valore di un elemento in una tabella o una matrice, selezionato mediante gli indici dei numeri di riga e colonna.

La funzione INDICE ha due forme di sintassi, ossia la forma per matrice e quella per riferimento.

La forma per matrice restituisce sempre un valore o una matrice di valori, mentre la forma per riferimento restituisce sempre un riferimento.

=INDICE(matrice,riga,col)

Il primo esempio lo troviamo sul foglio1 del file allegato orto.xls .

nell'esempio in cella A6 viene inserita la formula

=INDICE(A2:C4;3;2)

cioè viene restituito l'elemento in 3° riga ed in 2° colonna della matrice di valori A2:C4.

Se si imposta riga o col (colonna)su 0 (zero), INDICE restituisce i valori dell'intera riga o colonna rispettivamente. Per utilizzare i valori restituiti come matrice, immettere la funzione INDICE come formula in forma di matrice in un intervallo orizzontale di celle per una riga e in un intervallo verticale di celle per una colonna. Per immettere una formula in forma di matrice, premere CTRL+MAIUSC+INVIO.

In questo caso, se inseriamo la formula

=INDICE(A2:C4;3;0)

in cella A7 e trasciniamo la formula fino alla cella C7 o battiamo CTRL+MAIUSC+INVIO, otteniamo i valori contenuti in riga 3 . In modo analogo la formula :

=INDICE(A2:C4;0;1)

inserita in cella A8 produce la restituzione della matrice della prima colonna.

Il secondo tipo di sintassi restituisce il riferimento della cella all'intersezione di una particolare riga e colonna. Se il riferimento è costituito da selezioni non adiacenti, sarà possibile scegliere la selezione nella quale effettuare la ricerca. La sua forma generale è la seguente:

=INDICE(rif;riga;col;area)

Ricapitolando, la funzione INDICE dispone di due sintassi: matrice e riferimento. La sintassi 1 (matrice) restituisce sempre un valore o una matrice di valori, mentre la sintassi 2 (riferimento) restituisce sempre un riferimento. Utilizziamo l'elenco del foglio2 del file orto.xls.

La formula

=INDICE(A2:C6;2;3)

in cella A13 ritorna l'intersezione della seconda riga e della terza colonna nell'intervallo A2:C6, ossia il contenuto della cella C3. (11).

La formula

=INDICE((A1:C6;A8:C12);2;2;2)

in cella A14 ritorna l'intersezione della seconda riga e della seconda colonna nella seconda area di A8:C12, ossia il contenuto della cella B9. (2,65).

La formula

=SOMMA(INDICE(A1:C12;0;3;1))

in cella A15 ritorna la somma della terza colonna nella prima area dell'intervallo A1:C12, ossia la somma di C1:C12. (84).

La formula

=SOMMA(B2:INDICE(A2:C6;5;2))

restituisce la somma dell'intervallo che ha inizio in B2 e termina all'intersezione della quinta riga e della seconda colonna dell'intervallo A2:A6, ossia la somma di B2:B6. (13).

 

Funzione CONFRONTA

     

Restituisce la posizione relativa di un elemento in una matrice che corrisponde a un valore specificato in un ordine particolare. La sua sintassi è

=CONFRONTA(valore;matrice;corrisp)

● Valore è il valore utilizzato per ricercare il valore desiderato all'interno di una tabella; può essere un valore numerico, di testo o logico oppure un riferimento di cella a un numero, a del testo o a un valore logico.

● Matrice è un intervallo contiguo di celle adiacenti che contengono i possibili valori da ricercare. Matrice deve essere una matrice o un riferimento di matrice.

● Corrisp è il numero -1, 0 o 1. Corrisp specifica il modo in cui Microsoft Excel deve confrontare valore con i valori contenuti in matrice.

Se corrisp è 1, CONFRONTA troverà il valore più grande che è minore o uguale a valore. È necessario che i valori in matrice siano disposti in ordine crescente: ...-2; -1; 0; 1; 2;...A-Z; FALSO; VERO.

Il comando funziona anche su una base di numeri con la virgola, come si vede qui sotto.

Se corrisp è 0, CONFRONTA troverà il primo valore che corrisponde esattamente a valore. I valori in matrice possono essere disposti in qualsiasi ordine.

Se corrisp è -1, CONFRONTA troverà il valore più piccolo che è maggiore o uguale a valore. È necessario che i valori di matrice siano disposti in ordine decrescente: VERO; FALSO; Z-A;...2; 1; 0; -1; -2;... e così via.

se i valori di matrice non sono disposti in ordine decrescente viene restituito un errore #N/D.

CONFRONTA restituisce la posizione del valore confrontato all'interno di matrice, non il valore stesso.

Se CONFRONTA non riesce a trovare un valore corrispondente, restituirà il valore di errore #N/D che significa non disponibile.

Se corrisp è omesso, verrà considerato uguale a 1.

L'uso combinato della funzione INDICE e della funzione CONFRONTA può spesso essere utile nei processi decisionali come si vede nel seguente esempio.

Un'azienda riceve tre preventivi da altrettanti fornitori che chiamiamo alfa, beta e gamma. I preventivi variano in base al numero di pezzi del prodotto richiesti. Costruiamo una tabella con le tre offerte che variano in base al volume di pezzi acquistati.

Si vuole semplicemente scrivere nella colonna E quale delle tre offerte è la migliore per ogni volume di vendita. Per risolvere il problema si deve individuare, per ogni riga, l'offerta minore tra le tre. La funzione:

=MIN(B2:D2)

restituisce il valore minimo tra quelli elencati come argomento. Il valore minimo trovato andrà individuato fra i tre della riga con la funzione CONFRONTA che ritornerà la posizione sulla riga (ossia il valore 1, 2 o 3).
La funzione utilizza la seguente sintassi:

=CONFRONTA(valore;matrice;corrisp)

dove valore sarà il valore da cercare nella matrice secondo il tipo confronto indicato da 1, 0, -1 a seconda se si vuole, nell'ordine, una ricerca approssimata per eccesso, ricerca esatta (0) o approssimata per difetto.
Quindi avremo la prima parte della formula:

=CONFRONTA(MIN(B2:D2);B2:D2;0))

che cerca il valore minimo tra B2 :D2 nella matrice B2 :D2 ottenendo la posizione del valore minimo trovato (in questo caso 2).
La funzione INDICE ha la solita sintassi:
=INDICE(matrice,riga,col)
che restituisce il contenuto della cella individuata dai due indici di riga e colonna nella matrice. Avremo quindi la formula finale:

=INDICE($B$1:$D$1;1;CONFRONTA(MIN(B2:D2);B2:D2;0))

Rispetto alla prima riga, la funzione CONFRONTA restituisce il valore 2 che insieme al valore 1 di riga restituirà la posizione 1, 2 della matrice B2 :D2 ossia la cella C2 che contiene il valore beta. L'indice di riga sarà sempre 1 in questo caso, perché la matrice è composta da un'unica riga.

Funzione SCEGLI

     

Un'altra funzione che viene spesso usata in combinazione con le precedenti è la funzione SCEGLI.
La sua sintassi è:

=SCEGLI(indice; valore1; [valore2]; [valore3];...)

● indice è un valore numerico da 1 a 254 (se decimale viene presa solo la parte intera);

● tutti i termini tra parentesi quadre sono facoltativi; gli unici argomenti obbligatori sono i primi due;

● valore1, valore2, valore3... in base al valore dell'indice viene restituito valore1, valore2, valore3...; l'indice può non essere continuo, per esempio se indice vale 3 poi 5 si prende il terzo poi il quinto valore.

● non è possibile inserire come argomento un valore di default, se un valore di indice non viene trovato la funzione restituisce l'errore #VALORE.

Lo schema seguente rappresentato nel foglio5 del file allegato mostra due tabelle con elenchi di prodotti e dei fatturati relativi.

Per ottenere il fatturato del settore scritto in C9, si deve inserire la funzione SCEGLI dentro una funzione SOMMA: la funzione SCEGLI, in base all'indice in C9 seleziona l'intervallo B3:B7 oppure l'intervallo D3:D5 e lo restituisce alla funzione SOMMA che ne calcolerà il valore.
Nella cella D9 viene calcolata la somma del fatturato del settore indicato nella cella C9 attraverso la formula:

=SOMMA(SCEGLI(C9; B3:B7; D3:D5))

Se in C9 non fosse scritto nulla o ci fosse un valore diverso da 1 o 2, la funzione SCEGLI restituirebbe l'errore #VALORE! . Per evitare questa eventualità si può inserire la formula in D9 dentro una funzione SE. ERRORE in questo modo:

=SE.ERRORE(SOMMA(SCEGLI(C9; B3:B7; D3:D5)); "Settore non trovato")

Se i tre riferimenti inseriti nella funzione SCEGLI fossero denominati con il comando Formale > Definisci nome, la formula sarebbe potuta diventare:

=SE.ERRORE(SOMMA(SCEGLI(Indice; Settore1; Settore2)); "Settore non trovato")

forse più semplice da leggere della precedente.

Vediamo ora un secondo esempio. La tabella che si vede qui sotto contiene l'elenco dei dipendenti di una piccola azienda. Nella colonna C si deve scrivere il Settore aziendale in base al codice di colonna B.

La funzione SCEGLI contiene l'elenco dei cinque settori aziendali da inserire nelle celle della colonna C. Se il numero dei settori fosse stato troppo alto sarebbe stato più conveniente usare la funzione CERCA.VERT che prenderebbe i dati da una tabella esterna.

Funzione CERCA.VERT

     

La funzione di ricerca più utilizzata in Excel è sicuramente la CERCA.VERT anche se non è la migliore dal punto di vista delle prestazioni, ma probabilmente è la più facile da impiegare dato che non ricorre a cascate di indici e confronti per trovare il risultato.

La funzione CERCA.VERT cerca un dato in una matrice e ritorna il valore trovato sulla stessa riga ma in un'altra colonna.

=CERCA.VERT(valore;tabella_matrice;indice;intervallo)

Esiste anche una analoga funzione CERCA.ORIZZ che fa lo stesso cercando un valore per righe, ma CERCA.VERT rimane certamente la più sfruttata.

● valore è il dato da cercare nella prima colonna della matrice; attenzione a questo aspetto: la ricerca del datò verrà effettuata esclusivamente sulla prima colonna della matrice;

● matrice è la matrice dei dati, composta da colonne;

● indice è un numero che indica la colonna in cui si trova la cella con il valore da ritornare; riferendosi alla figura, la matrice dei dati ha 7 colonne, quindi il valore dell'indice potrà andare da 1 a 7;

● intervallo Se intervallo è VERO (1), i valori nella prima colonna di tabella_matrice dovranno essere disposti in ordine crescente; in caso contrario, CERCA.VERT potrebbe non restituire il valore corretto. Se intervallo è FALSO (0), non sarà necessario ordinare tabella_matrice.

Per chiarire ulteriormente le peculiarità della funzione CERCA.VERT osserviamo le formule inserite nel foglio7.

In cella A12 inseriamo

=CERCA.VERT(1;$A$2:$C$10;2)

Cerca 1 nella colonna A e restituisce il valore della colonna B sulla stessa riga (2,17)

In cella A13 inseriamo

=CERCA.VERT(1;$A$2:$C$10;3;VERO)

Cerca 1 nella colonna A e restituisce il valore della colonna C sulla stessa riga (100)

In cella A14 inseriamo

=CERCA.VERT(0,7;$A$2:$C$10;3;FALSO)

Cerca 0,7 nella colonna A. Non esiste corrispondenza esatta nella colonna A, pertanto viene restituito un errore (#N/D).

In cella A15 inseriamo

=CERCA.VERT(0,1; ;$A$2:$C$10;2;VERO)

Cerca 0,1 nella colonna A. Poiché 0,1 è minore del valore più piccolo presente nella colonna A, viene restituito un errore (#N/D).

In cella A16 inseriamo

=CERCA.VERT(2;$A$2:$C$10;2;VERO)

Cerca 2 nella colonna A e restituisce il valore della colonna B sulla stessa riga (1,71) .

A dimostrazione del fatto che anche la funzione CERCA.VERT migliora le sue prestazioni quando viene ibridizzata con altre funzioni, osserviamo il contenuto del foglio8 del file allegato, nel quale si vede una parte di una tabella dove passando la matricola alla funzione, CERCA.VERT vogliamo venga restituito il cognome o il nome o il settore e così via.

Inseriamo un codice in cella A23 e in cella A24 la seguente formula

=CERCA.VERT( $A$23; $A$2: $G$21; 2; 0)

il risultato sarà:

La funzione cercherà la matricola scritta in A23 nella prima colonna di A2:G21 e resti-tuirà il valore trovato nella cella all'incrocio tra la colonna 1 (colonna A) con la riga dove verrà trovata la matricola.

Se ci fosse l'esigenza di avere una rappresentazione più completa dei dati cercati dovremmo ricorrere a qualche artificio.

In linea di massima sia valore sia matrice devono avere gli indirizzi assoluti per non essere modificati. Altro aspetto da modificare: l'indice di colonna deve cambiare in ogni colonna dove verrà copiata la formula, prima 1, poi 2, poi 3 fino alla 7. Come fare? Si può utilizzare la funzione COLONNE la cui sintassi è: COLONNE(matrice) che ritorna il numero di colonne presenti in matrice. Infatti scrivendo in cella A24:

=CERCA.VERT( $A$23; $A$2:$G$21; COLONNE($A2:A2); 0)

e trascinando la formula verso destra per altre sei colonne otteniamo:

inserendo in una qualsiasi cella

=COLONNE($A2:A2)

la formula ritornerà 1 perché è una sola la colonna della matrice, ma, trascinando verso destra, il riferimento interno si modificherà in $A2:B2, poi $A2:C2, poi $A2:D2 e così fino a $A2:G2 e quindi la funzione ritornerà i valori 2, 3, 4, 5, 6 e 7, esattamente quello che serve: un indice che scorre per coprire via via tutte le colonne.

Come già detto la funzione CERCA.VERT è molto popolare, ciò nonostante, mantiene una serie di problemi di cui bisogna tener conto.

1 la funzione cerca un solo valore, non può eseguire una ricerca su più valori;

2 il valore da cercare dev'essere nella prima colonna della matrice: certo, se il valore da cercare fosse nella seconda colonna, la matrice potrebbe iniziare dalla seconda o terza colonna, ma le colonne a sinistra della prima non esisterebbero;

3 il valore restituito dev'essere a destra del valore da cercare: esattamente quanto detto al punto precedente, solo i valori a destra della colonna di ricerca sono raggiungibili; questa è una limitazione molto forte;

4 il valore da cercare deve essere unico, se fossero più di uno la funzione ritornerebbe soltanto il primo e non avviserebbe della presenza di altri valori.

5 la colonna su cui si trova la cella da restituire è indicata con un indice e non con un riferimento: per modificare la formula trascinando la funzione si deve inserire una funzione esterna, come per esempio COLONNE, per modificare l'indice;

6 eseguendo una ricerca approssimata, la funzione ritornerebbe soltanto il primo valore trova più piccolo e non si può agire in alcun modo per modificare il tipo di ricerca;

7 se la funzione non trovasse il valore cercato, si dovrebbe utilizzare la funzione esterna SE.ERRORE per gestire la mancanza di dato;

8 anche eseguendo una ricerca esatta di codici alfanumerici (matricole, targhe, numeri telefonici ecc.) si devono utilizzare codici univoci. Qualora anche uno solo di questi problemi si presentasse, la funzione dev' essere sostituita o ibridizzata con le funzioni INDICE, CONFRONTA, INDIRETTO, INDIRIZZO, SCEGLI, che, se utilizzate insieme, permettono di risolvere con la loro flessibilità tutti i problemi non affrontabili con la CERCA.VERT.

Questo insieme di problematiche viene almeno in parte risolto a partire dalla versione Office 365 dall'introduzione della nuova funzione CERCA.X che, di fatto, costituisce l'evoluzione della CERCA.VERT.

Funzione CERCA.X

     

La nuova funzione CERCA.X è disponibile soltanto dalle versioni Office 365 in poi. La sua sintassi è caratterizzata da sei argomenti, di cui i primi tre obbligatori e gli ultimi tre facoltativi:

=CERCA.X(valore; matrice_ricerca;matrice_restituita; [se_non_trovato];[modalità_confronto]; [modalità_ricerca])

● valore è il dato che deve essere cercato nella matrice di ricerca;

● matrice_ricerca è la matrice o intervallo nel quale cercare il valore;

● matrice_restituita è la matrice o intervallo che viene restituito;

● se_non_trovato è un valore o un testo come "Non trovato", che viene restituito quando la funzione non trova il valore da cercare, se omesso restituisce errore #N/D;

● modalità_confronto può assumere i valori 0, -1, 1, 2 secondo lo schema:
  ★ 0 per corrispondenza esatta, se non viene trovato il valore restituisce errore #N/D;
  ★ -1 per corrispondenza esatta, se non viene trovato il valore restituisce l'elemento successivo più piccolo;
  ★ 1 per corrispondenza esatta se non viene trovato il valore restituisce l'elemento successivo più grande;
  ★ 2 effettua una ricerca con i caratteri jolly:
    – * indica qualsiasi numero di caratteri, per esempio An* cerca qualsiasi testo che inizi con An;
    – ? indica un solo carattere qualsiasi, per esempio Ross??? può restituire Ros-sini, Rossano, Rossoni ecc. oppure R?m? può restituire Roma, Ramo, Remo, Rame ecc.;
    – ~ (tilde Alt+126) seguito da *, ? o ~ trova e restituisce un testo seguito dal carattere dopo tilde, per esempio ABD~? cerca il testo ABD?; se viene omesso vale 0 (ricerca esatta);

● modalità_ricerca individua la direzione di ricerca nella colonna:
  ★ 1 oppure omesso, effettua la ricerca dal primo elemento (impostazione predefinita);
  ★ -1 effettua una ricerca partendo dall'ultimo elemento;
  ★ 2 effettua una ricerca per valori binari in una matrice che dev'essere ordinata in modo crescente, se la matrice non fosse ordinata il risultato non sarebbe valido;
  ★ -2 effettua una ricerca binaria in una matrice ordinata in modo decrescente.

Vediamo un esempio che usa la precedente base di dati: in cella A23 inseriamo un codice univoco di ricerca, mentre in cella A24 inseriamo la formula:

=CERCA.X($A$23;$A$2:$A$21;A2:G21)

poi trasciniamo verso destra, ottenendo lo stesso risultato che abbiamo avuto con CERCA.VERT ma con una sintassi più semplice.

Una versione più selettiva del precedente comando è data dall'inserimento in cella B23 del campo in cui si vuole effettuare la ricerca . Allora i dati di input saranno due: in cella A23 il codice univoco cercato e in cella B23 il campo in coi cercare il dato. In cella A24 inseriamo allora la formula:

=CERCA.X(A23;A2:A21;CERCA.X(B23;A1:G1;A2:G21))

ottenendo il seguente risultato:

Sono state usate due funzioni CERCA.X, una come argomento dell'altra, la prima che cerca la riga riferita alla matrice e la seconda che cerca il campo tra le intestazioni di colonna: l'unione delle due formule restituisce la cella all'incrocio di riga e colonna.