วันนี้ผมจะแนะนำ ฟังก์ชัน OFFSET ของ Excel พร้อม 3 ตัวอย่างจากสถานการณ์จริง
ก่อนจะอธิบายไวยากรณ์ของสูตร แล้วจะพูดถึงวิธีใช้ OFFSET แก้ปัญหาในชีวิตจริง
บทนำ
ฟังก์ชัน OFFSET สามารถคืนค่าอ้างอิงไปยังเซลล์ (เรียกว่า target cell) หรือช่วง (target range) ที่อยู่ห่างจากเซลล์อ้างอิง (reference cell) หรือช่วงอ้างอิง (reference range) จำนวนแถวและคอลัมน์ที่ระบุ
ภาพด้านล่างแสดงการใช้ OFFSET เพื่อคืนอ้างอิงเซลล์ (ด้านซ้าย) หรือช่วง (ด้านขวา)
จะให้ความเข้าใจโดย trực觀เกี่ยวกับ target cell และ reference cell
เซลล์ที่ไฮไลท์สีเขียวคือ target cell ส่วนเซลล์สีเหลืองเป็น target range
เซลล์สีน้ำเงินคือ reference cells
Figure 1
OFFSET ใน Excel หมายถึงอะไร (Syntax)?
ไวยากรณ์ของฟังก์ชัน OFFSET: OFFSET (reference, rows, cols, [height], [width])
| Reference | จำเป็น. เซลล์หรือช่วงเซลล์ที่เป็นจุดเริ่มต้นของการ offset โปรดทราบว่าเซลล์ต้องติดกันหากระบุเป็นช่วง |
| Rows | จำเป็น. จำนวนแถวขึ้นหรือลงจากเซลล์อ้างอิงหรือเซลล์มุมซ้ายบนของช่วงอ้างอิง Rows เป็นบวกหรือลบได้ ดูด้านซ้าย Figure 1 ถ้าเปลี่ยนเป็น OFFSET(C3, -1, -1) target cell จะเป็น B2 ซึ่งอยู่ 1 แถวเหนือ C3 |
| Cols | จำเป็น. จำนวนคอลัมน์ซ้ายหรือขวาของเซลล์อ้างอิงหรือเซลล์มุมซ้ายบนของช่วงอ้างอิง ค่า Cols เป็นบวกหรือลบได้ เช่น ตั้ง B4 เป็น reference และ C3 เป็น target สูตรคือ OFFSET(B4, -1, 1) Cols เป็นบวก เพราะ C3 อยู่ขวา B4 1 คอลัมน์ |
| Height | ตัวเลือก. ใช้ก็ต่อเมื่อ target เป็นช่วง บอกจำนวนแถวของ target range Height ต้องเป็นบวก จาก Figure 1 ด้านขวา target range มี 2 แถว จึงตั้ง Height = 2 |
| Width | ตัวเลือก. ใช้ก็ต่อเมื่อ target เป็นช่วง (ดู Figure 1 ด้านขวา) บอกจำนวนคอลัมน์ของ target range Width ต้องเป็นบวก |
ต่อไปจะแสดงวิธีใช้ OFFSET แก้ปัญหาจริง
กรณีที่ 1: การค้นหาขวา-ซ้าย (Right-to-Left Lookup) ด้วย OFFSET และ MATCH
所周知ว่า VLOOKUP ค้นหาได้แค่ซ้าย-ขวา ค่าที่ค้นหาต้องอยู่คอลัมน์แรกของ table array หากต้องการเพิ่มคอลัมน์ค้นหาใหม่หรือเปลี่ยนคอลัมน์ค้นหา ต้องเลื่อนตารางทั้งหมดหรือเปลี่ยนโครงสร้างข้อมูล
แต่รวม OFFSET กับ MATCH จะขจัดข้อจำกัดของ VLOOKUP ได้
ฟังก์ชัน MATCH คืออะไร และรวมกับ OFFSET ยังไง?
MATCH ค้นหา item ในช่วงเซลล์ และคืนตำแหน่งสัมพัทธ์ของ item นั้น
ตัวอย่าง ช่วง B3:B8 จาก Figure 2.1 (แสดงรายได้ของประเทศต่างๆ ตามปี) สูตร “=MATCH(“USA”, B3:B8, 0)” คืน 1 เพราะ USA เป็นรายการแรก (ดูเซลล์ B10, C10)
สำหรับช่วง C2:F2 สูตร “=MATCH(2015, C2:F2, 0)” คืน 3 เพราะ 2015 เป็นรายการที่ 3 (ดู B11, C11)
กลับมาที่ OFFSET หากตั้ง B2 เป็น reference และ E3 เป็น target สูตร OFFSET จะเป็น?
E3 อยู่ 1 แถวใต้ B2 และ 3 คอลัมน์ขวา สูตรคือ “=OFFSET(B2, 1, 3)” สังเกตตัวเลขสีแดง จะพบว่าตรงกับผลลัพธ์จาก MATCH
นั่นคือคำตอบ: ฟังก์ชัน MATCH ใช้เป็นอาร์กิวเมนต์ที่ 2 หรือ 3 ของ OFFSET (ดูเซลล์ C13)
เซลล์ C14 ใช้ VLOOKUP ดึงข้อมูลเดียวกัน ต้องรู้ว่ารายได้ปี 2015 อยู่คอลัมน์ที่ 4 ของ table array B2:F8 ก่อนเขียน VLOOKUP ต้องรู้โครงสร้างข้อมูลดี ซึ่งเป็นข้อจำกัดอีกอย่าง แต่ใช้ MATCH เป็นอาร์กิวเมนต์ของ OFFSET ไม่ต้องรู้ column index มีประโยชน์มากถ้ามีคอลัมน์เยอะ
Figure 2.1
ต่อไปตัวอย่างซับซ้อนกว่า
สมมติมีตารางมี Company Name, Contact Name, Email Address ของบริษัทต่างๆ
ต้องการดึง Company Name จาก Contact Name ที่รู้ หรือดึง Contact Name จาก Email ที่รู้ ทำยังไง?
ดู Figure 2.2 ช่วง B5:E8 มีข้อมูลบริษัท ใส่ค่าใน C2 และ B3 ด้วยสูตรในกรอบสีแดง สามารถดึง Company Name จาก Contact Name ที่รู้ได้
ช่วง D2:E4 แสดงวิธีดึง Contact Name จาก Email ที่รู้
สรุป: ตัวอย่างทั้งสองแสดงให้เห็นว่าสามารถค้นหาขวา-ซ้ายได้ และค่าค้นหาไม่จำเป็นต้องอยู่คอลัมน์สุดขวา คอลัมน์ไหนใน table array ก็สามารถใส่ค่าค้นหาได้
Figure 2.2
กรณีที่ 2: การคำนวณอัตโนมัติด้วย OFFSET และ COUNT
ก่อนจะอธิบายการคำนวณอัตโนมัติเมื่อเพิ่มตัวเลขใหม่ในคอลัมน์ มาเริ่มจากการดึงตัวเลขตัวสุดท้ายในคอลัมน์อัตโนมัติ
ดูภาพด้านล่างแสดงรายการจาก Human Resources สมมติต้องการตัวเลขตัวสุดท้ายในคอลัมน์ B สูตร OFFSET จะเป็น “=OFFSET(C2, 9, 0)”
จากสูตร 9 คือตัวเลขสำคัญ ตราบใดที่หา 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(B:B)” คืน 0 ไม่ใช่ 9 เพราะคอลัมน์ B ไม่มีตัวเลข (ดู G3, H3)
คอลัมน์ D มี 10 เซลล์ที่มีตัวเลข COUNT(D:D) = 10 แต่ถ้าดึงตัวเลขตัวสุดท้ายใน D เหมือน C จะได้ 0 (G8, H8) เพราะ D13 อยู่ 11 แถวจาก D2 ไม่ใช่ 10 แถว สูตร “=OFFSET(D2, COUNT(D:D) + 1, 0)” ใน G7 ยืนยัน
สรุป: ตัวเลขต้องติดกันต่อเนื่อง ถ้าจะใช้ COUNT ร่วม OFFSET เพื่ออัตโนมัติการคำนวณ
Figure 3
กรณีที่ 3: ใช้ OFFSET สร้าง Dynamic Range
สมมติต้องการสร้างกราฟยอดขายรายเดือน Figure 4.1 แสดงข้อมูลปัจจุบันและกราฟที่สร้างจากข้อมูลนั้น
ทุกเดือนจะเพิ่มยอดขายเดือนล่าสุดด้านล่างตัวเลขตัวสุดท้ายในคอลัมน์ C
มีวิธีอัปเดตกราฟอัตโนมัติไหม?
กุญแจคือใช้ OFFSET สร้างชื่อช่วงไดนามิก (dynamic range names) สำหรับคอลัมน์ Units Sold
ช่วงไดนามิกสำหรับยอดขายจะรวมข้อมูลทั้งหมดอัตโนมัติเมื่อมีข้อมูลใหม่
Figure 4.1
สร้างช่วงไดนามิก: คลิกแท็บ Formulas เลือก Name Manager หรือ Define Name
กล่อง New Name จะขึ้นถ้าคลิก Define Name
ถ้าเลือก Name manager ต้องคลิก New เพื่อให้กล่อง New Name ปรากฏ
Figure 4.2
ในกล่อง “Name:” ใส่ชื่อช่วงไดนามิก ใน “Refers to:” ใส่สูตร OFFSET “=OFFSET(Figure4!$C$2, 1, 0, COUNT(!$C:$C), 1)” ที่จะสร้างช่วงไดนามิกจากค่า Units Sold ในคอลัมน์ C
โดยปกติชื่อจะใช้ได้ทั้งสมุดงานและต้องไม่ซ้ำ แต่ต้องการจำกัดขอบเขตให้แผ่นงานเฉพาะ
จึงเลือก Figure4 ใน “Scope:” คลิก OK ช่วงไดนามิกสร้างเสร็จ จะรวมข้อมูลใหม่อัตโนมัติ
คลิกขวาที่จุดใดในกราฟ เลือก “Select Data”
Figure 4.3
ใน Select Data Source เลือก Series1 แล้ว Edit
Figure 4.4
พิมพ์ “=Figure4!Units” ตาม Figure 4.5
Figure 4.5
ลองพิมพ์ 11 ใน C13 จะเห็นกราฟเปลี่ยนและรวมค่า 11 เข้าไป
กราฟจะเปลี่ยนอัตโนมัติเมื่อเพิ่มข้อมูลใหม่
Figure 4.6
อ่านเพิ่มเติม…
- Offset(…) Function in Excel with Examples
ดาวน์โหลดไฟล์ทำงาน
ดาวน์โหลดไฟล์ทำงานจากลิงก์ด้านล่าง
Excel-Offset-Function.rar