หนึ่งในปัญหาหนักอกของผู้ใช้ Excel คือ เมื่อข้อมูลที่บันทึกไว้หรือ copy มาใช้ต่อมีจำนวนรายการเพิ่มขึ้น จะสร้างสูตรอย่างไรดีหรือหาทางแก้ไขที่ตารางข้อมูลแทน เพื่อทำให้สูตรที่สร้างไว้สามารถคำนวณต่อไปได้โดยไม่ต้องแก้ไขสูตรใหม่อีกเลย
สูตร VLookup, Match, Index หรือสูตรใดก็ตามซึ่งใช้หาค่าคำตอบที่ต้องการมาจากตารางฐานข้อมูลจะไร้ค่าทันทีหากข้อมูลที่ต้องการหาถูกบันทึกเพิ่มต่อท้ายรายการเดิม ทำให้ไม่อยู่ใน range ที่กำหนดไว้ในสูตร เช่น =VLookup(Code,DataRange,3,0) ซึ่งใช้หารายละเอียดของรหัส Code ที่ต้องการจากตารางที่ตั้งชื่อว่า DataRange ย่อมจำกัดขอบเขตของการค้นหารหัสจากพื้นที่ในขอบเขต DataRange ที่กำหนดไว้เท่านั้น หรือ =CountA(DataRange) ก็ย่อมนับจำนวนเซลล์ที่มีข้อมูลในขอบเขตตารางที่ตั้งชื่อไว้ว่า DataRange ซึ่งโดยทั่วไปหากต้องการทำให้สูตรหาค่าจากตารางที่มีขนาดขยายตามปริมาณข้อมูลที่เพิ่มขึ้นสามารถใช้วิธีต่อไปนี้
- วิธีที่แย่ที่สุดแต่มักชอบใช้กันมากที่สุด ใช้การกำหนดขอบเขตตารางไว้ในสูตรแบบครอบคลุมพื้นที่ทุก column หรือทุก row เช่น =SUM(A:A) หรือ =SUM(5:5) หรือ =VLookup(C2,F:K,3,0) ทำให้ไม่ต้องปรับขนาดตำแหน่งตารางที่อ้างอิงไว้ในสูตรอีกต่อไป ซึ่งวิธีนี้จะส่งผลทำให้ Excel คำนวณช้าลงอย่างมาก หรือแฟ้มมีขนาดใหญ่มากตามไปด้วย
- สร้างสูตรที่กำหนดขอบเขตตารางไว้เกินกว่าจำนวนรายการที่มีในปัจจุบันไว้บ้างเผื่อเป็นเซลล์ว่างสำหรับรอบันทึกรายการเพิ่มเติมในอนาคต เช่น =SUM(A5:A5000) โดยอาจเทสีพื้นลงไปใน row 5000 เพื่อบอกตำแหน่งขอบเขตตารางด้านล่างเอาไว้ วิธีนี้ผู้ใช้งานต้องลำบากในการติดตาม insert row แทรกเพิ่มเหนือ row ที่เทสีลงไปเพื่อจะได้มีพื้นที่ตารางเพียงพอสำหรับรายการที่มีเพิ่มขึ้น หรือถ้า insert row สำรองไว้มากเกินไปก็ย่อมทำให้ Excel คำนวณช้าลงโดยไม่จำเป็น
- ใช้สูตรหาคำตอบจากตำแหน่งอ้างอิงที่ใช้สูตร Offset หรือ Indirect เพื่อกำหนดตำแหน่งตารางแบบ Dynamic เช่น =Offset(A1,0,0,CountA(A:A),5) หรือ Indirect(“A1:E”&CountA(A:A)) จะได้ตำแหน่งตารางเริ่มจากเซลล์ A1 ถึงเซลล์สุดท้ายที่บันทึกข้อมูลไว้ใน column E ซึ่งจะเห็นได้ว่ายังต้องพึ่งพาสูตร CountA นับค่าใน column A ทุก row อยู่อีก อีกทั้งสูตร Offset และ Indirect เป็นสูตรประเภท volatile ซึ่งจะคำนวณใหม่เองตามการเปลี่ยนแปลงค่าในเซลล์ใดๆเสมอแม้ในเซลล์ที่อ้างอิงไว้ไม่มีการเปลี่ยนแปลงก็ตาม ส่งผลทำให้ Excel คำนวณช้าลงและยังเป็นสูตรซึ่งหากลิงค์ข้ามแฟ้มจะต้องเปิดแฟ้มต้นทางพร้อมกับแฟ้มปลายทางเสมอจึงจะคำนวณหาค่าข้ามแฟ้มมาให้
- แทนที่จะใช้สูตรกำหนดตำแหน่งเซลล์อ้างอิงลงไปโดยตรง อาจตั้งชื่อให้กับพื้นที่ตารางที่ใช้ตำแหน่งอ้างอิงหรือตั้งชื่อให้กับสูตร Offset หรือ Indirect ไว้ก่อนจากนั้นจึงนำชื่อ Range Name มาซ้อนลงไปในสูตร Vlookup, Match, หรือ Index ซึ่งการตั้งชื่อ Range Name จะช่วยทำให้ Excel คำนวณเร็วขึ้นบ้างแต่ก็ยังช้าอยู่เพราะสาเหตุที่ Offset หรือ Indirect เป็น volatile นั่นเอง
การเลือกใช้วิธีใดนั้นขึ้นกับเงื่อนไขหลายอย่าง เช่น เป็นแฟ้มที่สร้างขึ้นเพื่อใช้เองคนเดียวหรือไม่ หากมีเพื่อนร่วมใช้ด้วยเขามีพื้นฐาน Excel มากน้อยเพียงใด มีการย้อนไปแก้ไขข้อมูลหรือปรับเปลี่ยนโครงสร้างตารางบ่อยไหม เครื่องคอมพิวเตอร์มีความเร็วมากน้อยขนาดไหน สูตรที่จำเป็นต้องอ้างอิงแบบ dynamic range มีมากน้อยกี่เซลล์ ในอนาคตอีกนานไหมที่ขนาดตารางข้อมูลจะเพิ่มขึ้นจนทำให้สูตรทำงานช้าลงจนรอไม่ไหวหรือไม่ หรือถ้าแบ่งแฟ้มข้อมูลเป็นแฟ้มย่อยๆได้แล้วใช้คำสั่ง Change Sources ช่วยอาจไม่จำเป็นต้องใช้สูตรแบบ dynamic range เลยก็ได้
อย่างไรก็ตามไม่ว่าจะใช้วิธีข้างต้นวิธีใดจะสามารถทำให้สูตรลิงค์ทำงานได้ตามเมื่อมีจำนวนรายการเพิ่มขึ้น แต่ถ้าในตารางต้นทางมีสูตรคำนวณปนอยู่ในแต่ละรายการ ผู้ใช้ Excel ยังต้องรับภาระในการ copy สูตรจากเซลล์บรรทัดบนลงไปในบรรทัดที่ insert เพิ่มอยู่ดี
ตั้งแต่ Excel 2003 มีคำสั่ง Data > List และต่อมาได้ปรับปรุงใน Excel 2007 เป็นต้นมาเปลี่ยนไปเป็นคำสั่ง Insert > Table (ต่างจากคำสั่ง Data > What-if Analysis > Data Table) โดยตารางที่กำหนดให้เป็น Table นี้จะส่งผลต่อเนื่องไปยัง Pivot Table หรือสูตรใดๆที่อ้างอิงกับพื้นที่ในตารางให้เพิ่มลดตำแหน่งอ้างอิงตามขนาดของข้อมูลที่บันทึกเพิ่มให้เองโดยอัตโนมัติ อีกทั้งหากมีรายการที่เป็นสูตรคำนวณก็จะ copy สูตรต่อลงไปยังรายการที่บันทึกเพิ่มให้เองทันที แต่มีข้อแม้ที่สำคัญว่าตารางที่จะนำมาใช้แบบ Table นี้ต้องเป็นตารางที่มีโครงสร้างแบบฐานข้อมูลที่ดีก่อนเท่านั้น ต่างจากวิธีข้างต้นซึ่งสามารถนำมาใช้กับตารางที่มีโครงสร้างแบบใดก็ได้
