Calculer un Taux

[EddyValier]

Bonjour amis d'Excel,

Je suis depuis plusieurs jour en train d'essayer de calculer un Taux de croissance de mes articles à l'aide de VBA mais je dois dire que je suis en difficultée :

Dans mon fichier une ligne correspond à la vente d'un produit. (on a donc comme information le nom du produit, l'année de la vente, le Chiffre d'affaire du produit). Ce que je souhaite faire c'est calculer le taux de croissance de mes produits de la façon suivante:

1. Trie par articles puis par année de vente
2. Faire la somme des chiffre d'affaire des articles par années
3. Si il y'a qu'une seule année de commercialisation de l'article, je mets rien soit ""
4. Sinon s'il y'en a plusieurs alors on ne mets rien "" à la première année et on calcule le Taux de croissance de la façon suivante: (CAn - CAn-1)/CAn-1 * 100

Je vous joint un fichier test accompagné d'un exemple d'applicatio.

Merci à tous ceux qui pourront m'aider 🙂

Cordialement,

 

[Guest]

Re : Calculer un Taux

Bonjour,

Ci-joint une proposition par Tableau croisé dynamique, Qui si cela convient devra être construit sur une feuille autre que celle des données.

Regarde dans les propriété des champs CA et vois si cela convient. Avec des exemples plus réalistes ce serait plus facile de voir s'il l'on tombe juste.

A+

 

[EddyValier]

Re : Calculer un Taux

Bonjour,

Ci-joint une proposition par Tableau croisé dynamique, Qui si cela convient devra être construit sur une feuille autre que celle des données.

Regarde dans les propriété des champs CA et vois si cela convient. Avec des exemples plus réalistes ce serait plus facile de voir s'il l'on tombe juste.

A+

Bonjour Hasco,
C'est bien ces données que je souhaite obtenir, cependant je ne peux pas le faire à parti d'un TCD car je voudrais que l'information se rajoute dans la colonne D. En effet, cela fera partie d'une procédure qui importe des données et calcule automatiquement le taux de croissance. Je pense que je dois plutôt une procédure VBA mais je ne sais pas comment m'y prendre :-/
Merci de ta réponse en tous cas 🙂

 

[JBOBO]

Re : Calculer un Taux

Bonjour,

A tester en D13 et à tirer ensuite vers le haut et vers le bas :

=SI(ET(A13=A12;C13<>C12);((SOMMEPROD(($A$2:$A$36=$A13)*($C$2:$C$36=C13)*($B$2:$B$36))-SOMMEPROD(($A$2:$A$36=$A12)*($C$2:$C$36=C12)*($B$2:$B$36)))/SOMMEPROD(($A$2:$A$36=$A12)*($C$2:$C$36=C12)*($B$2:$B$36)));"")

et mettre au format pourcentage.

 

[EddyValier]

Re : Calculer un Taux

Bonjour JBOBO,

Je ne dois pas utiliser une formule de ce type mais plutôt une procèdure VBA parceque le but c'est d'automatiser le calcul du Taux de croissance avec un bouton !

A bientôt ^^

 

[JBOBO]

Re : Calculer un Taux

Bonjour,

En utilisant l'enregistreur de macro voici à quoi j'arrive en code vba. il y a surement plus "propre" mais je ne sais pas faire. Donc en attendant mieux, je te propose ceci :

Sub Macro5()
'
' Macro5 Macro
'

'
    'Range("K2").Select
    'Selection.Copy
    Range("D2").Select
    'ActiveSheet.Paste
    'Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-3]=R[-1]C[-3],RC[-1]<>R[-1]C[-1]),((SUMPRODUCT((R2C1:R1000C1=RC1)*(R2C3:R1000C3=RC[-1])*(R2C2:R1000C2))-SUMPRODUCT((R2C1:R1000C1=R[-1]C1)*(R2C3:R1000C3=R[-1]C[-1])*(R2C2:R1000C2)))/SUMPRODUCT((R2C1:R1000C1=R[-1]C1)*(R2C3:R1000C3=R[-1]C[-1])*(R2C2:R1000C2))),"""")"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D1000"), Type:=xlFillDefault
    Range("D2:D1000").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-18
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"
    Range("D1").Select
End Sub

 

[EddyValier]

Re : Calculer un Taux

Re,
Merci pour ta proposition JBOBO ^^
Comme toi, j'ai aussi opter pour l'enregistreur de macro pour commencer, cependant la formule s'adapte-elle si je rajoute des articles déja existant, par exemple article 2 en 2017 ?

 

[JBOBO]

Re : Calculer un Taux

re,

Sous reserve que les articles et année soit classé comme dans ton exemple, j'ai l'impression que ça fonctionne. A tester.

 

[EddyValier]

Re : Calculer un Taux

Re,
Je viens de tester cela fonctionne bien lorsqu'on ajoute un produit mais il faut au préalable effectuer le Trie sur les produits puis les année 🙂

Cependant, il s'agissait d'un fichier test et je n'arrive pas a appliquer ta formule sachant que les données ne sont pas dans les même colonnes.

Et oui:
Les produits sont dans la colonne I
Les CA dans la colonne K
Année dans la colonne V
et le taux de croissance doit être rajouter dans la colonne X
Voici un autre fichier test !

Merci !

 

[JBOBO]

Re : Calculer un Taux

Re,

A tester :

Sub Macro3()
'
' Macro3 Macro
'

'
   ' Range("Z2").Select
   ' Selection.Copy
    Range("X2").Select
   ' ActiveSheet.Paste
   ' Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC9=R[-1]C9,RC22<>R[-1]C22),((SUMPRODUCT((R2C9:R1000C9=RC9)*(R2C22:R1000C22=RC22)*(R2C11:R1000C11))-SUMPRODUCT((R2C9:R1000C9=R[-1]C9)*(R2C22:R1000C22=R[-1]C22)*(R2C11:R1000C11)))/SUMPRODUCT((R2C9:R1000C9=R[-1]C9)*(R2C22:R1000C22=R[-1]C[-2])*(R2C11:R1000C11))),"""")"
    Range("X2").Select
    Selection.AutoFill Destination:=Range("X2:X1000"), Type:=xlFillDefault
    Range("X2:X1000").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"
    Range("X1").Select
End Sub

 

[Guest]

Re : Calculer un Taux

Bonjour,

Sur la base de la formule de JBOBO, (qui ne m'en voudra pas j'en suis sûr).

Sub Macro3()
    Const FORMULE As String = "=IF(AND($I2=$I1,$V2<>$V1),((SUMPRODUCT(($I$2:$I$1000=$I2)*($V$2:$V$1000=$V2)*($K$2:$K$1000))-SUMPRODUCT(($I$2:$I$1000=$I1)*($V$2:$V$1000=$V1)*($K$2:$K$1000)))/SUMPRODUCT(($I$2:$I$1000=$I1)*($V$2:$V$1000=V1)*($K$2:$K$1000))),"""")"
    With Range("X2:X1000")
        .Formula = FORMULE
        .Value = .Value
        .NumberFormat = "0.00%"
    End With
End Sub

A+

 

[EddyValier]

Re : Calculer un Taux

Bonjour,

Dans cette formule vous fixé la plage de cellule de la ligne 2 à 1000.
Comment faire pour fixer jusqu'à la dernière ligne de mon tableau de donnée ??
J'ai essayé d'utiliser Range("A65536").End(xlUp).Row mais sans succès !

 

[Guest]

Re : Calculer un Taux

Bonjour,

Dans le fichier joint il n'y a rien en colonne A donc dans la macro ci-dessous j'ai pris la colonne I (9) "Produit"
comme référence.

Sub Macro3()
    Dim Ligne As Long
    Const FORMULE As String = "=IF(AND($I2=$I1,$V2<>$V1),((SUMPRODUCT(($I$2:$I$?=$I2)*($V$2:$V$?=$V2)*($K$2:$K$?))-SUMPRODUCT(($I$2:$I$?=$I1)*($V$2:$V$?=$V1)*($K$2:$K$?)))/SUMPRODUCT(($I$2:$I$?=$I1)*($V$2:$V$?=V1)*($K$2:$K$?))),"""")"
    Ligne = Cells(Rows.Count, 9).End(xlUp).Row '9 = colonne  changer le n° pour une autre colonne
    If Ligne < 2 Then Exit Sub
    With Range("X2:X" & Ligne)
        .Formula = Replace(FORMULE, "?", Ligne)
        .Value = .Value
        .NumberFormat = "0.00%"
    End With
End Sub

A+

 

[EddyValier]

Re : Calculer un Taux

Bonjour,
Cette macro marche mais cependant, il se peut qu'il y est des divisions par 0, du coup #DIV/0! s'affiche dans ma colonne Taux de croissance 😕
Comment je pourrai ajuster ma macro pour que l'on remplace les erreurs par VIDE ?

Salutations !

 

[Guest]

Re : Calculer un Taux

Bonjour,

La Formule change (uniquement pour excel >=2010):

Sub Macro3()
    Dim Ligne As Long
    Const FORMULE As String = "=IFERROR(IF(AND($I2=$I1,$V2<>$V1),((SUMPRODUCT(($I$2:$I$?=$I2)*($V$2:$V$?=$V2)*($K$2:$K$?))-SUMPRODUCT(($I$2:$I$?=$I1)*($V$2:$V$?=$V1)*($K$2:$K$?)))/SUMPRODUCT(($I$2:$I$?=$I1)*($V$2:$V$?=V1)*($K$2:$K$?))),""""),"""")"
    Ligne = Cells(Rows.Count, 9).End(xlUp).Row '9 = colonne  changer le n° pour une autre colonne
    If Ligne < 2 Then Exit Sub
    With Range("X2:X" & Ligne)
        .Formula = Replace(FORMULE, "?", Ligne)
        .Value = .Value
        .NumberFormat = "0.00%"
    End With
End Sub

A+