Եթե ձեր Excel-ի աշխատաթերթը ներառում է հաշվարկներ, որոնք հիմնված են բջիջների փոփոխվող տիրույթի վրա, օգտագործեք SUM և OFFSET ֆունկցիաները միասին SUM OFFSET բանաձևում՝ պարզեցնելու համար հաշվարկները թարմացնելու խնդիրը:
Այս հոդվածի հրահանգները վերաբերում են Excel-ին Microsoft 365, Excel 2019, Excel 2016, Excel 2013 և Excel 2010-ի համար:
Ստեղծեք դինամիկ միջակայք SUM և OFFSET ֆունկցիաներով
Եթե դուք օգտագործում եք հաշվարկներ որոշակի ժամանակահատվածի համար, որը անընդհատ փոխվում է, օրինակ՝ ամսվա վաճառքը որոշելը, օգտագործեք OFFSET ֆունկցիան Excel-ում՝ դինամիկ տիրույթ ստեղծելու համար, որը փոխվում է յուրաքանչյուր օրվա վաճառքի թվերի ավելացման հետ մեկտեղ:
Ինքնին, SUM ֆունկցիան սովորաբար կարող է տեղավորել տվյալների նոր բջիջների տեղադրումը ամփոփվող տիրույթում: Մեկ բացառություն է տեղի ունենում, երբ տվյալները տեղադրվում են այն բջիջում, որտեղ ներկայումս գտնվում է ֆունկցիան:
Ստորև բերված օրինակում յուրաքանչյուր օրվա վաճառքի նոր թվերը ավելացվում են ցուցակի ներքևում՝ ստիպելով ընդհանուրը շարունակաբար տեղափոխել մեկ բջիջ ներքև ամեն անգամ, երբ ավելացվում են նոր տվյալները:
Այս ձեռնարկին հետևելու համար բացեք դատարկ Excel-ի աշխատաթերթը և մուտքագրեք նմուշի տվյալները: Ձեր աշխատաթերթը օրինակի նման ձևաչափման կարիք չունի, բայց համոզվեք, որ տվյալները մուտքագրեք նույն բջիջներում:
Եթե միայն SUM ֆունկցիան օգտագործվում է տվյալների գումարման համար, ապա որպես ֆունկցիայի արգումենտ օգտագործվող բջիջների շրջանակը պետք է փոփոխվի ամեն անգամ, երբ նոր տվյալներ են ավելացվում:
Օգտագործելով SUM և OFFSET ֆունկցիաները միասին՝ ընդգրկված միջակայքը դառնում է դինամիկ և փոխվում է՝ տեղավորելու տվյալների նոր բջիջները: Տվյալների նոր բջիջների ավելացումը խնդիրներ չի առաջացնում, քանի որ ընդգրկույթը շարունակում է ճշգրտվել, քանի որ յուրաքանչյուր նոր բջիջ ավելացվում է:
շարահյուսություն և փաստարկներ
Այս բանաձևում SUM ֆունկցիան օգտագործվում է որպես արգումենտ տրամադրված տվյալների տիրույթը գումարելու համար: Այս միջակայքի մեկնարկային կետը ստատիկ է և նույնականացվում է որպես բանաձևով հավաքվող առաջին թվի բջջային հղում:
OFFSET ֆունկցիան տեղադրված է SUM ֆունկցիայի ներսում և ստեղծում է դինամիկ վերջնակետ բանաձևով հավաքված տվյալների տիրույթում: Դա արվում է ընդգրկույթի վերջնակետը բանաձևի գտնվելու վայրից մեկ բջիջ դնելով:
Բանաձևի շարահյուսությունը հետևյալն է.
=SUM(Սկիզբ միջակայքը:OFFSET(Հղում, տողեր, սյունակներ))
Փաստարկներն են՝
- Rang Start. բջիջների տիրույթի մեկնարկային կետը, որը կհավաքվի SUM ֆունկցիայի միջոցով: Այս օրինակում ելակետը B2 բջիջն է։
- Հղում. Բջջի պահանջվող հղումը, որն օգտագործվում է միջակայքի վերջնակետը հաշվարկելու համար: Օրինակում Reference արգումենտը բանաձևի բջջային հղումն է, քանի որ միջակայքը վերջանում է բանաձևից մեկ բջիջ վերև:
- Տողեր. Հղման արգումենտից վեր կամ ներքև տողերի քանակը պահանջվում է, որն օգտագործվում է օֆսեթը հաշվարկելիս: Այս արժեքը կարող է լինել դրական, բացասական կամ սահմանվել զրոյի: Եթե օֆսեթ տեղադրությունը գտնվում է Reference արգումենտից վեր, արժեքը բացասական է: Եթե շեղումը ներքևում է, տողերի արգումենտը դրական է: Եթե օֆսեթը գտնվում է նույն տողում, ապա արգումենտը զրո է: Այս օրինակում օֆսեթը սկսվում է Reference արգումենտից մեկ տողով, ուստի արգումենտի արժեքը բացասական է (-1):
- Cols. Հղման արգումենտի ձախ կամ աջ կողմում գտնվող սյունակների քանակը, որոնք օգտագործվում են օֆսեթը հաշվարկելու համար: Այս արժեքը կարող է լինել դրական, բացասական կամ սահմանվել զրոյի: Եթե օֆսեթ տեղադրությունը գտնվում է Reference արգումենտի ձախ կողմում, ապա այս արժեքը բացասական է: Եթե շեղումը դեպի աջ է, ապա Cols-ի փաստարկը դրական է: Այս օրինակում հավաքվող տվյալները գտնվում են բանաձևի նույն սյունակում, ուստի այս արգումենտի արժեքը զրո է:
Օգտագործեք SUM OFFSET բանաձևը ընդհանուր վաճառքի տվյալների համար
Այս օրինակը օգտագործում է SUM OFFSET բանաձևը՝ աշխատանքային թերթի B սյունակում թվարկված օրական վաճառքի թվերի ընդհանուր գումարը վերադարձնելու համար: Սկզբում բանաձևը մուտքագրվեց B6 բջիջ և հավաքեց չորս օրվա վաճառքի տվյալները:
Հաջորդ քայլը SUM OFFSET բանաձևը մեկ տող ներքև տեղափոխելն է՝ հինգերորդ օրվա ընդհանուր վաճառքի համար տեղ ազատելու համար: Սա կատարվում է նոր 6-րդ տող տեղադրելով, որը բանաձևը տեղափոխում է 7-րդ տող:
Տեղափոխման արդյունքում Excel-ը ավտոմատ կերպով թարմացնում է Reference արգումենտը B7 բջիջ և ավելացնում է B6 բջիջը բանաձևով ամփոփված միջակայքին:
- Ընտրեք B6 բջիջը, որն այն վայրն է, որտեղ ի սկզբանե կցուցադրվեն բանաձևի արդյունքները:
-
Ընտրեք ժապավենի Formulas ներդիրը:
Image -
Ընտրեք Math & Trig.
Image -
Ընտրեք SUM.
Image - Function Arguments երկխոսության վանդակում տեղադրեք կուրսորը Number1 տեքստային վանդակում:
-
Աշխատանքային թերթում ընտրեք B2 բջիջը՝ այս բջիջի հղումը երկխոսության վանդակում մուտքագրելու համար: Այս տեղադրությունը բանաձևի ստատիկ վերջնակետն է։
Image - Function Arguments երկխոսության վանդակում տեղադրեք կուրսորը Number2 տեքստային վանդակում:
-
Մուտքագրեք OFFSET(B6, -1, 0): Այս OFFSET ֆունկցիան ձևավորում է բանաձևի դինամիկ վերջնակետը:
Image -
Ընտրեք OK ֆունկցիան ավարտելու և երկխոսության տուփը փակելու համար: Ընդհանուր թիվը հայտնվում է B6 բջիջում:
Image
Ավելացնել հաջորդ օրվա վաճառքի տվյալները
Հաջորդ օրվա վաճառքի տվյալները ավելացնելու համար՝
- Աջ սեղմեք տողի վերնագրի վրա 6-րդ տողի համար:
-
Ընտրեք Insert՝ աշխատաթերթում նոր տող տեղադրելու համար: SUM OFFSET բանաձևը մեկ տողով շարժվում է դեպի B7 բջիջ, իսկ 6-րդ տողն այժմ դատարկ է:
Image - Ընտրեք A6 բջիջը և մուտքագրեք 5 համարը՝ նշելու, որ հինգերորդ օրվա վաճառքի ընդհանուր գումարը մուտքագրված է:
-
Ընտրեք B6 բջիջը, մուտքագրեք $1458.25, ապա սեղմեք Enter.
Image - Cell B7-ի թարմացումներ 7137,40 դոլարի նոր ընդհանուր գումարի համար:
Երբ ընտրում եք B7 բջիջը, նորացված բանաձևը հայտնվում է բանաձևերի տողում:
=SUM(B2:OFFSET(B7, -1, 0))
OFFSET ֆունկցիան ունի երկու կամընտիր արգումենտ՝ Height և Width, որոնք չեն օգտագործվել այս օրինակում: Այս արգումենտները OFFSET ֆունկցիային ասում են ելքի ձևը՝ ըստ տողերի և սյունակների քանակի։
Բաց թողնելով այս արգումենտները՝ ֆունկցիան փոխարենը օգտագործում է Reference արգումենտի բարձրությունը և լայնությունը, որն այս օրինակում մեկ տող բարձր է և մեկ սյունակ լայնություն։
