Microsoft Excel có thể trả về lỗi khi bạn nhập một giá trị hoặc cố gắng thực hiện một hành động mà nó không hiểu. Có một số loại lỗi và mỗi lỗi có liên quan đến những loại lỗi cụ thể mà bạn có thể đã mắc phải.
Lỗi #N/A là lỗi chuẩn của Excel. Nó xuất hiện khi bạn tham chiếu dữ liệu không chính xác. Ví dụ: dữ liệu được tham chiếu không tồn tại hoặc tồn tại bên ngoài bảng tra cứu, mắc lỗi chính tả trong giá trị tra cứu hoặc thêm ký tự phụ vào giá trị tra cứu (dấu phẩy, dấu nháy đơn hoặc thậm chí là ký tự khoảng trắng).
Vì lỗi xảy ra khi bạn tham chiếu không chính xác một giá trị tra cứu nên lỗi này thường liên quan nhất đến các hàm tra cứu như LOOKUP, VLOOKUP, HLOOKUP và hàm MATCH. Hãy xem lý do, ví dụ và một số cách khắc phục lỗi #N/A.
Lý do lỗi #N/A
Sau đây là những nguyên nhân có thể gây ra lỗi #N/A trên bảng tính của bạn:
Ví dụ về lỗi #N/A
Hãy lấy sử dụng hàm VLOOKUP làm ví dụ để hiểu tại sao bạn có thể gặp phải lỗi #N/A sau khi sử dụng các hàm Excel như LOOKUP, HLOOKUP hoặc MATCH vì chúng có chung cấu trúc cú pháp.
Ví dụ: giả sử bạn có một danh sách dài các nhân viên và tiền thưởng của họ được liệt kê trong sổ làm việc Excel.
Bạn sử dụng công thức VLOOKUP, nhập [lookup_value]có liên quan để chèn tham chiếu ô (ô D4), xác định [table_array](A2:B7 ) và xác định [col_index_num](2).
Đối với đối số cuối cùng được gọi là [range_lookup], bạn nên sử dụng 1 (hoặc TRUE) để hướng dẫn Excel thu được kết quả khớp chính xác. Đặt giá trị này thành 2 (hoặc FALSE) sẽ hướng dẫn Excel tìm kiếm kết quả khớp gần đúng, điều này có thể cho kết quả đầu ra không chính xác..
Giả sử bạn đã thiết lập một công thức để nhận tiền thưởng cho một số nhân viên được chọn, nhưng bạn viết sai chính tả giá trị tra cứu. Bạn sẽ gặp lỗi #N/A vì Excel sẽ không thể tìm thấy kết quả khớp chính xác cho giá trị trong bảng tra cứu.
Vậy bạn phải làm gì để khắc phục lỗi này?
Cách khắc phục lỗi #N/A
Có một số cách để khắc phục lỗi #N/A nhưng các cách khắc phục chủ yếu có thể được phân loại thành hai phương pháp:
Sửa dữ liệu đầu vào
Lý tưởng nhất là bạn nên xác định nguyên nhân lỗi bằng cách sử dụng các lý do được liệt kê trước đó trong hướng dẫn này. Việc khắc phục nguyên nhân sẽ đảm bảo rằng bạn không chỉ loại bỏ được lỗi mà còn nhận được kết quả đầu ra chính xác.
Bạn nên bắt đầu bằng cách sử dụng các lý do được liệt kê trong hướng dẫn này làm danh sách kiểm tra. Việc làm này sẽ giúp bạn tìm ra đầu vào không chính xác mà bạn cần sửa để loại bỏ lỗi. Ví dụ: đó có thể là một giá trị sai chính tả, ký tự khoảng trắng thừa hoặc các giá trị có kiểu dữ liệu không chính xác trong bảng tra cứu.
Bẫy lỗi
Ngoài ra, nếu bạn muốn chỉloại bỏ lỗi khỏi bảng tính của mình mà không cần bận tâm đến việc kiểm tra lỗi riêng lẻ, bạn có thể sử dụng một số công thức Excel. Một số hàm được tạo riêng để bẫy lỗi, trong khi những hàm khác có thể giúp bạn xây dựng cú pháp logic bằng cách sử dụng nhiều hàm để loại bỏ lỗi.
Bạn có thể bẫy lỗi #N/A bằng một trong các hàm sau:
1. Hàm IFERROR
Hàm IFERROR được tạo ra với mục đích duy nhất là thay đổi kết quả đầu ra cho một ô trả về lỗi.
Việc sử dụng hàm IFERROR cho phép bạn nhập một giá trị cụ thể mà bạn muốn ô hiển thị thay vì hiển thị lỗi. Ví dụ: nếu bạn gặp lỗi #N/A trong ô E2 khi sử dụng VLOOKUP, bạn có thể lồng toàn bộ công thức vào hàm IFERROR, như sau:.
IFERROR(VLOOKUP(E4,B2:C7,2,1),“Không tìm thấy nhân viên”
Nếu hàm VLOOKUP bị lỗi, nó sẽ tự động hiển thị chuỗi văn bản “Không tìm thấy nhân viên” thay vì lỗi.
Bạn cũng có thể sử dụng chuỗi trống bằng cách chèn hai dấu ngoặc kép (“”) nếu bạn muốn hiển thị ô trống khi công thức trả về lỗi.
Lưu ý rằng hàm IFERROR hoạt động với mọi lỗi. Vì vậy, ví dụ: nếu công thức bạn đã lồng bên trong hàm IFERROR trả về lỗi #DIV thì IFERROR sẽ vẫn giữ lỗi và trả về giá trị trong đối số cuối cùng.
2. Hàm IFNA
Hàm IFNA là phiên bản cụ thể hơn của hàm IFERROR nhưng hoạt động chính xáctheo cách tương tự. Điểm khác biệt duy nhất giữa hai hàm là hàm IFERROR bẫy tất cảlỗi, trong khi hàm IFNA chỉ bẫy lỗi #N/A.
Ví dụ: công thức sau sẽ hoạt động nếu bạn gặp lỗi VLOOKUP #N/A, nhưng không có lỗi #VALUE:
IFNA(VLOOKUP(E4,B2:C7,2,1),“Không tìm thấy nhân viên”
3. Sự kết hợp giữa hàm ISERROR và hàm IF
Một cách khác để bẫy lỗi là sử dụng hàm ISERROR cùng với hàm IF. Về cơ bản, nó hoạt động giống như hàm IFERROR, trong đó nó dựa vào hàm ISERROR để phát hiện lỗi và hàm IF để hiển thị kết quả đầu ra dựa trên kiểm tra logic.
Sự kết hợp này có tác dụng với các lỗi allnhư hàm IFERROR, không chỉ với hàm #N/A. Dưới đây là ví dụ về cú pháp sẽ trông như thế nào khi bẫy lỗi VLOOKUP #N/A trong Excel bằng hàm IF và ISERROR:
=IF(ISERROR(VLOOKUP(E4,B2:C7,2,1)),VLOOKUP(E4,B2:C8,2,1),,”Không tìm thấy nhân viên”)
4. Chức năng TRIM
Chúng ta đã thảo luận trước đó rằng ký tự khoảng trắng được chèn vô tình vào giá trị tra cứu có thể dẫn đến lỗi #N/A. Tuy nhiên, nếu bạn có một danh sách dài các giá trị tra cứu đã được điền vào bảng tính của mình, bạn có thể sử dụng hàm TRIM thay vì xóa ký tự khoảng trắng khỏi từng giá trị tra cứu riêng lẻ.
Đầu tiên, tạo một cột khác để cắt bớt khoảng trắng ở đầu và cuối trong tên bằng hàm TRIM:.
Sau đó, sử dụng cột tên mới làm giá trị tra cứu trong hàm VLOOKUP.
Sửa lỗi #N/A trong macro
Không có công thức hoặc phím tắt cụ thể nào bạn có thể sử dụng để sửa lỗi #N/A trong macro. Vì có thể bạn đã thêm một số hàm vào macro khi bạn tạo nó của mình nên bạn cần kiểm tra các đối số được sử dụng cho từng hàm và xác minh xem chúng có đúng hay không để sửa lỗi #N/A trong maco.
Đã sửa lỗi #N/A
Sửa lỗi #N/A không khó khi bạn hiểu nguyên nhân gây ra lỗi đó. Nếu bạn không quá quan tâm đến kết quả đầu ra và chỉ không muốn công thức bị lỗi, bạn có thể sử dụng các hàm như IFERROR và IFNA để dễ dàng khắc phục lỗi #N/A.
.