ฟังก์ชัน Excel OFFSET

ฟังก์ชัน Excel OFFSET จะคืนค่าการอ้างอิงไปยังช่วงที่สร้างด้วยอินพุตห้าตัว: (1) จุดเริ่มต้น (2) ออฟเซ็ตของแถว (3) ออฟเซ็ตของคอลัมน์ (4) ความสูงเป็นแถว (5) ความกว้างใน คอลัมน์ OFFSET มีประโยชน์ในสูตรที่ต้องใช้ช่วงไดนามิก

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

ฟังก์ชัน Excel OFFSET ส่งคืนช่วงไดนามิกที่สร้างด้วยอินพุตห้ารายการ: (1) จุดเริ่มต้น (2) ออฟเซ็ตแถว (3) ออฟเซ็ตคอลัมน์ (4) ความสูงเป็นแถว (5) ความกว้างในคอลัมน์

จุดเริ่มต้น ( อ้างอิง อาร์กิวเมนต์) สามารถเป็นหนึ่งเซลล์หรือช่วงของเซลล์ NS แถว และ โคลส อาร์กิวเมนต์คือจำนวนเซลล์ที่จะ 'ออฟเซ็ต' จากจุดเริ่มต้น NS ความสูง และ ความกว้าง อาร์กิวเมนต์เป็นทางเลือกและกำหนดขนาดของช่วงที่สร้างขึ้น เมื่อไหร่ ความสูง และ ความกว้าง จะถูกละเว้น โดยค่าเริ่มต้นจะเป็นความสูงและความกว้างของ อ้างอิง .

คำนวณจำนวนวันทำงานระหว่างวันที่สองวัน

ตัวอย่างเช่น ในการอ้างอิง C5 เริ่มต้นที่ A1 อ้างอิง คือ A1, แถว คือ 4 และ โคลส คือ 2:

 
= OFFSET (A1,4,2) // returns reference to C5

ในการอ้างอิง C1:C5 จาก A1 อ้างอิง คือ A1, แถว คือ 0 โคลส คือ 2, ความสูง คือ 5 และ ความกว้าง คือ 1:

 
= OFFSET (A1,0,2,5,1) // returns reference to C1:C5

หมายเหตุ: สามารถละเว้นความกว้างได้ เนื่องจากจะมีค่าเริ่มต้นเป็น 1

เป็นเรื่องปกติที่จะเห็น OFFSET รวมอยู่ในฟังก์ชันอื่นที่คาดไว้เป็นช่วง ตัวอย่างเช่น ถึง SUM C1:C5 เริ่มต้นที่ A1:

 
= SUM ( OFFSET (A1,0,2,5,1)) // SUM C1:C5

วัตถุประสงค์หลักของ OFFSET คือการอนุญาตให้สูตรปรับแบบไดนามิกเป็นข้อมูลที่มีอยู่หรือป้อนข้อมูลของผู้ใช้ สามารถใช้ฟังก์ชัน OFFSET เพื่อสร้าง a ไดนามิกชื่อ range สำหรับแผนภูมิหรือตารางสรุปข้อมูล เพื่อให้มั่นใจว่าข้อมูลต้นฉบับเป็นปัจจุบันเสมอ

หมายเหตุ: เอกสาร Excel ระบุว่า ความสูง และ ความกว้าง ไม่สามารถลบได้ แต่ค่าลบดูเหมือนจะทำงานได้ดี ตั้งแต่ช่วงต้นทศวรรษ 1990 เป็นต้นมา . ฟังก์ชัน OFFSET ใน Google ชีตจะไม่อนุญาตให้มีค่าลบสำหรับอาร์กิวเมนต์ความสูงหรือความกว้าง

excel แยกข้อความจากกลางสตริง

ตัวอย่าง

ตัวอย่างด้านล่างแสดงให้เห็นว่าสามารถกำหนดค่า OFFSET ให้ส่งคืนช่วงประเภทต่างๆ ได้อย่างไร หน้าจอเหล่านี้ถ่ายด้วย Excel 365 ดังนั้น OFFSET จะส่งกลับ a ไดนามิกอาร์เรย์ เมื่อผลลัพธ์มีมากกว่าหนึ่งเซลล์ ใน Excel เวอร์ชันเก่า คุณสามารถใช้ปุ่ม แป้น F9 เพื่อตรวจสอบผลลัพธ์ที่ส่งคืนจาก OFFSET

ตัวอย่าง #1

ในหน้าจอด้านล่าง เราใช้ OFFSET เพื่อคืนค่าที่สาม (มีนาคม) ในคอลัมน์ที่สอง (ตะวันตก) สูตรใน H4 คือ:

 
= OFFSET (B3,3,2) // returns D6

ตัวอย่าง #2

ในหน้าจอด้านล่าง เราใช้ OFFSET เพื่อคืนค่าสุดท้าย (มิถุนายน) ในคอลัมน์ที่สาม (เหนือ) สูตรใน H4 คือ:

 
= OFFSET (B3,6,3) // returns E9

ตัวอย่าง #3

ด้านล่าง เราใช้ OFFSET เพื่อคืนค่าทั้งหมดในคอลัมน์ที่สาม (เหนือ) สูตรใน H4 คือ:

 
= OFFSET (B3,1,3,6) // returns E4:E9

ตัวอย่าง #4

ด้านล่าง เราใช้ OFFSET เพื่อคืนค่าทั้งหมดสำหรับเดือนพฤษภาคม (แถวที่ห้า) สูตรใน H4 คือ:

 
= OFFSET (B3,5,1,1,4) // returns C8:F8

ตัวอย่าง #5

ด้านล่าง เราใช้ OFFSET เพื่อคืนค่าเดือนเมษายน พฤษภาคม และมิถุนายนสำหรับภูมิภาคตะวันตก สูตรใน H4 คือ:

 
= OFFSET (B3,4,2,3,1) // returns D7:D9

ตัวอย่าง #6

ด้านล่างนี้ เราใช้ OFFSET เพื่อคืนค่าเดือนเมษายน พฤษภาคม และมิถุนายนสำหรับฝั่งตะวันตกและทิศเหนือ สูตรใน H4 คือ:

สร้างแผนภาพกระจายใน excel

 
= OFFSET (B3,4,2,3,2) // returns D7:E9

หมายเหตุ

• OFFSET จะคืนค่าอ้างอิงเท่านั้น ไม่มีการย้ายเซลล์
• ทั้งคู่ แถว และ โคลส สามารถระบุเป็นตัวเลขติดลบเพื่อกลับทิศทางออฟเซ็ตปกติได้ - ลบ โคลส ชดเชยไปทางซ้ายและลบ แถว ออฟเซ็ตด้านบน
• OFFSET คือ ' ฟังก์ชันระเหย ' – มันจะคำนวณใหม่เมื่อมีการเปลี่ยนแปลงแต่ละเวิร์กชีต ฟังก์ชันผันผวนสามารถทำให้เวิร์กบุ๊กขนาดใหญ่และซับซ้อนมากขึ้นทำงานช้าลง
• OFFSET จะแสดง #REF! ค่าความผิดพลาดหากออฟเซ็ตอยู่นอกขอบของเวิร์กชีต
• เมื่อละความสูงหรือความกว้าง ความสูงและความกว้างของ อ้างอิง ถูกนำมาใช้.
• สามารถใช้ OFFSET กับฟังก์ชันอื่นๆ ที่คาดว่าจะได้รับการอ้างอิง
• เอกสาร Excel พูดว่า ความสูง และ ความกว้าง ไม่สามารถเป็นค่าลบได้ แต่ค่าลบใช้งานได้