[Power Query] Arrotondamento Ore

Moderatore: Utilizzo_prof_Excel

Bloccato
Avatar utente

Andrea90
Messaggi: 2193 | Topic creati
Iscritto il: dom 28 giu 2020, 19:41
Luogo: Bologna
Ringraziato: 666 volte
Contatta:

[Power Query] Arrotondamento Ore

Messaggio da Andrea90 »

Buongiorno a tutti i partecipanti del forum :wave:

Visto che ormai il sito è diventato operativo, la nostra idea è quella di popolare questa sezione con esempi e spunti pratici che speriamo possano servire nello svolgimento delle attività quotidiane. Magari non saremo in grado di individuare un bisogno specifico, ma quanto meno ci auguriamo di fornire uno spunto di riflessione utile al fine di adattare la soluzione proposta a quella che è la vostra vera esigenza.

Oggi vorrei trattare il tema dell'arrotondamento degli orari.

Mi è capitato in passato di dover fornire supporto al team delle risorse umane durante l'elaborazione del dato di ingresso/uscita del personale. La loro esigenza era quella di arrotondare l'orario di timbratura del cartellino per un certo intervallo di tempo, espresso in minuti, in difetto o in eccesso (ad esempio l'orario 08:09:49 doveva essere arrotondato a 08:00:00, poiché l'intervallo di riferimento erano i 15 minuti).

Ovviamente la soluzione a questo problema si può ottenere in diversi modi, anche mediante l'utilizzo delle formule. Tuttavia siccome il file che conteneva queste informazioni era estrapolato dal gestionale in formato csv, e siccome veniva fornito un file per ciascuna postazione di rilevazione, la soluzione adottata prevedeva l'utilizzo di PowerQuery vista la possibilità di caricare ed accodare i dati direttamente da una cartella.

La logica di calcolo è la seguente:

Siccome in Excel le date e le ore sono in realtà espresse in formato numerico, con l'unità temporale rappresentata dal giorno mentre l'orario è espresso come frazione di quest'ultimo, potremmo rappresentare i diversi intervalli temporali in questo modo:

Immagine

Dunque se è vero che un minuto corrisponde a 0,00069 allora moltiplicando questo valore per l'intervallo di nostro interesse troveremo il numero decimale che rappresenta ciascuno di questi intervalli:

Immagine

Dove il numero decimale posto nella seconda colonna è stato ottenuto moltiplicando il valore decimale rappresentativo del minuto per il numero di minuti presi come riferimento (ad esempio: 0,00069 * 5 = 0,00347).
La terza colonna rappresenta invece il numero di volte che questo intervallo è contenuto all'interno dell'unità di riferimento (che ricordiamo essere il giorno). Dunque ci saranno 288 intervalli da 5 minuti all'interno di un singolo giorno, così come ci saranno 48 intervalli da 30 minuti. Se invece di 30 inseriamo 60 vedremo che il numero sarà pari a 24 ovvero il numero di ore presenti in un giorno.

Ora per il nostro calcolo possiamo osservare la seguente tabella:

Immagine

Moltiplicando il numero decimale che rappresenta l'orario (campo: [Numero]) per il numero di intervalli presenti in una giornata (dunque 288 se parliamo di 5 minuti, 96 per i 15 minuti, ecc.) troveremo il numero decimale che rappresenta l'intervallo al quale ci troviamo. Vediamo infatti che in corrispondenza di un multiplo di 5 il campo [Ora_5min] contiene valori interi per 00:05:00 - 00:10:00 - 00:15:00. Stesso discorso vale per le altre due colonne.

Per completezza riporto qualche altro esempio di calcolo relativo ad altre fasce orarie:

Immagine

Dunque se noi riuscissimo ad estrapolare da questi numeri decimali, solamente i valori interi troveremmo il numero di intervallo a cui appartiene quel dato orario.

Dividendo il numero di appartenenza all'intervallo per il numero di intervalli presenti, troveremo un valore unico per ciascun orario presente all'interno dell'intervallo di rilevanza:

Immagine

Ora che abbiamo compreso il punto di arrivo andiamo ad inserire qualche dato dal file demo:

Immagine

Carichiamo questi dati in PowerQuery (essendo inseriti all'interno di una tabella il caricamento avviene semplicemente selezionando l'opzione "da intervallo/tabella", presente nel menu "Dati").

Ora tramite interfaccia di PowerQuery andremo ad aggiungere una nuova colonna personalizzata.

Inseriremo la formula:

Codice: Seleziona tutto

Time.From(Number.RoundDown(Number.From([OraInizio])*96)/96)
Analizziamo i singoli passaggi:

1) Number.From([OraInizio])*96 --> Trasforma in numero il campo [OraInizio] e lo moltiplica per il corrispondente valore di intervallo

2) Number.RoundDown(Number.From([OraInizio])*96) --> Individua il valore intero del numero decimale trovato al punto precedente

3) Number.RoundDown(Number.From([OraInizio])*96)/96 --> Riporta il numero in formato decimale, considerando uguali gli orari che appartengono ad un dato intervallo

4) Time.From(Number.RoundDown(Number.From([OraInizio])*96)/96) --> Riporta il valore in formato Orario

Se invece avessimo voluto arrotondare l'orario per eccesso invece che per difetto, avremmo utilizzato la formula: Number.RoundUp

Carichiamo i dati in una tabella ed avremo il seguente risultato:

Immagine

Ovviamente potremmo arrotondare, per difetto o per eccesso, intervalli di tempo diversi sostituendo 96 al numero rappresentativo dell'intervallo desiderato (esempio 48 se vogliamo arrotondare alla mezz'ora).

Sperando che il tutorial sia stato utile vi lascio come allegato il file demo per esercitarsi. Per chi volesse ricevere il file con la soluzione vi invito ad inviarmi un messaggio privato, sarà poi mia premura inviarvelo.

Inoltre se ci sono argomenti non chiari, suggerimenti, richieste di tutorial riguardanti argomenti non trattati precedentemente vi invito a scriverci nell'apposita sezione dedicata "Feedback" --> Qui

Allegati
Dati.xlsx
(18.92 KiB) Scaricato 147 volte


Se hai gradito l'aiuto che hai ricevuto considera di contribuire alle spese per il mantenimento del forum facendo una libera DONAZIONE --> Link

Ricordarsi di segnare come "RISOLTE" le discussioni per le quali si è ricevuto un feedback positivo. Per vedere come fare --> Link
Bloccato