Excel đã được biết là trở nên chậm chạp và nặng nề vì càng có nhiều dữ liệu / công thức / hình ảnh được thêm vào nó.
Đôi khi, thật dễ dàng để loại bỏ các lỗi thừa và giữ kích thước tệp excel trong kiểm tra, và đôi khi, nó không phải là.
Và vì Excel không phải là một công cụ dựa trên web, hầu hết chúng ta làm việc và cộng tác bằng cách gửi các tệp Excel qua email. Và một tệp Excel lớn có trọng lượng 50 MB có thể khó chia sẻ với đồng nghiệp / khách hàng của bạn.
Trong công việc cuối cùng của tôi, chúng tôi đã sử dụng ứng dụng email Lotus Notes, rất chậm và gửi các tệp Excel nặng nề hơn là một tra tấn.
Mặc dù không có cách nào để giảm kích thước của sổ làm việc Excel, nhưng có một vài kỹ thuật mà bạn có thể sử dụng để giảm bớt một số lỗi.
Lưu ý: Tác động của các kỹ thuật này để giảm kích thước tệp Excel sẽ phụ thuộc vào dữ liệu của bạn. Đối với một số người, nó có thể giúp giảm kích thước xuống 50% và đối với một số người, nó chỉ có thể giảm kích thước xuống 5%.
Mẹo để giảm kích thước tệp Excel
Dưới đây là các kỹ thuật bạn có thể sử dụng để giảm kích thước tệp của sổ làm việc Excel của bạn.
Đối với mỗi mẹo, tôi đã thực hiện một số thử nghiệm và đã thêm ảnh chụp nhanh để hiển thị cho bạn tác động đối với kích thước tệp. Kết quả bạn nhận được sẽ khác nhau tùy thuộc vào dữ liệu và tệp của bạn.
Dựa trên sổ làm việc của bạn, bạn có thể sử dụng một hoặc nhiều mẹo sau.
Xóa bảng tính / dữ liệu không cần thiết
Các giải pháp tốt nhất thường là những giải pháp dễ nhất.
Và kỹ thuật này có ý nghĩa thông thường hơn và ít hơn của bất kỳ thuật sĩ Excel nào.
Nếu bạn có sổ làm việc Excel có một số trang tính không cần thiết (và không được sử dụng trong bất kỳ công thức nào ), hãy xem bạn có thể loại bỏ chúng không.
Điều này có tương quan trực tiếp với kích thước tệp Excel. Vì vậy, ngay sau khi bạn xóa một số trang tính và lưu sổ làm việc, bạn sẽ ngay lập tức thấy kích thước tệp bị giảm.
Kết quả kiểm tra:
Tôi đã tạo một tệp Excel với 10 trang tính trong đó mỗi trang tính có 2,6 triệu điểm dữ liệu (26 cột với 100.000 điểm dữ liệu trong mỗi cột).
Kích thước kết quả của sổ làm việc này là 143 MB (và mất khá nhiều thời gian để lưu và mở).
Bây giờ khi tôi xóa năm tờ, kích thước giảm một nửa (duh).
Như bạn thấy, dữ liệu mà bạn có bảng tính của bạn có mối tương quan trực tiếp với kích thước tệp.
Chuyển đổi sang định dạng nhị phân (XLSB)
Bạn có biết rằng chỉ bằng cách lưu một tệp Excel ở định dạng XLSB có thể giảm kích thước tệp không?
Vâng đúng vậy.
Tất cả những gì bạn phải làm là trong khi lưu sổ làm việc, hãy sử dụng định dạng .xlsb.
Dưới đây là các bước để thực hiện việc này:
- Nhấp vào tab Tệp.
- Nhấp vào Lưu dưới dạng.
- Nhấp vào Duyệt qua.
- Trong hộp thoại Lưu dưới dạng, thay đổi loại tệp thành Sổ làm việc nhị phân Excel (.xlsb)
Kết quả kiểm tra
Tôi đã có một bảng tính Excel mất 14,6 MB không gian.
Sau khi chuyển đổi cùng một tệp sang định dạng XLSB, tôi đã có thể giảm kích thước xuống còn 10,4 MB (giảm kích thước ~ 28%).
XLSB là gì?
Khi bạn lưu một tệp Excel (.xlsx hoặc .xlsm), nó sẽ được lưu ở định dạng XML.
Ví dụ, nếu tôi thay đổi phần mở rộng tập tin của tập tin XLSX thành ZIP và sau đó mở nó, đây là những gì tôi nhận được.
Và nếu tôi tiếp tục đi trong thư mục ‘xl’ và sau đó đi vào thư mục trang tính, nó sẽ hiển thị cho tôi các trang tính trong sổ làm việc đó (và các trang tính này được lưu dưới dạng tài liệu XML).
Bây giờ chúng ta hãy làm tương tự với một tệp XLSB.
Vì vậy, tôi lần đầu tiên thay đổi phần mở rộng tập tin để ZIP, sau đó mở nó và xác định vị trí sheet1. Dưới đây là những gì bạn nhận được:
Bạn sẽ nhận thấy rằng cả XLSX và XLSB sẽ lưu bảng tính theo một định dạng khác.
XLSX / XLSM lưu nó dưới dạng tệp XML và XLSB lưu nó dưới dạng tệp nhị phân (.bin).
Và không quá kỹ thuật, các tệp XML có kích thước lớn so với tệp nhị phân.
Vì vậy, khi bạn lưu sổ làm việc Excel của bạn ở định dạng XLSB, kích thước tệp bị giảm. Trong ví dụ trên, giảm kích thước là ~ 30%.
XLSB Vs XLSX / XLSM
Khi nói đến việc sử dụng định dạng XLSB, việc giảm kích thước là một lợi ích rất lớn. Nhưng bạn cần phải thận trọng khi làm việc với định dạng XLSB.
Ưu điểm của định dạng XLSB:
- Nó làm giảm kích thước tệp Excel. Điều này có thể hữu ích khi bạn có các tệp lớn và bạn muốn chia sẻ nó qua email. Bạn có thể nhanh chóng giảm kích thước bằng cách lưu nó trong XLSB.
- Các tệp XLSB có xu hướng mở và lưu nhanh hơn tệp XML. Dựa trên các thử nghiệm giới hạn mà tôi đã làm, các tệp XLSB đã được mở và tiết kiệm nhanh hơn 25% -50%. Sự khác biệt này chỉ đáng chú ý trong các tệp Excel lớn. Với các tệp nhỏ hơn, cả XLSB và XLSX đều nhanh.
Mặc dù XLSB có vẻ tuyệt vời nhưng có một vài lý do bạn nên sử dụng tệp XLSX / XLSM:
- Với định dạng XLSB, bạn không có cách nào để biết liệu nó có macro hay không. Điều này làm cho nó nguy hiểm hơn khi ai đó có thể sử dụng nó để thực thi mã độc hại.
- Các tệp XLSB được mã hóa theo định dạng tệp bin độc quyền trong khi XML là một định dạng tệp có thể đọc được nguồn mở. Điều này có nghĩa là nếu bạn đang sử dụng các công cụ của bên thứ ba với Excel, tốt hơn là sử dụng định dạng XML.
- Power Query không thể đọc dữ liệu từ tệp XLSB. Vì vậy, nếu bạn sử dụng Power Query, tốt hơn là bạn nên có dữ liệu của mình ở định dạng XLSX / XLSM.
Theo quy tắc chung, nếu kích thước tệp của bạn nhỏ (dưới 5 MB), tốt hơn là nên gắn với các định dạng XLSX / XLSM.
Dựa trên những gì tôi nghe được từ mọi người và đọc trên nhiều diễn đàn, rất nhiều người thích sử dụng XLSB như là định dạng tệp khi nói đến việc sử dụng Excel.
Loại bỏ các công thức không cần thiết
Quá nhiều công thức sẽ làm tăng kích thước tệp sổ làm việc của bạn.
Vì vậy, nếu bạn có một tập dữ liệu mà bạn cần, nhưng không cần các công thức trong đó, tốt hơn là chuyển đổi các công thức này thành các giá trị.
- Chọn toàn bộ trang tính / tập dữ liệu.
- Nhấn phím F5 .
- Trong hộp thoại Tới, bấm vào ‘Đặc biệt’.
- Trong hộp thoại Chuyển đến Đặc biệt, chọn Công thức.
- Nhấp vào OK.
Các bước trên sẽ chọn tất cả các ô có công thức trong đó.
Bây giờ, hãy sử dụng các bước dưới đây để chuyển đổi các công thức này thành các giá trị:
- Sao chép các ô đã chọn (control + C)
- Nhấp vào tab Trang chủ.
- Trong nhóm Clipboard, nhấp vào Dán.
- Nhấp vào biểu tượng Dán giá trị.
Các bước trên sẽ chuyển đổi tất cả các ô có công thức thành giá trị.
Kết quả kiểm tra
Tôi đã tạo một tệp Excel với 2,7 triệu điểm dữ liệu (1048576 * 26) và sau đó thêm ba cột có công thức (SUM, COUNT và AVERAGE).
Sau đó, tôi chuyển đổi các công thức này thành các giá trị và lưu tệp bằng một tên khác.
Dưới đây là kết quả:
Tệp không có công thức nào có kích thước nhỏ hơn ~ 8% (và lưu ý rằng tôi chỉ có 3 cột công thức).
Trong trường hợp bạn có nhiều công thức, nó có thể thêm vào kích thước.
LƯU Ý: Công thức dễ bay hơi thực sự có thể làm tăng kích thước tệp của bạn. Là một phần của thử nghiệm, khi tôi thay thế công thức SUM bằng một công thức dễ bay hơi (OFFSET), nó dẫn đến một bước nhảy trong kích thước tệp (từ 186 MB đến 198 MB). Vì vậy, nếu bạn có các công thức dễ bay hơi mà bạn có thể thay thế bằng các công thức không bay hơi hoặc có thể chuyển đổi chúng thành các giá trị, nó sẽ giúp bạn giảm kích thước tệp.
Nén hình ảnh (Trước và sau khi tải lên)
Nếu bạn làm việc với các tệp Excel có hình ảnh trong đó, bạn cần tối ưu hóa những hình ảnh này và đảm bảo kích thước nhỏ hơn.
Vấn đề với hình ảnh là khi bạn nén những hình ảnh này, đó cũng là một giảm chất lượng, mà bạn có thể không muốn.
Khi làm việc với hình ảnh, cách tốt nhất để giữ cho kích thước tệp Excel của bạn thấp là bằng cách nén hình ảnh trước khi bạn chèn nó vào Excel.
Một công cụ trực tuyến tốt để làm điều này là TinyPNG (hoặc bạn có thể sử dụng Photoshop hoặc bất kỳ công cụ tương tự nào khác).
Đó là một công cụ miễn phí có thể giảm kích thước của hình ảnh lên đến 90% và vẫn duy trì chất lượng hình ảnh tốt.
Dưới đây là ảnh chụp màn hình mà tôi đã có thể nén một hình ảnh với kích thước 3,9 MB đến 331 KB với tổn thất tối thiểu về chất lượng.
Một khi bạn đã tải lên các hình ảnh nén vào Excel, bạn có thể tiếp tục nén nó bằng cách sử dụng tùy chọn sẵn có trong Excel.
Dưới đây là các bước để nén hình ảnh trong Excel:
- Chọn hình ảnh bạn muốn nén.
- Nhấp vào tab Định dạng công cụ hình ảnh. Đây là tab theo ngữ cảnh chỉ xuất hiện khi bạn nhấp vào một hình ảnh.
- Trong nhóm Điều chỉnh, nhấp vào tùy chọn ‘Nén Ảnh’.
- Trong hộp thoại Compress Pictures:
- Bỏ chọn ‘Chỉ áp dụng cho ảnh này’ nếu bạn muốn nén tất cả ảnh trong sổ làm việc.
- Chọn Email (96 ppi) để nén tối đa.
- Nhấp vào OK.
- Lưu các tập tin.
Điều này sẽ nén tất cả các hình ảnh và giúp bạn giảm kích thước tệp của sổ làm việc Excel của bạn.
Kết quả kiểm tra:
Tôi đã sử dụng tùy chọn nén imge trong Excel bằng một hình ảnh.
Dưới đây là kết quả:
Bằng cách sử dụng tùy chọn nén ảnh, kích thước tệp đã giảm từ 445 KB xuống 43 KB (giảm 90% kích thước tệp).
Điều này cũng phụ thuộc vào hình ảnh. Một hình ảnh đã được nén có thể không dẫn đến giảm kích thước lớn.
Xóa Pivot Cache để tiết kiệm dung lượng
Nếu bạn làm việc với các bảng Pivot và đấu tranh với các tệp Excel lớn, việc biết về bộ đệm Pivot có thể hữu ích.
Khi bạn tạo bảng tổng hợp bằng cách sử dụng tập dữ liệu, Excel sẽ tự động tạo Pivot Cache.
Pivot cache là một đối tượng chứa một bản sao của nguồn dữ liệu.
Trong khi bạn không thể nhìn thấy nó, nó là một phần của sổ làm việc và được kết nối với Bảng tổng hợp. Khi bạn thực hiện bất kỳ thay đổi nào trong Bảng tổng hợp, nó không sử dụng nguồn dữ liệu, thay vào đó nó sử dụng Pivot Cache.
Mặc dù bạn cho rằng bạn được liên kết trực tiếp với dữ liệu nguồn, trên thực tế, bạn truy cập vào bộ đệm ẩn (không phải dữ liệu nguồn) khi bạn thực hiện thay đổi trong bảng tổng hợp.
Bây giờ kể từ khi bạn đang tạo một bản sao của tập dữ liệu, nó mất một số không gian.
Vì vậy, ngay sau khi bạn tạo một bảng Pivot, bạn sẽ thấy rằng kích thước của tệp Excel tăng lên.
Trong ví dụ trên, ngay sau khi tôi thêm bảng Pivot, kích thước của tệp tăng gấp hai lần.
Bây giờ, có hai cách bạn có thể giảm kích thước tệp khi làm việc với các bảng Pivot.
- Giữ dữ liệu nguồn và xóa Pivot Cache
- Giữ Pivot Cache và xóa dữ liệu nguồn
Hãy xem xét cả hai kỹ thuật này.
Giữ dữ liệu nguồn và xóa bộ đệm ẩn Pivot
Khi bạn đã hoàn tất với bảng Pivot và đang lưu nó, bạn có thể buộc Excel chỉ lưu dữ liệu nguồn và Bảng tổng hợp kết quả, chứ không phải Pivot Cache.
Làm như vậy sẽ làm cho tệp Excel của bạn nhẹ hơn và bạn có cách tạo lại bộ nhớ đệm Pivot khi bạn cần nó trong lần tiếp theo.
Dưới đây là sự khác biệt về kích thước khi bạn lưu tệp Excel có và không có Pivot Cache.
Dưới đây là các bước để KHÔNG lưu Pivot Cache:
- Chọn bất kỳ ô nào trong Bảng tổng hợp.
- Nhấp vào tab Phân tích.
- Nhấp vào biểu tượng Pivot Table ‘Tùy chọn’.
- Trong hộp thoại Tùy chọn Bảng Pivot, bấm vào tab Dữ liệu.
- Trong tùy chọn Dữ liệu Bảng tổng hợp, bỏ chọn ‘Lưu dữ liệu nguồn bằng tệp’. Tùy chọn này đảm bảo rằng Pivot Cache không được lưu khi bạn lưu sổ làm việc (và giúp giảm kích thước tệp Excel).
- Chọn tùy chọn – ‘Làm mới dữ liệu khi mở tệp’. Tùy chọn này đảm bảo rằng ngay khi bạn mở sổ làm việc, Bảng Pivot được làm mới và Pivot Cache được tạo tự động. Nếu bạn không chọn tùy chọn này, bạn sẽ phải làm mới bảng Pivot theo cách thủ công để tạo Pivot Cache.
- Nhấp vào OK.
Bây giờ khi bạn lưu tệp với bảng Pivot, bạn sẽ nhận thấy kích thước tệp bị giảm.
Giữ Pivot Cache và xóa dữ liệu nguồn
Một cách khác làm giảm kích thước tệp Excel là xóa dữ liệu nguồn của Bảng Pivot và chỉ giữ cho Pivot Cache.
Điều này hoạt động như một Pivot Cache là một hình ảnh phản chiếu của dữ liệu của bạn và bạn không truy cập dữ liệu nguồn khi thực hiện thay đổi cho Pivot Table, thay vào đó, bạn sử dụng Pivot Cache.
Dưới đây là kết quả khi tôi lưu tệp Excel mà không có dữ liệu nguồn.
Để thực hiện việc này, chỉ cần xóa dữ liệu (bằng cách xóa dữ liệu hoặc trang tính có dữ liệu).
Khi bạn muốn lấy lại dữ liệu nguồn, bấm đúp vào ô ‘Tổng cộng’ của bảng tổng hợp và nó sẽ ngay lập tức tạo toàn bộ tập dữ liệu trong một trang tính mới.
Nén tệp (ZIP IT)
Đây là thứ bạn có thể làm bên ngoài ứng dụng Excel.
Khi bạn nén tệp Excel, bạn sẽ thấy giảm kích thước khoảng 10-15% ngay lập tức.
Sau đó, bạn có thể chia sẻ tệp nén này qua email và người nhận được tệp này có thể giải nén tệp và sử dụng sổ làm việc Excel.
Dưới đây là các bước để Nén tệp Excel (dành cho Windows):
- Nhấp chuột phải vào tệp Excel
- Di con trỏ của bạn trên tùy chọn Gửi đến.
- Nhấp vào tùy chọn ‘Nén (nén)’.
Thao tác này sẽ tạo một tệp nén cho sổ làm việc Excel đã chọn.
Bạn cũng sẽ thấy giảm kích thước tệp (như được hiển thị bên dưới).
Kết quả kiểm tra
Với kỹ thuật này, tôi đã có thể giảm kích thước tệp của sổ làm việc Excel xuống 8%.
Kỹ thuật này hoạt động tốt hơn cho các tệp có phạm vi 20-50 MB. Khi kích thước tập tin tăng lên, tỷ lệ phần trăm giảm không phải là rất cao.
Xóa định dạng dữ liệu
Định dạng dữ liệu như áp dụng màu nền hoặc thêm đường viền hoặc thay đổi kiểu phông chữ có thể thêm vào kích thước tệp.
Mặc dù, dựa trên các thử nghiệm của tôi, tôi nhận thấy rằng nó không có tác động lớn.
Dưới đây là kích thước của hai sổ làm việc Excel, một với 2,6 triệu điểm dữ liệu (trong một trang tính) và một với cùng dữ liệu nhưng định dạng được áp dụng cho nó (màu ô, đường viền và in nghiêng).
Kết quả kiểm tra
Bạn có thể thấy sự khác biệt giữa hai tập tin không phải là rất lớn (mặc dù nó thực hiện một tác động khi bạn có rất nhiều dữ liệu).
Tệp có dữ liệu là 14,6 MB và tệp có dữ liệu và định dạng là 14,9 MB.
Vì vậy, nếu bạn muốn giảm kích thước tệp của sổ làm việc Excel, bạn có thể tìm kiếm định dạng mà bạn không cần. Loại bỏ nó có thể giúp bạn tiết kiệm một số không gian đĩa.
Dưới đây là các bước để ngay lập tức xóa tất cả các định dạng, trong khi vẫn giữ nguyên dữ liệu:
- Chọn toàn bộ tập dữ liệu (mà từ đó bạn muốn xóa định dạng).
- Nhấp vào tab Trang chủ.
- Trong nhóm Chỉnh sửa, nhấp vào ‘Xóa’.
- Nhấp vào ‘Xóa định dạng’.
- Lưu sổ làm việc.
Xóa định dạng có điều kiện
Cũng giống như định dạng dữ liệu thông thường, định dạng có điều kiện cũng thêm vào kích thước tệp.
Tuy nhiên, dựa trên các thử nghiệm của tôi, tôi nhận thấy rằng sự khác biệt là ít hơn (trừ khi bạn có nhiều quy tắc áp dụng cho nhiều trang tính với bộ dữ liệu khổng lồ).
Tuy nhiên, bạn nên xóa định dạng có điều kiện khỏi bất kỳ tập dữ liệu nào mà bạn không cần.
Ngoài kích thước, còn có một lý do khác để loại bỏ định dạng có điều kiện – TỐC ĐỘ.
Định dạng có điều kiện là dễ bay hơi và bất cứ khi nào có bất kỳ thay đổi nào trong trang tính, nó tính toán lại. Điều này có thể dẫn đến một bảng tính chậm hơn.
Kết quả kiểm tra
Tôi đã tạo một tệp với 27,2 triệu điểm dữ liệu và áp dụng 5 quy tắc định dạng có điều kiện cho nó.
Sự khác biệt về kích thước dường như chỉ là một vài KB.
Mẹo tăng cường hiệu năng Excel
Trong khi các phương pháp trên sẽ giúp bạn giảm kích thước tệp Excel, dưới đây là một số mẹo bổ sung để cải thiện hiệu suất của Sổ làm việc Excel:
- Tránh sử dụng các công thức dễ bay hơi. Ngoài việc tăng kích thước tập tin, các công thức dễ bay hơi cũng khiến bạn làm việc chậm. Ví dụ về các hàm dễ bay hơi bao gồm RAND , TODAY , OFFSET ,INDIREC , v.v.
- Nếu bạn có nhu cầu tạo tóm tắt của tập dữ liệu lớn, thay vì sử dụng công thức, hãy xem liệu bạn có thể sử dụng Bảng tổng hợp thay thế không. Bảng Pivot nhanh và rất trực quan so với công thức.
- Nếu bạn trích xuất dữ liệu từ nhiều nguồn hoặc kết hợp dữ liệu từ nhiều tệp , hãy sử dụng Power Query để nhận và chuyển đổi dữ liệu. Bạn có thể lưu dữ liệu dưới dạng kết nối Power Query thay vì giữ nó trong bảng tính. Bạn có thể dễ dàng tạo Pivot Table bằng cách sử dụng các kết nối được tạo bằng Power Query.
- Chuyển đổi dữ liệu bảng của bạn thành một bảng Excel và sử dụng bảng Excel trong các công thức. Nó làm cho sổ làm việc của bạn hoạt động nhanh hơn và các công thức được tạo bằng cách sử dụng các tham chiếu có cấu trúc Bảng Excel dễ quản lý hơn (vì chúng trực quan hơn).
- Tránh tham chiếu toàn bộ hàng hoặc cột trong một công thức. Điều này sẽ giúp công thức của bạn hiệu quả hơn.
- Nếu bạn có nhiều công thức trong sổ làm việc của mình, hãy tắt tính toán Tự động và chuyển sang tính toán thủ công. Bằng cách này, mỗi lần có một sự thay đổi trong bảng tính, các công thức sẽ không tính toán lại. Bạn có thể thực hiện thay đổi này bằng cách chuyển đến tab Công thức và thay đổi ‘Tùy chọn Tính toán’.
Hy vọng bạn tìm thấy phương pháp và mẹo trong hướng dẫn này hữu ích.
Tôi có thể thêm những gì khác vào hướng dẫn này để làm cho nó hữu ích hơn? Xin vui lòng cho tôi biết trong phần ý kiến.
DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.Cám ơn.
