วิธีใช้ฐานข้อมูลแบบ Dynamic Database - ตอนที่ 1 สูตร Offset

ทุกวันนี้ข้อมูลมีจำนวนมากขึ้น ทำให้เวลาที่สูตร Excel ใช้ในการคำนวณจะช้าลงไปเรื่อยๆ แล้วเรามักโทษไปว่าสาเหตุมาจาก Excel แล้วต้องหันไปใช้ Power Query มาช่วย ทั้งๆที่ต้นเหตุที่แท้จริงมาจากวิธีการใช้ฐานข้อมูลต่างหาก ... เราไม่รู้จักใช้ Excel แบบประหยัด

วิธีการยอดนิยมที่พบเสมอ หลายคนใช้การอ้างอิงแบบทั้ง column B:B เพื่อจะได้อ้างอิงพื้นที่เผื่อไว้ก่อน เวลามีรายการเพิ่มขึ้นจะได้ไม่ต้องแก้สูตร หรือหันไปเปลี่ยนตารางฐานข้อมูลให้เป็น Table ไว้ก่อน แม้ตารางที่เป็น Table ปรับการอ้างอิงตามรายการที่เพิ่มขึ้นให้เองก็ตาม แต่อย่างไรก็ตามจะใช้ข้อมูลทั้งหมดตั้งแต่รายการแรกจนถึงรายการสุดท้ายอยู่ดี ทำให้สูตรที่นำพื้นที่จาก Table ไปใช้หรือแม้แต่ PivotTable ก็ตาม ทำงานช้าลงไปเรื่อยๆ

แทนที่จะทำแบบที่ชอบใช้กัน ยังมีทางออกอีกหลายวิธีที่จะลดจำนวนรายการข้อมูลลงให้เหลือเท่าที่ต้องการใช้จริงๆ ซึ่งในตอนแรกนี้ ขอนำสูตร Offset ที่ใช้กันแต่ดั้งแต่เดิมมาเล่าให้ฟังก่อน จากนั้นจะทะยอยนำวิธีการอื่นที่เกิดขึ้นใหม่ใน Excel 365 มาให้เรียนรู้กัน

สูตร Offset เป็นสูตรที่มีความสามารถยืดได้หดได้ ตามโครงสร้างนี้

=Offset ( เซลล์แรกหัวมุมตารางข้อมูล, ตำแหน่ง Row ของรายการแรก, ตำแหน่ง Column ของรายการแรก, ขนาดจำนวน Rows ที่ต้องการ, ขนาดจำนวน Columns ที่ต้องการ)

ตามภาพตัวอย่างนี้ ใช้สูตรเลือกดึงข้อมูลเฉพาะช่วงที่ต้องการด้วยสูตร
=OFFSET( $B$4, เริ่มจากรายการที่, 0, จำนวนรายการที่ต้องการ, จำนวนฟิลด์ทั้งหมด )

$B$4 เป็นเซลล์แรกหัวมุมซ้ายสุดของตารางข้อมูล
เริ่มจากรายการที่ เท่าใดให้กำหนดเลขที่รายการลงไป
0 ใช้ตำแหน่ง Column เดียวกันกับเซลล์ B4
จำนวนรายการที่ต้องการ ตรงนี้แหละคือพระเอกของสูตรนี้ที่ช่วยลดจำนวนรายการลง
จำนวนฟิลด์ทั้งหมด โดยทั่วไปจะกำหนดขนาดคงที่ หาได้จากสูตร Columns

=OFFSET($B$4, 4, 0, 30, 7)
ทำหน้าที่ดึงรายการที่ 4 ตามแนวเดิม ออกมา 30 รายการ จากทั้ง 7 Columns

ถ้าใช้ Excel รุ่นเก่าที่ยังไม่รู้จัก Dynamic Array ต้องเลือกพื้นที่ให้มีขนาดใหญ่ๆเผื่อไว้ก่อนแล้วพอสร้างสูตร Offset ลงไปต้องกดปุ่ม Ctrl+Shift+Enter จะพบว่าสูตรกระจายค่าให้ ถ้าเนื้อที่ใหญ่เกินรายการที่ต้องการจะเห็น Error NA

ถ้าใช้ Excel 365/2021 ขึ้นไป สร้างสูตรนี้แค่เซลล์เดียวจะกระจายค่าออกมาให้เองแบบ Dynamic Array

นอกจากสูตร Offset นี้จะช่วยประหยัดจำนวนรายการที่ทำให้การคำนวณเร็วขึ้นแล้ว เมื่อต้องการนำสูตรไปใช้ในการอ้างอิงกับสูตรต่างๆ ควรนำสูตรไปตั้งเป็นชื่อ เช่น UsedDB (เรียกว่า Formula Name) พอจะนำพื้นที่ไปใช้ให้กดปุ่ม F3 เพื่อนำชื่อสูตรไปใช้ต่อ จะช่วยประหยัดเวลาในการสร้างและทำให้ลดข้อผิดพลาดในการอ้างอิงพื้นที่อีกด้วย

  

Download ตัวอย่างได้จาก https://drive.google.com/file/d/1RYS7pnf8QVchOeTvLDVzYI_lq887Mjc7/view?usp=sharing