Nilalaman
Ang maliit na tutorial na ito ay nagpapaliwanag kung paano gumawa ng isang function VPR (VLOOKUP) case-sensitive, nagpapakita ng ilang iba pang formula na maaaring hanapin ng Excel sa case-sensitive na paraan, at itinuturo ang mga kalakasan at kahinaan ng bawat function.
Sa palagay ko alam ng bawat gumagamit ng Excel kung anong function ang gumaganap ng vertical na paghahanap. Tama, ito ay isang function VPR. Gayunpaman, kakaunti ang nakakaalam nito VPR ay hindi case sensitive, ibig sabihin, ang lower at upper case na mga character ay magkapareho para dito.
Narito ang isang mabilis na halimbawa na nagpapakita ng kawalan ng kakayahan VPR kilalanin rehistro. Kumbaga sa isang selda A1 naglalaman ng value na "bill" at ang cell A2 – “Bill”, formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… ihihinto ang paghahanap nito sa "bill" dahil nauuna ang value na iyon sa listahan, at i-extract ang value mula sa cell B1.
Mamaya sa artikulong ito, ipapakita ko sa iyo kung paano gawin VPR case sensitive. Bilang karagdagan, matututo tayo ng ilan pang mga function na maaaring magsagawa ng mga case-sensitive na paghahanap sa Excel.
Magsisimula tayo sa pinakasimpleng – VIEW (LOOKUP) at SUMPRODUCT (SUMPRODUCT), na, sa kasamaang-palad, ay may ilang makabuluhang limitasyon. Susunod, titingnan natin nang mas malapitan ang bahagyang mas kumplikadong formula INDEX+MATCH (INDEX+MATCH), na gumagana nang walang kamali-mali sa anumang sitwasyon at sa anumang dataset.
Ang VLOOKUP function ay case sensitive
Tulad ng alam mo na, ang karaniwang pag-andar VPR ay case insensitive. Gayunpaman, mayroong isang paraan upang gawin itong case sensitive. Upang gawin ito, kailangan mong magdagdag ng isang pantulong na haligi sa talahanayan, tulad ng ipinapakita sa sumusunod na halimbawa.
Kumbaga sa isang column B may mga product identifier (Item) at gusto mong kunin ang presyo ng produkto at ang kaukulang komento mula sa mga column C и D. Ang problema ay ang mga identifier ay naglalaman ng parehong lowercase at uppercase na character. Halimbawa, ang mga halaga ng cell B4 (001Tvci3u) at B5 (001Tvci3U) naiiba lamang sa kaso ng huling karakter, u и U ayon sa pagkakabanggit.
Gaya ng maiisip mo, ang karaniwang formula ng paghahanap
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
babalik $ 90, dahil ang halaga 001Tvci3u ay nasa hanay ng paghahanap nang mas maaga kaysa sa 001Tvci3U. Pero hindi naman yun ang kailangan natin diba?
Upang maghanap gamit ang isang function VPR sa Excel case sensitive, kakailanganin mong magdagdag ng helper column at punan ang mga cell nito ng sumusunod na formula (kung saan ang B ay 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));"")
Hinahati ng formula na ito ang nais na halaga sa magkakahiwalay na mga character, pinapalitan ang bawat karakter ng code nito (halimbawa, sa halip na A sa 65, sa halip a code 97) at pagkatapos ay pagsasamahin ang mga code na ito sa isang natatanging string ng mga numero.
Pagkatapos nito, gumagamit kami ng isang simpleng function VPR para sa case sensitive na paghahanap:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Wastong pagpapatakbo ng function VPR Ang case-sensitive ay nakasalalay sa dalawang salik:
- Ang helper column ay dapat ang pinakakaliwang column sa viewable range.
- Ang halaga na iyong hinahanap ay dapat maglaman ng isang code ng character sa halip na ang tunay na halaga.
Paano gamitin nang tama ang function ng CODE
Ipinapalagay ng formula na ipinasok sa mga cell ng auxiliary column na ang lahat ng iyong mga halaga sa paghahanap ay may parehong bilang ng mga character. Kung hindi, kailangan mong malaman ang pinakamaliit at pinakamalaking mga numero at magdagdag ng maraming mga tampok IFERROR (IFERROR) kung gaano karaming mga character ang pagkakaiba sa pagitan ng pinakamaikling at pinakamatagal na hinanap na halaga.
Halimbawa, kung ang pinakamaikling halaga ng paghahanap ay 3 character at ang pinakamahaba ay 5 character, gamitin ang formula na ito:
=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) Ibigay mo ang mga sumusunod na argumento:
- Unang argumento - teksto (teksto) ay ang text o cell reference na naglalaman ng mga character na kukunin (sa aming kaso ito ay B2)
- Unang argumento - start_num (start_position) ay ang posisyon ng una sa mga character na iyon na i-extract. pumasok ka 1 sa unang function PSTR, 2 – sa pangalawang function PSTR at iba pa
- Unang argumento - num_chars (number_of_characters) – Tinutukoy ang bilang ng mga character na kukunin mula sa text. Dahil kailangan lang namin ng 1 character sa lahat ng oras, sa lahat ng mga function ay sinusulat namin 1.
LIMITASYON: tungkulin VPR ay hindi ang pinakamahusay na solusyon para sa mga case-sensitive na paghahanap sa Excel. Una, ang pagdaragdag ng isang pantulong na haligi ay kinakailangan. Pangalawa, ang formula ay gumagana lamang kung ang data ay homogenous, o ang eksaktong bilang ng mga character sa hinanap na mga halaga ay kilala. Kung hindi ito ang iyong kaso, mas mainam na gumamit ng isa sa mga solusyon na ipinapakita namin sa ibaba.
LOOKUP function para sa case sensitive na paghahanap
tungkulin VIEW (LOOKUP) kaugnay VPR, gayunpaman ang syntax nito ay nagbibigay-daan para sa mga case-sensitive na paghahanap nang hindi nagdaragdag ng pantulong na column. Upang gawin ito, gamitin VIEW pinagsama sa function Eksakto (EKSAKTO).
Kung kukuha kami ng data mula sa nakaraang halimbawa (nang walang pantulong na haligi), kung gayon ang sumusunod na pormula ay makayanan ang gawain:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Mga paghahanap ng formula sa hanay A2: A7 eksaktong tugma sa halaga ng cell F2 case sensitive at ibinabalik ang value mula sa column B ng parehong row.
katulad VPRtungkulin VIEW gumagana nang pantay sa mga text at numeric na halaga, tulad ng makikita mo sa screenshot sa ibaba:
Mahalaga! Upang ang pag-andar VIEW gumana nang tama, ang mga halaga sa hanay ng paghahanap ay dapat na pagbukud-bukurin sa pataas na pagkakasunud-sunod, ibig sabihin, mula sa pinakamaliit hanggang sa pinakamalaki.
Hayaan akong maikli na ipaliwanag kung paano gumagana ang function Eksakto sa formula na ipinakita sa itaas, dahil ito ang pangunahing punto.
tungkulin Eksakto inihahambing ang dalawang halaga ng teksto sa 1st at 2nd argument at nagbabalik ng TRUE kung eksaktong pareho ang mga ito, o FALSE kung hindi. Ito ay mahalaga para sa amin na ang function Eksakto case sensitive.
Tingnan natin kung paano gumagana ang aming formula TINGNAN+EKSAKTO:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- tungkulin Eksakto inihahambing ang halaga ng cell F2 kasama ang lahat ng elemento sa isang column A (A2:A7). Ibinabalik ang TRUE kung may nakitang eksaktong tugma, kung hindi, FALSE.
- Dahil binigay mo ang unang function argument VIEW value na TRUE, kinukuha nito ang katumbas na halaga mula sa tinukoy na column (sa aming kaso, column B) kung may nakitang eksaktong tugma, case sensitive.
Sana ay naging malinaw ang paliwanag na ito at ngayon ay naiintindihan mo na ang pangunahing ideya. Kung gayon, hindi ka magkakaroon ng anumang mga paghihirap sa iba pang mga pag-andar na susuriin pa namin, dahil. lahat sila ay gumagana sa parehong prinsipyo.
LIMITASYON: Ang data sa hanay ng paghahanap ay dapat na pinagbukud-bukod sa pataas na pagkakasunud-sunod.
SUMPRODUCT – nakakahanap ng mga text value, case sensitive, ngunit numero lang ang ibinabalik
Tulad ng naintindihan mo na mula sa pamagat, SUMPRODUCT Ang (SUMPRODUCT) ay isa pang Excel function na tutulong sa iyong gumawa ng case-sensitive na paghahanap, ngunit magbabalik lang ng mga numeric na halaga. Kung ang pagpipiliang ito ay hindi angkop sa iyo, pagkatapos ay maaari kang magpatuloy kaagad sa bundle INDEX+MATCH, na nagbibigay ng solusyon para sa anumang kaso at para sa anumang uri ng data.
Una, hayaan mo akong ipaliwanag nang maikli ang syntax ng function na ito, makakatulong ito sa iyong mas maunawaan ang case-sensitive na formula na sumusunod.
tungkulin SUMPRODUCT pinaparami ang mga elemento ng ibinigay na mga array at ibinabalik ang kabuuan ng mga resulta. Ang syntax ay ganito ang hitsura:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Dahil kailangan namin ng case-sensitive na paghahanap, ginagamit namin ang function Eksakto (EXACT) mula sa nakaraang halimbawa bilang isa 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)))
Tulad ng naaalala mo, Eksakto inihahambing ang halaga ng cell F2 kasama ang lahat ng elemento sa isang column A. Ibinabalik ang TRUE kung may nakitang eksaktong tugma, kung hindi, FALSE. Sa mathematical operations, ang Excel ay tumatagal ng TRUE bilang 1, at MALI para sa 0Pa SUMPRODUCT i-multiply ang mga numerong ito at isasama ang mga resulta.
Ang mga zero ay hindi binibilang dahil kapag pinarami ay palaging nagbibigay 0. Tingnan natin kung ano ang mangyayari kapag eksaktong tugma sa isang column A natagpuan at ibinalik 1… Pag-andar SUMPRODUCT nagpaparami ng bilang sa hanay B on 1 at ibinabalik ang resulta - eksaktong parehong numero! Ito ay dahil ang mga resulta ng iba pang mga produkto ay zero, at hindi ito nakakaapekto sa resultang kabuuan.
Sa kasamaang palad ang pag-andar SUMPRODUCT ay hindi maaaring gumana sa mga halaga ng teksto at mga petsa dahil hindi sila maaaring i-multiply. Sa kasong ito, makakatanggap ka ng isang mensahe ng error #VALUE! (#VALUE!) tulad ng sa isang cell F4 sa larawan sa ibaba:
LIMITASYON: Nagbabalik lamang ng mga numerong halaga.
INDEX + MATCH – case-sensitive na paghahanap para sa anumang uri ng data
Sa wakas, malapit na kami sa isang walang limitasyon at case-sensitive na formula sa paghahanap na gumagana sa anumang set ng data.
Huli ang halimbawang ito, hindi dahil naiwan ang pinakamahusay para sa dessert, ngunit dahil ang kaalamang nakuha mula sa mga nakaraang halimbawa ay makakatulong sa iyong maunawaan ang case-sensitive na formula nang mas mahusay at mas mabilis. INDEX+MATCH (INDEX+MATCH).
Tulad ng malamang na nahulaan mo, ang kumbinasyon ng mga function MAS EXPOSED и INDEX ginamit sa Excel bilang isang mas nababaluktot at makapangyarihang alternatibo para sa VPR. Ang artikulong Paggamit ng INDEX at MATCH sa halip na VLOOKUP ay perpektong magpapaliwanag kung paano gumagana ang mga function na ito nang magkasama.
Ire-recap ko lang ang mga pangunahing punto:
- tungkulin MAS EXPOSED (MATCH) ay naghahanap ng isang halaga sa isang ibinigay na hanay at ibinabalik ang kaugnay na posisyon nito, iyon ay, ang numero ng row at/o column;
- Susunod, ang function INDEX (INDEX) ay nagbabalik ng isang halaga mula sa isang tinukoy na column at/o row.
Sa formula INDEX+MATCH maaaring maghanap nang case-sensitive, kailangan mo lamang magdagdag ng isang function dito. Hindi mahirap hulaan kung ano ito muli Eksakto (EXACT):
=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))
Sa formula na ito Eksakto gumagana sa parehong paraan tulad ng kasabay ng function VIEW, at nagbibigay ng parehong resulta:
Tandaan na ang formula INDEX+MATCH na nakapaloob sa mga kulot na braces ay isang array formula at dapat mong kumpletuhin ito sa pamamagitan ng pagpindot Ctrl + Shift + Enter.
Bakit ang INDEX+MATCH ang pinakamahusay na solusyon para sa case-sensitive na paghahanap?
Ang pangunahing bentahe ng bundle INDEX и MAS EXPOSED:
- Hindi nangangailangan ng pagdaragdag ng auxiliary column, hindi katulad VPR.
- Hindi nangangailangan ng hanay ng paghahanap na pagbukud-bukurin, hindi katulad VIEW.
- Gumagana sa lahat ng uri ng data – mga numero, teksto at petsa.
Mukhang perpekto ang formula na ito, hindi ba? Sa totoo lang, hindi naman. At dahil jan.
Ipagpalagay na ang cell sa column ng return value na nauugnay sa lookup value ay walang laman. Anong resulta ang ibabalik ng formula? Hindi? Tingnan natin kung ano talaga ang ibinabalik ng formula:
Oops, ang formula ay nagbabalik ng zero! Maaaring hindi ito isang malaking problema kung nagtatrabaho ka sa mga purong halaga ng teksto. Gayunpaman, kung ang talahanayan ay naglalaman ng mga numero, kabilang ang "tunay" na mga zero, ito ay nagiging problema.
Sa katunayan, ang lahat ng iba pang mga formula sa paghahanap (VLOOKUP, LOOKUP, at SUMPRODUCT) na tinalakay natin kanina ay kumikilos sa parehong paraan. Ngunit gusto mo ang perpektong formula, tama ba?
Para gawing case sensitive ang isang formula INDEX+MATCH perpekto, ilagay ito sa isang function IF (KUNG) na susubok ng cell na may return value at magbabalik ng walang laman na resulta kung ito ay walang laman:
=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));"")
Sa formula na ito:
- B ay isang column na may mga return value
- 1+ ay isang numero na lumiliko sa relatibong posisyon ng cell na ibinalik ng function MAS EXPOSED, sa totoong address ng cell. Halimbawa, sa aming function MAS EXPOSED ibinigay na hanay ng paghahanap A2: A7, iyon ay, ang relatibong posisyon ng cell A2 habilin 1, dahil ito ang una sa array. Ngunit ang aktwal na posisyon ng cell A2 sa hanay ay 2, kaya idinagdag namin 1upang gawin ang pagkakaiba at magkaroon ng function HINDI DIREKTA (INDIRECT) nakuha ang halaga mula sa gustong cell.
Ang mga larawan sa ibaba ay nagpapakita ng itinamang case-sensitive na formula INDEX+MATCH Sa aksyon. Nagbabalik ito ng walang laman na resulta kung walang laman ang ibinalik na cell.
Isinulat kong muli ang formula sa mga hanay B:Dpara magkasya ang formula bar sa screenshot.
Nagbabalik ang formula 0kung ang ibinalik na cell ay naglalaman ng zero.
Kung gusto mo ng link INDEX и MAS EXPOSED nagpakita ng ilang mensahe kapag walang laman ang return value, maaari mo itong isulat sa mga huling quote (“”) ng formula, halimbawa, tulad nito:
=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.")
