Excel VBA: aplicație de calcul și analiză a zilelor lucrate și a orelor disponibile lunar

Deoarece în ultima perioadă m-am ocupat din ce în ce mai mult de automatizări în Excel și de crearea unor mici aplicații (metode) care să ușureze munca, am decis astăzi să postez un mic exemplu de cum poate fi utilizat VBA-ul în acest scop.

 

Care a fost problema ce trebuia rezolvată ?

O companie ține o evidență a angajațiilor într-un fișier Excel cu următoarele informații: 

• Nume angajat
• Departament, status și tip
• Data intrare: data la care a venit în firmă 
• Dată ieșire: data la care a plecat din firma. (dacă este angajat curent se lasă necompletat)
• Procent alocare: cât la sută din timp lucrează pt compania analizată (de ex. Daca un angajat lucrează 70% din timp pe această firma și restul pe o altă firmă din același grup, se completeaza 70%)

Toate datele de mai sus se găsesc în sheet-ul “Baza de date”. 

Zilele de concediu luate de fiecare angajat sunt în Sheet-ul “Concedii”.

*găsiți la finalul articolului și un link de download al fișierului

Andrei, ne poti ajuta cu un raport pentru primul semestru al anului 2014 din care să reiasă Full Time Equivalent și numărul total de ore disponibile pe fiecare lună și departament ?

Full Time Equivalent înseamnă câte zile din lună a lucrat efectiv fiecare persoană raportat la numărul de zile lucrătoare total disponibile din lună. Numărul de zile lucrat efectiv de fiecare persoană trebuie calculat ținând cont de zilele de concedii și de data la care angajatul a venit/plecat din firmă pentru cazurile celor veniți sau plecați în cursul lunilor, iar numărul total de zile din lună trebuie calculat ținând cont și de zilele libere naționale.

Pentru a înțelege mai bine cerința, haideți să luăm două exemple:

• Exemplul 1: Ion a fost angajat pe data de 13 Ianurie 2014, iar zilele 1 și 2 Ianuarie au fost zile libere naționale. În acest caz nr de zile lucrate de Ion au fost cele din intervalul 13-31 Ianuarie (nu a prins si zilele libere naționale), deci 15 zile, iar zilele totale lucrătoare disponibile din luna au fost  23 (total zile lună fără weekend-uri) – 2 (libera naționale) = 21. FTE pentru Ion în luna Ianuarie ar fi 15/21= 0.71.

• Exemplul 2: Vasile a plecat din firmă pe 25 Aprilie 2014 (ultima lui zi a fost 25), iar zilele 20 și 21 Aprilie au fost zile libere naționale (Paștele), dar 20 Aprilie pică în weekend deci nu trebuie scăzută. De asemenea, Vasile a avut și 4 zile de concediu în intervalul 1-4 Aprillie. În acest caz, zilele lucrate de Ion au fost cele din intervalul 7- 25 Aprilie, mai puțin ziua de 21 Aprilie, deci a lucrat în total 14 zile, iar zilele totale lucrătoare din lună au fost 22 (total zile din lună fără weekend-uri) – 1 (ziua liberă națională care pică în cursul săptămânii)= 21. FTE pentru Vasile în luna Aprilie va fi 14/21= 0.67.

Metode de rezolvare a problemei:

1. Prima metodă estecea manuală, adică să luăm la mână fiecare persoană din listă și să calculăm zilele lucrate efectiv și zilele totale din lună. Dar dacă avem 100 de angajați și 12 luni, cam cât timp ne ia să facem raportul, 3 zile ? Și dacă peste încă șase luni ni se cere încă o dată raportul actualizat la zi ? Ce facem, o luăm de la capăt cu munca de sisif ? Având în vedere aceste inconveniente, haideți să ne gândim la a doua metodă..
2. A doua metodă: scriem un Macro care să facă automat toată această muncă la apăsarea unui buton, ori de câte ori vom avea nevoie, iar noi putem pleca liniștiți în concediu în loc să ne apucăm de calculat manual datele pentru 100 de persoane... Sau ne apucăm de alte taskuri în timpul economisit.. dacă deja am fost în concediu.

Ce a ieșit ?

După câteva ore bune de lucru și mai multe modificări (din cauză că nu mă gândisem de la început la toate cazurile și problemele posibile și din cauză că încă sunt începător în lucrul cu VBA), am reușit să scriu codul care acum rezolvă corect această problemă în doar câteva secunde, prin apăsarea unui buton, pentru orice interval de luni din anul dorit de utilizator.

DESCARCĂ FIȘIER; Puteți descărca fișierul apăsând AICI.

* fișierul conține Macrocomenzi. Pentru a activa continutul apăsati "Enable Content"
* datele din fişier sunt fictive și au rol exemplificativ. Pot fi înlocuite cu date reale pentru utilizarea aplicației.

De asemenea, codul care face toate calculele în locul nostru este acesta:

Sub take1()

'define variables
Dim anraport  As Long, lunastart As Long, lunaend As Long, i As Long, _
j As Long, r As Long, concediilegale1 As Long, concediilegale2 As Long
Dim isweekend As Boolean

Set Oldselection = Selection

'clear contents of Sheet1 columns A and H
Range("A2:H2").Select
Range(Selection, Selection.End(xlDown)).ClearContents

'take criterias input by user in the UserForm1 in order to use them later
anraport = UserForm1.TextBox1.Value
lunastart = UserForm1.TextBox2.Value
lunaend = UserForm1.TextBox3.Value

Unload UserForm1

Sheet4.Range("L2") = anraport

'Loop through the data base and then write values in Sheet1 based on _
selected criterias
r = 2

For j = 3 To Sheet2.Range("A3").End(xlDown).Row

    For i = lunastart To lunaend
        
dataintrare = Sheet2.Cells(j, 5)
dataiesire = Sheet2.Cells(j, 6)

    If Year(dataintrare) > anraport Then 'do nothing

    ElseIf Year(dataintrare) = anraport And Month(dataintrare) > i Then _
    'do nothing
            
    ElseIf Year(dataiesire) = anraport And Month(dataiesire) < i _
    And dataiesire <> "" Then 'do nothing
            
    ElseIf Year(dataiesire) < anraport And dataiesire <> "" Then _
    'do nothing
            
    Else
            
        Sheet4.Cells(r, 2) = i
        Sheet4.Cells(r, 1) = Sheet2.Cells(j, 1)
        
'calculate national holidays
        concediilegale1 = 0
        concediilegale2 = 0
        For a = 2 To Sheet5.Range("A2").End(xlDown).Row
        
        If i = Month(Sheet5.Cells(a, 1)) And Year(Sheet5.Cells(a, 1)) _
        = anraport Then
        
'check if the national holiday falls in weekend or not
                Select Case Weekday(Sheet5.Cells(a, 1))
                Case vbSaturday, vbSunday
                isweekend = True
                Case Else
                isweekend = False
                End Select
'national holidays for every user based on the date he joined the company
                If dataintrare > Sheet5.Cells(a, 1) Or isweekend = True Then
                concediilegale1 = 0
                Else: concediilegale1 = concediilegale1 + 1
                End If
        'national holidays for each month's working days
                If isweekend = True Then
                concediilegale2 = 0
                Else: concediilegale2 = concediilegale2 + 1
                End If
             
        End If
        Next a

'calculate Networkdays for every user based on the days the users came/left _
the company and on the selected criteria
        If Month(dataintrare) = i And Year(dataintrare) = anraport _
        And Month(dataiesire) <> i Then
            Sheet4.Cells(r, 3) = Application.WorksheetFunction.NetworkDays _
            (dataintrare, DateSerial(Year(dataintrare), Month(dataintrare) + 1, 0)) _
            - concediilegale1
        ElseIf Month(dataiesire) = i And dataiesire <> "" _
        And Year(dataiesire) = anraport Then
            Sheet4.Cells(r, 3) = Application.WorksheetFunction.NetworkDays _
            (DateSerial(Year(dataiesire), Month(dataiesire), 1), dataiesire) _
            - concediilegale1
        Else: Sheet4.Cells(r, 3) = Application.WorksheetFunction.NetworkDays _
        (DateSerial(anraport, i, 1), DateSerial(anraport, i + 1, 0)) _
        - concediilegale1
        End If
        
'Calculate networkdays for every months and then substract the national holidays which _
are set in Sheet "Sarbatori legale"
        Sheet4.Cells(r, 4) = Application.WorksheetFunction.NetworkDays _
        (DateSerial(anraport, i, 1), DateSerial(anraport, i + 1, 0)) _
        - concediilegale2
        
'Calculate the next columns:Holidays, Networkdays minus Holidays and Full Time Equivalent
Sheet4.Cells(r, 5).FormulaR1C1 = _
"=SUMIFS(Concedii!C[4],Concedii!C[-2],Sheet1!RC[-4],Concedii!C[-3],Sheet1!RC[-3],Concedii!C[-4],Sheet1!R2C12)"
Sheet4.Cells(r, 6).FormulaR1C1 = "=RC[-3]-RC[-1]"
Sheet4.Cells(r, 7).FormulaR1C1 = _
"=RC[-1]/RC[-3]*SUMIFS('Baza de date'!C,'Baza de date'!C[-6],Sheet1!RC[-6],'Baza de date'!C[-5],Sheet1!RC[1])"
       
       Sheet4.Cells(r, 8) = Sheet2.Cells(j, 2)
       
       Columns("E:G").NumberFormat = "0.00"
       
       r = r + 1
        
    End If
    
    Next i
    
Next j
        
Oldselection.Activate
    
End Sub

Cu siguranță există și alte metode de rezolvare a problemei. De aceea, dacă aveți idei, lasați un comentariu :)

Subscribe to Un mod diferit de a privi Economia by Email