ทำงานกับ Dataset ใหญ่ Spreadsheet จะช้าลง การจัดการ Workbook ที่มีหลายพันแถวและสูตรซับซ้อนเป็นเรื่องท้าทาย ต้อง Optimize Spreadsheet เพื่อทำงานกับข้อมูลใหญ่ได้ Optimization ทำให้ Performance ดีขึ้น Load เร็วขึ้น และทำงานมีประสิทธิภาพมากขึ้น
บทความนี้อธิบายวิธี Optimize Spreadsheet เพื่อปรับปรุง Performance ของ Workbook ใหญ่
1. ลบข้อมูลที่ไม่จำเป็น
เก็บ Workbook ให้สะอาด ลบ Rows, Columns, Worksheets ที่ไม่ได้ใช้ เก็บแค่ข้อมูลที่ต้องการ
ลบ Rows และ Columns ว่าง:
Dataset อาจมี Rows/Columns ว่าง ใช้ Go To Special หาและลบได้เร็ว
- เลือกช่วงข้อมูล
- กด Ctrl + G >> Special >> Blanks
- Delete Rows/Columns ที่ไฮไลท์
ลดขนาดไฟล์และเพิ่มความเร็ว Recalculation
2. Optimize Formulas
2.1 แทนที่ Volatile Functions
Volatile Functions คำนวณใหม่ทุกครั้งที่เปิด Workbook หรือมีการเปลี่ยนแปลง แทนที่ด้วย Non-volatile Functions ถ้าเป็นไปได้
Volatile Functions พบบ่อย:
- NOW()
- TODAY()
- RAND()
- OFFSET()
- INDIRECT()
แทน NOW ด้วย Static Values
แทนที่จะใช้ NOW() แสดงวันที่/เวลาปัจจุบัน ใช้ Shortcut แทน NOW() คำนวณใหม่ทุกครั้งที่มีการเปลี่ยนแปลง
- ใช้ Ctrl + ; ใส่วันที่ปัจจุบันเป็น Static Value
2.2 ลดความซับซ้อน Array Formulas
ใช้ Array Formulas ให้มีประสิทธิภาพ แม้ทรงพลังแตชชาช้า Workbook ลดการใช้ Array Formulas ด้วย Helper Columns
แทนที่จะใช้ SUM และ IF แยกกัน ใช้ SUMIF แทน
Array Formula:
=SUM(IF(A1:A1000>10, B1:B1000, 0))
สำหรับ Spreadsheet ใหญ่ SUMIF ดีกว่าชัดเจนด้าน Performance และความเรียบง่าย ลดการใช้ทรัพยากร หลีกเลี่ยงความซับซ้อน เก็บ Workbook ให้สะอาด
=SUMIF(A1:A1000, ">10", B1:B1000)
ถ้า SUMIF ไม่มี ใช้ Helper Column
แทนที่ด้วย Helper Column พร้อม Optimization:
- เพิ่มคอลัมน์ใหม่ ใส่:
- จากนั้นใช้สูตร:
แยก Array Formula เป็นขั้นตอนเล็กๆ ที่ใช้น้อยทรัพยากร
ลดขอบเขต Reference:
- แทนที่จะอ้างอิงคอลัมน์ทั้งหมด >> A:A
- ใช้ Specific Cell Ranges หรือ Defined Names >> A1:A1000
3. ใช้ Conditional Formatting อย่างน้อย
แทนที่จะ Apply Formatting ทั้งคอลัมน์ จำกัดให้ช่วงข้อมูลจริง:
- เลือกแค่ช่วงข้อมูล (เช่น A1:A100)
- Apply Conditional Formatting Rules ที่นี่ หลีกเลี่ยง Overhead ไม่จำเป็น
4. ลด External Links
แทนที่จะ Link ข้อมูลจาก Workbook ภายนอก ใช้ Power Query Import และ Transform ข้อมูลภายใน Workbook:
- แท็บ Data >> Get Data >> From File >> From Workbook
- Load ข้อมูลที่ต้องการเข้ามาในไฟล์ปัจจุบัน
ลด Dependency ไฟล์ภายนอก และเร่งการดึงข้อมูล
5. ปรับ Calculation Mode
Dataset เล็ก ใช้ Automatic Calculation ได้ แต่ Dataset ใหญ่พร้อมสูตรซับซ้อน ควรใช้ Manual Recalculation
สำหรับ Workbook ที่มีสูตรเยอะ:
- ไป File >> Options
- เลือก Formulas >> Calculation Options >> Manual
- OK
- กด F9 คำนวณใหม่เฉพาะตอนต้องการ
ทำงานบนชีทได้โดยไม่มี Delay จาก Auto Recalculation
ปิด Iterative Calculations
ปิด Iterative Calculations ที่อาจทำให้ Recalculate ซ้ำไม่จำเป็น
- ปิด Enable iterative calculation
- OK
6. ใช้ Data Tables และ PivotTables
สร้าง Dynamic Summary 用 Pivot Table แทนการคำนวณ/อัปเดต Summary Tables ด้วยมือ
- เลือกข้อมูล
- แท็บ Insert >> PivotTable
- ลาก Fields ไป Rows/Columns สร้าง Dynamic Summaries
Pivot Table คำนวณรวมอัตโนมัติจาก Field List เร็วกว่าและจัดการ Dataset ใหญ่ได้ดีกว่า
7. Optimize Workbook Design
ใช้ Named Ranges อ้างอิงช่วงเซลล์แบบไดนามิก แทนการอ้างอิงคอลัมน์ทั้งหมดหรือ Array Formula
สมมติทำงานกับ Sales Dataset ที่โตขึ้นเรื่อยๆ แทน Fixed Range A1:A1000 ใช้ Dynamic Named Range:
- แท็บ Formulas >> Name Manager >> New
- ตั้งชื่อ: SalesData
- Refers to: ใส่สูตร:
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)
สูตรนี้จะอัปเดตสูตรหรือ Charts ที่มีอยู่อัตโนมัติเมื่อข้อมูลเพิ่มหรือลด
8. บีบอัดขนาดไฟล์
Optimize ไฟล์ ลดขนาดรูปภาพและไฟล์ภายนอก
ถ้า Workbook มีรูปภาพ:
- เลือกรูปภาพ
- แท็บ Picture Format >> Compress Pictures
- เลือก Resolution ต่ำกว่า (เช่น Web หรือ Email) ลดขนาดไฟล์โดยไม่สูญเสียรายละเอียดที่จำเป็น
9. ใช้เครื่องมือขั้นสูง
Use Power Query: Power Query Editor มี Built-in Features Import และ Clean Data ลดความต้องการสูตรซับซ้อน
Enable Data Model: สำหรับ Dataset ใหญ่ ใช้ Data Model ในตัวของ Excel สร้าง Relationships และคำนวณ
Use VBA for Repeated Tasks: Automation งานซ้ำด้วย VBA ลดความซับซ้อนของ Workbook
10. ปิด Add-ins ที่ไม่จำเป็น
ไม่ Enable Add-ins ถ้าไม่ได้ใช้ประจำ ทุกครั้งเปิด Excel มันโหลด Add-ins ทั้งหมด
- File >> Options
- Excel Options >> Add-Ins
- ระบุและ Disable Add-ins ที่ไม่จำเป็นภายใต้ Manage COM Add-ins
- Remove Add-ins ที่ไม่ใช้
ปล่อยทรัพยากร โดยเฉพาะตอนทำงานกับ Spreadsheet ใหญ่
11. บำรุงรักษาเป็นประจำ
ใช้ Find and Replace ล้าง Errors และ Broken Links กำจัด Anomalies และ Errors
ใช้ Find and Replace ล้าง Errors:
- แท็บ Home >> Find & Select >> Find/Replace
- Type ชื่อ Error ในกล่อง Find
- Replace All Errors หรือแก้ Links ที่ทำให้ Error
หลัง Optimize ทดสอบ Workbook ให้แน่ใจว่า Functionality ไม่ได้รับผลกระทบ
สรุป
Spreadsheet Optimization ไม่ใช่งานยาก Workbook ใหญ่ไม่จำเป็นต้องซับซ้อน ทำตามเทคนิคและขั้นตอนปฏิบัติข้างต้น ปรับปรุง Performance ได้ ไม่ว่าจะเป็น Optimize Formulas, ลดขนาดข้อมูล, หรือใช้ Advanced Excel Tools วิธีเหล่านี้ช่วยให้ Spreadsheet ทำงานราบรื่น
Get FREE Advanced Excel Exercises with Solutions!
