Excel – Bruk FINN. RAD for å sammenligne to lister

Trinn 1: Det er noen regler, så vi sjekket disse først

kunden hadde sendt et veldig grunnleggende Excel-regneark som viste en liste over betalingspostene etter innkjøpsordre, fakturanummer, beløp og dato.

administratoren (la Oss kalle Ham Guy) genererte deretter En Excel-rapport ut av selskapets regnskapssystem. Vi fortsatte deretter å bruke Begge Excel-regnearkene til å gjøre vår forsoning.

men før vi kunne begynne å bruke FINN. RAD-funksjonen, forklarte Jeg vanskelighetene med å få dataene dine til et format som vil sikre AT FINN.RAD-funksjonen fungerer.

Excel trengte følgende:

• innenfor kundens arbeidsbok og Fyrens arbeidsbok måtte det være minst ett samsvarende stykke informasjon for hver post. Begge arbeidsbøkene hadde for eksempel fakturanummeret og bestillingsnummeret for hver transaksjonspost. Vi bestemte oss for å bruke fakturanummeret I Guy arbeidsbok for å finne en kamp for samme fakturanummer i kundens arbeidsbok. Denne informasjonen kalles Oppslagsverdien.
• Oppslagsverdien må være unik, det vil si at Den ikke kan gjentas i kundens liste flere ganger. Heldigvis For Guy var det bare en post for hver faktura. I business faktura og PO tall er nesten alltid unik for en enkelt transaksjon, slik at de er gode å bruke som Oppslagsverdier. Andre eksempler er produktkoder, kunde-eller kontonummer, ansattnumre eller avdelingskoder. Det er alltid lurt å sjekke at det ikke er noen duplikater før DU utfører FINN. RAD.
• Oppslagsverdien måtte være plassert i den første kolonnen i listen i kundens arbeidsbok. Dessverre var det ikke. det var plassert i 2. kolonne (kolonne B) så vi kuttet og limte kolonnen foran P / O Nr. kolonne. Dette er en viktig regel når DU bruker VLOOKUP-funksjonen. Hvis Oppslagsverdien ikke er i den første kolonnen, fungerer ikke oppslaget.

Når vi hadde bestemt Oss for Vår Oppslagsverdi og omorganisert listen i kundens arbeidsbok, var vi klare til å lage VÅR FINN.RAD.

Trinn 2: Sette INN VLOOKUP-funksjonen

‘ V ‘i VLOOKUP står for «vertikal». Et loddrett oppslag brukes til å søke etter bestemte data i den første kolonnen i en datatabell. Når den finner raden som inneholder dataene du leter etter, hopper den over til en annen kolonne i samme datatabell og returnerer informasjon fra den.

vi bestemte oss for å sette INN VLOOKUP-funksjonen I Guy ‘ s arbeidsbok. På den måten kunne vi bruke funksjonen til å referere fakturanummeret fra sin liste og se etter samme fakturanummer i den første kolonnen i kundens liste. Hvis den fant en kamp, ville den da returnere til cellen beløpet kunden hadde mot fakturanummeret. Vi vil da ende Opp Med Guy ‘ s beløp og kundens beløp i samme regneark. Vi kunne da enkelt sammenligne de to og finne ut hvilke transaksjoner som ikke stemte overens.

vi opprettet en ny kolonneoverskrift kalt ‘Customer’ S Record’, og plasserte den på Slutten Av Guys eksisterende liste. Ved å plassere cellevelgeren På celle E5 satte VI INN VLOOKUP-funksjonen.

for å sette inn funksjonen klikket Vi På Formler-fanen og deretter fra Funksjonsbibliotekgruppen klikket På Sett Inn Funksjonsknappen.

Tips: FINN. RAD-funksjonen kan også settes inn ved å klikke På Sett Inn Funksjonsknappen På Formellinjen.

fra velg en kategori-boksen valgte Vi Oppslag og Referanse. Fra Velg en funksjon listeboks valgte vi VLOOKUP og klikket DERETTER OK.

På dette punktet måtte vi legge inn de riktige verdiene i Funksjonsargumentene. Jeg vil gå deg gjennom hver av argumentene.

• Lookup_value – dette var fakturanummeret i celle C5. Når vi skrev inn dette i Oppslagsverdi-boksen, Holdt Excel det i minnet og ventet på at vi skulle fullføre de resterende argumentene.

• Table_array-tabellen array er listen som inneholder transaksjonsdetaljene på kundens regneark. FINN. RAD vil se etter et treff for Oppslagsverdien i den første kolonnen i tabellen. Guy navigert til kundens regneark og deretter klikket og dratt over hele området som holdt alle transaksjoner detaljer. Han inkluderte ‘Date’ kolonne, Men A4:C26 ville ha vært fint også Som ‘Beløp’ kolonnen er innenfor dette området. Vær oppmerksom På At Guy gjorde området absolutt slik at formelen lett kunne kopieres.

• Col_index_num – dette er kolonnen Som Fyren ønsket å trekke gjennom kundens fakturabeløp. Dette var kolonne 3 i kundens transaksjonsliste. Kolonner er nummerert fra venstre til høyre med den første kolonnen i tabellarrayområdet som kolonne 1. Vær forsiktig så du ikke får kolonnenummer og kolonnebokstaver blandet opp. Selv om fakturabeløpet holdes i kolonne C, Vil Excel bare ha indeksnummerplasseringen til kolonnen, ikke kolonnebokstaven.

• Range_lookup-Området Oppslag styrer om du søker etter en eksakt match til Din ‘Oppslag Verdi’. Jeg forklarte Guy at Hvis Han skrev INN SANT eller forlot boksen, ville Excel søke etter fakturanummeret, men hvis Det ikke kunne finne en kamp, ville det returnere den neste største verdien som er mindre enn fakturanummeret. Hvis den for eksempel ikke finner faktura 2466, vil den finne posten for 2465 og returnere beløpet for det i stedet. For at dette skulle fungere, måtte kundens liste sorteres etter fakturanummer først. MEN hvis’ FALSE ‘ er angitt i boksen Excel vil søke etter et eksakt samsvar For Oppslagsverdien. Guy ønsket en nøyaktig match for hans fakturanummer så han skrev FALSK inn i boksen. Jeg passerte også på tipset at du kan skrive inn 0 (null) i stedet for ordet ‘FALSE’ og Excel vil gjøre det samme.

Med Alle argumentene angitt Fyr klikket OK.

Excel gikk deretter gjennom hver av verdiene som ble levert i argumentboksen. Funksjonen brukte fakturanummeret (Oppslagsverdi) til å søke etter et samsvarende fakturanummer i kolonnen lengst til venstre i kundens poster (Tabellmatrise). Når fakturanummeret ble funnet, hoppet det over det angitte antall kolonner (kolonneindeksnummer) på samme rad for å returnere fakturabeløpet.

Heldigvis, Som du kan se nedenfor, hadde kunden samme fakturaverdi som Guy.

Guy kopierte DERETTER FINN. RAD-funksjonen for alle fakturatransaksjonene.
innen sekunder kunne han se tre # N / A feil(verdi ikke tilgjengelig feil). Dette viste hvor kunden ikke faktisk har en oversikt over disse fakturaene. Han fant raskt forskjellene i hans og kundens fakturabeløp. Umiddelbart kunne han se at faktura 2464 var kort $100.00, sannsynligvis en nøkkelfeil.

Trinn 3: dobbeltsjekken

vi hadde nå sammenlignet våre poster med kundens. men som en dobbeltsjekk bestemte vi oss For å gjøre en sammenligning fra kundens poster, bare hvis vi hadde savnet noe.

Denne gangen opprettet VI FINN. RAD i kundens regneark. Det første vi trengte å gjøre var å flytte fakturakolonnen slik at den var den første kolonnen i vår transaksjonsliste (Tabellarray). Etter å ha gjort dette satte vi INN VLOOKUP-funksjonen i en ny kolonne kalt’Vår post’.

Guy kopierte funksjonen ned og opprettet Deretter Kolonnen ‘Sammenligning’. Vår dobbeltsjekk bekreftet at kunden hadde under betalt us $100 på faktura 2464. Vår sjekk viste også at kunden hadde fullt mulig registrert fakturanummeret for faktura 2466 som 2646.

Konklusjon

Guy kunne nå gå tilbake til kunden for å diskutere informasjonen som kan ha blitt tastet inn feil og å levere kopier av fakturaene som kunden ikke hadde en rekord for mottak.

DET er Unødvendig å si at VLOOKUP-funksjonen reddet Guy, Og hans arbeidskammerater, mye tid. Opplærings-Og utviklingslederen kontaktet meg etter treningen for å si at den forbedrede effektiviteten i avdelingen hadde spart selskapet 10s av 1000s av dollar.

vi dekker FINN. RAD-funksjonen i Våre Excel Stage 3-kurs. Hvis du arbeider med store data denne opplæringen er definitivt for deg.