Mnoho ľudí sa pokúša použiť program Excel ako databázu. Aj keď to môže fungovať ako databáza, niektoré úlohy, ktoré by boli v databázovom programe veľmi jednoduché, sú v Exceli pomerne zložité. Jednou z týchto úloh je priradenie dvoch zoznamov na základe spoločného poľa; to sa dá ľahko dosiahnuť pomocou programu Excel VLOOKUP. Funkciu VLOOKUP nájdete ako mimoriadne užitočnú, takže si nižšie pozrite príklad, kedy a ako túto funkciu používať.
Povedzme, že vaša cestovná kancelária vám pošle správu o konci mesiaca o všetkých miestach, ktoré vaši zamestnanci precestovali. V správe sa namiesto názvov miest používajú kódy letísk. Bolo by užitočné, keby ste mohli namiesto názvu kódu uviesť skutočné meno mesta.
Na internete nájdete a importujete zoznam zobrazujúci názov mesta pre každý kód letiska.
Ako však získate tieto informácie o každom zázname v správe?
- Použite funkciu VLOOKUP. VLOOKUP znamená „vertikálne vyhľadávanie“. Môže sa použiť kedykoľvek, keď máte zoznam údajov s poľom kľúča v stĺpci najviac vľavo.
- Začnite písať funkcie
=VLOOKUP(. Zadaním kombinácie klávesov Ctrl + A získate pomoc s funkciou. - VLOOKUP potrebuje štyri parametre. Prvý je kód mesta v pôvodnej správe. V tomto príklade by to bola bunka D4
- Ďalším parametrom je rozsah s vašou vyhľadávacou tabuľkou. Zvýraznite rozsah. Určite použite F4, aby bol rozsah absolútny. (Absolútna referencia má pred číslom stĺpca aj číslom riadku znak dolára. Po kopírovaní vzorca bude odkaz smerovať na I3: J351.
- Tretí parameter informuje program Excel, v ktorom stĺpci sa nachádza názov mesta. V rozsahu I3: J351 je názov mesta v stĺpci 2. Zadajte 2 pre tento parameter.
- Štvrtý parameter informuje Excel, či je zhoda typu „close“ v poriadku. V takom prípade to tak nie je, takže zadajte hodnotu False.
- Vzorec dokončite kliknutím na tlačidlo OK. Potiahnutím rukoväte výplne skopírujte vzorec nadol.
- Pretože ste starostlivo zadali absolútne vzorce, môžete skopírovať stĺpec E do stĺpca D a získať tak cieľové mesto. V takom prípade sú všetky odlety z medzinárodného letiska Pearson v Toronte.
Aj keď tento príklad vyšiel perfektne, keď diváci používajú VLOOKUP, zvyčajne to znamená, že porovnávajú zoznamy, ktoré pochádzajú z rôznych zdrojov. Ak zoznamy pochádzajú z rôznych zdrojov, môžu sa vždy vyskytnúť jemné rozdiely, vďaka ktorým sa zoznamy ťažko porovnávajú. Tu sú tri príklady toho, čo sa môže pokaziť a ako ich napraviť.
-
Jeden zoznam má pomlčky a druhý nie. Pomocou
=SUBSTITUTE()funkcie odstráňte pomlčky. Pri prvom vyskúšaní VLOOKUP sa vám zobrazia chyby N / A.Ak chcete pomlčky odstrániť vzorcom, použite vzorec NÁHRADA. Použite 3 argumenty. Prvým argumentom je bunka obsahujúca hodnotu. Ďalším argumentom je text, ktorý by ste chceli zmeniť. Posledným argumentom je náhradný text. V takom prípade chcete zmeniť pomlčky na nič, takže vzorec je
=SUBSTITUTE(A4,"-","").Túto funkciu môžete zabaliť do VÝHĽADU a získať popis.
-
Tento je jemný, ale veľmi častý. Jeden zoznam má za záznamom koncové prázdne miesto. Na odstránenie prebytočných medzier použite = TRIM (). Keď na začiatku zadáte vzorec, zistíte, že všetky odpovede obsahujú chyby N / A. Určite viete, že hodnoty sú v zozname a že so vzorcom je všetko v poriadku.
Jedna štandardná vec, ktorú treba skontrolovať, je presun do bunky s vyhľadanou hodnotou. Stlačením klávesu F2 prepnete bunku do režimu úprav. Keď ste v režime úprav, môžete vidieť, že kurzor je umiestnený o jednu medzeru od posledného písmena. To naznačuje, že v zázname je zadný priestor.
Na vyriešenie problému použite funkciu TRIM.
=TRIM(D4)odstráni vedúce medzery, koncové medzery a nahradí všetky vnútorné dvojité medzery jednou medzerou. V takom prípade TRIM dokonale odstráni zadný priestor.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)je vzorec. -
V poznámkach k show som uviedol bonusový tip: ako nahradiť výsledok # N / A pre chýbajúce hodnoty prázdnym znakom. Ak vaša vyhľadávacia hodnota nie je vo vyhľadávacej tabuľke, funkcia VLOOKUP vráti chybu N / A.
Tento vzorec využíva
=ISNA()funkciu na zistenie, či je výsledkom vzorca N / A chyba. Ak sa zobrazí chyba, druhý argument vo funkcii IF povie programu Excel, aby vložil ľubovoľný požadovaný text.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP vám umožňuje ušetriť čas pri porovnávaní zoznamov údajov. Urobte si čas a osvojte si základné použitie. V programe Excel budete môcť robiť oveľa výkonnejšie úlohy.
