การใช้ฟังก์ชันออฟเซ็ตใน Excel [Offset – Match Combo, Dynamic Range]

วันนี้ฉันอยากจะแนะนำให้คุณรู้จักกับฟังก์ชัน OFFSET ของ Excel กับ 3 ตัวอย่างในชีวิตจริง

ตอนแรก ฉันจะอธิบายไวยากรณ์ของสูตร จากนั้นฉันจะพูดถึงวิธีการใช้ฟังก์ชัน OFFSET เพื่อแก้ปัญหาในชีวิตจริง

แนะนำตัว

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

รูปด้านล่างแสดงวิธีใช้ฟังก์ชัน OFFSET เพื่อคืนค่าการอ้างอิงไปยังเซลล์ (ส่วนซ้าย) หรือช่วง (ส่วนขวา)

ซึ่งจะทำให้คุณเข้าใจโดยสัญชาตญาณว่าเซลล์เป้าหมายคืออะไรและเซลล์อ้างอิงคืออะไร

เซลล์ที่ไฮไลต์เป็นสีเขียวคือเซลล์เป้าหมาย ขณะที่เซลล์ที่ไฮไลต์ด้วยสีเหลืองประกอบด้วยช่วงเป้าหมาย

เซลล์ที่ไฮไลต์เป็นสีน้ำเงินคือเซลล์อ้างอิง

รูปที่ 1

OFFSET หมายถึงอะไรใน Excel (ไวยากรณ์)

นี่คือไวยากรณ์ของฟังก์ชันออฟเซ็ต: OFFSET (การอ้างอิง แถว cols [ความสูง] [ความกว้าง])

ตอนนี้ให้ฉันแสดงวิธีใช้ฟังก์ชัน OFFSET เพื่อแก้ปัญหาในชีวิตจริง

กรณีที่ 1:ค้นหาจากขวาไปซ้ายโดยการรวมฟังก์ชัน OFFSET และ MATCH

เป็นที่ทราบกันดีว่าคุณสามารถค้นหาจากซ้ายไปขวาได้ด้วยฟังก์ชัน VLOOKUP

ค่าที่จะค้นหาต้องอยู่ในคอลัมน์แรกของอาร์เรย์ตารางของคุณ

คุณต้องเลื่อนช่วงตารางทั้งหมดไปทางขวาทีละคอลัมน์หากต้องการเพิ่มค่าการค้นหาใหม่หรือต้องเปลี่ยนโครงสร้างข้อมูลหากต้องการใช้คอลัมน์อื่นเป็นค่าการค้นหา

แต่การรวม OFFSET ร่วมกับฟังก์ชัน Match จะทำให้สามารถลบข้อจำกัดของฟังก์ชัน VLOOKUP ได้

ฟังก์ชัน MATCH คืออะไร และเราจะรวมฟังก์ชัน OFFSET กับฟังก์ชัน Match เพื่อค้นหาได้อย่างไร

ฟังก์ชัน Match จะค้นหารายการที่ระบุในช่วงของเซลล์ จากนั้นส่งคืนตำแหน่งสัมพัทธ์ของรายการนั้นในช่วง

ลองใช้ช่วง B3:B8 จากรูปที่ 2.1 (ซึ่งแสดงรายได้ของประเทศต่างๆ ในปีต่างๆ) เป็นตัวอย่าง

สูตร “=MATCH (“USA”, B3:B8, 0)” จะส่งกลับ 1 เนื่องจากสหรัฐอเมริกาเป็นรายการแรกในช่วง (ดูเซลล์ B10 และ C10)

สำหรับช่วงอื่น C2:F2 สูตร “=MATCH (2015, C2:F2, 0)” จะส่งกลับ 3 เนื่องจากปี 2015 เป็นรายการที่สามในช่วง (ดูเซลล์ B11 และ C11)

กลับไปที่ฟังก์ชัน OFFSET

ถ้าเราตั้งค่าเซลล์ B2 เป็นเซลล์อ้างอิง และใช้เซลล์ E3 เป็นเซลล์เป้าหมาย เราจะเขียนสูตร OFFSET ได้อย่างไร

E3 คือ 1 แถวด้านล่าง B2 และ 3 คอลัมน์ทางขวาไปที่ B2

ดังนั้น สามารถเขียนสูตรเป็น “=OFFSET(B2, 1 , 3 )”. ดูตัวเลขสีแดงอย่างใกล้ชิด พบว่าตรงกันหรือไม่

นั่นคือคำตอบของคำถาม – วิธีรวมฟังก์ชัน OFFSET กับฟังก์ชันจับคู่ – ฟังก์ชันจับคู่สามารถใช้เป็นอาร์กิวเมนต์ที่สองหรือสามของฟังก์ชัน OFFSET (ดูเซลล์ C13)

เซลล์ C14 สาธิตวิธีใช้ฟังก์ชัน VLOOKUP เพื่อดึงข้อมูลเดียวกัน

เราต้องรู้ว่ารายได้ปี 2015 บันทึกไว้ใน 4 ^th ของอาร์เรย์ตาราง B2:F8 ก่อนเขียนฟังก์ชัน VLOOKUP

หมายความว่าเราต้องรู้เป็นอย่างดีเกี่ยวกับโครงสร้างข้อมูลเมื่อใช้ฟังก์ชัน VLOOKUP

นี่เป็นอีกหนึ่งข้อจำกัดสำหรับ VLOOKUP อย่างไรก็ตาม โดยใช้ฟังก์ชัน MATCH เป็นอาร์กิวเมนต์ของฟังก์ชัน OFFSET เราไม่จำเป็นต้องรู้ดัชนีคอลัมน์

สิ่งนี้มีประโยชน์มากหากมีคอลัมน์จำนวนมาก

รูปที่ 2.1

ต่อไปเรามาดูตัวอย่างที่ซับซ้อนกว่านี้กัน

สมมติว่าเรามีตารางที่มีชื่อบริษัท ชื่อผู้ติดต่อ และที่อยู่อีเมลสำหรับบริษัทต่างๆ

และเราต้องการเรียกชื่อบริษัทจากชื่อผู้ติดต่อที่รู้จักหรือรับชื่อผู้ติดต่อจากที่อยู่อีเมลที่รู้จัก เราทำอะไรได้บ้าง？

ดูรูปที่ 2.2 ช่วง B5:E8 รวมข้อมูลบริษัท ด้วยการใส่อินพุตในเซลล์ C2 และเซลล์ B3 ด้วยความช่วยเหลือของสูตรในสี่เหลี่ยมสีแดง ฉันสามารถเรียกชื่อบริษัทได้หากฉันรู้ชื่อผู้ติดต่อ

ช่วง D2:E4 แสดงวิธีรับชื่อผู้ติดต่อด้วยที่อยู่อีเมลที่รู้จัก

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

รูปที่ 2.2

กรณีที่ 2:คำนวณอัตโนมัติโดยรวมฟังก์ชัน OFFSET และ COUNT

ก่อนที่จะแนะนำวิธีการคำนวณอัตโนมัติทุกครั้งที่เราเพิ่มตัวเลขใหม่ในคอลัมน์ เรามาเริ่มกันที่วิธีคืนค่าตัวเลขสุดท้ายในคอลัมน์โดยอัตโนมัติในตอนแรกก่อน

ดูรูปด้านล่างซึ่งแสดงรายการจากทรัพยากรบุคคล สมมติว่าเราต้องการได้ตัวเลขสุดท้ายในคอลัมน์ B สูตรจะเป็น “=OFFSET (C2, 9 , 0)” หากเราใช้ฟังก์ชัน OFFSET

จากสูตรจะได้รู้ว่า 9 เป็นหมายเลขสำคัญ

ตราบใดที่เราสามารถส่งคืนหมายเลขนี้โดยอัตโนมัติ เราก็สามารถค้นหาหมายเลขสุดท้ายในคอลัมน์ได้โดยอัตโนมัติ

9 เป็นเพียงจำนวนเซลล์ที่มีตัวเลขในคอลัมน์ C

หากคุณคุ้นเคยกับฟังก์ชัน COUNT คุณจะรู้ว่าฟังก์ชัน COUNT สามารถนับจำนวนเซลล์ที่มีตัวเลขอยู่ในช่วงได้

ตัวอย่างเช่น สูตร “=COUNT (C3:C11)” จะนับจำนวนเซลล์ที่มีตัวเลขในเซลล์ C3 ถึง C11

ในกรณีของเรา เราต้องการทราบจำนวนตัวเลขในคอลัมน์ทั้งหมด ดังนั้น จึงควรใช้การอ้างอิงเช่น C:C ซึ่งรวมถึงแถวทั้งหมดในคอลัมน์ C

โปรดดูที่เซลล์ G4 และ H4 ตัวเลขที่ส่งคืนโดย “=COUNT(C:C)” เท่ากับ 9 .

ดังนั้น โดยการแทนที่ 9 ด้วย COUNT(C:C) ในฟังก์ชัน OFFSET ด้านบน เราจะได้สูตรใหม่ “=OFFSET (C2, COUNT(C:C) , 0)” (ในเซลล์ H5)

ตัวเลขที่ส่งคืนคือ 87000 ซึ่งเป็นตัวเลขสุดท้ายในคอลัมน์ C

ตอนนี้ให้ไปที่การคำนวณอัตโนมัติ สมมติว่าเราต้องการผลรวมของตัวเลขทั้งหมดในคอลัมน์ C

สูตรจะเป็น “=SUM (OFFSET (C2, 1, 0, 9 .) , 1))” หากเราใช้ SUM ร่วมกับ OFFSET

9 คือจำนวนแถวทั้งหมดในช่วง C3:C11 และจำนวนเซลล์ทั้งหมดที่มีตัวเลขในคอลัมน์ C

ดังนั้น เราสามารถเขียนสูตรในรูปแบบใหม่ เช่น “=SUM (OFFSET (C2,1, 0, COUNT (C:C), 1))”

ดูเซลล์ G10 และ H10 จำนวนเงินเดือนทั้งหมดสำหรับพนักงาน 9 คนนี้คือ $521,700

ตอนนี้ ถ้าคุณใส่ตัวเลข เช่น $34,000 ในเซลล์ C12 ทั้งตัวเลขในเซลล์ G5 และ G10 จะเปลี่ยนเป็น $34,000 และ $555,700 ตามลำดับ

นี่คือสิ่งที่เรียกว่าระบบอัตโนมัติ เพราะคุณไม่จำเป็นต้องอัปเดตสูตรในเซลล์ G5 หรือ G10

คุณต้องระวังเมื่อคุณใช้ฟังก์ชัน COUNT เนื่องจากฟังก์ชัน COUNT จะส่งกลับเฉพาะจำนวนเซลล์ที่มีตัวเลข

ตัวอย่างเช่น “=COUNT (B:B)” คืนค่า 0 แทนที่จะเป็น 9 เนื่องจากไม่มีเซลล์ในคอลัมน์ B ที่มีตัวเลข (ดูเซลล์ G3 และ H3)

คอลัมน์ D ประกอบด้วย 10 เซลล์ที่มีตัวเลขและตัวเลขที่ส่งคืนโดย “COUNT (D:D)” ก็คือ 10 ด้วย

แต่ถ้าเราต้องการดึงหมายเลขสุดท้ายในคอลัมน์ D เหมือนที่เราทำสำหรับคอลัมน์ C เราก็จะได้หมายเลข 0 (ดูเซลล์ G8 และ H8)

เห็นได้ชัดว่า 0 ไม่ใช่สิ่งที่เราต้องการ มีอะไรผิดปกติ? เซลล์ D13 อยู่ห่างจากเซลล์ D2 11 แถวแทนที่จะเป็น 10 แถว

นอกจากนี้ยังสามารถแสดงให้เห็นได้ด้วยสูตร “=OFFSET (D2, COUNT (D:D) + 1 , 0)” ในเซลล์ G7

โดยสรุป ตัวเลขควรอยู่ติดกันหากเราต้องการใช้ฟังก์ชัน COUNT ร่วมกับฟังก์ชัน OFFSET เพื่อเปิดใช้งานการคำนวณอัตโนมัติ

รูปที่ 3

กรณีที่ 3:ใช้ฟังก์ชัน OFFSET เพื่อสร้างช่วงไดนามิก

สมมติว่าเราต้องการสร้างแผนภูมิยอดขายหน่วยรายเดือนของบริษัท และรูปที่ 4.1 แสดงข้อมูลปัจจุบันและแผนภูมิที่สร้างขึ้นจากข้อมูลปัจจุบัน

ในแต่ละเดือน ยอดขายยูนิตของเดือนล่าสุดจะถูกเพิ่มไว้ด้านล่างตัวเลขสุดท้ายในคอลัมน์ C

มีวิธีง่ายๆ ในการอัปเดตแผนภูมิโดยอัตโนมัติหรือไม่

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

ช่วงไดนามิกสำหรับการขายของหน่วยจะรวมข้อมูลการขายทั้งหมดโดยอัตโนมัติเมื่อป้อนข้อมูลใหม่

รูปที่ 4.1

หากต้องการสร้างช่วงไดนามิก ให้คลิกสูตร แท็บ จากนั้นเลือก ตัวจัดการชื่อ หรือ กำหนดชื่อ .

ด้านล่าง ชื่อใหม่ กล่องโต้ตอบจะแจ้งหากคุณคลิกที่ กำหนดชื่อ .

หากคุณเลือก ตัวจัดการชื่อ คุณต้องคลิก ใหม่ . ด้วย เพื่อสร้าง ชื่อใหม่ . ด้านล่าง กล่องโต้ตอบปรากฏขึ้น

รูปที่ 4.2

ใน “ชื่อ: ” ช่องป้อนข้อมูล ควรกรอกชื่อช่วงไดนามิก และใน “อ้างอิงถึง:” ช่องใส่เราต้องพิมพ์สูตร OFFSET “=OFFSET (Figure4!$C$2, 1, 0, COUNT (!$C:$C), 1)” ที่จะสร้างช่วงไดนามิกของค่าตามค่าหน่วยที่ขาย พิมพ์ในคอลัมน์ C.

โดยค่าเริ่มต้น ชื่อจะนำไปใช้กับสมุดงานทั้งหมดและจะต้องไม่ซ้ำกันภายในสมุดงาน

อย่างไรก็ตาม เราต้องการจำกัดขอบเขตเฉพาะแผ่นงานบางแผ่น

ดังนั้นเราจึงเลือกรูปที่ 4 ที่นี่ใน “ขอบเขต: ” ช่องใส่ของ หลังจากคลิกที่ ตกลง , ช่วงไดนามิกถูกสร้างขึ้น

มันจะรวมข้อมูลการขายทั้งหมดโดยอัตโนมัติเมื่อป้อนข้อมูลใหม่

ตอนนี้ให้คลิกขวาที่จุดใดก็ได้ในแผนภูมิแล้วเลือก “เลือกข้อมูล”

รูปที่ 4.3

ในข้อความแจ้ง เลือกข้อมูล ที่มา เลือก Series1 แล้ว แก้ไข

รูปที่ 4.4

แล้วพิมพ์ “=Figure4!Units” ตามภาพที่ 4.5 แสดง

รูปที่ 4.5

สุดท้าย มาลองพิมพ์ 11 ในเซลล์ C13 คุณจะเห็นว่าแผนภูมิมีการเปลี่ยนแปลงและรวมค่า 11 แล้ว

แผนภูมิจะเปลี่ยนโดยอัตโนมัติเมื่อมีการเพิ่มข้อมูลใหม่

รูปที่ 4.6

อ่านต่อ…

• ฟังก์ชัน Offset(…) ใน Excel พร้อมตัวอย่าง

ดาวน์โหลดไฟล์การทำงาน

ดาวน์โหลดไฟล์การทำงานจากลิงค์ด้านล่าง