Friday, October 15, 2010

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

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 nó được . Trong một nhóm các công cụ có chức năng như trên mà Excel hỗ trợ thì VlOOKUP() có lẽ là được nhiều người dùng hơn cả, với cú pháp khá khó hiểu đối với người mới học Excel, ta sẽ làm rõ nó trong bài viết này.

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.

VLOOKUP("Trường cần lấy", "Bảng chứa các trường chọn dạng F4", "vi trị của danh sách trường cần lấy", "Cách sắp xếp 0/1");

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. Giá trị dò tìm 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 đơ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)

Giả thích:

+B6: Giá trị để dò tìm ( dò theo mã AF.81141)

+DLDV!$A$3:$G$11: Vùng dò là vùng từ A3:G11 trong sheet DLDV

+ 2 : Cột cần trả về là cột 2 trong vùng dò. ( cột tên công việc)

+0 : tìm chính xác

Như hình 4, sau đó nhấn Enter

"

Hình 3

+ 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 đánh hàm: =VLOOKUP(B6;DLDV!$A$3:$G$11;4;0)

+Tại ô G6 ta đánh hàm: =VLOOKUP(B6;DLDV!$A$3:$G$11;5;0)

+Tại ô H6 ta đánh hàm: =VLOOKUP(B6;DLDV!$A$3:$G$11;6;0)

+Tại ô I6 ta đánh hàm: =VLOOKUP(B6;DLDV!$A$3:$G$11;7;0)

- Ta phải điền dữ liệu từ C6:I8 nên 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 4

No comments:

About Me

My photo
Định Tường, Yên Định, Vietnam
KakalosVinh45

Followers