Nhóm hàm tham chiếu trong excel được sử dùng để dò tìm (lookup) và trích xuất thông tin từ những bảng liên quan (bảng tham chiếu) theo một tiêu chí nào đó. Đây cũng là một trong những nhóm hàm thường được sử dụng trong Excel.

Trong bài học này, chúng tôi sẽ trình bày 3 hàm trong nhóm hàm tham chiếu gồm hàm VLOOKUP, hàm HLOOKUP và hàm INDEX. Công dụng, cách sử dụng và ví dụ chúng tôi sẽ trình bày ngay bên dưới.

Nhóm hàm tham chiếu trong excel – Hàm VLOOKUP

Hàm VLOOKUP được sử dụng để trích xuất thông tin từ bảng tham chiếu theo chiều dọc. Hãy quan sát bảng tham chiếu 1 (Bảng chức vụ) ở hình bên trên để thấy rõ cách thể hiện thông tin của bảng này.

Cú pháp hàm VLOOKUP trong excel

VLOOKUP(Trị dò, Bảng tham chiếu, Chỉ số cột tham chiếu, Cách dò)

Trong đó: Trị dò thuộc bảng chính; Chỉ số cột tham chiếu tính từ trái qua phải và bắt đầu từ 1; Cách dò là 0 nếu Trị dò là chuỗi hoặc ngày tháng năm, Cách dò là 1 nếu Trị dò là số.

Ví dụ sử dụng hàm VLOOKUP trong excel (Lập công thức cho cột CHỨC VỤ của bảng Danh sách nhân viên công ty ABC) dựa vào bảng CHỨC VỤ

B9 là Mã chức vụ (Trị dò); B3:C6 là bảng chức vụ (Bảng tham chiếu luôn luôn dùng địa chỉ tuyệt đối); 2 là Chỉ số của cột tham chiếu (cột Chức vụ trong bảng chức vụ); vì B9 chứa chuỗi nên Cách dò là 0.

Kết quả sau khi hoàn thành

Nhóm hàm tham chiếu trong excel – Hàm HLOOKUP

Nếu VLOOKUP dò tìm theo chiều dọc thì HLOOKUP dò tìm theo chiều ngang (Bảng khu vực)

Cú pháp hàm HLOOKUP trong excel

HLOOKUP(Trị dò, Bảng tham chiếu, Chỉ số dòng tham chiếu, Cách dò)

Ví dụ sử dụng hàm HLOOKUP trong excel (Lập công thức cho cột KHU VỰC của bảng Danh sách nhân viên công ty ABC) dựa vào bảng KHU VỰC

Kết quả sau khi hoàn thành

Nhóm hàm tham chiếu trong excel – Bài tập thực hành

Cho bảng tính như hình và lập công thức cho những cột tô màu vàng

Câu 1: SỐ NGÀY THUÊ = NGÀY ĐI – NGÀY ĐẾN

Câu 2: TIỀN PHÒNG = Đơn giá phòng (Tra trong bảng ĐƠN GIÁ PHÒNG) * SỐ NGÀY THUÊ

Câu 3: Dựa vào 2 ký tự cuỗi của MÃ THANH TOÁN và tra trong bảng ĐƠN GIÁ KHẨU PHẦN ĂN để lập công thức cho cột ĐƠN GIÁ KHẨU PHẦN ĂN

Câu 4: TIỀN ĂN = SỐ NGÀY THUÊ * ĐƠN GIÁ KHẨU PHẦN ĂN

Câu 5: THÀNH TIỀN = TIỀN PHÒNG + TIỀN ĂN

Câu 6: TỶ LỆ GIẢM dựa vào SỐ NGÀY THUÊ và tra trong bảng TỶ LỆ GIẢM

Câu 7: PHẢI TRẢ = THÀNH TIỀN – TỶ LỆ GIẢM