• Thứ Sáu, 09/01/2004 14:12 (GMT+7)

    Tạo hàm chấm công trong Excel

    Đánh giá


    Ở các công ty, doanh nghiệp vừa và nhỏ việc chấm công thông thường được thực hiện trên Excel. Qua tìm hiểu tôi thấy rằng đa số dùng phương pháp chấm công như sau: có đi làm thì sẽ được chấm là X, nghỉ phép là P..., sau đó dùng một số hàm trong Excel để tính số ngày công trong một tháng của nhân viên. Thường số ngày làm việc luôn nhiều hơn số ngày nghỉ, nếu chấm công như trên thì bạn phải nhập vào bảng tính Excel nhiều hơn so với chỉ nhập vào những ngày nghỉ của nhân viên. Ngoài ra còn có những trường hợp đặc biệt như nghỉ phép ½ ngày, tăng ca... hoặc tính công nhật (ở một số công ty có cả công nhân thời vụ và công nhân chính thức), tính theo năng suất...

    Vâng, vấn đề thật rắc rối! Ở đây tôi không có tham vọng giải quyết tất cả các vấn đề trên mà chỉ muốn giới thiệu một hàm chấm công có thể giúp cho công việc này dễ dàng hơn.

    Cấu trúc của hàm chấm công như sau:

    Public Function chamcong(ByVal Khoang As Range, ByVal Chucnang As String) As Single

    Khoang: là khoảng (thông thường là một hàng, từ cột... đến cột...) tương ứng với hàng hay khoảng mà bạn chấm công cho một nhân viên.

    Chucnang: là chuỗi hay ký tự đại diện cho một chức năng chấm công của bạn.

    Giả sử ở đây tôi quy định:

    • Nghỉ phép là: A

    • Nghỉ bệnh là: S

    • Nghỉ ma chay là: C

    • Nghỉ không lương là: U

    • Tăng ca ngày thường (tính hệ số nhân          1,5) là: N

    • Tăng ca ngày nghỉ (tính hệ số nhân 2) là: D

    Vậy nếu trong ngày một nhân viên nghỉ phép ½ ngày, tăng ca (ngày thường) 3 tiếng thì chuỗi chấm công như sau: “A0.5;N3”. Ở đây dùng dấu chấm phẩy (“;”) để phân cách, nếu muốn bạn cũng có thể thay đổi thứ tự trên.

    Trong hàm chấm công, tôi có sử dụng Class “clsString” của tác giả Lê Đức Hồng ở website www.vovisoft.com. Trong cửa sổ Visual Basic Editor, bạn chọn Insert->Class Module, nhập và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 đó bạn “Insert” module và nhập đoạn mã hàm chấm công sau và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 hàm chấm công như trên, bạn có thể dùng các chuỗi ký tự dài hơn để thể hiện chức năng chấm công (ví dụ như: “nghiphep”, “nghibenh”, “tangca” ...), hay trong công thức bạn có thể tham chiếu đến một cell nào đó đại diện cho chức năng chấm công.

    Ví dụ: ngày 22/02 tôi chấm công “nghiphep1”, ngày 23/02 tôi chấm công “nghiphep0.5;tangca3”. Trong ô AM8, tôi sẽ nhập và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”)

    Dĩ nhiên sử dụng các chuỗi ký tự như trên thì dễ hiểu, nhưng bảng chấm công sẽ rất lượm thuộm.

    Bảng tính sau minh họa cho ví dụ trên.

    Hy vọng rằng bài viết trên giúp ích các bạn phần nào trong công việc chấm công nhàm chán nhưng... không được sai!

     

    levanduyet@pmail.vnn.vn.

     

    Đính Chính
    Trong bài “Điều khiển PC từ xa “ của tác giả Nguyễn Việt Hà đăng trên PC World VN A 11/2002, trang 107 có một số lỗi sau
    Đơn vị trị số F (microFara), nên nhiều bạn phản ánh làmcủa tụ C1 bị in sai thành 100 thiết bị không hoạt động. Xin sửa lại là 100nF (nanoFara; 1nF = 10-6F = 10m 6 pF).
    Mắt nhận hồng ngoại tiếng Anh gọi là “IR-detector” chứ không phải là “IR-decoder” như báo đã đăng.
    Thành thật xin cáo lỗi cùng tác giả và bạn đọc.

    ID: A0212_104
    Ý kiến của bạn? Ý kiến của bạn?
    Tin ngày :