Repartition complexe

Bonjour,

Bien sûr on vient vers vous que lorsque nous avons des problèmes.

Je bute !!! est ça me parait fort complexe pour moi.

Je dois faire une formulation à partir d’une liste de produits contenant 4 catégories A,B,C,D et chaque catégorie contient plusieurs composants.

Je dois :

1. Faire une repartitions aléatoire sans doublons pour repartir les composants de chaque catégorie. Dans mon exemple c’est remplir la colonne B6 :B24

2. Déterminer le % de chaque catégorie dans les cellules N3 ; P3 ; R3 ; T3 de sorte que leur somme en L3 doit être obligatoirement = 100%

3. On détermine un poids théorique total. Dans mon exemple C4=10000 ; F4=15000 ; I4=25000

4. Sachant que chaque composant a un poids unitaire pour la catégorie A= N5 :N17 ; catégorie B=P5 ; P25 ; catégorie C=R5 :R11 ; pour la catégorie D= T5 :T21

5. Je dois déterminer le nombre d’unité nécessaire des composants choisi aléatoirement au début de sorte que la somme du poids total de chaque unité soit égale au poids théorique déterminer précédemment à +/- 10%

Ci-joint un fichier exemple

Cordialement

23exemple.xlsx (41.86 Ko)

Salut,

J’avais commencé à m’intéresser à ton problème il y a deux jours, mais j’avais abandonné lorsque je me suis rendu compte que tu avais mis des données aléatoires dans tes colonnes N, P, R et T et que je n’en comprenais pas la raison.

Voyant le peu de succès qu’à ta demande, je te demande quand même quel est le but de ces chiffres aléatoires ?

Si tu veux des numéros de composants aléatoires dans ta colonne B, il n’est pas logique ou pas nécessaire d’avoir des ‘Poids composant /1 unité’ aléatoires, non ?

A te relire.

Bonsoir,

merci beaucoup de t'interesser à mon probleme.

je justifie les données aléatoires dans les colonnes N, P , R , T: au fait c'etait au lieu de recopier toutes les données que j'ai dans mon tableau d'origine j'ai mis cette formule aleatoirepour les remplir. Dans la réalité les colonnes N,P,R et T sont remplis par des données il n'y a donc aucune formule dans ces colonnes juste des données.

encore merci de t'interesser à mon probleme

Cordialement

Re,

Voici un premier jet par rapport à ce que j’ai compris de ton besoin.

Dans le fichier ci-joint, la macro placée derrière le bouton mis en place dans la première ligne réalise en gros ceci, provisoirement pour le premier 'Poids théorique' uniquement :

Elle choisit d’une manière aléatoire des composants des groupes A, B, C et D et les places avec leur poids dans les colonnes B et D.

Ensuite elle cherche à s’approcher des pourcents indiqués dans les cellules N3, P3, R3 et T3 en ajoutant des unités dans la colonne C, bien entendu catégorie après catégorie.

Un dernier contrôle essaie de rajouter des unités à n’importe quelle catégorie afin d’améliorer le résultat général.

Les pourcents finalement atteints – par catégorie et au total - sont visibles dans la plage D25 à D29.

J’atteins un résultat général (en D25) entre 98 et 101 % en fonction des valeurs comprises entre 1 et 300 que tu as toi-même fixées dans les colonnes N, P, R et T.

Ce résultat pourrait être très légèrement amélioré je pense en ne cherchant pas à atteindre à la base la plus petite valeur en-dessous de 100 %, mais en cherchant le plus petit écart en-dessus et en-dessous de 100 %. Je n’ai pas été si loin pour l’instant, en attendant de voir si cette première partie te convient déjà.

Il est bien entendu que si ceci te convient, je m’occuperai également des 'Poids théoriques' 2 et 3 (colonnes F à K).

A te relire.

EDIT : Quelques minutes après avoir posté ce message, je me rends compte d'un problème que je ne peux pas encore expliquer (par exemple un poids total de 9990 est atteint, il y a des composants qui ont un poids de moins de 10 et cette lacune n'est pas comblée !!!). Alors regarde déjà l'ensemble de ce qui est déjà réalisé et je verrai ces jours prochains pour corriger le problème remarqué en même temps que le reste à réaliser encore. Ce soir je n'ai plus le courage de continuer

13exemple-v2.xlsm (57.25 Ko)

Bonjour,

La nuit porte conseil

