formattazione condizionale di più' valori e celle

[gioh66]

Stavo guardando il file ma io vedo numeri diversi che verificati a mano con la calcolatrice corrispondono.

Non vorrei che le discrepanze siano dovute alle diverse versioni di office. Vedo in firma che tu usi la 3.1 mentre io uso la più recente 4.1.5

[diego68]

sto rifacendo il foglio perche' ho modificato mi ero dimenticato alcune cose te lo riallego perché' alcune variabili cambiano ora le feste sono in casella AG invece che AO per esempio, il resto lo devo ancora cancellare.
te lo allego cosi' vedi che a me mi da un risultato diverso
io uso apache open office 4.1.3

[gioh66]

Prova a vedere se così funziona

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$41:$A$71;2)>5;B$41:B$71)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$41:$A$71)<6;RESTO(B41:B71;1/3)*(B$41:B$71>1/3))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$41:$A$71;$AH$6:$AH$15;0))*(GIORNO.SETTIMANA($A$41:$A$71;2)<6);B41:B71)

Ho modificato l'ultimo MATR.SOMMA.PRODOTTO in modo che conti solo le ore delle festività che non cadono in un giorno festivo.
Ps.: ricordati di eliminare quel 7 dicembre che non è festivo.

[diego68]

grazie
funziona
ho una altra domanda.
ti allego il file che ho sistemato e quasi terminato credo.
Colonne da N a Y se vedi ho aggiunto una colonna con la scritta sede.
Mi piacerebbe avere un totale delle ore diviso per esempio quando a fianco c'e' scritto "C" e mi conti le ore dei giorni dove c'e' scritto "C" e quando c'e' "S" faccia il conto totale delle ore per "S" in modo da avere una ripartizione per i conti a secondo della Sede e una altra che mi faccia invece lo stesso conto ma me lo mostri in percentuale. Quindi una totale ore a secondo della sede una totale a secondo della sede ma espresso in percentuale.
Ma questo anche non lo so fare
Le colonne da AI in poi non sono più' valide le devo cancellare.
Poi una altra cosa che ho notato che e' un errore.
le celle per esempio B86 ad andare giu che sono gia formattate condizionalmente vorrei che se le ore riportate fossero meno di 8 mi segnasse per esempio il numero in colore rosso ma non ci riesco, ho provato in mille modi ma e' come se non mi vedesse la formattazione.
non riesco a capire come mai. mi mantiene solo quella che c'e' gia dentro e non insieme a quella del colore carattere diverso se per esempio il totale ore e' 7 oppure 5 etc.

[gioh66]

Ciao se vuoi un totale per sede ti basta usare il SOMMA.SE. Per esempio per Antonio metti per la sede "C"

Codice: Seleziona tutto

=SOMMA.SE($P$41:$P$71;"C";$N$41:$N$71) 

Poi per la percentuale usa
=

Codice: Seleziona tutto

SOMMA.SE($P$41:$P$71;"C";$N$41:$N$71)/SOMMA($N$41:$N$71)*100

Per la sede "S" ti basta sostituire la lettera nella prima funzione.
Per la formattazione condizionale in primis ti consiglio di eliminare le celle unite dalle tabelle. Sono come la peste per le formule (e per le macro...mi dicono).
Ho provato comunque ad inserire questa formula impostando il carattere rosso

Codice: Seleziona tutto

E(B85<1/3;VAL.NUMERO(B85))

ma e non capisco perchè colora di rosso tutto quello che è minore di 8 ore ma dove c'è 00.00 alcuni li colora di rosso e altri li lascia neri...
Ps.: non so se te l'ho già detto ma quando vuoi applicare una FC in OpenOffice devi selezionare l'intervallo dal basso in alto nel tuo caso da N115 a B85, in modo da avere la cella attiva sulla prima casella (B85), altrimenti la FC viene applicata solo all'ultima cella in basso a destra (N115).
È un bug di OpenOffice che in passato mi ha fatto dannare prima di capire la cosa.

[diego68]

sono ancora dietro a questa formula che mi sta facendo diventare matto
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)>5;B$42:B$72)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72)<5;RESTO(B42:B72;1/3)*(B$42:B$72>1/3))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))*(GIORNO.SETTIMANA($A$42:$A$72;2)<5);B42:B72)
non mi da il conto esatto perché' non mi calcola il venerdì.
ho aggiunto una riga pertanto anziché' B41-A71 e' diventato B42-A72
Se a mano faccio somma totale ore lavorate meno ore in busta mi da esatte le ore straordinarie , se metto la formula che mi hai dato non mi calcola i venerdì.
Potrei anche fare totale ore meno ore della busta paga ma se per esempio lavoro meno ore della busta paga perché' c'e' malattia o ferie ma un giorno ho fatto straordinari per esempio mi da ovviamente un numero negativo e non va bene..

