Aisea e sili atu ai le INDEX ma le MATCH nai lo le VLOOKUP ile Excel

Na matou faʻamatalaina muamua i tagata amata le faʻaogaina o galuega autu a le VLOOKUP (Igilisi VLOOKUP, o le faʻapuupuuga e tu mo le "vaega suʻesuʻe saʻo"). Ma o tagata fa'aoga poto na fa'aalia le tele o fa'asologa faigata.

Ma i lenei tusiga o le a tatou taumafai e tuʻuina atu faʻamatalaga e uiga i se isi metotia o le galue i luga ole suʻesuʻega.

Atonu e te mafaufau: "Aisea e tatau ai lenei mea?". Ma e manaʻomia lenei mea ina ia faʻaalia uma metotia suʻesuʻe. E le gata i lea, o le tele o tapula'a VLOOKUP e masani ona taofia ai le mauaina o le taunuuga mana'omia. I lenei tulaga, INDEX() MATCH() e sili atu ona aoga ma eseese, ma e itiiti foi a latou tapulaa.

Fa'avae INDEX MATCH

Talu ai o le faʻamoemoe o lenei taʻiala o le faʻaalia lea o le lelei o lenei vaega, matou Seʻi o tatou vaʻavaʻai i faʻamatalaga autu e uiga i mataupu faavae o lona faʻatinoga. Ma o le a matou faʻaalia faʻataʻitaʻiga, ma mafaufau foi pe aisea, e sili atu nai lo VLOOKUP ().

INDEX Galuega Fa'atusa ma Fa'aoga

O lenei galuega e fesoasoani i le sailia o le tau manaʻomia i totonu o nofoaga faʻapitoa suʻesuʻe e faʻavae i luga o le koluma poʻo le laina numera. Faʻailoga:

=INDEX(fa'asologa, numera laina, numera koluma):

• array – le vaega o le a faia ai le sailiga;
• numera laina - o le numera o le laina e suʻe i le faʻasologa faʻapitoa. Afai e le iloa le numera o le laina, e tatau ona faʻamaonia le numera koluma;
• numera koluma - o le numera o le koluma e maua i le faʻasologa faʻapitoa. Afai e le iloa le tau, e manaʻomia se numera laina.

O se faʻataʻitaʻiga o se fua faʻatatau faigofie:

=INDEX(A1:S10,2,3)

Ole galuega ole a su'e ile va'aiga mai le A1 ile C10. O fuainumera o lo'o fa'aalia ai le laina (2) ma le koluma (3) e fa'aalia ai le tau e mana'omia. Ole taunuuga ole cell C2.

Faigofie faigofie, sa'o? Ae a e galue ma pepa moni, atonu e te le maua ni faʻamatalaga e uiga i numera koluma poʻo sela. Ole mea lena ole MATCH() galuega e mo.

MATCH Function Syntax ma Fa'aoga

Ole galuega ole MATCH() e su'e le tau mana'omia ma fa'aalia lona numera fa'atatau ile vaega ole su'esu'ega.

O le searchpos() syntax e pei o lenei:

= MATCH(taua e su'e, fa'asologa e su'e, ituaiga fa'atusa)

• tau su'esu'e - le numera po'o tusitusiga e maua;
• laiga su'esu'e - o le vaega o le a faia ai le sailiga;
• ituaiga fetaui - faʻamaonia pe suʻe le tau saʻo poʻo tau e sili ona latalata i ai:
  • 1 (pe leai se tau faʻamaonia) - toe faʻafoʻi mai le tau sili ona maualuga e tutusa pe itiiti ifo nai lo le tau na faʻamaonia;
  • 0 - faʻaalia se fetaui tonu ma le tau suʻesuʻe. I le tuufaatasiga INDEX() MATCH() e toetoe lava o taimi uma e te manaʻomia ai se fetaui tonu, o lea matou te tusia 0;
  • -1 - Faʻaalia le tau sili ona laʻititi e sili atu pe tutusa ma le tau faʻamaonia i le fua faʻatatau. O le fa'avasegaina o lo'o fa'atinoina i le fa'asolo i lalo.

Mo se faʻataʻitaʻiga, i le vaʻaiga B1:B3 Niu Ioka, Paris, Lonetona ua resitalaina. O le fua fa'atatau o lo'o i lalo o le a fa'aalia ai le numera 3 ona o Lonetona lona tolu i le lisi:

=FA'ALI'I(Lonetona,B1:B3,0)

Fa'afefea ona galue ma le galuega INDEX MATCH 

Atonu ua amata ona e malamalama i le mataupu faavae e fausia ai le galuega soofaatasi o nei galuega tauave. I se faapuupuuga, la INDEX() su'esu'e le tau mana'omia i laina ma koluma fa'apitoa. Ma MATCH() o loʻo faʻaalia numera o nei tau:

=INDEX(koluma e toe faafoi mai ai le tau, MATCH(taua e su'e, koluma e su'e i totonu, 0))

O lo'o faigata pea ona malamalama pe fa'afefea ona galue? Atonu o se faʻataʻitaʻiga o le a sili atu ona faʻamatalaina. Fa'apea e iai sau lisi o laumua o le lalolagi ma lo latou faitau aofa'i:

Ina ia mafai ona iloa le tele o le faitau aofaʻi o se laumua, mo se faʻataʻitaʻiga, le laumua o Iapani, matou te faʻaaogaina le fua faʻatatau:

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

Faamatalaga:

• O le MATCH() galuega e suʻe le tau - "Iapani" i le laina A2: A10 ma toe faʻafoʻi le numera 3, aua o Iapani o le lona tolu lona tolu i le lisi. 
• O lenei fuainumera e alu inumera laina” i le INDEX() formula ma taʻu le galuega e lolomi se tau mai lenei laina.

O lea la o le fua o loo i luga ua avea ma fua masani FA'AMATALAGA(C2:C10,3). O le fua fa'atatau e su'e mai sela C2 i le C10 ma toe fa'afo'i mai fa'amatalaga mai le sela lona tolu i lenei laina, o lona uiga, C4, aua o le faitau e amata mai le laina lona lua.

E te le manaʻo e faʻatonu le igoa o le taulaga i le fua faʻatatau? Ona tusi lea i totonu o soʻo se sela, fai F1, ma faʻaaoga e fai ma faʻamatalaga ile MATCH() formula. Ma e fa'ai'u i se faiga su'esu'e malosi:

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

Taua! Numera o laina i totonu faatulagaga INDEX() e tatau ona tutusa ma le numera o laina i totonu fa'avasegaina i le MATCH (), a leai o le ae maua le iʻuga sese.

Faatali mo sina minute, aisea e le faʻaaogaina ai le VLOOKUP() fua?

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

 O le a le uiga o le faʻaumatia o le taimi e taumafai ai e suʻesuʻe nei faʻalavelave uma o INDEX MATCH?

I lenei tulaga, e le afaina po o le a le galuega e faʻaaoga. Ua na'o se fa'ata'ita'iga e malamalama ai pe fa'apefea ona galulue fa'atasi le INDEX() ma le MATCH(). O isi faʻataʻitaʻiga o le a faʻaalia ai mea e mafai e nei galuega i tulaga e leai se malosi o le VLOOKUP. 

INDEX MATCH poʻo VLOOKUP

Pe a filifili po o le fea suʻesuʻega e faʻaaoga, e ioe le toʻatele o INDEX() ma le MATCH() e sili atu nai lo VLOOKUP. Ae ui i lea, o loʻo faʻaaogaina pea e le toʻatele o tagata VLOOKUP(). Muamua, VLOOKUP() e sili atu ona faigofie, ma lona lua, e le o malamalama atoatoa tagata faʻaoga i mea lelei uma o le galue ma INDEX() ma MATCH(). A aunoa ma lenei malamalama, e leai se tasi e malie e faʻaalu lo latou taimi e suʻesuʻe ai se faiga faʻalavelave.

O mea taua ia ole INDEX() ma le MATCH() ile VLOOKUP():

• Su'e mai le taumatau i le agavale. VLOOKUP() e le mafai ona su'esu'e mai le taumatau i le agavale, o le mea lea o tau o lo'o e sailia e tatau lava ona i ai i koluma agavale o le laulau. Ae INDEX () ma MATCH () e mafai ona taulimaina lenei mea e aunoa ma se faʻafitauli. O lenei tusiga o le a taʻuina atu ia te oe le mea e foliga mai i le faʻatinoga: faʻafefea ona maua le tau manaʻomia i le itu tauagavale.

1. Fa'aopoopo saogalemu po'o le aveeseina o koluma. Ole fua ole VLOOKUP() e fa'aalia ai fa'ai'uga le sa'o pe a aveese pe fa'aopoopo koluma ona ole VLOOKUP() e mana'omia le numera sa'o o le koluma ina ia manuia. E masani lava, pe a faʻaopoopo pe aveese koluma, e suia foi a latou numera. 

Ma i totonu ole INDEX() ma le MATCH() fua, o le tele o koluma e faʻamaonia, ae le o koluma taʻitasi. O le i'uga, e mafai ona e fa'aopoopo ma aveese koluma ma le saogalemu e aunoa ma le toe fa'afouina o le fua fa'atatau i taimi ta'itasi.

1. Leai se tapula'a i le tele o su'esu'ega. A fa'aaoga VLOOKUP(), e le tatau ona sili atu i le 255 mata'itusi le aofa'i o su'esu'ega pe e te maua se #VALUE! Afai la o au faʻamatalaga o loʻo i ai se numera tele o mataitusi, INDEX () ma MATCH () o le filifiliga sili lea.
2. Saosaoa gaosiga maualuga. Afai e laʻititi au laulau, e te ono le matauina se eseesega. Ae, afai o le laulau o loʻo i ai le selau poʻo le afe o laina, ma, e tusa ai, o loʻo i ai le selau ma le afe o fua faʻatatau, INDEX () ma le MATCH () o le a sili atu le saoasaoa nai lo VLOOKUP (). O le mea moni o le Excel o le a faʻagasolo naʻo koluma o loʻo faʻamaonia i le fua, nai lo le faʻaogaina o le laulau atoa. 

Ole a'afiaga ole fa'atinoga ole VLOOKUP() ole a fa'apitoa ona iloa pe afai o lau pepa o galuega o lo'o i ai se numera tele o fua fa'atatau e pei ole VLOOKUP() ma SUM(). E mana'omia su'esu'ega tu'ufa'atasi o galuega VLOOKUP() e fa'avasega ai tau ta'itasi i se laina. O lea e tatau ai i Excel ona faʻatautaia le tele o faʻamatalaga, ma e faʻagesegese ai le galuega.

Fa'ata'ita'iga Fuafua 

Ua uma ona tatou iloa le aoga o nei galuega, o lea e mafai ai ona tatou agai i luma i le vaega sili ona manaia: o le faʻaaogaina o le malamalama i le faʻatinoga.

Fua fa'atatau e su'e mai le taumatau i le agavale

E pei ona taʻua muamua, e le mafai e VLOOKUP ona faia lenei ituaiga o sailiga. O lea la, afai e le o iai i le itu tauagavale pito i luga ole koluma, VLOOKUP() o le a le maua se taunuuga. Ole INDEX() ma le MATCH() galuega e sili atu ona fa'aogaina, ma o le nofoaga o tau e le tele sona sao mo i latou e galulue ai.

Mo se faʻataʻitaʻiga, o le a matou faʻaopoopoina se koluma tulaga i le itu agavale o la matou laulau ma taumafai e faʻavasega po o le a le tulaga i tulaga o le faitau aofaʻi o loʻo nofoia e le laumua o lo tatou Atunuʻu.

I totonu o le cell G1, matou te tusia le tau e maua, ona faʻaaoga lea o le fua faʻatatau e suʻe ai i le laina C1:C10 ma toe faʻafoʻi le tau tutusa mai le A2:A10:

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

Fa'anatinati. Afai e te fuafua e faʻaoga lenei fua mo le tele o sela, ia mautinoa e te faʻapipiʻi laina e faʻaaoga ai le tuatusi atoatoa (mo se faʻataʻitaʻiga, $A$2: $A$10 ma le $C$2: 4C$10).

INDEX MORE FA'AALI'INA MA LE FA'AALI'INA  e su'e i koluma ma laina

I faʻataʻitaʻiga o loʻo i luga, ua matou faʻaogaina nei galuega e fai ma sui mo VLOOKUP() e toe faʻafoʻi ai tau mai se faʻavasegaina o laina. Ae fa'apefea pe afai e te mana'omia le faia o se matrix po'o se su'esu'ega lua itu?

E foliga faigata, ae o le fua mo ia faʻatusatusaga e tutusa ma le faʻasologa masani INDEX() MATCH() fua, ma naʻo le tasi le eseesega: e tatau ona fa'aoga faalua le fua ole MATCH(). Le taimi muamua e maua ai le numera laina, ma le taimi lona lua e maua ai le numera koluma:

=INDEX(fa'asologa, MATCH(taua su'esu'e tūsa'o, koluma su'esu'e, 0), MATCH(taua su'esu'e fa'asaga, laina su'esu'e, 0))

Se'i o tatou tilotilo i le laulau o lo'o i lalo ma taumafai e fai se fua fa'atatau INDEX() EXPRESS() EXPRESS() ina ia mafai ona fa'aalia le faitau aofa'i o tagata i se atunu'u fa'apitoa mo se tausaga ua filifilia.

O le atunu'u fa'amoemoe o lo'o i le sela G1 (su'e sa'o) ma le tausaga fa'atatau o lo'o i le sela G2 (su'e fa'asaga'i). Ole fua ole a fa'apea:

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

E fa'afefea ona galue lenei fua fa'atatau

E pei o isi fua fa'atatau lavelave, e faigofie ona malamalama i ai e ala i le fa'amavaeina i fa'atusa ta'itasi. Ona mafai lea ona e malamalama i mea e fai e tagata taʻitoʻatasi:

• FESEGA(G1,A2:A11,0) - su'e se tau (G1) i le va'aiga A2:A11 ma fa'aalia le numera o lenei tau, i la matou tulaga o le 2;
• SILI(G2,B1:D1,0) – su'e se tau (G2) ile va'aiga B1:D1. I lenei tulaga, o le taunuuga e 3.

O le laina ma le koluma numera o loʻo maua e lafo i le tau tutusa i le INDEX() fua faʻatatau:

=INDEX(B2:D11,2,3)

O le i'uga, o lo'o i ai se matou tau o lo'o i totonu o se sela i le va'aiga o laina e 2 ma laina e 3 i le laina B2:D11. Ma o le fua faʻatatau o loʻo faʻaalia ai le tau manaʻomia, o loʻo i totonu o le cell D3.

Su'esu'e ile tele o tulaga ile INDEX ma le MATCH

Afai na e faitau i la matou taʻiala ile VLOOKUP(), masalo na e faʻataʻitaʻiina ni suʻesuʻega se tele. Ae o lenei metotia suʻesuʻe e tasi le tapulaʻa taua - o le manaʻoga e faʻaopoopo se koluma fesoasoani.

Ae o le tala lelei o lena Faatasi ai ma le INDEX() ma le MATCH() e mafai ona e su'eina le tele o tulaga e aunoa ma le toe fa'asa'o pe suia lau pepa o galuega.

Ole fua ole su'esu'ega fa'atele mo INDEX() MATCH():

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

O le fa'amatalaga: e tatau ona fa'aoga lenei fua fa'atasi ma le 'ala pupu'u CTRL+SHIFT+ENTER.

Fa'apea e te mana'omia le su'eina o le tau o lo'o e su'eina e fa'atatau i tulaga e 2: tagata faʻatau и Gaosiga.

O lenei mea e mana'omia ai le fua fa'atatau:

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

I lenei fua fa'atatau, C2:C10 o le va'aiga lea o le a faia ai le su'esu'ega, F1 - lea tulaga, A2:A10— o le va'aiga e fa'atusatusa ai le tulaga, F2 - tulaga 2, V2:V10 - va'aiga mo fa'atusatusaga o tulaga 2.

Aua nei galo e oomi le tuufaatasiga i le faaiuga o le galuega ma le fua CTRL + SHIFT + ULU - Excel o le a otometi lava ona tapunia le fua faʻatasi ma fusi faʻamalo, e pei ona faʻaalia i le faʻataʻitaʻiga:

Afai e te le manaʻo e faʻaoga se faʻasologa o faʻasologa mo lau galuega, ona faʻaopoopo lea o se isi INDEX() i le fua faʻatatau ma lolomi ENTER, e pei o le faʻataʻitaʻiga:

E fa'afefea ona galue nei fua fa'atatau

O lenei fua e galue i le auala lava e tasi e pei o le fua fa'atatau INDEX() MATCH(). Ina ia su'e ni tulaga se tele, na'o lou faia o le tele o tulaga False ma True e fa'atusa ai tulaga sa'o ma le sa'o a le tagata lava ia. Ona fa'aaoga lea o nei tulaga i elemene tutusa uma o le array. O le fua fa'atatau e fa'aliliuina ai fa'aupuga Sese ma le Moni i le 0 ma le 1, fa'asologa, ma fa'auluina se fa'asologa o lo'o i ai le 1 o tau tutusa na maua i le manoa. MATCH() o le a maua le tau muamua e fetaui ma le 1 ma pasi atu i le INDEX() fua. Ma, i le isi itu, o le a toe faʻafoʻi le tau manaʻomia i le laina faʻamaonia mai le koluma manaʻomia.

O se fua fa'atatau e aunoa ma se fa'asologa e fa'alagolago ile gafatia ole INDEX() e fa'afoeina na o ia. O le INDEX () lona lua i le fua fa'atatau e fetaui ma le pepelo (0), o lea e pasi atu ai le fa'asologa atoa ma na tau i le MATCH() fua. 

O se fa'amatalaga umi lea o le fa'atatau i tua atu o lenei fua fa'atatau. Mo nisi faʻamatalaga faitau le tusiga "INDEX MATCH ma le tele o tulaga".

AVERAGE, MAX ma MIN i INDEX ma MATCH

Excel ei ai ana lava galuega faʻapitoa mo le sailia o averesi, maualuga, ma laʻititi. Ae faʻafefea pe a e manaʻo e maua faʻamatalaga mai le sela e fesoʻotaʻi ma na tau? I lenei tulaga AVERAGE, MAX ma MIN e tatau ona faʻaoga faʻatasi ma INDEX ma MATCH.

INDEX MATCH ma MAX

Ina ia maua le tau sili ona tele ile koluma D ma fa'aali ile koluma C, fa'aoga le fua fa'atatau: 

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

INDEX MATCH ma MIN

Ina ia su'e le tau aupito itiiti ile koluma D ma fa'aali ile koluma C, fa'aoga le fua fa'atatau lea:

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

FAITAU SU'E ma SERPENT

Ina ia maua le tau averesi i le koluma D ma faʻaalia lenei tau ile C:

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

Faʻalagolago i le auala na tusia ai au faʻamatalaga, o le finauga lona tolu ile MATCH() ole 1, 0, poʻo le -1:

• pe a fa'avasegaina koluma i luga o le fa'asologa, seti le 1 (ona fa'atatauina lea e le fua fa'atatau le tau maualuga, lea e la'ititi pe tutusa ma le tau averesi);
• afai o le ituaiga o loʻo i lalo, ona -1 (o le fua faʻatatau e maua ai le tau maualalo e sili atu pe tutusa ma le averesi);
• afai o lo'o i ai i le va'aiga su'esu'e se tau e tutusa lelei ma le averesi, ona tu'u lea i le 0. 

 I la matou faʻataʻitaʻiga, o le faitau aofaʻi o loʻo faʻavasegaina i lalo, o lea matou te tuʻuina -1. Ma o le taunuuga o Tokyo, talu ai o le faitau aofaʻi (13,189) e sili ona latalata i le averesi tau (000).

VLOOKUP() e mafai fo'i ona fa'atino ia fa'atusatusaga, ae na'o se fa'asologa: VLOOKUP ma AVERAGE, MIN ma MAX..

INDEX MATCH ma ESND/IFERROR

Masalo ua uma ona e maitauina afai e le mafai e le fua faʻatatau ona maua le tau manaʻomia, e lafo ai se mea sese # N / A. E mafai ona e suia le feʻau faʻaletonu masani i se mea e sili atu faʻamatalaga. Mo se faʻataʻitaʻiga, seti le finauga i le fua faʻatatau I le XNUMXth:

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

Faatasi ai ma lenei fua, afai e te tuʻuina faʻamatalaga e le o iai i le laulau, o le a tuʻuina atu e le fomu le feʻau faʻamaonia.

Afai e te manaʻo e puʻeina mea sese uma, seʻi vagana ai I le XNUMXth e mafai ona faʻaaoga IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Ua iai se mea ua faaletonu!”)

Ae ia manatua o le ufiufi o mea sese i lenei auala e le o se manatu lelei, aua o mea sese masani e lipotia ai soliga i le fua.

Matou te faʻamoemoe na e mauaina le matou taʻiala i le faʻaaogaina o le INDEX MATCH() galuega fesoasoani.