OFFSET Function

สวัสดีครับทุกท่าน จากบทความที่แล้วเกี่ยวกับเรื่อง Array ใน Excel แล้วบทความนี้จะลองนำความรู้ของ Array มาประยุกต์ใช้กันครับ โดยสูตร OFFSET นี้มีประโยชน์มากเช่นกันครับ เอาไว้ทำหน้าที่ค้นหาข้อมูลคล้ายๆกับ VLOOKUP และ INDEX แต่มีความสามารถมากกว่าครับ ลองมาดูรายละเอียดจากบทความนี้กันครับ

 

โครงสร้างสูตร OFFSET

OFFSET(ช่องตั้งต้น, จำนวนการเลื่อนแนวแถว, จำนวนการเลื่อนแนวคอลัมม์, [จำนวนแถวเพื่อคืนค่า]. [จำนวนคอลัมม์เพื่อคืนค่า])

ดูสูตรแล้วอาจจะทำความเข้าใจยาก ผมเลยอยากให้นึกสูตร Index ครับ โดยหลักการคือใช้การเลื่อนตำแหน่งเพื่อไปดึงค่าออกมาครับ โดยเราจะต้องกำหนดอย่างน้อย 3 อย่างนี้ครับ
1. ช่องตั้งต้น > เป็นการกำหนดช่องเริ่มต้นว่าจะให้เริ่มตั้งต้นการเลื่อนที่ช่องไหน
2. จำนวนเลื่อนแนวแถว > ใส่เป็นตัวเลขว่าจะให้เลื่อนขึ้นหรือลงเท่าไหร่ เช่น 1 คือเลื่อนลง 1 ช่อง และ -1 คือเลื่อนขึ้น 1 ช่อง
3. จำนวนเลื่อนแนวคอลัมม์ > ใส่เป็นตัวเลขว่าจะให้เลื่อนซ้ายหรือขวาเท่าไหร่ เช่น 1 คือเลื่อนขวา 1 ช่อง และ -1 คือเลื่อนซ้าย 1 ช่อง
4. [จำนวนแถวเพื่อคืนค่า] > คือคืนค่ามาเป็นจำนวนกี่แถว
5. [จำนวนคอลัมม์เพื่อคืนค่า] > คือคืนค่ามาเป็นจำนวนกี่คอลัมม์
**ซึ่งข้อ 4-5 เป็น [ ] ครอบแปลว่าไม่ต้องใส่ก็ได้ แต่ถ้าใส่ค่านั้นมากกว่า 1 เวลาเราใช่สูตรก็ต้องใส่เป็นแบบ Array ด้วยนะครับ เพราะจะคืนค่ามากกว่า 1 ช่องแล้วนั่นเองครับ

มาลองดูตัวอย่างเพื่อให้เห็นภาพง่ายขึ้นครับ

ตัวอย่างนี้คือ เราเขียนสูตรที่ช่อง B8 ด้วย =OFFSET(B2,3,0) เปรียบเหมือนเราได้เลือกที่ช่อง B2 ก่อน แล้วเลื่อนลง 3 ช่อง แต่ไม่เลื่อนซ้ายหรือขวาเพราะใส่ค่าเป็น 0 ของแกนคอลัมม์ ก็จะได้ค่าออกจากช่อง B5 คือ เครื่องดื่ม นั่นเอง

มาดูอีกตัวอย่าง โดยจะใช้ OFFSET แบบเต็มๆคือใส่ทั้ง 5 ค่าลงไปเลยครับ

สูตร >> {=OFFSET(B2,MATCH(B8,B3:B6,0),1,1,12)}
ตัวอย่างนี้ใช้สูตร Match มาช่วยหาจำนวนการเลื่อนลงด้วยครับ อธิบายสูตรได้ดังนี้ครับ
1. ช่องตั้งต้น = B2
2. เลื่อนลงด้วยคำสั่ง MATCH(B8,B3:B6,0) โดย B8 เป็นคีย์ค้นหาคือ “ของใช้” และ B3:B6 เป็นช่วงไว้ค้นหา ก็จะได้ผลลัพธ์ = 2 แปลว่าเลื่อนลงจาก B2 จำนวน 2 ช่องจาก B2 ได้เป็น B4
3. จำนวนเลื่อนแนวคอลัมม์ใส่ค่า = 1 แปลว่าเลื่อนไปทางขวา 1 ช่อง จาก B4 >> ไปเป็น C4
4. จำนวนแถวคืนค่า = 1 คือคืนค่าทั้งหมด 1 แถว
5. จำนวนคอลัมม์คืนค่า = 12 คืนค่าแนวคอลัมม์ทั้งหมด 12 แถว
ดังนั้นค่าที่คืนจะเป็นค่าเดือน 1-12 ของประเภทสินค้า ของใช้ และค่าที่คืนมาจะมี 12 ค่า ฉะนั้นก่อนใส่สูตร เราต้องไฮไลท์ทั้ง 12 ช่องที่เราต้องการแสดงผลลัพธ์ก่อน แล้วจึงใส่สูตร + กด CTRL+SHIFT+ENTER ก็ได้ผลลัพธ์ตามภาพด้านบนเลยครับ

และเรายังสามารถใส่สูตรอื่นๆต่อจากสูตรนี้ได้อีก เช่นสูตร SUM ครับ

พอเราเอาสูตร SUM มาครอบจะได้เป็น =SUM(OFFSET(B2,MATCH(B8,B3:B6,0),1,1,12)) ซึ่งเป็นการรวมยอดเดือน 1-12 ของประเภทสินค้าของใช้นั่นเองครับ

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

 

No Comments

Leave a Comment