Khi làm việc với tập dữ liệu lớn, tốc độ bảng tính bị ảnh hưởng. Việc quản lý sổ làm việc Excel với hàng nghìn hàng và các tính toán phức tạp là một thách thức. Bạn cần tối ưu hóa bảng tính của mình để làm việc với tập dữ liệu lớn. Tối ưu hóa bảng tính của bạn đảm bảo hiệu suất mượt mà, thời gian tải nhanh hơn và hiệu quả tăng lên.
Trong bài viết này, chúng tôi sẽ giải thích cách bạn có thể tối ưu hóa bảng tính để cải thiện hiệu suất của các sổ làm việc lớn.
1. Loại bỏ Dữ liệu Không Cần Thiết
Giữ sổ làm việc của bạn sạch sẽ, xóa các hàng, cột và sheet không sử dụng, không mong muốn. Chỉ giữ dữ liệu bạn cần.
Xóa Hàng và Cột Trống:
Một tập dữ liệu có thể chứa hàng và cột trống không cần thiết. Bạn có thể sử dụng tính năng Chuyển đến Đặc biệt để xác định và xóa chúng nhanh chóng.
- Chọn phạm vi dữ liệu của bạn.
- Nhấn Ctrl + G >> chọn Đặc biệt >> chọn Trống.
- Xóa các hàng hoặc cột được làm nổi bật.
Điều này giảm kích thước tệp và cải thiện tốc độ tính toán lại.
2. Tối Ưu Hóa Công thức
2.1. Thay thế Các Hàm Volatile
Các hàm volatile tính toán lại mỗi khi sổ làm việc được mở hoặc thay đổi. Thay thế các hàm volatile bằng các hàm non-volatile khi có thể.
Dưới đây là danh sách các hàm volatile phổ biến:
- NOW()
- TODAY()
- RAND()
- OFFSET()
- INDIRECT()
Thay thế NOW bằng Giá trị Tĩnh
Thay vì sử dụng hàm NOW để hiển thị ngày và giờ hiện tại, hãy sử dụng phím tắt bàn phím. Hàm NOW tính toán lại với mọi thay đổi.
- Bạn có thể sử dụng Ctrl + ; để chèn ngày hiện tại dưới dạng giá trị tĩnh.
2.1. Đơn giản hóa Công thức Mảng
Bạn sẽ cần sử dụng Công thức Mảng hiệu quả. Mặc dù công thức mảng mạnh mẽ nhưng làm chậm sổ làm việc. Hạn chế việc sử dụng công thức mảng với sự giúp đỡ của cột trợ giúp.
Thay vì sử dụng SUM và IF riêng biệt, bạn có thể sử dụng hàm SUMIF.
Công thức Mảng:
=SUM(IF(A1:A1000>10, B1:B1000, 0))
Đối với bảng tính lớn, SUMIF là người chiến thắng rõ ràng về hiệu suất và sự đơn giản. Nó giảm thiểu việc sử dụng tài nguyên, tránh sự phức tạp không cần thiết và giữ sổ làm việc của bạn sạch sẽ.
=SUMIF(A1:A1000, ">10", B1:B1000)
Nếu SUMIF không khả dụng, bạn có thể sử dụng cột trợ giúp.
Thay thế bằng Cột Trợ giúp Với Tối Ưu Hóa:
- Chèn một cột mới và thêm:
- Sau đó, sử dụng công thức sau:
Điều này chia công thức mảng thành các bước nhỏ hơn, ít tốn tài nguyên hơn.
Giảm Phạm vi Tham chiếu:
- Thay vì tham chiếu toàn bộ cột >> A:A.
- Bạn có thể sử dụng phạm vi ô cụ thể hoặc phạm vi đã định nghĩa>> A1:A1000.
3. Sử Dụng Định dạng Có Điều kiện Thận Trọng
Thay vì áp dụng định dạng cho toàn bộ cột, hãy giới hạn nó trong phạm vi dữ liệu thực tế:
- Chỉ chọn phạm vi dữ liệu (ví dụ: A1:A100).
- Áp dụng quy tắc định dạng có điều kiện của bạn ở đây, tránh chi phí không cần thiết.
4. Giảm Liên kết Bên Ngoài
Thay vì nhập dữ liệu từ sổ làm việc bên ngoài bằng cách liên kết, bạn có thể sử dụng Power Query để nhập và biến đổi dữ liệu trong sổ làm việc của mình:
- Vào tab Dữ liệu >> nhấp Lấy Dữ liệu >> chọn Từ Tệp >> chọn Từ Sổ làm việc.
- Tải dữ liệu cần thiết vào tệp hiện tại của bạn.
Điều này giảm sự phụ thuộc vào tệp bên ngoài và tăng tốc truy xuất dữ liệu.
5. Điều chỉnh Chế độ Tính toán
Nếu bạn đang làm việc với tập dữ liệu nhỏ thì bạn có thể sử dụng tùy chọn tính toán tự động. Nhưng khi bạn đang xử lý dữ liệu lớn với các công thức phức tạp thì hãy sử dụng cài đặt tính toán lại thủ công.
Đối với sổ làm việc với nhiều công thức:
- Vào tab Tệp >> chọn Tùy chọn.
- Chọn Công thức >> từ Tùy chọn Tính toán >> chọn Thủ công.
- Nhấp OK.
- Nhấn F9 để tính toán lại chỉ khi cần thiết.
Điều này cho phép bạn làm việc trên sheet của mình mà không bị trễ do tính toán lại tự động.
Tắt Tính toán Lặp
Bạn cũng có thể tắt tính toán lặp có thể gây ra tính toán lại không cần thiết.
- Tắt Bật tính toán lặp.
- Nhấp OK.
6. Sử Dụng Bảng Dữ liệu và Bảng Pivot
Để tạo tóm tắt động, hãy sử dụng tính năng bảng pivot, thay vì tính toán hoặc cập nhật bảng tóm tắt thủ công.
- Chọn dữ liệu của bạn.
- Vào tab Chèn >> chọn Bảng Pivot.
- Kéo các trường vào hàng và cột để tạo tóm tắt động.
Bảng pivot tự động tính tổng dựa trên danh sách trường. Nó nhanh hơn để cập nhật và xử lý tập dữ liệu lớn hiệu quả hơn.
7. Tối Ưu Hóa Thiết kế Sổ làm việc
Để tối ưu hóa sổ làm việc của bạn, bạn có thể sử dụng Phạm vi Được đặt tên để tham chiếu đến phạm vi ô động thay vì tham chiếu đến toàn bộ cột hoặc công thức mảng.
Giả sử bạn đang làm việc với tập dữ liệu bán hàng tăng dần theo thời gian. Thay vì tham chiếu đến các phạm vi cố định như A1:A1000, hãy sử dụng phạm vi được đặt tên động:
- Vào tab Công thức >> chọn Trình quản lý Tên >> chọn Mới.
- Định nghĩa tên: SalesData.
- Tham chiếu đến: Chèn công thức sau:
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)
Công thức này sẽ tự động cập nhật bất kỳ công thức hoặc biểu đồ hiện có nếu dữ liệu tăng lên hoặc bị xóa.
8. Nén Kích thước Tệp
Để tối ưu hóa tệp, bạn có thể giảm kích thước hình ảnh và kích thước tệp bên ngoài.
Nếu sổ làm việc của bạn chứa hình ảnh:
- Chọn hình ảnh.
- Vào tab Định dạng Hình ảnh >> chọn Nén Hình ảnh.
- Chọn tùy chọn độ phân giải thấp hơn (ví dụ: Web hoặc Email) để giảm kích thước tệp mà không làm giảm chi tiết cần thiết.
9. Sử Dụng Công Cụ Nâng Cao
Sử Dụng Power Query: Trình chỉnh sửa Power Query có nhiều tính năng tích hợp để nhập và làm sạch dữ liệu, giảm nhu cầu về các công thức phức tạp.
Bật Mô hình Dữ liệu: Đối với tập dữ liệu lớn, bạn có thể sử dụng Mô hình Dữ liệu tích hợp của Excel để tạo mối quan hệ và thực hiện tính toán.
Sử Dụng VBA cho Các Tác Vụ Lặp Lại: Tự động hóa các quy trình lặp lại với VBA để giảm độ phức tạp của sổ làm việc.
10. Tắt Các Tiện ích Bổ sung Không Cần Thiết
Đừng bật tiện ích bổ sung nếu bạn không yêu cầu nó thường xuyên. Mỗi khi bạn mở Excel, nó cài đặt tất cả các tiện ích bổ sung.
- Vào tab Tệp >> chọn Tùy chọn.
- Từ Tùy chọn Excel >> chọn Tiện ích bổ sung
- Xác định và tắt bất kỳ tiện ích bổ sung không thiết yếu nào dưới Quản lý COM Add-ins.
- Gỡ bỏ Tiện ích bổ sung không sử dụng.
Điều này giải phóng tài nguyên, đặc biệt khi làm việc với bảng tính lớn.
11. Bảo Trì Định Kỳ
Để xóa lỗi và liên kết bị hỏng, hãy sử dụng tính năng Tìm và Thay thế. Bạn có thể loại bỏ các sự bất thường và lỗi bằng tính năng này.
Để sử dụng Tìm và Thay thế để xóa lỗi:
- Vào tab Trang chủ >> từ Tìm & Chọn >> chọn Tìm/Thay thế.
- Gõ tên lỗi trong hộp Tìm.
- Thay thế tất cả các lỗi hoặc giải quyết các liên kết gây ra chúng.
Sau khi thực hiện các tối ưu hóa, hãy kiểm tra sổ làm việc để đảm bảo chức năng không bị ảnh hưởng.
Kết luận
Tối ưu hóa bảng tính là một nhiệm vụ đơn giản, sổ làm việc lớn không nhất thiết phải phức tạp. Bạn có thể làm theo các kỹ thuật được đề cập với các bước thực tế để cải thiện hiệu suất của các sổ làm việc lớn. Dù là tối ưu hóa công thức, giảm kích thước dữ liệu hay sử dụng công cụ Excel nâng cao, các phương pháp này sẽ giúp bạn chạy mượt mà các bảng tính của mình.
Nhận bài tập Excel nâng cao miễn phí kèm lời giải!