Thep Excel
ฟังก์ชัน OFFSET ใน Excel
อ้างอิงช่วงข้อมูลที่เลื่อนจากตำแหน่งเริ่มต้น สร้าง dynamic range ได้
Function Metrics
Popularity
7/10
Difficulty
6/10
Usefulness
8/10
Syntax & Arguments
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| reference | required | Yes | ตำแหน่งเริ่มต้น (cell หรือ range) ที่จะเลื่อนออกมา | |
| rows | required | Yes | จำนวนแถวที่จะเลื่อน (บวก = ลง, ลบ = ขึ้น) | |
| cols | required | Yes | จำนวนคอลัมน์ที่จะเลื่อน (บวก = ขวา, ลบ = ซ้าย) | |
| height | optional | Yes | ความสูงของช่วง (จำนวนแถว) – ถ้าไม่ระบุ ใช้ความสูงเดิม | |
| width | optional | Yes | ความกว้างของช่วง (จำนวนคอลัมน์) – ถ้าไม่ระบุ ใช้ความกว้างเดิม |
How it works
สร้าง Dynamic Chart Source
ใช้ OFFSET ใน Named Range เพื่อให้กราฟสามารถปรับช่วงข้อมูลได้เองโดยอัตโนมัติเมื่อมีการเพิ่มข้อมูลใหม่ ทำให้ไม่ต้องมาแก้ไข Chart Source Data ซ้ำๆ
ดึงข้อมูลแถว/คอลัมน์สุดท้าย
ใช้ OFFSET ร่วมกับ COUNTA หรือ MATCH เพื่อดึงข้อมูลจากแถวหรือคอลัมน์สุดท้ายที่มีข้อมูลอยู่เสมอ เหมาะสำหรับรายงานที่ต้องการแสดงค่าล่าสุด
Examples
ตัวอย่างพื้นฐาน: ดึงค่าจากเซลล์ที่เลื่อน
OFFSET(A1,1,1)
=OFFSET(A1,1,1)
25 (ค่าจากเซลล์ B2)
ตัวอย่างการใช้งาน: ผลรวมข้อมูลแบบ Dynamic
SUM(OFFSET(A1,1,0,5,1))
=SUM(OFFSET(A1,1,0,5,1))
ผลรวมของ A2 ถึง A6
ตัวอย่างขั้นสูง: ค่าเฉลี่ยเคลื่อนที่ (Moving Average)
AVERAGE(OFFSET(D31,-6,0,7,1))
=AVERAGE(OFFSET(D31,-6,0,7,1))
ค่าเฉลี่ยของ 7 วันล่าสุด (D25:D31)
ตัวอย่างการใช้งาน: Dynamic Named Range
SUM(OFFSET(A1,1,0,COUNTA(A:A)-1,1))
=SUM(OFFSET(A1,1,0,COUNTA(A:A)-1,1))
ผลรวมข้อมูลทั้งหมด A2:A(สุดท้าย) โดยอัตโนมัติ
ตัวอย่างการใช้งาน: ดึงค่าจากตำแหน่งที่เลื่อนขึ้น
OFFSET(E5,-2,-1)
=OFFSET(E5,-2,-1)
ค่าจากเซลล์ D3
FAQs
OFFSET ต่างจาก INDIRECT อย่างไร?
OFFSET เลื่อนจากตำแหน่งเฉพาะ ส่วน INDIRECT ใช้ข้อความเพื่อสร้างอ้างอิง เช่น INDIRECT(‘A’&1) ใช้ตัวเลขในเซลล์ A1 เพื่อสร้างอ้างอิง OFFSET นั้นเหมาะเมื่อต้องการ ‘เลื่อน’ ไปจากตำแหน่งที่รู้จัก INDIRECT เหมาะเมื่อต้องการสร้างอ้างอิงจากข้อความ
OFFSET จะคืนค่าอะไร?
OFFSET คืนค่าช่วง (range) หรือเซลล์เดี่ยว ไม่ใช่ค่าตัวเลข ถ้าต้องการค่า ต้องใช้กับฟังก์ชันอื่น เช่น SUM, AVERAGE, MAX เป็นต้น
OFFSET เป็น volatile function หรือไม่?
ใช่ OFFSET เป็น volatile function ซึ่งหมายความว่ามันจะถูก recalculate ทุกครั้งที่มีการเปลี่ยนแปลงใน worksheet แม้เป็นการเปลี่ยนแปลงที่ไม่เกี่ยวข้อง ถ้าใช้มากเกินไปในไฟล์ขนาดใหญ่ อาจทำให้ Excel ช้า
OFFSET รองรับ dynamic array หรือไม่?
OFFSET ไม่เป็น dynamic array formula แต่สามารถใช้กับฟังก์ชันเช่น SUMPRODUCT, SUM array formulas โดยการ press Ctrl+Shift+Enter (Excel 2019 ขึ้นไป)
ถ้า OFFSET เลื่อนออกนอก worksheet ขอบเขตจะเกิดอะไร?
จะคืนค่า #REF! error ถ้าการเลื่อนออกไปนอกขอบเขตของ worksheet ต้องตรวจสอบว่าตำแหน่งหรือขนาดไม่ลงตัวกับพื้นที่ worksheet
Resources & Related
Resources
-
Microsoft Support – OFFSET Function
official -
ExcelJet – OFFSET Function
guide -
Microsoft Learn – Dynamic Named Ranges
official
Related posts
Related functions
Additional Notes
OFFSET คืออะไร
OFFSET เป็นฟังก์ชันที่ช่วยคุณ ‘เลื่อน’ ไปยังช่วงข้อมูลใหม่โดยอ้างอิงจากตำแหน่งเดิม ลองนึกภาพคุณชี้ไปที่เซลล์ A1 แล้วพูดว่า ‘เลื่อนลง 3 แถว ขวา 2 คอลัมน์’ – นั่นคือสิ่งที่ OFFSET ทำ 😎
ทำไมต้องใช้ OFFSET? เพราะมันทำให้สูตรของคุณ ‘อัจฉริยะ’ – เมื่อข้อมูลเพิ่มขึ้น สูตรจะปรับตัวเองโดยอัตโนมัติ ไม่ต้องแก้มือ
Syntax
=OFFSET(reference, rows, cols, [height], [width])วิธีการทำงาน
OFFSET ทำงานได้ 2 ขั้น:
- เลือกตำแหน่งเริ่มต้น – reference คือจุดเริ่มต้นของเรา
- เลื่อนไปยังตำแหน่งใหม่ – rows และ cols บอกว่าจะเลื่อนไปที่ไหน
เคล็ดลับ: เลขบวก = ลง/ขวา, เลขลบ = ขึ้น/ซ้าย ง่ายมาก!