Contents
Lỗi # N / A trong excel là một lỗi phổ biến của hầu hết người dùng Excel, nhưng điều quan trọng nhất là làm thế nào để sửa lỗi # N / A này, đặc biệt là # Không có lỗi khi sử dụng hàm VLOOKUP. hoặc TÌM KIẾM. Nếu bạn chưa biết cách khắc phục lỗi này thì cũng đừng lo lắng, hãy theo dõi bài viết thủ thuật vn này nhé. Trong bài viết này, những thủ thuật sẽ giúp bạn thoát khỏi nỗi ám ảnh mang tên # N / A lỗi trong hàm VLOOKUP VÀ HLOOKUP ở đây bạn có.
1. Lỗi # N / A là gì?
Trong tiếng Anh, N / A có nghĩa là “Chưa được trả lời” hoặc “Không có sẵn“ hoặc “Không áp dụng“, được dịch là không có câu trả lời hoặc không có câu trả lời đầy đủ hoặc không áp dụng cho việc tìm câu trả lời. Do đó, Lỗi # N / A thường thấp trong Excel nghĩa là không thể tìm thấy giá trị tương thích.
Đặc biệt, lỗi này thường xuyên xảy ra khi sử dụng hàm TÌM KIẾM và chức năng TÌM KIẾM vì Excel không thể xác định giá trị trong yêu cầu tra cứu. Ngay dưới đây, chúng tôi sẽ cùng bạn đi sâu hơn để xử lý lỗi # N / A khi sử dụng hai hàm này.
2. Cách sửa lỗi # N / A trong VLOOKUP
một. Lỗi xảy ra do sự cố trong khu vực tìm kiếm.
Vùng tìm kiếm có hai vấn đề chính: một là bạn chưa thiết lập vùng tìm kiế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 vùng tìm kiếm và cố định vùng tìm kiếm bằng ký tự “$”.
b. Xảy ra lỗi khi kết hợp hàm VLOOKUP / HLOOKUP với hàm LEFT, RIGHT hoặc MIDDLE.
Hãy làm theo ví dụ sau:
Chúng ta cần sử dụng HLOOKUP để tra cứu TÊN NGÀNH theo MÃ NGÀNH đã cho:
- Điều kiện tìm kiếm: Chỉ định ký tự thứ hai bằng cách sử dụng hàm MID.
- Khu vực tra cứu: bảng tra cứu cố định của tất cả các điểm
- Số thứ tự của hàng chứa giá trị đang được tra cứu: hàng thứ hai của bảng tra cứu điểm số
= HLOOKUP (MIDDLE (A2,2,1), $ B $ 7: $ E $ 8,2,0)
Cú pháp hàm hoàn toàn đúng nhưng nhấn Enter liên tục báo lỗi khiến chúng tôi thắc mắc WTF? Tôi không hiểu làm thế nào, tôi từ chối hiểu
Nguyên nhân ở đây là do Excel khó nhận dạng chữ và số, định dạng kết quả của hàm MID (hoặc LEFT hoặc RIGHT) luôn là văn bản, nhưng vùng tìm kiếm chỉ trả về kết quả là số.
Giải pháp trong trường hợp này cần phải chuyển đổi kết quả của MID thành một số, vì vậy chúng tôi sử dụng hàm VALUE để sửa lỗi này như sau:
= HLOOKUP (GIÁ TRỊ (TRUNG BÌNH (A2,2,1)), $ B $ 7: $ E $ 8,2,0)
C. Giá trị tham chiếu không tồn tại
Nguyên nhân phổ biến nhất của lỗi # N / A khi sử dụng hàm VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH là khi công thức không thể tìm thấy giá trị tham chiếu. Ví dụ: giá trị tra cứu của bạn không tồn tại trong dữ liệu nguồn.
Công thức trong ô E2 là = VLOOKUP (D2, $ D $ 6: $ E $ 8,2, FALSE). Không thể tìm thấy giá trị Banana, vì vậy công thức trả về lỗi # N / A.
Trong trường hợp này, không có giá trị “Banana” trong bảng tra cứu, vì vậy hàm VLOOKUP trả về Lỗi # N / A.
Giải pháp: Đảm bảo giá trị tra cứu tồn tại trong dữ liệu nguồn hoặc sử dụng trình gỡ lỗi như CÓ LỖI trong công thức
Ví dụ: = IF.ERROR (CÔNG THỨC (), 0)ở đó:
- Nếu công thức của bạn cho kết quả là một lỗi, hãy trả về 0; nếu không thì hiển thị kết quả của công thức
- Bạn có thể dùng “” để hiển thị không có gì hoặc thay thế nó bằng văn bản của riêng bạn: = IF.ERROR (FORMULA (), “Thông báo lỗi ở đây”)
d. dữ liệu khác nhau
Giá trị tra cứu và dữ liệu nguồn là các kiểu dữ liệu khác nhau. Ví dụ: chúng tôi đang cố gắng để hàm VLOOKUP tham chiếu đến một số, nhưng dữ liệu nguồn được lưu trữ dưới dạng văn bản.
Ví dụ cho thấy một công thức VLOOKUP trả về lỗi # N / A vì đầu vào tra cứu được định dạng là số nhưng bảng tra cứu được định dạng dưới dạng văn bản.
Giải pháp: Đảm bảo dữ liệu của bạn thuộc cùng một loại. Bạn có thể kiểm tra định dạng ô bằng cách chọn một ô hoặc một dải ô, sau đó nhấp chuột phải và chọn Định dạng ô (Định dạng ô)> Con số (hoặc nhấn Ctrl + 1) và thay đổi định dạng số, nếu cần.
tôi. Có thêm không gian trong các ô.
Bạn có thể sử dụng hàm TRIM để loại bỏ các khoảng trắng ở đầu hoặc cuối. Các ví dụ sau đây sử dụng hàm TRIM được lồng trong hàm VLOOKUP để loại bỏ khoảng trắng ở đầu khỏi tên có trong A2: A7 và trả về tên của các phòng ban.
Công thức trong ô E3 là {= VLOOKUP (D2, TRIM (A2: B7), 2, FALSE)} và phải được nhập bằng CTRL + SHIFT + ENTER.
= VLOOKUP (D2, TRIM (A2: B7), 2, FALSE)
F. Tìm kiếm kết quả tương đối và chính xác
Theo mặc định, các hàm tra cứu thông tin trong bảng phải được sắp xếp theo thứ tự tăng dần. Tuy nhiên, các hàm VLOOKUP và HLOOKUP chứa đối số. phạm vi tìm kiếm cho hàm này biết để tìm một kết quả phù hợp chính xác, ngay cả khi bảng không được sắp xếp. Để tìm một kết hợp chính xác, hãy đặt đối số. phạm vi tìm kiếm Mạnh SAI. Lưu ý rằng việc sử dụng TRUE, là một đối số yêu cầu hàm để tìm một kết quả phù hợp tương đối, không chỉ có thể gây ra #Error N / A nhưng nó cũng có khả năng trả về kết quả sai, như thể hiện trong ví dụ sau.
Trong ví dụ này, không chỉ giá trị “Banana” trả về lỗi # N / A mà giá trị “Pear” trả về giá trị sai. Điều này xảy ra do việc sử dụng đối số TRUE, đối số này yêu cầu hàm VLOOKUP tìm kiếm đối sánh tương đối thay vì đối sánh chính xác. Không có kết quả trùng khớp nào cho các giá trị “Banana” và “Pear” đứng trước “Peach” theo thứ tự bảng chữ cái. Trong trường hợp này, việc sử dụng hàm VLOOKUP với đối số FALSE sẽ trả về giá đúng cho giá trị “Pear”, nhưng giá trị “Banana” vẫn sẽ trả về lỗi # N / A vì không có giá trị “Bananas”. ” danh sách tìm kiếm.
Nếu bạn đang sử dụng hàm MATCH, hãy thử thay đổi giá trị của đối số match_type để chỉ định thứ tự sắp xếp của bảng. Để tìm một đối sánh chính xác, hãy đặt đối số match_type thành 0 (không).
Phần kết:
Vì vậy, chúng tôi đã tìm ra tất cả các nguyên nhân. # N / A lỗi trong hàm VLOOKUP VÀ HLOOKUP cũng như cách khắc phục lỗi. Nếu vẫn không khắc phục được lỗi #NA, hãy comment bên dưới bài viết này.