Usciamo dal lavoro di routine utilizzando la funzione CERCA.VERT in Excel. Funzione CERCA.VERT. Utilizzando la funzione CERCA.VERT. Excel - CERCA.VERT Utilizzo della funzione CERCA.VERT negli esempi di Excel

Nella versione inglese, questa funzione si chiama VLOOKUP - sta per visualizzazione verticale. C'è anche una funzione GLOOKUP focalizzata sulla visualizzazione orizzontale. Fondamentalmente, la funzione CERCA.VERT viene utilizzata per estrarre i dati da una tabella all'altra; può anche essere utilizzata per confrontare colonne in due tabelle diverse;

Per poter comprendere più a fondo tutte le caratteristiche del lavoro con una funzione, diamo un'occhiata a semplici esempi.

Abbiamo due tavoli. La prima tabella memorizza i dati sugli ordini per un giorno. Registra il nome del prodotto e la quantità di prodotti venduti per lotto. La seconda tabella memorizza le informazioni sui prezzi dei prodotti. Il compito è calcolare le entrate totali giornaliere per ciascun prodotto.

Per risolvere il problema, dobbiamo inserire i prezzi delle merci dalla tabella “Listino Prezzi” nella colonna “Prezzo al kg” della tabella “Ordini”. Quelli. sostituisci i prezzi che corrispondono a ciascun prodotto dalla tabella inferiore a quella superiore.

In Excel esistono diverse opzioni per risolvere questo esempio, una delle quali è utilizzare la funzione CERCA.VERT. Ci troviamo sulla prima cella in cui dobbiamo sostituire i dati e iniziamo a scrivere la funzione, iniziando come al solito con il segno uguale.

Dovrebbe apparire un messaggio in base al quale gli forniremo gli argomenti. Se decifriamo il lavoro di CERCA.VERT dal linguaggio tecnico al linguaggio normale, la funzione CERCA.VERT cercherà il prodotto che abbiamo selezionato dalla tabella "Ordini" nella colonna più a sinistra della tabella "Listino prezzi" e, se trova questo prodotto , visualizza il valore del suo prezzo, che si trova sulla stessa riga del prodotto trovato. In breve appare come lo screenshot qui sotto.

La funzione CERCA.VERT ha 4 argomenti. Il primo argomento che sostituiremo nella formula è lo stesso valore desiderato, il cui prezzo dovrà essere trovato nella seconda tabella “Listino Prezzi”. Dopo aver scritto il nome della funzione e aggiunto una parentesi di apertura, clicca sulla cella che stai cercando e aggiungila come argomento. Nel mio caso è la cella "E4" con il valore "Mele". Successivamente impostiamo il separatore: punto e virgola.

Il secondo argomento è la tabella in cui cerchiamo il valore e da cui ricaviamo il prezzo. Qui devi solo selezionare la tabella “Listino prezzi” ed è meglio non catturare celle extra (intestazione, celle vuote e solo informazioni non necessarie). Non dimenticare di mettere un punto e virgola alla fine.

Poiché nel nostro esempio la tabella in cui viene effettuata la ricerca si trova sullo stesso foglio della prima, trascinando la formula sulle celle sottostanti, la tabella specificata scorrerà automaticamente verso il basso. Per evitare che ciò accada, è necessario selezionare l'intervallo della tabella all'interno della formula con il tasto sinistro del mouse e premere il tasto F4 sulla tastiera. Verrà aggiunto il segno del dollaro agli indirizzi delle celle e il riferimento alla tabella passerà da relativo ad assoluto. Di seguito esamineremo i casi in cui non è necessario creare riferimenti assoluti alle celle.

Il terzo argomento è il numero della colonna nella tabella “Listino prezzi” da cui vogliamo estrarre il valore di cui abbiamo bisogno. Nel nostro esempio tutto è semplice. Nella prima colonna cerchiamo il valore, e dalla seconda colonna tiriamo su il prezzo. Inseriamo il numero 2 e poi un punto e virgola.

