Come usare VLOOKUP Excel? Funzione Excel in Excel per manichini e non solo

Qualsiasi persona che si occupa di numeri, sia per interesse professionale o semplicemente per amore dell'arte, ovviamente, la prima cosa è padroneggiare la calcolatrice. Qual è il prossimo? Il prossimo a sua volta è il geniale programma per lavorare con i numeri - Excel. Probabilmente è impossibile conoscere perfettamente questo programma, oltre a conoscere perfettamente la matematica. Tuttavia, ci sono funzionalità di base nel programma, la funzione di Excel, sapendo che con una sicurezza del 90% possiamo parlare della capacità di lavorare con i dati a livello di utente fidato. Una delle funzioni più importanti in Excel è VLOOKUP, che è molto versatile. Come utilizzare VLOOKUP in Excel (per i manichini) è trattato nell'articolo. Naturalmente, la percezione della teoria non porterà a una completa comprensione del processo di utilizzo della funzione. È necessario esercitarsi prima su matrici di dati di piccole dimensioni, quindi su tabelle di qualsiasi dimensione.

L'essenza e lo scopo della funzione CDF in Excel

La funzione CDF consente di risparmiare molto tempo a specialisti come economisti, finanzieri, contabili, tecnici. La descrizione della funzione CERCA.VERT in Excel e un esempio della sua applicazione dovrebbe essere avviata considerando la situazione che spesso gli esperti di vario genere incontrano nel loro lavoro. Se ci sono due tabelle in cui compaiono gli stessi dati definibili (ad esempio, i nomi delle merci), ma le definizioni per loro in diverse tabelle differiscono, per esempio, in uno c'è una quantità, e in un altro un prezzo. Allo stesso tempo, il compito è quello di combinare tutti questi dati insieme e fare i calcoli appropriati - in tali situazioni, la funzione CDF viene in soccorso. Per capire come funziona CERCA.VERT in Excel, vengono presentate due tabelle di seguito. In ognuno di essi c'è un codice prodotto. In uno c'è una quantità appropriata, nell'altro - il prezzo. Per la sostituzione dei prezzi nella prima tabella e CDF utilizzati.

La sostituzione manuale dei dati tra le tabelle è un enorme spreco di tempo e fatica del dipendente, gli errori non possono essere evitati. Con l'aiuto di CDF puoi trovare i valori corrispondenti rapidamente e facilmente. Per una conoscenza approfondita di come utilizzare VLOOKUP in Excel, è necessario analizzare tutti gli argomenti della funzione.

Argomenti di funzione

Per utilizzare la funzione, è necessario posizionarsi nella cella dei risultati, selezionare la scheda Formule - "Collegamenti e array" - "CDF" sulla barra multifunzione. Nella cella appare la scritta "= VLR" Ora devi inserire correttamente gli argomenti della funzione, puoi farlo attraverso un punto e virgola direttamente nella barra della formula, ma è più comodo per un utente principiante farlo attraverso la finestra di dialogo degli argomenti della funzione.

La funzione CDF ha quattro argomenti: il valore che stai cercando, la tabella, il numero di colonna, la ricerca a intervalli. I primi tre, evidenziati in grassetto nella finestra di dialogo, sono obbligatori. Spieghiamo quali sono gli argomenti della funzione:

• il valore ricercato è cosa cercare;
• tavolo - dove guardare;
• numero di colonna - in quale colonna cercare;
• intervallo di visualizzazione - ordinato.

Argomento "valore ricercato"

Nel primo campo devi inserire ciò che devi cercare. Ci può essere un numero, un testo, una data. Puoi inserire un valore assoluto, puoi apporre un riferimento di cella con il valore desiderato. Il valore ricercato dovrebbe teoricamente essere presente in entrambe le tabelle. Nell'esempio sopra, un tale valore può essere un codice prodotto. Per evitare errori, è meglio usare il link.

Quando si utilizza il testo come valore desiderato, deve essere tra virgolette. Quando ci si riferisce a una cella con il testo, le virgolette non sono necessarie. Registrati quando inserisci il testo non ha importanza. Può essere usata una ricerca fuzzy, basata su un frammento di testo. Per fare questo, all'interno delle virgolette è necessario racchiudere una parte di testo in * caratteri.

Il problema più comune quando si utilizza la funzione è il diverso formato delle celle negli intervalli. Se nel campo del primo argomento inserire un collegamento alla cella in cui i dati sono in formato testo e nell'intervallo di ricerca i dati saranno in formato numerico, non troverà una corrispondenza con l'identità esterna dei valori CDF.

La conversione in formato numerico è semplice: è necessario moltiplicare l'array di uno.

Argomento della tabella

Qui è necessario inserire l'intervallo in cui la funzione cercherà il primo valore inserito. L'intervallo per la ricerca dei valori sarà la prima colonna dell'area selezionata. Il valore che si desidera trovare e inserire come risultato deve essere nella colonna a destra rispetto alla colonna di ricerca della corrispondenza. Questo è uno dei principali svantaggi dell'utilizzo di CDF: per poter lavorare con esso, la tabella deve spesso essere ricostruita in modo che i dati richiesti siano a sinistra. La gamma può essere inserita sia manualmente che per riferimento. Nell'esempio, assomiglia a questo:

La gamma qui è la tabella giusta. Il valore cercato nella colonna a destra, dove Excel cercherà il valore 3187849428, il valore che deve essere trovato e sostituito come risultato della formula è a sinistra. Per ottenere un risultato accurato, è meglio correggere l'intervallo, selezionarlo e premere il tasto F4, il collegamento all'array diventerà assoluto.

Argomento "numero di colonna"

Qui è necessario inserire una cifra in cui nella colonna la colonna, da quella più a sinistra, è necessario prendere il valore per la sostituzione come risultato del calcolo. Nell'esempio precedente, questa è la seconda colonna, nella stringa di argomenti è necessario inserire il numero 2. Se dovesse esserci un'altra colonna tra le colonne Codice e Prezzo, sarà necessario inserire il numero 3 e così via.

Argomento di anteprima intervallo

Il riempimento in questo campo è facoltativo, ma può essere molto importante. Ci può essere uno dei due valori: 1 (vero) o 0 (falso). La maggior parte degli utenti ritiene che la funzione di questo argomento sia determinare l'accuratezza della corrispondenza dei valori desiderati. Questo non è del tutto corretto. Quando si specifica un'unità, se ci sono ripetizioni nell'intervallo della tabella, la funzione restituirà l'ultimo valore trovato. In questo caso, la funzione VLR terrà conto di tutti i valori inferiori o uguali a quelli immessi nel campo "Valore richiesto". Se la funzione trova un valore più grande ma non trova un valore inferiore o uguale, genererà un errore N / A.

Potete vedere che il numero 3187849425 è inserito nel campo "Valore", non esiste tale valore nell'intervallo desiderato e il programma, trovando tutti i valori inferiori o uguali a quello desiderato, ha restituito il valore corrispondente all'ultimo codice idoneo nell'elenco - 3187848593, il cui prezzo è 2.479,46 rubli. Se il campo di visualizzazione Intervallo è vuoto, la funzione funzionerà allo stesso modo del valore dell'unità.

Quando il valore 0 viene inserito nel campo argomento, la funzione restituirà solo il valore corrispondente a quello richiesto. Se c'è una ripetizione nell'intervallo di ricerca, la funzione avrà la prima corrispondenza. Quando si utilizza una formula con argomento 0, la funzione funziona molto più a lungo, ma in modo molto più preciso.

Caratteristiche dell'uso di CDF in diverse condizioni