J’ai corrigé mon code par rapport à mon ‘Edit’ d’hier soir et – sur la base des pourcents indiqués par toi dans les cases N3, P3, R3 et T3 – les résultats sont souvent entre 99 et 100 %.

J’ai découvert un autre problème : en fait, ma macro commence par attribuer le nombre d’unité 1 à chaque composant puis augmente gentiment ce nombre d’unités en fonction des besoins. Mais si par hasard tous les poids de chacun des composants d’une catégorie donne dès le départ un % pour cette catégorie trop élevé, cette catégorie aura bien entendu un % trop élevé et le total général peut être de plus de 100 %.

Afin d’illustré cette situation, j’ai indiqué dans le fichier ci-joint un % très bas pour la catégorie B en P3, et ceci sans savoir si un tel % est possible dans la réalité pour cette catégorie. Pratiquement à chaque nouveau tirage, le total des poids de cette catégorie – dont le nombre d’unité ne dépasse normalement pas 1 – donne alors un % en D27 bien supérieur à celui désiré et un total en D25 souvent supérieur à 100 %.

Afin de corriger cette situation, on pourrait par exemple retirer des unités d’autres composants dans d’autres catégories. Est-ce que ça te semble une solution possible ? Ceci voudrait alors évidemment dire que ces catégories auraient des % moins élevés que ceux désirés.

Il est bien entendu qu’avec des % tels qu’indiqués au préalable par toi pour les cellules N3, P3, R3 et T3, ce problème arrive beaucoup plus rarement et avec des dépassements bien moins importants.

A te relire.

21exemple-v3.xlsm (58.73 Ko)

Bonjour Yvouille,

pour commencer un tres grand merci pour ton travail et beaucoup d'admiration.

Travail impeccable à 99.99%.

concernant le cas de figure ou le % que je choisirai dans les colonnes N P R T est bas: d'apres les quelques tests que j'ai fait je ne depasse guere les 10% de variation.

Par contre actuellement le probleme reside dans le cas ou je dois mettre un 0% dans 1 ou 2 colonnes (N P R T) là le resultat doit être obligatoirement =0 concernant cette colonne.

J'attend ce correctif pour transferer tout ca sur mon fichier d'origine et faire les tests grandeur nature et verifer l'impact des % bas dans les colonnes N P R T

Et encore beaucoup d'admiration

A bientot

Salut,

Ta nouvelle demande change passablement la donne et je vais devoir changer passablement mon code XXXXXXX. Merci de bien réfléchir si tu as encore d’autres souhaits de ce genre là avant que je me lance dans ces corrections.

D’ailleurs, est-ce que une seule catégorie peut vouloir être exclue ou serait-il possible d’exclure plusieurs catégories ?

Tu n’as pas répondu à ma question quant à savoir si un groupe pouvait avoir bien plus de % que prévu si le total de chacune de ces unités représentait plus que le poids désiré pour cette catégorie.

Et - si oui - tu ne m’as pas dit s’il fallait accepter un dépassement ou si du total général ou s’il fallait compenser en baissant les autres catégories ?

Suite à ta réponse, il se peut que tu n’entendes plus parler de moi pour quelques jours, mais normalement pas plus d’une semaine.

A te relire.

Resalut,

je suis entrain de travaillerdessus mais je me rend compte que j'ai fait une erreur d'ennoncé dans probleme. j'espere sans grande consequence:

1-Concernant la repartition aléatoire des composants: je la fait moi même sur une autre feuille du classeur et que est recopie automatiquement avec liaison.

Donc plus besoin de la repartition aleatoire des composants mais travaller sur des composants données prédefinis pour lesquels on doit rattacher le nombre d'unités et le poids total de chaque composant.La suite est donc la même.

2-concernant l'exclusion des catégories: on peut éliminer jusqu'a 3 catégories

3-a propos des variations: la tolerance est de +/- 10% que ce soit pour les groupes individuellement ou pour le total general.donc on peut compenser en diminuant les autres groupes de 10% max de leur total.

et dis moi STP si je vais avoir de tes nouvelles.

Merci infiniment

Cordialement

Re,

Ta demande n’a pratiquement plus rien à voir avec ta demande initiale. Je vais donc laisser tomber, de peur que ça change encore vingt fois à l’avenir. Désolé

Indique s’il te plait ce fil comme résolu et recommence-en un autre en fixant dès le départ tes souhaits et en fournissant un fichier correspondant exactement à la situation réelle.

Bonnes continuations.

Rebonjour,

