Ընդլայնված VLOOKUP Օրինակներ. Բազմաչափ որոնում

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

• Որոնել Excel-ում բազմաթիվ չափանիշներով
• Մենք հանում ենք 2-րդ, 3-րդ և այլն արժեքները VLOOKUP-ի միջոցով
• Վերցրեք ցանկալի արժեքի բոլոր կրկնությունները
• XNUMXD որոնում ըստ հայտնի տողի և սյունակի
• Օգտագործելով բազմաթիվ VLOOKUP մեկ բանաձևով
• Տարբեր աղյուսակների տվյալների դինամիկ փոխարինում

Որոնել Excel-ում բազմաթիվ չափանիշներով

ֆունկցիա VPR- ը Excel-ում իսկապես հզոր գործիք է տվյալների բազայում որոշակի արժեքի որոնումներ կատարելու համար: Այնուամենայնիվ, կա մի զգալի սահմանափակում. դրա շարահյուսությունը թույլ է տալիս որոնել միայն մեկ արժեք: Իսկ եթե ցանկանում եք որոնել բազմաթիվ պայմաններով: Լուծումը կգտնեք ստորև։

Օրինակ 1. Որոնել 2 տարբեր չափանիշներով

Ենթադրենք, մենք ունենք պատվերների ցանկ և ուզում ենք գտնել Ապրանքների քանակը (Քանակ.), հիմնված երկու չափանիշների վրա. Հաճախորդի անունը (Հաճախորդ) и Название продукта (Ապրանք): Հարցը բարդանում է նրանով, որ գնորդներից յուրաքանչյուրը պատվիրել է մի քանի տեսակի ապրանք, ինչպես երևում է ստորև բերված աղյուսակից.

կանոնավոր գործառույթ VPR- ը չի աշխատի այս սցենարում, քանի որ այն կվերադարձնի իր գտած առաջին արժեքը, որը համապատասխանում է տվյալ որոնման արժեքին: Օրինակ, եթե ցանկանում եք իմանալ ապրանքի քանակը Քաղցրավենիք'պատվիրված գնորդի կողմից Remերեմի Հիլլ, գրեք հետևյալ բանաձևը.

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

- այս բանաձևը կվերադարձնի արդյունքը 15արտադրանքին համապատասխան խնձորներ, քանի որ դա առաջին արժեքն է, որը համընկնում է:

Կա մի պարզ լուծում՝ ստեղծել լրացուցիչ սյունակ, որտեղ կարելի է համատեղել բոլոր ցանկալի չափանիշները: Մեր օրինակում սրանք սյունակներն են Հաճախորդի անունը (Հաճախորդ) и Название продукта (Ապրանք): Մի մոռացեք, որ միավորված սյունակը միշտ պետք է լինի որոնման տիրույթի ամենաձախ սյունակը, քանի որ ձախ սյունակն է, որ գործառույթը VPR- ը բարձր է նայում, երբ փնտրում է արժեք:

Այսպիսով, դուք աղյուսակին ավելացնում եք օժանդակ սյունակ և պատճենում հետևյալ բանաձևը նրա բոլոր բջիջների վրա. =B2&C2. Եթե ​​ցանկանում եք, որ տողը ավելի ընթեռնելի լինի, կարող եք համակցված արժեքները առանձնացնել բացատով. =B2&» «&C2. Դրանից հետո կարող եք օգտագործել հետևյալ բանաձևը.

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Որտեղ է բջիջը B1 պարունակում է փաստարկի համակցված արժեքը lookup_value (որոնման_արժեք) և 4 - Վեճ col_index_num (column_number), այսինքն՝ առբերվող տվյալները պարունակող սյունակի թիվը։

Օրինակ 2. VLOOKUP երկու չափանիշներով, որտեղ աղյուսակը դիտվում է մեկ այլ թերթիկի վրա

Եթե ​​Ձեզ անհրաժեշտ է թարմացնել հիմնական աղյուսակը (Հիմնական աղյուսակը)՝ ավելացնելով տվյալներ երկրորդ աղյուսակից (Փնտրման աղյուսակ), որը գտնվում է մեկ այլ թերթիկի կամ մեկ այլ Excel աշխատանքային գրքում, ապա կարող եք հավաքել ցանկալի արժեքը անմիջապես այն բանաձևում, որը տեղադրում եք: հիմնական աղյուսակի մեջ:

Ինչպես նախորդ օրինակում, Ձեզ անհրաժեշտ կլինի օժանդակ սյունակ Փնտրման աղյուսակում՝ համակցված արժեքներով: Այս սյունակը պետք է լինի որոնման տիրույթի ամենաձախ սյունակը:

Այսպիսով, բանաձեւը հետ VPR- ը կարող է լինել այսպես.

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Այստեղ B և C սյունակները պարունակում են համապատասխանաբար հաճախորդների անունները և ապրանքների անունները և հղումը Պատվերներ $A&$2:$D$2 սահմանում է աղյուսակ՝ մեկ այլ թերթիկում փնտրելու համար:

Բանաձևն ավելի ընթեռնելի դարձնելու համար կարող եք անուն տալ դիտման տիրույթին, այնուհետև բանաձևը շատ ավելի պարզ տեսք կունենա.

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

Որպեսզի բանաձևը աշխատի, ձեր դիտած աղյուսակի ձախ սյունակի արժեքները պետք է համակցվեն ճիշտ նույն ձևով, ինչ որոնման չափանիշներում: Վերևի նկարում մենք համակցեցինք u2bu2band արժեքները, որոնց միջև տարածություն դրեցինք, նույն ձևով, որը դուք պետք է անեք ֆունկցիայի առաջին արգումենտում (BXNUMX& “” & CXNUMX):

Հիշե'ք: ֆունկցիա VPR- ը սահմանափակվում է 255 նիշով, այն չի կարող որոնել 255 նիշից ավելի արժեք: Հիշեք սա և համոզվեք, որ ցանկալի արժեքի երկարությունը չի գերազանցում այս սահմանը:

Համաձայն եմ, որ օժանդակ սյունակ ավելացնելը ամենաէլեգանտ և ոչ միշտ ընդունելի լուծումն է: Դուք կարող եք նույնն անել առանց օգնական սյունակի, բայց դա կպահանջի շատ ավելի բարդ բանաձև՝ ֆունկցիաների համակցությամբ INDEX (INDEX) և MATCH (ԱՎԵԼԻ ԲԱՑՎԱԾ):

Մենք հանում ենք 2-րդ, 3-րդ և այլն արժեքները VLOOKUP-ի միջոցով

Դուք դա արդեն գիտեք VPR- ը կարող է վերադարձնել միայն մեկ համընկնող արժեք, ավելի ճիշտ՝ հայտնաբերված առաջինը: Բայց ինչ անել, եթե այս արժեքը կրկնվի մի քանի անգամ դիտված զանգվածում, և դուք ցանկանում եք հանել դրանցից 2-րդ կամ 3-րդը: Իսկ եթե բոլոր արժեքները: Խնդիրը բարդ է թվում, բայց լուծումը կա։

Ենթադրենք, աղյուսակի մի սյունակ պարունակում է հաճախորդների անունները (Customer Name), իսկ մյուս սյունակում պարունակում է ապրանքները (Product), որոնք նրանք գնել են: Փորձենք գտնել տվյալ հաճախորդի կողմից գնված 2-րդ, 3-րդ և 4-րդ ապրանքները։

Ամենահեշտ ձևը սյունակից առաջ օժանդակ սյունակ ավելացնելն է հաճախորդի անունը և լրացրեք այն հաճախորդների անուններով յուրաքանչյուր անվան կրկնության համարով, օրինակ. Ջոն Դո 1, Ջոն Դո 2 և այլն։ Մենք կկատարենք համարակալման հնարքը՝ օգտագործելով ֆունկցիան COUNTIF (COUNTIF), հաշվի առնելով, որ հաճախորդների անունները B սյունակում են.

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

Դրանից հետո դուք կարող եք օգտագործել նորմալ գործառույթը VPR- ըանհրաժեշտ պատվերը գտնելու համար: Օրինակ:

• Գտնել 2-րդ հաճախորդի կողմից պատվիրված ապրանք Dan Brown:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• Գտնել 3-րդ հաճախորդի կողմից պատվիրված ապրանք Dan Brown:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

Փաստորեն, դուք կարող եք մուտքագրել բջջային հղում որպես որոնման արժեք տեքստի փոխարեն, ինչպես ցույց է տրված հետևյալ նկարում.

Եթե ​​դուք միայն փնտրում եք 2-e կրկնություն, դուք կարող եք դա անել առանց օժանդակ սյունակի՝ ստեղծելով ավելի բարդ բանաձև.

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

Այս բանաձեւում.

• $ 2 դոլար – գնորդի անունը պարունակող բջիջ (այն անփոփոխ է, խնդրում ենք նկատի ունենալ. հղումը բացարձակ է);
• $ B $ - սյունակ հաճախորդի անունը;
• Table4- ը – Ձեր սեղանը (այս վայրը կարող է լինել նաև սովորական տեսականի);
• $ C16 – ձեր աղյուսակի կամ տիրույթի վերջնական բջիջը:

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

Եթե ​​Ձեզ անհրաժեշտ է բոլոր համընկնումների ցանկը՝ ֆունկցիան VPR- ը սա օգնական չէ, քանի որ այն միաժամանակ վերադարձնում է միայն մեկ արժեք՝ ժամանակաշրջան: Բայց Excel-ն ունի մի ֆունկցիա INDEX (INDEX), որը հեշտությամբ կարող է հաղթահարել այս խնդիրը: Թե ինչպիսին կլինի նման բանաձևը, կիմանաք հետևյալ օրինակում։

Վերցրեք ցանկալի արժեքի բոլոր կրկնությունները

Ինչպես նշվեց վերեւում VPR- ը չի կարող դուրս հանել բոլոր կրկնօրինակ արժեքները սկանավորված տիրույթից: Դա անելու համար ձեզ հարկավոր է մի փոքր ավելի բարդ բանաձև, որը կազմված է Excel-ի մի քանի գործառույթներից, ինչպիսիք են INDEX (INDEX), ՓՈՔՐ (ՓՈՔՐ) և ROW (ԳԻԾ)

Օրինակ, ստորև բերված բանաձևը գտնում է F2 բջիջի արժեքի բոլոր կրկնությունները B2:B16 միջակայքում և վերադարձնում է արդյունքը C սյունակի նույն տողերից:

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

Մուտքագրեք այս զանգվածի բանաձևը մի քանի հարակից բջիջների մեջ, ինչպիսիք են բջիջները F4: F8ինչպես ցույց է տրված ստորև նկարում: Բջիջների թիվը պետք է հավասար կամ մեծ լինի որոնված արժեքի առավելագույն հնարավոր կրկնությունների քանակից: Մի մոռացեք սեղմել Ctrl + Shift + Մուտքզանգվածի բանաձևը ճիշտ մուտքագրելու համար.

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

Մաս 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 - համեմատեք F2 բջիջի արժեքը B2:B16 միջակայքի արժեքներից յուրաքանչյուրի հետ: Եթե ​​համընկնում է գտնվել, ապա արտահայտությունը STRING(C2:C16)-1 վերադարձնում է համապատասխան տողի համարը (արժեք -1 թույլ է տալիս չներառել վերնագրի տողը): Եթե ​​համընկնումներ չկան, ֆունկցիան IF (IF) վերադարձնում է դատարկ տող:

Գործառույթի արդյունքը IF (Եթե) կլինի այսպիսի հորիզոնական զանգված. {1,"",3,"",5,"","","","","","",12,"","",""}

Մաս 2:

ROW()-3

СТРОКА()-3

Ահա ֆունկցիան ROW (LINE) հանդես է գալիս որպես լրացուցիչ հաշվիչ: Քանի որ բանաձևը պատճենվում է F4:F9 բջիջներում, մենք հանում ենք թիվը 3 ֆունկցիայի արդյունքից արժեք ստանալու համար 1 խցում F4 (տող 4, հանել 3) ստանալու համար 2 խցում F5 (տող 5, հանել 3) և այլն:

Մաս 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

ֆունկցիա ՓՈՔՐ (ՓՈՔՐ) վերադառնում է n-oh տվյալների զանգվածի ամենափոքր արժեքը: Մեր դեպքում, թե որ դիրքը (ամենափոքրից) վերադարձնել, որոշվում է ֆունկցիայով ROW (ԳԻԾ) (տես Մաս 2): Այսպիսով, բջիջի համար F4 ֆունկցիա ՓՈՔՐ ({զանգված},1) Վերադառնում 1-րդ (ամենափոքր) զանգվածի տարրը, այսինքն 1. Բջջի համար F5 Վերադառնում 2-րդ զանգվածի ամենափոքր տարրը, այսինքն 3Եւ այլն:

Մաս 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

ֆունկցիա INDEX (INDEX) պարզապես վերադարձնում է զանգվածի կոնկրետ բջիջի արժեքը C2:C16. Բջջի համար F4 ֆունկցիա INDEX ($C$2:$C$16) կվերադառնա խնձորներհամար F5 ֆունկցիա INDEX ($C$2:$C$16) կվերադառնա Քաղցրավենիք' եւ այլն:

Մաս 5:

IFERROR()

ЕСЛИОШИБКА()

Ի վերջո, մենք բանաձևը դնում ենք ֆունկցիայի ներսում ԻՐԱER (IFERROR), քանի որ դժվար թե ձեզ գոհացնի սխալի հաղորդագրությունը #AT (# N/A), եթե բջիջների թիվը, որոնցում պատճենվում է բանաձևը, պակաս է, քան դիտվող տիրույթում կրկնվող արժեքների թիվը:

XNUMXD որոնում ըստ հայտնի տողի և սյունակի

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

Այսպիսով, եկեք դիմենք մեր աղյուսակին և գրենք բանաձև՝ ֆունկցիայով VPR- ը, որը տեղեկատվություն կգտնի մարտին վաճառվող կիտրոնի ինքնարժեքի մասին։

XNUMXD որոնում կատարելու մի քանի եղանակ կա: Ստուգեք տարբերակները և ընտրեք այն, որը ձեզ լավագույնս համապատասխանում է:

VLOOKUP և MATCH գործառույթները

Դուք կարող եք օգտագործել մի շարք գործառույթներ VPR- ը (VLOOKUP) և ԱՎԵԼԻ ԲԱՑՎԱԾ (MATCH) դաշտերի խաչմերուկում արժեքը գտնելու համար Название продукта (լար) և Ամիս խնդրո առարկա զանգվածի (սյունակ).

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

Վերոնշյալ բանաձևը սովորական գործառույթ է VPR- ը, որը փնտրում է «Lemons» արժեքի ճշգրիտ համապատասխանությունը A2-ից A9 բջիջներում: Բայց քանի որ դուք չգիտեք, թե որ սյունակում են մարտի վաճառքները, դուք չեք կարողանա սահմանել սյունակի համարը երրորդ ֆունկցիայի փաստարկի համար: VPR- ը. Փոխարենը օգտագործվում է ֆունկցիան ԱՎԵԼԻ ԲԱՑՎԱԾայս սյունակը սահմանելու համար:

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

Մարդկային լեզվով թարգմանված այս բանաձևը նշանակում է.

• Մենք փնտրում ենք «Մար» կերպարները՝ փաստարկ lookup_value (որոնման_արժեք);
• Փնտրում բջիջներում A1-ից մինչև I1 – փաստարկ lookup_հասցե (որոնման_զանգված);
• Ճշգրիտ համընկնման վերադարձ՝ փաստարկ համընկնում_տեսակ (համապատասխանի_տեսակ):

Օգտագործելով 0 երրորդ արգումենտում ասում եք ֆունկցիաներ ԱՎԵԼԻ ԲԱՑՎԱԾ փնտրեք առաջին արժեքը, որը ճշգրիտ համապատասխանում է ձեր փնտրած արժեքին: Սա համարժեք է արժեքին ԿԵՂԾ (FALSE) չորրորդ փաստարկի համար VPR- ը.

Ահա թե ինչպես կարող եք ստեղծել երկկողմանի որոնման բանաձև Excel-ում, որը նաև հայտնի է որպես երկչափ որոնում կամ երկկողմանի որոնում:

SUMPRODUCT ֆունկցիան

ֆունկցիա SUMPRODUCT- ը (SUMPRODUCT) վերադարձնում է ընտրված զանգվածների արտադրյալների գումարը.

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX և MATCH ֆունկցիաներ

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

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

Անվանված միջակայքերը և խաչմերուկի օպերատորը

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

1. Ընտրեք աղյուսակը, բացեք ներդիրը Բանաձեւեր (Բանաձևեր) և սեղմեք Ստեղծեք ընտրությունից (Ստեղծել ընտրվածից):
2. Ստուգեք արկղերը Վերին շարքը (վերևի տողում) և Ձախ սյունակ (ձախ սյունակում): Microsoft Excel-ը ձեր աղյուսակի վերին տողում և ձախ սյունակում գտնվող արժեքներից ընդգրկում է անուններ: Այժմ դուք կարող եք որոնել՝ օգտագործելով այս անուններն ուղղակիորեն՝ առանց բանաձևեր ստեղծելու:
3. Ցանկացած դատարկ բջիջում գրեք = տողի_անուն սյունակի_անուն, օրինակ այսպես.

   =Կիտրոններ Մար

   … կամ հակառակը.

   =Mar Lemons

   Հիշեք, որ տողերի և սյունակների անունները պետք է բաժանվեն բացատով, որն այս դեպքում աշխատում է խաչմերուկի օպերատորի նման:

Անուն մուտքագրելիս Microsoft Excel-ը կցուցադրի գործիքի հուշում համապատասխան անունների ցանկով, ինչպես որ բանաձև եք մուտքագրում:

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

Ընդհանուր առմամբ, վերը նշված մեթոդներից որն էլ ընտրեք, երկչափ որոնման արդյունքը կլինի նույնը.

Օգտագործելով բազմաթիվ VLOOKUP մեկ բանաձևով

Պատահում է, որ հիմնական աղյուսակը և որոնման աղյուսակը չունեն մեկ ընդհանուր սյունակ, և դա ձեզ խանգարում է օգտագործել սովորական գործառույթը: VPR- ը. Այնուամենայնիվ, կա ևս մեկ աղյուսակ, որը չի պարունակում մեզ հետաքրքրող տեղեկատվությունը, բայց ունի ընդհանուր սյունակ հիմնական աղյուսակի և որոնման աղյուսակի հետ:

Եկեք նայենք հետևյալ օրինակին. Մենք ունենք հիմնական աղյուսակ սյունակով SKU (նոր), որտեղ ցանկանում եք մեկ այլ աղյուսակից համապատասխան գներով սյունակ ավելացնել: Բացի այդ, մենք ունենք 2 որոնման աղյուսակ: Առաջինը (Փնտրման աղյուսակ 1) պարունակում է թարմացված թվեր SKU (նոր) և ապրանքների անվանումները, իսկ երկրորդը (Փնտրման աղյուսակ 2)՝ ապրանքների անվանումները և հին համարները SKU (հին).

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

1. Գրեք ֆունկցիա VPR- ը, որը գտնում է ապրանքի անվանումը աղյուսակում Փնտրման աղյուսակ 1օգտագործելով Կոդը, որպես ցանկալի արժեք.

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   Այստեղ Նոր_SKU - անվանված տիրույթ $A:$B աղյուսակում Փնտրման աղյուսակ 1, 2 - սա սյունակ B է, որը պարունակում է ապրանքների անվանումները (տես վերևի նկարը)

2. Գրեք բանաձև՝ աղյուսակից գները տեղադրելու համար Փնտրման աղյուսակ 2 հիմնված ապրանքների հայտնի անվանումների վրա: Դա անելու համար տեղադրեք ավելի վաղ ստեղծված բանաձևը որպես նոր գործառույթի որոնման արժեք VPR- ը:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   Այստեղ Գին - անվանված տիրույթ $ A: $ C աղյուսակում Փնտրման աղյուսակ 2, 3 C սյունակը պարունակում է գներ:

Ստորև բերված նկարը ցույց է տալիս մեր ստեղծած բանաձևով վերադարձված արդյունքը.

Տարբեր աղյուսակներից տվյալների դինամիկ փոխարինում VLOOKUP-ի և INDIRECT-ի միջոցով

Նախ, եկեք պարզենք, թե ինչ նկատի ունենք «Տարբեր աղյուսակներից տվյալների դինամիկ փոխարինում» արտահայտությունով, որպեսզի համոզվենք, որ մենք ճիշտ ենք հասկանում միմյանց:

Կան իրավիճակներ, երբ կան մի քանի թերթեր՝ նույն ձևաչափի տվյալներով, և անհրաժեշտ է որոշակի թերթիկից հանել անհրաժեշտ ինֆորմացիան՝ կախված տվյալ բջիջում մուտքագրված արժեքից։ Կարծում եմ՝ ավելի հեշտ է սա բացատրել օրինակով։

Պատկերացրեք, որ դուք ունեք վաճառքի հաշվետվություններ մի քանի տարածաշրջանների համար՝ նույն ապրանքներով և նույն ձևաչափով: Դուք ցանկանում եք գտնել վաճառքի թվեր որոշակի տարածաշրջանի համար.

Եթե ​​դուք ունեք ընդամենը երկու նման զեկույց, ապա կարող եք օգտագործել խայտառակ պարզ բանաձևը գործառույթներով VPR- ը и IF (IF) որոնման համար ցանկալի զեկույցը ընտրելու համար.

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

որտեղ `

• $ 2 $ ապրանքի անվանումը պարունակող բջիջ է: Նկատի ունեցեք, որ մենք այստեղ օգտագործում ենք բացարձակ հղումներ՝ բանաձևը այլ բջիջներում պատճենելիս որոնման արժեքը չփոխելու համար:
• $ 3 շրջանի անվանումով բջիջ է։ Մենք օգտագործում ենք բացարձակ սյունակի հղում և հարաբերական տողի հղում, քանի որ նախատեսում ենք պատճենել բանաձևը նույն սյունակի այլ բջիջներում:
• FL_Sales и CA_Sales - աղյուսակների (կամ անվանված միջակայքերի) անվանումները, որոնք պարունակում են համապատասխան վաճառքի հաշվետվություններ: Դուք, իհարկե, կարող եք օգտագործել թերթերի սովորական անունները և բջիջների տիրույթի հղումները, օրինակ «FL Sheet»!$A$3:$B$10, բայց անվանված միջակայքերը շատ ավելի հարմար են։

Այնուամենայնիվ, երբ կան բազմաթիվ նման աղյուսակներ, գործառույթը IF լավագույն լուծումը չէ: Փոխարենը, դուք կարող եք օգտագործել գործառույթը Անկախ (ԱՆՈՒՂԻՂ) որոնման ցանկալի տիրույթը վերադարձնելու համար:

Ինչպես հավանաբար գիտեք, գործառույթը Անկախ օգտագործվում է տեքստային տողի կողմից տրված հղումը վերադարձնելու համար, որը հենց այն է, ինչ մեզ հիմա անհրաժեշտ է: Այսպիսով, վերը նշված բանաձեւում համարձակորեն փոխարինեք արտահայտությունը ֆունկցիայով IF ֆունկցիայի հետ կապելու համար Անկախ. Ահա մի համադրություն VPR- ը и Անկախ հիանալի աշխատում է հետևյալի հետ.

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

որտեղ `

• $ 2 $ – սա ապրանքի անվանումով բջիջ է, բացարձակ կապի պատճառով այն անփոփոխ է:
• $ 3 շրջանի անվան առաջին մասը պարունակող բջիջն է: Մեր օրինակում սա FL.
• _Վաճառք - բոլոր անվանված միջակայքերի կամ աղյուսակների անվան ընդհանուր մասը: Երբ համակցվում է D3 բջիջի արժեքի հետ, այն կազմում է պահանջվող տիրույթի լրիվ որակավորված անվանումը: Ստորև բերված են մի քանի մանրամասներ նրանց համար, ովքեր նոր են գործում Անկախ.

Ինչպես են աշխատում INDIRECT-ը և VLOOKUP-ը

Նախ հիշեցնեմ ֆունկցիայի շարահյուսությունը Անկախ (ԱՆՈՒՂԻՂ):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

Առաջին արգումենտը կարող է լինել բջջային հղում (A1 կամ R1C1 ոճ), տիրույթի անվանում կամ տեքստային տող: Երկրորդ փաստարկը որոշում է, թե կապի ինչ ոճն է պարունակվում առաջին փաստարկում.

• A1եթե փաստարկն է TRԻՇՏ ԿՈԴ (ՃԻՇՏ) կամ նշված չէ;
• R1C1Եթե FԱՍ Ե (ՍՈՒՏ):

Մեր դեպքում հղումն ունի ոճ A1, այնպես որ կարող եք բաց թողնել երկրորդ փաստարկը և կենտրոնանալ առաջինի վրա:

Այսպիսով, եկեք վերադառնանք մեր վաճառքի հաշվետվություններին: Եթե ​​հիշում եք, ապա յուրաքանչյուր հաշվետվություն առանձին աղյուսակ է, որը գտնվում է առանձին թերթիկի վրա: Որպեսզի բանաձևը ճիշտ աշխատի, դուք պետք է անվանեք ձեր աղյուսակները (կամ միջակայքերը), և բոլոր անունները պետք է ունենան ընդհանուր մաս: Օրինակ, այսպես. CA_Sales, FL_Sales, TX_Sales եւ այլն։ Ինչպես տեսնում եք, «_Sales»-ն առկա է բոլոր անուններում։

ֆունկցիա Անկախ միացնում է D սյունակի արժեքը և «_Sales» տեքստային տողը, դրանով իսկ ասելով VPR- ը որ աղյուսակում որոնել: Եթե ​​D3 բջիջը պարունակում է «FL» արժեքը, ապա բանաձևը կփնտրի աղյուսակը FL_Sales, եթե «CA» - աղյուսակում CA_Sales եւ այլն:

Գործառույթների արդյունքը VPR- ը и Անկախ կլինի հետևյալը.

Եթե ​​տվյալները գտնվում են Excel-ի տարբեր գրքերում, ապա դուք պետք է ավելացնեք գրքի անունը նշված տիրույթից առաջ, օրինակ.

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

Եթե ​​ֆունկցիան Անկախ վերաբերում է մեկ այլ աշխատանքային գրքույկի, այդ աշխատանքային գրքույկը պետք է բաց լինի։ Եթե ​​այն փակ է, գործառույթը կհաղորդի սխալի մասին: # REF! (#SSYL!):