[gioh66]

....acc...acc... errore mio mettendo <5 ho escluso i venerdì. Correggi così
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)>5;B$42:B$72)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72)<=5;RESTO(B42:B72;1/3)*(B$42:B$72>1/3))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))*(GIORNO.SETTIMANA($A$42:$A$72;2)<=5);B42:B72)
o così
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)>5;B$42:B$72)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72)<6;RESTO(B42:B72;1/3)*(B$42:B$72>1/3))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))*(GIORNO.SETTIMANA($A$42:$A$72;2)<6);B42:B72)

[diego68]

aiiaa perdi colpi
Non funziona nemmeno cosi'
mi calcola più' ore
se metto per esempio tutti i giorni 10 ore alla fine vengono totale lavorate 310 ore
se in busta sono 160 faccio 310-160=150 ore giusto no? dimmi se sbaglio magari sono io che mi perdo qualcosa.
con entrambe le formule che mi hai scritto viene 156 ore e non 150 ed anche con quella iniziale viene cosi'...anche io avevo provato a cambiare >5 etc ma il risultato e' sempre lo stesso ed e' incomprensibile per me.
se metto 10 ore lavorate domenica 8 me ne calcola 12..
si sono le domeniche che calcola di più' anche le altre.metto 10 ore ne calcola 12
dopo le 8 ore la domenica calcola 10 salta le 9 ore non so se mi sono spiegato

[gioh66]

Sto facendo prove sull'ultimo file che mi hai mandato e i risultati sono corretti. Anche la formula per gli straordinari è come l'ultima che ti ho postato quella col <6. Tu su che file stai lavorando?

[diego68]

te lo allego cosi' lo vedi come lo sto portando avanti
la domenica dopo 8 ore di straordinario passa a 10 ore totali conteggiate
prova a segnare solo domenica 8 ore di lavoro e poi prova a segnare 9 ore
8 ore segna totale 8 ore mentre 9 ore segna totale 10 ore e cosi' via

[gioh66]

Ennesima correzione, speriamo l'ultima in B80
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)>5;B$42:B$72)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72)<=5*RESTO(B42:B72;1/3)*(B$42:B$72>1/3))
e in B81
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)>5;B$42:B$72)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72)<6*RESTO(B42:B72;1/3)*(B$42:B$72>1/3))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))*(GIORNO.SETTIMANA($A$42:$A$72;2)<6);B42:B72)
in rosso la modifica.

[diego68]

no
manca ancora un pezzo
poi ci siamo
calcola solo i festivi e non durante la settimana ora ma mi basta solo la formula in B81 l'altra formula non mi serve
se no si può' fare che tengo questa buona per i festivi e metto una altra in una altra casella solo per i feriali sopra le 8 ore e più' faccio la somma delle due caselle magari e' più' facile

[gioh66]

Non ho capito, la formula in B81 è la somma di tre formule di cui la prima calcola le ore nei giorni festivi, la seconda lo straordinario sopra le 8 ore nei giorni feriali e la terza calcola le ore delle festività che cadono nei giorni feriali. Cosa ti manca?

[diego68]

ti ho allegato il foglio che ho io prima cosi' prova a mettere la formula in b81 sul mio foglio
poi prova a compilare gli orari delle presenze per esempio la prima settimana da domenica a domenica con 10 ore al giorno e guarda il totale.
la domenica 1 per esempio in b81 hai il totale ma gia da lunedì 2 se metti 10 ore non le le calcola nel totale di b81
allora a questo punto visto che in B81 da solo il totale dei festivi si può' fare una formula che in B80 ad esempio dia la somma che va da b42 a b72 ma solo delle ore sopra le 8 e solo da lunedì a venerdì, cosi poi in b82 faccio la somma di b81+b80 no? non e' più semplice visto che la formula che ho ora non funziona mai?
ho provato ad inserire questa formula in una altra casella che poi il totale lo dovrei sommare a B81
la formula dovrebbe calcolare da lunedì a venerdì compreso le ore sopra le 8
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72)<6;RESTO(B42:B72;1/3)*(B$42:B$72>1/3))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0));B42:B72)
il problema e' che funziona ma calcola fino al giovedì', il venerdì non lo calcola.
ho provato anche a mettere<=5 ma e' lo stesso.

[gioh66]

... era la seconda formula che dava problemi perchè mi sono dimenticato di mettere il 2 nel GIORNO.SETTIMANA
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)<6;RESTO(B$42:B$72;1/3)*(B$42:B$72>1/3))
e se non lo metti conta i giorni della settimana col sistema anglosassone cioè inizia da domenica, invece che da lunedì come facciamo noi. Scusami della distrazione.
Ti riallego il file dove nella riga 82 ho scomposto la formula e adesso mi pare dia i risultati voluti

[diego68]

si pare funziona ma mancano le feste infatti 25.26 dicembre calcola il totale straordinario più' le ore sopra le 8 ore pertanto se 25 dicembre metto 10 ore lavorate mi calcola 10 ore+ 2 ore extra sopra le 8 ore invece deve calcolare ovviamente solo 10 ore.
quindi credo bisogna aggiungere tipo
$A$42:$A$72;$AH$7:$AH$16;0 ma non so come metterlo

[gioh66]

Prova così in B81
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)>5;B$42:B$72)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)<6;RESTO(B$42:B$72;1/3)*(B$42:B$72>1/3)*NON(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))*(GIORNO.SETTIMANA($A$42:$A$72;2)<6);B$42:B$72)
ho aggiunto (in rosso) al secondo MATR.SOMMA.PRODOTTO, quello che calcola le ore in più oltre le 8, che il giorno non sia una festività.

[diego68]

funziona!!!! a
ma ti volevo chiedere perche' mi ero dimenticato
nelle colonne P, S etc dove ho messo la sede e tu mi mi avevi dato questa formula per calcolare il totale
=SOMMA.SE($P$42:$P$72;"C";$N$42:$N$72)
adesso si può' aggiungere alla formula di prima
=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)>5;B$42:B$72)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)<6;RESTO(B$42:B$72;1/3)*(B$42:B$72>1/3)*NON(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))))+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))*(GIORNO.SETTIMANA($A$42:$A$72;2)<6);B$42:B$72)
anche questa formula? in modo che possa anche dividere per le sedi distinte i due straordinari?

[gioh66]

Allora per fare una prova ho inserito una colonna con le sedi da AG42 a AG72 e sotto, in AG 83 ho messo la formula

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)>5;$AG$42:$AG$72="c";B$42:B$72)+MATR.SOMMA.PRODOTTO(GIORNO.SETTIMANA($A$42:$A$72;2)<6;RESTO(B$42:B$72;1/3)*(B$42:B$72>1/3)*NON(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0)));AG$42:AG$72="c")+MATR.SOMMA.PRODOTTO(VAL.NUMERO(CONFRONTA($A$42:$A$72;$AH$7:$AH$16;0))*(GIORNO.SETTIMANA($A$42:$A$72;2)<6);B$42:B$72;AG$42:AG$72="c")

la formula è per la sede "c". Per la "s" basta fare la sostituzione. Ovviamente nel tuo file sostituisci anche l'intervallo AG42:AG72 con gli intervalli appropriati.

[diego68]

non calcola natale e santo stefano il resto sembra calcolare tutto
per la C funziona per la S non funziona ma forse sono io che sbaglio qualcosa ma non fa niente faccio totale ore - ore di C e trovo S cosi' e' più' facile
poi una domanda
ho visto che dalla lettera c la colonna C se scrollo la pagina a sinistra mi rientra dentro la lettera c tenendomela ferma non so se mi sono spiegato e le altre colonne si nascondono sotto la c in modo che si possa scorrere tutta la pagina mantenendo fermo l'inizio pagina. Come hai fatto? non avevo mai visto questa cosa.

[gioh66]

Calcola tutto deve solo esserci la lettera nella colonna AG. Se guardi l'esempio vedrai che nella formula che fa il calcolo per le festività: AH82 per "c" e AH86 per "s", calcola 11 ore a natale e 10 ore a santo Stefano.
Per quanto riguarda la formula con la "s" presta attenzione a quando fai il cambio perchè il programma appena digiti la esse "prevede" ciò che vuoi scrivere e mette un SE(), che ovviamente sballa la formula.
Per quanto riguarda lo scroll bloccato è la funzionalità blocca riquadri. Per fare quello che ho fatto io selezioni la colonna D vai nel menù "Finestra" e metti la spunta a "fissa".

Io l'ho fatto sulla colonna D ma si può anche selezionare una cella. Ad esempio se selezioni la cella D39 bloccherai la parte alta dalla riga 1 alla 38 e a sinistra fino alla colonna C.

[diego68]

ok ho capito tutto
se volessi ora calcolare sempre con questa formula che mi divida C ed S ma senza specificare sopra le 8 ore ma solo per esempio tutto il totale della colonna T42-T72 mi servirebbe solo il totale compressivo delle ore fatte ma divise per sede.
poi per esempio
=SE(O(GIORNO.SETTIMANA($A87;2)>5;VAL.NUMERO(CONFRONTA($A87;$AH$7:$AH$17;0)));"Riposo";D7)
mi riporta la casella D7 oppure se e' festivo mi riporta "Riposo". Ma ad esempio se ho un turno che parte dalle 17 alla 1 di notte ma sulla casella D7 ho scritto 16:00 vorrei che mi scrivesse lo stesso 17 come orario di inizio turno, e' possibile? e lo stesso per il turno che parte dalle 13 alle 21 se scrivessi 12 mi calcola sempre dalle 13

[gioh66]

Per fare solo il totale puoi usare il SOMMA.SE che ti ho già proposto, aggiustando i riferimenti.
Per l'altro quesito mi pare che avevamo fatto una formula più corposa. Se adesso vuoi queste aggiunta bisogna vedere come implementarla. Quali sono gli orari di inizio turno?

[diego68]

giusto hai ragione scusa, ho fatto tutto.
i turni normalmente sono 5-13 o 13-21 oppure 17-1

[gioh66]

Allora la nuova formula, che tiene conto anche dell'inizio turno, potrebbe essere così

Codice: Seleziona tutto

=SE(E(O(GIORNO.SETTIMANA($A86;2)>5;VAL.NUMERO(CONFRONTA($A86;$AH$7:$AH$17;0)));B7="malattia");"malattia";SE(E(O(GIORNO.SETTIMANA($A86;2)>5;VAL.NUMERO(CONFRONTA($A86;$AH$7:$AH$17;0)));B7="ferie");"ferie";SE(O(GIORNO.SETTIMANA($A86;2)>5;VAL.NUMERO(CONFRONTA($A86;$AH$7:$AH$17;0)));"riposo";SE(ORA(B7)<=5;ORARIO(5;0;0);SE(E(ORA(B7)>5;ORA(B7)<=13);ORARIO(13;0;0);SE(E(ORA(B7)>13;ORA(B7)<=17);ORARIO(17;0;0);SE(VAL.TESTO(B7);B7)))))))

Ps.: formatta le celle come HH.MM

[diego68]

siccome ho aggiunto delle righe nel frattempo dove andrebbe messa la formula? perché' A86 per esempio a cosa di riferisce? siccome devo adattarla al nuovo foglio e quindi cambiare tutte le caselle nella formula.

[gioh66]

A86 è la data che hai sulla sinistra delle tabelle. Ti rimando il file con la formula inserita in B86 e trascinata in basso.

[diego68]

ok ci lavoro sopra devo cambiare tutte le caselle ma ce la posso fare

[diego68]

Ho notato un errore nei miei fogli
io nella cesella che a te risulta come B77 ho messo i giorni lavorativi come da busta paga
pero' per esempio se cambio l'anno e metto 2020 nel calendario di febbraio anziché' fermarsi alla casella A al giorno 28 mi scrive anche gli altri 3 giorni successivi andando a finire fino al 2 marzo pertanto mi sballano i giorni lavorativi che dovrebbero essere 20 ma diventano 21
come faccio a fare finire il calendario al 28 se il mese ne ha 28 o 29 se e' bisestile come il 2020 ad esempio? in modo che il calcolo delle ore mensili in automatico diventino 20
non so se sono riuscito a spiegarmi.
il calendario si modifica da solo ma a febbraio dovrebbe fermarsi a 28 o 29 e non includermi i giorni successivi, anche aprile che ne ha 30 mi segna il 1 maggio

[gioh66]

Se ho capito e se i tuoi fogli sono strutturati nello stesso modo, prova a mettere in B77 questa formula per il conteggio dei giorni lavorativi

Codice: Seleziona tutto

=GIORNI.LAVORATIVI.TOT(A42;INDIRETTO("A"&CONTA.NUMERI(A42:A72)+41);AH7:AH16)