Stiahnite si ukážkový zošit
VLOOKUP, ktorý rozlišuje malé a veľké písmená - Excel
Tento tutoriál ukáže, ako vykonať VLOOKUP s rozlišovaním malých a veľkých písmen v programe Excel pomocou dvoch rôznych metód.
Metóda 1 - VLOOKUP s rozlišovaním malých a veľkých písmen s pomocným stĺpcom
= VLOOKUP (MAX (PRESNE (E2, $ B $ 2: $ B $ 7)**ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Funkcia VLOOKUP
Funkcia VLOOKUP sa používa na vyhľadanie približnej alebo presnej zhody hodnoty v stĺpci úplne vľavo v rozsahu a vráti zodpovedajúcu hodnotu z iného stĺpca. VLOOKUP bude v predvolenom nastavení fungovať iba pre hodnoty, ktoré nerozlišujú malé a veľké písmená, ako napríklad:
VLOOKUP citlivý na veľkosť písmen
Kombináciou VLOOKUP, EXACT, MAX a ROW môžeme vytvoriť vzorec VLOOKUP s rozlišovaním malých a veľkých písmen, ktorý vráti zodpovedajúcu hodnotu pre naše VLOOKUP s rozlišovaním malých a veľkých písmen. Poďme sa pozrieť na príklad.
Máme zoznam položiek a im zodpovedajúcich cien (všimnite si, že ID položky rozlišuje iba malé a veľké písmená):
Predpokladá sa, že sme požiadaní o získanie ceny za položku pomocou jej ID položky takto:
Aby sme to dosiahli, najskôr musíme vytvoriť pomocný stĺpec pomocou ROW:
= Kliknutím na riadok () a potiahnutím (alebo dvojitým kliknutím) predvyplníte všetky riadky v rozsahu
Potom skombinujte VLOOKUP, MAX, EXACT a ROW vo vzorci takto:
= VLOOKUP (MAX (PRESNE (,)*(ROW ())), ,, 0)
= VLOOKUP (MAX (PRESNE (E2, $ B $ 2: $ B $ 7)**ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Ako funguje vzorec?
- Funkcia EXACT kontroluje ID položky v E2 (vyhľadávacia hodnota) oproti hodnotám v B2: B7 (vyhľadávací rozsah) a vracia pole TRUE, kde je presná zhoda alebo FLASE v poli {FLASE, FLASE, FLASE, FLASE, FLASE, TRUE}.
- Toto pole sa potom vynásobí poľom ROW {2, 3, 4, 5, 6, 7} (toto sa zhoduje s naším pomocným stĺpcom).
- Funkcia MAX vracia maximálnu hodnotu z výsledného poľa {0,0,0,0,0,7}, čo je v našom prípade 7.
- Potom použijeme výsledok ako našu vyhľadávaciu hodnotu vo VLOOKUP a ako rozsah vyhľadávania zvolíme náš pomocný stĺpec. V našom prípade vzorec vráti zodpovedajúcu hodnotu 16,00 USD.
Metóda 2 - VLOOKUP s rozlišovaním malých a veľkých písmen s „virtuálnym“ pomocným stĺpcom
= VLOOKUP (MAX (PRESNE (D2, $ B $ 2: $ B $ 7)**(ROW ($ B $ 2: $ B $ 7))), CHOOSE ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)
Táto metóda používa rovnakú logiku ako prvá metóda, ale eliminuje potrebu vytvárania pomocného stĺpca a namiesto toho používa CHOOSE a ROW na vytvorenie „virtuálneho“ pomocného stĺpca takto:
= VLOOKUP (MAX (PRESNE (,)*(ROW ()))), VYBERTE ({1,2}, ROW (),),, 0)
= VLOOKUP (MAX (PRESNE (D2, $ B $ 2: $ B $ 7)**(ROW ($ B $ 2: $ B $ 7))), CHOOSE ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)
Ako funguje vzorec?
- Prvá časť vzorca funguje rovnako ako prvá metóda.
- Kombinácia CHOOSE a ROW vráti pole s dvoma stĺpcami, jedným pre číslo riadka a druhým pre cenu. Pole je oddelené bodkočiarkou, ktorá predstavuje ďalší riadok, a čiarkou pre nasledujúci stĺpec takto: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- Potom môžeme použiť výsledok z prvej časti vzorca vo VLOOKUP na nájdenie zodpovedajúcej hodnoty z nášho poľa CHOOSE a ROW.
VLOOKUP, ktorý rozlišuje malé a veľké písmená v Tabuľkách Google
Všetky vyššie uvedené príklady fungujú v Tabuľkách Google úplne rovnako ako v programe Excel.
