ฟังก์ชัน OFFSET ใน Excel

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

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

ประโยชน์ของฟังก์ชัน OFFSET ใน Excel

• อ้างอิงเซลล์หรือช่วงข้อมูลโดยเลื่อนตำแหน่งจากจุดเริ่มต้น
• สร้างสูตร dynamic ที่เปลี่ยนช่วงข้อมูลตามตัวแปร
• ใช้ทำตารางรายงาน, สรุปข้อมูล, หรือสร้าง Named Range dynamic
• ใช้ร่วมกับ SUM, AVERAGE, COUNT เพื่อคำนวณช่วงที่เปลี่ยนแปลง
• ลดการเขียนสูตรซ้ำเมื่อตารางข้อมูลมีการเพิ่มแถวหรือคอลัมน์

รูปแบบการใช้งาน (Syntax)

=OFFSET(reference, rows, cols, [height], [width])

• reference → จุดเริ่มต้น
• rows → จำนวนแถวที่เลื่อน
• cols → จำนวนคอลัมน์ที่เลื่อน
• [height] → ความสูงของช่วง (จำนวนแถว, ไม่บังคับ)
• [width] → ความกว้างของช่วง (จำนวนคอลัมน์, ไม่บังคับ)

ตัวอย่างการประยุกต์ใช้งานฟังก์ชัน OFFSET ใน Excel

ตัวอย่างที่ 1: อ้างอิงเซลล์ที่เลื่อนจากจุดเริ่มต้น

โจทย์: ต้องการอ้างอิงเซลล์ที่อยู่ใต้ A1 ลงมา 2 แถว และเลื่อนไปทางขวา 1 คอลัมน์

ข้อมูลตัวอย่าง

A	B	C
10	20	30
40	50	60
70	80	90

สูตร

=OFFSET(A1,2,1)

• reference → กำหนดค่า A1 เป็นจุดเริ่มต้น
• rows → กำหนดค่า 2 หมายถึง เลื่อนลง 2 แถว
• cols → กำหนดค่า 1 หมายถึง เลื่อนไปทางขวา 1 คอลัมน์
• [height], [width] → ไม่ได้กำหนด (ค่าเริ่มต้น = 1)
• OFFSET → อ้างอิงเซลล์ B3 → คืนค่า 80

ผลลัพธ์

80

ตัวอย่างที่ 2: ใช้ OFFSET กับ SUM คำนวณผลรวม dynamic

โจทย์: ต้องการรวมค่า 3 เซลล์ถัดจาก A1 ลงมา

ข้อมูลตัวอย่าง

A
10
20
30
40

สูตร

=SUM(OFFSET(A1,0,0,3,1))

• reference → A1 → จุดเริ่มต้น
• rows → 0 → ไม่เลื่อนแถว
• cols → 0 → ไม่เลื่อนคอลัมน์
• height → 3 → ช่วงสูง 3 แถว
• width → 1 → ช่วงกว้าง 1 คอลัมน์
• OFFSET → อ้างอิง A1:A3 → SUM → 10 + 20 + 30 = 60

ผลลัพธ์

60

ตัวอย่างที่ 3: อ้างอิงช่วงแนวนอน

โจทย์: ต้องการรวมค่า 2 คอลัมน์ถัดจาก A1 → A1 และ B1

ข้อมูลตัวอย่าง

A	B	C
5	15	25

สูตร

=SUM(OFFSET(A1,0,0,1,2))

• reference → A1 → จุดเริ่มต้น
• rows → 0 → ไม่เลื่อนแถว
• cols → 0 → ไม่เลื่อนคอลัมน์
• height → 1 → ความสูง 1 แถว
• width → 2 → ความกว้าง 2 คอลัมน์
• OFFSET → อ้างอิง A1:B1 → SUM → 5 + 15 = 20

ผลลัพธ์

20

ตัวอย่างที่ 4: ใช้ฟังก์ชัน OFFSET ใน Excel ร่วมกับ COUNTA ทำ Named Range dynamic

โจทย์: ต้องการสร้าง Named Range “ยอดขาย” ที่ขยายตามจำนวนรายการใน A1:A10

ข้อมูลตัวอย่าง

A
1000
2000
3000

สูตรใน Name Manager

=OFFSET(A1,0,0,COUNTA(A1:A10),1)

• reference → A1 → จุดเริ่มต้น
• rows → 0 → ไม่เลื่อนแถว
• cols → 0 → ไม่เลื่อนคอลัมน์
• height → COUNTA(A1:A10) → นับจำนวนที่ไม่ว่าง
• width → 1 → ช่วงกว้าง 1 คอลัมน์
• OFFSET → อ้างอิง A1:A3 (มีค่า) → สร้าง Named Range dynamic

ผลลัพธ์

A1:A3 → ครอบคลุมเฉพาะแถวที่มีค่า

คำแนะนำเพิ่มเติมในการใช้งาน

• OFFSET จะไม่แสดงผลลัพธ์เป็นค่า แต่เป็น “การอ้างอิงช่วง” ต้องใช้คู่กับฟังก์ชันอื่น (เช่น SUM, AVERAGE)
• ใช้ร่วมกับฟังก์ชันอื่น เช่น COUNTA, ROW, COLUMN เพื่อทำสูตร dynamic
• ถ้ากำหนดช่วงเกินขอบเขต จะคืนค่า #REF!
• OFFSET อาจทำให้ไฟล์คำนวณช้าลงในไฟล์ขนาดใหญ่

สรุป

ฟังก์ชัน OFFSET ใน Excel เป็นฟังก์ชันอ้างอิงที่มีประสิทธิภาพสูง สามารถเลื่อนตำแหน่งการอ้างอิงเซลล์หรือช่วงข้อมูลจากจุดเริ่มต้น และกำหนดขนาดของช่วงได้ตามต้องการ OFFSET มักถูกใช้ร่วมกับฟังก์ชันเชิงคำนวณอื่นเพื่อสร้างสูตร dynamic เช่น สรุปยอดรายเดือน สร้าง Named Range dynamic หรือทำแดชบอร์ดที่ปรับอัปเดตตามข้อมูล การเข้าใจการทำงานของฟังก์ชัน OFFSET จึงเป็นทักษะสำคัญของผู้ใช้ Excel ที่ต้องทำงานกับข้อมูลที่ซับซ้อนและเปลี่ยนแปลงบ่อย

ข้อมูลอ้างอิง: OFFSET function