Hiểu và giải quyết lỗi #NUM trong Excel không chỉ là nhiệm vụ troubleshooting; đó là kỹ năng thiết yếu cho bất kỳ ai làm việc với Excel. Dù bạn điều hướng dữ liệu tài chính, nghiên cứu khoa học, hoặc quản lý kho, các lỗi công thức này có thể nổi lên và làm hỏng phân tích của bạn. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn qua các cách khác nhau để sửa lỗi #NUM.
Cũng hãy chắc chắn xem hướng dẫn của chúng tôi về cách sửa lỗi #N/A trong công thức Excel và lỗi sharing violation thường gặp.
Lỗi #NUM Là Gì Và Tại Sao Nó Xảy Ra?
Thông báo lỗi #NUM của Excel là một trong những lỗi phổ biến nhất. Nó sẽ được kích hoạt vì các lý do sau:
- Đối số đầu vào không hợp lệ. Tất cả đầu vào cần kiểu dữ liệu hợp lệ để Excel nhận diện và hàm có thể tính toán.
- Số quá lớn hoặc quá nhỏ. Excel có giới hạn về kích thước số nó có thể tính. Nếu công thức vượt quá giới hạn, sẽ dẫn đến lỗi #NUM.
- Phép toán không thể thực hiện như tìm căn bậc hai của số âm.
- Công thức lặp không thể hội tụ (converge). Nếu công thức lặp không tìm thấy kết quả hợp lệ, sẽ trả về lỗi #NUM.
Sửa Lỗi #NUM Do Đối Số Hàm Không Đúng
Lý do phổ biến nhất gây lỗi #NUM là đối số không hợp lệ hoặc kiểu dữ liệu sai. Nếu bạn nghi ngờ đây là nguyên nhân gây lỗi #NUM trong hàm của bạn, hãy kiểm tra kiểu dữ liệu và cú pháp công thức cho bất kỳ lỗi nào.
Ví dụ, nếu bạn dùng hàm DATE, Excel mong đợi bạn chỉ dùng số giữa 1 và 9999 cho đối số year. Nếu bạn cung cấp giá trị year ngoài phạm vi này, sẽ dẫn đến lỗi #NUM.
Tương tự, nếu bạn dùng hàm DATEDIF, ngày kết thúc cung cấp phải lớn hơn ngày bắt đầu. Bạn cũng có thể có các mục ngày bằng nhau. Nhưng nếu ngược lại, kết quả sẽ là lỗi #NUM.
Hãy xem ví dụ:
=DATEDIF(A2,B2,"d") tính sự khác biệt số ngày giữa hai ngày (trong ô A2 và B2). Kết quả sẽ là số nếu ngày trong ô A2 nhỏ hơn ngày trong ô B2. Nếu không phải trường hợp đó, như ví dụ dưới, kết quả sẽ là lỗi #NUM.
Sửa Lỗi #NUM Do Số Quá Lớn Hoặc Quá Nhỏ
Nếu công thức của bạn có đối số vượt quá giới hạn số của Excel, sẽ dẫn đến lỗi #NUM. Có, Microsoft Excel có giới hạn về kích thước số nó có thể tính. Để giải quyết, bạn phải điều chỉnh giá trị đầu vào sao cho kết quả rơi trong phạm vi cho phép.
Nếu bạn phải làm việc với số lớn như vậy, cân nhắc chia nhỏ tính toán thành các phần nhỏ. Điều này cho phép bạn dùng nhiều ô để đạt kết quả cuối cùng.
Dưới đây là giới hạn tính toán trong Microsoft Excel:
- Số âm nhỏ nhất là -2.2251E-308.
- Số dương nhỏ nhất là 2.2251E-308.
- Số âm lớn nhất là -9.99999999999999E+307.
- Số dương lớn nhất là 9.99999999999999E+307.
- Số âm lớn nhất cho phép qua công thức là -1.7976931348623158E+308.
- Số dương lớn nhất cho phép qua công thức là 1.7976931348623158E+308.
Nếu kết quả công thức bạn dùng nằm ngoài phạm vi này, kết quả bạn nhận sẽ là lỗi #NUM.
Hãy xem ví dụ.
Lưu ý: trong phiên bản Microsoft Excel mới chỉ số lớn mới gây lỗi #NUM. Số quá nhỏ sẽ ra 0 (định dạng general), hoặc 0.00E+00 (định dạng khoa học).
Nếu bạn không quen định dạng khoa học, hãy nhớ ví dụ, phần "E-20" dịch thành "nhân 10 mũ -20".
Sửa Lỗi #NUM Do Phép Tính Không Thể Thực Hiện
Lý do khác gây lỗi #NUM là nếu Excel coi phép tính không thể thực hiện. Trong trường hợp đó, bạn phải xác định hàm hoặc phần hàm gây vấn đề và điều chỉnh công thức hoặc đầu vào cho phù hợp.
Ví dụ điển hình nhất của phép tính không thể là cố tìm căn bậc hai của giá trị số âm. Hãy xem ví dụ với hàm SQRT:
Nếu bạn dùng =SQRT(25) kết quả sẽ là 5.
Nếu bạn dùng =SQRT(-25) kết quả sẽ là #NUM.
Bạn có thể khắc phục vấn đề này nếu áp dụng hàm ABS và bạn sẽ lấy giá trị tuyệt đối của số.
=SQRT(ABS(-25))
Hoặc
=SQRT(ABS(cell_reference))
Tương tự, Excel không hỗ trợ số phức nên bất kỳ phép tính nào kết quả là số phức sẽ dẫn đến lỗi #NUM. Trong trường hợp đó, phép tính không thể đạt được trong giới hạn của Excel.
Ví dụ của điều này là cố gắng lũy thừa số âm với mũ không phải số nguyên.
Sửa Lỗi #NUM Khi Công Thức Lặp Không Hội Tụ
Bạn có thể nhận lỗi #NUM vì công thức bạn dùng không tìm thấy kết quả. Nếu đây là trường hợp, bạn phải sửa đổi giá trị đầu vào và giúp công thức thực hiện phép tính.
Lặp (Iteration) là tính năng Excel cho phép công thức tính toán liên tục cho đến khi điều kiện đúng được đáp ứng. Nghĩa là công thức sẽ thử tìm kết quả qua thử và sai. Có một số hàm Excel dùng tính năng lặp: IRR, XIRR, hoặc RATE. Công thức lặp không tìm thấy kết quả hợp lệ trong tham số cho trước sẽ trả về lỗi #NUM.
Ví dụ:
Giúp công thức bằng cách cung cấp dự đoán ban đầu (initial guess):
Bạn có thể cần điều chỉnh cài đặt lặp trong Excel để giúp công thức hội tụ. Cách làm:
- Đi đến File trong ribbon và chọn Options.
- Trong tab Formulas tìm Calculation options.
- Tích vào Enable iterative calculation.
- Trong hộp Maximum iterations nhập số lần bạn muốn công thức tính lại. Số cao hơn tăng khả năng tìm thấy kết quả.
- Trong hộp Maximum Change, chỉ định lượng thay đổi giữa kết quả tính toán. Số nhỏ hơn cung cấp kết quả chính xác hơn.
- Nhấp nút OK để áp dụng thay đổi.
Sửa Lỗi #NUM Trong Hàm IRR Của Excel
Nếu có vấn đề non-convergence (không hội tụ), công thức IRR sẽ không tìm thấy giải pháp. Kết quả sẽ là lỗi #NUM. Để xử lý, bạn phải điều chỉnh cài đặt lặp của Excel và cung cấp dự đoán ban đầu.
Giống như các hàm lặp khác, hàm IRR có thể dẫn đến lỗi #NUM vì công thức không tìm thấy kết quả sau một số lần lặp nhất định. Bạn có thể dễ dàng giải quyết vấn đề này nếu tăng số lần lặp và cung cấp dự đoán ban đầu. Xem phần trước để lấy ví dụ.
Nếu bạn đang đối mặt với dấu không nhất quán, như ví dụ dưới, hãy chắc chắn nhập tất cả dòng tiền ra ban đầu (initial outgoing cash flows) dưới dạng số âm. Hàm giả định có cả dòng tiền dương và âm.
Ví dụ, nếu Investment (Đầu tư) đặt thành $10,000, hàm IRR sẽ cho lỗi #NUM.
Nếu bạn thay Investment thành -$10,000, làm đầu vào âm như vậy:
Hàm IRR sẽ hoạt động.
Lưu ý phiên bản Microsoft Excel hiện đại sẽ thay đổi đầu vào âm thành ngoặc (đầu vào -$10,000 được thay thành ($10,000)), đừng lo nếu thấy điều này xảy ra. Công thức vẫn sẽ hoạt động.
Bằng cách làm theo các bước và thực hành tốt nhất được nêu trong bài viết này, bạn hiện đã được trang bị để đối phó trực diện những vấn đề #NUM! phiền toái, đảm bảo bảng tính Excel của bạn vẫn tin cậy và không lỗi. Chúc bạn tạo bảng tính vui vẻ!