Ứng dụng đặc biệt của hàm HLOOKUP

THÊM MỘT HÀM EXCEL CÓ NHIỀU ỨNG DỤNG:
HÀM HLOOKUP ĐƯỢC ỨNG DỤNG NHƯ THẾ NÀO?
Trong số báo 169 LBVMVT tôi đã có dịp giới thiệu đến các bạn một hàm Excel có nhiều ứng dụng khá thiết thực. Nay tôi xin giới thiệu thêm một hàm khác có ứng dụng với hiệu quả không kém: hàm HLOOKUP. Hàm này đã được ứng dụng rất phổ biến trong việc dò tìm mã hàng để tự động điền tên hàng, đơn vị tính và đơn giá vào bảng nhật ký bán hàng hết sức tiện lợi mà nhiều giáo trình tin học A đã đề cập. Trong bài viết này tôi giới thiệu thêm vài ứng dụng đặc biệt khác: xếp loại bài thi và đổi từ năm dương lịch sang năm âm lịch.
Trước hết ta hãy làm quen với đặc điểm của hàm:
a) Cú pháp: HLOOKUP(X,K,N,M) ( có máy phải thay dấu “,” bởi “;” do thiết lập khác nhau trong Control Panel)
b) Công dụng: Dò tìm giá trị X trong dòng đầu tiên của khối K, nếu tìm thấy thì lấy giá trị của ô ở cột X (cột có chứa X) tại dòng thứ N
c) Giải thích:
X là giá trị cần tìm, có thể là chuỗi, số, biểu thức, tọa độ ô, hàm.
K là khối tham chiếu tức là một bảng gồm tối thiểu 2 dòng, trong đó dòng đầu tiên gọi là dòng chỉ mục và nên được sắp xếp (sort), X được tìm trong dòng này
N là số thứ tự của dòng mà giá trị của ô tại dòng đó được trả về cho hàm
M nhận một trong hai giá trị : 0 ;1 (mặc định). Nếu M=0 thì dòng chỉ mục không cần sắp xếp. Nếu M=1 hoặc bỏ qua (không cần có M) thì dòng chỉ mục phải được sắp xếp. Nếu không có M mà dòng (hoặc cột) chỉ mục khối K chưa được sắp xếp thì giá trị trả về sẽ bị sai.
d) Mở rộng: Hàm trên được dùng khi khối tham chiếu có dạng bảng “ngang”. Nếu khối tham chiếu được bố trí theo dạng “đứng” thì ta thay hàm trên bới hàm VLOOKUP có cú pháp hoàn toàn tương tự nhưng trong phần công dụng và giải thích trên cần phải thay “dòng” thành “cột”
Ví dụ: Nếu đã có sẵn bảng tham chiếu như sau:
Mã hàng
Tên hàng
ĐV tính
Đơn giá
Nếu muốn lập công thức để “máy” tự động điền tên hàng, đơn vị tính, đơn giá mà dùng hàm IF thì sẽ hết sức dài dòng, dùng hàm VLOOKUP sẽ ngắn gọn hơn nhiều.
Chẳng hạn để điền tên hàng thì trong bảng nhật ký bán hàng thì ta chỉ cần ghi mã hàng sau đó gõ công thức vào ô tên hàng:
=VLOOKUP(đ/c ô ghi mã hàng, A2:D8,2).
Thật là ngắn gọn phải không bạn?

A01
HDD
Cái
50


B02
USB
Cái
15


C01
Chuột
Cái
8


D03
Phím
Cái
9


E01
Monitor
Cái
60


E02
FDD
Hộp
5


E03
RAM
Thanh
14


Cột chỉ mục là cột Mã hàng và đã được sắp xếp nên ta không cần có M và bảng được bố trí “đứng” nên dùng VLOOKUP. Để có thể copy công thức cho các ô khác thì khối A2:D8 phải được đổi sang địa chỉ tuyệt đối.
Ở đây tôi muốn giới thiệu thêm với các bạn 2 ứng dụng khá đặc biệt:
1) Dùng hàm HLOOKUP để xếp loại học sinh: Trước hết ta tạo bảng tham chiếu :
0
3
5
7
8.5
Đây là dòng chỉ mục và ta ghi các mốc điểm để xếp loại
(tức là điểm thấp nhất cho từng loại )