je te remercie beaucoup pour le gros effort que tu as fait.

je suis désolé pour les changements que j'ai du opéré car cela evolue , comme tu dois le savoir,en fonction des reponses.Car plus les réponses sont justes plus on voit les limites de nos projets et donc on les fais evoluer en fct des circonstances.

Je te remercie encore une fois de plus pour ton aide tres precieuse.

dans l'espoir de te relire

A+

Amicalement

Salut,

Je veux bien faire un dernier essai, mais il faut que tu y mettes un peu du tien.

D’abord tu te laisses le temps de me répondre et tu réfléchis bien, comme je te l’ai déjà demandé, à ce que tu désires.

Ensuite tu me présentes au minimum l’état actuel de l’avancement de ton fichier, avec ta manière d’effectuer le tri aléatoire.

Et on verra alors si je m’en sors avec tes nouvelles demandes.

A te relire.

Bonsoir,

c'est une tres bonne idée. en effet je suis entrain de travailler dessus à partir de ce que tu m'as donné.je pense que dans 3 ou 4 jours je t'enverrai un travail plus abouti avec des questions plus justes.

et encore désolé pour ce contre temps .

A bientot

Cordialement

Bonjour Yvouille,

Me voilà de retour.

Donc : je te resume ce que j’ai pu faire et ce qui me reste à faire :

1. J’ai fait la répartition aléatoire des composants à partir d’un copie collé avec liaison. Les données proviennent de la Feuil5 ! Comme j’ai des fonctions « alea entre bornes » sur la Feuil5 ! les données changent à chaque fois. Y a-t-il une possibilité de mettre un bouton pour que ça ne change que lorsqu’on clique dessus ?

2. La valeur de chaque composant est reporté par une rechercheV dans le tableau qui nous concerne est qui sur la Feuil3 !

3. Feuil3 ! : Il reste à repartir les valeurs dans les colonnes « Nombre de composants » qui sont les colonnes C-L-U-AD-AM-AV et BE en respectant a +/- 10% le poids total en C3-L3-U3-AD3-AM3-AV3 et BE3 et le % de chaque famille de composants.

4. Je n’arrive pas à supprimer les « 0 » et les cellules vides de chaque catégorie de composants. Y a-t-il une possibilité de supprimer les cellules vides ?

5. La base de données se trouve dans la Feuille BD !. Je dois avoir la possibilité de rajouter des composants dans la base de données

6. Possibilité de mettre une condition ? : la valeur des cellules : A5-J5-S5-AB5-AK5-AT5-BC5 doit être égale à 100%

Je te remercie par avance

Cordialement

13classeur1.xlsm (103.77 Ko)

Salut,

Je trouve tes explications directement dans le fichier Excel très embêtantes. Tout d’abord je ne sais pas toujours à qui elles s’adressent – à moi ? est-ce un simple rappel pour toi ? – et il est très difficile d’en avoir l’historique ; au bout de 20 échanges, si je me rappelle que tu m’avais donné telle ou telle information et que je dois ouvrir 5 fichiers Excel ancien afin de la retrouver, c’est la galère.

Tes aléatoires entre bornes - qui n’ont apparemment pas de raison d’être - sont très embêtant également ; je trouve cela totalement déstabilisant. Si tu utilises la fonction ALEA.ENTRE.BORNES() pour te simplifier la vie quant au remplissage d’un tableau exemple, s’il n’y a pas de raison de laisser la formule en place parce qu’elle est nécessaire, remplace les formules par leur valeur par un copier-coller ‘Spécial Valeurs’ et le tour est joué.

Ensuite je n’ai pas compris comment tu réalises la première partie de ton travail. Il serait intéressant que tu me montres A) comment se présente la Feuil3 avant toute intervention et B) comment elle se présente après tes premières modifications, mais avant la macro que tu attends de ma part.

Pour l’étape A) mentionnée ci-dessus, merci de m’expliquer également par du texte ce que tu réalises, mais en utilisant les références aux objets Excel (feuilles, plages de cellules, cellules, etc.). Lorsque tu me dis par exemple que ‘'la valeur de chaque composant est reporté par une rechercheV dans le tableau qui nous concerne'’, je ne sais pas exactement quelles plages sont copiées ni où elles sont collées exactement.

Pour l’étape B, essaie de m’expliquer - également en faisant référence aux objets Excel – ce que tu désires que ma macro réalise. Si tu désires qu’elle réalise 36 trucs différents, commençons peut être avec les 2 premiers seulement et on y va petit à petit, au risque de nous emmêler les pinceaux.

A te relire.

Bonjour Yvouille,

Content de te lire.

Merci pour ton conseil, j’ai supprimé la fct « alea entre borne » inutile servant uniquement à remplir le tableau.

La Feuil3 ! contient le tableau final. Dans son aspect definitif

La Feuil BD ! contient la base de données

La Feuil5 ! est la feuille de calcul : le tableau BR16 :BX42 contient le résultat des calculs. Ensuite le fais un copie-collé avec liaison à partir des cellules de la Feuil5 ! : vers la Feuil3 !

Le poids unitaire de chaque composant des colonnes : A ;M ;V ;AE ;AN ;AW ;BF provient de la base de données de la Feuil BD ! par la formule « =SI(ESTERREUR(RECHERCHEV(B7;BD!$A$7:$E$107;2;0));"";(RECHERCHEV(B7;BD!$A$7:$E$107;2;0))) »

Ce que je souhaite c’est :

  • J’utilise la fonction « alea entre bornes » dans plusieurs cellules dans les colonnes BB à BP de la Feuil5 ! : elles sont obligatoires dans mes calculs. Du coup à chaque operation sur mon classeur tout change. Y a-t-il une possibilité de déclencher à partir d’un bouton de macro. C’est-à-dire une macro qui « fige et declenche » les formules
  • Ensuite Je dois donc avoir une repartition du nombre de chaque composant. Pour ca : JE PENSE QUE LA FORMULE DE CALCUL DE TA PRECEDENTE MACRO « SS » qui se trouve dans le classeur est parfaite. Il faut juste supprimer la repartition aleatoire des composant et l’adapté a ce cas.
J’espere avoir été clair sinon n’hésite pas à me poser toutes les questions qu’il faut.

Cordialement

10classeur2.xlsm (111.58 Ko)

Salut,

Je t’ai demandé :

1) De ne pas mettre de commentaire dans tes fichiers Excel

2) De me fournir une feuille-exemple avant ton intervention et une feuille-exemple avant mon intervention.

De ton côté :

1) Tu as mis des commentaires en grandes quantités dans ton nouveau fichier

2) Tu n’y as pas mis les deux feuilles demandées.

Tant que tu ne liras pas mes messages, je ne lirai plus les tiens

Amicalement.

Bonsoir,

je te joins le classeur avec les 2 feuilles demandées:

avant TON intervention = Feuil3!

avant MON intervention = avant mon intervention!

dans un premier temps on peut se soucier que du tableau N°1. Quand tout sera ok on pourra aller ensuite vers les autres tableaux.

tu me dis si ca te convient je suis en ligne

Cordialement

8classeurv003.xlsm (129.57 Ko)

Re,

Tes excuses sont tout acceptées

Afin de figer un tirage aléatoire, la seule solution selon moi est de remplacer le tirage aléatoire par formule par un tirage aléatoire par macro. C’est ce que faisait mon tirage aléatoire dans le dernier fichier que je t’ai fourni.

Je crois qu’il existe une autre solution en bloquant le recalcul automatique d’une feuille et en permettant UN ET UN SEUL recalcul sur demande (par exemple en cliquant sur un bouton), mais je ne me suis jamais intéressé à cette solution car je crois que ça peut créer des problèmes si en finale tu oublies de réactiver le calcul automatique, calcul automatique qui ne se ferait par exemple plus pour un autre fichier Excel ouvert en même temps.

Si mon idée t’intéresse, je peux remplacer tes 126 formules comportant la fonction ALEA par des calculs par macro, lancé sur demande.

Ton tirage aléatoire me semble quand même un peu tiré par les cheveux.

Je vois dans ta colonne B de la Feuil3 que tu as placé des formules assez simples dès la ligne 7. Tu indiques que tu les as placées par copier-coller, mais je pense que ce n’est pas possible. Où aurais-tu copié cette formule et pourquoi dois-tu la copier-coller à chaque fois ? Es-tu bien certain que tu n’as pas simplement voulu dire que tu reportais des valeurs de la Feuil5 à la Feuil3 à l’aide de formules ?

Si c’est bien le cas, ça m’amènerais au point suivant. Je ne pense pas que ce soit une bonne idée de préparer en premier une macro afin de ne traiter que les colonnes A à H de la Feuil3 (réunies par ce 1 en A2) puis de penser que l’on va pouvoir étendre cette macro aux autres colonnes (J à Q pour le point 2, etc.). Il vaudrait mieux traiter toutes les colonnes à la fois.

