Stáhněte si ukázkový sešit
VLOOKUP rozlišující velká a malá písmena - Excel
Tento tutoriál předvede, jak provést VLOOKUP s rozlišováním velkých a malých písmen v aplikaci Excel pomocí dvou různých metod.
Metoda 1 - VLOOKUP rozlišující malá a velká písmena se sloupcem pomocníka
= SVYHLEDAT (MAX (PŘESNĚ (E2, $ B $ 2: $ B $ 7)*(ŘADA ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Funkce VLOOKUP
Funkce VLOOKUP slouží k vyhledání přibližné nebo přesné shody hodnoty ve sloupci zcela vlevo v rozsahu a vrací odpovídající hodnotu z jiného sloupce. Ve výchozím nastavení bude VLOOKUP fungovat pouze pro hodnoty, které nerozlišují velká a malá písmena, například:
VLOOKUP rozlišující velká a malá písmena
Kombinací VLOOKUP, EXACT, MAX a ROW můžeme vytvořit vzorec VLOOKUP citlivý na velká a malá písmena, který vrací odpovídající hodnotu pro naše VLOOKUP rozlišující velká a malá písmena. Pojďme se podívat na příklad.
Máme seznam položek a jejich odpovídající ceny (všimněte si, že ID položky rozlišuje velká a malá písmena):
Předpokládáme, že jsme požádáni o získání ceny za položku pomocí jejího ID položky takto:
Abychom toho dosáhli, musíme nejprve vytvořit pomocný sloupec pomocí ROW:
= ROW () kliknutím a přetažením (nebo dvojitým kliknutím) předvyplníte všechny řádky v rozsahu
Dále zkombinujte VLOOKUP, MAX, EXACT a ROW do vzorce takto:
= VLOOKUP (MAX (PŘESNĚ (,)*(ŘÁDEK ())), ,, 0)
= VLOOKUP (MAX (PŘESNĚ (E2, $ B $ 2: $ B $ 7)**(ŘADA ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Jak funguje vzorec?
- PŘESNÁ funkce kontroluje ID položky v E2 (vyhledávací hodnota) oproti hodnotám v B2: B7 (vyhledávací rozsah) a vrací pole PRAVDA, kde je přesná shoda nebo FLASE v poli {FLASE, FLASE, FLASE, FLASE, FLASE, TRUE}.
- Toto pole se pak vynásobí řádkovým polem {2, 3, 4, 5, 6, 7} (toto odpovídá našemu pomocnému sloupci).
- Funkce MAX vrací maximální hodnotu z výsledného pole {0,0,0,0,0,7}, což je v našem příkladu 7.
- Výsledek pak použijeme jako vyhledávací hodnotu ve VLOOKUP a jako rozsah vyhledávání zvolíme náš pomocný sloupec. V našem příkladu vzorec vrátí odpovídající hodnotu 16,00 $.
Metoda 2 - VLOOKUP rozlišující velká a malá písmena s „virtuálním“ pomocným sloupcem
= HLEDAT , $ C $ 2: $ C $ 7), 2,0)
Tato metoda používá stejnou logiku jako první metoda, ale eliminuje potřebu vytváření pomocného sloupce a místo toho používá CHOOSE a ROW k vytvoření „virtuálního“ pomocného sloupce takto:
= SVYHLEDAT (MAX (PŘESNĚ (,)*(ŘÁDEK ()))
= HLEDAT , $ C $ 2: $ C $ 7), 2,0)
Jak funguje vzorec?
- První část vzorce funguje stejně jako první metoda.
- Kombinace CHOOSE a ROW vrací pole se dvěma sloupci, jedním pro číslo řádku a druhým pro cenu. Pole je oddělené středníkem, které představuje další řádek, a čárkou pro další sloupec takto: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- Výsledek z první části vzorce pak můžeme použít ve VLOOKUP k nalezení odpovídající hodnoty z našeho pole CHOOSE a ROW.
VLOOKUP rozlišující velká a malá písmena v Tabulkách Google
Všechny výše uvedené příklady fungují v Tabulkách Google úplně stejně jako v Excelu.
