Kaundan
Kini nga gamay nga panudlo nagpatin-aw kung giunsa paghimo ang usa ka function VPR (VLOOKUP) case-sensitive, nagpakita sa daghang uban pang mga pormula nga mapangita sa Excel sa usa ka case-sensitive nga paagi, ug nagpunting sa mga kusog ug kahuyang sa matag function.
Sa akong hunahuna ang matag tiggamit sa Excel nahibal-an kung unsang function ang naghimo sa bertikal nga pagpangita. Husto, kini usa ka function VPR. Apan, pipila lang ka tawo ang nahibalo niana VPR dili case sensitive, ie ang lower ug upper case nga mga karakter parehas niini.
Ania ang usa ka dali nga pananglitan nga nagpakita sa kawalay katakus VPR pag-ila sa rehistro. Ibutang ta sa usa ka selda A1 naglangkob sa kantidad nga "bill" ug ang cell A2 – “Bill”, pormula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... mohunong sa pagpangita niini sa "bill" tungod kay kana nga bili nag-una sa listahan, ug kuhaon ang bili gikan sa cell B1.
Sa ulahi niini nga artikulo, ipakita ko kanimo kung unsaon pagbuhat VPR sensitibo sa kaso. Dugang pa, makakat-on kami og pipila pa nga mga gimbuhaton nga makahimo sa mga pagpangita sa case-sensitive sa Excel.
Magsugod kita sa pinakasimple - PANGUTANA (LOOKUP) ug SUMPRODUCT (SUMPRODUCT), nga, sa walay palad, adunay daghang hinungdanon nga mga limitasyon. Sunod, atong tan-awon pag-ayo ang gamay nga mas komplikado nga pormula INDEX+MATCH (INDEX+MATCH), nga wala’y mahimo sa bisan unsang sitwasyon ug sa bisan unsang dataset.
Ang VLOOKUP function kay case sensitive
Sama sa nahibal-an na nimo, ang naandan nga function VPR kay case insensitive. Bisan pa, adunay usa ka paagi aron mahimo kini nga sensitibo sa kaso. Aron mahimo kini, kinahanglan nimo nga idugang ang usa ka kolum sa auxiliary sa lamesa, ingon sa gipakita sa mosunod nga pananglitan.
Ibutang ta sa usa ka kolum B adunay mga identifier sa produkto (Item) ug gusto nimong makuha ang presyo sa produkto ug ang katugbang nga komento gikan sa mga kolum. C и D. Ang problema kay ang mga identifier adunay duha ka lowercase ug uppercase nga mga karakter. Pananglitan, ang mga kantidad sa cell B4 (001Tvci3u) ug B5 (001Tvci3U) lahi lamang sa kaso sa katapusang karakter, u и U matag.
Sama sa imong mahanduraw, ang naandan nga pormula sa pagpangita
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
mobalik $ 90, sukad sa bili 001Tvci3u naa sa search range nga mas sayo kaysa 001Tvci3U. Apan dili kana ang atong gikinahanglan, di ba?
Sa pagpangita sa usa ka function VPR sa Excel case sensitive, kinahanglan nimong idugang ang usa ka helper column ug pun-on ang mga cell niini sa mosunod nga pormula (diin ang B mao ang lookup column):
=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));"")
Kini nga pormula nagbungkag sa gitinguha nga bili ngadto sa lain nga mga karakter, nag-ilis sa matag karakter sa iyang code (pananglitan, imbes sa A sa 65, sa baylo a code 97) ug dayon gikombinar kini nga mga code ngadto sa usa ka talagsaon nga hugpong sa mga numero.
Pagkahuman niana, gigamit namon ang usa ka yano nga function VPR para sa case sensitive nga pagpangita:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Tukma nga operasyon sa function VPR case-sensitive nagdepende sa duha ka mga butang:
- Ang helper column kinahanglang mao ang pinakawala nga column sa viewable range.
- Ang kantidad nga imong gipangita kinahanglan adunay usa ka code sa karakter imbes sa tinuud nga kantidad.
Giunsa paggamit ang function sa CODE sa husto
Ang pormula nga gisulod sa mga selyula sa auxiliary nga kolum nagtuo nga ang tanan nimo nga mga kantidad sa pagpangita adunay parehas nga gidaghanon sa mga karakter. Kung dili, nan kinahanglan nimo mahibal-an ang pinakagamay ug pinakadako nga numero ug idugang ang daghang mga bahin IFERROR (IFERROR) pila ka mga karakter ang kalainan tali sa pinakamubo ug pinakadugay nga gipangita nga bili.
Pananglitan, kung ang pinakamubo nga bili sa pagpangita kay 3 ka karakter ug ang pinakataas 5 ka karakter, gamita kini nga pormula:
=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));"")
Para sa function PSTR (MID) Imong gihatag ang mosunod nga mga argumento:
- 1st argumento - teksto (teksto) mao ang teksto o cell reference nga adunay sulod nga mga karakter nga makuha (sa among kaso kini B2)
- 2st argumento - pagsugod_num (start_position) mao ang posisyon sa una sa mga karakter nga makuha. mosulod ka 1 sa unang function PSTR, 2 – sa ikaduhang function PSTR ug uban pa
- 3st argumento - num_chars (number_of_characters) – Nagtino sa gidaghanon sa mga karakter nga makuha gikan sa teksto. Tungod kay kinahanglan ra namon ang 1 nga karakter sa tanan nga oras, sa tanan nga mga gimbuhaton among gisulat 1.
LIMITASYON: function VPR dili ang labing maayo nga solusyon alang sa case-sensitive nga pagpangita sa Excel. Una, gikinahanglan ang pagdugang sa usa ka auxiliary column. Ikaduha, ang pormula usa ka maayong trabaho kung ang datos parehas, o nahibal-an ang eksaktong gidaghanon sa mga karakter sa gipangita nga mga kantidad. Kung dili kini ang imong kaso, mas maayo nga gamiton ang usa sa mga solusyon nga among gipakita sa ubos.
LOOKUP function para sa case sensitive nga pagpangita
function PANGUTANA (LOOKUP) nga may kalabutan VPR, bisan pa niana ang syntax niini nagtugot sa case-sensitive nga pagpangita nga walay pagdugang ug auxiliary column. Aron mahimo kini, gamita PANGUTANA inubanan sa function LABAW (EKSAKTO).
Kung atong kuhaon ang datos gikan sa miaging pananglitan (nga walay usa ka auxiliary nga kolum), nan ang mosunod nga pormula makasagubang sa buluhaton:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Mga pagpangita sa pormula sa range A2:A7 eksakto nga pagpares sa kantidad sa cell F2 case sensitive ug ibalik ang bili gikan sa column B sa samang laray.
Sama sa VPRfunction PANGUTANA parehas nga nagtrabaho sa teksto ug numeric nga mga kantidad, ingon sa imong makita sa screenshot sa ubos:
Importante! Para sa function PANGUTANA nagtrabaho sa hustong paagi, ang mga kantidad sa lookup column kinahanglang ihan-ay sa ascending order, ie gikan sa pinakagamay ngadto sa kinadak-an.
Tugoti ako sa mubo nga pagpasabut kung giunsa ang paglihok LABAW sa pormula nga gipakita sa ibabaw, tungod kay kini ang yawe nga punto.
function LABAW gitandi ang duha ka mga kantidad sa teksto sa 1st ug 2nd argumento ug mibalik sa TINUOD kung parehas ra sila, o FALSE kung dili. Importante alang kanato nga ang function LABAW sensitibo sa kaso.
Atong tan-awon kung giunsa ang atong pormula molihok TAN-AWA+EKSAKTO:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- function LABAW nagtandi sa bili sa selula F2 uban sa tanan nga mga elemento sa usa ka kolum A (A2:A7). Ibalik ang TINUOD kung makit-an ang eksaktong tugma, kung dili FALSE.
- Tungod kay gihatag nimo ang una nga argumento sa function PANGUTANA bili TINUOD, kini mokuha sa katugbang nga bili gikan sa espesipikong kolum (sa among kaso, kolum B) lamang kon ang usa ka tukma nga tugma makaplagan, case sensitive.
Nanghinaut ko nga kini nga katin-awan klaro ug karon imong nasabtan ang panguna nga ideya. Kung mao, nan wala ka'y bisan unsang mga kalisud sa ubang mga gimbuhaton nga among pag-analisar sa dugang, tungod kay. silang tanan nagtrabaho sa samang prinsipyo.
LIMITASYON: Ang datos sa kolum sa pagpangita kinahanglang ihan-ay sa pataas nga han-ay.
SUMPRODUCT – pangitag text values, case sensitive, pero numero ra ang ibalik
Sama sa imong nasabtan gikan sa titulo, SUMPRODUCT (SUMPRODUCT) mao ang lain nga Excel function nga makatabang kanimo sa paghimo sa usa ka case-sensitive nga pagpangita, apan ibalik lamang ang numeric values. Kung kini nga kapilian dili angay kanimo, nan mahimo ka nga magpadayon dayon sa bundle INDEX+MATCH, nga naghatag usa ka solusyon alang sa bisan unsang kaso ug alang sa bisan unsang mga tipo sa datos.
Una, tugoti ako sa mubo nga pagpatin-aw sa syntax niini nga function, kini makatabang kanimo nga mas masabtan ang case-sensitive nga pormula nga nagsunod.
function SUMPRODUCT gipadaghan ang mga elemento sa gihatag nga mga arrays ug gibalik ang sumada sa mga resulta. Ang syntax ingon niini:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Tungod kay kinahanglan namon ang usa ka case-sensitive nga pagpangita, among gigamit ang function LABAW (EXACT) gikan sa miaging pananglitan isip usa sa mga multiplier:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Sama sa imong nahinumduman, LABAW nagtandi sa bili sa selula F2 uban sa tanan nga mga elemento sa usa ka kolum A. Ibalik ang TINUOD kung makit-an ang eksaktong tugma, kung dili FALSE. Sa mga operasyon sa matematika, gikuha sa Excel ang TINUOD ingon 1, ug FALSE para sa 0Dugang pa SUMPRODUCT gipadaghan kini nga mga numero ug gisumada ang mga resulta.
Ang mga zero dili ihap tungod kay kung gipadaghan sila kanunay nga naghatag 0. Atong tan-awon pag-ayo kung unsa ang mahitabo kung ang usa ka eksaktong tugma sa usa ka kolum A nakit-an ug mibalik 1… Kalihokan SUMPRODUCT gipadaghan ang numero sa kolum B on 1 ug ibalik ang resulta - parehas nga numero! Kini tungod kay ang mga resulta sa ubang mga produkto zero, ug wala kini makaapekto sa resulta nga kantidad.
Ikasubo ang function SUMPRODUCT dili molihok sa mga kantidad sa teksto ug mga petsa tungod kay dili kini madugangan. Sa kini nga kaso, makadawat ka usa ka mensahe sa sayup #BILI! (#VALUE!) sama sa usa ka cell F4 sa hulagway sa ubos:
LIMITASYON: Nagbalik lang sa numeric values.
INDEX + MATCH – case-sensitive nga pagpangita alang sa bisan unsang tipo sa datos
Sa katapusan, hapit na kami sa usa ka walay limitasyon ug case-sensitive nga pormula sa pagpangita nga magamit sa bisan unsang data set.
Kini nga pananglitan moabut sa katapusan, dili tungod kay ang labing maayo nga nahabilin alang sa dessert, apan tungod kay ang kahibalo nga nakuha gikan sa miaging mga pananglitan makatabang kanimo nga masabtan ang case-sensitive nga pormula nga mas maayo ug mas paspas. INDEX+MATCH (INDEX+MATCH).
Sama sa imong nahunahunaan, ang kombinasyon sa mga gimbuhaton MAS EXPOSED и INDEX gigamit sa Excel isip usa ka mas flexible ug gamhanan nga alternatibo alang sa VPR. Ang artikulo nga Paggamit sa INDEX ug MATCH imbes sa VLOOKUP hingpit nga ipasabut kung giunsa kini nga mga gimbuhaton nagtinabangay.
I-recap lang nako ang mga importanteng punto:
- function MAS EXPOSED (MATCH) nangita og bili sa gihatag nga range ug ibalik ang relatibong posisyon niini, nga mao, ang numero sa laray ug/o kolum;
- Sunod, ang function INDEX (INDEX) nagbalik sa usa ka bili gikan sa usa ka piho nga kolum ug/o laray.
Sa pormula INDEX+MATCH makapangita sa case-sensitively, kinahanglan nimo nga idugang ang usa ka function niini. Dili lisud ang pagtag-an kung unsa kini pag-usab LABAW (EKSAKTO):
=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))
Niini nga pormula LABAW nagtrabaho sa parehas nga paagi sama sa inubanan sa function PANGUTANA, ug naghatag sa samang resulta:
Timan-i nga ang pormula INDEX+MATCH nga gilakip sa mga kulot nga braces usa ka array nga pormula ug kinahanglan nimo nga kompletohon kini pinaagi sa pagpindot Ctrl + Shift + Enter.
Ngano nga ang INDEX+MATCH ang labing maayo nga solusyon alang sa case-sensitive nga pagpangita?
Ang nag-unang bentaha sa bundle INDEX и MAS EXPOSED:
- Wala magkinahanglan og pagdugang og auxiliary column, dili sama VPR.
- Wala magkinahanglan sa kolum sa pagpangita nga masunod, dili sama PANGUTANA.
- Naglihok sa tanan nga mga matang sa datos - mga numero, teksto ug mga petsa.
Kini nga pormula morag perpekto, dili ba? Sa pagkatinuod, dili. Ug mao na.
Ibutang ta nga ang cell sa return value column nga nalangkit sa lookup value walay sulod. Unsang resulta ang ibalik sa pormula? Dili? Atong tan-awon kon unsa ang tinuod nga pagbalik sa pormula:
Oops, ang pormula mibalik ug zero! Mahimong dili kini usa ka dako nga problema kung nagtrabaho ka nga adunay puro nga kantidad sa teksto. Bisan pa, kung ang lamesa adunay mga numero, lakip ang "tinuod" nga mga sero, kini mahimong problema.
Sa tinuud, ang tanan nga uban pang mga pormula sa pagpangita (VLOOKUP, LOOKUP, ug SUMPRODUCT) nga among gihisgutan kaniadto parehas nga naggawi. Apan gusto nimo ang hingpit nga pormula, di ba?
Aron mahimo ang usa ka pormula nga sensitibo sa kaso INDEX+MATCH perpekto, ibutang kini sa usa ka function IF (KUNG) nga magsulay sa usa ka cell nga adunay usa ka pagbalik nga kantidad ug ibalik ang usa ka walay sulod nga resulta kung kini walay sulod:
=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));"")
Niini nga pormula:
- B usa ka kolum nga adunay mga kantidad sa pagbalik
- 1+ mao ang usa ka numero nga nagpabalik sa relatibong posisyon sa cell nga gibalik sa function MAS EXPOSED, ngadto sa tinuod nga adres sa selda. Pananglitan, sa atong function MAS EXPOSED search array gihatag A2:A7, nga mao, ang relatibong posisyon sa selula A2 kabubut-on 1, tungod kay kini ang una sa laray. Apan ang aktuwal nga posisyon sa selula A2 sa column kay 2, mao nga atong idugang 1sa paghimo sa kalainan ug sa pagbaton sa function KINAHANGLAN (INDIRECT) nakuha ang bili gikan sa gusto nga cell.
Ang mga hulagway sa ubos nagpakita sa gitul-id nga case-sensitive nga pormula INDEX+MATCH Sa aksyon. Nagbalik kini og walay sulod nga resulta kung ang gibalik nga cell walay sulod.
Gisulat nako pag-usab ang pormula sa mga kolum B:Daron mohaum sa formula bar sa screenshot.
Nagbalik ang pormula 0kung ang gibalik nga cell adunay zero.
Kung gusto nimo ang link INDEX и MAS EXPOSED nagpakita sa pipila ka mensahe sa diha nga ang pagbalik nga bili walay sulod, mahimo nimo kining isulat sa kataposang mga kinutlo (“”) sa pormula, pananglitan, sama niini:
=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.")
