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

Bonjour Babylonzoo et bienvenue ,
Je suis un candide en stats (c'est vieux la fac ), mais d'après tes demandes, j'ai l'impression que certaines formules existantes sous Excel te permettrait d'obtenir rapidement tes résultats, les statistiques sont très poussées.
Il y a aussi la possibilité d'ouvrir Analisys ToolPack en plus.
D'autre part, tu peux télécharger MoreFunc qui contient entre autre

[B]Statistiques[/B]
 
[I]Ce lien n'existe plus[/I] Evalue l'écart-type d'une population groupée en classes
[I]Ce lien n'existe plus[/I] Calcule l'écart-type d'une population groupée en classes
[I]Ce lien n'existe plus[/I] Renvoie les N plus grandes valeurs d'une table
[I]Ce lien n'existe plus[/I] Renvoie les N plus petites valeurs d'une table
[I]Ce lien n'existe plus[/I] Renvoie le nombre de valeurs distinctes d'une plage
[I]Ce lien n'existe plus[/I] Dénombrement conditionnel sur une plage 3D
[I]Ce lien n'existe plus[/I] Evalue la variance d'une population groupée en classes
[I]Ce lien n'existe plus[/I] Calcule la varianced'une population groupée en classes

sur Ce lien n'existe plus
Pour VBA, il faudrait déterminer quelques cas d'école (ton exemple ne contient ni les opérations intermédiaires que tu faits à la main, ni le résultat final) pour voir quelles sont les automatismes, et quelles sont les valeurs que tu souhaites pouvoir paramétrer (est-ce toujours 32 extrèmes [16 et 16 je penses] ou ça peux changer suivant le nombre d'échantillons ?). L'ajout via une table de permutation ne me parle pas du tout par exemple.
Bon courage

 

[babylonzoo]

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

merci JNP pour ta reponse et ton aide,
je vais regarder les formules excel pour voir ce que je peux faire avec.
Comme je l'ai ecrit, tout est varaibles dans c'est experiences ( nd individus, nb de groupe, nb d'individus par groupe et bien sur le nb d'individus "ecartés" de la réparttition).
la table de permutation permet d'attribuer un n° de groupe au hasard il y a des tables de permutation de 3 à n éléménts en fnc du nombre de groupes.
peut etre quand vba on peut faire qqchose du style definir la moyenne puis en partant de cette valeur "centrale" d'attribuer un individu a un groupe et de s'ecarter de cette moyenne j'usqua atteindre le nombre d'individus necessaires pour la répartition, ainsi les extremités serait automatiquement exclues !
bon va falloir que je me mette serieusement au vba moi !

merci encore pour tes infos

toutes les idées sont les bienvenues

Babylonzoo

 

[babylonzoo]

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

Bonjour a tous,

personne pour me donner un petit coup de main pour ce probleme de répartion en groupes homgènes ?
merci
babylonzoo

 

[JNP]

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

Re ,
Le problème est de définir tes bornes. Il est possible, par exemple, d'avoir des InputBox qui vont rentrer les extrèmes à exclure, mais je pense que ton exemple est trop vague pour l'instant pour qu'on puisse vraiment t'aider... Nous n'avons toujours pas la réponse à la question primordiale que je posais

Pour VBA, il faudrait déterminer quelques cas d'école (ton exemple ne contient ni les opérations intermédiaires que tu faits à la main, ni le résultat final) pour voir quelles sont les automatismes, et quelles sont les valeurs que tu souhaites pouvoir paramétrer

A te lire

 

[babylonzoo]

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

bonjour a tous,
je n'ai peut etre pas été super clair lors de mon explication !!
je vous joint donc un exemple complet de répartition. Chaque etape ce trouve dans un onglet du fichier. j'espere que cela pourra eclaircir mon besoin.
encore merci pour votre patience. Ce type de répartition est pas mal utilisé en biologie et je pense qu'une solution pour automatiser le principe pourrai etre la bienvenue (j'ai pas mal cherché mais rien trouvé).
bravo encore pour ce site
a+
babylonzoo

 

[JNP]

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

Bonsoir Babylonzoo ,
C'est effectivement beaucoup plus clair. Pour la répartition séquencielle, il s'agit bien de croissant/décroissant/croissant pair puis impair/décroissant et bis repetita ?
Bonne soirée

 

[babylonzoo]

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

salut JNP
pour la repartition en fait on prend une matrice a 7 elements c a dire les chiffres de 1 a 7 repartis de façon aléatoire. On peut ausssi faire de 1 a 7 10 fois de suite mais c'est beaucoup rigoureux d'un point de vu repartition.
j'espere que j'ai eté clair.
merci encore pour ton aide
babylonzoo

 

[JNP]

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

 

[babylonzoo]

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

salut a tous,
alors là chapeau MONSIEUR JNP !!
je suis épaté c'est exactement ce que je voulais, tu as parfaitement saisi le besoin.
Encore peut etre qqs petit probleme pour par exemple 10 groupes et 10 individus ou la case a1 est remplacé par un numéro et ou parfois certaines cases vides apparraissent mais je vais tester tout cela a fond et regarder attentivement le code pour essayer de comprendre tout cela (j'ai quand m vu que tu avais pris la medianne comme reference ce qui est parfait).
Par contre est t'il possible de retrouver sous forme de tableau les individus non pris en compte ainsi que leurs valeurs ?
En ajoutant le tableau que j'avais deja fait pour effectuer une analyse de variance se sera parfait (je joindrai ce tableau lundi pour voir si on peut l'integrer facilement a la feuille)
Merci encore et je regarde cela attentivement ce WE!
Baylonzoo

 

[babylonzoo]

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

bonjour,
j'ai regarder la macro que tu m'as fourni et je dois avouer que si comprend le sens général qqs détails m'echappent , mais je ne suis pas un spécialiste!

Mediane = Application.WorksheetFunction.Median(Sheets("Résultat").Range("B2:B103"))

je pense que cette ligne est une exemple vu que le range peut etre varaible selon le nonbre d'individus (Range("B2:B103")) )
C'est peut etre pour cela qu'il y a qqs bugs quand le nombre d'individus a repartir est importants ( par ex 10 x 10)

je ne comprends pas surtout les 2 dernieres fonction de convertion ?

sinon encore 2 details , je t'envoie deux feuilles résultats et design.
le premiere tu le connais deja sauf que tu verras que les valeurs se rajoutent par ligne ( les lignes sont masqués et se demasquent lorsque les valeurs sont saisies ) donc la ligne a choisr pour les valeurs est toujours la derniere remplie ( celle des individus est fixe)

est t'il possible de lié une fois la repartition effectuées les numeros d'individus dans la feuiile design ? C'est a dire remplir le tableau des individus en fonction de la repartition effectué pour eviter de le faire manuellement

merci encore de ta patience

babylonzoo

 

[JNP]

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

Bonsoir ,
Pour le médiane, effectivement, j'ai oublié de transcrire la largeur du tableau soit

Mediane = Application.WorksheetFunction.Median(Sheets("Résultat").Range("B2:B" & DernièreColonne))

Les 2 fonctions de conversions (valables uniquement jusqu'Excel 2003), permettent de convertir une colonnne du type AA en nombre et réciproquement. J'en avais besoin parce que ton tableau de base était horizontal...

Sheets("Feuil1").Range("A3:" & ConversionXC(DernièreColonne) & "4").Copy

Pour partir de la dernière ligne remplie, les exemples fourmillent sur le forum, donc il serait peut-être bien que tu y mettes un peu du tien .
Bonne nuit

 

[babylonzoo]

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

Bonsoir a tous,

il est bien entendu que je cherche aussi des solution de mon coté
j'ai bien sur essyé de dechiffrer la ligne :

Sheets("Feuil1").Range("A3:" & ConversionXC(DernièreColonne) & "4").Copy

je pense que c'est le "4" qui permet de choisir la deuxieme ligne, je connais la fonction "End(xlUp).Row" que j'utilise pas mal depuis 1 mois que je me suis mis au VBA mais je crois que je manque encore de "vocabulaire" )) et j'ai pas encore reussi a l'integrer pour changer la ligne 4 en derniere ligne non vide ....mais je desespere pas c pas mon genre! bon la je suis naze mais je reprendrai demain )

a+

babylonzoo

 

[JNP]

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

Re ,
De la même façon que

DernièreColonne = Sheets("Feuil1").Cells(3, 255).End(xlToLeft).Column
Sheets("Feuil1").Range("A3:" & ConversionXC(DernièreColonne) & "4").Copy

où DernièreColonne regarde la dernière cellule à droite, il va te falloir un DernièreLigne

DernièreLigne = Sheets("Feuil1").Cells(65536, 1).End(xlToLeft).Column
Sheets("Feuil1").Range("A" & DernièreLigne & ":" & ConversionXC(DernièreColonne) & DernièreLigne).Copy

ATTENTION il te faudra faire 2 copier/coller transposé vu que les 2 lignes ne seront pas ensembles...
Bonne journée

 

[babylonzoo]

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

salut a tous,

bon ben je n'arrive pas integrer la copie de la derniere ligne saisie dans mon tableau je dois surment mal m'y prendre !

Sub Répartition()
Groupes = InputBox("Nombres de groupes ?", "Répartition 1")
IndividusParGroupes = InputBox("Nombres d'individus par groupes ?", "Répartition 2")
Application.ScreenUpdating = False
Sheets("Résultat").Cells.ClearContents
Dim DernièreColonne As Integer, Mediane As Double, I As Integer, J As Integer, DernièreLigne As Integer

DernièreColonne = Sheets("Feuil1").Cells(3, 255).End(xlToLeft).Column
DernièreLigne = Sheets("Feuil1").Cells(65536, 1).End(xlToLeft).Column
Sheets("Feuil1").Range("A3:" & ConversionXC(DernièreColonne) & "4").Copy
Sheets("Feuil1").Range("A" & DernièreLigne & ":" & ConversionXC(DernièreColonne) & DernièreLigne).Copy

Sheets("Résultat").Cells(1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Sheets("Résultat").Select
Columns("A:B").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


en fait ne ne voit pas ou integrer le 2eme collage transposé , j'ai tout essayé mais ça na fonctionne pas . j'ai bien ajouter la variable dernière ligne puis explicité celle ci ( mais peut etre pas au bon endroit) il faut dire que j'ai encore bien du mal à "lire" le vocabulaire avec des variables et je pense que le "4" pose un probleme (mais pas sur !)

bon bref je nage !

mais je comprends aussi que donner le code comme ça n'est pas tellement formateur. peut etre est ce encore un peu balaise pour moi c tout . mais bon je me decourage pas

babylonzoo

ci joint un fichier excel qui permet par une analyse de variance de vérifier l'homogénéité des groupe ( la valeur PR>F doit etre supérieure a 0.05 et s'approcher au max de 1 ) ça pourra peut être servir a quelqu'un pour éventuellment faire en premier lieu une répartition homogene et ensuite de la vérifier grace a test ou encore mieux intégrer les 2 dans une seule manip

a+

babylonzoo