In diversi articoli pubblicati precedentemente abbiamo cercato di spiegare le basi della funzione di Excel CERCA.VERT sia per principianti sia fornendo esempi di formule più complesse per utenti esperti. E ora, cercherò invece di mostrarvi un modo alternativo per fare una ricerca verticale in Excel.
“A cosa mi potrebbe servire?” Potreste chiedermi. Perché CERCA.VERT non è l’unica formula di ricerca disponibile in Excel, e le sue numerose limitazioni potrebbero impedirvi di ottenere il risultato desiderato in molte situazioni. D’altra parte, INDICE e CONFRONTA sono più flessibili e hanno alcune caratteristiche che le rendono superiori a CERCA.VERT sotto molti aspetti.
Le funzioni INDICE e CONFRONTA di Excel – Le basi
Dal momento che l’obiettivo di questo tutorial è quello di dimostrare un modo alternativo per fare un CERCA.VERT in Excel, utilizzando la combinazione di funzioni INDICE e CONFRONTA, non ci dilungheremo molto sulle loro sintassi e utilizzi. Ci occuperemo solo il minimo necessario per comprendere l’idea generale e poi dare uno sguardo in profondità su esempi di formule che rivelano tutti i vantaggi di utilizzare INDICE / CONFRONTA invece di CERCA.VERT.
Sintassi e uso della funzione INDICE
La funzione di Excel INDICE restituisce un valore da una matrice in base ai numeri di riga e di colonna specificati. La sintassi della funzione INDICE è semplice:
INDICE(matrice; riga; [col])
Ecco una spiegazione molto semplice di ogni parametro:
- matrice – questo è un intervallo di celle da cui si desidera restituire un valore
- riga – il numero di riga in matrice da cui si desidera restituire un valore. Se omesso, è necessario indicare il parametro col
- col – il numero di colonna in matrice da cui si desidera restituire un valore. Se omesso, è necessario indicare il parametro riga
Se si utilizzano entrambi i parametri riga e col, la funzione INDICE restituisce il valore della cella all’intersezione della riga e della colonna specificate.
E qui è l’esempio più semplice della formula INDICE:
=INDICE(A1:C10; 2; 3)
Le formula ricerca nelle celle A1-C10 e restituisce il valore della cella nella 2^ riga e nella 3^ colonna, vale a dire la cella C2.
Molto facile, giusto? Tuttavia, quando si lavora con i dati reali difficilmente sapete quale riga e colonna volete trovare, è per questo che è necessario l’aiuto della funzione CONFRONTA.
Sintassi e uso della funzione CONFRONTA
La funzione CONFRONTA di Excel cerca un valore di ricerca in un intervallo di celle, e restituisce la posizione relativa di detto valore nell’intervallo.
Ad esempio, se l’intervallo B1:B3 contiene i valori “Milano”, “Firenze”, “Roma”, allora la formula =CONFRONTA(“Roma”; B1:B3; 0) restituisce il numero 3, perché “Roma” è il terzo elemento dell’intervallo.
La sintassi della funzione CONFRONTA è la seguente:
CONFRONTA(valore; matrice; [corrisp])
- valore – questo è il numero o il testo che si sta cercando. Può essere un valore, un riferimento di cella o un valore logico
- matrice – l’intervallo di celle da ricercare
- corrisp – questo parametro indica alla funzione CONFRONTA se si desidera restituire una corrispondenza esatta o la corrispondenza più vicina:
- 1 o omesso – trova il valore più grande che è minore o uguale al valore di ricerca. I valori nella matrice di ricerca devono essere ordinati in ordine crescente, cioè dal più piccolo al più grande
- 0 – trova il primo valore che è esattamente uguale al valore di ricerca. Nella combinazione di INDICE / CONFRONTA, è quasi sempre necessaria la corrispondenza esatta, pertanto il terzo argomento della funzione CONFRONTA è “0”
- -1 – Trova il valore più piccolo che è maggiore o uguale a valore. I valori nella matrice di ricerca devono essere ordinati in ordine decrescente, cioè dal più grande al più piccolo
A prima vista, l’utilità della funzione CONFRONTA può sembrare discutibile. A chi interessa la posizione di un valore in un intervallo? Quello che vogliamo conoscere è il valore stesso.
Vi ricordo che la posizione relativa del valore di ricerca (vale a dire la riga e/o il numero di colonna) è esattamente ciò che è necessario fornire agli argomenti riga o col della funzione INDICE. Come ricorderete, la funzione INDICE può restituire il valore all’intersezione di una data riga e colonna, ma non può determinare quali riga e colonna scegliere.
Come usare le funzioni INDICE e CONFRONTA in Excel
Ora che sapete le basi di queste due funzioni, credo che abbiate già iniziato a dare un senso a come esse lavorino insieme.
La funzione CONFRONTA determina la posizione relativa del valore di ricerca nell’intervallo di celle. Da lì, la funzione INDICE prende quel numero, o numeri, e restituisce il valore della cella corrispondente.
Avete ancora difficoltà a capirlo? Pensate a INDICE / CONFRONTA in questo modo:
=INDICE(colonna da cui restituire un valore; (CONFRONTA(valore di ricerca; colonna in cui ricercare il valore; 0))
Credo che sia ancora più facile da capire con un esempio. Supponiamo di avere un elenco di capoluoghi di regione come questo:
Troviamo la popolazione di un certo capoluogo, ad esempio il capoluogo della Sicilia, utilizzando la seguente formula INDICE / CONFRONTA:
=INDICE($D$2:$D$10; CONFRONTA("Sicilia"; $B$2:$B$10; 0))
Ora, cerchiamo di analizzare ciò che fa ogni parte di questa formula:
- La funzione CONFRONTA cerca il valore di ricerca “Sicilia” nella colonna B, più precisamente nelle celle B2:B10, e restituisce il numero 5, perché “Sicilia” è la quinta voce nella lista
- La funzione INDICE utilizza “5” nel secondo parametro (riga), che indica da quale riga si desidera restituire il valore, e si trasforma in un semplice =INDICE($D$2:$D$10; 5). Tradotto in parole povere, la formula dice: ricerca nelle celle da D2 a D10 e restituisci il valore della cella in 5^ riga, cioè la cella D6 (perché cominciamo a contare dalla seconda riga)
Ed ecco il risultato che si ottiene in Excel:
Aspetta, aspetta … perché non usiamo semplicemente la seguente formula CERCA.VERT? Qual è il senso di perdere tempo cercando di capire le arcane acrobazie di INDICE / CONFRONTA?
=CERCA.VERT("Sicilia"; $B$2:$D$2; 3)
In questo caso, nessun senso 🙂 Questo semplice esempio è solo a scopo dimostrativo, in modo da dare l’idea di come le funzioni INDICE e CONFRONTA lavorano insieme. Altri esempi che seguono qui sotto vi mostreranno il vero potere di INDICE / CONFRONTA, che affronta facilmente molti scenari complessi in cui CERCA.VERT inciampa.
Perché INDICE / CONFRONTA è meglio di CERCA.VERT
Al momento di decidere quale formula utilizzare per le ricerche verticali, la maggior parte dei guru di Excel concordano sul fatto che INDICE / CONFRONTA è molto meglio di CERCA.VERT. Tuttavia, molti utenti di Excel ancora continuano ad utilizzare CERCA.VERT perché è una funzione semplice. Questo accade perché pochissime persone comprendono appieno tutti i vantaggi del passaggio da CERCA.VERT a INDICE / CONFRONTA, e senza tale comprensione non sono disposte a investire del tempo per imparare una formula più complessa.
Qui di seguito, cercherò di evidenziare i vantaggi di utilizzare INDICE / CONFRONTA. Leggeteli, e poi decidere se fare anche voi il “grande salto”.
I 4 principali vantaggi di utilizzare INDICE / CONFRONTA in Excel
- Ricerca da destra a sinistra. Come ogni utente istruito sa, CERCA.VERT non può cercare alla sua sinistra, il che significa che il valore di ricerca deve sempre risiedere nella colonna più a sinistra dell’intervallo di ricerca. Con INDICE / CONFRONTA, la colonna di ricerca può essere anche sul lato destro della tabella. Il seguente esempio mostra questa funzione in azione – Come ricercare valori verso sinistra
- Inserire o eliminare colonne in modo sicuro. Le formule CERCA.VERT restituiscono errori oppure risultati non corretti quando una nuova colonna viene eliminata o aggiunta alla tabella di ricerca. Con CERCA.VERT, ogni colonna inserita o cancellata cambia i risultati restituiti dalle formule perché la sintassi della funzione CERCA.VERT richiede di specificare l’intera tabella e un certo numero che indica quale colonna che contiene i dati da estrarre. Ad esempio, se avete la tabella A1:C10 e volete restituire un valore da colonna B, metterete “2” nel terzo parametro (indice) della formula CERCA.VERT, vale a dire =CERCA.VERT(“valore di ricerca”; A1:C10; 2). Se in un momento successivo, inseriste una nuova colonna tra A e B, dovrete cambiare “2” in “3” nella formula, altrimenti vi vedrete restituire valori dalla colonna appena inserita.Con INDICE / CONFRONTA, è possibile eliminare o inserire nuove colonne nella tabella di ricerca senza distorcere i risultati in quanto si specifica direttamente la colonna contenente il valore che si desidera ottenere. E questo è davvero un grande vantaggio, soprattutto quando si lavora con ampie serie di dati, dal momento che si è in grado di inserire e rimuovere colonne, senza preoccuparsi di aggiornare ogni formula CERCA.VERT associata.
- Nessun limite per le dimensioni del valore di ricerca. Quando utilizzate la funzione CERCA.VERT, ricordate che la lunghezza totale dei vostri criteri di ricerca non deve superare i 255 caratteri, altrimenti si finirà per avere il valore d’errore #VALORE!. Quindi, se il set di dati contiene lunghe stringhe, INDICE / CONFRONTA è l’unica soluzione di lavoro.Supponiamo di utilizzare la seguente formula CERCA.VERT per cercare tra le celle da B5 a D10 il valore della cella A2:=CERCA.VERT(A2; B5:D10; 3; FALSO)La formula non funziona se il valore di ricerca nella cella A2 supera i 255 caratteri. Occorre quindi usare la formula INDICE / CONFRONTA analoga:=INDICE(D5:D10; CONFRONTA(VERO; INDICE(B5:B10=A2; 0); 0))
- Velocità di elaborazione maggiore. Se si lavora con tabelle relativamente piccole, la differenza in termini di prestazioni di Excel sarà probabilmente impercettibile, soprattutto nelle versioni più recenti. Ma se si utilizza grandi fogli di lavoro con migliaia di righe e migliaia di formule di ricerca, Excel funzionerà molto più velocemente se si utilizza INDICE / CONFRONTA piuttosto che CERCA.VERT. In generale, l’uso di formule INDICE / CONFRONTA aumenta le prestazioni di Excel del 13% rispetto alle formule CERCA.VERT equivalenti.L’impatto di CERCA.VERT sulle prestazioni di Excel può essere particolarmente evidente se la cartella di lavoro contiene centinaia di formule matriciali complesse come CERCA.VERT e SOMMA. Il punto è che la verifica di ogni valore nella matrice richiede una chiamata separata della funzione CERCA.VERT. Così, più valori la matrice contiene, più formule matriciali avete in una cartella di lavoro, più lento Excel sarà.D’altra parte, con INDICE / CONFRONTA, Excel deve considerare solo la ricerca e restituire le colonne, come risultato elaborerà tali formule molto più velocemente.
Esempi di formule INDICE / CONFRONTA
Ora che conoscete i motivi per imparare le funzioni INDICE / CONFRONTA, veniamo alla parte più interessante e vediamo come sia possibile applicare le conoscenze teoriche alla pratica.
Come cercare valori verso sinistra con INDICE / CONFRONTA
Come indicato in ogni tutorial su CERCA.VERT, questa funzione di Excel non può cercare alla sua sinistra. Quindi, a meno che la colonna di ricerca è la colonna più a sinistra dell’intervallo di ricerca, non c’è alcuna possibilità che una formula CERCA.VERT restituisca il risultato desiderato.
La funzione INDICE / CONFRONTA è più flessibile e in realtà non importa dove si trovi la colonna da restituire. A titolo di esempio, useremo di nuovo la tabella che elenca i capoluoghi di regione con il numero degli abitanti. Questa volta, scriviamo una formula INDICE / CONFRONTA per scoprire come si collochi il capoluogo del Piemonte, Torino, in termini di popolazione.
Come si può vedere nella schermata qui sotto, la seguente formula funziona perfettamente:
=INDICE($A$2:$A$10; CONFRONTA("Piemonte"; $B$2:$B$10; 0))
A questo punto, non dovreste avere alcuna difficoltà a capire come funziona la formula:
- In primo luogo, si scrive una semplice formula CONFRONTA che trova la posizione del Piemonte:=CONFRONTA(“Piemonte”; $B$2:$B$10; 0))
- Poi, si determina l’argomento matrice per la funzione INDICE, che nel nostro caso è la colonna A (A2:A10)
- Infine, si assemblano le due parti insieme per ottenere questa formula:=INDICE($A$2:$A$10; CONFRONTA(“Piemonte”; $B$2:$B$10; 0))
Calcoli con INDICE / CONFRONTA in Excel (MEDIA, MAX, MIN)
È possibile nidificare le altre funzioni di Excel all’interno della formula INDICE / CONFRONTA, ad esempio, per trovare il valore minimo o massimo, o il valore più vicino alla media dell’intervallo. Ecco alcuni esempi di formule per la tabella utilizzata nell’esempio precedente:
| Funzione | Formula di esempio | Descrizione | Risultato restituito |
| Min | =INDICE($C$2:$C$10; CONFRONTA(MIN($D$2:D$10); $D$2:D$10; 0)) | Trova il valore minimo di colonna D e restituisce il valore dalla colonna C, nella stessa riga. | Bari |
| Max | =INDICE($C$2:$C$10; CONFRONTA(MAX($D$2:D$10); $D$2:D$10; 0)) | Trova il valore massimo in colonna D e restituisce il valore dalla colonna C, nella stessa riga. | Roma |
| Media | =INDICE($C$2:$C$10; CONFRONTA(MEDIA($D$2:D$10); $D$2:D$10; 1)) | Calcola la media dell’intervallo D2:D10, trova il valore più vicino alla media e restituisce il valore corrispondente da colonna C. | Torino |
Cose da tenere a mente quando si usa MEDIA con INDICE / CONFRONTA
Quando si utilizza la funzione MEDIA, in combinazione con INDICE / CONFRONTA, il più delle volte è necessario immettere “1” o “-1” nel terzo argomento (corrisp) della funzione CONFRONTA, se non si è certi che la vostra matrice di ricerca contenga un valore esattamente uguale alla media. Se è questo il vostro caso, potete immettere “0” per ottenere una corrispondenza esatta.
Se si imposta 1, i valori nella colonna di ricerca devono essere ordinati in ordine crescente, e la formula restituirà il valore più grande che è minore o uguale al valore medio.
Se si imposta -1, i valori nella colonna di ricerca devono essere ordinati in ordine decrescente, e sarà restituito il più piccolo valore che è maggiore o uguale al valore medio.
Nel nostro esempio, i valori nella colonna D sono ordinati in ordine crescente, in modo da utilizzare “1”, come il tipo di corrispondenza, e la nostra formula MEDIA + INDICE / CONFRONTA restituisce “Torino”, in quanto la sua popolazione (907.563) è il numero “minore di” più vicino al numero medio (920.938).
Come utilizzare INDICE / CONFRONTA per cercare valori per riga e colonna
Questa formula è equivalente ad un CERCA.VERT bidimensionale che permette di trovare il valore all’intersezione di una certa riga e colonna.
In questo esempio, la funzione INDICE / CONFRONTA è molto simile ad altre formule che abbiamo già discusso in questo tutorial, con un’unica differenza. Indovinate un po?
Come ricorderete, la sintassi della funzione INDICE permette di indicare sia la riga che la colonna. E mi congratulo con quelli di voi che hanno indovinato 🙂
Per cominciare, creiamo il modello generale di formula. Prendiamo semplicemente la formula INDICE / CONFRONTA che già conosciamo e aggiungiamo un altra funzione CONFRONTA, che restituirà il numero di colonna:
=INDICE(matrice; (CONFRONTA(valore di ricerca verticale; colonna in cui cercare; 0)); (CONFRONTA(valore di ricerca orizzontale; riga in cui cercare; 0))
Fate attenzione a specificare l’intera tabella nell’argomento matrice della funzione INDICE in caso di ricerca a due vie.
E ora, cerchiamo di applicare questo modello di formula nella pratica. Qui di seguito, elenchiamo le regioni italiane ed il numero dei residenti negli ultimi 3 anni. Supponiamo di voler conoscere la popolazione della Liguria nel 2014:
Va bene, cominciamo con la formula. Ogni volta che ho bisogno di creare una formula complessa di Excel, con una o più funzioni nidificate, scrivo sempre prima ogni singola funzione singolarmente.
Quindi, iniziamo scrivendo due funzioni CONFRONTA che restituiranno il numero di riga e colonna per la funzione INDICE.
- Ricerca verticale – cerchiamo lungo la colonna A, più precisamente nelle celle da A1 a A21, il valore di cella G2 (“Liguria”), e la funzione CONFRONTA corrispondente è questa: =CONFRONTA($G$2; $A$1:$A$21; 0) Questa formula CONFRONTA restituisce 9 perché “Liguria” è il 9° elemento nella colonna A (compresa l’intestazione di colonna).
- Ricerca orizzontale – cerchiamo il valore nella cella G3 ( “2014”) nella riga 1, vale a dire nelle celle da A1 a D1: =CONFRONTA($G$3; $A$1:$D$1; 0). Questa formula CONFRONTA restituisce “3”, perché “2014” è la 3^ colonna.
Ora, mettete le formule di cui sopra all’interno della funzione INDICE, et voilà:
=INDICE($A$1:$D$21; CONFRONTA($G$2; $A$1:$A$21; 0); CONFRONTA($G$3; $A$1:$D$1; 0))
Se provate a sostituire le funzioni CONFRONTA con i rispettivi valori restituiti, la formula sarà molto più facile da capire: =INDICE($A$1:$D$21; 9; 3).
Ossia, restituisce un valore all’intersezione della riga 9 e della colonna 3 dell’intervallo A1:D21, che è il valore nella cella C9. Facile? Sì! 🙂
Cercare con criteri multipli utilizzando INDICE / CONFRONTA
Nel tutorial sulla funzione CERCA.VERT di Excel, ho mostrato una formula di esempio per fare un CERCA.VERT con criteri multipli. Tuttavia, una limitazione significativa di tale approccio è la necessità di aggiungere una colonna di supporto. La buona notizia è che anche la funzione INDICE / CONFRONTA può cercare valori in 2 colonne, in questo caso senza aver bisogno di colonne di supporto!
Supponiamo di avere una lista di ordini e che si voglia trovare la somma sulla base di 2 criteri, “Nome cliente” e “Prodotto”. Un fattore di complicazione è che un cliente può acquistare più prodotti e i nomi dei clienti sono elencati in ordine casuale nella tabella di ricerca:
La seguente formula matriciale INDICE / CONFRONTA riesce nell’intento:
{=INDICE('Tab di ricerca'!$A$2:$C$10; CONFRONTA(1; (A2='Tab di ricerca'!$A$2:$A$10) * (B2='Tab di ricerca'!$B$2:$B$10); 0); 3)}
Questa formula è più complessa rispetto alle altre che abbiamo discusso oggi, ma aiutati dalla conoscenza di INDICE / CONFRONTA che avrete già maturato, riuscirete sicuramente a comprenderla.
La parte più difficile è la funzione CONFRONTA, quindi cerchiamo di capirla per prima:
CONFRONTA(1; (A2='Tab di ricerca'!$A$2:$A$10) * (B2='Tab di ricerca'!$B$2:$B$10); 0)
Possiamo individuare i seguenti 3 argomenti:
- valore – 1
- matrice – (A2=’Tab di ricerca’!$A$2:$A$10) * (B2=’Tab di ricerca’!$B$2:$B$10)
- corrisp – 0
Il 1° e il 3° parametro sono cristallini – la funzione cerca “1”, e restituisce il primo valore trovato.
Ora, la domanda principale è: perché dobbiamo cercare “1”? Per ottenere la risposta, diamo uno sguardo più da vicino alla nostra matrice di ricerca.
Quello che facciamo qui è prendere il primo valore (A2) nella colonna “Cliente” della tabella principale e confrontarla con tutti i nomi dei clienti nella tabella di ricerca (A2:A10). Se viene trovata la corrispondenza, l’equazione restituisce VERO, altrimenti FALSO. Poi noi facciamo lo stesso per i valori in colonna B (“Prodotto”).
Per capire meglio di cosa sto parlando, potete selezionare le matrici nella barra della formula, e premere il tasto F9 per vedere come ciascuna delle parti selezionate viene valutata (si veda come valutare parti di una formula matriciale per ulteriori dettagli):
Come probabilmente sapete, nelle formule di Excel, il valore logico VERO equivale a 1 e FALSO equivale a 0. E poiché l’asterisco (*) funge da operatore E nelle formule matriciali, si ottiene “1” solo se la corrispondenza è trovata in entrambe le colonne, 0 altrimenti. Così, il risultato di questa operazione è una matrice di 1 e 0, dove “1” è il valore che soddisfa entrambe le condizioni specificate. Se la tabella di ricerca non ha righe duplicate, ci sarà un solo “1” nella matrice. E poiché “1” è il nostro valore di ricerca, la funzione CONFRONTA restituirà la posizione relativa di tale riga come illustrato nella seguente schermata:
Inoltre, vi invito a prestare attenzione che è necessario utilizzare il terzo parametro opzionale (col) della funzione INDICE. Questo perché si specifica l’intera tabella nel primo parametro (matrice), ed è necessario che la funzione sappia da quale colonna volete venga restituito il valore. Nel nostro caso si tratta della colonna C (“Somma”), così inseriamo 3 nel terzo argomento della funzione INDICE.
E, infine, dal momento che abbiamo bisogno di controllare ogni cella nella matrice, la nostra formula INDICE / CONFRONTA deve essere una formula matriciale. È possibile capirlo dalle parentesi graffe in cui la formula è incapsulata. Basta ricordarsi di premere Ctrl + Maiusc + Invio per completare la formula dopo aver finito l’inserimento.
Usare INDICE / CONFRONTA con SE.ERRORE
Come avrete probabilmente notato (più di una volta:)) se si immette un valore non valido, cioè un valore che non esiste nella matrice di ricerca, la funzione INDICE / CONFRONTA produce il messaggio di errore #N/D o #VALORE!. Se si preferisce sostituirlo con qualcosa di più significativo, si può avvolgere la formula INDICE / CONFRONTA nella funzione SE.ERRORE.
La sintassi della funzione SE.ERRORE è molto semplice:
SE.ERRORE(valore; se_errore)
Dove l’argomento valore è il valore in cui intercettare un errore (il risultato della formula INDICE / CONFRONTA nel nostro caso); e se_errore è il valore da restituire se la formula genera un errore.
Ad esempio, è possibile racchiudere la formula dell’esempio precedente all’interno della funzione SE.ERRORE in questo modo:
=SE.ERRORE(INDICE($A$1:$D$21; CONFRONTA($G$2; $A$1:$A$21; 0); CONFRONTA($G$3; $A$1:$D$1; 0)); "Nessuna corrispondenza trovata. Riprova!")
E ora, se qualcuno inserisse una voce non valida, la formula produrrà il risultato che si vede nello screenshot qui sotto:
Se preferite avere una cella vuota quando venga restituito un errore, si può semplicemente utilizzare le doppie virgolette (“”) nel secondo parametro di SE.ERRORE, come in questo esempio:
SE.ERRORE(INDICE(matrice; CONFRONTA(valore; matrice; 0); "")
Spero che almeno una formula descritta in questo tutorial si sia rivelata utile per voi. Alla prossima.
