VLOOKUP til at sammenligne to lister

Trin 1: der er et par regler, så vi kontrollerede disse først

kunden havde sendt et meget grundlæggende arbejdsark, der viste en liste over deres betalingsoptegnelser efter indkøbsordre, fakturanummer, beløb og dato.

administratoren (lad os kalde ham Guy) genererede derefter en rapport fra hans virksomheds regnskabssystem. Vi fortsatte derefter med at bruge begge regneark til at udføre vores forsoning.

men før vi kunne begynde at bruge VLOOKUP-funktionen forklarede jeg indviklingen ved at få dine data til et format, der sikrer, at VLOOKUP fungerer.

brug for følgende:

• i kundens projektmappe og Guy ‘ s projektmappe skulle der være mindst et matchende stykke information for hver post. For eksempel havde begge projektmapper fakturanummeret og indkøbsordrenummeret for hver transaktionspost. Vi besluttede at bruge fakturanummeret i Guy ‘ s projektmappe til at finde et match for det samme fakturanummer i kundens projektmappe. Dette stykke information kaldes ‘Opslagsværdi’.
• Opslagsværdien skal være unik, dvs.den kan ikke gentages på kundens liste flere gange. Heldigvis for Guy var der kun en rekord for hver faktura. I forretningsfaktura og PO-numre er næsten altid unikke for en enkelt transaktion, så de er fremragende at bruge som opslagsværdier. Andre eksempler er produktkoder, klient-eller kontonumre, medarbejdernumre eller afdelingskoder. Det er altid god praksis at kontrollere, at der ikke er nogen duplikater, før du udfører din VLOOKUP.
• Opslagsværdien skulle findes i den første kolonne på listen i kundens projektmappe. Desværre var det ikke. det var placeret i 2.kolonne (kolonne B), så vi skar og indsatte kolonnen foran P/O-nr. kolonne. Dette er en vigtig regel, når du bruger VLOOKUP-funktionen. Hvis Opslagsværdien ikke er i den første kolonne, fungerer opslag ikke.

når vi havde besluttet vores Opslagsværdi og reorganiseret listen i kundens projektmappe, var vi klar til at oprette vores VLOOKUP.

Trin 2: Indsættelse af VLOOKUP-funktionen

‘V’ i VLOOKUP står for “lodret”. Et lodret opslag bruges til at søge efter specifikke data i den første kolonne i en datatabel. Når den har fundet den række, der indeholder de data, du leder efter, hopper den derefter over til en anden kolonne i den samme datatabel og returnerer oplysninger fra den.

vi besluttede at indsætte VLOOKUP-funktionen i Guy ‘ s projektmappe. På den måde kunne vi bruge funktionen til at henvise til fakturanummeret fra hans liste og kontrollere for det samme fakturanummer i den første kolonne på kundens liste. Hvis det fandt et match, ville det derefter returnere i cellen det beløb, som kunden havde mod fakturanummeret. Vi ville så ende med Guy ‘ s beløb og kundens beløb i samme regneark. Vi kunne så nemt sammenligne de to og finde ud af, hvilke transaktioner der ikke matchede.

vi oprettede en ny kolonneoverskrift kaldet ‘Customer’ s Record ‘og placerede den i slutningen af Guy’ s eksisterende liste. Placering af cellevælgeren på celle E5 vi indsatte VLOOKUP-funktionen.

for at indsætte funktionen klikkede vi på fanen Formler og klikkede derefter på knappen Indsæt funktion fra gruppen funktionsbibliotek.

Tip: VLOOKUP-funktionen kan også indsættes ved at klikke på knappen Indsæt funktion på formellinjen.

fra feltet Vælg en kategori valgte vi opslag og Reference. Fra listen Vælg en funktion valgte vi VLOOKUP og klikkede derefter på OK.

på dette tidspunkt var vi nødt til at indtaste de relevante værdier i felterne med Funktionsargumenter. Jeg vil gennemgå hver af argumenterne.

• Lookup_value – dette var fakturanummeret i celle C5. Når vi indtastede dette i feltet Opslagsværdi, holdt vi det i hukommelsen og ventede på, at vi afsluttede de resterende argumenter.

