แฟ้ม 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 ค้นหาข้อมูลจากแฟ้มต้นทางได้โดยไม่จำเป็นต้องเปิดแฟ้มต้นทางขึ้นมาใช้พร้อมกับแฟ้มปลายทางแต่อย่างใด แต่ก็ทำให้แฟ้มปลายทางมีขนาดใหญ่ขึ้น

หากทุกครั้งต้องเปิดแฟ้มที่ link กันทั้งหมดขึ้นมาใช้งานพร้อมกันอยู่แล้ว เราสามารถสั่งให้ Excel ตัด Cache Data ในแฟ้มปลายทางทิ้งได้เลย โดยใช้คำสั่ง Tools > Options > Calculation > ตัดกาช่อง Save external link values ทิ้งไป ซึ่งเมื่อตัดกาช่องนี้ออกไปแล้ว จะพบว่าแฟ้มปลายทางมีขนาดเล็กลงอย่างมาก แต่อย่าลืมว่าหากต้องการให้สูตรคำนวณได้ตามปกติ ต้องเปิดแฟ้มทั้งหมดที่ link กันก่อน หรือในระหว่างที่เปิดแฟ้มปลายทางขึ้นมาใช้งาน เราจำเป็นต้องสั่ง Update Links ก่อนเสมอ (ซึ่งวิธีนี้จะช่วยสร้าง Temporary Cache Data เก็บไว้ในหน่วยความจำของคอมพิวเตอร์ ทำให้สูตรในแฟ้มปลายทางใช้ข้อมูลใน Cache นี้ต่อได้อีก)

แทนที่จะเสียเวลาสั่ง Update Links ในช่วงเปิดแฟ้ม เราสามารถสั่งให้เครื่องคอมพิวเตอร์จำไว้เลยว่า ทุกครั้งที่เปิดแฟ้มปลายทางขึ้นมาให้ Excel สั่ง Update Links ให้เองทันที โดยสั่ง Tools > Options > Calculation > Edit > ตัดกาช่อง Ask to update automatic links ทิ้งไป หรือภายหลังจากที่เปิดแฟ้มปลายทางขึ้นมาโดยไม่ได้สั่ง Update Links ตั้งแต่ตอนช่วงเปิดแฟ้ม เราก็สามารถสั่ง Update Links ได้ทุกเมื่อที่ต้องการในแฟ้มปลายทางโดยใช้คำสั่ง Edit > Links > Update Values ซึ่งจะช่วยสร้าง Temporary Cache ให้แฟ้มปลายทางทำงานต่อไปได้ตามปกติ

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

ในแฟ้มหนึ่งๆรับรูปแบบได้สูงสุดเพียง 4,000 แบบ โดยรูปแบบที่ว่านี้เป็นรูปแบบที่เกิดจากเมนูคำสั่ง Format > Cells นั่นเอง (ซึ่งน่าจะรวมถึง Format > Conditional Formatting หรือไม่นั้นยังไม่มีข้อมูลชัดเจน) หากเมื่อใดที่ Excel เตือนขึ้นมาบนจอว่า Too many formats อะไรทำนองนี้ เราต้องรีบลบรูปแบบที่ไม่ได้ใช้งานทิ้งออกไปทันที เพราะอาจไม่สามารถเปิดแฟ้มที่เคยถูกเตือนเช่นนี้ขึ้นมาใช้งานได้อีก

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

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

ในกรณีที่ต้องการกำหนดให้ทุกชีทมีรูปแบบที่ใช้เหมือนกัน ขอแนะนำให้ใช้คำสั่ง Format > Style > Modify เพื่อกำหนดรูปแบบมาตรฐานให้ใช้แบบเหมือนกันทุกชีท ซึ่งในคำสั่งนี้หากเลือก Merge แทน Modify จะช่วยเลือกรูปแบบมาตรฐานจากแฟ้มอื่นมาใช้ต่อในแฟ้มต่อไปทำให้เกิดมาตรฐานในการใช้งานที่ควรใช้ร่วมกันทั้งบริษัท

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

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

สำหรับผู้สนใจการทำให้ Excel คำนวณได้เร็วที่สุด ขอเชิญติดตามไปอ่านบทความที่ผมเขียนไว้ในหัวข้อ Excel @ The Maximum Speed ได้ที่ http://www.excelexperttraining.com/extra/tactic/maxspeed.html ส่วนในบทความนี้จะได้อธิบายเรื่องที่แปลกไปจากเดิม

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

แม้ยุคนี้เราใช้เครื่องคอมพิวเตอร์แทนเครื่องคิดเลขกันมากขึ้น แต่แทนที่เครื่องคอมพิวเตอร์จะช่วยให้ทำงานได้เร็วขึ้น หลายๆคนกลับทำงานเสร็จช้าลง จนแทบอยากจะย้อนไปกดปุ่มบนเครื่องคิดเลขที่ตนใช้ได้เร็วกว่า ทั้งนี้เนื่องจากคนเราไม่ชอบการเปลี่ยนแปลง ชอบทำอะไรใช้อะไรที่ตัวคุ้นเคยมาก่อน พอจะใช้คอมพิวเตอร์ก็คิดไม่ออกว่าจะใช้โปรแกรมอะไร ฟังเขามาว่าถ้าเรื่องของตัวเลขต้องใช้โปรแกรม 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 ข้ามแฟ้ม ให้ใช้คำสั่ง Edit > Links > 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 เพื่อคำนวณส่วนแรกแยกไว้ก่อน

เมื่อต้องเลือกระหว่างขนาดแฟ้มหรือความเร็วในการคำนวณ

ประเด็นเรื่องความอ้วนอุ้ยอ้ายของแฟ้มตามที่กล่าวมาทั้งหมดในบทความนี้จะไม่เป็นสาระสำคัญหากคิดแต่เพียงว่า ถ้าแฟ้มใหญ่ขึ้น หรือคอมพิวเตอร์ช้าลง เราก็ซื้อคอมพิวเตอร์เครื่องใหม่หรือซื้อ RAM มาเพิ่มหน่วยความจำในเครื่องให้มากขึ้นก็แก้ปัญหาได้แล้ว แต่ถ้าคิดกันง่ายๆเช่นนี้ ปัญหาที่เกิดจากการใช้ Excel จะยิ่งมีความสลับซับซ้อนมากขึ้น โดยเฉพาะอย่างยิ่งเมื่อใช้ Excel 2007 ขึ้นไป โดยในชีทหนึ่งมีกว่า 10,000 column และกว่า 1 ล้าน row ซึ่งย่อมทำให้ผู้ใช้ Excel ที่ขาดความรู้พื้นฐาน และขาดวินัยในการสร้างงาน สร้างแฟ้มงานที่จะย้อนกลับมาทำร้ายตัวเองในที่สุด

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

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