ZOBRAZIŤ ZOBRAZENIE so ZHODA Vytvorte flexibilný vzorec pomocou ZÁPASU ZHĽADÁVANIA

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 nad často sa meniacim indexom 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 dvojitými úvodzovkami, aby sa dali identifikovať v rozsahu stĺpcov.
  • Pole tabuľky -   Tento argument vyžaduje, aby ste zadali 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. Ďalej musia byť hodnoty v prvom stĺpci poľa tabuľky zoradené vzostupne.
  • FALSE - presná zhoda: - Tu môže byť zadaný argument 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, ktoré sú 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í.

Tento VLookup so zodpovedajúcou šablónou programu Excel si môžete stiahnuť tu - VLookup so zodpovedajúcou šablónou programu Excel

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 funkciu vlookup a párovanie, poďme pochopiť, ako funguje jednotlivý vzorec, a potom dospejeme k výsledkom zhody vlookup, keď sa dajú dohromady.

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 stĺpci „Typ“ a pri vyhľadávaní rozsahu je uvedená presná zhoda.

Nasledujúci vzorec sa teda použije na vrátenie hodnoty pre stĺpec „Palivo“.

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 vyhľadávací rozsah, ktorý sa má vyhľadať, sa uvádza ako rozsah hlavičiek stĺpcov a typu zhody sa dáva presná zhoda ako „ 0 ”.

Nasledujúca tabuľka teda poskytne požadovaný výsledok pre polohy stĺpca „Palivo“.

Teraz je tu 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ť ako vstup do argumentu indexu stĺpca vo funkcii vlookup. Čo následne 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 identifikovať 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.