MỘT HÀM EXCEL CÓ NHIỀU ỨNG DỤNG THIẾT THỰC
Có một hàm trong Excel có nhiều ứng dụng khá thiết thực nhưng lại ít được người sử dụng quan tâm chú ý đến, đó là hàm DCOUNT dùng để đếm số dòng thỏa mãn điều kiện nào đó.
Để thực hiện yêu cầu này nhiều người hay dùng hàm COUNTIF, tuy nhiên hàm này có nhiều nhược điểm là chỉ đếm các ô thỏa mãn 1 điều kiện nào đó chứ không thể thỏa mãn đồng thời nhiều điều kiện. Chẳng hạn ta chỉ có thể đếm các HS giỏi trong một vùng chứ không thể thêm điều kiện là HS giỏi đó là của lớp nào. Thông thường để giải quyết thêm điều kiện lớp thì chỉ có cách "kéo chuột" chọn vùng cho phù hợp với địa chỉ của từng lớp. Như vậy mỗi lần thống kê nếu số lượng HS của lớp thay đổi thì ta phải "kéo chuột" chọn lại vùng rất là mất công và mất tính chất tự động hóa của ngành Tin học, hơn nữa nếu HS các lớp học được xáo trộn lung tung (HS của một lớp lại nằm rãi rác trong danh sách) thì cũng không thể nào xác định vùng cần đếm được. Ngoài ra nếu thêm một yêu cầu nữa là đếm số HS nữ đạt loại giỏi của một lớp nào đó thì đành bó tay.
Làm thế nào để giải quyết yêu cầu này? Hãy dùng hàm DCOUNT. Nó không chỉ cho phép đếm các dòng thỏa mãn 2 điều kiện mà còn có thể nhiều hơn thế. Ví dụ với hàm này ta có thể đếm số HS trong một độ tuổi nhất định nào đó, đang học lớp 6 và có chỗ ở hiện tại là thôn 1, thậm chí có thể thêm điều kiện là tốt nghiệp Tiểu học năm vừa qua (để thống kê tỉ lệ tuyển sinh vào lớp 6).
Cách sử dụng hàm này như thế nào?
Trước hết tôi trình bày cú pháp của hàm : DCOUNT(vungdulieu; cot ; vungtieuchuan)
Công dụng
: Đếm các ô chứa số trên cột "cot" tại những dòng trong vùng "vungdulieu" thoả điều kiện được xác định bởi "vungtieuchuan". Nếu muốn đếm cả các ô trên cột "cot" chứa dữ liệu có kiểu bất kỳ ta cùng hàm DCOUNTA (cú pháp cũng giống như vậy)
Trong đó:
- vungdulieu (Vùng dữ liệu) là vùng có chứa dữ liệu cần đếm, vùng này cần nên chọn thật lớn, có thể nên là vùng quét hết số HS trong toàn trường và nhất thiết phải chứa đủ các cột cần phải thỏa mãn điều kiện, chẳng hạn ở ví dụ nêu trên thì phải đủ cột tên HS, lớp, năm sinh, chổ ở, năm tốt nghiệp Tiểu học ...Vùng này phải có dòng trên cùng là dòng tiêu đề. Dòng tiêu đề chứa tên cột và nên gõ không dấu.
- cot là cột chứa dữ liệu cần đếm, chẳng hạn cột tên ( với cột tên thì phải dùng hàm DCOUNTA, vì tên không phải là dữ liệu kiểu số) tuy nhiên nếu ta muốn đếm số liệu HS theo từng loại qua kết quả thi thì nên chọn cột điểm thi vì có thể có HS có tên nhưng bỏ thi (bỏ học). Cách chọn cot là nhập số thứ tự của cột tính từ cột đầu tiên của vungdulieu, ví dụ "vungduieu" là C4:K1500 mà cột cần đếm là cột E thì "cot" là số 3, cũng có thể nhấp chuột vào ô ở cột E tại dòng tiêu đề.
- vungtieuchuan (vùng tiêu chuẩn : criteria) gồm tối thiểu 2 dòng: dòng tiêu đề và dòng chứa điều kiện cần đếm và phải nằm ngoài vungdulieu Ví dụ để đếm số HS thỏa mãn những điều kiện như đã nêu trong ví dụ ở trên, ta lập bảng như sau :
[TABLE="width: 657"]
[TR]
[TD]
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Đây là tên cột do bảng tính Excel tự động tạo sẵn
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]lop
[/TD]
[TD]namsinh
[/TD]
[TD]Thon
[/TD]
[TD]NamTNTH
[/TD]
[TD]Tên cột giống với tên cột (dòng đầu) của vùng dữ liệu
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]6
[/TD]
[TD]1995
[/TD]
[TD]1
[/TD]
[TD]2005
[/TD]
[TD]Điều kiện cần đếm
[/TD]
[/TR]
[/TABLE]
Vungtieuchuan như trên sẽ có tọa độ là U1:Y2 và với vungtieuchuan như vậy số liệu đếm được sẽ là số HS đang học lớp 6, sinh năm 1995, ở thôn 1 và TNTH vào năm 2005.
Tiêu chuẩn (Criteria) được sử dụng như trên là tiêu chuẩn chuỗi, với dòng tiêu đề phải được lập giống nguyên xi như dòng tiêu đề của vùng dữ liệu (nên copy từ vungdulieu). Tuy nhiên nếu cột lớp (lop) người ta nhập cụ thể là 6/1, 6/2, 6/3 ... hay 6A, 6B, .... mà ta muốn thống kê theo khối lớp thì làm thế nào?
Trong trường hợp này ta không thể dùng tiêu chuẩn chuỗi được nữa mà phải thay bằng tiêu chuẩn công thức. Với tiêu chuẩn này, tại ô U1 ta phải sửa lại tên khác, không trùng với bất cứ ô nào trên dòng tiêu đề của vùng dữ liệu (vungdulieu), chẳng hạn tên là "lop6" và tại ô U2 phải nhập công thức là =left(diachio,1)="6", trong đó diachio là địa chỉ của ô ngay dưới ô lop của vungdulieu. Với vungtieuchuan này "máy" sẽ đếm tất cả những dòng mà ô ở cột lop trong vùng dữ liệu có kí tự bắt đầu là "6".
Dùng hàm này tôi đã giảm được rất nhiều công sức cho việc thống kê báo cáo các số liệu đáp ứng nhiều điều kiện ràng buộc. Trước đây tôi phải thường xuyên thay đổi công thức khi lâp bảng thống k ê do số lượng HS trong mỗi lớp thường hay thay đổi. Nay nhờ sử dụng hàm này, việc thống kê đã được tự động hóa, chỉ việc nhập số liệu xong là bảng thống kê đã có sẵn sàng cho ta in ngay mà không cần phải làm thêm bất cứ một thao tác nào.
Xin giới thiệu để các bạn tham khảo, vận dụng. Đối với những bạn thường xuyên lập bảng thống kê thì việc lập sẵn một bảng mẫu sẽ rất cần thiết nhằm loại bỏ các thao tác thủ công, giảm được khá nhiều thời gian cho công việc lập bảng báo cáo số liệu thống kê. Chúc các bạn thành công trong công việc của mình.
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 nhiều 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. 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 chỉ mục không có 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:
[TABLE="width: 693"]
[TR]
[TD]Mã hàng
[/TD]
[TD]Tên hàng
[/TD]
[TD]ĐV tính
[/TD]
[TD]Đơn giá
[/TD]
[/TR]
[TR]
[TD]A01
[/TD]
[TD]HDD
[/TD]
[TD]Cái
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]B02
[/TD]
[TD]USB
[/TD]
[TD]Cái
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]C01
[/TD]
[TD]Chuột
[/TD]
[TD]Cái
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]D03
[/TD]
[TD]Phím
[/TD]
[TD]Cái
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]E01
[/TD]
[TD]Monitor
[/TD]
[TD]Cái
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]E02
[/TD]
[TD]FDD
[/TD]
[TD]Hộp
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]E03
[/TD]
[TD]RAM
[/TD]
[TD]Thanh
[/TD]
[TD]
[/TD]
[/TR]
[/TABLE]
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
8 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 điểm thi: Trước hết ta tạo bảng tham chiếu :
Tại ô xếp loại thi của mỗi HS bạn gõ : HLOOKUP(ô ghi điểm thi,$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 .
Khi có thay đổi về thang điểm xếp loại (mốc điểm xếp loại) thì bạn chỉ cần thay đổi trong bảng tham chiếu mà không cần đá động gì đến công thức cả. (Khỏi mất công sửa công thức xếp loại cho tất cả HS)
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:
[TABLE="width: 483"]
[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]CAN
[/TD]
[TD]Canh
[/TD]
[TD]Tân
[/TD]
[TD]Nhâm
[/TD]
[TD]Quí
[/TD]
[TD]Giáp
[/TD]
[TD]Ất
[/TD]
[TD]Bính
[/TD]
[TD]Đinh
[/TD]
[TD]Mậu
[/TD]
[TD]Kỷ
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]CHI
[/TD]
[TD]Thân
[/TD]
[TD]Dậu
[/TD]
[TD]Tuất
[/TD]
[TD]Hợi
[/TD]
[TD]Tý
[/TD]
[TD]Sửu
[/TD]
[TD]Dần
[/TD]
[TD]Mão
[/TD]
[TD]Thìn
[/TD]
[TD]Tỵ
[/TD]
[TD]Ngọ
[/TD]
[TD]Mùi
[/TD]
[/TR]
[/TABLE]
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 CBCNV để điền hệ số lương và tính lương cho từng người một cách nhanh chóng.
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.
Có một hàm trong Excel có nhiều ứng dụng khá thiết thực nhưng lại ít được người sử dụng quan tâm chú ý đến, đó là hàm DCOUNT dùng để đếm số dòng thỏa mãn điều kiện nào đó.
Để thực hiện yêu cầu này nhiều người hay dùng hàm COUNTIF, tuy nhiên hàm này có nhiều nhược điểm là chỉ đếm các ô thỏa mãn 1 điều kiện nào đó chứ không thể thỏa mãn đồng thời nhiều điều kiện. Chẳng hạn ta chỉ có thể đếm các HS giỏi trong một vùng chứ không thể thêm điều kiện là HS giỏi đó là của lớp nào. Thông thường để giải quyết thêm điều kiện lớp thì chỉ có cách "kéo chuột" chọn vùng cho phù hợp với địa chỉ của từng lớp. Như vậy mỗi lần thống kê nếu số lượng HS của lớp thay đổi thì ta phải "kéo chuột" chọn lại vùng rất là mất công và mất tính chất tự động hóa của ngành Tin học, hơn nữa nếu HS các lớp học được xáo trộn lung tung (HS của một lớp lại nằm rãi rác trong danh sách) thì cũng không thể nào xác định vùng cần đếm được. Ngoài ra nếu thêm một yêu cầu nữa là đếm số HS nữ đạt loại giỏi của một lớp nào đó thì đành bó tay.
Làm thế nào để giải quyết yêu cầu này? Hãy dùng hàm DCOUNT. Nó không chỉ cho phép đếm các dòng thỏa mãn 2 điều kiện mà còn có thể nhiều hơn thế. Ví dụ với hàm này ta có thể đếm số HS trong một độ tuổi nhất định nào đó, đang học lớp 6 và có chỗ ở hiện tại là thôn 1, thậm chí có thể thêm điều kiện là tốt nghiệp Tiểu học năm vừa qua (để thống kê tỉ lệ tuyển sinh vào lớp 6).
Cách sử dụng hàm này như thế nào?
Trước hết tôi trình bày cú pháp của hàm : DCOUNT(vungdulieu; cot ; vungtieuchuan)
Công dụng
: Đếm các ô chứa số trên cột "cot" tại những dòng trong vùng "vungdulieu" thoả điều kiện được xác định bởi "vungtieuchuan". Nếu muốn đếm cả các ô trên cột "cot" chứa dữ liệu có kiểu bất kỳ ta cùng hàm DCOUNTA (cú pháp cũng giống như vậy)
Trong đó:
- vungdulieu (Vùng dữ liệu) là vùng có chứa dữ liệu cần đếm, vùng này cần nên chọn thật lớn, có thể nên là vùng quét hết số HS trong toàn trường và nhất thiết phải chứa đủ các cột cần phải thỏa mãn điều kiện, chẳng hạn ở ví dụ nêu trên thì phải đủ cột tên HS, lớp, năm sinh, chổ ở, năm tốt nghiệp Tiểu học ...Vùng này phải có dòng trên cùng là dòng tiêu đề. Dòng tiêu đề chứa tên cột và nên gõ không dấu.
- cot là cột chứa dữ liệu cần đếm, chẳng hạn cột tên ( với cột tên thì phải dùng hàm DCOUNTA, vì tên không phải là dữ liệu kiểu số) tuy nhiên nếu ta muốn đếm số liệu HS theo từng loại qua kết quả thi thì nên chọn cột điểm thi vì có thể có HS có tên nhưng bỏ thi (bỏ học). Cách chọn cot là nhập số thứ tự của cột tính từ cột đầu tiên của vungdulieu, ví dụ "vungduieu" là C4:K1500 mà cột cần đếm là cột E thì "cot" là số 3, cũng có thể nhấp chuột vào ô ở cột E tại dòng tiêu đề.
- vungtieuchuan (vùng tiêu chuẩn : criteria) gồm tối thiểu 2 dòng: dòng tiêu đề và dòng chứa điều kiện cần đếm và phải nằm ngoài vungdulieu Ví dụ để đếm số HS thỏa mãn những điều kiện như đã nêu trong ví dụ ở trên, ta lập bảng như sau :
[TABLE="width: 657"]
[TR]
[TD]
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Đây là tên cột do bảng tính Excel tự động tạo sẵn
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]lop
[/TD]
[TD]namsinh
[/TD]
[TD]Thon
[/TD]
[TD]NamTNTH
[/TD]
[TD]Tên cột giống với tên cột (dòng đầu) của vùng dữ liệu
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]6
[/TD]
[TD]1995
[/TD]
[TD]1
[/TD]
[TD]2005
[/TD]
[TD]Điều kiện cần đếm
[/TD]
[/TR]
[/TABLE]
Vungtieuchuan như trên sẽ có tọa độ là U1:Y2 và với vungtieuchuan như vậy số liệu đếm được sẽ là số HS đang học lớp 6, sinh năm 1995, ở thôn 1 và TNTH vào năm 2005.
Tiêu chuẩn (Criteria) được sử dụng như trên là tiêu chuẩn chuỗi, với dòng tiêu đề phải được lập giống nguyên xi như dòng tiêu đề của vùng dữ liệu (nên copy từ vungdulieu). Tuy nhiên nếu cột lớp (lop) người ta nhập cụ thể là 6/1, 6/2, 6/3 ... hay 6A, 6B, .... mà ta muốn thống kê theo khối lớp thì làm thế nào?
Trong trường hợp này ta không thể dùng tiêu chuẩn chuỗi được nữa mà phải thay bằng tiêu chuẩn công thức. Với tiêu chuẩn này, tại ô U1 ta phải sửa lại tên khác, không trùng với bất cứ ô nào trên dòng tiêu đề của vùng dữ liệu (vungdulieu), chẳng hạn tên là "lop6" và tại ô U2 phải nhập công thức là =left(diachio,1)="6", trong đó diachio là địa chỉ của ô ngay dưới ô lop của vungdulieu. Với vungtieuchuan này "máy" sẽ đếm tất cả những dòng mà ô ở cột lop trong vùng dữ liệu có kí tự bắt đầu là "6".
Dùng hàm này tôi đã giảm được rất nhiều công sức cho việc thống kê báo cáo các số liệu đáp ứng nhiều điều kiện ràng buộc. Trước đây tôi phải thường xuyên thay đổi công thức khi lâp bảng thống k ê do số lượng HS trong mỗi lớp thường hay thay đổi. Nay nhờ sử dụng hàm này, việc thống kê đã được tự động hóa, chỉ việc nhập số liệu xong là bảng thống kê đã có sẵn sàng cho ta in ngay mà không cần phải làm thêm bất cứ một thao tác nào.
Xin giới thiệu để các bạn tham khảo, vận dụng. Đối với những bạn thường xuyên lập bảng thống kê thì việc lập sẵn một bảng mẫu sẽ rất cần thiết nhằm loại bỏ các thao tác thủ công, giảm được khá nhiều thời gian cho công việc lập bảng báo cáo số liệu thống kê. Chúc các bạn thành công trong công việc của mình.
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 nhiều 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. 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 chỉ mục không có 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:
[TABLE="width: 693"]
[TR]
[TD]Mã hàng
[/TD]
[TD]Tên hàng
[/TD]
[TD]ĐV tính
[/TD]
[TD]Đơn giá
[/TD]
[/TR]
[TR]
[TD]A01
[/TD]
[TD]HDD
[/TD]
[TD]Cái
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]B02
[/TD]
[TD]USB
[/TD]
[TD]Cái
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]C01
[/TD]
[TD]Chuột
[/TD]
[TD]Cái
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]D03
[/TD]
[TD]Phím
[/TD]
[TD]Cái
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]E01
[/TD]
[TD]Monitor
[/TD]
[TD]Cái
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]E02
[/TD]
[TD]FDD
[/TD]
[TD]Hộp
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]E03
[/TD]
[TD]RAM
[/TD]
[TD]Thanh
[/TD]
[TD]
[/TD]
[/TR]
[/TABLE]
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
8 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 điểm thi: 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(ô ghi điểm thi,$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 .
Khi có thay đổi về thang điểm xếp loại (mốc điểm xếp loại) thì bạn chỉ cần thay đổi trong bảng tham chiếu mà không cần đá động gì đến công thức cả. (Khỏi mất công sửa công thức xếp loại cho tất cả HS)
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:
[TABLE="width: 483"]
[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]CAN
[/TD]
[TD]Canh
[/TD]
[TD]Tân
[/TD]
[TD]Nhâm
[/TD]
[TD]Quí
[/TD]
[TD]Giáp
[/TD]
[TD]Ất
[/TD]
[TD]Bính
[/TD]
[TD]Đinh
[/TD]
[TD]Mậu
[/TD]
[TD]Kỷ
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]CHI
[/TD]
[TD]Thân
[/TD]
[TD]Dậu
[/TD]
[TD]Tuất
[/TD]
[TD]Hợi
[/TD]
[TD]Tý
[/TD]
[TD]Sửu
[/TD]
[TD]Dần
[/TD]
[TD]Mão
[/TD]
[TD]Thìn
[/TD]
[TD]Tỵ
[/TD]
[TD]Ngọ
[/TD]
[TD]Mùi
[/TD]
[/TR]
[/TABLE]
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 CBCNV để điền hệ số lương và tính lương cho từng người một cách nhanh chóng.
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.
Lần chỉnh sửa cuối:
