Bạn đã bao giờ có một bảng tính lớn với dữ liệu trong Excel và cần một cách dễ dàng để lọc và trích xuất thông tin cụ thể từ nó? Nếu bạn học cách sử dụng hàm VLOOKUP trong Excel, bạn có thể thực hiện việc tra cứu này chỉ với một hàm Excel mạnh mẽ duy nhất.
Hàm VLOOKUP trong Excel khiến nhiều người sợ hãi vì nó có rất nhiều tham số và có nhiều cách sử dụng. Trong bài viết này, bạn sẽ tìm hiểu tất cả các cách bạn có thể sử dụng hàm VLOOKUP trong Excel và tại sao hàm này lại mạnh mẽ như vậy.
Tham số VLOOKUP trong Excel
Khi bạn bắt đầu nhập =VLOOKUP ( vào bất kỳ ô nào trong Excel, bạn sẽ thấy cửa sổ bật lên hiển thị tất cả các tham số hàm có sẵn.
Hãy xem xét từng thông số này và ý nghĩa của chúng.
- lookup_value :Giá trị bạn đang tìm kiếm từ bảng tính
- table_array :Phạm vi ô trong trang tính mà bạn muốn tìm kiếm
- col_index_num :Cột mà bạn muốn lấy kết quả của mình
- [range_lookup] :Chế độ đối sánh (TRUE =gần đúng, FALSE =chính xác)
Bốn tham số này cho phép bạn thực hiện nhiều tìm kiếm hữu ích, khác nhau đối với dữ liệu bên trong các tập dữ liệu rất lớn.
Ví dụ về hàm VLOOKUP đơn giản trong Excel
VLOOKUP không phải là một trong những hàm Excel cơ bản mà bạn có thể đã học, vì vậy hãy xem một ví dụ đơn giản để bắt đầu.
Đối với ví dụ sau, chúng tôi sẽ sử dụng một bảng tính điểm SAT lớn cho các trường học ở Hoa Kỳ. Bảng tính này bao gồm hơn 450 trường học cùng với điểm SAT cá nhân cho các môn đọc, toán và viết. Hãy tải về để cùng theo dõi. Có một kết nối bên ngoài kéo dữ liệu, vì vậy, bạn sẽ nhận được cảnh báo khi mở tệp, nhưng điều này vẫn an toàn.
Sẽ rất mất thời gian để tìm kiếm trong một tập dữ liệu lớn như vậy để tìm trường mà bạn quan tâm.
Thay vào đó, bạn có thể tạo một biểu mẫu đơn giản trong các ô trống bên cạnh bảng. Để thực hiện tìm kiếm này, chỉ cần tạo một trường cho Trường học và ba trường bổ sung cho điểm đọc, toán và viết.
Tiếp theo, bạn sẽ cần sử dụng hàm Vlookup trong Excel để làm cho ba trường này hoạt động. Trong Đọc , tạo hàm Vlookup như sau:
- Nhập =VLOOKUP (
- Chọn trường Trường học, trong ví dụ này là I2 . Nhập dấu phẩy.
- Chọn toàn bộ phạm vi ô chứa dữ liệu bạn muốn tra cứu. Nhập dấu phẩy.
Khi bạn chọn phạm vi, bạn có thể bắt đầu từ cột bạn đang sử dụng để tra cứu (trong trường hợp này là cột tên trường), sau đó chọn tất cả các cột và hàng khác có chứa dữ liệu.
Lưu ý :Hàm Vlookup trong Excel chỉ có thể tìm kiếm qua các ô bên phải cột tìm kiếm. Trong ví dụ này, cột tên trường cần phải ở bên trái dữ liệu bạn đang tra cứu.
- Tiếp theo, để truy xuất điểm Đọc, bạn sẽ cần chọn cột thứ 3 từ cột đã chọn ngoài cùng bên trái. Vì vậy, hãy nhập 3 rồi nhập một dấu phẩy khác.
- Cuối cùng, nhập FALSE để có kết quả khớp chính xác và đóng hàm bằng ) .
Hàm VLOOKUP cuối cùng của bạn sẽ trông giống như sau:
=VLOOKUP(I2,B2:G461,3,FALSE)
Khi bạn lần đầu tiên nhấn Enter và kết thúc chức năng, bạn sẽ nhận thấy trường Đọc sẽ chứa # N / A .
Điều này là do trường Trường trống và không có gì để hàm VLOOKUP tìm. Tuy nhiên, nếu bạn nhập tên của bất kỳ trường trung học nào bạn muốn tra cứu, bạn sẽ thấy kết quả chính xác từ hàng đó cho điểm Đọc.
Cách đối phó với hàm VLOOKUP có phân biệt chữ hoa chữ thường
Bạn có thể nhận thấy rằng nếu bạn không nhập tên của trường giống như cách nó được liệt kê trong tập dữ liệu, bạn sẽ không thấy bất kỳ kết quả nào.
Điều này là do hàm VLOOKUP phân biệt chữ hoa chữ thường. Điều này có thể gây khó chịu, đặc biệt là đối với một tập dữ liệu rất lớn trong đó cột bạn đang tìm kiếm không nhất quán với cách viết hoa mọi thứ.
Để giải quyết vấn đề này, bạn có thể buộc những gì bạn đang tìm kiếm chuyển sang chữ thường trước khi tra cứu kết quả. Để làm điều này, hãy tạo một cột mới bên cạnh cột bạn đang tìm kiếm. Nhập hàm:
=TRIM(LOWER(B2))
Thao tác này sẽ viết thường tên trường và xóa mọi ký tự không liên quan (khoảng trắng) có thể ở bên trái hoặc bên phải của tên.
Giữ phím Shift và đặt con trỏ chuột qua góc dưới bên phải của ô đầu tiên cho đến khi nó chuyển thành hai đường ngang. Nhấp đúp chuột để tự động điền toàn bộ cột.
Cuối cùng, vì hàm VLOOKUP sẽ cố gắng sử dụng công thức chứ không phải văn bản trong các ô này, bạn chỉ cần chuyển đổi tất cả chúng thành giá trị. Để thực hiện việc này, hãy sao chép toàn bộ cột, nhấp chuột phải vào ô đầu tiên và chỉ dán các giá trị.
Bây giờ, tất cả dữ liệu của bạn đã được dọn dẹp trong cột mới này, hãy sửa đổi một chút hàm VLOOKUP của bạn trong Excel để sử dụng cột mới này thay vì cột trước đó bằng cách bắt đầu phạm vi tra cứu tại C2 thay vì B2.
=VLOOKUP(I2,C2:G461,3,FALSE)
Bây giờ, bạn sẽ nhận thấy rằng nếu bạn luôn nhập tìm kiếm của mình bằng chữ thường, bạn sẽ luôn nhận được kết quả tìm kiếm tốt.
Đây là một mẹo Excel hữu ích để khắc phục thực tế là hàm VLOOKUP phân biệt chữ hoa chữ thường.
Kết hợp gần đúng VLOOKUP
Mặc dù ví dụ về đối sánh chính xác LOOKUP được mô tả trong phần đầu tiên của bài viết này khá đơn giản, nhưng đối sánh gần đúng phức tạp hơn một chút.
Đối sánh gần đúng được sử dụng tốt nhất để tìm kiếm thông qua các phạm vi số. Để làm điều này một cách chính xác, phạm vi tìm kiếm cần được sắp xếp đúng. Ví dụ tốt nhất về điều này là hàm VLOOKUP để tìm kiếm loại chữ cái tương ứng với loại số.
Nếu một giáo viên có một danh sách dài các điểm bài tập về nhà của học sinh trong cả năm với cột trung bình cuối cùng, thì sẽ rất tuyệt nếu điểm chữ cái tương ứng với điểm cuối cùng đó tự động xuất hiện.
Điều này có thể thực hiện được với hàm VLOOKUP. Tất cả những gì bắt buộc là một bảng tra cứu ở bên phải có chứa loại chữ cái thích hợp cho từng phạm vi điểm số.
Giờ đây, bằng cách sử dụng hàm Vlookup và một kết hợp gần đúng, bạn có thể tìm thấy loại chữ cái thích hợp tương ứng với dải số chính xác.
Trong hàm Vlookup này:
- lookup_value :F2, điểm trung bình cuối cùng
- table_array :I2:J8, Phạm vi tra cứu loại chữ cái
- index_column :2, cột thứ hai trong bảng tra cứu
- [range_lookup] :TRUE, đối sánh gần đúng
Khi bạn hoàn thành hàm VLOOKUP trong G2 và nhấn Enter, bạn có thể điền vào các ô còn lại bằng cách sử dụng cùng một cách tiếp cận được mô tả trong phần trước. Bạn sẽ thấy tất cả các loại chữ cái được điền đúng cách.
Lưu ý rằng hàm VLOOKUP trong Excel tìm kiếm từ cuối dải điểm có điểm chữ cái được chỉ định đến đầu dải của điểm chữ cái tiếp theo.
Vì vậy, “C” cần phải là chữ cái được chỉ định cho phạm vi thấp hơn (75) và B được chỉ định cho phần dưới cùng (tối thiểu) của phạm vi chữ cái của chính nó. VLOOKUP sẽ “tìm” kết quả cho 60 (D) là giá trị gần đúng nhất cho bất kỳ giá trị nào trong khoảng từ 60 đến 75.
Hàm VLOOKUP trong Excel là một hàm rất mạnh đã có từ lâu. Nó cũng hữu ích để tìm các giá trị phù hợp ở bất kỳ đâu trong sổ làm việc Excel.
Tuy nhiên, hãy nhớ rằng người dùng Microsoft có đăng ký Office 365 hàng tháng hiện có quyền truy cập vào chức năng XLOOKUP mới hơn. Chức năng này có nhiều tham số hơn và tính linh hoạt bổ sung. Người dùng có đăng ký nửa năm sẽ cần đợi bản cập nhật ra mắt vào tháng 7 năm 2020.