VLOOKUP - Dò tìm dữ liệu theo hàng
Nội dung bài viết [Hiển thị]
1. Để làm gì?
- Dò tìm dữ liệu theo hàng.
- Hàm có thể dò tìm dữ liệu trong cùng một sheet, giữa các sheet trong cùng một file hoặc giữa các file khác nhau.
- Là hàm đầu tiên bạn cần phải tìm hiểu khi muốn dùng phương thức Dò tìm – Truy xuất dữ liệu để hoàn thành công việc.
2. Dùng như thế nào?
2.1. Ví dụ minh họa
Trong quá trình thi công Đất đắp K95 ta sẽ phải làm thao tác phân lớp Đất đắp K95, chứa đầy đủ thông số Cao độ, Kích thước hình học của từng lớp. Để làm hồ sơ nghiệm thu của lớp bất kỳ thì ta sẽ nghĩ đến việc lấy thông số ở hồ sơ phân lớp đưa vào mẫu biên bản nghiệm thu. Bằng việc sử dụng hàm Vlookup thì có thể dễ dàng gọi và truy xuất dữ liệu này.
Đề bài: Bảng Phân lớp đắp K95 tại cọc Km0+100.
Yêu cầu: Điền kết quả Cao độ TK và Cao độ TT của lớp bất kỳ trong trắc ngang Km0+100 vào ô I7 và J7.
Hướng dẫn:
Phân tích yêu cầu
- Điền Cao độ TK và Cao độ TT vào ô I7 và J7.
- Vùng dữ liệu dò tìm B7:F11.
- Điều kiện dò tìm: Tên Lớp
Giải quyết yêu cầu
- Thông thường chúng ta sẽ tìm thủ công và điền thủ công kết quả vào từng địa chỉ ô.
- Ở đây, dựa vào Vùng dữ liệu dò tìm ta thấy có thể tìm thông số Cao độ theo hàng ngang dựa vào Điều kiện dò tìm là tên Lớp. Đó cũng là nguyên lý hoạt động của hàm Vlookup mà tôi đang muốn giới thiệu với các bạn.
- Để sử dụng hàm này bạn sẽ phải đưa điều kiện dò tìm vào một địa chỉ ô. Ở đây tôi đã đưa điều kiện dò tìm vào vị trí ô H7.
- Tại ô I7 gõ công thức =VLOOKUP(H7;B7:F11;3;0)
- Ngoài ra bạn muốn điền kết quả Cao độ của các Lớp khác thì bạn chỉ cần thay đổi điều kiện dò tìm và nhập chúng vào ô H7.
2.2. Cấu trúc
Cấu trúc tiếng Anh
Cấu trúc tiếng Việt
|
1 |
lookup_value |
Điều kiện tìm kiếm |
|
|
|
|
|
2 |
table_array |
Vùng dò tìm |
|
|
|
|
|
3 |
col_index_num |
Cột chứa giá trị cần tìm |
|
|
|
Cột chứa điều kiện dò tìm là cột B – tức Cột số 1. Cột chứa kết quả dò tìm (Cao độ TK) là cột D – Cột số 3. Nên Cột chứa giá trị dò tìm = 3. |
|
4 |
range_lookup |
0 hoặc 1 |
|
|
|
Giá trị 0: Dò tìm kết quả chính xác tuyệt đối. Giá trị 1: Dò tìm kết quả chính xác tương đối.
|
2.3. Khái niệm
- Là hàm dò tìm dựa vào điều kiện cho trước.
- Nguyên lý hoạt động: Dò tìm theo hàng ngang dựa vào Điều kiện cho trước nằm ở cột đầu tiên trong vùng tìm kếm, trả về kết quả có vị trí xác định bằng số thứ tự của cột chứa kết quả.
Lưu ý: Hàm luôn luôn dò tìm từ trái qua phải, vì vậy cột đầu tiên bên trái của vùng tìm kiếm phải là cột chứa điều kiện tìm kiếm.
3. Cần lưu ý gì không?
Điều kiện dò tìm
- Điều kiện dò tìm sẽ có các cách khác nhau để đưa vào cấu trúc hàm. Bạn có thể đưa vào dạng địa chỉ ô hoặc điền trực tiếp vào cấu trúc.
Điều kiện dò tìm dạng điền trực tiếp: Khi sử dụng dạng này thì bạn sẽ bị bó buộc về độ linh động trong việc thay đổi điều kiện dò tìm. Đồng thời về cách điền trực tiếp điều kiện vào cấu trúc hàm khá phức tạp (Định dạng số, ngày tháng, văn bản…).
Điều kiện dò tìm dạng địa chỉ ô: Đây là dạng hay dùng nhất, nó linh động trong việc thay đổi điều kiện dò tìm và đơn giản hóa trong việc hoàn thiện cấu trúc hàm.
- Giá trị “đặc biệt”: Để phát huy hết khả năng của hàm Vlookup thì đây là thứ bạn phải quan tâm nhiều nhất. Tự tạo những giá trị “đặc biệt”, là duy nhất và có tính logic để tìm kiếm. Hình ảnh bên dưới là một trong số cách để tự tạo những giá trị đặc biệt đó, bạn sẽ được hiểu nhiều hơn về cách tạo những giá trị này ở những ví dụ sau:
- Trong công thức Vlookup ký tự viết hoa hay viết thường đều được xử lý như nhau.
Lỗi thường gặp:
- Lỗi #N/A: Khôm tìm thấy giá trị.
Không tìm thấy điều kiện tìm kiếm trong vùng tìm kiếm
Định dạng số và chữ không đồng nhất
Cột chứa điều kiện tìm kiếm không phải là cột đầu tiên của vùng tìm kiếm
Vùng tìm kiếm chính xác phải là B7:F11, cột chứa điều kiện tìm kiếm nằm ở cột B7, không phải A7 như hình dưới:
Điều kiện tìm kiếm chứa dấu cách (khoảng trắng)
Do bạn copy hoặc vì một lý do nào đó điều kiện tìm kiếm chứa khoảng trắng không móng muốn.
- Lỗi #REF!
Số thứ tự cột tìm kiếm lớn hơn tổng số cột trong vùng tìm kiếm
Vùng tìm kiếm có tất cả 5 cột dữ liệu, nhưng trong phần công thức hàm cột chứa giá trị tìm kiếm lại là 6.
4. Ví dụ áp dụng?
Ví dụ 1: Dùng hàm Vlookup tìm thông số Tên cọc và Khối lượng cọc dựa vào STT
Đề bài:
- Dữ liệu: Bảng thống kế thông số Cọc khoan nhồi.
- Yêu cầu: Dựa vào STT cọc – Dò tìm và điền Tên cọc và Khối lượng vào ô I7 và J7.
Hướng dẫn:
Phân tích yêu cầu:
- Điền Tên cọc và Khối lượng vào ô I7 và J7.
- Vùng dữ liệu dò tìm B7:F11.
- Điều kiện Dò tìm: Số thứ tự Cọc khoan nhồi.
Giải quyết yêu cầu:
|
Bước 1: |
Tại ô I7 gõ công thức =VLOOKUP(H7;B7:F11;2;0) |
|
Bước 2 |
Cố định hàng, cột và vùng cần thiết |
||||||
|
|
- Cố định ô H7: Đặt chuột vào vị trí ô H7 rồi bấm F4. Khi ký tự $ đứng trước chữ cái thì ta đang cố định cột (Cột H), khi ký tự $ đứng trước số thì ta đang cố định hàng (Hàng 7) còn khi dấu $ đứng trước cả chữ cái và chữ số thì ta đang cố định ô.
Ở trường hợp này ta sẽ cố định cả ô (Điều kiện tìm kiếm) và cả bảng (Vùng tìm kiếm). |
|
Bước 3: |
Copy ô I7 dán vào ô J7 và sửa Cột chứa giá trị tìm kiếm là 5 để dò tìm Khối lượng cọc khoan nhồi ta được kết quả. |
Ví dụ 2: Dùng hàm Vlookup dò tìm Tên điền vào khung tên sau
Đề bài:
- Dữ liệu: Bảng thành phần ký trong hồ sơ nghiệm thu.
- Yêu cầu: Điền thông tin Thành phần ký của Nhóm 1 vào trong khung tên.
Hướng dẫn:
Nhận thấy rằng điều kiện dò tìm ở đây là Nhóm 1 (1) – Để sử dụng hàm Vlookup để tiến hành dò tìm thì chúng ta phải điền trực tiếp hoặc đưa giá trị dò tìm vào một địa chỉ ô nào đó (để dễ dàng thay đổi). Ở đây tôi đã phát sinh điều kiện tìm kiếm tại ô G10.
Dựa vào cấu trúc đã biết bạn có thể dễ dàng tiến hành dò tìm kết quả trong vùng tìm kiếm (B7:E10) dựa vào điều kiện đã phát sinh (G10).
Bước 1: Tạo giá trị đặc biệt (Điều kiện tìm kiếm) – Nhóm thành phần ký.
Bước 2: Viết hàm dò tìm tên theo nhóm.
Bước 3: Cố định ô, bảng để copy công thức sang vị trí khác.
