Calcul d'une somme ou d'une moyenne dans une plage horaire

[djf]

Bonjour

Dans le fichier ci-joint (généré à partir d'un fichier CSV), je souhaite réaliser une moyenne des valeurs de la colonne Item entre 7h et 19h(en fonction des horaires de la colonne Time bien sûr).
J'ai essayé un certain nombre de choses trouvées sur ce forum ou d'autres mais sans succès.
Par exemple ce type de formule ne fonctionne pas: =MOYENNE.SI.ENS(B1:B132;A1:A132;">="&E2;A1:A132;"<="&E3)
Sur le même principe, j'aimerai également réaliser une somme dans la même plage horaire.J'ai l'impression qu'il y a un problème avec le format de la date.
Merci d'avance pour votre aide.

 

[zeltron24]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Bonsoir djf,
Est ce que la moyenne doit se faire sur un jour précis ou sur la totalité ?

 

[Guest]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Bonjour,

Pour la moyenne: =SOMMEPROD((HEURE($B$4:$B$73)>=11)*(HEURE($B$4:$B$73)<=19)*$C$4:$C$73)/SOMMEPROD((HEURE($B$4:$B$73)>=11)*(HEURE($B$4:$B$73)<=19))

Pour la somme, préciser la demande

A+

 

[djf]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Merci pour vos réponses.

En fait, le fichier csv généré me donne toujours les résultats dans la même plage de cellule, mais la plage de comptage n'est pas forcément la même, d'où ma question.
Si je moyenne "manuellement", voici les cellules que je selectionne:
=MOYENNE(C4;C41:C73)

@Hasco: j'ai testé (en changeant la borne horaire de 11 en 7, car je compte à partir de 7h). Ca marche mais je n'obtiens pas le même résultats que la formule ci-dessus (0.780 vs 0.781).
Pour la somme, je souhaite faire exactement la même chose. Dans ce cas: = SOMME(C4;C41:C73)

@zeltron: dans ce cas la moyenne doit se faire entre 7h et 19h quelque soit le jour.


Autre question: Je dois également faire la moyenne (ou somme) entre 19h et 7h, j'imagine que je n'aurais qu'à changer les bornes horaires pour cela.

 

[R@chid]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Bonjour @ tous,
Un salut spécial @ Hasco (Ravis de te voir sur le forum)
Formule Matricielle,

=MOYENNE(SI((HEURE(B4:B73)>=19)+(HEURE(B4:B73)<=7);C4:C73))

@ valider par Ctrl+Maj+Entree
@ + +

 

[Guest]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

bonjour à tous,

N'apporte rien à la discussion sauf le plaisir de faire un grand Salut à @Rachid.
A+
A+++ l'ami
P.S. je savais bien qu'un des grands doués de la formule passerait par là!

 

[djf]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

On chauffe, mais ç'est pas encore ça

J'ai changé les valeurs de mon fichier pour y voir plus clair (système binaire, 0 entre 19h et 7h, et 1 entre 7h et 19h).
Je vous laisse regarder ds le fichier joint, j'ai appliqué les 2 formules proposées:
celle de Hasco est proche mais prend en compte les valeurs de 19hxx (19h15, 19h35 et 19h55) or je ne veux pas les inclure. Si possible, la formule doit prendre en compte jusqu'à 19h00 inclus.
Celle de R@chid compte à l'inverse de 19h à 7h, en incluant comme celle d'Hasco les valeurs entre 7h et 7h59.
J'ai essayé d''inverser les bornes horaires pour compte de 7h à 19h. Mais je ne comprends pas le résultat: on ne retombe pas sur la valeur de la formule d'Hasco.

 

[Guest]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

RE,

J'arrive à la même moyenne que ta moyenne manuelle avec:

=SOMMEPROD((TEMPS(HEURE(B4:B73);MINUTE(B4:B73);SECONDE(B4:B73))>=TEMPS(7;0;0))*(TEMPS(HEURE(B4:B73);MINUTE(B4:B73);SECONDE(B4:B73))<=TEMPS(19;0;0))*$C$4:$C$73)/SOMMEPROD((TEMPS(HEURE(B4:B73);MINUTE(B4:B73);SECONDE(B4:B73))>=TEMPS(7;0;0))*(TEMPS(HEURE(B4:B73);MINUTE(B4:B73);SECONDE(B4:B73))<=TEMPS(19;0;0)))

Je vais essayer de raccourcir la formule et reviendrai si j'y arrive.

En voilà une autre qui fait la même chose (matricielle à valider par CTRL+MAJ+ENTRER)

=MOYENNE(SI((TEXTE(B4:B73;"hh:mm:ss")>="07:00:00")*(TEXTE(B4:B73;"hh:mm:ss")<="19:00:00");C4:C73))

A+

 

[R@chid]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Bonsoir @ tous,
J'ai pas compris mais ça donne 1

=MOYENNE(SI((HEURE(B4:B73)<19)*(HEURE(B4:B73)>=7);C4:C73))

@ valider par Ctrl+Maj+Entree

Faut savoir jouer avec les >=, <=, > et < ...

@ + +

 

[djf]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Appliquées à mes données initiales, la formule longue de Hasco et la matricielle de R@chid me donne strictement le même résultat que ma moyenne "manuelle"
La matricielle de Hasco me donne un résultat approchant (différence au 100e) mais pas exactement identique.

Par contre pour changer la plage de comptage, j'essaye de:

jouer avec les >=, <=, > et < ...

Mais ça marche pas.

Avec les 3 dernières formules proposées, j'ai inversé les bornes horaires (ou jouer avec les >= <=...mais c'est pareil ?) pour compter entre 19h et 7h sur la même plage horaire.

=SOMMEPROD((TEMPS(HEURE(B4:B73);MINUTE(B4:B73);SECONDE(B4:B73))>=TEMPS(7;0;0))*(TEMPS(HEURE(B4:B73);MINUTE(B4:B73);SECONDE(B4:B73))<=TEMPS(19;0;0))*$C$4:$C$73)/SOMMEPROD((TEMPS(HEURE(B4:B73);MINUTE(B4:B73);SECONDE(B4:B73))>=TEMPS(7;0;0))*(TEMPS(HEURE(B4:B73);MINUTE(B4:B73);SECONDE(B4:B73))<=TEMPS(19;0;0)))

ou

=MOYENNE(SI((HEURE(B4:B73)>19)*(HEURE(B4:B73)<=7);C4:C73))

J'obtiens un magnifique #DIV/0! dans les 2 cas.

 

[R@chid]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Bonsoir @ tous,
J'arrive pas à bien saisir ta demande...
Essayer les deux...

=MOYENNE(SI((MOD(B4:B73;1)>7/24)*(MOD(B4:B73;1)<=19/24);C4:C73))

ou

=MOYENNE(SI((MOD(B4:B73;1)>=7/24)*(MOD(B4:B73;1)<19/24);C4:C73))

Toujours matricielles..

@ + +

 

[djf]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Je reviens avec une semaine de retard sur la dernière réponse de R@chid.

J'ai testé tes 2 formules: ça fonctionne. Pour faire la moyenne entre 7h et 19h.
Mais aucune ne permet de faire la moyenne entre 19h et 7h.

Pour être plus clair, j'ai modifié mon fichier, avec des couleurs matérialisant les plages à prendre en compte.
Et j'ai noté en bas les moyennes et sommes manuelles, et les matricielles proposées pour les moyennes.
J'ai mis aussi les sommes sous forme matricielles (qui ne fonctionne pas non plus entre 19h et 7h).

Voilà, j'espère que vous allez me comprendre et surtout résoudre mon problème

 

[Guest]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Bonjour,

Dernière tentative pour moi avec cette formule matricielle:

=MOYENNE(SI(((B4:B73-(ENT(B4:B73))>TEMPSVAL("19:00:00"))+(B4:B73-(ENT(B4:B73))<TEMPSVAL("07:00:00")))>0;C4:C73))

Dans le fichier joint j'ai mis trois colonnes qui, par ligne montre la logique de la matricielle.
Tu peux détruire ces 3 colonnes. date:heure - ENT(date:heure) enlève la partie entière (date) pour ne comparer que les heures. Le + dans la matricielle remplace un OU logique. si Ou l'heure est > 19 ou si elle est <7 .....

P.S. tempsval("19:00:00") peut être remplacé par (19/24)

A+

 

[R@chid]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Bonsoir @ tous,
Salut Hasco..
Oui bien sur, ma formule pour les bleues ne peut pas donner le bon résultat, car une heure ne peut en aucun cas être à la fois >=19 et <=7...
Alors une petite correction pour les bleues..

=MOYENNE(SI((HEURE(B4:B73)>=19)+(HEURE(B4:B73)<7);C4:C73))

@ valider par Ctrl+Maj+Entree

Pour les jaunes, ça fonctionne bien, du fait qu'on peut trouver des heures qui sont à la fois >=7 et <=19 ..

@ + +

 

[djf]

Re : Calcul d'une somme ou d'une moyenne dans une plage horaire

Merci beaucoup à tous les deux ! Tout fonctionne
Merci aussi Hasco pour le fichier didactique. Je viens de comprendre pourquoi mes essais n'ont pas fonctionné.
Le + était l'outil qu'il me manquait, pour résoudre le problème lié à la base horaire.

J'ai bien fait de m'arrêter par ici. En plus d'avoir trouvé une solution à ma question, je pense l'avoir à peu près comprise pour l'appliquer ailleurs.
J'aurais certainement d'autres problèmes à soumettre...mais je vais déjà mettre ça en pratique

@ +