Các giá trị lỗi phổ biến trong các công thức Excel và cách khắc phục chúng
Nếu Excel không thể đánh giá đúng công thức hoặc hàm trang tính; nó sẽ hiển thị một giá trị lỗi - chẳng hạn như #REF !, #NULL !, # DIV / 0! - trong ô có công thức.
Bản thân giá trị lỗi cộng với nút tùy chọn lỗi, được hiển thị trong các ô có công thức lỗi, cung cấp một số trợ giúp trong việc xác định sự cố về sự cố.
Tam giác xanh và kim cương vàng
Excel sẽ hiển thị một hình tam giác nhỏ màu xanh ở góc trên bên trái của các ô có chứa các giá trị lỗi - các ô D2 đến D9 trong hình trên. Hình tam giác màu xanh lục cho biết nội dung của ô vi phạm một trong các quy tắc kiểm tra lỗi của Excel.
Nhấp vào ô có chứa hình tam giác màu xanh lá cây sẽ khiến nút hình kim cương màu vàng xuất hiện bên cạnh hình tam giác. Kim cương màu vàng là nút tùy chọn lỗi của Excel và nó chứa các tùy chọn để sửa lỗi nhận biết.
Di chuột qua nút tùy chọn lỗi sẽ hiển thị một tin nhắn văn bản - được gọi là văn bản di chuột - giải thích lý do cho giá trị lỗi.
Dưới đây được liệt kê các giá trị lỗi phổ biến được hiển thị bởi Excel, cùng với một số nguyên nhân và giải pháp phổ biến để giúp khắc phục sự cố.
#VÔ GIÁ TRỊ! Lỗi - Tham chiếu ô được phân tách không chính xác
#VÔ GIÁ TRỊ! các giá trị lỗi xảy ra khi hai hoặc nhiều tham chiếu ô được phân cách không chính xác hoặc không chủ ý bởi khoảng trắng trong công thức - các hàng từ 2 đến 5 trong hình trên.
Trong công thức Excel, ký tự khoảng trắng được sử dụng làm toán tử giao nhau, có nghĩa là nó được sử dụng khi liệt kê hai hoặc nhiều phạm vi giao nhau hoặc chồng chéo dữ liệu, chẳng hạn như: A1: A5 A3: C3 (tham chiếu ô A3 là một phần của cả hai phạm vi , do đó, phạm vi làm giao nhau).
#VÔ GIÁ TRỊ! lỗi xảy ra nếu:
- nhiều tham chiếu ô trong công thức được phân tách bằng khoảng trắng thay vì toán tử toán học như dấu cộng ( + ). Ví dụ: = A1 A3 + A5;
- điểm bắt đầu và điểm kết thúc của dải ô được phân tách bằng dấu cách thay vì bởi toán tử dải ô - dấu hai chấm (:). Ví dụ: = SUM (A1 A5);
- tham chiếu ô riêng lẻ trong một công thức được phân tách bằng dấu cách thay vì toán tử kết hợp - dấu phẩy (,). Ví dụ: = SUM (A1 A3, A5);
- toán tử giao nhau (ký tự khoảng trắng) được sử dụng cố ý, nhưng phạm vi được chỉ định không giao nhau. Ví dụ: = SUM (A1: A5 B1: B5)
Giải pháp: Phân tách ô tham chiếu chính xác.
- tham chiếu ô riêng biệt trong công thức với toán tử toán học. Ví dụ: = A1 + A3 + A5;
- tách các điểm bắt đầu và điểm kết thúc của một dải bằng dấu hai chấm (:). Ví dụ: = SUM (A1: A5);
- các tham chiếu ô riêng lẻ trong công thức có dấu phẩy. Ví dụ: = SUM (A1, A3, A5);
- đảm bảo rằng các khoảng cách được phân tách bằng khoảng trắng thực sự giao nhau. Ví dụ: = SUM (A1: A5 A3: C3).
#REF! Lỗi - Tham chiếu ô không hợp lệ
Lỗi tham chiếu ô không hợp lệ xảy ra khi công thức chứa tham chiếu ô không chính xác - hàng 6 và 7 trong ví dụ ở trên. Điều này xảy ra thường xuyên nhất khi:
- các ô riêng lẻ hoặc toàn bộ các cột hoặc các hàng chứa dữ liệu được tham chiếu trong một công thức vô tình bị xóa;
- dữ liệu từ một ô được di chuyển (sử dụng cắt và dán hoặc kéo và thả) vào một ô được tham chiếu bởi công thức;
- công thức chứa liên kết - sử dụng OLE (Liên kết và nhúng đối tượng) - cho một chương trình hiện không chạy.
Các giải pháp:
- sử dụng tính năng hoàn tác của Excel để khôi phục dữ liệu bị mất trong các ô, cột hoặc hàng đã xóa;
- nếu dữ liệu không thể phục hồi, hãy nhập lại dữ liệu và điều chỉnh các tham chiếu ô nếu cần;
- mở các chương trình có chứa các liên kết OLE và cập nhật trang tính chứa #REF! lỗi.
# DIV / O! - Chia cho Zero Lỗi
Chia cho 0 lỗi xảy ra khi công thức tìm cách chia cho số không - hàng 8 và 9 trong hình trên. Điều này có thể xảy ra khi:
- số chia hoặc mẫu số trong phép chia chia bằng 0 - hoặc rõ ràng - chẳng hạn như = A5 / 0 hoặc là kết quả của phép tính thứ hai có kết quả bằng 0;
- một công thức tham chiếu một ô trống.
Các giải pháp:
- Kiểm tra xem bạn có dữ liệu chính xác trong các ô được tham chiếu trong công thức hay không.
- Kiểm tra xem dữ liệu của bạn có nằm đúng ô không.
- Kiểm tra xem các tham chiếu ô chính xác đã được sử dụng trong công thức chưa.
##### Lỗi - Định dạng ô
Một ô chứa một hàng gồm thẻ bắt đầu bằng # , ký hiệu số hoặc ký hiệu bảng Anh cũng được gọi là giá trị lỗi của Microsoft nhưng được cho là do độ dài của dữ liệu được nhập vào ô được định dạng.
Do đó, hàng ##### xảy ra trong nhiều trường hợp, chẳng hạn như khi:
- giá trị đã nhập rộng hơn chiều rộng ô hiện tại cho ô được định dạng cho ngày hoặc thời gian - hàng 10 trong hình ở trên.
- một công thức được nhập vào ô được định dạng cho các số tạo ra kết quả rộng hơn ô.
- một số hoặc dữ liệu văn bản, vượt quá 253 ký tự, được nhập vào ô được định dạng cho số ngày, giờ hoặc kế toán.
- số âm nằm trong ô đã được định dạng cho ngày hoặc giờ. Ngày và thời gian trong Excel phải là giá trị dương như được hiển thị trong hàng 11 trong hình trên.
Các giải pháp:
- Mở rộng ô bị ảnh hưởng bằng cách mở rộng cột (các ô riêng lẻ không thể được mở rộng mà không mở rộng toàn bộ cột).
- Hãy rút ngắn thời lượng dữ liệu trong ô nếu có thể hoặc chọn một định dạng khác cho ô như Tổng quát .
- Sửa giá trị ngày hoặc giờ trong ô bị ảnh hưởng để kết quả không âm.
- Sửa công thức dẫn đến giá trị ngày hoặc giờ âm được hiển thị trong ô bị ảnh hưởng.