Excel-käyttämällä VLOOKUP vertailla kahta listaa

Vaihe 1: on olemassa muutamia sääntöjä, joten tarkistimme nämä ensin

asiakas oli lähettänyt hyvin perus Excel-laskentataulukon, joka osoitti luettelon maksutiedot ostotilauksen, laskun numero, määrä ja päivämäärä.

ylläpitäjä (kutsutaan häntä nimellä Guy) loi sitten Excel-raportin yrityksensä tilijärjestelmästä. Sitten ryhdyimme käyttämään molempia Excel-laskentataulukoita tehdäksemme sovinnon.

kuitenkin, ennen kuin saatoimme aloittaa vlookup-funktion käytön, selitin, miten monimutkaista on saada tietosi muotoon, joka varmistaa vlookupin toiminnan.

Excel tarvitsi seuraavia:

• asiakkaan työkirjan ja kaverin työkirjan sisällä piti olla vähintään yksi täsmäävä tieto jokaista tietuetta varten. Esimerkiksi molemmissa työkirjoissa oli kunkin tapahtumatiedotteen laskunumero ja ostotilausnumero. Päätimme käyttää Laskunumeroa Guyn työkirjassa löytääksemme vastaavuuden samalle laskunumerolle asiakkaan työkirjasta. Tätä tietoa kutsutaan ”Hakuarvoksi”.
• Hakuarvon on oltava yksilöllinen eli sitä ei voi toistaa asiakkaan luettelossa useita kertoja. Guyn onneksi jokaisesta laskusta oli vain yksi ennätys. Business invoice-ja PO-numerot ovat lähes aina yksittäisiä liiketoimia, joten niitä on erinomainen käyttää Lookup-arvoina. Muita esimerkkejä ovat tuotekoodit, asiakas-tai tilinumerot, työntekijöiden numerot tai osastojen koodit. Se on aina hyvä käytäntö tarkistaa ei ole kaksoiskappaleita ennen suorittamista VLOOKUP.
• Hakuarvon piti sijaita asiakkaan työkirjan luettelon ensimmäisessä sarakkeessa. Valitettavasti se ei ollut. se sijaitsi 2.sarakkeessa (sarake B) joten me leikata ja liittää sarakkeen edessä P/o No. sarake. Tämä on tärkeä sääntö VLOOKUP-funktiota käytettäessä. Jos Hakuarvo ei ole ensimmäisessä sarakkeessa, haku ei toimi.

kun olimme päättäneet Lookup-arvomme ja järjestäneet Listan uudelleen asiakkaan työkirjassa, olimme valmiita luomaan VLOOKUPIMME.

Vaihe 2: Vlookup-Funktion lisääminen

”V”vlookupissa tulee sanoista ”vertical”. Vertikaalista hakua käytetään tiettyjen tietojen etsimiseen datataulukon ensimmäisestä sarakkeesta. Kun se löytää rivin, joka pitää tiedot etsit sitä sitten pomppii toiseen sarakkeeseen samassa taulukossa tietoja ja palauttaa tietoja siitä.

päätimme lisätä Vlookup-funktion Guyn työkirjaan. Näin voisimme käyttää toimintoa viitataksemme laskun numeron hänen listastaan ja tarkistaaksemme saman laskun numeron asiakkaan listan ensimmäisestä sarakkeesta. Jos se löytäisi vastaavuuden, se palauttaisi selliin summan, joka asiakkaalla oli laskun numeroa vastaan. Päädyimme sitten Guyn ja asiakkaan summaan samaan laskentataulukkoon. Voisimme sitten helposti vertailla näitä kahta ja selvittää, mitkä tapahtumat eivät täsmänneet.

loimme uuden sarakeotsikon nimeltä ”asiakkaan ennätys”, sijoittaen sen Guyn olemassa olevan listan loppuun. Asettamalla solun valitsimen soluun E5 lisäsimme VLOOKUP-toiminnon.

lisätäksemme funktion klikkasimme kaavat-välilehteä ja sitten Funktiokirjaston ryhmästä Klikkasimme Lisää funktio-painiketta.

Vihje: VLOOKUP-funktion voi lisätä myös klikkaamalla insertti-funktio-painiketta kaavapalkissa.

valitse A-luokka-ruudusta valitsimme haun ja viitteen. Valitse toiminto-luettelosta valitsimme VLOOKUPIN ja napsautimme sitten OK.

tässä vaiheessa piti syöttää funktion Argumenttilaatikoihin sopivat arvot. Kerron jokaisen väitteen.

• Lookup_value – tämä oli solussa C5 oleva laskun numero. Kun olemme syöttäneet tämän Lookup arvo ruutuun Excel piti sen muistissa ja odotti meitä loppuun jäljellä olevat argumentit.

