tirotiro
Kua whakamaramatia e matou i mua ki te hunga timata me pehea te whakamahi i nga mahi taketake o VLOOKUP (Ingarihi VLOOKUP, ko te whakapotonga mo te "mahi titiro poutū"). A i whakaatuhia ki nga kaiwhakamahi mohio he maha atu nga tauira uaua.
A i roto i tenei tuhinga ka ngana matou ki te tuku korero mo tetahi atu tikanga mahi me te rapu poutū.
Ka whakaaro pea koe: "He aha te mea e tika ana?". A he mea tika tenei hei whakaatu i nga tikanga rapu katoa. I tua atu, he maha nga here VLOOKUP e aukati ana i nga hua e hiahiatia ana. I runga i tenei ahuatanga, he nui ake te mahi me te kanorau o INDEX() MATCH(), a he iti ake o raatau here.
Tikanga INDEX MATCH
I te mea ko te kaupapa o tenei aratohu he whakaatu i te pai o tenei ahuatanga, na matou Kia titiro tatou ki nga korero taketake e pa ana ki nga tikanga o tana mahi. A ka whakaatu matou i nga tauira, me te whakaaro ano he aha, he pai ake i te VLOOKUP ().
INDEX Mahi Wetereo me te Whakamahinga
Ka awhina tenei mahi ki te rapu i te uara e hiahiatia ana i waenga i nga waahi rapu kua tohua i runga i te rarangi, te nama raina ranei. Tohu:
=INDEX(raupapa, tau haupae, tau tīwae):
- huānga – te waahi ka mahia te rapu;
- tau raina – te tau o te rarangi ka rapua i roto i te huinga kua tohua. Mēnā kāore i te mōhiotia te tau haupae, me tohu te tau tīwae;
- tau tīwae – te tau o te tīwae ka kitea i roto i te huinga kua tohua. Mena kaore i te mohiotia te uara, ka hiahiatia he nama raina.
He tauira o te tauira ngawari:
=INDEX(A1:S10,2,3)
Ka rapu te mahi i te awhe mai i A1 ki C10. Ko nga tau e whakaatu ana ko tehea rarangi (2) me te pou (3) hei whakaatu i te uara e hiahiatia ana. Ko te hua ko te pūtau C2.
He tino ngawari, tika? Engari ka mahi koe me nga tuhinga tuuturu, kare pea koe e whai korero mo nga nama pou, nga pūtau ranei. Koia te mahi MATCH() mo.
MATCH Function Syntax and Use
Ko te mahi MATCH() e rapu ana i te uara e hiahiatia ana ka whakaatu i tana tau tata ki te waahi rapu kua tohua.
Ko te ahua o te rapupos() he penei te ahua:
=MATCH(uara ki te titiro, huānga ki te titiro, momo ōrite)
- uara rapu – te tau, te kuputuhi ranei ka kitea;
- huānga rapu – te waahi ka mahia te rapu;
- momo orite – ka tohu mena ka rapua te uara tika, nga uara ranei e tata ana ki a ia:
- 1 (kaore ranei he uara kua tohua) – ka whakahoki i te uara nui rawa atu e rite ana, iti iho ranei i te uara i tohua;
- 0 – e whakaatu ana i te taurite tika me te uara i rapua. I roto i te huinga INDEX() MATCH() tata tonu ka hiahia koe ki te taurite tika, no reira ka tuhia e matou te 0;
- -1 – He whakaatu i te uara iti e nui ake ana, e rite ana ranei ki te uara kua tohua ki te tauira. Ka mahia te whakaraupapatanga.
Hei tauira, i te awhe B1:B3 New York, Paris, Raanana kua rehitatia. Ko te tauira i raro nei ka whakaatu i te nama 3 na te mea ko Raanana te tuatoru o te rarangi:
=WAKARANGA(London,B1:B3,0)
Me pehea te mahi me te mahi INDEX MATCH
Kua timata kee koe ki te mohio ki te kaupapa i hangaia ai te mahi tahi o enei mahi. I te poto, na INDEX() e rapu ana i te uara e hiahiatia ana i waenga i nga rarangi me nga pou kua tohua. A ko MATCH() e whakaatu ana i nga tau o enei uara:
=INDEX(tīwae i whakahokia mai ai te uara, MATCH(uara hei rapu, tīwae hei rapu, 0))
He uaua tonu ki te mohio me pehea te mahi? Tera pea he tauira ka pai ake te whakamarama. Mehemea kei a koe he rarangi o nga whakapaipai o te ao me o raatau taupori:
Kia kitea ai te rahi o te taupori o tetahi whakapaipai, hei tauira, te whakapaipai o Hapani, ka whakamahia e matou te tauira e whai ake nei:
=INDEX(C2:C10, MATCH(Hapana, A2:A10,0))
Whakamārama:
- Ko te mahi MATCH() e rapu ana i te uara – “Hapanihi” i te huinga A2:A10 ka whakahoki i te nama 3, na te mea ko Hapani te uara tuatoru o te rarangi.
- Ka haere tenei whika kitau raina” i roto i te tātai INDEX() me te korero ki te mahi ki te tā i tetahi uara mai i tenei rarangi.
Na ko te tauira i runga ake nei ka noho hei tauira paerewa TAURANGA(C2:C10,3). Ka rapu te tātai mai i ngā pūtau C2 ki te C10 me te whakahoki raraunga mai i te pūtau tuatoru o tēnei awhe, arā, C4, nā te mea ka timata te tatau mai i te rarangi tuarua.
Kaua e hiahia ki te tohu i te ingoa o te taone i roto i te tauira? Kātahi ka tuhi ki roto i tētahi pūtau, kī F1, ka whakamahi hei tohutoro i te tātai MATCH(). Na ka mutu koe me te tauira rapu hihiri:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Mea nui! Te maha o nga rarangi kei roto ngohi INDEX() me rite ki te maha o nga rarangi kei roto ka whakaarohia he huinga i MATCH(), ki te kore ka he te hua.
Taria he meneti, he aha e kore ai e whakamahi noa i te tauira VLOOKUP()?
=VLOOKUP(F1, A2:C10, 3, Heke)
He aha te take o te moumou taima ki te whakaaro ki enei uauatanga katoa o te INDEX MATCH?
I tenei keehi, kaore he mea nui ko tehea mahi hei whakamahi. He tauira noa tenei kia mohio ai koe ki te mahi tahi o te INDEX() me te MATCH(). Ko etahi atu tauira ka whakaatu he aha te kaha o enei mahi i roto i nga ahuatanga kaore he mana o te VLOOKUP.
INDEX MATCH, VLOOKUP ranei
I te wa e whakatau ana ko tehea ture rapu hei whakamahi, he tokomaha e whakaae ana ko INDEX() me MATCH() he tino pai ake i a VLOOKUP. Heoi, he maha nga tangata e whakamahi tonu ana i te VLOOKUP(). Tuatahi, he maamaa ake a VLOOKUP(), tuarua, kaore nga kaiwhakamahi e tino marama ki nga painga katoa o te mahi me INDEX() me MATCH(). Ki te kore tenei matauranga, kaore tetahi e whakaae ki te whakapau i to ratau wa ki te ako i tetahi punaha uaua.
Anei nga tino painga o INDEX() me MATCH() mo VLOOKUP():
- Rapua mai i matau ki maui. Kaore e taea e VLOOKUP() te rapu mai i te matau ki te maui, no reira ko nga uara e rapu ana koe me noho tonu ki nga pou kei te taha maui o te ripanga. Engari ka taea e INDEX() me MATCH() te whakahaere i tenei kaore he raru. Ma tenei tuhinga e korero ki a koe te ahua o te mahi: me pehea te kimi i te uara e hiahiatia ana i te taha maui.
- Te taapiri haumaru, te tango ranei i nga pou. Ko te tātai VLOOKUP() e whakaatu ana i nga hua he ina tango, he taapiri ranei i nga pou na te mea e hiahia ana a VLOOKUP() ki te tau tīwae tika kia angitu. Ko te tikanga, ka taapirihia, ka tangohia ranei nga pou, ka rereke ano o raatau nama.
A, i roto i te INDEX() me te MATCH() tātai, he awhe o nga pou kua tohua, kaua ko nga pou takitahi. Ko te mutunga, ka taea e koe te taapiri me te tango i nga pou me te kore e whakahou i te ture ia wa.
- Karekau he rohe mo nga pukapuka rapu. Ina whakamahi ana koe i te VLOOKUP(), ko te tapeke o nga paearu rapu me kaua e neke ake i te 255 pūāhua, ka whiwhi koe i te #VALUE! Na, mena he maha nga tohu kei roto i to raraunga, ko INDEX() me MATCH() te mea pai rawa atu.
- Te tere tukatuka teitei. Mena he iti noa to papapu, kare pea koe e kite i tetahi rereketanga. Engari, ki te mea he rau, he mano ranei nga rarangi kei roto i te ripanga, a, na reira, he rau me te mano o nga tauira, INDEX () me MATCH () ka tere ake i te VLOOKUP (). Ko te meka ka tukatukahia e Excel nga pou kua tohua i roto i te tauira, kaua ki te tukatuka i te ripanga katoa.
Ka tino kitea te awenga mahi a VLOOKUP() mena he maha nga tauira kei roto i to raumahi penei i te VLOOKUP() me te SUM(). Ko nga arowhai motuhake o nga mahi VLOOKUP() e hiahiatia ana hei tarai i ia uara i roto i te huranga. Na me mahi a Excel i te nui o nga korero, na tenei ka tino puhoi te mahi.
Tauira tauira
Kua kitea e matou te whaihua o enei mahi, kia taea ai e matou te neke atu ki te waahanga tino pai: ko te whakamahinga o te matauranga i roto i nga mahi.
Tātai hei rapu mai i te matau ki te maui
Ka rite ki te korero, kaore e taea e VLOOKUP te mahi i tenei momo rapu. Na, mena karekau nga uara e hiahiatia ana i te taha maui, ka kore e puta he hua a VLOOKUP(). Ko nga mahi INDEX() me te MATCH() he maha ake nga mahi, a ko te waahi o nga uara kaore e whai waahi nui ki te mahi.
Hei tauira, ka taapirihia he pou rarangi ki te taha maui o ta maatau ripanga ka ngana ki te whakaaro he aha te reanga i runga i te taupori e nohoia ana e te whakapaipai o To tatou Whenua.
I roto i te pūtau G1, tuhia te uara ka kitea, ka whakamahi i te tauira e whai ake nei hei rapu i te awhe C1:C10 me te whakahoki i te uara mai i A2:A10:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Whakatairanga. Mēnā ka whakamahere koe ki te whakamahi i tēnei tātai mō ngā pūtau maha, me whakatika e koe ngā awhe mā te whakamahi wāhitau tino (hei tauira, $A$2: $A$10 me te $C$2: 4C$10).
INDEX MORE MORE EXPOSED MORE EXPOSED ki te rapu i nga pou me nga rarangi
I nga tauira o runga ake nei, kua whakamahia e matou enei mahi hei whakakapi mo VLOOKUP() ki te whakahoki i nga uara mai i te awhe rarangi kua tautuhia. Engari he aha mena ka hiahia koe ki te mahi matrix, ki te rapu rua-taha ranei?
He uaua te ahua, engari he rite te tauira mo aua tatauranga ki te tauira INDEX() MATCH() paerewa, kotahi anake te rereketanga: me whakamahi rua te tātai MATCH(). Ko te wa tuatahi ki te tiki i te tau haupae, me te wa tuarua ki te tiki i te tau tīwae:
=INDEX(raupapa, MATCH(uara rapu poutū, tīwae rapu, 0), MATCH(uara rapu whakapae, haupae rapu, 0))
Me titiro ki te ripanga i raro nei ka ngana ki te hanga i tetahi tauira INDEX() EXPRESS() EXPRESS() kia taea ai te whakaatu taupori ki tetahi whenua motuhake mo te tau kua tohua.
Ko te whenua i whäia kei roto i te pūtau G1 (titiro poutū) ko te tau i whäia kei te pūtau G2 (titiro whakapae). Ka penei te ahua o te tauira:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Me pehea te mahi o tenei tauira
Pērā ki ētahi atu tātai matatini, he māmā ake te mārama mā te wāwāhi ki ngā whārite takitahi. Na ka mohio koe he aha te mahi a ia mahi:
- MATUA(G1,A2:A11,0) – rapua he uara (G1) i te awhe A2:A11 me te whakaatu i te tau o tenei uara, i roto i ta maatau he 2;
- RAPU(G2,B1:D1,0) – he rapu uara (G2) i te awhe B1:D1. I tenei keehi, ko te hua ko te 3.
Ko nga tau haupae me nga tau pou kua kitea ka tukuna ki te uara e rite ana ki te tauira INDEX():
=INDEX(B2:D11,2,3)
Ko te mutunga mai, he uara kei roto i te pūtau kei te haupae o nga rarangi e 2 me nga pou e 3 i te awhe B2:D11. A ka whakaatu te tauira i te uara e hiahiatia ana, kei roto i te pūtau D3.
Rapua ma nga tikanga maha me INDEX me MATCH
Mena kua panui koe i to maatau aratohu ki te VLOOKUP(), kua whakamatauria pea e koe nga tauira rapu maha. Engari ko tenei tikanga rapu he herenga nui - ko te hiahia ki te taapiri i tetahi pou awhina.
Engari ko te rongo pai ko tera Ma te INDEX() me te MATCH() ka taea e koe te rapu tikanga maha me te kore e whakarereke, e whakarereke ranei i to raumahi.
Anei te tātai rapu tikanga-maha whānui mo INDEX() MATCH():
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
Ko te tuhipoka: me whakamahi tahi tenei tauira me te pokatata papapātuhi CTRL+SHIFT+ENTER.
Ki te hiahia koe ki te rapu i te uara e rapu ana koe i runga i nga tikanga e 2: kaihoko и Hua.
Me whai tenei tauira:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
I roto i tenei tauira, ko C2:C10 te awhe ka puta te rapunga, F1 - tenei ahuatanga, A2:A10 — ko te awhe hei whakatairite i te ahuatanga, F2 – huru 2, V2:V10 – awhe mo te whakatairite o te ahuatanga 2.
Kaua e wareware ki te pehi i te huinga i te mutunga o te mahi me te tauira CTRL+SHIFT+ENTER – Ka kati aunoa a Excel i te tauira me nga taiapa mingimingi, penei i te tauira:
Ki te kore koe e pirangi ki te whakamahi i tetahi tauira huinga mo to mahi, katahi ka taapirihia tetahi atu INDEX() ki te tauira ka pehi i te ENTER, ka rite te ahua ki te tauira:
Me pehea te mahi o enei tauira
He rite tonu te mahi o tenei tauira ki te tauira INDEX() MATCH() paerewa. Ki te rapu i nga tikanga maha, ka hanga noa e koe nga tikanga Heke me te Pono e tohu ana i nga tikanga takitahi tika me te he. Na ka pa enei tikanga ki nga waahanga katoa o te huinga. Ka huri te tātai i nga tohenga Heke me te Tika ki te 0 me te 1, ka whakaputa i te huānga ko te 1 nga uara taurite i kitea i roto i te aho. Ka kitea e MATCH() te uara tuatahi e rite ana ki te 1 ka tukuna ki te tauira INDEX(). Na, ka hoki mai ano te uara e hiahiatia ana i roto i te raina kua tohua mai i te pou e hiahiatia ana.
Ko te tauira kaore he huinga e whakawhirinaki ana ki te kaha o INDEX() ki te hapai i a raatau ake. Ko te INDEX() tuarua i roto i te tātai e ōrite ana ki te hē (0), nā reira ka tukuna te huinga katoa me aua uara ki te tātai MATCH().
He whakamarama roa tenei mo te arorau kei muri i tenei tikanga. Mo etahi atu korero panuihia te tuhinga "INDEX MATCH me nga tikanga maha".
AVERAGE, MAX me te MIN i roto i te INDEX me te MATCH
Kei a Excel ana ake mahi motuhake mo te rapu i nga tauwaenga, te teitei, me te iti. Engari me pehea ki te hiahia koe ki te tiki raraunga mai i te pūtau e hono ana ki aua uara? I tenei take AVERAGE, MAX me te MIN me whakamahi tahi me INDEX me MATCH.
INDEX MATCH me MAX
Hei kimi i te uara nui rawa atu i te tīwae D ka whakaatu ki te tīwae C, whakamahia te tātai:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
INDEX MATCH me te MIN
Hei kimi i te uara iti rawa o te pou D me te whakaatu ki te pou C, whakamahia te tauira e whai ake nei:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
TE WHAKAMAHI RAPU me te NAKAHI
Hei kimi i te uara toharite i te pou D ka whakaatu i tenei uara ki C:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
I runga ano i te tuhi o to raraunga, ko te tohenga tuatoru ki te MATCH() he 1, 0, -1 ranei:
- ki te kōmakahia nga pou i roto i te raupapa aupiki, tautuhia te 1 (na te tauira ka tatau i te uara teitei, he iti iho, he rite ranei ki te uara toharite);
- mehemea kei te heke te ahua, katahi -1 (ka whakaputahia e te tauira te uara iti ka nui ake, e rite ana ranei ki te toharite);
- ki te mea he uara e rite ana ki te tauwaenga o te rarangi rapu, katahi ka tautuhi ki te 0.
I roto i ta maatau tauira, ka tohua te taupori i roto i te raupapa heke, no reira ka tohua -1. A ko te hua ko Tokyo, i te mea ko te uara taupori (13,189) te tata ki te uara toharite (000).
Ka taea hoki e VLOOKUP() te mahi i nga tatauranga penei, engari hei tauira huinga: VLOOKUP me AVERAGE, MIN me te MAX.
INDEX MATCH me ESND/IFERROR
Kua kite pea koe mena kaore e kitea e te tauira te uara e hiahiatia ana, ka puta he hapa # N / A. Ka taea e koe te whakakapi i te karere hapa paerewa me etahi atu korero. Hei tauira, whakatakotohia te tautohe ki te tauira I te XNUMXth:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
Ma tenei tauira, ki te whakauru koe i nga raraunga kaore i te ripanga, ka hoatu e te puka te karere kua tohua.
Mena kei te pirangi koe ki te hopu i nga hapa katoa, engari mo I te XNUMXth Ka taea te whakamahi IFERROR:
=IFERROR(INDEX(C2:C10, MATCH(F1,A2:A10,0)), “Kua raru tetahi mea!”)
Engari kia mahara ko te huna i nga hapa penei ehara i te mea pai, na te mea ko nga hapa paerewa e whakaatu ana i nga takahi i roto i te ture.
Ko te tumanako i kitea e koe to maatau kaiarahi mo te whakamahi i te mahi INDEX MATCH() he awhina.