Performance Tuning

Tối ưu workbook Excel khi dùng DVDAddin với data lớn — > 10,000 hàng, nhiều UDF, hàng trăm sheet.

Đo lường trước khi tối ưu

Tools

Tool	Mục đích
Windows Task Manager	Xem RAM + CPU của EXCEL.EXE khi mở file / khi recalc
Process Explorer	sysinternals.com — chi tiết hơn Task Manager (Working Set, Private Bytes, handle)
Sysinternals Process Monitor	Track file I/O nếu nghi vấn ổ đĩa / antivirus quét file là nút thắt
Đồng hồ bấm giờ + F9	Cách đơn giản nhất: chuyển sang Manual calc, bấm F9, đếm giây
Báo cáo giai đoạn của In hàng loạt	Built-in — xem phần dưới

Baseline đo

Trước khi optimize → đo baseline:

1. Đóng tất cả app khác.
2. Mở workbook → ghi thời gian load.
3. Trigger recalc (F9) → ghi thời gian. Muốn tính lại toàn bộ kể cả ô đã cache: Ctrl + Alt + Shift + F9.
4. Save → ghi thời gian.

Sau optimize → đo lại → so sánh. Đo cùng một máy, cùng một file, không mở app khác — số đo mới có nghĩa.

Top 10 nguyên nhân workbook chậm

#1 — Quá nhiều UDF cell-level

UDF của add-in chạy chậm hơn hàm built-in của Excel, và UDF không được Excel tính đa luồng (xem Multi-thread).

Detect: Workbook có hàng nghìn ô =dvd...(...) → recalc lâu.

Fix:

• Convert UDF result → static values (Ctrl+C → Paste Special → Values).
• Hoặc dùng lệnh ribbon thay UDF. Ví dụ thay vì 1000 ô =dvdTranslate(...), chọn vùng rồi chạy Dịch ngôn ngữ một lần — kết quả ghi thẳng vào ô, không còn công thức nào phải tính lại.

#2 — Volatile functions

Hàm volatile được Excel tính lại mỗi lần ANY cell đổi, kể cả ô không liên quan.

Volatile trong DVDAddin — đúng 5 hàm:

Hàm	Ghi chú
dvdAutoHide	Ẩn/hiện dòng theo điều kiện — phải volatile mới bám kịp dữ liệu
dvdSumVisible	Tổng các ô đang hiển thị — ẩn/hiện dòng không sinh sự kiện tính lại nên phải volatile
dvdMVLookup	Trả nhiều kết quả
dvdMCLookup	Trả nhiều kết quả, nhiều cột
dvdUniqueV	Danh sách giá trị duy nhất

Volatile built-in: NOW(), TODAY(), RAND(), OFFSET(), INDIRECT().

Detect: Edit 1 cell trong sheet khác → status bar hiện "Calculating (XX%)" lâu.

Fix:

• Thay OFFSET(A1,1,0,10,1) → A2:A11 (cứng).
• Thay INDIRECT("Sheet1!"&A1) → CHOOSE / IF (nhiều if).
• NOW() chỉ 1 cell duy nhất, các cell khác tham chiếu cell đó.
• Với 4 hàm volatile còn lại: giữ số lượng ô ở mức vài chục, đừng rải xuống 5000 dòng.

#3 — VLOOKUP toàn cột

=VLOOKUP(A1, BangGia!A:Z, 8, FALSE) — Excel quét full column A của BangGia mỗi cell.

Fix:

• Đổi sang range cố định: =VLOOKUP(A1, BangGia!$A$2:$Z$1000, 8, FALSE).
• Hoặc dùng INDEX/MATCH.
• Hoặc convert BangGia thành Excel Table (Ctrl+T) → reference theo column name.

#4 — Conditional Formatting với formula

Mỗi CF rule với formula chạy cho mỗi cell trong range mỗi lần recalc.

Detect: Workbook có 50+ CF rule + ranges lớn.

Fix:

• Gom range — thay 100 rules trên 100 cell riêng → 1 rule trên range tổng.
• Đơn giản hóa formula — dùng cell value check thay formula phức tạp.
• Disable CF tạm: Home → Conditional Formatting → Clear Rules (chỉ tạm thời để debug).

#5 — Data Validation phức tạp

Cell có Data Validation =COUNTIF(...) = 0 → recalc mỗi lần value đổi.

Fix: Đơn giản hóa list. Dùng Named Range tĩnh thay vì dynamic.

#6 — Image / Shape quá nhiều

Workbook nhiều ảnh nhúng → memory tốn nhiều, render lag.

Detect: File .xlsx > 50MB nhưng cell text không nhiều.

Fix:

• Chỉnh cỡ ảnh trước khi nhúng — hạ độ phân giải hàng loạt.
• Dùng Comment ảnh thay vì nhúng thẳng vào ô.
• Save as .xlsb (Binary Excel) — xem File size optimization.

#7 — Pivot Table quá lớn

Pivot từ source 100k+ row → recalc lâu.

Fix:

• Source dùng Excel Table (faster than range).
• Pivot → Options → Data → "Refresh data when opening file" = OFF (manual refresh).
• Dùng Data Model + Power Pivot cho dữ liệu rất lớn.

#8 — File chia sẻ qua mạng

File mở từ network drive (\\server\share\file.xlsx) → mỗi lần save đều lag.

Fix:

• Copy về local → edit → upload lại.
• Hoặc dùng OneDrive sync (file local, sync ngầm).

#9 — Nhiều External Reference

='[OtherFile.xlsx]Sheet'!A1 → Excel cần đọc file kia khi recalc.

Detect: File mở chậm + có popup "Update External References".

Fix:

• Data → Edit Links → Break Link → công thức thành giá trị tĩnh.
• Hoặc gom số liệu cần dùng thành một bảng snapshot trong chính file, cập nhật thủ công theo đợt.

#10 — Add-ins khác conflict

PowerPivot + Solver + Analysis ToolPak + add-in của antivirus + DVDAddin — mỗi add-in đều cộng thêm thời gian khởi động Excel.

Fix: Tắt add-in không dùng (File → Options → Add-ins → Manage → Go → uncheck).

DVDAddin-specific tuning

Tắt tự động tính / tự động vẽ khi đang nhập tiến độ

Tab DVD Cons có hai công tắc ảnh hưởng trực tiếp tới tốc độ nhập liệu:

• Tự động tính — bật thì mỗi lần đổi ô là add-in tính lại ngày tháng cho bảng tiến độ.
• Tự động vẽ — bật thì sau mỗi lần tính lại, Gantt được vẽ lại.

Khi nhập một loạt vài trăm công tác, tắt cả hai, nhập xong rồi bật lại (hoặc chạy Vẽ tiến độ một lần). Đây là khác biệt lớn nhất giữa "nhập mượt" và "nhập giật" trên bảng tiến độ lớn.

Chuyển Excel sang Manual calculation

Mode mặc định: Auto — Excel recalc mỗi lần cell đổi.

Workbook nhiều UDF → chuyển sang Manual:

• File → Options → Formulas → Calculation options → Manual.
• Bấm F9 để tính lại khi cần.

→ Sửa dữ liệu không kéo theo hàng nghìn lượt tính lại.

Hàm mạng: cái nào chặn Excel, cái nào không

Đây là điểm hay bị hiểu nhầm — không phải hàm mạng nào cũng chạy ngầm:

Hàm	Cách chạy	Hệ quả
dvdTranslate	Đồng bộ, timeout 15 giây	Excel đứng chờ từng ô một. 100 ô mạng chậm = Excel treo rất lâu
dvdStock	Đồng bộ, timeout 15 giây	Như trên
DVDFx	Bất đồng bộ + cache 1 giờ trên đĩa	Ô hiện #N/A một lát rồi tự có giá trị; không chặn Excel
dvdAIExplain	Bất đồng bộ	Như trên

Suy ra:

• Đừng rải dvdTranslate xuống cả cột. Dùng lệnh Dịch ngôn ngữ cho vùng lớn, chỉ để lại UDF ở vài ô cần cập nhật động.
• DVDFx cache theo bộ ba (từ tiền, sang tiền, ngày) trong %LocalAppData%\DVDAddin\fx_cache.json, TTL 1 giờ — 500 ô cùng cặp USD→VND chỉ tốn đúng 1 lần gọi mạng.
• =DVDFx(A1,B1) với A1 = B1 trả về 1.0 ngay, không gọi mạng.

Đo được bằng chính công cụ của add-in

Nếu nghi ngờ chậm ở khâu in/xuất, đừng đoán — In hàng loạt in ra bảng phân rã thời gian ở cuối mỗi lần chạy (xem ngay dưới).

Batch Print performance

In hàng loạt chạy N vòng lặp; cuối lượt add-in hiện báo cáo thời gian theo từng giai đoạn, kèm phần trăm — nhìn vào là biết nút thắt nằm ở đâu:

⏱  Tổng: 45.12s
   • Ghi driver cell:       1.23s (3%)
   • Calculate:             8.45s (19%)
   • AutoFilter:            0.12s (0%)
   • Resolve+Select sheet:  2.34s (5%)
   • Auto-fit merge:        4.56s (10%)
       ↳ Pass1 baseline:    1.10s (2%)
       ↳ Pass2 per-merge:   2.90s (6%)  (318 merge)
       ↳ Pass3 apply:       0.56s (1%)
   • Export PDF|In:         28.40s (63%)
   • Gộp PDF (PdfSharp):    0.50s (1%)

Ba dòng ↳ Pass... chỉ hiện khi có bật tùy chọn tự canh chiều cao ô gộp. Dòng Gộp PDF chỉ hiện khi có tick gộp file.

Đọc báo cáo:

• Calculate > 30% → bảng có công thức nặng. Giảm UDF, giảm volatile, giảm array formula trong các sheet được in.
• Auto-fit merge > 20% → đang canh chiều cao cho quá nhiều vùng. Vùng cần canh được khai ở cột C của sheet Mucluc (dòng 5–104, tra theo tên sheet ở cột B) — thu hẹp lại chỉ những vùng thật sự cần.
• Export PDF|In ~60-70% → bình thường. Đây là phần Excel tự xuất file, add-in không tối ưu thêm được.
• Ghi driver cell / Resolve+Select cao bất thường → workbook có quá nhiều sheet hoặc tên sheet trong Sheet-print cell đang trỏ lung tung.

Ngoài ra: vòng lặp dài đừng gõ vào ô nào trong lúc đang chạy — Excel bận, thao tác của bạn sẽ va vào add-in.

Memory consumption

Detect

Process Explorer (sysinternals):

• Add column "Working Set" + "Private Bytes".
• Monitor EXCEL.EXE qua thời gian.

Memory tăng dần và không tụt sau khi đóng workbook → nghi ngờ có COM object không được giải phóng từ macro / add-in nào đó.

Khoanh vùng

Tắt tất cả add-in → mở file → đo memory → bật lại từng add-in một → tìm cái làm memory phình.

Nếu khoanh được về DVDAddin, gửi kèm file (hoặc mô tả thao tác) qua kênh hỗ trợ trong hộp thoại Tác giả.

Multi-thread

Excel built-in threading

File → Options → Advanced → Formulas → "Enable multi-threaded calculation":

• Mặc định: theo số nhân CPU.
• Có thể đặt cứng số luồng nếu muốn chừa CPU cho việc khác.

Lưu ý quan trọng: chỉ hàm built-in của Excel mới được tính song song. UDF của add-in (mọi hàm dvd...) chạy đơn luồng — thêm nhân CPU không làm chúng nhanh hơn. Đây là lý do "giảm số ô UDF" hiệu quả hơn mọi tinh chỉnh phần cứng.

Hàng đợi macro

Vài hàm phải đợi Excel rảnh mới thực hiện được phần việc của mình. Một hàm đang tính thì không được phép sửa sheet, nên những hàm sau xếp việc vào hàng đợi và chạy ngay sau khi lượt tính kết thúc:

• dvdAutoHide — ẩn/hiện dòng.
• dvdPic — chèn ảnh vào ô.
• dvdMVLookup, dvdMCLookup, dvdUniqueV — ghi các kết quả thứ 2 trở đi xuống outputRange (đường dành cho Excel 2016/2019 không có dynamic array).

Hệ quả thực tế: đừng gõ dở dang trong một ô rồi mong dvdPic vẽ xong ảnh — hoàn tất việc nhập (Enter / Esc) thì hàng đợi mới chạy. Cũng vì vậy mà ba hàm ghi outputRange ở trên không hợp để rải hàng loạt: mỗi ô là một lượt ghi sheet xếp hàng sau lượt tính.

File size optimization

Save as .xlsb

.xlsb (Excel Binary Workbook) = cùng tính năng .xlsx nhưng lưu ở dạng nhị phân:

• File size nhỏ hơn đáng kể với workbook nhiều công thức.
• Mở/save nhanh hơn.
• Đôi khi gặp vấn đề tương thích với công cụ của bên thứ ba đọc file Excel.

→ Cân nhắc .xlsb cho file lớn.

Clean unused cells

Workbook có cell trống ở row 1,000,000 (do paste nhầm) → Excel vẫn coi sheet là to.

Fix:

1. Ctrl + End → nhảy tới ô cuối mà Excel nghĩ là cuối.
2. Nếu nhảy xa hơn nội dung thật → có ô rỗng còn định dạng.
3. Chọn các dòng/cột thừa → Right-click → Delete.
4. Save → đóng → mở lại → Ctrl + End → giờ nhảy đúng vị trí.

Hai lệnh giúp dọn nhanh phần này: Xoá format không dùng và Xoá Cell Style ngoại lai (style rác theo file người khác gửi tới, thường sinh ra hàng nghìn style thừa).

Compress images

File → Compress Pictures → 96 ppi (email) hoặc 150 ppi (web).

→ File giảm rất nhiều nếu workbook nhiều ảnh. Với ảnh chưa chèn, dùng Chỉnh cỡ ảnh xử lý hàng loạt trước.

Liên quan

• Best Practices — general tips.
• Backup & Restore — strategy.
• Power User Tips — advanced.
• Troubleshooting — mục #18: workbook chậm sau khi cài DVDAddin.