Trong TGVT A số 7/2006 có câu hỏi về hàm Excel đếm theo nhiều điều kiện... Ngoài cách dùng hàm User-defined trong VBA còn có thể dùng hàm SUMPRODUCT, vốn là hàm nhân ma trận.
1. Hàm đếm theo nhiều điều kiện
Ví dụ cách dùng hàm này như sau:
| A | B | C | D | E | F | G | H | I | |||||||||||||||||||||
| 1 | Ten | Tuoi | Gioitinh | Dantoc | D1 | D2 | D3 | DTB | Hoc bong | ||||||||||||||||||||
| 2 | Ng V A | 12 | Nam | Kinh | 5 | 5 | 6 | 5.3 | 150000 | ||||||||||||||||||||
| 3 | Tr T B | 32 | Nu | KHo | 6 | 6 | 8 | 6.7 | 130000 | ||||||||||||||||||||
| 4 | H T C | 26 | Nu | Kinh | 4 | 4 | 4 | 4.0 | 150000 | ||||||||||||||||||||
| 5 | Bui V Q | 54 | Nam | KHo | 8 | 8 | 8 | 8.0 | 200000 | ||||||||||||||||||||
| 6 | Tr T B | 2 | Nu | Man | 9 | 9 | 9 | 9.0 | 300000 | ||||||||||||||||||||
| 7 | H T C | 23 | Nu | Muong | 5 | 7 | 5 | 5.7 | 260000 | ||||||||||||||||||||
| 8 | Bui V Q | 62 | Nam | Dao | 4 | 4 | 4 | 4.0 | 150000 | ||||||||||||||||||||
| 9 | Tr T B | 52 | Nu | Dao | 8 | 8 | 8 | 8.0 | 200000 | ||||||||||||||||||||
| 10 | H T C | 16 | Nu | Muong | 7 | 7 | 7 | 7.0 | 300000 | ||||||||||||||||||||
| 11 | Bui V Q | 15 | Nam | Man | 9 | 9 | 9 | 9.0 | 260000 | ||||||||||||||||||||
| 12 | Tr T B | 14 | Nu | Kinh | 6 | 6 | 6 | 6.0 | 150000 | ||||||||||||||||||||
| 13 | H T C | 16 | Nu | KHo | 3 | 9 | 6 | 6.0 | 200000 | ||||||||||||||||||||
| 14 | Tr T B | 18 | Nu | Kinh | 6 | 6 | 5 | 5.7 | 300000 | ||||||||||||||||||||
| 15 | H T C | 20 | Nu | KHo | 8 | 5 | 3 | 5.3 | 260000 | ||||||||||||||||||||
| 16 | Tr T B | 22 | Nu | Kinh | 7 | 4 | 7 | 6.0 | 260000 |
Hàm SUMPRODUCT sẽ đếm số học sinh tuổi >14, dân tộc Kinh, DTB > 4, D1>6, giới tính là nữ tương tự như hàm COUNTIF với nhiều điều kiện:
=SUMPRODUCT(($B$2:$B$16>14)*($D$2:$D$16="kinh")*($H$2:$H$16>4)*(E2:E16>6)*($C$2:$C$16="NU"))
Nếu kết hợp với cách đặt tên cho các dãy, ví dụ Ten = $A$1:$A$16, Gioitinh = $C$1:$C$16 và các biến số nằm ở các ô, ta sẽ có công thức rất linh động dạng:
=SUMPRODUCT((Tuoi>A18)*( Dantoc=B18)*(DTB>4)*(D1>6)*(Gioitinh="NU"))
Ưu điểm: Số điều kiện có thể lên tới 30, tốc độ nhanh do sử dụng hàm có sẵn của Excel.
Tương tự, hàm SUMPRODUCT có thể dùng như hàm SUMIF với nhiều điều kiện. Công thức sau sẽ tính tổng số học bổng các học sinh thỏa mãn các điều kiện trên:
=SUMPRODUCT((Tuoi>A18)*( Dantoc=B18)*(DTB>4)*(D1>6)*(Gioitinh="NU")*Hocbong)
Lưu ý đặc biệt khi sử dụng là kích thước của tất cả các dãy phải bằng nhau (nhất là khi dùng bằng cách đặt tên), nếu không hàm sẽ trả về giá trị #VALUE!
2. Giấu công thức-chống sửa chữa
Chọn toàn bộ các ô trong Worksheet bằng cách nhấn Ctrl+A. Chọn menu Format > Cell > Protection, bỏ dấu chọn ở Locked và Hidden, OK.
|
|
Chọn ô bất kỳ, chọn menu Edit > Goto, chọn Special bạn sẽ thấy hộp thoại
|
|
Chọn Formulas, nếu cần hạn chế lựa chọn với các loại bên dưới, OK.
Bây giờ chỉ có các ô chứa công thức được chọn.
Chọn menu Format > Cell > Protection, đánh dấu chọn ở Locked và Hidden, OK.
|
|
Khóa worksheet để lựa chọn này có hiệu lực.
Chọn Tools > Protection > Protect Worksheet để khóa worksheet, đặt mật khẩu nếu cần.
3. Tạo nút giấu /hiện một worksheet
Bật thanh công cụ Forms qua menu View > Toolbars > Forms
Ví dụ ta có Sheet1 là giao diện chứa nút bấm và Sheet3 là bảng cần ẩn/ hiện
|
|
Tạo nút bấm Button1, nhấn chuột phải vào nút bấm và gán Macro Button1_click, nhấn Edit chuyển sang cửa sổ VBE. Gõ đoạn mã sau:
Sub Button1_Click()
With Sheets("Sheet3")
.Visible = Not (Sheets("Sheet3").Visible)
End With
End Sub
Nhấn nút Save, quay lại Excel thử nút Button1.
Nguyen Trong Thanh
Email: ng.tr.thanh@gmail.com