Đây là video tóm tắt bài viết.
Để minh họa các lý do công thức Excel không tự tính toán lại đúng, và giải pháp, dùng bộ dữ liệu báo cáo bán hàng Tháng 1, 2, 3. Dữ liệu 5 cột: Sản phẩm, Doanh thu Tháng 1, Tháng 2, Tháng 3, và Tổng doanh thu.
Các công thức Excel không tự tính trong dữ liệu. Hãy khắc phục.
Lưu ý: Hướng dẫn dùng Microsoft Office 365. Vị trí lệnh có thể khác chút giữa các phiên bản.
Nguyên nhân 1 – Tùy chọn Calculation Options không phù hợp
Nếu Calculation Options đặt ở Manual, công thức Excel không tự tính.
Giải pháp – Đặt Calculation Options về Automatic
CÁC BƯỚC:
- Nhấp tab Formulas trên Ribbon.
- Nhấp mũi tên drop-down dưới Calculation Options nhóm Calculation.
- Chọn Automatic.
- Công thức giờ tự cập nhật.
Cùng kết quả đạt được qua hộp thoại Excel Options.
CÁC BƯỚC:
- Nhấp tab File trên Ribbon.
- Nhấp Options.
- Hộp thoại Excel Options hiện ra.
- Nhấp tab Formula.
- Đặt Workbook Calculation = Automatic.
- Nhấp OK.
Đọc thêm: [Đã giải quyết]: Công thức Excel không cập nhật cho đến khi lưu
Nguyên nhân 2 – Giá trị ô định dạng Text
Lý do thường gặp khác: vô tình định dạng ô chứa công thức thành Text.
Giải pháp – Chuyển giá trị Text sang định dạng Number
CÁC BƯỚC:
- Chọn ô E6 nhấn Ctrl + 1. Hộp thoại Format Cells hiện ra.
- Chọn tab Number.
- Chọn Number từ danh sách Category.
- Tích Use 1000 Separator(,).
- Nhấp OK.
Công thức Excel giờ tự tính.
Lưu ý: Có thể dùng định dạng Number hoặc General thay vì Text.
Đọc thêm: [Đã sửa!] Công thức không chạy trong Excel và hiển thị như Text
Nguyên nhân 3 – Dấu nháy đơn (Apostrophes)
Dữ liệu có dấu nháy đơn mắc kẹt trong vài ô.
Giải pháp – Xóa dấu nháy đơn
Dấu nháy đơn hoặc khoảng trắng đầu phải xóa thủ công từng ô.
CÁC BƯỚC:
- Chọn ô có dấu nháy đơn. Ví dụ ô C8. Dấu không hiển thị trong ô nhưng căn lề dữ liệu cho thấy định dạng sai.
- Nhấp vào Formula Bar.
- Nhấn Delete xóa dấu apostrophe thủ công.
- Nhấn Enter.
- Lặp lại xóa dấu nháy đơn khác trong các ô chứa chúng.
Công thức giờ tự tính.
Nguyên nhân 4 – Khoảng trắng đầu hoặc cuối
Dữ liệu có khoảng trắng đầu/cuối ngăn công thức tự tính.
Giải pháp – Xóa khoảng trắng đầu/cuối
CÁC BƯỚC:
- Chọn ô có khoảng trắng đầu/cuối.
- Nhấp Formula Bar.
- Nhấn Backspace xóa khoảng trắng thủ công.
- Nhấn Enter.
- Tương tự xóa khoảng trắng khác trong các ô chứa chúng.
Nguyên nhân 5 – Tham chiếu ô sai
Lỗi thường gặp: dùng sai tham chiếu tuyệt đối.
Ví dụ:
- Nhập =B5 vào ô và AutoFill xuống, kết quả =B6 được sao chép.
- Nhập =$B$5 và AutoFill xuống, kết quả =$B$5 được sao chép.
Trong hình, công thức tham chiếu tuyệt đối gây sai kết quả ô D17 và E17.
Giải pháp – Dùng tham chiếu ô phù hợp
Chỉ cần xóa ký hiệu dollar ($) khỏi công thức gây lỗi.
CÁC BƯỚC:
- Chọn ô C17 xóa ký hiệu dollar ($) khỏi công thức.
- AutoFill công thức đến cột E.
Lưu ý – Chuyển đổi Nhị phân sang Thập phân
Máy tính dùng nhị phân (kết hợp 1 và 0) lưu số. Con người dùng hệ thập phân cơ số 10. Do đó Excel chuyển đổi giữa nhị phân và thập phân, đôi khi gây chênh lệch nhỏ.
Độ chính xác tối đa Excel là 15 chữ số có nghĩa. Do chuyển đổi nhị phân-thập phân, có thể xảy ra lỗi làm tròn rất nhỏ. Với một số logic, tạo ra kết quả bất ngờ.
Nguyên nhân 6 – Số bị đặt trong dấu ngoặc kép
Số trong dấu ngoặc kép gây nhầm lẫn giữa text và số, công thức ngừng tự tính. Hình dưới, hàm SUM sai do lý do này.
- Lỗi do dấu ngoặc kép trong hàm IF, khiến số bị coi là text.
Giải pháp – Dùng đối số đúng trong công thức
Chỉ cần xóa dấu ngoặc kép khỏi đối số IF để ra kết quả đúng.
Đọc thêm: [Đã sửa!] Công thức SUM không hoạt động trong Excel
Mẹo khắc phục công thức không tự tính trong Excel
Mẹo 1 – Áp dụng định dạng ô phù hợp
Chọn sai định dạng ô cũng gây công thức không tự tính. Chọn định dạng phù hợp khắc phục lỗi.
CÁC BƯỚC:
- Chọn phạm vi C6:E16.
- Nhấp tab Home.
- Chọn định dạng phù hợp nhóm Number, ví dụ Currency cho ô của chúng ta.
Công thức giờ tính đúng.
Lưu ý:
Khi nhập 12/10 vào ô, muốn kết quả gì? Kết quả phép chia 12/10? Hay ngày 10 tháng 12? Mặc định Excel hiểu muốn ngày 10 tháng 12 năm hiện tại.
Chuyển đổi tự động phổ biến nhất trong Excel liên quan đến ngày. Trong Excel, ngày biểu diễn bằng số ngày kể từ 31/12/1899. Năm hiện tại 2023. Nhập 12/10, Excel hiểu ngày 10/12, trả về 45271 là số ngày đó.
Mẹo 2 – Bỏ chọn Circular Reference
Kích hoạt Circular Reference đôi khi gây rắc rối cho công thức tự tính. Tắt tính năng khắc phục.
CÁC BƯỚC:
- Vào tab Formula.
- Nhóm Formula Auditing, nhấp mũi tên drop-down dưới Error Checking.
- Tắt Circular Reference.
Công thức lỗi giờ tính đúng.
Mẹo 3 – Tắt lệnh Show Formulas
CÁC BƯỚC:
- Nhấp tab Formula.
- Nhóm Formula Auditing, nhấp Show Formula để tắt.
Công thức lỗi giờ tính đúng.
Tại sao dùng chế độ Manual Calculation trong Excel?
- Kiểm soát tính toán: Mặc định Excel tự tính lại mọi công thức khi ô thay đổi. Manual mode cho kiểm soát khi nào tính toán xảy ra.
- Cải thiện hiệu suất: Manual calculation cải thiện hiệu suất Excel, nhất là bảng tính lớn, phức tạp, tự tính lại sau mỗi thay đổi làm chậm Excel. Dùng manual, bạn thay đổi nhiều lần rồi tính lại một lần, tiết kiệm tài nguyên.
- Phát hiện lỗi & Khắc phục sự cố: Manual mode cho phép xem xét, kiểm tra công thức trước khi tính. Giúp phát hiện lỗi, không nhất quán. Bằng cách thủ công kích hoạt tính lại, bạn kiểm tra độ chính xác, debug vấn đề.
- Xác thực dữ liệu: Trong một số trường hợp, cần xác thực dữ liệu trước khi tính. Manual mode cho phép nhập, kiểm tra dữ liệu không kích hoạt tự tính. Đảm bảo dữ liệu chính xác, hoàn chỉnh trước khi tính, bảo toàn tính toàn vẹn.
- Tránh tham chiếu vòng: Tham chiếu vòng xảy ra khi công thức tham chiếu ô chứa nó, tạo vòng lặp vô hạn. Manual mode giúp xác định, giải quyết tham chiếu vòng bằng cách yêu cầu thủ công tính lại. Dễ dàng phát hiện, sửa tham chiếu vòng, ngăn lỗi tính toán.
- Quản lý tài nguyên: Tự động tính tiêu tốn tài nguyên máy, nhất là bảng lớn. Dùng manual mode, tiết kiệm tài nguyên hệ thống, giảm dùng CPU, tối ưu hiệu suất máy.
Lưu quan trọng: dùng manual mode, nhớ thủ công tính lại công thức để đảm bảo kết quả chính xác.
Cách ép công thức tính lại trong Workbook hoặc Worksheet Excel
Nếu cần giữ Calculation = Manual, có 2 cách ép tính lại: dùng nút Calculate trên Ribbon, hoặc phím tắt.
➤ Tính lại sheet đang hoạt động:
- Nhấn Shift + F9, hoặc
- Tab Formulas >> chọn Calculate Sheet nhóm Calculation.
➤ Tính lại toàn bộ workbook:
- Nhấn F9, hoặc
- Tab Formulas, chọn Calculate Now nhóm Calculation.
➤ Tính lại tất cả sheet trong mọi workbook mở, nhấn Ctrl + Alt + F9.
➤ Tính lại công thức cụ thể trên sheet:
- Chọn ô chứa công thức.
- Vào chế độ chỉnh sửa bằng F2 hoặc nhấp đúp ô.
- Nhấn Enter thực hiện tính lại.
Lưu ý quan trọng
- Nhấn phím “F9” để thủ công tính lại sheet đang hoạt động.
- Đảm bảo ô chứa công thức định dạng “General” hoặc định dạng phù hợp kết quả mong đợi. Định dạng sai gây tính sai hoặc không tính.
- Xác định và giải quyết lỗi tham chiếu vòng.
- Nếu công thức có tham chiếu ngoài đến workbook/file khác, đảm bảo file liên kết truy cập được và công thức tham chiếu đúng.
Câu hỏi thường gặp
1. Làm sao nếu nhấn F9 không tính lại công thức?
Trả lời: Nếu F9 không kích hoạt tính lại, kiểm tra công thức có tham chiếu ngoài không. Đảm bảo tham chiếu hợp lệ, file liên kết truy cập được. Kiểm tra cài đặt phụ thuộc tính toán hoặc cài đặt ngăn tự tính lại.
2. Cách khắc phục công thức không tính trong phạm vi ô cụ thể?
Trả lời: Nếu công thức không tính trong phạm vi cụ thể, kiểm tra quy tắc định dạng có điều kiện hoặc cài đặt xác thực dữ liệu có ngăn tính toán không. Xác thực ô định dạng đúng và không có ghi đè thủ công ngăn tự tính.
3. Ô hoặc sheet bảo vệ có ảnh hưởng tính toán công thức không?
Trả lời: Có, ô hoặc sheet bảo vệ có thể hạn chế tính toán công thức. Đảm bảo ô/sheet cần thiết không bị khóa hoặc bảo vệ nếu yêu cầu tự động tính.
Tải file thực hành
Bài viết liên quan
- Cách làm mới công thức trong Excel
- [Đã sửa!]: Công thức Excel không hiển thị kết quả đúng
- [Đã sửa!] Công thức Excel không chạy trên máy tính khác
- [Đã giải quyết:] Công thức Excel không chạy trừ khi nhấp đúp ô
- [Đã giải quyết]: Công thức mảng Excel không hiển thị kết quả
<< Quay lại: Công thức không chạy trong Excel | Công thức Excel | Học Excel
Nhận miễn phí bài tập Excel nâng cao có lời giải!