Saga - Tạo hàm chấm công bằng Excel

Ở các công ty, doanh nghiệp vừa và nhỏ việc chấm công thông thường được thực hiện trên Excel. Qua tìm hiểu tôi thấy rằng đa số dùng phương pháp chấm công như sau: có đi làm thì sẽ được chấm là X, nghỉ phép là P..., sau đó dùng một số hàm trong Excel để tính số ngày công trong một tháng của nhân viên. Thường số ngày làm việc luôn nhiều hơn số ngày nghỉ, nếu chấm công như trên thì bạn phải nhập vào bảng tính Excel nhiều hơn so với chỉ nhập vào những ngày nghỉ của nhân viên. Ngoài ra còn có những trường hợp đặc biệt như nghỉ phép ½ ngày, tăng ca... hoặc tính công nhật (ở một số công ty có cả công nhân thời vụ và công nhân chính thức), tính theo năng suất...

Vâng, vấn đề thật rắc rối! Ở đây tôi không có tham vọng giải quyết tất cả các vấn đề trên mà chỉ muốn giới thiệu một hàm chấm công có thể giúp cho công việc này dễ dàng hơn.
Cấu trúc của hàm chấm công như sau:

Public Function chamcong(ByVal Khoang As Range, ByVal Chucnang As String) As Single
Khoang: là khoảng (thông thường là một hàng, từ cột... đến cột...) tương ứng với hàng hay khoảng mà bạn chấm công cho một nhân viên.
Chucnang: là chuỗi hay ký tự đại diện cho một chức năng chấm công của bạn.

Giả sử ở đây tôi quy định:

• Nghỉ phép là: A
• Nghỉ bệnh là: S
• Nghỉ ma chay là: C
• Nghỉ không lương là: U
• Tăng ca ngày thường (tính hệ số nhân 1,5) là: N • Tăng ca ngày nghỉ (tính hệ số nhân 2) là: D
Vậy nếu trong ngày một nhân viên nghỉ phép ½ ngày, tăng ca (ngày thường) 3 tiếng thì chuỗi chấm công như sau: “A0.5;N3”. Ở đây dùng dấu chấm phẩy (“;”) để phân cách, nếu muốn bạn cũng có thể thay đổi thứ tự trên.
Trong hàm chấm công, tôi có sử dụng Class “clsString” của tác giả Lê Đức Hồng. Trong cửa sổ Visual Basic Editor, bạn chọn Insert->Class Module, nhập vào class “clsString” như sau:

‘Author: Le Duc Hong
‘http://www.vovisoft.com
Option Explicit
Private SText As String
Private SDelimiter As String
Private IPos As Integer
Private ILen As Integer
Public MaxToken As Integer
Private Tokens() As String
Public Property Get Text() As Variant
   Text = SText
End Property
Public Property Let Text(ByVal vNewValue As Variant)
   SText = vNewValue
   ILen = Len(SText): IPos = 1
End Property
Public Property Get Delimiter() As Variant
   Delimiter = SDelimiter
End Property
Public Function TokenAt(TNum) As String
   If (TNum > 0) And (TNum <= MaxToken) Then
      TokenAt = Tokens(TNum)
   Else
      TokenAt = “”
   End If
End Function
Public Property Let Delimiter(ByVal vNewValue As Variant)
   SDelimiter = vNewValue
   Tokenise
End Property
Private Sub Tokenise()
   Dim i
   i = 0: IPos = 1
   Do Until IPos > ILen
      i = i + 1
      ReDim Preserve Tokens(i)
      Tokens(i) = GetToken
   Loop
   MaxToken = i
   IPos = 1
End Sub
Public Sub ReplaceToken(TNum, NewToken)
   If (TNum > 0) And (TNum <= MaxToken) Then
      Tokens(TNum) = NewToken
      ReconstructText
   End If
End Sub
Private Sub ReconstructText()
   Dim i
   SText = “”
   For i = 1 To MaxToken
      SText = SText & Tokens(i)
      If i < MaxToken Then SText = SText & SDelimiter
   Next
End Sub
Public Function KeepLeftPart(NumChar) As String
   If ILen >= NumChar Then
      SText = Left(SText, NumChar): ILen = Len(SText)
   End If
   KeepLeftPart = SText
End Function
Public Function KeepRightPart(NumChar) As String
   If ILen >= NumChar Then
      SText = Right(SText, NumChar): ILen = Len(SText)
   End If
   KeepRightPart = SText
End Function
Public Function KeepMidPart(SPos, NumChar) As String
   If ILen >= SPos Then
      SText = Mid(SText, SPos): ILen = Len(SText)
   End If
   If ILen >= NumChar Then
      SText = Right(SText, NumChar)
   End If
   KeepMidPart = SText
End Function
Public Property Get CurrentPos() As Variant
   CurrentPos = IPos
End Property
Public Property Let CurrentPos(ByVal vNewValue As Variant)
   IPos = vNewValue
End Property
Public Function GetToken() As String
   Dim Pos
   GetToken = “”
   If SDelimiter = “ “ Then
      Do While Mid(SText, IPos, 1) = “ “
         IPos = IPos + 1
         If IPos > ILen Then
            Exit Function
         End If
      Loop
   End If
   Pos = InStr(IPos, SText, SDelimiter)
   If Pos > 0 Then
      GetToken = Mid(SText, IPos, Pos - IPos)
      IPos = Pos + Len(SDelimiter)
   Else
      GetToken = Mid(SText, IPos, ILen - IPos + 1)
      IPos = ILen + 1
   End If
End Function
Public Sub Substitude(Param, ParamValue)
   Dim Pos, PLen
   PLen = Len(Param)
   Pos = InStr(SText, Param)
   Do While Pos > 0
      SText = Left(SText, Pos - 1) & ParamValue & Mid(SText, Pos + PLen)
      Pos = InStr(SText, Param)
   Loop
   ILen = Len(SText)
End Sub
Public Function GetLastToken() As String
   Dim Pos, Tlen
   Tlen = Len(SDelimiter)
   GetLastToken = “”
   If ILen = 0 Then
      Exit Function
   End If
   Pos = ILen - Tlen + 1
   Do While Pos > 0
      If Mid(SText, Pos, Tlen) = SDelimiter Then
         GetLastToken = Mid(SText, Pos + Tlen)
      End If
      Pos = Pos - 1
   Loop
End Function
Public Property Get Length() As Integer
   Length = ILen
End Property
Public Property Get TokenCount() As Variant
   TokenCount = MaxToken
End Property

Sau đó bạn “Insert” module và nhập đoạn mã hàm chấm công sau vào.

Public Function chamcong(ByVal Khoang As Range, ByVal Chucnang As String) As Single
Dim Socot As Integer, Sohang As Integer
Dim i As Integer, j As Integer, k As Integer
Dim Btotal As Single
Dim Bgiatriso As Single
Dim Bchucnang As String
Dim SoLoai As Byte ‘ Bien nay nham xac dinh so loai ngay nghi, tang ca... trong mot chuoi
Dim BChuoi As clsString
Dim BGiatri
On error resume next
‘Xac dinh so cot trong bien Khoang
Socot = Khoang.Columns.Count
‘Xac dinh so hang trong bien Khoang
Sohang = Khoang.Rows.Count
‘ Nham bao dam so sanh dung ta dung ham UCase
Chucnang = UCase(Chucnang)
‘Duyet qua cac cell trong bien Khoang
For i = 1 To Sohang
    For j = 1 To Socot
        BGiatri = Khoang.Cells(i, j).Value
        BGiatri = Trim(BGiatri)
        ‘ Bat dau xu ly bgiatri qua Class clsString
        Set BChuoi = New clsString
        BChuoi.Text = BGiatri
        ‘Ky tu de phan cach cac Chuc nang
        BChuoi.Delimiter = “;”
        ‘Xac dinh so Chuc nang trong 1 cell
        SoLoai = BChuoi.TokenCount
        For k = 1 To SoLoai
            ‘Chuoi cua tung chuc nang
            BGiatri = BChuoi.TokenAt(k)
            Bchucnang = UCase(Left(BGiatri, Len(Chucnang)))
            Bgiatriso = Val(Right(BGiatri, Len(BGiatri) - len(Chucnang)))
            Select Case Bchucnang
                Case Chucnang
                    Btotal = Btotal + Bgiatriso
            End Select
        Next k
    Next j
Next i
Chamcong = Btotal
End Function

Với hàm chấm công như trên, bạn có thể dùng các chuỗi ký tự dài hơn để thể hiện chức năng chấm công (ví dụ như: “nghiphep”, “nghibenh”, “tangca” ...), hay trong công thức bạn có thể tham chiếu đến một cell nào đó đại diện cho chức năng chấm công.

Ví dụ: ngày 22/02 tôi chấm công “nghiphep1”, ngày 23/02 tôi chấm công “nghiphep0.5;tangca3”.

Trong ô AM8, tôi sẽ nhập vào công thức:

=chamcong(G8:AH8,$AM$7) (ô AM7 chứa chữ “nghiphep”)

Trong ô AN8, tôi nhập công thức sau:

=chamcong(G8:AH8,$AN$7) (ô AN7 chứa chữ “tangca”)

Dĩ nhiên sử dụng các chuỗi ký tự như trên thì dễ hiểu, nhưng bảng chấm công sẽ rất lượm thuộm. Bảng tính minh họa cho ví dụ trên. Hy vọng rằng bài viết trên giúp ích các bạn phần nào trong công việc chấm công nhàm chán nhưng... không được sai!