Thep Excel

Excel · Lookup and reference มีตั้งแต่ Excel 2003 หรือก่อนหน้า ขึ้นไป

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

อ้างอิงช่วงข้อมูลที่เลื่อนจากตำแหน่งเริ่มต้น สร้าง dynamic range ได้

ความนิยม7/10
ความยาก6/10
ประโยชน์8/10

อัปเดต 30 November 2025

ในหน้านี้Syntax & ArgumentsAdditional NotesExamplesFAQsResources & Related

Syntax & Arguments

อาร์กิวเมนต์ ชนิด คำอธิบาย
reference required ตำแหน่งเริ่มต้น (cell หรือ range) ที่จะเลื่อนออกมา
rows required จำนวนแถวที่จะเลื่อน (บวก = ลง, ลบ = ขึ้น)
cols required จำนวนคอลัมน์ที่จะเลื่อน (บวก = ขวา, ลบ = ซ้าย)
height optional ความสูงของช่วง (จำนวนแถว) – ถ้าไม่ระบุ ใช้ความสูงเดิม
width optional ความกว้างของช่วง (จำนวนคอลัมน์) – ถ้าไม่ระบุ ใช้ความกว้างเดิม

Additional Notes

OFFSET คืออะไร

OFFSET เป็นฟังก์ชันที่ช่วยคุณ ‘เลื่อน’ ไปยังช่วงข้อมูลใหม่โดยอ้างอิงจากตำแหน่งเดิม ลองนึกภาพคุณชี้ไปที่เซลล์ A1 แล้วพูดว่า ‘เลื่อนลง 3 แถว ขวา 2 คอลัมน์’ – นั่นคือสิ่งที่ OFFSET ทำ 😎

ทำไมต้องใช้ OFFSET? เพราะมันทำให้สูตรของคุณ ‘อัจฉริยะ’ – เมื่อข้อมูลเพิ่มขึ้น สูตรจะปรับตัวเองโดยอัตโนมัติ ไม่ต้องแก้มือ

Syntax

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

วิธีการทำงาน

OFFSET ทำงานได้ 2 ขั้น:

  1. เลือกตำแหน่งเริ่มต้น – reference คือจุดเริ่มต้นของเรา
  2. เลื่อนไปยังตำแหน่งใหม่ – rows และ cols บอกว่าจะเลื่อนไปที่ไหน

เคล็ดลับ: เลขบวก = ลง/ขวา, เลขลบ = ขึ้น/ซ้าย ง่ายมาก!

How it works

สร้าง Dynamic Chart Source

ใช้ OFFSET ใน Named Range เพื่อให้กราฟสามารถปรับช่วงข้อมูลได้เองโดยอัตโนมัติเมื่อมีการเพิ่มข้อมูลใหม่ ทำให้ไม่ต้องมาแก้ไข Chart Source Data ซ้ำๆ

ดึงข้อมูลแถว/คอลัมน์สุดท้าย

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

Examples

ตัวอย่างพื้นฐาน: ดึงค่าจากเซลล์ที่เลื่อนOFFSET(A1,1,1)

เริ่มจาก A1 แล้วเลื่อนลง 1 แถว ขวา 1 คอลัมน์ ก็ไปหยุดที่ B2 พอดี สูตรเลยดึงค่าใน B2 ออกมาแสดง

fx
=OFFSET(A1,1,1)

ผลลัพธ์25 (ค่าจากเซลล์ B2)
ตัวอย่างการใช้งาน: ผลรวมข้อมูลแบบ DynamicSUM(OFFSET(A1,1,0,5,1))

ตั้งจุดเริ่มที่ A1 เลื่อนลง 1 แถวไปเริ่มที่ A2 แล้วกำหนดขนาดช่วงเป็นสูง 5 แถว กว้าง 1 คอลัมน์ ได้ช่วง A2:A6 ให้ SUM บวกรวมทันที

fx
=SUM(OFFSET(A1,1,0,5,1))

ผลลัพธ์ผลรวมของ A2 ถึง A6
ตัวอย่างขั้นสูง: ค่าเฉลี่ยเคลื่อนที่ (Moving Average)AVERAGE(OFFSET(D31,-6,0,7,1))

เริ่มจากแถวสุดท้าย D31 แล้วเลื่อนขึ้น 6 แถวไปเริ่มที่ D25 กำหนดสูง 7 แถว จึงได้ช่วง D25:D31 พอดี 7 วันล่าสุดเสมอไม่ว่าจะเพิ่มข้อมูลกี่แถว

fx
=AVERAGE(OFFSET(D31,-6,0,7,1))

ผลลัพธ์ค่าเฉลี่ยของ 7 วันล่าสุด (D25:D31)
ตัวอย่างการใช้งาน: Dynamic Named RangeSUM(OFFSET(A1,1,0,COUNTA(A:A)-1,1))

ใช้ COUNTA(A:A) นับจำนวนเซลล์ที่มีข้อมูลในคอลัมน์ A ทั้งหมดรวม header แล้วลบ 1 เพื่อตัด header ออก ทำให้ความสูงของช่วงขยับตามข้อมูลจริงที่เพิ่มเข้ามาเรื่อยๆ

fx
=SUM(OFFSET(A1,1,0,COUNTA(A:A)-1,1))

ผลลัพธ์ผลรวมข้อมูลทั้งหมด A2:A(สุดท้าย) โดยอัตโนมัติ
ตัวอย่างการใช้งาน: ดึงค่าจากตำแหน่งที่เลื่อนขึ้นOFFSET(E5,-2,-1)

ใส่เลขลบใน rows และ cols ทำให้เลื่อนขึ้น 2 แถวและเลื่อนซ้าย 1 คอลัมน์จาก E5 พาไปหยุดที่ D3 พอดี

fx
=OFFSET(E5,-2,-1)

ผลลัพธ์ค่าจากเซลล์ D3

FAQs

OFFSET กับ INDEX ต่างกันยังไง ใช้ตัวไหนดี+

OFFSET เป็นฟังก์ชัน volatile คือคำนวณใหม่ทุกครั้งที่ workbook เปลี่ยนแปลงแม้จะไม่เกี่ยวกับสูตรนี้เลย ทำให้ไฟล์ใหญ่ๆ อืด ส่วน INDEX ไม่ volatile และให้ผลลัพธ์แบบ dynamic range ได้เหมือนกัน ถ้าไม่จำเป็นจริงๆ แนะนำใช้ INDEX แทน

ทำไมสูตร OFFSET ของผมขึ้น #REF!+

เกิดจากเลื่อน rows หรือ cols ออกนอกขอบเขตของชีท เช่น เลื่อนขึ้นเกินแถว 1 หรือเลื่อนซ้ายเกินคอลัมน์ A ลองเช็คค่าที่ใส่ในอาร์กิวเมนต์ rows/cols ว่าเกินขอบไหม

OFFSET ใช้กับ Excel Table ได้ไหม+

ใช้ได้แต่ไม่ค่อยจำเป็น เพราะ Excel Table ขยายขนาดตามข้อมูลอัตโนมัติอยู่แล้วผ่าน structured reference เช่น Table1[คอลัมน์] จึงมักไม่ต้องพึ่ง OFFSET เลย

Resources & Related

ฟังก์ชันที่ผู้เขียนโยงไว้กับ ฟังก์ชัน OFFSET ใน Excel จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง

ฟังก์ชันนี้ฟังก์ชัน OFFSET ใน ExcelLookup and reference
Lookup and reference (6)
AREAS

AREAS นับว่าการอ้างอิงที่เรากำหนดนั้นมีกี่พื้นที่ (area) พื้นที่หมายถึงช่วงเซลล์ต่อเนื่องหรือเซลล์เดี่ยว เช่น ถ้าเราอ้างอิง (A1:B2, D5:E5) ถือว่ามี 2 พื้นที่แยกกัน

INDEX

INDEX คืนค่าหรือ reference ของเซลล์จากตำแหน่งที่ระบุใน range หรือ array โดยอ้างอิงจากหมายเลขแถวและคอลัมน์ที่เราระบุ มักถูกใช้ร่วมกับ MATCH เพื่อสร้าง INDEX-MATCH pattern ที่ยืดหยุ่นกว่า VLOOKUP เยอะ เพราะสามารถดึงข้อมูลจากทิศทางไหนก็ได้ และไม่พังเมื่อมีการแทรกหรือลบคอลัมน์

INDIRECT

INDIRECT แปลงข้อความเป็นการอ้างอิงเซลล์จริง ช่วยให้สามารถเปลี่ยนตำแหน่งเซลล์ได้ไดนามิกโดยใช้ตัวเลขหรือชื่อเซลล์

MATCH

MATCH คืนเลขลำดับตำแหน่งของค่าที่ค้นหาในช่วงข้อมูลแถวเดียวหรือคอลัมน์เดียว รองรับการค้นหา 3 โหมด คือ Exact Match (0) ที่ไม่ต้องเรียงข้อมูล, Approximate Match แบบ Less Than or Equal (1) ที่ต้องเรียงจากน้อยไปมาก, และ Greater Than or Equal (-1) ที่ต้องเรียงจากมากไปน้อย รองรับ Wildcard (* และ ?) ในโหมด Exact Match และมักใช้คู่กับ INDEX เป็นรูปแบบ INDEX-MATCH ที่ยืดหยุ่นกว่า VLOOKUP

TRIMRANGE

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

XLOOKUP

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

เอกสารอ้างอิง

Microsoft Support – OFFSET Functionofficial
ExcelJet – OFFSET Functionguide
Microsoft Learn – Dynamic Named Rangesofficial
บทความที่เกี่ยวข้อง

วิธี SUM เฉพาะค่าที่เห็นจาก Filter แต่มีเงื่อนไข!
วิธีใช้ Power Query ดึงข้อมูลจากไฟล์ Excel เดียวกัน โดยไม่ต้องแปลงเป็น Table ก่อน
สอนวิธีทำ Drop Down List แบบ Search ได้ใน Excel (ไม่ต้องใช้ VBA)
สร้าง Drop down list กี่ชั้นก็ได้ใน Excel (Drop down list หลายชั้น)
เจาะลึกฟังก์ชั่น OFFSET

Comments

อีเมลของคุณจะไม่ถูกเผยแพร่