บางครั้งต้องการอ้างอิงเซลล์ที่ «เลื่อนออกไป» จากจุดเริ่มต้นตามจำนวนแถว/คอลัมน์ที่กำหนดเอง โดยไม่ต้องพิมพ์ที่อยู่เซลล์ตรงๆ สูตร OFFSET ช่วยสร้างการอ้างอิงแบบยืดหยุ่นที่เปลี่ยนตำแหน่งได้ตามค่าที่คำนวณ
OFFSET คืออะไร
OFFSET คืนค่าการอ้างอิงเซลล์ (หรือช่วงเซลล์) ที่เลื่อนออกจากจุดเริ่มต้นตามจำนวนแถวและคอลัมน์ที่ระบุ รูปแบบสูตรคือ
=OFFSET(จุดเริ่มต้น, เลื่อนกี่แถว, เลื่อนกี่คอลัมน์, [ความสูง], [ความกว้าง])
=OFFSET(A1,2,1)| A | B | |
|---|---|---|
| 1 | มกราคม | 1200 |
| 2 | กุมภาพันธ์ | 1450 |
| 3 | มีนาคม | 1680 |
จากตัวอย่าง เริ่มที่ A1 เลื่อนลง 2 แถวและเลื่อนขวา 1 คอลัมน์ จึงได้ค่าจากเซลล์ B3 คือ 1680
ตัวอย่างการใช้งานจริง: สร้างช่วงข้อมูลที่ขยายอัตโนมัติ
OFFSET มักใช้ร่วมกับ COUNTA เพื่อสร้างช่วงข้อมูลที่ขยายตามจำนวนแถวที่มีข้อมูลจริง เหมาะกับ Dropdown List ที่ต้องอัปเดตรายการอัตโนมัติเมื่อเพิ่มแถวใหม่
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
ข้อควรระวัง
- OFFSET เป็นสูตรแบบ Volatile คือคำนวณใหม่ทุกครั้งที่ Excel คำนวณสูตร แม้เซลล์ที่เกี่ยวข้องจะไม่เปลี่ยนก็ตาม ทำให้ไฟล์ที่มี OFFSET จำนวนมากอาจทำงานช้าลง
- ถ้าใช้ในไฟล์ขนาดใหญ่ ควรพิจารณาใช้ Excel Table หรือ INDEX แทน เพราะให้ผลลัพธ์คล้ายกันแต่ไม่กินทรัพยากรเท่า OFFSET
สรุป
OFFSET เหมาะกับการสร้างการอ้างอิงที่ต้องขยับตำแหน่งตามเงื่อนไข แต่ถ้าไม่จำเป็นต้องใช้ความยืดหยุ่นระดับนี้ การใช้ Excel Table หรือ INDEX/MATCH มักเป็นทางเลือกที่เบากว่าและจัดการง่ายกว่า
