Tse ka Hare
Thupelo ena e nyane e hlalosa mokhoa oa ho etsa ts'ebetso VPR (VLOOKUP) ha e na taba, e bonts'a liforomo tse ling tse 'maloa tseo Excel e ka li batlang ka mokhoa o sa tsotelleng, 'me e supa matla le bofokoli ba mosebetsi ka mong.
Ke nahana hore mosebelisi e mong le e mong oa Excel oa tseba hore na ke ts'ebetso efe e etsang lipatlisiso tse otlolohileng. Ke hantle, ke mosebetsi VPR. Leha ho le joalo, ke batho ba fokolang ba tsebang seo VPR ha e na taba, ke hore, litlhaku tse tlase le tse kholo lia tšoana bakeng sa eona.
Mona ke mohlala o potlakileng o bontšang ho se khonehe VPR hlokomela rejisetara. A re re ka seleng A1 e na le boleng "bili" le sele A2 – “Bill”, foromo:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… e tla emisa patlo ea eona ho "bili" kaha boleng boo bo tla pele lethathamong, 'me e ntše boleng ho tsoa seleng B1.
Hamorao sehloohong sena, ke tla u bontša mokhoa oa ho etsa VPR e latela litlhaku. Ho feta moo, re tla ithuta tse ling tse 'maloa tse ka etsang lipatlisiso tsa maemo ho Excel.
Re tla qala ka tse bonolo ka ho fetisisa - VIEW (LOOKUP) le SUMPRODUCT (SUMPRODUCT), eo, ka bomalimabe, e nang le mefokolo e mengata ea bohlokoa. Ka mor'a moo, re tla shebisisa foromo e rarahaneng haholoanyane INDEX+MATCH (INDEX+MATCH), e sebetsang ka mokhoa o se nang sekoli maemong afe kapa afe le ka pokello ea data efe kapa efe.
Mosebetsi oa VLOOKUP o na le maemo a bobebe
Joalokaha u se u ntse u tseba, mosebetsi o tloaelehileng VPR ha e na taba. Leha ho le joalo, ho na le mokhoa oa ho etsa hore ho be bonolo. Ho etsa sena, o hloka ho eketsa kholomo e thusang tafoleng, joalo ka ha ho bontšitsoe mohlaleng o latelang.
Ha re re ka kholomong B ho na le li-identifiers tsa sehlahisoa (Item) 'me u batla ho ntša theko ea sehlahisoa le maikutlo a tšoanang ho tsoa likholomong. C и D. Bothata ke hore li-identifier li na le litlhaku tse nyane le tse kholo. Ka mohlala, litekanyetso tsa lisele B4 (001Tvci3u) le B5 (001Tvci3U) e fapana feela tabeng ea sebapali sa ho qetela, u и U ka ho latellana.
Joalokaha u ka inahanela, mokhoa o tloaelehileng oa ho batla
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
e tla khutla $ 90, kaha boleng 001Tvci3u e sebakeng sa ho batla pele ho nako 001Tvci3U. Empa ha se seo re se hlokang, na ha ho joalo?
Ho batla ka tshebetso VPR ho Excel case sensitive, u tla tlameha ho eketsa kholomo ea mothusi 'me u tlatse lisele tsa eona ka foromo e latelang (moo B e leng kholomo ea ho sheba):
=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));"")
Foromo ena e arola boleng bo lakatsehang ka litlhaku tse arohaneng, e nkela sebapali se seng le se seng sebaka ka khoutu ea sona (mohlala, sebakeng sa A ho 65, ho e-na le hoo a khoutu 97) ebe o kopanya likhoutu tsena ho etsa letoto le ikhethileng la linomoro.
Ka mor'a moo, re sebelisa mosebetsi o bonolo VPR bakeng sa patlo e hlokolosi ea taba:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Ts'ebetso e nepahetseng ea ts'ebetso VPR taba-sensitive e itšetlehile ka lintlha tse peli:
- Kholomo e thusang e tlameha ho ba kholomo e ka ho le letšehali sebakeng se bonoang.
- Boleng boo u bo batlang bo tlameha ho ba le khoutu ea litlhaku ho e-na le boleng ba sebele.
Mokhoa oa ho sebelisa tšebetso ea CODE ka nepo
Foromo e kentsoeng ka har'a lisele tsa kholomo e thusang e nka hore boleng bohle ba hau ba patlo bo na le litlhaku tse lekanang. Haeba ho se joalo, o hloka ho tseba lipalo tse nyane le tse kholo mme o kenye likarolo tse ngata IFEREKE (IFERROR) phapang ke hore na ke litlhaku tse kae lipakeng tsa boleng bo batliloeng bo bokhuts'oane le bo bolelele.
Mohlala, haeba palo e khuts'oane ea patlo e le litlhaku tse 3 'me e telele e le litlhaku tse 5, sebelisa foromo ena:
=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));"")
Bakeng sa tshebetso PSTR (MID) O fana ka mabaka a latelang:
- Khang ea pele - mongolo (mongolo) ke mongolo kapa tšupiso ea sele e nang le litlhaku tse lokelang ho ntšoa (ho rona ke B2)
- Khang ea pele - qala_nomoro (start_position) ke boemo ba baphetwa ba pele ba tla ntshwa. o kena 1 mosebetsing oa pele PSTR, 2 – tshebetsong ya bobedi PSTR joalo-joalo
- Khang ea pele - palo_ea litlhaku (nomber_of_characters) - E hlalosa palo ea litlhaku tse ka ntšoang sengolong. Kaha re hloka tlhaku e le 'ngoe feela ka linako tsohle, mesebetsing eohle eo re e ngolang 1.
LITLHAELO: mosebetsi VPR ha se tharollo e molemohali ea lipatlisiso tse sa tsotelleng taba ho Excel. Taba ea pele, ho hlokahala ho eketsoa ha kholomo e thusang. Taba ea bobeli, foromo e sebetsa hantle ha feela data e le homogeneous, kapa palo e nepahetseng ea batho bao ho buuoang ka litekanyetso tse batlisisitsoeng e tsejoa. Haeba sena ha se taba ea hau, ho molemo ho sebelisa e 'ngoe ea litharollo tseo re li bonts'ang ka tlase.
Mosebetsi oa LOOKUP bakeng sa patlo e hlokolosi ea taba
mosebetsi VIEW (LOOKUP) e amanang VPR, leha ho le joalo syntax ea eona e lumella lipatlisiso tse amehang ntle le ho eketsa kholomo ea tlatsetso. Ho etsa sena, sebelisa VIEW mmoho le mosebetsi LEHLOHONOLO (NETE).
Haeba re nka data ho tsoa mohlaleng o fetileng (ntle le kholomo e thusang), mokhoa o latelang o tla sebetsana le mosebetsi:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Lipatlisiso tsa foromo ka bongata A2:A7 e tšoanang hantle le boleng ba sele F2 case sensitive 'me e khutlisetsa boleng ho tsoa kholomong ea B ea mola o tšoanang.
joaloka VPRmosebetsi VIEW e sebetsa ka mokhoa o ts'oanang le boleng ba mongolo le linomoro, joalo ka ha u bona setšoantšong se ka tlase:
Bohlokoa! E le hore bakeng sa tshebetso VIEW e sebetsa ka nepo, litekanyetso tse kholomong ea ho sheba li lokela ho hlophisoa ka tatellano e nyolohang, ke hore, ho tloha ho tse nyane ho isa ho tse kholo.
E-re ke hlalose ka bokhutšoanyane hore na mosebetsi o sebetsa joang LEHLOHONOLO ka foromo e bontšitsoeng ka holimo, kaha ena ke ntlha ea bohlokoa.
mosebetsi LEHLOHONOLO e bapisa boleng ba litemana tse peli likhang tsa 1st le 2nd mme e khutlisa TRUE haeba li tšoana hantle, kapa FALSE haeba li sa tšoane. Ho bohlokoa ho rona hore mosebetsi LEHLOHONOLO e latela litlhaku.
A re boneng hore na foromo ea rona e sebetsa joang SHEBELLA+NETE:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- mosebetsi LEHLOHONOLO e bapisa boleng ba sele F2 e nang le likarolo tsohle ka kholomong A (A2:A7). E khutlisa TRUE haeba e tšoanang hantle e fumanoa, ho seng joalo FALSE.
- Ho tloha ha o fana ka khang ea pele ea ts'ebetso VIEW boleng TRUE, e ntša boleng bo lumellanang le kholomo e boletsoeng (boemong ba rona, kholomo ea B) hafeela ho na le ntho e tšoanang hantle, e hlokolosi.
Ke tšepa hore tlhaloso ena e ne e hlakile 'me joale u utloisisa mohopolo oa sehlooho. Haeba ho joalo, joale u ke ke ua ba le mathata leha e le afe ka mesebetsi e meng eo re tla e hlahloba ho ea pele, hobane. kaofela ba sebetsa ka molao-motheo o le mong.
LITLHAELO: Lintlha tse kholomong ea ho sheba li tlameha ho hlophisoa ka tatellano e nyolohang.
SUMPRODUCT - e fumana boleng ba mongolo, bo bobebe, empa e khutlisa linomoro feela
Joalo ka ha u se u utloisisitse ho tsoa sehloohong, SUMPRODUCT (SUMPRODUCT) ke ts'ebetso e 'ngoe ea Excel e tla u thusa ho etsa patlisiso e hlokolosi, empa e tla khutlisa lipalo tsa lipalo. Haeba khetho ena e sa u tšoanele, joale u ka tsoela pele hang-hang ho ea ho bongata INDEX+MATCH, e fanang ka tharollo bakeng sa nyeoe efe kapa efe le bakeng sa mefuta efe kapa efe ea data.
Taba ea pele, e re ke hlalose ka bokhuts'oane syntax ea tšebetso ena, sena se tla u thusa ho utloisisa hamolemo mokhoa o latelang oa taba e amehang.
mosebetsi SUMPRODUCT e atisa likarolo tsa lihlopha tse fanoeng ebe e khutlisetsa kakaretso ea liphetho. Syntax e shebahala tjena:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Kaha re hloka patlisiso e hlokolosi, re sebelisa ts'ebetso LEHLOHONOLO (EXACT) ho tsoa mohlaleng o fetileng e le e 'ngoe ea li-multiplier:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Joalokaha u hopola, LEHLOHONOLO e bapisa boleng ba sele F2 e nang le likarolo tsohle ka kholomong A. E khutlisa TRUE haeba e tšoanang hantle e fumanoa, ho seng joalo FALSE. Lits'ebetsong tsa lipalo, Excel e nka TRUE joalo ka 1, le BOHATA bakeng sa 0Ho feta moo SUMPRODUCT e atisa lipalo tsena ebe e akaretsa liphetho.
Li-zero ha li baloe hobane ha li ngatafala li fana kamehla 0. A re hlahlobeng ka hloko se etsahalang ha ho tšoana hantle kholomong A e fumanoeng mme e khutlisetsoe 1… Mosebetsi SUMPRODUCT atisa palo kholomong B on 1 mme e khutlisa sephetho - palo e ts'oanang hantle! Sena ke hobane liphetho tsa lihlahisoa tse ling ke zero, 'me ha li ame kakaretso e hlahisoang.
Ka bomalimabe mosebetsi SUMPRODUCT ha e khone ho sebetsa ka boleng ba mongolo le matsatsi kaha a ke ke a atisa. Tabeng ena, o tla fumana molaetsa oa phoso #BOTLALO! (#VALUE!) joalo ka seleng F4 setšoantšong se ka tlase:
LITLHAELO: E khutlisa feela lipalo.
INDEX + MATCH - patlo e hlokolosi ea mofuta ofe kapa ofe oa data
Qetellong, re haufi le mokhoa oa ho batla o sa lekanyetsoang le o amehang haholo o sebetsang le sehlopha sefe kapa sefe sa data.
Mohlala ona ke oa ho qetela, eseng hobane se theolelang se le molemo ka ho fetisisa, empa hobane tsebo e fumanoeng mehlaleng e fetileng e tla u thusa ho utloisisa mokhoa o bonolo haholoanyane le kapele. INDEX+MATCH (INDEX+MATCH).
Joalokaha mohlomong u nahanne, motsoako oa mesebetsi EKETSEHILENG HLAKOA и INDEX e sebelisoang ho Excel joalo ka mokhoa o feto-fetohang le o matla bakeng sa VPR. Sengoloa se Sebelisang INDEX le MATCH sebakeng sa VLOOKUP se tla hlalosa hantle hore na mesebetsi ena e sebelisana joang.
Ke tla pheta lintlha tsa bohlokoa feela:
- mosebetsi EKETSEHILENG HLAKOA (MATCH) e batla boleng boemong bo fanoeng mme e khutlisetsa boemo ba eona bo lekanyelitsoeng, ke hore, mola le/kapa nomoro ea kholumo;
- Ka mor'a moo, tshebetso INDEX (INDEX) e khutlisa boleng ho tsoa kholomong le/kapa mola o boletsoeng.
Ho foromo INDEX+MATCH e ka batlisisa ka hloko, o hloka feela ho kenya tšebetso e le 'ngoe ho eona. Ha ho thata ho hakanya hore na ke eng hape LEHLOHONOLO (NETE):
=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))
Ka foromo ena LEHLOHONOLO e sebetsa ka tsela e tshoanang le mmoho le mosebetsi VIEW, mme e fana ka sephetho se tšoanang:
Hlokomela hore formula INDEX+MATCH e kentsoeng ka litšepe tse kobehileng ke mokhoa o hlophisitsoeng 'me u tlameha ho e tlatsa ka ho tobetsa Ctrl + Shift + Kena.
Ke hobane'ng ha INDEX+MATCH e le tharollo e molemo ka ho fetisisa bakeng sa patlo e boima haholo?
Melemo e ka sehloohong ea bongata INDEX и EKETSEHILENG HLAKOA:
- Ha e hloke ho eketsa kholumo e thusang, ho fapana le eona VPR.
- Ha e hloke hore karolo ea ho batla e hlophisoe, ho fapana le hoo VIEW.
- E sebetsa ka mefuta eohle ea data - linomoro, mongolo le matsatsi.
Foromo ena e bonahala e phethahetse, na ha ho joalo? Ha e le hantle, ha ho joalo. Mme ke ka baka leo.
Nka hore sele e kholomong ea boleng ba ho khutla e amanang le boleng ba ho batla ha e na letho. Foromo e tla khutlisa sephetho sefe? Che? Ha re boneng hore na ha e le hantle foromo e khutlisetsa eng:
Oops, foromo e khutlisa zero! Sena e kanna ea se be bothata bo boholo haeba o sebetsa ka boleng bo hlakileng ba mongolo. Leha ho le joalo, haeba tafole e na le linomoro, ho kenyelletsa le zero "ea sebele", sena se fetoha bothata.
Ebile, liforomo tse ling kaofela (VLOOKUP, LOOKUP, le SUMPRODUCT) tseo re buileng ka tsona pejana li sebetsa ka tsela e ts'oanang. Empa u batla foromo e phethahetseng, na ha ho joalo?
Ho etsa hore khetsi ea foromo e be bobebe INDEX+MATCH e phethahetseng, e behe ts'ebetsong IF (IF) e tla leka sele e nang le boleng ba ho khutlisa le ho khutlisa sephetho se se nang letho haeba se se na letho:
=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));"")
Ka mokhoa ona:
- B ke kholomo e nang le boleng ba ho khutla
- 1+ ke nomoro e fetolang boemo bo lekanyelitsoeng ba sele e khutliselitsoeng ke mosebetsi EKETSEHILENG HLAKOA, ho ea atereseng ea sebele ea sele. Ka mohlala, mosebetsing oa rona EKETSEHILENG HLAKOA ho fanoe ka lethathamo la lipatlisiso A2:A7, ke hore, boemo bo lekanyelitsoeng ba sele A2 e tla 1, hobane ke ea pele lethathamong. Empa boemo ba sebele ba sele A2 kholomong ke 2, kahoo re eketsa 1ho etsa phapang le ho ba le mosebetsi TS'ELISO (INDIRECT) e fumane boleng ho tsoa seleng e batloang.
Lits'oants'o tse ka tlase li bonts'a mokhoa o lokisitsoeng oa boemo bo thata INDEX+MATCH Ketsong. E khutlisa sephetho se se nang letho haeba sele e khutlisitsoeng e se na letho.
Ke ngotse foromo hape ka likholomo B:Dho lekana bare ea foromo skrineng.
Foromo e khutla 0haeba sele e khutlisitsoeng e na le zero.
Haeba u batla link INDEX и EKETSEHILENG HLAKOA e bonts'itse molaetsa o itseng ha boleng ba ho khutla bo se na letho, u ka bo ngola mantsoeng a ho qetela (“”) a foromo, mohlala, tjena:
=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.")
