สูตร Excel SUM และ OFFSET

ใช้ SUM และ OFFSET เพื่อค้นหาผลรวมสำหรับช่วงข้อมูลแบบไดนามิก

ถ้า แผ่นงาน Excel ของคุณมีการคำนวณขึ้นอยู่กับ ช่วง ของเซลล์ที่มีการเปลี่ยนแปลงการใช้ฟังก์ชัน SUM และ OFFSET ร่วมกันในสูตร SUM OFFSET ช่วยลดความซับซ้อนในการคำนวณ

สร้างช่วงไดนามิคด้วยฟังก์ชัน SUM และ OFFSET

ถ้าคุณใช้การคำนวณเป็นระยะเวลาที่เปลี่ยนแปลงไปอย่างต่อเนื่องเช่นยอดขายรวมสำหรับเดือนฟังก์ชัน OFFSET ช่วยให้คุณสามารถตั้งค่าช่วงไดนามิคที่สามารถเปลี่ยนแปลงได้เนื่องจากตัวเลขยอดขายของแต่ละวันมีการเพิ่มขึ้น

โดยตัวเอง ฟังก์ชัน SUM สามารถรองรับเซลล์ใหม่ของข้อมูลที่ถูกแทรกลงในช่วงที่ถูกสรุป

ข้อยกเว้นหนึ่งเกิดขึ้นเมื่อข้อมูลถูกแทรกลงในเซลล์ที่ฟังก์ชันอยู่ในปัจจุบัน

ในตัวอย่างรูปภาพที่มาพร้อมกับบทความนี้ตัวเลขการขายใหม่ในแต่ละวันจะถูกเพิ่มลงในส่วนล่างสุดของรายการซึ่งจะบังคับให้จำนวนรวมลดลงต่อหนึ่งเซลล์ทุกครั้งที่มีการเพิ่มข้อมูลใหม่

ถ้าฟังก์ชัน SUM ถูกใช้ด้วยตัวเองเพื่อรวมข้อมูลจำเป็นที่จะต้องปรับเปลี่ยนช่วงของเซลล์ที่ใช้เป็น อาร์กิวเมนต์ ของฟังก์ชันทุกครั้งที่มีการเพิ่มข้อมูลใหม่

การใช้ฟังก์ชัน SUM และ OFFSET ร่วมกันอย่างไรก็ตามช่วงที่รวมทั้งหมดจะกลายเป็นแบบไดนามิก กล่าวคือจะเปลี่ยนแปลงเพื่อรองรับเซลล์ข้อมูลใหม่ ๆ การเพิ่มเซลล์ใหม่ของข้อมูลไม่ก่อให้เกิดปัญหาเนื่องจากช่วงดังกล่าวยังคงปรับตามการเพิ่มเซลล์ใหม่แต่ละเซลล์

ไวยากรณ์และอาร์กิวเมนต์

ดูภาพที่มาพร้อมกับบทความนี้เพื่อทำตามคำแนะนำนี้

ในสูตรนี้ฟังก์ชัน SUM จะใช้เพื่อรวมช่วงของข้อมูลที่จัดทำเป็นอาร์กิวเมนต์ จุดเริ่มต้นสำหรับช่วงนี้เป็นแบบสแตติกและถูกระบุว่าเป็นการ อ้างอิงเซลล์ ไปยังหมายเลขแรกที่จะรวมโดยสูตร

ฟังก์ชัน OFFSET ถูกซ้อนกันภายในฟังก์ชัน SUM และถูกใช้เพื่อสร้างปลายทางแบบไดนามิกไปยังช่วงของข้อมูลที่รวมอยู่ในสูตร ทำได้โดยตั้งจุดสิ้นสุดของช่วงหนึ่งเซลล์ไว้เหนือตำแหน่งของสูตร

ไวยากรณ์ ของสูตร:

= SUM (ช่วงเริ่ม: OFFSET (อ้างอิง, แถว, ขีด))

Range Start - (จำเป็น) จุดเริ่มต้นสำหรับช่วงของเซลล์ที่จะถูกนับโดยฟังก์ชัน SUM ในภาพตัวอย่างนี่คือเซลล์ B2

การอ้างอิง - (จำเป็น) การอ้างอิงเซลล์ที่ใช้สำหรับการคำนวณจุดสิ้นสุดของช่วงที่อยู่ห่างจากแถวและคอลัมน์ออกไป ในภาพตัวอย่างอาร์กิวเมนต์ อ้างอิง คือการอ้างอิงเซลล์สำหรับสูตรเองเนื่องจากเราต้องการให้ช่วงจบเซลล์หนึ่งสูตรด้านบนสูตร

แถว - (จำเป็น) จำนวนแถวด้านบนหรือด้านล่างอาร์กิวเมนต์ อ้างอิงที่ ใช้ในการคำนวณค่าชดเชย ค่านี้สามารถเป็นบวกลบหรือตั้งค่าเป็นศูนย์ได้

หากตำแหน่งออฟเซ็ตอยู่เหนืออาร์กิวเมนต์ อ้างอิง ค่านี้เป็นค่าลบ ถ้าด้านล่างอาร์กิวเมนต์ แถว เป็นค่าบวก ถ้าออฟเซทอยู่ในแถวเดียวกันอาร์กิวเมนต์นี้เป็นศูนย์ ในตัวอย่างนี้การชดเชยจะเริ่มต้นหนึ่งแถวเหนืออาร์กิวเมนต์ อ้างอิง ดังนั้นค่าของอาร์กิวเมนต์นี้เป็นค่าลบหนึ่ง (-1)

Cols - (จำเป็น) จำนวน คอลัมน์ ทางซ้ายหรือขวาของอาร์กิวเมนต์ อ้างอิงที่ ใช้ในการคำนวณออฟเซต ค่านี้สามารถเป็นบวกลบหรือตั้งค่าเป็นศูนย์ได้

ถ้าตำแหน่งออฟเซตอยู่ทางด้านซ้ายของอาร์กิวเมนต์ อ้างอิง ค่านี้เป็นค่าลบ ถ้าด้านขวาอาร์กิวเมนต์ Cols เป็นค่าบวก ในตัวอย่างนี้ข้อมูลที่ถูกรวมอยู่ในคอลัมน์เดียวกับสูตรดังนั้นค่าอาร์กิวเมนต์นี้เป็นศูนย์

ใช้สูตรการคำนวณยอดรวมในยอดขายรวม

ตัวอย่างนี้ใช้สูตร SUM OFFSET เพื่อส่งคืนผลรวมยอดขายรายวันที่ระบุไว้ในคอลัมน์ B ของแผ่นงาน

ขั้นแรกสูตรถูกป้อนลงในเซลล์ B6 และรวมยอดขายเป็นเวลาสี่วัน

ขั้นตอนต่อไปคือการย้ายสูตร SUM OFFSET ออกเป็นแถวเพื่อให้มียอดขายรวมในวันที่ห้า

ทำได้โดยการ แทรกแถว 6 ใหม่ซึ่งย้ายสูตรลงไปที่แถว 7

เป็นผลจากการย้าย Excel จะปรับปรุงอาร์กิวเมนต์อ้างอิงไปยังเซลล์ B7 และเพิ่มเซลล์ B6 ในช่วงที่สรุปโดยสูตร

การป้อนสูตรการลดผลรวม (SUM OFFSET Formula)

1. คลิกที่เซลล์ B6 ซึ่งเป็นตำแหน่งที่จะแสดงผลลัพธ์ของสูตรก่อน
2. คลิกแท็บ สูตร ในเมนู ริบบัว
3. เลือก คณิตศาสตร์และ Trig จากริบบิ้นเพื่อเปิดรายการแบบเลื่อนลงฟังก์ชั่น
4. คลิกที่ SUM ในรายการเพื่อเปิด กล่องโต้ตอบ ของฟังก์ชั่น
5. ในกล่องโต้ตอบคลิกที่บรรทัด Number1
6. คลิกที่เซลล์ B2 เพื่อป้อนข้อมูลอ้างอิงเซลล์นี้ลงในไดอะลอกบ็อกซ์ ตำแหน่งนี้เป็นจุดสิ้นสุดแบบคงที่สำหรับสูตร
7. ในกล่องโต้ตอบคลิกที่บรรทัด Number2
8. ป้อนฟังก์ชัน OFFSET ต่อไปนี้: OFFSET (B6, -1,0) เพื่อสร้างปลายทางแบบไดนามิกสำหรับสูตร
9. คลิก ตกลง เพื่อทำหน้าที่และปิดกล่องโต้ตอบ

รวม $ 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 มีสองอาร์กิวเมนต์ที่เป็นตัวเลือก: ความสูง และ ความกว้าง ซึ่งถูกละไว้ในตัวอย่างนี้

อาร์กิวเมนต์เหล่านี้สามารถใช้เพื่อบอกฟังก์ชัน OFFSET รูปร่างของเอาท์พุทในแง่ของมันเป็นจำนวนมากดังนั้นแถวสูงและจำนวนมากดังนั้นคอลัมน์กว้าง

โดยการละเว้นอาร์กิวเมนต์เหล่านี้ฟังก์ชันโดยค่าเริ่มต้นใช้ความสูงและความกว้างของอาร์กิวเมนต์อ้างอิงแทนซึ่งในตัวอย่างนี้เป็นแถวหนึ่งสูงและกว้างหนึ่งคอลัมน์