ถ้าเวิร์กชีต 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(การอ้างอิง, แถว, Cols))
อาร์กิวเมนต์คือ:
- ช่วงเริ่มต้น: จุดเริ่มต้นสำหรับช่วงของเซลล์ที่จะถูกรวมโดยฟังก์ชัน SUM ในตัวอย่างนี้ จุดเริ่มต้นคือเซลล์ B2
- Reference: การอ้างอิงเซลล์ที่จำเป็นซึ่งใช้ในการคำนวณจุดสิ้นสุดของช่วง ในตัวอย่าง อาร์กิวเมนต์ Reference คือการอ้างอิงเซลล์สำหรับสูตรเนื่องจากช่วงสิ้นสุดหนึ่งเซลล์เหนือสูตร
- Rows: จำนวนแถวด้านบนหรือด้านล่างอาร์กิวเมนต์อ้างอิงที่ใช้ในการคำนวณออฟเซ็ตเป็นสิ่งจำเป็น ค่านี้สามารถเป็นค่าบวก ค่าลบ หรือตั้งค่าเป็นศูนย์ได้ ถ้าตำแหน่งออฟเซ็ตอยู่เหนืออาร์กิวเมนต์ Reference ค่าจะเป็นค่าลบ ถ้าออฟเซ็ตอยู่ด้านล่าง อาร์กิวเมนต์ Rows จะเป็นค่าบวก ถ้าออฟเซ็ตอยู่ในแถวเดียวกัน อาร์กิวเมนต์จะเป็นศูนย์ ในตัวอย่างนี้ ออฟเซ็ตเริ่มต้นหนึ่งแถวเหนืออาร์กิวเมนต์ Reference ดังนั้นค่าสำหรับอาร์กิวเมนต์จึงเป็นค่าลบ (-1)
- Cols: จำนวนคอลัมน์ทางซ้ายหรือขวาของอาร์กิวเมนต์อ้างอิงที่ใช้ในการคำนวณออฟเซ็ต ค่านี้สามารถเป็นค่าบวก ค่าลบ หรือตั้งค่าเป็นศูนย์ได้ ถ้าตำแหน่งออฟเซ็ตอยู่ทางด้านซ้ายของอาร์กิวเมนต์ Reference ค่านี้เป็นค่าลบ ถ้าออฟเซ็ตอยู่ทางขวา อาร์กิวเมนต์ Cols จะเป็นค่าบวก ในตัวอย่างนี้ ข้อมูลที่รวมจะอยู่ในคอลัมน์เดียวกับสูตร ดังนั้นค่าสำหรับอาร์กิวเมนต์นี้คือศูนย์
ใช้สูตร SUM OFFSET กับข้อมูลยอดขายทั้งหมด
ตัวอย่างนี้ใช้สูตร SUM OFFSET เพื่อส่งคืนยอดรวมของยอดขายรายวันที่แสดงอยู่ในคอลัมน์ B ของเวิร์กชีต เริ่มแรก สูตรถูกป้อนลงในเซลล์ B6 และรวมข้อมูลยอดขายเป็นเวลาสี่วัน
ขั้นตอนต่อไปคือการย้ายสูตร SUM OFFSET ลงไปหนึ่งแถวเพื่อให้มีที่ว่างสำหรับยอดขายรวมของวันที่ห้า ทำได้โดยการแทรกแถว 6 ใหม่ ซึ่งย้ายสูตรไปที่แถว 7
จากการย้าย Excel จะอัปเดตอาร์กิวเมนต์อ้างอิงไปยังเซลล์ B7 โดยอัตโนมัติ และเพิ่มเซลล์ B6 ให้กับช่วงที่สรุปโดยสูตร
- เลือกเซลล์ B6 ซึ่งเป็นตำแหน่งที่จะแสดงผลลัพธ์ของสูตรในขั้นต้น
-
เลือกแท็บ Formulas ของริบบอน
Image -
เลือก คณิตศาสตร์ & Trig.
Image -
เลือก SUM.
Image - ในกล่องโต้ตอบ อาร์กิวเมนต์ของฟังก์ชัน วางเคอร์เซอร์ใน Number1 กล่องข้อความ
-
ในเวิร์กชีต เลือกเซลล์ B2 เพื่อป้อนการอ้างอิงเซลล์นี้ในกล่องโต้ตอบ ตำแหน่งนี้คือจุดสิ้นสุดแบบคงที่สำหรับสูตร
Image - ในกล่องโต้ตอบ อาร์กิวเมนต์ของฟังก์ชัน วางเคอร์เซอร์ใน Number2 กล่องข้อความ
-
Enter OFFSET(B6, -1, 0). ฟังก์ชัน OFFSET นี้สร้างจุดสิ้นสุดแบบไดนามิกสำหรับสูตร
Image -
เลือก OK เพื่อสิ้นสุดการทำงานและปิดกล่องโต้ตอบ ผลรวมปรากฏในเซลล์ B6
Image
เพิ่มข้อมูลการขายของวันถัดไป
หากต้องการเพิ่มข้อมูลยอดขายของวันถัดไป:
- คลิกขวาที่ส่วนหัวของแถวสำหรับแถวที่ 6
-
เลือก Insert เพื่อแทรกแถวใหม่ลงในเวิร์กชีต สูตร SUM OFFSET เลื่อนลงหนึ่งแถวไปยังเซลล์ B7 และแถวที่ 6 ว่างเปล่า
Image - เลือกเซลล์ A6 และป้อนหมายเลข 5 เพื่อระบุว่ากำลังป้อนยอดขายรวมสำหรับวันที่ห้า
-
เลือกเซลล์ B6 ป้อน $1458.25 จากนั้นกด Enter.
Image - เซลล์ B7 อัพเดทยอดรวมใหม่ $7137.40
เมื่อคุณเลือกเซลล์ B7 สูตรที่อัปเดตจะปรากฏในแถบสูตร
=SUM(B2:OFFSET(B7, -1, 0))
ฟังก์ชัน OFFSET มีสองอาร์กิวเมนต์ที่เป็นทางเลือก: Height และ Width ซึ่งไม่ได้ใช้ในตัวอย่างนี้ อาร์กิวเมนต์เหล่านี้บอกฟังก์ชัน OFFSET ถึงรูปร่างของเอาต์พุตในแง่ของจำนวนแถวและคอลัมน์
การละเว้นอาร์กิวเมนต์เหล่านี้ ฟังก์ชันจะใช้ความสูงและความกว้างของอาร์กิวเมนต์ Reference แทน ซึ่งในตัวอย่างนี้สูงหนึ่งแถวและกว้างหนึ่งคอลัมน์
