Jak používat VLOOKUP Excel? Excel funkce v aplikaci Excel pro figuríny a nejen

Každá osoba, která se zabývá čísly, ať už se jedná o profesionální zájem nebo o uměleckou lásku, je samozřejmě první ovládání kalkulačky. Co dál? Dalším z nich je geniální program pro práci s čísly - Excel. Pravděpodobně je nemožné tento program dokonale poznat, stejně jako matematiku dokonale poznat. Existují však základní funkce programu, funkce aplikace Excel, s vědomím, že s 90% jistotou můžeme mluvit o schopnosti pracovat s daty na úrovni sebejistého uživatele. Jednou z nejdůležitějších funkcí v aplikaci Excel je VLOOKUP, která je velmi všestranná. Jak používat VLOOKUP v aplikaci Excel (pro figuríny) je obsažen v článku. Samozřejmě, vnímání teorie nepovede k úplnému pochopení procesu využívání této funkce. Nejprve je třeba se cvičit na malých datových polích, pak na tabulkách jakékoliv velikosti.

Podstata a účel funkce CDF v aplikaci Excel

Funkce CDF ušetří spoustu času pro takové specialisty, jako jsou ekonomové, finančníci, účetní, technologové. Popis funkce VLOOKUP v aplikaci Excel a příklad její aplikace by měl být zahájen zvážením situace, kdy se odborníci různého druhu často setkávají ve své práci. Pokud existují dvě tabulky, ve kterých se objeví stejné definovatelné údaje (například názvy zboží), definice pro ně v různých tabulkách se liší, například v jednom je množství a v jiném je cena. Úkolem je zároveň spojit všechna tato data dohromady a provést příslušné výpočty - v takových situacích dochází k záchraně funkce CDF. Chcete-li pochopit, jak VLOOKUP funguje v aplikaci Excel, jsou uvedeny níže dvě tabulky. V každém z nich je kód produktu. V jedné je vhodné množství, v druhé - cena. Pro nahrazení cen v první tabulce a použitých CDF.

Ruční nahrazení dat mezi tabulkami je obrovským ztrátou času a úsilí zaměstnance, chybám se nelze vyhnout. Pomocí CDF můžete rychle a jednoduše najít odpovídající hodnoty. Pro hluboké pochopení toho, jak používat VLOOKUP v aplikaci Excel, je nutné analyzovat všechny argumenty funkcí.

Funkční argumenty

Chcete-li tuto funkci použít, je nutné stát ve výsledné buňce, vyberte kartu na kartě Vzorce - "Odkazy a matice" - "CDF" na pásu. V buňce se objevil nápis "= VLR" a nyní je třeba správně zadat argumenty funkce. Můžete to udělat přes středník přímo ve vzorci.

Funkce CDF má čtyři argumenty - hodnotu, kterou hledáte, tabulku, číslo sloupce, interval vyhledávání. První tři, zvýrazněné tučně v dialogovém okně, jsou vyžadovány. Vysvětlujeme, jaké jsou argumenty funkcí:

• hledaná hodnota je to, co hledat;
• tabulka - kde hledat;
• číslo sloupce - ve kterém sloupci chcete vyhledávat;
• interval zobrazení - tříděno.

Argument "vyhledávaná hodnota"

V prvním poli musíte zadat, co potřebujete vyhledat. Může jít o číslo, text, datum. Můžete zadat absolutní hodnotu, můžete připojit odkaz na buňku s požadovanou hodnotou. Požadovaná hodnota by měla být teoreticky přítomna v obou tabulkách. Ve výše uvedeném příkladu může být tato hodnota kódem produktu. Abyste předešli chybám, je lepší použít odkaz.

Při použití textu jako požadované hodnoty musí být v uvozovkách. Pokud odkazujete na buňku s textem, citace nejsou potřeba. Při zadávání textu se nezaregistrujete. Může být použito fuzzy hledání založené na fragmentu textu. Chcete-li to provést, musíte uvnitř citátů přiložit text v * znaky.

Nejběžnějším problémem při použití funkce je rozdílný formát buněk v rozsahu. Pokud se v poli prvního argumentu umístí odkaz na buňku, kde jsou data v textovém formátu, a ve vyhledávacím rozsahu budou data v číselném formátu, nenaleznou shodu s vnější identitou hodnot CDF.

Převod na číselný formát je snadný - musíte pole vynásobit jedním.

Argument tabulky

Zde musíte zadat rozsah, ve kterém funkce vyhledá první zadanou hodnotu. Rozsah pro zjištění hodnot bude prvním sloupcem vybrané oblasti. Hodnota, kterou chcete najít a vložit do výsledku, musí být ve sloupci napravo od sloupce vyhledávání. Jedná se o jednu z klíčových nevýhod při používání CDF: Aby bylo možné s ním pracovat, musí být tabulka často znovu sestavena tak, aby požadovaná data byla vlevo. Rozsah může být zadán jak ručně, tak odkazem. V příkladu vypadá takto:

Rozsah zde je správná tabulka. Požadovaná hodnota ve sloupci vpravo, kde aplikace Excel vyhledá hodnotu 3187849428, hodnota, kterou je třeba najít a nahradila výsledkem vzorce, je vlevo. Chcete-li získat přesný výsledek, je lepší určit rozsah, zvolit ho a stisknout klávesu F4, odkaz na pole se stane absolutní.

Argument "číslo sloupce"

Zde je nutné umístit číslici, ve které je ve sloupci sloupec, který je od levého, potřebný k tomu, aby hodnota substituce byla výsledkem počtu. Ve výše uvedeném příkladu se jedná o druhý sloupec, v řetězci argumentů je třeba položit číslo 2. Pokud by byl mezi sloupcem Kód a Cenou jiný sloupec, pak byste museli položit číslo 3 a tak dále.

Interval náhledu argumentu

Vyplnění tohoto pole je nepovinné, ale může být velmi důležité. Může existovat jedna ze dvou hodnot - 1 (true) nebo 0 (false). Většina uživatelů se domnívá, že funkcí tohoto argumentu je určit přesnost shody s požadovanými hodnotami. To není úplně správné. Při zadávání jednotky, pokud se v tabulce objeví opakování, vrátí funkce poslední nalezenou hodnotu. V tomto případě funkce VLR zohlední všechny hodnoty, které jsou menší nebo stejné jako hodnoty zadané v poli "Požadovaná hodnota". Pokud funkce zjistí větší hodnotu, ale nenajde menší nebo stejnou hodnotu, generuje chybu N / A.

Můžete vidět, že číslo 3187849425 je zadáno do pole "Hodnota", v požadovaném rozsahu není žádná taková hodnota a program, který najde všechny hodnoty, které jsou menší nebo rovna požadované hodnotě, vráti hodnotu odpovídající poslednímu vhodnému kódu v seznamu - 3187848593, jehož cena je 2 479,46 rublů. Pokud je pole pro sledování intervalu prázdné, funkce bude pracovat stejným způsobem jako hodnota jednotky.

Když je v poli argumentu zadána hodnota 0, funkce vrátí pouze hodnotu odpovídající požadované hodnotě. Pokud do vyhledávacího rozsahu dochází k opakování, funkce bude mít první shodu. Při použití vzorce s argumentem 0 funkce funguje mnohem déle, ale mnohem přesněji.

Vlastnosti využití CDF za několika podmínek

Použití funkce CDF je často kreativní proces, který vyžaduje od uživatele matematické myšlení. Často je potřeba najít shodu nejen pro jeden sloupec, ale pro dva nebo dokonce více. Pomocí dalších akcí lze také použít funkci VLOOKUP. V obou tabulkách je nutné vytvořit další sloupce, kde je možné kombinovat data z uvažovaných sloupců. To lze provést pomocí funkce "CLUTCH" nebo ikony "&".

Jak používat funkci CDF, pokud jsou data na různých stránkách

Jak používat VLOOKUP v aplikaci Excel na různých listech, v různých souborech? Často uživatelé vytvářejí některé příručky, s nimiž je nutné porovnávat a najít korespondence. Rozdíly v práci v takových podmínkách spočívají v poněkud odlišné formě pole "Tabulka" v okně funkčních argumentů. Před zadáním rozsahu je třeba umístit číslo listu (pokud jsou data v jedné knize) nebo název souboru (pokud nejsou data v jedné knize). Pokud ve výše uvedeném příkladu je ceník zkopírován do samostatného souboru, zavolejte jej "Cena" a pomocí funkce CDF se podívejte na ceny, bude vypadat takto:

Všechny výše uvedené akce lze provést pomocí odkazu. Musíte umístit kurzor do pole "Tabulka", přejít na požadovaný soubor a vybrat rozsah pomocí myši. Někdy se stane, že tabulka není vložena jako odkaz v okně. Poté je třeba provést následující: otevřete původní tabulku, kde je třeba provést výpočty, a pak pomocí nabídky "Soubor" - "Otevřít" najděte druhou tabulku. Takto otevřené soubory fungují hladce.

Všechny výše uvedené akce lze provést pomocí odkazu. Musíte umístit kurzor do pole "Tabulka", přejít na požadovaný soubor a vybrat rozsah pomocí myši. Někdy se stane, že tabulka není vložena jako odkaz v okně. Poté je třeba provést následující: otevřete původní tabulku, kde je třeba provést výpočty, a pak pomocí nabídky "Soubor" - "Otevřít" najděte druhou tabulku. Takto otevřené soubory fungují hladce.

Chcete-li vážit soubor, ve kterém existují vzorce s CDF, s odkazem na jiné soubory bude mnohem víc, než bez nich. To může být problematické, například při přenosu souborů. Abyste předešli těmto potížím, musíte přepočítat vzorec na hodnoty. Na pásu karet vyberte podnabídku "Data" a příkaz "Upravit komunikaci". Dokonce i v případě, že data nejsou převzata z jiného souboru, je vždy užitečné nahradit vzorce s hodnotami - tím jsou výpočty spolehlivější.

Rozbalovací seznam pro usnadnění práce s CDF

Funkce VLR často nefunguje, pokud existují menší nesrovnalosti v datech. Tento extra prostor v textu, data zadaná s chybami. Chcete-li se vyhnout všem těmto potížím, použijte rozevírací seznam v aplikaci Excel pro zadání hodnot. Má smysl spustit jej při práci s neustále opakujícími se údaji. Pokud existuje určitá referenční příručka, která se používá jako srovnávací tabulka, porovnávací rozsah lze považovat za údaje pro rozevírací seznam a použít k vytvoření tabulky, do které budou data vložena do funkce CDF. Kurzor se umístí do buňky, kde je třeba zadat hodnotu. Dále na pásku je podkapitola "Data", je vybrán příkaz "Ověření dat". V dialogovém okně v poli "Typ dat" zadejte hodnotu "Seznam". V poli "Zdroj" se zaznamenává rozsah referenční tabulky. Rozbalovací seznam je tvořen. Nyní při vyplňování tabulky je zaručena plná shoda hodnot.

Příklad použití funkce VLOOKUP v aplikaci Excel

Jak používat VLOOKUP v aplikaci Excel, každý uživatel se sám vyřeší na základě praktických úkolů v jeho práci. Vezmeme-li v úvahu všechny výše uvedené nuance, můžeme přinést předchozí příklad na svůj logický konec a vytvořit vhodný a jasný algoritmus pro uplatnění funkce VLR v praxi. Existuje odhad nákladů na náhradní díly pro opravu zařízení. K dispozici je také ceník náhradních dílů. Ekonom společnosti je povinen najít náklady na opravy náhradních dílů.

Nejprve musíte správně napsat vzorec.

Pomocí funkce VLOOKUP se tedy objeví cena odpovídající kódu v prvním řádku. Je třeba vynásobit vzorec dolů, abyste to provedli, vyberte buňku vzorce a vysuňte čtverec v pravém rohu buňky. Ve sloupci D funkce vrátila ceny odpovídající kódům. Je třeba nahradit vzorce s hodnotami. Chcete-li to provést, vyberte vyplněné buňky sloupce D, zkopírujte je a vložte jako hodnoty. Poté je třeba vytvořit sloupec "Sum", kde zadáte vzorec pro výrobek o množství a ceně, a pak sumu použijte ke shrnutí nákladů.

Toto je příklad toho, jak VLOOKUP funguje v aplikaci Excel.

Chyby při použití funkce CDF

V počáteční fázi použití namísto požadovaných hodnot funkce často indikuje různé typy chyb. Chcete-li vědět, co znamená chyba, je to správný způsob, jak to rychle řešit. Nejčastější chyby:

• "N / A" - nejběžnější typ chyby. To může nastat z několika důvodů.

1. Sloupec, na kterém funkce hledá shodu, je nesprávně umístěn (musí být vlevo). Pokud nastane situace, kdy požadovaná hodnota je vlevo od oblasti shody, musí být tabulka převedena. Zkopírujte například požadovaný sloupec a vložte ho vpravo od oblasti vyhledávání.
2. Chyba "N / A" může být vrácena, pokud rozsah hledání není opraven, zatímco vytahujete vzorce WFD.
3. Pokud je zadáno přesné vyhledávání pomocí argumentu Interval View (číslice 0 je nastavena), vrátí se chyba "N / A", pokud v obou tabulkách neexistuje přesná shoda.
4. Argument Rozsah zobrazení je nastaven na nejbližší hodnotu (1 je označeno nebo pole není vyplněno) a rozsah, ve kterém vyhledávání probíhá, není roztříděno. V případě nepřesného vyhledávání je nutné třídit levý sloupec rozsahu vyhledávání.
5. Porovnávané údaje mají další mezery (pro jejich odstranění je možné použít funkci "SIFPROBELS", která se použije na tabulku a požadovanou hodnotu), jiný formát, další uvozovky. Chcete-li v obou tabulkách psát stejné hodnoty, je vhodné použít rozevírací seznam.

• "LINK" - tato chyba se často vyskytuje, když je číslo sloupce zadáno nesprávně, pokud tolik sloupců není ve zvoleném rozsahu. V takových případech je nutné si uvědomit, že číslo je odloženo, počítá se z levého sloupce vybraného rozsahu, nikoliv z tabulky jako celku.
• "NAME" - chyba se často vrací, když je text zadán do "Požadované hodnoty". Text musí být napsán v uvozovkách.

Znalosti o tom, jak používat aplikaci VLOOKUP v aplikaci Excel, přicházejí, stejně jako vše ostatní v tomto programu, s praxí. Pouhé studium teorie místo jasnosti pravděpodobně přinese chaos v pochopení konkrétní operace v programu. Při studiu jakékoliv funkce aplikace Excel je vždy efektivnější, aby se uživatel mohl spolehnout na konkrétní, i když malou příkladnou tabletu. To vám umožní lépe porozumět podstatě analýzy, upevnit poznatky získané. Takže s funkcí CDF v aplikaci Excel. S velkým množstvím teorie, praktická aplikace ukazuje, že to není tak složité, jak je užitečné.