Excel OFFSET ฟังก์ชัน

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

วากยสัมพันธ์

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

ข้อโต้แย้ง

ส่งคืนค่า

ฟังก์ชัน OFFSET จะส่งคืนค่าชดเชยการอ้างอิงเซลล์จากจุดเริ่มต้นที่กำหนด

หมายเหตุฟังก์ชัน

1. # มูลค่า! ค่าความผิดพลาดจะส่งกลับเมื่อการอ้างอิงอ้างถึงช่วงของเซลล์ที่ไม่ต่อเนื่อง
2. #REF! ค่าความผิดพลาดจะกลับมาเมื่อแถวและคอลัมน์หักล้างการอ้างอิงที่ขอบของแผ่นงาน

ตัวอย่าง

ตัวอย่างที่ 1: การใช้งานพื้นฐานสำหรับฟังก์ชัน OFFSET

ส่งคืนการอ้างอิงไปยังเซลล์ด้วยสูตรด้านล่าง:
=OFFSET(B2,3,1)

ในกรณีนี้ B2 คือจุดเริ่มต้นหมายเลข 3 และ 1 หมายความว่าให้ย้าย 3 แถวลงและ 1 คอลัมน์จากเซลล์ B2 และส่งคืนค่าใน C5 ในที่สุด ดูภาพหน้าจอ:

ส่งคืนการอ้างอิงไปยังช่วงของเซลล์ด้วยสูตรด้านล่าง:
=OFFSET(B2,3,1,2,2)

ในกรณีนี้คุณจะได้ผลลัพธ์ของช่วง 2 x 2 ซึ่งเป็น 3 แถวด้านล่างและ 1 คอลัมน์ทางขวาของเซลล์ B2

หมายเหตุ: #VALUE! ข้อผิดพลาดจะเกิดขึ้นเมื่อคุณเลือกเพียงเซลล์เดียวเพื่อใช้ฟังก์ชัน OFFSET เพื่อส่งคืนช่วงของเซลล์ คุณต้องเลือกช่วง 2 x 2 (พูดว่าเซลล์ว่าง 4 เซลล์) ป้อนสูตรแล้วกดปุ่ม Ctrl + เปลี่ยน + เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

ตัวอย่างที่ 2: ใช้ฟังก์ชัน OFFSET เพื่อรวมช่วงของค่า

ดังที่เราได้กล่าวไว้ข้างต้นหากคุณพยายามใช้ฟังก์ชัน OFFSET เช่น =OFFSET(B2,3,1,2,2) ด้วยตัวมันเองในเซลล์เดียวมันจะส่งกลับ #VALUE! ข้อผิดพลาด อย่างไรก็ตามหากคุณรวมฟังก์ชัน SUM และ OFFSET ตามที่แสดงด้านล่างภาพหน้าจอจะส่งคืนผลรวมของค่าในช่วง C5: D6 โดยตรง

1. เลือกเซลล์ว่างคัดลอกสูตรด้านล่างลงไปแล้วกดปุ่ม เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
=SUM(OFFSET(B2,3,1,2,2)))

ตัวอย่างที่ 3: รวมคอลัมน์ตามเกณฑ์ที่กำหนด

ตามภาพด้านล่างนี้จะทำอย่างไรจึงจะได้ยอดขายมะม่วงทั้งหมดจากอาถึงอ. โปรดลองตามด้านล่าง

1. เลือกเซลล์ว่างคัดลอกสูตรด้านล่างลงไปแล้วกดปุ่ม เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

หมายเหตุ / รายละเอียดเพิ่มเติม:

1. ในสูตรข้างต้น MATCH(G4,B3:B8) กำลังมองหา Mango และคืนตำแหน่งในระยะ B3: B8. มาดูกันว่า Mango อยู่ 5 แถวด้านล่างเซลล์ B2 ดังนั้นจึงส่งกลับตัวเลข 5
2. เนื่องจากผลลัพธ์ MATCH คือ 5 และเครื่องหมายจุลภาคสุดท้ายที่นี่แทน 0 ฟังก์ชัน OFFSET ในขณะนี้จึงแสดงเป็น OFFSET(C2:E2,5,0)ซึ่งหมายความว่าค่าชดเชยเริ่มต้นจากช่วง C2: E2 เลื่อน 5 แถวลงและ 0 คอลัมน์ไปทางขวาเพื่อรับการอ้างอิงไปยังช่วง C7: E7
3. จากนั้นฟังก์ชัน SUM จะแสดงเป็น SUM(C7:E7)และสุดท้ายส่งคืนผลรวมของค่าในช่วง C7: E7

ตัวอย่างเพิ่มเติม

จะรวมทุก n แถวลงใน Excel ได้อย่างไร

วิธีสร้างช่วงที่ตั้งชื่อแบบไดนามิกใน Excel

วิธีการคืนค่าเซลล์ทุกๆห้าหรือแถวที่ n ใน Excel