Excel Hack 15

Chiêu thứ 15: Giảm kích thước file Excel bị phình to bất thường.

Bạn có bao giờ có 1 file Excel bị tăng kích thước đến mức báo động chả biết nguyên nhân? Có nhiều lý do làm cho bảng tính bị phình lên về kích thước file và có những cách để loại trừ. Những bí quyết sau đây có thể giúp bạn nếu bạn có 1 file Excel có kích thước lớn bất thường.
Có khi nào bạn bị bội thực do cố ăn mà chưa tiêu hoá kịp chưa? File Excel cũng thế. Nó bị phình ra là do bạn cố nhồi nhét đủ thứ vào bắt nó phải thực hiện, nó phải nuốt trọng chứ không kịp nhai, hậu quả là nó sẽ không làm việc như ý muốn.

Tôi đã thử với 1 file Excel chuẩn với khá nhiều dữ liệu. Với dữ liệu thô, nó có kích thước 1,37 Mb. Rồi tôi cho vào 1 Pivot Table, sử dụng dữ liệu nguồn là 4 cột của vùng dữ liệu. Kích thước file tăng lên 2,4 Mb. Tôi thử thiết lập vài định dạng, và kích thước file lập tức bị nhân đôi.

Một trong những nguyênnhân chủ yếu làm tăng kích thước file ở những file Excel trước 2007, khi chưa có khái niệm table, là các bạn cứ thế định dạng màu, cỡ font, màu chữ, đóng khung, . .. cho cả cột hoặc cho cả dòng, nếu không nói là cả bảng tính; chứ không chỉ định dạng cho khu vực chứa dữ liệu. Tôi từng thấy người hễ mở bảng tính mới lên, là lập tức Ctrl A, tô trắng toàn bộ, đóng khung toàn bộ. Không những thế họ còn xác định vùng dữ liệu nguồn cho biểu đồ, cho Pivot table, . . . là cả nguyên cột. Có khi thấy cái thanh cuộn dọc và ngang của Excel trở nên nhỏ tí tị, kéo hoài không thấy hết mà dữ liệu thì trống trơn.
Để giải quyết bạn phải sửa, bỏ hết những định dạng thừa trong những dòng cột trống, bỏ cái thói quen xoá màu fill color bằng cách chọn fill trắng. Hãy chọn No Fill! Sau đó điều chỉnh dữ liệu nguồn của biểu đồ, của Pivot Table chỉ vừa với vùng có dữ liệu.

Hãy làm như sau: trước hết hãy back up bảng tính lại.
1. Gỡ bỏ những định dạng trên những dòng cột thừa:
Bước đầu tiên là xác định bằng tay và mắt ô cuối cùng bên phải ở dưới, có chứa dữ liệu. Đừng có mà dùng phím ctrl+ mũi tên xuống hoặc ctrl + mũi tên qua phải, hoặc dùng Find - Select - Go To Special – Last Cells, cách này sẽ chỉ tới ô cuối cùng có định dạng, không phải ô cuối có dữ liệu. Khi đã xác định bằng mắt ô cuối chứa dữ liệu thì click chọn nguyên dòng kề dưới ô đó. Lúc này ới nhấn Ctrl + Shift + mũi tên xuống để chọn toàn bộ những dòng bên dưới vùng có dữ liệu. Sau đó dùng lệnh Clear – All để xoá vùng này.
Tương tự, khi xác định ô cuối có chứa dữ liệu xong, nhấn chọn nguyên cột liền kề bên phải, nhấn tiếp Ctrl + Shift + mũi tên qua phải để chọn toàn bộ cột bên phải. Sau đó lại dùng lệnh Clear – All để xoá vùng này.

Không nên dùng lệnh xoá dòng hoặc cột, vì có thể có những công thức tham chiếu đến chúng. Lưu bảng tính lại và thử xem kích thước file trong Property:
2007: Office button ➝Prepare ➝Properties ➝Document Properties ➝Advanced Properties
2003: File ➝Properties… ➝General

2. Xoá Macro: Không phảo là xoá hẳn Macro, mà chỉ gỡ chúng ra ngoài file Excel:
Có 1 cách nhanh, không gây hại đó là Export tất cả các modules và UserForms vào đĩa cứng. Nhấn Alt F11 vào cửa sổ VBA. nhấn chuột phải vào từng module, chọn remove module (tên môdule). Trong thông báo hiện ra nhấn Yes, và chọn 1 thư mục để lưu lại. làm tiếp cho từng cái Form. đừng quên cả những macro lưu trữ trong sheet hoặc workbook.

Sau khi đã xoá xong, lưu bảng tính lại. Sau đó, cũng trong cửa sổ VBA, vào menu File – Import File và import hết những modue và form hồi nãy. Làm như vậy, ta vẫn có thể sử dụng form và Macro, nhưng mỗi cái như vậy sẽ tạo1 file text, và ngăn chặn những thứ linh tinh mà Macro mang theo.

Một số ứng dụng trên Internet có thể làm công việc này, nhưng được biết rằng những tiện ích đó sẽ làm rối tung code và thậm chí làm tăng kích thước file. Tốt nhất trước khi ứng dụng 1 tiện ích nào, hãy backup file của bạn trước.

3. Điều chỉnh vùng dữ liệu nguồn:
Nếu sau khi làm những bước trên mà kích thước file của bạn không giảm được bao nhiêu, hãy kiểm tra dữ liệu nguồn của Pivot Table và Pivot Chart. Một vài người thường sử dụng nguyên cả cột dữ liệu để làm nguồn cho Pivot table, chỉ để phòng xa sau này có thêm dữ liệu cập nhật vào dù chỉ vài dòng. Nếu nhất thiết phải phòng xa như vậy, tốt hơn bạn dùng name động. Xem thêm tuyệt chiêu số 47.

4. Làm sạch các sheet:
Trong các sheet của bạn có thể tồn tại những định dạng thừa, những style, những autoshape thấy được và không thấy được (có khi lên đến hàng trăm hoặc hàng ngàn).

a. Xoá object và autoshape thừa. Để tìm ra những autoshape hoặc object ẩn bạn phải vào option của Excel sửa lại như hình sau:
2007:



2003:



Bạn có thể nhấn Ctrl + G, Special – Object để chọn 1 lúc nhiều object.



b. Làm sạch những cái không nhìn thấy trong sheet:
Trước tiên, bạn hãy backup bảng tính. Unhide toàn bộ sheet ẩn và cả sheet siêu ẩn. (xem thêm chiêu số 5). Bây giờ bạnthử xoá lần lượt từng sheet một (xoá hẳn), lưu lại, rồi vào Property xem lại kích thước. Nếu sau khi xoá sheet nào, mà kích thước file giảm đáng kể, thì sheet đó chứa nhiều rác (không kể dữ liệu). Vậy, khi đã xác định sheet chứa rác thì bạn phải làm gì?

Bạn hãy mở file backup hồi nãy lên, tạo 1 sheet mới toanh, chọn vùng dữ liệu của sheet chứa rác, dùng lệnh Cut (Ctrl + X), chứ đừng copy, Paste vào sheet mới. Bằng cách cắt (Cut), Excel sẽ giữ nguyên tham chiếu cho bạn.
Cuối cùng, delete sheet chứa rác.

Hy vọng là sau này, các bạn sẽ biết cách xử lý những file có kích thước lớn lạ thường.