Kwa nini INDEX na MATCH ni bora kuliko VLOOKUP katika Excel

Hapo awali tumewaeleza wanaoanza jinsi ya kutumia vipengele vya msingi vya VLOOKUP (Kiingereza VLOOKUP, kifupisho kinasimama kwa "kitendaji cha kuangalia wima"). Na watumiaji wenye uzoefu walionyeshwa fomula kadhaa ngumu zaidi.

Na katika makala hii tutajaribu kutoa taarifa kuhusu njia nyingine ya kufanya kazi na utafutaji wa wima.

Unaweza kujiuliza: "Kwa nini hii ni muhimu?". Na hii ni muhimu ili kuonyesha njia zote za utafutaji zinazowezekana. Kwa kuongeza, vikwazo vingi vya VLOOKUP mara nyingi huzuia kupata matokeo unayotaka. Katika suala hili, INDEX( ) MATCH( ) inafanya kazi zaidi na tofauti, na pia wana vizuizi vichache.

Misingi INDEX MECHI

Kwa kuwa madhumuni ya mwongozo huu ni kuonyesha jinsi kipengele hiki kilivyo kizuri, sisi Hebu tuangalie maelezo ya msingi kuhusu kanuni za uendeshaji wake. Na tutaonyesha mifano, na pia fikiria kwa nini, ni bora kuliko VLOOKUP ().

Sintaksia na Matumizi ya INDEX

Chaguo hili la kukokotoa husaidia katika kutafuta thamani inayotakiwa kati ya maeneo maalum ya utafutaji kulingana na safu wima au nambari ya mstari. Syntax:

=INDEX(safu, nambari ya safu, nambari ya safu):

  • safu - eneo ambalo utafutaji utafanyika;
  • nambari ya mstari - nambari ya mstari wa kutafutwa katika safu maalum. Ikiwa nambari ya safu haijulikani, nambari ya safu lazima ibainishwe;
  • nambari ya safu - nambari ya safu inayopatikana katika safu maalum. Ikiwa thamani haijulikani, nambari ya mstari inahitajika.

Mfano wa formula rahisi:

=INDEX(A1:S10,2,3)

Chaguo za kukokotoa zitatafuta katika safu kutoka A1 hadi C10. Nambari zinaonyesha safu mlalo (2) na safu wima (3) ya kuonyesha thamani inayotakiwa kutoka. Matokeo yake yatakuwa kiini C2.

Rahisi sana, sawa? Lakini unapofanya kazi na hati halisi, hakuna uwezekano wa kuwa na habari kuhusu nambari za safu au seli. Hiyo ndiyo kazi ya MATCH() ni yake.

MATCH Kazi Syntax na Matumizi

Chaguo za kukokotoa MATCH() hutafuta thamani inayotakiwa na kuonyesha nambari yake ya takriban katika eneo lililobainishwa la utafutaji.

Syntax ya searchpos() inaonekana kama hii:

=MATCH(thamani ya kuangalia, safu ya kuangalia, aina ya mechi)

  • thamani ya utafutaji - nambari au maandishi ya kupatikana;
  • safu iliyotafutwa - eneo ambalo utafutaji utafanyika;
  • aina ya mechi - inabainisha kama itatafuta thamani halisi au maadili yaliyo karibu nayo:
    • 1 (au hakuna thamani iliyotajwa) - inarejesha thamani kubwa zaidi ambayo ni sawa na au chini ya thamani iliyotajwa;
    • 0 - inaonyesha inayolingana kabisa na thamani iliyotafutwa. Katika mchanganyiko INDEX() MATCH() utahitaji karibu kila wakati mechi halisi, kwa hivyo tunaandika 0;
    • -1 - Huonyesha thamani ndogo zaidi ambayo ni kubwa kuliko au sawa na thamani iliyobainishwa katika fomula. Upangaji unafanywa kwa mpangilio wa kushuka.

Kwa mfano, katika aina mbalimbali B1:B3 New York, Paris, London wamesajiliwa. Fomu iliyo hapa chini itaonyesha nambari 3 kwa sababu London ni ya tatu kwenye orodha:

