Chifukwa chiyani INDEX ndi MATCH zili bwino kuposa VLOOKUP mu Excel

Tidafotokozeranso kwa oyamba kumene momwe angagwiritsire ntchito ntchito zoyambira za VLOOKUP (Chingerezi VLOOKUP, chidulecho chimayimira "ntchito yoyang'ana molunjika"). Ndipo ogwiritsa ntchito odziwa adawonetsedwa njira zingapo zovuta.

Ndipo m'nkhaniyi tiyesera kupereka zambiri za njira ina yogwirira ntchito ndi kufufuza koyang'ana.

Mutha kudzifunsa kuti: "Chifukwa chiyani izi ndizofunikira?". Ndipo izi ndizofunikira kuti muwonetse njira zonse zofufuzira zomwe zingatheke. Kuphatikiza apo, zoletsa zambiri za VLOOKUP nthawi zambiri zimalepheretsa kupeza zotsatira zomwe mukufuna. Pachifukwa ichi, INDEX ( ) MATCH ( ) ndi yogwira ntchito kwambiri komanso yosiyana siyana, ndipo ali ndi zoletsa zochepa.

Zoyambira INDEX MATCH

Popeza cholinga cha bukhuli ndikuwonetsa momwe mbaliyi ilili yabwino, ife Tiyeni tione mfundo zofunika zokhudza mfundo za ntchito yake. Ndipo tiwonetsa zitsanzo, ndikuganiziranso chifukwa chake, ili bwino kuposa VLOOKUP ().

INDEX Function Syntax ndi Kugwiritsa Ntchito

Ntchitoyi imathandizira kupeza mtengo womwe ukufunidwa pakati pa malo ofufuzira osankhidwa malinga ndi gawo kapena nambala ya mzere. Syntax:

=INDEX(gulu, nambala ya mzere, nambala yagawo):

• gulu - malo omwe kufufuza kudzachitikira;
• nambala ya mzere - nambala ya mzere womwe uyenera kufufuzidwa pamndandanda womwe watchulidwa. Ngati nambala ya mzere sichidziwika, nambala ya mzere iyenera kufotokozedwa;
• nambala yagawo - nambala ya gawo lomwe lingapezeke pamndandanda womwe watchulidwa. Ngati mtengo sudziwika, nambala ya mzere ikufunika.

Chitsanzo cha njira yosavuta:

= INDEX(A1:S10,2,3)

Ntchitoyi idzafufuza mumtundu wa A1 mpaka C10. Manambalawa akuwonetsa mzere (2) ndi gawo (3) kuti awonetse mtengo womwe mukufuna. Zotsatira zake zidzakhala cell C2.

Zosavuta, chabwino? Koma mukamagwira ntchito ndi zolemba zenizeni, simungakhale ndi chidziwitso chokhudza manambala amzati kapena ma cell. Ndizomwe ntchito ya MATCH() ndi yake.

MATCH Function Syntax ndi Kugwiritsa Ntchito

Ntchito ya MATCH() imasaka mtengo womwe ukufunidwa ndikuwonetsa pafupifupi nambala yake pamalo osaka omwe atchulidwa.

Kusaka () syntax ikuwoneka motere:

=KUGWIRITSA NTCHITO(mtengo woti muyang'ane, gulu loyang'ana, mtundu wa machesi)

• kusaka - nambala kapena mawu omwe angapezeke;
• kufufuza - malo omwe kufufuza kudzachitikira;
• mtundu wa machesi - umatanthawuza ngati muyang'ane mtengo weniweni kapena zomwe zili pafupi nazo:
  • 1 (kapena palibe mtengo wotchulidwa) - imabweretsa mtengo waukulu kwambiri womwe uli wofanana kapena wocheperapo kuposa mtengo womwe watchulidwa;
  • 0 - ikuwonetsa kufanana kwenikweni ndi mtengo womwe wafufuzidwa. Kuphatikiza INDEX() MATCH() nthawi zonse mudzafunika kufanana kwenikweni, kotero timalemba 0;
  • -1 - Imawonetsa mtengo wocheperako womwe uli waukulu kuposa kapena wofanana ndi mtengo womwe wafotokozedwa mu fomula. Kusanja kumachitika mwadongosolo lotsika.

Mwachitsanzo, mu B1:B3 New York, Paris, London amalembedwa. Njira yomwe ili pansipa iwonetsa nambala 3 chifukwa London ndi yachitatu pamndandanda:

=EXPOSE(London,B1:B3,0)

Momwe mungagwiritsire ntchito ndi INDEX MATCH ntchito 

Mwinamwake mwayamba kale kumvetsetsa mfundo yomwe ntchito yogwirizanitsa ntchitozi imamangidwa. Mwachidule, ndiye INDEX() amafufuza mtengo womwe ukufunidwa pakati pa mizere ndi mizere yotchulidwa. Ndipo MATCH() ikuwonetsa manambala azinthu izi:

=INDEX(ndime yomwe mtengowo wabwezedwa, MATCH(mtengo wofufuzidwa, gawo loti mufufuze, 0))

Mukuvutikabe kumvetsetsa momwe zimagwirira ntchito? Mwinamwake chitsanzo chidzalongosola bwino. Tiyerekeze kuti muli ndi mndandanda wa malikulu apadziko lonse lapansi ndi kuchuluka kwawo:

Kuti tidziwe kukula kwa anthu a likulu linalake, mwachitsanzo, likulu la Japan, timagwiritsa ntchito njira iyi:

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

Kufotokozera:

• Ntchito ya MATCH () imayang'ana mtengo - "Japan" mu gulu la A2: A10 ndikubwezeretsa nambala 3, chifukwa Japan ndiye mtengo wachitatu pamndandanda. 
• Chiwerengerochi chimapitanambala ya line” mu INDEX() fomula ndikuwuza ntchitoyi kuti isindikize mtengo kuchokera pamzerewu.

Kotero ndondomeko yomwe ili pamwambayi imakhala yokhazikika INDEX(C2:C10,3). Fomula imasaka kuchokera ku ma cell C2 kupita ku C10 ndikubweza deta kuchokera ku selo lachitatu pamndandanda uwu, ndiye C4, chifukwa kuwerengera kumayambira pamzere wachiwiri.

Sindikufuna kulemba dzina la mzinda mu chilinganizo? Kenako lembani m'selo iliyonse, nenani F1, ndikugwiritseni ntchito ngati chilankhulo cha MATCH() formula. Ndipo mudzakhala ndi njira yosakira yamphamvu:

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

Zofunika! Chiwerengero cha mizere mkati zolemba INDEX() iyenera kukhala yofanana ndi kuchuluka kwa mizere mkati kuganiziridwa ngati array mu MATCH(), apo ayi mudzapeza zotsatira zolakwika.

Dikirani pang'ono, bwanji osangogwiritsa ntchito fomula ya VLOOKUP()?

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

 Kutaya nthawi ndi chiyani kuyesa kudziwa zovuta zonse za INDEX MATCH?

Pamenepa, zilibe kanthu ntchito ntchito. Ichi ndi chitsanzo chabe kuti mumvetsetse momwe ntchito za INDEX() ndi MATCH() zimagwirira ntchito limodzi. Zitsanzo zina ziwonetsa zomwe izi zimatha kuchita pomwe VLOOKUP ilibe mphamvu. 

INDEX MATCH kapena VLOOKUP

Posankha njira yofufuzira yoti agwiritse ntchito, ambiri amavomereza kuti INDEX() ndi MATCH() ndi apamwamba kwambiri kuposa VLOOKUP. Komabe, anthu ambiri amagwiritsabe ntchito VLOOKUP (). Choyamba, VLOOKUP() ndiyosavuta, ndipo kachiwiri, ogwiritsa ntchito samamvetsetsa bwino zonse zaubwino wogwira ntchito ndi INDEX() ndi MATCH(). Popanda chidziwitso ichi, palibe amene angavomereze kugwiritsa ntchito nthawi yawo kuphunzira dongosolo lovuta.

Nawa maubwino ofunikira a INDEX() ndi MATCH() pa VLOOKUP():

