Các hàm tham chiếu trong excel

Chủ nhật - 15/09/2013 03:30
Các hàm đó là Vlookup và Hlookup giúp chúng ta dò tìm và trả về giá trị tham chiếu được ở một bảng dữ liệu khác
Excel có những công cụ tuyệt vời để giúp chúng ta làm việc nhanh chóng, chính xác và đạt hiệu quả cao trong công việc bằng cách tận dụng những thông tin sẵn có thể tham chiếu vào bảng tính mới mà không phải gõ lại thông tin,

Hôm nay tôi xin giới thiệu đến các bạn  các hàm tham chiếu trong excel: Vlookup, Hlookup

I. Vlookup up là dạng hàm tham chiếu dữ liệu trong mảng một chiều. có nghĩa là nó chỉ tham chiếu cho chính nó và những cột năm phía sau. Nôm na như thế này: Nếu cột tham chiếu là cột B; thì nó chỉ tham chiếu được từ cột B ~ mà không tham chiếu được cho cột A.


Cú pháp:

=VLOOKUP(lookup_value, table_arraycol_index_num, [range_lookup])

Trong đó:
  • lookup_value: giá trị cần dò tìm
  • table_array: một vùng để dò tìm lookup_value, cột đầu tiên trong table_array sẽ dùng để dò tìm giá trị lookup_value, cột này phải có chứa ít nhất một giá trị chứa trong lookup_value thì mới tìm được giá trị tham chiếu của nó, table_array có thể cùng sheet hoặc khác sheet với lookup_value, đồng thời table_array có thể cùng file hoặc khác file với lookup_value.
enlightenedLưu ý là nếu tham chiếu trong một vùng dữ liệu nội trong sheet thì phải nhấn phím F4 để đưa về địa chỉ tuyệt đối của vùng dữ liệu nếu không khi kéo thả công thức ở các ô khác thì sẽ bị lệch vùng chứa dữ liệu dẫn đến sai số.
Còn nếu chúng ta tham chiếu luôn cả 1 cột thì khi kéo công thức không sợ bị lệch dữ liệu nhé.

Ví dụ: Vlookup(A:A, A:B,2,0)
  • col_index_num: dùng để chỉ tổng số cột trong table_array kể từ cột tham chiếu đến cột chứa giá trị cần trả về nếu tìm thấy. cột đầu tiên được tính là 1.Để biết được số cột chứa giá trị cần trả về chúng ta để ý trong quá trình chọn vùng tham chiếu (Table_array) trong Excel sẽ đếm luôn và xuất hiện tại phía trên cùng góc phải tương ứng với vị trí chuột dừng lại. nên nhớ là bấm giữ chuột đủ lâu để ta nhìn thấy con số đó nhé.
  • range_lookup: Cái này rất quan trọng nếu chúng ta bỏ qua, mà chỉ dùng có ba tham số trên thì rất có thể dẫn đến kết quả trả về không như chúng ta mong muốn.
Ý nghĩa của Range_lookup là: là giá trị logic để chỉ định cho hàm vlookup thực hiện việc tìm kiếm và trả về giá trị tuyệt đối (Exact Match) hay tương đối approximate match.

Range_lookup có 2 giá trị True (1) và False (0)

►True (1) hoặc bỏ trống tham số này:
Nếu chúng ta nhập là True hoặc 1 hoặc bỏ trống tham số này thì hàm vlookup tìm kiếm và nó sẽ trả về giá trị của cột chứa giá trị cần trả về nếu giá trị tham chiếu ở lookup_value và giá trị tham chiếu ở cột đầu tiên trong table_array là khớp hoàn toàn, còn nếu không trùng khớp hoàn toàn nó sẽ trả về giá trị lớn nhất kết cận mà nhỏ hơn giá trị của vlookup value.
Do đó, nếu bạn bỏ không dùng giá tr range_lookup, hoặc được thiết lp TRUE hoặc số 1 thì ct đu tiên ca ca table_array phi được sp xếp theo th t tăng dần, nếu không thì hàm VLOOKUP của bạn sẽ chạy không đúng.
Ví dụ:

►False (0)
Nếu chúng ta nhập là False hoăc số 0 cho tham số này thì VLOOKUP sẽ chỉ tìm và trả về giá trị trùng khớp nhau hoàn toàn.

Ví dụ:

Giả giử bạn có 2 sheet có tên lần lượt là “Sheet1” và “Sheet2”, chứa dữ liệu bên dưới:

Sheet1:

  A B C D
  1 MNV Họ tên Năm sinh Giới tính
  2 41001 Nguyễn Khánh Minh 1984 Nam
  3 41002 Nguyễn Thanh Thúy 1983 Nữ
  4 41003 Trần Xuân Sơn 1986 Nam
  5 41004 Đoàn Minh Phúc 1986 Nam

Sheet2:

  A B C D
  1 MCC Nguyên quán Học Vấn Năm sinh
  2 41001 Hồ Chí Minh Đại học  
  3 41002 Bình Dương Đại học  
  4 41003 Đồng Nai Đại học  
  5 41004 Hà Giang Cao Đẳng  

Giá trị cột “Năm sinh” của Sheet2 được lấy từ Sheet1 thông qua hàm VLOOKUP và giá trị dò tìm & cột dò tìm là cột MNV (mã nhân viên) của 2 sheet.

Hàm VLOOKUP được đặt ở cột “Năm sinh” của Sheet2 như sau:

Dòng 2 –> 5 lần lược như sau:

=VLOOKUP(A2,'Sheet1'!$A$2:$C$5,3,0)

=VLOOKUP(A3,'Sheet1'!$A$2:$C$5,3,0)

=VLOOKUP(A4,'Sheet1'!$A$2:$C$5,3,0)

=VLOOKUP(A5,'Sheet1'!$A$2:$C$5,3,0)

Kết quả trả về của Sheet2 như bên dưới

  A B C D
  1 MCC Nguyên quán Học Vấn Năm sinh
  2 41001 Hồ Chí Minh Đại học 1984
  3 41002 Bình Dương Đại học 1983
  4 41003 Đồng Nai Đại học 1986
  5 41004 Hà Giang Cao Đẳng 1986

Giải thích:

Công thức dòng 2:

=VLOOKUP(A2,'Sheet1'!$A$2:$C$5,3,0)

Dò tìm giá trị A2 (41001) của Sheet 2, trong vùng chọn từ A2 đến A5 (từ 41001 đến 41005) của Sheet1, nếu tìm thấy thì trả về giá trị năm sinh tức cột thứ 3, tức cột “Năm sinh” (thứ tự dòng/hàng tương ứng với dòng/hàng có mã 41001 được tìm thấy)

Tương tự cho các dòng còn lại, từ 3 đến 5.

Típ: Nếu bạn không nhớ cấu trúc của hàm vlookup thì ta làm theo cách này nhé.
Sau khi chèn dấu = vào cell rồi đưa chuột lên góc bên trái của thanh address bar, Nếu hàm Vlookup chưa có ở đây thì bạn click bào mũi tên xổ xuống chọn, more functions rồi chọn loại hàm lookup & reference để chọn hàm Vlookup.
Sau đó nhấp chuột vào chữ Vlookup sẽ hiện ra hộp thọai Function Arguments và tiến hành nhập các tham số cần thiết sau đó nhấp OK ta sẽ thực hiện xong hàm Vlookup.

Vlookup 1Vlookup Wizard


xem video hướng dẫn hàm vlookup trên youtube
2. Hàm Hlookup
Hàm này cách dùng và chức năng giống hệt như hàm Vlookup, tuy nhiên thay vì tham chiếu theo cột thì nó lại tham chiếu theo dòng.

Giả sử ta có bảng Quản lý Nhân viên quay theo hướng ngang như bảng bên dưới.

  A B C D E
  1   NV1 NV2 NV3 NV4
  2 MNV 41001 41002 41003 41004
  3 Họ tên Nguyễn Khánh Minh Nguyễn Thanh Thúy Trần Xuân Sơn Đoàn Minh Phúc
  4 Năm sinh 1884 1983 1986 1986
 5 Giới Tính Nam Nữ Nam Nam

Và ta cũng dùng hàm Hlookup và tham chiếu số năm sinh vào bảng sau:

  A B C D
  1 MCC Nguyên quán Học Vấn Năm sinh
  2 41001 Hồ Chí Minh Đại học 1984
  3 41002 Bình Dương Đại học 1983
  4 41003 Đồng Nai Đại học 1986
  5 41004 Hà Giang Cao Đẳng 1986

Áp dụng Functions Wizard cho hàm Hlookup như sau:
Hlookup Wizard

Chúc các  bạn thành công!
Ngoài cách tham chiếu như hàm hlookup và vlookup ra thì trong excel cũng có hàm có chức năng tham chiếu và trả về dữ liệu của một ô xác định nào đó, tuy cách dùng hơi khác. Một trong số đó là hàm index.

 

Tác giả bài viết: Minh Phú

Chú ý: Việc đăng lại bài viết trên ở website hoặc các phương tiện truyền thông khác mà không ghi rõ nguồn http://aneedz.com là vi phạm bản quyền
 Từ khóa: hàm right

Tổng số điểm của bài viết là: 29 trong 6 đánh giá

Xếp hạng: 4.8 - 6 phiếu bầu
Click để đánh giá bài viết

  Ý kiến bạn đọc

Mã bảo mật   
Thống kê
  • Đang truy cập6
  • Máy chủ tìm kiếm3
  • Khách viếng thăm3
  • Hôm nay6,773
  • Tháng hiện tại64,508
  • Tổng lượt truy cập1,585,360
Bạn đã không sử dụng Site, Bấm vào đây để duy trì trạng thái đăng nhập. Thời gian chờ: 60 giây