Contents
Casharkan yar waxa uu sharxayaa sida loo sameeyo hawl VPR (VLOOKUP) kiis xasaasi ah, wuxuu muujinayaa dhowr habab oo kale oo Excel uu ku raadin karo qaab kiis xasaasi ah, wuxuuna tilmaamayaa meelaha ay ku fiican tahay iyo meelaha ay ku liidato hawl kasta.
Waxaan qiyaasayaa in isticmaale kasta oo Excel ah uu garanayo shaqada ay qabato raadinta tooska ah. Taasi waa sax, waa shaqo VPR. Si kastaba ha ahaatee, dad yar ayaa taas og VPR ma aha mid xasaasi ah, tusaale ahaan jilayaasha ka hoose iyo kuwa sare waa isku mid.
Halkan waxaa ah tusaale degdeg ah oo muujinaya awood la'aanta VPR aqoonsan diiwaanka. Ka soo qaad unug A1 ka kooban yahay qiimaha "biilka" iyo unugga A2 - "Bill", formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… waxay joojin doontaa raadinta “biil” maadaama qiimihiisu soo horeeyo liiska, oo uu ka soo saaro qiimaha unugga B1.
Mar dambe maqaalkan, waxaan ku tusi doonaa sida loo sameeyo VPR kiis xasaasi ah. Intaa waxaa dheer, waxaan baran doonaa dhowr hawlood oo dheeraad ah oo samayn kara baaritaanno xasaasi ah oo ku jira Excel.
Waxaan ku bilaabi doonaa kuwa ugu fudud - Muuqaal ahaan (DAAWO) iyo SUMPRODUCT (SUMPRODUCT), kaas oo, nasiib daro, leh dhawr xaddidaad oo muhiim ah. Marka xigta, waxaanu si qoto dheer u eegi doonaa qaacidada ka yara adag INDEX+CIYAAR (INDEX+MATCH), kaas oo si khaldan ugu shaqeeya xaalad kasta iyo xog kasta.
Shaqada VLOOKUP waa xaalad xasaasi ah
Sida aad horeba u ogtahay, shaqada caadiga ah VPR waa xaalad aan dareen lahayn. Si kastaba ha ahaatee, waxaa jira hab looga dhigi karo kiis xasaasi ah. Si tan loo sameeyo, waxaad u baahan tahay inaad ku darto tiir caawiye miiska, sida ku cad tusaalahan soo socda.
Ka soo qaad in tiir B waxaa jira calaamado alaabta (Shayga) oo aad rabto inaad ka soo saarto qiimaha alaabta iyo faallooyinka u dhigma ee tiirarka C и D. Dhibaatadu waxay tahay in aqoonsiga ay ka kooban yihiin xarfo yaryar iyo kuwa waaweyn labadaba. Tusaale ahaan, qiimaha unugyada B4 (001Tvci3u) iyo B5 (001Tvci3U) waxay ku kala duwan yihiin oo kaliya kiiska dabeecadda ugu dambeysa, u и U siday u kala horreeyaan.
Sida aad qiyaasi karto, qaacidada raadinta caadiga ah
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
soo laaban doona $ 90, tan iyo qiimaha 001Tvci3u wuxuu ku jiraa safafka raadinta ka hor 001Tvci3U. Laakiin taasi maaha waxa aan u baahanahay, ma aha?
Si aad shaqo ugu raadiso VPR Xaaladda xasaasiga ah ee Excel, waa inaad ku darto tiir caawiye oo aad unugyadeeda ku buuxisaa qaacidooyinka soo socda (halka B ay tahay tiirka raadinta):
=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));"")
Qaaciddadani waxay u kala jebisaa qiimihii la rabay xarfo gaar ah, waxay ku beddeshaa xarfo kasta koodkeeda (tusaale ahaan, halkii A at 65, halkii a code 97) ka dibna wuxuu isku daraa koodhkan lambarro gaar ah.
Taas ka dib, waxaan isticmaalnaa hawl fudud VPR Kiis raadinta xasaasiga ah:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Hawlgalka saxda ah ee shaqada VPR Kiis-xasaasiyaddu waxay ku xidhan tahay laba arrimood:
- Tiirka caawiye waa inuu ahaadaa tiirka bidix ee inta la arki karo.
- Qiimaha aad raadinayso waa in uu ka kooban yahay summada xarfaha halkii uu ka ahaan lahaa qiimaha dhabta ah.
Sida saxda ah ee loo isticmaalo shaqada CODE
Qaaciddada la geliyo unugyada tiirka kaalmuhu waxay u malaynaysaa in dhammaan qiyamka raadintaadu ay leeyihiin tiro isku mid ah oo xarfo ah. Haddii kale, markaa waxaad u baahan tahay inaad ogaato tirada ugu yar iyo kuwa ugu weyn oo aad ku darto sifooyin badan IFEROOR (IFERROR) inta xaraf ayaa ah farqiga u dhexeeya qiimaha ugu gaaban iyo kan ugu dheer ee la raadiyay.
Tusaale ahaan, haddii qiimaha raadinta ugu gaaban uu yahay 3 xaraf kan ugu dheerna uu yahay 5 xaraf, isticmaal qaacidadan:
=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));"")
Shaqada PSTR (MID) Waxaad bixisa doodaha soo socda:
- Doodda 1aad – text (qoraalka) waa tixraaca qoraalka ama unugga ka kooban jilayaasha la soo saarayo (xaaladkeena waa B2)
- Doodda 2aad – bilowga_nambar (start_position) waa booska ugu horreeya ee jilayaashaas la soo saaray. aad gasho 1 shaqada ugu horeysa PSTR, 2 - shaqada labaad PSTR iwm
- Doodda 3aad – tirada_chars (lambar_of_ characters) - Wuxuu qeexayaa tirada xarfaha laga soo saarayo qoraalka. Maadaama aan u baahanahay kaliya 1 dabeecad mar walba, dhammaan hawlaha waxaan ku qornaa 1.
XADGUDUBYADA: function VPR ma aha xalka ugu fiican ee raadinta xasaasiga ah ee Excel. Marka hore, ku-darka tiir-kaaliyaha ayaa loo baahan yahay. Marka labaad, qaaciddada waxay qabataa shaqo wanaagsan oo keliya haddii xogtu ay isku mid tahay, ama tirada saxda ah ee jilayaasha qiyamka la raadiyay waa la yaqaan. Haddii tani aysan ahayn kiiskaaga, waxaa fiican inaad isticmaasho mid ka mid ah xalalka aan ku muujineyno hoos.
Shaqada LOOKUP ee kiiska raadinta xasaasiga ah
function Muuqaal ahaan (LOOKUP) la xiriira VPR, si kastaba ha ahaatee syntaxkeedu wuxuu u oggolaanayaa raadinta kiisaska xasaasiga ah iyada oo aan lagu darin tiir caawiye ah. Si tan loo sameeyo, isticmaal Muuqaal ahaan oo lagu daray shaqada DHAQAN (SAX AH).
Haddii aan xogta ka soo qaadanno tusaalihii hore (iyada oo aan lahayn tiir caawiye ah), markaa qaacidada soo socota ayaa la qabsan doonta hawsha:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Baadhitaannada qaacidada ah ee kala duwan J2: A7 u dhigma saxda ah ee qiimaha unugga F2 kiis xasaasi ah oo ka soo celiya qiimaha tiirka B ee isla safka.
Like VPRfunction Muuqaal ahaan waxay si siman ugu shaqaysaa qoraalka iyo qiyamka tirooyinka, sida aad ku arki karto sawirka hoose:
Muhiim! Si ay u shaqeyso Muuqaal ahaan Si sax ah loo shaqeeyay, qiyamka tiirka raadinta waa in loo kala soocaa siday u kala horreeyaan, tusaale ahaan kan ugu yar ilaa kan ugu weyn.
Aan si kooban u sharaxo sida shaqadu u shaqeyso DHAQAN qaacidada kor ku xusan, maadaama ay tani tahay barta muhiimka ah.
function DHAQAN wuxuu isbarbar dhigayaa labada qiyam ee qoraalka ee 1aad iyo 2aad wuxuuna soo celinayaa RUN haddii ay isku mid yihiin, ama BEEN haddaysan ahayn. Waxaa muhiim noo ah in shaqada DHAQAN kiis xasaasi ah.
Aan aragno sida qaaciddeenu u shaqeyso DAAWO+SAX AH:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- function DHAQAN is barbar dhigaya qiimaha unugga F2 oo leh dhammaan walxaha ku jira tiir A (A2:A7). Waxay soo celisaa RUN haddii u dhigma saxda ah la helo, haddii kale BEEN.
- Maadaama aad siiso doodda shaqada ugu horreysa Muuqaal ahaan qiimaha TRUE, waxay ka soo saartaa qiimaha u dhigma tiirka la cayimay (xaaladkeena, tiirka B) kaliya haddii la helo isku mid sax ah, kiis xasaasi ah.
Waxaan rajeynayaa in sharaxaaddani ay ahayd mid cad oo hadda aad fahamtay fikradda ugu weyn. Haddii ay sidaas tahay, markaa wax dhib ah kuma yeelan doontid hawlaha kale ee aan sii falanqayn doono, sababtoo ah. Dhammaantood waxay ku shaqeeyaan mabda' isku mid ah.
XADGUDUBYADA: Xogta ku jirta tiirka raadinta waa in loo kala soocaa siday u korto.
SUMPRODUCT - waxay heshaa qiyamka qoraalka, kiis xasaasi ah, laakiin soo celisa tirooyinka kaliya
Sidaad ciwaanka horeba uga fahantay SUMPRODUCT (SUMPRODUCT) waa shaqo kale oo Excel ah oo kaa caawin doonta inaad samayso baaritaan kiis xasaasi ah, laakiin kaliya soo celin doona qiimayaasha tirada. Haddii doorashadani aysan kugu habboonayn, markaa waxaad isla markiiba u sii wadi kartaa xidhmada INDEX+CIYAAR, kaas oo siinaya xal kiis kasta iyo nooc kasta oo xog ah.
Marka hore, aan si kooban u sharaxo macnaha guud ee shaqadan, tani waxay kaa caawin doontaa inaad si fiican u fahanto qaacidada xasaasiga ah ee soo socota.
function SUMPRODUCT dhufaana cunsuryada hab-raacyada la bixiyay oo soo celiyaa wadarta natiijooyinka. Syntax-ku wuxuu u eg yahay sidan:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Maadaama aan u baahanahay baaritaan kiis xasaasi ah, waxaan isticmaalnaa shaqada DHAQAN (EXACT) laga soo bilaabo tusaalihii hore sida mid ka mid ah kuwa isku dhufanaya:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Sidaad xasuusato, DHAQAN is barbar dhigaya qiimaha unugga F2 oo leh dhammaan walxaha ku jira tiir A. Soo noqda RUN haddii u dhigma saxda ah la helo, haddii kale BEEN. Hawlaha xisaabta, Excel wuxuu u qaataa TRUE sida 1, iyo BEEN ABUUR 0Dheeraad ah SUMPRODUCT wuu badiyaa tirooyinkan oo wuxuu koobaa natiijooyinka.
Eber lama tiriyo sababtoo ah marka la dhufto waxay had iyo jeer bixiyaan 0. Aan si dhow u eegno waxa dhaca marka u dhigma saxda ah ee tiirka A helay oo soo noqday 1… Shaqada SUMPRODUCT ku dhufto tirada tiirka ku jira B on 1 oo soo celisa natiijadii - isla tirada dhabta ah! Tani waa sababta oo ah natiijooyinka alaabada kale waa eber, mana saameeyaan wadarta natiijada.
Nasiib darro shaqada SUMPRODUCT kuma shaqayn karaan qiyamka qoraalka iyo taariikhaha maadaama aan la badin karin. Xaaladdan, waxaad heli doontaa fariin qalad ah #QIIMAHA! (#QIIMAHA!) sida unugga dhexdiisa F4 sawirka hoose:
XADGUDUBYADA: Soo celisa qiimayaasha tirada kaliya.
INDEX + MATCH – kiis-xasaasi ah raadinta nooc kasta oo xog ah
Ugu dambeyntii, waxaan ku dhownahay qaacido raadin aan xadidneyn oo kiis xasaasi ah oo la shaqeysa xog kasta.
Tusaalahani wuxuu imanayaa ugu danbeyn, ma aha sababtoo ah waxa ugu fiican ayaa looga tagay macmacaan, laakiin sababtoo ah aqoonta laga helay tusaalooyinkii hore ayaa kaa caawin doona inaad si fiican oo degdeg ah u fahamto qaacidada xasaasiga ah ee kiiska. INDEX+CIYAAR (INDEX+MATCH).
Sida aad u malaynayso, isku darka hawlaha WAX KA BADAN и INDEX loo isticmaalo in Excel sida beddelka dabacsan oo awood badan VPR. Maqaalka Isticmaalka INDEX iyo MATCH halkii laga isticmaali lahaa VLOOKUP ayaa si fiican u sharxi doona sida hawlahani u wada shaqeeyaan.
Waxaan dib u soo koobayaa qodobbada muhiimka ah:
- function WAX KA BADAN (MATCH) waxay raadisaa qiyam kala duwan oo la bixiyay waxayna soo celisaa booskeeda qaraabada, taas oo ah, safka iyo/ama lambarka tiirka;
- Marka xigta, shaqada INDEX (INDEX) waxay ka soo celisaa qiimaha tiir cayiman iyo/ama saf.
Si loo habeeyo INDEX+CIYAAR Waxaad u baadhi kartaa kiis si xasaasi ah, kaliya waxaad u baahan tahay inaad ku darto hal shaqo. Ma adka in la qiyaaso waxa ay mar kale tahay DHAQAN (XAQ):
=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))
Qaabkan DHAQAN waxay u shaqeysaa si la mid ah sida ay ula shaqeyso shaqada Muuqaal ahaan, wuxuuna bixiyaa natiijooyin isku mid ah:
Ogow in qaacidada INDEX+CIYAAR ku lifaaqan biraha curdanka ah waa qaacido habaysan waana inaad ku buuxisaa adigoo riixaya Ctrl + Shift + Ku qor.
Maxay INDEX+MATCH u tahay xalka ugu fiican ee raadinta xasaasiga ah?
Faa'iidooyinka ugu muhiimsan ee xirmada INDEX и WAX KA BADAN:
- Uma baahna in lagu daro tiir caawiye ah, si ka duwan VPR.
- Uma baahna tiirka raadinta in la kala saaro, si ka duwan Muuqaal ahaan.
- La shaqeeya dhammaan noocyada xogta - tirooyinka, qoraalka iyo taariikhaha.
Habkani waxa uu u muuqdaa mid qumman, miyaanay ahayn? Dhab ahaantii, ma aha. Waana sababtaas.
Ka soo qaad in unugga ku jira tiirarka qiimaha soo celinta ee la xidhiidha qiimaynta qiimayntu ay faaruq tahay. Maxey natiijadu soo noqon doontaa? Maya? Aynu aragno waxa qaacidada dhabta ahi soo noqoto:
Haah, qaacidada waxay soo celisaa eber! Tani ma noqon karto dhibaato weyn haddii aad ku shaqaynayso qiyamka qoraalka saafiga ah. Si kastaba ha ahaatee, haddii miiska uu ka kooban yahay tirooyin, oo ay ku jiraan eber "dhabta ah", tani waxay noqonaysaa dhibaato.
Dhab ahaantii, dhammaan qaacidooyinka kale ee raadinta (VLOOKUP, LOOKUP, iyo SUMPRODUCT) ee aan horay uga hadalnay waxay u dhaqmaan si isku mid ah. Laakiin waxaad rabtaa qaacidada saxda ah, sax?
Si looga dhigo kiis qaacida mid xasaasi ah INDEX+CIYAAR kaamil ah, gelin shaqo IF (IF) kaas oo tijaabin doona unug leh qiime celin oo soo celin doona natiijo madhan haddii ay madhan tahay:
=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));"")
Qaabkan:
- B waa tiir leh qiyamka soo laabashada
- 1+ waa lambar roga booska qaraabo ee unugga shaqadu soo celisay WAX KA BADAN, cinwaanka dhabta ah ee unugga. Tusaale ahaan, shaqadeena WAX KA BADAN array la siiyay J2: A7, taas oo ah, booska qaraabada ee unugga A2 doono 1, sababtoo ah waa tii ugu horeysay ee diyaarinta. Laakiin booska dhabta ah ee unugga A2 tiirka waa 2, sidaas ayaan ku daraynaa 1in la sameeyo farqiga iyo in la yeesho shaqada TOOS AH (INDIRECT) ayaa ka soo saaray qiimihii unugga la rabay.
Sawirrada hoose waxay muujinayaan qaacidada xasaasiga ah ee kiiska la saxay INDEX+CIYAAR Ficil ahaan. Waxay soo celinaysaa natiijo madhan haddii unugga la soo celiyay uu madhan yahay.
Waxaan dib u qoray qaacidada tiirar B:Dsi ay ugu habboonaato bar caanaha ee shaashadda.
Formula soo laabtay 0haddii unugga la soo celiyay uu ka kooban yahay eber.
Haddii aad rabto isku xirka INDEX и WAX KA BADAN Muujiyay xoogaa fariin ah marka qiimaha soo laabashadu uu madhan yahay, waxaad ku qori kartaa xigashooyinka u dambeeya ("") ee qaacidada, tusaale ahaan, sidan oo kale:
=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.")
