Top 13 # Xem Nhiều Nhất Xóa #N/A Trong Excel Mới Nhất 6/2023 # Top Like

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ị.

Để sắp xếp thứ tự tăng dần hay nhỏ dần trên Excel, chúng ta có thể sử dụng công cụ Sort có sẵn. Tuy nhiên cách này chỉ áp dụng với tài liệu có nội dung ngắn, đơn giản mà thôi. Trong trường hợp người dùng phải làm việc với bảng số liệu cần so sánh phức tạp, tìm giá trị lớn nhất, nhỏ nhất, hoặc dùng kết quả để tính toán vào công thức khác thì cần tới cách làm khác.

1. Hướng dẫn tìm mức lương cao thứ N

Bước 1:

Để tìm người có mức lương cao thứ 2 trong bảng trên, trước tiên nhấn vào ô hiện kết quả là ô D9 trong bảng rồi nhấn vào Formulas.

Tiếp đó nhấn vào mục Insert Function, hoặc ấn vào fx như hình bên dưới.

Bước 2:

Xuất hiện giao diện Insert Function để người dùng thiết lập các mục.

Or select a category: Chọn Statistical.

Select a function: Chọn LARGE.

Cuối cùng nhấn Ok để đồng ý.

Array nhập vùng dữ liệu muốn xếp hạng. Ở ví dụ trên là D2:D7.

K để nhập thứ muốn tìm là số 2 với người có mức lương cao thứ 2.

Trong bảng cũng sẽ xuất hiện kết quả xem trước để bạn được biết. Nhấn OK để nhập kết quả vào bảng dữ liệu.

Lưu ý với bạn đọc:

K sẽ là thứ hạng muốn tìm trong danh sách, mặc định thứ hạng lớn nhất sẽ là 1, lớn thứ 2 sẽ là số 2,…

Công thức cho việc tìm kếm giá trị lớn thứ k này là =LARGE(Array,k), với Array là vùng dữ liệu muốn tìm giá trị và k là thứ hạng.

2. Cách tính tổng lương 3 người cao nhất

Chúng ta sẽ sử dụng hàm LARGE 3 lần trong công thức rồi cộng lại. Công thức sẽ là =LARGE(D2:D7,1)+LARGE(D2:D7,2)+LARGE(D2:D7,3) và nhấn Enter.

Kết quả sẽ được tổng lương 3 người cao nhất như hình.

3. Cách tìm lương thấp nhấp trong bảng

Bước 1:

Tại ô nhập kết quả cho lương người thấp nhấn cũng nhấn vào Formulas, chọn Insert Function rồi thực hiện các thiết lập sau đâu.

Or select a category: Chọn Statistical.

Select a function: Chọn SMALL.

Nhấn Ok để thực hiện.

Array chọn vùng dữ liệu là D2:D7.

K nhập thứ hạng muốn tìm kiếm là thấp nhất với số 1.

Ngay lập tức bạn cũng sẽ nhìn thấy kết quả với số tiền lương thấp nhất trong bảng. Nhấn OK để thực hiện.

Như vậy bạn có thể dễ dàng tìm kiếm người có thứ hạng k trong bảng số liệu. Thực chất chúng ta sẽ sử dụng 2 hàm là hàm LARGE để tìm thứ hạng giá trị cao và hàm SMALL để tìm số liệu có thứ hạng thấp. Thứ tự số hạng sẽ dùng chung là 1, 2, 3,… trở đi.

Lỗi #N/A trong excel là vấn nạn của nhiều người dùng Excel, quan trọng là làm thế nào để sửa lỗi, đặc biệt là khi sử dụng hàm VLOOKUP hoặc HLOOKUP.

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

Trong tiếng Anh, N/A là viết tắt của “No Answer” hoặc “Not Available” hoặc “Not Applicable”, có nghĩa là không có câu trả lời hoặc không tồn tại câu trả lời phù hợp hoặc không thể áp dụng để tìm câu trả lời. Do đó, lỗi #N/A khi qua công thức excel không thể tìm được giá trị tương thích.

Đặc biệt, lỗi này thường xảy ra khi sử dụng hàm VLOOKUP và hàm HLOOKUP vì Excel không thể xác định giá trị theo yêu cầu tìm kiếm. Tiếp theo đây, chúng ta sẽ đi sâu vào xử lý lỗi #N/A khi sử dụng hai hàm này.

2/ Cách khắc phục lỗi #N/A

Trường hợp 1: Lỗi xảy ra vì vấn đề ở vùng tìm kiếm.

Vùng tìm kiếm có 2 vấn đề chính: một là bạn chưa cố định vùng tìm kếm, hai là giới hạn vùng tìm kiếm chưa đủ rộng.

Giải pháp đơn giản là giới hạn lại vùng tìm kiếm và cố địng vùng tìm kiếm bằng ký tự “$”

Cùng theo dõi ví dụ sau đây:

Ta cần dùng HLOOKUP để dò TÊN NGÀNH theo MÃ NGÀNH cho trước:

Điều kiện tìm kiếm: chỉ ra ký tự đứng thứ 2 bằng hàm MID

Vùng tìm kiếm: đã cố định toàn bộ bảng tra cứu điểm

Số thứ tự của dòng chứa giá trị đang tìm: dòng thứ 2 của bảng tra cứu điểm

Cú pháp hàm đã hoàn toàn chuẩn xác, tuy vậy vẫn bị báo lỗi khiến người dùng vô cùng khó chịu.

Nguyên nhân ở đây do sự khó nhận diện chữ và con số của excel, định dạng kết quả của hàm MID (hay LEFT hoặc RIGHT) luôn là văn bản, nhưng khu vực tìm kiếm chỉ đưa kết quả là con số.

Giải pháp trong trường hợp này cần chuyển đổi kết quả của MID thành con số, nên chúng ta dùng hàm VALUE để sửa lỗi này như sau:

Trường hợp 3: Hạn chế khu vực tìm kiếm của hàm VLOOKUP và HLOOKUP

Hàm VLOOKUP và HLOOKUP chỉ có thể tìm kiếm theo cột hoặc theo hàng, nếu xét khu vực tìm kiếm là cả một bảng sẽ gây ra lỗi. Lợi thế tìm kiếm đa vị trí là của hàm MATCH, nhưng không đa dạng được yêu cầu. Vậy nên chúng ta có thể kết hợp hàm INDEX và hàm MATCH thay thế cho hàm VLOOKUP hay HLOOKUP.

1. Lỗi #N/A trong Excel

Lỗi #N/A trong Excel xảy ra khi không tìm thấy hoặc xác định được thông tin, dữ liệu quan trọng nào đó, chẳng hạn như tên nhân viên trong bảng tính sai chính tả, tùy chọn màu sắc không tồn tại, … . Ngoài ra một số nguyên nhân khác gây ra lỗi #N/A trong Excel có thể là do các ký tự khoảng trắng thừa, lỗi chính tả hoặc bảng tra cứu không đầy đủ.

Lỗi này xuất hiện chủ yếu trong các hàm tra cứu cổ điển trong Excel, bao gồm các hàm VLOOKUP, HLOOKUP, LOOKUP và hàm MATCH.

Cách tốt nhất để ngăn lỗi #N/A xảy ra là đảm bảo các giá trị tra cứu và bảng tra cứu phải chính xác và đầy đủ. Nếu không mong muốn lỗi #N/A xảy ra, trước hết kiểm tra xem:

Giá trị tra cứu đã viết đúng chính tả hay chưa và không chứa các ký tự khoảng trắng.

Các giá trị trong bảng tra cứu phải được viết đúng chính tả và không chứa khoảng trắng.

Bảng tra cứu phải chứa tất cả các giá trị.

Cung cấp phạm vi tra cứu cho hàm.

Loại giá trị tra cứu = loại bảng tra cứu (tức là cả 2 phải là văn bản, hoặc là chữ số, …).

Lưu ý: Nếu lỗi #N/A xảy ra, kết quả mà bạn nhận được sẽ không chính xác. Cần đảm bảo bạn đã khớp chính xác các cấu hình. Chế độ khớp gần đúng sẽ trả về tất cả các kết quả không chính xác.

2. Cách sửa lỗi #N/A trong Excel Cách 1: Sử dụng hàm IFERROR để bẫy lỗi #N/A

Để sửa lỗi #N/A, giải pháp đầu tiên là chúng ta sử dụng hàm IFERROR để bẫy lỗi. Hàm IFERROR có thể bắt bất kỳ lỗi nào và trả về kết quả thay thế.

=IFERROR(VLOOKUP(E7,data,2,0),”Not found”)

Nếu hàm VLOOKUP trả về lỗi, hàm IFERROR sẽ bắt lỗi đó và trả về kết quả “Not found”.

Cách 2: Bẫy lỗi #N/A bằng hàm IFNA

Hàm IFNA cũng có thể bẫy và xử lý các lỗi #N/A cụ thể. Cú pháp sử dụng hàm IFNA tương tự như hàm IFERROR:

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

Ưu điểm của hàm IFNA là chỉ nhắm mục tiêu lỗi #N/A. Ngược lại hàm IFERROR sẽ bắt bất kỳ lỗi nào mà hàm phát hiện. Chẳng hạn ngay cả khi nhập tên hàm VLOOKUP không viết hoa, hàm IFERROR cũng sẽ trả về lỗi “Not found”.

=IFERROR(VLOOKUP(E7,data,2,0),””) Cách 3: Sử dụng hàm INDEX và hàm MATCH để sửa lỗi #N/A

Hàm MATCH cũng trả về lỗi #N/A trong trường hợp nếu hàm không tìm thấy giá trị. Nếu đang sử dụng kết hợp hàm INDEX và MATCH, chúng ta có thể bẫy lỗi #N/A theo cùng một cách. Trong ví dụ trên, công thức trong ô F5 sẽ là: