VBA- Chiffres aléatoires avec conditions
Bonjour,
Le fichier ci-joint représente une feuille d'un fichier qui servira à optimiser le kilométrage des véhicules de locations en fonction des tournées.
Exemple (un peu parfait, juste pour comprendre le but du fichier) :
Un véhicule A a un kilométrage de 50 000 km, son contrat est de 100 000km sur 3 ans, il reste 18 mois avant la fin du contrat.
Un véhicule B a un kilométrage de 60 000 km, son contrat est de 100 000km sur 3 ans, il reste 10 mois avant la fin du contrat.
Le véhicule A est actuellement sur une tournée 1 qui nécessite en moyenne 5 000km/mois
Le véhicule B est actuellement sur une tournée 2 qui nécessite en moyenne 2 500km/mois
Lors de la remise au loueur :
Km véhicule A = 50 000 + 18*5 000 -100 000 = +40 000km d'écart par rapport au contrat
Km véhicule B = 60 000 + 10*2 500 -100 000 = -15 000km d'écart par rapport au contrat
Somme des kilométres perdus = 55 000km
Cet outil servira donc à déterminer quelle tournée pour quelle voiture :
Si Véhicule A sur Tournée 2 = 50 000 + 18*2 500 - 100 000 = -5 000km
Si véhicule B sur Tournée 1 = 60 000 + 10*5 000 - 100 000 = 10 000km
Somme des kilométres perdus = 15 000km donc gain de 40 000km
Je me suis renseigné sur l'algorithme du voyageur, au bout d'un certain temps de réflexion j'ai finalement accepté qu'il faut vachement s'y connaître en language VBA avant de pouvoir adapter un code à son fichier. Par manque de temps, j'ai donc fait cet outil à ma manière, via des simulations.
C'est là où j'ai un problème: quand il y a des sites où cet outil doit décider quelle tournée pour 25 véhicules, il arrive un moment où l'on est obligé de faire un code VBA avec conditions...
Voici ce que je cherche à faire :
Lancer la macro valeur cible
Elle va modifier les cellules (C37:F37) est rechercher les tournées qui correspondent au mieux pour chaque véhicule.
Cependant, elle ne tient pas compte des doubons. Pour certains sites, elle va dire que l'ensemble des véhicules doivent être sur une même tournée.
C'est par rapport à ces doublons que j'aurais besoin d'aide... J'ai essayé de reprendre un code VBA sur ce sujet mais même comme ça je n'est pas réussi...
Il faudrait qu'il y ai un tirage au sort aléatoire des tournées des cellules (I8:I11) s'il y a des doublons dans les cellules (C37:F37) préalablement remplies par la valeur cible.
Et si ce tirage au sort s'effectue en boucle jusqu'à qu'il trouve la valeur minimale en cellule G38 (Ou c'est peut être possible sans valeur cible aussi?) ça serait encore mieux!
Désolé pour ces explications aussi longues, j'espère qu'au moins elles ont été compréhensibles et que vous pourrez m'aider!
Bonjour,
un essai, basé sur l'algorithme du voyageur de commerce. j'ai créé des tableaux spécifiques pour la macro en feuille sheet1.
Bonjour,
En effet ça marche! En tout cas c'est la bonne tournée pour le bon véhicule!
J'ai pas encore tout compris mais je vais essayer de l'adapter de façon à ce que les tournées et véhicules s'affichent en ligne.
Est-ce que "t = .Cells(6, 3).Resize(dl - 5, dc - 1)" signifie qu'il faut faire une macro pour chaque onglet s'il comporte plus ou moins de véhicules ? Ou est-ce qu'on peut directement mettre 30 lignes même si elle ne sont pas toutes complétées ?
En tout cas j'ai de quoi avancer, merci beaucoup!
Bonsoir,
la macro s'adapte au nombre de lignes remplies.
Bonjour,
Ca me parait un peu compliqué tout ça.
Vue l'heure qu'il est, sans me prendre la tête je me suis dit que celui qui était le plus en avance sur son contrat devait prendre la tournée la plus petite.
Je calcule donc où il en est en % dans la réalisation du contrat, divisé par le nombre de mois restant.
Et j'attribue les tournées en fonction. Le plus en retard à la tournée plus grande, etc
Avec en $A$15:$A$18 les tournées en ordre décroissant des km, ça me fait 2 petites formules
=-E6/E4/E5=INDEX($A$15:$A$18;RANG(E14;$E$14:$H$14))pour obtenir directement 4, 2, 5, 8.
Formule qu'on peut encore simplifier si tu saisis les km réalisés au lieu de ceux restants à faire.
Bon, simplifié à l’extrême. Reste à voir si ça colle avec tes calcul de coûts (?), comme je ne vois pas trop à quoi correspondent tes nombres en E8:H11.
eric
Bonsoir,
Ca me parait un peu compliqué tout ça.
cela l'est sûrement pour 4 véhicules, mais pour 25 je ne pense pas que la formule trouvera toujours l'optimum.
Bonjour,
Bon je suis désolé h2so4, mais en essayant d'adapter ton code sur une feuille ("sheet 2") où il y a plus de véhicules, je crois que j'ai fais un carnage... Pourtant je voulais juste modifier les lignes et colonnes pour que ça parte au même départ de ce que t'avait fait sur la première version
Est-on obligé de recalculer les écarts (E8:Y28) pour que le tout fonctionne ?
Eriiic oui ça aurait plus pratique mais j'ai oublié de préciser que certains échanges se font intra-site donc il peut y avoir plusieurs tournées "1", du coup quand c'est le cas deux tournées "1" peuvent se numéroter 1 ou 100 en fonction des doublons...
Je suis actuellement sur tablette, donc pas facile de suivre sur Excel.
Si tu testes la formule , tu verras qu'elle s'en moque complètement. Il faut que les tournées soient triées épicétou.
Celui qui est classé 3eme prend la 3eme tournée qq soit son nom.
Eric
Bonjour Eriiic,
à voir
en fait c'est tout vu. tu as complètement raison, je me suis cassé la tête alors qu'il y avait beaucoup plus simple, obnubilé que j'étais par la perspective d'avoir à résoudre un problème dit " du voyageur de commerce".
de plus j'ai testé avec 25 véhicules et tournées, l'algorithme est loin d'être optimisé.
En effet je pense que mes galères seraient multipliées par 100 si j'utilisé excel sur tablette
Du coup si j'effectue un tri en ordre décroissant par exemple, la tournée "100" qui serait en réalité une deuxième tournée "1" fausserait ce tri.
Un tri manuel est possible, mais super long quand il y a beaucoup de contrats et de tournées, et beaucoup d'onglet à vérifier.
Mais merci beaucoup Eriiic parce que c'est une formule vraiment pratique!
Je travaille toujours sur le code VBA de h2so4, j'en suis au deuxième bug que j'ai créé en le modifiant aha : "If som + t(n,i) < solmin Then" Erreur 13 dans la Sub cherche,
Donc j'espère que je ne suis plus trop loin mais en cas d'urgence je garde ces formules en tête !
Bonjour à tous,
il me semble avoir trouvé un cas où les formules donnent un résultat différent et donc ne donnent pas le résultat optimum, à moins que j'aie mal compris/appliqué les formules.
J'avais pas vu le message d'avant.
L'algorithme du voyageur m'a, aussi, obnubilé avant de mettre ce post. Surtout que j'y connais le stricte minimum en vba et que j'avais cherché des formules qui permettaient la même chose de partout sur Google.
Là mon cerveau est out. Mais demain matin j'essaie d'adapter mon fichier de façon à pouvoir insérer ces formules.
Merci à tous les deux pour le temps précieux que vous accordé à mon problème !
Bonjour !
J'ai donc comparé avec formules et avec macro sur des données où les résultats sont sûrs (principalement calculé par la valeur cible),
La macro optimise l'utilisation des véhicules au maximum,
Les formules l'optimise mais pas au maximum,
Je pense que cette optimisation nécessite une vision globale, afin d'optenir la somme globale des écarts la plus basse possible.
En réalisant des boucles jusqu'à l'obtention de cette somme globale minimale, la macro le permet.
Sous réserves d'erreurs de ma part dans l'application des formules bien sûr!
Bonjour,
il faut donc que j'améliore les performances de l'algorithme pour pouvoir traiter plus de 10 véhicules.
Bonjour, Salut h2so4, Eric !
Traitant mes messages accumulés depuis au moins 2 mois, j'avais abouti sur ce sujet il y a 3 jours, que j'ai pieusement laissé de côté
Sans intervenir directement sur les développements en cours, il me semblerait utile d'introduire des pistes de réflexion plus générales concernant la question posée... D'abord j'étais d'emblée réticent à suivre la piste du voyageur de commerce, d'un maniement malaisé et qui me paraissait plus compliquer le problème que faciliter une solution...
Ensuite, il y a un préalable implicite, on présuppose que chercher à demeurer dans le respect le plus strict possible du contrat de location de véhicule est la situation la plus favorable à laquelle il faut parvenir. Soit que dans le cadre de la durée du contrat, si l'on dépasse le kilométrage max. prévu on a nécessairement une perte, et qu'il est donc plus favorable d'augmenter le nombre de contrats (donc de véhicules) plutôt que de forcer le kilométrage...
Exemple : sur la durée du contrat on devra faire 240000 km sur 2 tournées, on serait donc perdant à n'utiliser que 2 véhicules en dépassant de 40000 km et il serait préférable d'introduire un 3e véhicule (un contrat supplémentaire) qui éviterait tout dépassement sur les contrats en cours...
On peut le postuler comme condition de résolution de la question, mais il serait mieux de l'établir sur la base des dispositions négociées relatives aux contrats...
Une fois établis les objectifs à viser pour obtenir une gestion optimale de l'utilisation des véhicules, l'optimisation ne peut se contenter du suivi de cette utilisation car elle sera conditionnée par l'organisation mise en place.
Pour reprendre l'exemple du début : 2 tournées, représentant 5000 + 2500 = 7500 km mensuels, soit sur 3 ans (durée indiquée des contrats) 270000 km, pour demeurer dans les 100000 km par véhicule, il faudra donc 2,7 véhicules !
On ne découpera certes pas les véhicules ! Il en faut au minimum un par tournée devant être réalisée simultanément, soit 2, et le 0,7 véhicule supplémentaire nécessaire sera obtenu par l'échelonnement des dates de contrats dans le temps, de façon que dans la période considérée on obtienne les 2,7 véhicules-contrats permettant l'exécution de l'activité...
Il y a donc à mon sens d'une part l'organisation mise en place à traiter, à partir de laquelle le suivi permettant d'assurer l'optimisation prévue pourra prendre place pour ajuster la rotation sur les tournées permettant d'approcher au plus près l'objectif à atteindre... (globalement par site, et éventuellement mesures inter-sites susceptibles d'exiger des critères complémentaires).
Bonne continuation...
Cordialement.
Bonjour,
Du coup si j'effectue un tri en ordre décroissant par exemple, la tournée "100" qui serait en réalité une deuxième tournée "1" fausserait ce tri.
Heuuuu... On est bien d'accord qu'il s'agit d'un tri sur les longueurs des tournées, comme dit dans mon premier post, et non sur leur nom hein ?
Eric
Bonjour,
Du coup si j'effectue un tri en ordre décroissant par exemple, la tournée "100" qui serait en réalité une deuxième tournée "1" fausserait ce tri.
Heuuuu... On est bien d'accord qu'il s'agit d'un tri sur les longueurs des tournées, comme dit dans mon premier post, et non sur leur nom hein ?
Eric
Boulet que je suis, j'ai retenu un tri par tournée par habitude !
Autant pour moi Eriiic !
Il y a donc à mon sens d'une part l'organisation mise en place à traiter, à partir de laquelle le suivi permettant d'assurer l'optimisation prévue pourra prendre place pour ajuster la rotation sur les tournées permettant d'approcher au plus près l'objectif à atteindre... (globalement par site, et éventuellement mesures inter-sites susceptibles d'exiger des critères complémentaires).
Bonjour MFerand et merci pour tes conseils !
Les échanges inter et intra sites sont déjà différenciés par onglet dans le fichier. Certaines conditions m'ont été posées lorsque j'ai proposé ce projet pour ma mission de conseil.
Évidement, pour faire des économies incroyables le mieux serait de faire une analyse intersite au global !
Ça le sera peut être dans 1, 2, voir 3 ans, mais ce sera facilement modifiable car il suffira de modifier des filtres TCD qui alimentent les onglets de calculs.
Ce sont des contrats au moyen long terme, si cette analyse et faite chaque année, et qu'en plus de cela, au fil des ans il y a de plus en plus de sites concernés par les echanges inter-sites, on pourra arriver à un écart global proche de 0.
Pou savoir exactement quand on combien d'échanges pour arriver à 0, ça sera à l'aide de tests une fois que l'outil sera finalisé
j'ai retenu un tri par tournée par habitude !
Autant pour moi
Et du coup il reste des écarts avec la solution optimum ?