combinaisons multiples fichier excel

Version imprimable

1 pièce(s) jointe(s)

salut le phorum

j'ai de nouveau une requête qui m'est demandé sur le traitement d'un fichier
je vous explique
j'ai une table représentant divers éléments (voir ci dessous)

Pièce jointe 432350

j'aimerai une macro qui me permettrait de me donner toutes les combinaisons possibles de cette liste (colonne A du tableau)
Code:

1 2 3 4 5 6 7 8 9 01 A, 01 B, 02 B ... 01 A, 01 B, 02 B, 03 B, 04CD, 04CL, 05CD, 05CL, 06CD, 06CL 01 A, 01 B, 02 B, 03 B, 04CD, 04CL, 05CD, 05CL, 06CD, 52 01 A, 01 B, 02 B, 03 B, 04CD, 04CL, 05CD, 05CL, 06CD, 61 A ... 01 A, 01 B, 02 B, 03 B, 04CD, 04CL, 05CD, 05CL, 62 B, 64CL 01 A, 01 B, 02 B, 03 B, 04CD, 04CL, 05CD, 05CL, 62 B, 65CD 01 A, 01 B, 02 B, 03 B, 04CD, 04CL, 05CD, 05CL, 62 B, 65CL
j'ai bien trouvé une macro qui me génére les combinaisons mais pas les cas ou l'item est seul ou 2 ou 3
il me mets toutes les combinaisons comprenant l'intégralité des items

j'espère avoir été clair.... :calim2:

merci pour vos pistes ou solutions

david

05/12/2018, 20h27
Jean-Philippe André

Citation:

Envoyé par munity

j'aimerai une macro qui me permettrait de me donner toutes les combinaisons possibles de cette liste (colonne A du tableau)
...

j'ai bien trouvé une macro qui me génére les combinaisons mais pas les cas ou l'item est seul ou 2 ou 3
il me mets toutes les combinaisons comprenant l'intégralité des items

Salut,

si ta fonction arrive à sortir la liste des combinaison, peux tu faire en sorte qu'elle se limite à un, deux,... n éléments en sorties ?

salut jean philippe

non malheureusement, il faut dire que j'ai juste fait un copier coller sans vraiment comprendre comment fonction la macro
cela me paraissait bon mais après analyse je me suis rendu compte qu'il me manquant les éléments à 1, 2,3 jusque 6
sachant que j'ai 10 items au total
et mon autre souci c'est que j'ai plus d'un million de ligne ce qui est bon dans xl 2007 mais bon le fichier est très lourd et compliqué à manipuler......
voici le code

Code:

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
 
 
  '1. En A1, écrire c ou p ; (Combinaison ou Permutation)
  '2. En A2, écrire la valeur de R ;
  '3. Sous A2, écrire la liste des N éléments ;
  '4. Sélectionner A1 et activer la procédure.
 
'Exemple:
'A1 c
'A2 3
'A3 1
'A4 2
'A5 Excel
'A6 4
'A7 *
'A8 6
'
'La procédure donne alors la liste de toutes les combinaisons
'possibles de 3 éléments choisis parmi 6.
 
 
Option Explicit
 
Dim vAllItems As Variant
Dim Buffer() As String
Dim BufferPtr As Long
Dim Results As Worksheet
 
                              'procédure1
 
 
 
Sub ListPermutations()
Worksheets("combinaisons").Select
Range("A1").Select
  Dim Rng As Range
  Dim PopSize As Integer
  Dim SetSize As Integer
  Dim Which As String
  Dim N As Double
  Dim message As Integer
  Dim nom As String
  Dim sh As Worksheet, trouvé As Boolean
  trouvé = False
 
  message = InputBox("nombre d'actifs?", "Combinaison des actifs", 3)
  Range("A2") = message
  Const BufferSize As Long = 4096
 
  Set Rng = Selection.Columns(1).Cells
  If Rng.Cells.Count = 1 Then
    Set Rng = Range(Rng, Rng.End(xlDown))
  End If
 
  PopSize = Rng.Cells.Count - 2
  If PopSize < 2 Then GoTo DataError
 
  SetSize = Rng.Cells(2).Value
  If SetSize > PopSize Then GoTo DataError
 
  Which = UCase$(Rng.Cells(1).Value)
  Select Case Which
  Case "C"
    N = Application.WorksheetFunction.Combin(PopSize, SetSize)
  Case "P"
    N = Application.WorksheetFunction.Permut(PopSize, SetSize)
  Case Else
    GoTo DataError
  End Select
  If N > Cells.Count Then GoTo DataError
 
  Application.ScreenUpdating = False
 
 
 
  nom = "résultats"
  Set Results = Worksheets.Add
  Sheets("Feuil1").Name = "résultats"
 
  vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value
  ReDim Buffer(1 To BufferSize) As String
  BufferPtr = 0
 
  If Which = "C" Then
    AddCombination PopSize, SetSize
  Else
    AddPermutation PopSize, SetSize
  End If
  vAllItems = 0
 
  Application.ScreenUpdating = True
  Exit Sub
 
DataError:
  If N = 0 Then
    Which = "Enter your data in a vertical range of at least 4 cells. " _
      & String$(2, 10) _
      & "Top cell must contain the letter C or P, 2nd cell is the number" _
      & "of items in a subset, the cells below are the values from which" _
      & "the subset is to be chosen."
  Else
    Which = "This requires " & Format$(N, "#,##0") & _
      " cells, more than are available on the worksheet!"
  End If
  MsgBox Which, vbOKOnly, "DATA ERROR"
  Exit Sub
End Sub
 
Private Sub AddPermutation(Optional PopSize As Integer = 0, _
  Optional SetSize As Integer = 0, _
  Optional NextMember As Integer = 0)
 
  Static iPopSize As Integer
  Static iSetSize As Integer
  Static SetMembers() As Integer
  Static Used() As Integer
  Dim i As Integer
 
  If PopSize <> 0 Then
    iPopSize = PopSize
    iSetSize = SetSize
    ReDim SetMembers(1 To iSetSize) As Integer
    ReDim Used(1 To iPopSize) As Integer
    NextMember = 1
  End If
 
  For i = 1 To iPopSize
    If Used(i) = 0 Then
      SetMembers(NextMember) = i
      If NextMember <> iSetSize Then
        Used(i) = True
        AddPermutation , , NextMember + 1
        Used(i) = False
      Else
        SavePermutation SetMembers()
      End If
    End If
  Next i
 
  If NextMember = 1 Then
    SavePermutation SetMembers(), True
    Erase SetMembers
    Erase Used
  End If
 
End Sub  'AddPermutation
 
Private Sub AddCombination(Optional PopSize As Integer = 0, _
  Optional SetSize As Integer = 0, _
  Optional NextMember As Integer = 0, _
  Optional NextItem As Integer = 0)
 
  Static iPopSize As Integer
  Static iSetSize As Integer
  Static SetMembers() As Integer
  Dim i As Integer
 
  If PopSize <> 0 Then
    iPopSize = PopSize
    iSetSize = SetSize
    ReDim SetMembers(1 To iSetSize) As Integer
    NextMember = 1
    NextItem = 1
  End If
 
  For i = NextItem To iPopSize
    SetMembers(NextMember) = i
    If NextMember <> iSetSize Then
      AddCombination , , NextMember + 1, i + 1
    Else
      SavePermutation SetMembers()
    End If
  Next i
 
  If NextMember = 1 Then
    SavePermutation SetMembers(), True
    Erase SetMembers
  End If
 
End Sub  'AddCombination
 
Private Sub SavePermutation(ItemsChosen() As Integer, _
  Optional FlushBuffer As Boolean = False)
 
  Dim i As Integer, sValue As String
  Dim j As Integer
  Dim message As Integer
 
  Static RowNum As Long, ColNum As Long
 
  If RowNum = 0 Then RowNum = 1
  If ColNum = 0 Then ColNum = 1
 
  If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then
    If BufferPtr > 0 Then
      If (RowNum + BufferPtr - 1) > Rows.Count Then
        RowNum = 1
        ColNum = ColNum + 1
        If ColNum > 256 Then Exit Sub
      End If
 
      Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _
        = Application.WorksheetFunction.Transpose(Buffer())
      RowNum = RowNum + BufferPtr
    End If
 
    BufferPtr = 0
    If FlushBuffer = True Then
      Erase Buffer
      RowNum = 0
      ColNum = 0
      Exit Sub
    Else
      ReDim Buffer(1 To UBound(Buffer))
    End If
 
  End If
  'construct the next set
  For i = 1 To UBound(ItemsChosen)
  j = 1
  sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
  'and save it in the buffer
  Next i
  BufferPtr = BufferPtr + 1
  Buffer(BufferPtr) = Mid$(sValue, 3)
  End Sub

je l'ai récupéré sur ce post
https://www.developpez.net/forums/d3...epetition-vba/

si tu y comprends quelque chose qui pourrait me permettre de la modifier comme je le souhaites.....

ca serait cool

david

06/12/2018, 16h29
Jean-Philippe André

Salut,

comme la quantité de permutations possibles est ridiculement élevée, je pose la question de la pertinence de ton besoin :)

Que comptes tu faire en sortie de cette liste exhaustive des combinaisons ?
06/12/2018, 21h21
munity

2 pièce(s) jointe(s)

Salut Jean Philippe

bon je vais essayer de résumer mon besoin
j'ai ouvert un post afin de réduire le nombre de possibilité car au final j'ai appris que toutes les valeurs ne sont pas nécessaires et donc que certaines sont à exclure
de ce coté tout est réglé (ci dessous le post)
https://www.developpez.net/forums/d1...ne-x-criteres/

le ménage étant fait, j’obtiens donc une colonne concaténée uniquement avec les valeurs susceptibles de me retourner le résultat
donc voici le tableau récapitulatif avec une liste de cas et les valeurs à indiquer suivant la combinaison
Pièce jointe 432603

et au final, le résultat souhaité
Pièce jointe 432606

donc mon idée était de concaténer les colonnes REV1 A REV10 en excluant les données inutiles (cette opération est faite)
puis de faire un recherchev sur l'ensemble des combinaisons possibles afin de récupérer ma valeur texte
voilà mon besoin Jean Philippe
alors est ce possible selon toi dans excel, ma méthode est elle judicieuse ?
ou alors il faut que je travaille autrement ?
parce je me rends compte que je vais devoir renseigner manuellement le texte correspondant au différentes combinaisons et ca parait également être ridiculement chronophage et source d'erreur

en résumé donc je suis un peu perdu et un peu voire beaucoup de conseil me serait fort utile

merci d'avance à tous

david
06/12/2018, 21h42
Jean-Philippe André

Question à 10 sous : tu peux passer sous Access plutôt que d'essayer de faire ca sous Excel ?

Tu vas méchamment galérer, car ta recherche textuelle se fera par morceau, vu que tu n'as meme pas de tri par ordre alphabétique pour l'instant...
06/12/2018, 21h57
munity

Non je ne suis pas raciste
Access peut me convenir
Donc si je te suis je construit la base dans access et cree un lien vers excel ou tout doit se faire dans access?
06/12/2018, 22h11
Jean-Philippe André

Disons que pour comparer les données de la sorte, ca reste plus pertinent de les stocker et les administrer depuis/dans Access.

Concernant la partie recherche sur une combinaison de valeur, Access me semble bien pour stocker tout, Excel pour faire des calculs ultérieurs
07/12/2018, 17h42
munity

1 pièce(s) jointe(s)

oui je pensais également partir avec access pour développer la base de combinaisons et de valeurs associées
par contre comment faire pour créer les combinaisons et les valeurs associées depuis access
sachant que cette base doit pouvoir être facilement mise à jour si nécessaire (bien que logiquement statique pour un certain temps)
j'ai réussi à avoir du client la liste définitive des concordances
sans vouloir exagérer, pourrais tu me guider sur la marche à suivre pour établir la base de valeur :oops:
Pièce jointe 432799

merci beaucoup

david