• Table_array-tabelarrayet er den liste, der indeholder transaktionsoplysningerne på kundens regneark. VLOOKUP vil se efter et match for’ Opslagsværdien ‘ i den første kolonne i tabelarrayet. Guy navigerede til kundens regneark og klikkede derefter på og trak over hele området, der indeholdt alle transaktionsoplysningerne. Han inkluderede kolonnen ‘Dato’, men A4:C26 ville også have været fint, da kolonnen ‘Beløb’ er inden for dette interval. Bemærk, at Guy gjorde området absolut, så formlen let kunne kopieres.

• Col_indeks_num – dette er den kolonne, hvorfra Guy ønskede at trække gennem kundens fakturabeløb. Dette var kolonne 3 i kundens transaktionsliste. Kolonner nummereres fra venstre mod højre, hvor den første kolonne i tabelarrayområdet er kolonne 1. Pas på ikke at få kolonnenumre og kolonnebogstaver blandet sammen. Selvom fakturabeløbet holdes i kolonne’ C’, ønsker kun indeksnummerplaceringen af kolonnen, ikke kolonnebogstavet.

• Range_lookup-området opslag styrer, om du søger efter en nøjagtig match til din ‘opslag værdi’. Jeg forklarede Guy, at hvis han indtastede TRUE eller forlod boksen Tom, ville han søge efter hans fakturanummer, men hvis det ikke kunne finde et match, ville det returnere den næststørste værdi, der er mindre end fakturanummeret. For eksempel, hvis den ikke kunne finde faktura 2466, ville den finde posten for 2465 og returnere beløbet for det i stedet. For at dette kunne fungere, skulle kundens liste først sorteres efter fakturanummer. Men hvis’ falsk ‘ er indtastet i feltet vil der blive søgt efter et nøjagtigt match for Opslagsværdien. Guy ville have et nøjagtigt match for sit fakturanummer, så han indtastede falsk i kassen. Jeg gik også på spidsen, at du kan indtaste 0 (nul) i stedet for ordet ‘falsk’ og udmærke sig vil gøre det samme.

med alle de indtastede argumenter fyr klikkede OK.

gå derefter gennem hver af de værdier, der er angivet i argumentfeltet. Funktionen brugte fakturanummeret (Opslagsværdi) til at søge efter et matchende fakturanummer i kolonnen længst til venstre i kundens poster (Tabelarray). Når fakturanummeret blev fundet, sprang det derefter over det angivne antal kolonner (kolonneindeksnummer) på samme række for at returnere fakturabeløbet.

heldigvis, som du kan se nedenfor, havde kunden den samme fakturaværdi som Guy.

Guy kopierede derefter VLOOKUP-funktionen til alle fakturatransaktioner.
inden for få sekunder kunne han se tre #N/A-fejl (værdi ikke tilgængelige fejl). Dette viste, hvor kunden faktisk ikke havde en registrering af disse fakturaer. Derefter fandt han hurtigt forskellene i hans og kundens fakturabeløb. Umiddelbart kunne han se, at faktura 2464 var kort $100.00, sandsynligvis en nøglefejl.

Trin 3: dobbelttjekket

vi havde nu med succes sammenlignet vores poster med kundens. som dobbeltkontrol besluttede vi dog derefter at foretage en sammenligning fra kundens poster, bare hvis vi havde gået glip af noget.

denne gang oprettede vi VLOOKUP i kundens regneark. Den første ting, vi skulle gøre, var at flytte fakturakolonnen, så den var den første kolonne i vores transaktionsliste (Tabelarray). Efter at have gjort dette indsatte vi derefter VLOOKUP-funktionen i en ny kolonne kaldet ‘vores rekord’.

Guy kopierede funktionen ned og oprettede derefter kolonnen ‘sammenligning’. Vores dobbeltkontrol bekræftede, at kunden havde under betalt us $100 på faktura 2464. Vores check viste også, at kunden havde meget muligt registreret fakturanummeret for faktura 2466 som 2646.

konklusion

Guy kunne nu gå tilbage til kunden for at diskutere de oplysninger, der muligvis er indtastet ved en fejl, og for at levere kopier af de fakturaer, som kunden ikke havde en registrering af modtagelse.

det er overflødigt at sige, at VLOOKUP-funktionen reddede Guy og hans arbejdskammerater en enorm mængde tid. Uddannelses-og udviklingschefen kontaktede mig efter træningen for at sige, at den forbedrede effektivitet i afdelingen havde sparet deres firma 10s af 1000s dollars.

vi dækker VLOOKUP-funktionen i Vores Stage 3-kurser. Hvis du har at gøre med store data, er denne træning bestemt noget for dig.