Nội dung
Bài học này giải thích cách nhanh chóng đối phó với một tình huống mà một hàm VPR (VLOOKUP) không muốn hoạt động trong Excel 2013, 2010, 2007 và 2003 và cách xác định và sửa các lỗi phổ biến cũng như khắc phục các hạn chế VPR.
Trong một số bài viết trước, chúng ta đã khám phá các khía cạnh khác nhau của hàm VPR trong Excel. Nếu bạn đã đọc chúng một cách cẩn thận, bây giờ bạn nên là một chuyên gia trong lĩnh vực này. Tuy nhiên, không phải vô cớ mà nhiều chuyên gia Excel tin VPR một trong những tính năng phức tạp hơn. Nó có một loạt các hạn chế và tính năng trở thành nguồn gốc của nhiều vấn đề và lỗi.
Trong bài viết này, bạn sẽ tìm thấy những giải thích đơn giản về các lỗi #TẠI (# N / A), #TÊN? (#Tên và #GIÁ TRỊ! (#VALUE!) Xuất hiện khi làm việc với hàm VPR, cũng như các kỹ thuật và phương pháp đối phó với chúng. Chúng tôi sẽ bắt đầu với các trường hợp phổ biến nhất và lý do rõ ràng nhất tại sao. VPR không hoạt động, vì vậy tốt hơn là nghiên cứu các ví dụ theo thứ tự mà chúng được đưa ra trong bài báo.
Sửa lỗi # N / A trong hàm Vlookup trong Excel
Trong công thức có VPR thông báo lỗi #TẠI (# N / A) có nghĩa là không có sẵn (không có dữ liệu) - xuất hiện khi Excel không thể tìm thấy giá trị bạn đang tìm kiếm. Điều này có thể xảy ra vì một số lý do.
1. Giá trị mong muốn bị sai chính tả
Ý tưởng tốt để kiểm tra mục này đầu tiên! Lỗi chính tả thường xảy ra khi bạn làm việc với lượng dữ liệu rất lớn, bao gồm hàng nghìn dòng hoặc khi giá trị bạn đang tìm kiếm được viết thành công thức.
2. Lỗi # N / A khi tìm kiếm kết quả gần đúng với hàm VLOOKUP
Nếu bạn sử dụng công thức có điều kiện tìm kiếm so khớp gần đúng, tức là đối số phạm vi_lookup (range_lookup) là TRUE hoặc không được chỉ định, công thức của bạn có thể báo lỗi # N / A trong hai trường hợp:
- Giá trị cần tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng đang được tra cứu.
- Cột tìm kiếm không được sắp xếp theo thứ tự tăng dần.
3. Lỗi # N / A khi tìm kiếm đối sánh chính xác với hàm VLOOKUP
Nếu bạn đang tìm kiếm một kết hợp chính xác, tức là đối số phạm vi_lookup (range_lookup) là FALSE và không tìm thấy giá trị chính xác, công thức cũng sẽ báo lỗi # N / A. Tìm hiểu thêm về cách tìm kiếm các kết quả phù hợp chính xác và gần đúng với một hàm VPR.
4. Cột tìm kiếm không nằm ngoài cùng bên trái
Như bạn có thể biết, một trong những hạn chế đáng kể nhất VPR nó không thể hướng về bên trái, do đó cột tra cứu trong bảng của bạn phải ở ngoài cùng bên trái. Trong thực tế, chúng ta thường quên điều này, dẫn đến công thức không hoạt động và bị lỗi. # N / A.
Phán quyết: Nếu không thể thay đổi cấu trúc dữ liệu để cột tìm kiếm nằm ngoài cùng bên trái, bạn có thể sử dụng kết hợp các hàm INDEX (INDEX) và THÊM ĐƯỢC TIẾP XÚC (MATCH) như một giải pháp thay thế linh hoạt hơn cho VPR.
5. Các số được định dạng dưới dạng văn bản
Một nguồn lỗi khác # N / A trong công thức với VPR là các số ở định dạng văn bản trong bảng chính hoặc bảng tra cứu.
Điều này thường xảy ra khi bạn nhập thông tin từ cơ sở dữ liệu bên ngoài hoặc khi bạn nhập dấu nháy đơn trước một số để giữ số XNUMX ở đầu.
Các dấu hiệu rõ ràng nhất của một số ở định dạng văn bản được thể hiện trong hình bên dưới:
Ngoài ra, số có thể được lưu trữ ở định dạng Tổng Quát (Chung). Trong trường hợp này, chỉ có một tính năng đáng chú ý - các con số được căn chỉnh ở cạnh trái của ô, trong khi theo mặc định, chúng được căn chỉnh sang cạnh phải.
Phán quyết: Nếu đó là một giá trị duy nhất, chỉ cần nhấp vào biểu tượng lỗi và chọn Chuyển đổi sang số (Chuyển đổi thành Số) từ menu ngữ cảnh.
Nếu đây là trường hợp có nhiều số, hãy chọn chúng và nhấp chuột phải vào khu vực đã chọn. Trong menu ngữ cảnh xuất hiện, hãy chọn Format Cells (Định dạng ô)> tab Con số (Số)> định dạng Con số (Số) và nhấn OK.
6. Có khoảng trắng ở đầu hoặc cuối
Đây là lý do ít rõ ràng nhất cho lỗi. # N / A trong chức năng VPR, vì rất khó để nhìn thấy những khoảng trống thừa này bằng mắt thường, đặc biệt là khi làm việc với các bảng lớn, khi hầu hết dữ liệu nằm ngoài màn hình.
Giải pháp 1: Khoảng trắng thừa trong bảng chính (nơi chứa hàm Vlookup)
Nếu các khoảng trống thừa xuất hiện trong bảng chính, bạn có thể đảm bảo rằng các công thức hoạt động chính xác bằng cách bao quanh đối số lookup_value (lookup_value) vào một hàm TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Giải pháp 2: Thêm khoảng trắng trong bảng tra cứu (trong cột tra cứu)
Nếu các khoảng trắng thừa nằm trong cột tìm kiếm - các cách đơn giản # N / A trong công thức với VPR không thể tránh khỏi. Thay vì VPR Bạn có thể sử dụng công thức mảng với sự kết hợp của các hàm INDEX (MỤC LỤC), THÊM ĐƯỢC TIẾP XÚC (TRẬN ĐẤU) и TRIM (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Vì đây là một công thức mảng, đừng quên nhấn Ctrl + Shift + Enter thay vì bình thường đăng ký hạng mục thiđể nhập công thức một cách chính xác.
Lỗi #VALUE! trong công thức với hàm VLOOKUP
Trong hầu hết các trường hợp, Microsoft Excel báo cáo lỗi #GIÁ TRỊ! (#VALUE!) Khi giá trị được sử dụng trong công thức không khớp với kiểu dữ liệu. Liên quan VPR, thì thường có hai lý do gây ra lỗi #GIÁ TRỊ!.
1. Giá trị bạn đang tìm kiếm dài hơn 255 ký tự
Hãy cẩn thận: chức năng VPR không thể tìm kiếm các giá trị chứa nhiều hơn 255 ký tự. Nếu giá trị bạn đang tìm kiếm vượt quá giới hạn này, bạn sẽ nhận được thông báo lỗi. #GIÁ TRỊ!.
Phán quyết: Sử dụng nhiều tính năng INDEX + MATCH (INDEX + MATCH). Dưới đây là một công thức sẽ hoạt động tốt cho tác vụ này:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Đường dẫn đầy đủ đến sổ làm việc tìm kiếm không được chỉ định
Nếu bạn đang truy xuất dữ liệu từ một sổ làm việc khác, bạn phải chỉ định đường dẫn đầy đủ đến tệp đó. Cụ thể hơn, bạn phải bao gồm tên sổ làm việc (bao gồm cả phần mở rộng) trong dấu ngoặc vuông [], theo sau là tên trang tính, theo sau là dấu chấm than. Tất cả cấu trúc này phải được đặt trong dấu nháy đơn, trong trường hợp tên sách hoặc trang tính chứa khoảng trắng.
Đây là cấu trúc hoàn chỉnh của hàm VPR để tìm kiếm trong một cuốn sách khác:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Công thức thực có thể trông như thế này:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Công thức này sẽ tra cứu giá trị ô A2 trong một cột B trên trang tính Sheet1 trong sổ làm việc Giá mới và trích xuất giá trị tương ứng từ cột D.
Nếu bất kỳ phần nào của đường dẫn bảng bị bỏ qua, hàm của bạn VPR sẽ không hoạt động và sẽ báo lỗi #GIÁ TRỊ! (ngay cả khi sổ làm việc với bảng tra cứu hiện đang mở).
Để biết thêm thông tin về chức năng VPRtham chiếu đến một tệp Excel khác, hãy xem bài học: Tìm kiếm một sổ làm việc khác bằng hàm VLOOKUP.
3. Đối số Column_num nhỏ hơn 1
Thật khó để tưởng tượng một tình huống mà ai đó nhập giá trị nhỏ hơn 1để chỉ ra cột mà từ đó trích xuất giá trị. Mặc dù có thể xảy ra nếu giá trị của đối số này được tính toán bởi một hàm Excel khác được lồng trong VPR.
Vì vậy, nếu nó xảy ra rằng đối số col_index_num (column_number) nhỏ hơn 1chức năng VPR cũng sẽ báo lỗi #GIÁ TRỊ!.
Nếu đối số col_index_num (column_number) lớn hơn số cột trong mảng đã cho, VPR sẽ báo lỗi #REF! (#SSYL!).
Lỗi #NAME? trong VLOOKUP
Trường hợp đơn giản nhất là một sai lầm #TÊN? (#NAME?) - sẽ xuất hiện nếu bạn vô tình viết tên hàm bị lỗi.
Giải pháp là rõ ràng - hãy kiểm tra chính tả của bạn!
VLOOKUP không hoạt động (giới hạn, cảnh báo và quyết định)
Ngoài cú pháp khá phức tạp, VPR có nhiều hạn chế hơn bất kỳ hàm Excel nào khác. Vì những hạn chế này, các công thức có vẻ đơn giản với VPR thường dẫn đến những kết quả không như mong đợi. Dưới đây, bạn sẽ tìm thấy các giải pháp cho một số tình huống phổ biến trong đó VPR sai.
1. VLOOKUP không phân biệt chữ hoa chữ thường
Chức năng VPR không phân biệt chữ hoa và chữ thường và chữ hoa như nhau. Do đó, nếu có một số phần tử trong bảng chỉ khác nhau về trường hợp, thì hàm VLOOKUP sẽ trả về phần tử đầu tiên được tìm thấy, bất kể trường hợp nào.
Phán quyết: Sử dụng một hàm Excel khác có thể thực hiện tìm kiếm theo chiều dọc (LOOKUP, SUMPRODUCT, INDEX và MATCH) kết hợp với CHÍNH XÁCA phân biệt trường hợp. Để biết thêm chi tiết, bạn có thể học từ bài học - 4 cách tạo hàm VLOOKUP phân biệt chữ hoa chữ thường trong Excel.
2. Hàm VLOOKUP trả về giá trị đầu tiên được tìm thấy
Như bạn đã biết, VPR trả về giá trị từ cột đã cho tương ứng với kết quả phù hợp đầu tiên được tìm thấy. Tuy nhiên, bạn có thể yêu cầu nó trích xuất giá trị thứ 2, 3, 4 hoặc bất kỳ sự lặp lại nào khác của giá trị bạn muốn. Nếu bạn cần trích xuất tất cả các giá trị trùng lặp, bạn sẽ cần kết hợp các hàm INDEX (MỤC LỤC), ÍT NHẤT (NHỎ) và ĐƯỜNG DÂY (HÀNG NGANG).
3. Một cột đã được thêm vào hoặc bị xóa vào bảng
Thật không may, các công thức VPR ngừng hoạt động mỗi khi thêm hoặc bớt cột mới vào bảng tra cứu. Điều này xảy ra bởi vì cú pháp VPR yêu cầu bạn chỉ định toàn bộ phạm vi tìm kiếm và số cột cụ thể để trích xuất dữ liệu. Đương nhiên, cả phạm vi đã cho và số cột đều thay đổi khi bạn xóa cột hoặc chèn cột mới.
Phán quyết: Và một lần nữa các chức năng đang vội vàng để giúp đỡ INDEX (INDEX) và THÊM ĐƯỢC TIẾP XÚC (CUỘC THI ĐẤU). Trong công thức INDEX + MATCH Bạn xác định riêng các cột tìm kiếm và truy xuất, và kết quả là bạn có thể xóa hoặc chèn bao nhiêu cột tùy thích mà không cần lo lắng về việc phải cập nhật tất cả các công thức tìm kiếm có liên quan.
4. Tham chiếu ô bị cắt xén khi sao chép công thức
Tiêu đề này giải thích bản chất của vấn đề một cách thấu đáo, phải không?
Phán quyết: Luôn sử dụng tham chiếu ô tuyệt đối (với ký hiệu $) trên các bản ghi phạm vi, chẳng hạn $ A $ 2: $ C $ 100 or $ A: $ C. Trong thanh công thức, bạn có thể nhanh chóng chuyển đổi loại liên kết bằng cách nhấp vào F4.
VLOOKUP - làm việc với các hàm IFERROR và ISERROR
Nếu bạn không muốn khiến người dùng sợ hãi bằng các thông báo lỗi # N / A, #GIÁ TRỊ! or #TÊN?, bạn có thể hiển thị một ô trống hoặc thông báo của riêng bạn. Bạn có thể làm điều này bằng cách đặt VPR thành một chức năng SỐ PHIẾU (IFERROR) trong Excel 2013, 2010 và 2007 hoặc sử dụng nhiều hàm IF + ISERROR (IF + ISERROR) trong các phiên bản trước đó.
VLOOKUP: làm việc với hàm IFERROR
Cú pháp hàm SỐ PHIẾU (IFERROR) đơn giản và tự nó nói lên:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Nghĩa là, đối với đối số đầu tiên, bạn chèn giá trị để được kiểm tra lỗi và đối với đối số thứ hai, bạn chỉ định giá trị sẽ trả về nếu tìm thấy lỗi.
Ví dụ: công thức này trả về một ô trống nếu không tìm thấy giá trị bạn đang tìm kiếm:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Nếu bạn muốn hiển thị thông báo của riêng mình thay vì thông báo lỗi tiêu chuẩn của hàm VPR, hãy đặt nó trong dấu ngoặc kép, như sau:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: làm việc với hàm ISERROR
Kể từ khi chức năng SỐ PHIẾU xuất hiện trong Excel 2007, khi làm việc trong các phiên bản trước đó, bạn sẽ phải sử dụng kết hợp IF (IF) và EOSHIBKA (ISERROR) như thế này:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Ví dụ, công thức IF + ISERROR + VLOOKUP, tương tự như công thức IFERROR + VLOOKUPhiển thị ở trên:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Đó là tất cả cho ngày hôm nay. Tôi hy vọng hướng dẫn ngắn này sẽ giúp bạn đối phó với tất cả những sai lầm có thể xảy ra. VPR và làm cho các công thức của bạn hoạt động chính xác.