#Excel – CERCA.VERT più veloce con 2 CERCA.VERT

Con grandi insiemi di dati, usare la funzione CERCA.VERT con corrispondenza esatta può essere decisamente lento, ma potete rendere CERCA.VERT molto più veloce utilizzando due CERCA.VERT, come spiegato di seguito.

INFO
Se avete un insieme ridotto di dati e non notate problemi di prestazioni, questo approccio è eccessivo. Da utilizzare solo con grandi quantità di dati quando la velocità conta davvero.

CERCA.VERT con corrispondenza esatta è lento

L’utilizzo della funzione CERCA.VERT in “modalità corrispondenza esatta” su un ampio insieme di dati, può davvero rallentare il tempo di calcolo di un foglio di lavoro. Con, diciamo, 50.000 record, o 100.000 record, il calcolo può richiedere alcuni minuti.

La corrispondenza esatta viene impostata fornendo FALSO o zero come quarto argomento.

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

Il motivo per cui CERCA.VERT in questa modalità è lento è perché deve controllare ogni singolo record nel set di dati fino a quando non viene trovata una corrispondenza. Questa è talvolta indicata come “ricerca lineare”.

CERCA.VERT con corrispondenza approssimativa è molto veloce

In modalità corrispondenza approssimativa, CERCA.VERT è estremamente veloce. Per utilizzare la corrispondenza approssimativa con CERCA.VERT, è necessario ordinare i dati in base alla prima colonna (la colonna di ricerca), quindi specificare VERO come 4° argomento (oppure ometterlo, visto che è l’impostazione di default):

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

Con insiemi di dati molto grandi, passare ad una corrispondenza approssimativa può significare un aumento drammatico di velocità.

Quindi, non state a pensarci, giusto? Basta ordinare i dati, utilizzare la corrispondenza approssimativa, e il gioco è fatto.

Non così in fretta (eh).

Il problema con CERCA.VERT in modalità “corrispondenza approssimativa” è questo: CERCA.VERT non visualizzerà nessun errore se il valore di ricerca non esiste. Peggio ancora, il risultato può sembrare del tutto normale, anche se è del tutto sbagliato. Non è qualcosa che vorreste spiegare ai vostri capi.

LEGGI:  #Excel - Come trovare il k-esimo valore più piccolo con criteri

La soluzione è quella di utilizzare CERCA.VERT due volte, entrambe le volte in modalità corrispondenza approssimativa:

=SE(CERCA.VERT(valore; matrice_tabella; indice; VERO)=valore; CERCA.VERT(valore; matrice_tabella; indice; VERO); NON.DISP())

Come funziona questa formula

La prima istanza di CERCA.VERT semplicemente cerca il valore di ricerca (valore in questo esempio):

=SE(CERCA.VERT(valore; matrice_tabella; indice; VERO)=valore

e restituisce VERO solo quando viene trovato il valore di ricerca. In quel caso, la formula attiva CERCA.VERT di nuovo in modalità corrispondenza approssimativa per recuperare un valore da matrice_tabella:

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

Non c’è pericolo incorrere in un valore di ricerca mancante, dal momento che la prima parte della formula ha già controllato che il valore sia presente.

Se il valore di ricerca non venisse trovato, la parte “valore se FALSO” della funzione SE viene attivata, e potete restituire qualsiasi valore che vi aggradi. In questo esempio, usiamo NON.DISP() per restituire un errore #N/D, ma potreste anche restituire un messaggio come “mancante” o “non trovato”.

ATTENZIONE
Ricordate che è necessario ordinare i dati in base al valore di ricerca in modo che questo trucco funzioni.

 

Copyright © 2014-2017  Office Academy. Tutti i diritti riservati.
Vai alla barra degli strumenti