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:

 ABCD
  1MNVHọ tênNăm sinhGiới tính
  241001Nguyễn Khánh Minh1984Nam
  341002Nguyễn Thanh Thúy1983Nữ
  441003Trần Xuân Sơn1986Nam
  541004Đoàn Minh Phúc1986Nam

Sheet2:

 ABCD
  1MCCNguyên quánHọc VấnNăm sinh
  241001Hồ Chí MinhĐại học 
  341002Bình DươngĐại học 
  441003Đồng NaiĐại học 
  541004Hà GiangCao Đẳ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

 ABCD
  1MCCNguyên quánHọc VấnNăm sinh
  241001Hồ Chí MinhĐại học1984
  341002Bình DươngĐại học1983
  441003Đồng NaiĐại học1986
  541004Hà GiangCao Đẳng1986

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.

 ABCDE
  1 NV1NV2NV3NV4
  2MNV41001410024100341004
  3Họ tênNguyễn Khánh MinhNguyễn Thanh ThúyTrần Xuân SơnĐoàn Minh Phúc
  4Năm sinh1884198319861986
 5Giới TínhNamNữNamNam

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

 ABCD
  1MCCNguyên quánHọc VấnNăm sinh
  241001Hồ Chí MinhĐại học1984
  341002Bình DươngĐại học1983
  441003Đồng NaiĐại học1986
  541004Hà GiangCao Đẳng1986

Á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

Thống kê
  • Đang truy cập60
  • Máy chủ tìm kiếm5
  • Khách viếng thăm55
  • Hôm nay7,011
  • Tháng hiện tại150,393
  • Tổng lượt truy cập3,828,969
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