Note: The other languages of the website are Google-translated. Back to English
Přihlásit se  \/ 
x
or
x
Registrace  \/ 
x

or

Funkce VLOOKUP s některými základními a pokročilými příklady v aplikaci Excel

V aplikaci Excel je funkce VLOOKUP výkonnou funkcí pro většinu uživatelů aplikace Excel, která slouží k hledání hodnoty v levé části datového rozsahu a vrácení odpovídající hodnoty ve stejném řádku ze sloupce, který jste zadali, jak je uvedeno níže . Tento kurz hovoří o tom, jak používat funkci VLOOKUP s některými základními a pokročilými příklady v aplikaci Excel.

Obsah:

1. Představení funkce VLOOKUP - syntaxe a argumenty

2. Základní příklady VLOOKUP

3. Pokročilé příklady VLOOKUP

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

5. Stáhněte si ukázkové soubory VLOOKUP


Představení funkce VLOOKUP - Syntaxe a argumenty

Syntaxe funkce VLOOKUP:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

argumenty:

Vyhledávací_hodnota: Hodnota, kterou chcete vyhledat. Musí být v prvním sloupci rozsahu table_array.

Table_array: Rozsah dat nebo tabulka, kde je umístěn sloupec vyhledávací hodnoty a sloupec výsledné hodnoty.

Col_index_num: Počet sloupců, ze kterých bude vrácena odpovídající hodnota. Začíná to 1 z levého sloupce v poli tabulky.

Range_lookup: Logická hodnota, která určuje, zda tato funkce VLOOKUP vrátí přesnou shodu nebo přibližnou shodu.

  • Přibližná shoda - 1 / PRAVDA: Pokud není nalezena přesná shoda, vzorec vyhledá nejbližší shodu - největší hodnotu, která je menší než vyhledávací hodnota. V takovém případě byste měli vyhledávací sloupec seřadit vzestupně.
    = VLOOKUP (lookup_value, table_array, col_index, TRUE)
    = VLOOKUP (lookup_value, table_array, col_index, 1)
  • Přesná shoda - 0 / FALSE: Používá se k hledání hodnoty přesně stejné jako vyhledávací hodnota. Pokud není nalezena přesná shoda, bude vrácena chybová hodnota # N / A.
    = VLOOKUP (lookup_value, table_array, col_index, FALSE)
    = VLOOKUP (lookup_value, table_array, col_index, 0)

Poznámky:

  • 1. Funkce Vlookup hledá hodnotu pouze zleva doprava.
  • 2. Pokud existuje více shodných hodnot založených na vyhledávací hodnotě, pomocí funkce Vlookup bude vrácena pouze první shodná hodnota.
  • 3. Vrátí chybovou hodnotu # N / A, pokud vyhledávací hodnotu nelze najít.

Základní příklady VLOOKUP

1. Proveďte přesnou shodu Vlookup a přibližnou shodu Vlookup

V aplikaci Excel proveďte přesnou shodu Vlookup

Normálně, pokud hledáte přesnou shodu s funkcí Vlookup, stačí v posledním argumentu 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:

1. Použijte následující vzorec do prázdné buňky, kde chcete získat výsledek:

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

2. Poté přetáhněte úchyt výplně dolů do buněk, které chcete vyplnit do tohoto vzorce, a získáte výsledky podle potřeby. Viz screenshot:

Poznámky:

  • 1. Ve výše uvedeném vzorci F2 je hodnota, kterou chcete vrátit odpovídající hodnotu, A2: D7 je pole tabulky, číslo 3 je číslo sloupce, ze kterého je vrácena odpovídající hodnota, a NEPRAVDIVÉ odkazuje na přesnou shodu.
  • 2. Pokud hodnota vašeho kritéria není v datovém rozsahu nalezena, zobrazí se chybová hodnota # N / A.

Udělejte přibližnou shodu Vlookup v aplikaci Excel

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

Například pokud máte následující data rozsahu, zadané objednávky nejsou ve sloupci Objednávky, jak získat jeho nejbližší Sleva ve sloupci B?

1. Zadejte následující vzorec do buňky, kam chcete umístit výsledek:

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

2. Poté přetáhněte úchyt výplně dolů do buněk a použijte tento vzorec a získáte přibližné shody založené na daných hodnotách, viz screenshot:

Poznámky:

  • 1. Ve výše uvedeném vzorci D2 je hodnota, kterou chcete vrátit jeho relativní informace, A2: B9 je rozsah dat, číslo 2 označuje číslo sloupce, ve kterém je vrácena shodná hodnota, a TRUE odkazuje na přibližnou shodu.
  • 2. Přibližná shoda vrátí největší hodnotu, která je menší než vaše konkrétní vyhledávací hodnota.
  • 3. Chcete-li použít funkci Vlookup k získání přibližné hodnoty shody, musíte řadit sloupec úplně vlevo v rozsahu dat vzestupně, jinak vrátí nesprávný výsledek.

2. V aplikaci Outlook rozlišujte velká a malá písmena

Ve výchozím nastavení provádí funkce Vlookup rozlišování malých a velkých písmen, což znamená, že s malými a velkými písmeny zachází stejně. Někdy možná budete muset v aplikaci Excel vyhledat malá a velká písmena, laskavost vám mohou poskytnout funkce Index, Match a Exact nebo Lookup and 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.

