Le funzioni Excel di ricerca

Cosa sono le funzioni di Excel di ricerca? Quali sono? A cosa servono? Come si usano? Perché è opportuno conoscerle? Ci sono strumenti migliori? Questa settimana parliamo di un gruppo specifico di funzioni di Excel che dobbiamo conoscere per trarre il meglio dal foglio di calcolo. Parliamo delle funzioni Excel per ricerca.

Con questo post voglio condividere una sintesi di quanto esposto durante un recente incontro formativo. Nel post “Le funzioni che devi conoscere” abbiamo visto le funzioni di Excel fondamentali e più utilizzate. Non a caso tra queste hanno un ruolo centrale le funzioni di ricerca, che in pratica sono le funzioni più usate di Excel.

Quali sono le funzioni di ricerca che dobbiamo conoscere?

Cerca.Vert, che permette di individuare un valore in una matrice di celle;
Cerca.Orizz, come sopra, ma per lavorare su tabelle orizzontali;
Indice+Confronta, come Cerca.vert, ma più efficiente;
Cerca, la versione limitata di Cerca.Vert, ormai in disuso;
Ricerca, per trovare stringhe all’interno di testi;
Trova, come sopra, ma con la possibilità di distinguere maiuscole e minuscole.

A cosa servono le funzioni Excel di ricerca?

Sono strumenti che ci permettono di individuare valori in una tabella o matrice, oppure stringhe all’interno di un testo, per poi visualizzarli o elaborarli in una formula. Di conseguenza permettono per esempio di:

individuare un singolo valore specifico all’interno di una tabella,
individuare il valore più prossimo a quello ricercato,
aggiungere a una tabella un campo di un’altra tabella,
unire due tabelle,
analizzare e modificare un testo.

Queste funzioni sono notevoli strumenti di ricerca, che una volta impostate continuano a “lavorare” in modo dinamico, a meno di modifiche “strutturali” o nominative della tabella a cui sono collegate.

Vediamo in modo più approfondito le singole funzioni e il loro utilizzo.

CERCA

Questa funzione ha una doppia forma:

una forma vettoriale: CERCA(valore; vettore; risultati)
che ricerca e restituisce un valore da una singola riga o colonna di dati.
una forma “matrice”: CERCA(valore; matrice)
che ricerca un valore preciso nella prima riga o colonna di una matrice e restituisce il valore corrispondente presente nell’ultima riga o colonna della stessa matrice.
Questa forma della funzione è in disuso dall’introduzione delle funzioni Cerca.Vert() e Cerca.Orizz() più efficienti ed efficaci.

La forma vettoriale è quella utilizzata e permette di cercare un valore numerico specifico nella riga o colonna indicata (vettore) per poi restituirlo. Oppure restituire il valore corrispondente di un’altra riga o colonna indicata (risultato).

Se la funzione non riesce a trovare un valore preciso cercherà il primo valore minore. Altrimenti se non c’è un valore minore, perché il valore cercato è minore del valore più basso nella riga o colonna indicata, la funzione restituirà un errore (#N/D). Per approfondire la gestione degli errori delle formule ti invito a leggere la guida relativa.

Per esempio, se cerchi quale prodotto ha un prezzo corrispondente o più basso, puoi usare =CERCA(Prezzo; G2:G1000; B2:B1000); la funzione cercherà il valore inserito nella cella nominata “Prezzo” nell’intervallo “G2:G1000” e restituirà il valore corrispondente nell’intervallo “B2:B1000”.

È piuttosto utile in tabelle, report e maschere, ma chi impara a usare Cerca.Vert() e Cerca.Orizz() inevitabilmente abbandonerà questa funzione.

CERCA.VERT

CERCA.VERT(valore; matrice_tabella; indice; intervallo)

Complessa solo in apparenza, la funzione Cerca.Vert() permette di cercare nella prima colonna di una matrice di dati un valore per restituire il valore corrispondente della colonna indicata (indice). L’indice deve essere fornito come numero della colonna della matrice da sinistra a destra.

Può cercare un valore preciso (intervallo = Falso o 0) e se non lo trova restituire un errore, oppure può cercare il valore più prossimo (intervallo = Vero o 1).

Sostanzialmente la funzione cerca un valore preciso nel primo campo di una matrice e restituisce il valore corrispondente di un altro campo, in questo modo: CERCA.VERT(C2;TabellaCosti;3;Falso) cerca il valore della cella C2 nella prima colonna dell’intervallo nominato come “TabellaCosti” e se lo trova restituisce il valore della terza colonna della matrice.

Altrimenti restituisce un errore che deve essere gestito, soprattutto se riguarda valori numerici a loro volta oggetto di elaborazione. Altrimenti l’errore si propagherebbe ai risultati.

In caso di un valore non univoco, quindi di valori uguali presenti più volte nella prima colonna, verrà restituito il primo valore individuato dall’alto verso in basso, a meno che non siano consecutivi.

Come già detto Cerca.vert() può anche restituire un valore prossimo, impostando il quarto parametro (intervallo) a Vero oppure 1. Con prossimo si intende il primo valore inferiore, se presente, altrimenti restituisce un errore (#N/D!). La ricerca di valori prossimi è meno utilizzata rispetto ai valori precisi, è usata in situazioni particolari come in presenza di categorizzazioni, scale di valori, oppure per analisi esplorative o confronti.

I limiti del Cerca.Vert

Come vedi Cerca.Vert() è molto più efficiente ed efficace di Cerca(), ma come avrai notato ha diversi limiti. Per esempio:

cerca il valore sempre nella prima colonna della matrice,

quindi il campo di riferimento deve essere sempre a sinistra del campo dei valori da restituire; dove non è già così è necessario intervenire sulla struttura della tabella con possibili problemi per formule e strumenti non dinamici correlati, o più opportunamente aggiungere un campo “copia” della colonna di riferimento che però può generare formule ricorsive;

è probabile che restituisca errori, che di solito devono essere gestiti;

Come? Con funzioni apposite come Se.Errore(), oppure con la coppia Se() e Val.Errore(). Per esempio in questo modo: =SE.ERRORE( CERCA.VERT( Ricerca;A2:S100;2;FALSO); “Non trovo il valore”), oppure =SE(VAL.ERRORE(CERCA.VERT(Parametri));”Non presente”;CERCA.VERT(Parametri)).

Come vedi se Cerca.Vert() non trova il valore e quindi restituisce un errore, questo viene sostituito con la stringa. Se questo causa problemi di elaborazione, per esempio in campi numerici, possiamo sostituire la stringa con 0, 1 o valori nulli (“”) a seconda dei valori del campo e delle elaborazioni.

Per approfondire la gestione degli errori delle formule ti invito a leggere la guida relativa.

ricerca un valore singolo solo nella prima colonna della matrice, restituendo il primo che trova;

In realtà questi limiti sono nelle caratteristiche della funzione, ma in certe situazioni possono essere bypassati. Per esempio è possibile ricercare valori diversi di campi diversi concatenandoli in un unico campo iniziale (es: =C2 & “ “ D2) per creare un’unica chiave primaria “multipla”. Oppure possiamo cercare valori alternativi usando altre funzioni come SE(), E() e O() in modo gerarchico.

l’indicazione della colonna del risultato è un numero intero, non un riferimento a un nome di campo, quindi in caso di variazioni della tabella o matrice, la formula necessiterà di essere corretta.

Questo in verità può essere risolto, per esempio con l’utilizzo della funzione Confronta(), che vedremo più avanti, in grado di restituire il numero della colonna usando il nome del campo, in questo modo: =CONFRONTA( “ARTICOLO”; Articoli[#Intestazioni]; 0). Qui ci stiamo riferendo alle intestazioni di una tabella Exce “Articoli”.

D’altra parte questo richiede che il nome del campo non venga alterato, altrimenti la formula non funzionerà più. Quindi possiamo rendere più flessibile il Cerca.Vert, ma limitando le modifiche alle intestazioni dei campi.

Nonostante questi limiti, Cerca.Vert è una funzione notevole e relativamente semplice che permette di individuare un valore in una matrice di dati e restituirlo per visualizzarlo o elaborarlo. Questa possibilità permette di fare molto di più, come per esempio unire due tabelle, cioè inserire in una tabella uno o più campi di un’altra tabella che condivide una chiave primaria o secondaria. E altro ancora naturalmente.

CERCA.ORIZZ

CERCA.ORIZZ(valore; matrice_tabella; indice; intervallo)

È la funzione analoga a Cerca.vert(), ma permette di lavorare in orizzontale. Funziona nello stesso modo, ma ruotato di novanta gradi. In pratica Cerca.orizz() è usato raramente, in quanto le tabelle dei dati in Excel sono prevalentemente standard, cioè verticali. Quando lo usiamo? Viene impiegato di solito su tabelle di elaborazione o tabelle dei risultati, tabelle di formule costruite in orizzontale.

Per il resto, la funzione lavora come Cerca.vert(), ma in modo trasposto. Per esempio, CERCA.ORIZZ(“Bk836004”;B2:S14;3;FALSO) cerca il valore indicato nella prima riga della matrice e restituisce il dato contenuto nella terza riga della matrice, con corrispondenza esatta.

INDICE e CONFRONTA

INDICE (matrice; riga; col)

CONFRONTA (val; matrice; corr)

Usate insieme su una tabella verticale: INDICE (matrice; CONFRONTA (val; matrice; corr); col)

L’uso delle funzioni Indice() e Confronta() è un’alternativa più efficiente a Cerca.vert() e Cerca.orizz(), soprattutto in quelle situazioni dove queste non funzionano efficacemente (es: colonna di riferimento a destra, ricerca su parametri non univoci, ecc).

Indice() ha una doppia forma: matrice e riferimento; la prima restituisce un valore di una cella di una matrice, la seconda restituisce il riferimento. In questo caso ci interessa la forma matrice, che restituisce il valore della cella di una tabella individuandola tramite l’indicazione di riga e colonna. Per esempio Indice( A3:E70; 23; 4) mostrerà il valore della cella localizzata alla riga 23 della colonna 4 della matrice A3:E70. Facile, no?

Confronta() cerca un valore in una matrice, il primo se ce ne sono più di uno, oppure il valore più grande minore, o il più piccolo maggiore del valore cercato, e ne restituisce la posizione. Per esempio Confronta(F1; C3:C70; 0) cerca il valore esatto della cella F1 nell’intervallo C3:C70 e se individuato ne indica la posizione.

Altrimenti restituisce un errore che dovremo gestire, in modo analogo a quanto abbiamo visto per Cerca.Vert().

Insieme le due funzioni Indice e Confronta permettono di trovare e restituire il valore di una cella di una matrice/tabella partendo dal valore assegnato. In questo modo: Indice(A3:E70; Confronta(F1; C3:C70; 0); 4), dove la funzione Confronta() cerca e identifica la prima cella della colonna indicata “C3:C70”, che contiene il valore della cella F1, e di conseguenza identifica la riga relativa, mentre Indice() restituisce il valore della cella alla stessa riga, ma della colonna D, la quarta della matrice.

Naturalmente quando le usiamo insieme è necessario che gli intervalli indicati nei parametri delle due funzioni siano coerenti, cioè siano della stessa grandezza (es: A3:E70 e C3:C70), o potremmo generare errori.

Perché usare Indice e Confronta

Questo metodo di ricerca è usato di solito in alternativa a Cerca.Vert per diversi motivi che lo rendono preferibile. Per esempio:

perché permette di cercare i valori non solo nella prima colonna della matrice,
permette di gestire con più precisione l’individuazione del valore,
indice e confronta realizzano formule dinamiche, in grado di funzionare correttamente anche dopo modifiche alla tabella dei dati come aggiunte e rimozioni di righe e colonne,
permette di gestire valori di testo lunghi (con più di 255 caratteri),
richiede meno risorse (memoria, dimensioni del foglio, ecc), caratteristica importante quando usiamo le formule di ricerca in tabelle molto grandi con migliaia di record.

Quando parlo di formule dinamiche, mi riferisco all’uso della funzione Confronta anche per l’individuazione della colonna e non solo della riga della matrice su cui cerchiamo i valori. Per esempio =INDICE( DatiProduzione[#Tutti]; CONFRONTA(A5; DatiProduzione[[#Tutti];[ARTCOD]]; 0); CONFRONTA(“Produzione”; DatiProduzione[#Intestazioni]; 0)), cerca il valore presente in A5 nella tabella Excel con nome “DatiProduzione” nel campo “Artcod” e restituisce il valore corrispondente nella colonna con titolo “Produzione”. Dietro la relativa complessità, questa formula continuerà a lavorare nonostante le modifiche compiute sulla tabella, a meno di modificare i titoli dei campi indicati (Artcod e Produzione).

Vale la pena sottolineare che Confronta, oltre a cercare il primo valore preciso indicato (terzo parametro = 0), qualunque sia l’ordine dei record della matrice, permette di cercare valori approssimati.

Se corr viene impostato a 1, oppure viene omesso il parametro, la funzione troverà il valore minore più prossimo o uguale a quello ricercato, ma per questo è necessario che i valori nella matrice siano disposti in ordine crescente.

Se invece corr viene impostato a -1, la funzione cercherà il valore maggiore più prossimo o uguale a quello cercato, e per questo è necessario che i valori nella matrice siano ordinati in modo decrescente.

In pratica queste opzioni vengono utilizzate in modo limitato, per situazioni particolari, analoghe a quelle indicate per Cerca.Vert.

Infine è necessario sottolineare che anche Indice e Confronta, come le altre funzioni di ricerca, richiedono la gestione degli errori. In fondo se una funzione di ricerca non trova il valore indicato, non può restituirlo. Quindi anche queste funzioni vengono spesso accompagnate da funzioni per la gestione degli errori come Se.Errore o Se e Val.Errore.

RICERCA

RICERCA (testo; stringa; inizio)

È la prima funzione di ricerca specifica per i testi, che non fa altro che cercare una stringa all’interno di un testo per restituire un numero intero che corrisponde alla posizione iniziale della stringa individuata nel testo. Per esempio, se cerchiamo “pos” in “Composizione del prodotto” restituirà il valore 4.

Se la stringa non viene trovata viene restituito un errore (#Valore). Inoltre permette di indicare facoltativamente da quale carattere del testo la funzione deve iniziare a cercare (parametro “inizio”). Per esempio con: RICERCA(“yh”; B5; 3) la funzione cercherà “yh” nella cella B5 a partire dal terzo carattere e se la troverà restituirà la sua posizione. Altrimenti un errore.

Perché cercare stringhe in questo modo?

Per diversi motivi, dalla ricerca di parole chiave in un testo, all’estrazione di una porzione identificabile del testo stesso, alla sostituzione o inserimento di stringhe all’interno di testi, codici, chiavi o altro. Questo può essere fatto anche con funzioni come Rimpiazza(), Stringa.Estrai() o Sostituisci().

È importante sottolineare che:

Ricerca() non rileva la differenza tra maiuscole e minuscole,
mentre è possibile usare i caratteri jolly, per esempio il punto interrogativo (?), che rappresenta un singolo carattere, e l’asterisco (*), che invece rappresenta una sequenza qualsiasi di caratteri,
se il valore di inizio è minore di 1 o maggiore della lunghezza del testo, viene restituito un errore (#Value!).

Nonostante Excel non sia un word processor, è dotato di strumenti che possono manipolare finemente testi inseriti in celle o tabelle. Questo può essere utile per costruire e manipolare chiavi alfanumeriche codificate, come per individuare ed estrarre stringhe rilevanti, perfino correggere e costruire testi e note.

TROVA

TROVA (testo; stringa; inizio)

È la funzione gemella della precedente, funziona quasi allo stesso modo, con alcune differenze che possono diventare significative in certe situazioni. In particolare:

distingue tra lettere maiuscole e minuscole,
non supporta i caratteri jolly,
se la stringa è vuota (“”), restituirà il primo carattere della stringa o il numero di inizio.

Trova è utile quando dobbiamo lavorare su maiuscole, ma non permette di usare caratteri jolly con cui rendere più efficiente la ricerca.

Quando si usano le funzioni Excel di ricerca

Si usano per individuare ed estrarre dati da un database allo scopo di visualizzarli (es: stato dell’ordine di fornitura), oppure per elaborarli per ottenere un preciso risultato (es: tempi di produzione).

Queste funzioni sono le protagoniste di tabelle di formule in funzionalità di elaborazione o in report o dashboard, quindi usate in modo preciso e specifico, spesso insieme a funzioni per l’elaborazione numerica (es: somma.più.se) o a funzioni logiche.

Le funzioni di ricerca sono anche usate in tabelle excel o tabelle di formule, per visualizzare o per elaborare dati estratti da tabelle diverse, inserendo o generando interi campi di informazioni. Per esempio, abbiamo bisogno di visualizzare i dati del fornitore insieme alle giacenze del magazzino, o gli ordini di produzione insieme a quelli del cliente, o le ore lavorate nei costi aziendali e attingiamo a tabelle diverse per visualizzare informazioni che non sono già presenti nella tabella, come il fornitore, la data di consegna, i centri di costo, eccetera.

Questo può essere comodo, in effetti permette in modo veloce di inserire nella tabella interi campi di informazioni collegati a tabelle terze che si aggiornano in modo dinamico insieme alla fonte. D’altra parte questo tipo di uso, se applicato in modo estensivo, può generare complicazioni, in primo luogo nell’impegno di grandi quantità di risorse (tempi di gestione, memoria, grandezza del file, ecc) che riducono le prestazioni del foglio anche in modo rilevante.

Quindi è opportuno tenere sotto controllo l’uso di queste funzioni se le impieghiamo a migliaia nei nostri fogli.

Usare le funzioni Excel per unire 2 tabelle

Approfondiamo un attimo l’uso delle funzioni di ricerca come strumenti con cui unire in modo dinamico due tabelle di dati.

Come abbiamo già visto le funzioni di ricerca, quando vengono applicate in campi aggiuntivi specifici, come formule inserite in ogni riga, permettono di aggiungere dati relativi ai singoli record presenti in tabelle esterne.

Questo richiede la presenza nei record di entrambe le tabelle di chiavi univoche, cioè di informazioni che identificano in modo certo i singoli record, come per esempio un codice cliente, un codice articolo, un centro di costo, e così via. La presenza di questi valori permette di connettere i record delle tabelle in modo da garantire la corrispondenza corretta dei dati.

Questi valori sono usati dalle funzioni di ricerca per inserire le informazioni della seconda tabella che vogliamo visualizzare nella prima se non dispone già delle informazioni relative.

È il modo migliore per raggiungere il risultato?

No. Il modo migliore è ottenere una base dati completa da monte, cioè se i dati sono ottenuti dal gestionale, ottenere una base dati con tutte le informazioni che ci servono. Questo è la soluzione al problema che dovremmo cercare prima di unire due tabelle in Excel.

Laddove non è possibile, dove ci troviamo a lavorare su più basi dati e abbiamo l’esigenza di unirle o di elaborarle in modo concertato, ebbene le funzioni di ricerca non sono lo strumento migliore per farlo, sicuramente non quello più efficiente.

Excel dispone di altri strumenti specifici con cui lavorare sulle basi dati o con cui elaborare più tabelle di dati distinte. Parlo naturalmente di Power Query, ora chiamato Recupera e trasforma, dei modelli di dati e di Power Pivot. Il primo permette di creare query sia a database esterni che interni, quindi anche a tabelle Excel, con cui possiamo filtrare, ristrutturare, ripulire i dati e riversarli in tabelle dinamiche. I modelli dei dati permettono di collegare e gestire più database in una struttura unica che può essere usata per l’elaborazione con strumenti come Power Pivot.

Certo, puoi usare anche le funzioni di ricerca che abbiamo visto. Ma se la quantità di dati è considerevole e il foglio Excel di lunga durata ti invito a usare strumenti più efficienti.

Conclusioni

Le funzioni di ricerca sono strumenti potenti di excel con cui possiamo estrarre dati specifici per visualizzarli in tabelle o elaborarli. Abbiamo visto quali sono le funzioni più efficaci ed efficienti, in primis Cerca.Vert() e Indice+Confronta, come si usano e quali sono i loro limiti e caratteristiche.

Abbiamo anche visto che sono usate prevalentemente per inserire campi interi o per unire tabelle, con conseguenze a livello di prestazioni e risorse impiegate. Se un tempo non c’erano molte alternative, negli ultimi anni però Excel si è evoluto e ora dispone di strumenti avanzati, come Recupera e trasforma e power Pivot che permettono di ottenere gli stessi risultati in modo più efficiente.

Questo non è un invito ad abbandonare le funzioni di ricerca, che rimangono probabilmente le funzioni più utilizzate in report, dashboard e tabelle di formule, e che devi conoscere bene se lavori con Excel.

È piuttosto un invito ad approfondire la conoscenza degli strumenti avanzati di Excel, perché spesso permettono di ottenere risultati analoghi o perfino migliori in modo più efficiente.

Il post termina qui. Viste le richieste è probabile che torneremo a parlare di funzioni e del loro uso più pratico e avanzato.

Non esitare a lasciare commenti o a porre domande.

A presto ;D

Le funzioni Excel di ricerca