Nahoana INDEX sy MATCH no tsara kokoa noho ny VLOOKUP amin'ny Excel

Efa nohazavainay teo aloha tamin'ireo vao manomboka ny fomba fampiasana ireo fiasa fototra amin'ny VLOOKUP (anglisy VLOOKUP, ny fanafohezana dia midika hoe "asa fitadiavana mitsangana"). Ary nasehon'ireo mpampiasa efa za-draharaha ireo fomba fiasa sarotra kokoa.

Ary ato amin'ity lahatsoratra ity isika dia hiezaka hanome vaovao momba ny fomba hafa miasa amin'ny fikarohana mitsangana.

Mety hanontany tena ianao hoe: "Nahoana no ilaina izany?". Ary ilaina izany mba hampisehoana ny fomba fikarohana rehetra azo atao. Fanampin'izany, fameperana VLOOKUP maro matetika no manakana ny fahazoana vokatra irina. Amin'io lafiny io, ny INDEX() MATCH() dia miasa sy isan-karazany kokoa, ary manana famerana kely kokoa ihany koa izy ireo.

Basics INDEX MATCH

Satria ny tanjon'ity torolalana ity dia ny hampiseho ny hatsaran'ity endri-javatra ity, izahay Andeha hojerentsika ny fampahalalana fototra momba ny fitsipiky ny fiasan'izy io. Ary hampiseho ohatra isika, ary diniho ny antony, tsara kokoa noho ny VLOOKUP ().

INDEX Function Syntax sy fampiasana

Ity fiasa ity dia manampy amin'ny fitadiavana ny sanda irina eo amin'ireo faritra fikarohana voatondro mifototra amin'ny laharan'ny tsanganana na andalana. Syntaxe:

=INDEX(array, laharana laharana, laharan'ny tsanganana):

• array - ny faritra izay hanaovana ny fikarohana;
• laharan'ny tsipika - ny isan'ny tsipika hokarohana ao amin'ny laharan-tariby voafaritra. Raha tsy fantatra ny laharan'ny andalana, dia tokony hofaritana ny laharan'ny tsanganana;
• laharan'ny tsanganana - ny isan'ny tsanganana ho hita ao amin'ny laharan-tariby voafaritra. Raha tsy fantatra ny sandany dia ilaina ny laharana andalana.

Ohatra iray amin'ny formula simple:

=INDEX(A1:S10,2,3)

Ny asa dia hikaroka ao amin'ny faritra A1 ka hatramin'ny C10. Ny isa dia mampiseho ny andalana (2) sy ny tsanganana (3) hanehoana ny sanda irina. Ny vokatr'izany dia cell C2.

Tsotra tsotra, sa tsy izany? Saingy rehefa miasa amin'ny antontan-taratasy tena izy ianao, dia mety tsy hanana fampahalalana momba ny laharan'ny tsanganana na ny sela. Izany no atao hoe MATCH() asa.

MATCH Function Syntax sy fampiasana

Ny fiasa MATCH() dia mikaroka ny sanda irina ary mampiseho ny isa eo ho eo ao amin'ny faritra fikarohana voatondro.

Ny syntax searchpos() dia toy izao:

=MATCH(sanda amin'ny fitadiavana, sanda ho amin'ny fitadiavana, karazana mifanandrify)

• sanda fikarohana - ny isa na lahatsoratra ho hita;
• laharan'ny fikarohana - ny faritra hanaovana ny fikarohana;
• karazana lalao - mamaritra raha hitady ny sanda marina na ny sanda akaiky indrindra:
  • 1 (na tsy misy sanda voafaritra) - mamerina ny sanda lehibe indrindra mitovy na latsaka amin'ny sanda voatondro;
  • 0 - mampiseho mifanandrify tsara amin'ny sanda tadiavina. Ao amin'ny fitambarana INDEX() MATCH() dia saika mila mifanandrify marina foana ianao, ka manoratra 0 izahay;
  • -1 - Mampiseho ny sanda kely indrindra izay lehibe kokoa na mitovy amin'ny sanda voalaza ao amin'ny formula. Ny fanasokajiana dia atao amin'ny filaharana midina.

Ohatra, ao amin'ny faritra B1:B3 New York, Paris, London dia voasoratra anarana. Ny formula eto ambany dia hampiseho ny isa 3 satria i Londres no fahatelo amin'ny lisitra:

=EXPOSE(London,B1:B3,0)

Ahoana ny fomba fiasa miaraka amin'ny fiasa INDEX MATCH 

Mety efa nanomboka nahatakatra ny foto-kevitry ny fananganana ny fiaraha-miasa amin'ireo asa ireo ianao. Raha fintinina, dia INDEX() dia mikaroka ny sanda ilaina amin'ireo andalana sy tsanganana voafaritra. Ary ny MATCH() dia mampiseho ny isan'ireo soatoavina ireo:

=INDEX(tsangantsanganana iverenan'ny sanda, MATCH(sarobidy hikaroka, tsanganana hikaroka, 0))

Mbola sahirana amin'ny fahazoana ny fomba fiasan'izy io? Angamba ohatra iray no hanazava tsara kokoa. Eritrereto hoe manana lisitry ny renivohitra eran-tany sy ny mponina ao aminy ianao:

Mba hahitana ny haben'ny mponina ao amin'ny renivohitra iray, ohatra, ny renivohitr'i Japana, dia mampiasa izao manaraka izao:

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

fanazavana:

• Ny fiasa MATCH() dia mitady ny sanda - "Japon" ao amin'ny array A2: A10 ary mamerina ny isa 3, satria i Japana no sanda fahatelo amin'ny lisitra. 
• Ity tarehimarika ity dia mandeha anylaharana andalana” ao amin'ny formula INDEX() ary milaza ny fiasa hanonta sanda avy amin'ity laharana ity.

Noho izany dia lasa formula standard ny formula ambony INDEX(C2:C10,3). Ny raikipohy dia mikaroka avy amin'ny sela C2 ka hatramin'ny C10 ary mamerina ny angona avy amin'ny sela fahatelo amin'ity sehatra ity, izany hoe C4, satria manomboka amin'ny laharana faharoa ny fanisana.

Tsy te hanoritra ny anaran'ny tanàna ao amin'ny formula? Avy eo soraty amin'ny sela rehetra izany, lazao F1, ary ampiasao ho fanondroana ao amin'ny formula MATCH(). Ary miafara amin'ny raikipohy fikarohana mavitrika ianao:

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

Zava-dehibe! Isan'ny andalana ao fihaingoana INDEX() dia tsy maintsy mitovy amin'ny isan'ny andalana heverina array amin'ny MATCH(), raha tsy izany dia hahazo valiny diso ianao.

Andraso kely, maninona raha mampiasa ny formula VLOOKUP() fotsiny?

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

 Inona no dikan'ny fandaniam-potoana amin'ny fitadiavana ireo fahasarotana rehetra amin'ny INDEX MATCH?

Amin'ity tranga ity, tsy maninona izay asa ampiasaina. Ohatra fotsiny ity mba hahatakarana ny fomba fiasan'ny INDEX() sy MATCH(). Ny ohatra hafa dia hampiseho ny fahaizan'ireo fiasa ireo amin'ny toe-javatra tsy misy hery ny VLOOKUP. 

INDEX MATCH na VLOOKUP

Rehefa manapa-kevitra izay raikipohy fikarohana hampiasaina dia maro no manaiky fa INDEX() sy MATCH() dia ambony lavitra noho ny VLOOKUP. Na izany aza, mbola maro ny olona mampiasa VLOOKUP(). Voalohany, tsotra kokoa ny VLOOKUP(), ary faharoa, tsy azon'ny mpampiasa tsara ny tombony rehetra amin'ny fiaraha-miasa amin'ny INDEX() sy MATCH(). Raha tsy misy io fahalalana io dia tsy misy olona hanaiky handany ny fotoanany amin'ny fianarana rafitra sarotra.

Ireto ny tombony lehibe amin'ny INDEX() sy MATCH() amin'ny VLOOKUP():

• Mitadiava miankavanana miankavia. Ny VLOOKUP() dia tsy afaka mikaroka avy amin'ny havanana miankavia, ka ny soatoavina tadiavinao dia tsy maintsy ao amin'ny tsanganana ankavia indrindra amin'ny latabatra. Saingy ny INDEX () sy MATCH () dia afaka miatrika izany tsy misy olana. Ity lahatsoratra ity dia hilaza aminao ny endrik'izany amin'ny fampiharana: ny fomba hahitana ny sanda irina eo amin'ny ilany havia.

1. Fanampiana na fanesorana andry azo antoka. Ny formula VLOOKUP() dia mampiseho valiny diso rehefa manala na manampy tsanganana satria mila ny laharan'ny tsanganana marina ny VLOOKUP() mba hahomby. Mazava ho azy fa rehefa ampiana na esorina ny tsanganana dia miova koa ny isany. 

Ary ao amin'ny formulas INDEX() sy MATCH() dia misy tsanganana maromaro voatondro fa tsy tsanganana tsirairay. Vokatr'izany dia azonao atao ny manampy sy manala tsanganana tsy misy fanavaozam-baovao isaky ny mandeha.

1. Tsy misy fetra ny habetsahan'ny fikarohana. Rehefa mampiasa VLOOKUP() dia tsy tokony hihoatra ny tarehintsoratra 255 ny fitambaran'ny fepetra fikarohana raha tsy izany dia hahazo #VALUE ianao! Ka raha misy tarehintsoratra maro be ny angonao dia INDEX() sy MATCH() no safidy tsara indrindra.
2. Haingam-pandeha ambony. Raha kely ny latabatrao, dia mety tsy ho hitanao ny fahasamihafana. Saingy, raha misy andalana an-jatony na an'arivony ny latabatra, ary, araka izany, misy raikipohy an-jatony sy an'arivony, INDEX () sy MATCH () dia hiatrika haingana kokoa noho ny VLOOKUP (). Ny zava-misy dia ny Excel ihany no handamina ny tsanganana voatondro ao amin'ny formula, fa tsy ny fanodinana ny latabatra manontolo. 

Ny fiantraikan'ny VLOOKUP() dia ho tsikaritra indrindra raha misy raikipohy marobe toy ny VLOOKUP() sy SUM() ny takelakao. Ny fisavana misaraka amin'ny fiasa VLOOKUP() dia ilaina mba hamakiana ny sanda tsirairay ao anaty laharan-kira. Noho izany, Excel dia tsy maintsy mikarakara fampahalalana be dia be, ary mampiadana ny asa izany.

Ohatra Formula 

Efa hitantsika ny maha-zava-dehibe an'ireo asa ireo, ka afaka mandroso amin'ny ampahany mahaliana indrindra isika: ny fampiharana ny fahalalana amin'ny fampiharana.

Formula hikaroka miankavanana miankavia

Araka ny efa voalaza dia tsy afaka manao an'io karazana fikarohana io ny VLOOKUP. Noho izany, raha tsy ao amin'ny tsanganana ankavia ny soatoavina irina, dia tsy hamokatra vokatra ny VLOOKUP(). Ny fiasa INDEX() sy MATCH() dia maro kokoa, ary ny toerana misy ny soatoavina dia tsy manana anjara toerana lehibe amin'ny fiasan'izy ireo.

Ohatra, hanisy tsanganana laharana eo amin'ny ilany ankavia amin'ny latabatra isika ary hiezaka hamantatra hoe inona no laharan'ny mponina misy ny renivohitry ny Tanindrazanay.

Ao amin'ny sela G1, dia manoratra ny sanda ho hita, ary avy eo dia mampiasa ity formula manaraka ity mba hikaroka ao amin'ny faritra C1: C10 ary hamerina ny sanda mifanaraka amin'izany avy amin'ny A2: A10:

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

Prompt. Raha mikasa ny hampiasa an'io raikipohy io ho an'ny sela maro ianao, dia ataovy azo antoka fa amboarinao ireo faritra ireo amin'ny fampiasana adiresy tanteraka (ohatra, $A$2: $A$10 ary $C$2: 4C$10).

INDEX MORE EXPOSED MORE EXPOSED  hikaroka amin'ny tsanganana sy andalana

Amin'ireo ohatra etsy ambony ireo dia nampiasa ireo fiasa ireo izahay ho solon'ny VLOOKUP() hamerenana ny soatoavina avy amin'ny andalana efa voafaritra mialoha. Ahoana anefa raha mila manao matrix na fikarohana roa?

Toa sarotra izany, fa ny formula ho an'ny kajy toy izany dia mitovy amin'ny formula INDEX() MATCH() mahazatra, izay misy fahasamihafana tokana: ny formula MATCH() dia tsy maintsy ampiasaina indroa. Sambany nahazo ny laharana andalana, ary faharoa nahazo ny laharan'ny tsanganana:

=INDEX(array, MATCH(sandam-pikarohana mitsangana, tsanganana fikarohana, 0), MATCH(sanda fikarohana marindrano, laharan-karoka, 0))

Andeha hojerentsika ny tabilao etsy ambany ary hiezaka ny hanao raikipohy INDEX() EXPRESS() EXPRESS() mba hanehoana demografika any amin'ny firenena iray manokana mandritra ny taona voafantina.

Ny firenena kendrena dia ao amin'ny sela G1 (fijerena mitsangana) ary ny taona kendrena dia ao amin'ny sela G2 (fikarohana marindrano). Ny formula dia ho toy izao:

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

Ahoana no fiasan'ity formula ity

Toy ny amin'ny formula complexes hafa, mora azo izy ireo amin'ny fanaparitahana azy ireo ho equations tsirairay. Ary avy eo azonao takarina ny asan'ny tsirairay:

• FITIAVANA(G1,A2:A11,0) – mitady sanda (G1) ao amin'ny laharana A2:A11 ary mampiseho ny isan'io sanda io, raha ny eto amintsika dia 2;
• FIKARAZANA(G2,B1:D1,0) – mitady sanda (G2) amin'ny salan'isa B1:D1. Amin'ity tranga ity, ny vokatra dia 3.

Ny laharan'ny andalana sy ny tsanganana hita dia alefa amin'ny sanda mifanaraka amin'izany ao amin'ny formula INDEX():

=INDEX(B2:D11,2,3)

Vokatr'izany dia manana sanda iray isika ao amin'ny sela iray eo anelanelan'ny andalana 2 sy tsanganana 3 ao amin'ny salan'isa B2:D11. Ary ny formula dia mampiseho ny sanda irina, izay ao amin'ny sela D3.

Mitadiava amin'ny fepetra maromaro miaraka amin'ny INDEX sy MATCH

Raha namaky ny torolàlana momba ny VLOOKUP() ianao, dia mety efa nanandrana raikipohy fikarohana maro ianao. Saingy ity fomba fikarohana ity dia manana fetra lehibe iray - ny ilàna ny fampidirana tsanganana fanampiny.

Fa ny vaovao tsara dia izany Miaraka amin'ny INDEX() sy MATCH() dia afaka mikaroka fepetra maromaro ianao nefa tsy mila manova na manova ny taratasinao.

Ity ny raikipohy fikarohana amin'ny ankapobeny ho an'ny INDEX() MATCH():

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

Ny fanamarihana: ity raikipohy ity dia tsy maintsy ampiasaina miaraka amin'ny hitsin-dàlana fitendry CTRL+SHIFT+ENTER.

Eritrereto hoe mila mitady ny sanda tadiavinao ianao mifototra amin'ny fepetra 2: mpividy и Vokatra.

Mitaky ity formula manaraka ity izany:

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

Amin'ity formula ity, C2:C10 dia ny salan'isa izay hanaovana ny fikarohana, F1 - izany fepetra izany, A2:A10 — dia ny elanelana hampitahana ny fepetra, F2 - fepetra 2, v2:v10 - faritra fampitahana ny fepetra 2.

Aza adino ny manindry ny fitambarana amin'ny faran'ny asa miaraka amin'ny raikipohy CTRL+SHIFT+ENTER - Excel dia hanidy ho azy ny formula miaraka amin'ny braces olioly, araka ny aseho amin'ny ohatra:

Raha tsy te-hampiasa raikipohy array ho an'ny asanao ianao, dia ampio INDEX () hafa amin'ny formula ary tsindrio ny ENTER, dia ho toy ny amin'ny ohatra:

Ahoana no fiasan'ireo formulas ireo

Ity raikipohy ity dia miasa mitovy amin'ny fomba mahazatra INDEX() MATCH() formula. Raha hikaroka fepetra maromaro ianao dia mamorona fepetra Diso sy Marina marobe izay maneho ny fepetra tsirairay marina sy diso. Ary avy eo ireo fepetra ireo dia mihatra amin'ny singa mifandraika amin'ny array. Ny formula dia mamadika ny hevitra diso sy True ho 0 sy 1, tsirairay avy, ary mamoaka array izay misy 1 ny soatoavina mifanandrify hita ao amin'ny tady. MATCH() dia hahita ny sanda voalohany mifanandrify amin'ny 1 ary ampita izany amin'ny formula INDEX(). Ary izany indray dia hamerina ny sanda efa irina ao amin'ny tsipika voafaritra avy amin'ny tsanganana irina.

Ny formula tsy misy array dia miankina amin'ny fahaizan'ny INDEX() mitantana azy irery. Ny INDEX () faharoa ao amin'ny formula dia mifanandrify amin'ny sandoka (0), noho izany dia mampita ny sanda manontolo miaraka amin'ireo soatoavina ireo amin'ny formula MATCH(). 

Ity dia fanazavana lava be momba ny lojika ao ambadik'io formula io. Raha mila fanazavana fanampiny dia vakio ny lahatsoratra "INDEX MATCH misy fepetra maro".

AVERAGE, MAX ary MIN amin'ny INDEX sy MATCH

Excel dia manana ny asany manokana amin'ny fitadiavana ny salan'isa, ny ambony indrindra ary ny kely indrindra. Ahoana anefa raha te haka angona avy amin'ny sela mifandray amin'ireo soatoavina ireo ianao? Raha izany dia AVERAGE, MAX ary MIN dia tsy maintsy ampiasaina miaraka amin'ny INDEX sy MATCH.

INDEX MATCH sy MAX

Mba hahitana ny sanda lehibe indrindra amin'ny tsanganana D ary asehoy amin'ny tsanganana C, ampiasao ny formula: 

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

INDEX MATCH sy MIN

Raha te hahita ny sanda kely indrindra ao amin'ny tsanganana D ary asehoy amin'ny tsanganana C dia ampiasao ity formula manaraka ity:

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

SEARCH INDEX sy SERPENT

Mba hahitana ny sanda antonony ao amin'ny tsanganana D ary asehoy amin'ny C ity sanda ity:

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

Miankina amin'ny fomba nanoratana ny angonao, ny hevitra fahatelo amin'ny MATCH() dia 1, 0, na -1:

• raha alahatra amin'ny filaharana miakatra ny tsanganana, dia apetraho 1 (dia kajy ny sanda ambony indrindra ny raikipohy, izay latsaka na mitovy amin'ny sanda antonony);
• raha midina ny karazana, dia -1 (ny formula dia hamoaka ny sanda kely indrindra izay lehibe kokoa na mitovy amin'ny salan'isa);
• raha misy sanda mitovy tanteraka amin'ny salan'isa ny laharan'ny fitadiavana dia apetraho amin'ny 0 izany. 

 Ao amin'ny ohatra ataontsika, ny mponina dia alaina amin'ny filaharana midina, ka mametraka -1. Ary ny vokany dia Tokyo, satria ny sandan'ny mponina (13,189) no akaiky indrindra amin'ny sanda antonony (000).

Ny VLOOKUP() dia afaka manao kajikajy toy izany ihany koa, fa amin'ny endrika rindrankajy ihany: VLOOKUP miaraka amin'ny AVERAGE, MIN ary MAX.

INDEX MATCH sy ESND/IFERROR

Efa voamarikao angamba fa raha tsy mahita ny sanda irina ny formula, dia miteraka hadisoana izany # N / A. Azonao atao ny manolo ny hafatra diso mahazatra amin'ny zavatra mampahafantatra kokoa. Ohatra, apetraho ao amin'ny formula ny argument Amin'ny faha-XNUMX:

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

Miaraka amin'ity formula ity, raha mampiditra angona tsy ao anaty tabilao ianao dia hanome anao ny hafatra voatondro ilay endrika.

Raha te-hisambotra ny fahadisoana rehetra, dia afa-tsy ny Amin'ny faha-XNUMX Azo ampiasaina IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Nisy tsy nety!”)

Saingy tadidio fa tsy hevitra tsara ny manafina fahadisoana amin'ny fomba toy izany, satria ny fahadisoana mahazatra dia mitatitra fanitsakitsahana ao amin'ny formula.

Manantena izahay fa nahasoa anao ny torolàlana momba ny fampiasana ny asa INDEX MATCH().