VLOOKUP je jednou z najužitočnejších funkcií programu Excel a je tiež jedným z najmenej rozoznaných. V tomto článku demystifikujeme VLOOKUP ako skutočný príklad. Vytvoríme použiteľný Šablóna faktúry pre fiktívnu spoločnosť.
VLOOKUP je Excel funkcie, V tomto článku sa predpokladá, že čitateľ má predbežné pochopenie funkcií aplikácie Excel a môže používať základné funkcie ako SUM, AVERAGE a TODAY. V najbežnejšom použití je VLOOKUP a databázy funkcia, čo znamená, že pracuje s databázovými tabuľkami - alebo jednoducho, zoznamy vecí v pracovnom hárku programu Excel. Aké veci? No, akýkoľvek niečo také, ako toto. Môžete mať pracovný hárok, ktorý obsahuje zoznam zamestnancov, produktov, zákazníkov alebo CD v zbierke CD alebo hviezdy na nočnej oblohe. Nezáleží na tom.
Tu je príklad zoznamu alebo databázy. V tomto prípade je to zoznam produktov, ktoré naša fiktívna spoločnosť predáva:
Zvyčajne sa v zoznamoch uvádzajú nejaký jedinečný identifikátor pre každú položku v zozname. V tomto prípade sa jedinečný identifikátor nachádza v stĺpci "Kód položky". Poznámka: Ak má funkcia VLOOKUP pracovať s databázou / zoznamom, musí mať tento zoznam stĺpec obsahujúci jedinečný identifikátor (alebo "kľúč" alebo "ID") a tento stĺpec musí byť prvý stĺpec v tabuľke, Vzorová databáza uvedená vyššie spĺňa toto kritérium.
Najťažšou súčasťou používania VLOOKUP je presne pochopenie, prečo je to. Takže poďme sa pozrieť, či to môžeme najprv povedať takto:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
Vo vyššie uvedenom príklade by ste vložili funkciu VLOOKUP do inej tabuľky s kódom položky a vrátili by ste buď popis príslušnej položky, jej cenu alebo jej dostupnosť (jej množstvo v sklade), ako je to popísané v pôvodnom zoznam. Ktoré z týchto informácií vás odovzdá späť? No, toto sa rozhodnete pri vytváraní vzorca.
Ak všetko, čo potrebujete, je jedným z informácií z databázy, bolo by veľa problémov ísť na vytvorenie vzorca s funkciou VLOOKUP v nej. Typicky by ste používali tento typ funkcií v tabuľke s opakovane použiteľným použitím, napríklad v šablóne. Zakaždým, keď niekto zadá platný kód položky, systém získa všetky potrebné informácie o príslušnej položke.
Vytvorme si príklad toho: An Šablóna faktúry že môžeme opakovane používať v našej fiktívnej spoločnosti.
Najprv začneme Excel a vytvoríme si prázdnu faktúru:
Na účely zachovania tohto príkladu jednoducho vyhľadáme databázu produktov na samostatnom hárku v tom istom zošite:
Takže sme vytvorili našu databázu produktov, ktorá vyzerá takto:
Musíme nájsť zoznam všetkých dostupných funkcií, ktoré ponúka Excel, aby sme mohli vybrať VLOOKUP a získať nejakú pomoc pri vyplňovaní vzorca. Toto sa nachádza po prvom kliknutí na tlačidlo vzorca a potom kliknite na položku Vložiť funkciu:
Ak chcete nájsť ten, ktorý hľadáme, môžeme napísať hľadaný výraz ako "vyhľadávanie" (pretože funkcia, o ktorú nás zaujíma, je vyhľadať funkcie). Systém nám vráti zoznam všetkých funkcií súvisiacich s vyhľadávaním v programe Excel. VLOOKUP je druhá v zozname. Vyberte ho kliknutím OK.
Funkčné argumenty Zobrazí sa okno s výzvou pre všetky argumenty (alebo parametre) potrebných na dokončenie funkcie VLOOKUP. Môžete si myslieť na toto pole ako funkciu, ktorá nás pýta na nasledujúce otázky:
- Aký jedinečný identifikátor hľadáte v databáze?
- Kde je databáza?
- Ktoré informácie z databázy spojené s jedinečným identifikátorom chcete získať pre vás?
Zobrazia sa prvé tri argumenty zvýraznene, čo naznačuje, že sú povinná (funkcia VLOOKUP je bez nich úplná a nevráti platnú hodnotu). Štvrtý argument nie je tučný, čo znamená, že je nepovinné:
Prvý argument, ktorý musíme dokončiť, je lookup_value argumentom. Funkcia nás potrebuje povedať, kde nájsť jedinečný identifikátor ( kód položky v tomto prípade), že by mal vrátiť opis. Musíme vybrať kód položky, ktorý sme zadali skôr (v A11).
Kliknite na ikonu výberu napravo od prvého argumentu:
Potom kliknite raz na bunku obsahujúcu kód položky (A11) a stlačte vstúpiť:
Teraz musíme zadať hodnotu pre table_array argumentom. Inými slovami, musíme VLOOKUP povedať, kde nájsť databázu / zoznam. Kliknite na ikonu výberu vedľa druhého argumentu:
… a stlačte tlačidlo vstúpiť, Rozsah buniek, ktoré reprezentujú databázu (v tomto prípade "Databáza produktov"! A2: D7 ") sa zadá automaticky pre nás do druhého argumentu.
Teraz musíme vložiť tretí argument, Col_index_num, Tento argument používame na to, aby sme špecifikovali, do VLOOKUP, akú informáciu z databázy spájame s kódom položky v A11, chceli by sme sa nám vrátili. V tomto konkrétnom príklade by sme chceli mať položky popis vrátil sa nám. Ak sa pozriete na pracovný hárok databázy, všimnete si, že stĺpec "Popis" je druhý v databáze. To znamená, že musíme zadať hodnotu "2" do Col_index_num box:
Je dôležité poznamenať, že tu nezadávame "2", pretože stĺpec "Popis" je v tabuľke B v tomto pracovnom hárku. Ak sa databáza začala v stĺpci K pracovného hárku by sme ešte stále zadali hodnotu "2" v tomto poli, pretože stĺpec "Popis" je druhý stĺpec v množine buniek, ktoré sme vybrali pri zadávaní "Table_array".
Nakoniec sa musíme rozhodnúť, či zadáme hodnotu do záverečného argumentu VLOOKUP, argument typ, Tento argument vyžaduje buď a pravdivý alebo nepravdivý hodnota, alebo by mala byť prázdna. Pri použití VLOOKUPu s databázami (ako je to pravda 90% času), spôsob, ako sa rozhodnúť, čo vkladať do tohto argumentu, možno považovať za nasledovné:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Ako prvý stĺpec našej databázy je nie usporiadané, zadáme nepravdivý do tohto argumentu:
To je ono! Zadali sme všetky informácie požadované pre VLOOKUP na vrátenie hodnoty, ktorú potrebujeme. Kliknite na tlačidlo OK stlačte tlačidlo a všimnite si, že popis zodpovedajúci kódu položky "R99245" bol správne vložený do bunky B11:
Ak zadáme a rozdielny kód položky do bunky A11, začneme vidieť výkon funkcie VLOOKUP: Popis bunky sa zmení tak, aby zodpovedal novému kódu položky:
Môžeme vykonať podobnú sadu krokov na získanie položky cena vrátil sa do bunky E11. Všimnite si, že nový vzorec musí byť vytvorený v bunke E11. Výsledok bude vyzerať takto:
Upozorňujeme, že jediný rozdiel medzi týmito dvomi formami je tretí argument (Col_index_num) sa zmenil z "2" na "3" (pretože chceme získať údaje z tretieho stĺpca v databáze).
Ak sme sa rozhodli kúpiť 2 z týchto položiek, zadáme do bunky D11 "2". Potom by sme do bunky F11 zadali jednoduchý vzorec, aby sme získali celkový počet riadkov:
=D11*E11
… ktorý vyzerá takto …
Dokončenie šablóny faktúry
Doteraz sme sa naučili veľa o VLOOKUP. V skutočnosti sme sa naučili všetko, čo sa naučíme v tomto článku. Je dôležité poznamenať, že VLOOKUP môže byť použitý aj za iných okolností okrem databáz. To je menej obvyklé a môže byť pokryté v budúcich článkoch How-To Geek.
Šablóna faktúry zatiaľ nie je dokončená. Aby sme to dokončili, urobili by sme:
-
Odstránili sme vzorový kód položky z bunky A11 a "2" z bunky D11. To spôsobí, že naše novovytvorené vzorce VLOOKUP zobrazia chybové hlásenia:
Image Môžeme to napraviť rozumným používaním programu Excel IF () a ISBLANK () funkcie. Zmeňme vzorec z tohto … = VLOOKUP (A11, "Databáza produktov"! A2: D7,2, FALSE) …do tohto… = IF (ISBLANK (A11), "", VLOOKUP (A11, "Databáza produktov"! A2: D7,2, FALSE))
- Kódy B11, E11 a F11 by sme skopírovali do zostávajúcich riadkov položky faktúry. Všimnite si, že ak to urobíme, výsledné vzorce už nespĺňajú správne tabuľku databáz. Mohli by sme to vyriešiť zmenou referencie buniek pre databázu absolútny odkazy na bunky. Alternatívne - a ešte lepšie - by sme mohli vytvoriť názov rozsahu pre celú databázu produktov (napríklad "Produkty") a použite tento názov rozsahu namiesto odkazov na bunky. Vzorec by sa zmenil z tohto … = IF (ISBLANK (A11), "", VLOOKUP (A11, "Databáza produktov"! A2: D7,2, FALSE)) …do tohto… = IF (ISBLANK (A11), "", VLOOKUP (A11, Produkty, 2, FALSE)) … a potom skopírujte vzorce až do ostatných riadkov položiek faktúry.
- Pravdepodobne by sme zamkli bunky, ktoré obsahujú naše vzorce (alebo skôr odomknúť ostatné bunky) a potom chrániť pracovný hárok, aby sme sa uistili, že naše starostlivo zostavené vzorce nie sú náhodne prepísané, keď niekto príde k vyplneniu faktúry.
- Uložili by sme súbor ako šablóna, aby ho mohli znovu použiť všetci v našej spoločnosti
Ak by sme sa cítili naozaj chytré by sme vytvorili databázu všetkých našich zákazníkov v inom pracovnom hárku a potom použite ID zákazníka zadaného v bunke F5, aby automaticky vyplnili meno a adresu zákazníka v bunkách B6, B7 a B8.
