La gestione dei turni 4: la situazione ore e la situazione ferie

Riprendendo il file visto nella parte precedente, qui mostrerò una soluzione per leggere automaticamente valori inseriti in altri fogli, mostrerò alcune applicazioni di questa soluzione insieme ad applicazioni di lettura “statica”. Nel corso dell’articolo si capirà meglio cosa intendo.

La situazione ore anno in corso di un dipendente è calcolata sommando la differenza da turno (eventualmente corretta da ore aggiunte/sottratte manualmente) alla situazione ore anno in corso relativa al mese precedente se il mese non è gennaio. Se il mese è gennaio invece la situazione ore anno in corso è data dalla differenza da turno con le eventuali correzioni. Nella precedente parte ho mostrato il sistema che uso per trovare la DDT, non rimane che mostrare il sistema per leggere il valore d’interesse nel foglio del mese precedente.

Il nome del foglio

La sintassi per la lettura esterna al foglio corrente è formata dal nome del foglio a cui segue un punto di esclamazione e le coordinate della cella da leggere. Ad esempio, la formula “=Foglio2!A17” legge il valore della cella A17 nel foglio2. I fogli quindi devono essere rinominati opportunamente e la strutturazione dei fogli deve essere fissa, vale a dire che aggiungere o togliere righe/colonne nelle aree critiche può inficiare il comportamento atteso delle formule. Io ho scelto di rinominare i fogli con i primi tre caratteri del nome del mese per contenere la larghezza della linguetta: GEN, FEB, ecc.

La lettura “remota”

La formula utilizzata per la lettura esterna al foglio è questa:

“=INDIRETTO(TESTO(DATA.MESE($C$4;-1);”mmm”)&”!”&INDIRIZZO(RIF.RIGA();RIF.COLONNA()))”

La formula usa la funzione INDIRETTO per usare la stringa del nome del mese, DATA.MESE per calcolare la data del primo giorno del mese precedente, da questa la funzione TESTO estrae il nome del mese abbreviato (3 caratteri), a cui viene aggiunto il carattere “!” a cui le funzioni INDIRIZZO, RIF.RIGA e RIF.COLONNA aggiungono l’indirizzo della cella in cui la formula è inserita. Per capire come funziona è possibile metterla in una cella qualsiasi e usare Valuta formula.

Uso questa formula in una maniera particolare. Questa infatti va usata come un intervallo denominato e va inserita in Formule > Nomi definiti > Gestione nomi > Nuovo: bisogna far attenzione a cambiare l’ambito di validità del nome che deve essere limitato al solo foglio attivo, poi Excel ne farà un uso corretto anche copiando o creando un foglio nuovo. In questo modo nelle formule è sufficiente usare il nome assegnato come fosse un intervallo:

C:\Users\MN\AppData\Local\Microsoft\Windows\INetCache\Content.Word\2019-10-13_001636.png

PS: la formula restituisce l’errore di riferimento non valido #RIF! se non trova il foglio del mese precedente. Se il messaggio è inquietante è possibile usare la funzione SE.ERRORE per far apparire un altro messaggio, tipo “NO”:

“=SE.ERRORE(la_formula_intera; “NO”)”

La situazione ore

A questo punto è possibile ottenere la situazione ore anno in corso (SIT A) per il primo dipendente (AK6) con la formula:

“=SE(MESE($C$4)=1;AJ6;AJ6+MesePrec)”

La funzione SE verifica se il mese in C4 è uguale a 1 cioè gennaio, se lo è copia la differenza da turno, se non lo è inserisce la differenza da turno del mese corrente sommata alla situazione ore anno in corso relativa al mese precedente.

Sotto il risultato dopo aver inserito 10 ore per tutti nell’intervallo AK6:AK22 del foglio NOV, aver trascinato in basso la cella AK6, cancellato la formula in AK7 e corretto la formattazione condizionale sulle righe alterne:

C:\Users\MN\AppData\Local\Microsoft\Windows\INetCache\Content.Word\2019-10-13_132537.png

Dopo “SIT A” ho aggiunto il campo “SIT AA” che è formata dalla situazione ore anno in corso + situazione ore anno precedente (prevista solo per i dipendenti in banca ore) per conoscere la situazione ore totale di ogni dipendente. Nella cella AL6 ho inserito la formula sotto sfruttando i campi “Banca ore” e “Ore AP” (ore anno precedente) della tabella dei dipendenti e poi ho trascinato la cella in basso:

“=SE(IMP!A2=1;IMP!G2;”-“)”

La funzione SE verifica se nel campo “Banca ore” è inserito un 1, se è così riporta il valore che trova nel campo “Ore AP”, se non è così inserisce un trattino nella cella attiva:

I miei conteggi su giornate/ore si concludono con una formula che sfrutta un intervallo denominato per rilevare la serie di turni attivi più lunga per ogni dipendente, controllo necessario per il rispetto del riposo settimanale. La formula è da inserire con CTRL+ALT+INVIO in modo da renderla matriciale (nella barra della formula devono esserci le parentesi graffe a racchiudere la formula):

“=MAX(FREQUENZA(SE(1-VAL.ERRORE(CONFRONTA(C6:AG6;Tab.GG;0));RIF.COLONNA(C6:AG6));SE(VAL.ERRORE(CONFRONTA(C6:AG6;Tab.GG;0));RIF.COLONNA(C6:AG6))))”

La situazione ferie

Allo stesso modo ho aggiunto i conteggi per la verifica della situazione ferie. Il primo campo è stato il campo di conteggio per le ferie del mese, il secondo per le ferie annuali residue e il terzo campo per eventuali residui di ferie dell’anno precedente (FAP).

Ferie del mese (F): ho usato CONTA.SE per contare i giorni di congedo e di congedo aggiuntivo (sigle F e Fx) distribuiti nel mese corrente:

“=CONTA.SE(C6:AG6;”F*”)”

Ferie annuali residue (FR): se è gennaio il valore è uguale al campo “Cong tot” della tabella dei dipendenti (congedo ordinario + congedo aggiuntivo + congedo anno precedente) diminuito dalle ferie godute nel mese; se non è gennaio il valore è uguale al residuo ferie del mese precedente diminuito delle ferie godute nel mese. La seguente formula fa proprio questo:

“=SE(MESE($C$4)=1;IMP!H2-AN6;MesePrec-AN6)”

Ferie anno precedente (FAP): se ci sono dei residui ed è gennaio, il valore è dato dal campo “Cong AP” diminuito delle ferie godute nel mese; se ci sono residui e non è gennaio il valore è uguale al residuo ferie anno precedente del mese prima, diminuito delle ferie godute nel mese. La formula che segue fa questo, e sfrutta la funzione MAX per restituire il valore maggiore di un insieme (zero è maggiore dei numeri negativi):

=MAX(SE(MESE($C$4)=1;IMP!K2-AN6;MesePrec-AN6);0)

In basso il risultato finale dopo l’aggiunta di questi campi. Ho evidenziato gli sforamenti nel campo RS ma a seconda dei bisogni si potrebbero aggiungere altri avvisi:

Qui il file da scaricare: Turni-Post4.xlsx (10 download)

Buon lavoro!

Questa voce è stata pubblicata in Excel e contrassegnata con , , , , , , . Contrassegna il permalink.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *