7 cài đặt Excel ẩn làm giảm hiệu suất trong sổ làm việc lớn

Khi sổ làm việc Excel bắt đầu bị treo, tính toán lại mãi mãi hoặc bị lag mỗi khi bạn cuộn, hầu hết mọi người đều đổ lỗi cho các công thức phức tạp hoặc quá nhiều dữ liệu. Đôi khi điều đó là đúng. Nhưng cũng như thường lệ, nguyên nhân thực sự là do một số cài đặt Excel đang hoạt động chống lại bạn ở chế độ nền. Sổ làm việc Excel lớn có thể trở nên chậm chạp do các cài đặt và tính năng ẩn tiêu tốn tài nguyên một cách không cần thiết. Nếu bạn đang xử lý các tập dữ liệu lớn, công thức phức tạp hoặc nhiều trang tính, việc điều chỉnh các cài đặt này có thể cải thiện đáng kể hiệu suất.

Trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn bảy cài đặt Excel có thể bí mật làm chậm các sổ làm việc lớn, cùng với các bước thực tế để xác định và khắc phục chúng. Chúng tôi sẽ đề cập đến các chế độ tính toán, phần bổ trợ, định dạng có điều kiện, v.v.

1. Chế độ tính toán tự động – Luôn luôn tính toán lại mọi thứ

Theo mặc định, Excel thường chạy ở chế độ Tính toán tự động. Điều đó có nghĩa là mỗi khi bạn chỉnh sửa một giá trị, Excel sẽ tính toán lại tất cả các công thức phụ thuộc ngay lập tức. Trong một cuốn sổ làm việc nhỏ, điều đó thật tiện lợi. Trong một sổ làm việc lớn, nó có thể khiến mỗi lần nhấn phím trở nên tốn kém.

Nếu tệp của bạn bao gồm hàng nghìn công thức, tra cứu, mảng động, tóm tắt dựa trên trục hoặc hàm biến đổi, việc tính toán lại tự động có thể kích hoạt phản ứng dây chuyền ngay cả sau một chỉnh sửa nhỏ. Thay đổi một ô đầu vào và Excel có thể bắt đầu tính toán lại một nửa sổ làm việc.

Tại sao nó làm mọi thứ chậm lại:

Các mô hình lớn thường có sự phụ thuộc công thức sâu sắc. Tính toán tự động buộc Excel phải làm mới chúng liên tục, ngay cả khi bạn vẫn đang thực hiện chỉnh sửa. Trong các tệp lớn, ngay cả những thay đổi nhỏ cũng có thể kích hoạt việc tính toán lại toàn bộ và mức sử dụng CPU tăng đột biến.

Sửa:

• Đi tới Tệp tab>> chọn Tùy chọn>> chọn Công thức
• Trong Tùy chọn tính toán , chuyển từ Tự động sang Thủ công>> nhấp vào OK

• Hoặc từ dải băng:
  • Chuyển tới Công thức tab>> chọn Tùy chọn tính toán>> chọn Thủ công

• Để tính toán lại theo cách thủ công khi cần:
  • F9 tính toán lại tất cả các sổ làm việc đang mở
  • Shift + F9 tính toán lại bảng tính đang hoạt động
  • Ctrl + Alt + F9 buộc phải tính toán lại toàn bộ

Chỉ riêng thay đổi này có thể giảm độ trễ từ 50% đến 80% trong các tệp nặng về công thức.

Hãy nhớ: Đừng để chế độ Thủ công vĩnh viễn nếu người khác sử dụng tệp vì nó có thể gây ra lỗi nghiêm trọng. Thêm ghi chú nổi bật hoặc nhớ chuyển về Tự động trước khi giao tệp cho người khác, nếu không họ có thể thấy kết quả lỗi thời.

Mẹo chuyên nghiệp: Để kiểm soát có chọn lọc hơn, hãy sử dụng Tự động ngoại trừ bảng dữ liệu nếu bạn có bảng dữ liệu không cần cập nhật liên tục.

2. Phần bổ trợ không cần thiết – Chạy trong nền mà bạn không bao giờ sử dụng

Bổ trợ Excel là một trong những nguyên nhân bị bỏ qua nhiều nhất khiến hiệu suất chậm. Phần bổ trợ tải cùng ứng dụng và có thể kết nối với các sự kiện như mở sổ làm việc, thay đổi ô, tính toán lại và lưu. Ngay cả những phần bổ trợ mà bạn đã quên cũng có thể chặn những sự kiện này và hoạt động âm thầm ở chế độ nền mỗi khi bạn tương tác với dữ liệu của mình.

Các phần bổ trợ phổ biến bao gồm các công cụ cũ của công ty, phần bổ trợ Power Pivot COM, Analysis ToolPak (nếu không sử dụng), Bộ giải, thư viện biểu đồ của bên thứ ba và các công cụ của bên thứ ba như trình chuyển đổi PDF.

Tại sao nó làm mọi thứ chậm lại:

Ngay cả khi bạn không tích cực sử dụng chúng, các phần bổ trợ vẫn có thể tiêu tốn bộ nhớ và ảnh hưởng đến hiệu suất của sổ làm việc. Chúng có thể tăng thời gian khởi động Excel, sử dụng RAM ở chế độ nền, nối vào các sự kiện của sổ làm việc và tương tác với các công thức, kết nối dữ liệu hoặc dải băng, gây ra sự chậm trễ ngay cả khi chúng ở chế độ rảnh.

Trong một sổ làm việc nhẹ, bạn có thể không bao giờ nhận thấy. Trong một sổ làm việc nặng, chi phí bổ sung trở nên rõ ràng.

Sửa:

• Mở Tệp>> chọn Tùy chọn>> chọn Phần bổ trợ
• Mở rộng phần Quản lý thả xuống>> chọn Phần bổ trợ COM hoặc Phần bổ trợ Excel>> nhấp vào Đi

• Kiểm tra tất cả các phần bổ trợ và bỏ chọn mọi phần bổ trợ mà bạn không thường xuyên sử dụng
  • Vô hiệu hóa Analysis ToolPak nếu bạn không sử dụng các công cụ thống kê của nó

• Bạn luôn có thể kích hoạt lại chúng sau; bỏ chọn không gỡ cài đặt chúng
• Khởi động lại Excel và kiểm tra hiệu suất
• Nếu phần bổ trợ quan trọng, hãy kiểm tra các bản cập nhật
  • Mở Tệp>> chọn Tài khoản>> chọn Tùy chọn cập nhật , vì các phiên bản lỗi thời thường có lỗi

Việc vô hiệu hóa chỉ một phần bổ trợ cồng kềnh có thể giúp giảm thời gian tải vài giây.

3. Định dạng có điều kiện bị lạm dụng – Quy tắc áp dụng cho toàn bộ cột

Định dạng có điều kiện là một trong những tính năng tốt nhất của Excel, nhưng nó cũng là một trong những nguyên nhân phổ biến nhất khiến sổ làm việc bị chậm. Thật dễ dàng để tạo một vài quy tắc, sao chép một trang tính, sao chép các quy tắc đó nhiều lần và kết thúc bằng một sổ làm việc liên tục đánh giá lại logic định dạng trên toàn bộ phạm vi. Vấn đề không phải ở bản thân các quy tắc định dạng; đó là nơi chúng được áp dụng.

Tại sao nó làm mọi thứ chậm lại:

Excel có thể cần kiểm tra lại các quy tắc định dạng bất cứ khi nào dữ liệu thay đổi, trang tính được vẽ lại hoặc công thức được tính toán lại. Khi bạn áp dụng quy tắc cho một cột đầy đủ, Excel sẽ đánh giá quy tắc đó cho hơn một triệu ô mỗi khi trang tính tính toán lại, ngay cả khi hầu hết các ô đó đều trống. Định dạng có điều kiện thường gây ra độ trễ trong quá trình cuộn, lọc và chỉnh sửa, ngay cả khi bản thân các công thức không đặc biệt phức tạp.

Sửa:

• Chọn phạm vi có định dạng
• Truy cập Trang chủ tab>> chọn Định dạng có điều kiện>> chọn Quản lý quy tắc

• Xem lại và xóa các quy tắc thừa hoặc kết hợp các quy tắc tương tự
• Giới hạn quy tắc ở phạm vi cụ thể thay vì toàn bộ trang tính hoặc cột
  • Kiểm tra Áp dụng cho cột cho mỗi quy tắc
  • Thay thế phạm vi toàn bộ cột như =$A:$M bằng phạm vi giới hạn như =$A$2:$M$5000

• Nếu dữ liệu của bạn tăng trưởng linh hoạt, hãy sử dụng Bảng Excel. Định dạng có điều kiện được áp dụng cho một cột trong bảng sẽ tự động nằm trong phạm vi kích thước thực tế của bảng
• Tránh các quy tắc dựa trên công thức khi có thể; sử dụng các tùy chọn tích hợp sẵn như Greater Than hoặc Trùng lặp để xử lý nhanh hơn
  • Ví dụ:một quy tắc như =COUNTIF($B:$B,A1)>1 được đánh giá theo từng ô và có thể nặng hơn đáng kể so với quy tắc dựa trên giá trị

• Để xóa tất cả các quy tắc:
  • Truy cập Trang chủ tab>> chọn Định dạng có điều kiện>> chọn Xóa quy tắc>> chọn Xóa quy tắc khỏi toàn bộ trang tính

Bẫy phổ biến: Sao chép và dán các ô đã có định dạng có điều kiện là một trong những cách phổ biến nhất mà các quy tắc vô tình nhân lên. Theo thời gian, sổ làm việc có thể tích lũy hàng chục quy tắc gần như trùng lặp được áp dụng cho các phạm vi hơi khác nhau. Định kỳ kiểm toán và hợp nhất chúng; không có gì lạ khi tìm thấy 200 quy tắc dư thừa trở lên trong một sổ làm việc dành cho người trưởng thành.

Ngoài ra, hãy cân nhắc việc đơn giản hóa logic quy tắc hoặc chuyển logic phức tạp vào các cột trợ giúp, sau đó định dạng dựa trên các kết quả đó. Việc kiểm tra và giảm số lượng quy tắc có thể cải thiện đáng kể khả năng phản hồi của sổ làm việc.

4. Hàm biến động trong công thức – Kích hoạt tính toán lại đầy đủ sau mỗi lần chỉnh sửa

Một số hàm Excel nhất định được phân loại là không ổn định, có nghĩa là chúng tính toán lại mỗi khi có bất kỳ điều gì trong sổ làm việc thay đổi, bất kể dữ liệu đầu vào của chúng có thay đổi hay không. Các hàm như TODAY(), NOW(), RAND(), OFFSET() và INDIRECT() đều thuộc loại này.

Những người phạm tội phổ biến nhất:

• NOW() và TODAY():cập nhật mỗi lần tính toán lại
• RAND() và RANDBETWEEN():liên tục tạo ra các giá trị mới
• OFFSET():phổ biến trong các công thức phạm vi động
• INDIRECT():linh hoạt nhưng cực kỳ chậm trên quy mô lớn
• CELL() và INFO():hiếm khi cần thiết và thường tốn kém

Tại sao nó làm mọi thứ chậm lại:

Các chức năng này có thể buộc tính toán lại nhiều lần, ngay cả đối với những chỉnh sửa nhỏ dường như không liên quan. Điều đó khiến chúng đặc biệt nguy hiểm trong các sách bài tập vốn đã chịu áp lực từ các chuỗi công thức lớn. Một công thức INDIRECT() tham chiếu một phạm vi có 10.000 hàng, được sao chép trên 50 cột, có thể một tay khiến sổ làm việc có cảm giác chậm kinh khủng.

Sửa:

• Chuyển đến Công thức tab>> chọn Hiển thị công thức (Ctrl + `)

• Nhấn Ctrl + F để tìm kiếm các hàm dễ biến đổi trong công thức
• Thay thế chúng nếu có thể:
  • Sử dụng ngày tĩnh thay vì HÔM NAY()
  • Thay thế phạm vi động dựa trên OFFSET() bằng tham chiếu bảng có cấu trúc như Table1[Column]
  • Thay thế INDIRECT() bằng tham chiếu trực tiếp hoặc INDEX() khi phạm vi không cần phải điều khiển bằng văn bản

• Nếu không thể tránh khỏi, hãy tách chúng trong một ô duy nhất và tham chiếu ô đó đến nơi khác
• Đối với RAND(), hãy cân nhắc việc tạo các giá trị một lần và dán chúng dưới dạng số tĩnh
  • Sao chép>> Dán đặc biệt>> Giá trị

Việc giảm thiểu các công thức dễ biến đổi có thể ngăn chặn việc tính toán lại liên tục và làm cho sổ làm việc của bạn trở nên gọn gàng hơn nhiều.

5. Định dạng không được sử dụng trong toàn bộ bảng tính

Excel theo dõi định dạng trên toàn bộ trang tính. Việc áp dụng định dạng như phông chữ, màu sắc hoặc đường viền cho toàn bộ hàng, cột hoặc trang tính có thể làm tăng kích thước tệp và làm chậm quá trình hiển thị, ngay cả khi những ô đó không được sử dụng. Nếu định dạng vượt xa tập dữ liệu thực tế của bạn thì cả kích thước tệp và chi phí tính toán lại đều có thể tăng lên.

Tại sao nó làm mọi thứ chậm lại:

Excel có thể sẽ theo dõi định dạng cho hàng triệu ô, điều này làm tăng mức sử dụng bộ nhớ và làm chậm thời gian lưu và tải.

Sửa:

• Xác định các vùng được định dạng:
  • Nhấn Ctrl + Kết thúc để nhảy tới ô được sử dụng cuối cùng. Excel bao gồm các ô được định dạng ở đây, ngay cả khi chúng trống
• Chọn các hàng và cột không sử dụng ngoài dữ liệu của bạn
  • Nhấp chuột phải vào các hàng bên dưới hàng dữ liệu cuối cùng của bạn và chọn Xóa
• Xóa định dạng:
  • Chọn các dải ô chưa sử dụng
  • Truy cập Trang chủ tab>> chọn Chỉnh sửa>> chọn Xóa>> chọn Xóa định dạng

• Để đặt lại phạm vi đã sử dụng của trang tính, hãy lưu, đóng và mở lại sổ làm việc sau khi xóa các hàng và cột thừa
• Đối với nhiều trang tính, hãy sử dụng VBA nếu bạn cảm thấy thoải mái với nó:
  • Nhấn Alt + F11 , chèn một mô-đun và chạy mã để xóa định dạng khỏi các vùng không sử dụng

ActiveSheet.UsedRange.ClearFormats

Việc xóa các định dạng không sử dụng có thể thu nhỏ đáng kể kích thước tệp cũng như tăng tốc độ mở và lưu.

6. Liên kết bên ngoài và kết nối dữ liệu

Các liên kết tới sổ làm việc khác hoặc các nguồn bên ngoài, chẳng hạn như cơ sở dữ liệu, có thể tự động làm mới dữ liệu và truy vấn thông tin ở chế độ nền.

Tại sao nó làm mọi thứ chậm lại:

Sổ làm việc được liên kết với các tệp khác buộc Excel phải kiểm tra các tham chiếu bên ngoài, làm mới kết nối và tính toán lại các phần phụ thuộc. Các công thức bên ngoài như ='[Sales2024.xlsx]Sheet1′!A2 có thể làm chậm hiệu suất đáng kể nếu tệp được liên kết có dung lượng lớn hoặc được lưu trữ trên ổ đĩa mạng. Liên kết bị hỏng hoặc chậm cũng có thể gây ra thời gian chờ và việc tự động làm mới có thể làm gián đoạn công việc của bạn đối với các tệp lớn.

Sửa:

• Đi tới Dữ liệu tab>> chọn Truy vấn &Kết nối>> chọn Chỉnh sửa liên kết
• Ngắt các liên kết không cần thiết bằng cách nhấp vào Ngắt tất cả

• Đối với kết nối dữ liệu:
  • Đi tới Dữ liệu tab>> chọn Truy vấn &Kết nối
  • Nhấp chuột phải vào truy vấn>> bỏ chọn Bật làm mới nền

• Chuyển đổi dữ liệu ngoài thành giá trị tĩnh:
  • Làm mới một lần rồi Sao chép>> Dán đặc biệt>> Giá trị
• Tránh liên kết tới ổ đĩa mạng khi có thể; thay vào đó hãy sao chép dữ liệu nguồn cục bộ

Việc phá vỡ các liên kết không cần thiết có thể loại bỏ tình trạng treo ngẫu nhiên, đặc biệt là trong môi trường dùng chung.

7. Lưu và mở cài đặt – Làm cho các tệp lớn có cảm giác nặng hơn

Không phải tất cả tình trạng chậm đều xảy ra trong khi chỉnh sửa. Đôi khi sự thất vọng thực sự xuất hiện khi mở, lưu hoặc đóng sổ làm việc. Điều đó thường trỏ đến cài đặt xử lý tệp hơn là logic công thức.

Thủ phạm phổ biến: Một số điều thường đóng góp:

• Lưu Tự động Phục hồi quá thường xuyên
• Cập nhật các liên kết bên ngoài khi mở
• Dải ô được đặt tên cũ và đối tượng ẩn
• Chế độ tương thích trong tệp .xls cũ
• Lựa chọn định dạng tệp không hiệu quả

Tại sao nó làm mọi thứ chậm lại:

Sổ làm việc lớn mất nhiều thời gian hơn để ghi vào đĩa. Nếu Excel cũng thường xuyên tạo ảnh chụp nhanh khôi phục, kiểm tra liên kết bên ngoài hoặc duy trì hành vi tương thích cũ thì thời gian lưu và mở có thể trở nên kéo dài.

Sửa:

• Kiểm tra tần suất Tự động Phục hồi:
  • Đi tới Tệp tab>> chọn Tùy chọn>> chọn Lưu
  • Nếu Tự động Phục hồi được đặt quá mạnh, Excel có thể tạm dừng thường xuyên hơn mức cần thiết khi bạn làm việc với một tệp rất lớn
• Kiểm tra các liên kết bên ngoài. Nếu sổ làm việc của bạn chứa các tệp được liên kết, hãy quyết định xem các liên kết đó có còn cần cập nhật tự động hay không
• Kiểm tra định dạng tệp. Nếu bạn vẫn đang làm việc trong .xls , gần như chắc chắn bạn đang làm cho Excel làm việc vất vả hơn mức cần thiết
• Đối với sổ làm việc nội bộ lớn, hãy cân nhắc lưu dưới dạng .xlsb thay vì .xlsx . Định dạng nhị phân thường:
  • Mở nhanh hơn
  • Tiết kiệm nhanh hơn
  • Sử dụng ít dung lượng ổ đĩa hơn
  • Xử lý sổ làm việc lớn hiệu quả hơn

Nó không phải lúc nào cũng là lựa chọn phù hợp cho mọi quy trình làm việc, nhưng đối với các tệp nội bộ nhạy cảm với hiệu suất, nó có thể tạo ra sự khác biệt thực sự. Sổ làm việc nhị phân thường mở và lưu nhanh hơn trong khi sử dụng ít dung lượng ổ đĩa hơn. Sự đánh đổi chính là khả năng di chuyển giảm đi đôi chút với một số công cụ không phải của Microsoft.

Mẹo cuối cùng để đạt hiệu suất cao nhất

• Theo dõi thời gian tính toán lại bằng Công thức> Tính toán> Tính toán ngay trước và sau khi thay đổi
• Sử dụng Tệp> Thông tin> Kiểm tra sự cố> Kiểm tra tài liệu để xóa dữ liệu ẩn như thông tin cá nhân hoặc kiểu không sử dụng
• Đối với các tệp cực lớn, hãy cân nhắc chia sổ làm việc thành nhiều tệp hoặc sử dụng Power BI để phân tích
• Kiểm tra các thay đổi trên bản sao của sổ làm việc trước để tránh mất dữ liệu

Kết luận

Đây là bảy cài đặt Excel có thể bí mật làm chậm sổ làm việc lớn của bạn. Khi Excel chạy chậm, nhiều người bắt đầu viết lại công thức ngay lập tức. Đó không phải lúc nào cũng là bước đi đầu tiên tốt nhất. Hiệu suất của sổ làm việc lớn thường được định hình bởi các cài đặt ẩn trong nền:hành vi tính toán, phần bổ trợ, phạm vi định dạng có điều kiện, liên kết bên ngoài và tùy chọn lưu. Những điều này hiếm khi quan trọng trong một tập tin nhỏ. Trong một cái lớn, chúng rất quan trọng. Đó là lý do tại sao việc điều chỉnh hiệu suất trong Excel không chỉ là về các công thức thông minh hơn. Nó cũng liên quan đến việc loại bỏ chi phí im lặng. Bằng cách xử lý các cài đặt này, bạn có thể biến những bảng tính chậm chạp thành công cụ hiệu quả hơn nhiều.