Nhận dạng và xử lý công thức Excel trả về kết quả lỗi

 Khi làm công thức Excel thì thường xuyên gặp phải những tình huống công thức trả về là kết quả lỗi. Các kết quả lỗi thường xuyên gặp phải là: #N/A, #VALUE!, #NAME?, #REF!, #DIV/0!, #NUM!, #SPILL!, #NULL!

HƯỚNG DẪN NHẬN DẠNG LỖI VÀ XỬ LÝ CÔNG THỨC LỖI

Cách xử lý phổ biến đối với công thức trả về kết quả lỗi (Trừ #SPILL!) thì đa số dùng hàm IFERROR để lồng bên ngoài, nhưng trong nhiều tình huống sai do người dùng làm công thức, sai do dữ liệu thì người dùng vẫn phải tìm và xử lý các lỗi này.

#N/A - Lỗi không tìm thấy.

Là lỗi gặp nhiều nhất trong làm công thức, đặc biệt trong công thức có sử dụng đến các hàm tìm kiếm như: VLOOKUP, HLOOKUP, LOOKUP, MATCH

Nguyên nhân: Trong các hàm hoặc công thức sử dụng đến các hàm này thì có chứa giá trị tìm kiếm - lookup_value mà không tìm thấy bên bảng dữ liệu nguồn

Cách khắc phục:

- Kiểm tra giá trị tìm kiếm xem có thừa khoảng trống ở đầu và cuối hay không

- Filter bên bảng dữ liệu nguồn xem giá trị tìm kiếm có tồn tại không, nếu có thì kiểm tra xem có thừa khoảng trống hoặc ký tự lạ bên dữ liệu nguồn hay không

- Kết hợp với hàm TRIM và kiểu tìm kiếm Wildcard với dấu * để hạn chế rủi ro hơn. Ví dụ: VLOOKUP(TRIM(A1)&"*",DMHH!$A$1:$F$100,3,0)

#VALUE! - Lỗi sai kiểu giá trị

Lỗi này thường hay gặp khi sử dụng các phép tính toán học giản đơn (+,-,*,/) hoặc vùng không tương xứng trong công thức hoặc hàm tìm kiếm ký tự trong chuỗi, sai về dữ liệu ngày tháng

Nguyên nhân và cách khắc phục:

- Sai về phép tính toán học. Ví dụ A1 nhập là: 10, B1 nhập là 100k. Ô C1 lập công thức =A1*B1. Khi đó cần phải kiểm tra cột dữ liệu xem ô nào là Text (Dùng hàm ISTEXT) để kiểm tra

- Sai về dữ liệu ngày tháng: Ví dụ A1 nhập là: 13/13/2021 và ô B1 lập công thức =MONTH(A1). Khi đó cần kiểm tra cột ngày tháng để xử lý các ô bị lỗi

- Sai về hàm tìm kiếm trong chuỗi. Ví dụ: A1 nhập là ABCDE, ô B1 lập công thức =FIND("F",A1). Lỗi này thì thường dùng kết hợp với hàm IFERROR hoặc ISNUMBER để kết hợp bẫy lỗi

- Sai do vùng không tương xứng trong công thức. Ví dụ: =SUMIFS(A1:A10,B1:B15,"X") => Một vùng đến dòng số 10, 1 vùng đến dòng số 15. Khi đó phải chọn lại vùng cho đồng nhất số dòng

#REF - Sai tham chiếu

Lỗi này thường hay gặp khi người dùng sửa xóa dữ liệu nguồn hoặc tham chiếu tọa độ không đúng trong các hàm tìm kiếm như VLOOKUP, HLOOKUP, INDEX, OFFSET

Nguyên nhân và cách khắc phục:

- Do người dùng sửa xóa dữ liệu nguồn. Ví dụ: Lập công thức tham chiếu đến Sheet1 nhưng người dùng sau đó lại xóa Sheet1 đi

- Do tham chiếu ngoài tọa độ. Ví dụ: =VLOOKUP(A1,DATA!$A$1:$C$100,4,0) thì vùng dữ liệu nguồn đưa vào là 3 cột nhưng công thức lại yêu cầu trả về là cột số 4

#NAME! - Sai tên

Lỗi này thường hay gặp khi người dùng gõ sai tên hàm hoặc đặt Define Name nhưng dùng không đúng tên.

Nguyên nhân và cách khắc phục

- Do dùng sai tên hàm. Ví dụ gõ hàm thành VLOOOKUP, hoặc dùng hàm không có trong phiên bản Excel đang dùng. Ví dụ: Dùng hàm TEXTJOIN chỉ có ở Office 2019 trở lên nhưng máy tính lại đang cài Office 2010

- Do sai Define Name. Ví dụ người dùng đặt Name là DATA nhưng khi đưa vào công thức lại là DATAA

#DIV/0 - Chia cho 0

Lỗi này hay gặp và cũng dễ nhận dạng nhất. Nguyên nhân do trong công thức có sử dụng phép chia mà số chia lại là 0. Cách khắc phục hay dùng nhất là kết hợp cùng với hàm IFERROR

#SPILL! - Thiếu vùng hiển thị

Thực chất đây không phải là lỗi mà là do công thức trả về là một mảng gồm nhiều phần tử. Ví dụ công thức đặt tại ô A1 trả về là 3 phần tử gồm 10,50,20. Khi đó nếu ô A2 và A3 có dữ liệu trước đó rồi thì sẽ hiển thị là #SPILL!. Hiển thị này thường gặp ở Office 365

#NUM! - Sai kiểu toán học

Lỗi này cũng khá ít gặp. Thường hay gặp khi sử dụng các hàm toán học như AGGREGATE, SQRT. Ví dụ: =SQRT(-4) => SQRT là căn bậc 2 thì giá trị đưa vào không được phép là số âm

#NULL! - Không tồn tại vùng giao

Lỗi này khá hiếm gặp khi người dùng sử dụng các công thức liên quan đến vùng giao nhau Intersection. Ví dụ: =SUM(A1:A10 C1:D10)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334