วิธีจัดการกับความอ้วนอุ้ยอ้าย...ของแฟ้ม

แฟ้ม Excel ที่เราเพิ่งเปิดใช้งานขึ้นใหม่ในวันนี้ จะเริ่มมีขนาดแฟ้มใหญ่ขึ้นเรื่อยๆ แล้วเมื่อวันเวลาผ่านไปสักปีสองปี คอมพิวเตอร์ที่ใช้อยู่ก็จะทำงานช้าลงไปจนผิดสังเกตโดยมีเหตุมาจากแฟ้มใหญ่ขึ้นมากนี่แหละ ตัวผู้ใช้ Excel มักชอบวิ่งไปบอกหัวหน้า ขอให้ซื้อคอมพิวเตอร์เครื่องใหม่เพื่อจะได้ทำให้ Excel ทำงานเร็วเหมือนเดิม ซึ่งถ้าผมเป็นหัวหน้าของคุณล่ะก้อ ... แทนที่จะเปลี่ยนคอมพิวเตอร์เครื่องใหม่ ขอเปลี่ยนตัวพนักงานเป็นคนอื่นจะดีกว่า !!! เพราะแม้จะได้ใช้คอมพิวเตอร์เครื่องใหม่แล้วก็ตาม ต่อมาพอแฟ้มเริ่มใหญ่ขึ้นอีก คอมพิวเตอร์ก็จะช้าลงไปอีก แล้วเราก็ต้องซื้อเครื่องใหม่ที่เร็วขึ้นกว่าอีกหรืออย่างไรกัน

ก่อนที่จะสายเกินไป ผู้ใช้ Excel ควรเข้าใจก่อนว่า ที่ว่าแฟ้ม Excel ใหญ่ขึ้นนั้น อะไรบ้างที่เป็นเหตุให้แฟ้มมีขนาดใหญ่ขึ้น แล้วอย่านึกต่อไปว่า แฟ้มใหญ่ต้องใช้เวลาคำนวณนานกว่าแฟ้มที่เล็กกว่าเสมอไป ซึ่งเมื่อเข้าใจประเด็นเหล่านี้แล้วเราต้องหาทางออกแบบแฟ้มให้ใหญ่พอดีๆ ไม่ใหญ่ ไม่เล็กเกินไป และไม่ใช้เวลาคำนวณช้าจนรอไม่ไหว

สิ่งที่มีผลต่อขนาดของแฟ้ม

1. จำนวนเซลล์ที่ใช้
2. จำนวนเซลล์ที่ถูกอ้าง
3. รูปแบบหรือ Format ที่ใช้

จำนวนเซลล์ที่ใช้

แฟ้ม Excel ในยุคแรก มีขนาดแฟ้มใหญ่มากเพราะกำหนดว่า พื้นที่ตารางทุกเซลล์ตั้งแต่เซลล์ A1 ไปจนถึงเซลล์หัวมุมขวาล่างสุดที่มีข้อมูลอยู่ถือเป็นพื้นที่ซึ่งถูกใช้งาน ต่อมา Excel ได้พัฒนาให้ขนาดของแฟ้มไม่ต้องนับจากเซลล์ A1 แต่ให้นับเฉพาะเซลล์ที่ถูกใช้งานเท่านั้น ส่วนเซลล์ที่เราไม่ได้เข้าไปแตะต้องแก้ไขใส่อะไรลงไป ไม่มีส่วนกระทบต่อขนาดของแฟ้มแต่อย่างใด ซึ่งช่วยให้เราสามารถใช้พื้นที่ตารางได้สะดวก ไม่ต้องออกแบบตารางให้ชิดติดกันเป็นพืด เพื่อหาทางลดขนาดแฟ้มอย่างแต่ก่อน

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

1. ถ้าเป็นตัวเลข แฟ้มจะเล็กที่สุด ไม่ว่าจะเป็นเลข 1 หรือเลข 123 แฟ้มยังคงมีขนาดเท่ากัน
2. ถ้าเป็นตัวอักษร แฟ้มจะใหญ่ขึ้น ไม่ว่าจะเป็นตัวอักษร A หรือ ABC แฟ้มยังคงมีขนาดเท่ากัน
3. ถ้าเป็นสูตรและคืนค่าเป็นตัวเลข แฟ้มจะใหญ่ขึ้น
4. ถ้าเป็นสูตรและคืนค่าเป็นตัวอักษร แฟ้มจะใหญ่มากที่สุด

 จำนวนเซลล์ที่ถูกอ้าง

ในกรณีที่สร้างสูตร link ข้อมูลข้ามแฟ้ม จะพบว่าแฟ้มปลายทางมีขนาดใหญ่ขึ้นมาก หรือบางครั้งจะมีขนาดใหญ่กว่าแฟ้มต้นทางเสียอีก ทั้งๆที่ในแฟ้มปลายทางมีเซลล์ที่ใช้งานเพียงเซลล์เดียวก็ตาม เนื่องจากในสูตร link ข้ามแฟ้มที่สร้างไว้ในเซลล์ได้นำข้อมูลจากแฟ้มต้นทางมาเก็บไว้ในแฟ้มปลายทาง เรียกว่า Cache Data

ยกตัวอย่างเช่น สูตร =VLOOKUP(A1,[Input.xls]Sheet1!$B$2:$H$200,2) ที่หาค่าจากแฟ้ม Input.xls ในชีท Sheet1 ตั้งแต่เซลล์ B2:H200 นี้ จะทำให้ Excel แอบสร้างตารางที่เรามองไม่เห็นเก็บข้อมูลจากแฟ้มต้นทางทั้งหมดไว้ในแฟ้มปลายทางที่มีสูตรนี้อยู่ ซึ่งตารางที่มองไม่เห็นนี้แหละเป็น Cache Data

Cache Data ช่วยทำให้เมื่อเปิดแฟ้มปลายทางขึ้นมาใช้เพียงแฟ้มเดียว เราก็สามารถใช้สูตร VLookup ค้นหาข้อมูลจากแฟ้มต้นทางได้โดยไม่จำเป็นต้องเปิดแฟ้มต้นทางขึ้นมาใช้พร้อมกับแฟ้มปลายทางแต่อย่างใด แต่ก็ทำให้แฟ้มปลายทางมีขนาดใหญ่ขึ้น

รูปแบบหรือ Format ที่ใช้

การกำหนดรูปแบบให้กับชีททั้งชีทพร้อมกันทีเดียวทุกเซลล์ จะทำให้แฟ้มมีขนาดเล็กกว่าการกำหนดรูปแบบเพียงบางส่วนของตาราง เพราะ Excel จะใช้วิธีรับรู้ทีเดียวว่าชีทนั้นๆใช้รูปแบบใด แต่ถ้าในตารางมีการกำหนดรูปแบบต่างกันไปในแต่ละส่วน จะทำให้ Excel ต้องรับรู้ละเอียดมากขึ้นว่า พื้นที่ตารางส่วนใดใช้รูปแบบใด จึงทำให้แฟ้มมีขนาดใหญ่ขึ้น

ตามปกติ Excel ได้กำหนดไว้ในคำสั่ง Excel Options ให้กาช่อง Extend data range formats and formulas ไว้แล้วตั้งแต่ต้น เพื่อช่วยให้เซลล์ข้อมูลตัวเลขที่ใช้งานติดต่อกันไว้ตามแนวตั้งตั้งแต่ 3 เซลล์ขึ้นไป ส่งรูปแบบที่กำหนดไว้ใน 3 เซลล์นั้นต่อไปยังเซลล์ที่ 4 และเซลล์ต่อๆไปให้ทันที แล้วหากมีเซลล์ที่สร้างสูตรไว้ติดต่อกันตามแนวตั้ง 4 เซลล์อยู่แล้ว คำสั่งนี้จะช่วย copy สูตรและรูปแบบจากเซลล์ที่ 4 ไปยังเซลล์ที่ 5 ต่อไปเรื่อยๆให้เช่นกัน (ขอให้ท่องไว้ว่า ครบ 3 ได้ 4 ครบ 5 ได้สูตร หมายความว่า เดิมครบ 3 เซลล์ จะได้รูปแบบต่อในเซลล์ที่ 4 แล้วพอเริ่มครบ 5 เซลล์ จะได้ทั้งสูตรและรูปแบบตามต่อไปให้เอง) อย่างไรก็ตามคำสั่งนี้จะทำงานต่อเมื่อข้อมูลตามแนวตั้งที่บันทึกต่อกันไปเป็นตัวเลขทุกเซลล์ เช่น ถ้าบันทึกตัวเลขเอาไว้ ก็ต้องบันทึกตัวเลขในเซลล์ต่อไปเท่านั้น หากเมื่อใดที่บันทึกค่าเป็นตัวอักษรลงไปในเซลล์ที่ควรเป็นตัวเลข ต้องลบค่านั้นทิ้งไปก่อน จึงจะกระตุ้นให้คำสั่งนี้ทำงานดังเดิม

สิ่งที่ผลต่อความเร็ว

ในเรื่องความเร็วนี้ไม่ได้เกี่ยวข้องกับความเร็วในการคำนวณของโปรแกรม Excel เท่านั้น แต่ยังส่งผลกระทบต่อความเร็วของมนุษย์ในการใช้ Excel อีกด้วย นับตั้งแต่จะช้าหรือเร็วในการสร้างงาน สร้างเสร็จแล้วเมื่อนำมาแก้ไขจะต้องเสียเวลาแก้ไขช้าหรือเร็วเพียงใดกว่าจะแก้ไขเสร็จ บางคนใช้เวลาสร้างแฟ้มนิดเดียวแต่พอย้อนกลับมาแก้ไขแฟ้มเดิมกลับต้องเสียเวลาแก้ไขนานขึ้นมาก

มนุษย์นี่แหละต้นเหตุของความช้าอุ้ยอ้าย

แม้ยุคนี้เราใช้เครื่องคอมพิวเตอร์แทนเครื่องคิดเลขกันมากขึ้น แต่แทนที่เครื่องคอมพิวเตอร์จะช่วยให้ทำงานได้เร็วขึ้น หลายๆคนกลับทำงานเสร็จช้าลง จนแทบอยากจะย้อนไปกดปุ่มบนเครื่องคิดเลขที่ตนใช้ได้เร็วกว่า ทั้งนี้เนื่องจากคนเราไม่ชอบการเปลี่ยนแปลง ชอบทำอะไรใช้อะไรที่ตัวคุ้นเคยมาก่อน พอจะใช้คอมพิวเตอร์ก็คิดไม่ออกว่าจะใช้โปรแกรมอะไร ฟังเขามาว่าถ้าเรื่องของตัวเลขต้องใช้โปรแกรม Excel แต่ถ้าเรื่องเก็บข้อมูลต้องใช้ Access แล้วถ้าข้อมูลมีทั้งตัวเลขและตัวอักษรล่ะ จะใช้โปรแกรมอะไรดี บางคนอาจคิดง่ายๆว่า ถ้าข้อมูลมีเลขมากกว่า ก็ให้ใช้ Excel หรือถ้าข้อมูลมีตัวอักษรมากกว่าตัวเลข ก็ให้ใช้ Access แล้วกัน

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

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

1. ออกแบบตารางเก็บข้อมูลไม่เป็น พอเปิดแฟ้มขึ้นดู แล้วงงไปหมดว่าข้อมูลอะไรอยู่ตรงไหน
2. เก็บข้อมูลที่เกี่ยวข้องกับวันที่ แต่วันที่ซึ่งเก็บบันทึกไว้ ไม่สามารถนำมาใช้คำนวณต่อได้
3. เก็บข้อมูลซ้ำซ้อนกัน พอจะแก้ไขเป็นข้อมูลใหม่ต้องเสียเวลาหาตำแหน่งเซลล์ให้พบก่อนว่าอยู่ที่ชีทใดแฟ้มใด
4. แยกข้อมูลที่เก็บไว้ในตารางเดียวกันออกจากกันไม่ได้ ต้องฝืนบันทึกข้อมูลต่อไปเรื่อยๆนับพันนับหมื่นบรรทัด
5. สร้างสูตรยากๆยาวๆแบบฉันเองคนสร้างคนเดียวเท่านั้นที่จะแกะออก
6. เลือกใช้สูตรไม่เป็น ชอบใช้สูตรสั้นๆที่คนชอบ แต่ Excel ไม่ชอบเพราะโปรแกรมจะทำงานช้าลง
7. แยกตารางสูตรที่ไม่จำเป็นต้องคำนวณพร้อมกันออกจากกันไม่ได้ ทำให้ต้องเสียเวลารอการคำนวณสูตรซ้ำโดยไม่จำเป็น
8. ไม่รู้จักแยกค่าตัวแปรที่อาจเปลี่ยนแปลงภายหลังไว้นอกสูตร
9. สร้างชีทที่ใช้คำนวณเรื่องเดียวกันซ้ำกันเต็มไปหมด แต่ละชีทมีตารางหน้าตาเดียวกัน ต่างกันแต่ค่าที่นำมาใช้คำนวณ
10. เลือกใช้ VBA กับงานที่ยังไม่ควรใช้ VBA แม้ VBA จะช่วยให้เสร็จงานเร็วขึ้น แต่ก็ไม่ใช่เร็วขึ้นได้เต็มที่อย่างที่ควร

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

แนวทางการใช้ Excel ที่ดีและถูกต้อง

• เราต้องมุ่งหวังที่จะใช้ Excel ให้ได้ผลงานมากขึ้นและเร็วขึ้นแบบคอมพิวเตอร์ นั่นคือ ต้องสามารถนำแฟ้มที่สร้างไว้แล้วกลับมาใช้ซ้ำแล้วซ้ำอีก โดยไม่จำเป็นต้องแก้ไขสูตรหรือโครงสร้างตารางที่สร้างไว้แล้วอีกเลย
• แยกโครงสร้างตารางเป็น 3 ประเภท คือ ตารางที่ใช้เก็บข้อมูล(ตัวเลขและหรือตัวอักษร) ตารางสำหรับสร้างสูตรคำนวณ และตารางสำหรับแสดงเป็นรายงาน
• พึงระลึกไว้เสมอว่า สักวันหนึ่งข้อมูลในแฟ้มจะถูกเก็บไว้จนทำให้แฟ้มใหญ่และ Excel ทำงานช้าลง ดังนั้นต้องเตรียมโครงสร้างตารางที่พร้อมต่อการโยกย้ายไปเก็บไว้ที่แฟ้มอื่น
• ข้อมูลที่เก็บไว้ในตารางที่ใช้เก็บข้อมูลต้องจัดเก็บไว้ในลักษณะโครงสร้างฐานข้อมูลที่สามารถนำไปใช้กับเมนู Data หรือใช้กับสูตรพวก Lookup ได้ทันที
• ข้อมูลที่เป็นตัวเลข ต้องมีค่าเป็นตัวเลขอย่างแท้จริง ซึ่งมีลักษณะที่ต้องชิดขวาของเซลล์โดยไม่จำเป็นต้องใช้ Format
• ห้ามพิมพ์ข้อมูลใดๆซ้ำโดยไม่จำเป็น แต่ให้ใช้สูตรนำค่าส่งต่อไปใช้เมื่อต้องการใช้ข้อมูลนั้นซ้ำ
• ในชีทหนึ่งๆ พยายามสร้างสูตรเดียวซึ่งสามารถนำไปใช้ได้กับทุกตำแหน่งเซลล์ในชีทนั้นๆ ไม่ว่าจะมีการแก้ไขที่ทำให้ตำแหน่งโครงสร้างตารางเปลี่ยนไปอย่างไรก็ตาม
• สูตรที่สร้างขึ้นต้องใช้หลักการคำนวณที่ง่ายต่อการทำความเข้าใจของตนเองและผู้อื่น และเมื่อทีเงื่อนไขเปลี่ยนแปลงไปจากเดิมก็ไม่จำเป็นต้องแก้ไขโครงสร้างสูตรให้ต่างไปจากเดิมมากนัก
• ต้องใช้สูตรปรับค่าในเซลล์ให้ได้ค่าถูกต้องตามที่ต้องการก่อนแล้วจึงใช้คำสั่ง Format ปรับการแสดงผลของค่านั้นในภายหลัง
• ในโครงสร้างสูตร ห้ามใส่ค่าคงที่ใดๆไว้อย่างเด็ดขาด
• แทนที่จะสร้างสูตรยากๆยาวๆที่มีส่วนการคำนวณที่ซ้ำกันลงไปในตารางนับพันนับหมื่นเซลล์ ซึ่งทำให้แฟ้มใหญ่และคำนวณช้าลง ควรหาทางกระจายโครงสร้างสูตรออกเป็นสูตรสั้นๆหลายๆเซลล์แต่ใช้การคำนวณครั้งเดียว เพื่อทำให้แฟ้มเล็กลงและคำนวณเร็วขึ้น
• อย่าลืมว่า ตารางที่เป็นสูตร จะมีขนาดแฟ้มใหญ่กว่าตารางที่เป็นตัวเลขหรือตัวอักษร ดังนั้นแทนที่จะใช้สูตรคำนวณค่าใดๆที่เป็นค่าคงที่ เช่น เลขลำดับ แนะนำให้ใช้ตารางที่เป็นตัวเลขผลจากการคำนวณแทนไปเลยดีกว่า
• ในกรณีที่ต้องสร้างสูตรที่อ้างอิงกับตารางแนวนอนแนวตั้ง แนะนำให้เลือกตารางตามแนวนอนก่อนตารางแนวตั้งเพื่อสร้างมาตรฐานวิธีสร้างสูตรของตนเอง และนอกจากนั้นยังสอดคล้องกับโครงสร้างของสูตร Excel เช่น Index หรือ Offset จะใช้ตำแหน่งอ้างอิงตามแนวนอนก่อนแนวตั้งเสมอ
• หากสร้างสูตรที่ต้องนำมาใช้ซ้ำแล้วซ้ำอีก ควรตั้งชื่อตำแหน่งเซลล์ (Range name) ที่สื่อถึงที่ไปที่มาของสูตร แทนการอ้างอิงกับตำแหน่งอ้างอิงโดยตรง
• ควรแยกสูตรที่ไม่จำเป็นต้องคำนวณพร้อมกันออกเป็นแฟ้มแยกจากกัน (หรืออีกนัยหนึ่ง ให้เก็บเฉพาะสูตรที่ต้องคำนวณพร้อมกันเท่านั้นไว้ในแฟ้มเดียวกัน) เพื่อทำให้เราสามารถเลือกเปิดแฟ้มตามขอบเขตการคำนวณที่ชัดเจน และทำให้แฟ้มมีขนาดเล็กลง
• ให้เลือกใช้ VBA เฉพาะเมื่อต้องการใช้ Excel ที่เหนือกว่าปกติจากสูตรและเมนูคำสั่งที่ทำได้ และรหัส VBA ที่ใช้ต้องสามารถใช้ได้ต่อเนื่องไปตลอดแม้โครงสร้างตารางจะเปลี่ยนแปลงต่างไปจากเดิมก็ตาม
• ทุกอย่างมีได้ก็ต้องมีเสีย ได้อย่างก็ต้องเสียอย่าง ขอให้พิจารณาเปรียบเทียบข้อดีข้อเสียก่อนที่จะสร้างงาน เพื่อให้เหมาะสมกับเงื่อนไขและสภาพแวดล้อมของการทำงาน

แนวทางการลดความอ้วน

ลองคิดดูว่า ถ้าเราเก็บรายการข้อมูลลงไปในชีทเต็มทั้งหมดทุก row แล้ว จะนำข้อมูลรายการใหม่ไปเก็บไว้ที่ไหน จะเริ่มต้นเก็บไว้ในชีทใหม่ในแฟ้มเดิมหรือจะเปิดแฟ้มใหม่?

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

แล้วสูตรคำนวณที่สร้างไว้ มันต้องถูกนำมาคำนวณใหม่พร้อมกันทุกครั้งที่มีข้อมูลเปลี่ยนแปลงไปแค่เซลล์เดียวหรือไม่ สูตรที่คำนวณหาต้นทุนสินค้าหรือค่าใช้จ่ายซึ่งมีค่าคงที่ ซึ่งนานๆจึงจะมีค่าเปลี่ยนแปลงไปสักที จำเป็นหรือที่รวมตารางคำนวณค่าคงที่เหล่านั้นไว้ในแฟ้มซึ่งมีสูตรคำนวณยอดกำไรจากการขาย

จากนั้นเมื่อถึงคราวต้องนำข้อมูลนับพันนับหมื่นบรรทัด หรือตารางรายงานที่ต้องพิมพ์นับร้อยนับพันหน้ามาแสดงให้ดูนั้น เราใช้สายตาดูตารางทั้งหมดหรือกระดาษทุกแผ่นที่พิมพ์ออกมาพร้อมกันทุกหน้าหรือไม่?

ผู้สร้างงานต้องพิจารณาถึงประเด็นดังกล่าวข้างต้นไว้เสมอ ต้องพร้อมที่จะแยกข้อมูลที่เก็บไว้ในแฟ้มไปเก็บไว้ในแฟ้มอื่น ข้อมูลใดที่จำเป็นต้องเก็บไว้ใช้ เพื่อหาผลลัพธ์ที่ต้องการจากการคำนวณร่วมกันทุกครั้ง ก็ควรจัดเก็บไว้ในแฟ้มเดียวกับตารางที่เป็นสูตรคำนวณ แต่ถ้าไม่จำเป็นต้องใช้คำนวณพร้อมกันก็ควรแยกออกเป็นแฟ้มต่างหากทำให้แฟ้มมีขนาดเล็กลง แล้วจึงใช้สูตร link ข้อมูลข้ามแฟ้มมาใช้คำนวณต่อตามแต่ว่าเราจะเลือกเอาข้อมูลมาใช้จากแฟ้มไหน ซึ่งจะช่วยให้ใช้ตารางสูตรหน้าเดียวตารางเดียวแต่สามารถคำนวณผลลัพธ์ได้ตามแต่ข้อมูลที่ link มาใช้อีกต่อหนึ่ง

ในกรณีที่ link ข้อมูลข้ามชีท ให้ใช้สูตร Indirect เลือกนำข้อมูลจากชื่อชีทที่ต้องการมาใช้ในชีทคำนวณ ส่วนกรณีที่ link ข้ามแฟ้ม ให้ใช้คำสั่ง Change Source เพื่อเลือกชื่อแฟ้มที่ต้องการมาใช้ในแฟ้มคำนวณ

ผลจากสูตรที่มีต่อขนาดแฟ้มและความเร็วในการคำนวณ

ขอนำผลการทดสอบจากการใช้สูตรคำนวณหลายหลายประเภทมาเล่าสู่กันฟัง และขอให้ลองหาข้อสรุปกันเองว่า สูตรแบบไหนดีกว่ากันและเพราะเหตุใด (โดยการทดสอบนี้ได้จากการสร้างสูตรลงไปในตารางขนาดใหญ่ตั้งแต่เซลล์ D1:D55,000 และใช้ Excel 2003 กับ Pentium D 3.0 GHz)

กรณีใช้สูตร SumIF เพื่อหายอดรวมตามเงื่อนไขเดียว

• สูตร SumIF ใช้เวลา 4 วินาที / ขนาดแฟ้ม 2,984 KB เช่น =SumIF(IDRange,ID,NumRange)
• สูตร SumIFArray ใช้เวลา 38 วินาที / ขนาดแฟ้ม 6,089 KB เช่น {=Sum(IF(IDRange=ID,NumRange))}
• สูตร SumArray ใช้เวลา 47 วินาที / ขนาดแฟ้ม 5,547 KB เช่น {=Sum((IDRange=ID)*NumRange)}
• สูตร SumProduct แบบใส่เครื่องหมาย -- ใช้เวลา 44 วินาที / ขนาดแฟ้ม 2,987 KBเช่น =SumProduct(--(IDRange=ID),NumRange)
• สูตร SumProduct แบบใส่เครื่องหมายคูณ ใช้เวลา 50 วินาที / ขนาดแฟ้ม 2,986 KBเช่น =SumProduct((IDRange=ID)*NumRange)

กรณีใช้สูตร SumIF เพื่อหายอดรวมตามเงื่อนไข 2 เงื่อนไขต่อเนื่องกัน

• สูตร SumIF ใช้ได้เพียงเงื่อนไขเดียวจึงไม่ได้นำมาทดสอบ
• สูตร SumIFArray ใช้เวลา 2 นาที 23 วินาที / ขนาดแฟ้ม 9,716 KB เช่น {=Sum(IF(IDRange=ID,IF(KeyRange=Key,NumRange)))}
• สูตร SumArray ใช้เวลา 2 นาที 39 วินาที / ขนาดแฟ้ม 8,633 KB เช่น {=Sum((IDRange=ID)*(KeyRange=Key)*NumRange)}
• สูตร SumProduct แบบใส่เครื่องหมาย -- ใช้เวลา 2 นาที 30 วินาที / ขนาดแฟ้ม 5,168 KB เช่น =SumProduct(--(IDRange=ID),--(KeyRange=Key),NumRange)
• สูตร SumProduct แบบใส่เครื่องหมายคูณ ใช้เวลา 2 นาที 33 วินาที / ขนาดแฟ้ม 5,165 KB เช่น =SumProduct((IDRange=ID)*(KeyRange=Key),NumRange)
• สูตร SumProduct แบบใส่เครื่องหมายคูณทุกตัว ใช้เวลา 2 นาที 42 วินาที / ขนาดแฟ้ม 5,166 KB เช่น =SumProduct((IDRange=ID)*(KeyRange=Key)*NumRange)
• สูตร SumProduct แบบใส่เครื่องหมายคูณ 1 ใช้เวลา 2 นาที 50 วินาที / ขนาดแฟ้ม 5,171 KB เช่น =SumProduct((IDRange=ID)*1,(KeyRange=Key)*1,NumRange)

กรณีใช้สูตร VLookup VS Match

• VLookup แบบ Approaching Match ใช้เวลาไม่ถึง 1 วินาที / ขนาดแฟ้ม 2,979 KB แต่ถ้าหาค่าไม่พบ (Not Available) ใช้เวลา 3 วินาที เช่น =VLookup(ID,DataRange,2)
• Match แบบ Approaching Match ใช้เวลาไม่ถึง 1 วินาที / ขนาดแฟ้ม 2,976 KB แต่ถ้าหาค่าไม่พบ (Not Available) จะแสดงตำแหน่งสุดท้าย ใช้เวลาไม่ถึง 1 วินาที เช่น =Match(ID,IDRange)
• VLookup แบบ Exact Match ขนาดแฟ้ม 2,981 KB โดยจะใช้เวลานานขึ้นหากตำแหน่งค่าที่ใช้หาห่างจากบรรทัดแรกมากขึ้น แต่ถ้าหาค่าไม่พบ (Not Available) ใช้เวลา 7 วินาที เช่น =VLookup(ID,DataRange,2,0)
• Match แบบ Exact Match ขนาดแฟ้ม 2,979 KB โดยจะใช้เวลานานขึ้นหากตำแหน่งค่าที่ใช้หาห่างจากบรรทัดแรกมากขึ้น เช่น =Match(ID,IDRange,0)

นอกจากนั้นยังได้ทดสอบสูตรบวกลบคูณหาร ซึ่ง Excel คำนวณเร็วมากใช้เวลาคำนวณไม่ถึงวินาที แต่พบว่าขนาดแฟ้มจะเล็กหรือใหญ่ขึ้นกับความยาวของสูตรที่ใช้ เช่น =1+2+3+4+5 มีขนาดแฟ้มใหญ่กว่า =A2+5 โดยที่ A2 มีสูตร =1+2+3+4 เพื่อคำนวณส่วนแรกแยกไว้ก่อน