• Table_array-taulukon array on luettelo, joka pitää tapahtuman tiedot asiakkaan laskentataulukon. VLOOKUP etsii ottelun ’Lookup arvo’ ensimmäisessä sarakkeessa taulukon array. Kaveri navigoi asiakkaan laskentataulukon ja sitten napsautti ja veti yli koko alueen, joka piti kaikki liiketoimet tiedot. Hän sisällytti ”päivämäärä” – sarakkeen, mutta A4:C26 olisi ollut hieno myös, koska ”määrä” – sarake on tämän alueen sisällä. Huomaa, että Guy teki alue absoluuttinen niin, että kaava voidaan helposti kopioida.

• Col_index_num – tämä on sarake, josta kaveri halusi vetää läpi asiakkaan laskun summa. Tämä oli sarake 3 asiakkaan liiketoimilistalla. Sarakkeet numeroidaan vasemmalta oikealle siten, että taulukon array-alueen ensimmäinen sarake on sarake 1. Varo, ettet sekoita palstanumeroita ja palstakirjaimia. Vaikka laskun suuruus on sarakkeessa ” C ” Excel haluaa vain indeksinumeron sijainnin sarakkeen, ei sarakekirjaimen.

• Range_lookup – the Range Lookup control onko etsit tarkka ottelu ’Lookup arvo’. Selitin Guy, että jos hän syöttää TRUE tai jätti ruutuun tyhjä Excel etsisi hänen laskun numero, mutta jos se ei löytänyt ottelu se palauttaisi seuraavaksi suurin arvo, joka on pienempi kuin laskun numero. Esimerkiksi jos se ei löytänyt lasku 2466 se löytäisi ennätys 2465 ja palauttaa summan, että sen sijaan. Jotta tämä toimisi, asiakkaan lista piti lajitella ensin laskunumeron mukaan. Kuitenkin, jos’ FALSE ’ on syötetty ruutuun Excel etsii tarkka ottelu haun arvo. Mies halusi tarkan laskun numeron, joten hän laittoi väärän laatikkoon. Välitin myös vihjeen, että voit kirjoittaa 0 (nolla) sanan ’FALSE’ tilalle ja Excel tekee saman asian.

kaikilla syötetyillä argumenteilla Guy klikkasi OK.

Excel kävi sitten läpi jokaisen argumenttikentän antaman arvon. Toiminto käytti laskunumeroa (Hakuarvo) etsiäkseen vastaavan laskunumeron asiakkaan tietueiden vasemmanpuoleisimmasta sarakkeesta (Taulukkorivistö). Kun laskun numero löytyi, se sitten hyppäsi määritetyn sarakkeiden määrän yli (sarakkeen Indeksinumero) samalla rivillä palauttaakseen laskun summan.

onneksi, kuten alta näkyy, asiakkaalla oli sama laskuarvo kuin Guylla.

vlookup-formula_2011>
Guy kopioi tämän jälkeen vlookup-funktion kaikkiin laskutapahtumiin.
muutamassa sekunnissa hän saattoi nähdä kolme #N / A-virhettä (arvo ei käytettävissä olevia virheitä). Tämä osoitti, mistä asiakkaalla ei varsinaisesti ollut kirjausta näistä laskuista. Sen jälkeen hän löysi nopeasti erot omasta ja asiakkaan laskusummista. Heti hän näki, että lasku 2464 oli vajaat 100,00 dollaria, todennäköisesti näppäilyvirhe.

Vaihe 3: tuplatarkistus

olimme nyt onnistuneesti verranneet tietojamme asiakkaan tietoihin, mutta tuplatarkistuksena päätimme sitten tehdä vertailun asiakkaan tiedoista siltä varalta, että jotain olisi jäänyt huomaamatta.

tällä kertaa loimme vlookupin asiakkaan laskentataulukkoon. Ensimmäinen asia, mitä meidän piti tehdä, oli siirtää laskun sarake niin, että se oli ensimmäinen sarake meidän tapahtumaluettelo (taulukko Array). Tämän jälkeen laitoimme vlookup-funktion uuteen sarakkeeseen nimeltä ’meidän tietomme’.

vlookup-formula_1293>

Guy kopioi funktion alas ja loi sitten ”Vertailu” – sarakkeen. Tuplashekkimme varmisti, että asiakas oli alle maksanut 100 dollaria laskussa 2464. Tarkastuksessamme kävi myös ilmi, että asiakas oli aivan mahdollisesti kirjannut laskun numeron 2466 numeroksi 2646.

johtopäätös

kaveri saattoi nyt palata asiakkaan luo keskustelemaan mahdollisesti vahingossa näppäilemistään tiedoista ja toimittamaan kopiot laskuista, joita asiakkaalla ei ollut merkintää vastaanottamisesta.

Sanomattakin on selvää, että Vlookup-toiminto pelasti Guylle ja hänen työkavereilleen valtavasti aikaa. Koulutus-ja kehityspäällikkö otti minuun yhteyttä koulutuksen jälkeen ja sanoi, että tehokkuuden parantaminen osastolla oli säästänyt heidän yrityksensä 10s 1000s dollaria.

katamme Vlookup-toiminnon Excel-vaiheen 3 kursseilla. Jos olet tekemisissä suuria tietoja tämä koulutus on ehdottomasti sinua varten.