Formule pour calculer les x plus grandes valeurs qui font 80% du total

[amartin]

Bonjour à tous, je cherche une formule calculant automatiquement les "x" plus gros pays qui font 80% du chiffre d'affaires...

J'arrive à calculer "x" (cf. fichier joint) mais suis obligé pour cela de doubler mes colonnes de calculs, etc., ce que j'aimerais à tout prix éviter car cela alourdit considérablement mon tableau (plusieurs milliers de lignes!).

Si quelqu'un connait une formule combinant les fonctions somme prod, grandes.valeurs, etc. permettant de répondre à ma demande, je suis preneur !!!

D'avance, un énorme merci !!!

NB: Le tableau joint est une version (très) allégée comprenant seulement 3 lignes et 60 colonnes.
"x" = 11 pour la 1ère ligne car les 11 meilleurs pays (parmi 60) réalisent à eux seuls 80% du CA. "x" = 10 pour la 2ème ligne, "x"=7 pour la 3ème, etc.

 

[PMO2]

Re : Formule pour calculer les x plus grandes valeurs qui font 80% du total

Bonjour,

Une solution avec une fonction personnalisée.
Je ne me suis occupé que de "je cherche une formule calculant automatiquement les "x" plus gros pays qui font 80% du chiffre d'affaires…"

Copiez le code suivant dans un module standard

Function xPlusGros_pmo(Plage As Range, Pourcentage As Double) As Long
Dim Somme#
Dim Reste#
Dim Total#
Dim Maxi#
Dim var
Dim j&
Dim cpt&
Application.Volatile
If Pourcentage > 1 Then Exit Function
var = Plage
For j& = 1 To UBound(var, 2)
  Somme# = Somme# + var(1, j&)
Next j&
Reste# = Somme# * Pourcentage
Do Until Total# >= Reste#
  Maxi# = Application.WorksheetFunction.Max(var)
  Total# = Total# + Maxi#
  For j& = 1 To UBound(var, 2)
    If var(1, j&) = Maxi# Then var(1, j&) = 0
  Next j&
  cpt& = cpt& + 1
Loop
xPlusGros_pmo = cpt&
End Function

Exemple d'utilisation
1) en BL15, tapez la formule =xPlusGros_pmo(D4:BK4;80%)
OU
1) en BK12, tapez 80%
2) en BL12, tapez la formule =xPlusGros_pmo(D4:BK4;BK12)

La première façon recourt à une valeur nommée pour le paramètre Pourcentage de la fonction.
La deuxième façon recourt à une référence de cellule pour le paramètre Pourcentage de la fonction.

Cordialement.

PMO
Patrick Morange

 

[amartin]

Re : Formule pour calculer les x plus grandes valeurs qui font 80% du total

Alors là, bravo!
Cela correspond exactement à ma demande, un énorme merci pour le temps consacré et la solution à ma reqûete !
Je vais essayer de comprendre le code car je suis débutant en VBA...
Encore merci Patrick !!!