seychellesartprojects.org

Vlookup vzorec funguje pouze tehdy, když se pole tabulky ve vzorci nezmění, ale pokud je do tabulky vložen nový sloupec nebo je sloupec odstraněn, vzorec dává nesprávný výsledek nebo odráží chybu, aby vzorec byl bezchybný v v takových dynamických situacích používáme funkci shody, abychom skutečně porovnali index dat a vrátili skutečný výsledek.

Zkombinujte SVÉ VYHLEDÁVÁNÍ se shodou

Vzorec vlookup je nejčastěji používanou funkcí, která se používá k hledání a vrácení stejné hodnoty v zadaném indexu sloupce nebo hodnoty z jiného indexu sloupce s odkazem na odpovídající hodnotu z prvního sloupce. Hlavní výzvou při používání vlookup je, že zadaný index sloupce je statický a nemá dynamickou funkčnost. Zvláště když pracujete na více kritériích, která vyžadují ruční změnu indexu referenčního sloupce. Tím je tato potřeba splněna použitím vzorce „MATCH“ pro lepší uchopení nebo kontrolu často se měnícího indexu sloupců ve vzorci VLOOKUP.

VLookup a Match Formula

# 1 - vzorec VLOOKUP

Vzorec funkce VLOOKUP v aplikaci Excel

Zde jsou všechny argumenty, které je třeba zadat, povinné.

• Lookup_value - Zde je třeba zadat referenční buňku nebo text s uvozovkami, které budou identifikovány v rozsahu sloupců.
• Pole tabulky -   Tento argument vyžaduje, aby byl zadán rozsah tabulky, kde by měla být prohledána Lookup_value a data, která mají být načtena, umístěna v konkrétním rozsahu sloupců.
• Col_index_num - V tomto argumentu je třeba zadat číslo indexu sloupce nebo počet sloupců z referenčního prvního sloupce, ze kterého je třeba vytáhnout odpovídající hodnotu ze stejné pozice jako hodnota hledaná v prvním sloupci.
• [Range_lookup] - Tento argument poskytne dvě možnosti.
• TRUE - Přibližná shoda: - Argument lze zadat buď jako TRUE, nebo jako číselnou hodnotu „1“, která vrací přibližnou shodu odpovídající referenčnímu sloupci nebo prvnímu sloupci. Kromě toho musí být hodnoty v prvním sloupci pole tabulky seřazeny vzestupně.
• FALSE - Přesná shoda: - Zde může být zadávaný argument buď FALSE, nebo numerický „0“. Tato možnost vrátí pouze přesnou shodu hodnoty odpovídající identifikaci z pozice v rozsahu prvního sloupce. Pokud neprohledáte hodnotu z prvního sloupce, vrátí se chybová zpráva „# N / A“.

# 2 - Match Formula

Funkce shody vrací pozici buňky hodnoty zadané pro dané pole tabulky.

Všechny argumenty v syntaxi jsou povinné.

• Lookup_value - Zde zadaným argumentem může být buď odkaz na buňku hodnoty, nebo textový řetězec s uvozovkami, jehož polohu buňky je třeba stáhnout.
• Lookup_array - Je nutné zadat rozsah pole pro tabulku, jejíž hodnotu nebo obsah buňky chcete identifikovat.
• [typ shody] - Tento argument poskytuje tři možnosti, jak je vysvětleno níže.
• „1-Méně než“ - Zde je zadávaný argument číselný „1“, který vrátí hodnotu, která je menší nebo rovna vyhledávací hodnotě. A také vyhledávací pole musí být seřazeno vzestupně.
• „0-Přesná shoda“ - Zde by měl být zadávaný argument číselný „0“. Tato možnost vrátí přesnou pozici odpovídající vyhledávací hodnoty. Vyhledávací pole však může být v libovolném pořadí.
• „-1-Větší než“ -  Zadaný argument by měl být číselný „-1“. Třetí možnost najde nejmenší hodnotu, která je větší nebo rovna vyhledávací hodnotě. Zde musí být objednávka vyhledávacího pole umístěna v sestupném pořadí.

# 3 - VLOOKUP s MATCH Formula

= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])

Jak používat VLOOKUP se shodným vzorcem v aplikaci Excel?

Níže uvedený příklad pomůže pochopit fungování vzorce vlookup a shody při sestavování.

Tento VLookup se shodnou šablonou Excel si můžete stáhnout zde - VLookup se shodnou šablonou Excel

Zvažte níže uvedenou tabulku s údaji, která popisuje specifikace daného vozidla, které má být zakoupeno.

Abychom získali jasnost kombinované funkce pro vlookup a funkci shody, nechte nás pochopit, jak funguje jednotlivý vzorec, a poté, když se dáme dohromady, dosáhneme výsledků shody vlookup.

Krok 1 - Použijme vzorec vlookup na individuální úrovni, abychom dospěli k výsledku.

Výstup je uveden níže:

Zde se vyhledávací hodnota označuje jako $ B9, což je model „E“, a vyhledávací pole se udává jako rozsah datové tabulky s absolutní hodnotou „$“, index sloupce se označuje jako sloupec „4“, což je počet pro sloupec „Typ“ a vyhledávání rozsahu dostane přesnou shodu.

Pro vrácení hodnoty pro sloupec „Palivo“ se tedy použije následující vzorec .

Výstup je uveden níže:

Zde vyhledávací hodnota s absolutním řetězcem „$“ použitá pro vyhledávací hodnotu a lookup_array pomáhá opravit referenční buňku, i když je vzorec kopírován do jiné buňky. Ve sloupci „Palivo“ musíme změnit index sloupce na „5“, protože se mění hodnota, ze které je potřeba data načíst.

Krok 2 -  Nyní použijeme vzorec shody k načtení pozice pro danou vyhledávací hodnotu.

Výstup je uveden níže:

Jak je vidět na výše uvedeném snímku obrazovky, zde se pokoušíme načíst pozici sloupce z pole tabulky. V takovém případě se číslo sloupce, které má být vytaženo, označuje jako buňka C8, což je sloupec „Typ“, a rozsah vyhledávání, který má být prohledán, je uveden jako rozsah záhlaví sloupců a typu shody je dána přesná shoda jako „ 0 ”.

Níže uvedená tabulka tedy poskytne požadovaný výsledek pro polohy sloupce „Palivo“.

Nyní je sloupec, který má být prohledáván, označen jako buňka D8 a požadovaný index sloupce je vrácen jako „5“.

Krok # 3 - Nyní se ve funkci vlookup použije vzorec shody k získání hodnoty z identifikované polohy sloupce.

Výstup je uveden níže:

Ve výše uvedeném vzorci je funkce shody místo parametru indexu sloupce funkce vlookup. Zde funkce shody identifikuje referenční buňku vyhledávací hodnoty „C8“ a vrátí číslo sloupce přes dané pole tabulky. Tato pozice sloupce bude sloužit účelu jako vstup do argumentu indexu sloupce ve funkci vlookup. Který zase pomůže vlookup identifikovat hodnotu, která má být vrácena z výsledného indexového čísla sloupce?

Podobně jsme aplikovali vlookup se shodným vzorcem také pro sloupec „Palivo“.

Výstup je uveden níže:

Můžeme tak použít tuto kombinovanou funkci i pro další sloupce „Typ“ a „Palivo“.

Věci k zapamatování

• VLOOKUP lze použít k vyhledávacím hodnotám pouze na jeho nejpřednější levé straně. Jakékoli hodnoty přítomné k prohledání na pravé straně datové tabulky vrátí chybovou hodnotu „# N / A“.
• Rozsah table_array zadaný v druhém argumentu by měl být absolutní odkaz na buňku „$“, tím se zachová fixní rozsah pole tabulky při použití vzorce pro vyhledávání na jiné buňky, jinak se referenční buňky pro rozsah pole tabulky přesunou na další buňku odkaz.
• Hodnota zadaná do vyhledávací hodnoty by neměla být menší než nejmenší hodnota v prvním sloupci pole tabulky, jinak funkce vrátí chybovou hodnotu „# N / A“.
• Před použitím přibližné shody „TRUE“ nebo „1“ v posledním argumentu nezapomeňte vždy seřadit pole tabulky ve vzestupném pořadí.
• Funkce shody vrací pouze pozici hodnoty v poli tabulky vlookup a nevrací hodnotu.
• V případě, že funkce shody není schopna identifikovat polohu vyhledávací hodnoty v poli tabulky, vrátí vzorec v chybové hodnotě „# N / A“.
• Funkce Vlookup a shoda nerozlišují velká a malá písmena při porovnávání vyhledávací hodnoty s odpovídající textovou hodnotou v poli tabulky.