Répartition

[FreeKc]

Bonjour,

Je souhaiterai répartir des codes postaux (colonne B) qui ont chacun un nombre de clients (colonne A) à des sociétés (colonne G) qui peuvent accueillir chacune un nombre maximum de clients (Colonne H).

Je souhaiterai pouvoir compléter ma colonne C avec le nom de la société afin de savoir quel code postal est géré par quelle société sans dépasser le nombre maximum de clients possible.

Pensez-vous pouvoir m'aider ? J'ai essayé avec divers types de formule mais à chaque fois j'ai un problème pour la répartition.

Merci d'avance pour votre aide.

 

[CISCO]

Re : Répartition

Bonsoir

Une possibilité en pièce jointe, avec une formule matricielle à valider par Ctrl + majuscule temporaire + entrée, et à tirer vers la droite et vers le bas..

@ plus

 

[FreeKc]

Re : Répartition

Bonjour,

Merci pour le retour, mais votre possibilité se base sur la colonne C alors que le but est plus tôt de la remplir.

 

[CISCO]

Re : Répartition

Bonjour

Et sur ton fichier réel, tu as combien d'entreprises ?

@ plus

En fait, si j'ai bien compris, tu veux optimiser le nombre de clients par entreprise, c-à-d si la société x peut "traiter" 200 clients, trouver la somme des nombres de la colonne A s'approchant le plus de 200 par valeur inférieure, cette condition étant aussi vérifiée pour les autres entreprises ? Autrement dit, tu cherches à laisser le moins de clients sur la touche. Si c'est cela, je pense qu'il faudra passer par une macro, ce que je ne sais pas faire...

@ plus

 

[FreeKc]

Re : Répartition

Bonjour,

Dans mon fichier réel, je possède 4 entreprises et 608 codes postaux.

Oui en effet 🙂.

De mon côté, j'ai testé avec le solver Excel mais je possède trop de variables pour l'appliquer sur la totalité. La limite est de 200 et je possède 2424 variables différents.

 

[CISCO]

Re : Répartition

Bonjour

Un essai en pièce jointe, juste pour montrer une méthode possible :

Dans le tableau C14:F19, dans les colonnes C, D et F, on tire au hasard le nombre de clients attribués à chaque entreprise, sans dépasser le nombre max autorisé pour chacune. Dans la colonne F, on impose 1 ou 0 en fonction de ce qui a déjà été choisi avant dans la ligne et la colonne en question. On fait la somme de chaque colonne et on calcule le nombre de clients non pris en charge (différence). On répète le tirage autant de fois que désiré (avec la touche F9), et on copie-colle les valeurs (pas les formules) du tableau de gauche à chaque fois qu'une plus petite différence apparait en H21, vers le tableau de droite, en rouge.

En faisant plusieurs tirages, je n'ai obtenu comme différence que 400, 355 et 300, pour l'exemple traité (pas super comme résultat, mais assez logique, puisqu'aucune entreprise ne peut prendre en charge 300 clients d'un coup, hors, c'est le nombre en B19).

Reste plus qu'à adapter cela à ton fichier réel, et à faire une macro faisant par exemple 1000 tirages et ne gardant à droite que le tableau donnant la plus petite différence. Malheureusement, je ne sais pas faire...

Avec cette méthode, on n'est pas certain d'obtenir l'optimum, mais bon. Au besoin, faire plus de tirages...

@ plus

 

[mapomme]

Re : Répartition

Bonjour FreeKc, CISCO 🙂,

Un essai en VBA qui consiste à essayer toutes les combinaisons possibles (5 par CP). Je n'ai pas tenu compte des cas où le CP a un nombre de client dépassant la capacité d'un site. En tout cas, avec 608 CP et 4 sites , la résolution est inenvisageable par mon code (5^608 combinaisons !!!!). Je joins malgré tout le fichier.

On pourrait peut-être envisager de procéder en deux phases. D'abord distribuer les grosses masses, puis terminer avec le code du fichier pour distribuer les autres après avoir ôté les CP déjà distribués et diminué la capacité de chaque site du nombre de clients déjà ventilés. A voir 😕

Le second exemple trouve une solution après avoir testé environ 17% des combinaisons (% d'avancement dans la barre d'état pendant l'exécution).

 

[CISCO]

Re : Répartition

Bonjour à tous, bonjour mapomme.

C'est déjà pas mal comme ça, mais il est vrai que 5^608 cas, cela pose problème. C'est quand même mieux que d'essayer au hasard comme je le proposais (avec cette dernière méthode, on peut très bien passer à coté des meilleures combinaisons, même après 1 million de tirages !).

Il doit bien exister des méthodes adaptées à ce problème d'optimisation, mais ce n'est pas un problème lié à Excel, plutôt un problème d'analyse numérique... J'ai peut être traité cela en fac, mais il y a tellement longtemps... Roger2327 aurait quelques bonnes idées sur le sujet, que cela ne m'étonnerait pas.

@ plus

 

[CISCO]

Re : Répartition

Bonjour

J'ai modifié ma dernière proposition pour pouvoir traiter une centaine de CP, toujours avec la fonction ALEA.ENTRE.BORNES(). Résultat, on obtient très facilement, après 3 ou 4 tirages, une différence égale à 0. Est-ce une coïncidence ?

Tout compte fait, peut être que celle méthode suffira à Freekc...

@ plus