Werrej
Dan it-tutorja żgħira jispjega kif tagħmel funzjoni VPR (VLOOKUP) sensittiv għall-każ, turi diversi formuli oħra li Excel jista 'jfittex b'mod sensittiv għall-każ, u jindika s-saħħiet u d-dgħufijiet ta' kull funzjoni.
Naħseb li kull utent ta 'Excel jaf liema funzjoni twettaq tfittxija vertikali. Dak id-dritt, hija funzjoni VPR. Madankollu, ftit nies jafu dan VPR mhijiex sensittiva għall-każi, jiġifieri l-karattri l-ittri u l-ittri huma identiċi għaliha.
Hawn eżempju ta 'malajr li juri l-inkapaċità VPR jirrikonoxxu reġistru. Ejja ngħidu f'ċellula A1 fih il-valur “bill” u ċ-ċellula A2 – “Bill”, formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... se twaqqaf it-tfittxija tagħha fuq "kont" peress li dak il-valur jiġi l-ewwel fil-lista, u estratt il-valur miċ-ċellula B1.
Aktar tard f'dan l-artikolu, ser nuruk kif tagħmel VPR sensittiv għall-każ. Barra minn hekk, se nitgħallmu ftit funzjonijiet oħra li jistgħu jwettqu tfittxijiet sensittivi għall-każi f'Excel.
Nibdew bl-aktar sempliċi - VIEW (LOOKUP) u SUMPRODUCT (SUMPRODUCT), li, sfortunatament, għandhom diversi limitazzjonijiet sinifikanti. Sussegwentement, se nagħtu ħarsa aktar mill-qrib lejn il-formula kemmxejn aktar kumplessa INDIĊI+TAQBIL (INDIĊI+TAQBIL), li jaħdem bla difetti fi kwalunkwe sitwazzjoni u ma 'kwalunkwe dataset.
Il-funzjoni VLOOKUP hija sensittiva għall-każ
Kif diġà taf, il-funzjoni tas-soltu VPR hija insensittiva għall-każi. Madankollu, hemm mod kif tagħmilha sensittiva għall-każ. Biex tagħmel dan, għandek bżonn iżżid kolonna awżiljarja mat-tabella, kif muri fl-eżempju li ġej.
Ejja ngħidu f'kolonna B hemm identifikaturi tal-prodott (Oġġett) u trid tiġbed il-prezz tal-prodott u l-kumment korrispondenti mill-kolonni C и D. Il-problema hija li l-identifikaturi fihom kemm karattri żgħar kif ukoll kbar. Per eżempju, valuri taċ-ċelluli B4 (001Tvci3u) u B5 (001Tvci3U) huma differenti biss fil-każ tal-aħħar karattru, u и U rispettivament.
Kif tista 'timmaġina, il-formula ta' tfittxija tas-soltu
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
jirritorna $ 90, peress li l-valur 001Tvci3u jinsab fil-firxa tat-tfittxija qabel 001Tvci3U. Imma dan mhux dak li għandna bżonn, hux?
Biex tfittex b'funzjoni VPR f'Excel sensittiv għall-każi, ikollok iżżid kolonna helper u timla ċ-ċelloli tagħha bil-formula li ġejja (fejn B hija l-kolonna ta 'tfittxija):
=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));"")
Din il-formula tkisser il-valur mixtieq f'karattri separati, tissostitwixxi kull karattru bil-kodiċi tiegħu (per eżempju, minflok A fil-65, minflok a kodiċi 97) u mbagħad jgħaqqad dawn il-kodiċijiet f’sensiela unika ta’ numri.
Wara dan, nużaw funzjoni sempliċi VPR għal tfittxija sensittiva għall-każi:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Tħaddim tajjeb tal-funzjoni VPR sensittiv għall-każ jiddependi fuq żewġ fatturi:
- Il-kolonna helper trid tkun il-kolonna l-aktar fuq ix-xellug fil-medda li tista' tara.
- Il-valur li qed tfittex għandu jkun fih kodiċi tal-karattru minflok il-valur reali.
Kif tuża l-funzjoni tal-KODIĊI b'mod korrett
Il-formula mdaħħla fiċ-ċelloli tal-kolonna awżiljarja tassumi li l-valuri kollha tat-tfittxija tiegħek għandhom l-istess numru ta 'karattri. Jekk le, allura għandek bżonn tkun taf l-iżgħar u l-akbar numri u żżid daqstant karatteristiċi JEKK Żball (IFERROR) kemm-il karattri hija d-differenza bejn l-iqsar u l-itwal valur imfittex.
Pereżempju, jekk l-iqsar valur tat-tfittxija huwa 3 karattri u l-itwal huwa 5 karattri, uża din il-formula:
=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));"")
Għall-funzjoni PSTR (MID) Inti tipprovdi l-argumenti li ġejjin:
- L-ewwel argument - test (test) hija t-test jew ir-referenza taċ-ċellula li fiha l-karattri li għandhom jiġu estratti (fil-każ tagħna huwa B2)
- L-ewwel argument - start_num (start_position) hija l-pożizzjoni tal-ewwel minn dawk il-karattri li għandhom jiġu estratti. inti tidħol 1 fl-ewwel funzjoni PSTR, 2 – fit-tieni funzjoni PSTR eċċ.
- L-ewwel argument - num_chars (numru_ta_karattri) – Jispeċifika n-numru ta' karattri li għandhom jiġu estratti mit-test. Peress li għandna bżonn biss karattru 1 il-ħin kollu, fil-funzjonijiet kollha niktbu 1.
LIMITAZZJONIJIET: funzjoni VPR mhix l-aħjar soluzzjoni għal tfittxijiet sensittivi għall-każi f'Excel. L-ewwel, iż-żieda ta 'kolonna awżiljarja hija meħtieġa. It-tieni nett, il-formula tagħmel xogħol tajjeb biss jekk id-dejta tkun omoġenja, jew in-numru eżatt ta 'karattri fil-valuri mfittxija huwa magħruf. Jekk dan mhux il-każ tiegħek, huwa aħjar li tuża waħda mis-soluzzjonijiet li nuru hawn taħt.
Funzjoni LOOKUP għal tfittxija sensittiva għall-każ
funzjoni VIEW (LOOKUP) relatati VPR, madankollu s-sintassi tagħha tippermetti tfittxijiet sensittivi għall-każi mingħajr ma żżid kolonna awżiljarja. Biex tagħmel dan, uża VIEW flimkien mal-funzjoni EXACT (EŻATT).
Jekk nieħdu d-dejta mill-eżempju preċedenti (mingħajr kolonna awżiljarja), allura l-formula li ġejja tlaħħaq mal-kompitu:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Tfittxijiet tal-formula fil-medda A2: A7 taqbila eżatta mal-valur taċ-ċellula F2 sensittivi għall-każi u jirritorna l-valur mill-kolonna B tal-istess ringiela.
bħall VPRfunzjoni VIEW jaħdem bl-istess mod b'test u valuri numeriċi, kif tista' tara fil-screenshot hawn taħt:
Importanti! Sabiex il-funzjoni VIEW maħduma b'mod korrett, il-valuri fil-kolonna ta 'tfittxija għandhom jiġu magħżula f'ordni axxendenti, jiġifieri mill-iżgħar għall-akbar.
Ħa nispjega fil-qosor kif taħdem il-funzjoni EXACT fil-formula murija hawn fuq, peress li dan huwa l-punt ewlieni.
funzjoni EXACT iqabbel iż-żewġ valuri tat-test fl-1 u t-2 argumenti u jirritorna VERU jekk huma eżattament l-istess, jew FALZ jekk mhumiex. Huwa importanti għalina li l-funzjoni EXACT sensittiv għall-każ.
Ejja naraw kif taħdem il-formula tagħna ARA+EŻATT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- funzjoni EXACT tqabbel il-valur taċ-ċellula F2 bl-elementi kollha f'kolonna A (A2:A7). Jirritorna VERU jekk tinstab taqbila eżatta, inkella FALZ.
- Peress li inti tagħti l-ewwel argument funzjoni VIEW valur VERU, estratti l-valur korrispondenti mill-kolonna speċifikata (fil-każ tagħna, kolonna B) biss jekk tinstab taqbila eżatta, sensittiva għall-każ.
Nispera li din l-ispjegazzjoni kienet ċara u issa tifhem l-idea prinċipali. Jekk iva, allura ma jkollokx diffikultajiet b'funzjonijiet oħra li aħna se nanalizzaw aktar, għaliex. kollha jaħdmu fuq l-istess prinċipju.
LIMITAZZJONIJIET: Id-dejta fil-kolonna tal-ħarsa trid tiġi magħżula f'ordni axxendenti.
SUMPRODUCT – isib valuri tat-test, sensittivi għall-każi, iżda jirritorna biss numri
Kif diġà fhimt mit-titlu, SUMPRODUCT (SUMPRODUCT) hija funzjoni oħra ta' Excel li tgħinek tagħmel tfittxija sensittiva għall-każi, iżda tirritorna biss valuri numeriċi. Jekk din l-għażla ma taqbilx għalik, allura tista 'proċedi immedjatament għall-pakkett INDIĊI+TAQBIL, li tagħti soluzzjoni għal kwalunkwe każ u għal kwalunkwe tip ta 'dejta.
L-ewwel, ħalluni nispjega fil-qosor is-sintassi ta 'din il-funzjoni, dan jgħinek tifhem aħjar il-formula sensittiva għall-każi li ġejja.
funzjoni SUMPRODUCT jimmultiplika l-elementi tal-arrays mogħtija u jirritorna s-somma tar-riżultati. Is-sintassi tidher bħal din:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Peress li għandna bżonn tfittxija sensittiva għall-każi, nużaw il-funzjoni EXACT (EŻATT) mill-eżempju preċedenti bħala wieħed mill-multiplikaturi:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Kif tiftakar, EXACT tqabbel il-valur taċ-ċellula F2 bl-elementi kollha f'kolonna A. Jirritorna VERU jekk tinstab taqbila eżatta, inkella FALZ. F'operazzjonijiet matematiċi, Excel jieħu VERU bħala 1, u FALZ għal 0aktar SUMPRODUCT timmultiplika dawn in-numri u tgħodd ir-riżultati.
Iż-żeri ma jingħaddux għax meta jiġu mmultiplikati dejjem jagħtu 0. Ejja nagħtu ħarsa aktar mill-qrib lejn x'jiġri meta taqbila eżatta f'kolonna A misjuba u rritornat 1… Funzjoni SUMPRODUCT timmultiplika n-numru fil-kolonna B on 1 u jirritorna r-riżultat - eżattament l-istess numru! Dan għaliex ir-riżultati tal-prodotti l-oħra huma żero, u ma jaffettwawx is-somma li tirriżulta.
Sfortunatament il-funzjoni SUMPRODUCT ma jistax jaħdem bil-valuri tat-test u d-dati peress li ma jistgħux jiġu mmultiplikati. F'dan il-każ, inti tirċievi messaġġ ta 'żball #VALUE! (#VALUR!) bħal f'ċellula F4 fl-istampa hawn taħt:
LIMITAZZJONIJIET: Jirritorna biss valuri numeriċi.
INDIĊI + MATCH – tfittxija sensittiva għall-każi għal kwalunkwe tip ta’ dejta
Fl-aħħarnett, aħna qrib formula ta 'tfittxija illimitata u sensittiva għall-każi li taħdem ma' kwalunkwe sett ta 'dejta.
Dan l-eżempju jiġi l-aħħar, mhux għax l-aħjar jitħalla għad-deżerta, iżda minħabba li l-għarfien miksub mill-eżempji preċedenti jgħinek tifhem il-formula sensittiva għall-każi aħjar u aktar malajr. INDIĊI+TAQBIL (INDIĊI+TAQBIL).
Kif probabilment guessted, il-kombinazzjoni ta 'funzjonijiet AKTAR ESPOSTI и INDIĊI użat fl-Excel bħala alternattiva aktar flessibbli u qawwija għal VPR. L-artiklu L-użu ta 'INDIĊI u MATCH minflok VLOOKUP se jispjega perfettament kif dawn il-funzjonijiet jaħdmu flimkien.
Se nġabar biss il-punti ewlenin:
- funzjoni AKTAR ESPOSTI (MATCH) ifittex valur f'firxa partikolari u jirritorna l-pożizzjoni relattiva tiegħu, jiġifieri n-numru tar-ringiela u/jew tal-kolonna;
- Sussegwentement, il-funzjoni INDIĊI (INDIĊI) jirritorna valur minn kolonna u/jew ringiela speċifikati.
Biex formula INDIĊI+TAQBIL tista 'tfittex b'mod sensittiv għall-każ, għandek bżonn biss li żżid funzjoni waħda magħha. Mhux diffiċli li terġa’ taqta’ x’inhu EXACT (EŻATT):
=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))
F'din il-formula EXACT jaħdem bl-istess mod kif flimkien mal-funzjoni VIEW, u jagħti l-istess riżultat:
Innota li l-formula INDIĊI+TAQBIL magħluqa fi ċingi kaboċċi hija formula ta 'firxa u trid timlaha billi tagħfas Ctrl + Shift + Daħħal.
Għaliex hija INDEX+MATCH l-aħjar soluzzjoni għal tfittxija sensittiva għall-każ?
Il-vantaġġi ewlenin tal-gozz INDIĊI и AKTAR ESPOSTI:
- Ma jeħtieġx li żżid kolonna awżiljarja, b'differenza VPR.
- Ma jeħtieġx li l-kolonna tat-tfittxija tiġi magħżula, b'differenza VIEW.
- Jaħdem mat-tipi kollha ta 'data - numri, test u dati.
Din il-formula tidher perfetta, hux? Fil-fatt, mhuwiex. U għalhekk.
Assumi li ċ-ċellula fil-kolonna tal-valur tar-ritorn assoċjata mal-valur ta 'tfittxija hija vojta. X'riżultat se tirritorna l-formula? Le? Ejja naraw x'inhi l-formula fil-fatt tirritorna:
Oops, il-formula tirritorna żero! Din tista 'ma tkunx problema kbira jekk qed taħdem b'valuri ta' test pur. Madankollu, jekk it-tabella jkun fiha numri, inklużi żerijiet "reali", din issir problema.
Fil-fatt, il-formuli ta' tfittxija l-oħra kollha (VLOOKUP, LOOKUP, u SUMPRODUCT) li ddiskutejna qabel jaġixxu bl-istess mod. Imma trid il-formula perfetta, hux?
Biex tagħmel formula sensittiva għall-każ INDIĊI+TAQBIL perfetta, poġġih f'funzjoni IF (IF) li se tittestja ċellola b'valur ta' ritorn u tirritorna riżultat vojt jekk tkun vojta:
=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));"")
F'din il-formula:
- B hija kolonna b'valuri ta' ritorn
- 1+ huwa numru li jdawwar il-pożizzjoni relattiva taċ-ċellula rritornata mill-funzjoni AKTAR ESPOSTI, għall-indirizz reali taċ-ċellula. Per eżempju, fil-funzjoni tagħna AKTAR ESPOSTI firxa ta' tfittxija mogħtija A2: A7, jiġifieri, il-pożizzjoni relattiva taċ-ċellula A2 se 1, għax hija l-ewwel waħda fil-firxa. Iżda l-pożizzjoni attwali taċ-ċellula A2 fil-kolonna hija 2, hekk aħna żid 1li tagħmel id-differenza u li jkollha l-funzjoni INDIRETTA (INDIRETT) irkuprat il-valur miċ-ċellula mixtieqa.
L-istampi hawn taħt juru l-formula kkoreġuta sensittiva għall-każ INDIĊI+TAQBIL Fl-azzjoni. Jirritorna riżultat vojt jekk iċ-ċellula rritornata tkun vojta.
Reġgħet ktibt il-formula f'kolonni B:Dbiex taqbel mal-bar tal-formula fuq il-screenshot.
Il-formula tirritorna 0jekk iċ-ċellula rritornata fiha żero.
Jekk trid il-link INDIĊI и AKTAR ESPOSTI muri xi messaġġ meta l-valur tar-ritorn ikun vojt, tista 'tikteb fl-aħħar kwotazzjonijiet ("") tal-formula, pereżempju, bħal dan:
=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.")
