Tutti i segreti della funzione CERCA.VERT di Excel per trovare dati in una tabella ed estrarli in un'altra

2024 Autore: Malcolm Clapton | [email protected]. Ultima modifica: 2023-12-17 04:01

Dopo aver letto l'articolo, imparerai non solo come trovare i dati in un foglio di calcolo Excel ed estrarli in un altro, ma anche tecniche che possono essere utilizzate insieme alla funzione CERCA. VERT.

Quando si lavora in Excel, molto spesso è necessario trovare i dati in una tabella ed estrarli in un'altra. Se ancora non sai come farlo, dopo aver letto l'articolo, non solo imparerai come farlo, ma scoprirai anche in quali condizioni puoi ottenere le massime prestazioni dal sistema. Vengono considerate la maggior parte delle tecniche molto efficaci che dovrebbero essere utilizzate insieme alla funzione CERCA. VERT.

Anche se utilizzi la funzione CERCA. VERT da anni, con un alto grado di probabilità questo articolo ti sarà utile e non ti lascerà indifferente. Ad esempio, essendo uno specialista IT e poi un responsabile IT, utilizzo VLOOKUP da 15 anni, ma sono riuscito a gestire tutte le sfumature solo ora, quando ho iniziato a insegnare alle persone Excel su base professionale.

CERCA. VERT è l'abbreviazione di vverticale NSispezione. Allo stesso modo, CERCA. VERT - CERCA verticale. Il nome stesso della funzione ci suggerisce che cerca nelle righe della tabella (verticalmente - iterando sulle righe e fissando la colonna), e non nelle colonne (orizzontalmente - iterando sulle colonne e fissando la riga). Va notato che CERCA. VERT ha una sorella - un brutto anatroccolo che non diventerà mai un cigno - questa è la funzione CERCA. VERT. CERCA. ORIZZ, al contrario di CERCA. VERT, esegue ricerche orizzontali, ma il concetto di Excel (e in effetti il concetto di organizzazione dei dati) implica che le tue tabelle abbiano meno colonne e molte più righe. Ecco perché dobbiamo cercare per righe molte volte più spesso che per colonne. Se usi la funzione HLO troppo spesso in Excel, è probabile che tu non abbia capito qualcosa in questa vita.

Sintassi

La funzione CERCA. VERT ha quattro parametri:

= CERCA. VERT (;; [;]), qui:

- il valore desiderato (raramente) o un riferimento a una cella contenente il valore desiderato (nella stragrande maggioranza dei casi);

- riferimento ad un intervallo di celle (array bidimensionale), nella PRIMA colonna (!) di cui verrà cercato il valore del parametro;

- numero di colonna nell'intervallo da cui verrà restituito il valore;

- questo è un parametro molto importante che risponde alla domanda se la prima colonna dell'intervallo è ordinata in ordine crescente. Se l'array è ordinato, specifichiamo il valore TRUE o 1, altrimenti - FALSE o 0. Se questo parametro viene omesso, il valore predefinito è 1.

Scommetto che molti di coloro che conoscono la funzione CERCA. VERT come traballante, dopo aver letto la descrizione del quarto parametro, potrebbero sentirsi a disagio, poiché sono abituati a vederlo in una forma leggermente diversa: di solito parlano di una corrispondenza esatta quando ricerca (FALSE o 0) o una scansione dell'intervallo (TRUE o 1).

Ora devi sforzarti e leggere il paragrafo successivo più volte finché non senti il significato di ciò che è stato detto fino alla fine. Ogni parola è importante lì. Gli esempi ti aiuteranno a capirlo.

Come funziona esattamente la formula CERCA. VERT?

• Formula di tipo I. Se l'ultimo parametro viene omesso o specificato uguale a 1, CERCA. VERT presuppone che la prima colonna sia ordinata in ordine crescente, quindi la ricerca si ferma alla riga che precede immediatamente la riga contenente il valore maggiore di quello desiderato … Se non viene trovata tale stringa, viene restituita l'ultima riga dell'intervallo.

  

• Formula II. Se l'ultimo parametro è specificato come 0, VLOOKUP esegue la scansione della prima colonna dell'array in sequenza e interrompe immediatamente la ricerca quando viene trovata la prima corrispondenza esatta con il parametro, altrimenti il codice di errore # N / A (# N / A) viene restituito.

  

Flussi di lavoro delle formule

VPR tipo I

VPR tipo II

Corollari per formule della forma I

1. Le formule possono essere utilizzate per distribuire i valori tra gli intervalli.
2. Se la prima colonna contiene valori duplicati ed è ordinata correttamente, verrà restituita l'ultima delle righe con valori duplicati.
3. Se cerchi un valore che è ovviamente maggiore di quello che può contenere la prima colonna, puoi facilmente trovare l'ultima riga della tabella, che può essere piuttosto preziosa.
4. Questa vista restituirà l'errore #N/A solo se non trova un valore minore o uguale a quello desiderato.
5. È piuttosto difficile capire che la formula restituisce i valori errati se l'array non è ordinato.

Corollari per formule di tipo II

Se il valore desiderato si verifica più volte nella prima colonna dell'array, la formula selezionerà la prima riga per il successivo recupero dei dati.

Prestazioni CERCA. VERT

Sei arrivato al culmine dell'articolo. Sembrerebbe, beh, qual è la differenza se specifico zero o uno come ultimo parametro? Fondamentalmente, tutti indicano, ovviamente, zero, poiché questo è abbastanza pratico: non devi preoccuparti di ordinare la prima colonna dell'array, puoi vedere immediatamente se il valore è stato trovato o meno. Ma se hai diverse migliaia di formule CERCA. VERT sul tuo foglio, noterai che CERCA. VERT II è lento. Allo stesso tempo, di solito tutti iniziano a pensare:

• Ho bisogno di un computer più potente;
• Ho bisogno di una formula più veloce, ad esempio, molte persone conoscono INDEX + MATCH, che presumibilmente è più veloce di un misero 5-10%.

E poche persone pensano che non appena inizi a utilizzare VLOOKUP di tipo I e ti assicuri che la prima colonna sia ordinata con qualsiasi mezzo, la velocità di VLOOKUP aumenterà di 57 volte. Sto scrivendo a parole - CINQUANTA SETTE VOLTE! Non il 57%, ma il 5.700%. Ho verificato questo fatto in modo abbastanza affidabile.

Il segreto di un lavoro così veloce sta nel fatto che un algoritmo di ricerca estremamente efficiente può essere applicato su un array ordinato, che si chiama ricerca binaria (metodo dimezzato, metodo dicotomico). Quindi CERCA. VERT di tipo I lo applica e CERCA. VERT di tipo II esegue la ricerca senza alcuna ottimizzazione. Lo stesso vale per la funzione CONFRONTA, che include un parametro simile, e la funzione CERCA, che funziona solo su matrici ordinate ed è inclusa in Excel per compatibilità con Lotus 1-2-3.

Svantaggi della formula

Gli svantaggi di CERCA. VERT sono evidenti: in primo luogo, cerca solo nella prima colonna dell'array specificato e, in secondo luogo, solo a destra di questa colonna. E come capisci, può succedere che la colonna contenente le informazioni necessarie sia a sinistra della colonna in cui cercheremo. La già citata combinazione di formule INDEX + MATCH è priva di questo inconveniente, il che la rende la soluzione più flessibile per estrarre dati da tabelle rispetto a VLOOKUP (VLOOKUP).

Alcuni aspetti dell'applicazione della formula nella vita reale

Ricerca per intervallo

Un esempio classico di una ricerca per intervallo è il compito di determinare uno sconto in base alla dimensione dell'ordine.

Cerca stringhe di testo

Ovviamente, VLOOKUP cerca non solo i numeri, ma anche il testo. Va tenuto presente che la formula non distingue tra il caso dei caratteri. Se utilizzi i caratteri jolly, puoi organizzare una ricerca fuzzy. Ci sono due caratteri jolly: "?" - sostituisce qualsiasi carattere in una stringa di testo, "*" - sostituisce qualsiasi numero di qualsiasi carattere.

Spazi di combattimento

