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

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

Pretpostavljam da svaki korisnik Excela zna koja funkcija vrši vertikalno 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čni za njega.

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

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

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

… će zaustaviti svoju pretragu na "računu" jer je ta vrijednost prva na listi i izdvojiti vrijednost iz ćelije B1.

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

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

• VLOOKUP osjetljiv na velika i mala slova – zahtijeva pomoćnu kolonu
• Funkcija LOOKUP osjetljiva na velika i mala slova – zahtijeva sortiranje podataka
• ZBIRNI PROIZVOD – vraća samo numeričke vrijednosti
• INDEX+MATCH – pretraživanje osjetljivo na velika i mala slova za sve tipove podataka

Funkcija VLOOKUP je osjetljiva na velika i mala slova

Kao što već znate, uobičajena funkcija VPR je neosjetljivo na 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ćnu kolonu u tablicu, kao što je prikazano u sljedećem primjeru.

Pretpostavimo u koloni B postoje identifikatori proizvoda (stavka) i želite izvući 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 zadnjeg znaka, u и U respektivno.

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 je u opsegu pretrage ranije od 001Tvci3U. Ali to nije ono što nam treba, zar ne?

Za pretraživanje pomoću funkcije VPR u Excelu osjetljivom na velika i mala slova, morat ćete dodati pomoćni stupac i ispuniti njegove ćelije sljedećom formulom (gdje je B kolona 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 razbija željenu vrijednost u zasebne znakove, zamjenjuje svaki znak svojim kodom (na primjer, umjesto A na 65, umjesto toga a kod 97), a zatim kombinuje ove 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;ЛОЖЬ)

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

1. Kolona pomoćnika mora biti krajnja lijeva kolona u vidljivom rasponu.
2. Vrijednost koju tražite mora sadržavati karakterni kod umjesto stvarne vrijednosti.

Kako pravilno koristiti funkciju KOD

Formula umetnuta u ćelije pomoćne kolone 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 funkcija IFERROR (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, koristite 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) Navodite 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 - start_num (početna_pozicija) je pozicija prvog od tih znakova koji se izdvajaju. ulaziš 1 u prvoj funkciji PSTR, 2 – u drugoj funkciji PSTR itd
• 3. argument - broj_znakova (broj_karaktera) – Određuje broj znakova za izdvajanje iz teksta. Pošto nam je stalno potreban samo 1 znak, u svim funkcijama pišemo 1.

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

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

funkcija VIEW (LOOKUP) vezano VPR, međutim, njegova sintaksa dozvoljava pretraživanja osjetljiva na velika i mala slova bez dodavanja pomoćne kolone. Da biste to učinili, koristite VIEW u kombinaciji sa funkcijom TOČNO (TAČ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)

Pretraživanje formule u rasponu A2:A7 tačno podudaranje sa vrijednošću ćelije F2 razlikuje velika i mala slova i vraća vrijednost iz kolone B istog reda.

kao VPRFunkcija VIEW radi jednako s tekstualnim i numeričkim vrijednostima, kao što možete vidjeti na slici ispod:

Važno! Da bi funkcija VIEW radi ispravno, vrijednosti u koloni za traženje treba sortirati uzlaznim redoslijedom, tj. od najmanjeg prema najvećem.

Dozvolite mi da ukratko objasnim kako funkcija radi TOČNO u formuli prikazanoj iznad, jer je to ključna tač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. Važno nam je da funkcija TOČNO velika i mala slova.

Pogledajmo kako naša formula funkcionira POGLED+TAČ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 upoređuje vrijednost ćelije F2 sa svim elementima u koloni A (A2:A7). Vraća TRUE ako se pronađe točno podudaranje, u suprotnom FALSE.
• Pošto ste dali prvi argument funkcije VIEW vrijednost TRUE, izdvaja odgovarajuću vrijednost iz specificirane kolone (u našem slučaju kolone B) samo ako je pronađeno tač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 koloni za pretraživanje moraju biti sortirani uzlaznim redoslijedom.

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

Kao što ste već shvatili iz naslova, SUMPRODUCT (SUMPRODUCT) je još jedna Excel funkcija koja će vam pomoći u pretraživanju osjetljivom na velika i mala slova, ali će vratiti samo numeričke vrijednosti. Ako vam ova opcija ne odgovara, možete odmah nastaviti na paket INDEKS+PODRŽAVANJE, koji daje rješenje za svaki slučaj i za bilo koju vrstu podataka.

Prvo, dozvolite mi da ukratko objasnim sintaksu ove funkcije, to će vam pomoći da bolje razumijete formulu osjetljivu na velika i mala slova koja slijedi.

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

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

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

Pošto nam je potrebna pretraga osjetljiva na velika i mala slova, koristimo funkciju TOČNO (TAČ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 sećate TOČNO upoređuje vrijednost ćelije F2 sa svim elementima u koloni A. Vraća TRUE ako se pronađe točno podudaranje, u suprotnom FALSE. U matematičkim operacijama, Excel uzima TRUE kao 1, i FALSE za 0Dalje SUMPRODUCT množi ove brojeve i zbraja rezultate.

Nule se ne računaju jer kada se pomnože uvijek daju 0. Pogledajmo bliže šta se dešava kada se tačno podudara u koloni A pronađen i vraćen 1… Funkcija SUMPRODUCT množi broj u koloni B on 1 i vraća rezultat – potpuno isti broj! To je zato što su rezultati ostalih proizvoda jednaki nuli i ne utiču na rezultujuću sumu.

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 grešci # VALUE! (#VRIJEDNOST!) kao u ćeliji F4 na slici ispod:

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

INDEX + MATCH – pretraživanje bilo kojeg tipa podataka osjetljivo na velika i mala slova

Konačno, blizu smo neograničenoj formuli pretraživanja koja je osjetljiva na velika i mala slova koja radi s bilo kojim skupom podataka.

Ovaj primjer dolazi posljednji, ne zato što je najbolje ostavljeno za desert, već zato što će vam znanje stečeno iz prethodnih primjera pomoći da bolje i brže shvatite formulu osjetljivu na velika i mala slova. INDEKS+PODRŽAVANJE (INDEKS+PODRŽAVANJE).

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

Samo ću rezimirati ključne tačke:

• funkcija IZLOŽENIJI (MATCH) traži vrijednost u datom rasponu i vraća njenu relativnu poziciju, odnosno broj reda i/ili stupca;
• Dalje, funkcija INDEX (INDEX) vraća vrijednost iz specificirane kolone i/ili reda.

Za formulu INDEKS+PODRŽAVANJE može pretraživati ​​osjetljivo na velika i mala slova, trebate mu dodati samo jednu funkciju. Nije teško ponovo pogoditi šta je to TOČNO (TAČ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 sprezi sa funkcijom VIEW, i daje isti rezultat:

Imajte na umu da je formula INDEKS+PODRŽAVANJE zatvorena u vitičaste zagrade je formula niza i morate je dovršiti pritiskom Ctrl + Shift + Enter.

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

Glavne prednosti paketa INDEX и IZLOŽENIJI:

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

Ova formula izgleda savršeno, zar ne? Zapravo, nije. I zato.

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

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

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

Da formula bude osjetljiva na velika i mala slova INDEKS+PODRŽAVANJE savršeno, stavi u funkciju IF (IF) koji će testirati ćeliju s povratnom vrijednošću i vratiti prazan rezultat ako je prazan:

=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 relativnu poziciju ćelije koju vraća funkcija IZLOŽENIJI, na pravu adresu ćelije. Na primjer, u našoj funkciji IZLOŽENIJI dat niz pretraživanja A2:A7, odnosno relativni položaj ćelije A2 volja 1, jer je prvi u nizu. Ali stvarni položaj ćelije A2 u koloni je 2, pa dodajemo 1da nadoknadi razliku i da ima funkciju INDIREKTNO (INDIREKTNO) preuzima vrijednost iz željene ćelije.

Slike ispod pokazuju ispravljenu formulu koja razlikuje velika i mala slova INDEKS+PODRŽAVANJE U akciji. Vraća prazan rezultat ako je vraćena ćelija prazna.

Prepisao sam formulu u kolone B:Dkako bi se uklopila traka formule na snimku ekrana.

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

Ako želite link INDEX и IZLOŽENIJI prikazuje 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.")