Všechna tajemství funkce Excel VLOOKUP pro vyhledání dat v tabulce a jejich extrahování do jiné

2024 Autor: Malcolm Clapton | [email protected]. Naposledy změněno: 2023-12-17 03:50

Po přečtení článku se naučíte nejen najít data v excelové tabulce a extrahovat je do jiné, ale také techniky, které lze použít ve spojení s funkcí VLOOKUP.

Při práci v Excelu je velmi často potřeba najít data v jedné tabulce a extrahovat je do jiné. Pokud stále nevíte, jak na to, po přečtení článku se nejen dozvíte, jak na to, ale také zjistíte, za jakých podmínek můžete ze systému vymáčknout maximální výkon. Zvažuje se většina velmi účinných technik, které by měly být použity ve spojení s funkcí VLOOKUP.

I když funkci VLOOKUP používáte roky, pak s vysokou mírou pravděpodobnosti bude pro vás tento článek užitečný a nenechá vás lhostejnými. Například jako IT specialista a poté vedoucí v IT používám VLOOKUP 15 let, ale se všemi nuancemi jsem se vypořádal až teď, když jsem začal profesionálně učit lidi Excel.

SVYHLEDAT je zkratka pro protivertikální NSinspekce. Stejně tak VLOOKUP – Vertical LOOKUP. Samotný název funkce nám napovídá, že vyhledává v řádcích tabulky (vertikálně - iteruje přes řádky a opravuje sloupec), a nikoli ve sloupcích (horizontálně - iteruje přes sloupce a opravuje řádek). Nutno podotknout, že VLOOKUP má sestru – ošklivé káčátko, které se nikdy nestane labutí – to je funkce HLOOKUP. HLOOKUP, na rozdíl od VLOOKUP, provádí horizontální vyhledávání, ale koncept Excelu (a vlastně koncept organizace dat) znamená, že vaše tabulky mají méně sloupců a mnohem více řádků. Proto potřebujeme vyhledávat podle řádků mnohonásobně častěji než podle sloupců. Pokud v Excelu příliš často používáte funkci HLO, pak je pravděpodobné, že jste v tomto životě něčemu nerozuměli.

Syntax

Funkce VLOOKUP má čtyři parametry:

= SVYHLEDAT (;; [;]), tady:

- požadovaná hodnota (zřídka) nebo odkaz na buňku obsahující požadovanou hodnotu (naprostá většina případů);

- odkaz na rozsah buněk (dvourozměrné pole), v jehož PRVNÍM (!) sloupci bude vyhledána hodnota parametru;

- číslo sloupce v rozsahu, ze kterého bude vrácena hodnota;

- toto je velmi důležitý parametr, který odpovídá na otázku, zda je první sloupec rozsahu seřazen vzestupně. Pokud je pole seřazeno, zadáváme hodnotu TRUE nebo 1, jinak - FALSE nebo 0. Pokud je tento parametr vynechán, je výchozí hodnota 1.

Vsadím se, že mnozí z těch, kteří znají funkci SVYHLEDAT jako šupinatou, se po přečtení popisu čtvrtého parametru mohou cítit nepříjemně, protože jsou zvyklí vidět jej v trochu jiné podobě: obvykle mluví o přesné shodě, když vyhledávání (FALSE nebo 0) nebo prohledávání rozsahu (TRUE nebo 1).

Nyní se musíte napnout a přečíst si další odstavec několikrát, dokud neucítíte význam toho, co bylo řečeno až do konce. Každé slovo je tam důležité. Příklady vám pomohou přijít na to.

Jak přesně vzorec VLOOKUP funguje?

• Vzorec typu I. Pokud je poslední parametr vynechán nebo je zadán rovný 1, pak funkce SVYHLEDAT předpokládá, že první sloupec je seřazen vzestupně, takže hledání se zastaví na řádku, který bezprostředně před řádek obsahující hodnotu větší než požadovaná … Pokud žádný takový řetězec není nalezen, vrátí se poslední řádek rozsahu.

  

• Formule II. Pokud je poslední parametr zadán jako 0, pak SVYHLEDAT postupně prohledá první sloupec pole a okamžitě zastaví vyhledávání, když je nalezena první přesná shoda s parametrem, jinak se zobrazí chybový kód # N / A (# N / A). je vráceno.

  

Pracovní postupy vzorců

VPR typu I

VPR typ II

Důsledky pro vzorce tvaru I

1. Vzorce lze použít k distribuci hodnot napříč rozsahy.
2. Pokud první sloupec obsahuje duplicitní hodnoty a je seřazen správně, bude vrácen poslední z řádků s duplicitními hodnotami.
3. Pokud hledáte hodnotu, která je zjevně větší, než může obsahovat první sloupec, můžete snadno najít poslední řádek tabulky, což může být docela cenné.
4. Toto zobrazení vrátí chybu # N / A pouze v případě, že nenalezne hodnotu menší nebo rovnou požadované hodnotě.
5. Je poměrně obtížné pochopit, že vzorec vrací nesprávné hodnoty, pokud vaše pole není seřazeno.

Důsledky pro vzorce II

Pokud se požadovaná hodnota vyskytuje v prvním sloupci pole vícekrát, vzorec vybere první řádek pro následné načtení dat.

Výkon VLOOKUP

Dosáhli jste vrcholu článku. Zdá se, no, jaký je rozdíl, když jako poslední parametr uvedu nulu nebo jedničku? V zásadě každý uvádí samozřejmě nulu, protože to je docela praktické: nemusíte se starat o řazení prvního sloupce pole, okamžitě vidíte, zda byla hodnota nalezena nebo ne. Pokud však máte na listu několik tisíc vzorců SVYHLEDAT, všimnete si, že VLOOKUP II je pomalý. Ve stejnou dobu si obvykle každý začne myslet:

• Potřebuji výkonnější počítač;
• Potřebuji rychlejší vzorec, například mnoho lidí ví o INDEX + MATCH, který je prý rychlejší o ubohých 5-10%.

A málokdo si myslí, že jakmile začnete používat SVYHLEDAT I. typu a zajistíte seřazení prvního sloupce jakýmkoli způsobem, rychlost SVYHLEDAT se zvýší 57krát. Píšu slovy – 57 KRÁT! Ne 57 %, ale 5 700 %. Tuto skutečnost jsem si ověřil celkem spolehlivě.

Tajemství takové rychlé práce spočívá v tom, že na tříděné pole lze aplikovat extrémně účinný vyhledávací algoritmus, kterému se říká binární vyhledávání (metoda půlení, metoda dichotomie). VLOOKUP typu I jej tedy použije a VLOOKUP typu II vyhledává zcela bez jakékoli optimalizace. Totéž platí pro funkci MATCH, která obsahuje podobný parametr, a funkci LOOKUP, která funguje pouze na seřazených polích a je součástí Excelu kvůli kompatibilitě s Lotus 1-2-3.

Nevýhody vzorce

Nevýhody VLOOKUP jsou zřejmé: za prvé vyhledává pouze v prvním sloupci zadaného pole a za druhé pouze napravo od tohoto sloupce. A jak chápete, může se klidně stát, že sloupec obsahující potřebné informace bude vlevo od sloupce, ve kterém budeme hledat. Již zmíněná kombinace vzorců INDEX + MATCH tuto nevýhodu postrádá, což z ní dělá nejflexibilnější řešení pro extrakci dat z tabulek ve srovnání s VLOOKUP (VLOOKUP).

Některé aspekty použití vzorce v reálném životě

Hledání rozsahu

Klasickou ilustrací hledání sortimentu je úkol určit slevu podle velikosti objednávky.

Hledejte textové řetězce

VLOOKUP samozřejmě nehledá jen čísla, ale také text. Je třeba mít na paměti, že vzorec nerozlišuje velikost písmen. Pokud používáte zástupné znaky, můžete uspořádat nejasné vyhledávání. Existují dva zástupné znaky: "?" - nahradí libovolný jeden znak v textovém řetězci, "*" - nahradí libovolný počet libovolných znaků.

Bojové prostory

Často se objevuje otázka, jak vyřešit problém s mezerami navíc při vyhledávání. Pokud je lze stále z vyhledávací tabulky vymazat, pak první parametr vzorce SVYHLEDAT není vždy na vás. Pokud tedy existuje riziko ucpání buněk nadbytečnými mezerami, můžete k jeho vyčištění použít funkci TRIM.

Jiný formát dat

Pokud první parametr funkce SVYHLEDAT odkazuje na buňku, která obsahuje číslo, ale která je v buňce uložena jako text, a první sloupec pole obsahuje čísla ve správném formátu, hledání se nezdaří. Je možná i opačná situace. Problém lze snadno vyřešit převodem parametru 1 do požadovaného formátu:

= VLOOKUP (−− D7; Produkty! $ A $ 2: $ C $ 5; 3; 0) - pokud D7 obsahuje text a tabulka obsahuje čísla;

= SVYHLEDAT (D7 & ""); Produkty! $ A $ 2: $ C $ 5; 3; 0) - a naopak.

Mimochodem, text můžete přeložit do čísla několika způsoby najednou, vyberte si:

• Dvojitá negace -D7.
• Násobení jednou D7 * 1.
• Nulové sčítání D7 + 0.
• Zvýšení na první moc D7 ^ 1.

Převod čísla na text se provádí pomocí zřetězení s prázdným řetězcem, což přinutí Excel převést datový typ.

Jak potlačit # N / A

To je velmi pohodlné s funkcí IFERROR.

Například: = IFERROR (VLOOKUP (D7; Produkty! $ A $ 2: $ C $ 5; 3; 0); "").

Pokud funkce VLOOKUP vrátí kód chyby # N / A, IFERROR jej zachytí a nahradí parametr 2 (v tomto případě prázdný řetězec), a pokud nedojde k žádné chybě, bude tato funkce předstírat, že vůbec neexistuje, ale existuje pouze funkce VLOOKUP, která vrátila normální výsledek.

Pole

Často zapomenou uvést odkaz na pole absolutní a při roztahování pole „plave“. Nezapomeňte použít $ A $ 2: $ C $ 5 místo A2: C5.

Je vhodné umístit referenční pole na samostatný list sešitu. Neleze pod nohy a bude to bezpečnější.

Ještě lepší nápad by bylo deklarovat toto pole jako pojmenovaný rozsah.

Mnoho uživatelů při zadávání pole používá konstrukci jako A: C, která určuje celé sloupce. Tento přístup má právo na existenci, protože jste ušetřeni nutnosti sledovat skutečnost, že vaše pole obsahuje všechny požadované řetězce. Pokud přidáte řádky do listu s původním polem, pak rozsah zadaný jako A:C není nutné upravovat. Tato syntaktická konstrukce samozřejmě nutí Excel udělat trochu více práce, než přesně specifikovat rozsah, ale tuto režii lze zanedbat. Bavíme se o setinkách vteřiny.

No, na pokraji geniality - uspořádat pole ve formě.

Pomocí funkce COLUMN zadejte sloupec, který se má extrahovat

Pokud má tabulka, do které načítáte data pomocí funkce SVYHLEDAT, stejnou strukturu jako vyhledávací tabulka, ale obsahuje pouze méně řádků, můžete pomocí funkce COLUMN () ve SVYHLEDAT automaticky vypočítat čísla sloupců, které mají být načteny. V tomto případě budou všechny vzorce VLOOKUP stejné (upravené pro první parametr, který se automaticky změní)! Všimněte si, že první parametr má absolutní souřadnice sloupce.

Vytvoření složeného klíče pomocí & "|" &

Pokud je nutné hledat podle několika sloupců současně, je nutné vytvořit složený klíč pro vyhledávání. Pokud by návratová hodnota nebyla textová (jako je tomu u pole „Kód“), ale číselná, pak by se k tomu hodil pohodlnější vzorec SUMIFS a složený klíč sloupce by nebyl vůbec potřeba.

Toto je můj první článek pro Lifehackera. Pokud se vám to líbilo, zvu vás k návštěvě a také si rád přečtu v komentářích o vašich tajemstvích používání funkce VLOOKUP a podobně. Dík.:)