VLOOKUP je jedna z nejužitečnějších funkcí aplikace Excel a je také jedním z nejméně pochopených. V tomto článku demystifikujeme VLOOKUP jako příklad z reálného života. Pro fiktivní společnost vytvoříme použitelnou šablonu faktury

VLOOKUP je funkceaplikace Excel. Tento článek předpokládá, že čtenář již má absolutní znalost funkcí aplikace Excel a může používat základní funkce jako SUM, AVERAGE a TODAY. Ve svém nejčastějším použití je VLOOKUP funkce databáze , což znamená, že pracuje s databázovými tabulkami - nebo jednodušeji seznamy věcí v listu aplikace Excel. Jaké věci? No, nějaká věc. Můžete mít pracovní list, který obsahuje seznam zaměstnanců nebo produktů nebo zákazníků nebo CD ve sbírce CD nebo hvězdy na noční obloze. Na tom nezáleží. Zde je příklad seznamu nebo databáze. V tomto případě je to seznam produktů, které naše fiktivní firma prodává:

Obvykle jsou jako seznamy uvedeny nějaký jedinečný identifikátor pro každou položku v seznamu. V tomto případě je jedinečný identifikátor ve sloupci "Kód položky". Poznámka: Pro funkci VLOOKUP pro práci s databází / seznamem musí tento seznam

mít sloupec obsahující jedinečný identifikátor (nebo "klíč" nebo ID) a první sloupec v tabulce . Naše vzorová databáze výše vyhovuje tomuto kritériu. Nejtěžší částí použití VLOOKUP je přesné pochopení, proč je. Takže se podívejme, jestli to nejdřív dostaneme:

VLOOKUP načte informace z databáze / seznamu založené na dodané instanci jedinečného identifikátoru.

V příkladu výše bychom vložili funkci VLOOKUP do jiné tabulky s kód položky a vrátí vám buď popis příslušné položky, její cenu nebo její dostupnost (její "skladem"), jak je popsáno v původním seznamu. Které z těchto informací vás povede zpět? No, rozhodnete se to při vytváření vzoru.

Pokud vše, co potřebujete, je pouze jedna informace z databáze, bylo by spousta potíží jít na to, abychom vytvořili vzorec s funkcí VLOOKUP to. Obvykle byste použili tento druh funkcí v opakovaně použitelné tabulce, například v šabloně. Pokaždé, když někdo zadá platný kód položky, systém načte všechny potřebné informace o odpovídající položce.

Ukažme si příklad:

Šablona faktury , kterou můžeme znovu a znovu fiktivní společnost Nejdříve spustíme program Excel a vytvoříme prázdnou fakturu:

Takhle to funguje: Osoba, která používá šablonu faktury, vyplní řadu kódů položek ve sloupci "A" a systém načte popis a cenu každé položky z naší databáze produktů. Tyto informace budou použity k výpočtu celkového počtu řádků pro každou položku (za předpokladu, že zadáme platné množství).

Pro účely zachování tohoto příkladu je databáze produktu umístěna na samostatném listu ve stejném sešitu:

Ve skutečnosti je pravděpodobnější, že databáze produktů bude umístěna v samostatném sešitu. Funkce VLOOKUP má jen malý rozdíl, což se opravdu nezajímá, jestli je databáze umístěna na stejném listu, jiném listu nebo úplně jiném sešitu.

Takže jsme vytvořili databázi produktů, která vypadá takto:

Chcete-li otestovat vzorec VLOOKUP, který se chystáme napsat, nejprve zadejte platný kód položky do buňky A11 naší prázdné faktury:

Dále přemístíme aktivní buňku do buňky, ve které chceme, aby byly informace získané z databáze VLOOKUP uloženy. Je zajímavé, že se jedná o krok, kterým se většina lidí pokazí. Další vysvětlení: Chystáme se vytvořit vzorec VLOOKUP, který načte popis, který odpovídá kódu položky v buňce A11. Kde chceme tento popis dát, když ho dostaneme? V buňce B11 samozřejmě. Takže tam píšeme vzorec VLOOKUP: v buňce B11. Vyberte buňku B11 nyní.

Musíme najít seznam všech dostupných funkcí, které nabízí Excel, abychom mohli vybrat VLOOKUP a získat pomoc při vyplňování vzorce. Toto je nalezeno klepnutím na kartu

Formule a klepnutím na Vložit funkci : Zobrazí se pole, které nám umožňuje vybrat libovolné funkce dostupné v aplikaci Excel. Chcete-li najít ten, který hledáme, mohli bychom zadat hledaný výraz jako "vyhledávání" (protože funkce, o kterou nás zajímá, je funkce

vyhledávání

). Systém nám vrátí seznam všech vyhledávacích funkcí v aplikaci Excel. VLOOKUP je druhý v seznamu. Vyberte OK . Zobrazí se okno Function Arguments

, které nás vyzývá k tomu, aby všechny parametrypro dokončení funkce VLOOKUP. Toto pole můžete považovat za funkci, která nás dotazuje na následující otázky: Jaký jedinečný identifikátor hledáte v databázi? Kde je databáze? Která informace z databáze související s Prvé tři argumenty jsou zobrazeny

1. tučně
2. , což znamená, že jsou
3. povinné

argumenty (funkce VLOOKUP je neúplná bez a nevrátí platnou hodnotu). Čtvrtý argument není tučný, což znamená, že je nepovinné: Dokončíme argumenty v pořadí, shora dolů. První argument, který musíme dokončit, je argument Lookup_value . Funkce nám říká, kde se má najít jedinečný identifikátor (kód

v tomto případě), který by měl vracet popis. (9)> Klikněte na ikonu voliče napravo od prvního argumentu: Potom jednou klikněte na buňku obsahující kód položky (A11) a stiskněte Enter : Hodnota "A11" je vložena do prvního argumentu.

Nyní musíme zadat hodnotu pro argument

Table_array . Jinými slovy, musíme VLOOKUP informovat, kde najít databázi / seznam. Klepněte na ikonu voliče vedle druhého argumentu: Nyní vyhledejte databázi / seznam a vyberte celý seznam -

bez řádku záhlaví

. V našem příkladu je databáze umístěna na samostatném listu, takže nejprve klikneme na tuto tabulku: Dále vybereme celou databázi bez řádku záhlaví: ... a stiskněte

Enter . Rozsah buněk, které reprezentují databázi (v tomto případě "Databáze produktů"! A2: D7), je zadán automaticky do druhého argumentu. Nyní musíme zadat třetí argument

Col_index_num

. Použijeme tento argument k tomu, abychom specifikovali, do VLOOKUP, jakou informaci z databáze, spojenou s kódem položky v A11, se nám chtějí vrátit. V tomto konkrétním příkladu bychom se chtěli vrátit k nám popis . Pokud se podíváte na listu databáze, všimnete si, že sloupec "Popis" je sloupec

druhý v databázi. To znamená, že do kolonky Col_index_num musíme zadat hodnotu "2": Je důležité si uvědomit, že zde nezadáváme "2", protože sloupec "Popis" sloupec B v tomto listu. Pokud dojde ke spuštění databáze ve sloupci K listu, stále bychom do tohoto pole zadali hodnotu "2", protože sloupec "Popis" je druhý sloupec v množině buněk, které jsme vybrali při zadání " Table_array ". Nakonec se musíme rozhodnout, zda zadáme hodnotu do konečného argumentu VLOOKUP

Range_lookup . Tento argument vyžaduje hodnotu true nebo false

nebo by měl zůstat prázdný. Při použití VLOOKUPu s databázemi (jak je to pravdivé 90% času), způsob, jak se rozhodnout, co umístit v tomto argumentu, lze považovat za následující: Pokud je první sloupec databáze (sloupec obsahující jedinečný identifikátory) je řazeno abecedně nebo číselně ve vzestupném pořadí, pak je možné zadat hodnotu Pokud je první sloupec databáze ne seřazen nebo je řazen sestupně, musíte zadat

hodnota false

do tohoto argumentu Protože první sloupec naší databáze je ne zařazen do tohoto argumentu false to! Zadali jsme všechny informace požadované pro VLOOKUP, abychom vrátili hodnotu, kterou potřebujeme. Klikněte na tlačítko OK

a všimněte si, že popis odpovídající kódu položky "R99245" byl správně zadán do buňky B11: Vzorec, který byl pro nás vytvořen, vypadá takto: jiný kód do buňky A11, začneme vidět výkon funkce VLOOKUP: Popisní buňka se změní tak, aby odpovídala novému kódu položky:

Můžeme provést podobnou sadu kroků k získání hodnota položkyse vrátila do buňky E11. Všimněte si, že nový vzorec musí být vytvořen v buňce E11. Výsledek bude vypadat takto:

... a vzorec bude vypadat takto:

Všimněte si, že jediný rozdíl mezi dvěma vzorci je třetí argument ( Col_index_num ) změněn z "2 "Na" 3 "(protože chceme získat data z 3. sloupce v databázi).

Pokud jsme se rozhodli koupit 2 z těchto položek, vložili bychom do pole" 2 "do buňky D11. Pak bychom zadali jednoduchý vzorec do buňky F11, abychom získali celkovou čarou: = D11 * E11 ... což vypadá takto ...

Dokončení šablony faktury

Naučili jsme se hodně o VLOOKUP zatím. Ve skutečnosti jsme se dozvěděli vše, co se v tomto článku dozvíme. Je důležité si uvědomit, že VLOOKUP lze použít i za jiných okolností kromě databází. To je méně obvyklé a může být pokryto v budoucích článcích How-To Geek. Naše šablona faktur není dosud dokončena. Pro jeho dokončení bychom učinili následující: Odstraňujeme vzorový kód položky z buňky A11 a "2" z buňky D11. To způsobí, že se nově vytvořené vzorce VLOOKUP zobrazí chybové zprávy:

Můžeme to odstranit uvážlivým použitím funkcí

IF ()

a

ISBLANK ()

v aplikaci Excel. Změníme náš vzorec z tohoto ...

= VLOOKUP (A11, 'Databáze produktů'! A2: D7,2, FALSE)

1. ... na toto ...
   

   

   
   = IF (IS11L) (A11, 'Databáze produktů'! A2: D7,2, FALSE)) Formuláře B11, E11 a F11 zkopírujeme dolů na zbytek řádků položek faktury. Všimněte si, že pokud to uděláme, budoucí vzorce nebudou správně odkazovat na databázovou tabulku. Mohli bychom to vyřešit změnou odkazů na buňky pro databázi na absolutní odkazy na buňky. Alternativně - a ještě lépe - bychom mohli vytvořit název rozsahu pro celou databázi produktů (například "Produkty") a namísto odkazů na buňky použít tento název rozsahu. Vzorec by se změnil z tohoto ... = IF (IS11LANK (A11), ", VLOOKUP (A11, "Databáze produktů" A2: D7,2, FALSE) IF (IS11), ", VLOOKUP (A11, Products, 2, FALSE)) ... a
2. pak zkopírujeme vzorce do ostatních řádků položek faktury. pravděpodobně "uzamkne" buňky, které obsahují naše vzorce (nebo spíše odemknoutjiné buňky) a pak chrání pracovní list, abychom zajistili, že naše pečlivě sestavená vzorce nejsou náhodou přepsáno, když někdo vyplní fakturu Uložili bychom soubor jako šablonu , takže by ho mohli znovu použít všichni v naší společnostichytré bychom vytvořili databázi všech našich zákazníků v jiném pracovním listu a poté použijeme ID zákazníka zadaného v buňce F5, abychom automaticky vyplnili jméno a adresu zákazníka v buňkách B6, B7 a B8. chtěli byste s VLOOKUPem procvičovat nebo jednoduše vidět naši výslednou fakturu T lze jej stáhnout zde.

---



Jak zastavit myš Bluetooth od neustále se odpojujícího

Bluetooth myši jsou pohodlně bezdrátové, ale je to velmi frustrující, když ztratí spojení. Přečtěte si, jak řešíme problém s čtečkovým notebookem Windows a pomáháme jim držet myši bdělí a komunikovat s jejich počítačem. Drahý How-To Geek, Jsem tak mrzutý myší, že se chystám přepnout zpět do modelu v plné velikosti.

(how-to)



Jak nakonfigurovat aplikaci Windows 10 Weather App

Přestože v aktualizaci Windows Windows 8 nebyla revoluční vylepšení v aplikacích Windows Weather, je to stále populární způsob, jak pro lidi rychle se zkontrolovat s povětrnostními podmínkami z pracovní plochy. Zde je návod, jak nakonfigurovat nastavení aplikace, spravovat seznam oblíbených a nastavit živou dlaždicku.

(how-to)