Formula generica
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Sommario
Con grandi set di dati, la corrispondenza esatta CERCA.VERT può essere estremamente lenta, ma puoi velocizzare la RICERCA. CERCA.
Appunti:
- Se hai un set di dati più piccolo, questo approccio è eccessivo. Usalo solo con grandi set di dati quando la velocità conta davvero.
- È necessario ordinare i dati in base al valore di ricerca affinché questo trucco funzioni.
- In questo esempio vengono utilizzati intervalli denominati. Se non si desidera utilizzare intervalli denominati, utilizzare invece riferimenti assoluti.
CERCA.VERT con corrispondenza esatta è lento
Quando si utilizza CERCA.VERT in "modalità di corrispondenza esatta" su un ampio set di dati, può davvero rallentare il tempo di calcolo in un foglio di lavoro. Con, diciamo, 50.000 record o 100.000 record, il calcolo può richiedere minuti.
La corrispondenza esatta viene impostata fornendo FALSE o zero come quarto argomento:
=VLOOKUP(val,data,col,FALSE)
Il motivo per cui CERCA.VERT in questa modalità è lento è perché deve controllare ogni singolo record nel set di dati finché non viene trovata una corrispondenza. A volte si parla di ricerca lineare.
La corrispondenza approssimativa CERCA.VERT è molto veloce
Nella modalità di corrispondenza approssimativa, CERCA.VERT è estremamente veloce. Per utilizzare CERCA.VERT con corrispondenza approssimativa, è necessario ordinare i dati in base alla prima colonna (la colonna di ricerca), quindi specificare VERO per il quarto argomento:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP è impostato su true, che è un valore predefinito spaventoso, ma questa è un'altra storia).
Con set di dati molto grandi, passare a CERCA.VERT con corrispondenza approssimativa può significare un notevole aumento della velocità.
Quindi, gioco d'azzardo, giusto? Basta ordinare i dati, utilizzare la corrispondenza approssimativa e il gioco è fatto.
Non così velocemente (eh).
Il problema con CERCA.VERT in modalità "corrispondenza approssimativa" è questo: CERCA.VERT non visualizzerà un errore se il valore di ricerca non esiste. Peggio ancora, il risultato potrebbe sembrare completamente normale, anche se è completamente sbagliato (vedi esempi). Non qualcosa che vuoi spiegare al tuo capo.
La soluzione è utilizzare CERCA.VERT due volte, entrambe le volte in modalità di corrispondenza approssimativa:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Spiegazione
La prima istanza di CERCA.VERT cerca semplicemente il valore di ricerca (l' id in questo esempio):
=IF(VLOOKUP(id,data,1,TRUE)=id
e restituisce TRUE solo quando viene trovato il valore di ricerca. In tal caso,
la formula esegue nuovamente CERCA.VERT in modalità di corrispondenza approssimativa per recuperare un valore da quella tabella:
VLOOKUP(id,data,col,TRUE)
Non c'è pericolo di un valore di ricerca mancante, poiché la prima parte della formula è già stata controllata per assicurarsi che sia presente.
Se il valore di ricerca non viene trovato, viene eseguita la parte "valore se FALSO" della funzione SE e puoi restituire qualsiasi valore desideri. In questo esempio, usiamo NA () restituiamo un errore # N / D, ma potresti anche restituire un messaggio come "Mancante" o "Non trovato".
Ricorda: devi ordinare i dati in base al valore di ricerca affinché questo trucco funzioni.