Kém
Yếu
TBình
Khá
Giỏi


Tại ô xếp loại thi của mỗi HS bạn gõ : HLOOKUP(đ/c_ôghiđiểm,$A$1:$E$2,2)
Nếu dùng hàm IF thì bạn phải dùng đến 4 IF với 4 cặp dấu ngoặc .
Hơn nữa khi có thay đổi về thang điểm xếp loại mà nếu dùng hàm IF thì bạn phải sửa tất cả các công thức xếp loại cho từng học sinh (tất nhiên chỉ cần sửa cho 1 HS rồi dùng chức năng Fill Handle để kéo xuống, nhưng cũng phải mất công kéo cho hết số HS trong toàn trường). Ngược lại, nếu bạn dùng HLOOKUP thì chỉ cần sửa đổi các mốc điểm xếp loại trong bảng tham chiếu mà không cần đá động gì đến công thức cả. (Không biết ở địa phương bạn thế nào chứ chỗ tôi chẳng hiểu vì sao mà các mốc điểm xếp loại cứ thay đổi xoành xoạch, lúc thì từ 8 điểm được xếp loại Giỏi, lúc thì từ 8.5 có lúc lại phải từ 9 điểm, cả loại Yếu và Khá cũng được thường xuyên thay đổi mốc điểm như vậy.) Nếu không nhờ chiêu thức trên có lẽ mỗi lần thống kê chúng tôi phải vả mồ hôi.
2) Dùng hàm HLOOKUP để “đọc tên âm lịch” của năm dương lịch
Trước hết bạn lập bảng tham chiếu:

A
B
C
D
E
F
G
H
I
J
K
L
M

1

0
1
2
3
4
5
6
7
8
9
10
11

2
CAN
Canh
Tân
Nhâm
Quí
Giáp
Ất
Bính
Đinh
Mậu
Kỷ



3
CHI
Thân
Dậu
Tuất
Hợi
Tý
Sửu
Dần
Mão
Thìn
Tỵ
Ngọ
Mùi

Muốn có tên năm âm lịch của một năm dương lịch ( được ghi ở ô A5 chẳng hạn), tại ô B5 bạn gõ: = HLOOKUP(mod(a5,10),B1:K2,2)&” “& HLOOKUP(mod(A5,12),B1:M3,3)
Giải thích: Hàm Mod(A5,10) dùng để xác định số dư của năm chia 10 (tức là chữ số cuối của năm) vì CAN chỉ phụ thuộc vào chữ số này)
Hàm Mod(A5:12) dùng để xác định số dư của năm chia 12, vì tên năm ÂL (CHI) được tính theo 12 con giáp.
Các vùng B1:K2,B1:M3 chính là các khối tham chiếu (khối K trong cú pháp)
Nếu bạn dùng hàm IF để thực hiện việc này thì không biết bạn có đủ kiên nhẫn để gõ 21 lần tên hàm và chừng đó lần để mở, đóng dấu ngoặc hay không nữa, đó là chưa tính hàm Mod.
Ngoài ra cũng sẽ rất tiện lợi nếu bạn dùng hàm này vào việc dò tìm và tham chiếu bậc lương của CBCC để điền hệ số lương và tính lương cho từng người một cách nhanh chóng: Với mỗi CBCC bạn chỉ cần ghi bậc lương, còn hệ số lương thì máy sẽ dò trong thang bảng lương mà tự động điền vào, còn việc tính lương khi đã có hệ số thì quá đơn giản rồi phải không bạn?
Chúc bạn sử dụng có hiệu quả hàm trên vào công việc của mình.

HUỲNH CÔNG DŨ – THCS Trần Cao Vân, Duy Xuyên, Quảng Nam
E-mail: [email protected] ; [email protected]