Tổng hợp lỗi công thức trong Excel

Trước đây, Zaidap.com đã giới thiệu tới bạn đọc một số lỗi Excel thường gặp và trong bài viết này sẽ giới thiệu đầy đủ, chi tiết hơn về những lỗi công thức khi sử dụng phần mềm tính toán này. Dưới đây là danh sách tổng hợp lỗi công thức trong Excel. Bạn đọc có thể tham khảo để biết thêm cách sửa từng loại lỗi.

2016, 2013, 2010, 2007, 2003

#NULL! - Xuất hiện khi giữa 2 hàm không có điểm giao nhau.

#DIV/0! - Xảy ra khi một công thức cố gắng chia cho số 0.

#VALUE! - Xảy ra nếu một trong các biến trong công thức bị sai loại biến (chẳng hạn như gía trị text bị sai khi một giá trị số được mong đợi).

#REF! - Xuất hiện khi một công thức chứa một tham chiếu ô không hợp lệ.

#NAME? - Xảy ra nếu Excel không nhận ra tên công thức hoặc không nhận ra text trong một công thức.

#NUM! - Xảy ra khi Excel gặp giá trị không hợp lệ.

#N/A - Cho biết một giá trị không có sẵn trong công thức.

Lỗi Excel #NULL!

Excel hiển thị lỗi #NULL! khi giữa 2 hàm không có điểm giao nhau. Chẳng hạn như công thức =SUM(B1:B10 A5:D7) sẽ trả lại tổng các giá trị trong khoảng B5:B7 (giao điểm của các dãy B1: B10 và A5: D7).

Tuy nhiên nếu nhập công thức =SUM(B1:B10 C5:D7) bạn sẽ nhận được thông báo lỗi #NULL! vì giá trị trong khoảng phạm vi B1: B10 và C5: D7 không giao nhau.

Lỗi này có thể sửa bằng cách xem lại công thức của bạn và thay đổi các biến để đảm bảo các hàm có điểm giao nhau hợp lệ, hoặc bằng cách sử dụng hàm Excel Iferror để thay thế, chẳng hạn như:

=IFERROR( SUM(B1:B10 C5:D7), 0 )

Lỗi Excel #DIV/0!

Lỗi Excel #DIV/0! xảy ra khi một công thức cố gắng chia cho số 0. Rõ ràng khi chia cho số 0 thì kết quả bằng vô cực, không thể là giá trị bảng tính, vì vậy Excel trả về lỗi Excel #DIV/0!

Ví dụ, nếu ô C1 chứa giá trị 0, thì công thức: =B1/C1 sẽ trả lại lỗi #DIV/0!.

Lỗi này có thể được khắc phục bằng cách sử dụng hàm IF để xác định kết quả chia bằng 0, trong trường hợp này sẽ tạo ra một kết quả thay thế. Ví dụ:

=IF(C1=0, "n/a", B1/C1)

Lỗi Excel #VALUE!

Lỗi công thức trong Excel #VALUE! xảy ra khi một trong các biến trong một công thức bị sai loại biến. Ví dụ, công thức đơn giản =B1+C1, trong đó các ô B1 và ​​C1 chứa các giá trị số. Giả sử, nếu một trong 2 ô hoặc B1, hoặc C1 có chứa một giá trị là văn bản, kết quả công thức sẽ hiển thị lỗi #VALUE!.

Cách tốt nhất để tiếp cận và giải quyết lỗi này là kiểm tra từng phần riêng lẻ của công thức để đảm bảo mỗi đối số có cùng một loại biến. Nếu hàm của bạn chứa các hàm lồng nhau, tốt nhất bạn nên sao chép từng phần của hàm lồng nhau vào từng ô riêng biệt để kiểm tra. Nếu cần bạn có thể chia nhỏ từng phần và kiểm tra các thành phần cho đến khi tìm ra ngọn ngành gây ra lỗi.

Lỗi Excel #REF!

Nếu một ô Excel hiển thị lỗi công thức trong Excel #REF!, chứng tỏ một tham chiếu ô không hợp lệ. Có 2 trường hợp phổ biến là nguyên nhân gây ra lỗi công thức Excel này:

1. Công thức trước đây tham chiếu một ô đã bị xóa.

Ví dụ: Ô A1 chứa công thức:

=B1+C1

Nếu bây giờ bạn xóa cột C trong bảng tính, công thức tham chiếu đến C1 không cong hợp lệ và bạn sẽ nhận được thông báo lỗi #REF!.

2. Công thức đã được sao chép từ một ô tham chiếu nằm gần cạnh bảng tính. Khi công thức được sao chéo vào một ô mới, phạm vi thay đổi, phạm vi kết quả (các giả định) các ô tham chiếu nằm bên ngoài phạm vi bảng tính.

Ví dụ công thức được hiển thị dưới đây được nhập vào ô A1, đếm số ô trong Sheet 2 có chứa giá trị 1:

=COUNTIF(Sheet2!1:1048576, 1)

Nếu bây giờ bạn sao chép công thức trên vào ô C2, Excel sẽ cố gắng điều chỉnh phạm vi ô thành Sheet2!2:1048577. Tuy nhiên số hàng 1048577 không tồn tại (vì chỉ có 1048576 hàng trong bảng Excel hiện tại). Vì vậy, bạn sẽ nhận được lỗi #REF!.

Trong cả 2 ví dụ trên, nếu click chọn ô chứa lỗi #REF!, bạn sẽ nhìn thấy tham chiếu ô trong công thức được thay thế bằng #REF!. Vì vậy để khắc phục lỗi, bạn cần phải nhập lại các tham chiếu ô chính xác trong công thức.

Lưu ý trong ví dụ thứ 2 ở trên bạn có thể tránh được lỗi bằng cách thay đổi tham chiếu Sheet!1: 1048576 thành Sheet!$1:$1048576.

Lỗi Excel #NAME?

Khi Excel gặp phải text trong công thức, nó sẽ cố gắng giải thích text giống như một tham chiếu, tên một phạm vi hoặc tên một hàm. Nếu text không được công nhận sẽ tạo ra lỗi #NAME?.

Ví dụ nếu muốn nhập hàm =SUM(B1:C2), nhưng bạn vô tình nhập vào đó hàm =SM(B1:C2), Excel không nhận ra tên hàm SM và tạo ra lỗi #NAME?.

Cách tiếp cận và khắc phục lỗi hàm Excel #NAME? là kiểm tra tên hàm, tham chiếu và tên các phạm vi đã chính xác hay chưa, và kiểm tra xem có bất kỳ biến nào được đặt trong ngoặc kép hay không.

Nếu công thức của bạn chứa các hàm lồng nhau, hãy kiểm tra kết quả của từng hàm riêng lẻ, cho đến khi xác định được lỗi.

Lỗi Excel #NUM!

Lỗi công thức Excel #NUM! xuất hiện khi Excel phát hiện có một giá trị không hợp lệ trong công thức. Ví dụ tất cả số chính phương là số dương, vì vậy không có căn bậc 2 của một số âm . Do đó hàm SQRT (-2) của Excel sẽ tạo ra lỗi #NUM!, để chỉ ra rằng đối số không hợp lệ.

Để sửa lỗi #NUM! có một cách là kiểm tra từng đối số trong công thức của bạn. Giống như các loại đối số khác, nếu công thức của bạn được lồng hoặc được tạo thành từ nhiều phần, tốt nhất nên chia nhỏ công thức và đánh giá từng đối số một.

Lỗi Excel #N/A

Lỗi công thức Excel #N/A hay chính là lỗi hàm Vlookup xảy ra khi một giá trị không có sẵn trong công thức của bạn. Trong ví dụ dưới đây bạn có thể thấy sử dụng hàm Vlookup để tìm giá trị "Cabbage" trong cột C trên bảng tính và trả lại giá trị liên quan trong cột D. Tuy nhiên vì "Cabbage" không xuất hiện trong cột C, lỗi hàm VLOOKUP không thể tìm thấy giá trị này và trả về lỗi #N/A.

Để xác định lỗi và sửa lỗi, bạn cần xem xét các giá trị mà công thức đang truy cập và xác định xem lý do tại sao giá trị yêu cầu không có sẵn.

Nếu cho rằng giá trị mà bạn đang cố tìm kiếm có trong danh sách mà bạn đang tìm kiếm, thử truy cập trang Failure To Match Values để được trợ giúp thêm cách khắc phục lỗi này.

http://thuthuat.taimienphi.vn/loi-cong-thuc-trong-excel-27614n.aspx
Trên đây Zaidap.com vừa cung cấp cho bạn danh sách tổng hợp lỗi công thức trong Excel cũng như cách sửa lỗi. Nếu có thắc mắc hoặc câu hỏi nào cần giải đáp, bạn có thể để lại bình luận trong phần bên dưới bài viết.