Excel-a VLOOKUP összehasonlítani két listát

1. lépés: van néhány szabály, így ellenőriztük ezeket az első

az ügyfél küldött egy nagyon alapvető Excel munkalapot, amely megmutatta a Fizetési rekordok listáját megrendelés, számlaszám, összeg és dátum szerint.

az adminisztrátor (nevezzük Guy-nak) ezután létrehozott egy Excel jelentést a cég számlarendszeréből. Ezután mindkét Excel munkalapot használtuk a megbékéléshez.

mielőtt azonban elkezdhettük volna használni a VLOOKUP funkciót, elmagyaráztam az adatok olyan formátumba történő beillesztésének bonyodalmait, amely biztosítja a VLOOKUP működését.

Excel szükséges a következő:

• az ügyfél munkafüzetében és a fickó munkafüzetében minden rekordhoz legalább egy megfelelő információnak kellett lennie. Például mindkét munkafüzet tartalmazta a számlaszámot és a megrendelés számát az egyes tranzakciós rekordokhoz. Úgy döntöttünk, hogy a Guy munkafüzetében található számlaszámot használjuk, hogy megtaláljuk az azonos számlaszámot az ügyfél munkafüzetében. Ezt az információt Keresési értéknek nevezzük.
• a keresési értéknek egyedinek kell lennie, azaz nem ismételhető meg többször az ügyfél listájában. Guy szerencséjére minden számlához csak egy rekord volt. Az üzleti számla-és PO-számok szinte mindig egyediek egyetlen tranzakcióhoz, így kiválóan használhatók Keresési értékként. További példák a termékkódok, ügyfél-vagy számlaszámok, alkalmazotti számok vagy osztálykódok. Ez mindig jó gyakorlat, hogy ellenőrizze nincsenek ismétlődések elvégzése előtt a VLOOKUP.
• a keresési értéket az ügyfél munkafüzetének listájának első oszlopában kellett elhelyezni. Sajnos nem volt. a 2. oszlopban (B oszlop) található, így kivágtuk és beillesztettük az oszlopot a P/O No elé. oszlop. Ez fontos szabály a VLOOKUP funkció használatakor. Ha a keresési érték nem szerepel az első oszlopban, a keresés nem fog működni.

miután eldöntöttük a keresési értéket, és átszerveztük a listát az ügyfél munkafüzetében, készen álltunk a VLOOKUP létrehozására.

2.lépés: a VLOOKUP funkció beszúrása

A V A VLOOKUPBAN a “függőleges”kifejezést jelenti. A függőleges keresést arra használják, hogy konkrét adatokat keressenek az adattábla első oszlopában. Miután megtalálta azt a sort, amely az Ön által keresett adatokat tartalmazza, átugrik egy másik oszlopra ugyanabban az adattáblázatban, és információt ad vissza belőle.

úgy döntöttünk, hogy beillesztjük a VLOOKUP funkciót Guy munkafüzetébe. Így a függvény segítségével hivatkozhatunk a számlaszám listájára, és ellenőrizhetjük ugyanazt a számlaszámot az ügyfél listájának első oszlopában. Ha talál egyezést, akkor visszatér a cellába azt az összeget, amelyet az ügyfél a számlaszámmal szemben kapott. Ezután a srác összege és az ügyfél összege ugyanabban a munkalapban lesz. Ezután könnyedén összehasonlíthatjuk a kettőt, és kideríthetjük, hogy mely tranzakciók nem egyeznek.

létrehoztunk egy új oszlopfejlécet ‘Ügyfélrekord’ néven, a Guy meglévő listájának végére helyezve. A cellaválasztó elhelyezése az E5 cellában beillesztettük a VLOOKUP funkciót.

a függvény beszúrásához a képletek fülre kattintottunk, majd a függvénykönyvtár csoportból a funkció beszúrása gombra kattintottunk.

tipp: a VLOOKUP funkció beilleszthető a Képletsávon található Beszúrás funkció gombra kattintva is.

a kategória kiválasztása mezőben a Keresés és Hivatkozás lehetőséget választottuk. A funkció kiválasztása listából kiválasztottuk a VLOOKUP lehetőséget, majd az OK gombra kattintottunk.

ezen a ponton meg kellett adnunk a megfelelő értékeket a függvény argumentumok mezőkbe. Végigvezetlek minden érven.

• Lookup_value – ez volt a C5 cellában tárolt számlaszám. Miután beírtuk ezt a keresési Érték mezőbe, az Excel megtartotta a memóriában, és megvárta, hogy befejezzük a fennmaradó argumentumokat.

• Table_array-a table tömb az a lista, amely a tranzakció részleteit tartalmazza az ügyfél munkalapján. A VLOOKUP a táblázat tömb első oszlopában keresi a ‘keresési érték’ egyezését. A fickó az ügyfél munkalapjára navigált, majd rákattintott és áthúzta az egész területet, amely az összes tranzakció részleteit tartalmazta. Tartalmazza a ‘dátum’ oszlopot, de az A4:C26 is rendben lett volna, mivel az ‘összeg’ oszlop ezen a tartományon belül van. Felhívjuk figyelmét, hogy Guy abszolútvá tette a tartományt, hogy a képlet könnyen másolható legyen.

• Col_index_num-ez az oszlop, ahonnan Guy át akarta húzni az ügyfél számlájának összegét. Ez az ügyfél tranzakciós listájának 3. oszlopa volt. Az oszlopok balról jobbra vannak számozva, a táblázat tömbtartományának első oszlopa az 1. oszlop. Legyen óvatos, ne kap oszlop számokat és oszlop betűket összekeverjük. Annak ellenére, hogy a számla összege a ‘C’ oszlopban van, az Excel csak az oszlop indexszámának helyét akarja, nem az oszlop betűjét.

• Range_lookup – a Tartománykeresés szabályozza, hogy a ‘keresési érték’ pontos egyezését keresi-e. Elmagyaráztam Guy-nak, hogy ha TRUE-t ad meg, vagy üresen hagyja a mezőt, Az Excel megkeresi a számlaszámát, de ha nem talál egyezést, akkor a következő legnagyobb értéket adja vissza, amely kisebb, mint a számlaszám. Például, ha nem találja a 2466-os számlát, akkor megtalálja a 2465-ös rekordot,és visszaadja az összeget. Ahhoz, hogy ez működjön, az ügyfél listáját először számlaszám szerint kellett rendezni. Ha azonban a ‘FALSE’ be van írva a mezőbe, az Excel pontos egyezést keres a keresési értékhez. A fickó pontos egyezést akart a számlaszámával, ezért hamisat írt be a dobozba. Azt is átadtam a tippnek, hogy a ‘hamis’ szó helyett 0 (nulla) értéket adhat meg, és az Excel ugyanezt fogja tenni.

az összes megadott argumentum srác kattintott OK.

az Excel ezután átlépte az argumentum mezőben megadott értékeket. A függvény a számlaszámot (keresési érték) használta, hogy megkeresse a megfelelő számlaszámot az ügyfél rekordjainak bal oldali oszlopában (Table array). Miután megtalálta a számlaszámot, átugrott a megadott számú oszlopon (oszlopindexszám) ugyanabban a sorban, hogy visszatérjen a számla összegéhez.

szerencsére, amint az alább látható, az ügyfélnek ugyanaz volt a számlaértéke, mint a Guy-nak.

Guy ezután átmásolta a VLOOKUP függvényt az összes számlázási tranzakcióhoz.
másodperceken belül három # N / a hibát látott (érték nem elérhető hibák). Ez azt mutatta, hogy az ügyfélnek valójában nem volt nyilvántartása ezekről a számlákról. Ezután gyorsan megtalálta a különbségeket az ő és az ügyfél számlaösszegében. Azonnal látta, hogy a számla 2464 rövid volt $100.00, valószínűleg egy kulcs hiba.

3. lépés: a double-check

most sikeresen összehasonlítottuk a nyilvántartásainkat az ügyfélével. azonban kettős ellenőrzésként úgy döntöttünk, hogy összehasonlítjuk az ügyfél nyilvántartásait, csak abban az esetben, ha valamit kihagytunk.

ezúttal létrehoztuk a VLOOKUP-ot az ügyfél munkalapján. Az első dolog, amit meg kellett tennünk, a számla oszlop áthelyezése volt, hogy ez legyen az első oszlop a tranzakciós listánkban (Table Array). Ezt követően beillesztettük a VLOOKUP funkciót egy új oszlopba, a ‘rekordunk’néven.

Guy lemásolta a függvényt, majd létrehozta az ‘összehasonlítás’ oszlopot. Kettős ellenőrzésünk megerősítette, hogy az ügyfél 100 USD-t fizetett a 2464-es számlán. Ellenőrzésünk azt is kimutatta, hogy az ügyfél a 2466-os számla számlaszámát 2646-ként rögzítette.

következtetés

Guy most visszamehetett az ügyfélhez, hogy megvitassa azokat az információkat, amelyeket esetleg hibásan írtak be, és hogy átadja a számlák másolatait, amelyeket az ügyfél nem kapott.

mondanom sem kell, hogy a VLOOKUP funkció rengeteg időt mentett meg Guy-tól és munkatársaitól. A képzési és fejlesztési menedzser a képzés után kapcsolatba lépett velem, hogy elmondja, hogy az osztály jobb hatékonysága 10 1000 dollárt takarított meg.

a VLOOKUP funkciót az Excel Stage 3 tanfolyamainkban fedjük le. Ha nagy adatokkal foglalkozik, ez a képzés határozottan az Ön számára.