Nguyên nhân và xử lý File Excel bị nặng, chạy chậm

Khi mở File Excel mà bị nặng và chạy chậm với các biểu hiện như:

• Mở hoặc lưu file rất lâu, thực hiện cuộn/lọc bị khựng khung hình, công thức thì “Calculating…” rất lâu.
• Khi gõ ký tự bị trễ, đổi sheet hoặc kéo công thức bị giật lag, xuất hiện thông báo “Not Responding”
• Kích thước file lớn bất thường trong khi dữ liệu không có nhiều.

Vậy khi gặp tình trạng này ta sẽ xử lý như thế nào? Nguyên nhân chính gây tới lỗi này thường là:

• Do công thức nặng hoặc Volatility cao: sử dụng các hàm OFFSET, INDIRECT, NOW/TODAY, RAND/RANDBETWEEN; SUMPRODUCT trên mảng dữ liệu lớn,…
• Sử dụng định dạng Conditional Formatting trùng lặp, tô màu/viền toàn bảng quá nhiều.
• Vùng dùng (UseRange) phình to gồm ô rác, định dạng chạy tới cột/hàng rất xa.
• Đang bật PivotTable/Power Query/Pivot cache.
• Có nhiều ảnh, shape, icon, SmartArt, Chart nặng.
• Add-in xung đột, hoặc đang sử dụng phiên bản 32-bit thiếu RAM.

Sau đây là hướng dẫn chi tiết cách để xử lý khi gặp lỗi File Excel bị nặng:

1. Quy trình xử lý nhanh khi gặp File Excel nặng, giật lag

Để file Excel hết nặng gây ra lỗi giật lag, hãy thử nhanh các cách dưới đây:

• Lưu file dưới dạng .xlsb: Vào File → Save As → Excel Binary Workbook
• Mở file ở chế độ Safe Mode (Win+R → gõ excel /safe → mở file) => nếu file excel mở lên nhanh hơn thì chứng tỏ lỗi do Add-ins => tiến hành loại bỏ Add-ins (File → Tùy chọn/Options → Add-ins).
• Tắt tính năng tự động tính toán trong lúc sửa (Formulas → Calculation Options → Manual). Khi cần tính thì bấm F9.
• Tiến hành dọn UseRange (vào từng sheet bấm Ctrl + End để xem ô cuối) => nếu thấy nhiều hàng và cột thừa thì tiến hành xóa (Home → Clear → Clear All → lưu file, mở lại.)
• Giảm định dạng thừa (Home → Conditional Formatting → Manage Rules → hợp nhất/bỏ trùng, tick Stop If True, giới hạn phạm vi áp dụng định dạng)
• Kiểm tra các link ngoài (Data → Edit Links (nếu có) → Break Links/Update hợp lý)
• Tắt “Save source data with file” ở Pivot (Right-click Pivot → PivotTable Options → Data → bỏ Save source data with file → OK → Refresh khi cần.)
• Gỡ hoặc ẩn ảnh & shape không dùng (Home → Find & Select → Selection Pane → ẩn/xóa)

Sau đây sẽ là hướng dẫn chi tiết từng cách kèm ảnh minh họa để xử lý lỗi file excel chậm và giật do file nặng:

2. Lưu file dưới định dạng .xlsb

File .xlsb lưu dữ liệu dưới dạng nhị phân thay vì định dạng XML như .xlsx hoặc .xlsm, giúp giảm kích thước tệp và tăng tốc độ xử lý. Nhất là các file excel chứa nhiều dữ liệu, hoặc công thức phức tạp.

Để lưu file dưới dạng .xlsb ta thực hiện như sau:

• Mở file Excel
• Vào File => chọn Save As
• Tại mục Save As Type chọn Excel Binary Workbook => rồi chọn Save.

3. File Excel nặng do Add-ins

Thử mở file Excel ở chế độ Safe Mode bằng cách nhấn giữ Ctrl + Enter vào file.

Nếu khi mở bằng chế độ Safe Mode mà File bị nặng lại mở nhanh hơn thì là do lỗi ở xung đột Add-ins. Ta sẽ tiến hành tắt các Add-ins bằng cách:

• Vào File => Chọn Options => Chọn Add-ins
• Tại ô Mangage chọn Excel Add-ins => Click bỏ chọn tất cả các add-ins => chọn Ok

4. Tối ưu công thức

Một phần nguyên nhân File Excel bị nặng với giật lag có thể là do sử dụng các hàm nặng. Nguyên tắc sử dụng công thức để tránh xảy ra tình trạng này như sau:

• Luôn giới hạn phạm vi với dải động thực tế (vd: $A$2:$A$1000) hoặc Excel Table (Ctrl + T) thay vì sử dụng phạm vi rộng như A:A hoặc 1:1
• Ưu tiên sử dụng hàm không volatile như thay hàm OFFSET/INDIRECT bằng hàm INDEX/CHOOSE + MATCH; và hạn chế sử dụng các hàm NOW/TODAY/RAND*.
• Tránh sử dụng hàm SUMPRODUCT trên phạm vi dữ liệu lớn nếu chỉ cần lọc điều kiện thì hãy sử dụng SUMIFS/COUNTIFS.
• Khi dò tìm dữ liệu thì sử dụng hàm XLOOKUP với match_mode=0 (chính xác). Nếu dữ liệu đã sắp xếp tăng, có thể sử dụng hàm approximate cho nhanh.
• Sử dụng tạo cột phụ để tách và giảm độ phức tạp của mảng dữ liệu.
• Tránh sử dụng công thức trong Conditional Formatting, nếu cần sử dụng thì hãy dùng cột phụ và Stop If True.

Để dễ hình dung hơn thì mình sẽ đi vào ví dụ thực tế như sau:

• Hàm INDIRECT:
  • Trước sử dụng =SUM(INDIRECT("'"&A2&"'!B:B"))
  • Ta tạo Name/Table cố định cho từng sheet hoặc cấu trúc dữ liệu lại; hoặc dùng INDEX: =SUM(INDEX(B:B,1):INDEX(B:B,last_row)) (last_row là số đã biết/hàm tính ra).
• Hàm OFFSET để làm dải động:
  • Trước sử dụng =SUM(OFFSET($B$2,0,0,COUNTA(B:B)-1,1))
  • Sau sử dụng Tale thì ta sẽ chuyển cột B thành Table Tbl[DoanhThu] → =SUM(Tbl[DoanhThu])
• SUMPRODUCT lọc nhiều dữ liệu:
  • Trước sử dụng =SUMPRODUCT((A:A="Hà Nội")*(B:B="Tháng 8")*C:C)
  • Sau sử dụng =SUMIFS(C:C,A:A,"Hà Nội",B:B,"Tháng 8")

5. Xóa Name rác trong file Excel

Khi làm việc trên file Excel, việc sao chép, move/copy sheet có thể kéo theo các name rác. Các name sẽ là “<definedName>[…]</definedName>“, và Name rác thường là có lỗi: “<definedName>#REF!</definedName>“.

Để xóa name rác trong file Excel, Ta đổi tên mở rộng của file Excel thành .zip.

Tiếp theo, nhấn chuột phải vào file đó rồi chọn Extract Here.

Khi file Excel đã được giải nén ra thành rất nhiều file nhỏ. Các bạn chọn file xl rồi nhấn chuột phải vào mục workbook.xml. Thanh cuộn hiện ra thì các bạn chọn mục Edit with Notepad++.

Tiếp theo, các bạn đặt dấu con trỏ ngay vị trí đầu tiên của file (ngay trước “<?xml”). Các bạn nhấn tổ hợp phím tắt Ctrl + H, sau đó các bạn chọn mục Regular expression trong phần Search Mode. Tìm với từ khóa sau: (<definedName\s.*>#REF!<\/definedName>). Sau đó tìm kiếm thử, tìm thấy sẽ được highlight, và bạn chọn Replace All. Cuối cùng các bạn lưu file lại Workbook.xml, và đóng Notepad++.

6. Xóa file chứa các object

Tương ứng với các objects trong mỗi sheet[…].xml tương ứng với mỗi file drawing[…].xml, trong sheet[…].xml sẽ có chỗ chứa sẽ là <drawing …/>. Để xóa nội dung đi, ta làm tương tự với workbook.xml [phần 1], sẽ mở tuần tự các file sheet[..].xml ra và tìm xóa đi các đoạn chứa <drawing… />. Các bạn chọn thư mục xl => worksheets. Sau đó bôi đen toàn bộ sheet rồi nhấn chuột phải và chọn mục Edit with Notepad++.

Tiếp theo, các bạn đặt dấu con trỏ ngay vị trí đầu tiên của file (ngay trước “<?xml”). Các bạn nhấn tổ hợp phím tắt Ctrl + H, sau đó các bạn chọn mục Regular expression trong phần Search Mode. Tìm với từ khóa sau: (<drawing r:id=”.*”/>). Nhấn Replace All in Opened Documents. Sau đó các bạn chọn File => Save All và đóng Notepad++ lại. Cuối cùng các bạn tìm đến trong thư mục drawings rồi xóa hết tất cả các file bên trong là xong.

7. Xóa liên kết bị lỗi trong file Excel

Khi bạn mở file và hiện ra thông báo này “SECURITY WARNING Automatic update of links has been disabled” tức là file của bạn đang có liên kết bị lỗi và Excel không thể update những liên kết này. Để xóa bỏ những link này, các bạn hãy làm theo các bước sau:

Đầu tiên, các bạn nhấn vào vào Enable Content ở thông báo. Lúc này thông báo tiếp theo hiện ra thì các bạn chọn Edit Links…

Hoặc các bạn cũng có thể chọn thẻ Data trên thanh công cụ Ribbon. Sau đó chọn Edit Links ở mục Connections.

Lúc này, hộp thoại Edit Links hiện ra với danh sách các link bị lỗi trong file của bạn. Các bạn nhấn chọn liên kết cần xóa sau đó nhấn Break Link.

Thông báo về kết quả dữ liệu sẽ bị ảnh hưởng nếu bạn xóa liên kết này hiện ra. Các bạn tiếp tục nhấn Break Links. Chỉ cần như vậy là chúng ta đã có thể xóa được link bị lỗi. Tiếp tục chọn các link bị lỗi tiếp theo rồi Break Links đến khi nào không còn link nào bị lỗi nữa.

8. Dọn định dạng & Conditional Formatting

Nếu bạn mở file Excel hoặc lưu file xong thấy file có kích thước tệp rất nặng lên đến hàng chục Mb trong khi các file Excel bình thường chỉ từ vài trăm kb đến vài Mb. Vậy thì bạn kiểm tra giúp mình xem file Excel đang có Option nào bị ẩn không bằng cách:

• Nhấn Ctrl + G => Chọn Special => chọn Objects để hiển thị các ẩn (thường sẽ là Icon, hình ảnh)

Sau khi đã tìm ra được các Options ẩn, ta sẽ tiến hành xóa chúng đi bằng Manage Rules trong Conditional Formatting như sau:

• Trong thẻ Home => Conditional Formatting => Chọn Manage Rules
• Khi giao diện Manage Rules hiện lên => chọn This Worksheet => để xem các Rules điều kiện có trong Sheet làm việc. Nếu thấy các Rules không phải do mình hay tiếng Nhật thì xóa bỏ đi.

Sau đó ta sẽ tiến hành xóa các định dạng bằng cách:

• Vào thẻ Home => Conditional Formatting => Chọn Manage Rules
• Chọn Clear Rules => chọn Clear Rules From Entire Sheet

Sau khi xóa định dạng xong thì file Excel của bạn sẽ giảm đáng kể dung lượng. Nhưng nếu vẫn thấy nặng hơn bình thường thì khả năng nguyên nhân nằm ở việc bạn đã format cho cả các ô dữ liệu không cần thiết. Điều này thường xuất hiện vì thói quen hay Ctrl + A để chọn tất cả các ô dữ liệu và đặt định dạng. Để xử lý ta sẽ sử dụng tính năng Check Performance như sau:

• Vào Review => chọn Check Performance
• Khi giao diện Workbook Performance hiện lên => chọn Optimize All => sau đó lưu lại file và mở lại để kiểm tra.

Sau khi xong thì file Excel của bạn sẽ nhẹ đi rất nhiều và hết bị lỗi nặng phải đợi load hoặc giật lag.

9. Tắt Save source data with file

Ngoài các lỗi trên, File Excel có thể bị nặng do:

• PivotTable mặc định sẽ bật lưu dữ liệu nguồn trong file khiến kích thước file Excel nặng.
• Nhiều PivotTable cùng nguồn nhưng không chia sẻ Pivot Cache khiến Excel lưu trùng dữ liệu nhiều lần.
• Power Query tải tất cả dữ liệu về sheet thay vì chỉ dùng kết nối khiến file nặng.

Để xử lý ta sẽ làm như sau:

• Tắt Save Source data with file: Vào PivotTable → Right-click → PivotTable Options → tab Data → bỏ chọn “Save source data with file”.

Như vậy, bài viết trên đã hướng dẫn các bạn cách khắc phục file Excel bị nặng, chậm, giật lag. Hy vọng bài viết sẽ hữu ích với các bạn trong quá trình làm việc. Chúc các bạn thành công!