[COLOR="DarkSlateGray"]Sub mini_nPrix()
Dim zPrix As String, sPrix, zMoy As String, sMoy, zDat As String, sDat As String
Dim Prix, Moy, Dat, rPrix, nPrix As Integer
Dim temp, aux
Dim col As Long
Dim i As Long, j As Long, k As Long
[COLOR="SeaGreen"]'---------------------------- PARAMETRES A ADAPTER ----------------------------[/COLOR]
sPrix = "Feuil1": zPrix = "H2:Z2" [COLOR="SeaGreen"]'Liste des prix[/COLOR]
sMoy = "Feuil1": zMoy = "AG3:AG36" [COLOR="SeaGreen"]'Zone des résultats[/COLOR]
sDat = "Feuil1": zDat = "H3:Z36" [COLOR="SeaGreen"]'Table de données[/COLOR]
nPrix = 10 [COLOR="SeaGreen"]'Nombre maximum d'articles pris en compte
'------------------------------------------------------------------------------[/COLOR]
Prix = Sheets(sPrix).Range(zPrix).Value
Moy = Sheets(sMoy).Range(zMoy).Value
Dat = Sheets(sDat).Range(zDat).Value
col = UBound(Prix, 2)
ReDim rPrix(1 To col)
temp = Prix
For i = 1 To col - 1 [COLOR="SeaGreen"]'Liste des prix en ordre croissant dans 'temp'[/COLOR]
For j = i + 1 To 2 Step -1
If temp(1, j) < temp(1, j - 1) Then
aux = temp(1, j): temp(1, j) = temp(1, j - 1): temp(1, j - 1) = aux
Else
Exit For
End If
Next j
Next i
i = 1
Do Until i > col [COLOR="SeaGreen"]'Indexation de la liste des prix en ordre croissant[/COLOR]
For j = 1 To col
If temp(1, i) = Prix(1, j) Then k = k + 1: rPrix(k) = j
Next j
i = k + 1
Loop
For i = 1 To UBound(Moy, 1)
temp = 0: aux = 0
For j = 1 To col
If IsNumeric(Prix(1, rPrix(j))) And Prix(1, rPrix(j)) <> 0 Then
k = Application.Min(Application.Max(0, nPrix - aux), Dat(i, rPrix(j)))
temp = temp + k * Prix(1, rPrix(j))
aux = aux + k
End If
Next j
If aux = nPrix Then Moy(i, 1) = temp / aux Else Moy(i, 1) = "ND"
Next i
Sheets(sMoy).Range(zMoy).Value = Moy
End Sub[/COLOR]
