Trong hướng dẫn này, sẽ phân tích các nguyên nhân gây ra lỗi #NUM trong Excel và chỉ cho bạn cách khắc phục.
Lỗi #NUM trong Excel có nghĩa là gì?
Lỗi #NUM trong Excel là thông báo lỗi phổ biến được kích hoạt bởi các giá trị số không hợp lệ dẫn đến kết quả không thể thực hiện được. Lỗi này thường xảy ra khi một hàm chứa đối số không chính xác hoặc khi số hoặc ngày được cung cấp không phải là giá trị số hợp lệ hoặc khi phép tính vượt quá giới hạn mà Excel có thể xử lý.
Lỗi #NUM có thể được giải quyết theo một số cách khác nhau tùy thuộc vào nguyên nhân cơ bản. Chúng có thể bao gồm việc điều chỉnh giá trị đầu vào, thay đổi công thức hoặc phép tính hoặc sửa đổi cài đặt tính toán của Excel. Một giải pháp cụ thể phụ thuộc vào lỗi cụ thể và bối cảnh xảy ra lỗi đó.
Lỗi #NUM trong Excel – nguyên nhân và giải pháp
Dưới đây, sẽ đi sâu vào từng nguyên nhân có thể gây ra lỗi #NUM một cách chi tiết hơn và cung cấp cho bạn các bước cần thiết để giải quyết lỗi đó.
Đối số đầu vào không hợp lệ
Lý do: Một số hàm Excel yêu cầu đối số đầu vào cụ thể. Nếu bạn cung cấp đối số không hợp lệ hoặc loại dữ liệu không chính xác, Excel có thể tạo ra lỗi #NUM.
Giải pháp: Đảm bảo rằng tất cả đối số đầu vào đều hợp lệ và có kiểu dữ liệu chính xác. Kiểm tra kỹ công thức của bạn để tìm lỗi cú pháp.
Ví dụ: hàm DATE trong Excel yêu cầu đối số năm là một số từ 1 đến 9999. Nếu giá trị năm được cung cấp nằm ngoài phạm vi này thì lỗi #NUM sẽ xảy ra.
Tương tự, hàm DATEDIF yêu cầu ngày kết thúc luôn lớn hơn ngày bắt đầu (hoặc cả hai ngày đều bằng nhau). Nếu ngược lại công thức sẽ báo lỗi #NUM.
Ví dụ: công thức này sẽ trả về chênh lệch 10 ngày:
=DATEDIF(“1/1/2023”, “1/11/2023”, “d”)
Và điều này sẽ gây ra lỗi #NUM:
=DATEDIF(“1/11/2023”, “1/1/2023”, “d”)
Số quá lớn hoặc quá nhỏ
Lý do: Microsoft Excel có giới hạn về kích thước của các con số mà nó có thể tính toán. Nếu công thức của bạn tạo ra một số vượt quá giới hạn này thì sẽ xảy ra lỗi #NUM.
Giải pháp: Nếu công thức của bạn tạo ra một số nằm ngoài giới hạn, hãy điều chỉnh các giá trị đầu vào để đảm bảo kết quả nằm trong phạm vi cho phép. Ngoài ra, bạn có thể xem xét chia phép tính thành các phần nhỏ hơn và sử dụng nhiều ô để đi đến kết quả cuối cùng.
Các phiên bản hiện đại của Excel có các giới hạn tính toán sau:
Số âm nhỏ nhất
|
-2.2251E-308
|
Số dương nhỏ nhất
|
2.2251E-308
|
Số dương lớn nhất
|
9.99999999999999E+307
|
Số âm lớn nhất
|
-9.99999999999999E+307
|
Số dương lớn nhất được phép thông qua công thức
|
1.7976931348623158E+308
|
Số âm lớn nhất được phép thông qua công thức
|
-1.7976931348623158E+308
|
Nếu bạn không quen với ký hiệu khoa học, sẽ giải thích ngắn gọn ý nghĩa của nó trong ví dụ về số dương nhỏ nhất được phép 2.2251E-308. Phần “E-308” của ký hiệu có nghĩa là “nhân 10 lũy thừa -308”, do đó, số 2,2251 được nhân với 10 được nâng lên lũy thừa -308 (2,2251×10^-308). Đây là một số rất nhỏ có 307 số 0 ở phần thập phân trước chữ số đầu tiên khác 0 (là 2).
Như bạn thấy, các giá trị đầu vào được phép trong Excel nằm trong khoảng từ -2,2251E-308 đến 9,99999999999999E+307. Trong công thức, các số nằm trong khoảng -1,7976931348623158E+308 và 1,7976931348623158E+308 được cho phép. Nếu kết quả của công thức của bạn hoặc bất kỳ đối số nào của nó nằm ngoài phạm vi, bạn sẽ nhận được lỗi #NUM! lỗi.
Xin lưu ý rằng trong phiên bản Excel 365 hiện tại, chỉ những số lớn mới tạo ra lỗi #NUM. Các số nhỏ nằm ngoài giới hạn sẽ hiển thị dưới dạng 0 (Định dạng chung) hoặc 0,00E+00 (Định dạng khoa học). Có vẻ như một hành vi không có giấy tờ mới:
Hoạt động bất khả thi
Lý do: Khi Excel không thể thực hiện phép tính vì được cho là không thể thực hiện được, nó sẽ trả về lỗi #NUM! lỗi.
Giải pháp: Xác định hàm hoặc thao tác gây ra lỗi và điều chỉnh dữ liệu đầu vào hoặc sửa đổi công thức cho phù hợp.
Một ví dụ điển hình về phép tính không thể thực hiện được là cố gắng tìm căn bậc hai của số âm bằng hàm SQRT. Vì không thể xác định căn bậc hai của số âm nên công thức sẽ dẫn đến lỗi #NUM. Ví dụ:
=SQRT(25)– trả về 5
=SQRT(-25)– trả về #NUM!
Để khắc phục lỗi, bạn có thể bao số âm trong hàm ABS để lấy giá trị tuyệt đối của một số:
=SQRT(ABS(-25))
Hoặc
=SQRT(ABS(A2))
Trong đó A2 là ô chứa số âm.
Một trường hợp khác là lũy thừa của một số âm thành lũy thừa không nguyên. Nếu bạn cố gắng nâng số âm lên lũy thừa không nguyên, kết quả sẽ là số phức. Vì Excel không hỗ trợ số phức nên công thức sẽ tạo ra lỗi #NUM. Trong trường hợp này, lỗi cho biết rằng phép tính không thể thực hiện được trong giới hạn của Excel.
Công thức lặp không thể hội tụ
Lý do: Công thức lặp không tìm được kết quả gây ra lỗi #NUM trong Excel.
Giải pháp: Sửa đổi các giá trị đầu vào để giúp công thức hội tụ hoặc điều chỉnh cài đặt lặp của Excel hoặc sử dụng hoàn toàn một công thức khác để tránh lỗi.
Lặp lại là một tính năng trong Excel cho phép các công thức tính toán lại nhiều lần cho đến khi đáp ứng một điều kiện nhất định. Nói cách khác, một công thức cố gắng đạt được kết quả thông qua việc thử và sai. Trong Excel, có một số hàm như vậy bao gồm IRR, XIRR và RATE. Khi một công thức lặp không thể tìm thấy kết quả hợp lệ trong các ràng buộc đã cho, nó sẽ trả về lỗi #NUM.
Để khắc phục lỗi này, bạn có thể cần thay đổi giá trị đầu vào hoặc giảm mức dung sai cho độ hội tụ để đảm bảo rằng công thức có thể tìm thấy kết quả hợp lệ.
Ví dụ: nếu công thức RATE của bạn dẫn đến lỗi #NUM, bạn có thể hỗ trợ công thức đó tìm giải pháp bằng cách đưa ra dự đoán ban đầu:
Ngoài ra, 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ụ. Dưới đây là các bước để làm theo:
1. Bấm vào menu Tệp và chọn Tùy chọn.
2. Trên tab Công thức, trong phần Tùy chọn tính toán, hãy chọn tùy chọn Bật tính toán lặp.
3. Trong hộp Số lần lặp tối đa, nhập số lần bạn muốn Excel thực hiện tính toán lại công thức. Số lần lặp cao hơn sẽ làm tăng khả năng tìm được kết quả nhưng cũng cần nhiều thời gian hơn để tính toán.
4. Trong hộp Thay đổi Tối đa, chỉ định mức độ thay đổi giữa các kết quả tính toán. Số nhỏ hơn cho kết quả chính xác hơn nhưng cần nhiều thời gian hơn để tính toán.
Lỗi #NUM trong hàm IRR của Excel
Lý do: Công thức IRR không tìm được giải pháp do vấn đề không hội tụ hoặc khi dòng tiền có dấu hiệu không nhất quán.
Giải pháp: Điều chỉnh cài đặt lặp của Excel, đưa ra dự đoán ban đầu và đảm bảo dòng tiền có dấu hiệu phù hợp.
Hàm IRR trong Excel tính toán tỷ suất hoàn vốn nội bộ cho một chuỗi dòng tiền. Tuy nhiên, nếu dòng tiền không tạo ra IRR hợp lệ thì hàm sẽ trả về #NUM! lỗi.
Giống như bất kỳ hàm lặp nào khác, lỗi #NUM trong IRR có thể xảy ra do không thể tìm thấy kết quả sau một số lần lặp nhất định. Để giải quyết vấn đề, hãy tăng số lần lặp tối đa hoặc/và đưa ra dự đoán ban đầu về tỷ lệ hoàn trả (xem phần trước để biết thêm chi tiết).
Một lý do khác khiến hàm IRR có thể trả về lỗi #NUM là do các dấu hiệu không nhất quán trong dòng tiền . Hàm này giả định rằng có cả dòng tiền dương và âm. Nếu không có sự thay đổi dấu của dòng tiền, công thức IRR sẽ dẫn đến lỗi #NUM. Để khắc phục sự cố này, hãy đảm bảo rằng tất cả các khoản thanh toán đi, bao gồm cả khoản đầu tư ban đầu, đều được nhập dưới dạng số âm.
Tóm lại, lỗi #NUM trong Excel là một sự cố phổ biến có thể xảy ra vì nhiều lý do khác nhau, bao gồm đối số không chính xác, số/ngày không được nhận dạng, các thao tác không thể thực hiện được và vượt quá giới hạn tính toán của Excel. Hy vọng bài viết này đã giúp bạn hiểu rõ những nguyên nhân này và khắc phục lỗi một cách hiệu quả.