XL 2010 compter uniquement les cellules avec le format 00:00:00

[chac10]

Bonjour,

Je rencontre un problème insoluble 🙂 en tous cas pour le moment !!
En effet, je souhaiterais compter le nombre de cellule avec le format heure et surtout avec la valeur 00:00:00
sur une plage de donnée.
Aujourd'hui , excel confonds le format heure et le format nombre 0
Donc lorsque je fais la formule qui suit nb.si(plage de donnée; "00:00:00"), il me donne le même résultat que si je faisais nb.si(plage de donnée; 0)
Je vous remercie pour votre aide.

Cordialement,
Chac10

 

[sylvanu]

Bonjour Chac,
Je ne connais aucune fonction XL qui différencie les formats. Mais peut être existe t-elle. 🙂
Solution : passer par une fonction perso :

Function CompterHeureNulle(Plage)
    Dim c, N%: N = 0
    For Each c In Plage
        If c = 0 And c.NumberFormat = "h:mm:ss" Then N = N + 1
    Next c
    CompterHeureNulle = N
End Function

Syntaxe : =CompterHeureNulle(D10:H10)
voir PJ.

 

[job75]

Bonjour chac10,

Voyez le fichier .xlsm joint et cette fonction VBA :

Function S(r As Range)
For Each r In r
    If r = 0 And r.Text Like "*:*" Then S = S + 1
Next
End Function

Fonctionne avec les formats heures 00:00:00 ou 00:00 ou 0:0.

A+

 

[chac10]

Bonjour chac10,

Voyez le fichier .xlsm joint et cette fonction VBA :

Function S(r As Range)
For Each r In r
    If r = 0 And r.Text Like "*:*" Then S = S + 1
Next
End Function

Fonctionne avec les formats heures 00:00:00 ou 00:00 ou 0:0.

A+

Bonjour Job 75,

ca c'est du job 🙂 merci beaucoup, pour ta fonction et celle de sylvanu, elles sont parfaites.
Je suis gâté.

chac10

 

[chac10]

Bonjour Chac,
Je ne connais aucune fonction XL qui différencie les formats. Mais peut être existe t-elle. 🙂
Solution : passer par une fonction perso :

Function CompterHeureNulle(Plage)
    Dim c, N%: N = 0
    For Each c In Plage
        If c = 0 And c.NumberFormat = "h:mm:ss" Then N = N + 1
    Next c
    CompterHeureNulle = N
End Function

Syntaxe : =CompterHeureNulle(D10:H10)
voir PJ.

Bonjour Sylvanu,

idem au top, c'est vrai que j'ai cherché un petit moment.
J'ai même tenté une approche par la couleur de la mise en forme conditionnelle.
Mais cela ne fonctionnait pas, il faudrait ajouter une colonne supplémentaire, chose que je ne peux pas faire.
Merci beaucoup encore , vous m'avez facilité la tâche car vos fonctions sont efficaces !!

Chac10

 

[chac10]

Sylvanu, Job 75,

j'ai un problème lorsque je fais la moyenne d'une série d'heure.
En effet, je souhaiterai ne pas tenir compte des heures qui sont à zéro, car les personnes sont tous simplement absente. C'est un indicateur pour moi, car il me permet d'avoir une idée du temps passée sur une tâche.
Le fait qu'une personne soit la ou non à une implication sur mon analyse.
un temps de zéro n' a pas de sens en soi, or dans la moyenne classique , ce temps nul est pris en compte ce qui par logique réduit le temps moyen. car le nombre de personne ne change pas.

Est ce qu'il existe une fonction permettant de faire cette moyenne sans prendre en compte les valeurs à zero (format heure) . D'où d'ailleurs ma demande ci dessus, je préférais faire une formule unique plutôt que d'imbriquer que celle que vous m'avez transmise avec brio d'ailleurs 🙂

en vous remerciant,

Chac10

 

[sylvanu]

Vous disposez de la fonction Moyenne.si.
Vous pouvez faire dans votre fichier :

=MOYENNE.SI(F3:F9;">0";F3:F9)

Fonction MOYENNE.SI - Support Microsoft

Renvoie la moyenne (arithmétique) de toutes les cellules d’une plage qui répondent à des critères donnés.

support.microsoft.com

 

[sylvanu]

Ou encore mieux avec une fonction perso qui tient compte du format :

Function MoyenneSiNonNulle(Plage)
    Dim c, N%, S: N = 0: S = 0
    For Each c In Plage
        If c <> 0 And c.NumberFormat = "h:mm:ss" Then
            N = N + 1
            S = S + c
        End If
    Next c
    If N > 0 Then
        MoyenneSiNonNulle = S / N
    Else
        MoyenneSiNonNulle = ""
    End If
End Function

Syntaxe : =MoyenneSiNonNulle(D10:H10)

 

[chac10]

Ou encore mieux avec une fonction perso qui tient compte du format :

Function MoyenneSiNonNulle(Plage)
    Dim c, N%, S: N = 0: S = 0
    For Each c In Plage
        If c <> 0 And c.NumberFormat = "h:mm:ss" Then
            N = N + 1
            S = S + c
        End If
    Next c
    If N > 0 Then
        MoyenneSiNonNulle = S / N
    Else
        MoyenneSiNonNulle = ""
    End If
End Function

Syntaxe : =MoyenneSiNonNulle(D10:H10)

Pas mal du tout merci Sylvanu !!
J'ai une dernière requête , j'ai un fichier de suivi journalier construit jour par jour de manière horizontale
En fin de semaine, je fais une synthèse , c'est pourquoi j 'ai besoin de faire des moyennes de deux temps principaux. Avec la fonction moyenne si, je constate qu'elle fonctionne par plage ce qui pose problème car elle prends donc des éléments dont je ne veux pas.
Y aurait il un moyen de contourner le problème, pour avoir la moyenne de la semaine et qu'elle ne compte que les heures <> de zéro. ( avec le fichier joint tu comprendras mieux je pense 🙂

En utilisant ta formule , je parviens à obtenir un résultat.
Mais il y a peut- être un moyen plus simple dont je n'ai pas la connaissance?
Chac 10

 

[sylvanu]

Re,
J'ai l'impression que tout ce qu'on a fait ce matin n'a servi à rien.
Une nouvelle fonction :

Function MoyParSemaine(J1 As Range, J2 As Range, Optional J3 = 0, Optional J4 = 0, Optional J5 = 0, Optional J6 = 0, Optional J7 = 0)
    Dim c, N%, S: N = 0: S = 0
    On Error Resume Next
    If J1 <> 0 Then N = N + 1: S = S + J1
    If J2 <> 0 Then N = N + 1: S = S + J2
    If J3 <> 0 Then N = N + 1: S = S + J3
    If J4 <> 0 Then N = N + 1: S = S + J4
    If J5 <> 0 Then N = N + 1: S = S + J5
    If J6 <> 0 Then N = N + 1: S = S + J6
    If J7 <> 0 Then N = N + 1: S = S + J7
    If N > 0 Then
        MoyParSemaine = S / N
    Else
        MoyParSemaine = ""
    End If
End Function

Syntaxe : =MoyParSemaine(J7;Y7;AL7) avec nombre de paramètres compris entre 2 et 7, pour faire une semaine.

 

[chac10]

Sylvanu ,
Tout ce que l'on a fait a servi !!!
La moyenne si est déjà en place pour exemple.
La première me sera utile peut être dès à présent ou un jour ou l'autre , elle est top et la je vais découvrir celle que tu as composé. Je choisirai entre les deux.

Et crois moi, ca m'instruit beaucoup !!!

Je te remercie énormément pour ton soutien.
Chac10

 

[chac10]

Re,
J'ai l'impression que tout ce qu'on a fait ce matin n'a servi à rien.
Une nouvelle fonction :

Function MoyParSemaine(J1 As Range, J2 As Range, Optional J3 = 0, Optional J4 = 0, Optional J5 = 0, Optional J6 = 0, Optional J7 = 0)
    Dim c, N%, S: N = 0: S = 0
    On Error Resume Next
    If J1 <> 0 Then N = N + 1: S = S + J1
    If J2 <> 0 Then N = N + 1: S = S + J2
    If J3 <> 0 Then N = N + 1: S = S + J3
    If J4 <> 0 Then N = N + 1: S = S + J4
    If J5 <> 0 Then N = N + 1: S = S + J5
    If J6 <> 0 Then N = N + 1: S = S + J6
    If J7 <> 0 Then N = N + 1: S = S + J7
    If N > 0 Then
        MoyParSemaine = S / N
    Else
        MoyParSemaine = ""
    End If
End Function

Syntaxe : =MoyParSemaine(J7;Y7;AL7) avec nombre de paramètres compris entre 2 et 7, pour faire une semaine.

Bravo, c'est magnifique, tu m'as simplifié la tâche grave.
Merci beaucoup, ta fonction marche parfaitement.
Il y a possibilité sur le site de parler en privé ?

 

[sylvanu]

Re,
Oui, mais le public offre l'avantage d'être plus souple, en particulier pour les PJ.
Pensez à clore ce post en Résolu si c'est fini. C'est mieux pour les lecteurs futurs.

 

[job75]

Pour la dernière requête on peut utiliser la fonction MOYENNE.SI.ENS, fichier joint :

- en AQ7 =MOYENNE.SI.ENS(D7:AO7;D7:AO7;">0";D5:AO5;"Temps 1")

- en AR7 =MOYENNE.SI.ENS(D7:AO7;D7:AO7;">0";D5:AO5;"Temps 2")

 

[chac10]

Sylvanu, je te remercie grandement.
Bon week