4 načini, kako VLOOKUP v Excelu razlikovati med velikimi in malimi črkami

Ta majhna vadnica pojasnjuje, kako narediti funkcijo VPR (VLOOKUP), ki razlikuje med velikimi in malimi črkami, prikazuje več drugih formul, po katerih lahko Excel išče na način, ki razlikuje med velikimi in malimi črkami, ter opozarja na prednosti in slabosti vsake funkcije.

Mislim, da vsak uporabnik Excela ve, katera funkcija izvaja navpično iskanje. Tako je, to je funkcija VPR. Vendar le malo ljudi to ve VPR ne razlikuje med velikimi in malimi črkami, kar pomeni, da so male in velike črke zanj enake.

Tukaj je hiter primer, ki dokazuje nezmožnost VPR prepoznati register. Recimo v celici A1 vsebuje vrednost »račun« in celico A2 – »Račun«, formula:

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

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

... bo ustavil iskanje na "računu", ker je ta vrednost prva na seznamu, in izvlekel vrednost iz celice B1.

Kasneje v tem članku vam bom pokazal, kako to storiti VPR občutljivo na velike in male črke. Poleg tega se bomo naučili še nekaj funkcij, ki lahko v Excelu izvajajo iskanja z upoštevanjem velikih in malih črk.

Začeli bomo z najpreprostejšim – OGLED (ISKANJE) in SUMPRODUCT (SUMPRODUCT), ki pa imajo na žalost več pomembnih omejitev. Nato si bomo podrobneje ogledali nekoliko bolj zapleteno formulo INDEKS+UJEMANJE (INDEX+MATCH), ki deluje brezhibno v vsaki situaciji in s katerim koli naborom podatkov.

• VLOOKUP, ki razlikuje med velikimi in malimi črkami – zahteva pomožni stolpec
• Funkcija LOOKUP, ki razlikuje med velikimi in malimi črkami – zahteva, da so podatki razvrščeni
• SUMPRODUCT – vrne samo številske vrednosti
• INDEX+MATCH – iskanje z upoštevanjem velikih in malih črk za vse vrste podatkov

Funkcija VLOOKUP razlikuje med velikimi in malimi črkami

Kot že veste, običajna funkcija VPR ne razlikuje med velikimi in malimi črkami. Vendar pa obstaja način, da bo občutljiv na velike in male črke. Če želite to narediti, morate tabeli dodati pomožni stolpec, kot je prikazano v naslednjem primeru.

Recimo v stolpcu B obstajajo identifikatorji izdelka (izdelek) in želite iz stolpcev izluščiti ceno izdelka in ustrezen komentar C и D. Težava je v tem, da identifikatorji vsebujejo male in velike črke. Na primer vrednosti celic B4 (001Tvci3u) in B5 (001Tvci3U) se razlikujejo le v primeru zadnjega znaka, u и U oz.

Kot si lahko predstavljate, običajna iskalna formula

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

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

se bo vrnil $ 90, saj je vrednost 001Tvci3u je v iskalnem območju prej kot 001Tvci3U. Ampak to ni tisto, kar potrebujemo, kajne?

Za iskanje s funkcijo VPR v Excelu, ki razlikuje med velikimi in malimi črkami, boste morali dodati pomožni stolpec in njegove celice izpolniti z naslednjo formulo (kjer je B stolpec za iskanje):

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

Ta formula razdeli želeno vrednost na ločene znake, vsak znak nadomesti z njegovo kodo (na primer namesto A pri 65, namesto tega a koda 97) in nato te kode združi v edinstven niz številk.

Nato uporabimo preprosto funkcijo VPR za iskanje, občutljivo na velike in male črke:

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

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

Pravilno delovanje funkcije VPR razlikovanje med velikimi in malimi črkami je odvisno od dveh dejavnikov:

1. Pomožni stolpec mora biti skrajno levi stolpec v vidnem območju.
2. Vrednost, ki jo iščete, mora vsebovati kodo znakov namesto prave vrednosti.

Kako pravilno uporabljati funkcijo CODE

Formula, vstavljena v celice pomožnega stolpca, predvideva, da imajo vse vaše iskalne vrednosti enako število znakov. Če ne, potem morate poznati najmanjše in največje številke ter dodati toliko funkcij NAPAKA (IFERROR) koliko znakov je razlika med najkrajšo in najdaljšo iskano vrednostjo.

Na primer, če je najkrajša iskalna vrednost 3 znaki in najdaljša 5 znakov, uporabite to formulo:

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

Za funkcijo PSTR (MID) Navedete naslednje argumente:

• 1. argument – besedilo (besedilo) je besedilo ali sklic na celico, ki vsebuje znake, ki jih je treba ekstrahirati (v našem primeru je B2)
• 2. argument – začetna_številka (začetni_položaj) je položaj prvega od teh znakov, ki jih je treba ekstrahirati. vstopiš 1 v prvi funkciji PSTR, 2 – v drugi funkciji PSTR in tako naprej
• 3. argument – številskih znakov (število_znakov) – Določa število znakov, ki jih je treba izvleči iz besedila. Ker ves čas potrebujemo samo 1 znak, v vseh funkcijah pišemo 1.

OMEJITVE: funkcija VPR ni najboljša rešitev za iskanje v Excelu, ki razlikuje med velikimi in malimi črkami. Najprej je treba dodati pomožni stolpec. Drugič, formula deluje dobro le, če so podatki homogeni ali je znano natančno število znakov v iskanih vrednostih. Če to ni vaš primer, je bolje, da uporabite eno od rešitev, ki jih prikazujemo spodaj.

Funkcija LOOKUP za iskanje, ki razlikuje med velikimi in malimi črkami

funkcija OGLED (LOOKUP) povezano VPR, vendar njegova sintaksa omogoča iskanje, ki razlikuje med velikimi in malimi črkami, brez dodajanja pomožnega stolpca. Če želite to narediti, uporabite OGLED v kombinaciji s funkcijo Točno (TOČNO).

Če vzamemo podatke iz prejšnjega primera (brez pomožnega stolpca), bo naslednja formula kos nalogi:

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

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

Formula išče v območju A2: A7 natančno ujemanje z vrednostjo celice F2 razlikuje med velikimi in malimi črkami in vrne vrednost iz stolpca B iste vrstice.

Kot VPRfunkcija OGLED deluje enako z besedilnimi in številskimi vrednostmi, kot lahko vidite na spodnjem posnetku zaslona:

Pomembno! Za funkcijo OGLED delovalo pravilno, morajo biti vrednosti v stolpcu za iskanje razvrščene v naraščajočem vrstnem redu, tj. od najmanjše do največje.

Naj na kratko razložim, kako funkcija deluje Točno v zgornji formuli, saj je to ključna točka.

funkcija Točno primerja dve besedilni vrednosti v 1. in 2. argumentu in vrne TRUE, če sta popolnoma enaki, ali FALSE, če nista. Za nas je pomembno, da funkcija Točno občutljivo na velike in male črke.

Poglejmo, kako deluje naša formula OGLED+TOČNO:

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

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

• funkcija Točno primerja vrednost celice F2 z vsemi elementi v stolpcu A (A2:A7). Vrne TRUE, če je najdeno natančno ujemanje, sicer FALSE.
• Ker podate prvi argument funkcije OGLED vrednost TRUE, izvleče ustrezno vrednost iz navedenega stolpca (v našem primeru stolpca B) le, če je najdeno natančno ujemanje, občutljivo na velike in male črke.

Upam, da je bila ta razlaga jasna in da zdaj razumete glavno idejo. Če je tako, potem ne boste imeli težav z drugimi funkcijami, ki jih bomo analizirali naprej, ker. vsi delujejo po istem principu.

OMEJITVE: Podatki v stolpcu za iskanje morajo biti razvrščeni v naraščajočem vrstnem redu.

SUMPRODUCT – najde besedilne vrednosti, razlikuje med velikimi in malimi črkami, vendar vrne samo številke

Kot ste razumeli že iz naslova, SUMPRODUCT (SUMPRODUCT) je še ena Excelova funkcija, ki vam bo pomagala pri iskanju z upoštevanjem velikih in malih črk, vendar bo vrnila samo številske vrednosti. Če vam ta možnost ne ustreza, lahko takoj nadaljujete s svežnjem INDEKS+UJEMANJE, ki daje rešitev za vsak primer in za vse vrste podatkov.

Najprej naj na kratko razložim sintakso te funkcije, kar vam bo pomagalo bolje razumeti naslednjo formulo, ki razlikuje med velikimi in malimi črkami.

funkcija SUMPRODUCT pomnoži elemente danih nizov in vrne vsoto rezultatov. Sintaksa izgleda takole:

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

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

Ker potrebujemo iskanje, ki razlikuje med velikimi in malimi črkami, uporabimo funkcijo Točno (TOČNO) iz prejšnjega primera kot enega od množiteljev:

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

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

Kot se spomnite, Točno primerja vrednost celice F2 z vsemi elementi v stolpcu A. Vrne TRUE, če je najdeno natančno ujemanje, sicer FALSE. Pri matematičnih operacijah Excel vzame TRUE kot 1, in FALSE za 0Nadaljnje SUMPRODUCT ta števila pomnoži in rezultate sešteje.

Ničle se ne štejejo, ker pri množenju vedno dajo 0. Oglejmo si podrobneje, kaj se zgodi, ko je v stolpcu natančno ujemanje A našli in vrnili 1… Funkcija SUMPRODUCT pomnoži število v stolpcu B on 1 in vrne rezultat – popolnoma isto število! To je zato, ker so rezultati drugih produktov enaki nič in ne vplivajo na končno vsoto.

Na žalost funkcija SUMPRODUCT ne more delovati z besedilnimi vrednostmi in datumi, ker jih ni mogoče pomnožiti. V tem primeru boste prejeli sporočilo o napaki #VALUE! (#VREDNOST!) kot v celici F4 na spodnji sliki:

OMEJITVE: Vrne samo številske vrednosti.

INDEX + MATCH – iskanje z upoštevanjem velikih in malih črk za katero koli vrsto podatkov

Končno smo blizu neomejeni iskalni formuli, ki razlikuje med velikimi in malimi črkami, ki deluje s katerim koli naborom podatkov.

Ta primer je zadnji, ne zato, ker je najboljše ostalo za posladek, temveč zato, ker vam bo znanje, pridobljeno iz prejšnjih primerov, pomagalo bolje in hitreje razumeti formulo, ki razlikuje med velikimi in malimi črkami. INDEKS+UJEMANJE (KAZALO+UDJEMANJE).

Kot ste verjetno uganili, kombinacija funkcij BOLJ IZPOSTAVLJENO и INDEX uporablja v Excelu kot bolj prilagodljiva in zmogljiva alternativa za VPR. Članek Uporaba INDEX in MATCH namesto VLOOKUP bo odlično pojasnil, kako ti funkciji delujeta skupaj.

Samo povzel bom ključne točke:

• funkcija BOLJ IZPOSTAVLJENO (MATCH) išče vrednost v danem obsegu in vrne njen relativni položaj, to je številko vrstice in/ali stolpca;
• Nato funkcija INDEX (INDEX) vrne vrednost iz določenega stolpca in/ali vrstice.

Za formulo INDEKS+UJEMANJE lahko išče z upoštevanjem velikih in malih črk, mu morate dodati samo eno funkcijo. Spet ni težko uganiti, za kaj gre Točno (TOČNO):

=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 tej formuli Točno deluje na enak način kot v povezavi s funkcijo OGLED, in daje enak rezultat:

Upoštevajte, da formula INDEKS+UJEMANJE v zavitih oklepajih je matrična formula in jo morate dokončati s pritiskom na Ctrl + Shift + Enter.

Zakaj je INDEX+MATCH najboljša rešitev za iskanje, ki razlikuje med velikimi in malimi črkami?

Glavne prednosti paketa INDEX и BOLJ IZPOSTAVLJENO:

1. Ne zahteva dodajanja pomožnega stolpca, za razliko od VPR.
2. Ne zahteva, da je iskalni stolpec razvrščen, za razliko od OGLED.
3. Deluje z vsemi vrstami podatkov – številkami, besedilom in datumi.

Ta formula se zdi popolna, kajne? Pravzaprav ni. In zato.

Predpostavimo, da je celica v stolpcu povratne vrednosti, povezana z iskalno vrednostjo, prazna. Kakšen rezultat bo vrnila formula? ne? Poglejmo, kaj formula dejansko vrne:

Ups, formula vrne nič! To morda ni velika težava, če delate s čistimi besedilnimi vrednostmi. Če pa tabela vsebuje številke, vključno s »pravimi« ničlami, to postane problem.

Pravzaprav se vse druge formule za iskanje (VLOOKUP, LOOKUP in SUMPRODUCT), o katerih smo razpravljali prej, obnašajo enako. Ampak želite popolno formulo, kajne?

Da bo formula občutljiva na velike in male črke INDEKS+UJEMANJE popolno, postavite ga v funkcijo IF (IF), ki bo preizkusil celico s povratno vrednostjo in vrnil prazen rezultat, če je prazna:

=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 tej formuli:

• B je stolpec s povratnimi vrednostmi
• 1+ je število, ki obrne relativni položaj celice, ki ga vrne funkcija BOLJ IZPOSTAVLJENO, na pravi naslov celice. Na primer v naši funkciji BOLJ IZPOSTAVLJENO dano iskalno polje A2: A7, to je relativni položaj celice A2 bo 1, ker je prvi v nizu. Toda dejanski položaj celice A2 v stolpcu je 2, zato dodajamo 1nadomestiti razliko in imeti funkcijo INDIRECT (INDIRECT) je pridobil vrednost iz želene celice.

Spodnje slike prikazujejo popravljeno formulo, ki razlikuje med velikimi in malimi črkami INDEKS+UJEMANJE V akciji. Če je vrnjena celica prazna, vrne prazen rezultat.

Formulo sem prepisal v stolpce B:Dda se vrstica formule prilega posnetku zaslona.

Formula se vrne 0če vrnjena celica vsebuje nič.

Če želite povezavo INDEX и BOLJ IZPOSTAVLJENO prikaže neko sporočilo, ko je vrnjena vrednost prazna, jo lahko zapišete v zadnje narekovaje (“”) formule, na primer takole:

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