Extragere ore suplimentare

Informatii despre cum se utilizeaza Microsoft Excel 2003. Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
iang
Mesaje: 46
Membru din: Joi Oct 27, 2011 6:23 pm

Extragere ore suplimentare

Mesaj de iang » Vin Aug 19, 2016 3:22 pm

Bună ziua!
Aş dori să extrag din tabel orele suplimentare pentru zilele de sâmbăta si duminica si cele din cursul săptămânii (acestea doar care depăşesc 8 ore) pentru fiecare persoană.
De reţinut că ordinea zilelor se modifică de la o lună la alta.
Am încercat eu ceva....dar când se schimbă luna apar probleme.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

csaba1960
Moderator
Moderator
Mesaje: 160
Membru din: Mie Feb 02, 2011 4:05 pm
Localitate: Cluj-Napoca

Re: Extragere ore suplimentare

Mesaj de csaba1960 » Vin Aug 19, 2016 6:00 pm

Buna

In atașament ai o soluție, conține macrou.
Am modificat putin fișierul am introdus anul si luna.
La schimbarea lunii, o rutina actualizează automat zilele săptămânii.

Cod: Selectaţi tot

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
If Target.Address = "$B$3" Then
    For i = 3 To 33
    Cells(4, i) = WeekdayName(Weekday(DateSerial(Cells(2, 2), Cells(3, 2), Cells(3, i))), True, 1)
    Next
End If
End Sub
La apăsarea butonului se face calculul orelor suplimentare.

Cod: Selectaţi tot

Sub calculOreSupl()
Dim nRr As Integer, i As Integer, j As Integer
Dim oreS As Integer, oreW As Integer
nRr = a.Cells(a.Rows.Count, "B").End(xlUp).Row
For i = 5 To nRr
oreW = 0: oreS = 0
    For j = 3 To 33
    If IsNumeric(Cells(i, j)) = False Then GoTo et1
     If Cells(i, j) <= 8 Then GoTo et1
        If Cells(4, j) = "D" Or Cells(4, j) = "S" Then
        oreW = oreW + Cells(i, j)
        Else: oreS = oreS + Cells(i, j) - 8
        End If
et1:
    Next
Cells(i, 34) = oreS: Cells(i, 35) = oreW:
Next
End Sub
Eu am setările pentru limba romana, daca ai pentru alta limba, trebuie sa modifici rândul If Cells(4, j) = "D" Or Cells(4, j) = "S" Then.
Pentru weekend orice ora lucrata am considerat ora suplimentara.
In coloana B nu poate fi nimic sub tabel.

Realizat in Excel 2013, in principiu ar trebui sa meargă si in Excel 20003.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

iang
Mesaje: 46
Membru din: Joi Oct 27, 2011 6:23 pm

Re: Extragere ore suplimentare

Mesaj de iang » Vin Aug 19, 2016 7:09 pm

Mulţumesc frumos!
Tabelul lucrează foarte bine, nu m-am aşteptat ca rezolvarea să vină în macro......
Îmi cer mii de scuze că nu am specificat de la început..... eu căutam o formulă dacă există...... ca să o pot îngloba la tabelul care pe care îl am deja!

TudyBTH
Moderator
Moderator
Mesaje: 904
Membru din: Joi Feb 11, 2016 2:12 pm
Localitate: Cluj Napoca

Re: Extragere ore suplimentare

Mesaj de TudyBTH » Vin Aug 19, 2016 8:05 pm

Buna,
Ca alternativa la solutia prezentata de dl Csaba1960, pentru eventualitatea in care doriti o solitie fara macro.

Pentru orele suplimentare din cursul saptamanii o formula CSE (se introduce cu tastele Ctrl+Shift+Enter, nu doar Enter)

Cod: Selectaţi tot

=SUMPRODUCT(IF(ISNUMBER($C5:INDEX($C5:$AG5;;COUNT($C$3:$AG$3)));($C5:INDEX($C5:$AG5;;COUNT($C$3:$AG$3))-8);0)*(WEEKDAY($C$4:INDEX($C$4:$AG$4;COUNT($C$3:$AG$3));2)<6)*($C5:INDEX($C5:$AG5;;COUNT($C$3:$AG$3))>8))
Pentru orele din weekend , tot cu CSE

Cod: Selectaţi tot

=SUMPRODUCT(IF(ISNUMBER($C5:INDEX($C5:$AG5;;COUNT($C$3:$AG$3)));($C5:INDEX($C5:$AG5;;COUNT($C$3:$AG$3)));0)*(WEEKDAY($C$4:INDEX($C$4:$AG$4;COUNT($C$3:$AG$3));2)>5))
In fisierul atasat am introdus cateva formule care automatizeaza afisarea zilei din luna si a zilei din saptamana in functie de luna curenta (B2).

Tot in fisierul atasat, mai gasiti un set de formule care folosesc doua nume definite (ore si zile), utilein cazul in care doriti sa le folositi pe o alta structura a fisierului, fiind mai usor de implementat.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Am invatat sa inotam in apa, ca pestii
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.

iang
Mesaje: 46
Membru din: Joi Oct 27, 2011 6:23 pm

Re: Extragere ore suplimentare

Mesaj de iang » Vin Aug 19, 2016 9:54 pm

Super tare! ;) Genială ideia dv..... Mă chinui să descifrez formulele..... :roll: ......e suficient să schimb data si se aranjează totul.....perfect!
Am căutat la formatări condiţionate da nu am găsit nimic legat de colorarea zilelor de sâmbătă si duminică.
Acasă am office 2010 şi acum mă obişnuiesc cu el, la lucu am 2003.
Vă mulţumesc foarte frumos!

TudyBTH
Moderator
Moderator
Mesaje: 904
Membru din: Joi Feb 11, 2016 2:12 pm
Localitate: Cluj Napoca

Re: Extragere ore suplimentare

Mesaj de TudyBTH » Sâm Aug 20, 2016 12:13 am

Buna,

La Conditional Formatting am folosit formula

Cod: Selectaţi tot

=WEEKDAY(C$4;2)>5
Capture1.JPG
Versiunea 2003 nu accepta din cate stiu eu mai multe formatari conditionate pentru acelasi range, de aceea pentru zilele din luna 29-31 am folosit formula

Cod: Selectaţi tot

=IF(AD3="";"";IF(MONTH($B$2)=MONTH(AD4+1);AD3+1;""))
Astfel, pentru lunile mai scurte de 31 de zile va afisa null strig ("") in locul zilei din luna. Este important pentru ca asta se verifica in formulele de calcul pentru excluderea eventualelor valori introduse la aceste zile (nu le ia in considerare) si deasemenea pentru a ascunde si zilele din saptamana in aceasta zona.

Cod: Selectaţi tot

=IF(AE3="";"";DATE(YEAR($B$2);MONTH($B$2);AE$3))
cu formatarea celulelor C4:AG4 custom "ddd".
Pentru calculul orelor suplimentare am definit doua nume si am adaptat formulele pentru a fi mai usor de "mutat" in alte configuratii.
Primul nume "zile" este definit cu formula

Cod: Selectaţi tot

=OFFSET(Foaie1!$C$4;;;1;COUNT(Foaie1!$C$3:$AG$3))
A doua definitie se refra la orele inregistrate in linia in care se face calculul. De aceea foloseste o adresare reletiva pe linii si este necesar ca inainte de a defini numele sa fie selectata o celula din aceeasi linie cu cea la care se face referire (in acest caz row 5). Odata selectata celula se defineste numele "ore" cu formula

Cod: Selectaţi tot

=OFFSET(Foaie1!$C4;;;1;COUNT(Foaie1!$C$3:$AG$3))
Odata rezolvate acestea, formulele pentru ore suplimentare devin:
pentru ore supl. L-V

Cod: Selectaţi tot

=SUMPRODUCT(IF(ISNUMBER(ore);(ore-8);0)*(WEEKDAY(zile;2)<6)*(ore>8))
iar pentru weekend

Cod: Selectaţi tot

=SUMPRODUCT(IF(ISNUMBER(ore);(ore);0)*(WEEKDAY(zile;2)>5))
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Am invatat sa inotam in apa, ca pestii
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.

iang
Mesaje: 46
Membru din: Joi Oct 27, 2011 6:23 pm

Re: Extragere ore suplimentare

Mesaj de iang » Dum Aug 21, 2016 10:22 am

Mulţumesc foarte frumos şi pentru explicaţii. :D
Multă sănătate şi tot cei mai bun pentru dv. şi pentru cei dragi!

Scrie răspuns

Înapoi la “Intrebari despre Excel 2003”