SOMMEPROD + HEURE

**QuestVba** · 20/04/2017, 18h50

Bonjour,

Ben voilà que je n'y arrive pas.
J'ai les données suivantes (chaque ligne correspond à un dossier) :

Qui	Date	Heure Ouverture	Heure Fermeture
Lili	14/04/2017	14/04/2017 13:22	14/04/2017 13:22
Lili	14/04/2017	14/04/2017 13:23	14/04/2017 13:25
Lolo	14/04/2017	14/04/2017 13:03	14/04/2017 13:05
Lili	14/04/2017	14/04/2017 13:28	14/04/2017 13:29
Lolo	14/04/2017	14/04/2017 13:33	14/04/2017 13:35
Lili	14/04/2017	14/04/2017 13:38	14/04/2017 13:39
Lolo	14/04/2017	14/04/2017 13:43	14/04/2017 13:45
Lili	14/04/2017	14/04/2017 13:48	14/04/2017 13:49
Lolo	14/04/2017	14/04/2017 14:03	14/04/2017 14:05
Lili	14/04/2017	14/04/2017 14:28	14/04/2017 14:29
Lolo	15/04/2017	15/04/2017 14:03	15/04/2017 14:05
Lili	15/04/2017	15/04/2017 14:28	15/04/2017 14:29

Je cherche connaître le nombre de dossiers que Lili a ouvert le 14/04/2017 à l'heure '13'. J'ai essayé SOMMEPROD avec HEURE de 'Heure Ouverture' du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((Qui=A10)*(Date=$A$1)*(HEURE(DateO)=U9)*1)

A10 = Lili
A1 = 14/04/2017
DateO = Nom de la colonne Heure d'ouverture

Si vous avez des idées, ... je suis preneur.

**Marc-L** · 20/04/2017, 20h08

'soir,

un exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((A2:A13="Lili")*(C2:C13>=DATEVAL("14/4/2017")+TEMPSVAL("13:00"))*(C2:C13<DATEVAL("14/4/2017")+TEMPSVAL("14:00")))

En plus simple avec le nom recherché dans la cellule F2 et la date & heure de début en G2 :

=SOMMEPROD((A2:A13=F2)*(C2:C13>=G2)*(C2:C13<G2+1/24)) …

**QuestVba** · 21/04/2017, 11h38

Hello, Marc-L !
C'est IMPEC.

Et en plus, je n'ai pas une mais bien deux solutions. Et surtout, je viens d'en savoir plus. Merci

**Marc-L** · 21/04/2017, 12h23

Merci !

La solution est en fait unique car c'est juste la même logique pour rechercher une plage horaire pour une personne …

**QuestVba** · 23/04/2017, 17h23

Re-,

J'avais posé une question avec ces données :

Qui	Date	Heure Ouverture	Heure Fermeture
Lili	14/04/2017	14/04/2017 13:22	14/04/2017 13:22
Lili	14/04/2017	14/04/2017 13:23	14/04/2017 13:25
Lolo	14/04/2017	14/04/2017 13:03	14/04/2017 13:05
Lili	14/04/2017	14/04/2017 13:28	14/04/2017 13:29
Lolo	14/04/2017	14/04/2017 13:33	14/04/2017 13:35
Lili	14/04/2017	14/04/2017 13:38	14/04/2017 13:39
Lolo	14/04/2017	14/04/2017 13:43	14/04/2017 13:45
Lili	14/04/2017	14/04/2017 13:48	14/04/2017 13:49
Lolo	14/04/2017	14/04/2017 14:03	14/04/2017 14:05
Lili	14/04/2017	14/04/2017 14:28	14/04/2017 14:29
Lolo	15/04/2017	15/04/2017 14:03	15/04/2017 14:05
Lili	15/04/2017	15/04/2017 14:28	15/04/2017 14:29

Marc-L m'avait donné la réponse (à la question suivante : nombre de dossiers que Lili a ouvert le 14/04/2017 à l'heure '13') :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((A2:A13=F2)*(C2:C13>=G2)*(C2:C13<G2+1/24))

Une autre question :
Est-il possible de connaître le nombre de dossiers ouverts par Lili à l'heure '14' quel que soit le jour ? (ici, deux car un le 14/04 et un le 15/04)

**Marc-L** · 23/04/2017, 19h55

Exactement de la même manière en extrayant juste l'heure :

=SOMMEPROD((A2:A13="Lili")*(HEURE(C2:C13)=14)) …

**QuestVba** · 24/04/2017, 08h23

Merci ... encore à ... Marc-L. Et qui m'a permis de plus de découvrir une erreur dans une plage variable nommée.

NB : c'est ce qui était plus moins dans mon premier message.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMMEPROD((Qui=$A10)*(Date>=$A$2)*(Date<=$B$2)*(HEURE(HeureO)=AI$9)*1)}

**QuestVba** · 24/04/2017, 19h51

Le retour ... de la question.

Je viens de calculer, pour une période (ex: 01/01/2016 > 31/12/2016 [sur base de Date]), le temps que Lili [sur base de Qui] a passé à ouvrir et fermer les fichiers [sur base de DateO et DateF] :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMMEPROD((Qui=$A18)*(Date>=$A$2)*(Date<=$B$2)*(DateF-DateO))}

Le nom de mes plages variables :

Qui : les différentes personnes qui travaillent sur ce projet
Date : toutes les dates où le contrôle s'est effectué
DateO : date et heure d'ouverture du fichier
DateF : date et heure de fermeture du fichier

Je cherche maintenant à trouver le temps - pour toute une période - qu'une personne a consacré à ce travail, ce qui pourrait se résumer à :

Pour Lili / JOUR 1 de la période / MAX(DateF)-MIN(DateO) +
Pour Lili / JOUR 2 de la période / MAX(DateF)-MIN(DateO) +
Pour Lili / JOUR 3 de la période / MAX(DateF)-MIN(DateO) +
Pour Lili / JOUR 4 de la période / MAX(DateF)-MIN(DateO) +
Pour Lili / JOUR 5 de la période / MAX(DateF)-MIN(DateO) +
Pour Lili / JOUR 6 de la période / MAX(DateF)-MIN(DateO) +
...

Je suis parti sur un truc du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMMEPROD((Qui=$A18)*(Date>=$A$2)*(Date<=$B$2)*(DateF-DateO))}

Mais cela ne fonctionne pas.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMMEPROD((Qui=$A18)*(Date>=$A$2)*(Date<=$B$2)*(MAX(DateF)-MIN(DateO)))}

N'est pas mieux.
Et j'en passe des meilleures ....

Bon je continue mais si jamais vous avez une piste, je suis preneur.

NB : pour trouver la donnée sur un jour j'ai fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=MIN(SI((Qui=$A10)*(Date=$A$1);DateO))}

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=MAX(SI((Qui=$A10)*(Date=$A$1);DateF))}

Et une bête soustraction mais cela ne résout pas mon problème

**Marc-L** · 25/04/2017, 17h17

As-tu tenté une somme d'une colonne moins la somme d'une autre ?
Même si cela parait bizarre pour des dates …

**QuestVba** · 25/04/2017, 18h50

Hello, Marc-L,

J'ai tenté ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMMEPROD((Qui=$A18)*(Date>=$A$2)*(Date<=$B$2)*(DateF-DateO))}

Maiscela ne fonctionne pas. Normal, car il faut prendre la première données DateO du jour moisn la dernière données DateF du même jour.
Je me suis donc orienté vers ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMMEPROD((Qui=$A18)*(Date>=$A$2)*(Date<=$B$2)*(MAX(DateF)-MIN(DateO)))}

Mais ce n'est pas concluant. Résultat : 11759213:59:35

Ben oui je suppose qu'il s'amuse à additionner toutes les soustractions, ce qui donne un résultat de malade. Le résultat final ne doit pas être loin mais il n'est pas tout proche.

**Marc-L** · 25/04/2017, 19h16

Je verrais plutôt un SOMMEPROD avec dans les critères la colonne Date - Heure de fermeture
moins un SOMMEPROD avec les mêmes critères sauf la colonne Date - Heure d'ouverture …

Et sinon en plus simple via une colonne supplémentaire effectuant déjà le calcul Heure de fin - Heure de début !

**QuestVba** · 25/04/2017, 19h44

Bon,

Je vais réfléchir à ceci

Je verrais plutôt un SOMMEPROD avec dans les critères la colonne Date - Heure de fermeture
moins un SOMMEPROD avec les mêmes critères sauf la colonne Date - Heure d'ouverture …

Par contre, pour l'ajout d'une colonne, je suis pas trop preneur :

J'ai déjà 248.498 lignes sur 14 colonnes
Je ne vois pas comment faire le calcul car sur une même journée Lili peut faire plusieurs job
- Job1 : 08:14:15 - 08:14:25
- Job2 : 08:14:26 - 08:14:31
- Job3 : 08:14:40 - 08:15:35
- Job4 : 08:16:15 - 08:17:22
- ...
Le but serait de faire la différence entre 08:17:22 et 08:14:15 sans tenir compte des 'pauses' (ex : entre 08:15:35 et 08:26:15)

**Pierre Fauconnier** · 26/04/2017, 10h05

Salut.

Je vois que tu as nommé tes plages( qui, ...)... En XL2010, tu aurais intérêt à travailler avec des tableaux de données (activer une cellule du tableau puis Insertion/Tableaux/Tableau). Tu pourrais alors utiliser des références structurées et te dispenser de nommer tes plages.

Tu te simplifierais la vie en ne reprenant que les heures (et non les dates et heures dans les colonnes début et fin)...

Pour les durées, tu pourrais faire SOMMEPROD((FERMETURE-OUVERTURE)*CRITERE1*CRITERE2...)

Par rapport à une formule donnée par Marc, tu aurais

=SOMMEPROD((D2:d13-C2:C13) * (A2:A13="Lili") * (HEURE(C2:C13)=14))

**Marc-L** · 26/04/2017, 11h37

Bonjour Pierre,

ta formule est bien plus simple !

**QuestVba** · 26/04/2017, 19h40

'Soir, Pierre,

Eh ben là, je n'ai pas tout compris.

Du coup, j'ai plusieurs questions :

Quel est l'avantage de travailler avec des références structurées ?
Est-ce que cela permet de remplacer mes plages nommées (variables) ?
Est-ce aussi un avantage quant à la lourdeur du fichier ? Et des calculs ?
J'ai pas bien vu la simplification. Je suis un peu honteux. Il faut que je prenne le temps de me concentrer.

Pour le moment, mes calculs sont les suivants :

Temps cumulé pour chaque job pour un jour (donc, on ne compte pas les 'pauses') :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMMEPROD((Qui=$A18)*(Date=$A$1)*(DateF-DateO))}

Temps passé pour le job pour un jour (de la première ouverture jusqu'à la dernière fermeture - donc, on compte les pauses) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=MAX(SI((Qui=$A18)*(Date=$A$1);DateF))-MIN(SI((Qui=$A18)*(Date=$A$1);DateO))}

Temps cumulé pour chaque job pour une période (donc, on ne compte pas les 'pauses') :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMMEPROD((Qui=$A18)*(Date>=$A$2)*(Date<=$B$2)*(DateF-DateO))}

Temps passé pour le job pour une période (de la première ouverture jusqu'à la dernière fermeture - donc, on compte les pauses) :
RIEN N'Y FAIT

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
{=SOMMEPROD((MAX(SI((Qui=$A18)*(Date>=$A$2)*(Date<=$B$2);DateF))-MIN(SI((Qui=$A18)*(Date>=$A$2)*(Date<=$B$2);DateO))))}
{=SOMMEPROD((GRANDE.VALEUR(DateF;1)-PETITE.VALEUR(DateO;1))*(Qui=$A18)*(Date>=$A$2)*(Date<=$B$2))}
{=SOMMEPROD((MAX(SI((Qui=$A18)*(Date>=$A$2)*(Date<=$B$2);DateF))-MIN(SI((Qui=$A18)*(Date>=$A$2)*(Date<=$B$2);DateO)))*(Qui=$A18)*(Date>=$A$2)*(Date<=$B$2))}

**Pierre Fauconnier** · 26/04/2017, 19h47

Hello

Envoyé par QuestVba

[...]

Quel est l'avantage de travailler avec des références structurées ?
Est-ce que cela permet de remplacer mes plages nommées (variables) ?
Est-ce aussi un avantage quant à la lourdeur du fichier ? Et des calculs ?
J'ai pas bien vu la simplification. Je suis un peu honteux. Il faut que je prenne le temps de me concentrer.

[...]

Tu peux remplacer les plages nommées par les références structurées. Du coup, tu n'as plus besoin de nommer des plages. Les références structurées se dimensionnent toutes seules (si tu ajoutes ou retranches des données, elles s'adaptent). Tu peux utiliser les références structurées en VBA. C'est hyper pratique. Ca n'enlève rien à la lourdeur du fichier

Envoyé par QuestVba

[...]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=MAX(SI((Qui=$A18)*(Date=$A$1);DateF))-MIN(SI((Qui=$A18)*(Date=$A$1);DateO))}

[...]

SOMMEPROD n'aime pas les SI().

Je ne sais pas ce que tu entends par pause... Je quitte 1/4 d'heure. Je reprends notre intéressante conversation tout à l'heure... ;

**QuestVba** · 26/04/2017, 20h17

Pierre,

J'en apprends (génial). Donc, si cela permet d'avoir le même rendu qu'avec mes plages nommées et DECALER, NBVAL, .... et tout le bazard, c'est super. Et en plus, dans un autre projet, je travaille avec VBA et des plages variables nommées. Quel boulet ! Moi, par ignorance.

Effectivement, j'ai compris que les SI, il fallait les abandonner. J'ai vu cela dans un document 'Somme et dénombrement conditionnel' de ... ben toi. Très éclairant.

Ah oui, tant que j'y suis, j'y ai vu une coquille. dans le point 'V-B. Retenir les montants pour les lignes qui satisfont aux conditions', il est indiqué :

Les constantes VRAI et FAUX renvoyées par Excel sont en fait des valeurs numériques.
VRAI = 1
VAUX = 0.

Tu vois, je lis attentivement.

Bon on revient au sujet. Que signifie les pauses ?

JOUR 1 :
Job1 : 08:14:30 - 08:14:45
Job2 : 08:15:00 - 08:15:20
Job3 : 08:15:40 - 08:16:00
Job4 : 08:16:05 - 08:16:25

JOUR 2 :
Job1 : 07:24:30 - 07:25:00
Job2 : 07:25:00 - 07:25:20
Job3 : 07:25:40 - 07:26:00
Job4 : 07:26:05 - 07:26:25

Temps cumulé pour chaque job (job par job) pour jour 1 :
00:00:15 + 00:00:20 + 00:00:20 + 00:00:20 = 00:01:15

Temps cumulé pour chaque job (job par job) pour jour 2 :
00:00:30 + 00:00:20 + 00:00:20 + 00:00:20 = 00:01:30

Temps passé au total pour jour 1 :
08:16:25 - 08:14:30 = 00:01:55

Temps passé au total pour jour 2 :
07:26:25 - 08:24:30 = 00:01:55

Temps cumulé pour chaque job (job par job) pour jour 1 et jour 2 :
00:00:15 + 00:00:20 + 00:00:20 + 00:00:20 + 00:00:30 + 00:00:20 + 00:00:20 + 00:00:20 = 00:02:45

Temps passé au total pour jour 1 et jour 2 :
(08:16:25 - 08:14:30) + (07:26:25 - 08:24:30) = 00:03:50

Et, c'est cette dernière formule que je cherche.

NB : désolé, mais je vais devoir partir sans connexion. Mais, dès demain, je reviens pour voir. Car je suis impatient d'en savoir plus. Surtout, que je vois que cela va pouvoir me servir à un autre moment.
déjà un grand merci,

**Pierre Fauconnier** · 27/04/2017, 06h36

Je ne plus trop quelles formules tu as utilisées pour obtenir tes résultats, notamment pour les temps totaux par jour (je ne pense pas que tu aies utilisé GRANDE.VALEUR(...). Je te mets les miennes. Si les tiennes diffèrent, peux-tu les remettre de façon synthétique? C'est toujours intéressant d'avoir plusieurs chemins. Evidemment, il faudrait ajouter les autres critères (par exemple, sur Lili comme dans certains de tes messages) mais ça risque de devenir compliqué pour les temps de pause et peut-être aussi pour les temps totaux par jour ou du tableau.

Dans la formule du temps cumulé de tableau, j'ai multiplié par 1 car SOMMEPROD demande une SOMME de PRODuits. Sans le *1, pas de produit donc pas de somme de produits... Logique. Tu n'auras pas ce *1 avec d'autres critères, comme sur le nom de la personne, par exemple.

Pour le temps des pauses, l'idée est de calculer les différences entre le début et la fin de la ligne précédente (=> DECALER()) pour autant que la date de la ligne précédente soit la même. La première ligne du tableau va donc tenter de soustraire le mot "fin" du premier début, ce qui produirait une erreur que l'on capture avec SIERREUR. La formule utilise SOMME et pas SOMMEPROD car SOMMEPROD n'aime pas trop les SI, SIERREUR, etc... Il faut donc la valider en matricielle (CTRL+SHIFT+ENTER), comme les autres formules qui n'utilisent pas SOMMEPROD (celles des temps totaux par jour)

Pour le temps total tableau, on prend le temps cumulé tableau et on y ajoute le temps des pauses. A valider également en matricielle.

Au passage, tu remarqueras que j'ai utilisé un tableau de données nommé t_Jobs, ce qui me permet d'utiliser les références structurées sans devoir créer des plages nommées dynamiques.

Cela étant dit, le calcul des temps de pause impose que les données soient triées d'une certaine manière, ce qui est un des sept péchés capitaux d'Excel. Ce n'est pas super pour la fiabilité des résultats, et j'aurais tendance à me diriger vers du VBA.

Nom : 2017-04-27_063451.png
Affichages : 1330
Taille : 58,4 Ko

Nom : 2017-04-27_063451.png
Affichages : 1330
Taille : 58,4 Ko

**Pierre Fauconnier** · 27/04/2017, 07h09

Si on ajoute un critère (ici, la personne qui a réalisé le job), les temps cumulés se calculent assez facilement. Pour le temps total par personne et par jour, ça devient plus délicat car il faut compter la nième plus grande valeur en tenant compte du critère ajouté (d'où le NB.SI.ENS(...)). Le temps des pauses doit tenir compte que la personne ET le jour sont les mêmes sur la ligne et sa précédente. Le temps total pour la personnes s'obtient en sommant temps cumulé et temps de pause. Ces formules imposent que le tableau soit trié sur la personne puis la date puis l'heure de début (péché capital, je le rappelle...)

Nom : 2017-04-27_070516.png
Affichages : 1326
Taille : 54,1 Ko

Nom : 2017-04-27_070516.png
Affichages : 1326
Taille : 54,1 Ko

**QuestVba** · 27/04/2017, 19h42

Attention, Pierre, c'est ma première réaction car tes deux messages contiennent énormément de données à assimiler.

Mais, je vois déjà le truc avec le tableau. C'est effectivement génial ! Donc, déjà rien que cela, c'est super. Un grand merci !!!!!!

Maintenant, je dois me consacrer à l'analyse des formules. Je reviens rapidement et mettrais en correspondance les deux chemins pour autant que j'arrive à autant de réussite.

A+

SOMMEPROD + HEURE [XL-2010]

Excel

Discussions similaires

Partager

Partager