L'uso della funzione CDF è spesso un processo creativo che richiede il pensiero matematico dell'utente. Spesso è necessario trovare una corrispondenza non per una colonna, ma per due o anche più. Con l'aiuto di ulteriori azioni, è possibile applicare anche la funzione CERCA.VERT. È necessario creare colonne aggiuntive in entrambe le tabelle, in cui combinare i dati delle colonne considerate. Questo può essere fatto usando la funzione "CLUTCH" o l'icona "&".

Come utilizzare la funzione CDF, se i dati sono su fogli diversi

Come si usa VLOOKUP in Excel su diversi fogli, in diversi file? Spesso gli utenti creano alcuni libri di consultazione con i quali è necessario confrontare e trovare corrispondenze. Le differenze di lavoro in tali condizioni consistono in una forma alquanto diversa del campo "Tabella" nella finestra degli argomenti della funzione. Prima di inserire l'intervallo, è necessario inserire il numero del foglio (se i dati sono in un libro) o il nome del file (se i dati non sono in un libro). Se nell'esempio precedente, il listino prezzi viene copiato in un file separato, chiamalo "Prezzo" e, utilizzando la funzione VPR, cerca i prezzi lì, sarà simile a questo:

Tutte le azioni di cui sopra possono essere fatte usando il link. È necessario posizionare il cursore nel campo "Tabella", andare al file desiderato e selezionare l'intervallo con il mouse. A volte capita che una tabella non sia inserita come collegamento in una finestra. Quindi devi fare quanto segue: apri la tabella originale, dove devi fare i calcoli, poi attraverso il menu "File" - "Apri" trova la seconda tabella. I file aperti in questo modo funzionano senza intoppi.

Tutte le azioni di cui sopra possono essere fatte usando il link. È necessario posizionare il cursore nel campo "Tabella", andare al file desiderato e selezionare l'intervallo con il mouse. A volte capita che una tabella non sia inserita come collegamento in una finestra. Quindi devi fare quanto segue: apri la tabella originale, dove devi fare i calcoli, poi attraverso il menu "File" - "Apri" trova la seconda tabella. I file aperti in questo modo funzionano senza intoppi.

Per pesare il file in cui ci sono le formule con CDF, con riferimento ad altri file sarà molto più che senza di essi. Questo può essere problematico, ad esempio il trasferimento di file. Per evitare questi problemi, è necessario convertire la formula in valori. Sulla barra multifunzione selezionare il sottomenu "Dati" e il comando "Modifica comunicazione". Anche se i dati non sono presi da un altro file, è sempre utile sostituire le formule con i valori - questo rende i calcoli più affidabili.

Elenco a discesa per facilitare il lavoro con CDF

Spesso la funzione VLR non funziona se ci sono discrepanze nei dati minori. Quel spazio in più nel testo, i dati inseriti con errori. Per evitare tutti questi problemi, utilizzare l'elenco a discesa in Excel per immettere i valori. Ha senso avviarlo quando si lavora con dati costantemente ripetitivi. Se esiste un determinato libro di riferimento che viene utilizzato come tabella per il confronto, l'intervallo di confronto può essere considerato come dati per un elenco a discesa e utilizzato per formare una tabella, in cui i dati verranno quindi inseriti nella funzione CDF. Il cursore viene posizionato nella cella in cui è necessario immettere un valore. Più avanti sul nastro c'è una sottosezione "Dati", il comando "Verifica dati" è selezionato. Nella finestra di dialogo, nel campo "Tipo di dati", inserire il valore "Elenco". Nel campo "Sorgente" viene registrato il range della tabella di riferimento. L'elenco a discesa è formato. Ora, durante il riempimento della tabella, è garantita la piena conformità dei valori.

Un esempio di utilizzo della funzione CERCA.VERT in Excel

Come utilizzare VLOOKUP in Excel, ogni utente risolve se stesso, sulla base di compiti pratici nel suo lavoro. Prendendo in considerazione tutte le sfumature di cui sopra, si può portare l'esempio precedente alla sua fine logica e costruire un algoritmo comodo e chiaro per applicare la funzione CDF nella pratica. Quindi, c'è una stima dei costi per i pezzi di ricambio per la riparazione delle attrezzature. C'è anche un listino prezzi per i pezzi di ricambio. L'economista della compagnia è tenuto a trovare il costo dei pezzi di ricambio per le riparazioni.

Per prima cosa devi scrivere correttamente la formula.

Quindi, usando la funzione CERCA.VERT, il prezzo apparirà corrispondente al codice nella prima riga. È necessario moltiplicare la formula verso il basso, per fare ciò, selezionare la cella con la formula e abbassare il quadrato nell'angolo destro della cella. Nella colonna D, la funzione ha restituito i prezzi corrispondenti ai codici. È necessario sostituire le formule con i valori. Per fare ciò, selezionare le celle piene della colonna D, copiarle e incollarle come valori. Successivamente, è necessario creare una colonna "Somma", in cui immettere la formula per il prodotto di quantità e prezzo, quindi utilizzare la somma per sommare i costi.

Questo è un esempio di come VLOOKUP funziona in Excel.

Errori durante l'utilizzo della funzione CDF

Nella fase iniziale di utilizzo, anziché i valori desiderati, la funzione indica spesso vari tipi di errori. Sapere cosa significa un errore è il modo giusto per risolverlo rapidamente. Gli errori restituiti più frequentemente:

• "N / A" - il tipo più comune di errore. Potrebbe verificarsi per diverse ragioni.

1. La colonna su cui la funzione sta cercando una corrispondenza non è posizionata correttamente (deve essere la più a sinistra). Se si verifica una situazione in cui il valore desiderato si trova a sinistra dell'area della partita, la tabella deve essere convertita. Ad esempio, copia la colonna desiderata e incollala a destra dell'area di ricerca.
2. L'errore "N / A" può essere restituito se l'intervallo di ricerca non è fisso, mentre si tira la formula WFD.
3. Se viene specificata una ricerca esatta utilizzando l'argomento Intervallo vista (la cifra 0 è impostata), viene restituito l'errore "N / A" se non esiste una corrispondenza esatta nelle due tabelle.
4. L'argomento Intervallo di visualizzazione è impostato sul valore più vicino (viene indicato 1 o il campo non viene riempito) e l'intervallo sul quale sta effettuando la ricerca non è ordinato. In caso di ricerca imprecisa, è necessario ordinare la colonna più a sinistra dell'intervallo di ricerca.
5. I dati confrontati hanno spazi extra (per rimuoverli, puoi usare la funzione "SIFPROBELS", applicandola alla tabella e al valore desiderato), un formato diverso, virgolette extra. Per scrivere gli stessi valori in entrambe le tabelle, ha senso utilizzare l'elenco a discesa.

• "LINK": questo errore si verifica spesso quando il numero di colonna è specificato in modo errato, se così tante colonne non si trovano nell'intervallo selezionato. In questi casi è necessario ricordare che il numero è messo giù, contando dalla colonna sinistra dell'intervallo selezionato e non dalla tabella nel suo insieme.
• "NOME" - l'errore viene spesso restituito quando il testo viene inserito nel "Valore richiesto". Il testo deve essere scritto tra virgolette.

Conoscenza di come utilizzare VLOOKUP in Excel, vieni, come tutto il resto in questo programma, con la pratica. Il semplice studio della teoria invece della chiarezza è probabile che porti il ​​caos nella comprensione di una particolare operazione nel programma. Quando si studia una funzione di Excel, è sempre più efficace per l'utente affidarsi a un tablet specifico, anche se di piccole dimensioni. Questo ti permette di capire meglio l'essenza dell'analisi, per consolidare le conoscenze acquisite. Quindi con la funzione CDF in Excel. Con una grande quantità di teoria, l'applicazione pratica mostra che non è così complessa come è utile.