Բովանդակություն
Վերջերս մենք հոդված ենք նվիրել Excel-ի ամենաօգտակար գործառույթներից մեկին, որը կոչվում է VPR- ը և ցույց տվեց, թե ինչպես կարելի է այն օգտագործել տվյալների բազայից անհրաժեշտ տեղեկատվությունը աշխատանքային թերթիկի բջիջ հանելու համար: Մենք նաև նշեցինք, որ ֆունկցիայի օգտագործման երկու դեպք կա VPR- ը և դրանցից միայն մեկն է զբաղվում տվյալների բազայի հարցումներով: Այս հոդվածում դուք կսովորեք ֆունկցիան օգտագործելու ևս մեկ քիչ հայտնի միջոց VPR- ը Excel- ում:
Եթե դեռ չեք արել դա, ապա անպայման կարդացեք ֆունկցիայի մասին վերջին հոդվածը VPR- ը, քանի որ ստորև բերված ամբողջ տեղեկատվությունը ենթադրում է, որ դուք արդեն ծանոթ եք առաջին հոդվածում նկարագրված սկզբունքներին։
Տվյալների բազաների, գործառույթների հետ աշխատելիս VPR- ը փոխանցվում է եզակի նույնացուցիչ, որն օգտագործվում է նույնականացնելու այն տեղեկատվությունը, որը մենք ցանկանում ենք գտնել (օրինակ՝ ապրանքի կոդը կամ հաճախորդի նույնականացման համարը): Այս եզակի կոդը պետք է առկա լինի տվյալների բազայում, հակառակ դեպքում VPR- ը կհայտնի սխալի մասին: Այս հոդվածում մենք կանդրադառնանք ֆունկցիայի օգտագործման այս եղանակին VPR- ըերբ id-ն ընդհանրապես գոյություն չունի տվյալների բազայում: Ոնց որ ֆունկցիան VPR- ը անցել է մոտավոր ռեժիմի և ընտրում է, թե ինչ տվյալներ տրամադրել մեզ, երբ մենք ուզում ենք ինչ-որ բան գտնել: Որոշակի հանգամանքներում դա հենց այն է, ինչ անհրաժեշտ է:
Օրինակ կյանքից. Մենք խնդիր ենք դրել
Եկեք այս հոդվածը ցույց տանք իրական օրինակով. միջնորդավճարների հաշվարկ՝ հիմնված վաճառքի չափման լայն շրջանակի վրա: Մենք կսկսենք շատ պարզ տարբերակից, այնուհետև աստիճանաբար կբարդացնենք այն, մինչև խնդրի միակ ռացիոնալ լուծումը լինի գործառույթի օգտագործումը. VPR- ը. Մեր ֆիկտիվ առաջադրանքի նախնական սցենարը հետևյալն է. եթե վաճառողը մեկ տարում 30000 դոլարից ավելի վաճառք է անում, ապա նրա միջնորդավճարը կազմում է 30%: Հակառակ դեպքում միջնորդավճարը կազմում է ընդամենը 20 տոկոս։ Եկեք այն դնենք աղյուսակի տեսքով.
Վաճառողը մուտքագրում է իր վաճառքի տվյալները B1 բջիջում, իսկ B2 բջիջի բանաձևը որոշում է ճիշտ միջնորդավճարը, որը կարող է ակնկալել վաճառողը: Իր հերթին, ստացված դրույքաչափն օգտագործվում է B3 բջիջում՝ հաշվարկելու ընդհանուր միջնորդավճարը, որը պետք է ստանա վաճառողը (պարզապես բազմապատկելով B1 և B2 բջիջները):
Աղյուսակի ամենահետաքրքիր մասը պարունակվում է B2 բջիջում. սա միջնորդավճարը որոշելու բանաձևն է: Այս բանաձևը պարունակում է Excel ֆունկցիա, որը կոչվում է IF (ԵԹԵ): Այն ընթերցողների համար, ովքեր ծանոթ չեն այս գործառույթին, ես կբացատրեմ, թե ինչպես է այն աշխատում.
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Պայման ֆունկցիայի արգումենտ է, որն ընդունում է որևէ մեկի արժեքը TRԻՇՏ ԿՈԴ (ՃԻՇՏ), կամ ԿԵՂԾ (ՍՈՒՏ): Վերևի օրինակում B1 արտահայտությունը
Ճի՞շտ է, որ B1-ը B5-ից փոքր է:
Կամ կարող եք այլ կերպ ասել.
Ճի՞շտ է, որ տարվա ընթացքում վաճառքի ընդհանուր գումարը շեմային արժեքից պակաս է։
Եթե այս հարցին պատասխանենք ԱՅՈ (TRUE), ապա ֆունկցիան վերադառնում է արժեքը, եթե ճիշտ է (արժեքը, եթե ՃԻՇՏ է): Մեր դեպքում սա կլինի B6 բջիջի արժեքը, այսինքն՝ միջնորդավճար, երբ ընդհանուր վաճառքը գտնվում է շեմից ցածր: Եթե պատասխանենք հարցին NO (FALSE), ապա վերադառնում է արժեքը, եթե կեղծ է (արժեքը, եթե FALSE): Մեր դեպքում սա B7 բջիջի արժեքն է, այսինքն՝ միջնորդավճարը, երբ ընդհանուր վաճառքը գերազանցում է շեմը:
Ինչպես տեսնում եք, եթե վերցնենք $20000 ընդհանուր վաճառքը, B2 բջիջում կստանանք 20% միջնորդավճար: Եթե մուտքագրենք $40000 արժեքը, ապա միջնորդավճարը կփոխվի 30%-ով.
Ահա թե ինչպես է աշխատում մեր սեղանը.
Մենք բարդացնում ենք խնդիրը
Եկեք ամեն ինչ մի փոքր ավելի բարդացնենք: Եկեք մեկ այլ շեմ սահմանենք. եթե վաճառողը վաստակում է ավելի քան $40000, ապա միջնորդավճարը բարձրանում է մինչև 40%:
Թվում է, թե ամեն ինչ պարզ է և պարզ, բայց B2 բջիջի մեր բանաձևը նկատելիորեն ավելի բարդ է դառնում: Եթե ուշադիր նայեք բանաձևին, ապա կտեսնեք, որ ֆունկցիայի երրորդ արգումենտը IF (IF) վերածվել է մեկ այլ լիարժեք գործառույթի IF (ԵԹԵ): Այս կոնստրուկցիան կոչվում է ֆունկցիաների բույն դրություն միմյանց մեջ։ Excel-ը ուրախությամբ թույլ է տալիս այս կոնստրուկցիաները, և դրանք նույնիսկ աշխատում են, բայց դրանք շատ ավելի դժվար է կարդալ և հասկանալ:
Մենք չենք խորանա տեխնիկական մանրամասների մեջ՝ ինչու և ինչպես է այն աշխատում, և չենք խորանալու ներդիր ֆունկցիաներ գրելու նրբություններին: Ի վերջո, սա գործառույթին նվիրված հոդված է VPR- ը, Excel-ի ամբողջական ուղեցույց չէ:
Ինչ էլ որ լինի, բանաձևն ավելի է բարդանում։ Ի՞նչ կլինի, եթե մենք ներկայացնենք մեկ այլ տարբերակ 50% միջնորդավճարի համար այն վաճառողների համար, ովքեր 50000 դոլարից ավելի են վաճառքում: Իսկ եթե ինչ-որ մեկը 60000 դոլարից ավել է վաճառել, 60% միջնորդավճար կվճարի՞։
Այժմ B2 բջիջի բանաձեւը, նույնիսկ եթե այն գրված է առանց սխալների, դարձել է բոլորովին անընթեռնելի։ Կարծում եմ, որ քիչ են նրանք, ովքեր ցանկանում են իրենց նախագծերում օգտագործել 4 մակարդակի բույն ունեցող բանաձևեր։ Պետք է ավելի հեշտ ճանապարհ լինի?!
Եվ կա այդպիսի ճանապարհ! Գործառույթը կօգնի մեզ VPR- ը.
Խնդիրը լուծելու համար կիրառում ենք VLOOKUP ֆունկցիան
Եկեք մի փոքր փոխենք մեր սեղանի դիզայնը։ Մենք կպահենք բոլոր նույն դաշտերն ու տվյալները, բայց դրանք դասավորելու ենք նոր, ավելի կոմպակտ ձևով.
Մի պահ տրամադրեք և համոզվեք, որ նոր սեղանը Գնահատել աղյուսակ ներառում է նույն տվյալները, ինչ նախորդ շեմային աղյուսակը:
Հիմնական գաղափարը գործառույթն օգտագործելն է VPR- ը աղյուսակի համաձայն որոշելու ցանկալի սակագնի դրույքաչափը Գնահատել աղյուսակ կախված վաճառքի ծավալից. Խնդրում ենք նկատի ունենալ, որ վաճառողը կարող է ապրանքներ վաճառել այնպիսի գումարով, որը հավասար չէ աղյուսակի հինգ շեմերից մեկին: Օրինակ՝ նա կարող էր վաճառել 34988 դոլարով, բայց նման գումար չկա։ Տեսնենք, թե ինչպես է գործում VPR- ը կարող է դիմակայել նման իրավիճակին.
VLOOKUP ֆունկցիայի տեղադրում
Ընտրեք B2 բջիջը (որտեղ մենք ցանկանում ենք տեղադրել մեր բանաձևը) և գտնել VLOOKUP- ը (VLOOKUP) Excel-ի գործառույթների գրադարանում. Բանաձեւեր (բանաձևեր) > Ֆունկցիոնալ գրադարան (Function Library) > Փնտրում և տեղեկանք (Հղումներ և զանգվածներ):
Երկխոսության տուփ է հայտնվում Գործառույթի փաստարկները (Ֆունկցիայի փաստարկներ): Մենք հերթով լրացնում ենք արգումենտների արժեքները՝ սկսած Փնտրման_ արժեք (Փնտրել_արժեքը): Այս օրինակում սա B1 բջիջից վաճառքի ընդհանուր գումարն է: Կուրսորը դրեք դաշտում Փնտրման_ արժեք (Lookup_value) և ընտրեք B1 բջիջը:
Հաջորդը, դուք պետք է նշեք գործառույթները VPR- ըորտեղ փնտրել տվյալներ: Մեր օրինակում սա աղյուսակ է Գնահատել աղյուսակ. Կուրսորը դրեք դաշտում Աղյուսակ_ զանգված (Աղյուսակ) և ընտրեք ամբողջ աղյուսակը Գնահատել աղյուսակբացառությամբ վերնագրերի:
Հաջորդը, մենք պետք է նշենք, թե որ սյունակից պետք է տվյալների հանել՝ օգտագործելով մեր բանաձևը: Մեզ հետաքրքրում է միջնորդավճարը, որը գտնվում է աղյուսակի երկրորդ սյունակում։ Հետեւաբար, փաստարկի համար Col_index_num (Column_number) մուտքագրեք արժեքը 2:
Եվ վերջապես ներկայացնում ենք վերջին փաստարկը. Range_lookup (Interval_lookup):
Կարեւոր է. այս փաստարկի օգտագործումն է, որ տարբերություն է դնում ֆունկցիայի կիրառման երկու եղանակների միջև VPR- ը. Տվյալների բազաների հետ աշխատելիս փաստարկը Range_lookup (range_lookup) միշտ պետք է արժեք ունենա ԿԵՂԾ (FALSE) ճշգրիտ համընկնում փնտրելու համար: Գործառույթի մեր օգտագործման մեջ VPR- ը, մենք պետք է դատարկ թողնենք այս դաշտը կամ մուտքագրենք արժեք TRԻՇՏ ԿՈԴ (ՃԻՇՏ). Չափազանց կարևոր է այս տարբերակը ճիշտ ընտրելը։
Ավելի պարզ դարձնելու համար կներկայացնենք TRԻՇՏ ԿՈԴ (ՃԻՇՏ) դաշտում Range_lookup (Interval_lookup): Չնայած, եթե դաշտը դատարկ թողնեք, դա սխալ չի լինի, քանի որ TRԻՇՏ ԿՈԴ նրա լռելյայն արժեքն է.
Մենք լրացրել ենք բոլոր պարամետրերը։ Այժմ մենք սեղմում ենք OK, իսկ Excel-ը մեզ համար բանաձև է ստեղծում ֆունկցիայով VPR- ը.
Եթե մենք փորձարկենք մի քանի տարբեր արժեքներ վաճառքի ընդհանուր գումարի համար, ապա մենք կհամոզվենք, որ բանաձևը ճիշտ է աշխատում:
Եզրափակում
Երբ ֆունկցիան VPR- ը աշխատում է տվյալների շտեմարաններով, փաստարկ Range_lookup (range_lookup) պետք է ընդունվի ԿԵՂԾ (ՍՈՒՏ): Եվ արժեքը մուտքագրված է որպես Փնտրման_ արժեք (Lookup_value) պետք է գոյություն ունենա տվյալների բազայում: Այլ կերպ ասած, այն փնտրում է ճշգրիտ համապատասխանություն:
Այս հոդվածում մեր դիտարկած օրինակում ճշգրիտ համընկնում ստանալու կարիք չկա: Սա այն դեպքն է, երբ ֆունկցիան VPR- ը պետք է անցնի մոտավոր ռեժիմի՝ ցանկալի արդյունքը վերադարձնելու համար:
Օրինակ ` Մենք ցանկանում ենք որոշել, թե ինչ դրույքաչափ պետք է օգտագործվի 34988 դոլար վաճառքի ծավալ ունեցող վաճառողի համար միջնորդավճարի հաշվարկում: Գործառույթ VPR- ը վերադարձնում է մեզ 30% արժեք, որը բացարձակապես ճիշտ է: Բայց ինչո՞ւ է բանաձևն ընտրել ուղիղ 30% պարունակող տողը և ոչ թե 20% կամ 40%: Ի՞նչ է նշանակում մոտավոր որոնում: Եկեք պարզ լինենք.
Երբ փաստարկը Range_lookup (interval_lookup) ունի արժեք TRԻՇՏ ԿՈԴ (ՃԻՇՏ) կամ բաց թողնված, ֆունկցիա VPR- ը կրկնվում է առաջին սյունակի միջով և ընտրում ամենամեծ արժեքը, որը չի գերազանցում որոնման արժեքը:
Կարեւոր կետ ` Որպեսզի այս սխեման աշխատի, աղյուսակի առաջին սյունակը պետք է տեսակավորվի աճման կարգով: