Cerca.vert e Indice confronta in excel: analisi avanzate dei dati

La raccolta strutturata delle informazioni in un database, la gestione e il monitoraggio continuativo dei dati possono rappresentare vere e proprie opportunità per supportare gli addetti ai lavori nel prendere decisioni.
Nelle PMI è ancora molto comune l’utilizzo di excel per creare applicazioni customizzate  per eseguire elaborazioni e analisi dei dati. Lo strumento di casa Microsoft offre diverse potenzialità e la conoscenza delle sue funzioni può rappresentare un valore aggiunto per rendere più immediate le reportistiche o la lavorazione del dato.
Ricercare dati in excel  e confrontarli
Tra queste funzioni trovano spazio quelle di ricerca, che consentono di confrontare e ricercare informazioni da database o tabelle diverse attraverso l’individuazione di una chiave comune.
La funzione Cerca.Vert
La funzione più nota di ricerca è sicuramente quella denominata CERCA.VERT, che presenta la seguente forma:
=CERCA.VERT(VALORE MATRICE_TABELLA;INDICE;INTERVALLO)
Per sfruttare al meglio le potenzialità della funzione CERCA.VERT, è fondamentale la raccolta dei dati ordinata che il valore cercato (esempio il nome di un venditore) si trovi alla sinistra del valore restituito da trovare (ad esempio: numero di vendite effettuate).
La tabella qui sotto descrive la sintassi:
 
(fonte: https://support.office.com)
Esempio di applicazione della funzione Cerca.Vert
Supponiamo di avere un enorme database e di voler conoscere le vendite dell’agente.
Qui sotto per semplicità si riporta uno stralcio del database.

Mi colloco nella cella D20. La formula dovrà essere strutturata nel seguente modo:
=CERCA.VERT($C$2;$C$6:$E$17;2;FALSO)
o in alternativa
=CERCA.VERT(“Pippo”;$C$6:$E$17;2;FALSO)
La formula in pratica deve essere letta nel seguente modo :
Cercami il contenuto della cella C2 (Pippo) nell’area del database compresa nell’intervallo C6:E17, se lo trovi restituiscimi il valore corrispondente presente nella seconda colonna.
Nel nostro caso la formula darà il risultato 100.
Si suggerisce di usare sempre i riferimenti assoluti $$ per bloccare le celle soprattutto se la formula va trascinata per n righe.
Cerca.vert per confrontare dati tra diverse tabelle o file
La funzione Cerca.vert può avere applicazione anche nel confronto di informazioni contenute in due database o fogli distinti di excel. In questo caso ci dovrà essere sempre una id comune.
Supponiamo di volere associare l’area territoriale degli agenti che è contenuta in un’altra tabella; dovremo procedere come indicato qui di seguito:

-    Collochiamoci in una cella, ad esempio H21;
-    Scriviamo la seguente funzione: CERCA.VERT(C6;$H$6:$J$17;3;FALSO)
Ovvero:
cercami il valore contenuto nella cella C6 nell’intervallo H6:J17, se lo trovi restituiscimi il valore corrispondente contenuto nella colonna numero 3.
La funzione INDICE CONFRONTA
In alternativa alla funzione CERCA.Vert si può utilizzare anche la funzione indice-confornta.
Come utilizzare la funzione Indice Confronta
La funzione indice può essere rappresentata dalla sintassi indicata qui di seguito:
=INDICE(matrice; riga; colonna)
La funzione Confronta invece ha il compito di cercare un dato in un range di valori e restituisce la relativa posizione (il numero della riga).
Qui di seguito si riporta un esempio della sintassi:
=INDICE(Dati!$A$14:$D$18;CONFRONTA(Dashboard!C1; Dati!$C$14:$C$18;0);1)
Per vedere un esempio di applicazione si rimanda al seguente link:  http://www.b2corporate.com/excel-per-valorizzare-le-risorse-umane-scarica-il-modello-gratuito, dove era già stato trattato questo argomento, durante la creazione di un dashboard per la valorizzazione delle risorse umane.
Una questione di sensitivity lookups: la funzione Identico combinata con Indice Confronta
L’utilizzo di queste formule presuppone che i dati siano scritti correttamente.
Chandoo noto esperto indiano di excel evidenzia poi il concetto di sensitivity lookups e suggerisce di sfruttare un ulteriore sfumatura della formula quando c’è il rischio di avere parole uguali ma scritte in maniera non uguale.
Se ad esempio ho un database che contiene il nome Paolo scritto in diversi modi, come nella tabella riportata qui sotto:

Se noi applicassimo la funzione =CERCA.VERT(C15;$C$8:$D$11;2;FALSO)
Il risultato che otterremo sarà quello del primo Paolo che si trova nella tabella ovvero pAOLO.
Pertanto il valore che restituirà sarà 5.
Se invece volessimo trovare proprio il nome “Paolo” dovremmo applicare un funzione combinata indice confronta con “Identico”.
La funzione Identico, serve per il confronto di due stringhe di testo; in pratica restituisce VERO se le stringhe sono identiche e FALSO in caso contrario.
La funzione IDENTICO rileva le maiuscole, ma tuttavia ignora le differenze di formattazione. (per ulteriori dettagli leggi qui https://support.office.com/it-it/article/IDENTICO-funzione-IDENTICO-d3087698-fc15-4a15-9631-12575cf29926)
Tornando al nostro esempio applicheremo la seguente formula:
=INDICE($D$8:$D$11;CONFRONTA(VERO;IDENTICO(C15;$C$8:$C$11);0))
A cui andranno poi aggiunte le parentesi graffe con CTRL + MAIUSC +INVIO
Nell’esempio il valore restituito sarà 8.
Risorse per approfondire
Se ti interessa approfondire le tematiche di excel e scoprire nuove formule e campi di applicazione ti suggeriamo di leggere i seguenti ebook:

- Applicazioni aziendali in excel: casi ed esempi pratici

- Tabelle Pivot: analisi e Perfomance
- Modelli economici e finanziari in excel