Mukati
Ichi chidzidzo chidiki chinotsanangura maitiro ekuita basa VPR (VLOOKUP) kesi-sensitive, inoratidza mamwe akati wandei mafomula ayo Excel anogona kutsvaga nenzira inobata nyaya, uye anonongedza kusimba uye kusasimba kwebasa rega rega.
Ini ndinofungidzira wese mushandisi weExcel anoziva basa rinoita kutsvaga kwakamira. Ndizvozvo, ibasa VPR. Zvisinei, vanhu vashoma vanoziva izvozvo VPR harisi nyore kuona, kureva mavara ezasi uye epamusoro akafanana nawo.
Heino muenzaniso wekukurumidza unoratidza kusagona VPR kuziva rejista. Tomboti murusero A1 ine kukosha kwe "bhiri" uye sero A2 - "Bill", fomula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... ichamisa kutsvaga kwayo pa "bhiri" sezvo kukosha ikoko kunouya pekutanga mune rondedzero, uye kubvisa kukosha kubva muchitokisi B1.
Gare gare munyaya ino, ini ndichakuratidza maitiro VPR case sensitive. Pamusoro pezvo, isu tichadzidza mamwe mashoma mashandiro anogona kuita kesi-sensitive yekutsvaga muExcel.
Tichatanga nezviri nyore - ONA (LOOKUP) uye SUMPRODUCT (SUMPRODUCT), iyo, zvinosuruvarisa, ine zvinoverengeka zvakakosha zvisingakwanisi. Tevere, isu ticha nyatso tarisisa iyo yakati wandei yakaoma formula INDEX+MATCH (INDEX+MATCH), iyo inoshanda zvisina mhosva mune chero mamiriro uye nechero dataset.
VLOOKUP basa rinonzwa nyaya
Sezvaunotoziva, iyo yakajairika basa VPR haina hanya nenyaya. Zvisinei, pane nzira yekuita kuti nyaya yacho ionekwe. Kuti uite izvi, unofanirwa kuwedzera mutsara wekubatsira patafura, sezvinoratidzwa mumuenzaniso unotevera.
Ngatitii muchikamu B kune zviziviso zvechigadzirwa (Item) uye iwe unoda kuburitsa mutengo wechigadzirwa uye inoenderana mhinduro kubva kumakoramu. C и D. Dambudziko nderekuti zviziviso zvine mavara madiki uye makuru. Semuenzaniso, kukosha kwesero B4 (001Tvci3u) uye B5 (001Tvci3U) inosiyana chete munyaya yemunhu wekupedzisira, u и U azvaitwa.
Sezvaunogona kufungidzira, yakajairika yekutsvaga formula
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
vachadzoka $ 90, kubva kukosha 001Tvci3u iri munzvimbo yekutsvaga kare kupfuura 001Tvci3U. Asi handizvo zvatinoda, handizvo here?
Kutsvaga nechishandiso VPR muExcel kesi inonzwisisika, iwe uchafanirwa kuwedzera mubatsiri koramu uye kuzadza maseru ayo neiyo inotevera fomula (apo B ndiyo yekutarisa 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));"")
Iyi fomula inotyora kukosha kwaunoda kuita mavara akapatsanurwa, inotsiva yega yega kodhi nekodhi yayo (semuenzaniso, pachinzvimbo che A pa65, panzvimbo a kodhi 97) uye wozobatanidza makodhi aya kuita tambo yakasarudzika yenhamba.
Pashure pacho, tinoshandisa basa riri nyore VPR yekutsvaga nyaya inonzwisisika:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Kushanda kwakakodzera kwebasa racho VPR case-sensitive zvinoenderana nezvinhu zviviri:
- Koramu yemubatsiri inofanira kuva irikuruboshwe pachikamu chinooneka.
- Hukoshi hwauri kutsvaga hunofanirwa kunge huine kodhi yehunhu pachinzvimbo cheukoshi chaihwo.
Maitiro ekushandisa iyo CODE basa nemazvo
Iyo fomula yakaiswa mumaseru eiyo anobatsira column inofungidzira kuti ese ako ekutsvaga kukosha ane nhamba yakafanana yemavara. Kana zvisina kudaro, saka iwe unofanirwa kuziva nhamba diki uye hombe uye wedzera akawanda maficha IFERROR (IFERROR) mabhii mangani ndiwo mutsauko pakati peukoshi hupfupi uye hurefu hwakatsvakwa.
Semuyenzaniso, kana hukoshi hwekutsvaga hupfupi huine mavara matatu uye hurefu huine mavara mashanu, shandisa fomula iyi:
=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));"")
Zvebasa PSTR (MID) Iwe unopa zvinotevera nharo:
- 1st nharo - chinyorwa (chinyorwa) ndiwo mavara kana sero referefeta rine mavara achatorwa (kwatiri iB2)
- 2st nharo - start_num (start_position) ndiyo nzvimbo yekutanga yemavara aya kuburitswa. unopinda 1 mubasa rekutanga PSTR, 2 - mubasa rechipiri PSTR etc.
- 3st nharo - num_chars (nhamba_ye_characters) - Inotsanangura nhamba yemavara ekutora kubva muzvinyorwa. Sezvo isu tichingoda 1 chimiro nguva dzese, mumabasa ese atinonyora 1.
ZVINOKUDZIDZA: basa VPR haisiriyo mhinduro yakanakisa yekutsvaga-sensitive yekutsvaga muExcel. Kutanga, kuwedzerwa kwekoramu yekubatsira kunodiwa. Chechipiri, fomula inoita basa rakanaka chete kana iyo data iri homogeneous, kana iyo chaiyo nhamba yemavara muakatsvaga maitiro anozivikanwa. Kana iyi isiri nyaya yako, zviri nani kushandisa imwe yemhinduro dzatinoratidza pazasi.
LOOKUP basa rekutsvaga kwenyaya
basa ONA (LOOKUP) inoenderana VPR, zvisinei kuti syntax yaro inobvumira kutsvaga-sensitive kutsvaga pasina kuwedzera chikamu chekubatsira. Kuti uite izvi, shandisa ONA zvakasanganiswa nebasa BASA (EXACT).
Kana isu tikatora iyo data kubva kumuenzaniso wapfuura (pasina mbiru yekubatsira), saka inotevera fomula ichaita basa:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formula inotsvaga muhuwandu A2:A7 machisi chaiwo ane kukosha kwesero F2 case sensitive uye inodzorera kukosha kubva pa column B yemutsara mumwe chete.
se VPRbasa ONA inoshanda zvakaenzana nemavara uye manhamba manhamba, sezvauri kuona pamufananidzo pazasi:
Zvinokosha! Kuti basa racho ONA yakashanda nemazvo, kukosha kuri mukoramu yekutarisa kunofanirwa kurongwa mukukwira, kureva kubva padiki kusvika pakukura.
Rega nditsanangure muchidimbu kuti basa racho rinoshanda sei BASA mune fomula yaratidzwa pamusoro, sezvo iri iro chinhu chakakosha.
basa BASA inofananidza iwo maviri mameseji kukosha mu1st uye 2nd nharo uye inodzosa TRUE kana dzakafanana, kana FALSE kana zvisiri. Zvakakosha kwatiri kuti basa racho BASA case sensitive.
Ngationei kuti formula yedu inoshanda sei ONA+CHAKO:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- basa BASA inoenzanisa kukosha kwesero F2 nezvose zviri muchikamu A (A2:A7). Inodzosa TRUE kana mucheno chaiwo wawanikwa, zvikasadaro FALSE.
- Sezvo iwe uchipa yekutanga basa nharo ONA kukosha CHOKWADI, inobvisa kukosha kunoenderana kubva kune yakatsanangurwa koramu (munyaya yedu, koramu B) chete kana machisi chaiwo awanikwa, anonzwira tsitsi.
Ndinovimba tsananguro iyi yaive yakajeka uye ikozvino unonzwisisa pfungwa huru. Kana zvakadaro, saka hauzove nematambudziko nemamwe mabasa atichaongorora zvakare, nekuti. vose vanoshanda pamusimboti mumwe chete.
ZVINOKUDZIDZA: Iyo data iri mukoramu yekutarisa inofanirwa kurongwa mukukwira.
SUMPRODUCT - inowana mameseji makoshero, anonzwira tsitsi, asi anodzosa nhamba chete
Sezvaunonzwisisa kubva mumusoro, SUMPRODUCT (SUMPRODUCT) nderimwe basa reExcel rinokubatsira kuita tsvakiridzo isinganzwisisike, asi inongodzosera manhamba. Kana iyi sarudzo isingakodzeri iwe, saka iwe unogona pakarepo kuenda kune bundle INDEX+MATCH, iyo inopa mhinduro kune chero nyaya uye kune chero mhando dzedata.
Chekutanga, rega nditsanangure muchidimbu syntax yebasa iri, izvi zvinokubatsira kuti unzwisise zviri nani iyo kesi-sensitive formula inotevera.
basa SUMPRODUCT inowanza zvinhu zvezvirongwa zvakapihwa uye inodzosa huwandu hwemibairo. Syntax inotaridzika seizvi:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Sezvo tichida tsvakiridzo isinganzwisisike, tinoshandisa basa racho BASA (EXACT) kubva kumuenzaniso wapfuura semumwe wevanowedzera:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Sezvaunorangarira, BASA inoenzanisa kukosha kwesero F2 nezvose zviri muchikamu A. Inodzosa TRUE kana mucheno chaiwo wawanikwa, zvikasadaro FALSE. Mukuita kwemasvomhu, Excel inotora TRUE se 1, uye NHEMA ye 0Uyezve SUMPRODUCT inowanza manhamba aya uye nekupfupikisa zvabuda.
Mazero haaverengwe nekuti kana awanda anogara achipa 0. Ngatitarisei zvakanyanya izvo zvinoitika kana machisi chaiwo mumutsara A akawana ndokudzoka 1… Basa SUMPRODUCT inowanza nhamba mukoramu B on 1 uye inodzosera mhedzisiro - chaiyo nhamba imwechete! Izvi zvinodaro nekuti mhedzisiro yezvimwe zvigadzirwa ndeye zero, uye haikanganise huwandu hunobuda.
Zvinosuruvarisa basa SUMPRODUCT haigone kushanda nemavara emavara nemisi sezvo isingagone kuwanzwa. Muchiitiko ichi, iwe uchagamuchira meseji yekukanganisa #VALUE! (#VALUE!) semuchitokisi F4 pamufananidzo uri pazasi:
ZVINOKUDZIDZA: Inodzosa nhamba dzenhamba chete.
INDEX + MATCH - kesi-sensitive kutsvaga kune chero mhando yedata
Chekupedzisira, tave pedyo neasina muganho uye ane-case-sensitive yekutsvaga formula inoshanda nechero data set.
Uyu muenzaniso unouya wekupedzisira, kwete nekuti yakanakisa yasara yedhizeti, asi nekuti ruzivo rwakawanwa kubva mumienzaniso yapfuura ruchakubatsira iwe kunzwisisa kesi-sensitive formula zvirinani uye nekukurumidza. INDEX+MATCH (INDEX+MATCH).
Sezvaungangofungidzira, musanganiswa wemabasa ZVAKAWANDA AKAFUMURWA и INDEX inoshandiswa muExcel seimwe nzira inoshanduka uye ine simba kune VPR. Chinyorwa Kushandisa INDEX uye MATCH pachinzvimbo cheVLOOKUP chinotsanangura zvakakwana kuti aya mabasa anoshanda pamwechete.
Ndichangodzokorora pfungwa dzinokosha:
- basa ZVAKAWANDA AKAFUMURWA (MATCH) inotsvaga kukosha muchikamu chakapihwa uye inodzosa chinzvimbo chayo, kureva, mutsara uye/kana nhamba yekoramu;
- Tevere, basa INDEX (INDEX) inodzosa kukosha kubva kune yakatsanangurwa koramu uye/kana mutsara.
Kuti formula INDEX+MATCH unogona kutsvaga nyaya-sensitively, unongoda kuwedzera basa rimwe pairi. Hazvina kuoma kufungidzira kuti chii zvakare BASA (ZVO):
=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))
Muchirongwa ichi BASA inoshanda nenzira imwechete seyakabatana nebasa ONA, uye inopa mhedzisiro yakafanana:
Cherechedza kuti formula INDEX+MATCH yakavharirwa mune dzakamonana braces is array formula uye unofanira kuipedzisa nekudzvanya Ctrl + Shift + Pinda.
Sei INDEX+MATCH iriyo mhinduro yakanakisa yekutsvaga-sensitive search?
Izvo zvakakosha zvebundle INDEX и ZVAKAWANDA AKAFUMURWA:
- Hazvidi kuti uwedzere chikamu chekubatsira, zvakasiyana VPR.
- Hazvidi kuti chikamu chekutsvaga chirongedzwe, zvakasiyana ONA.
- Inoshanda nemhando dzese dze data - nhamba, zvinyorwa uye misi.
Fomula iyi inoita kunge yakakwana, handizvo here? Chaizvoizvo, hazvisi. Uye ndosaka.
Fungidzira kuti sero iri mukoramu yeukoshi yekudzoka inosanganisirwa nehuwandu hwekutarisa haina chinhu. Mugumisiro uchadzoka sei? Aihwa? Ngationei kuti formula inodzosa chii chaizvo:
Maiwe, formula inodzosa zero! Iri rinogona kunge risiri dambudziko rakakura kana uri kushanda neakachena mameseji kukosha. Zvisinei, kana tafura ine nhamba, kusanganisira "chaiyo" zero, izvi zvinova dambudziko.
Asi izvo, mamwe ese ekutarisa mafomula (VLOOKUP, LOOKUP, uye SUMPRODUCT) atakambokurukura anoita nenzira imwechete. Asi iwe unoda iyo fomula yakakwana, handiti?
Kuita kuti formula iite sensitive INDEX+MATCH zvakakwana, zviise mukushanda IF (IF) iyo inoedza sero ine kukosha kwekudzoka uye kudzosera isina chinhu kana isina chinhu:
=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));"")
Mune iyi formula:
- B i column ine kudzoka ukoshi
- 1+ inhamba inoshandura chinzvimbo chesero yakadzoserwa nebasa ZVAKAWANDA AKAFUMURWA, kukero chaiyo yesero. Somuenzaniso, mubasa redu ZVAKAWANDA AKAFUMURWA tsvakiridzo yakapihwa A2:A7, ndiko kuti, nzvimbo ine hukama yesero A2 chido 1, nekuti ndiyo yekutanga muhurongwa. Asi nzvimbo chaiyo yesero A2 mu column ndi 2, saka tinowedzera 1kuita mutsauko uye kuve nebasa INDIRECT (INDIRECT) yatora kukosha kubva musero yaida.
Mifananidzo iri pazasi inoratidza yakagadziriswa kesi-sensitive formula INDEX+MATCH Mukuita. Inodzosa mhinduro isina chinhu kana sero yakadzoserwa isina chinhu.
Ndakanyora zvakare fomula kuita makolamu B:Dkuti ikwane iyo formula bar pane iyo skrini.
Formula returns 0kana sero rakadzoserwa riine zero.
Kana iwe uchida iyo link INDEX и ZVAKAWANDA AKAFUMURWA yakaratidza imwe meseji kana kukosha kwekudzoka kusina chinhu, unogona kuinyora mumashoko ekupedzisira (“”) efomati, semuenzaniso, seizvi:
=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.")
