Hàm CROSSTAB Ver 1.00 đây
CSDL giống như trên đã nêu:
[Ma], [Ho], [Ten], [NgayCT], [Nu], [MaDV]
. (A) . . . . [C] . . [D] . . [E] . . [F] - là các cột ương ứng
Nhiệm vụ đề ra là viết hàm thống kê số năm tham gia công tác trung bình của từng đơn vị theo tổng & theo từng đối tượng giới tính
Trong VD cơ quan chỉ 7 đơn vị; khoảng 333 người;
Ta chọn vùng trống nào đó đủ 9 * 7 = 63 ô và nhập hàm sau:
=CROSSTABttb( D2: F399) & kết thúc = tổ hợp 3 fím ta sẽ có kết quả tại cột đầu sẽ là các mã ĐVị; Cột tiếp là số lượng nhân viên trong đơn vị tương ứng; cột 3 sẽ là trung bình số năm công tác của từng đơn vị ; cột 4 & 5 thống kê cho giới nữ & C6 & C7 là của Nam
Còn dòng 9 cuối cùng thể hiện các tổng tương ứng
Hàm có nội dung như sau:
Option Explicit: Option Base 1
Function CROSSTABttb(CSDL As Object) As Variant
Dim MSLieu(9, 7) As Variant
Dim Zz As Integer, jJ As Integer: Dim SDV As String
Dim TTuoi As Double, TNu As Double, TNam As Double, TTrB As Double, TTrB0 As Double, TTrB_1 As Double
Dim Tong As Long, TgNu As Long
For Zz = 1 To 999
If Len(CSDL.Cells(Zz, 3)) < 1 Then Exit For
If CSDL.Cells(Zz, 3) <> SDV Then
SDV = CSDL.Cells(Zz, 3): jJ = jJ + 1
If Zz > 1 Then
MSLieu(jJ - 1, 3) = TTuoi / MSLieu(jJ - 1, 2): TTuoi = 0
MSLieu(jJ - 1, 5) = TNu / MSLieu(jJ - 1, 4): MSLieu(jJ - 1, 7) = TNam / MSLieu(jJ - 1, 6)
Tong = Tong + MSLieu(jJ - 1, 2): TgNu = TgNu + MSLieu(jJ - 1, 4)
TTrB = TTrB + MSLieu(jJ - 1, 3): TTrB0 = TTrB0 + MSLieu(jJ - 1, 7)
TTrB_1 = TTrB_1 + MSLieu(jJ - 1, 5)
End If
MSLieu(jJ, 1) = SDV: MSLieu(jJ, 2) = 1
TNu = 0: TNam = 0
TTuoi = (Date - CSDL.Cells(Zz, 1)) / 365.25
If CSDL.Cells(Zz, 2) = 0 Then
MSLieu(jJ, 6) = 1: TNam = (Date - CSDL.Cells(Zz, 1)) / 365.25
Else
MSLieu(jJ, 4) = 1: TNu = (Date - CSDL.Cells(Zz, 1)) / 365.25
End If
Else
MSLieu(jJ, 2) = 1 + MSLieu(jJ, 2)
TTuoi = TTuoi + (Date - CSDL.Cells(Zz, 1)) / 365.25
If CSDL.Cells(Zz, 2) = 0 Then
MSLieu(jJ, 6) = MSLieu(jJ, 6) + 1: TNam = TNam + (Date - CSDL.Cells(Zz, 1)) / 365.25
ElseIf CSDL.Cells(Zz, 2) = 1 Then
MSLieu(jJ, 4) = MSLieu(jJ, 4) + 1: TNu = TNu + (Date - CSDL.Cells(Zz, 1)) / 365.25
End If
End If
Next Zz
MSLieu(jJ, 3) = TTuoi / MSLieu(jJ, 2)
MSLieu(jJ, 5) = TNu / MSLieu(jJ, 4): MSLieu(jJ, 7) = TNam / MSLieu(jJ, 6)
MSLieu(9, 1) = jJ: MSLieu(9, 2) = Tong + MSLieu(jJ, 2)
MSLieu(9, 4) = TgNu + MSLieu(jJ, 4): MSLieu(9, 6) = MSLieu(9, 2) - MSLieu(9, 4)
MSLieu(9, 3) = (TTrB + MSLieu(jJ, 3)) / jJ: MSLieu(9, 7) = (TTrB0 + MSLieu(jJ, 7)) / jJ
MSLieu(9, 5) = (TTrB_1 + MSLieu(jJ, 5)) / jJ
CROSSTABttb = MSLieu
End Function
