Obsah
Tento malý návod vysvetľuje, ako vytvoriť funkciu VPR (VLOOKUP) rozlišuje veľké a malé písmená, zobrazuje niekoľko ďalších vzorcov, ktoré môže Excel prehľadávať s rozlíšením malých a veľkých písmen, a poukazuje na silné a slabé stránky každej funkcie.
Hádam každý používateľ Excelu vie, aká funkcia vykonáva vertikálne vyhľadávanie. Presne tak, je to funkcia VPR. Málokto to však vie VPR nerozlišuje veľké a malé písmená, tj malé a veľké písmená sú preň rovnaké.
Tu je rýchly príklad demonštrujúci neschopnosť VPR rozpoznať register. Predpokladajme, že v bunke A1 obsahuje hodnotu „účet“ a bunku A2 – „Bill“, vzorec:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… zastaví vyhľadávanie na „účet“, pretože táto hodnota je na prvom mieste v zozname, a extrahuje hodnotu z bunky B1.
Ďalej v tomto článku vám ukážem, ako na to VPR rozlišovať malé a veľké písmená. Okrem toho sa naučíme niekoľko ďalších funkcií, ktoré dokážu v Exceli vyhľadávať veľké a malé písmená.
Začneme tým najjednoduchším – ZOBRAZIŤ (LOOKUP) a SUMPRODUCT (SUMPRODUCT), ktoré, žiaľ, majú niekoľko výrazných obmedzení. Ďalej sa bližšie pozrieme na trochu zložitejší vzorec INDEX+MATCH (INDEX+MATCH), ktorý funguje bezchybne v každej situácii a s akýmkoľvek súborom údajov.
Funkcia VLOOKUP rozlišuje veľké a malé písmená
Ako už viete, obvyklá funkcia VPR nerozlišuje veľké a malé písmená. Existuje však spôsob, ako rozlišovať malé a veľké písmená. Ak to chcete urobiť, musíte do tabuľky pridať pomocný stĺpec, ako je znázornené v nasledujúcom príklade.
Predpokladajme, že v stĺpci B existujú identifikátory produktu (položka) a chcete zo stĺpcov extrahovať cenu produktu a zodpovedajúci komentár C и D. Problém je v tom, že identifikátory obsahujú malé aj veľké písmená. Napríklad hodnoty buniek B4 (001Tvci3u) a B5 (001Tvci3U) sa líšia iba v prípade posledného znaku, u и U resp.
Ako si viete predstaviť, obvyklý vzorec vyhľadávania
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
vráti sa $ 90, keďže hodnota 001Tvci3u je v rozsahu vyhľadávania skôr ako 001Tvci3U. Ale to nie je to, čo potrebujeme, však?
Na vyhľadávanie pomocou funkcie VPR v Exceli, kde sa rozlišujú malé a veľké písmená, budete musieť pridať pomocný stĺpec a vyplniť jeho bunky nasledujúcim vzorcom (kde B je vyhľadávací stĺpec):
=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 rozdelí požadovanú hodnotu na samostatné znaky, nahradí každý znak jeho kódom (napríklad namiesto A namiesto toho vo veku 65 rokov a kód 97) a potom tieto kódy skombinuje do jedinečného reťazca čísel.
Potom použijeme jednoduchú funkciu VPR pre vyhľadávanie citlivé na malé a veľké písmená:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Správne fungovanie funkcie VPR rozlišovanie malých a veľkých písmen závisí od dvoch faktorov:
- Pomocný stĺpec musí byť stĺpec úplne vľavo v viditeľnom rozsahu.
- Hodnota, ktorú hľadáte, musí obsahovať kód znaku namiesto skutočnej hodnoty.
Ako správne používať funkciu CODE
Vzorec vložený do buniek pomocného stĺpca predpokladá, že všetky hodnoty vyhľadávania majú rovnaký počet znakov. Ak nie, potom musíte poznať najmenšie a najväčšie čísla a pridať čo najviac funkcií IFERROR (IFERROR) koľko znakov je rozdiel medzi najkratšou a najdlhšou hľadanou hodnotou.
Ak má napríklad najkratšia hľadaná hodnota 3 znaky a najdlhšia 5 znakov, použite 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));"")
Pre funkciu PSTR (MID) Uvádzate nasledujúce argumenty:
- 1 argument - text (text) je text alebo odkaz na bunku obsahujúci znaky, ktoré sa majú extrahovať (v našom prípade je to B2)
- 2 argument - počiatočné_číslo (start_position) je pozícia prvého z týchto znakov, ktoré sa majú extrahovať. zadáte 1 v prvej funkcii PSTR, 2 – v druhej funkcii PSTR a tak ďalej
- 3 argument - počet_znakov (počet_znakov) – Určuje počet znakov, ktoré sa majú extrahovať z textu. Keďže nám neustále stačí 1 znak, vo všetkých funkciách píšeme 1.
Obmedzenia: Funkcie VPR nie je najlepším riešením pre vyhľadávanie v Exceli, v ktorom sa rozlišujú malé a veľké písmená. Najprv je potrebné pridanie pomocného stĺpca. Po druhé, vzorec robí dobrú prácu iba vtedy, ak sú údaje homogénne alebo ak je známy presný počet znakov vo vyhľadávaných hodnotách. Ak to nie je váš prípad, je lepšie použiť jedno z riešení, ktoré uvádzame nižšie.
Funkcia LOOKUP pre vyhľadávanie citlivé na malé a veľké písmená
Funkcie ZOBRAZIŤ (LOOKUP) súvisiace VPR, jeho syntax však umožňuje vyhľadávanie citlivé na veľkosť písmen bez pridania pomocného stĺpca. Ak to chcete urobiť, použite ZOBRAZIŤ v kombinácii s funkciou EXACT (PRESNÉ).
Ak vezmeme údaje z predchádzajúceho príkladu (bez pomocného stĺpca), potom sa s úlohou vyrovná nasledujúci 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 hľadá v rozsahu A2: A7 presná zhoda s hodnotou bunky F2 rozlišuje veľké a malé písmená a vráti hodnotu zo stĺpca B v tom istom riadku.
Ako VPRfunkcie ZOBRAZIŤ funguje rovnako s textovými a číselnými hodnotami, ako môžete vidieť na obrázku nižšie:
Dôležité! Aby bola funkcia ZOBRAZIŤ fungoval správne, hodnoty vo vyhľadávacom stĺpci by mali byť zoradené vzostupne, tj od najmenšej po najväčšiu.
Dovoľte mi stručne vysvetliť, ako funkcia funguje EXACT vo vzorci uvedenom vyššie, pretože toto je kľúčový bod.
Funkcie EXACT porovná dve textové hodnoty v 1. a 2. argumente a vráti TRUE, ak sú úplne rovnaké, alebo FALSE, ak nie sú. Pre nás je dôležité, aby funkcia EXACT rozlišovať malé a veľké písmená.
Pozrime sa, ako funguje náš vzorec ZOBRAZIŤ + PRESNÉ:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Funkcie EXACT porovnáva hodnotu bunky F2 so všetkými prvkami v stĺpci A (A2:A7). Ak sa nájde presná zhoda, vráti hodnotu TRUE, v opačnom prípade vráti hodnotu FALSE.
- Keďže zadáte prvý argument funkcie ZOBRAZIŤ hodnotu TRUE, získa zodpovedajúcu hodnotu zo zadaného stĺpca (v našom prípade stĺpca B) iba v prípade, že sa nájde presná zhoda, pričom sa rozlišujú malé a veľké písmená.
Dúfam, že toto vysvetlenie bolo jasné a teraz ste pochopili hlavnú myšlienku. Ak áno, nebudete mať žiadne problémy s inými funkciami, ktoré budeme ďalej analyzovať, pretože. všetky fungujú na rovnakom princípe.
Obmedzenia: Údaje vo vyhľadávacom stĺpci musia byť zoradené vzostupne.
SUMPRODUCT – nájde textové hodnoty, rozlišuje malé a veľké písmená, ale vráti iba čísla
Ako ste už z nadpisu pochopili, SUMPRODUCT (SUMPRODUCT) je ďalšia funkcia Excelu, ktorá vám pomôže pri vyhľadávaní s rozlišovaním malých a veľkých písmen, ale vráti iba číselné hodnoty. Ak vám táto možnosť nevyhovuje, môžete okamžite prejsť na balík INDEX+MATCH, ktorý poskytuje riešenie pre každý prípad a pre akékoľvek dátové typy.
Najprv mi dovoľte stručne vysvetliť syntax tejto funkcie, čo vám pomôže lepšie pochopiť nasledujúci vzorec rozlišujúci veľké a malé písmená.
Funkcie SUMPRODUCT vynásobí prvky daných polí a vráti súčet výsledkov. Syntax vyzerá takto:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Keďže potrebujeme vyhľadávanie citlivé na veľké a malé písmená, použijeme funkciu EXACT (PRESNÝ) z predchádzajúceho príkladu ako jeden z násobiteľov:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Ako si pamätáte, EXACT porovnáva hodnotu bunky F2 so všetkými prvkami v stĺpci A. Ak sa nájde presná zhoda, vráti hodnotu TRUE, v opačnom prípade vráti hodnotu FALSE. V matematických operáciách Excel berie TRUE as 1a FALSE pre 0ďalej SUMPRODUCT vynásobí tieto čísla a spočíta výsledky.
Nuly sa nepočítajú, pretože pri násobení vždy dávajú 0. Pozrime sa bližšie na to, čo sa stane, keď sa v stĺpci presne zhoduje A našiel a vrátil 1… Funkcia SUMPRODUCT vynásobí číslo v stĺpci B on 1 a vráti výsledok – presne to isté číslo! Je to preto, že výsledky ostatných produktov sú nulové a neovplyvňujú výsledný súčet.
Bohužiaľ funkcia SUMPRODUCT nemôže pracovať s textovými hodnotami a dátumami, pretože ich nemožno násobiť. V tomto prípade sa zobrazí chybové hlásenie #HODNOTA! (#HODNOTA!) ako v bunke F4 na obrázku nižšie:
Obmedzenia: Vráti iba číselné hodnoty.
INDEX + MATCH – vyhľadávanie akéhokoľvek dátového typu s rozlišovaním veľkých a malých písmen
Nakoniec sme blízko k neobmedzenému vyhľadávaciemu vzorcu, v ktorom sa rozlišujú malé a veľké písmená, ktorý funguje s akoukoľvek množinou údajov.
Tento príklad je posledný, nie preto, že to najlepšie je ponechané na dezert, ale preto, že poznatky získané z predchádzajúcich príkladov vám pomôžu lepšie a rýchlejšie pochopiť vzorec rozlišujúci malé a veľké písmená. INDEX+MATCH (INDEX+MATCH).
Ako iste tušíte, kombinácia funkcií VIAC EXPOZOVANÉ и INDEX používa sa v Exceli ako flexibilnejšia a výkonnejšia alternatíva pre VPR. Článok Používanie INDEX a MATCH namiesto VLOOKUP dokonale vysvetlí, ako tieto funkcie spolupracujú.
Len zrekapitulujem kľúčové body:
- Funkcie VIAC EXPOZOVANÉ (MATCH) vyhľadá hodnotu v danom rozsahu a vráti jej relatívnu pozíciu, to znamená číslo riadku a/alebo stĺpca;
- Ďalej funkcia INDEX (INDEX) vráti hodnotu zo zadaného stĺpca a/alebo riadka.
Formulovať INDEX+MATCH môže vyhľadávať s rozlíšením malých a veľkých písmen, stačí k nemu pridať jednu funkciu. Nie je ťažké uhádnuť, čo to zase je EXACT (PRESNÉ):
=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 EXACT funguje rovnakým spôsobom ako v spojení s funkciou ZOBRAZIŤa dáva rovnaký výsledok:
Všimnite si, že vzorec INDEX+MATCH v zložených zátvorkách je vzorec poľa a musíte ho doplniť stlačením Ctrl + Shift + Enter.
Prečo je INDEX+MATCH najlepším riešením na vyhľadávanie s rozlíšením malých a veľkých písmen?
Hlavné výhody zväzku INDEX и VIAC EXPOZOVANÉ:
- Na rozdiel od toho nevyžaduje pridanie pomocného stĺpca VPR.
- Na rozdiel od toho nevyžaduje triedenie vyhľadávacieho stĺpca ZOBRAZIŤ.
- Pracuje so všetkými typmi údajov – číslami, textom a dátumami.
Tento vzorec vyzerá perfektne, však? V skutočnosti nie je. A preto.
Predpokladajme, že bunka v stĺpci návratovej hodnoty priradená k hodnote vyhľadávania je prázdna. Aký výsledok vráti vzorec? nie? Pozrime sa, čo vzorec skutočne vráti:
Ojoj, vzorec vracia nulu! To nemusí byť veľký problém, ak pracujete s čisto textovými hodnotami. Ak však tabuľka obsahuje čísla vrátane „skutočných“ núl, stáva sa to problémom.
V skutočnosti sa všetky ostatné vyhľadávacie vzorce (VLOOKUP, LOOKUP a SUMPRODUCT), o ktorých sme hovorili vyššie, správajú rovnako. Ale chcete dokonalý vzorec, však?
Aby sa vo vzorci rozlišovali malé a veľké písmená INDEX+MATCH perfektné, dajte to do funkcie IF (IF), ktorá otestuje bunku s návratovou hodnotou a vráti prázdny výsledok, ak je prázdna:
=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 stĺpec s návratovými hodnotami
- 1+ je číslo, ktoré zmení relatívnu polohu bunky vrátenej funkciou VIAC EXPOZOVANÉ, na skutočnú adresu bunky. Napríklad v našej funkcii VIAC EXPOZOVANÉ dané vyhľadávacie pole A2: A7, teda relatívnu polohu bunky A2 vôľa 1, pretože je prvý v poli. Ale skutočná poloha bunky A2 v stĺpci je 2, tak pridávame 1vyrovnať rozdiel a mať funkciu NEPRIAME (NEPRIAME) získal hodnotu z požadovanej bunky.
Na obrázkoch nižšie je znázornený opravený vzorec, v ktorom sa rozlišujú malé a veľké písmená INDEX+MATCH V akcii. Ak je vrátená bunka prázdna, vráti prázdny výsledok.
Vzorec som prepísal do stĺpcov B:Daby sa riadok vzorcov zmestil na snímku obrazovky.
Formula sa vracia 0ak vrátená bunka obsahuje nulu.
Ak chceš link INDEX и VIAC EXPOZOVANÉ zobrazila nejakú správu, keď je návratová hodnota prázdna, môžete ju napísať do posledných úvodzoviek (“”) vzorca, naprí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.")
