Ինչու INDEX-ը և MATCH-ն ավելի լավն են, քան VLOOKUP-ը Excel-ում

Մենք նախապես բացատրել ենք սկսնակներին, թե ինչպես օգտագործել VLOOKUP-ի հիմնական գործառույթները (անգլերեն VLOOKUP, հապավումը նշանակում է «ուղղահայաց որոնման գործառույթ»): Իսկ փորձառու օգտատերերին ցույց են տվել մի քանի ավելի բարդ բանաձևեր։

Եվ այս հոդվածում մենք կփորձենք տեղեկատվություն տալ ուղղահայաց որոնման հետ աշխատելու մեկ այլ մեթոդի մասին։

Դուք կարող եք մտածել. «Ինչու է դա անհրաժեշտ»: Եվ դա անհրաժեշտ է որոնման բոլոր հնարավոր մեթոդները ցուցադրելու համար։ Բացի այդ, VLOOKUP-ի բազմաթիվ սահմանափակումները հաճախ խանգարում են ստանալ ցանկալի արդյունք: Այս առումով INDEX( ) MATCH( )-ը շատ ավելի ֆունկցիոնալ ու բազմազան է, ինչպես նաև ունեն ավելի քիչ սահմանափակումներ։

Հիմունքներ INDEX MATCH

Քանի որ այս ուղեցույցի նպատակն է ցույց տալ, թե որքան լավն է այս հատկությունը, մենք Դիտարկենք հիմնական տեղեկատվությունը դրա գործունեության սկզբունքների վերաբերյալ: Եվ մենք ցույց կտանք օրինակներ, ինչպես նաև հաշվի առնենք, թե ինչու, դա ավելի լավ է, քան VLOOKUP ():

INDEX ֆունկցիայի շարահյուսություն և օգտագործում

Այս ֆունկցիան օգնում է գտնել ցանկալի արժեքը նշված որոնման տարածքներում՝ սյունակի կամ տողի համարի հիման վրա: Շարահյուսություն:

=INDEX (զանգված, տողի համար, սյունակի համար):

• զանգված - այն տարածքը, որտեղ տեղի կունենա որոնումը.
• տողի համարը - նշված զանգվածում փնտրվող տողի համարը: Եթե ​​տողի համարը անհայտ է, ապա պետք է նշվի սյունակի համարը.
• սյունակի համարը - սյունակի համարը, որը պետք է գտնվի նշված զանգվածում: Եթե ​​արժեքը անհայտ է, տողի համարը պահանջվում է:

Պարզ բանաձևի օրինակ.

=INDEX(A1:S10,2,3)

Ֆունկցիան որոնելու է A1-ից մինչև C10 միջակայքում: Թվերը ցույց են տալիս, թե որ տողից (2) և (3) սյունակից պետք է ցույց տալ ցանկալի արժեքը: Արդյունքը կլինի C2 բջիջը:

Բավականին պարզ, չէ՞: Բայց երբ աշխատում եք իրական փաստաթղթերի հետ, դժվար թե տեղեկություն ունենաք սյունակների համարների կամ բջիջների վերաբերյալ: Հենց դրա համար է MATCH() ֆունկցիան:

MATCH ֆունկցիայի շարահյուսություն և օգտագործում

MATCH() ֆունկցիան փնտրում է ցանկալի արժեքը և ցույց է տալիս դրա մոտավոր թիվը նշված որոնման տարածքում:

Searchpos() շարահյուսությունն ունի հետևյալ տեսքը.

=MATCH (որոնման արժեք, զանգված՝ փնտրելու համար, համընկնման տեսակ)

• որոնման արժեքը - համարը կամ տեքստը, որը պետք է գտնվի.
• որոնված զանգված – այն տարածքը, որտեղ տեղի կունենա որոնումը.
• համընկնման տեսակը – սահմանում է՝ փնտրել ճշգրիտ արժեքը, թե դրան ամենամոտ արժեքները.
  • 1 (կամ ոչ մի արժեք նշված) – վերադարձնում է ամենամեծ արժեքը, որը հավասար է կամ փոքր է նշված արժեքին.
  • 0 – ցույց է տալիս ճշգրիտ համընկնում փնտրված արժեքի հետ: INDEX() MATCH() կոմբինացիայի մեջ ձեզ գրեթե միշտ անհրաժեշտ կլինի ճշգրիտ համընկնում, ուստի մենք գրում ենք 0;
  • -1 – Ցույց է տալիս ամենափոքր արժեքը, որը մեծ է կամ հավասար է բանաձևում նշված արժեքին: Տեսակավորումն իրականացվում է նվազման կարգով։

Օրինակ՝ B1:B3 միջակայքում գրանցված են Նյու Յորքը, Փարիզը, Լոնդոնը։ Ստորև բերված բանաձևը ցույց կտա 3 թիվը, քանի որ Լոնդոնը երրորդն է ցուցակում.

=EXPOSE (Լոնդոն,B1:B3,0)

Ինչպես աշխատել INDEX MATCH ֆունկցիայի հետ 

Դուք հավանաբար արդեն սկսել եք հասկանալ, թե ինչ սկզբունքով է կառուցված այդ գործառույթների համատեղ աշխատանքը։ Մի խոսքով, ուրեմն INDEX()-ը որոնում է ցանկալի արժեքը նշված տողերի և սյունակների միջև: Իսկ MATCH()-ը ցույց է տալիս այս արժեքների թվերը.

=INDEX (սյունակ, որտեղից վերադարձվում է արժեքը, MATCH (արժեքը որոնման համար, սյունակը որոնելու համար, 0))

Դեռ դժվարանում եք հասկանալ, թե ինչպես է այն աշխատում: Միգուցե օրինակն ավելի լավ բացատրի։ Ենթադրենք, դուք ունեք աշխարհի մայրաքաղաքների և դրանց բնակչության ցուցակը.

Որոշակի մայրաքաղաքի, օրինակ՝ Ճապոնիայի մայրաքաղաքի բնակչության թվաքանակը պարզելու համար մենք օգտագործում ենք հետևյալ բանաձևը.

=INDEX(C2:C10, MATCH(Ճապոնիա, A2:A10,0))

Բացատրությունը.

• MATCH() ֆունկցիան փնտրում է «Japan» արժեքը A2:A10 զանգվածում և վերադարձնում է 3 թիվը, քանի որ Ճապոնիան երրորդ արժեքն է ցուցակում: 
• Այս ցուցանիշը գնում էգծի համարը” INDEX() բանաձևում և գործառույթին ասում է այս տողից արժեք տպել:

Այսպիսով, վերը նշված բանաձեւը դառնում է ստանդարտ բանաձեւ INDEX(C2:C10,3): Բանաձևը որոնում է C2-ից մինչև C10 բջիջներից և վերադարձնում տվյալներ այս տիրույթի երրորդ բջիջից, այսինքն՝ C4-ից, քանի որ հետհաշվարկը սկսվում է երկրորդ շարքից:

Չե՞ք ուզում բանաձևում նշանակել քաղաքի անունը: Այնուհետև գրեք այն ցանկացած բջիջում, ասենք F1, և օգտագործեք այն որպես հղում MATCH() բանաձևում: Եվ դուք հայտնվում եք դինամիկ որոնման բանաձևով.

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

Կարեւոր. Ներսում գտնվող տողերի քանակը դասավորություն INDEX()-ը պետք է լինի նույնը, ինչ տողերի քանակը համարվող զանգված MATCH(-ում), հակառակ դեպքում դուք սխալ արդյունք կստանաք:

Սպասեք մի րոպե, ինչու՞ չօգտագործել VLOOKUP() բանաձևը:

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

 Ի՞նչ իմաստ ունի ժամանակ վատնել՝ փորձելով պարզել INDEX MATCH-ի այս բոլոր բարդությունները:

Այս դեպքում էական չէ, թե որ ֆունկցիան օգտագործել։ Սա ընդամենը մի օրինակ է հասկանալու համար, թե ինչպես են աշխատում INDEX() և MATCH() ֆունկցիաները միասին: Այլ օրինակները ցույց կտան, թե ինչի են ունակ այս գործառույթները այն իրավիճակներում, երբ VLOOKUP-ն անզոր է: 

INDEX MATCH կամ VLOOKUP

Որոշելով, թե որ որոնման բանաձևն օգտագործել, շատերը համաձայն են, որ INDEX()-ը և MATCH()-ը զգալիորեն գերազանցում են VLOOKUP-ին: Այնուամենայնիվ, շատերը դեռ օգտագործում են VLOOKUP(). Նախ, VLOOKUP()-ն ավելի պարզ է, և երկրորդը, օգտվողները լիովին չեն հասկանում INDEX() և MATCH() հետ աշխատելու բոլոր առավելությունները: Առանց այս գիտելիքի, ոչ ոք չի համաձայնի իր ժամանակը ծախսել բարդ համակարգի ուսումնասիրության վրա:

Ահա INDEX()-ի և MATCH()-ի հիմնական առավելությունները VLOOKUP(-ի նկատմամբ):

• Որոնել աջից ձախ: VLOOKUP()-ը չի կարող որոնել աջից ձախ, այնպես որ ձեր փնտրած արժեքները միշտ պետք է լինեն աղյուսակի ամենաձախ սյունակներում: Բայց INDEX()-ը և MATCH()-ը կարող են դա անել առանց խնդիրների: Այս հոդվածը ձեզ կպատմի, թե ինչ տեսք ունի այն գործնականում. ինչպես գտնել ցանկալի արժեքը ձախ կողմում:

1. Սյուների անվտանգ ավելացում կամ հեռացում: VLOOKUP() բանաձևը ցույց է տալիս սխալ արդյունքներ սյունակներ հեռացնելիս կամ ավելացնելիս, քանի որ VLOOKUP()-ին անհրաժեշտ է ճշգրիտ սյունակի համարը հաջող լինելու համար: Բնականաբար, երբ սյունակները ավելացվում կամ հեռացվում են, դրանց թիվը նույնպես փոխվում է: 

Իսկ INDEX() և MATCH() բանաձևերում նշված է սյունակների շարք, այլ ոչ թե առանձին սյունակներ: Արդյունքում, դուք կարող եք ապահով կերպով ավելացնել և հեռացնել սյունակներ՝ առանց ամեն անգամ բանաձևը թարմացնելու:

1. Որոնման ծավալների սահմանափակումներ չկան. VLOOKUP() օգտագործելիս որոնման չափանիշների ընդհանուր թիվը չպետք է գերազանցի 255 նիշը, այլապես կստանաք #VALUE: Այսպիսով, եթե ձեր տվյալները պարունակում են մեծ թվով նիշ, INDEX() և MATCH() լավագույն տարբերակն են:
2. Մշակման բարձր արագություն: Եթե ​​ձեր սեղանները համեմատաբար փոքր են, ապա դժվար թե տարբերություն նկատեք: Բայց, եթե աղյուսակը պարունակում է հարյուրավոր կամ հազարավոր տողեր, և, համապատասխանաբար, կան հարյուրավոր և հազարավոր բանաձևեր, ապա INDEX () և MATCH () կկատարվեն շատ ավելի արագ, քան VLOOKUP (): Բանն այն է, որ Excel-ը կմշակի միայն բանաձևում նշված սյունակները՝ ամբողջ աղյուսակը մշակելու փոխարեն: 

VLOOKUP()-ի կատարողականի ազդեցությունը հատկապես նկատելի կլինի, եթե ձեր աշխատաթերթը պարունակում է մեծ թվով բանաձևեր, ինչպիսիք են VLOOKUP() և SUM(): Զանգվածի յուրաքանչյուր արժեք վերլուծելու համար պահանջվում է VLOOKUP() ֆունկցիաների առանձին ստուգումներ: Այսպիսով, Excel-ը պետք է մշակի հսկայական քանակությամբ տեղեկատվություն, և դա զգալիորեն դանդաղեցնում է աշխատանքը:

Բանաձևի օրինակներ 

Մենք արդեն պարզել ենք այս գործառույթների օգտակարությունը, ուստի կարող ենք անցնել ամենահետաքրքիր մասին՝ գիտելիքների կիրառումը գործնականում:

Աջից ձախ որոնելու բանաձև

Ինչպես արդեն նշվեց, VLOOKUP-ը չի կարող կատարել որոնման այս ձևը: Այսպիսով, եթե ցանկալի արժեքները ձախ սյունակում չեն, VLOOKUP() արդյունք չի տա: INDEX() և MATCH() ֆունկցիաներն ավելի բազմակողմանի են, և արժեքների գտնվելու վայրը մեծ դեր չի խաղում նրանց աշխատանքի համար:

Օրինակ՝ մեր աղյուսակի ձախ մասում կավելացնենք աստիճանային սյունակ և կփորձենք պարզել, թե բնակչության թվաքանակով ինչ վարկանիշ է զբաղեցնում Մեր երկրի մայրաքաղաքը։

G1 բջիջում մենք գրում ենք գտնվելիք արժեքը, այնուհետև օգտագործում ենք հետևյալ բանաձևը՝ C1:C10 միջակայքում որոնելու համար և A2:A10-ից վերադարձնում ենք համապատասխան արժեքը.

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

Հուշել. Եթե ​​նախատեսում եք օգտագործել այս բանաձևը մի քանի բջիջների համար, համոզվեք, որ շտկել եք միջակայքերը՝ օգտագործելով բացարձակ հասցեավորում (օրինակ՝ $A$2՝ $A$10 և $C$2: 4C$10).

ԻՆԴԵՔՍ ԱՎԵԼԻ ԲԱՑՎԱԾ ԱՎԵԼԻ ԲԱՑՎԱԾ  սյունակներում և տողերում որոնելու համար

Վերոնշյալ օրինակներում մենք օգտագործել ենք այս գործառույթները՝ որպես VLOOKUP()-ի փոխարինում՝ տողերի նախապես սահմանված տիրույթից արժեքներ վերադարձնելու համար: Բայց ինչ անել, եթե ձեզ անհրաժեշտ է մատրիցային կամ երկկողմանի որոնում կատարել:

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

=INDEX (զանգված, MATCH (ուղղահայաց որոնման արժեք, որոնման սյունակ, 0), MATCH (հորիզոնական որոնման արժեք, որոնման տող, 0))

Եկեք նայենք ստորև բերված աղյուսակին և փորձենք կազմել բանաձև INDEX() EXPRESS() EXPRESS() ընտրված տարվա համար կոնկրետ երկրում ժողովրդագրական տվյալները ցուցադրելու համար:

Թիրախային երկիրը գտնվում է G1 բջիջում (ուղղահայաց որոնում), իսկ նպատակային տարին՝ G2 բջիջում (հորիզոնական որոնում): Բանաձևը կունենա հետևյալ տեսքը.

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

Ինչպես է գործում այս բանաձեւը

Ինչպես ցանկացած այլ բարդ բանաձևի դեպքում, դրանք ավելի հեշտ է հասկանալ՝ դրանք բաժանելով առանձին հավասարումների: Եվ հետո դուք կարող եք հասկանալ, թե ինչ է անում յուրաքանչյուր առանձին գործառույթ.

• ՄԱՏՉ (G1,A2:A11,0) – փնտրում է արժեք (G1) A2:A11 միջակայքում և ցույց է տալիս այս արժեքի թիվը, մեր դեպքում դա 2 է;
• SEARCH (G2,B1:D1,0) – փնտրում է արժեք (G2) B1:D1 միջակայքում: Այս դեպքում արդյունքը 3 էր։

Գտնված տողերի և սյունակների համարները ուղարկվում են INDEX() բանաձևի համապատասխան արժեքին.

=INDEX(B2:D11,2,3)

Արդյունքում մենք ունենք արժեք, որը գտնվում է B2:D3 միջակայքի 2 տողերի և 11 սյունակների հատման բջիջում: Եվ բանաձևը ցույց է տալիս ցանկալի արժեքը, որը գտնվում է D3 բջիջում:

Որոնեք բազմաթիվ պայմաններով INDEX-ով և MATCH-ով

Եթե ​​կարդացել եք VLOOKUP(-ի մեր ուղեցույցը), հավանաբար փորձել եք որոնման բազմաթիվ բանաձևեր: Բայց այս որոնման մեթոդն ունի մեկ նշանակալի սահմանափակում՝ օժանդակ սյունակ ավելացնելու անհրաժեշտությունը:

Բայց լավ նորությունն այն է, որ INDEX()-ի և MATCH()-ի միջոցով դուք կարող եք որոնել բազմաթիվ պայմաններ՝ առանց ձեր աշխատաթերթը խմբագրելու կամ փոխելու:

Ահա INDEX() MATCH():

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

Նշում. այս բանաձևը պետք է օգտագործվի ստեղնաշարի դյուրանցման հետ միասին CTRL+SHIFT+ENTER:

Ենթադրենք, որ դուք պետք է գտնեք ձեր փնտրած արժեքը 2 պայմանի հիման վրա. Գնորդ и Արտադրանք:

Սա պահանջում է հետևյալ բանաձևը.

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

Այս բանաձևում C2:C10 այն տիրույթն է, որտեղ որոնումը տեղի կունենա, F1 - այս պայմանը, A2: A10 — պայմանը համեմատելու միջակայքն է, F2 - պայման 2, V2: V10 – միջակայք 2-րդ պայմանի համեմատության համար:

Մի մոռացեք սեղմել համադրությունը բանաձևով աշխատանքի վերջում CTRL+SHIFT+ENTER – Excel-ը ավտոմատ կերպով կփակի բանաձևը գանգուր փակագծերով, ինչպես ցույց է տրված օրինակում.

Եթե ​​դուք չեք ցանկանում օգտագործել զանգվածի բանաձև ձեր աշխատանքի համար, ապա բանաձևին ավելացրեք ևս մեկ INDEX() և սեղմեք ENTER, այն նման կլինի օրինակին.

Ինչպես են աշխատում այս բանաձևերը

Այս բանաձևն աշխատում է նույն կերպ, ինչ ստանդարտ INDEX() MATCH() բանաձևը: Բազմաթիվ պայմաններ որոնելու համար դուք պարզապես ստեղծում եք բազմաթիվ False և True պայմաններ, որոնք ներկայացնում են ճիշտ և սխալ անհատական ​​պայմանները: Եվ հետո այս պայմանները կիրառվում են զանգվածի բոլոր համապատասխան տարրերի վրա։ Բանաձևը փոխակերպում է False և True արգումենտները համապատասխանաբար 0-ի և 1-ի, և թողարկում է զանգված, որտեղ 1-ը համընկնող արժեքներն են, որոնք գտնվել են տողում: MATCH()-ը կգտնի 1-ին համապատասխանող առաջին արժեքը և կփոխանցի այն INDEX() բանաձևին: Եվ դա, իր հերթին, կվերադարձնի արդեն ցանկալի արժեքը նշված տողում ցանկալի սյունակից:

Առանց զանգվածի բանաձևը կախված է INDEX()-ի՝ դրանք ինքնուրույն կառավարելու կարողությունից: Բանաձևի երկրորդ INDEX()-ը համընկնում է կեղծ (0) հետ, ուստի այն փոխանցում է այդ արժեքներով ամբողջ զանգվածը MATCH() բանաձևին: 

Սա այս բանաձեւի տրամաբանության բավականին երկար բացատրությունն է: Լրացուցիչ տեղեկությունների համար կարդացեք հոդվածը «INDEX MATCH բազմաթիվ պայմաններով.

AVERAGE, MAX և MIN INDEX-ում և MATCH-ում

Excel-ն ունի իր հատուկ գործառույթները՝ գտնելու միջինները, առավելագույնը և նվազագույնը: Բայց ի՞նչ անել, եթե ցանկանում եք տվյալներ ստանալ այդ արժեքների հետ կապված բջիջից: Այս դեպքում AVERAGE-ը, MAX-ը և MIN-ը պետք է օգտագործվեն INDEX-ի և MATCH-ի հետ միասին:

INDEX MATCH և MAX

D սյունակում ամենամեծ արժեքը գտնելու և C սյունակում ցուցադրելու համար օգտագործեք բանաձևը. 

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

INDEX MATCH և MIN

D սյունակում ամենափոքր արժեքը գտնելու և C սյունակում ցուցադրելու համար օգտագործեք հետևյալ բանաձևը.

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

SEARCH INDEX և SERPENT

D սյունակում միջին արժեքը գտնելու և այս արժեքը C-ում ցուցադրելու համար.

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

Կախված նրանից, թե ինչպես են գրված ձեր տվյալները, MATCH()-ի երրորդ արգումենտը կամ 1, 0 կամ -1 է:

• եթե սյունակները դասավորված են աճման կարգով, սահմանեք 1 (այնուհետև բանաձևը կհաշվի առավելագույն արժեքը, որը փոքր է կամ հավասար է միջին արժեքին);
• եթե տեսակավորումը նվազման է, ապա -1 (բանաձևը կարտադրի նվազագույն արժեքը, որը մեծ է կամ հավասար է միջինին);
• եթե որոնման զանգվածը պարունակում է արժեք, որը ճշգրիտ հավասար է միջինին, ապա այն սահմանեք 0: 

 Մեր օրինակում բնակչությունը դասավորված է նվազման կարգով, ուստի մենք դնում ենք -1: Իսկ արդյունքը Տոկիոն է, քանի որ բնակչության թիվը (13,189) ամենամոտն է միջին արժեքին (000):

VLOOKUP()-ը կարող է նաև կատարել նման հաշվարկներ, բայց միայն որպես զանգվածի բանաձև՝ VLOOKUP՝ AVERAGE, MIN և MAX-ով:.

INDEX MATCH և ESND/IFERROR

Դուք հավանաբար արդեն նկատել եք, որ եթե բանաձևը չի կարողանում գտնել ցանկալի արժեքը, այն սխալ է թույլ տալիս # N / A. Դուք կարող եք փոխարինել ստանդարտ սխալի հաղորդագրությունը ավելի տեղեկատվական բանով: Օրինակ, սահմանեք փաստարկը բանաձևում XNUMX-րդում:

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

Այս բանաձևով, եթե մուտքագրեք տվյալներ, որոնք չկան աղյուսակում, ձևը ձեզ կտա նշված հաղորդագրությունը:

Եթե ​​ցանկանում եք բռնել բոլոր սխալները, ապա բացառությամբ XNUMX-րդում կարող են օգտագործվել ԻՐԱER:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), «Սխալ առաջացավ»)

Բայց հիշեք, որ այս կերպ սխալները քողարկելը լավ գաղափար չէ, քանի որ ստանդարտ սխալները հայտնում են բանաձևի խախտումների մասին:

Հուսով ենք, որ ձեզ համար օգտակար է եղել INDEX MATCH() ֆունկցիան օգտագործելու մեր ուղեցույցը: