Làm cách nào để thực hiện vlookup mà không gặp lỗi trong Google Sheets? Hãy xem xét kỹ hơn các lỗi phổ biến nhất được thực hiện khi làm việc với chức năng VLOOKUP.
Google Sheets VLOOKUP là một trong những chức năng hữu ích nhất nhưng khó nhất. Nó giúp tìm dữ liệu phù hợp trên nhiều tờ. Tuy nhiên, có những trường hợp khi công thức của bạn có thể trả về lỗi như # Không có, #TÊN?, #GIÁ TRỊ!và #REF!.
Hãy cùng nhau thử và trả lời câu hỏi sau, “Tại sao VLOOKUP của tôi không hoạt động?”
Lỗi phổ biến nhất của VLOOKUP
Lý do đầu tiên là đơn giản là không có trận đấu nào trong phạm vi bạn chỉ định. Bạn bất lực ở đây và không thể ảnh hưởng đến kết quả.
Các trường hợp khác có nghĩa là bạn đã mắc lỗi ở đâu đó trong công thức. Hãy xem xét kỹ hơn những gì có thể đã sai.
Phân định sai
Dấu phân cách là những ký tự được sử dụng trong công thức làm dấu phân cách. Ví dụ: nếu bạn ở Vương quốc Anh hoặc Hoa Kỳ, rất có thể các công thức của bạn yêu cầu dấu phẩy là dấu phân cách:
=VLOOKUP(A2,$F$2:$G$10,2,FALSE)
Và đó là ngôn ngữ bảng tính của bạn điều khiển sự lựa chọn dấu phân cách. Địa phương khác nhau gọi cho phân cách khác nhau.
Để kiểm tra ngôn ngữ của bạn trong Google Sheets, hãy đi đến Tệp> Cài đặt bảng tính. Bạn sẽ thấy miền địa phương dưới Chung chuyển hướng:
Để hiểu những gì phân định VLOOKUP của bạn cần dựa trên ngôn ngữ của bạn, chỉ cần bắt đầu nhập chức năng. Google Sheets sẽ cho bạn thấy công thức của bạn sẽ trông chính xác như thế nào và phải sử dụng dấu phân cách nào:
Nếu các dấu phân cách đều đúng nhưng VLOOKUP Google Sheets của bạn vẫn phát sinh lỗi, hãy kiểm tra các sự cố có thể khác và các giải pháp của chúng bên dưới.
Dữ liệu không chính xác
Công thức không thể tìm thấy bất cứ điều gì và trả về # Không có lỗi?
Giải pháp đơn giản nhất là tốt nhất ở đây – rất có thể, bạn đã nhập dữ liệu không chính xác. Điều này có thể xảy ra đặc biệt nếu bạn nhập công thức trực tiếp vào thanh công thức chứ không phải ô.
Tên hàm không chính xác
Nếu bạn thấy #TÊN? lỗi, rất có thể bạn đã viết tên hàm không chính xác – bỏ lỡ hoặc viết sai một hoặc hai chữ cái.
Kiểm tra xem và Cú pháp VLOOKUP chỉ trong trường hợp, sửa lỗi chính tả, và mọi thứ sẽ hoạt động.
Tham chiếu ô VLOOKUP không chính xác
Công thức chỉ hoạt động tốt cho đến khi bạn thêm một vài hàng hoặc cột vào bảng và # Không có lỗi đã xuất hiện ngoài màu xanh?
Kiểm tra xem phạm vi tìm kiếm và ô có giá trị cần tìm có được nhập chính xác không.
Nếu bạn sử dụng tham chiếu ô tương đối (ví dụ A1) thay vì tham chiếu tuyệt đối (ví dụ $ A $ 1) và sau đó sửa đổi bảng (ví dụ: thêm một cột), dữ liệu sẽ thay đổi, các tham chiếu sẽ thay đổi và công thức sẽ tham chiếu đến các ô sai. :
Tôi đã thêm cột “Bài viết”. Cột “Giá” không được bao gồm trong phạm vi nữa, do đó không thể tìm thấy giá.
Nếu điều đó xảy ra, đơn giản Hoàn tác các sửa đổi bảng và sửa chữa các tài liệu tham khảo.
Bạn có thể sử dụng công cụ Công thức trong Dụng cụ điện add-on để chuyển đổi tất cả các tham chiếu tương đối thành tuyệt đối trong một vài lần nhấp:
Đối số “is_sort” không chính xác
Công thức VLOOKUP của bạn được đặt để trả về kết quả khớp gần đúng (TRUE), trong khi cột tra cứu không được sắp xếp.
Để khắc phục điều đó, hãy sắp xếp cột tra cứu A thành Z hoặc thay đổi TRUE thành FALSE.
Cột tra cứu không chính xác
Bạn có thể đã quên rằng chức năng VLOOKUP trong Google Sheets luôn tìm kiếm trong cột đầu tiên (ngoài cùng bên trái) của phạm vi tra cứu. Nếu các giá trị cho tìm kiếm_key đối số nằm trong cột thứ hai hoặc một số cột khác, VLOOKUP sẽ không hoạt động và sẽ trả về # Không có lỗi:
Ghi chú. Xin lưu ý rằng VLOOKUP không thể nhìn vào bên trái của nó.
Số cột không chính xác
Đôi khi, đối số thứ ba của Google Sheets VLOOKUP được chỉ định không chính xác.
Không thể ít hơn 1 và nhiều hơn tổng số cột trong phạm vi tìm kiếm. Nếu số không chính xác, VLOOKUP sẽ trả về #GIÁ TRỊ! lỗi.
Ghi chú. Số lượng cột phải là 1 hoặc nhiều hơn. Tất nhiên, không chắc là bạn sẽ nhập 0 hoặc -1, nhưng nếu bạn sử dụng một số chức năng khác để lấy số sê-ri của cột bắt buộc, bạn có thể vấp phải lỗi.
Nếu điều đó xảy ra, vui lòng đếm số lượng cột trong phạm vi đã nhập và đảm bảo rằng nó bao gồm số từ đối số thứ ba của hàm VLOOKUP.
Tham chiếu không chính xác đến bảng khác
Phạm vi tìm kiếm có thể nằm trong một bảng khác hoặc thậm chí là một bảng tính Google khác.
Bạn sẽ nhận thấy rằng một cái gì đó tắt nếu bạn nhìn thấy #REF! lỗi.
Điều đó có nghĩa là phạm vi bạn đưa vào công thức không chính xác và hàm không thể định vị được nó.
Định dạng số không chính xác
Nói cách khác, định dạng của ô có giá trị bắt buộc (D3 trong trường hợp của chúng tôi) và định dạng của cột ngoài cùng bên trái trong phạm vi tìm kiếm (A3: A21 trong trường hợp của chúng tôi) khác nhau (số và văn bản).
Điều này có thể xảy ra khi bạn sử dụng mã số thay vì các mục nhập văn bản (số thứ tự, ID hàng hóa, ngày tháng và các mã định danh khác).
Như bạn ví dụ ở trên cho thấy, các giá trị trông giống nhau nhưng mục nhập trong D6 được định dạng dưới dạng số trong khi A10 chứa văn bản. Một văn bản không thể được coi là một số, làm cho hàm VLOOKUP trả về # Không có lỗi trong Google Sheets.
Làm thế nào để nhanh chóng giải quyết vấn đề này?
- Bạn có thể thêm BẢN VĂN Hàm sẽ chuyển đổi bất kỳ giá trị nào thành văn bản:
Tôi đã sử dụng công thức sau đây:
=VLOOKUP(TEXT(D6,"https://www.ablebits.com/office-addins-blog/2018/05/17/fix-vlookup-errors-google-sheets/#"),$A$3:$B$21,2,FALSE)Nơi tôi đã thay thế tham chiếu ô thông thường thành D6 bằng một chức năng khác:
TEXT(D6,"https://www.ablebits.com/office-addins-blog/2018/05/17/fix-vlookup-errors-google-sheets/#")Các đối số của BẢN VĂN chức năng rất đơn giản:
- Đối số đầu tiên, con số, cho biết ô cần định dạng.
- Đối số thứ hai, định dạng, là một mẫu mà tôi sẽ chuyển đổi giá trị của ô.
- Nếu chỉ có một lỗi như thế này, bạn có thể khắc phục nhanh hơn bằng cách thay đổi định dạng số của ô. Chọn ô và đi đến Định dạng> Số> Văn bản đơn trong menu Google Sheets. Nội dung của nó sẽ được thay đổi thành văn bản:
Như bạn có thể thấy, lỗi đã xảy ra kể từ bây giờ chúng tôi đang tìm kiếm giá trị văn bản trong số các giá trị văn bản khác.
Không gian thừa và ký tự không in
Hàm không thể tìm thấy giá trị bắt buộc do ô chứa khoảng trắng thừa hoặc ký tự không in (ngắt dòng và các ký tự khác).
Trong ví dụ của tôi, VLOOKUP trả về lỗi vì vô tình có hai khoảng trắng được nhập vào D4. Và vì hàm so sánh các ký hiệu, nên việc tìm kiếm rõ ràng đã thất bại:
Lỗi này có thể xảy ra khá thường xuyên và gần như không thể bắt gặp bằng mắt. Ví dụ: nếu giá trị bao gồm hai từ, một khoảng trắng thừa có thể tìm đường đi giữa các từ.
Bạn cũng có thể nhập nhầm do các ký tự không in như vậy bị ẩn trên màn hình (bảng, ngắt dòng, v.v.).
Dưới đây là các giải pháp:
- Các chức năng văn bản TRIM và DỌN DẸP sẽ giúp và loại bỏ các ký tự vô hình không mong muốn:
=VLOOKUP(TRIM(CLEAN(D4)),$A$3:$B$21,2,FALSE)
Các TRIM chức năng loại bỏ không gian thừa trong khi DỌN DẸP chức năng xóa tất cả các ký tự không in.
Tiền boa. Bạn luôn có thể sử dụng kết hợp các chức năng “chỉ trong trường hợp” này để tránh mọi lỗi.
- Hoặc sử dụng công cụ Xóa khỏi Dụng cụ điện để xóa tất cả các ký tự không mong muốn cùng một lúc:
Thay thế lỗi VLOOKUP bằng văn bản của riêng bạn
Tôi nghĩ bạn sẽ đồng ý về thực tế là các lỗi không làm cho bảng của bạn trông đẹp hơn. Có cách nào để thoát khỏi chúng?
Vâng, vâng, nhưng sẽ không gây hại gì cho lần kiểm tra đầu tiên nếu công thức VLOOKUP của bạn trong Google Sheets được xây dựng chính xác:
- Bắt đầu với phạm vi tra cứu. Bạn đã chỉ định nó một cách chính xác? Giá trị của bạn có xuất hiện trong các cột ngoài cùng bên trái của phạm vi này không?
- Hãy đảm bảo rằng “bị cấm“Đối số là chính xác: TRUE hoặc FALSE, tùy thuộc vào trận đấu bạn cần và cách sắp xếp phạm vi của bạn.
- Kiểm tra tất cả các định dạng số có thể không khớp và không gian thêm hoặc ký tự không in.
Nếu mọi thứ đều ổn nhưng công thức không trả về giá trị, hãy thêm IFERROR chức năng ở đó:
=IFERROR(VLOOKUP(D9,$A$3:$B$21,2,FALSE),"No items")
Cú pháp của IFERROR rất dễ hiểu:
- giá trị – một cái gì đó chúng tôi kiểm tra lỗi. Nếu mọi thứ đều ổn, kết quả tính toán của chúng tôi sẽ trả về.
- [value_if_error] – một cái gì đó hàm trả về nếu đối số đầu tiên đã gặp lỗi.
Trong trường hợp của tôi, F9 cho thấy “Không có sản phẩm“Bởi vì VLOOKUP không thể tìm thấy bất kỳ trận đấu nào.
Đây là cách bạn kiểm tra chức năng VLOOKUP của mình để tìm lỗi trong Google Sheets. Nếu bạn có bất kỳ câu hỏi, xin vui lòng hỏi họ trong phần ý kiến dưới đây.
Bạn cũng có thể quan tâm:
Source link
The post Bẫy và sửa lỗi trong công thức VLOOKUP của bạn trong Google Sheets appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/bay-va-sua-loi-trong-cong-thuc-vlookup-cua-ban-trong-google-sheets.html
Không có nhận xét nào:
Đăng nhận xét