อยากให้ Excel Sum แบบเปลี่ยนช่วงข้อมูลได้ ต้องเขียนสูตรยังไง?

เช่น อยากได้ผลลัพธ์เหมือน SUM(B3:B6) แต่ระบุ B3, B6 ไม่ได้ เพราะอาจเปลี่ยนเป็น B2, B8

ถ้าห้ามระบุตรง ๆ ก็ระบุอ้อม ๆ โดยเขียนสูตรเป็น

=SUM(INDEX(B2:B9,E2):INDEX(B2:B9,E3))

(เปลี่ยน E2, E3 เป็นลำดับข้อมูลที่ต้องการ)

ใช่แล้ว 🙂,
INDEX(B2:B9,E2) เทียบเท่า B3
INDEX(B2:B9,E3) เทียบเท่า B6

ทำไม INDEX(B2:B9,E2) เทียบเท่า B3?

เพราะผลลัพธ์ที่แท้จริงของฟังก์ชัน INDEX คือ Cell Reference

Excel จะมองว่าผลลัพธ์ของสูตรนี้คือ B3
ถ้าใช้เป็น B3 ได้ ก็จะใช้เป็น B3 เลย
(แต่ถ้าใช้ไม่ได้ ก็จะแปลงเป็น 200)

การ Sum แบบนี้ ศัพท์เทคนิคเรียกว่า Dynamic Sum Range

บางคนอาจใช้ฟังก์ชัน OFFSET หรือ INDIRECT แต่โดยส่วนตัวแล้วไม่ชอบสองฟังก์ชันนี้ เพราะเป็นฟังก์ชัน Volatile (Volatile Function)

การใช้ฟังก์ชันที่เป็น Volatile ทำให้ไฟล์คำนวณใหม่ทุกครั้งที่เกิดการเปลี่ยนแปลง (แม้การเปลี่ยนแปลงจะไม่เกี่ยวกับเซลล์นั้น ๆ ก็ตาม) ส่งผลให้ไฟล์ประมวลผลช้าลง

ถ้า OFFSET, INDIRECT ทำให้ช้า งั้นเราเปลี่ยนเป็น INDEX ก็ได้
เนอะ 😊