Viene spesso sollevata la domanda su come risolvere il problema degli spazi extra durante la ricerca. Se la tabella di ricerca può ancora essere cancellata, il primo parametro della formula CERCA. VERT non dipende sempre da te. Pertanto, se è presente il rischio di intasare le celle con spazi aggiuntivi, è possibile utilizzare la funzione TRIM per cancellarlo.

Formato dati diverso

Se il primo parametro della funzione CERCA. VERT fa riferimento a una cella che contiene un numero, ma che è memorizzata nella cella come testo e la prima colonna dell'array contiene numeri nel formato corretto, la ricerca avrà esito negativo. È possibile anche la situazione opposta. Il problema può essere facilmente risolto traducendo il parametro 1 nel formato richiesto:

= CERCA. VERT (−− D7; Prodotti! $ A $ 2: $ C $ 5; 3; 0) - se D7 contiene testo e la tabella contiene numeri;

= CERCA. VERT (D7 & ""); Prodotti $ A $ 2: $ C $ 5; 3; 0) - e viceversa.

A proposito, puoi tradurre il testo in un numero in diversi modi contemporaneamente, scegli:

• Doppia negazione -D7.
• Moltiplicazione per un D7 * 1.
• Addizione zero D7 + 0.
• Elevazione alla prima potenza D7^1.

La conversione di un numero in testo viene eseguita tramite la concatenazione con una stringa vuota, che obbliga Excel a convertire il tipo di dati.

Come sopprimere # N/A

Questo è molto comodo da fare con la funzione SE. ERRORE.

Ad esempio: = SEERRORE (CERCA. VERT (D7; Prodotti! $ A $ 2: $ C $ 5; 3; 0); "").

Se CERCA. VERT restituisce il codice di errore # N / A, IFERROR lo intercetterà e sostituirà il parametro 2 (in questo caso una stringa vuota) e se non si verifica alcun errore, questa funzione fingerà che non esista affatto, ma c'è solo VLOOKUP che ha restituito un risultato normale.

Vettore

Spesso dimenticano di rendere assoluto il riferimento dell'array e quando si allunga l'array "fluttua". Ricorda di usare $ A $ 2: $ C $ 5 invece di A2: C5.

È una buona idea posizionare la matrice di riferimento su un foglio separato della cartella di lavoro. Non si metterà sotto i piedi e sarà più sicuro.

Un'idea ancora migliore sarebbe dichiarare questo array come un intervallo denominato.

Molti utenti, quando specificano un array, usano una costruzione come A: C, specificando intere colonne. Questo approccio ha il diritto di esistere, dal momento che sei risparmiato dal dover tenere traccia del fatto che il tuo array include tutte le stringhe richieste. Se aggiungi righe al foglio con l'array originale, non è necessario regolare l'intervallo specificato come A: C. Naturalmente, questo costrutto sintattico costringe Excel a fare un po' più di lavoro rispetto alla specificazione esatta dell'intervallo, ma questo sovraccarico può essere trascurato. Parliamo di centesimi di secondo.

Bene, sull'orlo del genio: organizzare l'array nella forma.

Utilizzo della funzione COLUMN per specificare la colonna da estrarre

Se la tabella in cui si stanno recuperando i dati utilizzando CERCA. VERT ha la stessa struttura della tabella di ricerca, ma contiene semplicemente meno righe, è possibile utilizzare la funzione COLONNA () in CERCA. VERT per calcolare automaticamente i numeri delle colonne da recuperare. In questo caso, tutte le formule CERCA. VERT saranno le stesse (regolate per il primo parametro, che cambia automaticamente)! Si noti che il primo parametro ha una coordinata di colonna assoluta.

Creazione di una chiave composta con & "|" &

Se diventa necessario cercare per più colonne contemporaneamente, è necessario creare una chiave composita per la ricerca. Se il valore restituito non fosse testuale (come nel caso del campo "Codice"), ma numerico, la formula SUMIFS più conveniente sarebbe adatta a questo e la chiave di colonna composita non sarebbe affatto richiesta.

Questo è il mio primo articolo per un Lifehacker. Se ti è piaciuto, ti invito a visitare e anche a leggere volentieri nei commenti i tuoi segreti sull'utilizzo della funzione CERCA. VERT e simili. Grazie.:)