Excel SUM და ოფშორული ფორმულა

გამოიყენეთ SUM და OFFSET მონაცემების დინამიკური მერყეობს

იმ შემთხვევაში, თუ თქვენი Excel worksheet შეიცავს გათვლებს ცვალებადი სპექტრი უჯრედების გამოყენებით SUM და OFFSET ფუნქციების ერთად SUM OFFSET ფორმულა ამარტივებს ამოცანა შენახვა კალკულატორი დღემდე.

შექმნა დინამიური დიაპაზონი SUM და ოფსეტური ფუნქციები

© ტედ ფრანგული

თუ თქვენ იყენებთ გაანგარიშებას პერიოდს, რომელიც მუდმივად იცვლება - მაგალითად, თვის გაყიდვები - ოფსეტური ფუნქცია საშუალებას გაძლევთ შეიქმნას დინამიური დიაპაზონი, რომელიც ინარჩუნებს იცვლება ყოველი დღის გაყიდვების მაჩვენებელს.

თავისთავად, SUM ფუნქცია, როგორც წესი, იტევს მონაცემების ახალ უჯრედებს, რომლებიც შეჯამებულია სპექტრში.

ერთი გამონაკლისი ხდება მაშინ, როდესაც მონაცემები ჩადებულია საკანში, სადაც ფუნქცია ამჟამად მდებარეობს.

ამ სურათზე გამოსახულ მაგალითში, ყოველწლიურად ემატება ახალი გაყიდვების მოღვაწეები სიის ბოლოში, რაც აიძულებს საერთო ჯამში მუდმივად გადავიდეს ერთ საკანში ყოველ ჯერზე ახალი მონაცემების დამატება.

იმ შემთხვევაში, თუ SUM ფუნქცია გამოიყენებოდა საკუთარი მონაცემებით, საჭირო იყო, რომ საჭიროა ცვლილებების შეტანა მთელი რიგი ახალი მონაცემების დამატებამდე გამოყენებული უჯრედების სპექტრით.

გამოყენებით SUM და OFFSET ფუნქციების ერთად, თუმცა, სპექტრი, რომელიც შეადგინა ხდება დინამიური. სხვა სიტყვებით რომ ვთქვათ, ეს ცვლილებები მონაცემთა ახალ უჯრედებზე იტევს. მონაცემთა ახალი უჯრედების დამატება არ იწვევს პრობლემებს, რადგან სპექტრი განაგრძობს შეცვალოს ყოველი ახალი საკანში.

სინტაქსი და არგუმენტები

იხილე სურათი, რომელიც ამ სტატიას ახლავს ამ ტერმინასთან ერთად.

ამ ფორმულაში SUM ფუნქცია გამოიყენება მისი არგუმენტის სახით მიწოდებული მონაცემების ჯამური. ამ დიაპაზონის დაწყების წერტილი სტატიკურია და განისაზღვრება როგორც უჯრედის მითითება ფორმულით პირველი რიცხვისთვის.

OFFSET ფუნქცია წყობილია SUM ფუნქციის შიგნით და გამოიყენება დინამიური საბოლოო წერტილის შექმნისთვის, რომელიც შეესაბამება ფორმულით. ეს ხორციელდება დიაპაზონის საბოლოო წერტილამდე ფორმულის ადგილმდებარეობის ზემოთ ერთ საკანში.

ფორმულის სინტაქსი :

= SUM (დიაპაზონის დაწყება: OFFSET (მითითება, რიგები, Cols))

დიაპაზონის დაწყება - (საჭიროა) დაწყებული წერტილი სპექტრი საკნები, რომელიც შეადგინა SUM ფუნქცია. მაგალითად იმიჯი, ეს არის საკანში B2.

Reference - (საჭირო) საკანში მინიშნება, რომელიც გამოიყენება სპექტრი- ს საბოლოო წერტილის გამოსათვლელად, რომელიც მდებარეობს ბევრ რიგს და სვეტს. მაგალითად იმიჯი, Reference არგუმენტი არის საკანში მინიშნება ფორმულა, რადგან ჩვენ ყოველთვის გვინდა სპექტრი დასრულდება ერთი საკანში ზემოთ ფორმულა.

რიგები - (საჭიროა) რიგების რიგით ზემოთ ან ქვემოთ მითითება არგუმენტი, რომელიც გამოიყენება ოფსეტების გაანგარიშებაში. ეს მნიშვნელობა შეიძლება იყოს დადებითი, უარყოფითი, ან დადგენილი ნულიდან.

თუ ოფსეტური მდებარეობა ზემოთ არგუმენტის ზემოთ, ეს მნიშვნელობა უარყოფითია. თუ ეს ქვემოთ, რიგები არგუმენტი არის დადებითი. თუ ოფცია იმავე რიგში მდებარეობს, ეს არგუმენტი ნულოვანია. ამ მაგალითში ოფსეტური იწყება ერთი ზედიზედ ზემოთ მოყვანილ არგუმენტზე, ამიტომ ამ არგუმენტის მნიშვნელობა არის უარყოფითი ერთი (-1).

Cols - (საჭირო) ოფსეტური გამოთვლისას გამოყენებული Reference არგუმენტის მარცხნივ ან მარჯვნივ სვეტების რაოდენობა . ეს მნიშვნელობა შეიძლება იყოს დადებითი, უარყოფითი, ან დადგენილი ნულიდან

თუ ოფსეტური მდებარეობა არგუმენტირებული არგუმენტის მარცხნივ, ეს მნიშვნელობა უარყოფითია. თუ მარჯვნივ, Cols არგუმენტი არის დადებითი. ამ მაგალითში, მონაცემთა შეჯამება არის იმავე სვეტში, როგორც ფორმულა, ამიტომ ამ არგუმენტის მნიშვნელობა არის ნულოვანი.

გამოყენებით SUM OFFSET ფორმულა მთლიანი გაყიდვების მონაცემები

ეს მაგალითი იყენებს SUM OFFSET- ს ფორმულას, რათა დაუბრუნდეს სულ გაყიდვების ფიგურებს, რომლებიც ჩამოთვლილია სვეტის B ნაწარმოებზე.

თავდაპირველად, ფორმულა შევიდა საკანში B6 და შეადგინა გაყიდვების მონაცემები ოთხი დღის განმავლობაში.

შემდეგი ნაბიჯი გადადის SUM OFFSET ფორმულაზე ზედიზედ, რათა მეხუთე დღის გაყიდვების საერთო ოთახის გაკეთება.

ეს ხორციელდება ახალი რიგის 6-ის ჩასმის გზით , რომელიც ფორმულა 7-ზე გადადის.

ნაბიჯიდან გამომდინარე, Excel ავტომატურად განაახლებს Reference არგუმენტს B7 საკანში და დასძენს საკანში B6 ფორმულით შეაჯამებს Range.

შეყვანის SUM OFFSET ფორმულა

  1. დაწკაპეთ საკანში B6, რომელიც არის ადგილი, სადაც ფორმულის შედეგები თავდაპირველად გამოჩნდება.
  2. დაწკაპეთ ფორმულების ჩანართის ლენტი მენიუ.
  3. არჩევა მათემატიკა და Trig საწყისი ლენტი ფუნქციის ჩამოსაშლელი სია.
  4. დააჭირეთ SUM სიაში, რათა შეიმუშაოს ფუნქციის დიალოგური ფანჯარა .
  5. დიალოგურ ფანჯარაში დააჭირეთ ნომერზე 1 .
  6. დააჭირეთ საკანში B2 ამ საკანში მინიშნება შევიდა დიალოგური ფანჯარა. ეს მდებარეობა ფორმულის სტატიკური საბოლოო წერტილია;
  7. დიალოგურ ფანჯარაში დააჭირეთ ღილაკს Number2 ხაზი.
  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. დაწკაპეთ A6 საკანში.
  5. შეიყვანეთ ნომერი 5, რათა მიუთითოთ, რომ გაყიდვების საერთო მეხუთე დღეა შეყვანილი.
  6. დაწკაპეთ საკანში B6.
  7. ჩაწერეთ ნომერი $ 1458.25 და დააჭირეთ Enter ღილაკს კლავიატურაზე.

უჯრედის B7 განახლებები სულ $ 7137.40.

როდესაც თქვენ დააჭირეთ საკანში B7, განახლებული ფორმულა = SUM (B2: OFFSET (B7, -1,0)) გამოჩნდება ფორმულა ბარი.

შენიშვნა : OFFSET ფუნქციას ორი არგუმენტი აქვს: სიმაღლე და სიგანე, რომლებიც ამ მაგალითში გამოტოვებული იყო.

ეს არგუმენტები შეიძლება გამოყენებულ იქნას ოფსეტური ფუნქციის გასაგებად გამომავალი ფორმის თვალსაზრისით იმდენად, რამდენადაც ამდენი რიგები მაღალია და მრავალი სვეტი ფართოა.

ამ არგუმენტების გამოტოვებით, ფუნქცია, ჩვეულებრივ, იყენებს რეფერაციის არგუმენტის სიმაღლეს და სიგანეს, რომელიც, ამ მაგალითში არის ერთი რიგის მაღალი და ერთი სვეტი ფართო.