4 načina da VLOOKUP razlikuje velika i mala slova u Excelu

Ovaj mali vodič objašnjava kako napraviti funkciju VPR (VLOOKUP) osjetljiv na velika i mala slova, prikazuje nekoliko drugih formula koje Excel može pretraživati ​​na način koji razlikuje velika i mala slova te ističe prednosti i slabosti svake funkcije.

Pretpostavljam da svaki korisnik programa Excel zna koja funkcija izvodi okomito pretraživanje. Tako je, to je funkcija VPR. Međutim, malo ljudi to zna VPR ne razlikuje velika i mala slova, tj. mala i velika slova su identična za njega.

Evo kratkog primjera koji pokazuje nesposobnost VPR prepoznati registar. Pretpostavimo u ćeliji A1 sadrži vrijednost "račun" i ćeliju A2 – “Mjenica”, formula:

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

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

... zaustavit će svoju pretragu na "računu" jer je ta vrijednost prva na popisu i izvući vrijednost iz ćelije B1.

Kasnije u ovom članku pokazat ću vam kako to učiniti VPR osjetljivo na velika i mala slova. Osim toga, naučit ćemo još nekoliko funkcija koje mogu izvoditi pretraživanja osjetljiva na velika i mala slova u Excelu.

Počet ćemo s najjednostavnijim – POGLED (POTRAGA) i SUMPRODUCT (SUMPRODUCT), koji, nažalost, imaju nekoliko značajnih ograničenja. Zatim ćemo pobliže pogledati malo složeniju formulu INDEKS+MATCH (INDEX+MATCH), koji radi besprijekorno u svakoj situaciji i sa bilo kojim skupom podataka.

• VLOOKUP koji razlikuje velika i mala slova – zahtijeva pomoćni stupac
• Funkcija LOOKUP koja razlikuje velika i mala slova – zahtijeva sortiranje podataka
• SUMPRODUCT – vraća samo numeričke vrijednosti
• INDEX+MATCH – pretraživanje osjetljivo na velika i mala slova za sve vrste podataka

Funkcija VLOOKUP razlikuje velika i mala slova

Kao što već znate, uobičajena funkcija VPR ne razlikuje velika i mala slova. Međutim, postoji način da ga učinite osjetljivim na velika i mala slova. Da biste to učinili, trebate dodati pomoćni stupac u tablicu, kao što je prikazano u sljedećem primjeru.

Pretpostavimo u stupcu B postoje identifikatori proizvoda (artikl) i želite izdvojiti cijenu proizvoda i odgovarajući komentar iz stupaca C и D. Problem je u tome što identifikatori sadrže i mala i velika slova. Na primjer, vrijednosti ćelija B4 (001Tvci3u) i B5 (001Tvci3U) razlikuju se samo u slučaju posljednjeg znaka, u и U odnosno.

Kao što možete zamisliti, uobičajena formula za pretraživanje

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

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

će se vratiti $ 90, budući da je vrijednost 001Tvci3u nalazi se u rasponu pretraživanja prije nego 001Tvci3U. Ali to nije ono što nam treba, zar ne?

Za pretraživanje pomoću funkcije VPR u Excelu koji razlikuje velika i mala slova, morat ćete dodati pomoćni stupac i ispuniti njegove ćelije sljedećom formulom (gdje je B stupac za traženje):

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

Ova formula rastavlja željenu vrijednost u zasebne znakove, zamjenjuje svaki znak svojim kodom (na primjer, umjesto A u 65, umjesto toga a kod 97) i zatim kombinira te kodove u jedinstveni niz brojeva.

Nakon toga koristimo jednostavnu funkciju VPR za pretraživanje osjetljivo na velika i mala slova:

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

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

Pravilan rad funkcije VPR osjetljivost na velika i mala slova ovisi o dva faktora:

1. Pomoćni stupac mora biti krajnji lijevi stupac u vidljivom rasponu.
2. Vrijednost koju tražite mora sadržavati kod znakova umjesto prave vrijednosti.

Kako pravilno koristiti funkciju CODE

Formula umetnuta u ćelije pomoćnog stupca pretpostavlja da sve vaše vrijednosti pretraživanja imaju isti broj znakova. Ako ne, onda morate znati najmanji i najveći broj i dodati što više značajki AKO POGREŠKA (IFERROR) koliko je znakova razlika između najkraće i najduže tražene vrijednosti.

Na primjer, ako je najkraća vrijednost pretraživanja 3 znaka, a najduža 5 znakova, upotrijebite ovu formulu:

=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 funkciju PSTR (MID) Dajete sljedeće argumente:

• 1. argument – tekst (tekst) je tekst ili referenca ćelije koja sadrži znakove koje treba izdvojiti (u našem slučaju to je B2)
• 2. argument – početni_broj (start_position) je položaj prvog od tih znakova koji se izdvaja. ulaziš 1 u prvoj funkciji PSTR, 2 – u drugoj funkciji PSTR i tako dalje
• 3. argument – broj_znakova (number_of_characters) – Određuje broj znakova za izdvajanje iz teksta. Budući da nam stalno treba samo 1 znak, u svim funkcijama koje pišemo 1.

OGRANIČENJA: funkcija VPR nije najbolje rješenje za pretraživanje u Excelu koje razlikuje velika i mala slova. Prvo, potrebno je dodati pomoćni stupac. Drugo, formula radi dobar posao samo ako su podaci homogeni ili je poznat točan broj znakova u traženim vrijednostima. Ako to nije vaš slučaj, bolje je upotrijebiti jedno od rješenja koja prikazujemo u nastavku.

LOOKUP funkcija za pretraživanje osjetljivo na velika i mala slova

funkcija POGLED (LOOKUP) povezano VPR, međutim njegova sintaksa dopušta pretraživanja koja razlikuju velika i mala slova bez dodavanja pomoćnog stupca. Da biste to učinili, koristite POGLED u kombinaciji s funkcijom TOČNO (TOČNO).

Ako uzmemo podatke iz prethodnog primjera (bez pomoćnog stupca), tada će se sljedeća formula nositi sa zadatkom:

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

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

Formula traži u rasponu A2: A7 točno podudaranje s vrijednošću ćelije F2 razlikuje velika i mala slova i vraća vrijednost iz stupca B istog retka.

Like VPRfunkcija POGLED radi jednako s tekstualnim i numeričkim vrijednostima, kao što možete vidjeti na snimci zaslona u nastavku:

Važno! Kako bi funkcija POGLED radilo ispravno, vrijednosti u stupcu pretraživanja trebale bi biti poredane uzlaznim redoslijedom, tj. od najmanje prema najvećoj.

Dopustite mi da ukratko objasnim kako funkcija radi TOČNO u gore prikazanoj formuli, jer je to ključna točka.

funkcija TOČNO uspoređuje dvije tekstualne vrijednosti u 1. i 2. argumentu i vraća TRUE ako su potpuno iste, ili FALSE ako nisu. Bitno nam je da funkcija TOČNO osjetljivo na velika i mala slova.

Pogledajmo kako funkcionira naša formula POGLED+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 uspoređuje vrijednost ćelije F2 sa svim elementima u stupcu A (A2:A7). Vraća TRUE ako se pronađe točno podudaranje, inače FALSE.
• Budući da dajete prvi argument funkcije POGLED vrijednost TRUE, izvlači odgovarajuću vrijednost iz navedenog stupca (u našem slučaju, stupca B) samo ako se pronađe točno podudaranje, osjetljivo na velika i mala slova.

Nadam se da je ovo objašnjenje bilo jasno i da sada razumijete glavnu ideju. Ako je tako, onda nećete imati poteškoća s drugim funkcijama koje ćemo dalje analizirati, jer. svi rade na istom principu.

OGRANIČENJA: Podaci u stupcu pretraživanja moraju biti poredani uzlaznim redoslijedom.

SUMPRODUCT – pronalazi tekstualne vrijednosti, razlikuje velika i mala slova, ali vraća samo brojeve

Kao što ste već shvatili iz naslova, SUMPRODUCT (SUMPRODUCT) još je jedna Excelova funkcija koja će vam pomoći u pretraživanju s razlikovanjem velikih i malih slova, ali će vratiti samo numeričke vrijednosti. Ako vam ova opcija ne odgovara, možete odmah prijeći na paket INDEKS+MATCH, koji daje rješenje za svaki slučaj i za sve vrste podataka.

Prvo, dopustite mi da ukratko objasnim sintaksu ove funkcije, to će vam pomoći da bolje razumijete formulu koja slijedi.

funkcija SUMPRODUCT množi elemente zadanih nizova i vraća zbroj rezultata. Sintaksa izgleda ovako:

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

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

Budući da nam je potrebna pretraga koja razlikuje velika i mala slova, koristimo ovu funkciju TOČNO (TOČNO) iz prethodnog primjera kao jedan od množitelja:

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

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

Kao što se sjećate, TOČNO uspoređuje vrijednost ćelije F2 sa svim elementima u stupcu A. Vraća TRUE ako se pronađe točno podudaranje, inače FALSE. U matematičkim operacijama Excel uzima TRUE kao 1, i FALSE za 0Unaprijediti SUMPRODUCT množi te brojeve i zbraja rezultate.

Nule se ne broje jer kad se pomnože uvijek daju 0. Pogledajmo pobliže što se događa kada postoji točno podudaranje u stupcu A pronašao i vratio 1… Funkcija SUMPRODUCT množi broj u stupcu B on 1 i vraća rezultat – potpuno isti broj! To je zato što su rezultati ostalih proizvoda jednaki nuli i ne utječu na rezultirajući zbroj.

Nažalost, funkcija SUMPRODUCT ne može raditi s tekstualnim vrijednostima i datumima jer se ne mogu množiti. U tom slučaju dobit ćete poruku o pogrešci #VRIJEDNOST! (#VRIJEDNOST!) kao u ćeliji F4 na slici ispod:

OGRANIČENJA: Vraća samo numeričke vrijednosti.

INDEX + MATCH – pretraživanje osjetljivo na velika i mala slova za bilo koju vrstu podataka

Konačno, blizu smo neograničene formule za pretraživanje koja razlikuje velika i mala slova koja radi s bilo kojim skupom podataka.

Ovaj primjer je posljednji, ne zato što je najbolje ostalo za desert, već zato što će vam znanje stečeno iz prethodnih primjera pomoći da bolje i brže razumijete formulu koja razlikuje velika i mala slova. INDEKS+MATCH (INDEX+MATCH).

Kao što ste vjerojatno pogodili, kombinacija funkcija EKSPONIRANIJI и INDEKS koristi se u Excelu kao fleksibilnija i snažnija alternativa za VPR. Članak Korištenje INDEX-a i MATCH-a umjesto VLOOKUP-a savršeno će objasniti kako ove funkcije rade zajedno.

Samo ću rezimirati ključne točke:

• funkcija EKSPONIRANIJI (MATCH) traži vrijednost u zadanom rasponu i vraća njen relativni položaj, to jest broj retka i/ili stupca;
• Zatim, funkcija INDEKS (INDEX) vraća vrijednost iz navedenog stupca i/ili retka.

Za formuliranje INDEKS+MATCH mogao pretraživati ​​s razlikovanjem velikih i malih slova, trebate mu dodati samo jednu funkciju. Nije teško pogoditi o čemu se opet radi 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))

U ovoj formuli TOČNO radi na isti način kao u kombinaciji s funkcijom POGLED, i daje isti rezultat:

Imajte na umu da formula INDEKS+MATCH u vitičastim zagradama je formula polja i morate je dovršiti pritiskom na Ctrl + Shift + Enter.

Zašto je INDEX+MATCH najbolje rješenje za pretraživanje koje razlikuje velika i mala slova?

Glavne prednosti paketa INDEKS и EKSPONIRANIJI:

1. Ne zahtijeva dodavanje pomoćnog stupca, za razliku od VPR.
2. Ne zahtijeva sortiranje stupca za pretraživanje, za razliku od POGLED.
3. Radi sa svim vrstama podataka – brojevima, tekstom i datumima.

Ova formula se čini savršenom, zar ne? Zapravo nije. I zato.

Pretpostavimo da je ćelija u stupcu povratne vrijednosti povezana s vrijednošću pretraživanja prazna. Kakav će rezultat dati formula? Ne? Pogledajmo što formula zapravo vraća:

Ups, formula vraća nulu! Ovo možda neće biti veliki problem ako radite s čistim tekstualnim vrijednostima. Međutim, ako tablica sadrži brojeve, uključujući "prave" nule, to postaje problem.

Zapravo, sve ostale formule pretraživanja (VLOOKUP, LOOKUP i SUMPRODUCT) o kojima smo ranije govorili ponašaju se na isti način. Ali želite savršenu formulu, zar ne?

Da bi formula bila osjetljiva na velika i mala slova INDEKS+MATCH savršeno, stavi ga u funkciju IF (IF) koji će testirati ćeliju s povratnom vrijednošću i vratiti prazan rezultat ako 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));"")

U ovoj formuli:

• B je stupac s povratnim vrijednostima
• 1+ je broj koji okreće relativni položaj ćelije koju vraća funkcija EKSPONIRANIJI, na pravu adresu ćelije. Na primjer, u našoj funkciji EKSPONIRANIJI traženi niz dano A2: A7, odnosno relativni položaj ćelije A2 volja 1, jer je prvi u nizu. Ali stvarni položaj ćelije A2 u stupcu je 2, pa dodajemo 1nadoknaditi razliku i imati funkciju INDIREKTNO (INDIRECT) je dohvatio vrijednost iz željene ćelije.

Slike u nastavku prikazuju ispravljenu formulu koja razlikuje velika i mala slova INDEKS+MATCH U akciji. Vraća prazan rezultat ako je vraćena ćelija prazna.

Prepisao sam formulu u stupce B:Dda stane traka formule na snimku zaslona.

Formula se vraća 0ako vraćena ćelija sadrži nulu.

Ako želite link INDEKS и EKSPONIRANIJI prikazao neku poruku kada je povratna vrijednost prazna, možete je napisati u zadnjim navodnicima (“”) formule, na primjer, ovako:

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