Obsah
Tento malý tutoriál vysvětluje, jak vytvořit funkci VPR (VLOOKUP) rozlišuje velká a malá písmena, ukazuje několik dalších vzorců, které může Excel prohledávat s rozlišením malých a velkých písmen, a poukazuje na silné a slabé stránky každé funkce.
Myslím, že každý uživatel Excelu ví, jaká funkce provádí vertikální vyhledávání. Přesně tak, je to funkce VPR. Málokdo to však ví VPR nerozlišuje velká a malá písmena, tj. malá a velká písmena jsou u něj totožná.
Zde je rychlý příklad demonstrující neschopnost VPR rozpoznat registr. Předpokládejme v buňce A1 obsahuje hodnotu „bill“ a buňku A2 – „Bill“, vzorec:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… zastaví vyhledávání na „účet“, protože tato hodnota je na prvním místě v seznamu, a extrahuje hodnotu z buňky B1.
Později v tomto článku vám ukážu, jak na to VPR citlivý na velká písmena. Kromě toho se naučíme pár dalších funkcí, které dokážou v Excelu provádět vyhledávání rozlišující malá a velká písmena.
Začneme tím nejjednodušším – NÁHLED (VYHLEDAT) a SUMPRODUCT (SUMPRODUCT), které mají bohužel několik významných omezení. Dále se blíže podíváme na trochu složitější vzorec INDEX+MATCH (INDEX+MATCH), který funguje bezchybně v jakékoli situaci a s jakýmkoliv souborem dat.
Funkce VLOOKUP rozlišuje velká a malá písmena
Jak již víte, obvyklá funkce VPR nerozlišuje malá a velká písmena. Existuje však způsob, jak rozlišovat malá a velká písmena. Chcete-li to provést, musíte do tabulky přidat pomocný sloupec, jak ukazuje následující příklad.
Předpokládejme ve sloupci B existují identifikátory produktu (Item) a chcete ze sloupců extrahovat cenu produktu a odpovídající komentář C и D. Problém je v tom, že identifikátory obsahují malá i velká písmena. Například hodnoty buněk B4 (001Tvci3u) a B5 (001Tvci3U) se liší pouze v případě posledního znaku, u и U resp.
Jak si dokážete představit, obvyklý vyhledávací vzorec
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
vrátí se $ 90, protože hodnota 001Tvci3u je v rozsahu vyhledávání dříve než 001Tvci3U. Ale to není to, co potřebujeme, že?
Pro vyhledávání pomocí funkce VPR v aplikaci Excel rozlišují velká a malá písmena, budete muset přidat pomocný sloupec a vyplnit jeho buňky následujícím vzorcem (kde B je vyhledávací sloupec):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Tento vzorec rozdělí požadovanou hodnotu na samostatné znaky, nahradí každý znak jeho kódem (např A místo toho v 65 letech a kód 97) a poté tyto kódy zkombinuje do jedinečného řetězce čísel.
Poté použijeme jednoduchou funkci VPR pro vyhledávání rozlišující malá a velká písmena:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Správné fungování funkce VPR rozlišování velkých a malých písmen závisí na dvou faktorech:
- Pomocný sloupec musí být sloupec zcela vlevo v viditelném rozsahu.
- Hodnota, kterou hledáte, musí místo skutečné hodnoty obsahovat kód znaku.
Jak správně používat funkci CODE
Vzorec vložený do buněk pomocného sloupce předpokládá, že všechny vaše hledané hodnoty mají stejný počet znaků. Pokud ne, pak musíte znát nejmenší a největší čísla a přidat co nejvíce funkcí IFERROR (IFERROR) kolik znaků je rozdíl mezi nejkratší a nejdelší hledanou hodnotou.
Pokud jsou například nejkratší hledaná hodnota 3 znaky a nejdelší 5 znaků, použijte tento vzorec:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Pro funkci PSTR (MID) Uvádíte následující argumenty:
- 1 argument - (souhrnně „Stránka (Stránky)“), a naše postupy pro shromažďování, využívání, uchovávání, ochranu a zpřístupnění takových informací. Tyto zásady platí pro informace, které shromažďujeme na těchto stránkách nebo v e-mailu, textových a jiných elektronických zprávách mezi vámi a těmito stránkami. Rovněž popisují vaše možnosti týkající se využívání vašich osobních údajů, přístupu k nim a jejich opravě. (text) je text nebo odkaz na buňku obsahující znaky, které mají být extrahovány (v našem případě je to B2)
- 2 argument - počáteční_číslo (start_position) je pozice prvního z těchto znaků, které mají být extrahovány. zadáte 1 v první funkci PSTR, 2 – ve druhé funkci PSTR atd.
- 3 argument - počet_znaků (počet_znaků) – Určuje počet znaků, které se mají z textu extrahovat. Vzhledem k tomu, že neustále potřebujeme pouze 1 znak, ve všech funkcích píšeme 1.
Omezení: funkce VPR není nejlepším řešením pro vyhledávání rozlišující malá a velká písmena v Excelu. Nejprve je nutné přidat pomocnou kolonu. Za druhé, vzorec dělá dobrou práci, pouze pokud jsou data homogenní nebo je znám přesný počet znaků ve hledaných hodnotách. Pokud to není váš případ, je lepší použít jedno z řešení, která uvádíme níže.
Funkce LOOKUP pro vyhledávání rozlišující malá a velká písmena
funkce NÁHLED (LOOKUP) související VPR, nicméně jeho syntaxe umožňuje vyhledávání rozlišující malá a velká písmena bez přidání pomocného sloupce. Chcete-li to provést, použijte NÁHLED v kombinaci s funkcí PŘESNÝ (PŘESNÝ).
Pokud vezmeme data z předchozího příkladu (bez pomocného sloupce), pak si s úkolem poradí následující vzorec:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Vzorec hledá v rozsahu A2: A7 přesná shoda s hodnotou buňky F2 rozlišuje malá a velká písmena a vrátí hodnotu ze sloupce B stejného řádku.
Like VPRfunkce NÁHLED funguje stejně s textovými a číselnými hodnotami, jak můžete vidět na obrázku níže:
Důležité! Aby funkce NÁHLED fungovaly správně, hodnoty ve vyhledávacím sloupci by měly být seřazeny vzestupně, tj. od nejmenší po největší.
Dovolte mi krátce vysvětlit, jak funkce funguje PŘESNÝ ve vzorci uvedeném výše, protože to je klíčový bod.
funkce PŘESNÝ porovná dvě textové hodnoty v 1. a 2. argumentu a vrátí TRUE, pokud jsou přesně stejné, nebo FALSE, pokud nejsou. Pro nás je důležité, aby funkce PŘESNÝ citlivý na velká písmena.
Podívejme se, jak náš vzorec funguje ZOBRAZIT + PŘESNĚ:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- funkce PŘESNÝ porovnává hodnotu buňky F2 se všemi prvky ve sloupci A (A2:A7). Vrátí TRUE, pokud je nalezena přesná shoda, jinak FALSE.
- Protože zadáte první argument funkce NÁHLED hodnotu TRUE, vytáhne příslušnou hodnotu ze zadaného sloupce (v našem případě sloupce B), pouze pokud je nalezena přesná shoda, rozlišuje se malá a velká písmena.
Doufám, že toto vysvětlení bylo jasné a nyní chápete hlavní myšlenku. Pokud ano, nebudete mít žádné potíže s dalšími funkcemi, které budeme dále analyzovat, protože. všechny fungují na stejném principu.
Omezení: Data ve vyhledávacím sloupci musí být řazena vzestupně.
SUMPRODUCT – vyhledá textové hodnoty, rozlišuje malá a velká písmena, ale vrací pouze čísla
Jak jste již pochopili z nadpisu, SUMPRODUCT (SUMPRODUCT) je další funkce aplikace Excel, která vám pomůže s vyhledáváním citlivým na velká a malá písmena, ale vrátí pouze číselné hodnoty. Pokud vám tato možnost nevyhovuje, můžete okamžitě přejít k balíčku INDEX+MATCH, která poskytuje řešení pro jakýkoli případ a pro jakékoli datové typy.
Nejprve mi dovolte stručně vysvětlit syntaxi této funkce, což vám pomůže lépe porozumět následujícímu vzorci s rozlišováním velkých a malých písmen.
funkce SUMPRODUCT vynásobí prvky daných polí a vrátí součet výsledků. Syntaxe vypadá takto:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Protože potřebujeme vyhledávání rozlišující malá a velká písmena, použijeme funkci PŘESNÝ (PŘESNĚ) z předchozího příkladu jako jeden z násobitelů:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Jak si pamatujete, PŘESNÝ porovnává hodnotu buňky F2 se všemi prvky ve sloupci A. Vrátí TRUE, pokud je nalezena přesná shoda, jinak FALSE. V matematických operacích Excel bere TRUE jako 1a FALSE pro 0Další SUMPRODUCT vynásobí tato čísla a sečte výsledky.
Nuly se nepočítají, protože při násobení vždy dávají 0. Podívejme se blíže na to, co se stane, když se ve sloupci přesně shoduje A nalezen a vrácen 1… Funkce SUMPRODUCT vynásobí číslo ve sloupci B on 1 a vrátí výsledek – přesně stejné číslo! Je to proto, že výsledky ostatních produktů jsou nulové a neovlivňují výsledný součet.
Bohužel funkce SUMPRODUCT nemůže pracovat s textovými hodnotami a daty, protože je nelze násobit. V takovém případě se zobrazí chybová zpráva #HODNOTA! (#VALUE!) jako v buňce F4 na obrázku níže:
Omezení: Vrátí pouze číselné hodnoty.
INDEX + MATCH – vyhledávání libovolného datového typu s rozlišením velkých a malých písmen
Konečně jsme blízko neomezenému vyhledávacímu vzorci rozlišujícímu velká a malá písmena, který funguje s jakýmkoli souborem dat.
Tento příklad je poslední, ne proto, že to nejlepší je ponecháno na dezert, ale protože znalosti získané z předchozích příkladů vám pomohou lépe a rychleji porozumět vzorci rozlišující malá a velká písmena. INDEX+MATCH (INDEX+MATCH).
Jak asi tušíte, kombinace funkcí VÍCE EXPOZOVANĚ и INDEX používá se v Excelu jako flexibilnější a výkonnější alternativa pro VPR. Článek Použití INDEX a MATCH místo VLOOKUP dokonale vysvětlí, jak tyto funkce spolupracují.
Jen zrekapituluji hlavní body:
- funkce VÍCE EXPOZOVANĚ (MATCH) hledá hodnotu v daném rozsahu a vrací její relativní pozici, tj. číslo řádku a/nebo sloupce;
- Dále funkce INDEX (INDEX) vrátí hodnotu ze zadaného sloupce a/nebo řádku.
Formulovat INDEX+MATCH mohl vyhledávat s rozlišováním malých a velkých písmen, stačí k němu přidat jednu funkci. Není těžké uhodnout, co to zase je PŘESNÝ (PŘESNÝ):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
V tomto vzorci PŘESNÝ funguje stejným způsobem jako ve spojení s funkcí NÁHLEDa dává stejný výsledek:
Všimněte si, že vzorec INDEX+MATCH ve složených závorkách je maticový vzorec a musíte jej dokončit stisknutím Ctrl + Shift + Enter.
Proč je INDEX+MATCH nejlepším řešením pro vyhledávání rozlišující malá a velká písmena?
Hlavní výhody svazku INDEX и VÍCE EXPOZOVANĚ:
- Na rozdíl od toho nevyžaduje přidání pomocného sloupce VPR.
- Na rozdíl od toho nevyžaduje řazení vyhledávacího sloupce NÁHLED.
- Pracuje se všemi typy dat – čísly, textem a daty.
Tenhle vzorec vypadá perfektně, že? Ve skutečnosti není. A právě proto.
Předpokládejme, že buňka ve sloupci vrácené hodnoty přidružená k vyhledávací hodnotě je prázdná. Jaký výsledek vzorec vrátí? Ne? Podívejme se, co vzorec skutečně vrací:
Jejda, vzorec vrací nulu! To nemusí být velký problém, pokud pracujete s čistě textovými hodnotami. Pokud však tabulka obsahuje čísla včetně „skutečných“ nul, stává se to problémem.
Ve skutečnosti se všechny ostatní vyhledávací vzorce (VLOOKUP, LOOKUP a SUMPRODUCT), o kterých jsme hovořili dříve, chovají stejně. Ale chcete dokonalý vzorec, že?
Chcete-li, aby vzorec rozlišoval malá a velká písmena INDEX+MATCH perfektní, dejte to do funkce IF (IF), která otestuje buňku s návratovou hodnotou a vrátí prázdný výsledek, pokud je prázdná:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
V tomto vzorci:
- B je sloupec s návratovými hodnotami
- 1+ je číslo, které otočí relativní polohu buňky vrácené funkcí VÍCE EXPOZOVANĚ, na skutečnou adresu buňky. Například v naší funkci VÍCE EXPOZOVANĚ dané vyhledávací pole A2: A7, tedy relativní polohu buňky A2 vůle 1, protože je první v poli. Ale skutečná poloha buňky A2 ve sloupci je 2, tak přidáváme 1vyrovnat rozdíl a mít funkci NEPŘÍMÝ (NEPŘÍMÉ) načteno hodnotu z požadované buňky.
Obrázky níže ukazují opravený vzorec rozlišující malá a velká písmena INDEX+MATCH V akci. Pokud je vrácená buňka prázdná, vrátí prázdný výsledek.
Vzorec jsem přepsal do sloupců B:Daby se řádek vzorců vešel na snímek obrazovky.
Vzorec se vrátí 0pokud vrácená buňka obsahuje nulu.
Pokud chcete odkaz INDEX и VÍCE EXPOZOVANĚ zobrazila nějakou zprávu, když je návratová hodnota prázdná, můžete ji napsat do posledních uvozovek (“”) vzorce, například takto:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
