VLOOKUP ֆունկցիան Excel-ի ամենահայտնի գործառույթներից է: Այս ձեռնարկը կներկայացնի, թե ինչպես օգտագործել VLOOKUP ֆունկցիան Excel-ում տասնյակ հիմնական և առաջադեմ օրինակներով քայլ առ քայլ:

VLOOKUP գործառույթի ներդրում. Շարահյուսություն և փաստարկներ

Excel-ում VLOOKUP ֆունկցիան հզոր ֆունկցիա է Excel-ի օգտատերերի մեծամասնության համար, այն թույլ է տալիս արժեք փնտրել տվյալների տիրույթի ձախ մասում և վերադարձնել համապատասխան արժեք նույն տողում այն ​​սյունակից, որը դուք նշել եք հետևյալ սքրինշոթի տեսքով: .

VLOOKUP գործառույթի շարահյուսությունը.

Արձանագրություններ.

Ֆունկցիայի նշումներ.

VLOOKUP- ի հիմնական օրինակներ

Այս բաժնում մենք կխոսենք Vlookup որոշ բանաձևերի մասին, որոնք դուք հաճախ եք օգտագործել:

2.1 Ճշգրիտ համընկնում և մոտավոր համընկնում VLOOKUP

 2.1.1 Կատարեք ճշգրիտ համընկնում VLOOKUP

Սովորաբար, եթե դուք փնտրում եք ճշգրիտ համապատասխանություն VLOOKUP ֆունկցիայի հետ, պարզապես անհրաժեշտ է օգտագործել FALSE-ը որպես վերջին փաստարկ:

Օրինակ, հատուկ ID համարների հիման վրա մաթեմատիկայի համապատասխան միավորներ ստանալու համար կատարեք հետևյալ կերպ.

Խնդրում ենք պատճենեք և տեղադրեք ստորև բերված բանաձևը դատարկ բջիջի մեջ (այստեղ ես ընտրում եմ G2) և սեղմեք Մտնել արդյունքը ստանալու բանալին.

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Ինչպե՞ս է աշխատում VLOOKUP բանաձևը:

Նախ, այն փնտրում է ID - C1005 աղյուսակի ամենաձախ սյունակում: Այն անցնում է վերևից ներքև և գտնում է արժեքը A6 բջիջում:

Հենց որ գտնում է արժեքը, երրորդ սյունակում գնում է աջ և հանում դրա արժեքը։

Այսպիսով, դուք կստանաք արդյունքը, ինչպես ցույց է տրված ստորև նշված սքրինշոթը.

 2.1.2 Կատարեք մոտավոր համընկնում VLOOKUP

Մոտավոր համընկնումն օգտակար է տվյալների տիրույթների միջև արժեքներ որոնելու համար: Եթե ​​ճշգրիտ համընկնում չգտնվի, մոտավոր VLOOKUP-ը կվերադարձնի ամենամեծ արժեքը, որը փոքր է որոնման արժեքից:

Օրինակ, եթե դուք ունեք տվյալների հետևյալ տիրույթը, և նշված պատվերները Պատվերների սյունակում չեն, ինչպե՞ս ստանալ B սյունակի ամենամոտ Զեղչը:

Քայլ 1. Կիրառեք VLOOKUP բանաձևը և լրացրեք այն այլ բջիջներում

Պատճենեք և տեղադրեք հետևյալ բանաձևը մի բջիջի մեջ, որտեղ ցանկանում եք տեղադրել արդյունքը, այնուհետև քաշեք լրացման բռնակը ներքև՝ այս բանաձևը այլ բջիջների վրա կիրառելու համար:

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Արդյունքը:

Այժմ դուք կստանաք մոտավոր համընկնումներ՝ հիմնված տրված արժեքների վրա, տես սքրինշոթը.

2.2 Excel-ում կատարեք մեծատառերի զգայուն VLOOKUP

Լռելյայնորեն, VLOOKUP ֆունկցիան կատարում է մեծատառերի որոնում, ինչը նշանակում է, որ այն վերաբերվում է փոքրատառ և մեծատառ նիշերին որպես նույնական: Երբեմն կարող է անհրաժեշտ լինել Excel-ում մեծատառերի նկատմամբ զգայուն որոնում կատարել, նորմալ VLOOKUP ֆունկցիան կարող է չլուծել այն: Այս դեպքում կարող եք օգտագործել այլընտրանքային գործառույթներ, ինչպիսիք են INDEX-ը և MATCH-ը EXACT ֆունկցիայի հետ, կամ ՓՆՏՐԵԼ և EXACT ֆունկցիաները:

Օրինակ, ես ունեմ հետևյալ տվյալների տիրույթը, որը ID սյունը պարունակում է տեքստային տող `մեծատառով կամ փոքրատառով, այժմ ուզում եմ վերադարձնել տրված նույնականացման համարի համապատասխան մաթեմատիկական գնահատականը:

Քայլ 1. Կիրառեք ցանկացած բանաձև և լրացրեք այն այլ բջիջներում

Խնդրում ենք պատճենել և տեղադրել ստորև բերված բանաձևերից որևէ մեկը դատարկ բջիջի մեջ, որտեղ ցանկանում եք ստանալ արդյունքը: Այնուհետև ընտրեք բանաձևի բջիջը, քաշեք լրացման բռնակը դեպի այն բջիջները, որտեղ ցանկանում եք լրացնել այս բանաձևը:

Formula 1: Բանաձևը կպցնելուց հետո սեղմեք Ctrl + Shift + Մուտք ստեղները:

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formula 2: Բանաձևը կպցնելուց հետո սեղմեք Մտնել բանալի.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Արդյունքը:

Այնուհետև դուք կստանաք ձեզ անհրաժեշտ ճիշտ արդյունքներ: Տես սքրինշոթը.

2.3 VLOOKUP արժեքները աջից ձախ Excel-ում

VLOOKUP ֆունկցիան միշտ որոնում է արժեք տվյալների տիրույթի ամենաձախ սյունակում և վերադարձնում համապատասխան արժեքը սյունակից աջ: Եթե ​​ցանկանում եք կատարել հակադարձ VLOOKUP, որը նշանակում է որոնել որոշակի արժեք աջ սյունակում և վերադարձնել դրա համապատասխան արժեքը ձախ սյունակում, ինչպես ցույց է տրված ստորև ներկայացված սքրինշոթը.

Կտտացրեք ՝ այս գործի վերաբերյալ մանրամասները քայլ առ քայլ իմանալու համար

2.4 VLOOKUP երկրորդ, n-րդ կամ վերջին համապատասխան արժեքը Excel-ում

Սովորաբար, եթե Vlookup ֆունկցիան օգտագործելիս գտնվեն մի քանի համապատասխան արժեքներ, կվերադարձվի միայն առաջին համապատասխանող գրառումը: Այս բաժնում ես կխոսեմ այն ​​մասին, թե ինչպես կարելի է ստանալ երկրորդ, n-րդ կամ վերջին համապատասխան արժեքը տվյալների տիրույթում:

 2.4.1 VLOOKUP և վերադարձրեք երկրորդ կամ n-րդ համապատասխան արժեքը

Ենթադրենք, որ դուք ունեք անունների ցանկ A սյունակում, վերապատրաստման դասընթացը, որը նրանք գնել են սյունակ B-ում: Այժմ դուք փնտրում եք գտնել տվյալ հաճախորդի կողմից գնված 2-րդ կամ n-րդ դասընթացը: Տես սքրինշոթը.

Այստեղ VLOOKUP ֆունկցիան կարող է ուղղակիորեն չլուծել այս խնդիրը: Բայց որպես այլընտրանք կարող եք օգտագործել INDEX ֆունկցիան:

Քայլ 1. Կիրառեք և լրացրեք բանաձևը այլ բջիջներում

Օրինակ՝ տրված չափանիշների հիման վրա երկրորդ համապատասխանող արժեքը ստանալու համար խնդրում ենք կիրառել հետևյալ բանաձևը դատարկ բջիջի մեջ և սեղմել Ctrl + Shift + Մուտք բանալիները միասին՝ առաջին արդյունքը ստանալու համար: Եվ այնուհետև ընտրեք բանաձևի բջիջը, քաշեք լրացման բռնակը դեպի այն բջիջները, որտեղ ցանկանում եք լրացնել այս բանաձևը:

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Արդյունքը:

Այժմ բոլոր երկրորդ համընկնող արժեքները՝ հիմնված տրված անունների վրա, միանգամից ցուցադրվել են:

 2.4.2 VLOOKUP և վերադարձրեք վերջին համապատասխան արժեքը

Եթե ​​ցանկանում եք դիտել և վերադարձնել համապատասխանության վերջին արժեքը, ինչպես ցույց է տրված նկարում, VLOOKUP և վերադարձրեք վերջին համապատասխան արժեքը ձեռնարկը կարող է օգնել ձեզ մանրամասնորեն ստանալ վերջին համապատասխանող արժեքը:

2.5 VLOOKUP համապատասխանող արժեքներ երկու տրված արժեքների կամ ամսաթվերի միջև

Երբեմն, գուցե ցանկանաք արժեքներ փնտրել երկու արժեքների կամ ամսաթվերի միջև և վերադարձնել համապատասխան արդյունքները, ինչպես ցույց է տրված ստորև ներկայացված սքրինշոթում: Նման դեպքում կարող եք տեսակավորված աղյուսակով VLOOKUP ֆունկցիայի փոխարեն օգտագործել LOOKUP ֆունկցիան։

 2.5.1 VLOOKUP համընկնող արժեքներ երկու տրված արժեքների կամ ամսաթվերի միջև բանաձևով

Քայլ 1. Դասավորեք տվյալները և կիրառեք հետևյալ բանաձևը

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

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Արդյունքը:

Եվ հիմա, դուք կստանաք բոլոր համապատասխան գրառումները՝ հիմնված տվյալ արժեքի վրա, տես սքրինշոթը.

 2.5.2 VLOOKUP արժեքների համապատասխանեցում երկու տրված արժեքների կամ ամսաթվերի միջև՝ հարմար հատկանիշով

Եթե ​​դժվարանում եք հիշել և հասկանալ վերը նշված բանաձևը, այստեղ ես կներկայացնեմ հեշտ գործիք. Excel- ի համար նախատեսված գործիքներԻր Փնտրեք երկու արժեքների միջև հնարավորությունը, դուք կարող եք հեշտությամբ վերադարձնել համապատասխան տարրը՝ հիմնվելով երկու արժեքների կամ ամսաթվերի միջև եղած կոնկրետ արժեքի կամ ամսաթվի վրա:

1. Սեղմել Կուտոլս > Super Փնտրել > Փնտրեք երկու արժեքների միջև այս հնարավորությունը միացնելու համար:
2. Այնուհետև նշեք գործողությունները երկխոսության տուփից՝ ձեր տվյալների հիման վրա:

2.6 VLOOKUP ֆունկցիայի մասնակի համընկնումների համար վիրաբուժական նշանների օգտագործումը

Excel-ում wildcards-ը կարող է օգտագործվել VLOOKUP ֆունկցիայի շրջանակներում, որը թույլ է տալիս մասնակի համընկնում կատարել որոնման արժեքի վրա: Օրինակ, դուք կարող եք օգտագործել VLOOKUP-ը՝ որոնման արժեքի մի մասի վրա հիմնված աղյուսակից համապատասխան արժեք վերադարձնելու համար:

Ենթադրելով, որ ես ունեմ տվյալների մի շարք, ինչպես ցույց է տրված սքրինշոթից ներքևում, այժմ ուզում եմ արդյունքը հանել ՝ ելնելով անունից (ոչ լրիվ անունից): Ինչպե՞ս կարող է լուծել այս խնդիրը Excel- ում:

Քայլ 1. Կիրառեք և լրացրեք բանաձևը այլ բջիջներում

Խնդրում ենք պատճենել կամ մուտքագրել հետևյալ բանաձևը դատարկ բջիջի մեջ, այնուհետև քաշել լրացման բռնակը այս բանաձևը լրացնելու համար ձեզ անհրաժեշտ այլ բջիջներում.

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Արդյունքը:

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

2.7 VLOOKUP արժեքներ մեկ այլ աշխատաթերթից

Սովորաբար, դուք կարող եք ստիպված լինել աշխատել մեկից ավելի աշխատաթերթի հետ, VLOOKUP ֆունկցիան կարող է օգտագործվել մեկ այլ թերթից տվյալներ փնտրելու համար նույնը, ինչ մեկ աշխատաթերթում:

Օրինակ, դուք ունեք երկու աշխատանքային թերթ, ինչպես ցույց է տրված ստորև նշված էկրանի նկարը, ձեր կողմից նշված աշխատաթերթից համապատասխան տվյալները որոնելու և վերադարձնելու համար կատարեք հետևյալ քայլերը.

Քայլ 1. Կիրառեք և լրացրեք բանաձևը այլ բջիջներում

Խնդրում ենք մուտքագրել կամ պատճենել ստորև բերված բանաձևը դատարկ բջիջի մեջ, որտեղ ցանկանում եք ստանալ համապատասխան տարրերը: Այնուհետև քաշեք լրացման բռնակը դեպի այն բջիջները, որոնք ցանկանում եք կիրառել այս բանաձևը:

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Արդյունքը:

Դուք կստանաք համապատասխան արդյունքներ, ինչպես ձեզ հարկավոր է, տես սքրինշոթը.

2.8 VLOOKUP արժեքներ մեկ այլ աշխատանքային գրքույկից

Այս բաժինը կխոսի որոնման և համապատասխան արժեքների վերադարձի մասին մեկ այլ աշխատանքային գրքույկից՝ օգտագործելով VLOOKUP ֆունկցիան:

Օրինակ, ենթադրենք, դուք ունեք երկու աշխատանքային գրքույկ: Առաջին աշխատանքային գրքույկը պարունակում է ապրանքների և դրանց համապատասխան ծախսերի ցանկը: Երկրորդ աշխատանքային գրքում դուք ցանկանում եք հանել համապատասխան արժեքը յուրաքանչյուր ապրանքի համար, ինչպես ցույց է տրված ստորև ներկայացված սքրինշոթը:

Քայլ 1. Դիմել և լրացնել բանաձևը

Բացեք երկու աշխատանքային գրքույկները, որոնք ցանկանում եք օգտագործել, այնուհետև կիրառեք հետևյալ բանաձևը մի բջիջի մեջ, որտեղ ցանկանում եք արդյունքը տեղադրել երկրորդ աշխատանքային գրքում: Այնուհետև քաշեք և պատճենեք այս բանաձևը ձեզ անհրաժեշտ այլ բջիջներում:

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Արդյունքը:

2.9 Վերադարձեք դատարկ կամ կոնկրետ տեքստ՝ 0 կամ #N/A սխալի փոխարեն

Սովորաբար, երբ դուք օգտագործում եք VLOOKUP ֆունկցիան համապատասխան արժեք վերադարձնելու համար, եթե համապատասխան բջիջը դատարկ է, այն կվերադարձնի 0: Իսկ եթե համապատասխան արժեքը չգտնվի, դուք կստանաք #N/A սխալի արժեքը, ինչպես ցույց է տրված նկարում: սքրինշոթ ստորև: Եթե ​​ցանկանում եք ցուցադրել դատարկ բջիջ կամ որոշակի արժեք 0-ի կամ #N/A-ի փոխարեն, սա VLOOKUP՝ 0-ի կամ N/A-ի փոխարեն դատարկ կամ հատուկ արժեք վերադարձնելու համար ձեռնարկը կարող է ձեզ լավություն անել:

VLOOKUP- ի առաջադեմ օրինակներ

3.1 Երկկողմանի որոնում (VLOOKUP տողում և սյունակում)

Երբեմն ձեզ կարող է անհրաժեշտ լինել երկչափ որոնում կատարել, ինչը նշանակում է որոնել արժեք ինչպես տողերում, այնպես էլ սյունակում միաժամանակ: Օրինակ, եթե դուք ունեք տվյալների հետևյալ տիրույթը, և ձեզ կարող է անհրաժեշտ լինել որոշակի ապրանքի արժեքը որոշակի եռամսյակում ստանալ: Այս բաժնում կներկայացվի Excel-ում այս աշխատանքի հետ կապված բանաձև:

Excel-ում դուք կարող եք օգտագործել VLOOKUP և MATCH ֆունկցիաների համակցությունը երկկողմանի որոնում կատարելու համար:

Խնդրում ենք կիրառել հետևյալ բանաձևը դատարկ բջիջի մեջ և սեղմել Մտնել արդյունքը ստանալու բանալին:

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 VLOOKUP-ի համապատասխան արժեք՝ հիմնված երկու կամ ավելի չափանիշների վրա

Ձեզ համար հեշտ է գտնել համապատասխան արժեքը մեկ չափանիշի հիման վրա, բայց եթե ունեք երկու կամ ավելի չափանիշներ, ի՞նչ կարող եք անել:

 3.2.1 VLOOKUP համընկնող արժեք՝ հիմնված բանաձևերով երկու կամ ավելի չափանիշների վրա

Այս դեպքում Excel-ում LOOKUP կամ MATCH և INDEX ֆունկցիաները կարող են օգնել ձեզ արագ և հեշտությամբ լուծել այս խնդիրը:

Օրինակ, ես ունեմ ստորև բերված տվյալների աղյուսակը `համապատասխան ապրանքի և չափի հիման վրա համապատասխան գինը վերադարձնելու համար հետևյալ բանաձևերը կարող են օգնել ձեզ:

Քայլ 1. Կիրառեք ցանկացած բանաձև

Formula 1: Բանաձևը կպցնելուց հետո սեղմեք Մտնել բանալի.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formula 2: Բանաձևը կպցնելուց հետո սեղմեք Ctrl + Shift + Մուտք ստեղները:

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Արդյունքը:

 3.2.2 VLOOKUP-ի համապատասխան արժեք՝ հիմնված երկու կամ ավելի չափանիշների վրա՝ խելացի հատկանիշով

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

1. Սեղմել Կուտոլս > Super Փնտրել > Բազմապայմանների որոնում այս հնարավորությունը միացնելու համար:
2. Այնուհետև նշեք գործողությունները երկխոսության տուփից՝ ձեր տվյալների հիման վրա:

3.3 VLOOKUP՝ մեկ կամ մի քանի չափանիշներով բազմաթիվ արժեքներ վերադարձնելու համար

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

 3.3.1 VLOOKUP բոլոր համապատասխանող արժեքները՝ հիմնված մեկ կամ մի քանի պայմանների վրա հորիզոնական

Ենթադրելով, որ դուք ունեք տվյալների աղյուսակ, որը պարունակում է երկիր, քաղաք և անուններ A1:C14 միջակայքում, և այժմ, դուք ցանկանում եք հորիզոնական վերադարձնել բոլոր անունները, որոնք «ԱՄՆ»-ից են, ինչպես ցույց է տրված ստորև նշված սքրինշոթը: Այս խնդիրը լուծելու համար խնդրում ենք սեղմեք այստեղ՝ քայլ առ քայլ արդյունքը ստանալու համար.

 3.3.2 VLOOKUP բոլոր համապատասխան արժեքները՝ հիմնված մեկ կամ մի քանի պայմանների վրա՝ ուղղահայաց

Եթե ​​Ձեզ անհրաժեշտ է Vlookup և վերադարձնել բոլոր համապատասխան արժեքները ուղղահայաց՝ հիմնվելով հատուկ չափանիշների վրա, ինչպես ցույց է տրված ստորև նշված սքրինշոթը, խնդրում ենք սեղմել այստեղ՝ լուծումը մանրամասնորեն ստանալու համար.

 3.3.3 VLOOKUP բոլոր համապատասխան արժեքները, որոնք հիմնված են մեկ կամ մի քանի պայմանների վրա մեկ բջիջի մեջ

Եթե ​​ցանկանում եք Vlookup կատարել և վերադարձնել մի քանի համընկնող արժեքներ մեկ բջիջում՝ նշված բաժանարարով, TEXTJOIN-ի նոր գործառույթը կարող է օգնել ձեզ արագ և հեշտությամբ լուծել այս աշխատանքը.

3.4 VLOOKUP՝ համապատասխան բջիջի ամբողջ տողը վերադարձնելու համար

Այս բաժնում ես կխոսեմ այն ​​մասին, թե ինչպես կարելի է ստանալ համընկնող արժեքի ամբողջ տողը՝ օգտագործելով VLOOKUP ֆունկցիան:

Քայլ 1. Կիրառեք և լրացրեք հետևյալ բանաձևը

Խնդրում ենք պատճենել կամ մուտքագրել ստորև բերված բանաձևը դատարկ բջիջի մեջ, որտեղ ցանկանում եք դուրս բերել արդյունքը և սեղմել Մտնել բանալին՝ առաջին արժեքը ստանալու համար: Այնուհետև քաշեք բանաձևի բջիջը դեպի աջ, մինչև ցուցադրվեն ամբողջ տողի տվյալները:

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Արդյունքը:

Այժմ դուք կարող եք տեսնել, որ ամբողջ տողի տվյալները վերադարձվում են: Տես սքրինշոթը.

3.5 Ներդրված VLOOKUP Excel-ում

Երբեմն ձեզ կարող է անհրաժեշտ լինել որոնել արժեքներ, որոնք փոխկապակցված են բազմաթիվ աղյուսակներում: Այս դեպքում, դուք կարող եք տեղադրել մի քանի VLOOKUP ֆունկցիա՝ վերջնական արժեքը ստանալու համար:

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

Ներկառուցված VLOOKUP ֆունկցիայի ընդհանուր բանաձևը հետևյալն է.

Քայլ 1. Կիրառեք և լրացրեք հետևյալ բանաձևը

Խնդրում ենք կիրառել հետևյալ բանաձևը դատարկ բջիջի մեջ, այնուհետև քաշել լրացման բռնակը դեպի այն բջիջները, որոնք ցանկանում եք կիրառել այս բանաձևը:

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Արդյունքը:

Այժմ դուք կստանաք արդյունքը, ինչպես ցույց է տրված հետևյալ սքրինշոթում.

3.6 Ստուգեք, արդյոք արժեքը գոյություն ունի մեկ այլ սյունակի ցուցակի տվյալների հիման վրա

VLOOKUP ֆունկցիան կարող է նաև օգնել ձեզ ստուգել, ​​թե արդյոք արժեքները գոյություն ունեն մեկ այլ սյունակի տվյալների ցանկի հիման վրա: Օրինակ, եթե ցանկանում եք որոնել անունները C սյունակում և պարզապես վերադարձնել Այո կամ Ոչ, եթե անունը գտնված է կամ ոչ սյունակ Ա-ում, ինչպես ցույց է տրված ստորև նշված սքրինշոթը:

Քայլ 1. Կիրառեք և լրացրեք հետևյալ բանաձևը

Խնդրում ենք կիրառեք հետևյալ բանաձևը դատարկ բջիջի մեջ, այնուհետև քաշեք լրացման բռնակը դեպի այն բջիջները, որոնք ցանկանում եք լրացնել այս բանաձևը:

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Արդյունքը:

Եվ դուք կստանաք արդյունք, ինչպես ձեզ հարկավոր է, տես սքրինշոթը.

3.7 VLOOKUP և գումարել բոլոր համընկնող արժեքները տողերում կամ սյունակներում

Թվային տվյալների հետ աշխատելիս ձեզ կարող է անհրաժեշտ լինել համընկնող արժեքներ հանել աղյուսակից և թվերը գումարել մի քանի սյունակներում կամ տողերում: Այս բաժնում կներկայացվեն որոշ բանաձևեր, որոնք կարող են օգնել ձեզ կատարել այս խնդիրը:

 3.7.1 VLOOKUP և գումարել բոլոր համապատասխան արժեքները տողում կամ մի քանի տողում

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

Քայլ 1. Կիրառեք և լրացրեք հետևյալ բանաձևը

Խնդրում ենք պատճենել կամ մուտքագրել հետևյալ բանաձևը դատարկ բջիջի մեջ, այնուհետև սեղմել Ctrl + Shift + Մուտք բանալիները միասին՝ առաջին արդյունքը ստանալու համար: Այնուհետև քաշեք լրացման բռնակը ներքև՝ այս բանաձևը ձեզ անհրաժեշտ այլ բջիջներում պատճենելու համար:

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Արդյունքը:

Առաջին համապատասխան արժեքի անընդմեջ բոլոր արժեքներն ամփոփվել են միասին, տես սքրինշոթը՝

 3.7.2 VLOOKUP և գումարել բոլոր համապատասխան արժեքները սյունակում կամ մի քանի սյունակներում

Եթե ​​ցանկանում եք գումարել որոշակի ամիսների ընդհանուր արժեքը, ինչպես ցույց է տրված ստորև ներկայացված սքրինշոթում: Նորմալ VLOOKUP ֆունկցիան կարող է չօգնել ձեզ, այստեղ դուք պետք է կիրառեք SUM, INDEX և MATCH ֆունկցիաները միասին՝ բանաձև ստեղծելու համար:

Քայլ 1. Կիրառեք հետևյալ բանաձևը

Կիրառեք ստորև բերված բանաձևը դատարկ բջիջի մեջ, այնուհետև քաշեք լրացման բռնակը ներքև՝ այս բանաձևը այլ բջիջներում պատճենելու համար:

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Արդյունքը:

Այժմ, սյունակի կոնկրետ ամսվա հիման վրա առաջին համընկնող արժեքներն ամփոփվել են միասին, տես սքրինշոթը.

 3.7.3 VLOOKUP և գումարեք առաջին համընկնող կամ բոլոր համապատասխան արժեքները հզոր հատկանիշով

Միգուցե վերը նշված բանաձևերը ձեզ համար դժվար է հիշել, այս դեպքում ես խորհուրդ կտամ հզոր հատկանիշ. Փնտրել և գումարել of Excel- ի համար նախատեսված գործիքներ, այս հատկանիշով դուք կարող եք Vlookup և հնարավորինս հեշտությամբ գումարել առաջին համապատասխանող կամ բոլոր համապատասխան արժեքները տողերում կամ սյունակներում:

1. Սեղմել Կուտոլս > Super Փնտրել > Փնտրել և գումարել այս հնարավորությունը միացնելու համար:
2. Այնուհետև նշեք գործողությունները երկխոսության տուփից՝ ելնելով ձեր կարիքից:

 3.7.4 VLOOKUP և գումարել բոլոր համապատասխան արժեքները ինչպես տողերում, այնպես էլ սյունակներում

Եթե ​​ցանկանում եք ամփոփել արժեքները, երբ դուք պետք է համընկնեք և՛ սյունակին, և՛ տողին, օրինակ ՝ մարտ ամսվա սվիտերի արտադրանքի ընդհանուր արժեքը ստանալու համար, ինչպես ցույց է տրված նկարում:

Այստեղ դուք կարող եք օգտագործել SUMPRODCT ֆունկցիան այս առաջադրանքն իրականացնելու համար:

Խնդրում ենք կիրառել հետևյալ բանաձևը բջիջի մեջ, այնուհետև սեղմել Մտնել արդյունք ստանալու համար ստեղնը, տես նկարի նկարը.

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 VLOOKUP՝ առանցքային սյունակների վրա հիմնված երկու աղյուսակներ միավորելու համար

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

 3.8.1 VLOOKUP՝ մեկ հիմնական սյունակի հիման վրա երկու աղյուսակներ միավորելու համար

Օրինակ, դուք ունեք երկու աղյուսակ, առաջին աղյուսակը պարունակում է ապրանքների և անունների տվյալները, իսկ երկրորդ աղյուսակը պարունակում է ապրանքների և պատվերների տվյալները, այժմ դուք ցանկանում եք միավորել այս երկու աղյուսակները՝ համընկնելով ընդհանուր արտադրանքի սյունակը մեկ աղյուսակի մեջ:

Քայլ 1. Կիրառեք և լրացրեք հետևյալ բանաձևը

Խնդրում ենք կիրառել հետևյալ բանաձևը դատարկ բջիջի մեջ. Այնուհետև քաշեք լրացման բռնակը դեպի այն բջիջները, որոնք ցանկանում եք կիրառել այս բանաձևը

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Արդյունքը:

Այժմ դուք կստանաք միաձուլված աղյուսակ, որի կարգի սյունակը միանում է առաջին աղյուսակին՝ հիմնվելով հիմնական սյունակի տվյալների վրա:

 3.8.2 VLOOKUP՝ երկու աղյուսակներ միացնելու համար՝ հիմնված բազմաթիվ հիմնական սյունակների վրա

Եթե ​​երկու աղյուսակները, որոնց ցանկանում եք միանալ, ունեն բազմաթիվ հիմնական սյունակներ, այս ընդհանուր սյունակների հիման վրա աղյուսակները միացնելու համար խնդրում ենք հետևել ստորև նշված քայլերին:

Ընդհանուր բանաձևն է.

Քայլ 1. Կիրառեք հետևյալ բանաձևը

Խնդրում ենք կիրառել ստորև բերված բանաձևը դատարկ բջիջի մեջ, որտեղ ցանկանում եք տեղադրել արդյունքը, այնուհետև սեղմել Ctrl + Shift + Մուտք ստեղները միասին ՝ առաջին համապատասխան արժեքը ստանալու համար, տե՛ս նկարը.

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Քայլ 2. Լրացրեք բանաձևը այլ բջիջներում

Այնուհետև ընտրեք բանաձևի առաջին բջիջը և քաշեք լրացման բռնակը, որպեսզի պատճենեք այս բանաձևը այլ բջիջներում, ինչպես ձեզ անհրաժեշտ է.

3.9 VLOOKUP համընկնող արժեքները մի քանի աշխատաթերթերում

Ձեզ երբևէ անհրաժեշտ է եղել կատարել VLOOKUP մի քանի աշխատաթերթերի վրա Excel-ում: Օրինակ, եթե դուք ունեք երեք աշխատաթերթեր՝ տվյալների տիրույթներով, և ցանկանում եք այս թերթերից չափանիշների հիման վրա հատուկ արժեքներ ստանալ, կարող եք հետևել քայլ առ քայլ ձեռնարկին։ VLOOKUP արժեքներ բազմաթիվ աշխատաթերթերում իրականացնել այս խնդիրը:

VLOOKUP- ի համապատասխան արժեքները պահպանում են բջիջների ձևաչափումը

Համապատասխան արժեքներ փնտրելիս բնօրինակ բջիջների ձևաչափերը, ինչպիսիք են տառատեսակի գույնը, ֆոնի գույնը, տվյալների ձևաչափը և այլն, չեն պահպանվի: Բջջի կամ տվյալների ֆորմատավորումը պահպանելու համար այս բաժինը կներկայացնի որոշ հնարքներ՝ խնդիրները լուծելու համար:

4.1 VLOOKUP-ի համապատասխան արժեքը և պահպանել բջջային գույնը, տառատեսակի ձևաչափումը

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

Խնդրում ենք արեք հետևյալ քայլերը ՝ բջջի ձևաչափմանը զուգահեռ որոնելու և վերադարձնելու դրա համապատասխան արժեքը.

Քայլ 1. Պատճենեք կոդը 1 թերթի կոդի մոդուլում

1. Աշխատանքային թերթիկը պարունակում է այն տվյալները, որոնք ցանկանում եք VLOOKUP անել, աջ սեղմեք թերթի ներդիրին և ընտրեք Դիտել կոդը համատեքստային ընտրացանկից: Տեսեք,
   
2. Բացվածքում Microsoft Visual Basic հավելվածների համար պատուհան, խնդրում ենք պատճենել ստորև նշված VBA կոդը օրենսգրքի պատուհանում:
3. VBA կոդ 1. VLOOKUP՝ բջջային ֆորմատավորում ստանալու համար որոնման արժեքի հետ մեկտեղ

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Քայլ 2. Պատճենեք 2 կոդը Մոդուլի պատուհանում

1. Շարունակում է Microsoft Visual Basic հավելվածների համար պատուհանը, սեղմեք Տեղադրել > Մոդուլներ, ապա պատճենեք ներքևի VBA կոդը 2 Մոդուլի պատուհանում:
2. VBA կոդ 2. VLOOKUP՝ բջջային ֆորմատավորում ստանալու համար որոնման արժեքի հետ մեկտեղ

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Քայլ 3. Ընտրեք տարբերակը VBAproject-ի համար

1. Վերոնշյալ ծածկագրերը տեղադրելուց հետո սեղմեք Գործիքներ > Սայլակ է Microsoft Visual Basic հավելվածների համար պատուհան Ապա ստուգեք Microsoft Scripting Runtime վանդակում Հղումներ - VBAP նախագիծ երկխոսության տուփ: Տեսեք սքրինշոթեր.
2. Այնուհետեւ կտտացրեք OK փակել երկխոսության տուփը, այնուհետև պահպանել և փակել ծածկագրի պատուհանը:

Քայլ 4. Մուտքագրեք արդյունքը ստանալու բանաձևը

1. Այժմ, վերադարձեք աշխատանքային թերթիկ, կիրառեք հետևյալ բանաձևը. Եվ այնուհետև քաշեք լցման բռնակը ներքև, որպեսզի ստանաք բոլոր արդյունքները դրանց ձևաչափման հետ մեկտեղ: Տես սքրինշոթը.
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 Պահպանեք ամսաթվի ձևաչափը VLOOKUP վերադարձված արժեքից

VLOOKUP ֆունկցիան օգտագործելիս ամսաթվի ձևաչափով արժեք փնտրելու և վերադարձնելու համար, վերադարձված արդյունքը կարող է ցուցադրվել որպես թիվ: Ամսաթվի ձևաչափը վերադարձված արդյունքում պահելու համար դուք պետք է ներառեք VLOOKUP ֆունկցիան TEXT ֆունկցիայի մեջ:

Քայլ 1. Կիրառեք և լրացրեք հետևյալ բանաձևը

Խնդրում ենք կիրառել ստորև բերված բանաձևը դատարկ բջիջի մեջ: Այնուհետև քաշեք լրացման բռնիչը՝ այս բանաձևը այլ բջիջներ պատճենելու համար:

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Արդյունքը:

Բոլոր համընկնող ամսաթվերը վերադարձվել են, ինչպես ցույց է տրված ստորև նշված սքրինշոթը.

4.3 Վերադարձեք բջջային մեկնաբանությունը VLOOKUP-ից

Երբևէ կարիք եղե՞լ է ստանալ և՛ համապատասխան բջջային տվյալները, և՛ դրա հետ կապված մեկնաբանությունը՝ օգտագործելով VLOOKUP-ը Excel-ում, ինչպես ցույց է տրված հետևյալ սքրինշոթում: Եթե ​​այո, ապա ստորև ներկայացված Օգտագործողի կողմից սահմանված գործառույթը կարող է օգնել ձեզ կատարել այս խնդիրը:

Քայլ 1. Պատճենեք կոդը մոդուլի մեջ

1. Անջատեք ALT + F11 բացել ստեղները Microsoft Visual Basic հավելվածների համար պատուհան.
2. Սեղմել Տեղադրել > Մոդուլներ, ապա պատճենեք և տեղադրեք հետևյալ կոդը Մոդուլի պատուհանում:
   VBA կոդ. Vlookup և վերադարձի համապատասխան արժեքը բջջային մեկնաբանության հետ.

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Այնուհետև պահպանեք և փակեք ծածկագրի պատուհանը:

Քայլ 2. Մուտքագրեք բանաձևը՝ արդյունքը ստանալու համար

1. Այժմ մուտքագրեք հետևյալ բանաձևը և քաշեք լրացման բռնիչը՝ այս բանաձևը այլ բջիջներ պատճենելու համար: Այն միաժամանակ կվերադարձնի և՛ համընկնող արժեքները, և՛ մեկնաբանությունները, տես սքրինշոթը՝
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 VLOOKUP համարները պահվում են որպես տեքստ

Օրինակ, ես ունեմ տվյալների մի շարք, որտեղ սկզբնական աղյուսակի ID համարը թվային ձևաչափով է, իսկ ID համարը որոնման բջիջներում պահվում է որպես տեքստ, սովորական VLOOKUP ֆունկցիան օգտագործելիս կարող եք հանդիպել #N/A սխալի: Այս դեպքում ճիշտ տեղեկատվություն ստանալու համար կարող եք TEXT և VALUE ֆունկցիաները փաթաթել VLOOKUP ֆունկցիայի մեջ: Ստորև բերված է դրան հասնելու բանաձևը.

Քայլ 1. Կիրառեք և լրացրեք հետևյալ բանաձևը

Խնդրում ենք կիրառել հետևյալ բանաձևը դատարկ բջիջի մեջ, այնուհետև քաշել ներքև՝ այս բանաձևը պատճենելու համար:

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Արդյունքը:

Այժմ դուք կստանաք ճիշտ արդյունքներ, ինչպես ցույց է տրված ստորև նշված սքրինշոթը.