Maxay INDEX iyo MATCH uga fiican yihiin VLOOKUP gudaha Excel

Waxaan horey ugu sharaxnay bilawga sida loo isticmaalo hawlaha aasaasiga ah ee VLOOKUP ( Ingiriisi VLOOKUP, soo gaabinta waxay u taagan tahay "shaqada raadinta toosan"). Isticmaalayaasha khibradda leh ayaa la tusay dhowr habab oo aad u adag.

Maqaalkan waxaan isku dayi doonaa inaan ku siinno macluumaad ku saabsan hab kale oo lagu shaqeynayo raadinta toosan.

Waxaa laga yaabaa inaad la yaabban tahay: "Waa maxay sababta tani loogu baahan yahay?". Tanina waa lagama maarmaan si loo muujiyo dhammaan hababka raadinta ee suurtogalka ah. Intaa waxaa dheer, xaddidaadyo badan oo VLOOKUP ah ayaa inta badan ka hortagaya helitaanka natiijada la rabo. Marka tan la eego, INDEX () MATCH () aad ayey u shaqeysaa oo kala duwan tahay, waxayna sidoo kale leeyihiin xaddidaadyo yar.

Aasaaska INDEX MATCH

Maadaama ujeedada hagahan ay tahay in la muujiyo sida uu u wanaagsan yahay habkani, annagu Aynu eegno macluumaadka aasaasiga ah ee ku saabsan mabaadi'da hawlgalkeeda. Oo waxaan tusi doonaa tusaalooyin, iyo sidoo kale tixgelin sababta, waa ka fiican yahay VLOOKUP ().

INDEX Shaqada Isku-dhafka iyo Isticmaalka

Shaqadani waxay ka caawisaa in la helo qiimaha la rabo oo ka mid ah meelaha raadinta ee la cayimay ee ku salaysan tiirka ama lambarka khadka. Kalfadhiga:

=INDEX(hab, nambar saf, nambarka tiirka):

• array - aagga goobtu ka dhici doonto;
• nambarka xariiqda – tirada xariiqda la raadinayo ee soo diyaarsanaanta la cayimay. Haddii lambarka safka aan la garanayn, lambarka tiirka waa in la cayimaa;
• nambarka tiirka - tirada tiirka ee laga heli karo shaxda la cayimay. Haddii qiimaha aan la garanayn, lambarka xariiqa ayaa loo baahan yahay.

Tusaale qaacido fudud:

=INDEX(A1:S10,2,3)

Shaqadu waxay ka raadin doontaa inta u dhaxaysa A1 ilaa C10. Tirooyinka ayaa muujinaya safka (2) iyo tiirka (3) ee muujinaya qiimaha la rabo. Natiijadu waxay noqon doontaa unugga C2.

Aad u fudud, sax? Laakin marka aad ku shaqayso dukumentiyada dhabta ah, uma badna inaad hayso xog ku saabsan tirooyinka tiirka ama unugyada. Taasi waa waxa shaqada MATCH() loogu talagalay.

MATCH Function Syntax iyo Isticmaalka

Shaqada MATCH() waxay raadisaa qiimaha la rabo oo waxay ku tusinaysaa nambarkeeda qiyaas ahaan goobta raadinta ee la cayimay.

Syntax-ka raadinta () waxay u egtahay sidan:

= MATCH (qiimaha in la eego, habayn wax lagu baadho, nooca u dhigma)

• qiimaha raadinta - lambarka ama qoraalka la heli karo;
• array la baadhay - aagga goobtu ka dhacayso;
• nooca ciyaarta - wuxuu qeexayaa haddii la raadinayo qiimaha saxda ah ama qiyamka ugu dhow:
  • 1 (ama aan qiimo la cayimin) - waxay soo celisaa qiimaha ugu weyn ee la mid ah ama ka yar qiimaha la cayimay;
  • 0 - wuxuu muujinayaa u dhigma saxda ah ee qiimaha la raadiyay. Isku dhafka INDEX() MATCH() waxaad had iyo jeer u baahan doontaa ciyaar sax ah, marka waxaan ku qornaa 0;
  • -1 - Wuxuu muujiyaa qiimaha ugu yar ee ka weyn ama la mid ah qiimaha lagu sheegay qaacidada. Kala soocida waxa loo fuliyaa siday u kala horeeyaan.

Tusaale ahaan, inta u dhaxaysa B1:B3 New York, Paris, London way ka diiwaan gashan yihiin. Qaaciddada hoose waxay tusi doontaa lambarka 3 sababtoo ah London waa saddexaad ee liiska:

=EXPOSE(London,B1:B3,0)

Sida loola shaqeeyo shaqada INDEX MATCH 

Waxay u badan tahay inaad mar hore bilowday inaad fahamto mabda'a ay ku dhisan tahay shaqada wadajirka ah ee hawlahan. Marka la soo koobo, haddaba INDEX() waxay ka dhex raadisaa qiimaha la rabo ee safafka iyo tiirarka la cayimay. Iyo MATCH() waxay tusinaysaa tirooyinka qiimayaashan:

=INDEX(saxda qiimaha laga soo celiyay, MATCH

Weli waxaa kugu adag inaad fahamto sida ay u shaqeyso? Waxaa laga yaabaa in tusaale si fiican u sharaxi doono. Ka soo qaad inaad haysato liiska caasimadaha adduunka iyo dadkooda:

Si loo ogaado tirada dadka ku nool caasimad gaar ah, tusaale ahaan, caasimadda Japan, waxaan isticmaalnaa qaacidada soo socota:

=INDEX(C2:C10, MATCH (Japan, A2:A10,0))

Sharaxaada:

• Shaqada MATCH() waxay eegaysaa qiimaha - "Japan" ee qaabka A2: A10 oo soo celisa lambarka 3, sababtoo ah Japan waa qiimaha saddexaad ee liiska. 
• Tiradan ayaa ku socotalambarka khadka” ee qaaciidada INDEX() oo u sheegaysa shaqada inay qiime ka daabacdo safkan.

Markaa qaacidada sare waxay noqotaa qaacidada caadiga ah INDEX(C2:C10,3). Nadaafadu waxay ka baadhaysaa unugyada C2 ilaa C10 waxayna ka soo celisaa xogta unugga saddexaad ee kala duwan, taas oo ah, C4, sababtoo ah tirinta waxay ka bilaabataa safka labaad.

Ma rabto inaad magaca magaalada ku qorto qaacidada? Kadibna ku qor unug kasta, dheh F1, una isticmaal tixraac ahaan qaacidada MATCH(). Oo waxaad ku dhammaynaysaa qaacido raadin firfircoon:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Muhiim! Tirada khadadka gudaha array INDEX() waa in ay la mid noqotaa tirada safafka loo tixgeliyey diyaarsan MATCH(), haddii kale waxaad heli doontaa natiijo khaldan.

Sug hal daqiiqo, maxaad u isticmaali weyday kaliya qaacidada VLOOKUP()?

= VLOOKUP (F1, A2: C10, 3, Been)

 Maxay tahay faa'iidada wakhtiga luminaysa isku dayga lagu ogaanayo dhammaan caqabadahan kakan ee INDEX MATCH?

Xaaladdan oo kale, dhib ma laha shaqada la isticmaalayo. Kani waa tusaale si loo fahmo sida INDEX() iyo MATCH() u wada shaqeeyaan. Tusaalooyinka kale ayaa tusi doona waxa ay hawlahani awood u leeyihiin xaaladaha VLOOKUP aanu awood lahayn. 

INDEX MATCH ama VLOOKUP

Marka la go'aaminayo qaacidada raadinta ee la isticmaalayo, qaar badan ayaa isku raacay in INDEX() iyo MATCH() ay aad uga sarreeyaan VLOOKUP. Si kastaba ha ahaatee, dad badan ayaa wali isticmaala VLOOKUP(). Marka hore, VLOOKUP () way ka sahlan tahay, marka labaadna, isticmaalayaashu si buuxda uma fahmaan dhammaan faa'iidooyinka la shaqeynta INDEX () iyo MATCH (). Aqoontan la'aanteed, qofna ma aqbali doono inuu waqtigooda ku bixiyo barashada nidaam adag.

Waa kuwan faa'iidooyinka muhiimka ah ee INDEX() iyo MATCH() marka loo eego VLOOKUP():

• Ka raadi midig ilaa bidix VLOOKUP() kama raadin karto midig ilaa bidix, markaa qiyamka aad raadinayso waa inay had iyo jeer ku jiraan tiirarka bidix ee miiska. Laakin INDEX() iyo MATCH() way xamili karaan tan dhib la'aan. Maqaalkani wuxuu kuu sheegi doonaa waxa uu u eg yahay ficil ahaan: sida loo helo qiimaha la rabo ee dhinaca bidixda.

1. Ku-darka ama ka-saarista tiirarka badbaadada leh. Qaaciddada VLOOKUP() waxay muujinaysaa natiijooyin khaldan marka la saarayo ama lagu darayo tiirarka sababtoo ah VLOOKUP() waxay u baahan tahay lambarka saxda ah si uu u guuleysto. Dabiici ahaan, marka tiirarka lagu daro ama la saaro, tiradooda sidoo kale way isbedelaan. 

INDEX() iyo MATCH() qaacidooyinka, tiirar kala duwan ayaa lagu cayimay, ma aha tiirar gaar ah. Natiijo ahaan, waxaad si badbaado leh ugu dari kartaa oo uga saari kartaa tiirarka adiga oo aan u baahnayn inaad cusboonaysiiso caanaha mar kasta.

1. Ma xadidna tirada raadinta. Markaad isticmaalayso VLOOKUP(), tirada guud ee shuruudaha raadinta waa in aanay dhaafin 255 xaraf ama waxaad heli doontaa #VALUE! Markaa haddii xogtaadu ka kooban tahay tiro badan oo xarfo ah, INDEX() iyo MATCH() ayaa ah ikhtiyaarka ugu fiican.
2. Xawaaraha farsamaynta sare. Haddii miisaskaagu ay yar yihiin, markaa lagama yaabo inaad aragto wax farqi ah. Laakiin, haddii miiska uu ka kooban yahay boqollaal ama kumanaan saf, iyo, si waafaqsan, waxaa jira boqollaal iyo kun oo qaacido ah, INDEX () iyo MATCH () ayaa la qabsan doona si ka dhaqso badan VLOOKUP (). Xaqiiqdu waxay tahay in Excel uu farsamayn doono oo keliya tiirarka lagu qeexay qaacidada, halkii laga shaqayn lahaa miiska oo dhan. 

Saamaynta waxqabadka VLOOKUP() ayaa si gaar ah loo ogaan doonaa haddii xaashida shaqada ay ka kooban tahay tiro badan oo qaabab ah sida VLOOKUP() iyo SUM(). Jeegaga kala duwan ee hawlaha VLOOKUP() ayaa loo baahan yahay si loo kala saaro qiime kasta oo habaysan. Markaa Excel waa inuu farsameeyaa macluumaad aad u badan, taasina waxay hoos u dhigtaa shaqada si weyn.

Tusaalooyinka Formula 

Waxaan horay u ogaanay faa'iidada shaqooyinkan, sidaas darteed waxaan u gudbi karnaa qaybta ugu xiisaha badan: adeegsiga aqoonta ee ficil ahaan.

Foomamka lagu baadho midig ilaa bidix

Sidii horeba loo sheegay, VLOOKUP ma samayn karto qaabkan raadinta. Marka, haddii qiyamka la rabo aysan ku jirin tiirka bidix, VLOOKUP () ma soo saari doonto natiijo. Hawlaha INDEX () iyo MATCH () waa kuwo aad u badan, iyo goobta qiyamku ma door weyn inay shaqeeyaan.

Tusaale ahaan, waxaan ku dari doonaa tiir darajada dhinaca bidix ee miiskayaga oo aan isku dayno inaan ogaanno darajada marka loo eego tirada dadka caasimadda Dalkeenu ay ku jirto.

Unugga G1, waxaanu ku qornaa qiimaha la heli karo, ka dibna isticmaal qaacidada soo socota si aan u baadho xadka C1:C10 oo aan ka soo celinno qiimaha u dhigma A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

degdeg Haddii aad qorsheyneyso inaad u isticmaasho qaaciidadan unugyo badan, hubi inaad hagaajiso kala duwanaanta adigoo isticmaalaya ciwaan buuxa (tusaale, $ A$2: $A$10 iyo $C$2: 4C$10).

INDEX BADAN OO BANDHIGAY IN KA BADAN  si loo raadiyo tiirar iyo saf

Tusaalooyinka kor ku xusan, waxaan u isticmaalnay shaqooyinkan beddelka VLOOKUP() si aan uga soo celinno qiyamka safaf hore loo sii qeexay. Laakiin maxaa dhacaya haddii aad u baahan tahay inaad sameyso matrix ama raadinta laba dhinac ah?

Waxay u egtahay mid adag, laakiin qaacidada xisaabinta noocaas ah waxay la mid tahay INDEX() MATCH() caadiga ah, oo leh hal farqi oo keliya: qaacidada MATCH() waa in la isticmaalo laba jeer. Marka ugu horraysa ee la helo lambarka safka, iyo marka labaad ee la helo lambarka tiirka:

=INDEX(array, MATCH(qiimaha raadinta toosan, tiirka raadinta, 0), MATCH(qiimaha goob raadinta, safka goobida, 0))

Aan eegno shaxda hoose oo aan isku dayno inaan samayno qaacido INDEX() EXPRESS() EXPRESS() si loo muujiyo tirakoobka dal gaar ah sanad la doortay.

Dalka la beegsanayo waxa uu ku jiraa unugga G1 (fiirin toosan) halka sanadka la beegsanayaana uu ku jiro unugga G2 (hoos ka eegid). Qaaciddada ayaa u ekaan doonta sidan:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Sida caanahani u shaqeyso

Sida hababka kale ee kakan, way fududahay in la fahmo iyada oo loo kala qaybinayo isla'egyo gaar ah. Kadibna waxaad fahmi kartaa shaqada shaqsi kastaa waxay qabato:

• ISKUULKA(G1,A2:A11,0) - waxay raadisaa qiime (G1) inta u dhaxaysa A2: A11 waxayna muujinaysaa tirada qiimahan, kiiskeena waa 2;
• SEARCH (G2,B1:D1,0) - waxay ka raadisaa qiime (G2) inta u dhaxaysa B1:D1. Xaaladdan, natiijadu waxay noqotay 3.

Safka la helay iyo tirooyinka tiirka waxaa loo diraa qiimaha u dhigma ee qaacidada INDEX():

=INDEX(B2:D11,2,3)

Natiijo ahaan, waxaan leennahay qiime ku jira unug ku yaal isgoyska 2 saf iyo 3 tiir ee xadka B2:D11. Waxayna qaacidadu muujinaysaa qiimaha la rabo, kaas oo ku jira unugga D3.

Ku raadi shuruudo badan INDEX iyo MATCH

Haddii aad akhriday hagahayaga VLOOKUP(), waxa ay u badan tahay in aad isku dayday qaabab badan oo raadin. Laakiin habkan raadinta ayaa leh hal xaddidan oo muhiim ah - baahida loo qabo in lagu daro tiir caawiye ah.

Laakiin warka wanaagsani waa kaas INDEX() iyo MATCH() waxaad ku raadin kartaa xaalado badan adiga oo aan wax ka beddelin ama bedelin xaashida shaqada.

Waa kan qaacidada guud ee shuruudaha badan ee INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Qoraalka: qaaciidadan waa in lala isticmaalo kiiboodhka gaaban CTRL+SHIFT+ENTER.

Ka soo qaad inaad u baahan tahay inaad hesho qiimaha aad raadinayso iyadoo lagu salaynayo 2 shuruudood: Buyer и Wax soo saar.

Tani waxay u baahan tahay qaacidada soo socota:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Qaaciddan, C2:C10 waa inta u dhaxaysa raadinta F1 - xaaladdan, A2: A10 — waa kala duwanaanshaha isbarbardhigga xaaladda, F2 - xaalad 2, V2:V10 - kala duwanaanshaha isbarbardhigga xaaladda 2.

Ha iloobin inaad ku cadaadiso isku dhafka dhamaadka shaqada ee qaacidada CTRL + SHIFT + GAL - Excel si otomaatig ah ayuu u xiri doonaa qaacidada isagoo leh xargo-duuban, sida ku cad tusaalaha:

Haddii aadan rabin inaad u isticmaasho hab habaysan shaqadaada, dabadeed ku dar INDEX() kale qaaciddada oo taabo ENTER, waxay u ekaan doontaa tusaale ahaan:

Sida qaababkani u shaqeeyaan

Habkani wuxuu u shaqeeyaa si la mid ah habka INDEX() MATCH() ee caadiga ah. Si aad u raadiso xaalado badan, waxaad si fudud u abuurtaa xaalado badan oo Been iyo Run ah kuwaas oo matalaya xaaladaha shakhsi ahaaneed ee saxda ah iyo kuwa khaldan. Kadibna shuruudahani waxay khuseeyaan dhammaan walxaha u dhigma ee diyaarinta. Qaaciddada waxay u beddeshaa doodaha beenta iyo runta ah ee 0 iyo 1, siday u kala horreeyaan, waxayna soo saartaa array halka 1 uu yahay qiimaha isku midka ah ee laga helay xadhigga. MATCH() ayaa heli doona qiimaha ugu horeeya ee u dhigma 1 oo u gudbi doona INDEX() qaacidada Oo iyadu, markeeda, waxay soo celin doontaa qiimihii hore loo rabay ee xariiqda la cayimay ee tiirka la rabo.

Qaaciddada aan habayn lahayn waxay ku xidhan tahay awoodda INDEX() in ay iskeed wax uga qabato. INDEX labaad () ee qaacidadu waxay u dhigantaa been abuur (0), markaa waxay u gudbisaa dhammaan shaxanka qiimayaashaas MATCH() formula. 

Tani waa sharraxaad aad u dheer oo ku saabsan macquulka ka dambeeya qaacidada this. Wixii macluumaad dheeraad ah akhri maqaalka “INDEX MATCH oo leh shuruudo badan".

Celceliska, MAX iyo MIN ee INDEX iyo MATCH

Excel waxay leedahay hawlo gaar ah oo u gaar ah si loo helo celceliska, ugu badnaan, iyo ugu yaraan. Laakiin maxaa dhacaya haddii aad rabto inaad xogta ka hesho unugga la xidhiidha qiyamkaas? Kiiskan Celceliska, MAX iyo MIN waa in lala isticmaalo INDEX iyo MATCH.

INDEX MATCH iyo MAX

Si aad u heshid qiimaha ugu weyn ee tiirka D oo aad ku muujiso tiirka C, isticmaal qaacidada: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH iyo MIN

Si aad u heshid qiimaha ugu yar ee tiirka D oo aad ku muujiso tiirka C, isticmaal qaacidada soo socota:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SEARCH INDEX iyo Abeeso

Si aad u hesho celceliska qiimaha tiirka D oo ku muuji qiimahan C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Iyadoo ku xiran sida xogtaada loo qoro, doodda saddexaad ee MATCH() waa 1, 0, ama -1:

• haddii tiirarka loo kala soocaa sida ay u korayaan, deji 1 (ka dibna qaaciddada ayaa xisaabin doonta qiimaha ugu sarreeya, taas oo ka yar ama la mid ah qiimaha celceliska);
• haddii nooca ay hoos u dhacayso, ka dibna -1 (qaaciddu waxay soo saari doontaa qiimaha ugu yar ee ka weyn ama la mid ah celceliska);
• haddii shaxanka la raadiyo uu ka kooban yahay qiime la siman celceliska, ka dibna u dhig 0. 

 Tusaalahayaga, dadku waxay u kala soocaan siday u kala horreeyaan, markaa waxaan dhignaa -1. Natiijaduna waa Tokyo, maadaama qiimaha dadka (13,189) uu yahay kan ugu dhow celceliska qiimaha (000).

VLOOKUP() waxa kale oo ay samayn kartaa xisaabintan oo kale,laakin kaliya sida qaacido habayn ah: VLOOKUP oo leh AVERAGE,MIN iyo MAX.

INDEX MATCH iyo ESND/IFERROR

Waxay u badan tahay inaad hore u ogaatay in haddii qaacidadu aysan heli karin qiimaha la rabo, waxay tuuraysaa qalad # N / A. Waxaad ku bedeli kartaa fariinta qaladka caadiga ah wax macluumaad dheeraad ah. Tusaale ahaan, dhig doodda qaacidada XNUMX-kii:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Qaaciddadan, haddii aad geliso xogta aan shaxda ku jirin, foomku wuxuu ku siin doonaa farriinta la cayimay.

Haddii aad rabto inaad qabato dhammaan khaladaadka, markaa marka laga reebo XNUMX-kii waa la isticmaali karaa IFEROOR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0))), "Waxbaa khaldamay!")

Laakin xasuusnoow in khaladaadka loo qariyo habkan aysan ahayn fikrad wanaagsan, sababtoo ah khaladaadka caadiga ah waxay soo sheegaan xadgudubyada qaabka.

Waxaan rajeyneynaa inaad heshay hagahayaga adeegsiga INDEX MATCH() shaqada mid waxtar leh.