Għaliex INDEX u MATCH huma aħjar minn VLOOKUP f'Excel

Preċedentement spjegajna lil dawk li jibdew kif tuża l-funzjonijiet bażiċi ta 'VLOOKUP (VLOOKUP bl-Ingliż, l-abbrevjazzjoni tirrappreżenta "funzjoni ta' tfittxija vertikali"). U utenti b'esperjenza ġew murija diversi formuli aktar ikkumplikati.

U f'dan l-artikolu nippruvaw nagħtu informazzjoni dwar metodu ieħor ta 'ħidma b'tfittxija vertikali.

Inti tista 'tistaqsi: "Għaliex dan huwa meħtieġ?". U dan huwa meħtieġ sabiex jintwerew il-metodi ta 'tfittxija kollha possibbli. Barra minn hekk, bosta restrizzjonijiet VLOOKUP ħafna drabi jipprevjenu l-kisba tar-riżultat mixtieq. F'dan ir-rigward, INDEX( ) MATCH( ) hija ħafna aktar funzjonali u diversa, u għandhom ukoll inqas restrizzjonijiet.

Basics TAQBIL TA' L-INDIĊI

Peress li l-għan ta 'din il-gwida huwa li turi kemm hi tajba din il-karatteristika, aħna Ejja nħarsu lejn l-informazzjoni bażika dwar il-prinċipji tat-tħaddim tagħha. U se nuru eżempji, u nikkunsidraw ukoll għaliex, huwa aħjar minn VLOOKUP ().

INDIĊI Funzjoni Sintassi u Użu

Din il-funzjoni tgħin biex jinstab il-valur mixtieq fost l-oqsma tat-tfittxija speċifikati bbażati fuq in-numru tal-kolonna jew tal-linja. Sintassi:

=INDIĊI (matriċi, numru tar-ringiela, numru tal-kolonna):

• array – iż-żona li fiha se ssir it-tfittxija;
• numru tal-linja – in-numru tal-linja li trid tiġi mfittxija fil-firxa speċifikata. Jekk in-numru tar-ringiela mhux magħruf, in-numru tal-kolonna għandu jiġi speċifikat;
• numru tal-kolonna – in-numru tal-kolonna li tinsab fil-firxa speċifikata. Jekk il-valur mhuwiex magħruf, huwa meħtieġ numru tal-linja.

Eżempju ta' formula sempliċi:

=INDIĊI(A1:S10,2,3)

Il-funzjoni se tfittex fil-medda minn A1 sa C10. In-numri juru minn liema ringiela (2) u kolonna (3) juru l-valur mixtieq. Ir-riżultat se jkun iċ-ċellula C2.

Pjuttost sempliċi, id-dritt? Imma meta taħdem b'dokumenti reali, mhux probabbli li jkollok informazzjoni dwar in-numri jew iċ-ċelloli tal-kolonni. Dak huwa l-funzjoni MATCH() għal.

MATCH Funzjoni Sintassi u Użu

Il-funzjoni MATCH() tfittex il-valur mixtieq u turi n-numru approssimattiv tagħha fiż-żona tat-tfittxija speċifikata.

Is-sintassi searchpos() tidher bħal din:

=TAQQAB (valur biex tfittex, firxa biex tfittex, tip taqbila)

• valur tat-tfittxija – in-numru jew it-test li għandu jinstab;
• searched array – iż-żona fejn se ssir it-tfittxija;
• tip ta’ taqbila – jispeċifika jekk tfittexx il-valur eżatt jew il-valuri l-eqreb tiegħu:
  • 1 (jew l-ebda valur speċifikat) – jirritorna l-akbar valur li huwa ugwali għal jew inqas mill-valur li kien speċifikat;
  • 0 – juri taqbila eżatta mal-valur imfittex. Fil-kombinazzjoni INDEX() MATCH() kważi dejjem ikollok bżonn taqbila eżatta, għalhekk niktbu 0;
  • -1 – Juri l-iżgħar valur li huwa akbar minn jew ugwali għall-valur speċifikat fil-formula. L-għażla ssir f'ordni dixxendenti.

Per eżempju, fil-medda B1:B3 New York, Pariġi, Londra huma rreġistrati. Il-formula hawn taħt se turi n-numru 3 għaliex Londra hija t-tielet fil-lista:

=ESPONI(Londra,B1:B3,0)

Kif taħdem mal-funzjoni INDEX MATCH 

Probabilment diġà bdejt tifhem il-prinċipju li bih jinbena x-xogħol konġunt ta 'dawn il-funzjonijiet. Insomma, mela INDEX() ifittex il-valur mixtieq fost ir-ringieli u l-kolonni speċifikati. U MATCH() turi n-numri ta’ dawn il-valuri:

=INDIĊI(kolonna li minnha jiġi rritornat il-valur, MATCH(valur biex tfittex, kolonna biex tfittex fiha, 0))

Għad ikollok diffiċli biex tifhem kif taħdem? Forsi eżempju jispjega aħjar. Ejja ngħidu li għandek lista ta’ kapitali dinjija u l-popolazzjoni tagħhom:

Sabiex issir taf id-daqs tal-popolazzjoni ta 'ċertu kapital, pereżempju, il-kapital tal-Ġappun, nużaw il-formula li ġejja:

=INDIĊI(C2:C10, MATCH(Ġappun, A2:A10,0))

Spjegazzjoni:

• Il-funzjoni MATCH() tfittex il-valur - "Ġappun" fil-firxa A2: A10 u tirritorna n-numru 3, minħabba li l-Ġappun huwa t-tielet valur fil-lista. 
• Din iċ-ċifra tmur għalnumru tal-linja” fil-formula INDIĊI () u jgħid lill-funzjoni biex tipprintja valur minn din ir-ringiela.

Allura l-formula ta 'hawn fuq issir il-formula standard INDIĊI(C2:C10,3). Il-formula tfittex miċ-ċelloli C2 sa C10 u tirritorna data mit-tielet ċellula f'din il-medda, jiġifieri, C4, minħabba li l-countdown jibda mit-tieni ringiela.

Ma tridx tippreskrivi l-isem tal-belt fil-formula? Imbagħad iktebha fi kwalunkwe ċellola, ngħidu aħna F1, u ​​użaha bħala referenza fil-formula MATCH(). U tispiċċa b'formula ta' tfittxija dinamika:

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

Importanti! Numru ta' linji fi firxa INDEX() għandu jkun l-istess għan-numru ta’ ringieli fih firxa meqjusa f'MATCH(), inkella se tikseb ir-riżultat ħażin.

Stenna ftit, għaliex ma tużax biss il-formula VLOOKUP()?

=TFITTXIJA(F1, A2:C10, 3, Falza)

 X'inhu l-punt li taħli l-ħin biex nippruvaw insemmu dawn il-kumplessitajiet kollha ta' INDEX MATCH?

F'dan il-każ, ma jimpurtax liema funzjoni tuża. Dan huwa biss eżempju biex tifhem kif il-funzjonijiet INDEX() u MATCH() jaħdmu flimkien. Eżempji oħra se juru x'inhuma dawn il-funzjonijiet huma kapaċi f'sitwazzjonijiet fejn il-VLOOKUP huwa bla saħħa. 

INDIĊI TAQBIL jew VLOOKUP

Meta jiddeċiedu liema formula ta’ tfittxija jużaw, ħafna jaqblu li INDEX() u MATCH() huma ferm superjuri għal VLOOKUP. Madankollu, ħafna nies għadhom jużaw VLOOKUP(). L-ewwelnett, VLOOKUP () hija aktar sempliċi, u t-tieni, l-utenti ma jifhmux bis-sħiħ il-vantaġġi kollha li jaħdmu ma 'INDEX () u MATCH (). Mingħajr dan l-għarfien, ħadd ma jaqbel li jqatta' l-ħin tiegħu jistudja sistema kumplessa.

Hawn huma l-vantaġġi ewlenin ta 'INDEX() u MATCH() fuq VLOOKUP():

• Fittex mil-lemin għax-xellug. VLOOKUP() ma tistax tfittex mil-lemin għax-xellug, għalhekk il-valuri li qed tfittex għandhom dejjem ikunu fil-kolonni l-aktar fuq ix-xellug tat-tabella. Iżda INDIĊI () u MATCH () jistgħu jimmaniġġjaw dan mingħajr problema. Dan l-artikolu jgħidlek kif jidher fil-prattika: kif issib il-valur mixtieq fuq in-naħa tax-xellug.

1. Żieda jew tneħħija sikura ta 'kolonni. Il-formula VLOOKUP() turi riżultati żbaljati meta tneħħi jew iżżid kolonni minħabba li VLOOKUP() jeħtieġ in-numru eżatt tal-kolonna biex tirnexxi. Naturalment, meta l-kolonni jiżdiedu jew jitneħħew, in-numri tagħhom jinbidel ukoll. 

U fil-formuli INDEX() u MATCH(), hija speċifikata firxa ta 'kolonni, mhux kolonni individwali. Bħala riżultat, tista 'żżid u tneħħi kolonni mingħajr periklu mingħajr ma jkollok taġġorna l-formula kull darba.

1. L-ebda limitu fuq il-volumi tat-tiftix. Meta tuża VLOOKUP(), in-numru totali ta' kriterji ta' tfittxija m'għandux jaqbeż il-255 karattru jew ikollok #VALUR! Mela jekk id-dejta tiegħek fiha numru kbir ta’ karattri, INDEX() u MATCH() huma l-aħjar għażla.
2. Veloċità għolja tal-ipproċessar. Jekk it-tabelli tiegħek huma relattivament żgħar, allura mhux probabbli li tinnota xi differenza. Iżda, jekk it-tabella fiha mijiet jew eluf ta 'ringieli, u, għaldaqstant, hemm mijiet u eluf ta' formuli, INDIĊI () u MATCH () se jlaħħqu ħafna aktar malajr minn VLOOKUP (). Il-fatt hu li Excel se jipproċessa biss il-kolonni speċifikati fil-formula, minflok ma jipproċessa t-tabella kollha. 

L-impatt fuq il-prestazzjoni ta 'VLOOKUP() se jkun notevoli b'mod speċjali jekk il-folja tax-xogħol tiegħek fiha numru kbir ta' formuli bħal VLOOKUP() u SUM(). Verifiki separati tal-funzjonijiet VLOOKUP() huma meħtieġa biex jiġu analizzati kull valur f'firxa. Allura Excel għandu jipproċessa ammont kbir ta 'informazzjoni, u dan inaqqas ix-xogħol b'mod sinifikanti.

Eżempji ta' Formula 

Aħna diġà dehret l-utilità ta 'dawn il-funzjonijiet, sabiex inkunu nistgħu ngħaddu għall-parti l-aktar interessanti: l-applikazzjoni tal-għarfien fil-prattika.

Formula biex tfittex mil-lemin għax-xellug

Kif diġà ssemma, VLOOKUP ma jistax iwettaq din il-forma ta 'tfittxija. Għalhekk, jekk il-valuri mixtieqa ma jkunux fil-kolonna l-aktar fuq ix-xellug, VLOOKUP() mhux se jipproduċi riżultat. Il-funzjonijiet INDEX() u MATCH() huma aktar versatili, u l-post tal-valuri ma għandux rwol kbir biex jaħdmu.

Pereżempju, se nżidu kolonna ta’ rank fuq in-naħa tax-xellug tat-tabella tagħna u nippruvaw insemmu liema rank f’termini ta’ popolazzjoni tokkupa l-kapitali ta’ Pajjiżna.

Fiċ-ċellula G1, niktbu l-valur li għandu jinstab, u mbagħad nużaw il-formula li ġejja biex tfittex fil-medda C1: C10 u rritorna l-valur korrispondenti minn A2: A10:

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

Pront. Jekk qed tippjana li tuża din il-formula għal ċelloli multipli, kun żgur li tiffissa l-firxiet billi tuża indirizzar assolut (per eżempju, $A$2: $A$10 u $C$2: 4C$10).

INDIĊI AKTAR ESPOSTA AKTAR ESPOSTA  biex tfittex fil-kolonni u ringieli

Fl-eżempji ta 'hawn fuq, użajna dawn il-funzjonijiet bħala sostitut għal VLOOKUP() biex jirritorna valuri minn firxa predefinita ta' ringieli. Imma x'jiġri jekk ikollok bżonn tagħmel matriċi jew tfittxija fuq żewġ naħat?

Jidher ikkumplikat, iżda l-formula għal kalkoli bħal dawn hija simili għall-formula standard INDEX() MATCH(), b'differenza waħda biss: il-formula MATCH() għandha tintuża darbtejn. L-ewwel darba biex tikseb in-numru tar-ringiela, u t-tieni darba biex tikseb in-numru tal-kolonna:

=INDIĊI(matriċi, MATCH(valur tat-tfittxija vertikali, kolonna tat-tfittxija, 0), MATCH(valur tat-tfittxija orizzontali, ringiela tat-tfittxija, 0))

Ejja nħarsu lejn it-tabella hawn taħt u nippruvaw nagħmlu formula INDIĊI() ESPRESSI() ESPRESSI() sabiex turi d-demografija f’pajjiż speċifiku għal sena magħżula.

Il-pajjiż fil-mira huwa fiċ-ċellula G1 (tfittxija vertikali) u s-sena fil-mira hija fiċ-ċellula G2 (tfittxija orizzontali). Il-formula se tidher bħal din:

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

Kif taħdem din il-formula

Bħal kull formula kumplessa oħra, huma aktar faċli biex jinftiehmu billi jinqasmu f'ekwazzjonijiet individwali. U allura tista' tifhem x'tagħmel kull funzjoni individwali:

• TQABBA(G1,A2:A11,0) – tfittex valur (G1) fil-medda A2:A11 u turi n-numru ta 'dan il-valur, fil-każ tagħna huwa 2;
• FITXEX(G2,B1:D1,0) – ifittex valur (G2) fil-medda B1:D1. F'dan il-każ, ir-riżultat kien 3.

In-numri ta' ringiela u kolonna misjuba jintbagħtu lill-valur korrispondenti fil-formula INDEX():

=INDIĊI (B2:D11,2,3)

Bħala riżultat, għandna valur li jinsab f'ċellula fl-intersezzjoni ta 'ringieli 2 u kolonni 3 fil-medda B2: D11. U l-formula turi l-valur mixtieq, li jinsab fiċ-ċellula D3.

Fittex minn kundizzjonijiet multipli bl-INDIĊI u MATCH

Jekk qrajt il-gwida tagħna għal VLOOKUP(), probabilment ippruvajt diversi formuli ta' tfittxija. Iżda dan il-metodu ta 'tfittxija għandu limitazzjoni sinifikanti waħda - il-ħtieġa li tiżdied kolonna awżiljarja.

Imma l-aħbar it-tajba hija dik B'INDEX() u MATCH() tista' tfittex għal kundizzjonijiet multipli mingħajr ma jkollok bżonn teditja jew tibdel il-worksheet tiegħek.

Hawnhekk hawn il-formula ġenerali ta' tfittxija b'ħafna kundizzjonijiet għal INDEX() MATCH():

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

In-nota: din il-formula għandha tintuża flimkien mal-keyboard shortcut CTRL+SHIFT+ENTER.

Ejja ngħidu li għandek bżonn issib il-valur li qed tfittex ibbażat fuq 2 kundizzjonijiet: Xerrej и Prodott.

Dan jeħtieġ il-formula li ġejja:

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

F'din il-formula, C2:C10 hija l-firxa li fiha se ssir it-tfittxija, F1 – din il-kundizzjoni, A2:A10 — hija l-firxa biex titqabbel il-kundizzjoni, F2 – kundizzjoni 2, V2:V10 – firxa għat-tqabbil tal-kundizzjoni 2.

Tinsiex li tagħfas il-kombinazzjoni fl-aħħar tax-xogħol bil-formula CTRL + SHIFT + DĦUL – Excel awtomatikament jagħlaq il-formula bi ċineg kaboċċi, kif muri fl-eżempju:

Jekk ma tridx tuża formula array għax-xogħol tiegħek, imbagħad żid INDIĊI () ieħor mal-formula u agħfas ENTER, se tidher bħal fl-eżempju:

Kif jaħdmu dawn il-formuli

Din il-formula taħdem bl-istess mod bħall-formula standard INDEX() MATCH(). Biex tfittex kundizzjonijiet multipli, sempliċement toħloq kundizzjonijiet multipli Foloz u Veri li jirrappreżentaw il-kundizzjonijiet individwali korretti u mhux korretti. U mbagħad dawn il-kundizzjonijiet japplikaw għall-elementi kollha korrispondenti tal-firxa. Il-formula tikkonverti l-argumenti Foloz u Veru għal 0 u 1, rispettivament, u toħroġ firxa fejn 1 huwa l-valuri li jaqblu li nstabu fis-sekwenza. MATCH() isib l-ewwel valur li jaqbel ma' 1 u jgħaddih għall-formula INDEX(). U, min-naħa tiegħu, se jirritorna l-valur diġà mixtieq fil-linja speċifikata mill-kolonna mixtieqa.

Formula mingħajr firxa tiddependi fuq il-kapaċità ta 'INDEX() li jimmaniġġjahom waħdu. It-tieni INDIĊI () fil-formula jaqbel falz (0), għalhekk jgħaddi l-firxa kollha b'dawk il-valuri għall-formula MATCH(). 

Din hija spjegazzjoni pjuttost twila tal-loġika wara din il-formula. Għal aktar informazzjoni aqra l-artiklu “INDIĊI TAQBIL b'ħafna kundizzjonijiet".

MEDJA, MAX u MIN fl-INDIĊI u MATCH

Excel għandu l-funzjonijiet speċjali tiegħu stess biex isib medji, massimi u minimi. Imma x'jiġri jekk trid tikseb data miċ-ċellula assoċjata ma 'dawk il-valuri? F'dan il-każ MEDJA, MAX u MIN għandhom jintużaw flimkien ma 'INDIĊI u MATCH.

INDIĊI MATCH u MAX

Biex issib l-akbar valur fil-kolonna D u turih fil-kolonna Ċ, uża l-formula: 

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

INDIĊI MATCH u MIN

Biex issib l-iżgħar valur fil-kolonna D u turih fil-kolonna Ċ, uża l-formula li ġejja:

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

FITXEX INDIĊI u SERPENT

Biex issib il-valur medju fil-kolonna D u turi dan il-valur f'Ċ:

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

Skont kif tinkiteb id-dejta tiegħek, it-tielet argument għal MATCH() huwa jew 1, 0, jew -1:

• jekk il-kolonni huma magħżula f'ordni axxendenti, issettja 1 (imbagħad il-formula tikkalkula l-valur massimu, li huwa inqas minn jew ugwali għall-valur medju);
• jekk it-tip huwa dixxendenti, allura -1 (il-formula se toħroġ il-valur minimu li huwa akbar minn jew ugwali għall-medja);
• jekk il-firxa ta' tfittxija fiha valur li huwa eżattament ugwali għall-medja, imbagħad issettjah għal 0. 

 Fl-eżempju tagħna, il-popolazzjoni hija magħżula f'ordni dixxendenti, għalhekk inpoġġu -1. U r-riżultat huwa Tokyo, peress li l-valur tal-popolazzjoni (13,189) huwa l-eqreb għall-valur medju (000).

VLOOKUP() jista' wkoll iwettaq kalkoli bħal dawn, iżda biss bħala formula ta' firxa: VLOOKUP b'MEJJA, MIN u MAX.

INDIĊI MATCH u ESND/IFERROR

Probabilment diġà ndunajt li jekk il-formula ma tistax issib il-valur mixtieq, tarmi żball # N / A. Tista 'tissostitwixxi l-messaġġ ta' żball standard b'xi ħaġa aktar informattiva. Per eżempju, issettja l-argument fil-formula Fis-XNUMX:

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

B'din il-formula, jekk iddaħħal data li mhix fit-tabella, il-formola tagħtik il-messaġġ speċifikat.

Jekk trid taqbad l-iżbalji kollha, allura ħlief għal Fis-XNUMX jistgħu jintużaw JEKK Żball:

=JEKKERROR(INDIĊI(C2:C10,TAQBILB(F1,A2:A10,0)), "Xi ħaġa marret ħażin!")

Imma ftakar li l-iżbalji tal-maskra b'dan il-mod mhix idea tajba, minħabba li l-iżbalji standard jirrappurtaw ksur fil-formula.

Nittamaw li sibt il-gwida tagħna biex tuża l-funzjoni INDEX MATCH() ta’ għajnuna.