თუ თქვენი 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(მინიშნება, სტრიქონები, სვეტები))
არგუმენტებია:
- დიაპაზონის დაწყება: ამოსავალი წერტილი უჯრედების დიაპაზონისთვის, რომელიც დაჯილდოვდება SUM ფუნქციით. ამ მაგალითში, საწყისი წერტილი არის უჯრედი B2.
- მინიშნება: უჯრედის საჭირო მითითება, რომელიც გამოიყენება დიაპაზონის საბოლოო წერტილის გამოსათვლელად. მაგალითში, Reference არგუმენტი არის უჯრედის მითითება ფორმულისთვის, რადგან დიაპაზონი მთავრდება ფორმულის ზემოთ ერთი უჯრედით.
- მწკრივები: საჭიროა სტრიქონების რაოდენობა საცნობარო არგუმენტის ზემოთ ან ქვემოთ, რომელიც გამოიყენება ოფსეტის გამოსათვლელად. ეს მნიშვნელობა შეიძლება იყოს დადებითი, უარყოფითი ან ნულზე დაყენებული. თუ ოფსეტური მდებარეობა არის Reference არგუმენტის ზემოთ, მნიშვნელობა უარყოფითია. თუ ოფსეტი ქვემოთაა, რიგების არგუმენტი დადებითია. თუ ოფსეტი მდებარეობს იმავე რიგში, არგუმენტი არის ნული. ამ მაგალითში, ოფსეტი იწყება ერთი მწკრივის ზემოთ Reference არგუმენტი, ამიტომ არგუმენტის მნიშვნელობა არის უარყოფითი ერთი (-1).
- Cols: სვეტების რაოდენობა მითითების არგუმენტის მარცხნივ ან მარჯვნივ, რომელიც გამოიყენება ოფსეტის გამოსათვლელად. ეს მნიშვნელობა შეიძლება იყოს დადებითი, უარყოფითი ან ნულზე დაყენებული. თუ ოფსეტური მდებარეობა არის Reference არგუმენტის მარცხნივ, ეს მნიშვნელობა უარყოფითია. თუ ოფსეტი მარჯვნივ არის, Cols არგუმენტი დადებითია. ამ მაგალითში, შეკრებილი მონაცემები იმავე სვეტშია, როგორც ფორმულა, ამიტომ ამ არგუმენტის მნიშვნელობა არის ნული.
გამოიყენეთ SUM OFFSET ფორმულა მთლიანი გაყიდვების მონაცემებზე
ეს მაგალითი იყენებს SUM OFFSET ფორმულას, რათა დააბრუნოს ჯამი სამუშაო ფურცლის B სვეტში ჩამოთვლილი ყოველდღიური გაყიდვების მაჩვენებლებისთვის. თავდაპირველად, ფორმულა შევიდა B6 უჯრედში და შეადგინა გაყიდვების მონაცემები ოთხი დღის განმავლობაში.
შემდეგი ნაბიჯი არის SUM OFFSET ფორმულის ერთი რიგის ქვემოთ გადატანა, რათა ადგილი დარჩეს მეხუთე დღის გაყიდვების ჯამურ რაოდენობას. ეს მიიღწევა ახალი მე-6 მწკრივის ჩასმით, რომელიც გადააქვს ფორმულას მე-7 რიგში.
გადაადგილების შედეგად, Excel ავტომატურად აახლებს Reference არგუმენტს B7 უჯრედში და ამატებს უჯრედს B6 დიაპაზონს ფორმულით შეჯამებულ დიაპაზონში.
- აირჩიეთ უჯრედი B6, რომელიც არის ადგილი, სადაც თავდაპირველად გამოჩნდება ფორმულის შედეგები.
-
აირჩიეთ ლენტის ფორმულები ჩანართი.
Image -
აირჩიეთ მათემატიკა და ტრიგ.
Image -
აირჩიეთ SUM.
Image - ფუნქციის არგუმენტები დიალოგურ ფანჯარაში მოათავსეთ კურსორი Number1 ტექსტურ ველში..
-
სამუშაო ფურცელში აირჩიეთ უჯრედი B2, რათა შეიყვანოთ ეს უჯრედის მითითება დიალოგურ ფანჯარაში. ეს მდებარეობა არის ფორმულის სტატიკური საბოლოო წერტილი.
Image - ფუნქციის არგუმენტები დიალოგურ ფანჯარაში მოათავსეთ კურსორი 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 ფუნქციას აქვს ორი არჩევითი არგუმენტი: სიმაღლე და სიგანე, რომლებიც არ იყო გამოყენებული ამ მაგალითში. ეს არგუმენტები OFFSET ფუნქციას ეუბნება გამოსავლის ფორმას მწკრივებისა და სვეტების რაოდენობის მიხედვით.
ამ არგუმენტების გამოტოვებით, ფუნქცია იყენებს Reference არგუმენტის სიმაღლეს და სიგანეს, რომელიც ამ მაგალითში არის ერთი მწკრივის სიმაღლე და ერთი სვეტის სიგანე.
