จุดอ่อนของสูตร Excel แทบทุกสูตรมีข้อจำกัดอยู่ตรงที่ต้องกำหนดพื้นที่ตารางหรือขอบเขตตำแหน่งเซลล์อ้างอิงไว้เสมอภายในวงเล็บของสูตร เข่น
=Sum(A1:B12) ใช้หายอดรวมของตัวเลขในพื้นที่ตารางจากเซลล์ A1 ถึง B12
=VLookup(C3,G3:K100,2) ใช้ดึงข้อมูลที่เก็บไว้ในพื้นที่ตารางจากเซลล์ G3 ถึง K100
=Index(F2:J30,4,5) ใช้ดึงข้อมูลจาก Row ที่ 4 ตัดกับ Column ที่ 5 ซึ่งเก็บไว้ในพื้นที่ตารางจากเซลล์ F2 ถึง J30
หากหลังจากนั้นจำนวนรายการของข้อมูลมีจำนวนเซลล์เพิ่มขึ้น ย่อมส่งผลกระทบทำให้สูตรเหล่านี้ไม่สามารถหาค่าจากพื้นที่เซลล์ส่วนที่ขยายไปจากเดิม ซึ่งหากไม่ต้องการย้อนกลับไปแก้ไขขอบเขตที่อ้างไว้ในสูตรแต่ละสูตรให้มีขนาดเพิ่มตาม สามารถแก้ไขโดยใช้วิธีอื่นได้อีก เช่น
- ก่อนที่จะบันทึกข้อมูลเกินขอบเขตตารางเดิม ให้สั่ง Insert Row หรือ Column ซึ่งจะทำให้ตำแหน่งอ้างอิงที่กำหนดไว้ในสูตรมีการขยายพื้นที่ตาม แต่วิธีนี้ต้องคอยระวังไว้ว่าต้องสั่ง Insert Row หรือ Column ก่อนที่จะมีข้อมูลเพิ่ม
- เตรียมพื้นที่ขอบเขตตารางให้ใหญ่เกินไว้ก่อน หากกำหนดพื้นที่ตารางไว้ใหญ่เกินไปจะทำให้ Excel คำนวณช้าลงและเสี่ยงต่อผู้ใช้งานที่อาจจะเผลอกรอกค่าที่ไม่เกี่ยวข้องลงไปในพื้นที่ตารางที่เห็นว่าว่างอยู่
ไม่ว่าจะแก้ไขด้วยวิธีใด วิธีปฏิบัติที่ดีควรเริ่มต้นจากการตีเส้นกรอบหรือเทสีพื้นกำหนดขอบเขตตารางซึ่งถูกนำไปใช้อ้างอิงไว้ในวงเล็บของสูตรให้เห็นขอบเขตชัดเจนไว้ก่อนเสมอ หรืออาจใช้วิธีพิมพ์ข้อความว่า Last หรือคำอื่นใดไว้ในเซลล์ด้านล่างสุดหรือขวาสุดไว้เพื่อบอกให้ทราบว่านี่คือ ขอบเขตที่ใช้งานอยู่
ท้าชนสูตร Offset vs VLookup Match Index
สูตรอะไรเอ่ยที่ใช้หาค่าแบบไม่จำกัดขอบเขตและไร้กาลเวลา
- สามารถใช้หาค่าที่ไม่ได้อยู่ในแนวตั้งฉากก็ได้
- สามารถใช้หาค่าในปัจจุบัน อนาคต หรือย้อนหลังไปในอดีตก็ได้
Download คู่มือติดไม้ติดมือ ได้จาก
https://drive.google.com/file/d/1bnEacK0O38WOg_LBDQB4Msi85GkoK_5q/view?usp=sharing
หรือคู่มือรุ่นใหม่พิมพ์แบบสี
https://drive.google.com/file/d/11p8i0L42WnUeWaHuZgTVhqot3AzUnHMg/view?usp=sharing
ดูเฉลยได้จากวิดีโอ
https://www.facebook.com/ExcelExpertTraining/videos/2087088408227633/
