Хэрэв таны Excel-ийн ажлын хуудсанд нүднүүдийн өөрчлөгдөж буй мужид суурилсан тооцоо багтсан бол SUM OFFSET томьёоны хамт SUM болон OFFSET функцийг ашиглан тооцооллыг шинэчилж байх ажлыг хялбарчлаарай.
Энэ нийтлэл дэх заавар нь Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010-д зориулагдсан Excel-д хамаарна.
SUM болон OFFSET функцээр динамик муж үүсгэх
Хэрэв та тухайн сарын борлуулалтыг тодорхойлох гэх мэт тогтмол өөрчлөгддөг тооцооллыг ашигладаг бол Excel-ийн OFFSET функцийг ашиглан өдрийн борлуулалтын тоо нэмэгдэх тусам динамик мужийг тохируулна уу.
SUM функц нь дангаараа нийлбэрлэж буй мужид өгөгдлийн шинэ нүдийг оруулахад тохиромжтой. Өгөгдлийг тухайн функц байгаа нүдэнд оруулах үед нэг онцгой тохиолдол гардаг.
Доорх жишээн дээр өдөр бүрийн борлуулалтын шинэ тоон үзүүлэлтүүдийг жагсаалтын доод талд нэмж оруулсан бөгөөд шинэ өгөгдөл нэмэгдэх бүрд нийт дүнг нэг нүдээр доош шилжүүлэхийг албаддаг.
Энэ зааварчилгааг дагаж мөрдөхийн тулд хоосон Excel ажлын хуудсыг нээж, жишээ өгөгдлийг оруулна уу. Таны ажлын хуудсыг жишээ шиг форматлах шаардлагагүй, гэхдээ өгөгдлийг ижил нүдэнд оруулахаа мартуузай.
Өгөгдлийг нэгтгэхэд зөвхөн SUM функцийг ашигладаг бол шинэ өгөгдөл нэмэх бүрд функцийн аргумент болгон ашигладаг нүдний мужийг өөрчлөх шаардлагатай.
SUM болон OFFSET функцийг хамтад нь ашигласнаар нийлсэн муж нь динамик болж, өгөгдлийн шинэ нүдийг багтаахын тулд өөрчлөгдөнө. Мэдээллийн шинэ нүдийг нэмэхэд асуудал үүсгэхгүй, учир нь шинэ нүд нэмэгдэхийн хэрээр хүрээ өөрчлөгдсөөр байна.
Синтакс ба аргументууд
Энэ томъёонд SUM функцийг аргумент болгон нийлүүлсэн өгөгдлийн хүрээг нэгтгэхэд ашигладаг. Энэ мужын эхлэх цэг нь статик бөгөөд томьёогоор нийлэгдэх эхний тооны нүдний лавлагаа гэж тодорхойлогддог.
OFFSET функц нь SUM функц дотор байрласан бөгөөд томъёогоор нийлсэн өгөгдлийн мужид динамик төгсгөлийн цэгийг үүсгэдэг. Энэ нь мужын төгсгөлийн цэгийг томъёоны байршлаас дээш нэг нүдээр тохируулах замаар хийгддэг.
Томьёоны синтакс нь:
=SUM(Мужийн эхлэл:OFFSET(Лавлагаа, мөр, багана))
Аргументууд нь:
- Мужийн эхлэл: SUM функцээр нийлэгдэх нүднүүдийн мужийг эхлүүлэх цэг. Энэ жишээний эхлэл цэг нь B2 нүд байна.
- Лавлах: Хүрээний төгсгөлийн цэгийг тооцоолоход шаардлагатай нүдний лавлагаа. Жишээн дээр лавлагааны аргумент нь томьёоны нүдний лавлагаа болно, учир нь муж нь томьёоны нэг нүдээр төгсдөг.
- Мөр: Офсетийг тооцоолоход ашигласан лавлагааны аргументаас дээш эсвэл доор байгаа мөрийн тоо шаардлагатай. Энэ утга нь эерэг, сөрөг эсвэл тэг байж болно. Хэрэв офсет байршил нь лавлагааны аргументаас дээш байвал утга нь сөрөг байна. Хэрэв офсет доор байгаа бол мөрийн аргумент эерэг байна. Хэрэв офсет нь нэг мөрөнд байрласан бол аргумент нь тэг болно. Энэ жишээн дээр офсет нь лавлагааны аргументаас нэг мөр дээгүүр эхэлдэг тул аргументийн утга сөрөг нэг (-1) байна.
- Cols: Офсетийг тооцоолоход ашигласан лавлагааны аргументын зүүн эсвэл баруун талд байгаа баганын тоо. Энэ утга нь эерэг, сөрөг эсвэл тэг байж болно. Хэрэв офсет байршил нь лавлагааны аргументийн зүүн талд байвал энэ утга сөрөг байна. Хэрэв офсет баруун тийш байвал Колсын аргумент эерэг байна. Энэ жишээн дээр нэгтгэж буй өгөгдөл нь томьёотой ижил баганад байгаа тул энэ аргументын утга тэг байна.
Нийт борлуулалтын өгөгдөлд НИЙТЛЭЛИЙН OFSET томъёог ашиглана
Энэ жишээ нь ажлын хуудасны В баганад жагсаасан өдөр тутмын борлуулалтын тоонуудын нийлбэр дүнг буцаахын тулд НИЙТЛЭЛИЙН OFSET томьёог ашигладаг. Эхэндээ томъёог B6 нүдэнд оруулж, дөрвөн өдрийн борлуулалтын мэдээллийг нэгтгэсэн.
Дараагийн алхам бол 5 дахь өдрийн нийт борлуулалтад зай гаргахын тулд SUM OFFSET томьёог нэг мөр доош зөөх явдал юм. Энэ нь томьёог 7-р эгнээ рүү зөөх шинэ 6 мөр оруулах замаар хийгддэг.
Шилжүүлсний үр дүнд Excel нь лавлагааны аргументыг B7 нүд рүү автоматаар шинэчилж, B6 нүдийг томъёогоор нэгтгэсэн мужид нэмнэ.
- Томъёоны үр дүнг анх харуулах байршил болох B6 нүдийг сонгоно уу.
-
Туузны Томъёо табыг сонгоно уу.
Image -
Математик ба триг сонгоно уу.
Image -
SUM-г сонгоно уу.
Image - Функцийн аргументууд харилцах цонхны Дугаар1 текстийн хайрцагт курсорыг байрлуул.
-
Ажлын хуудасны B2 нүдийг сонгон харилцах цонхонд энэ нүдний лавлагааг оруулна уу. Энэ байршил нь томьёоны статик төгсгөлийн цэг юм.
Image - Функцийн аргументууд харилцах цонхны Дугаар2 текстийн хайрцагт курсорыг байрлуул.
-
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 нүдийг сонгох үед томьёоны мөрөнд шинэчилсэн томъёо гарч ирнэ.
=НИЙЛҮҮЛЭГ(B2:OFSET(B7, -1, 0))
OFFSET функц нь энэ жишээнд ашиглагдаагүй Өндөр ба Өргөн гэсэн хоёр нэмэлт аргументтай. Эдгээр аргументууд нь OFFSET функцэд гаралтын хэлбэрийг мөр, баганын тоогоор илэрхийлдэг.
Эдгээр аргументыг орхисноор функц нь оронд нь лавлагааны аргументийн өндөр болон өргөнийг ашигладаг бөгөөд энэ жишээнд нэг мөр өндөр, нэг баганын өргөн байна.
