Calcolo orario con Calcolo retribuzione netta: celle appoggio o formula?

Ultima modifica: 21-06-2016

 

In questo nuovo tutorial verranno discussi alcuni aspetti di alcune funzioni usate nel foglio di calcolo. Come esempio prendiamo il calcolo della retribuzione giornaliera netto da trattenute degli orari effettuati giornalmente.

Gli argomenti di questa pagina


La tabella in esame

Questa che segue è la tabella con cui intendiamo lavorare. Lo scopo da raggiungere è quello di ottenere, una volta indicati gli orari di entrata ed uscita, la retribuzione giornaliera netta delle ritenute, Come è mostrato nella tabella stessa.

E' possibile ottenere questo risultato in due maniere:

 

  A B C D
1 Retr Oraria € 25,00    
2 Ritenute % 33%    
3        
4 Data Entra Esce Retrib. Netta
5 01/10/2014 08:10 12:15 € 68,40
6 02/10/2014 21:00 08:15 € 188,44
7 03/10/2014 21:15 07:15 € 167,50
8 04/10/2014 07:15 14:15 € 117,25
9 05/10/2014 14:00 21:00 € 117,25
10 06/10/2014 21:00 07:00 € 167,50
11 07/10/2014 06:55 14:05 € 120,04
12 08/10/2014   14:15  
13 09/10/2014 06:55    
14 10/10/2014      
15 11/10/2014      

Prima di ottenere questo cerchiamo di esaminare le varie formule e funzioni che dobbiamo usare oltre al modo di formattare le celle.

 

Formattazione delle celle

Quando nelle celle del foglio di Excel si inseriscono delle date o delle ore (rispettando la giusta notazione) generalmente la cella prende la giusta formattazione. Se così non dovesse accadere si può procedere alla formattazione manuale delle celle.

Col tasto destro del mouse sulla cella o selezione di più celle, si sceglie la voce formato celle.

Si apre la finestra formato celle e, se non già selezionata, si sceglie la scheda Numero.

Per la cella o le celle che debbono contenere i numeri si sceglie formato numero a due decimali o, se si preferisce, formato valuta

Per la cella o le celle che debbono contenere le ore (entrata, uscita e calcolate) si sceglie il formato ora 13.30.

 

Differenza tra due orari in ore minuti

E' semplice calcolare la differenza tra due orari: è sufficiente una semplice sottrzione tra l'orario di uscita e quello di entrata:

=C5-B5 0,170139

Ma così facendo otteniamo un semplice numero decimale incomprensibile per i nostri scopi. Ma se applichiamo il giusto formato alla cella che ospita la nostra formula (Formato celle => Numero => Ora => 13:30) otteniamo il risultato che ci aspettavamo:

=C5-B5 4:05

Calcolo degli orari a cavallo della mezzanotte

A volte, lavorando con gli orari, potremmo imbatterci con orari che si trovano a cavallo della mezzanotte. In questo caso occorre aggiungere un correttivo come in questo caso:

IN OUT Diff. Formula
21:15 07:15 10:00 =C7-B7+(B7>C7)

In B7 c'è l'orario di partenza o di inizio.

In C7 c'è l'orario di arrivo, di fine, di chiusura o quel che si vuole.

Orbene l'espressione B7>C7

restituisce TRUE (1) se B7 è maggiore di C7 (siamo a cavallo della mezzanotte)

restituisce FALSE (0) se B7 è minore di C7 (START ed END sono tra le 00:00 e le 23:59 dello stesso giorno).

Questa espressione, quindi aggiungerà 1 o 0 all'espressione C7-B7, a seconda dei casi, aggiustando il calcolo finale.

Questo avviene perchè Excel, anche se ci mostra l'orario nel modo da noi formattato, tuttavia lo gestisce come numero decimale (numero seriale) compreso tra 0 e 0,999999. Il conteggio, naturalmente, ha inizio alla mezzanotte e termina un secondo prima della mezzanotte successiva.

Lo stesso esempio fatto con i valori formattati ad orario lo ripetiamo ora coi valori non formattati, quindi come semplici numeri decimali.

Dall'esempio che segue vediamo che eseguendo il semplice =C7-B7 si ottiene un -0,58........ . Nella formula sottostante =C7-B7+(B7>C7) viene aggiunta una unità e lo stesso decimale diventa 0,41....... che formattato ad orario diventa tranquillamente 10:00 che è il valore che ci aspettiamo.

IN OUT Diff. Formula
0,885417 0,302083 -0,583333 =C7-B7
    0,416667 =C7-B7+(B7>C7)
In tutti gli esempi che seguono, ove necessario verrà usata il correttivo per il calcolo dell'orario a cavallo della mezzanotte:
=C7-B7+(B7>C7)

 

Gli operatori AND e OR nella funzione SE

C'è da affrontare un altro problema. Se volessimo preparare il foglio con tutte le formule anche se la tabella è vuota, per evitare di vedere nella colonna delle formule dei fastidiosi € 0,00 o degli antiestetici #VALORE! o ########, è necessario ricorrere alla funzione SE. Con questa funzione, per esempio, possiamo verificare se una cella è valorizzata utilizzandola nel modo più semplice: =SE(B2="";VERO;FALSO). Ma questo non basta.

A noi serve verificare che entrambe le celle siano valorizzate. Per far questo possiamo usare la funzione SE anche in abbinamento con operatori logici AND e OR come è mostrato qui di seguito:

=SE(E(B12="";C12="");"";C12-B12)

=SE(O(B12="";C12="");"";C12-B12)

Ora dobbiamo decidere quale delle due forme usare.

Con la prima forma viene verificato che entrambe le celle siano vuote quindi il calcolo viene eseguito anche se una sola cella sia valorizzata perchè non viene verificato se entrambe le celle sono valorizzate.

Con la seconda forma viene verificato che sia una sola cella quella vuota, quindi il calcolo viene eseguito solo se entrambe le celle sono valorizzate.

Nella tabella di esempio qui sotto vengono mostrati i risultati che si ottengono con la prima e la seconda forma. E' ovvio che a noi interessa il secondo metodo che usa l'operatore logico OR.

  A B C D E F G
... ... ... ... ... ... ... ...
4 Data Entra Esce Ore fatte AND Ore fatte OR
5 01/10/2014 08:10 12:15 4:05 =SE(E(B5="";C5="");"";C5-B5+(B5>C5)) 4:05 =SE(O(B5="";C5="");"";C5-B5+(B5>C5))
6 02/10/2014 21:00 08:15 11:15   11:15  
7 03/10/2014 21:15 07:15 10:00   10:00  
8 04/10/2014 07:15 14:15 7:00   7:00  
9 05/10/2014 14:00 21:00 7:00   7:00  
10 06/10/2014 21:00 07:00 10:00   10:00  
11 07/10/2014 06:55 14:05 7:10   7:10  
12 08/10/2014   14:15 14:15      
13 09/10/2014 06:55   17:05      
14 10/10/2014            
15 11/10/2014            

 

Calcolo orari senza minuti con la funzione ORA()

Usando la formula già vista per calcolare la differenza tra due date è possibile estrarre la sola ora usando la funzione ORA() su quella formula.

La sintassi è semplice: ORA(num_seriale)

num_seriale può essere può essere rappresentato da stringhe di testo racchiuse tra virgolette, da numeri decimali, oppure da risultati di altre formule o funzioni, come quella che stiamo usando in questo tutorial: C5-B5+(B5>C5).

Questo che segue è un semplice esempio

=ORA(C5-B5+(B5>C5))
................................
................................
=ORA(C15-B15+(B15>C15))

Calcolo orari senza i minuti con la funzione TESTO()

E' possibile eseguire il calcolo per ottenere le sole ore senza i minuti. Da una tabella come quella in uso in questa pagina si potrebbero usare formule è funzioni più complesse, ma la funzione TESTO ci semplifica la formula.

La funzione TESTO converte un valore in testo assegnandogli un formato numerico specifico.

La sintassi è TESTO(val;formato)

Val è un valore numerico, una formula che calcola un valore numerico o un riferimento a una cella che contiene un valore numerico.

Formato è un formato numerico sotto forma di testo che si vede nella finestra di dialogo Formato celle => Numero nella casella Categoria.

Questo è quel che si può usare nel nostro esempio:

=TESTO(C5-B5+(B5>C5);"h")
................................
................................
=TESTO(C15-B15+(B15>C15);"h")

Bloccare il riferimento alle celle

Nelle nostre formule normalmente usiamo creare dei riferimenti alle celle. Questi riferimenti possono essere di tre tipi: relativo, assoluto o misto.

Per far questi cambiamenti di riferimento è possibile usare il tasto F4:

 

Calcolo retribuzione di una prestazione oraria

Anche questo tipo di calcolo si può eseguire in maniera molto semplice. Basta moltiplicare l'orario effettuato per la retribuzione oraria.

Può capitare che la cella che ospita la nuova formula mostri il risultato in un formato differente da quel che ci aspettiamo (normalmente in formato hh:mm). Se così fosse basta eseguire questo passaggio: Formato celle => Scheda Numero e scegliere o Numero con 2 decimali o Valuta per mettere le cose a posto.

Possiamo decidere di eseguire il calcolo su ORE:MINUTI oppure sulle sole ORE.

Primo caso: calcolo retribuzione su ORE:MINUTI

La formula è ancora semplice: si moltiplica il costo orario per le ore effettuate e si moltiplica ancora per 24.

=$B$1*(C5-B5+(B5>C5))*24

Per ottenere questo

Data Entra Esce Competenza Formula
01/10/2014 08:10 12:15 102,08 =$B$1*(C5-B5+(B5>C5))*24

 

Secondo caso: calcolo retribuzione sulle sole ore

E' possibile eseguire il calcolo della retribuzione anche sulle sole ore. Qui di seguito mostrerò due formule per calcolare le ore fatte con differenti funzioni Excel: la funzione ORA() e la funzione TESTO().

= ORA(C5-B5+(B5>C5)) che, nel nostro esempio, restituisce 4 usando la funzione ORA()

=TESTO(C5-B5+(B5>C5);"h") che, nel nostro esempio, restituisce 4 usando la funzione TESTO()

Questo è quel che si ottiene con le due funzioni:

Data Entra Esce Competenza con ORA Competenza con TESTO
01/10/2014 08:10 12:15 =$B$1*ORA(C5-B5+(B5>C5)) =$B$1*TESTO((C5-B5)+(B5>C5);"h")
01/10/2014 08:10 12:15 100 100

 

Calcolo percentuale

Anche il calcolo delle percentuali non presenta grosse difficoltà.

Nelle formule che si vedono nella tabella sottostante si possono notare tre particolari sintassi:

Anche in questi casi, se nella cella che ospita la formula non vediamo il valore atteso, è sufficiente applicare alla cella il formato celle => numero a 2 decimali.

  A B C D E F G
1 Retr Oraria 25                   
2 Ritenute % 33%          
3 Numero toglie % aggiunge % calcola %   verifica per toglie verifica per aggiunge
4 275,00 184,25 365,75 90,75   275,00 275,00
5   =A4*(1-B2) =A4*(1+B2) =A4*B2   =B4+D4 =C4-D4

 

Celle di appoggio o formula complessa?

Tutte queste istruzioni che abbiamo passato in rassegna serviranno per raggiungere il nostro obiettivo: mostrare il netto delle competenze per gli orari effettuati.

Possiamo scegliere due strade differenti:

Celle di appoggio

Questo è un esempio di come ottenere il calcolo per il netto delle competenze per gli orari effettuati usando le ore ed i minuti.

Se si desidera effettuare il calcolo usando le sole ore è sufficiente modificare le formule mostrate nelle colonne L - M.

In questo esempio prima di creare le istruzioni nella colonna D vengono eseguiti dei calcoli nelle colonne L - M - N.

In tutte le colonne si scrive la formula nella prima riga della tabella e poi, una volta verificata che la formula è esatta, invece di ripeterla in ogni riga, si può usare la tecnica del trascinamento verso il basso.

Ricordiamoci che, se nelle celle che contengono le formule, presentano valori in formato inatteso, è sufficiente applicare alla cella il formato celle => scheda numero.

Per la colonna L scegliamo ora => 13:30

Per le colonne M - N scegliamo numero => Posizioni decimali 2

Per la colonna D possiamo scegliere numero => Posizioni decimali 2 oppure valuta => Posizioni decimali 2, a seconda dei gusti o delle esigenze.

Per usare correttamente il trasferimento occorre prestare attenzione alle istruzioni delle colonne M - N.

=$B$1*L5*24

=M5*(1-$B$2)

Occorre congelare i riferimenti alle celle B1 e B2 aggiungendo i simboli del $ altrimenti col trascinamento questi riferimenti cambierebbero. L'operazione è agevolata se si selezionano questi riferimenti sulla barra delle funzioni e si preme il tasto F4.

Nella colonna L viene calcolato l'orario con la formula =C5-B5+(B5>C5) che tiene conto anche degli orari a cavallo della mezzanotte. Al suo posto può essere usata anche l'istruzione mostrata più in basso =ORA(C5-B5+(B5>C5)) che estrae le sole ore.

Nella colonna M viene calcolato l'importo lordo. Per il primo calcolo che tiene conto delle ore e dei minuti la formula sarà =$B$1*L5*24, ma per la formula che estrae le sole ore la formula sarà =$B$1*L5.

Nella colonna N viene calcolato l'importo netto dalle ritenute e viene usata la formula =M5*(1-$B$2). Questa formula si sarebbe potuta mettere già nella colonna D. Tuttavia è bene tenerla qui perchè potremmo trovarci nella situazione di incontrare righe vuote o incomplete per gli orari di entrata e di uscita, come è visibile dalla riga 12.

In questi casi c'è bisogno di un correttivo come mostrato nella colonna D: =SE(O(B5="";C5="");..................

Infine possiamo spostarci nella colonna D. In questa colonna Possiamo copiare la formula presente nella colonna N, ma aggiungiamo il correttivo =SE(O(B5="";C5="");................. per evitare fastidioso contenuto o addirittura messaggi di errori. Per questo nella colonna D scriviamo la formula completa di correttivo: =SE(O(B5="";C5="");"";N5).

  A B C D E F G H I J K L M N
1 Retr Oraria 25                        
2 Ritenute % 33%                        
3                            
4 Data Entra Esce Competenze               Orario Comp. Lorde Comp. Nette
5 01/10/2014 08:10 12:15 68,40               4:05 102,08 68,40
6 02/10/2014 21:00 08:15 188,44               11:15 281,25 188,44
7 03/10/2014 21:15 07:15 167,50               10:00 250,00 167,50
8 04/10/2014 07:15 14:15 117,25               7:00 175,00 117,25
9 05/10/2014 14:00 21:00 117,25               7:00 175,00 117,25
10 06/10/2014 21:00 07:00 167,50               10:00 250,00 167,50
11 07/10/2014 06:55 14:05 120,04               7:10 179,17 120,04
12 08/10/2014   14:15                 14:15 356,25 238,69
13 09/10/2014 06:55                   17:05 427,08 286,15
14 10/10/2014                     0:00 0,00 0,00
15 11/10/2014                     0:00 0,00 0,00
...                            
...                            
...       =SE(O(B5="";C5="");"";N5)   =C5-B5+(B5>C5) =$B$1*L5*24 =M5*(1-$B$2)
... se si vuole le competenze sulle sole ore anzichè ore:min cambiare solo questo
...           =ORA(C5-B5+(B5>C5)) =$B$1*L5  

 

Formula complessa senza celle di appoggio

Infine possiamo considerare l'idea di scrivere una formula complessa senza l'aiuto delle celle di appoggio.

In realtà possiamo usare temporaneamente le celle di appoggio, ma solo per avere un aiuto nello scrivere la nostra formula complessa. Queste celle verranno usate temporaneamente solo per avere un aiuto nel compilare la formula finale. Vediamo come procedere.

Partiamo dalla situazione appena lasciata e partiamo dalla formula già presente nella colonna D. Procediamo con questo schema

nella colonna D c'è =N5 in N5 c'è la formula =M5*(1-$B$2) prendiamo questa e la scriviamo al posto di N5 che si trova nella colonna D =M5*(1-$B$2)
ora nella colonna D c'è =M5*(1-$B$2) in M5 c'è la formula =$B$1*L5*24 prendiamo questa e la scriviamo al posto di M5 della colonna D =($B$1*L5*24)*(1-$B$2)
ora nella colonna D c'è =($B$1*L5*24)*(1-$B$2) in L5 c'è la formula =C5-B5+(B5>C5) prendiamo questa e la scrfiviamo al posto di L5 della colonna d =($B$1*(C5-B5+(B5>C5))*24)*(1-$B$2)

A questo punto la formula potrebbe dichiararsi completa se non fosse per quel correttivo da fare per individuare nelle colonne C e B eventuali celle vuote. Perciò, davanti alla formula appena completata cominciamo a scrivere:

=SE(O(B5="";C5="")        ($B$1*(C5-B5+(B5>C5))*24)*(1-$B$2)

aggiungiamo un punto e virgola ed una coppia di doppi apici nel caso che la condizione sia vera, ossia sia stata individuata almeno una delle due celle indicate vuota

;""

ora abbiamo =SE(O(B5="";C5="");""        ($B$1*(C5-B5+(B5>C5))*24)*(1-$B$2)

ora aggiungiamo un punto e virgola, la formula appena completata e chiudiamo la parentesi tonda aperta con la funzione SE )

;($B$1*(C5-B5+(B5>C5))*24)*(1-$B$2))

Ora abbiamo la formula completa: =SE(O(B5="";C5="");"";($B$1*(C5-B5+(B5>C5))*24)*(1-$B$2)).

Fatto questo e se tutto è stato fatto bene possiamo cancellare tutte le celle di appoggio ed abbiamo la tabella pulita

  A B C D
1 Retr Oraria 25    
2 Ritenute % 33%    
3        
4 Data Entra Esce Retrib. Netta
5 01/10/2014 08:10 12:15 68,40
6 02/10/2014 21:00 08:15 188,44
7 03/10/2014 21:15 07:15 167,50
8 04/10/2014 07:15 14:15 117,25
9 05/10/2014 14:00 21:00 117,25
10 06/10/2014 21:00 07:00 167,50
11 07/10/2014 06:55 14:05 120,04
12 08/10/2014   14:15  
13 09/10/2014 06:55    
14 10/10/2014      
15 11/10/2014      
         

Per ora è tutto. Grazie.