XL 2019 Somme des écarts au temps de référence

[carlos]

Bonjour,
J'aimerias calculer dans la cellule B4 jaune la somme des écarts au temps de réference pour chaque.
colonne . Voir PJ
Merci

 

[M12]

Bonjour,
J'aimerias calculer dans la cellule B4 jaune la somme des écarts au temps de réference pour chaque.
colonne . Voir PJ
Merci

Bonjour,
Teste cette formule

=SOMMEPROD((B3)-(C4:AA4))

 

[carlos]

Merci beaucoup M12 mais j'ai des élements supplémentaires à préciser.
Tout ce qui n'est pas entre le temps mini et le temps maxi doit etre compté. Voir PJ
Et comment gérer les cellules vides? Ne pas les compter.

 

[M12]

Re,
Ton écart mini = 0:00:58
Ton écart maxi =1:00:02 --> C'est pas plutôt 0:01:02
si oui
A tester par macro

 

[carlos]

Re,
Oui c'etait une erreur:
Ta macro fonctionne super bien.
C'etait impossible de la faore sous forme de formule?
Merci beaucoup M12 et bonne journée

 

[mapomme]

Bonjour @carlos , @M12

Un autre essai avec une fonction personnalisée (voir formule en B4 à copier vers le bas):

=Ecart (xplage , xmini A , xmaxi )

• xplage est la plage des horaires
• xmini est la cellule de la référence mini
• xmaxi est la celule de la référence maxi

Comme absolument rien n'est précisé quand aux signes des écarts, j'ai considéré que si c'est inférieur à xmini alors l'écart est négatif et si c'est supérieur à xmaxi alors l'écart est positif. La somme des écarts peut donc être négative, nulle ou positive. La fonction tient compte du signe du résultat et renvoie un :

• un texte avec un signe - si la somme des écarts est négative
• un texte avec un signe + si la somme des écarts est positive
• un texte sans signe si l'écart est nul
• un texte vide si la ligne ne comporte aucun horaire

Excel ne gère pas les heures négatives (ou alors il faut changer l'origine du calendrier et ça je le déconseille fortement). C'est pourquoi, la fonction renvoie un texte "signé" et pas un nombre.

A partir de la somme des écarts en texte signé, il est simple de retrouver :

• La valeur absolue de l'écart : =TEMPSVAL(DROITE(B4;8))
• si c'est en plus, en moins ou nul : =GAUCHE(B4;1) (qui vaudra + ou - ou vide)

Le code de la fonction est dans Module1:

Function Ecart$(xplage As Range, xmini As Range, xmaxi As Range)
Dim min#, max#, t, x, ecartmin#, ecartneg#, ecartpos#, nbv&
   t = xplage.Value2: min = xmini.Value2: max = xmaxi.Value2
   For Each x In t
      If x = "" Then
         nbv = nbv + 1
      ElseIf x < min Then
         ecartneg = ecartneg + (min - x)
      ElseIf x > max Then
         ecartpos = ecartpos + (x - max)
      End If
   Next x
   If nbv = xplage.Cells.Count Then
      Ecart = "": Exit Function
   ElseIf Abs(ecartneg - ecartpos) < 0.000001 Then
      Ecart = Format(0, "hh:mm:ss")
   ElseIf ecartneg > ecartpos Then
      Ecart = "- " & Format(ecartneg - ecartpos, "hh:mm:ss")
   Else
      Ecart = "+ " & Format(ecartpos - ecartneg, "hh:mm:ss")
   End If
End Function

 

[JHA]

Bonjour à tous,

A essayer

JHA

 

[carlos]

Merci Mapomme, j'ai effectivement pas précisé que je voulais en valeur absolue les ecarts :
J'ai adappté ta formule et ca marche tres bien.
Function Ecart$(xplage As Range, xmini As Range, xmaxi As Range)
Dim min#, max#, t, x, ecartmin#, ecartneg#, ecartpos#, nbv&
t = xplage.Value2: min = xmini.Value2: max = xmaxi.Value2
For Each x In t
If x = "" Then
nbv = nbv + 1
ElseIf x < min Then
ecartneg = ecartneg + (min - x)
ElseIf x > max Then
ecartpos = ecartpos + (x - max)
End If
Next x
If nbv = xplage.Cells.Count Then
Ecart = "": Exit Function
Else
Ecart = Format(ecartpos + ecartneg, "hh:mm:ss")
End If
End Function
Merci aussi à JHA .
Bonne journée à tous

 

[carlos]

Bonjour,
je relance ce fil pour un créer une petite variant à partir de la proposiion de Mapomme:
Comment ne prendre en compte que les durée liées au temps de travail (en rouge dans PJ).
J'ai essayé d'uiliser d'ajouter une condition :
and cells(3,x.column).value
mais ca ne marche pas ... probleme d'objet?
Merci

 

[mapomme]

Bonjour @carlos ,

Voir le fichier joint. J'ai ajouté un paramètre qui est la plage des titres. J'ai fait cela assez vite. Faites des vérifications .

=Ecart (xPlageValeurs , xPlageTitres , xmini , xmaxi )

 

[mapomme]

J'ai essayé d'uiliser d'ajouter une condition :
and cells(3,x.column).value
mais ca ne marche pas ... probleme d'objet?

Version v1 :
x n'est pas un objet range mais une valeur (un nombre ou 'une valeur vide' du tableau des valeurs).
Une valeur n'a pas de propriété au sens objet (et donc pas de propriété Column).

Version v2 :
Pour repérer la même "colonne" dans le tableau v des valeurs et dans le tableau t des titres, on ne passe plus par une boucle For...Each mais par une boucle d'indice j qui parcourt toutes les colonnes du tableau v des valeurs. Pour un j donné, l'élément t(1 , j) nous donne directement l'information "Travail" ou "Repos".

 

[carlos]

Bonjour Mapomme,
Bravo, ca marche impeccable.
Merci aussi pour les explicaions pédagogiques sur les range.
Bon WE