=EXPOSE(London,B1:B3,0)

Jinsi ya kufanya kazi na kitendakazi cha INDEX MATCH 

Labda tayari umeanza kuelewa kanuni ambayo kazi ya pamoja ya kazi hizi imejengwa. Kwa kifupi, basi INDEX() hutafuta thamani inayotakiwa kati ya safu mlalo na safu wima maalum. Na MATCH() inaonyesha nambari za maadili haya:

=INDEX(safu wima ambayo thamani inarudishwa, MATCH(thamani ya kutafuta, safu wima ya kutafuta, 0))

Bado una wakati mgumu kuelewa jinsi inavyofanya kazi? Labda mfano utaelezea vizuri zaidi. Tuseme una orodha ya miji mikuu ya dunia na idadi ya watu:

Ili kujua saizi ya idadi ya watu wa mji mkuu fulani, kwa mfano, mji mkuu wa Japani, tunatumia fomula ifuatayo:

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

maelezo:

  • Chaguo za kukokotoa MATCH() hutafuta thamani - "Japani" katika safu A2:A10 na kurejesha nambari 3, kwa sababu Japani ni thamani ya tatu katika orodha. 
  • Takwimu hii inakwendanambari ya mstari” katika fomula ya INDEX() na huambia chaguo za kukokotoa kuchapisha thamani kutoka safu mlalo hii.

Kwa hivyo formula hapo juu inakuwa fomula ya kawaida INDEX(C2:C10,3). Fomula hutafuta kutoka seli C2 hadi C10 na kurudisha data kutoka kwa kisanduku cha tatu katika safu hii, yaani, C4, kwa sababu muda uliosalia huanza kutoka safu mlalo ya pili.

Hawataki kuagiza jina la jiji katika fomula? Kisha iandike kwenye seli yoyote, sema F1, na uitumie kama rejeleo katika MATCH() formula. Na unaishia na fomula ya utafutaji yenye nguvu:

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

Kwa nini INDEX na MATCH ni bora kuliko VLOOKUP katika Excel

Muhimu! Idadi ya mistari ndani safu INDEX() lazima iwe sawa na idadi ya safu mlalo ndani inazingatiwa safu katika MATCH(), vinginevyo utapata matokeo yasiyo sahihi.

Subiri kidogo, kwa nini usitumie tu fomula ya VLOOKUP()?

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

 Kuna umuhimu gani wa kupoteza muda kujaribu kubaini matatizo haya yote ya INDEX MATCH?

Katika kesi hii, haijalishi ni kazi gani ya kutumia. Huu ni mfano tu wa kuelewa jinsi kazi za INDEX() na MATCH() zinavyofanya kazi pamoja. Mifano mingine itaonyesha kazi hizi zinaweza kufanya nini katika hali ambapo VLOOKUP haina nguvu. 

INDEX MATCH au VLOOKUP

Wakati wa kuamua ni fomula gani ya utafutaji ya kutumia, wengi wanakubali kwamba INDEX() na MATCH() ni bora zaidi kuliko VLOOKUP. Hata hivyo, watu wengi bado wanatumia VLOOKUP(). Kwanza, VLOOKUP() ni rahisi zaidi, na pili, watumiaji hawaelewi kikamilifu faida zote za kufanya kazi na INDEX() na MATCH(). Bila ujuzi huu, hakuna mtu atakayekubali kutumia muda wao kusoma mfumo mgumu.

Hapa kuna faida kuu za INDEX() na MATCH() juu ya VLOOKUP():

 

  • Tafuta kutoka kulia kwenda kushoto. VLOOKUP() haiwezi kutafuta kutoka kulia kwenda kushoto, kwa hivyo thamani unazotafuta lazima ziwe katika safu wima za kushoto kabisa za jedwali. Lakini INDEX() na MATCH() wanaweza kushughulikia hili bila tatizo. Nakala hii itakuambia jinsi inavyoonekana katika mazoezi: jinsi ya kupata thamani inayotaka upande wa kushoto.

 

  1. Kuongeza au kuondolewa kwa safu kwa usalama. Fomula ya VLOOKUP() inaonyesha matokeo yasiyo sahihi wakati wa kuondoa au kuongeza safu wima kwa sababu VLOOKUP() inahitaji nambari kamili ya safu wima ili kufaulu. Kwa kawaida, wakati nguzo zinaongezwa au kuondolewa, nambari zao pia hubadilika. 

Na katika fomula za INDEX() na MATCH(), safu wima mbalimbali zimebainishwa, si safu wima mahususi. Kwa hivyo, unaweza kuongeza na kuondoa safu kwa usalama bila kusasisha fomula kila wakati.

  1. Hakuna kikomo kwa wingi wa utafutaji. Unapotumia VLOOKUP(), jumla ya idadi ya vigezo vya utafutaji lazima isizidi vibambo 255 au utapata #VALUE! Kwa hivyo ikiwa data yako ina idadi kubwa ya wahusika, INDEX() na MATCH() ndio chaguo bora zaidi.
  2. Kasi ya juu ya usindikaji. Ikiwa meza zako ni ndogo, basi hakuna uwezekano wa kugundua tofauti yoyote. Lakini, ikiwa jedwali lina mamia au maelfu ya safu mlalo, na, ipasavyo, kuna mamia na maelfu ya fomula, INDEX () na MATCH () zitakabiliana kwa kasi zaidi kuliko VLOOKUP (). Ukweli ni kwamba Excel itashughulikia tu safu wima zilizoainishwa kwenye fomula, badala ya kusindika meza nzima. 

Athari ya utendaji ya VLOOKUP() itaonekana hasa ikiwa lahakazi yako ina idadi kubwa ya fomula kama vile VLOOKUP() na SUM(). Ukaguzi tofauti wa vitendakazi vya VLOOKUP() unahitajika ili kuchanganua kila thamani katika safu. Kwa hivyo Excel inapaswa kusindika kiasi kikubwa cha habari, na hii inapunguza kazi kwa kiasi kikubwa.

Mifano ya Mfumo 

Tayari tumegundua manufaa ya kazi hizi, hivyo tunaweza kuendelea na sehemu ya kuvutia zaidi: matumizi ya ujuzi katika mazoezi.

Mfumo wa kutafuta kutoka kulia kwenda kushoto

Kama ilivyotajwa tayari, VLOOKUP haiwezi kutekeleza aina hii ya utafutaji. Kwa hivyo, ikiwa thamani zinazohitajika haziko kwenye safu wima ya kushoto kabisa, VLOOKUP() haitatoa matokeo. Chaguo za kukokotoa za INDEX() na MATCH() ni nyingi zaidi, na eneo la thamani halina jukumu kubwa kwao kufanya kazi.

Kwa mfano, tutaongeza safu wima kwenye upande wa kushoto wa jedwali letu na kujaribu kujua ni daraja gani kwa idadi ya watu mji mkuu wa Nchi Yetu unachukua.

Katika kisanduku G1, tunaandika thamani itakayopatikana, na kisha kutumia fomula ifuatayo kutafuta katika safu C1:C10 na kurudisha thamani inayolingana kutoka A2:A10:

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

Kwa nini INDEX na MATCH ni bora kuliko VLOOKUP katika Excel

Haraka. Ikiwa unapanga kutumia fomula hii kwa seli nyingi, hakikisha kuwa umerekebisha masafa kwa kutumia anwani kamili (kwa mfano, $A$2: $A$10 na $C$2:4C$10).

INDEX ZAIDI IMEFICHULIWA ZAIDI  kutafuta katika safu na safu

Katika mifano iliyo hapo juu, tumetumia chaguo za kukokotoa kama mbadala wa VLOOKUP() kurejesha thamani kutoka kwa safu mlalo zilizobainishwa awali. Lakini vipi ikiwa unahitaji kufanya utafutaji wa matrix au pande mbili?

Inaonekana kuwa ngumu, lakini fomula ya hesabu kama hizo ni sawa na INDEX() MATCH() ya kawaida, na tofauti moja tu: MATCH() fomula lazima itumike mara mbili. Mara ya kwanza kupata nambari ya safu, na mara ya pili kupata nambari ya safu:

=INDEX(safu, MATCH(thamani ya utafutaji wima, safu wima ya utafutaji, 0), MATCH(thamani ya utafutaji mlalo, safu mlalo ya utafutaji, 0))

Wacha tuangalie jedwali hapa chini na jaribu kutengeneza fomula INDEX() EXPRESS() EXPRESS() ili kuonyesha idadi ya watu katika nchi mahususi kwa mwaka uliochaguliwa.

Nchi inayolengwa iko katika kisanduku cha G1 (utafutaji wima) na mwaka unaolengwa uko katika kisanduku G2 (utafutaji mlalo). Formula itaonekana kama hii:

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

Kwa nini INDEX na MATCH ni bora kuliko VLOOKUP katika Excel

Jinsi fomula hii inavyofanya kazi

Kama ilivyo kwa fomula zingine ngumu, ni rahisi kuelewa kwa kuzigawanya katika milinganyo ya kibinafsi. Na kisha unaweza kuelewa kile kila kazi ya mtu binafsi hufanya:

  • MECHI(G1,A2:A11,0) - hutafuta thamani (G1) katika safu A2:A11 na inaonyesha nambari ya thamani hii, kwa upande wetu ni 2;
  • TAFUTA(G2,B1:D1,0) - hutafuta thamani (G2) katika masafa B1:D1. Katika kesi hii, matokeo yalikuwa 3.

Nambari za safu mlalo na safu zilizopatikana hutumwa kwa thamani inayolingana katika fomula INDEX():

=INDEX(B2:D11,2,3)

Kwa hivyo, tuna thamani iliyo katika kisanduku kwenye makutano ya safu mlalo 2 na safu wima 3 katika safu B2:D11. Na formula inaonyesha thamani inayotakiwa, ambayo iko kwenye seli D3.

Tafuta kwa hali nyingi ukitumia INDEX na MATCH

Ikiwa umesoma mwongozo wetu wa VLOOKUP(), labda umejaribu fomula nyingi za utafutaji. Lakini njia hii ya utafutaji ina kizuizi kimoja muhimu - haja ya kuongeza safu ya msaidizi.

Lakini habari njema ni kwamba Ukiwa na INDEX() na MATCH() unaweza kutafuta hali nyingi bila kuhitaji kuhariri au kubadilisha laha yako ya kazi.

Hapa kuna fomula ya jumla ya utaftaji wa hali nyingi ya INDEX() MATCH():

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

Ujumbe: fomula hii lazima itumike pamoja na njia ya mkato ya kibodi CTRL+SHIFT+ENTER.

Tuseme unahitaji kupata thamani unayotafuta kulingana na masharti 2: Mnunuzi и Bidhaa.

Hii inahitaji formula ifuatayo:

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

Katika fomula hii, C2:C10 ni safu ambayo utafutaji utafanyika, F1 - hali hii, A2:A10 - ni safu ya kulinganisha hali, F2 - hali ya 2, V2:V10 - anuwai kwa kulinganisha hali ya 2.

Usisahau kushinikiza mchanganyiko mwishoni mwa kazi na formula CTRL + SHIFT + INGIA - Excel itafunga kiotomatiki fomula na brashi za curly, kama inavyoonyeshwa kwenye mfano:

Kwa nini INDEX na MATCH ni bora kuliko VLOOKUP katika Excel

Ikiwa hutaki kutumia fomula ya safu kwa kazi yako, kisha ongeza INDEX() nyingine kwenye fomula na ubonyeze ENTER, itaonekana kama katika mfano:

Kwa nini INDEX na MATCH ni bora kuliko VLOOKUP katika Excel

Jinsi fomula hizi zinavyofanya kazi

Fomula hii inafanya kazi kwa njia sawa na fomula ya INDEX() MATCH() ya kawaida. Ili kutafuta hali nyingi, unaunda tu hali nyingi za Uongo na Kweli ambazo zinawakilisha hali sahihi na zisizo sahihi za mtu binafsi. Na kisha masharti haya yanatumika kwa vipengele vyote vinavyolingana vya safu. Fomula hubadilisha hoja za Uongo na Kweli kuwa 0 na 1, mtawalia, na kutoa safu ambapo 1 ni thamani zinazolingana ambazo zilipatikana kwenye mfuatano. MATCH() itapata thamani ya kwanza inayolingana na 1 na kuipitisha kwa INDEX() fomula. Nayo, kwa upande wake, itarudisha thamani inayotakiwa kwenye mstari ulioainishwa kutoka kwa safu inayotakiwa.

Fomula isiyo na safu inategemea uwezo wa INDEX() kuzishughulikia peke yake. INDEX() ya pili katika fomula inalingana na uwongo (0), kwa hivyo hupitisha safu nzima yenye thamani hizo hadi MATCH() fomula. 

Haya ni maelezo marefu ya mantiki nyuma ya fomula hii. Kwa habari zaidi soma makala “INDEX MECHI yenye masharti mengi'.

WASTANI, MAX na MIN katika INDEX na MATCH

Excel ina kazi zake maalum za kutafuta wastani, upeo na viwango vya chini. Lakini vipi ikiwa unataka kupata data kutoka kwa seli inayohusishwa na maadili hayo? Kwa kesi hii AVERAGE, MAX na MIN lazima zitumike pamoja na INDEX na MATCH.

INDEX MATCH na MAX

Ili kupata dhamana kubwa zaidi kwenye safu D na kuionyesha kwenye safu C, tumia fomula: 

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

INDEX MATCH na MIN

Ili kupata dhamana ndogo zaidi kwenye safu D na kuionyesha kwenye safu C, tumia fomula ifuatayo:

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

TAFUTA INDEX na SERPENT

Ili kupata thamani ya wastani katika safu D na kuonyesha thamani hii katika C:

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

Kulingana na jinsi data yako inavyoandikwa, hoja ya tatu kwa MATCH() ni 1, 0, au -1:

  • ikiwa nguzo zimepangwa kwa utaratibu wa kupanda, kuweka 1 (basi formula itahesabu thamani ya juu, ambayo ni chini ya au sawa na thamani ya wastani);
  • ikiwa aina inashuka, basi -1 (formula itatoa thamani ya chini ambayo ni kubwa kuliko au sawa na wastani);
  • ikiwa safu ya utafutaji ina thamani ambayo ni sawa kabisa na wastani, basi iweke kwa 0. 

 Katika mfano wetu, idadi ya watu imepangwa kwa utaratibu wa kushuka, kwa hiyo tunaweka -1. Na matokeo yake ni Tokyo, kwani thamani ya idadi ya watu (13,189) ndiyo iliyo karibu zaidi na thamani ya wastani (000).

Kwa nini INDEX na MATCH ni bora kuliko VLOOKUP katika Excel

VLOOKUP() pia inaweza kufanya hesabu kama hizo, lakini tu kama fomula ya safu: VLOOKUP yenye AVERAGE, MIN na MAX..

INDEX MATCH na ESND/IFERROR

Labda tayari umegundua kuwa ikiwa fomula haiwezi kupata thamani inayotaka, inatupa makosa # N / A. Unaweza kubadilisha ujumbe wa kawaida wa makosa na kitu cha kuelimisha zaidi. Kwa mfano, weka hoja katika fomula Katika XNUMX:

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

Kwa fomula hii, ikiwa utaingiza data ambayo haipo kwenye jedwali, fomu itakupa ujumbe maalum.

Kwa nini INDEX na MATCH ni bora kuliko VLOOKUP katika Excel

Ikiwa unataka kupata makosa yote, basi isipokuwa Katika XNUMX inaweza kutumika IFERRO:

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

Lakini kumbuka kwamba makosa ya masking kwa njia hii sio wazo nzuri, kwa sababu makosa ya kawaida yanaripoti ukiukaji katika fomula.

Tunatumahi umepata mwongozo wetu wa kutumia kazi ya INDEX MATCH() kuwa ya manufaa.

Acha Reply