• Thứ Ba, 05/09/2006 07:16 (GMT+7)

    Góp ý giải đáp thắc mắc

    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

    ID: A0608_142