Excel – Použití funkce SVYHLEDAT porovnat dva seznamy

Krok 1: Existuje několik pravidel, takže jsme zkontrolovali tyto první

zákazník poslal velmi základní aplikace Excel, který ukázal seznamu svých platebních záznamů do objednávky, číslo faktury, částku a datum.

Správce (říkejme mu Guy) poté vygeneroval zprávu Excel ze systému účtů své společnosti. Poté jsme přistoupili k použití obou listů aplikace Excel k provedení našeho usmíření.

než jsme však mohli začít používat funkci VLOOKUP, vysvětlil jsem složitosti získání vašich dat do formátu, který zajistí, že VLOOKUP funguje.

Excel potřeboval následující:

• v sešitu zákazníka a sešitu chlapa musí být pro každý záznam alespoň jedna odpovídající informace. Například oba sešity měly číslo faktury a číslo objednávky pro každý záznam transakce. Rozhodli jsme se použít číslo faktury v sešitu Guy k nalezení shody pro stejné číslo faktury v sešitu zákazníka. Tato informace se nazývá „vyhledávací hodnota“.
• vyhledávací hodnota musí být jedinečná, tj. nemůže být opakována v seznamu zákazníka vícekrát. Naštěstí pro Guye byl pro každou fakturu pouze jeden záznam. V obchodních fakturách a číslech PO jsou téměř vždy jedinečné pro jednu transakci, takže jsou vynikající pro použití jako hodnoty vyhledávání. Dalšími příklady jsou kódy produktů, čísla klientů nebo účtů, čísla zaměstnanců nebo kódy oddělení. Před provedením VLOOKUP je vždy dobré zkontrolovat, zda neexistují žádné duplikáty.
• vyhledávací hodnota musí být umístěna v prvním sloupci seznamu v sešitu zákazníka. Bohužel to nebyl. To bylo nachází ve 2. sloupci (sloupec B), takže jsme vyjmout a vložit sloupec před P/O Ne. sloupec. Toto je důležité pravidlo při použití funkce VLOOKUP. Pokud hodnota vyhledávání není v prvním sloupci vyhledávání nebude fungovat.

Jakmile jsme se rozhodli na naše Vyhledávání Hodnoty a reorganizována na seznamu v zákazníka sešit byli jsme připraveni vytvořit naše SVYHLEDAT.

Krok 2: vložení funkce VLOOKUP

‚V‘ ve VLOOKUP znamená „vertikální“. Vertikální vyhledávání se používá k vyhledání konkrétních dat v prvním sloupci datové tabulky. Jakmile najde řádek, který obsahuje data, která hledáte, pak se odrazí do jiného sloupce ve stejné tabulce dat a vrátí z něj informace.

rozhodli jsme se Vložit funkci VLOOKUP do sešitu Guy. Tímto způsobem bychom mohli pomocí funkce odkazovat na číslo faktury z jeho seznamu a zkontrolovat stejné číslo faktury v prvním sloupci seznamu zákazníka. Pokud by našel shodu, vrátil by do buňky částku, kterou měl zákazník proti číslu faktury. Pak bychom skončili s částkou Guy a částkou zákazníka ve stejném listu. Pak jsme mohli snadno porovnat dva a zjistit, které transakce neodpovídaly.

vytvořili jsme nový nadpis sloupce s názvem „záznam zákazníka“ a umístili jej na konec existujícího seznamu Guy. Umístěním voliče buněk na buňku E5 jsme vložili funkci VLOOKUP.

pro vložení funkce jsme klikli na kartu vzorce a poté ze skupiny Knihovna funkcí klikli na tlačítko Vložit funkci.

Tip: funkci VLOOKUP lze také vložit kliknutím na tlačítko Vložit funkci na panelu vzorců.

z pole Vybrat kategorii jsme vybrali vyhledávání a odkaz. Z pole Vybrat seznam funkcí jsme vybrali VLOOKUP a poté klikli na OK.

v tomto okamžiku jsme potřebovali zadat příslušné hodnoty do polí argumentů funkce. Provedu vás všemi argumenty.

• Lookup_value-toto bylo číslo faktury uložené v buňce C5. Jakmile jsme to zadali do vyhledávacího pole Hodnota Excel ji držel v paměti a čekal, až dokončíme zbývající argumenty.

• Tabulka – tabulka, pole je seznam, který drží podrobnosti transakce na zákazníka listu. VLOOKUP bude hledat shodu pro „vyhledávací hodnotu“ v prvním sloupci pole tabulky. Guy navigoval do listu zákazníka a poté klikl a přetáhl celou oblast, která obsahovala všechny podrobnosti o transakcích. Zahrnoval sloupec „Datum“, ale A4: C26 by bylo také v pořádku, protože sloupec „částka“ je v tomto rozsahu. Vezměte prosím na vědomí, že Guy udělal rozsah absolutní, takže vzorec lze snadno zkopírovat.

• Col_index_num-toto je sloupec, ze kterého chtěl člověk vytáhnout částku faktury zákazníka. To byl sloupec 3 v seznamu transakcí zákazníka. Sloupce jsou očíslovány zleva doprava, přičemž první sloupec v rozsahu pole tabulky je sloupec 1. Dávejte pozor, abyste si čísla sloupců a písmena sloupců smíšené. I když je částka faktury držena ve sloupci “ C “ Excel chce pouze umístění indexového čísla sloupce, ne písmeno sloupce.

• Range_lookup – Rozsah Vyhledávání určuje, zda hledáte přesnou shodu pro váš Vyhledávací Hodnota‘. Vysvětlil jsem Guyovi, že pokud zadá TRUE nebo ponechá pole prázdné, Excel vyhledá jeho číslo faktury, ale pokud nemůže najít shodu, vrátí další největší hodnotu, která je menší než číslo faktury. Například pokud by nemohl najít fakturu 2466, našel by záznam za 2465 a místo toho vrátil částku. Aby to fungovalo, musí být seznam zákazníka nejprve seřazen podle čísla faktury. Pokud je však do pole zadáno „FALSE“, Excel vyhledá přesnou shodu hodnoty vyhledávání. Chlap chtěl přesnou shodu s číslem faktury, takže do krabice vložil FALSE. Také jsem předal tip, že můžete zadat 0 (nula) místo slova „FALSE“ a Excel udělá totéž.

se všemi zadanými argumenty chlap klikl OK.

Excel pak prošel každou z hodnot dodaných v poli argumentů. Funkce použila číslo faktury (vyhledávací hodnota) k vyhledání odpovídajícího čísla faktury v levém sloupci záznamů zákazníka (pole tabulky). Jakmile bylo nalezeno číslo faktury, skočil přes zadaný počet sloupců (číslo indexu sloupce) na stejném řádku a vrátil částku faktury.

naštěstí, jak vidíte níže, měl zákazník stejnou hodnotu faktury jako Guy.

Guy poté zkopíroval funkci VLOOKUP pro všechny fakturační transakce.
během několika sekund viděl tři #N/A chyby (hodnota není k dispozici chyby). To ukázalo, kde zákazník vlastně neměl záznam o těchto fakturách. Poté rychle zjistil rozdíly v částkách faktury jeho a zákazníka. Okamžitě viděl, že faktura 2464 byla krátká $ 100.00, s největší pravděpodobností chyba klíče.

Krok 3: double-zkontrolujte,

měli Jsme nyní úspěšně ve srovnání záznamů zákazníka. Nicméně, jak to zkontrolovat a pak jsme se rozhodli udělat srovnání od zákazníka záznamy, jen v případě, že jsme něco přehlédli.

tentokrát jsme vytvořili VLOOKUP v listu zákazníka. První věc, kterou jsme museli udělat, bylo přesunout sloupec faktury tak, aby to byl první sloupec v našem seznamu transakcí (pole tabulky). Poté jsme vložili funkci VLOOKUP do nového sloupce s názvem „náš záznam“.

Guy zkopíroval funkci dolů a poté vytvořil sloupec „srovnání“. Naše dvojitá kontrola potvrdila, že zákazník zaplatil 100 USD na faktuře 2464. Naše kontrola také ukázala, že zákazník měl docela možné zaznamenal číslo faktury pro fakturu 2466 jako 2646.

Závěr

Chlap by se mohl nyní vrátit zákazníkovi k projednání informací, které mohou být zadané v omylu a dodávky kopií faktury, že zákazník neměl zaznamenat příjem.

Netřeba dodávat, že funkce VLOOKUP zachránila chlapa a jeho spolupracovníky obrovské množství času. Manažer školení a vývoje mě kontaktoval po školení, abych řekl, že zlepšená efektivita v oddělení zachránila jejich společnost 10s 1000s dolarů.

funkci VLOOKUP pokrýváme v našich kurzech Excel Stage 3. Pokud máte co do činění s velkými daty, toto školení je určitě pro vás.