ในยุคปัจจุบันต้องทำงานกับข้อมูลจำนวนมหาศาล มี Application ที่เกี่ยวข้องจำนวนมาก หลาย ๆ คนใช้ไฟล์ที่ได้จากการ Export มาจากระบบอื่นเช่น SAP มาใช้งานต่อ หรือใช้ไฟล์ต่อกันเป็นทอด ๆ นอกจากนี้ยังมีการจัดรูปแบบเช่นใส่สี ตีเส้น ชนิดตัวอักษร ขนาดตัวอักษร ฯลฯ ที่หลากหลาย เพิ่มรูปภาพ รูปวาด เพื่อแบ่งหมวดหมู่ เน้นความสำคัญ หรือเพื่อเป้าหมายอื่น ๆ จำนวนมาก ในงานที่ต้องทำหลายชีตหรือมีการทำงานเป็นงวด ๆ มักจะมีการคัดลอกชีตก่อนหน้ามาทั้งชีตแล้วเปลี่ยนเฉพาะข้อมูลให้เป็นของเดือนปัจจุบัน
การทำงานลักษณะที่กล่าวมาจะทำให้มีขนาดไฟล์ที่ใหญ่ขึ้นอย่างต่อเนื่องที่จะเป็นอุปสรรคในการทำงานในอนาคตทั้งในเรื่องการคำนวณที่ต้องใช้เวลานานขึ้นและขนาดไฟล์ที่ใหญ่ขึ้นจนบางไฟล์ใช้เวลาเปิดนานหรือบางไฟล์ไม่สามารถเปิดได้
เพื่อเป็นแนวทางป้องกันและแก้ไขปัญหา จึงได้สรุปปัญหาหลัก ๆ ที่ทำให้ไฟล์ใหญ่และคำนวณช้าไว้ตามด้านล่าง
- นำไฟล์ที่ได้จากการ Export มาจากระบบอื่นเช่น SAP หรือคัดลอกทั้งชีตของไฟล์ที่ได้จาก ระบบอื่นเช่น SAP มาใช้งานต่อ
- ไฟล์ใหญ่
Yes - คำนวณข้า
Yes - ปัญหา
มี Range Name ที่เกินความจำเป็นติดมาด้วยจำนวนมาก มีการอ้างอิงที่เกินความจำเป็น ทำให้สิ้นเปลือง Memory - การแก้ไข
เข้าเมนู Formulas > Name Manager > ลบ Range Name ที่ไม่ใช้งานทิ้งไป
- ไฟล์ใหญ่
- มีพื้นที่สำรองการใช้งานไว้มากเกินไป
- ไฟล์ใหญ่
Yes - คำนวณช้า
มีผลกระทบต่อการคำนวณบ้าง ไม่ถือเป็นสาระสำคัญ - ปัญหา
เคยใช้งานในพื้นที่จำนวนมากจะเกิดการสำรอง Memory ไว้จำนวนมาก ทำให้สิ้นเปลือง Memory - การแก้ไข
กดแป้น Ctrl+End แล้วสังเกตว่า Cursor หยุดอยู่ ณ ตำแหน่งใด หากว่าด้านบนและด้านซ้ายของตำแหน่งดังกล่าวไม่มีข้อมูลให้ลบบรรทัดว่างและคอลัมน์ว่างก่อนตำแหน่งนั้นทิ้งทั้งหมด วิธีการการลบ ให้คลิกหัวคอลัมน์หรือหัวบรรทัดแล้วคลิกขวา > เลือก Delete > กดแป้น Ctrl+Home เพื่อให้ Cursor กลับไปยังเซลล์แรกของชีต > Save
- ไฟล์ใหญ่
- สร้างรูปภาพ รูปวาด จำนวนมาก
- ไฟล์ใหญ่
Yes - คำนวณช้า
Yes - ปัญหา
การสร้างรูปภาพ รูปวาด กล่องข้อความจะใช้พื้นที่เก็บขนาดใหญ่ ทั้งการแสดงผลต้องสิ้นเปลืองการประมวลผล หากเกิดจากการคัดลอกทั้งชีตมาวางเป็นไปได้ว่าได้คัดลอกกล่องข้อความที่มองไม่เห็นติดมาด้วย หากมีการคัดลอกมาวางซ้ำ ๆ จะเกิด Object ที่มองไม่เห็นจำนวนมาก - การแก้ไข
กดแป้น F5 > Special > Objects > OK > กดแป้น Delete เพื่อลบ Object ทิ้งทั้งหมดพร้อมกันในคราวเดียว จากนั้นสร้าง Object ใหม่ตามที่จำเป็น
- ไฟล์ใหญ่
- ใช้สูตร Vlookup แบบตรงตัว (ส่วนประกอบสุดท้ายเป็น False หรือ 0) และอ้างอิงช่วงข้อมูลเพื่อใช้ในสูตรโดยเลือกทั้งคอลัมน์
- ไฟล์ใหญ่
Yes - คำนวณช้า
Yes - ปัญหา
สูตร Vlookup ที่ใช้แบบตรงตัว หากใช้กับพื้นที่ขนาดใหญ่จะทำงานช้ามากเป็นลักษณะปกติของ Vlookup
การแก้ไข - ปรับปรุงสูตรโดยกำหนดช่วงเซลล์ให้มีขนาดเท่าพื้นที่ที่จะใช้งานจริงหรือเผื่อไว้เพียงเท่าที่คิดว่าข้อมูลจะขยายไปถึง
- ไฟล์ใหญ่
- มี Range Name เกินความจำเป็นจำนวนมาก
- ไฟล์ใหญ่
Yes - คำนวณช้า
Yes - ปัญหา
เกิดจากการใช้ไฟล์ต่อกันมาเป็นทอด ๆ แต่ละทอดอาจจะมีการ Link มาจากไฟล์อื่น ๆ ทำให้โปรแกรมสร้าง Range Name สำหรับการเชื่อมโยงถึงกันจำนวนมาก ทุกครั้งที่เปิดไฟล์โปรแกรมจะขึ้นกล่องโต้ตอบมาสอบถามเสมอว่าต้องการ Update หรือไม่ - การแก้ไข
เข้าเมนู Formulas > Name Manager > ลบ Range Name ที่ไม่ใช้งานทิ้งไป
- ไฟล์ใหญ่
- มีรูปแบบหลากหลายและมีรูปแบบจำนวนมาก
- ไฟล์ใหญ่
Yes - คำนวณช้า
มีโอกาสคำนวณช้า - ปัญหา
รูปแบบหรือ Style เช่นใส่สี ตีเส้น ตัวใหญ่ ตัวเล็ก ตัวห้อย ตัวยก ตัวหนา รูปแบบตัวอักษร การกำหนดรูปแบบใช้เอง ฯลฯ ที่มีจำนวนมากโปรแกรมจะต้องใช้ Memory จำนวนมากขึ้นไปด้วยเป็นเงาตามตัว รูปแบบที่หลากหลายอาจจะมีขึ้นโดยไม่ได้ตั้งใจเช่นมีการใช้ไฟล์นั้นสืบทอดกันมาจากผู้อื่น - การแก้ไข
สังเกตรูปแบบได้จากเมนู Home > Styles หากเห็นว่ามีจำนวนมากผิดปกติจะต้องเขียนโปรแกรมด้วย VBA เข้ามาจัดการ อาจจะต้องพึ่งพาหน่วยงาน IT ขององค์กร
- ไฟล์ใหญ่
- ใช้ Volatile Function จำนวนมาก
- ไฟล์ใหญ่
ขึ้นอยู่กับปริมาณสูตร - คำนวณช้า
Yes - ปัญหา
Volatile ฟังก์ชั่น เช่น Rand(), Indirect(), Now(), Today(), Offset(), Cell(), Info() เป็นฟังก์ชั่นที่ถูกกระทบให้เปลี่ยนแปลงได้โดยง่าย จะเกิดการคำนวณทุกครั้งที่เกิดการเปลี่ยนแปลงในเซลล์ใด ๆ หรือแม้แต่ปรับความกว้างเซลล์ ปรับความกว้างคอลัมน์ก็จะคำนวณทันที - การแก้ไข
ใช้ Volatile Function เท่าที่จำเป็น ไม่ควรใช้กับพื้นที่ขนาดใหญ่
- ไฟล์ใหญ่
กรณีมีปัญหาการใช้งาน Microsoft Excel and VBA สามารถสอบถามได้ที่ Excel Forum ครับ