Hàm VLOOKUP() và ứng dụng trong công tác lập dự toán trên Excel

Excel hỗ trợ một công cụ tìm kiếm và trả về giá trị rất hiệu quả nhưng không phải ai cũng khai thác được. Trong một nhóm các công cụ thì Vlookup() có lẽ là hàm được nhiều người dùng hơn cả, ta sẽ làm rõ hàm Vlookup() trong bài viết này.

Ngoài bài này các bạn tham khảo thêm tài liệu dùng Vlookup điền giá vật liệu dùng trong dự toán nhiều hạng mục, gói thầu

Hàm VLOOKUP

Chức năng: Hàm VLOOKUP là hàm dò tìm theo cột, sẽ trả về giá trị của một ô nằm trên một cột nào đó nếu thỏa mãn điều kiện dò tìm.

Cú pháp hàm:  Vlookup(lookup_value,table_array,col_index_num,option_lookup)

- Lookup_value: là giá trị dùng để dò tìm, giá trị này sẽ được dò tìm trong cột đầu tiên của bảng dữ liệu dò tìm. Nó có thể là một số, một chuỗi, một công thức trả về giá trị hay một tham chiếu đến một ô nào đó dùng làm giá trị dò tìm.

- Table_array: là bảng dùng để dò tìm, bảng dò tìm có thể là tham chiếu đến một vùng nào đó hay Name trả về vùng dò tìm. Bảng dò tìm gồm có Rj hàng và Ci cột (I,j >=1), trong đó cột thứ nhất của bảng dò tìm sẽ được dùng để dò tìm. ( thường chuyển về địa chỉ tuyệt đối bằng cách nhấn F4 để cố định vùng dò tìm, vùng dò tìm này phải bao các giá trị cần trả về, vùng dò được bắt đầu tại cột có giá trị dò tìm)

- Col_index_num: là số thứ tự của cột (tính từ trái qua phải) trong bảng dò tìm chứa giá trị mà ta muốn trả về. Col_index_num phải >=1 và <= số cột lớn nhất có trong bảng dò tìm, ngược lại hàm sẽ trả về #VALUE! hoặc #REF. ( số thứ tự này được xác định trong vùng dò tìm. VD vùng dò tìm C2:F10 nếu muốn giá trị trả về là cột C thì đánh 1, D đánh 2 ..nhưng không được vượt qua vùng dò tìm như trong ví dụ này không được vựơt quá 4)

- Option_lookup: là tùy chọn xác định kiểu dò tìm, có 2 kiểu dò tìm:

+ True hoặc 1 hoặc để trống: là kiểu dò tìm tương đối, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, nó sẽ lấy giá trị lớn nhất mà có giá trị nhỏ hơn giá trị dò tìm.

+ False hoặc 0: là kiểu dò tìm chính xác, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, hàm sẽ trả về #N/A.

Ví dụ: Giả sử có một file dữ liệu đơn giá như hình 1

Hình 1

Và một bảng dự toán có các mã hiệu đơn giá có trong file trên như hình 2

Hình 2

Yêu cầu: Dùng hàm VLOOKUP( ) để điền đúng tên công việc, đơn vị, đơn giá vật liệu chính, vật liệu phụ, nhân công,  máy thi công vào bảng trên.

Cách làm:

-Giả sử 2 file trên là 2 sheet của một file Excel
+ Bảng như hình 1 là sheet DLDV (dữ liệu đầu vào)
+ Bảng như hình 2 là sheet DTCT (dự toán công trình)
Sang sheet DTCT tại ô C6 ta đánh:  =VLOOKUP(B6;DLDV!$A$3:$G$11;2;0) như hình 4, sau đó nhấn Enter

Hình 4

Giải thích:
+ B6: Giá trị để tìm kiếm (tìm kiếm theo mã AF.81141)
+ DLDV!$A$3:$G$11: Vùng tìm kiếm là vùng từ A3:G11 trong sheet DLDV
+ 2: Cột cần trả về là cột 2 trong vùng tìm kiếm. (cột tên công việc)
+ 0: Tìm chính xác

+ Tương tự tại ô D6 ta đánh hàm: =VLOOKUP(B6;DLDV!$A$3:$G$11;3;0) chỉ khác chỉ số cột (Col_index_num)=3
+Tại ô F6 ta gõ hàm: =VLOOKUP(B6;DLDV!$A$3:$G$11;4;0)
+Tại ô G6 ta gõ hàm: =VLOOKUP(B6;DLDV!$A$3:$G$11;5;0)
+Tại ô H6 ta gõ hàm: =VLOOKUP(B6;DLDV!$A$3:$G$11;6;0)
+Tại ô I6 ta gõ hàm:  =VLOOKUP(B6;DLDV!$A$3:$G$11;7;0)

- Sau khi lập công thức như trên cho hàng 1, để tính cho cả bảng tính từ C6:I8, ta bôi đen từ C6:I8 rồi nhấn Ctrl + D được kết quả như hình 5.

Hình 5

Tweet