Vzorec Vlookup funguje, iba ak sa pole tabuľky vo vzorci nezmení, ale ak je do tabuľky vložený nový stĺpec alebo je stĺpec vymazaný, vzorec dáva nesprávny výsledok alebo odráža chybu, aby vzorec bol bezchybný v v takýchto dynamických situáciách používame funkciu zhody na to, aby sme skutočne porovnali index údajov a vrátili skutočný výsledok.
Kombinujte VLOOKUP s funkciou Match
Vzorec vlookup je najbežnejšie používanou funkciou, ktorá sa používa na vyhľadávanie a vrátenie rovnakej hodnoty v zadanom indexe stĺpca alebo hodnoty z iného indexu stĺpca s odkazom na zhodnú hodnotu z prvého stĺpca. Hlavnou výzvou pri používaní vlookup je, že zadaný index stĺpca je statický a nemá dynamickú funkčnosť. Najmä keď pracujete na viacerých kritériách, ktoré si vyžadujú manuálnu zmenu indexu referenčného stĺpca. Preto je táto potreba splnená použitím vzorca „MATCH“ na lepšie uchopenie alebo kontrolu často sa meniaceho indexu stĺpcov vo vzorci VLOOKUP.
Vzhľad a vzhľad
# 1 - Vzorec VLOOKUP
Vzorec funkcie VLOOKUP v programe Excel
Tu sú všetky argumenty, ktoré sa majú zadať, povinné.
- Lookup_value - Tu by sa mala zadať referenčná bunka alebo text s úvodzovkami, aby sa dali identifikovať v rozsahu stĺpcov.
- Pole tabuľky - Tento argument vyžaduje, aby bol zadaný rozsah tabuľky, kde by sa mala vyhľadať Lookup_value a dáta, ktoré sa majú načítať, sa nachádzajú v konkrétnom rozsahu stĺpcov.
- Col_index_num - V tomto argumente je potrebné zadať číslo indexu stĺpca alebo počet stĺpcov z prvého referenčného stĺpca, z ktorého je potrebné vytiahnuť zodpovedajúcu hodnotu z rovnakej pozície ako hodnota hľadaná v prvom stĺpci.
- (Range_lookup) - Tento argument poskytne dve možnosti.
- TRUE - Približná zhoda: - Argument je možné zadať buď ako TRUE, alebo ako číselnú hodnotu „1“, ktorá vráti približnú zhodu zodpovedajúcu referenčnému alebo prvému stĺpcu. Hodnoty v prvom stĺpci poľa tabuľky musia byť navyše zoradené vzostupne.
- FALSE - presná zhoda: - Tu môže byť argument, ktorý sa má zadať, buď FALSE, alebo číselný „0“. Táto možnosť vráti iba presnú zhodu hodnoty zodpovedajúcej identifikácii z pozície v rozsahu prvého stĺpca. Ak nevyhľadáte hodnotu z prvého stĺpca, vráti sa chybové hlásenie „# N / A“.
# 2 - Match Formula
Funkcia zhody vráti pozíciu bunky hodnoty zadanej pre dané pole tabuľky.
Všetky argumenty v syntaxi sú povinné.
- Lookup_value - Tu môže byť zadaný argument buď odkaz na bunku hodnoty alebo textový reťazec s dvojitými úvodzovkami, ktorých polohu bunky je potrebné vytiahnuť.
- Lookup_array - je potrebné zadať rozsah polí pre tabuľku, ktorej hodnotu alebo obsah bunky chcete identifikovať.
- (typ zhody) - Tento argument poskytuje tri možnosti, ako je vysvetlené nižšie.
- „1-Menej ako“ - Tu bude zadaný argument číselný „1“, ktorý vráti hodnotu, ktorá je menšia alebo rovná vyhľadanej hodnote. Vyhľadávacie pole musí byť tiež zoradené vzostupne.
- „0-presná zhoda“ - tu by mal byť argument, ktorý sa má zadať, číselný „0“. Táto možnosť vráti presnú pozíciu priradenej vyhľadávacej hodnoty. Vyhľadávacie pole však môže byť v akomkoľvek poradí.
- „-1-Väčší ako“ - Argument, ktorý sa má zadať, by mal byť číselný „-1“. Tretia možnosť vyhľadá najmenšiu hodnotu, ktorá je väčšia alebo rovná vyhľadávanej hodnote. Tu musí byť poradie vyhľadávacieho poľa umiestnené v zostupnom poradí.
# 3 - VLOOKUP s MATCH Formula
= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, (match_type)), (range lookup))
Ako používať VLOOKUP so zhodným vzorcom v programe Excel?
Nasledujúci príklad pomôže pochopiť fungovanie vzhľadu vlookup a zhody pri zostavovaní.
Zvážte nižšie uvedenú tabuľku s údajmi, ktorá popisuje technické parametre daného vozidla, ktoré sa má kúpiť.
Ak chcete získať jasnosť kombinovanej funkcie pre vlookup a funkciu zhody, pochopme, ako funguje jednotlivý vzorec, a potom po zostavení dospejeme k výsledkom zhody vlookup.
Krok 1 - Použime vzorec vlookup na individuálnej úrovni, aby sme dospeli k výsledku.
Výstup je uvedený nižšie:
Tu sa vyhľadávacia hodnota označuje $ B9, čo je model „E“, a vyhľadávacie pole sa uvádza ako rozsah údajovej tabuľky s absolútnou hodnotou „$“, index stĺpca sa označuje ako stĺpec „4“, čo je počet pre v stĺpci „Typ“ a pri vyhľadávaní rozsahu je uvedená presná zhoda.
Na vrátenie hodnoty pre stĺpec „Palivo“ sa teda použije nasledujúci vzorec .
Výstup je uvedený nižšie:
Tu hľadaná hodnota s absolútnym reťazcom „$“ použitá pre vyhľadávaciu hodnotu a lookup_array pomáha opraviť referenčnú bunku, aj keď sa vzorec kopíruje do inej bunky. V stĺpci „Palivo“ musíme zmeniť index stĺpca na „5“, pretože sa mení hodnota, z ktorej sú potrebné údaje na načítanie.
Krok 2 - Teraz poďme použiť Vzorec zhody na získanie polohy pre danú vyhľadávaciu hodnotu.
Výstup je uvedený nižšie:
Ako je vidieť na snímke vyššie, tu sa pokúšame načítať pozíciu stĺpca z poľa tabuľky. V takom prípade sa číslo stĺpca, ktorý sa má vytiahnuť, označuje ako bunka C8, čo je stĺpec „Typ“, a rozsah vyhľadávania, ktorý sa má vyhľadať, sa uvádza ako rozsah hlavičiek stĺpcov a typu zhody sa pridelí presná zhoda, ktorá sa má vyhľadať. ako „0“.
Nasledujúca tabuľka teda poskytne požadovaný výsledok pre polohy stĺpca „Palivo“.
Teraz je stĺpec, ktorý má byť prehľadaný, bunkou D8 a požadovaný index stĺpca je vrátený ako „5“.
Krok 3 - Teraz sa vo funkcii vlookup použije vzorec zhody na získanie hodnoty z identifikovanej polohy stĺpca.
Výstup je uvedený nižšie:
Vo vyššie uvedenom vzorci je funkcia zhody umiestnená na miesto parametra indexu stĺpcov funkcie vlookup. Tu funkcia zhody identifikuje referenčnú bunku vyhľadávacej hodnoty „C8“ a vráti číslo stĺpca cez dané pole tabuľky. Táto pozícia stĺpca bude slúžiť na tento účel ako vstup do argumentu indexu stĺpca vo funkcii vlookup. Čo zase pomôže programu vlookup identifikovať hodnotu, ktorá sa má vrátiť z výsledného indexového čísla stĺpca?
Podobne sme aplikovali vlookup so zhodným vzorcom aj pre stĺpec „Palivo“.
Výstup je uvedený nižšie:
Takto môžeme použiť túto kombinovanú funkciu aj pre ďalšie stĺpce „Typ“ a „Palivo“.
Na čo treba pamätať
- VLOOKUP sa dá použiť na vyhľadávané hodnoty iba na jeho najprednejšej ľavej strane. Všetky hodnoty, ktoré sa majú vyhľadať na pravej strane tabuľky s údajmi, vrátia chybovú hodnotu „# N / A“.
- Rozsah parametra table_array zadaný v druhom argumente by mal byť absolútny odkaz na bunku „$“, čím sa zachová pevný rozsah poľa tabuľky pri aplikácii vyhľadávacieho vzorca na iné bunky, inak sa referenčné bunky pre rozsah poľa tabuľky presunú do ďalšej bunky odkaz.
- Hodnota zadaná do vyhľadávacej hodnoty by nemala byť menšia ako najmenšia hodnota v prvom stĺpci poľa tabuľky, inak funkcia vráti chybovú hodnotu „# N / A“.
- Pred použitím približnej zhody „TRUE“ alebo „1“ v poslednom argumente nezabudnite vždy zoradiť pole tabuľky vo vzostupnom poradí.
- Funkcia zhody iba vráti pozíciu hodnoty v poli tabuľky vlookup a nevráti hodnotu.
- V prípade, že funkcia zhody nedokáže určiť pozíciu vyhľadávanej hodnoty v poli tabuľky, potom vzorec vráti chybovú hodnotu „# N / A“.
- Funkcie Vlookup a Match nerozlišujú veľké a malé písmená pri porovnávaní vyhľadávacej hodnoty so zodpovedajúcou textovou hodnotou v poli tabuľky.
