Contents
A baya mun yi bayani ga masu farawa yadda ake amfani da ainihin ayyukan VLOOKUP (Turanci VLOOKUP, gajarta tana nufin “aikin duba tsaye”). Kuma an nuna ƙwararrun masu amfani da dabaru da yawa masu rikitarwa.
Kuma a cikin wannan labarin za mu yi ƙoƙarin ba da bayani game da wata hanyar aiki tare da bincike a tsaye.
Kuna iya yin mamaki: "Me yasa wannan ya zama dole?". Kuma wannan ya zama dole don nuna duk hanyoyin bincike mai yiwuwa. Bugu da ƙari, ƙuntatawa na VLOOKUP da yawa sukan hana samun sakamakon da ake so. Dangane da wannan, INDEX () MATCH () yana da ƙarin aiki da bambanta, kuma suna da ƙarancin ƙuntatawa.
Tushen INDEX MATCH
Tun da manufar wannan jagorar ita ce nuna yadda wannan fasalin yake da kyau, mu Bari mu dubi ainihin bayanin game da ka'idodin aikinsa. Kuma za mu nuna misalai, da kuma la'akari da dalilin da ya sa, shi ne mafi alhẽri daga VLOOKUP ().
Daidaita Ayyukan INDEX da Amfani
Wannan aikin yana taimakawa wajen gano ƙimar da ake so a cikin ƙayyadaddun wuraren bincike dangane da shafi ko lambar layi. Hadin rubutu:
=INDEX(jeri, lambar jere, lambar shafi):
- tsararru - yankin da za a gudanar da bincike;
- lambar layi - adadin layin da za a bincika a cikin ƙayyadaddun tsararru. Idan ba a san lambar layin ba, dole ne a ƙayyade lambar shafi;
- lambar shafi - adadin ginshiƙi da za a samu a cikin ƙayyadaddun tsararru. Idan ba a san ƙimar ba, ana buƙatar lambar layi.
Misalin dabara mai sauƙi:
=INDEX(A1:S10,2,3)
Aikin zai bincika cikin kewayo daga A1 zuwa C10. Lambobin suna nuna wane jere (2) da shafi (3) don nuna ƙimar da ake so daga. Sakamakon zai zama cell C2.
M sauki, dama? Amma lokacin da kuke aiki tare da takaddun gaske, da wuya ku sami bayanai game da lambobi ko sel. Wannan shine aikin MATCH() don.
Ayyukan MATCH da Amfani
Aikin MATCH() yana neman ƙimar da ake so kuma yana nuna kimanin adadinsa a cikin ƙayyadadden yankin bincike.
Rubutun searchpos() yayi kama da haka:
= MATCH (darajar dubawa, tsararru don dubawa, nau'in wasa)
- ƙimar bincike - lambar ko rubutun da za a samo;
- tsararrun bincike - yankin da za a gudanar da binciken;
- nau'in wasa - yana ƙayyade ko don neman ainihin ƙimar ko ƙimar mafi kusa da shi:
- 1 (ko babu ƙimar da aka ƙayyade) - ya dawo da ƙimar mafi girma wanda yayi daidai da ko ƙasa da ƙimar da aka ƙayyade;
- 0 - yana nuna daidai daidai da ƙimar da aka nema. A cikin haɗin INDEX() MATCH() kusan koyaushe kuna buƙatar daidaitaccen wasa, don haka muna rubuta 0;
- -1 - Yana Nuna mafi ƙanƙanta darajar da ta fi ko daidai da ƙimar da aka ƙayyade a cikin dabara. Ana aiwatar da rarrabuwa cikin tsari mai saukowa.
Misali, a cikin kewayon B1:B3 New York, Paris, London an yi rajista. Tsarin da ke ƙasa zai nuna lamba 3 saboda London ita ce ta uku a jerin:
= EXPOSE (London, B1: B3,0)
Yadda ake aiki tare da aikin INDEX MATCH
Wataƙila kun riga kun fara fahimtar ƙa'idar da aka gina aikin haɗin gwiwa na waɗannan ayyuka. A takaice, to INDEX() yana neman ƙimar da ake so tsakanin layuka da ginshiƙai da aka ƙayyade. Kuma MATCH() yana nuna lambobin waɗannan ƙimar:
=INDEX(ginin da aka dawo da kimar, MATCH (darajar bincike, shafi don bincika, 0))
Har yanzu kuna da wahalar fahimtar yadda yake aiki? Wataƙila misali zai bayyana mafi kyau. A ce kana da jerin manyan manyan duniya da yawansu:
Don gano girman yawan jama'ar wani babban birni, alal misali, babban birnin Japan, muna amfani da dabara mai zuwa:
=INDEX(C2:C10, MATCH (Japan, A2:A10,0))
Ƙarin bayani:
- Aikin MATCH () yana neman ƙimar - "Japan" a cikin tsararrun A2: A10 kuma ya dawo da lamba 3, saboda Japan ita ce ƙima ta uku a cikin jerin.
- Wannan adadi yana zuwalambar layi” a cikin dabarar INDEX() kuma yana gaya wa aikin don buga ƙima daga wannan layin.
Don haka dabarar da ke sama ta zama daidaitaccen tsari INDEX(C2:C10,3). Tsarin yana bincika daga sel C2 zuwa C10 kuma yana dawo da bayanai daga tantanin halitta na uku a cikin wannan kewayon, wato, C4, saboda ƙidayar tana farawa daga jere na biyu.
Ba ku son rubuta sunan birni a cikin dabarar? Sannan rubuta shi a kowace tantanin halitta, a ce F1, kuma a yi amfani da shi azaman tunani a cikin tsarin MATCH(). Kuma kun ƙare da tsarin bincike mai ƙarfi:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Muhimmin! Adadin layukan da ke ciki tsararru INDEX() dole ne ya zama daidai da adadin layuka a ciki dauke tsararru a cikin MATCH(), in ba haka ba za ku sami sakamako mara kyau.
Jira minti daya, me yasa ba kawai amfani da dabarar VLOOKUP() ba?
= VLOOKUP (F1, A2: C10, 3, Ƙarya)
Menene amfanin ɓata lokaci don gano duk waɗannan sarƙaƙƙiya na INDEX MATCH?
A wannan yanayin, ba kome ko wane aikin da za a yi amfani da shi ba. Wannan misali ne kawai don fahimtar yadda ayyukan INDEX() da MATCH() suke aiki tare. Wasu misalan za su nuna abin da waɗannan ayyuka ke da iko a cikin yanayin da VLOOKUP ba shi da ƙarfi.
INDEX MATCH ko VLOOKUP
Lokacin yanke shawarar wace dabarar neman amfani da ita, mutane da yawa sun yarda cewa INDEX() da MATCH() sun fi VLOOKUP fifiko. Koyaya, mutane da yawa har yanzu suna amfani da VLOOKUP(). Da fari dai, VLOOKUP () ya fi sauƙi, kuma na biyu, masu amfani ba su da cikakkiyar fahimtar duk fa'idodin aiki tare da INDEX () da MATCH (). Idan ba tare da wannan ilimin ba, ba wanda zai yarda su ciyar da lokacinsu don nazarin tsarin hadaddun.
Anan ga mahimman fa'idodin INDEX() da MATCH() akan VLOOKUP():
- Bincika daga dama zuwa hagu. VLOOKUP() ba zai iya bincika daga dama zuwa hagu ba, don haka ƙimar da kuke nema dole ne koyaushe su kasance a cikin ginshiƙan hagu na tebur. Amma INDEX() da MATCH() na iya ɗaukar wannan ba tare da matsala ba. Wannan labarin zai gaya muku abin da yake kama a aikace: yadda za a sami darajar da ake so a gefen hagu.
- Amintaccen ƙari ko cire ginshiƙai. Tsarin VLOOKUP() yana nuna sakamakon da ba daidai ba lokacin cirewa ko ƙara ginshiƙai saboda VLOOKUP() yana buƙatar ainihin lambar shafi don samun nasara. A zahiri, lokacin da aka ƙara ko cire ginshiƙai, lambobin su kuma suna canzawa.
Kuma a cikin tsarin INDEX() da MATCH(), an ƙayyade kewayon ginshiƙai, ba ginshiƙai ɗaya ba. Sakamakon haka, zaku iya ƙarawa da cire ginshiƙai cikin aminci ba tare da sabunta tsarin kowane lokaci ba.
- Babu iyaka akan adadin bincike. Lokacin amfani da VLOOKUP(), jimlar adadin ma'aunin bincike dole ne ya wuce haruffa 255 ko za ku sami #VALUE! Don haka idan bayananku sun ƙunshi babban adadin haruffa, INDEX() da MATCH() sune mafi kyawun zaɓi.
- Babban saurin sarrafawa. Idan tebur ɗinku ƙanƙanta ne, to da wuya ku ga wani bambanci. Amma, idan tebur ya ƙunshi daruruwan ko dubban layuka, kuma, bisa ga haka, akwai ɗaruruwan da dubunnan dabaru, INDEX () da MATCH () za su jimre da sauri fiye da VLOOKUP (). Gaskiyar ita ce, Excel zai aiwatar da ginshiƙan da aka ƙayyade a cikin dabarar, maimakon sarrafa duka tebur.
Tasirin aikin VLOOKUP() zai zama sananne musamman idan takardar aikinku ta ƙunshi ƙididdiga masu yawa kamar VLOOKUP() da SUM(). Ana buƙatar dubawa daban-daban na ayyukan VLOOKUP() don tantance kowace ƙima a cikin tsararru. Don haka Excel dole ne ya aiwatar da adadi mai yawa na bayanai, kuma wannan yana rage saurin aiki sosai.
Misalai Formula
Mun riga mun gano amfanin waɗannan ayyuka, don haka za mu iya ci gaba zuwa mafi ban sha'awa: aikace-aikacen ilimi a aikace.
Formula don bincika daga dama zuwa hagu
Kamar yadda aka ambata, VLOOKUP ba zai iya yin wannan nau'in bincike ba. Don haka, idan ƙimar da ake so ba a cikin ginshiƙi na hagu ba, VLOOKUP() ba zai haifar da sakamako ba. Ayyukan INDEX () da MATCH () sun fi dacewa, kuma wurin da ƙimar ba ta taka muhimmiyar rawa a gare su suyi aiki ba.
Alal misali, za mu ƙara ginshiƙi a gefen hagu na teburin mu kuma mu yi ƙoƙarin gano ko wane matsayi dangane da yawan jama'a babban birnin ƙasarmu ya mamaye.
A cikin tantanin halitta G1, muna rubuta ƙimar da za a samo, sannan mu yi amfani da dabarar da ke gaba don bincika cikin kewayon C1:C10 kuma mu dawo da madaidaicin ƙimar daga A2:A10:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Da sauri Idan kuna shirin amfani da wannan dabarar don sel da yawa, tabbatar kun gyara jeri ta amfani da cikakkiyar magana (misali, $2: $A $10 da $C$2: 4C$10).
LITTAFI MAI KYAU YA KARA BAYYANA don bincika cikin ginshiƙai da layuka
A cikin misalan da ke sama, mun yi amfani da waɗannan ayyuka azaman maye gurbin VLOOKUP() don dawo da ƙima daga kewayon layuka da aka riga aka ayyana. Amma menene idan kuna buƙatar yin matrix ko bincike mai gefe biyu?
Yana kama da rikitarwa, amma dabarar irin waɗannan ƙididdiga tana kama da daidaitaccen tsarin INDEX() MATCH(), tare da bambanci ɗaya kawai: dole ne a yi amfani da dabarar MATCH() sau biyu. Lokaci na farko don samun lambar jere, da kuma karo na biyu don samun lambar shafi:
=INDEX(tsararru, MATCH(ƙimar bincike a tsaye, shafi na bincike, 0), MATCH (ƙimar bincike a kwance, layin bincike, 0))
Bari mu kalli teburin da ke ƙasa kuma muyi ƙoƙarin yin dabara INDEX() EXPRESS() EXPRESS() don nuna kididdigar alƙaluma a wata ƙasa ta musamman na shekara da aka zaɓa.
Ƙasar da aka yi niyya tana cikin cell G1 (duba tsaye) kuma shekarar da aka yi niyya tana cikin cell G2 (duba a kwance). Tsarin tsari zai yi kama da haka:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Yadda wannan dabara ke aiki
Kamar yadda yake da sauran hadaddun dabaru, suna da sauƙin fahimta ta hanyar rarrabuwar su cikin daidaitattun daidaikun mutane. Sannan zaku iya fahimtar abin da kowane aikin mutum yake yi:
- MATSA (G1, A2: A11,0) - yana neman ƙima (G1) a cikin kewayon A2: A11 kuma yana nuna adadin wannan ƙimar, a cikin yanayin mu shine 2;
- BINCIKE(G2,B1:D1,0) - yana neman ƙima (G2) a cikin kewayon B1:D1. A wannan yanayin, sakamakon ya kasance 3.
Ana aika layin da aka samo da lambobin shafi zuwa madaidaicin ƙimar a cikin dabarar INDEX():
=INDEX(B2:D11,2,3)
Sakamakon haka, muna da ƙimar da ke cikin tantanin halitta a mahadar layuka 2 da ginshiƙai 3 a cikin kewayon B2:D11. Kuma tsarin yana nuna ƙimar da ake so, wanda ke cikin cell D3.
Bincika ta yanayi da yawa tare da INDEX da MATCH
Idan kun karanta jagorar mu zuwa VLOOKUP(), tabbas kun gwada dabarun bincike da yawa. Amma wannan hanyar bincike tana da ƙayyadaddun ƙayyadaddun ƙayyadaddun ƙayyadaddun ƙayyadaddun ƙayyadaddun ƙayyadaddun bayanai - buƙatar ƙara ginshiƙi mai taimako.
Amma labari mai dadi shine haka Tare da INDEX() da MATCH() zaku iya nemo sharuɗɗa da yawa ba tare da gyara ko canza takardar aikinku ba.
Anan ga cikakken tsarin bincike na yanayi da yawa na INDEX() MATCH():
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
Bayanan kula: dole ne a yi amfani da wannan dabarar tare da gajeriyar hanyar madannai CTRL+SHIFT+ENTER.
A ce kana buƙatar nemo darajar da kake nema bisa sharuɗɗa 2: Mai saye и Samfuri.
Wannan yana buƙatar tsari mai zuwa:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
A cikin wannan dabarar, C2: C10 shine kewayon da za a gudanar da bincike, F1 - wannan hali, A2: A10 — shine kewayon kwatanta yanayin, F2 - Sharadi na 2, V2:V10 - iyaka don kwatanta yanayin 2.
Kar ka manta da danna haɗin haɗin gwiwa a ƙarshen aikin tare da dabara CTRL+SHIFT+ENTER - Excel zai rufe dabara ta atomatik tare da takalmin gyaran kafa, kamar yadda aka nuna a cikin misali:
Idan ba kwa son yin amfani da dabarar tsararru don aikinku, to, ku ƙara wani INDEX() a cikin dabarar kuma danna ENTER, zai yi kama da misalin:
Yadda waɗannan hanyoyin ke aiki
Wannan dabarar tana aiki daidai da daidaitaccen tsarin INDEX() MATCH(). Don nemo sharuɗɗa da yawa, kawai kuna ƙirƙiri yanayi na Ƙarya da Gaskiya da yawa waɗanda ke wakiltar daidaitattun yanayin mutum ɗaya da kuskure. Sannan waɗannan sharuɗɗan sun shafi duk abubuwan da suka dace na tsararru. Tsarin yana jujjuya muhawarar arya da ta gaskiya zuwa 0 da 1, bi da bi, kuma tana fitar da tsararru inda 1 shine madaidaitan dabi'u waɗanda aka samo a cikin kirtani. MATCH() zai nemo kimar farko da ta yi daidai da 1 kuma ta wuce zuwa dabarar INDEX(). Kuma shi, bi da bi, zai dawo da ƙimar da aka riga aka so a cikin ƙayyadadden layin daga ginshiƙi da ake so.
Dabarar ba tare da tsararru ba ya dogara da ikon INDEX() don sarrafa su da kanta. INDEX na biyu() a cikin dabara ya yi daidai da karya (0), don haka ya wuce gaba dayan tsararru tare da waɗannan dabi'u zuwa dabarar MATCH().
Wannan wani dogon bayani ne na dabaru da ke tattare da wannan dabara. Don ƙarin bayani karanta labarin ”INDEX MATCH tare da yanayi da yawa".
MATAKI, MAX da MIN a cikin INDEX da MATCH
Excel yana da nasa ayyuka na musamman don nemo matsakaita, mafi girma, da mafi ƙanƙanta. Amma idan kuna son samun bayanai daga tantanin halitta mai alaƙa da waɗannan ƙimar fa? A wannan yanayin AVERAGE, MAX da MIN dole ne a yi amfani da su tare da INDEX da MATCH.
INDEX MATCH da MAX
Don nemo ƙima mafi girma a shafi na D kuma nuna shi a shafi na C, yi amfani da dabara:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
INDEX MATCH da MIN
Don nemo mafi ƙanƙanta ƙima a shafi na D kuma nuna shi a shafi na C, yi amfani da dabara mai zuwa:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
SEARCH INDEX da MACIJI
Don nemo matsakaicin ƙima a shafi na D kuma nuna wannan ƙimar a C:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
Dangane da yadda aka rubuta bayanan ku, hujja ta uku zuwa MATCH() ita ce 1, 0, ko -1:
- idan an jera ginshiƙai a cikin tsari mai hawa, saita 1 (sannan tsarin zai lissafta matsakaicin ƙimar, wanda bai kai ko daidai da matsakaicin ƙimar);
- idan nau'in yana saukowa, to -1 (tsarin zai fitar da mafi ƙarancin ƙimar da ta fi girma ko daidai da matsakaici);
- idan tsarin binciken ya ƙunshi ƙimar da ta yi daidai da matsakaita, sannan saita shi zuwa 0.
A cikin misalinmu, an jera yawan jama'a a cikin tsari mai saukowa, don haka muka sanya -1. Kuma sakamakon shine Tokyo, tunda ƙimar yawan jama'a (13,189) ita ce mafi kusa da matsakaicin ƙimar (000).
VLOOKUP() na iya yin irin waɗannan ƙididdiga, amma kawai azaman tsarin tsararru: VLOOKUP tare da AVERAGE, MIN da MAX.
INDEX MATCH da ESND/IFERROR
Wataƙila kun riga kun lura cewa idan tsarin ba zai iya samun ƙimar da ake so ba, yana jefa kuskure # N / A. Kuna iya maye gurbin daidaitaccen saƙon kuskure tare da wani abu mai ƙarin bayani. Misali, saita hujja a cikin dabara A cikin XNUMXth:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
Tare da wannan dabarar, idan kun shigar da bayanan da ba a cikin tebur ba, fom ɗin zai ba ku takamaiman saƙon.
Idan kana son kama duk kurakurai, to sai dai A cikin XNUMXth za a iya amfani IFEROR:
= IFERROR (INDEX (C2: C10, MATCH (F1, A2: A10,0)), "Wani abu ya faru!")
Amma ku tuna cewa rufe kurakurai ta wannan hanya ba kyakkyawan ra'ayi bane, saboda daidaitattun kurakurai suna ba da rahoton cin zarafi a cikin dabara.
Muna fatan kun sami jagorar mu don amfani da aikin INDEX MATCH() yana taimakawa.