Je te prierais donc de bien vouloir préparer un fichier dans lequel tu placeras toutes les formules nécessaires dans les colonnes B, K, T, AC, etc. de la Feuil3.

Encore une chose : tes formules de la Feuil5 renvoient parfois des 0, parfois des cellules vides. Quel est le but d’avoir ces deux différences – qui se répercutent sur la Feuil3 à la colonne B et identiques – et qui me compliquent la suite des opérations ?

Et puis également : Si sur la Feuil3, en A2, J2, S2, etc., tu plaçais un texte et un chiffre à la place d'un chiffre seul, on pourrait y faire référence (Par exemple 'Répartition 1' en A2, 'Répartition 2' en J2, etc.).

Ne brulons pas les étapes, liquidons déjà les problèmes indiqués ci-dessus et on verra plus tard pour la suite

A te relire.

Bonjour Yvouille,

Je suis arrivé à reporter les résultats et a les « figer » sur le tableau final de la « Feuil3 ! »Malgré les fct « aléatoires » de la Feuil5 ! Grâce au bouton « répartition composants » sur Feuil3 !

De plus j’ai supprimé tous les « 0 » donc il ne reste que des « blancs »

Concernant ta proposition de remplacer les formules « alea entre bornes » : je t’en remercie beaucoup mais je préfère les garder comme ça pour que je puisse avoir un peu de maitrise sur le classeur car je suis ignorant devant le VBA.

Ce que je souhaite c’est que tu adaptes ta macro (Sub aa) à mon nouveau tableau car elle fonctionne parfaitement. A savoir remplir les colonnes C-K-S-AA-AI-AQ-AY de sorte que les différentes colonnes « PT » soit égale (à +/-10%) des différents pourcentages de contrôle.

Je pense que si tu t’inspires de ta propre macro car elle était parfaite : Sub aa

Si possible supprimer les cellules vides des différentes catégories.

Ci-joint mon nouveau fichier avec tous les tableaux (formulation de 1 à 7)

Cordialement

11clas-v004.xlsm (154.28 Ko)

Re,

latef1991 a écrit :

De plus j’ai supprimé tous les « 0 » donc il ne reste que des « blancs »

Ce n’est pas tout à fait correct ; il reste des 0 invisibles et des cellules vides. Du point de vue des macros, ce n’est pas identique et ça complique un tout petit peu les choses.

Ton code ‘Copie’ est un peu dément, mais pourquoi pas !

Tu m’as répété plusieurs fois que le travail restant était simple, que ne n’avais qu’à adapter mon ancien code. Je peux alors te dire que tu te trompes passablement vu que la structure de ta feuille et que ta demande ont tellement changé ; il s’agit donc bel et bien d’un nouveau code pour lequel je peux juste pu récupérer une ou deux idées de base. La valeur de mon nouveau travail n’est dont en rien diminuée

Je n’ai pas très bien compris pourquoi les valeurs des lignes 19, 26 et 33 de la Feuil3 ne changeaient jamais.

Tu as oublié les formules dans la plage S35:S39.

Dans tes plages L35:L39, T35:T39, ….. AZ35:AZ39, tu as fait référence à la valeur de la cellule B3 au lieu de faire référence aux cellules J3, R3, ……AX3.

J’ai lié ma macro aux tiennes au travers du bouton ‘Repartition composant’. Le résultat obtenu est normalement jamais plus de 1% d’écart. On pourrait améliorer le résultat général en effectuant une correction finale du nombre d’unités, au détriment de la répartition par catégorie. Faut-il se lancer dans l’aventure ?

Afin de contrôler les écarts maximaux et minimaux, j’ai placé des infos dans les cellules B42 et B43. Comme tu arrondis tes résultats dans les cellules D35, L35, etc. à deux chiffres après la virgule, tu peux avoir l’impression que ça ne joue pas (Par exemple tu vois en D35 100 % et en B42 0.0030 %. C’est parce qu’en fait la cellule D35 (ou une cellule suivante L35, T35, etc.) présente une valeur de 100.0030 %). Si aucun chiffre n’apparait dans la cellule B42, c’est-à-dire que le résultat 0% a été atteint.

A te relire.

EDIT : VOIR EGALEMENT MON SECOND MESSAGE

15clas-v005.xlsm (162.18 Ko)
Rechercher des sujets similaires à "repartition complexe"