Excel-Usando VLOOKUP per confrontare due liste

Passo 1: Ci sono alcune regole quindi abbiamo controllato questi primi

Il cliente aveva inviato un foglio di lavoro Excel molto semplice che mostrava un elenco dei loro record di pagamento per ordine di acquisto, numero di fattura, importo e data.

L’amministratore (chiamiamolo Guy) ha quindi generato un rapporto Excel dal sistema di account della sua azienda. Abbiamo quindi proceduto a utilizzare entrambi i fogli di lavoro Excel per fare la nostra riconciliazione.

Tuttavia, prima di poter iniziare a utilizzare la funzione VLOOKUP, ho spiegato la complessità di ottenere i tuoi dati in un formato che garantirà il funzionamento di VLOOKUP.

Excel aveva bisogno di quanto segue:

• All’interno cartella di lavoro del cliente e cartella di lavoro del ragazzo c’era bisogno di essere almeno un pezzo corrispondente di informazioni per ogni record. Ad esempio, entrambe le cartelle di lavoro avevano il numero della fattura e il numero dell’ordine di acquisto per ogni record di transazione. Abbiamo deciso di utilizzare il numero di fattura nella cartella di lavoro del ragazzo per individuare una corrispondenza per lo stesso numero di fattura nella cartella di lavoro del cliente. Questa informazione è chiamata “Valore di ricerca”.
• Il valore di ricerca deve essere univoco, ovvero non può essere ripetuto più volte nell’elenco del cliente. Fortunatamente per Guy c’era solo un record per ogni fattura. Nella fattura aziendale e i numeri PO sono quasi sempre unici per una singola transazione, quindi sono eccellenti da utilizzare come valori di ricerca. Altri esempi sono codici prodotto, numeri cliente o conto, numeri dipendenti o codici reparto. È sempre buona norma controllare che non ci siano duplicati prima di eseguire il VLOOKUP.
• Il valore di ricerca doveva essere posizionato nella prima colonna dell’elenco nella cartella di lavoro del cliente. Sfortunatamente non lo era. Si trovava nella 2a colonna (colonna B) quindi abbiamo tagliato e incollato la colonna davanti al P/O No. colonna. Questa è una regola importante quando si utilizza la funzione VLOOKUP. Se il valore di ricerca non è nella prima colonna, la ricerca non funzionerà.

Una volta deciso il nostro Valore di ricerca e riorganizzato l’elenco nella cartella di lavoro del cliente, eravamo pronti a creare il nostro VLOOKUP.

Passaggio 2: Inserimento della funzione VLOOKUP

La ‘V’ in VLOOKUP sta per “verticale”. Una ricerca verticale viene utilizzata per cercare dati specifici nella prima colonna di una tabella di dati. Una volta trovata la riga che contiene i dati che stai cercando, rimbalza su un’altra colonna nella stessa tabella di dati e restituisce informazioni da essa.

Abbiamo deciso di inserire la funzione VLOOKUP nella cartella di lavoro di Guy. In questo modo potremmo utilizzare la funzione per fare riferimento al numero di fattura dal suo elenco e verificare lo stesso numero di fattura nella prima colonna dell’elenco del cliente. Se ha trovato una corrispondenza, restituirà nella cella l’importo che il cliente aveva rispetto al numero della fattura. Avremmo quindi finito con l’importo del ragazzo e l’importo del cliente nello stesso foglio di lavoro. Potremmo quindi confrontare facilmente i due e capire quali transazioni non corrispondevano.

Abbiamo creato una nuova intestazione di colonna chiamata “Record del cliente”, posizionandola alla fine dell’elenco esistente di Guy. Posizionando il selettore di cella sulla cella E5 abbiamo inserito la funzione VLOOKUP.

Per inserire la funzione abbiamo cliccato sulla scheda Formule e quindi dal gruppo Libreria Funzioni cliccato sul pulsante Inserisci funzione.

Suggerimento: la funzione VLOOKUP può essere inserita anche facendo clic sul pulsante Inserisci funzione sulla barra della formula.

Dalla casella Seleziona una categoria abbiamo selezionato Ricerca e riferimento. Dalla casella di riepilogo Seleziona una funzione abbiamo selezionato VLOOKUP e quindi fatto clic su OK.

A questo punto dovevamo inserire i valori appropriati nelle caselle degli argomenti della funzione. Ti passo attraverso ciascuno degli argomenti.

• Lookup_value – Questo era il numero della fattura contenuto nella cella C5. Una volta inserito questo nella casella del valore di ricerca, Excel lo ha tenuto in memoria e ha aspettato che completassimo gli argomenti rimanenti.

• Table_array-L’array di tabelle è l’elenco che contiene i dettagli della transazione sul foglio di lavoro del cliente. VLOOKUP cercherà una corrispondenza per il ‘Valore di ricerca’ nella prima colonna dell’array di tabelle. Guy ha navigato verso il foglio di lavoro del cliente e quindi ha fatto clic e trascinato sull’intera area che conteneva tutti i dettagli delle transazioni. Ha incluso la colonna ‘Date’, ma A4:C26 sarebbe andato bene anche perché la colonna’ Amount ‘ si trova all’interno di questo intervallo. Si prega di notare che Guy ha reso l’intervallo assoluto in modo che la formula potesse essere facilmente copiata.

• Col_index_num-Questa è la colonna da cui Guy voleva estrarre l’importo della fattura del cliente. Questa era la colonna 3 nell’elenco delle transazioni del cliente. Le colonne sono numerate da sinistra a destra con la prima colonna nell’intervallo dell’array di tabelle che è la colonna 1. Fare attenzione a non ottenere numeri di colonna e lettere di colonna mescolati. Anche se l’importo della fattura è tenuto nella colonna ‘C’ Excel desidera solo la posizione del numero di indice della colonna, non la lettera della colonna.

• Range_lookup-La ricerca Intervallo controlla se stai cercando una corrispondenza esatta con il tuo ‘Valore di ricerca’. Ho spiegato a Guy che se avesse inserito TRUE o lasciato la casella vuota Excel avrebbe cercato il suo numero di fattura, ma se non riusciva a trovare una corrispondenza avrebbe restituito il valore più grande successivo che è inferiore al numero di fattura. Ad esempio, se non riuscisse a trovare la fattura 2466, troverebbe il record per 2465 e restituirebbe invece l’importo per quello. Affinché ciò funzioni, l’elenco dei clienti deve essere prima ordinato in base al numero di fattura. Tuttavia, se’ FALSE ‘ viene inserito nella casella Excel cercherà una corrispondenza esatta per il valore di ricerca. Guy voleva una corrispondenza esatta per il suo numero di fattura così ha inserito falso nella scatola. Ho anche passato il suggerimento che puoi inserire 0 (zero) al posto della parola ‘FALSE’ e Excel farà la stessa cosa.

Con tutti gli argomenti inseriti Guy ha fatto clic su OK.

Excel passa quindi attraverso ciascuno dei valori forniti nella casella argomento. La funzione ha utilizzato il numero di fattura (valore di ricerca) per cercare un numero di fattura corrispondente nella colonna più a sinistra dei record del cliente (array di tabelle). Una volta trovato il numero della fattura, il numero di colonne specificato (numero di indice della colonna) è passato attraverso la stessa riga per restituire l’importo della fattura.

Per fortuna, come puoi vedere qui sotto, il cliente aveva lo stesso valore di fattura di Guy.

Guy ha quindi copiato la funzione VLOOKUP per tutte le transazioni della fattura.
In pochi secondi poteva vedere tre errori # N/A (errori di valore non disponibili). Questo ha mostrato dove il cliente non ha effettivamente un record di queste fatture. Ha poi trovato rapidamente le differenze nella sua e gli importi della fattura del cliente. Immediatamente poté vedere che la fattura 2464 era breve $100.00, molto probabilmente un errore di keying.

Fase 3: il doppio controllo

Avevamo ora confrontato con successo i nostri record per il cliente. Tuttavia, come un doppio controllo abbiamo poi deciso di fare un confronto tra i record del cliente, nel caso in cui avevamo perso qualcosa.

Questa volta abbiamo creato il VLOOKUP nel foglio di lavoro del cliente. La prima cosa che dovevamo fare era spostare la colonna della fattura in modo che fosse la prima colonna nel nostro elenco di transazioni (Array di tabelle). Dopo aver fatto questo abbiamo quindi inserito la funzione VLOOKUP in una nuova colonna chiamata ‘Il nostro record’.

Guy ha copiato la funzione e quindi creato la colonna “Confronto”. Il nostro doppio controllo ha confermato che il cliente aveva sotto pagato us us 100 sulla fattura 2464. Il nostro controllo ha anche dimostrato che il cliente aveva del tutto possibile registrato il numero di fattura per la fattura 2466 come 2646.

Conclusione

Guy potrebbe ora tornare dal cliente per discutere le informazioni che potrebbero essere state digitate per errore e per fornire copie delle fatture che il cliente non aveva un record di ricezione.

Inutile dire che la funzione VLOOKUP ha salvato Guy e i suoi compagni di lavoro, una quantità enorme di tempo. Il responsabile della formazione e dello sviluppo mi ha contattato dopo la formazione per dire che la migliore efficienza nel reparto aveva salvato la loro azienda 10s di 1000s di dollari.

Copriamo la funzione VLOOKUP nei nostri corsi Excel Stage 3. Se hai a che fare con dati di grandi dimensioni questo allenamento è sicuramente per te.