Hàm VLOOKUP trong Excel rất mạnh, nhưng nó sẽ không hoạt động khi bạn gặp các tình huống cụ thể. Hôm nay, hãy xem cách khắc phục sự cố VLOOKUP.
VLOOKUP là hàm yêu thích của tôi trong Excel. Nếu bạn có thể thực hiện hàm VLOOKUP, bạn có thể giải quyết nhiều vấn đề trong Excel. Nhưng có những thứ có thể tăng gấp ba lần một hàm VLOOKUP. Chủ đề này nói về một vài trong số họ.
Nhưng trước tiên, những điều cơ bản về VLOOKUP bằng tiếng Anh đơn giản.
Dữ liệu trong A: C đến từ bộ phận CNTT. Bạn đã yêu cầu bán hàng theo mặt hàng và ngày. Họ đã cung cấp cho bạn số mục. Bạn cần Mô tả mặt hàng. Thay vì đợi bộ phận CNTT chạy lại dữ liệu, bạn tìm bảng hiển thị trong cột F: G.
Bạn muốn VLOOKUP tìm mục trong A2 trong khi nó tìm kiếm qua cột đầu tiên của bảng trong $ F $ 3: $ G $ 30. Khi hàm VLOOKUP tìm thấy kết quả phù hợp trong F7, bạn muốn hàm VLOOKUP trả về mô tả được tìm thấy trong cột thứ hai của bảng. Mọi hàm VLOOKUP đang tìm kiếm kết quả khớp chính xác phải kết thúc bằng False (hoặc không, tương đương với False). Công thức dưới đây được thiết lập đúng cách.
Lưu ý rằng bạn sử dụng F4 để thêm bốn ký hiệu đô la vào địa chỉ cho bảng tra cứu. Khi bạn sao chép công thức xuống cột D, bạn cần địa chỉ cho bảng tra cứu không đổi. Có hai lựa chọn thay thế phổ biến: Bạn có thể chỉ định toàn bộ cột F: G làm bảng tra cứu. Hoặc, bạn có thể đặt tên F3: G30 với tên như ItemTable. Nếu bạn sử dụng =VLOOKUP(A2,ItemTable,2,False), phạm vi được đặt tên hoạt động giống như một tham chiếu tuyệt đối.
Bất cứ khi nào bạn thực hiện một loạt các VLOOKUP, bạn cần phải sắp xếp cột VLOOKUP. Sắp xếp ZA và bất kỳ lỗi # N / A nào sẽ xuất hiện trên cùng. Trong trường hợp này, có một. Mục BG33-9 bị thiếu trong bảng tra cứu. Có thể đó là một lỗi đánh máy. Có thể đó là một món đồ mới tinh. Nếu là hàng mới, hãy chèn một hàng mới vào bất kỳ vị trí nào giữa bảng tra cứu của bạn và thêm mục mới.
Việc có một vài lỗi # N / A là điều khá bình thường. Nhưng trong hình dưới đây, chính xác cùng một công thức không trả về giá trị nào ngoài # N / A. Khi điều này xảy ra, tôi xem liệu tôi có thể giải được hàm VLOOKUP đầu tiên hay không. Bạn đang tìm BG33-8 được tìm thấy trong A2. Bắt đầu lướt qua cột đầu tiên của bảng tra cứu. Như bạn có thể thấy, giá trị khớp rõ ràng là ở F10. Tại sao bạn có thể nhìn thấy điều này, nhưng Excel không thể nhìn thấy nó?
Đi đến từng ô và nhấn phím F2. Đây là F10. Lưu ý rằng con trỏ chèn xuất hiện ngay sau dấu 8.
Đây là ô A2 trong chế độ Chỉnh sửa. Con trỏ chèn cách số 8. Đây là một dấu hiệu cho thấy tại một thời điểm nào đó, dữ liệu này được lưu trữ trong tập dữ liệu COBOL cũ. Quay lại COBOL, nếu trường Item được xác định là 10 ký tự và bạn chỉ nhập 6 ký tự, COBOL sẽ chèn thêm 4 khoảng trắng.
Giải pháp? Thay vì tra cứu A2, hãy tra cứu TRIM(A2).
Hàm TRIM () loại bỏ các khoảng trắng ở đầu và cuối. Nếu bạn có nhiều khoảng trắng giữa các từ, TRIM sẽ chuyển chúng thành một khoảng trắng. Trong hình bên dưới có khoảng trắng trước và sau cả hai tên trong A1. =TRIM(A1)loại bỏ tất cả trừ một khoảng trống trong A3.
Nhân tiện, điều gì sẽ xảy ra nếu vấn đề là dấu cách ở cột F thay vì cột A? Thêm một cột các hàm TRIM () vào E, trỏ đến cột F. Sao chép chúng và dán làm giá trị trong F để làm cho việc tra cứu bắt đầu hoạt động trở lại.
Lý do rất phổ biến khác khiến hàm VLOOKUP không hoạt động được hiển thị ở đây. Cột F có số thực. Cột A có văn bản giống như số.
Chọn tất cả cột A. Nhấn alt = "" + D, E, F. Thao tác này chuyển Văn bản thành Cột mặc định và sẽ chuyển đổi tất cả các số văn bản thành số thực. Việc tra cứu bắt đầu hoạt động trở lại.
Xem video
- VLOOKUP giải quyết nhiều vấn đề
- Sự cố VLOOKUP thường gặp:
- Nếu hàm VLOOKUP bắt đầu hoạt động, nhưng # N / A trở nên nổi bật hơn: quên $ trong bảng tra cứu
- Một vài # N / A: thiếu các mục trong bảng
- Không có hàm VLOOKUP nào hoạt động: kiểm tra dấu cách ở cuối
- Xóa dấu cách ở cuối bằng TRIM
- Số và số được lưu trữ dưới dạng văn bản
- Chọn cả hai cột và sử dụng alt = "" + DEF
- Tập bao gồm một câu chuyện cười mà cả kế toán và dân IT đều thấy buồn cười, nhưng vì những lý do khác nhau
Bản ghi video
Học Excel từ podcast, tập 2027 - Khắc phục sự cố VLOOKUP!
Được rồi, podcasting toàn bộ cuốn sách này, nhấp vào "i" ở góc trên bên phải để truy cập danh sách phát!
VLOOKUP Là hàm yêu thích của tôi trong tất cả Excel, và tôi luôn kể câu chuyện cười này trong một buổi hội thảo của mình, và nó sẽ gây cười cho dù bạn là dân IT hay không phải dân IT. Tôi luôn nói “Xem này, chúng tôi có tập dữ liệu này ở bên trái, và tôi có thể nhìn vào dữ liệu đó và nói rằng dữ liệu đó đến từ bộ phận CNTT vì đó chính xác là những gì tôi yêu cầu, nhưng không thực sự là những gì tôi cần. Tôi hỏi ngày và số lượng mặt hàng, và họ cho tôi ngày và số lượng mặt hàng, nhưng họ không buồn cho tôi mô tả, phải không? ” Và các nhân viên kế toán luôn cười vào điều này, bởi vì điều này xảy ra với họ mọi lúc, và các chàng trai IT thì kiểu "Chà, tôi đã đưa cho bạn những gì bạn yêu cầu, đó không phải lỗi của tôi!" Vì vậy, cả hai đều nghĩ rằng thật buồn cười vì những lý do khác nhau, bạn biết đấy, và anh chàng IT đang bận, anh ta không thể quay lại để viết lại truy vấn,vì vậy chúng ta phải làm gì đó để tự giải cứu điều này.
Và vì vậy bảng nhỏ này tôi đã sao chép từ một nơi khác trong máy tính của mình, bằng tiếng Anh đơn giản, hàm VLOOKUP thực hiện là "Này, chúng tôi có một mục số W25-6." Chúng tôi có một bảng ở đây, tôi muốn lướt qua cột đầu tiên của bảng cho đến khi tôi tìm thấy số mục đó, và sau đó trả lại một số thứ từ hàng đó. Được rồi, trong trường hợp này, thứ tôi muốn là cột thứ 2 từ hàng đó. Và sau đó ở cuối mỗi hàm VLOOKUP, chúng ta phải đặt FALSE hoặc 0. Bây giờ ngay tại đây, sau khi tôi chọn phạm vi, tôi sẽ nhấn phím F4, sau đó tôi muốn cột thứ 2 và sau đó là FALSE cho chính xác trận đấu. Đừng bao giờ chọn kết hợp gần đúng, không bao giờ, không bao giờ! Nó không phải là một trận đấu gần đúng, nó là một điều rất đặc biệt, nó không phải lúc nào cũng hoạt động. Nếu bạn muốn trình bày lại các con số của mình cho Ủy ban Chứng khoán và Giao dịch, hãy sử dụng mọi cách,ĐÚNG hoặc chỉ bỏ, FALSE tắt. Nhưng mọi hàm VLOOKUP mà bạn từng tạo phải kết thúc bằng FALSE hoặc, 0, 0 ngắn hơn FALSE, bạn phải đặt FALSE ở đó, nhưng số 0 cũng giống như FALSE.
Được rồi, hãy khắc phục sự cố, điều đầu tiên, khi bạn thực hiện toàn bộ các VLOOKUP, việc có một vài ký hiệu # N / As là rất bình thường, phải không? Và tôi luôn tìm thấy điểm # N / A bằng cách sử dụng Data, ZA, đưa # N / As lên đầu, ngay tại đó, BG33-9, đó là lỗi đánh máy hoặc đó là một mặt hàng hoàn toàn mới, được rồi, và chúng tôi đã để tìm ra nó. Vì vậy, ở đây bên phải tôi có dữ liệu mới, tôi sẽ cắt nó, và sau đó Alt + IED, chèn các ô đó vào giữa, nó không cần phải theo thứ tự bảng chữ cái, bảng này không cần phải được sắp xếp. Khi bạn đang sử dụng phiên bản FALSE, bảng không phải là - bạn chỉ có thể đặt nó ở bất cứ đâu bạn muốn, tôi không đặt ở cuối vì tôi không phải viết lại công thức, tôi chỉ muốn công thức công việc. Được rồi, một vài điểm # N / A, cực kỳ, cực kỳ bình thường, nhưng hãy kiểm tra điều này.
Khi bạn bắt đầu với các câu trả lời hiệu quả, nhưng sau đó số # N / A bắt đầu xuất hiện hơi thường xuyên, và sau đó cuối cùng chúng xuất hiện hết cỡ, đó là một dấu hiệu chắc chắn rằng bạn đã không khóa tham chiếu bảng. Được rồi, thấy vậy, đây là bảng đang di chuyển khi tôi sao chép công thức xuống, đúng vậy, và vì vậy tôi may mắn đánh trúng một vài công thức ở cuối danh sách. Nhưng cuối cùng tôi nhận ra rằng nó đang nhìn ở đây trong các ô trống hoàn toàn, và tất nhiên là không tìm thấy gì. Được rồi, đó là điều đầu tiên, bạn sẽ có một vài cái hoạt động, một vài cái # N / A, một vài cái khác hoạt động và sau đó tất cả # N / A là hết cách - chắc chắn rằng bạn đã không đặt $ vào.
Chỉ cần quay lại, F2 cho chế độ Chỉnh sửa, chọn dấu hai chấm, nhấn F4, đặt tất cả $ vào, nhấp đúp để bắn nó xuống và mọi thứ bắt đầu hoạt động trở lại, được rồi. Tiếp theo, cùng một công thức, công thức hoàn hảo, BG33-8 thấy không, chúng ta không hiểu điều đó đúng. Được rồi, vậy tôi đi tìm BG33-8, này, nó đây, nó ở ngay đây, nó màu đỏ, lẽ ra tôi phải nhìn thấy nó! Vì vậy, tôi đến cái này ở bên tay phải, tôi nhấn F2, và tôi quan sát điểm chèn nhấp nháy, và thấy, nó nằm ngay sau số 8, như vậy, và sau đó tôi đến đây và nhấn F2, có một số dấu cách ở đó. Điều này rất, rất phổ biến trong những ngày của COBOL, họ sẽ nói "Bạn biết đấy, xem này, chúng tôi sẽ cung cấp cho bạn 10 dấu cách cho số mục và nếu bạn không nhập tất cả 10 dấu cách, chúng sẽ lấp đầy khoảng trống . ” Và điều này rất phổ biến,và giải pháp tuyệt vời ở đây là loại bỏ những khoảng trống ở đầu và cuối với hàm TRIM. Thay vì A2, hãy sử dụng TRIM (A2), hãy nhấp đúp để bắn nó xuống, được rồi, BG33-9 vẫn quay trở lại bảng khác.
Được rồi, để bạn hiểu cách TRIM hoạt động, vì vậy tôi đã nhập một loạt dấu cách, John, một loạt dấu cách, Durran và một loạt dấu cách, sau đó tôi nối dấu * trước và sau để bạn có thể thấy nó trông như thế nào . Khi tôi yêu cầu TRIM (P4), chúng tôi loại bỏ tất cả các khoảng trống phía trước, tất cả các khoảng trống phía sau, và sau đó nhiều không gian bên trong được giảm xuống còn một khoảng trống. Được rồi, bạn có thể thấy, hãy hình dung ở đó, rằng chúng đang loại bỏ các khoảng trắng ở đầu và cuối, bất kỳ khoảng trắng nhân đôi nào ở giữa đều trở thành một không gian duy nhất như vậy. Vì vậy, TRIM, một công cụ tuyệt vời, tuyệt vời trong kho vũ khí của bạn, được rồi, đây là một công cụ thực sự phổ biến khác.
Nếu đó không phải là các dấu cách ở cuối, thì điều phổ biến nhất mà tôi đã thấy, là nơi ở đây chúng ta có các con số thực và ở đây chúng ta có các số được lưu trữ dưới dạng văn bản. Và hàm VLOOKUP sẽ không xem đó là một kết quả trùng khớp, mặc dù 4399, đây là một số, đây là văn bản, không hoạt động. Cách nhanh nhất để chuyển đổi một cột văn bản thành số, chọn cột, 3 chữ cái theo thứ tự, alt = "" DEF, và đột nhiên, các VLOOKUP của chúng tôi bắt đầu hoạt động trở lại, tuyệt vời, mẹo hay từ khoảng 1500 podcast trước đây. Được rồi, đó là những sự cố VLOOKUP phổ biến nhất, hoặc bạn quên $ hoặc bạn có dấu cách ở cuối hoặc bạn có số và số được lưu trữ dưới dạng văn bản. Tất cả những lời khuyên này đều có trong cuốn sách “MrExcel XL” này, nhấp vào “i” ở góc trên bên phải, bạn có thể mua cuốn sách.
Được rồi, tóm tắt nhanh: VLOOKUP giải quyết nhiều vấn đề, nếu một hàm VLOOKUP bắt đầu hoạt động nhưng lỗi # N / A! trở nên nổi bật hơn, bạn đã quên đặt $ trong bảng tra cứu. Nếu có một vài # N / A, đó chỉ là các mục bị thiếu trong bảng. Nếu không có hàm VLOOKUP nào hoạt động và đó là văn bản, hãy kiểm tra các dấu cách ở cuối, bạn có thể sử dụng hàm TRIM. Nếu bạn có số và số được lưu trữ dưới dạng văn bản, hãy chọn một trong hai cột, cột có văn bản, sau đó thực hiện alt = "" DEF phải chuyển tất cả các số đó về số.
Được rồi, tôi muốn cảm ơn bạn đã ghé qua, chúng tôi sẽ gặp lại bạn vào lần sau với một netcast khác từ!
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2027.xlsx
