Ինչպես օգտագործել INDEX և MATCH ֆունկցիաները Excel-ում

2024 Հեղինակ: Abigail Brown | [email protected]. Վերջին փոփոխված: 2023-12-17 06:51

Ինչ իմանալ

• INDEX ֆունկցիան կարող է օգտագործվել միայնակ, սակայն դրա ներսում MATCH ֆունկցիայի տեղադրումը ստեղծում է առաջադեմ որոնում:
• Այս ներդիր ֆունկցիան ավելի ճկուն է, քան VLOOKUP-ը և կարող է ավելի արագ արդյունք տալ:

Այս հոդվածը բացատրում է, թե ինչպես օգտագործել INDEX և MATCH գործառույթները միասին Excel-ի բոլոր տարբերակներում, ներառյալ Excel 2019 և Microsoft 365:

Որո՞նք են INDEX և MATCH ֆունկցիաները:

INDEX-ը և MATCH-ը Excel-ի որոնման գործառույթներ են: Թեև դրանք երկու բոլորովին առանձին գործառույթներ են, որոնք կարող են օգտագործվել ինքնուրույն, դրանք կարող են նաև համակցվել՝ առաջադեմ բանաձևեր ստեղծելու համար:

INDEX ֆունկցիան վերադարձնում է արժեք կամ հղում որոշակի ընտրության ներսում գտնվող արժեքին: Օրինակ, այն կարող է օգտագործվել տվյալների հավաքածուի երկրորդ շարքում կամ հինգերորդ տողում և երրորդ սյունակում արժեքը գտնելու համար:

Չնայած INDEX-ը շատ լավ կարող է օգտագործվել միայնակ, MATCH-ի տեղադրումը բանաձևում այն մի փոքր ավելի օգտակար է դարձնում: MATCH ֆունկցիան որոնում է որոշակի տարր բջիջների տիրույթում, այնուհետև վերադարձնում է տարրի հարաբերական դիրքը տիրույթում: Օրինակ, այն կարող է օգտագործվել որոշելու համար, որ կոնկրետ անունը անունների ցանկի երրորդ կետն է:

INDEX and MATCH Syntax & Arguments

Այսպես պետք է գրվեն երկու գործառույթներն էլ, որպեսզի Excel-ը հասկանա դրանք:

=INDEX(զանգված, տող_թիվ, [սյունակի_թիվ])

• զանգված-ը բջիջների շրջանակն է, որը կօգտագործի բանաձևը: Այն կարող է լինել մեկ կամ մի քանի տող և սյունակ, օրինակ՝ A1:D5: Դա պարտադիր է։
• տող_թիվ-ը զանգվածի այն տողն է, որտեղից պետք է վերադարձվի արժեք, օրինակ՝ 2 կամ 18: Այն պահանջվում է, եթե չկա սյունակի_թիվը:
• column_num զանգվածի այն սյունակն է, որտեղից պետք է վերադարձվի արժեք, օրինակ՝ 1 կամ 9: Դա կամընտիր է:

=MATCH(որոնման_արժեք, փնտրման_զանգված, [համապատասխանի_տիպ])

• lookup_value արժեքն է, որը ցանկանում եք համապատասխանեցնել lookup_array-ին: Այն կարող է լինել թիվ, տեքստ կամ տրամաբանական արժեք, որը մուտքագրվում է ձեռքով կամ հղում է կատարվում բջջային հղումի միջոցով: Սա պարտադիր է։
• lookup_array բջիջների միջակայքն է, որը պետք է դիտարկել: Այն կարող է լինել մեկ տող կամ մեկ սյունակ, օրինակ՝ A2:D2 կամ G1:G45: Սա պարտադիր է։
• match_type կարող է լինել -1, 0 կամ 1: Այն նշում է, թե ինչպես է lookup_value-ը համընկնում lookup_array-ի արժեքների հետ (տես ստորև): 1-ը լռելյայն արժեքն է, եթե այս արգումենտը բաց է թողնվել:

Համապատասխանության ո՞ր տեսակն օգտագործել
Համապատասխանության տեսակ	Ինչ է դա անում	Կանոն	Օրինակ
1	Գտնում է ամենամեծ արժեքը, որը փոքր է կամ հավասար է lookup_value-ին:	Փնտրման_զանգվածի արժեքները պետք է տեղադրվեն աճման կարգով (օրինակ՝ -2, -1, 0, 1, 2; կամ A-Z;, կամ FALSE, TRUE:	lookup_value-ը 25 է, բայց այն բացակայում է lookup_array-ից, ուստի փոխարենը վերադարձվում է հաջորդ ամենափոքր թվի դիրքը, ինչպես 22-ը:
0	Գտնում է առաջին արժեքը, որը ճիշտ հավասար է lookup_value-ին:	Փնտրման_զանգվածի արժեքները կարող են լինել ցանկացած հերթականությամբ:	lookup_value-ը 25 է, ուստի այն վերադարձնում է 25-ի դիրքը:
-1	Գտնում է ամենափոքր արժեքը, որը մեծ է կամ հավասար է lookup_value-ին:	Որոնման_զանգվածի արժեքները պետք է տեղադրվեն նվազման կարգով (օրինակ՝ 2, 1, 0, -1, -2):	lookup_value-ը 25 է, բայց այն բացակայում է lookup_array-ից, ուստի փոխարենը վերադարձվում է հաջորդ ամենամեծ թվի դիրքը, ինչպես 34-ը:

Օգտագործեք 1 կամ -1 այն դեպքերի համար, երբ ձեզ անհրաժեշտ է մոտավոր որոնում կատարել սանդղակի երկայնքով, օրինակ՝ երբ գործ ունենք թվերի հետ և երբ մոտավորությունները նորմալ են: Բայց հիշեք, որ եթե չնշեք match_type, 1-ը կլինի լռելյայն, ինչը կարող է շեղել արդյունքները, եթե իսկապես ցանկանում եք ճշգրիտ համընկնում:

Օրինակ INDEX և MATCH բանաձևեր

Նախքան նայենք, թե ինչպես համատեղել INDEX-ը և MATCH-ը մեկ բանաձևի մեջ, մենք պետք է հասկանանք, թե ինչպես են այս գործառույթներն ինքնուրույն աշխատում:

INDEX Օրինակներ

=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=INDEX(B1:B2, 1)





Այս առաջին օրինակում կան չորս INDEX բանաձևեր, որոնք կարող ենք օգտագործել տարբեր արժեքներ ստանալու համար.

=INDEX(A1:B2, 2, 2) նայում է A1:B2 միջով` գտնելու արժեքը երկրորդ սյունակում և երկրորդ շարքում, որը Stacy է:
=INDEX(A1:B1, 1) նայում է A1:B1-ի միջոցով՝ գտնելու արժեքը առաջին սյունակում, որը Jon է:
=INDEX(2:2, 1)-ն ուսումնասիրում է երկրորդ շարքի ամեն ինչ՝ գտնելու արժեքը առաջին սյունակում, որը Tim է:
=INDEX(B1:B2, 1) նայում է B1:B2-ի միջով` գտնելու արժեքը առաջին շարքում, որն է Amy.

MATCH Օրինակներ
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=ՄԱՏՉ(13, A1:D1, 0)





Ահա MATCH ֆունկցիայի չորս հեշտ օրինակ՝

=MATCH("Stacy", A2:D2, 0)-ը փնտրում է Stacy A2:D2 միջակայքում և որպես արդյունք վերադարձնում է 3:
=MATCH(14, D1:D2)-ը փնտրում է 14 D1:D2 միջակայքում, բայց քանի որ այն չի գտնվել աղյուսակում, MATCH-ը գտնում է հաջորդ ամենամեծ արժեքը: դա փոքր է կամ հավասար է 14-ի, որն այս դեպքում 13 է, որը գտնվում է lookup_array-ի 1 դիրքում:
=MATCH(14, D1:D2, -1)-ը նույնական է վերևի բանաձևին, բայց քանի որ զանգվածը նվազման կարգով չէ, ինչպես պահանջվում է -1-ը, մենք սխալ ենք ստանում։
=MATCH(13, A1:D1, 0)-ը թերթի առաջին շարքում փնտրում է 13-ը, որը վերադարձնում է 4-ը, քանի որ այն այս զանգվածի չորրորդ տարրն է:


INDEX-MATCH Օրինակներ
Ահա երկու օրինակ, որտեղ մենք կարող ենք միավորել INDEX-ը և MATCH-ը մեկ բանաձևում՝
Գտնել բջջային հղումը աղյուսակում
=INDEX(B2:B5, MATCH(F1, A2:A5))






Այս օրինակը տեղադրում է MATCH բանաձևը INDEX բանաձևի մեջ: Նպատակը ապրանքի գույնը նույնացնելն է՝ օգտագործելով ապրանքի համարը:
Եթե նայեք նկարին, ապա «Առանձնացված» տողերում կարող եք տեսնել, թե ինչպես կգրվեն բանաձևերը ինքնուրույն, բայց քանի որ մենք դրանք տեղադրում ենք, ահա թե ինչ է տեղի ունենում.

MATCH(F1, A2:A5)-ը փնտրում է F1 արժեքը (8795) A2:A5 տվյալների հավաքածուում: Եթե մենք հետ հաշվենք սյունակը, մենք կարող ենք տեսնել, որ այն 2 է, ուստի MATCH ֆունկցիան հենց դա է պարզել:
INDEX զանգվածը B2:B5 է, քանի որ մենք ի վերջո փնտրում ենք արժեքը այդ սյունակում:
INDEX ֆունկցիան այժմ կարող է վերաշարադրվել այսպես, քանի որ 2-ն այն է, ինչ MATCH-ը գտավ.
Քանի որ column_num-ը կամընտիր է, մենք կարող ենք հեռացնել այն, որպեսզի մնա հետևյալով՝ INDEX(B2:B5, 2)։
Այսպիսով, սա նման է սովորական INDEX բանաձևի, որտեղ մենք գտնում ենք երկրորդ կետի արժեքը B2:B5-ում, որը կարմիր է:

Փնտրում ըստ տողերի և սյունակների վերնագրերի
=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))






MATCH-ի և INDEX-ի այս օրինակում մենք կատարում ենք երկկողմանի որոնում: Գաղափարն այն է, որ տեսնենք, թե մայիսին որքան գումար ենք վաստակել կանաչ իրերից: Սա իսկապես նման է վերը բերված օրինակին, բայց հավելյալ MATCH բանաձևը տեղադրված է INDEX-ում:

MATCH(G1, A2:A13, 0) այս բանաձևում լուծված առաջին տարրն է: Այն փնտրում է G1 («մայիս» բառը) A2:A13-ում՝ որոշակի արժեք ստանալու համար: Մենք դա չենք տեսնում այստեղ, բայց դա 5 է:
MATCH(G2, B1:E1, 0)-ը երկրորդ MATCH բանաձևն է, և այն իսկապես նման է առաջինին, բայց փոխարենը փնտրում է G2 («Կանաչ» բառը:) B1:E1 հասցեում գտնվող սյունակների վերնագրերում: Այս մեկը լուծում է 3.
Այժմ կարող ենք INDEX բանաձևը վերաշարադրել այսպես՝ պատկերացնելու համար, թե ինչ է կատարվում. =INDEX(B2:E13, 5, 3): Սա ամբողջ աղյուսակում, B2:E13, փնտրում է հինգերորդ տող և երրորդ սյունակ, որը վերադարձնում է $180:


MATCH և INDEX կանոններ
Այս գործառույթներով բանաձևեր գրելիս պետք է հիշել մի քանի բան.

MATCH-ը մեծատառերի նկատմամբ զգայուն չէ, ուստի մեծատառերն ու փոքրատառերը նույն կերպ են վերաբերվում տեքստային արժեքներին համապատասխանեցնելիս:
MATCH-ը վերադարձնում է N/A մի քանի պատճառներով. եթե match_type-ը 0 է, և lookup_value-ը չի գտնվել, եթե match_type-ը -1 է, և lookup_array-ը նվազման կարգով չէ, եթե match_type-ը 1 է, և lookup_array-ը աճող չէ: կարգը, և եթե lookup_array-ը մեկ տող կամ սյունակ չէ:
Դուք կարող եք օգտագործել wildcard նիշը lookup_value արգումենտում, եթե match_type-ը 0 է, իսկ lookup_value-ը տեքստային տող է: Հարցական նշանը համապատասխանում է ցանկացած նիշի, իսկ աստղանիշը՝ նիշերի ցանկացած հաջորդականության (օր.g., =MATCH(«Jo», 1:1, 0)): MATCH-ն իրական հարցական նշան կամ աստղանիշ գտնելու համար օգտագործելու համար նախ մուտքագրեք ~:
INDEX-ը վերադարձնում է REF! եթե row_num-ը և column_num-ը չեն մատնանշում զանգվածի բջիջը:


Հարակից Excel-ի գործառույթներ
MATCH ֆունկցիան նման է LOOKUP-ին, բայց MATCH-ը վերադարձնում է նյութի դիրքը հենց իրի փոխարեն:
VLOOKUP-ը մեկ այլ որոնման գործառույթ է, որը կարող եք օգտագործել Excel-ում, սակայն ի տարբերություն MATCH-ի, որը պահանջում է INDEX առաջադեմ որոնումների համար, VLOOKUP բանաձևերին անհրաժեշտ է միայն այդ մեկ գործառույթը: