Contenuti
- Funzione CERCA.VERT in Excel: descrizione generale e sintassi
- CERCA.VERT Esempi
- Corrispondenza esatta o approssimativa nella funzione CERCA.VERT
- CERCA.VERT in Excel: devi ricordarlo!
Oggi iniziamo una serie di articoli che descrivono una delle funzionalità più utili di Excel − VPR (Cerca. Questa funzione, allo stesso tempo, è una delle più complesse e meno comprese.
In questo tutorial su VPR Cercherò di esporre le basi nel modo più semplice possibile in modo da rendere il processo di apprendimento il più chiaro possibile per gli utenti inesperti. Inoltre, studieremo diversi esempi con formule di Excel che dimostreranno i casi d'uso più comuni per la funzione VPR.
Funzione CERCA.VERT in Excel: descrizione generale e sintassi
Quindi, cos'è VPR? Bene, prima di tutto, è una funzione di Excel. Cosa fa? Cerca il valore specificato e restituisce il valore corrispondente dall'altra colonna. Parlando tecnicamente, VPR cerca il valore nella prima colonna dell'intervallo specificato e restituisce il risultato da un'altra colonna nella stessa riga.
Nell'applicazione più comune, la funzione VPR cerca nel database un determinato identificatore univoco ed estrae alcune informazioni ad esso correlate dal database.
Prima lettera nel nome della funzione VPR (CERCA.VERT) significa Вverticale (Vverticale). Da esso puoi distinguere VPR da GPR (HLOOKUP), che ricerca un valore nella riga superiore di un intervallo − Гorizzontale (Horizzontalmente).
Funzione VPR disponibile in Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP ed Excel 2000.
Sintassi della funzione CERCA.VERT
Funzione VPR (VLOOKUP) ha la seguente sintassi:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Come puoi vedere, una funzione VPR in Microsoft Excel ha 4 opzioni (o argomenti). I primi tre sono obbligatori, l'ultimo facoltativo.
- valore di ricerca (lookup_value) – Il valore da cercare. Può essere un valore (numero, data, testo) o un riferimento di cella (contenente il valore di ricerca) o un valore restituito da qualche altra funzione di Excel. Ad esempio, questa formula cercherà il valore 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Se il valore di ricerca è inferiore al valore più piccolo nella prima colonna dell'intervallo da cercare, la funzione VPR segnalerà un errore #at (#N / A).
- matrice di tabella (tabella) – due o più colonne di dati. Ricorda, la funzione VPR cerca sempre il valore nella prima colonna dell'intervallo fornito nell'argomento matrice di tabella (tavolo). L'intervallo visualizzabile può contenere vari dati, come testo, date, numeri, valori booleani. La funzione non fa distinzione tra maiuscole e minuscole, il che significa che i caratteri maiuscoli e minuscoli sono considerati uguali. Quindi la nostra formula cercherà il valore 40 nelle cellule da A2 a A15, perché A è la prima colonna dell'intervallo A2:B15 fornito nell'argomento matrice di tabella (tavolo):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (column_number) è il numero della colonna nell'intervallo specificato da cui verrà restituito il valore nella riga trovata. La colonna più a sinistra nell'intervallo specificato è 1, la seconda colonna è 2, la terza colonna è 3 e così via. Ora puoi leggere l'intera formula:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Formula alla ricerca del valore 40 nell'intervallo LA2: LA15 e restituisce il valore corrispondente dalla colonna B (perché B è la seconda colonna nell'intervallo A2:B15).
Se il valore dell'argomento col_index_num (colonna_numero) minore di 1poi VPR segnalerà un errore #VALORE! (#VALORE!). E se è superiore al numero di colonne nell'intervallo matrice di tabella (tabella), la funzione restituirà un errore #RIF! (#COLLEGAMENTO!).
- ricerca_intervallo (range_lookup) – determina cosa cercare:
- corrispondenza esatta, l'argomento deve essere uguale FALSO (FALSO);
- corrispondenza approssimativa, argomento uguale VERO CODICE (VERO) o non specificato affatto.
Questo parametro è facoltativo, ma molto importante. Più avanti in questo tutorial su VPR Ti mostrerò alcuni esempi che spiegano come scrivere formule per trovare corrispondenze esatte e approssimative.
CERCA.VERT Esempi
Spero che la funzione VPR diventa un po' più chiaro per te. Ora diamo un'occhiata ad alcuni casi d'uso VPR in formule con dati reali.
Come utilizzare CERCA.VERT per cercare in un altro foglio Excel
In pratica, formule con una funzione VPR sono usati raramente per cercare dati sullo stesso foglio di lavoro. Il più delle volte, cercherai e recupererai i valori corrispondenti da un altro foglio.
Per poter utilizzare VPR, cerca in un altro foglio di Microsoft Excel, Devi nell'argomento matrice di tabella (tabella) specificare il nome del foglio con un punto esclamativo seguito da un intervallo di celle. Ad esempio, la formula seguente mostra che l'intervallo LA2: SI15 è su un foglio chiamato Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Naturalmente, il nome del foglio non deve essere inserito manualmente. Inizia a digitare la formula e quando si tratta dell'argomento matrice di tabella (tabella), passare al foglio desiderato e selezionare l'intervallo di celle desiderato con il mouse.
La formula mostrata nello screenshot qui sotto cerca il testo "Prodotto 1" nella colonna A (è la prima colonna dell'intervallo A1:B2) su un foglio di lavoro Prezzi.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Ricorda che quando cerchi un valore di testo, devi racchiuderlo tra virgolette (""), come di solito avviene nelle formule di Excel.
Per argomentazione matrice di tabella (tabella) è preferibile utilizzare sempre riferimenti assoluti (con il segno $). In questo caso, l'intervallo di ricerca rimarrà invariato durante la copia della formula in altre celle.
Cerca in un'altra cartella di lavoro con VLOOKUP
Funzionare VPR lavorato tra due cartelle di lavoro di Excel, è necessario specificare il nome della cartella di lavoro tra parentesi quadre prima del nome del foglio.
Ad esempio, di seguito è riportata una formula che cerca il valore 40 sul foglio Sheet2 nel libro Numeri.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Ecco il modo più semplice per creare una formula in Excel con VPRche si collega a un'altra cartella di lavoro:
- Apri entrambi i libri. Questo non è necessario, ma è più facile creare una formula in questo modo. Non vuoi inserire manualmente il nome della cartella di lavoro, vero? Inoltre, ti proteggerà da errori di battitura accidentali.
- Inizia a digitare una funzione VPRe quando si tratta dell'argomento matrice di tabella (tabella), passare a un'altra cartella di lavoro e selezionare l'intervallo di ricerca richiesto al suo interno.
Lo screenshot seguente mostra la formula con la ricerca impostata su un intervallo nella cartella di lavoro Listino prezzi.xlsx sul foglio Prezzi.
Funzione VPR funzionerà anche quando chiudi la cartella di lavoro cercata e il percorso completo del file della cartella di lavoro viene visualizzato nella barra della formula, come mostrato di seguito:
Se il nome della cartella di lavoro o del foglio contiene spazi, deve essere racchiuso tra apostrofi:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
Come utilizzare un intervallo o una tabella con nome nelle formule con CERCA.VERT
Se prevedi di utilizzare lo stesso intervallo di ricerca in più funzioni VPR, puoi creare un intervallo denominato e inserirne il nome nella formula come argomento matrice di tabella (tavolo).
Per creare un intervallo denominato, seleziona semplicemente le celle e inserisci un nome appropriato nel campo Nome, a sinistra della barra della formula.
Ora puoi scrivere la seguente formula per trovare il prezzo di un prodotto Prodotto 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
La maggior parte dei nomi di intervallo funziona per l'intera cartella di lavoro di Excel, quindi non è necessario specificare il nome del foglio per l'argomento matrice di tabella (tabella), anche se la formula e l'intervallo di ricerca si trovano su fogli di lavoro diversi. Se si trovano in cartelle di lavoro diverse, prima del nome dell'intervallo è necessario specificare il nome della cartella di lavoro, ad esempio, in questo modo:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Quindi la formula sembra molto più chiara, d'accordo? Inoltre, l'utilizzo di intervalli denominati è una buona alternativa ai riferimenti assoluti perché l'intervallo denominato non cambia quando si copia la formula in altre celle. Ciò significa che puoi essere certo che l'intervallo di ricerca nella formula rimarrà sempre corretto.
Se si converte un intervallo di celle in un foglio di calcolo Excel completo utilizzando il comando Table scheda (Tabella). Inserimento (Inserisci), quindi quando selezioni un intervallo con il mouse, Microsoft Excel aggiungerà automaticamente i nomi delle colonne (o il nome della tabella se selezioni l'intera tabella) alla formula.
La formula finita sarà simile a questa:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
O forse anche così:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
Quando si utilizzano intervalli denominati, i collegamenti punteranno alle stesse celle, indipendentemente da dove si copia la funzione VPR all'interno della cartella di lavoro.
Utilizzo di caratteri jolly nelle formule VLOOKUP
Come per molte altre funzioni, VPR È possibile utilizzare i seguenti caratteri jolly:
- Punto interrogativo (?) – sostituisce ogni singolo carattere.
- Asterisco (*) – sostituisce qualsiasi sequenza di caratteri.
Utilizzo di caratteri jolly nelle funzioni VPR può essere utile in molti casi, ad esempio:
- Quando non ricordi esattamente il testo che devi trovare.
- Quando vuoi trovare una parola che fa parte del contenuto di una cella. Sapere che VPR ricerca in base al contenuto della cella nel suo insieme, come se l'opzione fosse abilitata Abbina l'intero contenuto della cella (Intera cella) nella ricerca standard di Excel.
- Quando una cella contiene spazi extra all'inizio o alla fine del contenuto. In una situazione del genere, puoi scervellarti a lungo, cercando di capire perché la formula non funziona.
Esempio 1: ricerca di testo che inizia o finisce con determinati caratteri
Supponiamo che tu voglia cercare un cliente specifico nel database mostrato di seguito. Non ricordi il suo cognome, ma sai che inizia con “ack”. Ecco una formula che farà bene il lavoro:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Ora che sei sicuro di aver trovato il nome corretto, puoi utilizzare la stessa formula per trovare l'importo pagato da questo cliente. Per fare ciò, basta cambiare il terzo argomento della funzione VPR al numero di colonna desiderato. Nel nostro caso, questa è la colonna C (3a nell'intervallo):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Ecco altri esempi con caratteri jolly:
~ Trova un nome che termina con "uomo":
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ Trova un nome che inizi con "annuncio" e termini con "figlio":
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ Troviamo il nome nell'elenco, composto da 5 caratteri:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Funzionare VPR con i caratteri jolly ha funzionato correttamente, come quarto argomento da usare sempre FALSO (FALSO). Se l'intervallo di ricerca contiene più di un valore che corrisponde ai termini di ricerca con caratteri jolly, verrà restituito il primo valore trovato.
Esempio 2: combina caratteri jolly e riferimenti di cella nelle formule CERCA.VERT
Ora diamo un'occhiata a un esempio leggermente più complesso di come cercare utilizzando la funzione VPR per valore in una cella. Immagina che la colonna A sia un elenco di chiavi di licenza e la colonna B sia un elenco di nomi che possiedono una licenza. Inoltre, hai una parte (diversi caratteri) di una sorta di chiave di licenza nella cella C1 e vuoi trovare il nome del proprietario.
Questo può essere fatto usando la seguente formula:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Questa formula cerca il valore dalla cella C1 nell'intervallo specificato e restituisce il valore corrispondente dalla colonna B. Si noti che nel primo argomento, utilizziamo un carattere e commerciale (&) prima e dopo il riferimento di cella per collegare la stringa di testo.
Come puoi vedere nella figura seguente, la funzione VPR restituisce "Jeremy Hill" perché la sua chiave di licenza contiene la sequenza di caratteri dalla cella C1.
Si noti che l'argomento matrice di tabella (tabella) nello screenshot sopra contiene il nome della tabella (Tabella7) invece di specificare un intervallo di celle. Questo è ciò che abbiamo fatto nell'esempio precedente.
Corrispondenza esatta o approssimativa nella funzione CERCA.VERT
E infine, diamo un'occhiata più da vicino all'ultimo argomento specificato per la funzione VPR - ricerca_intervallo (vista_intervallo). Come accennato all'inizio della lezione, questo argomento è molto importante. Puoi ottenere risultati completamente diversi nella stessa formula con il suo valore VERO CODICE (VERO) o FALSO (FALSO).
Per prima cosa, scopriamo cosa significa Microsoft Excel per corrispondenze esatte e approssimative.
- Se l'argomento ricerca_intervallo (range_lookup) è uguale a FALSO (FALSE), la formula cerca una corrispondenza esatta, ovvero esattamente lo stesso valore fornito nell'argomento valore di ricerca (valore di ricerca). Se nella prima colonna dell'intervallo tarray_abile (tabella) incontra due o più valori che corrispondono all'argomento valore di ricerca (valore_ricerca), quindi verrà selezionato il primo. Se non vengono trovate corrispondenze, la funzione segnalerà un errore #at (#N / A). Ad esempio, la seguente formula riporterà un errore #at (#N/A) se non è presente alcun valore nell'intervallo A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Se l'argomento ricerca_intervallo (range_lookup) è uguale a VERO CODICE (TRUE), la formula cerca una corrispondenza approssimativa. Più precisamente, prima la funzione VPR cerca una corrispondenza esatta e, se non la trova, ne seleziona una approssimativa. Una corrispondenza approssimativa è il valore più grande che non supera il valore specificato nell'argomento. valore di ricerca (valore di ricerca).
Se l'argomento ricerca_intervallo (range_lookup) è uguale a VERO CODICE (TRUE) o non specificato, i valori nella prima colonna dell'intervallo devono essere ordinati in ordine crescente, ovvero dal più piccolo al più grande. Altrimenti, la funzione VPR potrebbe restituire un risultato errato.
Per capire meglio l'importanza della scelta VERO CODICE (VERITÀ) o FALSO (FALSO), diamo un'occhiata ad altre formule con la funzione VPR e guarda i risultati.
Esempio 1: trovare una corrispondenza esatta con VLOOKUP
Come ricorderete, per cercare una corrispondenza esatta, il quarto argomento della funzione VPR dovrebbe importare FALSO (FALSO).
Torniamo alla tabella dal primo esempio e scopriamo quale animale può muoversi ad una velocità 50 miglia all'ora. Credo che questa formula non ti creerà alcuna difficoltà:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Si noti che il nostro intervallo di ricerca (colonna A) contiene due valori 50 – nelle cellule A5 и A6. La formula restituisce il valore dalla cella B5. Come mai? Perché quando si cerca una corrispondenza esatta, la funzione VPR utilizza il primo valore trovato che corrisponde a quello cercato.
Esempio 2: utilizzo di VLOOKUP per trovare una corrispondenza approssimativa
Quando usi la funzione VPR per cercare una corrispondenza approssimativa, cioè quando l'argomento ricerca_intervallo (range_lookup) è uguale a VERO CODICE (TRUE) o omesso, la prima cosa che devi fare è ordinare l'intervallo in base alla prima colonna in ordine crescente.
Questo è molto importante perché la funzione VPR restituisce il valore più grande successivo a quello dato, quindi la ricerca si interrompe. Se trascuri l'ordinamento corretto, ti ritroverai con risultati molto strani o un messaggio di errore. #at (#N / A).
Ora puoi usare una delle seguenti formule:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Come puoi vedere, voglio scoprire quale degli animali ha la velocità più vicina 69 miglia all'ora. Ed ecco il risultato che la funzione mi ha restituito VPR:
Come puoi vedere, la formula ha restituito un risultato Antilope (Antilope), la cui velocità 61 miglia orarie, sebbene l'elenco includa anche Cheetah (Cheetah) che corre veloce 70 miglia orarie, e 70 è più vicino a 69 che a 61, vero? Perché sta succedendo? Perché la funzione VPR durante la ricerca di una corrispondenza approssimativa, restituisce il valore più grande che non è maggiore di quello cercato.
Spero che questi esempi facciano luce sul lavoro con la funzione VPR in Excel e non la guardi più come un'estranea. Ora non fa male ripetere brevemente i punti chiave del materiale che abbiamo studiato per fissarlo meglio nella memoria.
CERCA.VERT in Excel: devi ricordarlo!
- Funzione VPR Excel non può guardare a sinistra. Cerca sempre il valore nella colonna più a sinistra dell'intervallo fornito dall'argomento matrice di tabella (tavolo).
- In funzione VPR tutti i valori non fanno distinzione tra maiuscole e minuscole, ovvero lettere piccole e grandi sono equivalenti.
- Se il valore che stai cercando è inferiore al valore minimo nella prima colonna dell'intervallo che stai cercando, la funzione VPR segnalerà un errore #at (#N / A).
- Se 3° argomento col_index_num (colonna_numero) minore di 1function VPR segnalerà un errore #VALORE! (#VALORE!). Se è maggiore del numero di colonne nell'intervallo matrice di tabella (tabella), la funzione segnalerà un errore #RIF! (#COLLEGAMENTO!).
- Usa riferimenti di cella assoluti nell'argomento matrice di tabella (tabella) in modo da preservare l'intervallo di ricerca corretto durante la copia della formula. Prova a utilizzare intervalli o tabelle con nome in Excel come alternativa.
- Quando esegui una ricerca di corrispondenza approssimativa, ricorda che la prima colonna nell'intervallo che stai cercando deve essere ordinata in ordine crescente.
- Infine, ricorda l'importanza del quarto argomento. Usa i valori VERO CODICE (VERITÀ) o FALSO (FALSO) deliberatamente e ti libererai di molti mal di testa.
Nei seguenti articoli del nostro tutorial sulle funzioni VPR in Excel, impareremo esempi più avanzati, come eseguire vari calcoli utilizzando VPR, estraendo valori da più colonne e altro ancora. Grazie per aver letto questo tutorial e spero di rivederti la prossima settimana!