[VB6 -> VBA]Addition vers le haut ET vers le bas sur un même colonne

Signaler
-
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
-
Bonjour,

J'espère poster ce message au bon endroit j'ai cherché le thème "Excel sans VBA" en vain...

http://www.developpez.net/forums/attachment.php?attachmentid=116153&d=1366225529

Tout est dit dans l'image (je m'étonne aussi de ne pas avoir trouvé le moyen d'attacher le ficher .xls)

Je voudrais donc calculer pour chaque trajet le nombre d'heure effectuées à partir de 22h jusque 6h. Puis entre chaque jour additionner ce qu'on a obtenu depuis 22h jusque 6h.

Dans l'expemple : en N3 on a 4:20 de travaillé dans le trajet de la ligne 3

En ligne 5 obtient en N 3:20 effectués jusque 6h

Dans la ligne 4 on est à cheval entre 2 jours donc on calcul vers le haut (depuis 22h) et vers le bas (jusque 6h) le travail de nuit. (le résultat est d'ailleurs 7:40 et non 6:20!)

Attention il peut y avoir 1 ou plusieurs lignes dans une journée!

j'avais trouvé cette formule (à mettre en N10)

=SOMME(INDIRECT("N"&EQUIV(B11-1;B$1:B11)):N9)+SOMME(INDIRECT("N"&EQUIV(B11+1;B:B)-2):N11)

mais ça additionne toute la journée du 3/04 et toute la journée du 4/04

Ce que je voudrais c'est additionner la journée du 3/04 QUE à partir de 22h jusque le 4/04 à 6h.

pour le calcul du trajet on a par exemple en N5 sachant que Y1="22:00" et AA1="6:00"

SI(OU(ET(M5<>"";M5"";L5=Y$1);T5;SI(ET(J5<>"";J5"";M5>Y$1);M5-Y$1;"")))

Cette formule me va donc inutile de s'attarder dessus!

J'espère avoir été assez clair. N'hésitez pas à me poser vos questions si besoin

Merci d'avance!

Jojo

24 réponses

Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
234
Bonjour,
Si tu veux de l'aide :
1) ne nous invite pas à ouvrir un lien pour comprendre. D'autant que ce lien-là ne peut être ouvert que si l'on est inscrit sur le site concerné !
2) reprends donc ton exposé, mais de manière purement technique, concise et claire


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviendrai que si nécessité de la compléter.
Bonjour,

Voici quelques explications avec le classeur exemple (il y a une erreur en [N4], c'est "7:40" et non 6:20!

http://cjoint.com/?0DrnFN3EwnF

J'ai en colonne [J] le début du trajet. En [L] et [M] la fin du trajet, si [L] est rempli [M] sera vide et vice versa.

En fonction des heures de début et fin de trajet je veux calculer les heures dans la tranche 22h/6h. On a donc en [N] : le nombre d'heures effetuées par trajet dans cette tranche. Jusque la tout va bien j'ai trouvé la formule.

Ce que je voudrais donc c'est le calcul vers le haut et le bas pour chaque jour entre 22h00 et 6h00.

Comme le nombre de lignes varie chaque jour, faire la somme N3+N5 ne sera pas suffisant.

J'ai bidouillé un peu et j'ai trouvé

SOMMEPROD(($B$2:B3B3)*($J$2:J3>6/24)*$N$2:N3)+SOMMEPROD((B5:$B$8999=B5)*((J5:$J$8999<6/24))*N5:$N$8999);"")

(à condition d'avoir la date inscrite en [B] pour chaque trajet)

Comme je ne maitrise pas très bien les fonctions SOMMEPROD ET MIN/MAX, c'est un peu chaud. Je me suis lancer sur une piste (et je suis quasi sur que cela peut fonctionner) voila avec quoi j'ai commencer

= SOMME(INDIRECT("N"&EQUIV(B12-1;B$1:B12)):N10)+SOMME(INDIRECT("N"&EQUIV(B12+1;B:B)-2):N12)


Cette formule que j'ai faite est partiellement bonne puisqu'elle calcule vers le haut et vers le bas qu'une seule journée en haut et une en bas mais je n'arrive pas à ajouter comme condition "calcule vers de haut depuis 22h au jour précédent et vers le bas jusque 6h". Je pense qu'en fusionnant la formule de SOMMEPROD avec la mienne on obtiendra THE bonne formule!!


Autre précision la fomule que j'ai faite fonctionne avec, dans la colonne [B], (en B10 par exemple)

=SI(A10<>"";INDIRECT("B"&EQUIV(9^9;$B$1:B10))+1;"")

et c'est cette formule que je veux garder en [B]

J'espère avoir été clair dans mes explications

Un grand merci d'avance !!!

Cordialement
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
234
Il n'y a pas plus sourd que celui qui ne veut pas entendre !
Encore un lien ... et vers un classeur (donc un exécutable), en plus !
Bonne chance.


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviendrai que si nécessité de la compléter.
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
18
Pas trop certain de tout comprendre et je ne vais pas aller chercher ton fichier. Si tu pouvais inscrire quelques données pour qu'on comprenne bien, ce serait plus facile pour ceux qui aident.

Quoiqu'il en soit, au cas où ça pourrait t'aider, voici une petite formule simple pour calculer l'ecart entre 2 heures, même si ce sont des heures de nuit qui changent donc de jour.

A B C
1: Début Fin Temps écoulé
2: 22:00 06:55 08:55

La formule en C2
=($B2-$A2+($A2>$B2))


MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Oups! oui apparement je n'ai pas compris ! Désolé je pensais qu'un petit classeur exemple pouvait aider!

C'est un peu dur (et long) à expliquer donc je voulais illustrer...

Sinon avec les explications que j'ai donné tu arriverai à quelque chose ?

Merci

Bonjour,

Il y a un formatage spécial (que j'ai oublié et que je n'ai pas envie de chercher) quand les horaires couvrent deux jours.

Si tu le fais par VBA, tu dois tester les fins de journées et additionner la première journée, du début jusqu'à minuit (ou peut-être 23:59:59) et la seconde journée de minuit à la fin.
Merci MPi mais pour ce calcul j'avais réussi à trouver. je vais tenter d'explique en faisant un petit tableau comme tu l'as fait.


| A | B |...J.........L......M......N
1|...|........|..............................
2|...|01/04/08| 14:40....21:30...............
3|...|........| 21:30.............2:20...4:20
4|14 |........|..........................7:40
5|...|02/04/08| 2:40.............7:00...3:20
6|...|........| 8:40............12:00

(Désolé pour les points j'ai voulu faire avec des espaces mais ça fausse l'alignements des colonnes)

A= numéro de semaine (toujours affiché sur la ligne ou je souhaiterai avoir le calcul c'est à dire une ligne au dessus de la date affichée en B)
J= heure de début de trajet
L et M= heure de fin de trajet
N= 2 formules en une : - le calcul des heures éffectuées dans la tranche 22h/6h pour chaque trajet (cette formule ça va, je l'ai trouvée)
- lorsque l'on est à cheval entre 2 jours (c'est a dire lorsqu'il y a le numéro de semaine affiché en A) on additionne toutes les heures des trajets entre les 2 jours ou on se trouve.
Par exemple en N4 on additionne les heures entre 22h le 1/04 et 6h le 2/04, donc N3+N5. Le problème c'est que le nombre de trajets varie chaque jour donc on ne peut pas se contenter de faire betement =N3+N5.

Voila ce que je voudrais c'est la formule pour trouver "7:40" en N4 et que je pourrai copier vers le bas. (quitte à faire des références circulaire!)

N'hésitez pas à me poser vos questions.

Encore désolé pour les liens, j'ai jamais posté de choses sur les forums de ma vie. Depuis une semaine je me suis inscrit sur 7 en même temps pour résoudre ce problème. D'un forum à l'autre ça change : personne ne veut rien faire s'il n'y a pas d'exemple, ailleurs, c'est l'inverse, d'autres veulent une page d'explication, d'autres 2-3 lignes... tout ça pour 400 vues et que 2 réponses sur ces forums non constructives... Donc désolé si je n'ai pas compris de suite ce que vous vouliez !

Merci de prendre le temps de me lire en tous cas !!
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
234
de toutes manières, sjojo59
lorsque l'on écrit le 17-04-2013, pour ce même problème :
Je me permet d'insister je dois rendre ce dossier lundi. Merci d'avance !

C'est déjà trop tard, car la journée de travail du Lundi 22 avril est déjà terminée.
A moins que tu n'aies menti "là-bas" ?
Bon... si c'est trop tard, n'en parlons plus.



________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviendrai que si nécessité de la compléter.
Non, je me suis mal exprimé c'est un dossier urgent et important où je me suis fixé un planning : en gros, avoir trouvé cette formule aujourd'hui pour éviter d'avoir du retard pour les autres formules à trouver ... Donc, oui j'ai pris un raccourci en parlant ainsi et je répète c'est urgent et très important. (même se c'est 2 points ne regardent que moi je te l'accorde ...

Ce n'est pas trop tard, je voudrais trouver cette formule au plus vite ...

Merci
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
234
Ah ! Un "raccourci" .... ! Bon ! Bon à savoir ...


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviendrai que si nécessité de la compléter.
Désolé mais T'es la pour m'aider ou pour faire que des remarques ?
Messages postés
18038
Date d'inscription
lundi 7 décembre 2009
Statut
Modérateur
Dernière intervention
11 avril 2018
234
Moi ? Oh ... facile ... juste pour me faire une petite idée de à qui j'ai exactement affaire, avant de dépenser mon temps ...
Mais là .. j'ai sommeil.
Bonne nuit.


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" facilitera les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement répéter son contenu. Je n'interviendrai que si nécessité de la compléter.
Ok ok Bonne nuit aussi à toi. Je suis peut etre un peu à cran mais ça fait un an que je suis sur ce tableau et c'est la première fois que je demande de l'aide parce qu'une personne qui maitrise Excel (moi c'est de la bidouille) pourrait trouver cette formule en 10min. Moi je n'y arrive pas. Voila pourquoi je suis sur (maintenant) 8 forum et que j'insiste sur le caractère URGENT. Je ne vais pas supplier mais c'est limite !
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
18
Une idée comme ça...

Je pense que tu devrais inscrire la date à chaque ligne.
Si tu sais afficher les sommes d'heures pour chaque trajet, il ne reste que la somme d'heures de tous les trajets d'une journée, si j'ai bien compris.

Alors, peut-être qu'en concaténant la date aux sommes trouvées (dans une colonne cachée, éventuellement), tu pourrais faire un SOMME.SI() en utilisant cette colonne comme 1er critère.

Mais peut-être que je patauge...


MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Mpi, malheureusement ça ne fonctionne malheureusement pas comme ça :

Je te remets le tableau pour mieux illustrer.

...A |....B...|...J........L.......M......N..
1|...|........|..............................
2|...|01/04/08| 14:40....21:30...............
3|...|........| 21:30............2:20....4:20
4|14 |........|..........................7:40
5|...|02/04/08| 2:40.............7:00....3:20
6|...|........| 8:40............12:00........
7|...|........|22:00.............2:00... 4:00
8|14.|........|..........................4:00
9|...|03/04/08| 9:00............12:00........

Si je fais comme tu dis, si j'ai bien compris, on additionnerai pour la journée du 02/04/08, dans la colonne N:

N5+N6+N7 soit 3:20 + "" + 4:00 = 7:20. En fait si tu fais ça tu n'as pas le travail de nuit entre le 1/04 (depuis 22h) et le 2/04 (jusque 6h) mais que la journée du 2/04 de 00h00 à 6h et de 22h à 00h00. C'est pour ça que j'ai mis (entre autre) une ligne séparatrice entre chaque jour (la ou on trouve systématiquement en colonne A le numéro de semaine) pour avoir le calcul du travail de nuit entre chaque jour.

Je ne sais pas trop quelle fonction utiliser, j'ai eu quelques solutions mais qui n'était pas complète. J'ai donc essayé de bidouiller, mais en vain. Je pense que je suis pas loin de la solution. Ça donne à peu près ça en N8 par exemple:

=SI(A8<>"";SOMMEPROD(((INDIRECT("J"&EQUIV(B9-1;B$1:B9)):J7)>22/24)*INDIRECT("N"&EQUIV(B9-1;B$1:B9)):N7)+SOMMEPROD((INDIRECT("J"&EQUIV(B9+1;B:B)-2):J9)<6/24)*INDIRECT("N"&EQUIV(B9+1;B:B)-2):N9);...

"..." veut dire que je connais la suite qui servira aux calculs par trajet (dans l'exemple, ça servira pour N3,N5,N6,N7,N9) donc inutile d'aller plus loin.

Je ne maitrise pas très bien SOMMEPROD mais ça me parait une très bonne fonction pour ce que je désire. Les "bouts" de formule "INDIRECT(...&€EQUIV(...):..." sont déja utilisés dans d'autres formules de mon tableau d'origine et fonctionne bien le problème c'est que je n'arrive pas à imbriquer ça avec le SOMMEPROD

Tu aurais une idée? Je ne vois pas où ça coince !!

Merci!
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
18
En fait, je pensais plutôt écrire la date "présumée" selon les heures entrées
Dans ton cas, le 2 avril avant 6h00 devrait avoir comme date le 1er avril (et ce n'est pas un poisson...)

Il s'agirait, dans une ou plusieurs colonnes, éventuellement masquées, de mettre les dates que ton calcul a besoin et laisser affichée la date normale qui ne servirait pas à ce calcul.

Donc, le 2 avril entre 2h40 et 7h00 tu as besoin d'une donnée du 1er avril, soit 2h40 à 6h00, mais j'imagine que tu as aussi besoin de la partie 6h00 à 7h00 pour le 2 avril... De là l'utilisation de plusieurs colonnes
3h20 comme total pour le 1er avril et 1h00 pour le calcul du 2 avril

C'est certain qu'avec une macro ce serait plus simple... mais bon...


MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Quel rapidité !!

(Lol le poisson d'avril )

Je ne comprend pas trop ce que tu veux dire en fait la ligne 5 correspond à un trajet en J on a l'heure du début et en M l'heure de fin, ça je pense que tu as compris. Comme en J5 le début commence après 00h00 on est au 2/04. Le trajet se fini à 7h00. Oui, pour le calcul on enlève ce qui est supérieur à 6h donc c'est bien 6:00-2:40=3:20.

Par contre à ou je ne comprends plus :

3h20 comme total pour le 1er avril et 1h00 pour le calcul du 2 avril


Les 1h en trop on ne les compte pas puisque pour la journée du 2/04 la tranche horaire 22h/6h s'arrête à effectivement à 6h. Donc on ne prend que la partie avant 6h soit 3:20 (La partie après 22h servira pour la nuit du 2/04 au 3/04)
Tu arriverais à manipuler la formule que j'ai écris ?
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
18
Laquelle des formules exactement ?


MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Je l'ai légèrement modifiée depuis tout à l'heure

la colonne N que j'ai donnée en exemple est la colonne W dans mon tableau d'origine.


En W8 donc:

=SI(A8<>"";SOMMEPROD((INDIRECT("J"&EQUIV(B9-1;B$1:B9)):J7>22/24)*INDIRECT("W"&EQUIV(B9-1;B$1:B9)):W7)+SOMMEPROD((INDIRECT("J"&EQUIV(B9+1;B:B)-1):J9<6/24)*INDIRECT("W"&EQUIV(B9+1;B:B)-1):W9);...

Je l'ai testée elle fonctionne partiellement : elle n'accepte pas les cellules vides en W ni les heures entre 6h et 22h en J

en fait pour prendre l'exemple, en J8 on a "8:40" donc c'est exclu de la tranche 22h/6h donc FAUX il me renvoie "#VALEUR!"

J'y suis presque !!!!!!!