Formule 1: Použití funkcí EXACT, INDEX, MATCH

1. Zadejte nebo zkopírujte následující vzorec pole do prázdné buňky, kde chcete získat výsledek:

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

2. Poté stiskněte tlačítko Ctrl + Shift + Enter kláves současně pro získání prvního výsledku a poté vyberte buňku vzorce, přetáhněte úchyt výplně dolů k buňkám, které chcete vyplnit do tohoto vzorce, a získáte správné výsledky, které potřebujete. Viz screenshot:

Poznámky:

  • 1. Ve výše uvedeném vzorci A2: A10 je sloupec, který obsahuje konkrétní hodnoty, ve kterých chcete vyhledat, F2 je vyhledávací hodnota, C2: C10 je sloupec, ze kterého bude výsledek vrácen.
  • 2. Pokud bylo nalezeno více shod, vrátí tento vzorec vždy první shodu.

Formule 2: Použití funkcí Lookup a Exact

1. Použijte následující vzorec do prázdné buňky, kde chcete získat výsledek:

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

2. Poté přetáhněte popisovač výplně dolů do buněk, které chcete zkopírovat tento vzorec, a získáte odpovídající hodnoty s rozlišením malých a velkých písmen, jak je uvedeno níže:

Poznámky:

  • 1. Ve výše uvedeném vzorci A2: A10 je sloupec, který obsahuje konkrétní hodnoty, ve kterých chcete vyhledat, F2 je vyhledávací hodnota, C2: C10 je sloupec, ze kterého bude výsledek vrácen.
  • 2. Pokud bylo nalezeno více shod, vrátí tento vzorec vždy poslední shodu.

3. Hodnoty vlookup v aplikaci Excel zprava doleva

Funkce Vlookup vždy vyhledá hodnotu v levém sloupci datového rozsahu a vrátí odpovídající hodnotu ze sloupce vpravo. Pokud chcete udělat reverzní Vlookup, což znamená vyhledat konkrétní hodnotu vpravo a vrátit její odpovídající hodnotu v levém sloupci, jak je znázorněno níže:

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


4. Vlookup druhou, n-tou nebo poslední odpovídající hodnotu v aplikaci Excel

Normálně, pokud je při použití funkce Vlookup nalezeno více hodnot shody, bude vrácen pouze první uzavřený záznam. V této části budu hovořit o tom, jak získat druhou, n-tou nebo poslední odpovídající hodnotu s funkcí Vlookup.

Vlookup a vrátit druhou nebo n-tu odpovídající hodnotu

Předpokládejme, že máte ve sloupci A seznam jmen, školicí kurz, který si zakoupili ve sloupci B, a nyní hledáte 2. nebo n. Školicí kurz zakoupený daným zákazníkem. Viz screenshot:

1. Chcete-li na základě daných kritérií získat druhou nebo n-tu odpovídající hodnotu, použijte do prázdné buňky následující vzorec pole:

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

2. Pak stiskněte tlačítko Ctrl + Shift + Enter klíče společně, abyste získali první výsledek, a poté vyberte buňku vzorce, přetáhněte úchyt výplně dolů do buněk, které chcete vyplnit do tohoto vzorce, a všechny druhé shodné hodnoty založené na zadaných názvech byly zobrazeny najednou, viz screenshot:

Poznámka:

  • V tomto vzorci, A2: A14 je rozsah se všemi hodnotami pro vyhledávání, B2: B14 je rozsah odpovídajících hodnot, ze kterých se chcete vrátit, E2 je vyhledávací hodnota a poslední číslo 2 označuje druhou odpovídající hodnotu, kterou chcete získat, pokud chcete vrátit třetí odpovídající hodnotu, stačí ji změnit na 3, jak potřebujete.

Vlookup a vrátit 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 Vlookup a návrat poslední odpovídající hodnoty tutoriál vám může pomoci získat podrobnosti o poslední shodné hodnotě.


5. Vlookup odpovídající hodnoty 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 uvedeno níže, v tomto případě můžete použít funkci VYHLEDAT a seřazenou tabulku.

Vlookup shoda hodnot mezi dvěma danými hodnotami nebo daty pomocí vzorce

1. Nejprve by měla být původní tabulka seřazený rozsah dat. Potom zkopírujte nebo zadejte následující vzorec do prázdné buňky:

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

2. Poté přetáhněte popisovač výplně a vyplňte tento vzorec do dalších buněk, které potřebujete, a nyní získáte všechny odpovídající záznamy založené na dané hodnotě, viz screenshot:

Poznámky:

  • 1. Ve výše uvedeném vzorci A2: A6 je rozsah menších hodnot a B2: B6 je rozsah větších čísel ve vašem datovém rozsahu, E2 je daná hodnota, kterou chcete získat odpovídající hodnotu, C2: C6 jsou data sloupce, ze kterých chcete extrahovat.
  • 2. Tento vzorec lze také použít k extrakci shodných hodnot mezi dvěma daty, jak je ukázáno níže:

Vlookup shoda hodnot mezi dvěma danými hodnotami nebo daty s užitečnou funkcí

Pokud vás výše uvedený vzorec trápí, představím zde snadný nástroj - Kutools pro Excel, S jeho VYHLEDÁVÁNÍ mezi dvěma hodnotami funkce, můžete vrátit odpovídající položku na základě konkrétní hodnoty nebo data mezi dvěma hodnotami nebo daty, aniž byste si pamatovali jakýkoli vzorec.   Klikněte a stáhněte si nyní Kutools pro Excel!


6. Použití zástupných znaků pro částečné shody ve funkci Vlookup

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

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?

1. Normální funkce Vlookup nefunguje správně, je třeba spojit odkaz na text nebo buňku se zástupným znakem, zkopírujte nebo zadejte následující vzorec do prázdné buňky:

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

2. Poté přetáhněte úchyt výplně a vyplňte tento vzorec do dalších buněk, které potřebujete, a všechna shodná skóre byla vrácena, jak je uvedeno níže:

Poznámky:

  • 1. Ve výše uvedeném vzorci E2 & ”*” je vyhledávací hodnota, hodnota v E2 a * zástupný znak („*“ označuje libovolný znak nebo libovolné znaky), A2: C11 je rozsah vyhledávání, číslo 3 sloupec, který obsahuje hodnotu, která se má vrátit.
  • 2. Vlookup při použití zástupných znaků, musíte nastavit režim přesné shody s FALSE nebo 0 pro poslední argument ve funkci Vlookup.

Tip:

1. Najděte a vraťte odpovídající hodnoty končící určitou hodnotou, použijte tento vzorec: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Chcete-li vyhledat a vrátit shodnou hodnotu na základě části textového řetězce, ať je zadaný text před, za nebo uprostřed textového řetězce, stačí spojit dva * znaky kolem odkazu na buňku nebo textu. Postupujte podle tohoto vzorce: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Hodnoty Vlookup z jiného listu

Obvykle možná 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:

1. Zadejte nebo zkopírujte níže uvedený vzorec do prázdné buňky, kde chcete získat shodné položky:

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

2. Poté přetáhněte popisovač výplně dolů do buněk, na které chcete použít tento vzorec, a podle potřeby získáte odpovídající výsledky, viz screenshot:

Poznámka: Ve výše uvedeném vzorci:

  • A2 představuje vyhledávací hodnotu;
  • list je název listu, ze kterého chcete vyhledat data, (Pokud název listu obsahuje mezery nebo interpunkční znaky, měli byste kolem názvu listu uvést jednoduché uvozovky, jinak můžete přímo použít název listu jako = VLOOKUP (A2, Datasheet! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 je rozsah dat v datovém listu, kde hledáme data;
  • číslo 3 je číslo sloupce, které obsahuje shodná data, ze kterých se chcete vrátit.

8. Hodnoty Vlookup z jiného sešitu

Tato část pojednává o vyhledávání a vrácení odpovídajících hodnot z jiného sešitu pomocí funkce Vlookup.

Například první sešit obsahuje seznamy produktů a nákladů, nyní chcete extrahovat odpovídající cenu ve druhém sešitu na základě položky produktu, jak je uvedeno níže.

1. Chcete-li načíst relativní náklady z jiného sešitu, nejprve otevřete oba sešity, které chcete použít, a pak použijte následující vzorec do buňky, kam chcete umístit výsledek:

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

2. Poté přetáhněte a zkopírujte tento vzorec do dalších buněk, které potřebujete, viz screenshot:

Poznámky:

  • 1. Ve výše uvedeném vzorci:
    B2 představuje vyhledávací hodnotu;
    [Seznam produktů.xlsx] List1 je název sešitu a listu, ze kterého chcete vyhledat data, (Odkaz na sešit je uzavřen v hranatých závorkách a celý sešit + list je uzavřen do jednoduchých uvozovek);
    A2: B6 je rozsah dat v listu jiného sešitu, kde hledáme data;
    číslo 2 je číslo sloupce, které obsahuje shodná data, ze kterých se chcete vrátit.
  • 2. Pokud je vyhledávací sešit uzavřen, ve vzorci se zobrazí úplná cesta k souboru pro vyhledávací sešit, jak ukazuje následující snímek obrazovky:

9. Vlookup a vrátit prázdný nebo konkrétní text namísto 0 nebo # N / A chybová hodnota

Normálně, když použijete funkci vlookup k vrácení odpovídající hodnoty, pokud je vaše odpovídající buňka prázdná, vrátí 0 a pokud vaše odpovídající hodnota není nalezena, zobrazí se chybová hodnota # N / A, jak je uvedeno níže. Namísto zobrazení hodnoty 0 nebo # N / A s prázdnou buňkou nebo jinou hodnotou, která se vám líbí, toto Vlookup Chcete-li vrátit prázdnou nebo specifickou hodnotu namísto 0 nebo N / A tutoriál vám může udělat laskavost krok za krokem.


Pokročilé příklady VLOOKUP

1. Obousměrné vyhledávání s funkcí Vlookup (Vlookup v řádku a sloupci)

Někdy možná budete muset provést dvourozměrné vyhledávání, což znamená Vlookup v řádku i sloupci současně. Řekněme, že pokud máte následující rozsah dat, a nyní možná budete muset získat hodnotu pro konkrétní produkt v zadaném čtvrtletí. Tato část představí nějaký vzorec pro řešení této úlohy v aplikaci Excel.

Formule 1: Použití funkcí VLOOKUP a MATCH

V aplikaci Excel můžete použít kombinaci funkcí VLOOKUP a MATCH k oboustrannému 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(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Poznámka: Ve výše uvedeném vzorci:

  • H1: vyhledávací hodnota ve sloupci, podle kterého chcete získat odpovídající hodnotu;
  • A2: E6: rozsah dat včetně záhlaví řádků;
  • H2: vyhledávací hodnota v řádku, ze kterého chcete získat odpovídající hodnotu;
  • A1: E1: buňky záhlaví sloupců.

Vzorec 2: Použití funkcí INDEX a MATCH

Zde je další vzorec, který vám také pomůže provést dvourozměrné vyhledávání, použijte níže uvedený vzorec a stiskněte vstoupit klíč k dosažení požadovaného výsledku.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Poznámka: Ve výše uvedeném vzorci:

  • B2: E6: rozsah dat, ze kterého se má shodná položka vrátit;
  • H1: vyhledávací hodnota ve sloupci, podle kterého chcete získat odpovídající hodnotu;
  • A2: A6: záhlaví řádků obsahuje produkt, který chcete vyhledat.
  • H2: vyhledávací hodnota v řádku, ze kterého chcete získat odpovídající hodnotu;
  • B1: E1: záhlaví sloupců obsahují čtvrtinu, kterou chcete hledat.

2. Hodnota shody vlookup 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? Funkce LOOKUP nebo MATCH a INDEX v aplikaci Excel vám pomohou rychle a snadno vyřešit tuto úlohu.

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.

Formule 1: Používání funkce VYHLEDÁVÁNÍ

Použijte následující vzorec do buňky, kde chcete získat výsledek, a poté stiskněte klávesu Enter, viz screenshot:

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

Poznámky:

  • 1. Ve výše uvedeném vzorci:
    A2: A12 = G1: znamená vyhledávat kritéria G1 v rozsahu A2: A12;
    B2: B12 = G2: znamená vyhledávat kritéria G2 v rozsahu B2: B12;
    D2: D12: rozsah, kterému chcete vrátit odpovídající hodnotu.
  • 2. Pokud máte více než dvě kritéria, stačí do vzorce připojit další kritéria, například: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Vzorec 2: Použití funkcí INDEXT A MATCH

Kombinace funkce Index a Match lze také použít k vrácení shodné hodnoty na základě více kritérií. Zkopírujte nebo zadejte následující vzorec:

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

Poté stiskněte Ctrl + Shift + Enter společně, abyste získali relativní hodnotu podle potřeby. Viz screenshot:

Poznámky:

  • 1. Ve výše uvedeném vzorci:
    A2: A12 = G1: znamená vyhledávat kritéria G1 v rozsahu A2: A12;
    B2: B12 = G2: znamená vyhledávat kritéria G2 v rozsahu B2: B12;
    D2: D12: rozsah, kterému chcete vrátit odpovídající hodnotu.
  • 2. Pokud máte více než dvě kritéria, stačí do vzorce připojit nová kritéria, například: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup vrátí více hodnot shody s jednou nebo více podmínkami

V aplikaci Excel vyhledá funkce Vlookup hodnotu a vrátí první odpovídající hodnotu, pouze pokud bylo nalezeno více odpovídajících hodnot. 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 hodnot shody s jednou nebo více podmínkami v sešitu.

Vlookup všechny odpovídající hodnoty založené na jedné nebo více podmínkách vodorovně

Vlookup horizontálně všechny odpovídající hodnoty založené na jedné podmínce:

Chcete-li Vlookup a vrátit všechny odpovídající hodnoty na základě jedné konkrétní hodnoty vodorovně, obecný vzorec je:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Poznámka: m je číslo řádku první buňky v rozsahu návratnosti minus 1.
      n je číslo sloupce první buňky vzorce minus 1.

1. Použijte následující vzorec do prázdné buňky a stiskněte Ctrl + Shift + Enter klávesy společně pro získání první shodné hodnoty, viz screenshot:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. A pak vyberte první buňku vzorce a přetáhněte popisovač výplně do pravých buněk, dokud se nezobrazí prázdná buňka a nebudou extrahovány všechny odpovídající položky, viz screenshot:

Tip:

Pokud jsou ve vráceném seznamu duplicitní shodné hodnoty, duplikáty ignorujte, použijte prosím tyto vzorce a stiskněte vstoupit získat první výsledek: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Pokračujte zadáním tohoto vzorce: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") do buňky vedle prvního výsledku a poté stiskněte Ctrl + Shift + Enter klávesy dohromady, abyste získali druhý výsledek, pak přetáhněte tento vzorec do pravých buněk, abyste získali všechny ostatní odpovídající hodnoty, dokud se nezobrazí prázdná buňka, viz screenshot:


Vlookup všechny odpovídající hodnoty založené na dvou nebo více podmínkách vodorovně:

Pro Vlookup a vrácení všech odpovídajících hodnot na základě konkrétnějších hodnot vodorovně je obecný vzorec:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Poznámka: m je číslo řádku první buňky v rozsahu návratnosti minus 1.
      n je číslo sloupce první buňky vzorce minus 1.

1. Použijte následující vzorec do prázdné buňky, do které chcete výsledek odeslat:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Poté vyberte buňku vzorce a přetáhněte popisovač výplně do pravých buněk, dokud se nezobrazí prázdná buňka a nebudou vráceny všechny odpovídající hodnoty založené na konkrétních kritériích, viz screenshot:

Poznámka: Pro více kritérií, stačí se připojit lookup_value a lookup_range do vzorce, jako například: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Vlookup všechny odpovídající hodnoty na základě jedné nebo více podmínek svisle

Vlookup všechny odpovídající hodnoty založené na jedné podmínce svisle:

Chcete-li Vlookup a vrátit všechny odpovídající hodnoty na základě jedné konkrétní hodnoty svisle, obecný vzorec je:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Poznámka: m je číslo řádku první buňky v rozsahu návratnosti minus 1.
      n je číslo řádku první buňky vzorce minus 1.

1. Zkopírujte nebo zadejte následující vzorec do buňky, kde chcete získat výsledek, a poté stiskněte Ctrl + Shift + Enter klávesy společně pro získání první shodné hodnoty, viz screenshot:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Poté vyberte první buňku vzorce a přetáhněte popisovač výplně dolů do dalších buněk, dokud se nezobrazí prázdná buňka a ve sloupci nebudou uvedeny všechny odpovídající položky, viz screenshot:

Tip:

Chcete-li ignorovat duplikáty ve vrácených shodných hodnotách, použijte tyto vzorce: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Pak stiskněte tlačítko Ctrl + Shift + Enter klávesy dohromady, abyste získali první shodnou hodnotu, a pak přetáhněte tuto buňku vzorce dolů do dalších buněk, dokud se nezobrazí prázdná buňka, a získáte výsledek, jak potřebujete:


Vlookup všechny odpovídající hodnoty založené na dvou nebo více podmínkách svisle:

Obecný vzorec pro Vlookup a vrácení všech odpovídajících hodnot na základě konkrétnějších hodnot svisle je:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Poznámka: m je číslo řádku první buňky v rozsahu návratnosti minus 1.
      n je číslo řádku první buňky vzorce minus 1.

1. Zkopírujte níže uvedený vzorec do prázdné buňky a stiskněte Ctrl + Shift + Enter dohromady, abyste získali první shodnou položku.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Poté přetáhněte buňku vzorce dolů do dalších buněk, dokud se nezobrazí prázdná buňka, viz screenshot:

Poznámka: Pro více kritérií, stačí se připojit lookup_value a lookup_range do vzorce, jako například: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup všechny odpovídající hodnoty založené na dvou nebo více podmínkách do jedné buňky

Pokud chcete Vlookup a vrátit více shodných hodnot do jedné buňky se specifikovaným oddělovačem, nová funkce TEXTJOIN vám pomůže rychle a snadno vyřešit tuto úlohu.

Vlookup všechny odpovídající hodnoty založené na jedné podmínce do jedné buňky:

Použijte následující jednoduchý vzorec do prázdné buňky a stiskněte Ctrl + Shift + Enter klíče společně pro získání výsledku:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Tip:

Chcete-li ignorovat duplikáty ve vrácených shodných hodnotách, použijte tyto vzorce: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Vlookup všechny odpovídající hodnoty založené na dvou nebo více podmínkách do jedné buňky:

Pro řešení více podmínek při vracení všech odpovídajících hodnot do jedné buňky použijte níže uvedený vzorec a stiskněte Ctrl + Shift + Enter klíče společně pro získání výsledku:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Poznámky:

1. Funkce TEXTJOIN je k dispozici pouze v Excel 2019 a Office 365.

2. Pokud používáte Excel 2016 a dřívější verze, použijte funkci definovanou uživatelem v následujících článcích:


4. Vlookup vrátí celý nebo celý řádek uzavřené buňky

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

1. Zkopírujte nebo zadejte následující vzorec do prázdné buňky, do které chcete výsledek odeslat, a stiskněte vstoupit klíč k získání první hodnoty.

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

2. Poté přetáhněte buňku vzorce na pravou stranu, dokud se nezobrazí data celého řádku, viz screenshot:

Poznámka: Ve výše uvedeném vzorci, F2 je vyhledávací hodnota, na které chcete vrátit celý řádek, A1: D12 je rozsah dat, který chcete použít, A1 označuje číslo prvního sloupce ve vašem datovém rozsahu.

Tip:

Pokud bylo nalezeno více řádků založených na shodné hodnotě, pro vrácení všech odpovídajících řádků použijte tento vzorec: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), a poté stiskněte tlačítko Ctrl + Shift + Enter klávesy společně pro získání prvního výsledku, pak přetáhněte úchyt výplně doprava do buněk, viz screenshot:

A potom přetáhněte úchyt výplně dolů přes buňky, abyste získali všechny odpovídající řádky, jak je uvedeno níže:


5. Proveďte více funkcí Vlookup (vnořených Vlookup) v aplikaci Excel

Někdy můžete chtít vyhledat hodnoty ve více tabulkách, pokud některá z tabulek obsahuje danou vyhledávací hodnotu, jak je ukázáno níže, v tomto případě můžete kombinovat jednu nebo více funkcí Vlookup společně s funkcí IFERROR k provedení vícenásobného vyhledávání.

Obecný vzorec pro vnořenou funkci Vlookup je:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Poznámka:

  • lookup_value: hodnota, kterou hledáte;
  • Table1, Table2, Table3, ...: tabulky, ve kterých existuje vyhledávací hodnota a návratová hodnota;
  • s: číslo sloupce v tabulce, ze kterého chcete vrátit odpovídající hodnotu.
  • 0: Používá se pro přesnou shodu.

1. Použijte následující vzorec do prázdné buňky, kam chcete umístit výsledek:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Poté přetáhněte úchyt výplně dolů do buněk, na které chcete použít tento vzorec, a všechny shodné hodnoty byly vráceny, jak je uvedeno níže:

Poznámky:

  • 1. Ve výše uvedeném vzorci J3 je hodnota, kterou hledáte; A3: B7, D3: E7, G3: H7 jsou rozsahy tabulek, ve kterých existuje vyhledávací hodnota a návratová hodnota; Číslo 2 je číslo sloupce v rozsahu, ze kterého se má vrátit odpovídající hodnota.
  • 2. Pokud vyhledávací hodnotu nelze najít, zobrazí se chybová hodnota. Chcete-li chybu nahradit čitelným textem, použijte tento vzorec: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup zkontroluje, zda hodnota existuje na základě dat seznamu v jiném sloupci

Funkce Vlookup vám také pomůže zkontrolovat, zda existují hodnoty na základě jiného seznamu, například pokud chcete vyhledat jména ve sloupci C a vrátit pouze Ano nebo Ne, pokud je název nalezen nebo ne ve sloupci A, jak je uvedeno níže zobrazeno.

1. Do prázdné buňky použijte následující vzorec:

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

2. Poté přetáhněte úchyt výplně dolů do buněk, které chcete vyplnit do tohoto vzorce, a získáte výsledek, jak potřebujete, viz screenshot:

Poznámka: Ve výše uvedeném vzorci, C2 je vyhledávací hodnota, kterou chcete zkontrolovat; A2: A10 je seznam rozsahu, ve kterém budou nalezeny vyhledávací hodnoty; číslo 1 je číslo sloupce, ze kterého chcete načíst hodnotu ve vašem rozsahu.


7. Vlookup a sečtěte všechny odpovídající hodnoty v řádcích nebo sloupcích

Pokud pracujete s číselnými údaji, někdy při extrahování shodných hodnot z tabulky možná budete muset sečíst čísla v několika sloupcích nebo řádcích. Tato část představí některé vzorce pro dokončení této úlohy v aplikaci Excel.

Vlookup a součet všech shodných hodnot v řádku nebo více řádcích

Předpokládejme, že máte seznam produktů s prodejem několik měsíců, jak je ukázáno níže, nyní je třeba sečíst všechny objednávky ve všech měsících na základě daných produktů.

Vlookup a sečtěte první shodné hodnoty v řadě:

1. Zkopírujte nebo zadejte následující vzorec do prázdné buňky a stiskněte Ctrl + Shift + Enter dohromady, abyste získali první výsledek.

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

2. Poté přetáhněte úchyt výplně dolů a zkopírujte tento vzorec do dalších buněk, které potřebujete, a všechny hodnoty v řádku první odpovídající hodnoty byly sečteny dohromady, viz screenshot:

Poznámka: Ve výše uvedeném vzorci: H2 je buňka obsahující hodnotu, kterou hledáte; A2: F9 je rozsah dat (bez hlaviček sloupců), který zahrnuje vyhledávací hodnotu a odpovídající hodnoty; Číslo 2,3,4,5,6 {} jsou čísla sloupců použitá k výpočtu součtu rozsahu.


Vlookup a součet všech shodných hodnot ve více řádcích:

Výše uvedený vzorec může součet hodnot v řadě pouze pro první shodnou hodnotu. Pokud chcete sečíst všechny shody ve více řádcích, použijte následující vzorec a potom přetáhněte popisovač výplně dolů do buněk, které chcete použít tento vzorec, a získáte požadovaný výsledek, který potřebujete, viz screenshot:

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

Poznámka: Ve výše uvedeném vzorci: H2 je vyhledávací hodnota, kterou hledáte; A2: A9 je záhlaví řádků, které obsahují vyhledávací hodnotu; B2: F9 rozsah dat číselných hodnot, které chcete sečíst.


Vlookup a součet všech shodných hodnot ve sloupci nebo více sloupcích

Vlookup a sečtěte první shodné hodnoty ve sloupci:

Pokud chcete sečíst celkovou hodnotu pro konkrétní měsíce, jak je znázorněno na následujícím obrázku.

Použijte níže uvedený vzorec do prázdné buňky a potom přetáhněte popisovač výplně dolů a zkopírujte tento vzorec do dalších buněk, nyní byly sečteny první shodné hodnoty založené na konkrétním měsíci ve sloupci, viz screenshot:

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

Poznámka: Ve výše uvedeném vzorci: H2 je vyhledávací hodnota, kterou hledáte; B1: F1 je záhlaví sloupců, které obsahují vyhledávací hodnotu; B2: F9 rozsah dat číselných hodnot, které chcete sečíst.


Vlookup a sečíst všechny shodné hodnoty ve více sloupcích:

Chcete-li Vlookup a sečíst všechny shodné hodnoty ve více sloupcích, měli byste použít následující vzorec:

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

Poznámka: Ve výše uvedeném vzorci: H2 je vyhledávací hodnota, kterou hledáte; B1: F1 je záhlaví sloupců, které obsahují vyhledávací hodnotu; B2: F9 rozsah dat číselných hodnot, které chcete sečíst.


Vlookup a sečtěte první nebo všechny uzavřené hodnoty pomocí výkonné funkce

Možná si výše uvedené vzorce obtížně pamatujete, v tomto případě doporučím praktickou funkci - Vyhledávání a součet of Kutools pro Excel, s touto funkcí můžete dosáhnout výsledku co nejjednodušší.    Klikněte a stáhněte si nyní Kutools pro Excel!


Vlookup a součet všech shodných hodnot 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.

Použijte následující vzorec do buňky a poté stiskněte klávesu Enter pro získání výsledku, viz screenshot:

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

Poznámka: Ve výše uvedeném vzorci: B2: F9 je rozsah dat číselných hodnot, které chcete sečíst; B1: F1 is záhlaví sloupců obsahují vyhledávací hodnotu, na které chcete sečíst; I2 je vyhledávací hodnota v hlavičkách sloupců, které hledáte; A2: A9 je záhlaví řádků, které obsahují vyhledávací hodnotu, na které chcete sečíst; H2 je vyhledávací hodnota v hlavičkách řádků, které hledáte.


8. Vlookup sloučí dvě tabulky na základě jednoho nebo více 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 jedné tabulky na základě jednoho nebo více klíčových sloupců. K vyřešení této úlohy vám může udělat laskavost také funkce Vlookup.

Vlookup ke sloučení dvou tabulek na základě jednoho sloupce klíče

Například máte dvě tabulky, první tabulka obsahuje data produktů a názvů a druhá tabulka obsahuje produkty a objednávky, nyní chcete tyto dvě tabulky zkombinovat porovnáním společného sloupce produktu do jedné tabulky.

Formule 1: Použití funkce VLOOKUP

Chcete-li sloučit dvě tabulky do jedné na základě sloupce klíče, použijte následující vzorec do prázdné buňky, kde chcete získat výsledek, a poté přetáhněte popisovač výplně dolů do buněk, na které chcete použít tento vzorec, získejte sloučenou tabulku s připojením sloupce objednávky k datům první tabulky na základě údajů sloupce klíče.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

Poznámka: Ve výše uvedeném vzorci, A2 je hodnota, kterou hledáte, E2: F8 je vyhledávací tabulka, číslo 2 je číslo sloupce v tabulce, ze kterého se má načíst hodnota.

Vzorec 2: Použití funkcí INDEX a MATCH

Pokud vaše společná data na pravé straně a vrácená data v levém sloupci v druhé tabulce, ke sloučení sloupce objednávky, funkce Vlookup není schopna vykonat úlohu. Chcete-li vyhledat zprava doleva, můžete pomocí funkcí INDEX a MATCH nahradit funkci Vlookup.

Zkopírujte nebo zadejte níže uvedený vzorec do prázdné buňky, poté zkopírujte vzorec dolů do sloupce a sloupec objednávky byl připojen k první tabulce, viz screenshot:

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

Poznámka: Ve výše uvedeném vzorci, A2 je vyhledávací hodnota, kterou hledáte, E2: E8 je rozsah dat, která chcete vrátit, F2: F8 je rozsah vyhledávání, který obsahuje hodnotu vyhledávání.


Vlookup ke sloučení dvou tabulek na základě více klíčových sloupců

Pokud mají dvě tabulky, ke kterým se chcete připojit, více klíčových sloupců, můžete ke sloučení tabulek na základě těchto společných sloupců pomoci funkce INDEX a MATCH.

Obecný vzorec pro sloučení dvou tabulek na základě více klíčových sloupců je:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. Použijte následující vzorec do prázdné buňky, kam chcete umístit výsledek, a potom 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)

Poznámka: Ve výše uvedeném vzorci, co představují odkazy na buňky, jak je ukázáno níže:

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

Tipy: V aplikaci Excel 2016 a novějších verzích můžete také použít Dotaz na napájení funkce sloučit dvě nebo více tabulek do jedné na základě klíčových sloupců. Kliknutím zobrazíte podrobné informace krok za krokem.

9. Hodnoty shody Vlookup na více pracovních listech

Zkoušeli jste někdy hodnoty Vlookup na více pracovních listech? Předpokládám, že mám následující tři pracovní listy s rozsahem dat, a teď chci získat část odpovídajících hodnot na základě kritérií z těchto tří pracovních listů, abych získal výsledek, jak je uvedeno níže. V tomto případě Hodnoty Vlookup ve více pracovních listech tutoriál vám může udělat laskavost krok za krokem.


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

1. Vlookup pro získání formátování buněk (barva buňky, barva písma) spolu s vyhledávací hodnotou

Jak všichni víme, normální funkce Vlookup nám může pomoci vrátit shodnou hodnotu z jiného rozsahu dat, ale někdy můžete chtít vrátit odpovídající hodnotu spolu s formátováním buňky, jako je barva výplně, barva písma, styl písma jak je ukázáno níže. Tato část bude hovořit o tom, jak získat formátování buněk s vrácenou hodnotou v aplikaci Excel.

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

1. V listu obsahuje data, která chcete Vlookup, klikněte pravým tlačítkem na kartu 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.

Kód VBA 1: Vlookup pro získání formátování buněk spolu s vyhledávací hodnotou

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

3. Stále 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.

Kód VBA 2: Vlookup pro získání formátování buněk spolu s vyhledávací hodnotou

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. Po vložení výše uvedených kódů klikněte na Tools > Reference v Microsoft Visual Basic pro aplikace okno. Poté zkontrolujte Microsoft Script Runtime zaškrtávací políčko Reference - VBAProject dialogové okno. Zobrazit snímky obrazovky:

5. Potom klepněte na tlačítko OK zavřete dialogové okno a poté uložte a zavřete okno kódu, vraťte se zpět do listu a poté použijte tento vzorec: =LookupKeepFormat(E2,$A$1:$C$10,3) do prázdné buňky, kam chcete výsledek odeslat, a poté stiskněte klávesu Enter. Viz screenshot:

Poznámka: Ve výše uvedeném vzorci, E2 je hodnota, kterou vyhledáte, A1: C10 je rozsah tabulky a číslo 3 je číslo sloupce tabulky, které chcete vrátit odpovídající hodnotu.

6. Poté vyberte první buňku výsledků a 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.


2. Zachovejte formát data z vrácené hodnoty Vlookup

Normálně se při použití funkce Vloook k vyhledání a vrácení hodnoty shodného formátu data zobrazí některý formát čísel, jak je uvedeno níže. Chcete-li zachovat formát data z vráceného výsledku, měli byste funkci TEXT přiložit k funkci Vlookup.

Použijte prosím následující vzorec do prázdné buňky a poté přetáhněte popisovač výplně a zkopírujte tento vzorec do dalších buněk a všechna shodná data byla vrácena, jak je znázorněno níže:

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

Poznámka: Ve výše uvedeném vzorci, E2 je hodnota vzhledu, A2: C9 je rozsah vyhledávání, číslo 3 je číslo sloupce, u kterého chcete vrátit hodnotu, mm/dd/yyy je formát data, který chcete zachovat.


3. Vlookup a návrat odpovídající hodnoty s komentářem buňky

Už jste někdy zkoušeli Vlookup vrátit nejen shodná data buněk, ale také komentář k buňce v aplikaci Excel, jak ukazuje následující snímek obrazovky? K vyřešení tohoto úkolu vám může poskytnout laskavost níže uvedená funkce definovaná uživatelem.

1. Podržte 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, zadejte tento vzorec: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) do prázdné buňky k vyhledání výsledku a poté přetažením úchytky výplně zkopírujte tento vzorec do dalších buněk, nyní se shodné hodnoty i komentáře vrátí najednou, viz screenshot:

Poznámka: Ve výše uvedeném vzorci, D2 je vyhledávací hodnota, kterou chcete vrátit odpovídající hodnotu, A2: B9 je datová tabulka, kterou chcete použít, číslo 2 je číslo sloupce, které obsahuje odpovídající hodnotu, kterou chcete vrátit.


4. Zabývejte se textem a reálnými čísly ve Vlookup

Například mám řadu dat, ID číslo v původní tabulce je formát čísla, ve vyhledávací buňce, která je uložena jako text, se při použití normální funkce Vlookup zobrazí výsledek chyby # N / A, jak je uvedeno níže zobrazeno. V tomto případě, jak byste mohli získat správné informace, pokud mají vyhledávací číslo a původní číslo v tabulce jiný datový formát?

Pro práci s textem a reálnými čísly ve funkci Vlookup použijte následující vzorec do prázdné buňky a poté přetáhněte popisovač výplně dolů a zkopírujte tento vzorec a získáte správné výsledky, jak je uvedeno níže:

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

Poznámky:

  • 1. Ve výše uvedeném vzorci D2 je vyhledávací hodnota, kterou chcete vrátit odpovídající hodnotu, A2: B8 je datová tabulka, kterou chcete použít, číslo 2 je číslo sloupce, které obsahuje odpovídající hodnotu, kterou chcete vrátit.
  • 2. Tento vzorec funguje také dobře, pokud si nejste jisti, kde máte čísla a kde máte text.

Stáhněte si ukázkové soubory VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
  • Sloučit buňky / řádky / sloupce a uchovávání údajů; Rozdělit obsah buněk; Zkombinujte duplicitní řádky a součet / průměr... Zabraňte duplicitním buňkám; Porovnat rozsahy...
  • Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
  • Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
  • Oblíbené a rychlé vkládání vzorců„Rozsahy, grafy a obrázky; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
  • Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
  • Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
  • Seskupování kontingenčních tabulek podle číslo týdne, den v týdnu a další ... Zobrazit odemčené, zamčené buňky různými barvami; Zvýrazněte buňky, které mají vzorec / název...
karta kte 201905
  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50% a omezuje stovky kliknutí myší každý den!
officetab dno
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.