VBA- Chiffres aléatoires avec conditions
Bonjour,
oui, tu t'es trompé, tu ramènes seulement le rang et non le n° de tournée.
En C21 il faut :
=INDEX($A$6:$A$11;RANG(C20;$C$20:$H$20))Ceci dit je ne vois pas à quoi correspondent les nombres du tableau sur lequel vous vous basez.
Ce serait bien de donner leur formule. Parce que dire qu'une solution est meilleure en se basant de des chiffres sortis d'on ne sait où (pour moi en tout cas
21956 c'est quoi ?
De plus il faut voir que la formule s'ajuste au fil du temps qui passe. D'un mois sur l'autre les tournées peuvent évoluer. Une fois qu'un véhicule a comblé son retard, un autre lui passe devant.
Pas d'anticipation sur plusieurs mois, ceci explique peut-être cela.
eric
Bonsoir Eriiic
Ce serait bien de donner leur formule. Parce que dire qu'une solution est meilleure en se basant de des chiffres sortis d'on ne sait où (pour moi en tout cas
), ça je ne sais pas faire. 21956 c'est quoi ?
résultat de la formule donnée par laulau dans son message initial
=abs(56*2000+(-90044))Merci, je n'avais pas fait le lien avec ça.
Le bien fondé de ce calcul m'échappe quand même
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
Déjà, pour moi, on ne peut mettre pas sur le même plan des km non réalisés et des km qui dépassent le contrat.
Si des km sont non réalisés c'est qu'il y a une voiture en trop, mais a-t-on le choix ? Je ne pense pas. Ou bien que les contrats de 100000 km sont trop gros. Là encore a-t-on le choix ?
Avec cette logique on peut faire comme à l'armée. Il suffit de les faire tourner dans la cour pour ne plus perdre d'argent.
6 véhicules ça fait 600000 km. Si les contrats sont de 5 ans ça fait 8300 km par mois. Et ce même si on a en réel que 6000 km de tournées par mois. Le reste est obligatoirement 'perdu'.
Par contre les km qui dépassent sont surfacturés, ça compte un peu plus...
Si le but est d'optimiser, le seul moyen est de changer les voitures de trajet en cours de contrat pour éviter tout dépassement.
Dans ce cas quel est l'intérêt de calculer chaque trajet sur l'ensemble du temps restant à courir sur le contrat ?
Enfin c'est comme ça que je vois les choses avec les éléments fournis. Pour alimenter la réflexion
eric
Bonjour,
Eriiic, je comprends qu'il soit difficile de comprendre un fichier sans voir sa globalité. Mais crois moi, c'était pour un gain de temps !
Le fichier commence par un premier onglet reçu une fois par mois, voir une fois tous les deux mois, indiquant, pour chaque véhicule, le nombre de km effectués les 6 derniers mois, le kilométrage réalisé depuis la mise en service, la durée et les kilomètres fixés au contrat.
Un autre onglet est aussi un copié/coller des affectations de chaque immatriculation à chaque tournée.
J'ai donc créé un onglet pour réunir les informations nécessaires : Immat, site, tournée, nombre de mois restants, kilométrage (mis à jour avec la fonction aujourd'hui) etc.
Bon tout ça pour dire que les KPI ont été déterminées dans cet onglet qui transforme des bases de données sources. Pour ensuite faire des TCD pour filtrer en fonctions des contraintes : les sites, les types de véhicules, pour enlever les véhicules qui ont été rendu depuis, etc.
Les tableaux sur lesquels ont travaillent ici sont reliés à ces TCD dans le fichier complet. Tout est relié, c'est donc un copié/coller de valeurs pour vous épargner des détails qui précèdent cette étape pour laquelle je bloqué..
Le bien fondé de ce calcul m'échappe quand même
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
Déjà, pour moi, on ne peut mettre pas sur le même plan des km non réalisés et des km qui dépassent le contrat.
Si des km sont non réalisés c'est qu'il y a une voiture en trop, mais a-t-on le choix ? Je ne pense pas. Ou bien que les contrats de 100000 km sont trop gros. Là encore a-t-on le choix ?
Les contrats de location imposent un loyer fixe :
- si on sous-roule : on perd le quota du loyer fixe dont on aurait pu se passer
- si on sur-roule : on perd en terme de défaillances techniques, et de la valeur résiduelle à laquelle le loueur ne sera plus tenu
Les contrats sont confidentiels et non modifiables, le nombre de tournées ne peut être changé non plus et il faudra forcément 1 véhicule pour 1 tournée. Ce sont des contraintes qui ne changeront pas dans le temps.
Par ailleurs, en ce qui concerne l'onglet d'optimisation :
La formule est top à connaître, et une autre personne m'en avait parlé mais, part rapport à une optimisation, quelque chose cloché quand j'y pensais.
J'ai dû mal à bien exprimer une idée qui cogite encore dans ma tête mais je vais essayer à l'aide d'un exemple:
Voici comment la formule agit sur le tableau :
La CZ-331 est celle qui part le plus tôt et qui sur roule le plus donc on doit prioriser la tournée avec l'écart le plus proche de 0 : 3 452km sur la T3
Actuellement, elle est sur la tournée 10 (désolé la ligne n'y ai plus en haut mais si je la remet la macro bug
Donc son écart actuel est de 12 259km - 3 452km = économie de 10 726km
Cependant, si on gagne 10 726km en mettant la CZ-331 sur la T3,
dans ce cas si on mettrait la DW-951 qui est actuellement sur la T10, on économiserait 19 931km soit 9 205km de plus
Pour résumer, l'écart km d'un véhicule peut en impacter un autre, c'est pour cela qu'il faut avoir une visions plus globale pour otptimiser l'ensemble des véhicules.
Il y a tellement de véhicules et de kilomètres à gagner que l'on cherche à optimiser une charge globale. Tous les véhicules ne pourront jamais avoir un écart de 0 à moins que cet outil soit suivi régulièrement sur du long terme
Ci-dessous le même fichier que j'ai envoyé avec mon erreur de tri modifié :
Bonjour,
Bonjour,
il faut donc que j'améliore les performances de l'algorithme pour pouvoir traiter plus de 10 véhicules.
As-tu réussi ?
J'essaie de le comprendre pour pouvoir l'adapter avec 4 colonnes et 6 lignes d'infos sur les véhicules, mais les bugs m'épuisent!
" Dim sol
Sub aargh()
With Sheets("Sheet1") -> est-ce qu’on peut remplacer “sheet1” de sorte à ce que l’algorithme soit applicable sur n’importe quelle feuille à partir du moment où on clique sur un bouton sur la feuille en question ?
dl = .Cells(Rows.Count, 1).End(xlUp).Row
dc = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 7 To dl -> pour remplir les lignes 1 à 6
For j = 5 To dc -> les calculs concernent les colonnes 5 à n
.Cells(i, j) = Abs(.Cells(4, j) * .Cells(i, 4) + .Cells(5, j))
Next j
.Cells(i, dc + 1) = Application.Min(.Cells(i, 6).Resize(, dc - 4)) -> 6 lignes et 4colones concernant les contrats
Next i
t = .Cells(7, 5).Resize(dl - 6, dc - 4)
cherche t
Columns(dc + 1).ClearContents
Rows(dl + 1 & ":" & dl + dl).ClearContents
dl = dl + 1
.Cells(dl, 2) = "solution optimale"
For i = 1 To Len(sol)
.Cells(dl + i, 2) = .Cells(5 + i, 1)
.Cells(dl + i, 3) = .Cells(2, Asc(Mid(sol, i, 1)) - 62)
Next i
End With
End Sub "
Le deuxième j'en parle même pas!
Bonjour,
Merci pour les compléments, ça éclaire un peu plus.
Si je trouve du temps je regarderai ce qui est faisable avec le solveur.
eric
Bonjour,
Merci pour les compléments, ça éclaire un peu plus.
Si je trouve du temps je regarderai ce qui est faisable avec le solveur.
eric
Bonjour,
J'avais oublié ce solveur !
J'ai tenté de définir la somme des nouveaux écarts en Min, les nouveaux écarts sont trouvés à partir d'une recherche V en fonction des cellules des nouvelles tournées.
Donc objectif à définir : somme des écarts min
Cellules variables : nouvelles tournées et j'avais tenté la contrainte Tousdifférents,
Enfin bref, j'ai encore beaucoup à apprendre puisque ça ne fonctionnait pas..
Bonne après midi
Bonjour,
voici une version quelque peu optimisée (jusqu'à 14 tournées en moins de 5 minutes) et adaptée pour s'exécuter sur la feuille active.
Bonjour,
La 2ème version beug, mais avec la première c'est niquel ! Pour le site où il y a une vintaine de véhicule, l'utilisateur réfléchira tout simplement au préalable pour les découper en deux parties.
Je pense qu'on peut dire que le problème est résolu,
Merci beaucoup pour votre aide !
Bonjour,
h2so4 dans le fichier ci-joint la solution optimale l'est moins que l'attribution des tournées actuelle (8Tournées)
Bonjour,
je pense qu'il y a un problème avec tes calculs.
voir dans le fichier le résultat que je pense correct.
Bonsoir,
Je viens de réessayer et ça marche
Merci !