L'ultimo parametro si chiama "Vista Intervallo" - è un argomento booleano e può assumere solo 2 valori: 0 o 1 (on/off). Con questo valore determiniamo esattamente il numero 0, o approssimativamente il numero 1, il nome che cerchiamo nel listino prezzi. Nel caso dei nomi di testo è meglio utilizzare la ricerca esatta (numero 0), perché la ricerca approssimata funziona più o meno accuratamente nel caso di celle con numeri. Immettere la visualizzazione dell'intervallo come 0 e chiudere la parentesi. Successivamente, premi il tasto Invio affinché la formula funzioni. Di conseguenza, otterremo una cella con un prezzo estratto dalla tabella del listino prezzi.

Nella colonna "Costo totale" scriviamo una semplice formula per moltiplicare il peso del lotto per il prezzo al kg.

Seleziona entrambe le celle e, tenendo premuto il pulsante sinistro del mouse nell'angolo in basso a destra della selezione, trascina le formule su tutte le celle rimanenti.

Di conseguenza, otteniamo il prezzo e il costo finale di ciascun lotto. Quando si utilizzano ulteriormente i dati ottenuti, non dimenticare che sono il risultato della formula e non valori già pronti. Per poter lavorare con loro in futuro, è necessario copiare le celle con i dati risultanti e incollarle nuovamente al loro posto tramite incolla speciale come valori normali.

Abbiamo discusso con voi uno dei casi in cui è necessario estrarre i dati da una tabella che si trova sullo stesso foglio. Naturalmente è possibile utilizzare tabelle che si trovano su fogli diversi e anche quando sono aperte in documenti diversi. In questo caso, il secondo argomento della funzione avrà un aspetto leggermente diverso. Nel caso di un altro foglio o documento, non è affatto necessario specificare collegamenti assoluti, ed è meglio aggiungere il terzo e il quarto argomento nel campo di input della funzione, senza tornare al foglio da cui hai iniziato a digitarlo.

Un altro uso utile di CERCA.VERT è utilizzarlo per confrontare due tabelle per trovare somiglianze o differenze. Ad esempio, se rimuovi più righe da una delle tabelle, non ci sarà più una corrispondenza completa nei dati e in alcune celle apparirà la scritta #N/A. Questa iscrizione indica che i valori della tabella con gli ordini non sono stati trovati nella tabella del listino prezzi, il che porta di conseguenza ad un errore.

Consideriamo ora il caso in cui è necessario utilizzare la visualizzazione a intervalli, non esatta, con il numero 0, ma approssimativa, con il numero 1. Questo metodo viene utilizzato raramente, ma tuttavia ci sono attività in cui potrebbe essere necessario.

Torniamo al nostro tavolo con gli ordini e proviamo a determinare la dimensione del lotto in base al suo peso. Avremo una tabella con gli ordini e una tabella con i parametri batch.

Inoltre, abbiamo il criterio “da e a”, che indica la dimensione del lotto. Nell'esempio inizieremo dal suo peso. Ad esempio, se Excel mostra il peso di un lotto di 15 kg, capirà che si tratta di un lotto piccolo.

Scopriamo come funziona. Iniziamo a scrivere la funzione CERCA.VERT, ma alla fine della formula non inseriamo 0, ma 1. Nell'argomento in cui dobbiamo specificare una tabella con celle, non specificheremo una colonna con il parametro "Da e a" , poiché Excel non capirà tale voce. Introduciamo invece un campo criterio che determinerà l'inizio e la fine dell'intervallo di peso. Alla fine, premi il tasto Invio e trascina su tutte le celle.

Ci sono alcune caratteristiche del lavoro che devono essere spiegate in modo che sia chiaro cosa è successo qui. Vediamo che ad un certo peso la funzione ha estratto un certo lotto dalla seconda tabella. Per comprendere più precisamente la logica, consideriamo il peso, ad esempio 15 kg. Nella tabella con le dimensioni dei lotti non esiste tale criterio. Ma quando impostiamo la ricerca dell'intervallo su 1, la funzione estrarrà il valore più piccolo più vicino. Se il peso del lotto è 15 kg, CERCA.VERT estrarrà un valore con il criterio 10 dalla tabella. Se il peso del lotto è 47 kg, la funzione estrarrà un valore con il criterio 30, che sarà il più vicino e il più piccolo.

Quando si lavora con una funzione in questa modalità, c'è un punto molto importante che è necessario tenere in considerazione. I criteri devono essere ordinati in ordine crescente, dal più piccolo al più grande. Se i criteri vengono ordinati in ordine inverso, la funzione non funzionerà.

Funzione CERCA.VERT di Excel ti permette di trovare i dati richiesti nella tabella e nella colonna specificate e restituirli come risultato. Questa funzione può essere molto utile quando confronto dei dati da tabelle diverse o quando si combinano informazioni in un unico array per ulteriori analisi.

CERCA.VERT ( CERCA.VERT nella versione inglese) decifrato, come la visualizzazione verticale. La funzione è una di il più popolare in Excel. Permette, ad esempio, di trovare e confrontare facilmente i dati telefonici di una persona o di un'organizzazione da una tabella di ricerca in base al suo nome. Puoi confrontare il prezzo dei prodotti in base al loro nome. Queste e molte altre possibilità ti verranno fornite dalla funzione CERCA.VERT. È abbastanza semplice da usare.

Utilizzando la funzione

Diamo un'occhiata alla struttura CERCA.VERT e agli argomenti che utilizza. Come qualsiasi altra funzione in Excel, inizia con segno di uguale(=). Ulteriore Nome funzioni e argomenti racchiusi tra parentesi.

CERCA.VERT contiene 4 argomenti.

La funzione cerca il valore desiderato in estrema sinistra colonna e restituisce il valore in stessa linea dalla colonna specificata.

Secondo - specifica esattamente la tabella (o l'intervallo di celle) in cui deve essere eseguita questa ricerca.

Deve includere una colonna per la risposta, si trova a destra della colonna con il valore originale.

Ultimo argomento– visualizzazione ad intervalli, possono esserci 2 valori: 0 – MENZOGNA, 1 — VERO. è responsabile per ricerca esatta(corrisponde se visto dall'alto verso il basso). Se non viene trovato nulla, viene restituito l'errore N/A (nessun dato). 1 – approssimativo.

Per comprendere meglio come funziona la funzione, considera un esempio di utilizzo di CERCA.VERT.

Esempi di utilizzo

Il primo semplice esempio: ci sono 2 tabelle. IN uno sono indicati Merce e i loro identificatori (ID). In secondo, utilizzando un filtro per ID, vogliamo ottenere Nome del prodotto.

Dopo il segno di uguale immettere CERCA.VERT, Poi accedere E Fx per inserire argomenti.

Gli argomenti possono anche essere inseriti nella riga appropriata, separati da punto e virgola.

I valori ricercati saranno i valori in base ai quali cercheremo le corrispondenze. In questo caso lo è cella E1.

Per secondo evidenziamo l'argomento gamma di tavoli.

Necessario aggiustare(specificare il segno del dollaro o il tasto F4 per l'intero intervallo) e farlo collegamenti assoluti in modo che l'intervallo non “scivoli”, poiché in questo caso non viene specificato il nome della tabella.

- qualcosa che deve essere restituito. Questo esempio richiede la restituzione della colonna 2 ( Prodotto). Per una ricerca esatta, l'argomento 4 è .

Dopo aver inserito tutti i valori, premere il pulsante OK.

Ora, quando si modifica il numero ID nel filtro, lo sarà cambio di nome merce.

Ora vediamo un altro esempio.

Adesso ne hai bisogno prendi il lotto per ciascuno nomi prodotto per criterio Quantità.

Ad esempio, per piccolo la quantità del lotto deve provenire da da 100 a 200, media – 200-300 eccetera.

Il valore desiderato in questo caso sarà quantità, Selezionare la tabella Criterio di portata – La spedizione(correggere F4). Colonna numero 2, visualizzazione time-lapse in questo caso dovrebbe essere 1 (ti permetterà di ottenere il valore più piccolo più vicino al valore desiderato).

Come possiamo vedere nella tabella risultante per la quantità, ad esempio 110, il lotto è stato emesso da Melkaya (il 100 più piccolo più vicino), ecc. L'ordinamento dei criteri deve essere dal più al meno, altrimenti CERCA.VERT non funzionerà.

Diciamo che abbiamo una tabella con un listino prezzi delle merci. L'attività è compilare la tabella Ordini.

Per risolvere questo problema useremo Funzione CERCA.VERT. Esistono altre funzioni in Excel che possono essere utilizzate per risolvere questo problema, ma le esamineremo la funzione CERCA.VERT più popolare e utilizzata di frequente in Excel.

Quindi, per risolvere il problema nel nostro esempio, dobbiamo prima compilare la colonna "CON" nella tabella degli ordini, ovvero trova il prezzo della merce nella tabella “Listino prezzi”, quindi, per scoprire il costo, moltiplica il prezzo per il numero della merce.

Sintassi della funzione CERCA.VERT in Excel

CERCA.VERT(valore_ricerca, tabella, numero_colonna, [ricerca_intervallo])

La funzione CERCA.VERT cerca il valore nella colonna più a sinistra di una tabella e restituisce il valore della cella che si trova nella colonna specificata della stessa riga.

Diamo un'occhiata alla funzione usando il nostro esempio. Inseriamo le tariffe nella cella C3 e inseriamo la formula =CERCA.VERT(A3,$F$2:$H$22,3,0)

nella versione inglese =CERCA.VERT (A3;$F$2:$H$22;3;0)

In questa formula con CERCA.VERT:

A3 – valore di ricerca. Nel nostro caso si tratta di “Bucket”, cioè dobbiamo trovare il “bucket” nella tabella “Listino Prezzi”

$F$2:$H$22 – tavolo. Nel nostro esempio, questa è la tabella “Listino prezzi” (F2:H22). I simboli $ sono stati inseriti nell'intervallo di questa tabella per ancorarla in modo che non si sposti verso il basso quando allunghiamo la formula. Il simbolo del dollaro in Excel trasforma un intervallo relativo in un intervallo assoluto.

3 – numero di colonna. Nel nostro caso si tratta del numero “3”, poiché il prezzo si trova nella terza colonna della nostra tabella “Listino Prezzi”.

0 – visualizzazione a intervalli. Possono assumere solo due valori, 0 o 1: 0 – cerca una corrispondenza esatta, 1 – approssimativa. Nel 99% dei casi bisogna cercare il significato esatto (nel nostro caso bisogna cercare la parola “Bucket”). Pertanto, viene quasi sempre indicato il numero 0.

Così, Logica della funzione CERCA.VERT di Excel nel nostro esempio quanto segue. La funzione cerca il valore desiderato (“bucket”) nella colonna più a sinistra della tabella (“Listino prezzi”), dopo averlo trovato restituisce il valore della cella situata nella colonna specificata stessa linea, cioè il prezzo è di 120 rubli.

Dopodiché andiamo in cella D3 e trova il costo delle merci. Scriviamo la formula =C3*B3, cioè moltiplichiamo il prezzo del prodotto per la quantità.

Successivamente, per inserire automaticamente le formule per il resto dei prodotti, è necessario trascinare la formula verso il basso. Per fare ciò, è necessario selezionare entrambe le celle che devono essere allungate e abbassate nell'angolo in basso a destra (vedi Figura 3)

Lavorare con una tabella di riepilogo implica inserire al suo interno valori da altre tabelle. Se sono presenti molte tabelle, il trasferimento manuale richiederà molto tempo e se i dati vengono costantemente aggiornati, questo sarà un compito di Sisifo. Fortunatamente, esiste una funzione CERCA.VERT che offre la possibilità di recuperare automaticamente i dati. Diamo un'occhiata ad esempi specifici di come funziona questa funzione.

Il nome della funzione CERCA.VERT sta per “funzione di visualizzazione verticale”. In inglese il suo nome è CERCA.VERT. Questa funzione cerca i dati nella colonna di sinistra dell'intervallo esaminato e quindi restituisce il valore risultante nella cella specificata. In poche parole, CERCA.VERT ti consente di riorganizzare i valori da una cella in una tabella a un'altra tabella. Scopriamo come utilizzare la funzione CERCA.VERT in Excel.

Esempio di utilizzo di CERCA.VERT

Diamo un'occhiata a come funziona la funzione CERCA.VERT utilizzando un esempio specifico.

Abbiamo due tavoli. Il primo è una tabella acquisti in cui sono inseriti i nomi dei prodotti alimentari. Nella colonna successiva dopo il nome è riportato il valore della quantità del prodotto che deve essere acquistato. Poi arriva il prezzo. E nell'ultima colonna - il costo totale dell'acquisto di un prodotto specifico, che viene calcolato utilizzando la formula già inserita nella cella per moltiplicare la quantità per il prezzo. Ma non ci resta che aumentare il prezzo utilizzando la funzione CERCA.VERT della tabella adiacente, che è un listino prezzi.

Come puoi vedere, il prezzo delle patate è stato aggiunto alla tabella dal listino prezzi. Per non dover eseguire una procedura così complessa con altri nomi di prodotti, ci fermiamo semplicemente nell'angolo in basso a destra della cella riempita in modo che appaia una croce. Disegniamo questa croce in fondo al tavolo.

Pertanto, abbiamo estratto tutti i dati necessari da una tabella all'altra utilizzando la funzione CERCA.VERT.

Come puoi vedere, la funzione CERCA.VERT non è così complicata come sembra a prima vista. Comprenderne l'utilizzo non è molto difficile, ma padroneggiare questo strumento ti farà risparmiare molto tempo quando lavori con le tabelle.

Microsoft Excel ha molte funzioni diverse che semplificano molto il lavoro dell'utente e in questo articolo ne parleremo. Si chiama CERCA.VERT e, se in inglese, CERCA.VERT.

La funzione CERCA.VERT trasferisce i valori da una tabella a celle specifiche in un'altra. Lascia che ti spieghi più in dettaglio: nella prima tabella seleziona il valore che devi trovare nella colonna di sinistra della seconda. Se ci sono corrispondenze, CERCA.VERT trasferisce il valore dalla colonna specificata in questa riga alla prima tabella. Sebbene la definizione sia un po’ confusa, usare la funzione non è poi così difficile. Diamo un'occhiata ad alcuni esempi.

Poiché la funzione viene spesso utilizzata per compilare la colonna con il prezzo, indicato nei singoli listini prezzi, prendiamo il seguente esempio. Ad esempio, c'è una tabella con la frutta (arancione), dove per ciascuna è indicato quanti chilogrammi vogliamo ordinare. Di conseguenza, per ogni frutto è necessario annotare il prezzo dal listino (verde) fornito dal fornitore, e poi calcolare quanto costerà l'acquisto. È difficile visualizzare ogni articolo e trasferire i dati, soprattutto se ci sono migliaia di righe con merci, quindi utilizzeremo CERCA.VERT.

Fai clic sulla cella in alto nella prima tabella nella colonna Prezzo, quindi sul pulsante "fx" nella barra della formula per aprire la finestra Creazione guidata funzione.

La prossima cosa che facciamo è scrivere gli argomenti nei campi proposti.

Metti il ​​corsivo nel campo "Valore_ricerca" ed evidenziare nella prima tabella il valore che cercheremo. Ho questa mela.

Nella riga "Tabella", è necessario selezionare quella da cui verranno presi i dati - non selezionare l'intestazione. Tieni presente che la colonna più a sinistra dovrebbe essere composta dai valori che stiamo cercando. Cioè, ho bisogno di una mela e di altri frutti, il che significa che il loro elenco dovrebbe trovarsi nella colonna di sinistra dell'area selezionata.

Affinché dopo aver scritto la formula e averla allungata su tutta la colonna, l'intervallo selezionato non si sposti verso il basso, dobbiamo rendere assoluti i collegamenti: selezionare i dati nel campo e premere “F4”. Ora l'indirizzo delle celle è diventato assoluto, ad esse è stato aggiunto il segno "$" e l'intervallo non si sposterà.

Dove si trova il numero di colonna, inserisci il numero corrispondente nella seconda tabella alla colonna da cui desideri trasferire i dati. Il mio listino prezzi è composto da frutta e prezzo, mi serve il secondo, quindi metto il numero “2”.

IN "Lasso di tempo" Scriviamo "FALSO" - se devi cercare corrispondenze esatte, o "VERO" - se i valori possono essere approssimativi. Per il nostro esempio, scegliamo il primo. Se non specifichi nulla in questo campo, per impostazione predefinita verrà selezionato il secondo. Quindi fare clic su "OK".

Qui fai attenzione a quanto segue: se lavori con i numeri e specifichi "Vero", la seconda tabella (questo è il nostro listino prezzi) deve essere ordinata in ordine crescente. Ad esempio, quando si cerca 5.25, verrà trovato 5.27 e verranno presi i dati di questa riga, sebbene potrebbe esserci anche un numero inferiore a 5.2599 - ma la formula non cercherà oltre, perché pensa che i numeri sottostanti siano solo più grandi .

Come funziona CERCA.VERT? Prende il valore desiderato (mela) e lo cerca nella colonna più a sinistra dell'intervallo specificato (elenco dei frutti). Se c'è una corrispondenza, il valore viene preso dalla stessa riga, solo dalla colonna specificata negli argomenti (2) e trasferito nella cella di cui abbiamo bisogno (C2). La formula è simile alla seguente:

CERCA.VERT(A2,$G$2:$H$12,2,FALSO)

Ora puoi allungarlo fino al numero di linee richiesto tirando l'angolo in basso a destra.

Tutti i prezzi sono stati trasferiti dal listino prezzi alla tabella acquisti in base ai nomi dei frutti.

Se nella prima tabella hai i nomi di prodotti che non sono nel listino prezzi, per me si tratta di verdure, allora di fronte a questi punti la formula CERCA.VERT produrrà l'errore #N/A.

Man mano che aggiungi colonne al foglio di lavoro, i dati per l'argomento Tabella della funzione cambieranno automaticamente. Nell'esempio, il prezzo viene spostato di 2 colonne a destra. Selezioniamo qualsiasi cella con una formula e vediamo che invece di $G$2:$H$12 ora è $I$2:$J$14 .

Ora scopriamo come lavorare con la funzione CERCA.VERT in Excel e con gli elenchi a discesa. Per prima cosa devi creare un elenco a discesa. Seleziona la cella, apri “Dati” in alto e fai clic sul pulsante "Controllo dei dati".

Nella finestra "Tipo di dati" che si apre ci sarà "Elenco", di seguito indichiamo l'area di origine: questi sono i nomi dei frutti, cioè la colonna che si trova sia nella prima che nella seconda tabella. Fare clic su "OK".

Seleziono F2 e inserisco la funzione CERCA.VERT. Il primo argomento è la lista creata (F1).

La seconda è una tabella dei fornitori con i prezzi indicati. Ricorda che qui la colonna di sinistra deve corrispondere ai dati che compongono l'elenco a discesa.

Si è rivelata una sorta di ricerca: selezioniamo un frutto e VPR ne trova il prezzo nel listino prezzi.

Se avevi gli stessi prezzi e poi i fornitori ti hanno dato un nuovo elenco, allora devi in ​​qualche modo vedere cosa è cambiato. Farlo manualmente richiede molto tempo, ma con l'aiuto della funzione in questione si può fare tutto molto velocemente. Dovrai aggiungere una colonna e trasferirvi nuovi valori, quindi confrontare semplicemente i dati.

Fare clic su qualsiasi cella nella colonna D e inserirne una nuova.

L'ho chiamato Nuovo Prezzo: i nuovi prezzi saranno qui, mentre i vecchi prezzi verranno mostrati nella colonna a sinistra. La mia nuova tabella si trova su un altro foglio, in modo che tu possa capire come utilizzare CERCA.VERT se i dati si trovano su fogli diversi. Nella colonna aggiunta, seleziona la prima cella vuota.

Inseriamo la funzione e specifichiamo gli argomenti. Innanzitutto cosa cercheremo, nell'esempio una mela (A2). Per selezionare una fascia dal nuovo listino posizionare il cursore nel campo “Tabella” e portarsi sul foglio desiderato, io ho il “Foglio1”.

Utilizza il mouse per selezionare le colonne e le righe richieste, senza intestazioni.

Successivamente facciamo riferimenti assoluti alle celle: “Sheet1!$A$2:$B$12” . Seleziona la riga e premi "F4" per aggiungere il simbolo del dollaro agli indirizzi delle celle. Indichiamo la colonna (2) e scriviamo “FALSO”.

Alla fine, fare clic sul pulsante "OK".

Ora due colonne con i prezzi nuovi e vecchi si trovano una accanto all'altra ed è possibile effettuare un confronto visivo oppure applicando determinate formule o formattazione condizionale.

Spero di aver fornito istruzioni dettagliate su come utilizzare e applicare la funzione CERCA.VERT in Excel e ora tutto ti è chiaro.

(15 valutazioni, media: 5,00 su 5)

Webmaster. Istruzione superiore con laurea in Sicurezza informatica. Autore della maggior parte degli articoli e lezioni di alfabetizzazione informatica