Գտնել մոտակա համարը – Առողջ սնունդ ինձ մոտ

Բովանդակություն

Գործնականում շատ հաճախ լինում են դեպքեր, երբ ես և դու պետք է գտնենք ամենամոտ արժեքը հավաքածուում (աղյուսակում) տվյալ թվի նկատմամբ։ Դա կարող է լինել, օրինակ.

Զեղչի հաշվարկ՝ կախված ծավալից։
Բոնուսների չափի հաշվարկ՝ կախված պլանի իրականացումից:
Առաքման սակագների հաշվարկ՝ կախված հեռավորությունից:
Ապրանքների համար հարմար տարաների ընտրություն և այլն:

Ավելին, կլորացումը կարող է պահանջվել ինչպես վերև, այնպես էլ վար՝ կախված իրավիճակից:

Նման խնդիրը լուծելու մի քանի եղանակ կա՝ ակնհայտ և ոչ այնքան ակնհայտ: Դիտարկենք դրանք հաջորդաբար:

Սկսելու համար, եկեք պատկերացնենք մի մատակարար, որը զեղչեր է տալիս մեծածախի վրա, և զեղչի տոկոսը կախված է գնված ապրանքների քանակից: Օրինակ՝ 5 հատից ավելի գնելիս տրվում է 2% զեղչ, իսկ 20 հատից գնելիս՝ արդեն 6% և այլն։

Ինչպե՞ս արագ և գեղեցիկ հաշվարկել զեղչի տոկոսը գնված ապրանքների քանակը մուտքագրելիս:

Մեթոդ 1. Ներդրված IF-ներ

Մեթոդ «Ի՞նչ կա մտածելու, պետք է ցատկել» շարքից։ Ներդրված գործառույթների օգտագործումը IF (ԵԹԵ) հաջորդաբար ստուգելու համար, թե արդյոք բջիջի արժեքը ընկնում է յուրաքանչյուր ինտերվալների մեջ և ցուցադրում է զեղչ համապատասխան տիրույթի համար: Բայց այս դեպքում բանաձևը կարող է շատ ծանրաբեռնված լինել.

Գտնելով մոտակա թիվը

Կարծում եմ, ակնհայտ է, որ նման «հրեշ տիկնիկին» վրիպազերծելը կամ որոշ ժամանակ անց դրան մի քանի նոր պայմաններ ավելացնելու փորձը զվարճալի է:

Բացի այդ, Microsoft Excel-ն ունի IF ֆունկցիայի ներդիրի սահմանափակում՝ 7 անգամ հին տարբերակներում և 64 անգամ ավելի նոր տարբերակներում: Իսկ եթե ձեզ ավելին է պետք:

Մեթոդ 2. VLOOKUP ինտերվալային դիտմամբ

Այս մեթոդը շատ ավելի կոմպակտ է: Զեղչի տոկոսը հաշվարկելու համար օգտագործեք լեգենդար ֆունկցիան VPR- ը (VLOOKUP) մոտավոր որոնման ռեժիմում.

Գտնելով մոտակա թիվը

որտեղ

B4 – ապրանքների քանակի արժեքը առաջին գործարքում, որի համար մենք զեղչ ենք փնտրում
$G$4:$H$8 – հղում դեպի զեղչային աղյուսակ՝ առանց «վերնագրի» և $ նշանով ամրագրված հասցեներով:
2 — զեղչի աղյուսակի սյունակի հերթական համարը, որից ցանկանում ենք ստանալ զեղչի արժեքը
TRUE – այստեղ է թաղված «շունը»։ Եթե որպես վերջին ֆունկցիայի փաստարկ VPR- ը նշեք ՍՈՒՏ (ՍՈՒՏ) կամ 0, ապա գործառույթը կփնտրի խիստ համընկնում քանակի սյունակում (իսկ մեր դեպքում այն կտա #N/A սխալ, քանի որ զեղչի աղյուսակում 49 արժեք չկա): Բայց եթե փոխարենը ՍՈՒՏ գրել TRUE (ՃԻՇՏ) կամ 1, ապա ֆունկցիան կփնտրի ոչ թե ճշգրիտ, այլ ամենամոտ ամենափոքրը արժեքը և կտա մեզ անհրաժեշտ զեղչի տոկոսը:

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

Գտնելով մոտակա թիվը

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

Մեթոդ 3. Գտնել ամենամոտ ամենամեծը՝ օգտագործելով INDEX և MATCH ֆունկցիաները

Հիմա մեր խնդրին նայենք մյուս կողմից։ Ենթադրենք, մենք վաճառում ենք տարբեր հզորությունների արդյունաբերական պոմպերի մի քանի մոդելներ։ Ձախ կողմում գտնվող վաճառքի աղյուսակը ցույց է տալիս հաճախորդի պահանջվող հզորությունը: Մենք պետք է ընտրենք մոտակա առավելագույն կամ հավասար հզորության պոմպ, բայց ոչ պակաս, քան պահանջվում է նախագծով:

VLOOKUP ֆունկցիան այստեղ չի օգնի, այնպես որ դուք ստիպված կլինեք օգտագործել դրա անալոգը` INDEX ֆունկցիաների մի փունջ: (INDEX) և ԱՎԵԼԻ ԲԱՑՎԱԾ (ՄԱՏՉ):

Գտնելով մոտակա թիվը

Այստեղ MATCH ֆունկցիան վերջին արգումենտով -1 աշխատում է մոտակա ամենամեծ արժեքը գտնելու ռեժիմում, իսկ INDEX ֆունկցիան այնուհետև հարակից սյունակից հանում է մեզ անհրաժեշտ մոդելի անունը:

Մեթոդ 4. Նոր ֆունկցիա VIEW (XLOOKUP)

Եթե ունեք Office 365-ի տարբերակ՝ տեղադրված բոլոր թարմացումներով, ապա VLOOKUP-ի փոխարեն (VLOOKUP) կարող եք օգտագործել դրա անալոգը` VIEW ֆունկցիան (XLOOKUP), որը ես արդեն մանրամասն վերլուծել եմ.

Գտնելով մոտակա թիվը

Այստեղ `

B4 – ապրանքի քանակի սկզբնական արժեքը, որի համար մենք փնտրում ենք զեղչ
$G$4:$G$8 – այն շրջանակը, որտեղ մենք փնտրում ենք համընկնումներ
$ H $ 4: $ H $ 8 – արդյունքների շրջանակը, որից ցանկանում եք վերադարձնել զեղչը
չորրորդ փաստարկ (-1) ներառում է մեր ուզած մոտակա ամենափոքր թվի որոնումը ճշգրիտ համընկնման փոխարեն:

Այս մեթոդի առավելություններն այն են, որ զեղչերի աղյուսակը տեսակավորելու կարիք չկա և անհրաժեշտության դեպքում ոչ միայն մոտակա ամենափոքր, այլև մոտակա ամենամեծ արժեքը որոնելու հնարավորություն: Վերջին փաստարկն այս դեպքում կլինի 1-ը:

Սակայն, ցավոք, դեռ ոչ բոլորն ունեն այս հատկությունը՝ միայն Office 365-ի երջանիկ սեփականատերերը:

Մեթոդ 5. Power Query

Եթե դեռ ծանոթ չեք Excel-ի հզոր և լիովին անվճար Power Query հավելմանը, ապա այստեղ եք: Եթե արդեն ծանոթ եք, ապա եկեք փորձենք օգտագործել այն մեր խնդիրը լուծելու համար։

Սկզբում կատարենք մի քանի նախապատրաստական աշխատանք.

Եկեք փոխարկենք մեր աղբյուրի աղյուսակները դինամիկ (խելացի) ստեղնաշարի դյուրանցման միջոցով Ctrl+T կամ թիմ Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ).
Պարզության համար եկեք նրանց անուններ տանք։ Sales и Զեղչեր ականջակալ շինարար (Դիզայն).
Սեղաններից յուրաքանչյուրը հերթով բեռնեք Power Query-ում՝ օգտագործելով կոճակը Սեղանից / միջակայքից ականջակալ Ամսաթիվ (Տվյալներ — աղյուսակից/տիրույթից). Excel-ի վերջին տարբերակներում այս կոճակը վերանվանվել է Տերեւներով (թերթից).
Եթե աղյուսակները ունեն տարբեր սյունակների անվանումներ՝ քանակներով, ինչպես մեր օրինակում («Ապրանքների քանակ» և «Քանակը…»), ապա դրանք պետք է վերանվանվեն Power Query-ում և անվանվեն նույնը:
Դրանից հետո կարող եք վերադառնալ Excel՝ ընտրելով հրամանը Power Query խմբագրիչի պատուհանում Գլխավոր — Փակել և բեռնել — Փակել և բեռնել… (Տուն — Փակել և բեռնել — Փակել և բեռնել…) իսկ հետո տարբերակ Պարզապես կապ ստեղծեք (Միայն ստեղծել կապ).
Հետո սկսվում է ամենահետաքրքիրը. Եթե դուք փորձ ունեք Power Query-ում, ապա ես ենթադրում եմ, որ հետագա մտքի գիծը պետք է լինի այս երկու աղյուսակների միացման հարցում (միաձուլման) a la VLOOKUP-ի հետ միաձուլելու ուղղությամբ, ինչպես դա եղել է նախորդ մեթոդի դեպքում: Փաստորեն, մեզ անհրաժեշտ կլինի միաձուլվել ավելացնելու ռեժիմում, որն առաջին հայացքից բոլորովին ակնհայտ չէ։ Ընտրեք Excel ներդիրում Տվյալներ – Ստացեք տվյալներ – Համակցեք հարցումները – Ավելացնել (Տվյալներ — Ստացեք տվյալներ — Միավորել հարցումները — Հավելված) իսկ հետո մեր սեղանները Sales и Զեղչեր պատուհանում, որը հայտնվում է.
Սեղմելուց հետո OK մեր սեղանները կսոսնձվեն մեկ ամբողջության մեջ՝ մեկը մյուսի տակ: Խնդրում ենք նկատի ունենալ, որ այս աղյուսակներում ապրանքների քանակով սյունակներն ընկել են միմյանց տակ, քանի որ. նրանք ունեն նույն անունը.
Եթե վաճառքի աղյուսակում տողերի սկզբնական հաջորդականությունը կարևոր է ձեզ համար, ապա, որպեսզի բոլոր հետագա փոխակերպումներից հետո կարողանաք վերականգնել այն, ավելացրեք համարակալված սյունակ մեր աղյուսակում՝ օգտագործելով հրամանը: Սյունակի ավելացում – ինդեքսի սյունակ (Ավելացնել սյունակ — ինդեքսի սյունակ). Եթե տողերի հաջորդականությունը ձեզ համար նշանակություն չունի, ապա կարող եք բաց թողնել այս քայլը:
Այժմ, օգտագործելով աղյուսակի վերնագրի բացվող ցուցակը, դասավորեք այն ըստ սյունակի Քանակ Բարձրանում:
Եվ գլխավոր հնարքը՝ աջ սեղմեք սյունակի վերնագրի վրա Զեղչել ընտրիր թիմ Լրացնել – Ներքև (Լրացնել — Ներքև). Դատարկ բջիջների հետ զրո ավտոմատ կերպով լրացվում է նախորդ զեղչի արժեքներով.
Մնում է վերականգնել տողերի սկզբնական հաջորդականությունը՝ տեսակավորելով ըստ սյունակների ինդեքս (հետագայում կարող եք ապահով ջնջել այն) և ֆիլտրով ազատվել ավելորդ գծերից զրո ըստ սյունակի Գործարքի կոդը: