#Excel – Come usare la funzione CERCA.VERT – Parte 2

Utilizzare CERCA.VERT con criteri multipli

La funzione CERCA.VERT di Excel è davvero insuperabile quando si tratta di ricercare un certo valore all’interno di un database. Tuttavia, è priva di una caratteristica importante – la sua sintassi consente solo un valore di ricerca. Ma cosa succede se voleste cercare in base a diverse condizioni? La soluzione è qui sotto 🙂

Esempio 1. Ricercare in base a 2 diversi criteri

Supponiamo che abbiate un elenco di ordini e che desideriate trovare la Quantità sulla base di 2 criteri: “Nome cliente” e “Prodotto”. Un fattore di complicazione è che ogni cliente ha ordinato più prodotti, come si vede nella tabella qui sotto:

La solita formula con CERCA.VERT non funzionerebbe in questo scenario, perché restituirebbe il primo valore trovato in base al criterio di ricerca specificato. Quindi, ad esempio, volendo sapere la quantità di “Uva” ordinata da “Bianchi Ugo” scriverete la formula =CERCA.VERT(B1;$A$5:$C$14;3;FALSO), vi restituirà “15” corrispondente a “Mele” in quanto questa è la prima corrispondenza.

Una semplice soluzione è la creazione di una colonna aggiuntiva che concateni tutti i criteri che volete, le colonne “Cliente” e “Prodotto” in questo esempio. Ricordatevi che la colonna concatenata dovrà sempre essere la colonna più a sinistra nel vostro intervallo di ricerca perché è qui che il CERCA.VERT di Excel cercherà il valore di ricerca.

Quindi, si aggiunge una colonna di supporto alla tabella e vi si inserisce una formula del tipo =B2&C2, copiandola lungo tutta la colonna (o =B2&” “&C2 se volete separare i valori concatenati con uno spazio per rendere i dati più leggibili).

A questo punto, è possibile utilizzare una semplice formula CERCA.VERT simile a questa:

=CERCA.VERT("Bianchi Ugo Uva";$A$7:$D$15;4;FALSO)

oppure

=CERCA.VERT(B1;$A$7:$D$15;4;FALSO)

Dove la cella B1 contiene il valore concatenato di ricerca (valore) e 4 è il numero della colonna contenente i dati che si desidera trovare (indice).

Esempio 2. Ricercare in base a 2 diversi criteri in fogli di lavoro diversi

Se volete utilizzare una tabella principale che raccolga dati da un’altra tabella (situata in un foglio o cartella di lavoro diversi), è possibile concatenare i valori di ricerca direttamente nella formula che inserirete nella tabella principale.

Come nell’esempio precedente, avrete bisogno di aggiungere una colonna di supporto alla tabella con i valori di ricerca concatenati e questa dovrebbe essere la colonna più a sinistra dell’intervallo di ricerca.

Quindi, la vostra formula CERCA.VERT potrebbe apparire come questa:

=CERCA.VERT(A2&" "&B2;Ordini!$A$2:$D$10;4;FALSO)

Dove le colonne A e B contengono rispettivamente i nomi dei clienti e dei prodotti, e Ordini!$A$2:$D$10 è la tabella di ricerca in un altro foglio di lavoro.

Inoltre, vi ricordiamo che la funzione CERCA.VERT di Excel ha il limite dei 255 caratteri. La funzione CERCA.VERT non può cercare un valore di ricerca che contiene più di 255 caratteri. Quindi, tenete questo numero a mente e assicuratevi che la lunghezza totale dei vostri criteri di ricerca non superi questo limite.

Sono d’accordo che l’aggiunta di una colonna di supporto non sia una soluzione molto elegante e non è sempre accettabile. Si può effettivamente fare a meno di tale colonna, ma in questo caso si avrà bisogno di una formula un po’ più complessa, con una combinazione delle funzioni INDICE() e CONFRONTA().

Come utilizzare CERCA.VERT per ottenere il 2°, il 3°, il 4°, valore corrispondente

Come già sapete, il CERCA.VERT di Excel può recuperare un solo valore corrispondente, più precisamente, il primo elemento trovato. Ma cosa succede se ci sono diverse corrispondenze nella vostra tabella di ricerca e desiderate che venga restituita la 2^ o la 3^? Inoltre, cosa succede se si vuole ottenere tutti i valori corrispondenti? Il compito parrebbe essere piuttosto complicato, ma esiste la soluzione!

Supponiamo che abbiate i nomi dei clienti in una colonna e i prodotti acquistati in un’altra. E ora, volete trovare il 2°, il 3°, o il 4° prodotto acquistato da un determinato cliente.

Il modo più semplice è quello di aggiungere una colonna di supporto prima della colonna con i nomi dei clienti e popolarlo con nome e il numero di occorrenza del cliente, ad esempio, “Rossi Mario1”, “Rossi Mario2”, ecc. La seguente formula CONTA.SE riesce nell’intento (supponendo che i nomi dei clienti siano in colonna B):

=B2&CONTA.SE($B$2:B2;B2)

Dopo di che, è possibile utilizzare la solita formula CERCA.VERT per trovare l’ordine corrispondente. Per esempio:

• Trovare il secondo prodotto acquistato da Rossi Mario:
  =CERCA.VERT(“Rossi Mario2”;$A$2:$C$10;3;FALSO)
• Trovare il terzo prodotto acquistato da Rossi Mario:
  =CERCA.VERT(“Rossi Mario3”;$A$2:$C$10;3;FALSO)

Naturalmente, è possibile inserire un riferimento di cella al posto del testo del valore di ricerca, come si vede nello screenshot qui sotto:

Se siete solo alla ricerca del 2° evento, potete fare anche senza la colonna di supporto con la creazione di una formula CERCA.VERT più complessa:

=SE.ERRORE(CERCA.VERT($E$2;INDIRETTO("$A$"&(CONFRONTA($E$2;Tabella5[Cliente];0)+2)&":$B10");2;FALSO);"")

Nella formula:

• $E$2 – la cella con il nome del cliente (che è costante, si prega di notare i riferimenti di cella assoluti);
• $A$ – la colonna con i nomi dei clienti;
• Tabella5[Cliente] – colonna di ricerca nella tabella o un intervallo di ricerca;
• $B10 – l’ultima cella (in basso a sinistra) della tabella di ricerca.

Se volete ottenere l’elenco di tutti i valori che corrispondono al criterio, la funzione CERCA.VERT non può esservi di aiuto, in quanto può restituire un solo valore alla volta, ahimè. Ma la funzione INDICE() di Excel è in grado di gestire questo scenario e vi mostrerò come nel prossimo esempio.

Come ottenere tutti i valori duplicati nell’intervallo di ricerca

Come accennato in precedenza, la funzione CERCA.VERT di Excel non riesce a restituire istanze duplicate del valore di ricerca. Per fare questo, avrete bisogno di una formula matriciale molto più complessa costituita da diverse funzioni di Excel come INDICE(), PICCOLO() e RIF.RIGA().

Ad esempio, la formula seguente trova tutte le istanze del valore presente in cella F2 nell’intervallo di ricerca B2:B16, e restituisce valori corrispondenti presenti in colonna C:

=SE.ERRORE(INDICE($B$2:$B$10; PICCOLO(SE($E$2=A2:A10; RIF.RIGA(B2:B10)-1;""); RIF.RIGA()-3));"")

Copiate la formula qui sopra in più celle adiacenti, ad esempio, nelle celle F4:F8 come mostrato nello screenshot qui sotto. Il numero di celle in cui si copia la formula dovrebbe essere uguale o superiore al numero massimo di possibili voci duplicate. Inoltre, ricordatevi di premere Ctrl + Maiusc + Invio per inserire correttamente la formula matriciale.

Se siete curiosi di conoscere la logica sottostante, cerchiamo di vedere un po’ in dettaglio la formula:

Parte 1: SE($E$2=A2:A10; RIF.RIGA(B2:B10)-1;””)

$E$2=A2:A10 – confronta il valore nella cella E2 con ogni valore nell’intervallo A2:A10. Se viene trovata una corrispondenza, RIF.RIGA(B2:B10) -1 restituisce il numero di riga corrispondente (-1 viene utilizzata per togliere la riga di intestazione). Se i valori confrontati non corrispondono, la funzione SE restituisce una stringa vuota.

Il risultato della funzione SE è la seguente matrice {1.””.3.””.””.””.””.8.””}

Parte 2: RIF.RIGA()-3

In questo caso, la funzione RIF.RIGA agisce come un contatore incrementale. Dal momento che la formula viene copiata nelle celle E4:E6, aggiungiamo -3 in modo che la funzione restituisca 1 per la cella E4 (riga 4 meno 3), 2 per la cella E5 (riga 5 meno 3), ecc.

Parte 3: PICCOLO(SE($E$2=A2:A10; RIF.RIGA(B2:B10)-1;””); RIF.RIGA()-3))

La funzione PICCOLO() restituisce l’n-esimo valore più piccolo in un insieme di dati. Nel nostro caso, la posizione (dalla più piccola) da restituire è definita dalla funzione RIF.RIGA (Parte 2). Così, per la cella E4, la funzione PICCOLO({matrice}; 1) restituirà il primo elemento (più piccolo) della matrice, ossia 1. Per la cella E5, restituirà il secondo elemento più piccolo della matrice, che è 3, e così via.

Parte 4: INDICE($B$2:$B$10; PICCOLO(SE($E$2=A2:A10; RIF.RIGA(B2:B10)-1;””); RIF.RIGA()-3))

La funzione INDICE() restituisce semplicemente il valore di una cella specificata nella matrice B2:B10. Per la cella E4, INDICE($B$2:$B$10;1) restituisce “Mele”; per la cella E5, INDICE($B$2:$B$10;3) restituisce “Pere”, ecc.

Parte 5: SE.ERRORE()

Infine, avvolgiamo la formula nella funzione SE.ERRORE, perché difficilmente vorreste vedere degli errori #N/D nel foglio di lavoro quando il numero delle celle in cui avete copiato la formula è inferiore al numero di occorrenze duplicate del valore di ricerca.

Come fare ricerche a due dimensioni in Excel

Eseguire una ricerca a 2 dimensioni in Excel significa cercare un valore sulla base di un valore di colonna e di riga. In altre parole, si cerca un valore all’intersezione di una determinata riga e colonna.

Quindi, usiamo la nostra tabella ‘Vendite mensili “e scriviamo la formula CERCA.VERT che trova il fatturato della vendita di banane nel mese di aprile.

Una ricerca a due dimensioni può essere fatta in diversi modi. Vi presento un po’ di possibili alternative tra cui potrete scegliere il vostro vincitore 🙂

Funzioni CERCA.VERT e CONFRONTA

Potete utilizzare le funzioni CERCA.VERT e CONFRONTA per fare un riferimento incrociato tra due campi di un database. In questo esempio sono Prodotto (riga) e Mese (colonna):

=CERCA.VERT("Banane";$A$2:$M$9;CONFRONTA("Apr";$A$1:$M$1;0);FALSO)

La formula è una normale funzione CERCA.VERT di Excel che cerca la corrispondenza esatta di “Banane” nelle celle da A2 a M9. Ma, dal momento che non si sa esattamente in quale colonna siano le vendite di aprile, non è possibile fornire il numero della colonna nel terzo argomento della formula CERCA.VERT. Invece, è possibile utilizzare la funzione CONFRONTA() per trovare quella colonna.

Tradotta in italiano, la funzione CONFRONTA(“Apr”; $A$1:$M$1; 0) significa:

• Cerca “Apr” (valore)
• Cercalo nelle celle tra A1 e M1 (matrice)
• Restituisci la corrispondenza esatta ([corrisp]). Utilizzando “0” nel 3° parametro, si istruisce la funzione CONFRONTA a trovare il primo valore che è esattamente uguale al valore di ricerca. Si può considerare come un equivalente del parametro Falso del CERCA.VERT.

Ecco come creare una formula di ricerca con due criteri in Excel, conosciuta anche come “a due vie”, o di ricerca bidimensionale.

La funzione MATR.SOMMA.PRODOTTO

La funzione MATR.SOMMA.PRODOTTO moltiplica i componenti nelle matrici specificate e restituisce la loro somma:

=MATR.SOMMA.PRODOTTO(($A$2:$A$9="Banane")*($A$1:$M$1="Apr");$A$2:$M$9)

Le funzioni INDICE e CONFRONTA

Vi spiegherò in dettaglio queste funzioni in uno dei prossimi articoli, in modo da poter semplicemente prendere la formula così com’è per ora:

=INDICE($A$2:$M$9;CONFRONTA("Banane";$A$2:$A$9;0);CONFRONTA("Apr";$A$1:$M$1;0))

Intervalli denominati & SPAZIO (operatore di intersezione)

Se non siete particolarmente affezionati a tutte quelle formule complesse di Excel, potrebbe piacervi questo sistema visivo e facile da ricordare:

• Selezionate la vostra tabella, passate alla scheda FORMULE e fate clic su “Crea da selezione” nel gruppo “Nomi definiti”.
• Selezionate le opzioni “Riga superiore” e “Colonna sinistra”. Microsoft Excel creerà i nomi direttamente dai valori della riga superiore e della colonna più a sinistra della selezione, e sarete in grado di cercare quei nomi direttamente invece di usare una formula.

• In una qualsiasi cella vuota, digitate =valore_di_colonna (spazio) valore_di _colonna o viceversa, ad esempio, =Banane Apr, oppure =Apr Banane
  Ricordate di separare il valore di riga e di colonna con uno spazio, che in questo caso funge da operatore di intersezione.
  Durante la digitazione, Microsoft Excel visualizzerà un elenco di nomi corrispondenti, esattamente come avviene quando si inizia a digitare una formula.

• Premete il tasto Invio e verificare il risultato.

Come fare più CERCA.VERT in una stessa formula (CERCA.VERT nidificati)

A volte può capitare che la tabella principale e la tabella di ricerca non abbiano una singola colonna in comune, impedendovi quindi di usare un normale CERCA.VERT. Tuttavia, esiste un’altra tabella, che non contiene le informazioni che state cercando, ma ha una colonna in comune con la vostra tabella principale e un’altra colonna in comune con la vostra tabella di ricerca.

Considerate il seguente esempio. Avete la tabella principale con una sola colonna, “Cod. prodotto (new)”, e avete bisogno di recuperare i prezzi corrispondenti da un’altra tabella. Poi, ci sono 2 tabelle di ricerca – la prima contiene gli stessi “Cod. prodotto (new)” e i nomi dei prodotti, mentre l’altra i nomi dei prodotti, i prezzi, ma i vecchi codici prodotto “Cod. prodotto (old)”.

Per recuperare i prezzi dalla tabella di ricerca 2 e inserirli nella tabella principale, è necessario eseguire il cosiddetto doppio CERCA.VERT, o CERCA.VERT nidificato.

1. Create una formula CERCA.VERT che trovi il nome del prodotto in “Tabella di ricerca 1”, utilizzando “Cod. prodotto (new)” come valore di ricerca:
   =CERCA.VERT(A2;Cod_new;2;FALSO)
   Dove “Cod_new” è un intervallo denominato per $A:$B in “Tabella di ricerca 1”
2. Scrivi la formula per recuperare i prezzi da “Tabella di ricerca 2”, in base al nome del prodotto, annidando la precedente funzione CERCA.VERT nei criteri di ricerca:
   =CERCA.VERT(CERCA.VERT(A2;Cod_new;2;FALSO);Prezzo;3;FALSO)
   Dove “Prezzo” è un intervallo denominato $A:$C in “Tabella di ricerca 2”, e 3 è la colonna C che contiene i prezzi.

La schermata qui sotto mostra il risultato restituito dalla nostra formula CERCA.VERT nidificata:

Usare CERCA.VERT e INDIRETTO per recuperare dinamicamente dati provenienti da fogli diversi

Per cominciare, definiamo che cosa significhi esattamente “recuperare dinamicamente dati provenienti da fogli diversi” 🙂

In alcuni casi, potreste avere dati nello stesso formato suddivisi tra più fogli di calcolo. Desiderate quindi estrarre i dati corrispondenti da un foglio specifico a seconda di quale valore venga immesso in una determinata cella. Penso che questo sia più facile da capire con un esempio.

Supponiamo che abbiate due report sulle vendite effettuate in regioni diverse, ma per gli stessi prodotti e nello stesso formato, e volete trovare l’importo delle vendite per una determinata regione:

Se avete solo un paio di report regionali, è possibile utilizzare una formula CERCA.VERT abbastanza semplice, con una funzione SE per selezionare il foglio corretto (in realtà l’intervallo corretto) per il CERCA.VERT:

=CERCA.VERT($D$2;SE(E2="Lombardia";Vendite_Lombardia;Vendite_Piemonte);2;FALSO)

Dove:

• $D$2 è una cella che contiene il nome del prodotto. Si noti che, in questo caso, usiamo riferimenti di cella assoluti per evitare che il valore di ricerca venga modificato durante la copia della formula in altre celle
• E2 è una cella con il nome della regione
• Vendite_Lombardia e Vendite_Piemonte sono nomi di tabelle, o di intervalli denominati, per i corrispondenti report di vendita. È possibile anche utilizzare nomi di fogli di lavoro e intervalli di celle standard, ad esempio, ‘Vendite Lombardia’!$A$3:$B$10, ma utilizzare intervalli denominati è molto più comodo

Tuttavia, se avete più tabelle di ricerca, la funzione SE non è la soluzione ideale. Invece, è possibile utilizzare la funzione INDIRETTO per restituire il campo di ricerca desiderato.

Come probabilmente sapete, la funzione INDIRETTO di Excel viene utilizzata per fare riferimento indirettamente ad una cella, e questo è esattamente quello che ci serve ora. Quindi, andiamo avanti e sostituiamo l’istruzione SE con INDIRETTO nella formula di cui sopra. La seguente combinazione di CERCA.VERT e INDIRETTO funziona a meraviglia nel nostro caso:

=CERCA.VERT($D$2;INDIRETTO("Vendite_"&E2);2;FALSO)

Dove:

• $D$2 è la cella contenente il nome del prodotto, rimane sempre costante grazie ai riferimenti assoluti
• “Vendite_” è la parte comune dei nostri nomi di intervallo, o nomi di tabella. Concatenato con il valore della cella E2 restituisce il nome completo dell’intervallo desiderato
• E2 è la cella contenente il nome della regione, “Lombardia” nel nostro caso. Qui di seguito fornirò qualche dettaglio in più per coloro che non hanno molta esperienza con la funzione INDIRETTO() di Excel

INDIRETTO e CERCA.VERT – Come funziona

Prima di tutto, vi ricordo la sintassi della funzione INDIRETTO: =INDIRETTO(rif; [a1]).

Il primo parametro può essere un riferimento di cella nel formato A1 oppure R1C1, un nome di intervallo, o una stringa di testo. Il secondo parametro specifica quale tipo di riferimento è contenuto in rif : se in formato A1 (VERO o omesso) o R1C1 (FALSO). Nel nostro caso è A1, in modo da poter omettere il secondo parametro e concentrarsi esclusivamente sul primo.

Ora, torniamo ai nostri report di vendita. Come ricorderete, ogni report è una tabella separata residente in un foglio a parte. Affinché la formula funzioni, è necessario assegnare un nome alle tabelle o agli intervalli, e tutti i nomi dovrebbero avere qualche parte in comune. Ad esempio, i miei report di vendita sono denominati: Vendite_Lombardia, Vendite_Piemonte, Vendite_Veneto, ecc. Come si vede, la parte”Vendite_” è comune.

Quindi, la nostra funzione INDIRETTO(“Vendite_”&E2) concatena la stringa “Vendite_” e il valore nella cella E2, e dice alla funzione CERCA.VERT esattamente in quale tabella cercare. Cioè, se abbiamo “Lombardia” nella cella E2, la formula cercherà nella tabella Vendite_Lombardia, se abbiamo “Piemonte” nella tabella Vendite_Piemonte, e così via.

Se i dati risiedono in cartelle di lavoro diverse, si dovrà aggiungere il nome della cartella di lavoro prima del nome della regione (Cartella!IntervalloDenominato), ad esempio:

=CERCA.VERT($D$2;INDIRETTO("Cartella1!Vendite_"&E2);2;FALSO)