• Sakani kuchokera kumanja kupita kumanzere. VLOOKUP () silingafufuze kuchokera kumanja kupita kumanzere, kotero kuti zomwe mukuyang'ana ziyenera kukhala kumanzere kwa tebulo. Koma INDEX() ndi MATCH() amatha kuthana ndi izi popanda vuto. Nkhaniyi ikuuzani momwe zikuwonekera pochita: momwe mungapezere mtengo womwe mukufuna kumanzere.

1. Kuwonjezera kotetezedwa kapena kuchotsa mizati. Fomula ya VLOOKUP() imawonetsa zotsatira zolakwika pochotsa kapena kuwonjezera mizati chifukwa VLOOKUP() imafunika nambala yeniyeni kuti ikhale yopambana. Mwachibadwa, mizati ikawonjezeredwa kapena kuchotsedwa, manambala awo amasinthanso. 

Ndipo mu INDEX() ndi MATCH() formulas, mitundu ingapo yatchulidwa, osati magawo amodzi. Zotsatira zake, mutha kuwonjezera ndi kuchotsa zipilala mosasintha nthawi zonse.

1. Palibe malire pama voliyumu osakira. Mukamagwiritsa ntchito VLOOKUP(), chiwerengero chonse cha zofufuzira sichiyenera kupitirira zilembo 255 kapena mupeza #VALUE! Chifukwa chake ngati deta yanu ili ndi zilembo zambiri, INDEX() ndi MATCH() ndiye njira yabwino kwambiri.
2. Mkulu processing liwiro. Ngati matebulo anu ndi ochepa, ndiye kuti simungazindikire kusiyana kulikonse. Koma, ngati tebulo lili ndi mizere mazana kapena masauzande, ndipo, motere, pali mazana ndi masauzande a ma formula, INDEX () ndi MATCH () athana mwachangu kuposa VLOOKUP (). Chowonadi n'chakuti Excel idzakonza mizati yokhayo yomwe ili mu fomula, m'malo mokonza tebulo lonse. 

Mphamvu ya VLOOKUP() idzawoneka makamaka ngati tsamba lanu lantchito lili ndi ma formula ambiri monga VLOOKUP() ndi SUM(). Macheke osiyana a ntchito za VLOOKUP() akufunika kuti muwunikire mtengo uliwonse pamndandanda. Chifukwa chake Excel iyenera kukonza zidziwitso zambiri, ndipo izi zimachepetsa ntchitoyo kwambiri.

Zitsanzo za Fomula 

Tazindikira kale kufunika kwa ntchitozi, kotero titha kupita ku gawo losangalatsa kwambiri: kugwiritsa ntchito chidziwitso pochita.

Fomula yosaka kuchokera kumanja kupita kumanzere

Monga tanenera kale, VLOOKUP siyingafufuze mwanjira iyi. Chifukwa chake, ngati zomwe mukufuna sizili kumanzere, VLOOKUP () sizitulutsa zotsatira. Ntchito za INDEX () ndi MATCH () zimasinthasintha, ndipo malo omwe amafunikira sakhala ndi gawo lalikulu kuti agwire ntchito.

Mwachitsanzo, tiwonjezera gawo kumanzere kwa tebulo lathu ndikuyesera kudziwa kuti likulu la Dziko Lathu limakhala liti malinga ndi kuchuluka kwa anthu.

Mu selo G1, timalemba mtengo womwe ungapezeke, kenako timagwiritsa ntchito njira yotsatirayi kuti tifufuze mumtundu C1:C10 ndikubweza mtengo womwewo kuchokera ku A2:A10:

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

Mwachangu. Ngati mukufuna kugwiritsa ntchito fomulayi pamaselo angapo, onetsetsani kuti mwakonza magawowo pogwiritsa ntchito maadiresi athunthu (mwachitsanzo, $A$2: $A$10 ndi $C$2:4C$10).

INDEX ZAMBIRI ZAVUTIKA ZAMBIRI  kufufuza m'mizere ndi mizere

M'zitsanzo zomwe zili pamwambazi, tagwiritsa ntchito izi m'malo mwa VLOOKUP() kubweza ma values ​​​​kuchokera pamizere yodziwikiratu. Koma bwanji ngati mukufuna kusaka matrix kapena mbali ziwiri?

