วิธีใช้สูตร Offset กำหนดขอบเขตตารางแบบยืดหยุ่น

image019
June 28, 2018
Category: Online Dynamic Range

สูตร VLookup และสูตร Index มีลักษณะการใช้งานเหมือนกันประการหนึ่ง คือ ใช้กับพื้นที่ตารางซึ่งกำหนดขอบเขตไว้ตายตัวอยู่แล้ว แม้จะนำสูตร IF หรือ Choose เข้ามาช่วยก็เป็นเพียงช่วยทำให้สูตร VLookup และสูตร Index สามารถเลือกพื้นที่ตารางจากต่างพื้นที่กันมาใช้งาน แต่จะไม่รับรู้ขอบเขตใหม่หากภายหลังมีการเพิ่มลดจำนวนรายการ

ส่วนการใช้คำสั่ง Table เหมาะสำหรับตารางฐานข้อมูลซึ่งมีลักษณะถูกต้องและจะช่วยขยายตำแหน่งอ้างอิงตามจำนวนรายการ แต่จะไม่ลดขนาดเว้นแต่ว่าผู้ใช้จะสั่ง delete row

สูตร Offset เป็นสูตรที่ช่วยกำหนดขอบเขตตารางให้สามารถเพิ่มลดขนาดตามจำนวนรายการและสามารถนำไปใช้กับตารางทุกประเภท โดยไม่จำกัดว่าจะต้องเป็นตารางฐานข้อมูลเท่านั้น แต่มีข้อจำกัดว่า หากใช้สูตร Offset ดึงข้อมูลข้ามแฟ้มมาใช้ จะต้องเปิดแฟ้มต้นทางขึ้นมาใช้ร่วมกับแฟ้มปลายทางที่ใช้สูตรนี้ด้วยเสมอ

image019

จากภาพนี้เมื่อต้องการขอบเขตตารางตั้งแต่ B3:D7 ให้ใช้สูตร =OFFSET($B$3,0,0,5,3)

  • $B$3 เป็นตำแหน่งเซลล์อ้างอิงเพื่อกำหนดเซลล์อ้างอิงเริ่มต้นของตาราง
  • เลข 0 ตัวแรกในสูตร เป็นตำแหน่งจำนวน row ถัดไป สาเหตุที่ใช้เลข 0 เพื่อชี้ว่าจุดเริ่มต้นของตารางยังคงอยู่ใน row เดียวกันกับเซลล์อ้างอิงเริ่มต้นของตาราง $B$3
  • เลข 0 ตัวที่สองในสูตร เป็นตำแหน่งจำนวน column ถัดไป สาเหตุที่ใช้เลข 0 เพื่อชี้ว่าจุดเริ่มต้นของตารางยังคงอยู่ใน column เดียวกันกับเซลล์อ้างอิงเริ่มต้นของตาราง $B$3
  • เลข 5 เป็นส่วนของความสูงของตาราง ในที่นี้แสดงว่ามีความสูง 5 row
  • เลข 3 เป็นส่วนของความกว้างของตาราง ในที่นี้แสดงว่ามีความกว้าง 3 column

หากต้องการให้สูตรนี้ปรับขนาดแบบยืดหยุ่น (Dynamic Range) โดยทั่วไปเฉพาะส่วนของความสูงของตารางเท่านั้นที่จะเพิ่มลดตามจำนวนรายการที่บันทึกไว้ เมื่อต้องการทำให้สูตร Offset ปรับขนาดความสูงตาม สามารถหาความสูงได้หลายวิธี ดังนี้

  1. สูตร CountA($B:$B)-1 เพื่อนับจำนวนเซลล์ใน column B ที่มีข้อมูลแล้วลบทิ้งเสีย 1 เพราะไม่ต้องการนับรวมหัวตารางเซลล์ B2 ซึ่งมีคำว่า Id
  2. ใช้สูตร CountA($B$3:$B$10000) เพื่อนับจำนวนเซลล์ที่มีข้อมูลตั้งแต่รายการแรกลงมา อาจเผื่อขนาดไว้ถึง row 10000

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

หากเป็นตารางซึ่งมีช่องว่างแทรกไว้ไม่แน่นอน สามารถคำนวณหาความสูงของตารางได้ โดยใช้สูตร Array {=MAX(IF(B:D<>0,ROW(B:D)))-MIN(IF(B:D<>0,ROW(B:D)))}

  • สูตร Array { =MAX(IF(B:D<>0,ROW(B:D)))} หาเลขที่ row สุดท้ายของตาราง
  • สูตร Array { =MIN(IF(B:D<>0,ROW(B:D)))} หาเลขที่ row แรกของตาราง

หมายเหตุ สูตร Array มีวงเล็บปีกกาปิดหัวท้ายสูตร เกิดจากการกดปุ่ม Ctrl+Shift+Enter พร้อมกันแทนการกดปุ่ม Enter และอาจต้องปรับเพิ่มลดตัวเลขให้เท่ากับความสูงที่แท้จริงหากตารางในช่วง column B:D มีข้อมูลอื่นซึ่งไม่เกี่ยวข้องบันทึกอยู่

เมื่อหาความสูงของตารางด้วยสูตรได้แล้ว จากเดิมที่เป็นสูตร =OFFSET($B$3,0,0,5,3) ให้นำสูตรคำนวณหาความสูงมาซ้อนกันกลายเป็นสูตร
=OFFSET($B$3,0,0, CountA($B:$B)-1, 3)

หรือจะลิงค์เฉพาะผลลัพธ์ของความสูงที่คำนวณได้มาใช้ก็ได้ กลายเป็นสูตร
=OFFSET($B$3,0,0, เซลล์หาความสูง, 3)

สูตร Offset ที่ทำหน้าที่เป็น Dynamic Range นี้ หากสร้างลงไปในเซลล์เดียวจะเกิด error #VALUE! เสมอ เนื่องจากสูตรนี้หาค่าหลายค่ามาให้จึงไม่สามารถแสดงครบทุกค่าในเซลล์ใดเซลล์หนึ่งได้ หากต้องการตรวจสอบว่าสูตรทำงานได้ถูกต้องหรือไม่ ให้กดปุ่ม F2 ตามด้วย F9 จะพบว่าสูตรคืนค่าเป็นค่าทั้งหมดในตารางนั้นออกมาให้เห็นแบบ Array

={“a001″,”a”,10;”a002″,”b”,20;”a003″,”c”,30;”a004″,”d”,40;”a005″,”e”,50}

เมื่อเห็นค่าที่แท้จริงของสูตรแล้ว ให้กดปุ่ม Esc เพื่อแก้กลับคืนเป็นสูตรตามเดิม

อีกวิธีหนึ่งในการตรวจสอบสูตร ให้คลิกลงไปในช่อง Formula Bar ลากเลือกสูตรทั้งหมด =OFFSET($B$3,0,0, CountA($B:$B)-1, 3) แล้วกดปุ่ม F5 แล้วกดปุ่ม Enter เพียงครั้งเดียว จะพบว่า Excel เปลี่ยนสูตร Offset เป็นตำแหน่งอ้างอิง =B3:D7 พร้อมกับแสดงตำแหน่งในตารางให้เห็น จากนั้นให้กดปุ่ม Esc เพื่อแก้กลับคืนเป็นสูตรตามเดิม

เมื่อพบว่าสูตร Offset แสดงค่าหรือชี้ตำแหน่งได้ถูกต้อง ให้ตรวจสอบตำแหน่งอ้างอิงทุกตำแหน่งในสูตรว่าเป็น Absolute Reference เช่น $B$3 และ $B:$B ก่อน จากนั้นจึงนำสูตรนี้ไปตั้งชื่อให้เป็น Formula Name โดยใช้คำสั่ง Formulas > Define Name ต่อมาเมื่อต้องการสร้างสูตร VLookup, Match, หรือ Index ให้นำชื่อ Formula Name ที่ตั้งขึ้นนี้ไปใช้แบบเดียวกับการใช้ชื่อ Range Name โดยทั่วไป

ถ้าต้องการปรับสูตร Offset ให้คืนค่าเป็นพื้นที่ตารางเพียงบางส่วน ให้ปรับส่วนของตำแหน่ง row, column, ความสูง และความกว้างในสูตร เช่น

image021

  • =OFFSET($B$3,2,0) หมายถึง B5
  • =OFFSET($B$3,4,2) หมายถึง D7
  • =OFFSET($B$3,2,0,1,3) หมายถึง B5:D5
  • =OFFSET($B$3,4,0,1,3) หมายถึง B7:D7
  • =OFFSET($B$3,2,0,2,3) หมายถึง B5:D6
  • =OFFSET($B$3,0,0, CountA($B:$B)-1, 1) หมายถึง B3:B7
  • =OFFSET($B$3,0,1, CountA($B:$B)-1, 1) หมายถึง C3:C7
  • =OFFSET($B$3,0,2, CountA($B:$B)-1, 1) หมายถึง D3:D7
  • =OFFSET($B$3,0,0, CountA($B:$B)-1, 2) หมายถึง B3:C7
  • =OFFSET($B$3,0,1, CountA($B:$B)-1, 2) หมายถึง C3:D7
  • =OFFSET($B$3, CountA($B:$B)-2,0,1,3) หมายถึง B7:D7 หรือรายการสุดท้าย

นอกจากนี้ ผู้ใช้สูตร Offset สามารถกำหนดตำแหน่งเซลล์อ้างอิงเซลล์แรกไว้ที่เซลล์ใดก็ได้ (ไม่จำเป็นต้องใช้เซลล์ B3 ซึ่งเป็นเซลล์แรกของรายการแรก) และยังสามารถใช้หาค่าในทุกทิศทาง เพียงกำหนดจำนวน row ถัดไปหรือเลข column ถัดไปให้เป็นค่าลบ

image023