Excel – folosind VLOOKUP pentru a compara două liste

Pasul 1: Există câteva reguli, așa că am verificat aceste prime

clientul a trimis o foaie de lucru Excel foarte de bază, care a arătat o listă a înregistrărilor lor de plată prin comanda de cumpărare, numărul facturii, suma și data.

administratorul (să-l numim Guy) a generat apoi un raport Excel din sistemul de conturi al companiei sale. Apoi am procedat la utilizarea ambelor foi de lucru Excel pentru a face reconcilierea noastră.

cu toate acestea, înainte de a putea începe să utilizați funcția VLOOKUP am explicat complexitatea de a obține datele într-un format care va asigura VLOOKUP funcționează.

Excel avea nevoie de următoarele:

• în registrul de lucru al clientului și în registrul de lucru al tipului, trebuie să existe cel puțin o informație potrivită pentru fiecare înregistrare. De exemplu, ambele registre de lucru aveau numărul facturii și numărul comenzii de achiziție pentru fiecare înregistrare de tranzacție. Am decis să folosim numărul facturii din registrul de lucru al lui Guy pentru a găsi o potrivire pentru același număr de factură din registrul de lucru al clientului. Această informație se numește ‘valoare de căutare’.
• valoarea de căutare trebuie să fie unică, adică nu poate fi repetată în lista clientului de mai multe ori. Din fericire pentru Guy, exista o singură înregistrare pentru fiecare factură. În factura de afaceri și numerele PO sunt aproape întotdeauna unice pentru o singură tranzacție, astfel încât acestea sunt excelente de utilizat ca valori de căutare. Alte exemple sunt codurile produselor, numerele clienților sau ale contului, numerele angajaților sau codurile Departamentului. Este întotdeauna o practică bună să verificați dacă nu există duplicate înainte de a efectua VLOOKUP-ul.
• valoarea de căutare trebuie să fie localizată în prima coloană a listei din registrul de lucru al clientului. Din păcate, nu a fost. a fost localizat în coloana a 2-a (coloana B), așa că am tăiat și lipit coloana din fața P/O nr. coloana. Aceasta este o regulă importantă atunci când utilizați funcția VLOOKUP. Dacă valoarea de căutare nu este în prima coloană căutare nu va funcționa.

odată ce am decis valoarea noastră de căutare și am reorganizat lista din registrul de lucru al clientului, am fost gata să creăm VLOOKUP-ul nostru.

Pasul 2: introducerea funcției VLOOKUP

„V”din VLOOKUP înseamnă „vertical”. O căutare verticală este utilizată pentru a căuta date specifice în prima coloană a unui tabel de date. Odată ce găsește rândul care deține datele pe care il cautati apoi sare peste la o altă coloană în același tabel de date și returnează informații de la ea.

am decis să inserăm funcția VLOOKUP în registrul de lucru al lui Guy. În acest fel, am putea folosi funcția pentru a face referire la numărul facturii din lista sa și pentru a verifica același număr de factură în prima coloană a listei clientului. În cazul în care a găsit un meci ar reveni apoi în celulă suma pe care clientul a avut față de numărul facturii. Ne-ar termina apoi cu suma Guy și suma clientului în aceeași foaie de lucru. Am putea apoi să le comparăm cu ușurință pe cele două și să stabilim care tranzacții nu se potrivesc.

am creat un nou titlu de coloană numit ‘înregistrarea Clientului’, plasându-l la sfârșitul listei existente a lui Guy. Plasând selectorul de celule pe celula E5 am introdus funcția VLOOKUP.

pentru a insera funcția am făcut clic pe fila Formule și apoi din grupul Biblioteca de funcții am făcut clic pe butonul Inserare funcție.

sfat: funcția VLOOKUP poate fi inserată și făcând clic pe butonul Insert Function din bara de formule.

din caseta Selectare categorie am selectat căutare și referință. Din caseta Selectați o listă de funcții am selectat VLOOKUP și apoi am făcut clic pe OK.

în acest moment trebuia să introducem valorile corespunzătoare în casetele Argumente funcționale. Te voi pas prin fiecare dintre argumentele.

• Lookup_value – acesta a fost numărul facturii deținut în celula C5. Odată ce am introdus acest lucru în caseta de valoare de căutare Excel a ținut-o în memorie și a așteptat să finalizăm argumentele rămase.

• Table_array – matricea tabelului este lista care conține detaliile tranzacției în foaia de lucru a clientului. VLOOKUP va căuta o potrivire pentru ‘Lookup Value’ în prima coloană a tabloului. Guy navigat la foaia de lucru a clientului și apoi a făcut clic și târât pe întreaga zonă care a avut loc toate detaliile tranzacțiilor. El a inclus coloana ‘Data’, dar A4:C26 ar fi fost bine, deoarece coloana’ Suma ‘ se află în acest interval. Vă rugăm să rețineți că Guy a făcut gama absolută, astfel încât formula să poată fi ușor copiată.

• Col_index_num – aceasta este coloana din care Guy a vrut să tragă prin suma facturii clientului. Aceasta a fost coloana 3 din lista de tranzacții a clientului. Coloanele sunt numerotate de la stânga la dreapta, prima coloană din intervalul matricei tabelului fiind coloana 1. Aveți grijă să nu amestecați numerele coloanelor și literele coloanelor. Chiar dacă suma facturii este păstrată în coloana ‘C’ Excel dorește doar locația numărului de index al coloanei, Nu litera coloanei.

• Range_lookup-range Lookup controlează dacă sunteți în căutarea pentru o potrivire exactă a ‘valoare de căutare’. I-am explicat lui Guy că, dacă ar intra adevărat sau ar lăsa caseta goală, Excel ar căuta numărul facturii sale, dar dacă nu ar putea găsi o potrivire, ar returna următoarea valoare cea mai mare, care este mai mică decât numărul facturii. De exemplu, dacă nu ar putea găsi factura 2466, ar găsi înregistrarea pentru 2465 și ar returna suma pentru aceasta. Pentru ca aceasta să funcționeze, lista clientului trebuia să fie sortată mai întâi după numărul facturii. Cu toate acestea, dacă’ FALSE ‘ este introdus în caseta Excel va căuta o potrivire exactă pentru valoarea de căutare. Tipul a vrut o potrivire exactă pentru numărul facturii, așa că a intrat fals în cutie. De asemenea, am trecut pe vârful pe care îl puteți introduce 0 (zero) în locul cuvântului ‘fals’ și Excel va face același lucru.

cu toate argumentele introduse Tip clic OK.

Excel a trecut apoi prin fiecare dintre valorile furnizate în caseta de argumente. Funcția a folosit numărul facturii (valoarea de căutare) pentru a căuta un număr de factură care se potrivește în coloana din stânga a înregistrărilor clientului (matrice de tabele). Odată ce numărul facturii a fost găsit, acesta a sărit peste numărul specificat de coloane (numărul indexului coloanei) pe același rând pentru a returna suma facturii.

din fericire, după cum puteți vedea mai jos, clientul a avut aceeași valoare a facturii ca Guy.

Guy apoi copiat funcția VLOOKUP pentru toate tranzacțiile Factură.
în câteva secunde el a putut vedea trei # N / A erori (valoare erori nu sunt disponibile). Acest lucru a arătat unde clientul nu avea de fapt o înregistrare a acestor facturi. Apoi a găsit rapid diferențele dintre sumele facturii sale și ale clientului. Imediat a putut vedea că factura 2464 a fost scurtă $100.00, cel mai probabil o eroare de tastare.

Pasul 3: verificarea dublă

acum am comparat cu succes înregistrările noastre cu cele ale clientului. cu toate acestea, ca o verificare dublă, am decis apoi să facem o comparație din înregistrările clientului, doar în cazul în care am pierdut ceva.

de data aceasta am creat VLOOKUP în foaia de lucru a clientului. Primul lucru pe care trebuia să-l facem a fost să mutăm coloana factură astfel încât să fie prima coloană din lista noastră de tranzacții (Table Array). După ce am făcut acest lucru, am introdus funcția VLOOKUP într-o nouă coloană numită ‘înregistrarea noastră’.

Guy a copiat funcția în jos și apoi a creat coloana ‘comparație’. Verificarea noastră dublă a confirmat că clientul a plătit sub 100 USD pe factura 2464. Verificarea noastră a arătat, de asemenea, că clientul a înregistrat destul de posibil numărul facturii pentru factura 2466 ca 2646.

concluzie

Guy ar putea acum să se întoarcă la client pentru a discuta informațiile care ar fi putut fi tastate din greșeală și pentru a furniza copii ale facturilor pe care clientul nu avea o înregistrare de primire.

inutil să spun, funcția VLOOKUP salvat tip, și colegii săi de muncă, o mare cantitate de timp. Managerul de formare și dezvoltare ma contactat după formare pentru a spune că eficiența îmbunătățită în departamentul a salvat compania lor 10s de 1000s de dolari.

acoperim funcția VLOOKUP în cursurile noastre Excel Stage 3. Dacă aveți de-a face cu date mari, acest antrenament este cu siguranță pentru dvs.