Excel SUM եւ OFFSET բանաձեւը

Օգտագործեք SUM եւ OFFSET, տվյալների ամբողջական դինամիկները գտնելու համար

Եթե ​​Ձեր Excel- ի աշխատաթերթը ներառում է հաշվարկներ, փոփոխվող բջիջների շրջանակում, SUM OFFSET բանաձեւով միասին օգտագործելով SUM եւ OFFSET գործառույթները, պարզեցնում է հաշվարկները մինչեւ օրս պահելու խնդիրը:

Ստեղծեք դինամիկ տիրույթ SUM եւ OFFSET գործառույթների հետ

Եթե ​​դուք օգտագործում եք հաշվարկներ ժամանակի ընթացքում, որոնք շարունակաբար փոփոխվում են, օրինակ, ամսվա վաճառքի ծավալը, OFFSET- ի գործառույթը թույլ է տալիս ստեղծել դինամիկ տիրույթ, որը շարունակում է փոփոխել, քանի որ յուրաքանչյուր օրվա վաճառքի տվյալները ավելացվում են:

Իրականում, SUM ֆունկցիան սովորաբար կարող է տեղավորել տվյալների նոր բջիջները, որոնք տեղադրվում են ամփոփված միջակայքում:

Բացառություն է լինում, երբ տվյալները տեղադրվում են այն բջիջում, որտեղ գործում է ներկայումս:

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

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

Այն օգտագործելով SUM եւ OFFSET գործառույթները միասին, սակայն, ընդգրկված տիրույթը դինամիկ է դառնում: Այլ կերպ ասած, այն փոխվում է տվյալների նոր բջիջների տեղակայման համար: Տվյալների նոր բջիջների ավելացումը չի առաջացնում խնդիրներ, քանի որ շարքը շարունակում է հարմարեցնել, քանի որ յուրաքանչյուր նոր բջիջ ավելացված է:

Սինտացիա եւ փաստարկներ

Տեսեք այս նկարը, որը ուղեկցում է այս հոդվածին, հետեւելու այս ձեռնարկին:

Այս բանաձեւում SUM գործառույթը օգտագործվում է որպես իր փաստարկը ներկայացված տվյալների շարքը: Այս միջակայքի մեկնարկային կետը ստատիկ է եւ նույնացվում է որպես բջջային հղում առաջին տողում, որը պետք է կազմվի բանաձեւով:

OFFSET գործառույթը ներկառուցված է SUM ֆունկցիայի ներսում եւ օգտագործվում է դինամիկ վերջնակետ ստեղծել բանաձեւով ընդգրկված տվյալների շարքում: Դա կատարվում է ընդգրկույթի վերջնակետը `բանաձեւի գտնվելու վայրից մեկ խցում:

Բանաձեւի տեքստը `

= SUM (Range Start: OFFSET (Հղում, տողեր, Cols))

Range Start - (պահանջվում է) բջիջների համար սկիզբը, որը կկազմի SUM գործառույթը: Օրինակ պատկերում սա B2 բջիջ է:

Հղում - (պահանջվում է) հեռավորության վերջնակետը հաշվարկելու համար օգտագործվող բջջային տեղեկանք, որը տեղակայված է բազմաթիվ տողերի եւ սյունակների հեռավորության վրա: Օրինակի պատկերում, Reference argument- ը բանաձեւի համար բջջային հղում է, քանի որ միշտ ուզում ենք, որ միջակայքը վերջանա մեկ բանաձեւի վերեւում:

Տողեր - (պարտադիր է) հաշվարկի հաշվարկի մեջ օգտագործվող Արժեքի փաստարկի վերեւից կամ ներքեւում գտնվող շարքերում: Այս արժեքը կարող է լինել դրական, բացասական կամ զրոյի:

Եթե ​​օֆսեթի գտնվելու վայրը վերը նշված Արժեքի փաստարկից բարձր է, ապա այդ արժեքը բացասական է: Եթե ​​ստորեւ գտնվում է, տողերի փաստարկը դրական է: Եթե ​​օֆսեթը գտնվում է նույն շարքում, ապա այս փաստարկը զրո է: Այս օրինակում, օֆսեթը սկսվում է Reference շարադրանքից մեկ տողից, ուստի այս փաստարկի արժեքը բացասական է (-1):

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

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

Օգտագործելով SUM OFFSET բանաձեւը `ընդհանուր վաճառքի տվյալները

Այս օրինակը օգտագործվում է SUM OFFSET բանաձեւի համար, աշխատանքային թերթիկի B սյունակում թվարկված օրական վաճառքի տվյալները վերադարձնելու համար:

Սկզբում բանաձեւը մտել էր B6 բջջային եւ վաճառքի տվյալները կազմել չորս օր:

Հաջորդ քայլը, SUM OFFSET բանաձեւը տատանելու համար, հինգերորդ օրվա վաճառքի համար տեղ տեղադրելու համար:

Սա կատարվում է նոր շարքով 6 տեղադրելով , որը բանաձեւը տեղափոխում է տող 7:

Տեղափոխման արդյունքում Excel- ը ավտոմատ կերպով թարմացնում է Reference argument- ը B7 բջջային բջիջին եւ ավելացնում է բջիջ B6 բանաձեւով ամփոփված տիրույթում:

Մտնելով SUM OFFSET բանաձեւը

1. Սեղմեք B6 բջջի վրա, որը այն վայրն է, որտեղ ձեւակերպման արդյունքներն ի սկզբանե դրսեւորվելու են:
2. Սեղմեք ժապավենի մենյուի բանաձեւերի ներդիրին:
3. Ընտրեք Մաթեմատիկա եւ Տրիգ ժապավենից ֆունկցիան բացելու համար:
4. Ցանկում կտտացրեք SUM- ը ֆունկցիայի երկխոսության վանդակը բարձրացնելու համար:
5. Երկխոսության դաշտում սեղմեք 1-ին տողում:
6. Սեղմեք այս բջիջի հղումը երկխոսության վանդակում մուտք գործելու համար բջիջ B2 : Այս դիրքը բանաձեւի ստատիկ վերջնական կետն է.
7. Երկխոսության դաշտում սեղմեք 2 համարը :
8. Մուտքագրեք հետեւյալ OFFSET գործառույթը ` OFFSET (B6, -1,0) ` բանաձեւի դինամիկ վերջնակետը ձեւավորելու համար:
9. Սեղմեք OK , գործառույթն ավարտելու եւ երկխոսության արկղը փակելու համար:

Ընդհանուր $ 5679,15 հայտնվում B7 բջիջում:

Երբ դուք սեղմում եք B3 բջջի վրա, ամբողջ գործառույթը = SUM (B2: OFFSET (B6, -1,0)) հայտնվում է աշխատանքային թերթիկի վերեւում գտնվող բանաձեւի բարում :

Հաջորդ օրը վաճառքի տվյալները ավելացնելու համար

Հաջորդ օրվա վաճառքի տվյալները ավելացնելու համար `

1. Համատեքստի ընտրացանկը բացելու համար սահեցրեք 6-ի համար անընդմեջ վերնագրի աջ սեղմեք:
2. Մենյուում կտտացրեք Տեղադրելու `աշխատանքային աղյուսակը նոր տող ներդնելու համար:
3. Արդյունքում, SUM OFFSET բանաձեւը տեղափոխվում է B7 բջջի եւ 6-րդ շարքում այժմ դատարկ է:
4. Սեղմեք Ա 6 բջիջը:
5. Մուտքագրեք 5-ը , նշելով, որ վաճառքի ընդհանուր գումարը հինգերորդ օրը մուտքագրվում է:
6. Սեղմեք B6 բջջի վրա:
7. Մուտքագրեք $ 1458.25 համարը եւ ստեղնաշարի վրա մուտքագրեք ստեղնը:

Բջջային B7 թարմացումները $ 7137.40 նոր ընդհանուր գումարով:

Երբ դուք սեղմեք B7 բջջի վրա, բանաձեւի բարում հայտնվում է նորացված բանաձեւը = SUM (B2: OFFSET (B7, -1,0)) :

Նշում . OFFSET ֆունկցիան ունի երկու լրացուցիչ փաստարկներ ` բարձրությունը եւ լայնությունը, որոնք բացակայում են այս օրինակում:

Այս փաստարկները կարող են օգտագործվել OFFSET- ի գործառույթը պատմել արդյունքի ձեւի առումով, քանի որ այդքան շատ տողեր են բարձր եւ այնքան շատ սյունակներ:

Այս փաստարկները անտեսելով, ֆունկցիան, որպես լռելյայն, օգտագործում է «Արժեքի փաստարկ» -ի բարձրությունը եւ լայնությունը, որը, այս օրինակում, մեկ տող է բարձր եւ մեկ սյունակ լայն: