TL;DR: Cách khắc phục nhanh
Excel trả về lỗi #NUM! khi gặp phải một rào cản toán học. Nếu bạn cần giải pháp ngay lập tức, hãy kiểm tra ba phương diện sau:
- Tìm các phép toán vi phạm quy tắc: Bạn đang cố tính căn bậc hai của một số âm hay logarit của số không?
- Kiểm tra quy mô: Kết quả của bạn có thể quá lớn hoặc quá nhỏ. Excel sẽ dừng lại nếu một số vượt quá 1,79 x 10^308.
- Hỗ trợ các công thức tài chính: Các hàm như
IRRhoặcRATEcó thể cần một giá trị "đoán" (guess) để tìm ra kết quả.
Tại sao Excel dừng tính toán
Hãy coi lỗi này là cách Excel thông báo rằng công thức được nhập đúng cú pháp, nhưng kết quả không thể tính toán được. Nó thường xảy ra khi một con số quá cực đoan hoặc một hàm nhận dữ liệu mà nó không được thiết kế để xử lý.
1. Tràn tính toán (Giới hạn 308 chữ số)
Excel có một mức trần cố định. Nó sử dụng độ chính xác dấu phẩy động 64-bit, nghĩa là nó không thể xử lý các số lớn hơn 1.7976931348623158e+308. Nếu phép tính của bạn chỉ cần vượt quá con số này một chút, bạn sẽ nhận được lỗi #NUM!.
// Điều này gây ra lỗi #NUM! vì kết quả có tới 3.000 chữ số
=1000^1000
Mặc dù 1000 lũy thừa 1000 là một khái niệm hợp lệ trong sách giáo khoa toán học, nhưng nó quá lớn để bộ nhớ của Excel có thể lưu trữ dưới dạng một giá trị đơn lẻ.
2. Các phép toán không thể thực hiện
Các hàm như SQRT hoặc LOG không linh hoạt. Chúng có các quy tắc nghiêm ngặt về loại số mà bạn có thể đưa vào. Vi phạm các quy tắc này là nguyên nhân phổ biến nhất gây ra lỗi #NUM! trong các bảng tính hàng ngày.
- SQRT:
=SQRT(-25)thất bại vì số thực không có căn bậc hai âm. - LOG:
=LOG(-10)hoặc=LOG(0)sẽ thất bại ngay lập tức.
3. Công thức lặp không thể hội tụ
Các hàm tài chính như IRR (Tỷ suất hoàn vốn nội bộ) hoặc RATE không tìm ra đáp án ngay lập tức. Chúng sử dụng phương pháp thử và sai, bắt đầu với một dự đoán 10% và tinh chỉnh tối đa 20 lần. Nếu phép toán không chốt được kết quả trong khoảng đó, Excel sẽ ngừng thử.
4. Lỗi logic trong hàm DATEDIF
Hàm DATEDIF là một trường hợp đặc biệt. Nó được thiết kế để tính toán khoảng thời gian giữa hai ngày, nhưng nó chỉ hoạt động theo một chiều. Nếu ngày bắt đầu của bạn muộn hơn ngày kết thúc, công thức sẽ bị hỏng.
// Nếu A1 là ngày 1 tháng 1 năm 2024 và B1 là ngày 1 tháng 1 năm 2023
=DATEDIF(A1, B1, "d") // Trả về #NUM!
Cách giải quyết
Cách A: Xử lý đầu vào âm
Khi tính căn bậc hai trên dữ liệu có thể bao gồm các số âm, hãy sử dụng hàm ABS (giá trị tuyệt đối). Hàm này chuyển đổi các giá trị âm thành dương trước khi phép tính diễn ra.
// Sử dụng ABS để ngăn chặn lỗi
=SQRT(ABS(A1))
// Hoặc sử dụng IF để hiển thị một thông báo hữu ích
=IF(A1<0, "Đầu vào âm", SQRT(A1))
Cách B: Giải quyết lỗi lặp (IRR/RATE)
Nếu IRR thất bại, chuỗi dòng tiền có thể quá phức tạp đối với mức dự đoán 10% mặc định. Bạn có thể cung cấp điểm bắt đầu theo cách thủ công để giúp công cụ tính toán tìm ra hướng đi đúng.
// IRR tiêu chuẩn có thể thất bại với dữ liệu biến động
=IRR(A1:A20)
// IRR với dự đoán 5% (0.05) để điều hướng tính toán
=IRR(A1:A20, 0.05)
Cách C: Thay đổi quy mô số lớn
Bạn đang gặp giới hạn 10^308? Hãy thử thay đổi quy mô của mình. Thay vì tính toán các đơn vị thô, hãy chia đầu vào của bạn cho 1.000 hoặc 1.000.000. Làm việc với đơn vị "nghìn" hoặc "triệu" giúp các kết quả trung gian nằm trong vùng an toàn của Excel.
Cách D: Sửa thứ tự thời gian trong DATEDIF
Luôn đảm bảo ngày tháng của bạn theo đúng thứ tự. Bạn có thể sử dụng MIN và MAX để làm cho công thức "không thể sai sót", đảm bảo ngày sớm hơn luôn là điểm bắt đầu.
=DATEDIF(MIN(A1, B1), MAX(A1, B1), "d")
Xác minh bản sửa lỗi
Đừng chỉ giả định rằng lỗi đã biến mất. Hãy làm theo các bước sau để đảm bảo bảng tính của bạn ổn định:
- Kiểm tra áp lực (Stress Test): Nhập các giá trị cực đoan (như -1 hoặc 1.000.000) vào các ô nhập liệu của bạn để xem công thức có trụ vững không.
- Sử dụng công cụ Evaluate Tool: Truy cập tab Formulas và nhấp vào Evaluate Formula. Điều này cho phép bạn theo dõi từng bước tính toán để xem chính xác nơi con số trở nên không hợp lệ.
- Kiểm tra các lỗi ẩn: Sử dụng
=ISNUMERIC()cho kết quả của bạn để xác minh đó là một số hợp lệ mà các công thức khác có thể sử dụng.
Mẹo chuyên nghiệp: Sử dụng IFERROR một cách khôn ngoan
Bạn có thể sử dụng IFERROR để ẩn lỗi #NUM! và giữ cho các báo cáo của mình trông sạch sẽ. Ví dụ: =IFERROR(SQRT(A1), 0) sẽ hiển thị số 0 thay vì thông báo lỗi.
Tuy nhiên, hãy cẩn thận. Việc che giấu lỗi có thể ẩn đi các sai sót nhập liệu thực tế. Chỉ sử dụng IFERROR nếu bạn chắc chắn rằng kết quả không hợp lệ là một phần bình thường và được dự đoán trước trong tập dữ liệu của mình.