Xóa #N/A Trong Excel / TOP 10 # Xem Nhiều Nhất & Mới Nhất 3/2023 # Top View

Lỗi #N/A trong Excel là gì – nguyên nhân và các sửa lỗi #N/A trong các hàm VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH của Excel. Nhiều người không biết lỗi #N/A xảy ra khi nào, ý nghĩa chữ viết tắt n/a ra sao và hướng giải quyết. Phải nói, dân văn phòng đã không lạ gì khi xuất hiện thông báo #n/a trên bảng tính Excel, quan trọng hơn cả là bạn phải biết được hướng khắc phục để công việc mang lại kết quả.

Lỗi #N/A trong Excel là gì?

Lỗi #N/A trong Excel là lỗi xuất hiện khi lệnh thực thi không tìm thấy giá trị, nguyên nhân có thể giá trị tra cứu không tồn tại hoặc công thức Excel ghi chưa chính xác. Lỗi Lỗi #N/A xuất hiện trên tất cả các phiên bản Excel 2003, 2007, 2010, 2013 và cả 2016 chứ không của riêng bản Excel nào.

Chữ N/A trong tiếng Anh có thể hiểu là Not Available – No Answer – Not Applicable, nghĩa là không sẵn có – không tìm thấy – không áp dụng được. Thông thường, lỗi #N/A xuất hiện khi chúng ta thực hiện các hàm VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH trên Excel.

Phân loại và cách sửa lỗi #N/A trên Excel

Xuất hiện lỗi #N/A khi viết Hàm do không cố định vùng tìm kiếm trước khi sao chép công thức: Bởi vì không cố định vùng tìm kiếm, nên khi copy công thức thì vùng tìm kiếm sẽ thay đổi theo, từ đó dẫn tới thông báo lỗi #N/A.

Lỗi #N/A do kết hợp hàm các hàm Vlookup, Hlookup với các hàm Mid, Right, Left mà giữa các hàm sai về định dạng (số và văn bản). Vì vậy, cách xử lý lỗi #N/A trường hợp này thì bạn chuyển định dạng số trong hàm Mid (Rigt, Left) về dạng văn bản.

Lỗi #N/A khi nhìn thấy có điều kiện tìm kiếm trong vùng điều kiện, vốn rất phổ biến và khó để xử lý. Đó là do điều kiện tìm kiếm hoặc giá trị tương ứng ở vùng điều kiện tìm kiếm thừa dấu cách ở phía sau. Cách sửa lỗi #N/A trường hợp này là chọn lần lượt từng giá trị.

1/ Lỗi #n/a là gì?

Bạn chắc hẳn gặp khá thường xuyên khi nhập công thức và kết quả cho ra lỗi này hoặc có thể là một số các lỗi khác. Nhưng nghĩa của từng lỗi lại là khác nhau. Như lỗi n/a lỗi này là lỗi có tên viết tắt tiếng anh là Not Available có nghĩa khi kết quả của công thức cho ra lỗi này thì có thể là trong công thức của bạn thiết lập có thể chứa một nội dung nào không tồn tại có sẵn. Lỗi #n/a thường xảy ra xuất hiện khi bạn áp dụng với một số đối tượng không tồn tại khi sử dụng hàm tìm kiếm hay tham chiếu như Vlookup, Match,…

2/ Cách xử lý lỗi #n/a

Bạn nên biết rằng lỗi này xuất hiện không phải là bạn đã nhập sai một công thức nào đó mà lỗi cho bạn biết rằng không thể tìm thấy đối tượng cần tìm, đối tượng đó có thể là không tồn tại. Vậy nên cách để xử lý được lỗi này mỗi khi bạn gặp phải là bạn cần giải thích biện luận cho những công thức không thấy đối tượng hoặc có thể là đưa về rỗng khi không tìm thấy đối tượng.

Xử lý lỗi #n/a trong Excel (1)

– Kiểm tra sự tồn tại của lookup_value, kết quả hiện ra là không tồn tại thay vì là xuất hiện lỗi #n/a bạn sử dụng công thức để hiển thị ra Không tồn tại tại ô F9:

Xử lý lỗi #n/a trong Excel (2)

– Đối với một số trường hợp đặc biệt bạn không hay để ý đến là lỗi định dạng dữ liệu không giống nhau. Ví dụ như định dạng ngày tháng năm ở một vị trí là dạng text nhưng cũng giống hệt như vậy, vị trí khác lại là dạng date vậy nên khi thực hiện công thức áp dụng cho value đó bạn chỉ nhận kết quả báo về lỗi #n/a:

Xử lý lỗi #n/a trong Excel (3)

Vậy nên việc của bạn làm cần cho việc xử lý lại lỗi này là bạn cần định dạng lại dữ liệu cho thống nhất một dạng và trong một số trường hợp bạn cần xem các ký tự đã đầy đủ chưa…

Nếu bạn đang gặp khóa khăn vì chưa thành thạo Excel công sở hay không biết cách phân tích, xử lý định dạng cũng như số liệu làm việc trong Excel thì hãy tham khảo khóa học ” Chinh phục Excel công sở” của giảng viên Nguyễn Thành Đông trên UNICA.

Khóa học “Chinh phục Excel công sở”

Đến với khóa học, bạn sẽ biết cách xử ký, tổng hợp, quản lý cũng như phân tích số liệu, biết cách thực hành cách lệnh và nắm bắt được những hàm lỗi cùng nhiều thủ thuật , tiện ích khác khác giúp cho công việc văn phòng trên Microsoft Excel và học Excel cơ bản trở nên sinh động và hấp dẫn hơn.

Chủ đề tóm tắt trong bài viết

Lỗi #N/A trong Excel là gì?

Nếu như bạn nào chưa biết thì #N/A (Not Available) có nghĩa là không tồn tại, không có sẵn. Khi bạn gặp trường hợp lỗi này sẽ sẽ xuất hiện khi giá trị bạn muốn tìm kiếm không thể tìm thấy hay không có sẵn trong vùng dữ liệu được chọn. Và lỗi thường xảy ra khi sử dụng các hàm dò tìm, tham chiếu như Hlookup, Vlookup, Lookup, Match. Khi định nghĩa được phần lỗi này rồi thì mọi việc cũng dễ dàng thực hiện hơn, sau đây mình sẽ trình bày cho các bạn những trường hợp gây ra lỗi này và cách khắc phục #N/A trong Excel như thế nào ở phần bên dưới

Nguyên nhân và cách sửa lỗi #N/A trong Excel

Giá trị tìm kiếm bị ghi sai hoặc chứa khoảng trống

Trong hình trên ta có thể thấy, vì giá trị tìm kiếm ở ô E5 là 3 69 chứa khoảng trống nên khi thực hiện lệnh Vlookup tìm kiếm giá trị ô E5 trong bảng điểm thì xuất hiện lỗi #N/A.

Cách sửa: Để sửa lỗi #N/A ở file Excel trong trường hợp này bạn chỉ cần xóa khoảng trống thừa trong giá trị ở ô E5 là có thể khắc phục được một cách dễ dàng

Ghi nhầm hoặc chứa khoảng trống trong Excel

Giá trị trong bảng tìm kiếm bị ghi nhầm hoặc chứa các khoảng trống hoặc giá trị trong bảng tìm kiếm là kiểu số nhưng bị ghi nhầm thành kiểu text. Trong trường hợp này, giá trị ở ô B4 chứa khoảng trống không hợp lệ còn ở ô B6 giá trị là kiểu text nên khi thực hiện dò tìm thì sẽ xảy ra lỗi và việc sửa mọi người chỉ cần thay đổi thành hợp lệ là sẽ không xuất hiện #N/A nữa

Chọn sai hoặc chọn thiếu vùng dữ liệu khi thực hiện câu lệnh

Câu lệnh Vlookup ở ô F4 trả về lỗi #N/A là vì hàm Vlookup có dạng :

VLOOKUP(Giá trị cần tìm kiếm, Vùng tìm kiếm , Cột lấy dữ liệu ra, Giới hạn tìm kiếm)

Trong đó, cột đầu tiên của vùng tìm kiếm phải là cột chứa giá trị cần tìm kiếm, mà ở công thức trên thì cột đầu tiên của vùng tìm kiếm là cột STT không chứa MS cần tìm nên câu lệnh xuất hiện lỗi.

Cách sửa: Trong phần khắc phục lỗi N/A ở file Excel này bạn chỉ cần chọn lại vùng tìm kiếm và điều chỉnh cột lấy dữ liệu bằng cách sửa câu lệnh từ VLOOKUP(E6,$A$4:$C$13,3,0) thành VLOOKUP(E6, $B$4:$C$13,2,0).

4. Giá trị tìm kiếm không có trong vùng tìm kiếm

Lỗi #N/A sẽ xuất hiện khi giá trị bạn tìm kiếm không nằm trong vùng tìm kiếm, lúc này lỗi #N/A sẽ được hiểu là “Giá trị tìm kiếm không tồn tại trong bảng”.

Cách nhận biết lỗi này:

+ Sử dụng hàm IFERROR(VLOOKUP(A1,table,column,0),”Not found”) thay thế cho Vlookup ban đầu để có thể dễ dàng nhận biết được lỗi này. Ngoài ra, còn một hàm có chức năng tương tự như IFERROR mà bạn cũng có thể sử dụng đó là hàm

IFNA(VLOOKUP(A1,table,column,0),”Not found”) .

Quên cố định vùng tìm kiếm khi copy công thức

Ta có thể thấy trong trường hợp này vì vùng tìm kiếm không được cố định nên khi copy công thức từ hàng đầu tiên cho các hàng tiếp theo thì vùng tìm kiếm đã bị thay đổi nên xuất hiện lỗi #N/A.

Cách khắc phục: Để sửa lỗi #N/A ở phần phần trên thì mọi người chỉ cần cố định vùng giá trị bằng cách là chuyển từ B4:C13 thành $B$4:$C$13 là xong.

1. Ý nghĩa của lỗi #N/A trong Excel

N/A trong tiếng anh được viết tắt là Not Available = Không tồn tại, không có sẵn

Do đó lỗi này được hiểu là trong công thức bạn sử dụng có chứa nội dung không có sẵn trong vùng dữ liệu, dẫn tới không thể tính toán, hoàn thành công thức được.

Lỗi này thường xảy ra khi sử dụng các hàm dò tìm, tham chiếu. Đối tượng cần dò tìm, tham chiếu không có sẵn trong vùng cần tra cứu nên báo lỗi #N/A

2. Giải thích cơ chế hoạt động của lỗi

Trong hình trên chúng ta thấy tại ô F2 và F3 đều trả về lỗi #N/A, trong đó:

Hàm Vlookup tại ô F2 có lookup_value là nội dung ở ô E2, nội dung này không tồn tại trong vùng B2:B15

Hàm Vlookup tại ô F3 tương tựn như F2 về lookup_value nhưng col_index_num cũng sai. Trong ô F3 này có tới 2 lỗi #N/A và #REF! nhưng kết quả trả về là #N/A

Để kiểm tra xem giá trị ở ô E2 có nằm trong vùng B2:B15 không, chúng ta kiểm tra bằng cách sử dụng hàm COUNTIF tại ô F4 thấy kết quả = 0. Điều đó chứng tỏ nội dung ở ô E2 không có (không tồn tại) trong danh sách cần tìm.

Cơ chế hiển thị lỗi: Theo thứ tự từ trái sang. Tại vị trí nào phát hiện ra lỗi trước thì sẽ báo theo lỗi đó.

Khi sử dụng các hàm dò tìm, tham chiếu có chứa đối tượng lookup_value thì chúng ta lưu ý rằng có thể xảy ra lỗi #N/A. Các hàm đó là: Lookup, Vlookup, Hlookup, Match

3. Cách khắc phục lỗi N/A trong Excel

Bởi vì lỗi #N/A không phải là lỗi sai hàm, sai công thức mà chỉ là không tìm thấy đối tượng cần tìm. Do đó cách khắc phục thường dùng là biện luận trước trường hợp không tìm thấy đối tượng cần tìm. Nếu không tìm thấy thì trả về rỗng chứ không thực hiện công thức.

Cụ thể như sau:

Nếu lookup_value không tồn tại (là 1 ô trống) thì trả về rỗng ngay, không cần tới công thức (ví dụ: ô F8 sẽ tránh được lỗi)

Kiểm tra xem nếu lookup_value không tồn tại thì sẽ thông báo ngay rằng Giá trị cần tìm không tồn tại thay vì sử dụng ngay công thức (ô F9 báo kết quả là Không tồn tại thay cho lỗi #N/A)

Ngoài ra còn 1 vấn đề nữa gây lỗi #N/A mà người dùng khó nhận ra đó là: Dữ liệu cần tìm tuy nhìn bằng mắt thường giống nhau nhưng thực chất lại khác nhau về dạng dữ liệu.

Ví dụ:

Kiểm tra lại xem có thừa ký tự không, bởi việc thừa ký tự cũng dẫn tới lỗi không đồng nhất dữ liệu, gây ra lỗi không tồn tại dữ liệu cần tìm.

Cách khắc phục sửa lỗi #N/A

Format dữ liệu về cùng 1 loại, bao gồm cả ở bảng dữ liệu và ở giá trị tìm kiếm

Kiểm tra lại ký tự ở vùng giá trị tìm kiếm xem có ký tự thừa không

Sử dụng hàm COUNTIF để kiểm tra sự tồn tại của giá trị tìm kiếm trong vùng tìm kiếm

Như vậy là chúng ta có thể yên tâm làm việc với Excel mà không còn sợ lỗi #N/A nữa rồi.

Ngoài ra Học Excel Online xin giới thiệu với các bạn ” Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm “. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để đáp ứng yêu cầu công việc tổ chức, quản lý dữ liệu và lập báo cáo trên Excel.