Selection avec répartition des valeurs sur les colonnes

**lili31** · 14/04/2012, 19h21

Bonjour,

Voici mon problème.

J'ai un tableau de 1000 lignes et 10 colonnes.
Dans chaque cellule, j'ai la valeur A,B ou C.

Je souhaite faire une sélection de 100 lignes parmi les 1000, respectant une certaine répartition de A, B et C sur chaque colonne.
Je m'explique :
J'aimerais par exemple avoir dans mes 100 lignes sélectionnées 30% de A, 20% de B et 50% de C dans la colonne 1, puis 25% de A, 35% de B et 40% de C dans la colonne 2, ainsi de suite avec une répartition différente dans les 8 autres colonnes.

Cette sélection ne pouvant être qu'approximative, j'aurais besoin de définir un écart, par exemple 5%, me permettant d'obtenir :
dans la colonne 1, 25-35% de A, 15-25% de B et 45-55% de C, ainsi de suite dans les autres colonnes.

Je pensais à utiliser une méthode statistique avec une loi normale (moyenne et écart type).
Je ne sais pas du tout si cette méthode est viable pour résoudre mon problème.

J'attends vos propositions ou solutions avec impatience.

Merci d'avance,
Lili

**L'Albatros** · 15/04/2012, 11h29

Bonjour,
Ton tableau est-il remplit aléatoirement (et alors suivant quelle loi), ou bien est-ce toi qui veut opérer une sélection aléatoire sur un échantillon donné ?

'--------------------------------------------------------------------------
Si j'ai pu vous aider, merci de me le faire savoir en me donnant un pouce

**lili31** · 15/04/2012, 12h42

Bonjour,

Mon tableau est rempli aléatoirement, un exemple sera plus parlant, j'ai joint mon fichier xls en copie.

J'ai regardé sur le net, et mon besoin correspondrait à la définition d'un échantillon à l'aide d'un tirage aléatoire respectant certaines contraintes.

Lili

**L'Albatros** · 15/04/2012, 14h27

Ok, c'est plus un problème de maths que de développement.
Bon tu as de la chance je suis étudiant en maths appliquées...
Dis-moi juste si tu as des contraintes au niveau de l'algorithme qui sera développé (à part une gaussienne, tu peux utiliser d'autres lois, et si oui, as-tu d'autres idées ?)
A tout à l'heure

'-------------------------------------------------------------------------
Si j'ai pu vous aider, merci de me le faire savoir en me donnant un pouce

**lili31** · 15/04/2012, 15h08

Tu as raison, c'est effectivement plus un problème de maths.

Je n'ai aucune contrainte au niveau de l'algorithme, je ne sais pas quelle loi serait la mieux adaptée pour résoudre mon problème.
Je cherche juste une méthode qui fonctionne. Si avec une gaussienne, cela fonctionne, ça me va parfaitement.

**L'Albatros** · 15/04/2012, 16h46

Je ne crois pas qu'il existe des fonctions toutes faites avec VBA.
D'habitude, je programme ce type d'algo sur SAS ou encore R.
Cependant cela doit être faisable, voici l'algo que je propose:

Etape 1: Tirage d'un échantillonnage aléatoire de 100 lignes
Etape 2: Vérification des conditions de répartition de A, B et C sur chaque colonne de l'échantillon tiré
Si oui : OK
Si non: on recommence avec un nouveau tirage (avec une boucle while)

pour le tirage:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
'Déclaration des variables
    Dim compteur_boucle As Integer
    Dim Max_borne As Long
    Dim Min_borne As Long
    Dim nombre_aleatoire As Integer
 
    'Initialisation des bornes de l'ensemble des nombres aléatoires (futurs numéro de lignes)
    Max_borne = 1000
    Min_borne = 2
 
    'Tirage aléatoire de 100 lignes, copie de ces lignes dans la feuille 2 du classeur
    For compteur_boucle = 1 To 100
        Sheets(Feuil1).Select
        nombre_aleatoire = Int(Rnd * (Max_borne + 1 - Min_borne)) + Min_borne
        Range("A" & nombre_aleatoire).EntireRow.Copy
        Sheets(Feuil2).Select
        Range("A" & compteur_boucle).PasteSpecial
    Next compteur_boucle

Qu'en penses-tu ?

'----------------------------------------------------------------------
Si j'ai pu vous aider, merci de me donner 1 pouce

**lili31** · 15/04/2012, 17h02

En choisissant l'échantillon au hasard, cela va boucler à l'infini, il ne trouvera jamais un échantillon respectant toutes les contraintes.
Je dois avoir 10 contraintes, 1 sur chaque colonne, cela donnera :

1ère contrainte : 50% de A, 35% de B et 15% de C sur la colonne1
2ème contrainte : 45% de A, 30% de B et 25% de C sur la colonne2
. . .
10ème contrainte : 25% de A, 45%de B et 30% de C sur la colonne10

Il me faut une méthode bien précise pour choisir mon échantillon en fonction des contraintes.

**L'Albatros** · 15/04/2012, 18h34

Si. Le PC va ramer pendant un bon bout de temps, mais il va trouver.
Une fois l'algorithme déterminé, on optimisera le code en utilisant une variable de type tableau. Le temps de calcul sera divisé par un nombre à 3 chiffres.

Sinon, on peut filtrer d'abord puis tirer au sort ensuite parmi les lignes ciblées.
Cela revient à tester un très très grand nombre de combinaisons... et cela va ramer tout autant...

Ma méthode est "brutale" mais théoriquement, elle fonctionne au bout d'un certain temps, probablement long, mais que l'on pourra réduire par la suite.

Qu'en penses-tu ?
Et toi Ô Forum, n'aurais-tu pas une idée de fonction statistique toute prête dispo sur VBA ?

'-----------------------------------------------------------------------
Si j'ai pu vous aider, merci de me donner un pouce

**lili31** · 15/04/2012, 19h35

L'algo que tu me proposes ne fonctionnera pas tout le temps car, lorsque je vérifierai les conditions de répartition de A,B et C, je tomberai souvent dans le cas où il n'y a pas de solution.
Par exemple, je cherche une répartition sur la colonne2 25% de A, 35% de B, 40% de C. Même si je m'autorise un delta de 5%, dans certains cas, au mieux je n'aurais que 30% de C, et là l'algo bouclera.

Mon problème comporte 10 contraintes, il me faut trouver une solution approximative.

Peut-être devrais-je mettre mon sujet dans la partie mathématiques ?

**L'Albatros** · 15/04/2012, 23h00

J'entends

Cela concerne la "sensibilité" du filtre bouclé. Cela équivaudra "juste" à un code plus élaboré qu'une simple condition de type:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
'Comptage des A, B et C
        If Cells(Nombre_aléatoire, numero_colonne).Value = "A" Then
            compteur_A = A + 1
        End If
        If Cells(Nombre_aléatoire, numero_colonne).Value = "B" Then
            compteur_B = B + 1
        End If
        If Cells(Nombre_aléatoire, numero_colonne).Value = "C" Then
            compteur_C = C + 1
        End If
 
        'Ciblage des cellules potentiellement tirables
        If (compteur_A > 4) And (compteur_B > 3) And (compteur_C > 1) Then
            'marquage de la ligne
        End If

Ecoute, mets un post sur un forum de maths, de mon côté je pose la question à mon prof de stat. (en plus si ça se trouve il existe une formule toute bidon...)
En attendant, mets moi au moins 1 pouce vert stp...

**lili31** · 16/04/2012, 14h00

Cool, j'attends ton retour pour voir si ton prof de stat a une idée.

Merci,
Lili

**lili31** · 17/04/2012, 19h53

As-tu pu contacter ton prof de stats ?

**L'Albatros** · 17/04/2012, 20h31

Je le vois demain soir

Pour info, de quel délai disposes-tu ?

'------------------------------------------------------------------------
Si j'ai pu vous aider, merci de me le dire avec 1 pouce vert

**lili31** · 17/04/2012, 20h44

Ce n'est pas urgent, j'espère trouver une solution d'ici la fin du mois d'avril.

**AlphaScorpi** · 17/04/2012, 23h38

bonsoir.

Si j'ai bien saisie le truc. Il faut "trouver" parmi ces 1000 lignes, une sélection de 100 lignes (donc par forcement jointes) qui corresponde aux contraintes exposées et ce pour chacune des colonnes. (avec une approximation autorisée)

Doit on utiliser la base fournie avec le fichier excel ? Par ce que si tel est le cas. Il n'y a pas de colonne 1. Et certaines colonnes ne contiennent pas toutes les lettres...

J'ai pris le loisir d'essayer avec une série aléatoire en feuille2 obtenue avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub ABC()
 
      Dim i As Integer
      Dim j As Integer
      Dim k As Byte
      Dim WS2 As Worksheet
      Set WS2 = ThisWorkbook.Worksheets("Feuille2")
      With WS2
            For i = 1 To 1000
                  For j = 1 To 10
                        Randomize
                        k = Int(Rnd * 3)
                        Select Case k
                              Case Is = 0
                                    .Cells(i, j) = "A"
                              Case Is = 1
                                    .Cells(i, j) = "B"
                              Case Is = 2
                                    .Cells(i, j) = "C"
                        End Select
                  Next j
            Next i
      End With
End Sub

Après je fais ça :

Dans la plage "R2C12:R11C15" les conditions

==> "Colonne 1 - 50 - 35 - 15"
==> "Colonne 2 - 45 - 30 - 25"
etc...

Et Plage "R14C12:R14C13"
==> "Delta - 5 "

Les lignes solutions apparaissent colonne 17

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
Sub TestRech()
      Dim Haz As New Collection
      Dim Z As Long
      Dim k As Long
      Dim i As Long
      Dim j As Integer
      Dim A As Integer, B As Integer, C As Integer, D As Integer
      Dim L(3) As String
      Dim WS2 As Worksheet
      Dim WS3 As Worksheet
      Set WS2 = ThisWorkbook.Worksheets("Feuille2")
      Set WS3 = ThisWorkbook.Worksheets("Feuille3")
 
      D = WS2.Cells(14, 13)
1
      Do
            Randomize
            k = Int(Rnd * 1000 + 1)
            On Error Resume Next
            Haz.Add k, CStr(k)
            On Error GoTo 0
      Loop Until Haz.Count = 100
 
      For i = 1 To 100
            For j = 1 To 10
                  WS3.Cells(Haz(i), j) = WS2.Cells(Haz(i), j)
            Next j
      Next i
 
      With WS3
 
 
            For i = 1 To 10
                  A = WorksheetFunction.CountIf(.Cells(1, i).Resize(1000), "A")
                  If A < WS2.Cells(1 + i, 13) + D And A > WS2.Cells(1 + i, 13) - D Then
                        B = WorksheetFunction.CountIf(.Cells(1, i).Resize(1000), "B")
                        If B < WS2.Cells(1 + i, 14) + D And B > WS2.Cells(1 + i, 14) - D Then
                              C = WorksheetFunction.CountIf(.Cells(1, i).Resize(1000), "C")
                              If C < WS2.Cells(1 + i, 15) + D And C > WS2.Cells(1 + i, 15) - D Then
                                    If i = 10 Then
                                          MsgBox "Solution"
                                          For j = 1 To 100
                                                WS2.Cells(j, 17) = Haz(j)
                                          Next j
                                          Exit Sub
                                    End If
                              Else
                                    Exit For
                              End If
                        Else
                              Exit For
                        End If
                  Else
                        Exit For
                  End If
            Next i
 
            .Cells.ClearContents
      End With
 
      Set Haz = New Collection
      GoTo 1
 
End Sub

Le temps de réponse croit exponentiellement quand delta tend vers 0
(Débuter avec D=20 puis descendre)

(Ps : Code à confirmer, il se fait tard, j'ai peux être manqué quelque chose)

**L'Albatros** · 18/04/2012, 19h32

Désolé lili je n'ai pas réussi à attraper le prof que je voulais voir...

Dans l'algorithme d'AlphaScorpi il y a un truc qui cloche du point de vue maths:

Est-ce que tu réalises un tirage avec ou sans remise ?
Est-ce que tu prends les 100 premières lignes qui respectent les contraintes, ou est-ce que c'est à ce moment que tu effectues un tirage aléatoire ?

Merci d'avance

**lili31** · 18/04/2012, 20h28

AlphaScorpi, je vais regarder ton algo.
J'avais pensé tout comme toi à faire un random sur le choix des lignes, mais l'optimisation du delta risque de prendre du temps.

Ce que j'aimerais, c'est plutôt une méthode mathématique permettant de trouver automatiquement une des solutions les plus optimales. Je suis sûre qu'il doit y avoir une méthode statistique, en suivant une loi ou quelque chose de ce style.

Je fais un tirage sans remise, je ne dois pas prendre 2 fois la même ligne.
J'aurais aimé faire un tirage aléatoire respectant les contraintes.

**AlphaScorpi** · 18/04/2012, 23h12

Envoyé par L'Albatros

Dans l'algorithme d'AlphaScorpi il y a un truc qui cloche du point de vue maths:

Est-ce que tu réalises un tirage avec ou sans remise ?
Est-ce que tu prends les 100 premières lignes qui respectent les contraintes, ou est-ce que c'est à ce moment que tu effectues un tirage aléatoire ?

Merci d'avance

C'est un tirage sans remise. J'utilise une collection pour éviter les doublons.
Le tirage est aléatoire.