Việc hiểu và giải quyết lỗi #NUM trong Excel là một kỹ năng cần thiết cho bất kỳ ai làm việc với Excel. Từ dữ liệu tài chính, tiến hành nghiên cứu khoa học hay quản lý hàng tồn kho, những lỗi công thức này có thể xuất hiện và ảnh hưởng đến phân tích . Trong bài viết này, mình sẽ hướng dẫn bạn các cách khác nhau để sửa lỗi #NUM.
Lỗi #NUM là gì và tại sao nó xảy ra?
Thông báo lỗi Excel #NUM là một trong những lỗi thường gặp nhất. Nó sẽ được kích hoạt vì những lý do sau:
- Đối số đầu vào không hợp lệ . Tất cả dữ liệu đầu vào cần phải thuộc loại dữ liệu hợp lệ để Excel có thể nhận dạng chúng và để các hàm có thể tính toán chúng.
- Số quá lớn hoặc quá nhỏ . Excel có giới hạn về kích thước của các số mà nó có thể tính toán. Nếu công thức vượt quá giới hạn đó thì sẽ dẫn đến lỗi #NUM.
- Các phép toán không thể thực hiện được như tìm căn bậc hai của số âm.
- Công thức lặp không thể hội tụ . Nếu công thức lặp không thể tìm thấy kết quả hợp lệ, nó sẽ trả về lỗi #NUM.
Sửa lỗi #NUM do đối số hàm không chính xác gây ra
Lý do phổ biến nhất đằng sau lỗi #NUM là đối số không hợp lệ hoặc loại dữ liệu không chính xác. Nếu bạn nghi ngờ điều này đang gây ra lỗi #NUM trong hàm của mình, hãy kiểm tra loại dữ liệu và cú pháp công thức xem có lỗi nào không.
Ví dụ: nếu bạn đang dùng hàm DATE, Excel yêu cầu bạn chỉ dùng các số từ 1 đến 9999 cho đối số năm. Nếu bạn cung cấp giá trị năm nằm ngoài phạm vi này thì sẽ dẫn đến lỗi #NUM.
Tương tự, nếu bạn đang dùng hàm DATEDIF thì ngày kết thúc đượ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 thì kết quả sẽ là lỗi #NUM.
Hãy xem nó trong một ví dụ:
=DATEDIF(A2,B2,”d”) tính toán sự chênh lệch về số ngày giữa hai ngày (trong ô A2 và B2). Kết quả sẽ là số nếu ngày ở ô A2 nhỏ hơn ngày ở ô B2. Nếu không đúng như trong ví dụ bên dưới của mình, 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ó đối số vượt quá giới hạn số của Excel thì sẽ dẫn đến lỗi #NUM. Có, 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. Để giải quyết vấn đề này, bạn sẽ phải điều chỉnh giá trị đầu vào để kết quả nằm trong phạm vi cho phép.
Nếu bạn phải làm việc với những con số lớn như vậy, hãy cân nhắc việc chia phép tính thành những phần nhỏ hơn. Điều này sẽ cho phép bạn dùng nhiều ô để đạt được kết quả cuối cùng.
Dưới đây là các 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,999999999999999E+307.
- Số dương lớn nhất là 9.999999999999999E+307 .
- Số âm lớn nhất được phép thông qua công thức là -1,7976931348623158E+308.
- Số dương lớn nhất được phép thông qua công thức là 1,7976931348623158E+308.
Nếu kết quả của công thức bạn đang dùng nằm ngoài phạm vi này thì kết quả bạn nhận được sẽ là lỗi #NUM.
Hãy xem nó như một ví dụ.
Lưu ý: ở các phiên bản Microsoft Excel mới chỉ số lớn sẽ dẫn đến lỗi #NUM. Các số quá nhỏ sẽ có kết quả là 0 (format chung) hoặc 0,00E+00 (format khoa học).
Nếu bạn không quen với format khoa học, hãy nhớ rằng, ví dụ: phần ”E-20” được dịch là “nhân 10 lũy thừa -20”.
Sửa lỗi #NUM do các phép tính không thể thực hiện được
Một lý do khác dẫn đến lỗi #NUM là nếu Excel cho rằng không thể tính toán được. Trong trường hợp đó, bạn sẽ phải xác định hàm hoặc phần hàm gây ra sự cố và điều chỉnh công thức hoặc dữ liệu đầu vào cho phù hợp.
Ví dụ điển hình nhất của 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 một giá trị số âm. Hãy xem nó trong ví dụ với hàm SQRT:
Nếu bạn dùng =SQRT(25) thì kết quả sẽ là 5.
Nếu bạn dùng =SQRT(-25) thì kết quả sẽ là #NUM.
Bạn có thể khắc phục lỗi này nếu áp dụng hàm ABS và bạn sẽ nhận được giá trị tuyệt đối của số.
=SQRT(ABS(-25))
Hoặc
=SQERT(ABS(ô_tham chiếu))
Tương tự, Excel không hỗ trợ số phức nên bất kỳ phép tính nào bạn thực hiện dẫn đến số phức sẽ dẫn đến lỗi #NUM. Trong trường hợp đó, việc tính toán không thể đạt được trong giới hạn của Excel.
Một ví dụ về điều này là cố gắng nâng số âm lên lũy thừa không nguyên.
Sửa lỗi #NUM khi công thức lặp không hội tụ
Bạn có thể gặp lỗi #NUM vì công thức bạn đang dùng không thể tìm thấy kết quả. Trong trường hợp này, bạn sẽ phải sửa đổi các giá trị đầu vào và giúp công thức thực hiện phép tính.
Lặp lại là một tính năng của Excel cho phép các công thức tính toán nhiều lần cho đến khi đáp ứng được các điều kiện thích hợp. Điều đó có nghĩa là công thức sẽ cố gắng tìm ra kết quả thông qua việc 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 các tham số đã cho sẽ trả về lỗi #NUM.
Ví dụ:
Hỗ trợ công thức bằng cách đưa ra dự đoán ban đầu:
Bạn có thể cần phải điều chỉnh cài đặt lặp lại trong Excel để giúp công thức hội tụ. Đây là cách thực hiện:
- Đi tới File trong ribbon và chọn Options .
- Trong tab Công thức , tìm các tùy chọn Tính toán .
- Chọn tùy chọn Bật tính toán lặp lại .
- Trong hộp Số lần lặp tối đa , hãy nhập số lần bạn muốn công thức của mình tính toán lại. Con số cao hơn làm tăng khả năng tìm thấy kết quả.
- 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 cung cấp kết quả chính xác hơn.
- Nhấp vào nút OK để áp dụng các thay đổi.
Sửa lỗi #NUM trong hàm IRR của Excel
Nếu có vấn đề không hội tụ, công thức IRR sẽ không tìm được giải pháp. Kết quả sẽ là lỗi #NUM. Để giải quyết vấn đề này, bạn sẽ phải điều chỉnh cài đặt lặp của Excel và đưa ra dự đoán ban đầu.
Giống như các hàm lặp khác, hàm IRR có thể gây ra lỗi #NUM vì công thức không thể 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 bạn tăng số lần lặp và đưa ra dự đoán ban đầu. Nhìn vào phần trước để biết ví dụ.
Nếu bạn đang xử lý các dấu hiệu không nhất quán, như trong ví dụ bên dưới của mình, hãy đảm bảo nhập tất cả các dòng tiền đi ban đầu dưới dạng số âm. Hàm này giả định rằng có cả dòng tiền dương và âm.
Ví dụ: nếu Khoản đầu tư được đặt thành 10.000 USD, hàm IRR sẽ dẫn đến lỗi #NUM.
Nếu bạn thay đổi Khoản đầu tư thành -$10.000, dưới dạng đầu vào âm như sau:
Chức năng IRR sẽ hoạt động.
Lưu ý rằng các phiên bản hiện đại của Microsoft Excel sẽ thay đổi dữ liệu đầu vào phủ định thành dấu ngoặc (-$10.000 của mình đã được đổi thành ($10.000)), vì vậy đừng lo lắng nếu bạn 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à phương pháp hay nhất được nêu trong bài viết này, giờ đây bạn đã được trang bị để giải quyết #NUM phiền toái đó! trực tiếp giải quyết các vấn đề, đảm bảo rằng bảng tính Excel vẫn đáng tin cậy và không có lỗi. Chúc bạn tạo bảng tính thành công!
