4 būdai, kaip padaryti VLOOKUP skirtingą didžiosioms ir mažosioms raidėms programoje „Excel“.

Šioje mažoje pamokoje paaiškinama, kaip sukurti funkciją VPR (VLOOKUP) didžiosios ir mažosios raidės, rodomos kelios kitos formulės, kuriose „Excel“ gali ieškoti didžiųjų ir mažųjų raidžių skyrimo būdu, ir nurodo kiekvienos funkcijos stipriąsias ir silpnąsias puses.

Manau, kiekvienas Excel vartotojas žino, kokia funkcija atlieka vertikalią paiešką. Teisingai, tai funkcija VPR. Tačiau mažai žmonių tai žino VPR neskiria didžiųjų ir mažųjų raidžių, ty mažosios ir didžiosios raidės yra identiškos.

Štai greitas pavyzdys, parodantis nesugebėjimą VPR atpažinti registrą. Tarkime, ląstelėje A1 yra reikšmė „bill“ ir langelis A2 – „Sąskaita“, formulė:

=VLOOKUP("Bill",A1:A10,2)

=ВПР("Bill";A1:A10;2)

... sustabdys paiešką pagal „sąskaitą“, nes ši reikšmė sąraše yra pirmoji, ir ištrauks reikšmę iš langelio B1.

Vėliau šiame straipsnyje parodysiu, kaip tai padaryti VPR didžiosios ir mažosios raidės. Be to, išmoksime dar keletą funkcijų, kurios gali atlikti paieškas pagal didžiąsias ir mažąsias raides Excel programoje.

Pradėsime nuo paprasčiausio – VAIZDAS (LOOKUP) ir SUMPRODUCT (SUMPRODUCT), kuri, deja, turi keletą reikšmingų apribojimų. Toliau atidžiau pažvelgsime į šiek tiek sudėtingesnę formulę INDEX+MATCH (INDEX+MATCH), kuris veikia nepriekaištingai bet kokioje situacijoje ir su bet kokiu duomenų rinkiniu.

• Skiriamos didžiosios ir mažosios raidės VLOOKUP – reikalingas pagalbinis stulpelis
• Didžiųjų ir mažųjų raidžių funkcija LOOKUP – reikia rūšiuoti duomenis
• SUMPRODUCT – pateikia tik skaitines reikšmes
• INDEX+MATCH – visų tipų duomenų paieška naudojant didžiąsias ir mažąsias raides

Funkcija VLOOKUP yra didžiosios ir mažosios raidės

Kaip jau žinote, įprasta funkcija VPR neskiriamos didžiosios ir mažosios raidės. Tačiau yra būdas, kad būtų skiriamos didžiosios ir mažosios raidės. Norėdami tai padaryti, į lentelę turite įtraukti pagalbinį stulpelį, kaip parodyta kitame pavyzdyje.

Tarkime, stulpelyje B yra prekės identifikatoriai (Prekė) ir norite iš stulpelių ištraukti prekės kainą bei atitinkamą komentarą C и D. Problema ta, kad identifikatoriuose yra ir mažųjų, ir didžiųjų raidžių. Pavyzdžiui, ląstelių reikšmės B4 (001Tvci3u) ir B5 (001Tvci3U) skiriasi tik paskutinio simbolio atveju, u и U atitinkamai.

Kaip galite įsivaizduoti, įprasta paieškos formulė

=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)

=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)

grįš $ 90, nes vertė 001Tvci3u yra paieškos diapazone anksčiau nei 001Tvci3U. Bet ne to mums reikia, ar ne?

Norėdami ieškoti su funkcija VPR Jei naudojate „Excel“ didžiąsias ir mažąsias raides, turėsite pridėti pagalbinį stulpelį ir užpildyti jo langelius šia formule (kur B yra paieškos stulpelis):

=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));"")

Ši formulė suskaido norimą reikšmę į atskirus simbolius, kiekvieną simbolį pakeičia jo kodu (pavyzdžiui, vietoj A vietoj 65 metų a kodas 97) ir sujungia šiuos kodus į unikalią skaičių eilutę.

Po to naudojame paprastą funkciją VPR mažųjų ir mažųjų raidžių paieškai:

=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)

=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)

Tinkamas funkcijos veikimas VPR didžiosios ir mažosios raidės priklauso nuo dviejų veiksnių:

1. Pagalbinis stulpelis turi būti kairysis matomo diapazono stulpelis.
2. Reikšmė, kurios ieškote, turi turėti simbolio kodą, o ne tikrąją reikšmę.

Kaip teisingai naudoti CODE funkciją

Formulė, įterpta į pagalbinio stulpelio langelius, daro prielaidą, kad visos paieškos reikšmės turi tą patį simbolių skaičių. Jei ne, tuomet reikia žinoti mažiausius ir didžiausius skaičius ir pridėti kuo daugiau funkcijų IFERROR (IFERROR), kiek simbolių yra skirtumas tarp trumpiausios ir ilgiausios ieškomos reikšmės.

Pavyzdžiui, jei trumpiausia paieškos reikšmė yra 3 simboliai, o ilgiausia – 5 simboliai, naudokite šią formulę:

=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));"")

Dėl funkcijos PSTR (MID) Pateikiate šiuos argumentus:

• 1 argumentas - tekstas (tekstas) yra teksto arba langelio nuoroda, kurioje yra išgautini simboliai (mūsų atveju tai yra B2)
• 2 argumentas - pradžios_numeris (pradžios_pozicija) yra pirmojo iš tų simbolių, kuriuos reikia išgauti, padėtis. tu įeini 1 pirmoje funkcijoje PSTR, 2 – antroje funkcijoje PSTR ir taip toliau
• 3 argumentas - simbolių skaičius (simbolių_skaičius) – nurodo, kiek simbolių reikia ištraukti iš teksto. Kadangi mums visą laiką reikia tik 1 simbolio, visose funkcijose rašome 1.

APRIBOJIMAI: Funkcija VPR nėra geriausias sprendimas atliekant „Excel“ paieškas, kuriose skiriamos didžiosios ir mažosios raidės. Pirma, reikia pridėti pagalbinę kolonėlę. Antra, formulė gerai veikia tik tuo atveju, jei duomenys yra vienarūšiai arba žinomas tikslus ieškomų verčių simbolių skaičius. Jei tai ne jūsų atvejis, geriau naudoti vieną iš toliau pateiktų sprendimų.

Funkcija LOOKUP, skirta paieškai skyrus didžiąsias ir mažąsias raides

Funkcija VAIZDAS (LOOKUP) susiję VPR, tačiau jo sintaksė leidžia ieškoti didžiųjų ir mažųjų raidžių nepridedant pagalbinio stulpelio. Norėdami tai padaryti, naudokite VAIZDAS kartu su funkcija TIKSLAS (TIKSLAI).

Jei paimsime duomenis iš ankstesnio pavyzdžio (be pagalbinio stulpelio), tada ši formulė susidoros su užduotimi:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

Formulės ieško diapazone A2: A7 tiksli atitiktis su langelio verte F2 didžiosios ir mažosios raidės ir grąžina reikšmę iš tos pačios eilutės B stulpelio.

Kaip VPRfunkcija VAIZDAS veikia vienodai su teksto ir skaitinėmis reikšmėmis, kaip matote toliau pateiktoje ekrano kopijoje:

Svarbu! Norint atlikti funkciją VAIZDAS veikė tinkamai, reikšmės peržvalgos stulpelyje turėtų būti rūšiuojamos didėjančia tvarka, ty nuo mažiausios iki didžiausios.

Leiskite trumpai paaiškinti, kaip ši funkcija veikia TIKSLAS aukščiau pateiktoje formulėje, nes tai yra pagrindinis dalykas.

Funkcija TIKSLAS lygina dvi 1-ojo ir 2-ojo argumentų teksto reikšmes ir grąžina TRUE, jei jos yra visiškai vienodos, arba FALSE, jei jos nėra. Mums svarbu, kad funkcija TIKSLAS didžiosios ir mažosios raidės.

Pažiūrėkime, kaip veikia mūsų formulė PERŽIŪRĖTI+TIKSLAI:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

• Funkcija TIKSLAS lygina ląstelės vertę F2 su visais elementais stulpelyje A (A2:A7). Grąžina TRUE, jei rasta tiksli atitiktis, kitu atveju FALSE.
• Kadangi pateikiate pirmąjį funkcijos argumentą VAIZDAS reikšmę TRUE, ji ištraukia atitinkamą reikšmę iš nurodyto stulpelio (mūsų atveju B stulpelio) tik tuo atveju, jei randama tiksli atitiktis, skiriamos didžiosios ir mažosios raidės.

Tikiuosi, kad šis paaiškinimas buvo aiškus ir dabar supratote pagrindinę mintį. Jei taip, tuomet jums nekils jokių sunkumų su kitomis funkcijomis, kurias analizuosime toliau, nes. jie visi veikia tuo pačiu principu.

APRIBOJIMAI: Duomenys paieškos stulpelyje turi būti rūšiuojami didėjančia tvarka.

SUMPRODUCT – randa tekstines reikšmes, skiriamos didžiosios ir mažosios raidės, bet pateikia tik skaičius

Kaip jau supratote iš pavadinimo, SUMPRODUCT (SUMPRODUCT) yra dar viena „Excel“ funkcija, kuri padės atlikti didžiųjų ir mažųjų raidžių paiešką, tačiau pateiks tik skaitines reikšmes. Jei ši parinktis jums netinka, galite nedelsdami pereiti prie paketo INDEX+MATCH, kuris suteikia sprendimą bet kokiam atvejui ir bet kokiems duomenų tipams.

Pirmiausia leiskite trumpai paaiškinti šios funkcijos sintaksę, kuri padės geriau suprasti toliau pateiktą didžiųjų ir mažųjų raidžių formulę.

Funkcija SUMPRODUCT padaugina pateiktų masyvų elementus ir grąžina rezultatų sumą. Sintaksė atrodo taip:

SUMPRODUCT(array1,[array2],[array3],...)

СУММПРОИЗВ(массив1;[массив2];[массив3];…)

Kadangi mums reikia paieškos, skirtos didžiosioms ir mažosioms raidėms, naudojame funkciją TIKSLAS (TIKSLUS) iš ankstesnio pavyzdžio kaip vieną iš daugiklių:

=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))

=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))

Kaip jūs atsimenate, TIKSLAS lygina ląstelės vertę F2 su visais elementais stulpelyje A. Grąžina TRUE, jei rasta tiksli atitiktis, kitu atveju FALSE. Atlikdama matematinius veiksmus, „Excel“ laiko TRUE kaip 1, ir FALSE už 0toliau SUMPRODUCT padaugina šiuos skaičius ir susumuoja rezultatus.

Nuliai neskaičiuojami, nes padauginus jie visada duoda 0. Pažiūrėkime atidžiau, kas nutinka, kai stulpelyje yra tiksli atitiktis A surado ir grąžino 1… Funkcija SUMPRODUCT padaugina skaičių stulpelyje B on 1 ir grąžina rezultatą – lygiai tą patį skaičių! Taip yra todėl, kad kitų produktų rezultatai yra nuliniai ir jie neturi įtakos gautai sumai.

Deja, funkcija SUMPRODUCT negali dirbti su teksto reikšmėmis ir datomis, nes jų negalima padauginti. Tokiu atveju gausite klaidos pranešimą #VALUE! (#VALUE!) kaip langelyje F4 žemiau esančiame paveikslėlyje:

APRIBOJIMAI: Grąžina tik skaitines reikšmes.

INDEX + MATCH – bet kokio tipo duomenų paieška naudojant didžiąsias ir mažąsias raides

Galiausiai, esame arti neribotos ir didžiosioms ir mažosioms raidėms skirtos paieškos formulės, kuri veikia su bet kokiu duomenų rinkiniu.

Šis pavyzdys yra paskutinis ne todėl, kad geriausia lieka desertui, o todėl, kad ankstesniuose pavyzdžiuose įgytos žinios padės geriau ir greičiau suprasti didžiųjų ir mažųjų raidžių formulę. INDEX+MATCH (INDEX+MATCH).

Kaip tikriausiai atspėjote, funkcijų derinys DAUGIAU ATSKIRTA и RODYKLĖ naudojama programoje Excel kaip lankstesnė ir galingesnė alternatyva VPR. Straipsnyje INDEX ir MATCH naudojimas vietoj VLOOKUP puikiai paaiškins, kaip šios funkcijos veikia kartu.

Aš tik pakartosiu pagrindinius dalykus:

• Funkcija DAUGIAU ATSKIRTA (MATCH) ieško reikšmės tam tikrame diapazone ir grąžina jos santykinę padėtį, tai yra, eilutės ir (arba) stulpelio numerį;
• Toliau funkcija RODYKLĖ (INDEX) grąžina reikšmę iš nurodyto stulpelio ir (arba) eilutės.

Į formulę INDEX+MATCH gali ieškoti skyrus didžiąsias ir mažąsias raides, tereikia prie jos pridėti vieną funkciją. Nesunku vėl atspėti, kas tai yra TIKSLAS (TIKSLUS):

=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))

Šioje formulėje TIKSLAS veikia taip pat, kaip ir kartu su funkcija VAIZDAS, ir duoda tą patį rezultatą:

Atkreipkite dėmesį, kad formulė INDEX+MATCH Garbanotuose skliaustuose yra masyvo formulė, kurią turite užpildyti paspausdami Ctrl + Shift + Enter.

Kodėl INDEX+MATCH yra geriausias sprendimas ieškant didžiųjų ir mažųjų raidžių?

Pagrindiniai paketo privalumai RODYKLĖ и DAUGIAU ATSKIRTA:

1. Nereikia pridėti pagalbinio stulpelio, skirtingai nei VPR.
2. Nereikia rūšiuoti paieškos stulpelio, kitaip nei VAIZDAS.
3. Veikia su visų tipų duomenimis – skaičiais, tekstu ir datomis.

Ši formulė atrodo tobula, ar ne? Tiesą sakant, taip nėra. Ir todėl.

Tarkime, kad grąžinamos vertės stulpelio langelis, susietas su peržvalgos reikšme, yra tuščias. Kokį rezultatą pateiks formulė? Ne? Pažiūrėkime, ką formulė iš tikrųjų grąžina:

Oi, formulė grąžina nulį! Tai gali būti ne didelė problema, jei dirbate su gryno teksto reikšmėmis. Tačiau jei lentelėje yra skaičiai, įskaitant „tikruosius“ nulius, tai tampa problema.

Tiesą sakant, visos kitos paieškos formulės (VLOOKUP, LOOKUP ir SUMPRODUCT), kurias aptarėme anksčiau, veikia taip pat. Bet jūs norite tobulos formulės, tiesa?

Kad formulėje būtų skiriamos didžiosios ir mažosios raidės INDEX+MATCH tobula, įdėkite jį į funkciją IF (IF), kuris patikrins langelį su grąžinama reikšme ir pateiks tuščią rezultatą, jei jis tuščias:

=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));"")

Šioje formulėje:

• B yra stulpelis su grąžinamomis reikšmėmis
• 1+ yra skaičius, paverčiantis santykinę ląstelės padėtį, kurią grąžina funkcija DAUGIAU ATSKIRTA, į tikrąjį ląstelės adresą. Pavyzdžiui, mūsų funkcijoje DAUGIAU ATSKIRTA pateiktas paieškos masyvas A2: A7, tai yra santykinė ląstelės padėtis A2 valia 1, nes tai pirmasis masyve. Tačiau tikroji ląstelės padėtis A2 stulpelyje yra 2, todėl pridedame 1kompensuoti skirtumą ir atlikti funkciją NETIESIOGINĖS (NETIESIOGINĖ) gavo reikšmę iš norimos ląstelės.

Toliau pateiktose nuotraukose parodyta ištaisyta didžiųjų ir mažųjų raidžių formulė INDEX+MATCH Veikiant. Jis grąžina tuščią rezultatą, jei grąžintas langelis yra tuščias.

Formulę perrašiau į stulpelius B:Dkad ekrano kopijoje tilptų formulės juosta.

Formulė grįžta 0jei grąžintame langelyje yra nulis.

Jei nori nuoroda RODYKLĖ и DAUGIAU ATSKIRTA rodomas tam tikras pranešimas, kai grąžinama reikšmė yra tuščia, galite ją įrašyti paskutinėse formulės kabutėse („“), pavyzdžiui, taip:

=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.")