Répartition de valeurs en groupes homogènes

[babylonzoo]

Bonjour a tous,

j'ai souvent pour mes études besoin de faire des répartitions en groupes homogenes , je m'explique:
Chaque individiu de ma population a tester a été mesuré sur un volume qui lui est propre, je dispose dans ce cas d'un population de 102 individus avec chacun 1 valeur qui lui est propre ( mais qui peut etre identique) . je dois ensuite repartir ces individus en 7 groupes de 10 sur le critere d'un volume moyen le plus proche possible ( ainsi qu'un ecart type le plus petit possible ), en fait j'effectue un randomisation sur le critere du volume.
Actuellement voici la methode utilisée :

mise en colonne du tableau.
tri en volume croissant .
elimination des 32 individus situés aux extremes.
ajout d'une colonne "groupe" .
ajout dans cette colonne du numero de groupe grace a un table de permutation a 7 elements ( 1,2,3,4,5,7,6,-2,3,1,4,6,7,5-6,1,3,7,5,4,2....ect ).
et enfin classement par tri croissant de la colonne groupe.

N'existe t'il pas un méthode un peu plus automatisé pour effectuer ce genre de travail . je precise que je n'ai jamais le meme nombre d'individu a chaque repartition, que le nombre d'individus par groupe peut changer ( de 5 à 12 en general et que le nombres de groupes est aussi variable( 3 à 12 )

je suis debutant en VBA et j'ai essayer de chercher une soluce dans ce forum ( et d'autres ..) mais je n'ai rien trouver de concluant.
si qu'elqu'un a entendu parler ou peut me donner des pistes merci encore

ci joint le fichier de base sur lequel je travaille actuellement

 

[JNP]

Re : Répartition de valeurs en groupes homogènes

Re ,
Comme cela, ça devrait marcher (j'avais fait une petite erreur pour voir si tu suivais )

Dim DernièreColonne As Integer, Mediane As Double, I As Integer, J As Integer, [COLOR=red][B]DernièreLigne As Integer[/B][/COLOR]
DernièreColonne = Sheets("Feuil1").Cells(3, 255).End(xlToLeft).Column
DernièreLigne = Sheets("Feuil1").Cells(65536, 1).[B][COLOR=red]End(xlUp)[/COLOR][COLOR=red].Row[/COLOR][/B]
Sheets("Feuil1").Range("A3:" & ConversionXC(DernièreColonne) & "4").Copy
Sheets("Résultat").[B][COLOR=red]Cells(1, 1).[/COLOR][/B]PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Sheets("Feuil1").Range("A" & DernièreLigne & ":" & ConversionXC(DernièreColonne) & DernièreLigne).Copy
Sheets("Résultat").[B][COLOR=red]Cells(1, 2).[/COLOR][/B]PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False

Bonne nuit

 

[babylonzoo]

Re : Répartition de valeurs en groupes homogènes

salut JNP,
merci pour la solution car c'était vraiment un secré casse tête.
je t'envoie a nouveau le fichier que j'ai testé et ou il y a parfois des valeurs vides qui apparraissent dans certain cas (ici 9 groupes de 9 individus ?) et je vois pas trop ou ce situe le pbs

si il y a des valeurs que je ne veux pas prendre en compte j'ai penser a mettre une lettre a la place pour que cette valeur ne soit pas utilisée dans la repartition et déclaré une condition "If IsNumeric" est ce une bonne solution a ton avis ? a placer avant la copie des valeurs (atuellement les lettres sont prise en compte et la répartition est faussée? )
ou bien faire la liste des individus dans un userform pour declarer les colonnes de la feuilles 1 a ne pas prendre en compte.

merci encore pour ta patience

babylonzoo

 

[JNP]

Re : Répartition de valeurs en groupes homogènes

Re ,

il y a parfois des valeurs vides qui apparraissent

Les trous proviennent de la localisation de la médiane. En effet, dans ton premier échantillon, la médiane était égale à 3 valeurs d'échantillon (ce qui a tendance à prouver que l'échantillon est valable, et un bon échantillon risque d'avoir 10 valeurs semblables...). En prenant le premier, je décalais les résultats vers le bas, en prenant le dernier, je décalais vers le haut, donc je me suis arrangé pour en faire la moyenne. Ton nouvel échantillon (qui n'est pas expérimental en passant ), ne produit qu'une seule valeur et la macro ne s'arrêtait pas. Pour y remédier, change la ligne en rouge.

I = 2
While Cells(I, 2) < Mediane
I = I + 1
Wend
J = I
[COLOR=red][B]While Cells(J, 2) = Cells(I, 2)[/B][/COLOR]
J = J + 1
Wend

si il y a des valeurs que je ne veux pas prendre en compte

Là, tu m'inquiètes plus... C'est une attitude scientifique de tripatouiller les résultats ? Tu travailles pour le gouvernement sur les chiffres du chomage ? Je sais qu'on peux faire dire aux chiffres ce que l'on veut, mais bon... La méthode de suppression des extrêmes devrait suffire. J'ose espérer que ce que tu veux, c'est décaler la médiane pour tenir compte, par exemple, de beaucoup de résultats nuls... ATTENTION, c'est dangereux (au niveau des résultats, si l'échantillonnage est insuffisant, il réapparaitra des trous...), mais surtout, si tu testes les résultats d'un médicament miracle et que tu augmentes la médiane, ou pire, que sur des effets indésirables, tu descends la médiane, tu deviens un danger public !
Pour cela, il te suffit de modifier le début du code

Public Groupes As Integer, IndividusParGroupes As Integer[COLOR=red][B], CorrectionMediane As Integer[/B][/COLOR]
Sub Répartition()
Groupes = InputBox("Nombres de groupes ?", "Répartition 1")
IndividusParGroupes = InputBox("Nombres d'individus par groupes ?", "Répartition 2")
[COLOR=red][B]CorrectionMediane = InputBox("Décalage mediane (entier) ?", "Correction de la mediane")[/B][/COLOR]

et le mode de localisation

Range("A2:B" & (Int((I + J) / 2)) - _
    Int(Groupes * IndividusParGroupes / 2) [COLOR=red][B]+ CorrectionMediane[/B][/COLOR]).Delete Shift:=xlUp
Range("A" & Groupes * IndividusParGroupes + 2 & ":B" & DernièreColonne).Delete Shift:=xlUp

Voila. A +

 

[babylonzoo]

Re : Répartition de valeurs en groupes homogènes

salut JNP,

tripatouiller les valeurs ))) oh mon dieu !!!
je t'explique . les valeurs que je t'ai fourni (au moins les premieres tu as raisons, la 2eme ligne n'etait qu'un test ) sont des volumes de cellules.Ces cellules sont en croissance et on effectue 1 mesure environ tous les 2 jours pour s'assurer de leur croissance . quand celles cc arrivent a un volume corect (apres 1 a 2 mois) on effectue la répartition. le hic c'est que le volume a un jour donné n'est pas le seul critere a prendre en compte ( meme si c'est celui sur lequel sont effectuées les stats). il faut aussi tenir compte du taux de croissance, de leur aspect, et de divers autres facteurs. par contre les valeurs que je t'ai fourni sont des "données brutes" ou toutes les valeurs doivent apparaitre. donc la methode en fait est d'effectuer toute les mesures , d'eliminer les plus "capricieuses" (mais parfois tout va bien et il n'y a pas d'elimination autre que les extremes) et de faire la repartition. Voila j'espere que je t'ai rassuré.
Pour preuve, dans un de mes precedents post je te demandai si il etait possible de retrouver dans un tableau les individus avec leurs valeurs qui n'ont pas eté pris en compte pour assurer une bonne traçabilité ......et oui la l'assurance qualité exite aussi dans le milieu scientifique

je te remercie encore pour ton aide, je vais travailler sur les nouveau code que tu m'as fourni (le decalage de la medianne est interresant dans d'autres cas et je compte bien m'en servir), et me trouver un serieuse formation en VBA pour combler mes lacunes (bien que j'ai deja appri bcp grace au forum

a bientot

babylonzoo

 

[JNP]

Re : Répartition de valeurs en groupes homogènes

Re ,
Si tu tripatouilles pas les valeurs, alors je te propose plutôt de mettre la cellule en orange (ou autre couleur, il faudra juste que tu changes le ColorIndex). J'en ai profité pour sauvegarder les échantillons supprimés.
A +

 

[babylonzoo]

Re : Répartition de valeurs en groupes homogènes

salut,

avec la couleur tu veux dire que les animaux ne sont pas pris en compte dans la repart ? c'est bien )) mais je crosi qu'il disparraissent aussi de l'autre tableau enfin pas tous il en reste (en tete de liste, cf fichier joint) un en general d'apres les test que je fait.

j'essaye maintenant d'inclute la fiche de test anova dans la feuille resultats .

a+

babylonzoo

 

[JNP]

Re : Répartition de valeurs en groupes homogènes

Re ,
Je n'ai jamais compris pourquoi, mais il arrive que la détermination de la dernière cellule réagisse mal (après un clear, on dirait que ça garde des traces) et avec le décalage, les cellules oranges se mangeaient. Change cette partie du code

With Sheets("Résultat")
For I = 2 To DernièreColonne
If .Cells(I, 2).Interior.ColorIndex = 44 Then
[B][COLOR=red]J = 1
If .Cells(J, 7) = "" Then
Else
While .Cells(J, 7) <> ""
J = J + 1
Wend
End If
.Range("A" & I & ":B" & I).Copy .Cells(J, 7)
[/COLOR][/B].Range("A" & I & ":B" & I).Delete Shift:=xlUp
DernièreColonne = DernièreColonne - 1
End If
Next I
End With

A +

 

[babylonzoo]

Re : Répartition de valeurs en groupes homogènes

merci a toi pour ton aide,
je joint la derniere version avec le test stat a effectuer apres repartition.
J'ai enlevé le clear content a partir de la colonne I pour pa avoir a le recopier a chaque fois,
bien sur il faut encore copier/coller manuellement les valeurs de moyenne, de STD nb d'individus dans le tableau ANOVA ( j'ai pas encore pu copier automatiquement ces valeurs) mais le temps gagné et la qualité de la repart est impec ( anova souvent = 1 ce que est parfait !).

encore une question cependant, si les valeurs de la feuil1 sont des formules (c'est a dire le résultat de calcul d'autres feuilles par ex) y a t'il une modif a faire (surement d'apres les tests ! ) pour que tout fonctionne de la m façon.

merci encore a toi JNP et merci a excel-downloads

je laisse le post ouvert car je pense peut etre encore trouver des améliorations (en plus je sais pas comment on le ferme )

a+
babylonzoo

 

[JNP]

Re : Répartition de valeurs en groupes homogènes

Re ,
Pour fermer un post, il suffit de dire que tu es satisfait . Cela prouve que les réponses trouvées sont bonnes, et celui qui fera une recherche saura que le fruit du post est abouti pour lui .
Maintenant, il est sûr que ton fichier peut être amélioré (à longueur d'année, on améliore, code plus court, options plus larges...).
Ôter le Clear me parait une abération... Il vaudrait mieux créer un onglet par ligne de test...
Maintenant, si la ligne est un résultat de formule, la transposition devrait marcher, mais il peut être plus raisonnable de passer par un copier spécial de valeurs. Fait quelques essais avec l'enregistreur de macro, et adapte le à ton code. Ce n'est pas que je ne veuille pas continuer à t'aider, c'est que si tu ne t'appropries pas le fichier, et le code, tu finiras par avoir un outil défaillant à un moment, sans avoir le moyen de trouver une correction. Et, pour avoir testé, même quelqu'un qui pratique régulièrement VBA peut trouver des difficultés à lire le code d'un autre, car si la programmation est universelle, la méthode de programmation, elle, ne l'est pas ...
Bon courage

 

[babylonzoo]

Re : Répartition de valeurs en groupes homogènes

bonjour a tous,
ci joint la derniere version de la repartition avec les modifs suivantes :
-boutons annuler opérationnels ( j'ai été obligé de mettre des variables string)
-modif de la copie des cellules pour accepter les valeurs sous forme de formules ( là : double copie pour la valeur et la couleur car on ne peut pas copier en m temps une valeur a partir d'une formule dont la cellule est colorée)
-ajout du test anova avec bouton et macro (j'ai j'ai du bidouiller : transformation des formules moyenne - ecartype en valeur, puis copie dans le tableau anova en enlevant les espaces.)
Un grand merci encore a JNP son aide indispensable

la suite au prochain post

babylonzoo

 

[Estel372]

Re : Répartition de valeurs en groupes homogènes

Re ,
je pense que cette première ébauche te remontera le moral.
Il reste la présentation et quelques tests pour vérifier les entrées à faire.
Un petit merci à Roger2327 à qui j'ai piqué la macro pour la séquentielle aléatoire https://www.excel-downloads.com/threads/trier-aleatoirement-une-liste.118132/
Bonne soirée

Bonjour,
Je trouve ton fichier génial, c'est exactement ce que je recherche sauf que mes données de départ sont en colonnes et non en lignes. Je ne suis pas suffisamment compétente pour faire la modification. pourrais-tu m'aider ?