XL 2013 Combinaisons complexes sous VBA

marcodeco

XLDnaute Nouveau
Bonjour à tous,

Après d'innombrables recherches sur google, les forums et tests d'adaptations de bouts de code,
je me casse les dents sur une méthodologie de calculs que je tente de coder depuis de nombreuses semaines maintenant.

Mes résultats obtenus sont pas si mal mais restent bien inférieurs à ceux obtenus avec notre cerveau en 5 minutes.

Je me rends compte finalement que le plus simple serait encore de calculer directement un ensemble de combinaisons
et pour chaque résultat lui attribuer une note afin d'atteindre la meilleure solution tant recherchée.

Oui mais pourtant, le code VBA pour faire cela est encore plus complexe que ce que j'ai pu créer jusqu'à présent.

Alors voilà, je m'avoue vaincu par mon incapacité à traduire des combinaisons imbriquées en code et me tourne vers vous, férus du VBA.
Vous trouverez pour 1 exemple, les résultats recherchés de combinaisons, avec toutes mes explications dans le fichier joint.

Je remercie d'avance ceux qui prendront la peine de me lire et m'aider.
 

marcodeco

XLDnaute Nouveau
Bj Sylvanu,

Le but est de trouver le meilleur amalgame (celui ayant le moins de perte possible).
Je viens de rajouter 1 onglet "Etude principale" dans mon fichier.

Je précise la contrainte suivante : on ne peut pas mettre plusieurs visuels dans 1 même zone.

Nb : Après résultat trouvé par mon code VBA, je pensais qu'une méthode de "vases communicants" serait logique pour finaliser la "meilleure solution"...
sauf qu'il faut intégrer différentes combinaisons selon multicritères.

Je me dis du coup qu'il serait préférable que je reprenne mon code à zéro
pour réduire au minimum les critères et calculer par contre toutes les combinaisons possibles ;
la perte la plus faible donnant la meilleure "note" pour cibler le résultat optimal "meilleur amalgame".
 
Dernière édition:

marcodeco

XLDnaute Nouveau
J'ai l'impression que pour la 1ère partie du calcul des combinaisons (combinaisons des scindements possibles) je suis obligé de passer par un tableau à 3 dimensions (ou dynamique ?).

Dim Tableau(X, Y, Z) devrait suffire ?

Le X serait le nom du volume concerné
Le Y serait le n° de combinaison
Le Z serait le n° de position

Quand Dim Tableau(X, Y, Z+?) = "rien" ou "zéro" alors Z=1 et Y=Y+1
Quand Dim Tableau(X, Y+?,1) = "rien" ou "zéro" alors Z=1 et Y=1 et X=X+1
Quand Dim Tableau(X+?, 1,1) = "rien" ou "zéro" alors arrêt boucle du tableau

Dans l'exemple (de mon fichier 2ème onglet) du volume nommé B ont a en combinaisons :
B = 6000
B = 2500 puis 3500
B = 2500 puis 2500 puis 1000

on est en cours de recherche de valeurs sur B donc X=2

Dim Tableau(2, 1, 1) = 6000
Dim Tableau(2, 1, 2) = "rien" ou "zéro" alors Z=1 et Y=Y+1
Dim Tableau(2, 2, 1) = 2500
Dim Tableau(2, 2, 2) = 3500
Dim Tableau(2, 2, 3) = "rien" ou "zéro" alors Z=1 et Y=Y+1
Dim Tableau(2, 3, 1) = 2500
Dim Tableau(2, 3, 2) = 2500
Dim Tableau(2, 3, 3) = 1000
Dim Tableau(2, 3, 4) = "rien" ou "zéro" alors Z=1 et Y=Y+1
Dim Tableau(2, 4, 1) = "rien" ou "zéro" alors Z=1 et Y=1 et X=X+1
Dim Tableau(3, 1, 1) = (ça passe au volume nommé C)
....jusqu'à
Dim Tableau(X+?, 1, 1) = "rien" ou "zéro" donc arrêt boucle
On a terminé le tableau

Si je suis sur la bonne voie,
comment énumérer les combinaisons citées dans mon fichier ?
exemple : B = 6000 / scindements maxi 3 / multiple 2500 / combinaisons sans doublon
B = 6000
B = 2500 puis 3500
B = 2500 puis 2500 puis 1000

Enfin si cette 1ère partie est valide, Il faudrait utiliser ce tableau pour trouver toutes les combinaisons finales sans doublon & avec règles à prendre en compte
pour réduire un maximum la démultiplication de combinaisons inutiles au(x) résultat(s) souhaité(s) et donc optimiser le temps de calculs et d'affichage d'excel pour éviter d'attendre des minutes (ou des heures ?), la meilleure solution tant attendue.

Bref un vrai casse tête...
 
Dernière édition:

eriiic

XLDnaute Barbatruc
Bonjour,

c'est quand tu veux pour les explications.
Sous peine de continuer ton monologue encore longtemps...
On voit des pertes, apparaitre ex-nihilo sans savoir ce que c'est
On voit des Amalgames/Nombre de zones sans savoir entre combien et combien chacun peut varier
etc, etc ,etc
eric
 
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour à @marcodeco, à @sylvanu :), à @eriiiic :), à tous,

@marcodeco,
D'après ta feuille "Etude principale", on pourrait penser que le problème consiste en:
  • Avoir 14 lettres (chacune associée à une valeur)
  • Avoir 4 contenants (ici 4 lignes) devant contenir au final chacun 5 lettres
  • On commence par répartir les 14 lettres dans les 4 contenants (peu importe l'ordre) -> on est ainsi certain que chacune des 14 lettres y figure au moins une fois
  • Puis on considère chaque contenant et pour chaque lettre qui y manque, on choisit une lettre au hasard parmi les 14 qu'on place dans le contenant
Au final on se retrouve avec quatre contenants de 5 lettres avec au moins une fois chaque lettre parmi les 14.

Pour chaque contenant, on calcule (5 fois la valeur max du contenant diminué de la somme des valeurs du contenant). On ajoute ces 4 valeurs calculées et cela donne la perte totale.

Ai-je bon ?
 

marcodeco

XLDnaute Nouveau
Bonjour eriiic,
Pas de souci, je pensais qu'expliquer le tout serait pire (trop gonflant à me lire) que d'expliquer seulement le code que je cherche mais je réalise que ça fait le phénomène inverse, désolé.

Bonjour mapomme,
Ton analyse est parfaite mais les valeurs ne sont pas fixes ; elles sont variables.
eriiic a raison, je viens d'ajouter un nouvel onglet à mon fichier expliquant mes travaux.

Nb : depuis mon 1er post, je ne suis plus sûr que calculer toutes les combinaisons est le meilleur code à faire, il y a peut-être d'autres manières de faire ?
 

Pièces jointes

  • Combinaisons v3.xlsx
    143.3 KB · Affichages: 15

marcodeco

XLDnaute Nouveau
C'est ce que je crains, je ne me rends pas compte du temps nécessaire à boucler le code (VBA pur sans afficher les combinaisons dans des cellules ) selon la puissance de calculs d'un ordinateur lambda.

Après, les valeurs maxi mises dans "données saisies par l'utilisateur" peuvent être plus faibles.
Comme, 20 refs maxi/12 pistes maxi/10 amalgames maxi, cela pourrait suffire dans la majorité des cas d'impression.

Dire qu'au quotidien je mets en moyenne 5 à 10 minutes à calculer cela manuellement en testant 1 à 10 combinaisons par dossier d'impression.
Une logique de codage devrait pourtant bien pouvoir se faire qui prendrait moins d'1 minute d'attente de calculs, c'est frustrant...un challenge à mes yeux.

Sinon s'il fallait écarter cette piste (calculer toutes combinaisons),
est-ce que les variables et les règles d'amalgames peuvent se traduire en systèmes avec autant d'inconnues, nombre d'inconnues variant en plus en fonction des nombres de : reférences/pistes/amalgames/pertes/scindements volumes refs...) le tout compatible/traductible sous Excel/VBA ?
 
Dernière édition:

marcodeco

XLDnaute Nouveau
Est-il possible de faire cela en VBA, (je n'ai pas besoin de visualiser le résultat dans cellules Excel) :
entrer dans un tableau à 2 dimensions, les combinaisons possibles d'un tableau à 3 dimensions ?

1579912365655.png