Khi làm việc với tập dữ liệu lớn, tốc độ của bảng tính sẽ bị ảnh hưởng. Thật khó để quản lý sổ làm việc Excel với hàng nghìn hàng và các phép tính phức tạp. 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 sẽ đảm bảo hiệu suất mượt mà hơn, thời gian tải nhanh hơn và tăng hiệu quả.
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 sổ làm việc lớn.
1. Xóa dữ liệu không cần thiết
Giữ sổ làm việc của bạn sạch sẽ và xóa các hàng, cột và trang tính không sử dụng, không mong muốn. Chỉ giữ lại 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 các hàng và cột trống không cần thiết. Bạn có thể sử dụng tính năng Go To Special để nhận dạng 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 Khoảng trống .
- Xóa các hàng hoặc cột được đánh dấu.
Điều này làm 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 biến động
Các hàm dễ bay hơi sẽ 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 dễ thay đổi bằng các hàm không thay đổi nếu có thể.
Đây là danh sách các hàm dễ bay hơi phổ biến:
- BÂY GIỜ()
- HÔM NAY()
- RAND()
- OFFSET()
- GIÁN TIẾP()
Thay thế NGAY bằng giá trị tĩnh
Thay vì sử dụng hàm NOW để hiển thị ngày giờ hiện tại, hãy sử dụng phím tắt. Hàm NOW tính toán lại sau 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 một cách hiệu quả. Mặc dù công thức mảng rất mạnh mẽ nhưng lại làm chậm sổ làm việc. Hạn chế sử dụng công thức mảng với sự trợ giúp của các cột trợ giúp.
Thay vì sử dụng riêng SUM và IF, 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 các bảng tính lớn, SUMIF rõ ràng là người chiến thắng về hiệu suất và tính đơ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ữ cho sổ làm việc của bạn luôn sạch sẽ.
=SUMIF(A1:A1000, ">10", B1:B1000)
Nếu không có SUMIF, bạn có thể sử dụng cột trợ giúp.
Thay thế bằng Cột trợ giúp bằng Tối ưu hóa:
- Chèn 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 được xác định>> A1:A1000.
3. Sử dụng định dạng có điều kiện một cách tiết kiệm
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 các quy tắc định dạng có điều kiện của bạn tại đây, tránh những 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à chuyển đổi dữ liệu trong sổ làm việc của mình:
- Chuyển đến tab Dữ liệu>> nhấp vào Nhận 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 làm giảm sự phụ thuộc vào các 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 một 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 có nhiều công thức:
- Đi tới Tệp tab>> 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 vào OK .
- Nhấn F9 chỉ tính toán lại khi cần thiết.
Điều này cho phép bạn làm việc trên trang tính của mình mà không bị chậm trễ do tính toán lại tự động gây ra.
Tắt tính toán lặp lại
Bạn cũng có thể tắt các phép tính lặp lại có thể gây ra những lần tính toán lại không cần thiết.
- Tắt Bật tính toán lặp lại .
- Nhấp vào OK .
6. Sử dụng Bảng dữ liệu và PivotTable
Để tạo bản tóm tắt động, hãy sử dụng tính năng bảng tổng hợp thay vì tính toán hoặc cập nhật bảng tóm tắt theo cách thủ công.
- Chọn dữ liệu của bạn.
- Đi tới Chèn tab>> chọn PivotTable .
- Kéo các trường vào hàng và cột để tạo bản tóm tắt động.
Bảng tổng hợp tự động tính tổng dựa trên danh sách trường. Việc cập nhật và xử lý các tập dữ liệu lớn hiệu quả hơn sẽ nhanh 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 động đến phạm vi ô 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ên động:
- Đi tới Công thức tab>> chọn Trình quản lý tên>> chọn Mới .
- Xác định tên:SalesData .
- Đề cập đế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 mọi 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 có chứa hình ảnh:
- Chọn hình ảnh.
- Chuyển đến Định dạng ảnh tab>> chọn Nén ảnh .
- Chọn tùy chọn có độ 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 ảnh hưởng đến các chi tiết cần thiết.
9. Sử dụng Công cụ nâng cao
Sử dụng Power Query: Trình soạn thảo Power Query có nhiều tính năng tích hợp sẵn khác nhau để nhập và làm sạch dữ liệu, giúp 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 các tập dữ liệu lớn, bạn có thể sử dụng Mô hình dữ liệu tích hợp sẵn của Excel để tạo mối quan hệ và thực hiện các phép tính.
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 đi lặp lại bằng VBA để giảm độ phức tạp của sổ làm việc.
10. Tắt các phần bổ trợ không cần thiết
Không bật phần bổ trợ nếu bạn không yêu cầu nó thường xuyên. Mỗi lần bạn mở Excel, nó sẽ cài đặt tất cả các phần bổ trợ.
- Đi tới Tệp tab>> chọn Tùy chọn .
- Từ Tùy chọn Excel>> chọn Phần bổ trợ
- Xác định và vô hiệu hóa mọi phần bổ trợ không cần thiết trong Quản lý phần bổ trợ COM .
- Xóa Phần bổ trợ 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 các 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 điểm 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:
- Truy cập Trang chủ tab>> từ Tìm và chọn >> chọn Tìm/Thay thế .
- Loại tên lỗi trong Tìm cái hộp.
- Thay thế tất cả lỗi hoặc giải quyết các liên kết gây ra lỗi.
Sau khi thực hiện 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 cần 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 sổ làm việc lớn. Cho 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ác công cụ Excel nâng cao, những phương pháp này sẽ giúp bạn chạy bảng tính của mình một cách suôn sẻ.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!