Discussion :

[sjojo59]

Bonjour,

Une image, un exemple est mieux qu'un long discours !! J'ai donc mis ce que je voulais en exemple.

http://cjoint.com/?0DrnFN3EwnF

Quelques précisions tout de même : le nombre de lignes peut varier d'un jour à l'autre.
Il peut donc y avoir plusieurs cellules vers le haut ou/et vers le bas à additionner !
N'hésitez pas à me posez vos questions !

Un grand merci d'avance!!!

Jojo

[SfJ5Rpw8]

Vu que je n'ouvre que sous la torture les pièges joint.. je préfère un discours...

[sjojo59]

Pas de soucis ! Même si ça va être un peu dur à expliquer mais j'ai mis une image pour simplifier.

Dans la colonne [N] je voudrais calculer entre chaque journée (lignes grisées) la somme des heures à partir de 22h (donc recherche vers le haut) + les heures jusque 6h (recherche vers le bas). Sachant que le nombre de cellules vers le haut et vers le bas est variable !!

Merci.

[papouclo]

Bonjour,
Petite remarque : sauf erreur de ma part, le contenu de la cellule N4 devrait être 07:40 et non 06:20.
La durée pour chaque course comprise entre 22h et 6h peut s'obtenir avec ,en N2, la formule :

Code :

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

=SI(M2<J2;1+MIN(6/24;M2)-MAX(22/24;J2);MIN(6/24;M2)-MIN(6/24;J2)+MAX(22/24;M2)-MAX(22/24;J2))

à recopier vers le bas et en décochant dans les Options avancées la case : Afficher un zéro dans les cellules qui ont une valeur nulle.
Cette formule ne donne pas le résultat attendu pour les sommes partielles en N4, N8, N10.
Mais quelle est l'importance des sommes à trouver dans ces cellules ?
En supposant que ces heures de travail certainement plus difficile soient rémunérées de façon spécifique, on pourrait les totaliser en sommant les durées par course obtenues en N3, N5, N7,N11
indépendamment de ces sous-totaux difficiles à obtenir par formule,
d'autant plus qu'en l'absence de ces totaux partiels, il suffirait de demander SOMME(N2:N12).
Cordialement
Claude

[sjojo59]

Bonjour Claude,

Merci d'avoir répondu!

Oui le résultat à obtenir en N4 est bien 7:40. J'ai modifié l'exemple plusieurs fois et j'ai oublié de changé cette cellule!

En fait la formule que je voudrais obtenir est justement en N4. Pour calculer les heures de nuit pour chaque course j'avais trouvé une formule que je n'ai pas mis en exemple car dans l'exemple toute les colonnes du tableau original ne sont pas mise.

=SI(OU(ET(M4<>"";M4<AA$1);ET(L4<>"";L4<AA$1);J4>=Y$1);N4;SI(ET(J4<>"";J4<AA$1);AA$1-J4;SI(ET(M4<>"";M4>Y$1);M4-Y$1;"")))

AA1="6:00"
Y1="21:00"
N4 est le temps de travail complet dans la course


L'importance des sommes à trouver dans ces cellules : Je dois présenter un dossier le plus précis possible avec le moins de saisie possible il y a environ 2000 courses à saisir en colonne C, D et E. Dans le tableau original il y a 36 colonnes de calculs qui dépendent les une des autres ! + 2 feuilles récapitulatives en liaison...

Dans ces feuilles je dois justement avoir les heures de nuit effectuées entre chaque jour pour calculer au jour PUIS à la semaine et pour calculer d'autres heures.

Je pensais à EQUIV(INDIRECT(~vers le haut depuis 22h~)+EQUIV(INDIRECT(~vers le bas jusque 6h~) mais je ne sais pas comment rédiger cette formule.

Je ne sais pas très bien rédiger une formule avec MIN(...) et MAX(...) mais j'arrive à l'interpréter je pense que le cumul de EQUIV(INDIRECT(~MIN(...) et/ou MAX(...)~) il y a quelque chose à faire.

Jojo

[papouclo]

Bonjour,
Après un essai infructueux pour obtenir les totaux désirés en colonne N, j'ai obtenu ces résultats dans une colonne supplémentaire,
d'où un tableau légèrement modifié dans le classeur joint.
Ci dessous les modifications du tableau et les formules employées :
j'ai rajouté une ligne 2 vide et l'ai masquée d'où un aspect identique.

Dans la colonne B, j'ai changé la formule pour obtenir la date du jour non pas seulement sur la ligne de la première course de la journée mais sur les lignes de toutes les courses de la journée
En B3, on écrit donc la première date : mar 01/04/08 et en B4 la formule :

Code :

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

=CHOISIR((A4<>"")+(A4="")*((A3="")+2);"";MAX($B$3:B3)+1;MAX($B$3:B3))

à recopier vers le bas
La date du 02/04/08 n'est pas seulement écrite en B6 mais aussi en B7 et B8 même si cela ne se voit pas (utilisation d'une règle de MFC dans la colonne B pour revenir à la présentation précédente).

Dans la colonne N (titre changé en N1 pour distinguer de la prochaine colonne : Course entre 22h et 6h ou autre), la formule en N3 est celle que j'avais proposée dans le message précédent :

Code :

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

=SI(M3<J3;1+MIN(6/24;M3)-MAX(22/24;J3);MIN(6/24;M3)-MIN(6/24;J3)+MAX(22/24;M3)-MAX(22/24;J3))

à recopier vers le bas

Dans la colonne O ( titre en O1 : Durée de travail entre 22h et 6h), la formule en O3 est :

Code :

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

=SI(B3="";SOMMEPROD(($B$2:B2=B2)*($J$2:J2>6/24)*$N$2:N2)+SOMMEPROD((B4:$B$9000=B4)*((J4:$J$9000<6/24))*N4:$N$9000);"")

à recopier vers le bas
Cordialement
Claude

[sjojo59]

Bonjour,

Wow! Ça commence vraiment à ressembler à mes attentes ! Impressionnant!

Mais apparemment j'ai oublié de préciser :

Lorsqu'il y a un départ suivi juste après d'un retour c'est la colonne L qui remplace la colonne M : elle signifie que je n'arrete pas de travailler entre l'heure de dépôt des premiers clients.
Ex : le 1/04, que j'ai modifié, j'ai déposé les clients à 21:30 [K3] et je reprend les autres à 23:00 [L3]. A noter que lorsque [L3] n'est pas vide [L3]=[J4].

Donc pour le la première course la fin est à 23:00 donc nous sommes dans la tranche 22h/6h. Ce qui rajoute bien 1h en N3 si [M3]="23:00" mais non en [L3]. Désolé pour cet oubli !! Comme je vous disais je ne maitrise pas correctement le MIN/MAX(...) donc inserer la condition : "quand [L] est rempli alors calculer si on est dans la tranche 22h/6h" sera trop compliqué pour moi!

Pour le reste ça a l'air de fonctionner et vraiment encore MERCI !

Sinon je me suis posé quelques questions:
- Pourquoi avoir changer la formule pour la date en [B]?
- Pourquoi avoir insérer la ligne 2 et la cacher ?

Cela sert pour les colonnes [N] et [O]?


Est-ce possible de mettre le résultat dans la même colonne [N] par exemple cumuler vos 2 formules:

en [N5]

Code :

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

=SI(A5<>"";SOMMEPROD(($B$2:B4=B4)*($J$2:J4>6/24)*$N$2:N4)+SOMMEPROD((B6:$B$9000=B6)*((J6:$J$9000<6/24))*N6:$N$9000);SI(M5<J5;1+MIN(6/24;M5)-MAX(22/24;J5);MIN(6/24;M5)-MIN(6/24;J5)+MAX(22/24;M5)-MAX(22/24;J5)))

Cela fonctionne en [N5], mais pas pour les autres résultats

Je pense qu'il n'y aurait pas beaucoup de choses en plus à modifier, l'idée serait: (j'ai enlever la 2eme partie de la formule précédente qui ne nous intéressera pas ici pour une lecture plus facile.

Partie à coriger:
=SI(A5<>"";SOMMEPROD(($B$2:B4=B4)*($J$2:J4>6/24)*$N$2:N4)+SOMMEPROD((B6:$B$9000=B6)*((J6:$J$9000<6/24))*N6:$N$9000);...

Partie voulue:
=SI(A5<>"";SOMMEPROD(depuis 22:00)+SOMMEPROD(jusque 6:00);...

Je ne vois pas la difference entre la partie à corriger et la partie voulue ...

Cordialement

Jojo

[papouclo]

Rebonjour,
Je viens seulement de voir le retour.
Quelques explications:
La ligne 2 vide provient d'une erreur #VALEUR apparue dans un calcul à cause de l'appel d'une cellule de la ligne 1 contenant du texte; d'où l'emploi d'une ligne "tampon".
Mais comme j'ai changé certaines formules de calcul, il se pourrait que cette ligne ne soit plus nécessaire.

Le changement de la formule colonne B provient de la nécessité d'avoir pour chaque course la date correspondante en regard à cause de l'emploi de la fonction SOMMEPROD dans la formule de calcul colonne O.

La tentative de calcul de tous les résultats dans la seule colonne N au moyen d'une formule combinant les deux calculs ( calcul pour une course et somme pour la période 22h-6h ) conduit à un avertissement de référence circulaire.
En effet, le calcul de N5 ferait appel à la plage N6:N9000 donc à la cellule N9 - jusque là pas de problème - mais, le calcul de N9 ferait appel à la la plage N3:N8 donc à la cellule N5, donc à une valeur dépendant de N9 et là, bien sûr, Excel proteste.
Bon, je me pencherai demain sur la remarque en début de message mais ce soir...
Cordialement
Claude

[sjojo59]

Re!

Merci pour toutes ces explications!

Pour la ligne 2 effectivement, si c'est possible de l'enlever ça serait le pied!
Le tableau original est tellement lourd que tout à l'heure en essayant de rajouter celle ligne Excel a planté !!!

Pour le changement en [B], me douté que c'était pour la bonne cause, mais quand j’insère cette formule dans mon tableau ça ne va pas puisqu'il faut une date par jour. Mais s'il faut faire ainsi je changerai mes autres formules...

Pour ce qui est des références circulaires je pense que j'en ai déjà à toutes les colonnes (35) dans mon tableau original (et elle me donnent le resultat souhaité) donc je ne suis plus à une près !! Si ça peut me permettre d'avoir tout dans la même colonne... Je suis preneur !!

Je vous remercie beaucoup de l'attention portée à mon problème, surtout que je dois rendre ce dossier lundi et que je commence à y voir plus clair !!

Je suis sur 7 forum pour ce problème depuis 4 jours et vous êtes le seul à répondre pour l'instant vous comprenez que chacune de vos réponses me réjoui !!!

Cordialement

Jojo

[sjojo59]

Je pense être parti sur une bonne piste même si elle est incomplète

Code :

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

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

Avec cette formule en N10 je pars vers le haut et vers le bas en calculant TOUT ce que j'ai obtenu dans une seule journée vers le haut et une seule journée vers le bas. A condition que dans la colonne [B] on a (par exemple en B11)

Code :

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

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

Je pense qu'en "fusionnant" cette formule (en [N])

Code :

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

1
2
3
 
 
=SI(A5<>"";SOMMEPROD(($B$2:B4=B4)*($J$2:J4>6/24)*$N$2:N4)+SOMMEPROD((B6:$B$9000=B6)*((J6:$J$9000<6/24))*N6:$N$9000);SI(M5<J5;1+MIN(6/24;M5)-MAX(22/24;J5);MIN(6/24;M5)-MIN(6/24;J5)+MAX(22/24;M5)-MAX(22/24;J5)))

à celle que j'ai trouvé il y a moyen de trouver THE formule... Le hic c'est que je n'arrive pas à "fusionner" le SOMMEPROD avec EQUIV ...

Merci d'avance !

[sjojo59]

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.

Quelqu'un pourrait essayer de corriger ?

Merci