Zikumveka zovuta, koma njira yowerengera ngati imeneyi ndi yofanana ndi INDEX() MATCH() formula, ndi kusiyana kumodzi kokha: MATCH () fomula iyenera kugwiritsidwa ntchito kawiri. Nthawi yoyamba kuti mupeze nambala ya mzere, ndipo kachiwiri kuti mupeze nambala yamzere:

=INDEX(gulu, MATCH(mtengo wofufuzira molunjika, gawo losakira, 0), MATCH(mtengo wosakanikira wopingasa, mzere wosakira, 0))

Tiyeni tiwone tebulo ili m'munsimu ndikuyesera kupanga chilinganizo INDEX() EXPRESS() EXPRESS() kuti awonetse kuchuluka kwa anthu m'dziko linalake kwa chaka chosankhidwa.

Dziko lomwe likuyembekezeredwa lili mu cell G1 (kuyang'ana molunjika) ndipo chaka chandamale chili mu selo G2 (kuyang'ana kopingasa). Fomula idzawoneka motere:

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

Momwe fomula iyi imagwirira ntchito

Monga momwe zilili ndi njira zina zovuta, ndizosavuta kuzimvetsetsa pozigawa m'ma equation amtundu uliwonse. Kenako mutha kumvetsetsa zomwe ntchito iliyonse imachita:

• MATCH(G1,A2:A11,0) - imayang'ana mtengo (G1) mumtundu wa A2: A11 ndikuwonetsa chiwerengero cha mtengo uwu, kwa ife ndi 2;
• FUFUZANI(G2,B1:D1,0) - imayang'ana mtengo (G2) mumtundu B1:D1. Pankhaniyi, zotsatira zake zinali 3.

Mzere wopezeka ndi manambala amizere amatumizidwa ku mtengo wofananira mu INDEX() formula:

=INDEX(B2:D11,2,3)

Zotsatira zake, tili ndi mtengo womwe uli muselo pamzere wa mizere iwiri ndi mizere itatu mugulu B2:D3. Ndipo chilinganizocho chikuwonetsa mtengo womwe mukufuna, womwe uli mu cell D2.

Sakani ndi zinthu zingapo ndi INDEX ndi MATCH

Ngati mudawerenga kalozera wathu ku VLOOKUP(), mwina mwayesa njira zingapo zosakira. Koma njira yofufuzira iyi ili ndi malire amodzi - kufunikira kowonjezera gawo lothandizira.

Koma uthenga wabwino ndi umenewo Ndi INDEX() ndi MATCH() mutha kusaka zinthu zingapo popanda kusintha kapena kusintha tsamba lanu.

Nayi njira yosakira yamitundu yambiri ya INDEX() MATCH():

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

Chidziwitso: fomula iyi iyenera kugwiritsidwa ntchito limodzi ndi njira yachidule ya kiyibodi CTRL+SHIFT+ENTER.

Tiyerekeze kuti muyenera kupeza mtengo womwe mukuufuna potengera mikhalidwe iwiri: wogula и Zogulitsa.

Izi zimafuna njira iyi:

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

Munjira iyi, C2:C10 ndiye mtundu womwe kusaka kudzachitikira, F1 - chikhalidwe ichi, A2:A10 ndiye mtundu wofananiza mkhalidwewo, F2 - chikhalidwe 2, ndi 2:v10 - osiyanasiyana kufananiza chikhalidwe 2.

Musaiwale kukanikiza kuphatikiza kumapeto kwa ntchitoyo ndi chilinganizo CTRL+SHIFT+ENTER - Excel imangotseka chilinganizocho ndi ma curly braces, monga momwe tawonetsera mu chitsanzo:

Ngati simukufuna kugwiritsa ntchito fomula yophatikizika pantchito yanu, yonjezerani INDEX() ina ku fomulayo ndikudina ENTER, izi ziwoneka ngati mu chitsanzo:

Momwe mafomuwa amagwirira ntchito

