เชิญทุกท่านที่สนใจสมัครเข้าอบรมในวันอาทิตย์ครับ ... คลิกที่นี่เพื่อดูรายละเอียดและลงทะเบียน

Arrow up
Arrow down
Print
Parent Category: Training Manuals
Category: Dynamic Range Manual

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

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

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

image019

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

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

image023

Author: สมเกียรติ ฟุ้งเกียรติEmail: This email address is being protected from spambots. You need JavaScript enabled to view it.