Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak provést VLOOKUP rozlišující malá a velká písmena v aplikaci Excel pomocí dvou různých metod a tabulek Google pomocí jedné metody.
Rozlišování malých a velkých písmen s pomocným sloupcem
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:
| 1 | = VLOOKUP ($ E $ 2, $ B $ 2: $ C $ 4,2,0) |
VLOOKUP
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:
| 1 | = ŘÁDEK () |
= 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:
| 12 | = SVYHLEDAT (MAX (PŘESNĚ (,)*(ŘÁDEK ())),,, 0) |
| 1 | = 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
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:
| 12 | = SVYHLEDAT (MAX (PŘESNĚ (,)*(ŘÁDEK ())),VYBERTE ({1,2}, ŘÁDEK (),),, 0) |
| 1 | = 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
K provedení VLOOKUPU rozlišujícího malá a velká písmena v Tabulkách Google použijte tuto metodu:
