Բաց թողնել հիմնական բովանդակությունը

Excel INDEX MATCH. Հիմնական և առաջադեմ որոնումներ

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


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

Նախքան INDEX և MATCH գործառույթների օգտագործումը, եկեք համոզվենք, որ մենք գիտենք, թե ինչպես կարող են INDEX- ը և MATCH- ը օգնել մեզ արժեքներ փնտրելիս:


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

The INDEX Excel- ում գործառույթը վերադարձնում է տվյալ տիրույթի արժեքը որոշակի տիրույթում: INDEX ֆունկցիայի շարահյուսությունը հետևյալն է.

=INDEX(array, row_num, [column_num])
  • դասավորություն (պարտադիր է) վերաբերում է այն տիրույթին, որտեղից ցանկանում եք վերադարձնել արժեքը:
  • տող_թիվ (պարտադիր է, եթե սյունակ_թիվ առկա է) վերաբերում է զանգվածի տողի համարին:
  • սյունակ_թիվ (ըստ ցանկության, բայց պարտադիր, եթե տող_թիվ բաց է թողնված) վերաբերում է զանգվածի սյունակի համարին:

Օրինակ՝ իմանալ Ջեֆի հաշիվըԷ, 6Ցանկում գտնվող ուսանողը կարող եք օգտագործել INDEX ֆունկցիան այսպես.

=INDEX(C2:C11,6)

excel ցուցանիշի համընկնում 01

√ Նշում. միջակայքը C2: C11 այն է, որտեղ նշված են միավորները, մինչդեռ թիվը 6 գտնում է քննության միավորը 6աշակերտ.

Այստեղ եկեք մի փոքր փորձություն անենք: Բանաձևի համար =INDEX(A1:C1,2), ի՞նչ արժեք կվերադարձնի։ --- Այո, կվերադառնա Ծննդյան ամսաթիվըԷ, 2nd արժեքը տվյալ տողում:

Այժմ մենք պետք է իմանանք, որ INDEX ֆունկցիան կարող է կատարելապես աշխատել հորիզոնական կամ ուղղահայաց միջակայքերով: Բայց ի՞նչ, եթե մեզ անհրաժեշտ լինի՝ մի քանի տողերով և սյունակներով ավելի մեծ տիրույթում արժեք վերադարձնելու համար: Դե, այս դեպքում մենք պետք է կիրառենք և՛ տողի համարը, և՛ սյունակի համարը: Օրինակ՝ պարզելու համար Ջեֆի հաշիվը աղյուսակի միջակայքում մեկ սյունակի փոխարեն մենք կարող ենք գտնել նրա միավորը a-ով 6 շարքի համարը եւ սյունակի համարը 3 է բջիջները A2-ից մինչև C11 միջով սրա նման:

=INDEX(A2:C11,6,3)

excel ցուցանիշի համընկնում 02

Այն, ինչ մենք պետք է իմանանք Excel-ում INDEX ֆունկցիայի մասին.
  • INDEX ֆունկցիան կարող է աշխատել ուղղահայաց և հորիզոնական տիրույթների հետ:
  • Եթե ​​երկուսն էլ տող_թիվ և սյունակ_թիվ օգտագործվում են փաստարկներ, տող_թիվ առաջ է գնում սյունակ_թիվ, և INDEX-ը վերականգնում է արժեքը նշվածի խաչմերուկում տող_թիվ և սյունակ_թիվ.

Այնուամենայնիվ, բազմաթիվ տողերով և սյուներով իսկապես մեծ տվյալների բազայի համար մեզ համար, իհարկե, հարմար չէ կիրառել տողի և սյունակի ճշգրիտ համարով բանաձևը: Եվ դա այն դեպքում, երբ մենք պետք է համատեղենք MATCH գործառույթի օգտագործումը:


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

Excel- ում MATCH գործառույթը վերադարձնում է թվային արժեք, տվյալ տիրույթում որոշակի տարրի գտնվելու վայրը: MATCH ֆունկցիայի շարահյուսությունը հետևյալն է.

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value (պարտադիր) վերաբերում է արժեքին, որը պետք է համապատասխանի դրան lookup_հասցե.
  • lookup_հասցե (պարտադիր է) վերաբերում է բջիջների այն տիրույթին, որտեղ ցանկանում եք որոնել MATCH:
  • համընկնում_տեսակ (ըստ ցանկության): 1, 0 or -1.
    • 1 (կանխադրված), MATCH- ը կգտնի ամենամեծ արժեքը, որը փոքր է կամ հավասար է lookup_value. Արժեքները մեջ lookup_հասցե պետք է տեղադրվի աճման կարգով:
    • 0, MATCH-ը կգտնի առաջին արժեքը, որը ճիշտ հավասար է lookup_value. Արժեքները մեջ lookup_հասցե կարող է լինել ցանկացած կարգով: (Այն դեպքերում, երբ համընկնումի տեսակը դրված է 0-ի, կարող եք օգտագործել նիշերը):
    • -1, MATCH- ը կգտնի ամենափոքր արժեքը, որը մեծ է կամ հավասար է lookup_value. Արժեքները մեջ lookup_հասցե պետք է տեղադրվի նվազման կարգով:

Օրինակ՝ իմանալ Վերայի դիրքը անունների ցանկում, կարող եք օգտագործել MATCH բանաձևը հետևյալ կերպ.

=MATCH("Vera",A2:A11,0)

excel ցուցանիշի համընկնում 3

√ Ծանոթագրություն. «4» արդյունքը ցույց է տալիս, որ «Վերա» անունը ցուցակի 4-րդ հորիզոնականում է:

Այն, ինչ մենք պետք է իմանանք Excel-ում MATCH ֆունկցիայի մասին.
  • MATCH ֆունկցիան վերադարձնում է որոնման արժեքի դիրքը որոնման զանգվածում, ոչ թե ինքնին արժեքը:
  • MATCH ֆունկցիան վերադարձնում է առաջին համընկնումը կրկնօրինակների դեպքում:
  • Ինչպես INDEX ֆունկցիան, MATCH ֆունկցիան կարող է աշխատել նաև ուղղահայաց և հորիզոնական տիրույթների հետ:
  • MATCH-ը մեծատառերի զգայուն չէ:
  • Եթե lookup_value MATCH բանաձևը տեքստի ձևով է, այն փակցրեք չակերտների մեջ:
  • Եթե lookup_value -ում չի հայտնաբերվել lookup_հասցեԷ, # N / A սխալը վերադարձվում է:

Այժմ, երբ մենք իմացանք Excel-ում INDEX և MATCH գործառույթների հիմնական կիրառությունների մասին, եկեք թևերը գլորենք և պատրաստվենք համատեղելու երկու գործառույթները:


Ինչպես համատեղել INDEX-ը և MATCH-ը Excel-ում

Խնդրում ենք տեսնել ստորև բերված օրինակը ՝ պարզելու համար, թե ինչպես կարող ենք համատեղել INDEX և MATCH գործառույթները.

Գտնել Էվելինի հաշիվը, իմանալով, որ քննության միավորները գտնվում են 3րդ սյունակ, մենք կարող ենք օգտագործեք MATCH ֆունկցիան՝ ավտոմատ կերպով որոշելու տողի դիրքը առանց այն ձեռքով հաշվելու անհրաժեշտության: Հետագայում մենք կարող ենք օգտագործել INDEX ֆունկցիան՝ առբերելու համար արժեքը նշված տողի և 3-րդ սյունակի խաչմերուկում:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

excel ցուցանիշի համընկնում 4

Դե, քանի որ բանաձևը կարող է մի փոքր բարդ թվալ, եկեք անցնենք դրա յուրաքանչյուր մասի միջով:

excel ցուցանիշի համընկնում 5

The INDEX բանաձևը պարունակում է երեք փաստարկ.

  • տող_թիվ: ՄԱՏՉ («Էվելին», A2:A11,0) տրամադրում է INDEX արժեքի տողի դիրքը «Evelyn« տիրույթում A2: A11, Որը 5.
  • սյունակ_թիվ: 3 նշում է 3րդ սյունակ INDEX-ի համար՝ միավորը զանգվածի ներսում գտնելու համար:
  • դասավորություն: A2: C11 հրահանգում է INDEX-ին վերադարձնել համապատասխան արժեքը նշված տողի և սյունակի հատման կետում՝ սկսած տիրույթից: A2-ից մինչև C11. Ի վերջո, մենք ստանում ենք արդյունքը 90.

Վերոնշյալ բանաձևում մենք օգտագործել ենք կոշտ կոդավորված արժեք, «Էվելին». Այնուամենայնիվ, գործնականում կոշտ կոդավորված արժեքներն անիրագործելի են, քանի որ դրանք փոփոխության կպահանջեն ամեն անգամ, երբ մենք փնտրում ենք տարբեր տվյալներ, օրինակ՝ մեկ այլ ուսանողի գնահատականը: Նման սցենարներում մենք կարող ենք օգտագործել բջջային հղումներ՝ դինամիկ բանաձևեր ստեղծելու համար: Օրինակ, այս դեպքում ես կանեմ փոխել «Էվելին»-ը F2-ի:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

(AD) Պարզեցրեք որոնումները Kutools-ի միջոցով. բանաձևի մուտքագրում չի պահանջվում:

Excel- ի համար նախատեսված գործիքներ's Super Փնտրել տրամադրում մի շարք որոնման գործիքներ հարմարեցված է բավարարելու ձեր յուրաքանչյուր կարիքը: Անկախ նրանից, թե դուք կատարում եք բազմաթիվ չափանիշների որոնումներ, որոնում եք բազմաթիվ թերթերում կամ կատարում եք մեկից շատ որոնումներ, Super Փնտրել պարզեցնում է գործընթացը ընդամենը մի քանի կտտոցով: Ուսումնասիրեք այս հատկանիշները տեսնել, թե ինչպես Super Փնտրել փոխում է Excel-ի տվյալների հետ ձեր փոխազդեցության ձևը: Հրաժեշտ տվեք բարդ բանաձևերը հիշելու դժվարությանը:

Kutools Փնտրման գործիքներ

Excel- ի համար նախատեսված գործիքներ - Ձեզ հզորացնելով ավելի քան 300 հարմար գործառույթներ՝ հեշտ արտադրողականության համար: Բաց մի թողեք այն փորձելու ձեր հնարավորությունը 30-օրյա լիարժեք հնարավորություններով անվճար փորձաշրջանով: Սկսում ենք հիմա!


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

Այս մասում մենք կխոսենք տարբեր հանգամանքների մասին `INDEX և MATCH գործառույթներն օգտագործելու համար` տարբեր կարիքներ բավարարելու համար:


INDEX և MATCH ՝ երկկողմանի որոնում կիրառելու համար

Նախորդ օրինակում մենք գիտեինք սյունակի համարը և օգտագործեցինք MATCH բանաձևը՝ տողի համարը գտնելու համար: Բայց ի՞նչ, եթե մենք նույնպես վստահ չենք սյունակի համարի հարցում:

Նման դեպքերում մենք կարող ենք կատարել երկկողմանի որոնում, որը նաև հայտնի է որպես մատրիցային որոնում, օգտագործելով երկու MATCH ֆունկցիա՝ մեկը գտնելու տողի համարը, իսկ մյուսը՝ որոշելու սյունակի համարը: Օրինակ՝ իմանալ Էվելինի հաշիվը, մենք պետք է օգտագործենք բանաձևը.

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

excel ցուցանիշի համընկնում 6

Ինչպես է աշխատում այս բանաձևը.
  • Առաջին MATCH բանաձևը գտնում է Էվելինի գտնվելու վայրը A2:A11 ցուցակում՝ ապահովելով 5 որպես INDEX տողի համար:
  • Երկրորդ MATCH բանաձևը որոշում է միավորների և վերադարձի սյունակը 3 որպես INDEX-ի սյունակի համար:
  • Բանաձևը պարզեցնում է =INDEX(A2:C11,5,3), և INDEX-ը վերադառնում է 90.

INDEX և MATCH՝ ձախ որոնում կիրառելու համար

Հիմա եկեք դիտարկենք մի սցենար, որտեղ դուք պետք է որոշեք Էվելինի դասը: Դուք կարող եք նկատել, որ դասի սյունակը տեղադրված է անվանման սյունակի ձախ կողմում, իրավիճակ, որը գերազանցում է Excel-ի մեկ այլ հզոր որոնման ֆունկցիայի՝ VLOOKUP-ի հնարավորությունները:

Փաստորեն, ձախակողմյան որոնումներ կատարելու ունակությունը այն ասպեկտներից մեկն է, որտեղ INDEX-ի և MATCH-ի համադրությունը գերազանցում է VLOOKUP-ին:

Գտնել Էվելինի դասարան, կիրառել հետևյալ բանաձևը որոնել Էվելին B2:B11-ում և ստանալ համապատասխան արժեքը A2:A11-ից.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

excel ցուցանիշի համընկնում 7

Նշում: Դուք կարող եք հեշտությամբ կատարել ձախ որոնում հատուկ արժեքների համար՝ օգտագործելով Փնտրեք աջից ձախ առանձնահատկությունը Excel- ի համար նախատեսված գործիքներ ընդամենը մի քանի կտտոցով: Գործառույթն իրականացնելու համար անցեք դեպի Կուտոլս ներդիր ձեր Excel-ում և սեղմեք Super Փնտրել > Փնտրեք աջից ձախ է Ֆորմուլա խումբ:

Փնտրեք աջից ձախ

Եթե ​​դուք չեք տեղադրել Kutools-ը, սեղմեք այստեղ, որպեսզի ներբեռնեք և ստացեք 30-օրյա լիարժեք հնարավորություններով անվճար փորձարկում:


INDEX և MATCH ՝ գործերի նկատմամբ զգայուն որոնում կիրառելու համար

MATCH ֆունկցիան իր էությամբ մեծատառերի նկատմամբ զգայուն չէ: Այնուամենայնիվ, երբ դուք պահանջում եք ձեր բանաձևը տարբերակել մեծատառ և փոքրատառ նիշերը, կարող եք բարելավել այն՝ ներառելով EXACT- ը ֆունկցիան։ Համատեղելով MATCH ֆունկցիան EXACT-ի հետ INDEX բանաձևով, դուք կարող եք արդյունավետորեն կատարել մեծատառերի նկատմամբ զգայուն որոնում, ինչպես ցույց է տրված ստորև.

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))
  • դասավորություն վերաբերում է այն միջակայքին, որտեղից ցանկանում եք վերադարձնել արժեքը:
  • lookup_value վերաբերում է արժեքին, որը պետք է համապատասխանի, հաշվի առնելով նիշերի դեպքը, մեջ lookup_հասցե.
  • lookup_հասցե վերաբերում է բջիջների շրջանակին, որտեղ դուք ցանկանում եք, որ MATCH-ը համեմատվի lookup_value.

Օրինակ՝ իմանալ JIMMY-ի քննության միավորը, օգտագործեք հետևյալ բանաձևը.

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Նշում. Սա զանգվածի բանաձև է, որը պահանջում է մուտքագրել Ctrl + Հերթափոխություն + Մտնել, բացառությամբ Excel 365-ի և Excel 2021-ի:

excel ցուցանիշի համընկնում 8

Ինչպես է աշխատում այս բանաձևը.
  • EXACT ֆունկցիան համեմատում է «IMԻՄԻ» ցուցակի արժեքներով A2: A11, հաշվի առնելով նիշերի դեպքը. Եթե երկու տողերը ճշգրիտ համընկնում են՝ հաշվի առնելով և՛ մեծատառերը, և՛ փոքրատառերը, EXACT-ը վերադարձնում է TRUE; հակառակ դեպքում այն ​​վերադառնում է ԿԵՂԾ. Արդյունքում մենք ստանում ենք TRUE և FALSE արժեքներ պարունակող զանգված.
  • MATCH ֆունկցիան այնուհետև առբերում է առաջին TRUE արժեքի դիրքը զանգվածում, որը պետք է լինի 10.
  • Ի վերջո, INDEX-ը վերականգնում է արժեքը 10MATCH-ի կողմից տրված դիրքը զանգվածում:

Նշումներ:

  • Հիշեք, որ բանաձևը ճիշտ մուտքագրեք՝ սեղմելով Ctrl + Shift + Մուտք, եթե չես օգտագործում Excel 365 or Excel 2021, որի դեպքում պարզապես սեղմեք Մտնել.
  • Վերոնշյալ բանաձևը որոնում է մեկ ցուցակում C2: C11. Եթե ​​ցանկանում եք որոնել մի շարք սյունակներով և տողերով, ասեք A2: C11, դուք պետք է առաջարկեք և՛ սյունակների, և՛ տողերի համարներ INDEX-ին.
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • Այս վերանայված բանաձևում մենք օգտագործում ենք MATCH ֆունկցիան՝ «JIMMY» որոնելու համար՝ հաշվի առնելով նիշերի դեպքը, տիրույթում: A2: A11, և երբ մենք գտնում ենք համընկնում, մենք ստանում ենք համապատասխան արժեքը the-ից 3տիրույթի երրորդ սյունակ A2: C11.

INDEX և MATCH՝ ամենամոտ համընկնում գտնելու համար

Excel-ում դուք կարող եք հանդիպել այնպիսի իրավիճակների, երբ դուք պետք է գտնեք ամենամոտ կամ մոտակա համընկնումը որոշակի արժեքի տվյալների բազայում: Նման սցենարներում INDEX և MATCH ֆունկցիաների համակցումը ABS և MIN ֆունկցիաների հետ միասին կարող է աներևակայելի օգտակար լինել:

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • դասավորություն վերաբերում է այն միջակայքին, որտեղից ցանկանում եք վերադարձնել արժեքը:
  • lookup_հասցե վերաբերում է արժեքների այն տիրույթին, որտեղ դուք ցանկանում եք գտնել ամենամոտ համընկնում lookup_value.
  • lookup_value վերաբերում է արժեքին՝ իր ամենամոտ համընկնում գտնելու համար:

Օրինակ ՝ պարզելու համար որի միավորը ամենամոտն է 85-ին, օգտագործեք հետևյալ բանաձևը որոնել 85-ին ամենամոտ միավորը C2:C11-ում և ստանալ համապատասխան արժեքը A2:A11-ից.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Նշում. Սա զանգվածի բանաձև է, որը պահանջում է մուտքագրել Ctrl + Հերթափոխություն + Մտնել, բացառությամբ Excel 365-ի և Excel 2021-ի:

Ինչպես է աշխատում այս բանաձևը.
  • ABS (C2:C11-85) հաշվարկում է տիրույթի յուրաքանչյուր արժեքի բացարձակ տարբերությունը C2: C11 և 85, որի արդյունքում առաջանում է բացարձակ տարբերությունների զանգված:
  • MIN(ABS(C2:C11-85)) բացարձակ տարբերությունների զանգվածում գտնում է նվազագույն արժեքը, որը ներկայացնում է 85-ի ամենամոտ տարբերությունը:
  • MATCH գործառույթը MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) ապա գտնում է նվազագույն բացարձակ տարբերության դիրքը բացարձակ տարբերությունների զանգվածում, որը պետք է լինի 10.
  • Վերջապես, INDEX-ը վերցնում է ցուցակի դիրքի արժեքը A2: A11 որը համապատասխանում է ամենամոտ միավորին 85 միջակայքում C2: C11.

Նշումներ:

  • Հիշեք, որ բանաձևը ճիշտ մուտքագրեք՝ սեղմելով Ctrl + Shift + Մուտք, եթե չես օգտագործում Excel 365 or Excel 2021, որի դեպքում պարզապես սեղմեք Մտնել.
  • Ոչ-ոքի արդյունքի դեպքում այս բանաձեւը կվերադարձնի առաջին հանդիպումը։
  • Գտնել միջին միավորին ամենամոտ համընկնում, փոխարինել 85 հետ բանաձեւում ՄԻՋԻՆ (C2:C11).

INDEX և MATCH ՝ բազմաթիվ չափանիշներով որոնում կիրառելու համար

Մի քանի պայմաններին համապատասխանող արժեք գտնելու համար, որը պահանջում է որոնել երկու կամ ավելի սյունակներում, օգտագործեք հետևյալ բանաձևը. Բանաձևը թույլ է տալիս կատարել բազմաչափ չափանիշների որոնում՝ տարբեր սյունակներում նշելով տարբեր պայմաններ՝ օգնելով գտնել ցանկալի արժեքը, որը համապատասխանում է նշված բոլոր չափանիշներին:

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…), 0))

√ Նշում. Սա զանգվածի բանաձև է, որը պահանջում է մուտքագրել Ctrl + Հերթափոխություն + Մտնել. Հետո մի զույգ գանգուր փակագծեր կհայտնվեն բանաձևի բարում:

  • դասավորություն վերաբերում է այն միջակայքին, որտեղից ցանկանում եք վերադարձնել արժեքը:
  • (lookup_value=որոնման_զանգված) ներկայացնում է մեկ պայման. Այս պայմանը ստուգում է, եթե կոնկրետ lookup_value համապատասխանում է արժեքներին lookup_հասցե.

Օրինակ՝ գտնելու համար Ա դասի Կոկո միավորը, որի ծննդյան ամսաթիվը 7/2/2008 է, կարող եք օգտագործել հետևյալ բանաձևը.

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

excel ցուցանիշի համընկնում 9

Նշումներ:

  • Այս բանաձևում մենք խուսափում ենք կոշտ կոդավորման արժեքներից՝ հեշտացնելով տարբեր տեղեկություններով միավոր ստանալը՝ փոփոխելով արժեքները բջիջներում: G2, G3, եւ G4.
  • Դուք պետք է մուտքագրեք բանաձևը սեղմելով Ctrl + Shift + Մուտք բացառությամբ ներս Excel 365 or Excel 2021, որտեղ կարող եք պարզապես սեղմել Մտնել.
    Եթե ​​անընդհատ մոռանում եք օգտագործել Ctrl + Shift + Մուտք բանաձևը լրացնելու և սխալ արդյունքներ ստանալու համար օգտագործեք հետևյալ մի փոքր ավելի բարդ բանաձևը, որով կարող եք լրացնել պարզ. Մտնել բանալի:
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • Բանաձևերը կարող են լինել բարդ և դժվար հիշելու համար: Բազմաչափերի որոնումները պարզեցնելու համար առանց ձեռքով բանաձևի մուտքագրման անհրաժեշտության, օգտագործեք Excel- ի համար նախատեսված գործիքներ'S Բազմապայմանների որոնում հատկանիշ. Kutools-ը տեղադրելուց հետո անցեք դեպի Կուտոլս ներդիր ձեր Excel-ում և սեղմեք Super Փնտրել > Բազմապայմանների որոնում է Ֆորմուլա խումբ:

    Բազմապայմանների որոնում

    Եթե ​​դուք չեք տեղադրել Kutools-ը, սեղմեք այստեղ, որպեսզի ներբեռնեք և ստացեք 30-օրյա լիարժեք հնարավորություններով անվճար փորձարկում:


INDEX և MATCH՝ մի քանի սյունակներում որոնում կիրառելու համար

Պատկերացրեք մի սցենար, որտեղ դուք գործ ունեք տվյալների բազմաթիվ սյունակների հետ: Առաջին սյունակը գործում է որպես բանալին՝ մյուս սյունակներում տվյալները դասակարգելու համար: Որոշակի մուտքի կատեգորիան կամ դասակարգումը որոշելու համար դուք պետք է որոնեք տվյալների սյունակներում և կապեք այն հղումային սյունակում համապատասխան բանալիի հետ:

Օրինակ՝ ստորև բերված աղյուսակում ինչպե՞ս կարող ենք աշակերտ Շոնին համապատասխանեցնել իր համապատասխան դասարանին՝ օգտագործելով INDEX և MATCH: Դե, դուք կարող եք դրան հասնել բանաձևով, բայց բանաձևը բավականին ընդարձակ է և կարող է դժվար լինել հասկանալը, էլ չասած հիշելու և տպելու համար:

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Ահա թե որտեղ Excel- ի համար նախատեսված գործիքներ's Ինդեքս և համընկնում մի քանի սյունակների վրա հատկությունը գալիս է հարմար: Այն հեշտացնում է գործընթացը՝ արագ և հեշտ դարձնելով համապատասխան գրառումները համապատասխան կատեգորիաների հետ: Այս հզոր գործիքը բացելու և Շոնին իր դասի հետ առանց ջանքերի համադրելու համար պարզապես ներբեռնեք և տեղադրեք Kutools Excel-ի հավելումը, և ապա արեք հետևյալ կերպ.

  1. Ընտրեք նպատակակետ բջիջը, որտեղ ցանկանում եք ցուցադրել համապատասխան դասը:
  2. Մասին Կուտոլս էջանշանը, սեղմեք Բանաձևի օգնական > Փնտրում և տեղեկանք > Ինդեքս և համընկնում մի քանի սյունակների վրա.
  3. excel ցուցանիշի համընկնում 11
  4. Բացվող երկխոսության վանդակում կատարեք հետևյալը.
    1. Սեղմեք 1-ին excel ինդեքսի համընկնման պատկերակ կոճակը կողքին Փնտրել_կոլ ընտրել այն սյունակը, որը պարունակում է հիմնական տեղեկատվությունը, որը ցանկանում եք վերադարձնել, այսինքն՝ դասերի անունները: (Այստեղ կարող եք ընտրել միայն մեկ սյունակ):
    2. Սեղմեք 2-րդը excel ինդեքսի համընկնման պատկերակ կոճակը կողքին Աղյուսակ_ջանգ ընտրված արժեքներին համապատասխանող բջիջները ընտրելու համար Փնտրել_կոլ, այսինքն՝ ուսանողների անունները։
    3. Սեղմեք 3-րդը excel ինդեքսի համընկնման պատկերակ կոճակը կողքին Փնտրման_ արժեք ընտրելու համար աշակերտի անունը պարունակող բջիջը, որը ցանկանում եք համապատասխանեցնել նրա դասին, այս դեպքում՝ Շոնին:
    4. Սեղմել OK.
    5. excel ցուցանիշի համընկնում 12

Արդյունք

Kutools-ը ավտոմատ կերպով ստեղծել է բանաձևը, և ​​դուք անմիջապես կտեսնեք Շոնի դասի անունը, որը ցուցադրվում է նպատակակետ բջիջում:

Նշում: Փորձելու համար Ինդեքս և համընկնում մի քանի սյունակների վրա առանձնահատկությունը, ձեզ հարկավոր է ձեր համակարգչում տեղադրված Kutools Excel-ի համար: Եթե ​​դեռ չեք տեղադրել այն, մի սպասեք --- Ներբեռնեք և տեղադրեք այն հիմա 30-օրյա անվճար փորձարկման համար՝ առանց սահմանափակումների. Դարձրեք Excel-ի աշխատանքը ավելի խելացի այսօր:


INDEX և MATCH՝ առաջին ոչ դատարկ արժեք փնտրելու համար

Առաջին ոչ դատարկ արժեքը, անտեսելով սխալները, սյունակից կամ տողից, կարող եք օգտագործել INDEX և MATCH ֆունկցիաների վրա հիմնված բանաձև: Այնուամենայնիվ, եթե դուք չեք ցանկանում անտեսել ձեր տիրույթի սխալները, ավելացրեք ISBLANK գործառույթը:

  • Ստացեք առաջին ոչ դատարկ արժեքը սյունակում կամ տողում՝ անտեսելով սխալները.
  • =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))
  • Ստացեք առաջին ոչ դատարկ արժեքը սյունակում կամ տողում՝ ներառյալ սխալները.
  • =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

Նշումներ:


INDEX և MATCH՝ առաջին թվային արժեքը փնտրելու համար

Սյունակից կամ տողից առաջին թվային արժեքը ստանալու համար օգտագործեք INDEX, MATCH և ISNUMBER ֆունկցիաների վրա հիմնված բանաձևը:

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

Նշումներ:


INDEX և MATCH՝ MAX կամ MIN ասոցիացիաներ փնտրելու համար

Եթե ​​Ձեզ անհրաժեշտ է առբերել արժեք, որը կապված է տիրույթում առավելագույն կամ նվազագույն արժեքի հետ, կարող եք օգտագործել MAX կամ MIN ֆունկցիաները INDEX և MATCH գործառույթների հետ միասին:

  • INDEX և MATCH՝ առավելագույն արժեքի հետ կապված արժեք ստանալու համար.
  • =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
  • INDEX և MATCH՝ նվազագույն արժեքի հետ կապված արժեք ստանալու համար.
  • =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
  • Վերոնշյալ բանաձևերում կան երկու փաստարկներ.
    • դասավորություն վերաբերում է այն տիրույթին, որտեղից ցանկանում եք վերադարձնել առնչվող տեղեկատվությունը:
    • lookup_հասցե ներկայացնում է արժեքների մի շարք, որոնք պետք է ուսումնասիրվեն կամ որոնվեն հատուկ չափանիշների համար, այսինքն՝ առավելագույն կամ նվազագույն արժեքներ:

Օրինակ, եթե ցանկանում եք որոշել ով ունի ամենաբարձր միավորը, կիրառել հետևյալ բանաձևը.

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Ինչպես է աշխատում այս բանաձևը.
  • MAX (C2:C11) որոնում է տիրույթում ամենաբարձր արժեքը C2: C11, Որը 96.
  • Այնուհետև MATCH ֆունկցիան գտնում է զանգվածի ամենաբարձր արժեքի դիրքը C2: C11, որը պետք է լինի 1.
  • Վերջապես, INDEX-ը առբերում է 1st արժեքը ցուցակում A2: A11.

Նշումներ:

  • Մեկից ավելի առավելագույն կամ նվազագույն արժեքների դեպքում, ինչպես երևում է վերը նշված օրինակում, որտեղ երկու ուսանող ստացել են նույն ամենաբարձր միավորը, այս բանաձևը կվերադարձնի առաջին համընկնում:
  • Որոշելու համար, թե ով ունի ամենացածր միավորը, օգտագործեք հետևյալ բանաձևը.
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))

Հուշում. հարմարեցրեք ձեր սեփական #N/A սխալ հաղորդագրությունները

Excel-ի INDEX և MATCH ֆունկցիաների հետ աշխատելիս կարող եք հանդիպել #N/A սխալի հետ, երբ համապատասխան արդյունք չկա: Օրինակ՝ ստորև բերված աղյուսակում Սամանթա անունով ուսանողուհու գնահատականը գտնելիս հայտնվում է #N/A սխալ, քանի որ նա չկա տվյալների հավաքածուում:

excel ցուցանիշի համընկնում 15

Ձեր աղյուսակներն ավելի հարմարավետ դարձնելու համար կարող եք հարմարեցնել այս սխալի հաղորդագրությունը՝ փաթեթավորելով ձեր INDEX MATCH բանաձևը IFNA ֆունկցիայի մեջ.

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

excel ցուցանիշի համընկնում 16

Նշումներ:

  • Դուք կարող եք հարմարեցնել ձեր սխալի հաղորդագրությունները՝ փոխարինելով "Չի գտնվել" ձեր ընտրած ցանկացած տեքստով:
  • Եթե ​​ցանկանում եք կարգավորել բոլոր սխալները, ոչ միայն #N/A, մտածեք օգտագործելու մասին ԻՐԱER ֆունկցիայի փոխարեն IFNA:
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    Նկատի ունեցեք, որ հնարավոր է, որ նպատակահարմար չլինի ճնշել բոլոր սխալները, քանի որ դրանք ծառայում են որպես զգուշացումներ ձեր բանաձևերում հնարավոր խնդիրների համար:

Վերևում ներկայացված է Excel-ում INDEX և MATCH գործառույթների հետ կապված բոլոր համապատասխան բովանդակությունը: Հուսով եմ, որ ձեզ օգտակար կլինի ձեռնարկը: Եթե ​​ցանկանում եք ուսումնասիրել Excel-ի ավելի շատ խորհուրդներ և հնարքներ, խնդրում ենք սեղմել այստեղ: մուտք գործելու հազարավոր ձեռնարկներից բաղկացած մեր լայնածավալ հավաքածուն: