Bonjour à tous,

Je fais face à un soucis de calcul.

Imaginons que j'ai plusieurs activités, toutes affiliées à une certaine durée d'exécution (en hh:mm:ss), et toutes executables plusieurs fois.

Par exemple : l'activité 1C dure 00:52:00, l'activité 2C dure 01:05:00, l'activité 3C dure 01:12:00, l'activité 4C dure 01:22:00, etc

A cela s'ajoute les disponiblités au planning qui vont de 01h à 04h30 par tranche de 00:30:00. Je dispose donc de 01:00:00, 01:30:00, 02:00:00, 02:30:00, 03:00:00, 03:30:00, 04:00:00, 04:30:00

Je cherche à faire les calculs suivants :

Calculer toutes les combinaisons d'activités possible pour chaque catégorie de durée disponible, je prend l'exemple pour 03:30:00 :

Je voudrais que cela m'affiche toutes les combinaisons possibles de 1 à 4 activités avec la durée totale qui leur est associée, en sachant qu'une même activité peut être réalisée plusieurs fois :

• 1C+1C+1C+1C = 03:28:00
• 1C+1C+1C+2C = 03:41:00
• 1C+1C+2C+2C = 03:54:00
• 1C+2C+2C+2C = 04:07:00
• 2C+2C+2C+2C = 04:20:00
• .........
• 3C+3C+2C = 03:29:00
• .........
• 4C+4C = 02:44:00

Et que pour chacune des durées associées, j'ai une comparaison par rapport au temps attendu, par exemple pour la combinaison 1C+1C+1C+1C qui dure 03:28:00 cela m'affiche dans une case "Conforme" écrit en vert car la durée totale de la combinaison est inférieure à 03:30:00, pour la combinaison 1C+2C+2C+2C qui dure 04:07:00 cela m'affiche dans une case "Non Conforme" écrit en rouge et ainsi de suite pour chaque combinaison d'activités et également pour chaque combinaisons de durées allant de 01:00:00 à 04:30:00. Car la combinaison 2C+2C+2C+2C qui dure 04:20:00 est "Non Conforme" pour toutes les tranches horaires de 01:00:00 à 04:00:00 mais est "Conforme" pour la tranche horaire de 04:30:00

Le but est de savoir comment je peux agencer mes fabrications par rapport aux temps alloués, j'espère avoir réussi à être clair.

Je vous remercie grandement par avance pour le temps consacré à la lecture et à la resolution du problème auquel je fais face

AB

Salut,

Tu devrais regarder du côté du problème du cas à dos, ça ressemble à ce que tu décris : https://fr.wikipedia.org/wiki/Probl%C3%A8me_du_sac_%C3%A0_dos

Sinon ça devrait pouvoir se faire assez facilement en VBA comme tu n'as pas beaucoup de possiblités, voire même avec les formules mais les calculs avec des temps peuvent être capricieux (ou alors je m'en servais mal).

Hello,

Merci pour ta réponse.

Effectivement, le problème du sac à dos semble assez similaire en certains points.

Le soucis étant que je n'ai jamais fait de VBA sur Excel et que je n'ai aucune idée de commencer renseigner mes formules pour obtenir les calculs que je souhaite

Pourrais-je avoir un début d'ébauche ou quelque chose ?

Merci

AB

Pour le VBA il y a des pseudo codes dans l'article wiki et tu peux regarder ce tuto : https://openclassrooms.com/fr/courses/825502-analysez-des-donnees-avec-excel/822888-premiers-pas-en-vba

Si tu veux le faire avec des formules doit y avoir moyen de faire ça en brute force : générer toutes les combinaisions possibles d'activité pour sommer les temps que ça prendrait et ensuite comparer aux différents créneaux pour voir si ça rentre.

Les combinaisons possibles sont :

1+1+1+1

1+1+1+2

1+1+1+3

1+1+1+4

1+1+2+1

1+1+2+2

etc...

Pour chaque terme de la somme on cherche à quelle suite il correspond, le dernier c'est facile il fait +1 à chaque fois et va de 1 à 4 ça fait penser à du modulo et effectivement si on considère n>=0 le numéro du calcul alors la formule (n % 4)+1 va donner le bon truc.

Pour les autres termes il y aura aussi de la répétition mais pas à chaque fois, ça se fera bien avec la partie entière pour "fixer" la valeur, par exemple pour le troisième terme ce sera (Ent(n/4) % 4) +1. Pour les autres termes il faudra changer un peu les valeurs, ce sera sans doute n/16 et n/64 à utiliser.

Maintenant pour faire ça dans Excel on va utiliser les fonctions MOD et ENT pour faire les calculs précédents, LIGNE qui va nous permettre d'avoir un calcul par ligne (on va en gros considérer n = ligne actuelle - ligne du premier calcul) et enfin DECALER qui permet de lire la valeur d'une cellule décalée d'un certain nombre de lignes et de colonnes par rapport à une cellule de référence.

Dans les cellules A2, B2, C2 et D2 tu mets les durées des activités 1, 2, 3 et 4 respectivement, ce seront ces cellules qui vont être utilisées avec DECALER.

Ensuite tu pourras décomposer un peu comme tu veux Mais chaque ligne va correspondre à un calcul et je te donne la formule qui te permet de lire la valeur du quatrième terme de la somme :

=DECALER($A$2;0;MOD(LIGNE()-LIGNE($D$5);4))

J'ai mis cette formule en D5. Pour détailler un peu on peut dire (je pose des variables pour expliquer chaque morceau) :

• n = LIGNE()-LIGNE($D$5) renvoie le numéro du calcul, LIGNE() renvoie le numéro de ligne de la cellule actuelle et LIGNE($D$5) renvoie le numéro de ligne de D5 (celle du premier calcul) ;
• m = MOD(n;4) fait l'opération n % 4 (j'ai viré le +1 que j'avais écrit tout à l'heure car je veux aller de 0 à 3) ;
• DECALER($A$2;0;m) va partir de la cellule A2 et lire la valeur de la cellule qui est décalée de 0 lignes et m colonnes, comme m varie de 0 à 3 on peut lire les cellules de A2 à D2.

Tu peux étendre cette formule vers le bas et tu verras les temps correspondants à chaque activité avec la rotation 1C, 2C, 3C, 4C, 1C, ...

Liens de la doc vers les formules si tu veux plus de détail :

https://support.office.com/fr-fr/article/mod-mod-fonction-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3

https://support.office.com/fr-fr/article/ent-ent-fonction-a6c4af9e-356d-4369-ab6a-cb1fd9d343ef

https://support.office.com/fr-fr/article/fonction-ligne-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d

https://support.office.com/fr-fr/article/fonction-decaler-c8de19ae-dd79-4b9b-a14e-b4d906d11b66