Fomula iyi imagwira ntchito mofanana ndi INDEX() MATCH() formula. Kuti mufufuze zinthu zingapo, mumangopanga zinthu zingapo zabodza ndi Zowona zomwe zikuyimira zolondola komanso zolakwika zamunthu. Kenako zinthu izi zimagwiranso ntchito pazinthu zonse zofananira za gululo. Fomula imatembenuza mfundo zabodza ndi zowona kukhala 0 ndi 1, motsatana, ndikutulutsa mndandanda pomwe 1 ndi milingo yofananira yomwe idapezeka mu chingwe. MATCH() apeza mtengo woyamba wofanana ndi 1 ndikuupereka ku INDEX() formula. Ndipo izo, nazonso, zidzabweza mtengo womwe ukufunidwa kale pamzere wotchulidwa kuchokera pamndandanda womwe mukufuna.

Fomu yopanda mndandanda imadalira kuthekera kwa INDEX() kuti igwire yokha. Yachiwiri INDEX() mu fomula imafanana ndi zabodza (0), kotero imadutsa gulu lonse ndi zikhalidwezo ku MATCH() formula. 

Uku ndi kufotokoza kwanthawi yayitali pamalingaliro amtundu uwu. Kuti mudziwe zambiri werengani nkhani yakuti “INDEX MATCH yokhala ndi zinthu zingapo".

AVERAGE, MAX ndi MIN mu INDEX ndi MATCH

Excel ili ndi ntchito zake zapadera zopezera ma avareji, ma maximums, ndi minimals. Koma bwanji ngati mukufuna kupeza deta kuchokera ku selo yokhudzana ndi makhalidwe amenewo? Pamenepa AVERAGE, MAX ndi MIN ayenera kugwiritsidwa ntchito molumikizana ndi INDEX ndi MATCH.

INDEX MATCH ndi MAX

Kuti mupeze mtengo waukulu kwambiri pagawo la D ndikuchiwonetsa pagawo C, gwiritsani ntchito fomula: 

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

INDEX MATCH ndi MIN

Kuti mupeze phindu laling'ono kwambiri pagawo la D ndikuliwonetsa pagawo C, gwiritsani ntchito njira iyi:

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

SEARCH INDEX ndi SERPENT

Kuti mupeze mtengo wapakati pagawo D ndikuwonetsa mtengo uwu mu C:

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

Kutengera momwe deta yanu idalembedwera, mtsutso wachitatu ku MATCH() mwina ndi 1, 0, kapena -1:

• ngati mizati yasankhidwa mwadongosolo lokwera, ikani 1 (ndiye ndondomekoyi idzawerengera mtengo wapamwamba, womwe ndi wocheperapo kapena wofanana ndi mtengo wapakati);
• ngati mtunduwo ukutsika, ndiye -1 (chilinganizo chidzatulutsa mtengo wocheperako womwe uli waukulu kapena wofanana ndi pafupifupi);
• ngati gulu loyang'ana lili ndi mtengo womwe uli wofanana ndendende ndi avareji, ndiye ikani ku 0. 

 Mu chitsanzo chathu, chiwerengero cha anthu chimasanjidwa motsika, kotero timayika -1. Ndipo zotsatira zake ndi Tokyo, popeza kuchuluka kwa anthu (13,189) ndikoyandikira kwambiri pamtengo wapakati (000).

VLOOKUP () imathanso kuwerengera motere, koma ngati njira yophatikizira: VLOOKUP yokhala ndi AVERAGE, MIN ndi MAX..

INDEX MATCH ndi ESND/IFERROR

Mwinamwake mwazindikira kale kuti ngati fomula silingathe kupeza mtengo womwe mukufuna, imabweretsa cholakwika # N / A. Mutha kusintha uthenga wolakwika wamba ndi zina zambiri. Mwachitsanzo, ikani mkangano mu fomula Mu XNUMXth:

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

Ndi fomula iyi, ngati mulowetsa zomwe sizili patebulo, fomuyo ikupatsani uthenga womwe watchulidwa.

Ngati mukufuna kugwira zolakwika zonse, kupatulapo Mu XNUMXth angagwiritsidwe ntchito IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "China chake chalakwika!")

Koma kumbukirani kuti zolakwitsa za masking mwanjira iyi sibwino, chifukwa zolakwika zomwe zachitika zimawonetsa kuphwanya mu chilinganizo.

Tikukhulupirira kuti mwapeza kalozera wathu wogwiritsa ntchito INDEX MATCH() ntchito yothandiza.