[Excel] calcolare percentuali diverse in un foglio lavoro

[zem]

Ciao a tutti...
Ho creato due righe con excel ma sicuramente c'e' un errore che non trovo
Per Antony.... ti mando il fille
Fammi sapere
Ciao

[Anthony47]

Non so a che errore alludi; io ho messo le formule XSTRA sul foglio ORE, colonne N:Q, per il calcolo di periodi del tipo segnato in N3:Q3, usando la tabella in Codici!A1 (quindi relativi a straordinari)
Le formule restituiscono valori coerenti a quanto mappato in tabella:
-lunedi' 2 feb, 2h:00 tipo 1 e 0h:55 di tipo 2
-martedi' 3 feb, 4h:30 tipo 1, 4h:55 tipo 2
-domenica: non ci sono straordinari, ma non essendo compilata la riga "7" sulla tabella DefOrari in ogni caso non sarebbe stato calcolato

Ho replicato la tabella in Codici!L1, ma specificando OrarioStd=0h:0m, e replicato formule su Ore!S:V (quindi calcoli relativi a maggiorazioni per orario standard).
Anche qui i calcoli mi sembrano coerenti con i valori mappati in tabella.

RIMANE il problema di mappare correttamente i dati nelle tabelle, perche' palesemente ci sono valori solo di prova.
Per questo devi crearti un tuo standard (questa fascia oraria corrisponde a questo "tipo", e a questa maggiorazione) e poi creare le fasce orarie. Ricorda che nella tabella l' orario nella intestazione rappresenta il "fine orario per quella classificazione" (es: con la tabella presente sul foglio, la fascia oraria "fino alle 6:00 e' di tipo 2); ricorda anche che se le tue presenze possono continuare fino "al giorno dopo" (es classico: dalle 22:00 alle 6:00), allora la tabella deve essere piu' larga delle 24:00, inserendo quindi orari quali (ad esempio) 30h:00 che rappresentano le 6:00 del giorno dopo. A scanso di sorprese suggerisco di arrivare fino alle 48:00, cioe' fino alla fine del giorno dopo, ricordando (sempre ad esempio) che "il giorno dopo" del venerdi' e' sabato, quindi la fascia oraria 24:00-48:00 sono orari relativi a un sabato.
Ricorda infine che la tabella deve avere a destra una colonna vuota.

Ciao, fai sapere.

[zem]

Grazie tantissimo Anthony per aver risolto il mio problema e per avermi modificato la mia struttura..

Ci sono ora alcuni aspetti che vorrei risolvere e ti spiego
intanto ti ho inviato un nuovo file cosi' puoi vedere.

1 caso: nelle giornate di domenica (riga 4 e riga 11 nella tabella ore) ci sono 2 tipologie di lavoro, quella nella riga 4 lavoro solo al mattino e in questo caso sono solo straordinari, quindi se metto in B8 00:00 (nella tabella codici) la riga 4 (nella tabella ore) gli straordinari e le maggiorazioni mi riportano il valore giusto ma nella riga 11 mi sballa tutto... non mi dovrebbe segnare 8 ore straordinario75% ma solo 4 ore festivo e 8 ore maggiorazioni 75%.
Il motivo penso di averlo capito e cioè che lui conta dalla prima timbratura del giorno ma in questo caso ci sarebbero prima sraordinari e poi ore ordinarie.Come risolvo questo problema?

2 caso: Vorrei che le maggiorazioni me le calcolasse solo sulle 8 ore lavorative e non sugli straordinari. vedi come esempio riga 6.. gli straordinari sono ok, la cella S6 è giusta ma nella cella T6 ci dovrebbe essere 1 e il resto non ci deve essere.

Risolveremo anche questo??? sono fiducioso..
Un'altra cosa la funzione xstra nella mia pagina non e' come quelle che mi hai sempre fatto vedere nei topic es: la formula =XSTRA($A4;$B4;DefOrari;N$3) dove N$ è ok ma il 3 e' uguale per tutte le formule... mi spieghi? e poi perche' non vedo la macro?BO!!! non ci capisco niente.

ciao

zem

[Anthony47]

Ora che hai potuto applicare il metodo devi variare la tua base dati, alquanto particolare in verita', per poter eseguire tutti i calcoli che ti servono.

Per il considerare straordinari la prima parte di una giornata, splitti le timbrature di quel giorno su due righe, di cui una la manipoli con una DefOrari di OrarioStd =0 e codici di straordinari e una con OrarioStd=8:00 e codici di maggiorazione

Per calcolare le maggiorazioni solo sulle prime 8 ore devi, nel calcolo, inserire due componenti: la prima, con una tabella con OrarioStd=0 e codici di maggiorazione, e una seconda con altra tabella avente OrarioStd=8:00 e codici di maggiorazione, il cui risultato va sottratto al calcolo precedente.
Es: =XSTRA($A4;$B4;DefOrari1;1)- XSTRA($A4;$B4;DefOrari2;1)
Cioe': calcoli il dato (tipo 1, nella formula) per tutte le ore lavorate, sottrai il dato relativo alle ore oltre le 8:00.

Mi chiedi perche' ho usato =XSTRA($A4;$B4;DefOrari;N$3) invece di una cosa tipo =XSTRA($A4;$B4;DefOrari;1): nel mio modello ho inserito i codici su riga 3, da N3 a V3; con quella sintassi posso copiare la stessa formula da N4 nelle colonne adiacenti e poi nelle righe sottostanti, ed excel adattera' gli indirizzi alle colonne/righe giuste.
Questo viene fatto dal simbolo "$" che trasforma quella parte di indirizzo (lettera di colonna, o numero di riga) in indirizzo "assoluto", che non viene modificato con la copia della formula.
Ti conviene guardare l' help on line alla voce "Informazioni sui riferimenti di cella e di intervallo", perche' sono informazioni che semplificano molto la creazione di fogli di una certa complessita'.

Ciao.

[zem]

Grazie Anthony.... ho chiesto a mia moglie di fare il caffe' e mi ha risposto di farlo fare a te visto che sono sempre al computer... eheheh Piccola battuta....

Ho eseguito tutto quello che mi hai detto e funziona quasi tutto..
Il problema e' se guardi al file che ho inviato se cambio nella casella C5 metto 10.20 il sistema funziona, se metto 10.30 va in errore valore, se metto 10.40 torna a funzionare....
Pensavo fosse un errore nella tabella codici ma quella fascia oraria e' uguale per tutti i tre valori di orario... Cosa e' che non funziona?
Per quanto riguarda il problema della domenica penso di averlo risolto e cioe' avere prima gli straordinari e poi le ore lavorative.

Un'altra domanda... questa macro gestisce solo 4 timbrature 2 entrate e 2 uscite... ma se creo una seconda colonna fantasma dopo le prime 4 timbrature di data e se si crea una seconda tabella codici si puo' fare la somma delle 2?

zem

[Anthony47]

Bello! hai trovato una combinazione di numeri per cui gli arrotondamenti di calcolo di excel non portano a "zero" lo straordinario residuo anche dopo aver applicato tutta la tabella; per curiosita' vostra, il dato residuale e' di 2,9*10^(-8) pari a qualche centesimo di secondo.
Ho rimediato modificando questa istruzione:

Codice: Seleziona tutto

    Xstra = Xstra - TabTy0
    If Xstra <= 0.00001 Then      '<<< MODIFICATA
    TabTy(CT) = TabTy(CT) + Xstra


E' modificata solo quella marcata (prima era If Xstra <= 0 Then), le altre due sono mostrate solo per identificare la posizione.

Se hai 6 timbrature secondo me e' un casino, salvo l' ipotesi che con le prima 2 o 4 fai esattamente 8 ore, ipotesi ovviamente non realistica.
Una ipotesi intermedia potrebbe essere che nelle prime 4 timbrature si superino sempre le ore standard (ma mai con le prime due) e le ultime due siano sempre di straordinario. In questo caso si potrebbero creare con formule excel due quartetti di timbrature: il primo quartetto e' uguale alle prime 4 timbrature e viene usato solo per il calcolo delle maggiorazioni delle ore standard; il secondo ha la prima entrata pari alla 4° timbratura meno lo straordinario che risulta nel primo quartetto, poi la 4°, 5° e 6° timbratura e verra' usato solo per il calcolo degli straordinari (usando una tabella DefOrari con OrarioStd=0:00).

Vedi tu se ci sono le condizioni per cimentarsi...
Per quanto riguarda il problema che hai con la moglie, non sono proprio in grado di aiutarti

Ciao.

[zem]

GRAZIE GRAZIE GRAZIE... FUNZIONA BENISSIMO
L'unico problema e' come aumentare le timbrature...
Perche' capita spesso di uscire e rientrare 3 4 volte al giorno...
Non e' possibile modificare la macro? Cos'è che impedisce questo blocco...?
Ho creato 4 tibrature ma giustamente sballa i calcoli perche' nel secondo blocco ricomincia il conteggio dall'inizio

Secondo te si puo' agire sulla tabella deforari dove riporto il valore 08.00 con qualche formula che mi conteggi le 8 ore?
Es. se CALCOLO PRIMA TIMBRATURA + CALCOLO SECONDA TIMBRATURA + ECC.. ECC... E DIRE DI CALCOLARE TUTTO QUESTO DOPO LE 8 ORE LAVORATIVE...? Trasformarla in una formula?
Fammi sapere perche' ho proprio bisogno di questo... Altrimenti tutto il lavoro che abbiamo fatto e' inutile per il mio caso..

CIAO

[Anthony47]

Non e' possibile modificare la macro? Cos'è che impedisce questo blocco...?

E' che riprendere una macro di oltre 1 anno fa non mi eccita... tra l' altro penso che sei l' unico in Italia che timbra 6 volte al giorno.
L' uso di una formula per variare l' OrarioStd nella deforari non e' applicabile, salvo definire una deforari diversa per ogni giorno che hai 6 timbrature; mi pare pero' un' operazione ancora piu' masochistica che non controllare a mano il terzo gruppo di timbrature.
Comunque non disperare, se mi viene voglia di farmi del male provo a modificare la macro. Se mi viene...

Ciao.

[zem]

Caro, carissimo Anthony... sono nelle tue mani.
Aspetto.
Zem

[zem]

Ogni tanto passo per vedere se qualcuno, o Anthony, ha messo mano al mio problema...

[zem]

Ciao Anthony,
Ci siamo quasi... sta venendo alla perfezione
Ho allegato il file con tutte le modifiche che ho fatto.
Adesso vorrei sistemare intanto un'altra cosa, tu sai che i miei straordinari e maggiorazioni sono contorte, per quanto riguarda dal lunedi al sabato per gli straordinari e per le maggiorazioni ho risolto inserendo la formula che mi hai consigliato e cioe' per gli straordinari =xstra($A7;$B7;DefOrari;N$1) e per le maggiorazioni =xstra($A7;$B7;DefOrari2;S$1)-xstra($A7;$B7;DefOrari3;S$1) ma per la domenica devo usare questo tipo di formule per far quadrare tutto e cioè per straordinari =xstra($A4;$B4;DefOrari;N$1) e per le maggiorazioni =xstra($A4;$B4;DefOrari2;S$1), il problema e' quando creo un mese nuovo e cioè che la formula delle maggiorazioni della domenica mi si può trovare in un giorno della settimana. C'è un modo per abbinare una data e riportare la formula a lei corrispondente?
Quello che mi piacerebbe fare e' che aprendo una nuova tabella e inserendo nella casella A1 il nome del mese mi si predispone tutta la tabella nel formato come quella che desidero lasciando in bianco solo la parte da inserire le ore....
Chiedo troppo??? Sono esagerato vero? Ma so che si può fare!!!

Ciao

ZEM

[Anthony47]

Ho guardato il tuo file.
Spero che le tabelle deforari che riporti siano solo delle bozze, perche' e' erratissimo avere nelle tabelle deforari delle celle senza codice associato; significa che le ore lavorarate in quell' intervallo non vengono calcolate ne' come straordinario ne' come maggiorazione.

E' anche opportuno che le tabelle arrivino fino a 10, cioe' con i giorni Prefestivo, Festivo infrasettimanale, Festivo seguito da festivo; anche se tu non fai queste distinzioni (e soprattutto non hai una colonna con il flag "festivo infrasettimanale") la macro si aspetta una tabella di 10 righe.

Per quanto riguarda il calcolo da fare la domenica, non serve fare voli pindarici: metti la stessa formula degli altri giorni ma in DefOrari3, come orario std della domenica scriverai 24:00
Tra l' altro, lo ricordo, le tabelle DefOrari2 e DefOrari3 devono avere lo stesso contenuto, a parte l' orario std; cosa che nell' allegato non avviene.

Ti quadra?
Ciao.