Le Linh 01/11/2021 N/A trong tiếng anh có thể là [Not Available] hoặc [No Answer] hoặc [Not Applicable]. Cả 3 từ trên đều có thể hiểu là không sẵn có hoặc không thể áp dụng. Hay có thể hiểu lỗi #N/A nghĩa là trong công thức của bạn có chứa nội dung không có sẵn trong vùng dữ liệu thuộc Excel, dẫn đến việc tính toán, hoàn thành công thức sẽ không có kết quả như ý muốn. #N/A! trong Excel là lỗi được trả về khi công thức không tìm thấy giá trị. Các hàm có thể dễ mắc lỗi #N/A! như: VLOOKUP, HLOOKUP, IF,... Nguyên nhân: Do hàm tính không tìm thấy giá trị. Nghĩa là trong vùng tìm kiếm bạn nhập vào không có dữ liệu để truy suất. Ví dụ: Tìm sản phẩm Xoài trong bảng dữ liệu sau: Bước 1: Mình nhập hàm =VLOOKUP["XOÀI",A2:D6,4,0] vào bảng tính Excel. Bước 2: Sau đó bạn nhấn phím Enter. Vì bảng dữ liệu không có tên cần tìm nên Excel sẽ xuất hiện lỗi #N/A! như hình. Cách khắc phục: Kiểm tra dữ liệu tính toán của hàm và chỉnh sửa lại. Ở đây mình chỉnh lại sản phẩm cần tìm là Sầu Riêng với công thức =VLOOKUP["Sầu Riêng",B2:D6,2,0] nên Excel trả kết quả như hình. Nguyên nhân: Khi không cố định vùng tìm kiếm thì công thức copy trong Excel sẽ tự động lấy dữ liệu từ ô đầu tiên xuống cho các ô còn lại thì vùng tìm kiếm sẽ thay đổi theo. Ví dụ: Như bảng minh họa trên mình sử dụng công thức để tìm sản phẩm Sầu Riêng nhưng không cố định vùng dữ liệu. Khi mình copy xuống dưới thì Excel sẽ báo lỗi như hình. Cách khắc phục: Để khắc phục lỗi này cần cố định lại vùng dữ liệu trong công thức. Bước 1: Trong vùng dữ liệu chọn ở công thức tính. Bạn bôi đen vùng dữ liệu đó. Ở đây mình chọn bôi đen ô B2:D6 như hình. Bước 2: Trên bàn phím bạn ấn phím F4. Excel sẽ tự động cố định vùng dữ liệu được chọn như hình. Sau đó bạn có thể copy mà không sợ dữ liệu bị thay đổi. Nguyên nhân: Thường gặp lỗi #N/A! khi định dạng không trùng khớp giữa kết quả hiển thị và dữ liệu tra cứu. Ví dụ: Dùng hàm Hlookup để tìm điểm khu vực dựa và số báo danh của sinh viên. Bước 1: Trong bảng tính mình nhập hàm =HLOOKUP[MID[A3,4,1],$C$9:$F$10,2,0] như hình. Trong đó: Bước 2: Minh nhấn phím Enter thì Excel báo lỗi như hình. Cách khắc phục: Có 2 cách khắc phục lỗi do nguyên nhân này gây ra là: Ở đây, mình sử dụng cách hàm Value để xử lý lỗi trên. Bằng cách thêm hàm Value vào như sau =HLOOKUP[VALUE[MID[A3,4,1]],$C$9:$F$10,2,0].
Sau đó bạn ấn phím Enter. Excel sẽ hiển thị kết quả như hình.
Ví dụ: Điền tên hãng vào mã lô hàng tương ứng.
Bước 1: Mình nhập vào bảng tính Excel công thức hàm Vlookup: =VLOOKUP[LEFT[B3,3],E8:F11,2,0] như hình.
Giải thích hàm:
- VLOOKUP: Là tên hàm tính toán dò tìm dữ liệu.
- LEFT[A3,3]: Là lệnh hàm tìm dữ liệu ô A3 với 3 ký tự.
- E8:F11: Là vùng dữ liệu tìm kiếm.
Bước 2: Bạn ấn phím Enter. Excel sẽ hiển thị kết quả như hình.
Cách khắc phục: Để khắc phục lỗi này bạn kiểm tra và chỉnh lại giá trị trong hàm LEFT vì ô bảng tính chỉ chứa 2 ký tự mà ở đây mình lấy giá trị bằng 3 như sau =VLOOKUP[LEFT[A3,2],HANGSANXUAT,2,0]. Sau khi bạn sửa lại giá trị thì Excel hiển thị kết quả như hình.
Ví dụ: Điền tên hãng vào mã lô hàng tương ứng.
Bước 1: Mình nhập vào bảng tính Excel công thức hàm Vlookup: =VLOOKUP[RIGHT[A3,2],D7:E11,2,0] như hình.
Trong đó:
- RIGHT[A3,2]: Lấy 2 kí tự ở bên phải của ô A3 [cụ thể ở đây là SS].
- D7:E11: Vị trí của BẢNG THÔNG TIN chứa mã hàng sản xuất.
- 2: Vị trí lấy ở cột tên hãng.
- 0: Chọn kiểu giá trị chính xác tuyệt đối.
Bước 2: Bạn nhấn phím Enter. Excel sẽ hiển thị kết quả như hình.
Ở đây mình đã kiểm tra giá trị cần lấy và hàm tính toàn là đúng vậy mà Excel vẫn báo lỗi #N/A!. Nguyên nhân là vì trong điều kiện cần lấy ở bảng thông tin có chứa khoảng trắng.
Cách khắc phục: Ở đây mình kiểm tra và xóa bỏ khoảng trắng trong mã hàng SX ở bảng thông tin và Excel hiển thị kết quả như hình.
Nguyên nhân: Lỗi #N/A xuất hiện ở trường hợp này là do trong điều kiện tìm kiếm hoặc giá trị tương ứng trong vùng điều kiện tìm kiếm có thừa một hoặc một số dấu cách ở đằng sau.
Cách khắc phục:
- Cách đơn giản nhất để khắc phục lỗi nãy là bạn chọn lần lượt từng giá trị. Giá trị dùng làm điều kiện và giá trị tương ứng trong vùng điều kiện.
- Nhấn phím F2 nếu thấy dấu nháy chuột nằm cách xa ký tự cuối cùng một hoặc một vài dấu cách thì bạn xóa các dấu cách thừa đi. Làm như vậy cho cả 2 giá trị.
Đối với lỗi này thì bạn cũng có thể sử dụng hàm ISNA để sửa lỗi 1 cách nhanh, dễ dàng chỉ với thao tác lồng hàm ISNA vào.
Trên đây là bài viết về lỗi #N/A! trong Excel và những cách sửa lỗi #N/A! trong Excel. Hy vọng bài viết sẽ giúp bạn thực hiện được hiểu rõ hơn về lỗi này và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé.
Khi sử dụng hàm dò tìm Vlookup nếu người dùng không chú ý sẽ dễ gặp một sổ lỗi cơ bản như lỗi #N/A, #VALUE… Chính vì vậy, hocexcelcoban.com sẽ tổng hợp và hướng dẫn bạn các lỗi thường gặp khi sử dụng hàm Vlookup bị lỗi cũng như cách sửa lỗi
Hàm VLOOKUP [Tra cứu dọc] tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng và sau đó trả về một giá trị trong cùng một hàng từ một cột khác mà bạn chỉ định.
Cú pháp:
=VLOOKUP[Lookup_value, Table, Col_index_num, [Range_lookup]]- Lookup_value – Giá trị cần tìm trong cột đầu tiên của bảng.
- Table – Bảng để truy xuất một giá trị.
- Col_index – Cột trong bảng để truy xuất một giá trị.
- range_lookup – [tùy chọn] TRUE = đối sánh gần đúng [mặc định]. FALSE = đối sánh chính xác
CÁC LỖI PHỔ BIẾN THƯỜNG GẶP KHI SỬ DỤNG HÀM VLOOKUP
1. HÀM VLOOKUP BỊ LỖI #N/A
Khi hàm VLOOKUP không thể tìm thấy giá trị trong bảng tra cứu, hàm này sẽ trả về lỗi #N/A.
– Bạn có thể xem thêm về Hàm Vlookup nâng cao tại đây: Học excel cơ bản
1.1 LỖI #N/A KHÔNG CÓ TRONG BẢNG TÌM KIẾM
Khi giá trị chúng ta đang tìm kiếm không có trong bảng tìm kiếm thì lỗi #N/A cũng có thể xảy ra.
Hình 1: Hàm vlookup lỗi #N/A
1.2 SỬ DỤNG VLOOKUP ĐỂ DÒ TÌM GẦN ĐÚNG
Hình 2: Hàm vlookup bị lỗi #N/A TRUE
Nếu tham số range_lookup chúng ta bỏ qua hoặc để là TRUE thì loại VLOOKUP đang được sử dụng là loại dùng để dò tìm gần đúng. Trong trường hợp này, lỗi #N/A phát sinh khi:
- Giá trị cần tìm kiếm nhỏ hơn giá trị nhỏ nhất trong mảng tìm kiếm
- Cột tìm kiếm không được sắp xếp theo thứ tự nhỏ đến lớn
1.3 KHI TRA CỨU CỘT TÌM KIẾM KHÔNG Ở VỊ TRÍ ĐẦU TIÊN
Vị trí cột rất quan trọng ở đây:
Hình 3: Lỗi #N/A khi sử dụng vlookup
Trong trường hợp bạn không thể thay đổi vị trí các cột trong dữ liệu, bạn nên sử dụng hàm INDEX và MATCH sẽ tốt hơn sử dụng hàm VLOOKUP và giúp bạn xử lý trường hợp này.
1.4 SỐ NHƯNG ĐỊNH DẠNG KIỂU CHỮ:
Ví dụ như hình vẽ sau đây:
Hình 4: Số định dạng kiểu chữ
Để giải quyết lỗi này, chúng ta sẽ làm như sau: Chọn toàn bộ những ô bị lỗi này, bấm phím tắt CTRL + 1 hộp thoại Number Format sẽ được hiện ra, bấm chọn thẻ Number > Number rồi bấm OK. Hoặc làm như hình vẽ bên dưới:
1.5 SỬA LỖI CHÍNH TẢ TRONG GIÁ TRỊ ĐƯỢC TÌM KIẾM [LOOKUP VALUE]
Dữ liệu của chúng ta có thể đến từ nhiều nguồn, nguồn từ các hệ thống khác, nguồn từ Web, nguồn được gõ tay lại từ 1 tài liệu đã in ra . Đây là nơi mà dữ liệu phát sinh lỗi, phát sinh những kí tự lạ. Nếu chúng ta rà soát lại lỗi chính tả, làm sạch dữ liệu xong, thì nhiều khả năng lỗi #NA này sẽ biến mất.
2.XỨ LÝ LỖI #VALUE KHI SỬ DỤNG VLOOKUP:
Một vài nguyên nhân chính sau đây:
2.1 GIÁ TRỊ TRA CỨU BẰNG VLOOKUP CÓ ĐỘ DÀI LỚN HƠN 255 KÍ TỰ
Kích thước tối đa của giá trị tra cứu VLOOKUP là 255 ký tự. Thay vào đó chúng ta có thể giải quyết bằng kết hợp INDEX với MATCH
2.2 THAM CHIẾU KHÔNG CHÍNH XÁC
Nếu bạn sử dụng VLOOKUP để tra cứu dữ liệu trong 1 file Excel khác và đường dẫn đến file Excel này không hoạt động, có thể do file excel không còn được lưu ở đó nữa hoặc vì lý do quyền truy cập.
2.3 CỘT THAM SỐ LẤY DỮ LIỆU VỀ NHỎ HƠN 1
Bình thường, cú pháp của VLOOKUP như sau:
=VLOOKUP[Lookup_value, Table, Col_index_num, [Range_lookup]]Nếu tham số Col_index_num có giá trị nhỏ hơn 1, bạn sẽ gặp lỗi #VALUE khi sử dụng VLOOKUP
3. Lỗi #NAME khi sử dụng VLOOKUP:
3.1 HÀM VLOOKUP BỊ LỖI SAI CHÍNH TẢ
Nếu hàm VLOOKUP bị lỗi của bạn không phải là #VALUE hoặc #N/A, thì đó có thể là lỗi #NAME. Đừng hoảng hốt! Đây là lỗi VLOOKUP dễ nhất để khắc phục.
Hình 5: Hàm vlookup bị lỗi #NAME
Một lỗi #NAME xuất hiện khi bạn viết sai chính tả một hàm trong Excel, cho dù đó là VLOOKUP hoặc một hàm khác như SUM. Nhấp vào ô VLOOKUP của bạn và kiểm tra kỹ xem bạn có thực sự đánh vần chính xác VLOOKUP không.
3.2 TRONG QUÁ TRÌNH COPY CÔNG THỨC THAM CHIẾU BỊ THAY ĐỔI
Khi bạn copy hay di chuyển công thức, nếu bạn gặp phải lỗi #NAME, hãy nghĩ xem công thức của bạn đã có những kí tự $ để khoá tham chiếu hay chưa.
VD: Thay vì viết V1:L6, bạn cần viết là $V$1:$L$6
3.3 CỘT ĐƯỢC THÊM HOẶC BỚT TRONG BẢNG TRA CỨU
Vấn đề khi xử dụng VLOOKUP mà chúng ta rất hay gặp đó là: thay đổi cấu trúc bảng tính: thêm và bớt cột, khi thêm và bớt cột như vậy, VLOOKUP có tham số Col_index không được cập nhật theo, gây nên lỗi #NAME.
Giải pháp cho trường hợp này: Sử dụng hàm INDEX và MATCH
4. Lỗi #REF! khi sử dụng VLOOKUP:
Nguyên nhân của lỗi #REF! :
- Đối số col_index_num được cung cấp lớn hơn số trong cột table_array được cung cấp.
- Công thức cố gắng tham chiếu các ô không tồn tại. Điều này có thể do lỗi tham chiếu khi Vlookup được sao chép vào các ô khác.
Hình 6: Lỗi #REF! khi sử dụng VLOOKUP
5. SỬ DỤNG HÀM IFERROR HOẶC ISERROR ĐỂ XỬ LÝ LỖI:
5.1 SỬ DỤNG VLOOKUP KẾT HỢP VỚI IFERROR, IFNA
Khi hàm VLOOKUP không thể tìm thấy giá trị trong bảng tra cứu, hàm này sẽ trả về lỗi #N/A… Hàm IFERROR cho phép bạn bắt lỗi và trả về giá trị tùy chỉnh của riêng bạn khi có lỗi.
Cú pháp:
=IFERROR[VLOOKUP[value, Table, Col_index, [Range_lookup]], < thông báo lỗi >]HÌnh 7: Xử lý lỗi VLOOKUP kết hợp IFERROR
Nếu bạn có giá trị tra cứu trong ô H2 và giá trị tra cứu trong một dải ô có tên bảng và bạn muốn ô trống nếu không tìm thấy bảng tra cứu nào, bạn có thể sử dụng:
= IFERROR [ VLOOKUP [ H2 , B2:E9, 4 , FALSE ], "" ]Nếu bạn muốn trả lại thông báo “Not found” khi không tìm thấy kết quả phù hợp nào, hãy sử dụng
= IFERROR [ VLOOKUP [ H2 , B2:E9, 4 , FALSE ], "Not found" ]Tương tự IFNA
Trong Excel 2013, hàm IFNA có sẵn để xử lý các lỗi #N/A cụ thể. Cú pháp sử dụng giống như với IFERROR:
= IFERROR [ VLOOKUP [ H2 , B2:E9, 4 , FALSE ], "Not found" ] = IFNA [ VLOOKUP [ H2 , B2:E9, 4 , FALSE ], "Not found" ]5.2 SỬ DỤNG VLOOKUP KẾT HỢP VỚI ISERROR
Vì IFERROR chỉ được giới thiệu kể từ Excel 2007, nếu các bạn vẫn cần xử lý và làm việc với những file Excel cũ hơn, thì hàm ISERROR sẽ giúp các bạn xử lý lỗi của VLOOKUP. Cú pháp sử dụng như sau:
=IF[ ISERROR [ VLOOKUP[…], thông báo lỗi ], VLOOKUP […] ]KẾT LUẬN
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Quan trọng bài học này chúng tôi mong bạn nắm được các lỗi thường gặp khi sử dụng hàm Vlookup bị lỗi cũng như cách sửa lỗi giúp bạn sử dụng Excel một cách chuyên nghiệp hơn.
GÓC HỌC TẬPTHAM KHẢO THÊM
>> Hàm VLOOKUP kết hợp hàm IF >> Hàm VLOOKUP trong Excel >> Hàm VLOOKUP 2 điều kiện >> Hàm VLOOKUP kết hợp hàm SUMIFVIDEO HƯỚNG DẪN SỬA LỖI HÀM VLOOKUP:bị lỗi hàm vlookuphàm vlookup bị lỗilỗi #N/Axử lý lỗi vlookup