สูตร Excel SUM และ OFFSET

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• ช่วงเริ่มต้น: ต้องใช้จุดเริ่มต้นสำหรับช่วงของเซลล์ที่จะนับโดยฟังก์ชัน SUM ในภาพตัวอย่างนี่คือเซลล์ B2
• การอ้างอิง: การอ้างอิงเซลล์ที่ต้องการใช้สำหรับการคำนวณจุดสิ้นสุดของช่วงอยู่ห่างจากแถวและคอลัมน์มากเกินไป ในภาพตัวอย่างอาร์กิวเมนต์อ้างอิงคือการอ้างอิงเซลล์สำหรับสูตรเองเนื่องจากคุณต้องการให้ช่วงจบเซลล์หนึ่งด้านบนสูตร
• แถว: จำนวนแถวด้านบนหรือด้านล่างอาร์กิวเมนต์อ้างอิงที่ใช้ในการคำนวณค่าชดเชยจะต้อง ค่านี้สามารถเป็นบวกลบหรือตั้งค่าเป็นศูนย์ได้ ถ้าตำแหน่งออฟเซ็ตอยู่ด้านบน การอ้างอิง อาร์กิวเมนต์ค่าเป็นลบ ถ้าด้านล่างอาร์กิวเมนต์แถวเป็นค่าบวก ถ้าออฟเซทอยู่ในแถวเดียวกันอาร์กิวเมนต์เป็นศูนย์ ในตัวอย่างนี้การชดเชยจะเริ่มต้นหนึ่งแถวเหนืออาร์กิวเมนต์อ้างอิงดังนั้นค่าของอาร์กิวเมนต์นี้เป็นค่าลบหนึ่ง (-1)
• คอลัมน์: ต้องใช้จำนวนคอลัมน์ทางด้านซ้ายหรือด้านขวาของอาร์กิวเมนต์อ้างอิงที่ใช้ในการคำนวณค่าชดเชย ค่านี้สามารถเป็นบวกลบหรือตั้งค่าเป็นศูนย์ได้ ถ้าตำแหน่งออฟเซตอยู่ทางด้านซ้ายของอาร์กิวเมนต์อ้างอิงค่านี้เป็นค่าลบ ถ้าด้านขวาอาร์กิวเมนต์ 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. คลิก ตกลง เพื่อทำหน้าที่และปิดกล่องโต้ตอบ

จำนวนรวมจะปรากฏในเซลล์ B7

การเพิ่มข้อมูลการขายของวันถัดไป

หากต้องการเพิ่มข้อมูลการขายในวันถัดไปให้ทำดังนี้

1. คลิกขวาที่ส่วนหัวแถวสำหรับแถว 6 เพื่อเปิดเมนูบริบท
2. ในเมนูให้คลิกที่แทรก เพื่อแทรกแถวใหม่ลงในแผ่นงาน
3. เป็นผลให้สูตร SUM OFFSET เลื่อนลงไปที่เซลล์ B7 และแถว 6 ว่างเปล่า
4. คลิกที่เซลล์ A6.
5. ป้อนหมายเลข5 เพื่อระบุว่ามีการป้อนยอดขายรวมสำหรับวันที่ห้า
6. คลิกที่เซลล์ B6
7. พิมพ์หมายเลข$1458.25 และกดปุ่มเข้าสู่ บนแป้นพิมพ์

เซลล์ B7 อัปเดตเป็นจำนวนรวมของ$7137.40.

เมื่อคุณคลิกที่เซลล์ B7 สูตรที่ปรับปรุงแล้ว= SUM (B2: OFFSET (B7, -1,0)) จะปรากฏในแถบสูตร

ฟังก์ชัน OFFSET มีสองอาร์กิวเมนต์: ความสูงและความกว้าง , ซึ่งไม่ได้ใช้ในตัวอย่างนี้

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

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