Funkce VLOOKUP je jednou z nejoblíbenějších funkcí v Excelu. Tento tutoriál vám krok za krokem představí, jak používat funkci SVYHLEDAT v Excelu s desítkami základních a pokročilých příkladů.

Představení funkce VLOOKUP - Syntaxe a argumenty

V Excelu je funkce SVYHLEDAT výkonnou funkcí pro většinu uživatelů Excelu, umožňuje vám vyhledat hodnotu v rozsahu dat zcela vlevo a vrátit odpovídající hodnotu ve stejném řádku ze sloupce, který jste zadali jako následující snímek obrazovky. .

Syntaxe funkce VLOOKUP:

argumenty:

Poznámky k funkci:

Základní příklady VLOOKUP

V této části budeme hovořit o některých vzorcích Vlookup, které jste často používali.

2.1 Přesná a přibližná shoda VLOOKUP

 2.1.1 Proveďte přesnou shodu VLOOKUP

Normálně, pokud hledáte přesnou shodu s funkcí SVYHLEDAT, stačí jako poslední argument použít FALSE.

Chcete-li například získat odpovídající matematické skóre na základě konkrétních identifikačních čísel, postupujte takto:

Zkopírujte a vložte níže uvedený vzorec do prázdné buňky (zde vyberu G2) a stiskněte vstoupit klíč k získání výsledku:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Jak funguje vzorec VLOOKUP?

Nejprve hledá ID - C1005 v levém sloupci tabulky. Projde shora dolů a najde hodnotu v buňce A6.

Jakmile najde hodnotu, přejde do třetího sloupce doprava a hodnotu z něj extrahuje.

Takže dostanete výsledek, jak je zobrazen níže na snímku obrazovky:

 2.1.2 Proveďte přibližnou shodu VLOOKUP

Přibližná shoda je užitečná pro vyhledávání hodnot mezi rozsahy dat. Pokud není přesná shoda nalezena, přibližné SVYHLEDAT vrátí největší hodnotu, která je menší než vyhledávací hodnota.

Pokud máte například následující rozsah dat a zadané objednávky nejsou ve sloupci Objednávky, jak získat nejbližší slevu ve sloupci B?

Krok 1: Použijte vzorec SVYHLEDAT a vyplňte jej do ostatních buněk

Zkopírujte a vložte následující vzorec do buňky, kam chcete umístit výsledek, a poté přetáhněte úchyt výplně dolů, abyste tento vzorec aplikovali na další buňky.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Výsledek:

Nyní získáte přibližné shody na základě daných hodnot, viz snímek obrazovky:

2.2 Proveďte v Excelu SVYHLEDAT s rozlišováním malých a velkých písmen

Ve výchozím nastavení funkce SVYHLEDAT provádí vyhledávání bez ohledu na malá a velká písmena, což znamená, že zachází s malými a velkými písmeny jako s identickými. Někdy může být nutné provést v Excelu vyhledávání rozlišující malá a velká písmena, běžná funkce SVYHLEDAT to nemusí vyřešit. V tomto případě můžete použít alternativní funkce jako INDEX a MATCH s funkcí EXACT nebo funkce LOOKUP a EXACT.

Například mám následující rozsah dat, který sloupec ID obsahuje textový řetězec s velkými nebo malými písmeny, nyní chci vrátit odpovídající matematické skóre daného ID čísla.

Krok 1: Použijte libovolný jeden vzorec a vyplňte jej do ostatních buněk

Zkopírujte a vložte kterýkoli z níže uvedených vzorců do prázdné buňky, kde chcete získat výsledek. Poté vyberte buňku vzorce a přetáhněte úchyt výplně dolů k buňkám, do kterých chcete tento vzorec vyplnit.

Formula 1: Po vložení vzorce stiskněte Ctrl + Shift + Enter klíče.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formula 2: Po vložení vzorce stiskněte vstoupit klíč.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Výsledek:

Pak získáte správné výsledky, které potřebujete. Viz snímek obrazovky:

2.3 Hodnoty VLOOKUP zprava doleva v Excelu

Funkce SVYHLEDAT vždy vyhledává hodnotu ve sloupci zcela vlevo v rozsahu dat a vrací odpovídající hodnotu ze sloupce vpravo. Pokud chcete provést zpětné SVYHLEDAT, což znamená vyhledat konkrétní hodnotu v pravém sloupci a vrátit její odpovídající hodnotu v levém sloupci, jak je uvedeno níže:

Kliknutím zobrazíte podrobné informace o této úloze krok za krokem…

2.4 SVYHLEDAT druhou, n-tou nebo poslední odpovídající hodnotu v Excelu

Obvykle, pokud je při použití funkce Vlookup nalezeno více odpovídajících hodnot, bude vrácen pouze první odpovídající záznam. V této části budu hovořit o tom, jak získat druhou, n-tou nebo poslední odpovídající hodnotu v datovém rozsahu.

 2.4.1 VLOOKUP a vrátí druhou nebo n-tou odpovídající hodnotu

Předpokládejme, že ve sloupci A máte seznam jmen, ve sloupci B školení, které si zakoupili. Nyní hledáte 2. nebo n. školení zakoupené daným zákazníkem. Viz snímek obrazovky:

Zde nemusí funkce VLOOKUP tento úkol přímo vyřešit. Jako alternativu však můžete použít funkci INDEX.

Krok 1: Použijte a vyplňte vzorec do dalších buněk

Chcete-li například získat druhou odpovídající hodnotu na základě daných kritérií, použijte prosím následující vzorec do prázdné buňky a stiskněte Ctrl + Shift + Enter klíče dohromady, abyste získali první výsledek. A pak vyberte buňku vzorce, přetáhněte úchyt výplně dolů k buňkám, kam chcete tento vzorec vyplnit.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Výsledek:

Nyní jsou všechny druhé shodné hodnoty založené na zadaných názvech zobrazeny najednou.

 2.4.2 VLOOKUP a vrátí poslední odpovídající hodnotu

Pokud chcete vlookup a vrátit poslední odpovídající hodnotu, jak je ukázáno na následujícím obrázku obrazovky, toto SVYHLEDAT a vrátit poslední odpovídající hodnotu tutoriál vám může pomoci získat poslední odpovídající hodnotu v detailech.

2.5 VLOOKUP párování hodnot mezi dvěma danými hodnotami nebo daty

Někdy můžete chtít vyhledat hodnoty mezi dvěma hodnotami nebo daty a vrátit odpovídající výsledky, jak je znázorněno na obrázku níže. V takovém případě můžete místo funkce SVYHLEDAT s seřazenou tabulkou použít funkci VYHLEDAT.

 2.5.1 SVYHLEDAT porovnávání hodnot mezi dvěma danými hodnotami nebo daty pomocí vzorce

Krok 1: Uspořádejte data a použijte následující vzorec

Vaše původní tabulka by měla být seřazeným rozsahem dat. A pak zkopírujte nebo zadejte následující vzorec do prázdné buňky. Poté přetažením úchytu vyplňte tento vzorec do dalších buněk, které potřebujete.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Výsledek:

A nyní získáte všechny odpovídající záznamy na základě dané hodnoty, viz snímek obrazovky:

 2.5.2 SVYHLEDAT porovnávání hodnot mezi dvěma danými hodnotami nebo daty s praktickou funkcí

Pokud je pro vás obtížné zapamatovat si výše uvedený vzorec a porozumět mu, zde vám představím snadný nástroj – Kutools pro Excel, S jeho VYHLEDÁVÁNÍ mezi dvěma hodnotami můžete snadno vrátit odpovídající položku na základě konkrétní hodnoty nebo data mezi dvěma hodnotami nebo daty.

1. klikněte Kutools > Super VZHLED > VYHLEDÁVÁNÍ mezi dvěma hodnotami pro povolení této funkce.
2. Poté určete operace z dialogového okna na základě vašich dat.

2.6 Použití zástupných znaků pro dílčí shody ve funkci VLOOKUP

V aplikaci Excel lze zástupné znaky použít v rámci funkce SVYHLEDAT, která umožňuje provést částečnou shodu s vyhledávanou hodnotou. Například můžete použít VLOOKUP k vrácení odpovídající hodnoty z tabulky na základě části hodnoty vyhledávání.

Předpokládejme, že mám řadu dat, jak je uvedeno níže, nyní chci extrahovat skóre na základě křestního jména (ne celého jména). Jak by mohl tento úkol vyřešit v aplikaci Excel?

Krok 1: Použijte a vyplňte vzorec do dalších buněk

Zkopírujte nebo zadejte následující vzorec do prázdné buňky a poté přetažením úchytu vyplňte tento vzorec do dalších buněk, které potřebujete:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Výsledek:

A všechna shodná skóre byla vrácena, jak je zobrazeno na níže uvedeném snímku obrazovky:

2.7 Hodnoty VLOOKUP z jiného listu

Obvykle budete muset pracovat s více než jedním listem, funkci VLOOKUP lze použít k vyhledání dat z jiného listu, stejně jako na jednom listu.

Například máte dva pracovní listy, jak je ukázáno níže, pro vyhledání a vrácení odpovídajících dat z listu, který jste zadali, proveďte následující kroky:

Krok 1: Použijte a vyplňte vzorec do dalších buněk

Zadejte nebo zkopírujte níže uvedený vzorec do prázdné buňky, kde chcete získat odpovídající položky. Potom přetáhněte úchyt výplně dolů k buňkám, na které chcete použít tento vzorec.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Výsledek:

Dostanete odpovídající výsledky, jak potřebujete, viz snímek obrazovky:

2.8 Hodnoty VLOOKUP z jiného sešitu

Tato část bude hovořit o vyhledávání a vrácení odpovídajících hodnot z jiného sešitu pomocí funkce SVYHLEDAT.

Řekněme například, že máte dva sešity. První sešit obsahuje seznam produktů a jejich příslušných nákladů. Ve druhém sešitu chcete extrahovat odpovídající náklady pro každou položku produktu, jak je znázorněno na obrázku níže.

Krok 1: Aplikujte a vyplňte vzorec

Otevřete oba sešity, které chcete použít, a poté aplikujte následující vzorec na buňku, do které chcete výsledek vložit do druhého sešitu. Potom tento vzorec přetáhněte a zkopírujte do dalších buněk, které potřebujete

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Výsledek:

2.9 Vraťte prázdný nebo specifický text místo chyby 0 nebo #N/A

Obvykle, když použijete funkci SVYHLEDAT k vrácení odpovídající hodnoty, pokud je odpovídající buňka prázdná, vrátí 0. A pokud odpovídající hodnota nebude nalezena, dostanete chybovou hodnotu #N/A, jak je uvedeno v snímek obrazovky níže. Pokud chcete zobrazit prázdnou buňku nebo konkrétní hodnotu místo 0 nebo #N/A, toto VLOOKUP vrátí prázdnou nebo specifickou hodnotu namísto 0 nebo N/A tutoriál vám může udělat laskavost.

Pokročilé příklady VLOOKUP

3.1 Obousměrné vyhledávání (SVYHLEDAT v řádku a sloupci)

Někdy může být nutné provést 2-rozměrné vyhledávání, což znamená hledat hodnotu v řádku i ve sloupci současně. Pokud máte například následující rozsah dat a možná budete muset získat hodnotu pro konkrétní produkt v určeném čtvrtletí. Tato část představí vzorec pro práci s touto úlohou v Excelu.

V Excelu můžete použít kombinaci funkcí VLOOKUP a MATCH k provedení obousměrného vyhledávání.

Použijte prosím následující vzorec do prázdné buňky a poté stiskněte vstoupit klíč k získání výsledku.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 Shoda hodnoty SVYHLEDAT na základě dvou nebo více kritérií

Je pro vás snadné vyhledat odpovídající hodnotu na základě jednoho kritéria, ale pokud máte dvě nebo více kritérií, co můžete dělat?

 3.2.1 SVYHLEDAT odpovídající hodnotu na základě dvou nebo více kritérií se vzorci

V tomto případě vám k rychlému a snadnému vyřešení této úlohy mohou pomoci funkce LOOKUP nebo MATCH a INDEX v Excelu.

Například mám níže uvedenou datovou tabulku, aby vám vrátila uzavřenou cenu na základě konkrétního produktu a velikosti, mohou vám pomoci následující vzorce.

Krok 1: Aplikujte libovolný vzorec

Formula 1: Po vložení vzorce stiskněte vstoupit klíč.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formula 2: Po vložení vzorce stiskněte Ctrl + Shift + Enter klíče.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Výsledek:

 3.2.2 SVYHLEDAT odpovídající hodnotu na základě dvou nebo více kritérií s inteligentní funkcí

Může být náročné zapamatovat si výše uvedené složité vzorce, které je třeba aplikovat opakovaně, což může zpomalit vaši efektivitu práce. Nicméně, Kutools pro Excel nabízí Vyhledávání více podmínek funkce, která vám umožňuje vrátit odpovídající výsledek na základě jedné nebo více podmínek pouze několika kliknutími.

1. klikněte Kutools > Super VZHLED > Vyhledávání více podmínek pro povolení této funkce.
2. Poté určete operace z dialogového okna na základě vašich dat.

3.3 VLOOKUP pro vrácení více hodnot s jedním nebo více kritérii

V aplikaci Excel funkce SVYHLEDAT hledá hodnotu a pouze v případě, že je nalezeno více odpovídajících hodnot, vrátí první odpovídající hodnotu. Někdy můžete chtít vrátit všechny odpovídající hodnoty v řádku, ve sloupci nebo v jedné buňce. Tato část bude hovořit o tom, jak vrátit více odpovídajících hodnot s jednou nebo více podmínkami v sešitu.

 3.3.1 VYHLEDAT všechny odpovídající hodnoty na základě jedné nebo více podmínek horizontálně

Za předpokladu, že máte tabulku dat, která obsahuje zemi, město a jména v rozsahu A1:C14, a nyní chcete vodorovně vrátit všechna jména, která jsou z „USA“, jak je zobrazeno na obrázku níže. Chcete-li vyřešit tento úkol, prosím kliknutím sem získáte výsledek krok za krokem.

 3.3.2 SVYHLEDAT všechny odpovídající hodnoty na základě jedné nebo více podmínek vertikálně

Pokud potřebujete vyhledávat a vrátit všechny odpovídající hodnoty vertikálně na základě konkrétních kritérií, jak je uvedeno níže, prosím klikněte zde pro získání podrobností o řešení.

 3.3.3 VYHLEDAT všechny odpovídající hodnoty na základě jedné nebo více podmínek do jedné buňky

Pokud chcete provést vyhledávání a vrátit více odpovídajících hodnot do jedné buňky se zadaným oddělovačem, nová funkce TEXTJOIN vám může pomoci vyřešit tuto úlohu rychle a snadno.

3.4 SVYHLEDAT pro vrácení celého řádku odpovídající buňky

V této části budu hovořit o tom, jak načíst celý řádek shodné hodnoty pomocí funkce SVYHLEDAT.

Krok 1: Aplikujte a vyplňte následující vzorec

Zkopírujte nebo zadejte níže uvedený vzorec do prázdné buňky, kam chcete vytisknout výsledek, a stiskněte vstoupit klíč k získání první hodnoty. Poté přetáhněte buňku vzorce doprava, dokud se nezobrazí data celého řádku.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Výsledek:

Nyní můžete vidět, že jsou vrácena celá data řádku. Viz snímek obrazovky:

3.5 Vnořené SVYHLEDAT v Excelu

Někdy možná budete muset vyhledat hodnoty, které jsou propojeny ve více tabulkách. V tomto případě můžete vnořit více funkcí SVYHLEDAT dohromady, abyste získali konečnou hodnotu.

Mám například list, který obsahuje dvě samostatné tabulky. V první tabulce jsou uvedeny všechny názvy produktů spolu s příslušným prodejcem. Druhá tabulka uvádí celkové tržby jednotlivých prodejců. Nyní, pokud chcete zjistit prodeje každého produktu, jak je znázorněno na následujícím snímku obrazovky, můžete k provedení tohoto úkolu vnořit funkci VLOOKUP.

Obecný vzorec pro vnořenou funkci SVYHLEDAT je:

Krok 1: Aplikujte a vyplňte následující vzorec

Použijte prosím následující vzorec do prázdné buňky a poté přetáhněte úchyt výplně dolů k buňkám, na které chcete tento vzorec použít.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Výsledek:

Nyní získáte výsledek, jak je znázorněno na následujícím snímku obrazovky:

3.6 Zkontrolujte, zda hodnota existuje na základě dat seznamu v jiném sloupci

Funkce VLOOKUP vám také může pomoci zkontrolovat, zda existují hodnoty na základě seznamu dat v jiném sloupci. Například, pokud chcete vyhledat názvy ve sloupci C a vrátit pouze Ano nebo Ne, pokud je název nalezen nebo ne ve sloupci A, jak je znázorněno na obrázku níže.

Krok 1: Aplikujte a vyplňte následující vzorec

Použijte prosím následující vzorec do prázdné buňky a poté přetáhněte úchyt výplně dolů k buňkám, které chcete vyplnit tento vzorec.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Výsledek:

A výsledek dostanete, jak potřebujete, viz snímek obrazovky:

3.7 SVYHLEDAT a sečíst všechny odpovídající hodnoty v řádcích nebo sloupcích

Při práci s číselnými daty může být nutné extrahovat odpovídající hodnoty z tabulky a sečíst čísla do několika sloupců nebo řádků. Tato část představí některé vzorce, které vám mohou pomoci tento úkol splnit.

 3.7.1 SVYHLEDAT a sečíst všechny odpovídající hodnoty v řádku nebo více řádcích

Předpokládejme, že máte seznam produktů s prodejem na několik měsíců, jak ukazuje následující snímek obrazovky. Nyní je potřeba sečíst všechny objednávky ve všech měsících na základě daných produktů.

Krok 1: Aplikujte a vyplňte následující vzorec

Zkopírujte nebo zadejte následující vzorec do prázdné buňky a poté stiskněte Ctrl + Shift + Enter klíče dohromady, abyste získali první výsledek. Potom přetáhněte úchyt výplně dolů a zkopírujte tento vzorec do dalších buněk, které potřebujete.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Výsledek:

Všechny hodnoty v řadě první odpovídající hodnoty byly sečteny, viz snímek obrazovky:

 3.7.2 SVYHLEDAT a sečíst všechny odpovídající hodnoty ve sloupci nebo více sloupcích

Pokud chcete sečíst celkovou hodnotu za konkrétní měsíce, jak je znázorněno na obrázku níže. Běžná funkce SVYHLEDAT vám nemusí pomoci, zde byste měli použít funkce SUM, INDEX a MATCH společně, abyste vytvořili vzorec.

Krok 1: Použijte následující vzorec

Aplikujte níže uvedený vzorec do prázdné buňky a poté přetažením úchytu výplně dolů zkopírujte tento vzorec do jiných buněk.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Výsledek:

Nyní byly sečteny první odpovídající hodnoty na základě konkrétního měsíce ve sloupci, viz snímek obrazovky:

 3.7.3 SVYHLEDAT a sečíst první nebo všechny shodné hodnoty pomocí výkonné funkce

Možná jsou pro vás výše uvedené vzorce obtížné zapamatovat si, v tomto případě vám doporučím výkonnou funkci - Vyhledávání a součet of Kutools pro Excel, s touto funkcí můžete vyhledávat a co nejsnáze sečíst první odpovídající nebo všechny odpovídající hodnoty v řádcích nebo sloupcích.

1. klikněte Kutools > Super VZHLED > VYHLEDÁVÁNÍ a součet pro povolení této funkce.
2. Poté určete operace z dialogového okna podle vaší potřeby.

 3.7.4 SVYHLEDAT a sečíst všechny odpovídající hodnoty v řádcích i sloupcích

Chcete-li například sečíst hodnoty, když potřebujete porovnat sloupec i řádek, abyste získali celkovou hodnotu svetru produktu v měsíci březnu, jak je ukázáno níže.

Zde můžete k provedení tohoto úkolu použít funkci SUMPRODCT.

Použijte prosím následující vzorec do buňky a poté stiskněte vstoupit klíč k získání výsledku, viz screenshot:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 VLOOKUP pro sloučení dvou tabulek na základě klíčových sloupců

Při každodenní práci při analýze dat možná budete muset shromáždit všechny potřebné informace do jediné tabulky založené na jednom nebo více klíčových sloupcích. K provedení tohoto úkolu můžete místo funkce VLOOKUP použít funkce INDEX a MATCH.

 3.8.1 VLOOKUP pro sloučení dvou tabulek na základě jednoho klíčového sloupce

Například máte dvě tabulky, první tabulka obsahuje data produktů a názvů a druhá tabulka obsahuje data produktů a objednávek, nyní chcete tyto dvě tabulky zkombinovat tak, že spojíte společný sloupec produktu do jedné tabulky.

Krok 1: Aplikujte a vyplňte následující vzorec

Použijte prosím následující vzorec do prázdné buňky. Potom přetáhněte úchyt výplně dolů k buňkám, na které chcete použít tento vzorec

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Výsledek:

Nyní získáte sloučenou tabulku se sloupcem objednávky připojeným k první tabulce na základě dat klíčového sloupce.

 3.8.2 VLOOKUP pro sloučení dvou tabulek na základě více klíčových sloupců

Pokud mají dvě tabulky, které chcete spojit, více klíčových sloupců, pro sloučení tabulek na základě těchto společných sloupců postupujte podle níže uvedených kroků.

Obecný vzorec je:

Krok 1: Použijte následující vzorec

Použijte níže uvedený vzorec do prázdné buňky, kam chcete vložit výsledek, a stiskněte Ctrl + Shift + Enter klávesy společně pro získání první shodné hodnoty, viz screenshot:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Krok 2: Doplňte vzorec do dalších buněk

Poté vyberte první buňku vzorce a přetažením úchytu výplně zkopírujte tento vzorec do dalších buněk, jak potřebujete:

3.9 SVYHLEDAT porovnávání hodnot ve více listech

Potřebovali jste někdy provést SVYHLEDAT ve více listech v Excelu? Pokud máte například tři listy s rozsahy dat a chcete z těchto listů získat konkrétní hodnoty na základě kritérií, můžete postupovat podle podrobného návodu SVYHLEDAT hodnoty ve více listech k provedení tohoto úkolu.

VLOOKUP shodné hodnoty udržují formátování buněk

Při vyhledávání odpovídajících hodnot nebude zachováno původní formátování buňky, jako je barva písma, barva pozadí, formát dat atd. Chcete-li zachovat formátování buněk nebo dat, představí tato část několik triků pro řešení úloh.

4.1 SVYHLEDAT odpovídající hodnotu a zachovat barvu buňky, formátování písma

Jak všichni víme, normální funkce SVYHLEDAT dokáže načíst odpovídající hodnotu pouze z jiného rozsahu dat. Mohou však nastat případy, kdy byste chtěli získat odpovídající hodnotu spolu s formátováním buňky, jako je barva výplně, barva písma a styl písma. V této části probereme, jak načíst odpovídající hodnoty při zachování formátování zdroje v Excelu.

Proveďte následující kroky k vyhledání a vrácení odpovídající hodnoty spolu s formátováním buněk:

Krok 1: Zkopírujte kód 1 do modulu Sheet Code Module

1. V listu obsahuje data, která chcete SVYHLEDAT, klikněte pravým tlačítkem na záložku listu a vyberte Zobrazit kód z kontextové nabídky. Viz snímek obrazovky:
   
2. V otevřeném Microsoft Visual Basic pro aplikace zkopírujte níže uvedený kód VBA do okna Kód.
3. Kód VBA 1: SVYHLEDAT pro získání formátování buňky spolu s vyhledávanou hodnotou

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Krok 2: Zkopírujte kód 2 do okna modulu

1. Ještě v Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko Vložit > Modula potom zkopírujte níže uvedený kód VBA 2 do okna modulu.
2. Kód VBA 2: SVYHLEDAT pro získání formátování buňky spolu s vyhledávanou hodnotou

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Krok 3: Vyberte možnost pro VBAproject

1. Po vložení výše uvedených kódů klikněte Tools > Reference v Microsoft Visual Basic pro aplikace okno. Poté zkontrolujte Microsoft Scripting Runtime zaškrtávací políčko Reference - VBAProject dialogové okno. Zobrazit snímky obrazovky:
2. Potom klepněte na tlačítko OK zavřete dialogové okno a poté uložte a zavřete okno kódu.

Krok 4: Zadejte vzorec pro získání výsledku

1. Nyní se vraťte do listu a použijte následující vzorec. A pak přetáhněte úchyt výplně dolů, abyste získali všechny výsledky spolu s jejich formátováním. Viz snímek obrazovky:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 Zachovejte formát data z vrácené hodnoty SVYHLEDAT

Při použití funkce SVYHLEDAT k vyhledání a vrácení hodnoty ve formátu data se vrácený výsledek může zobrazit jako číslo. Chcete-li zachovat formát data ve vráceném výsledku, měli byste do funkce TEXT uzavřít funkci SVYHLEDAT.

Krok 1: Aplikujte a vyplňte následující vzorec

Použijte níže uvedený vzorec do prázdné buňky. Potom přetažením úchytu výplně zkopírujte tento vzorec do jiných buněk.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Výsledek:

Všechna odpovídající data byla vrácena, jak je zobrazeno na níže uvedeném snímku obrazovky:

4.3 Vraťte komentář k buňce z funkce VLOOKUP

Potřebovali jste někdy načíst odpovídající data buňky a související komentář pomocí funkce VLOOKUP v aplikaci Excel, jak je znázorněno na následujícím snímku obrazovky? Pokud ano, níže uvedená uživatelsky definovaná funkce vám může pomoci tento úkol splnit.

Krok 1: Zkopírujte kód do modulu

1. Podržte stisknuté tlačítko ALT + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
2. klikněte Vložit > Modul, potom zkopírujte a vložte následující kód do okna modulu.
   Kód VBA: Vlookup a návrat odpovídající hodnoty s komentářem buňky:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Poté uložte a zavřete okno s kódem.

Krok 2: Zadejte vzorec, abyste získali výsledek

1. Nyní zadejte následující vzorec a přetažením úchytu výplně zkopírujte tento vzorec do jiných buněk. Vrátí současně odpovídající hodnoty i komentáře, viz snímek obrazovky:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 Čísla VLOOKUP uložená jako text

Mám například rozsah dat, kde je číslo ID v původní tabulce ve formátu čísla a číslo ID ve vyhledávacích buňkách je uloženo jako text, při použití normální funkce SVYHLEDAT se můžete setkat s chybou #N/A. V tomto případě můžete pro získání správných informací zabalit funkce TEXT a VALUE do funkce SVYHLEDAT. Níže je uveden vzorec, jak toho dosáhnout:

Krok 1: Aplikujte a vyplňte následující vzorec

Použijte prosím následující vzorec do prázdné buňky a poté přetažením úchytu výplně dolů zkopírujte tento vzorec.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Výsledek:

Nyní získáte správné výsledky, jak je uvedeno níže: