Macro - Calcul de rentabilités mensuellesRésolu

Question

Bonjour, Je souhaite calculer les rentabilités mensuelles avec une macro [soit (B3-B1)/B1, puis (B5-B4/B4), etc...], la solution manuelle n'est pas envisageable car les colonnes sont remplies. Les données sont comme ceci: A B C 03/03/1997 6 17/03/1997 1 31/03/1997 7 07/04/1997 6 21/04/1997 4 05/05/1997 1 19/05/1997 7 02/06/1997 3 16/06/1997 6 30/06/1997 1 ... ... Le code suivant peut etre utile, il permet de calculer les moyennes mensuelles:

Sub Test()
Dim i%, k%, Resultat As Single, v
Do Until IsDate(Cells(i + 1, 1).Value)
i = i + 1
Loop
Do While IsDate(Cells(i + 1, 1).Value)
v = Cells(i + 1, 1).Value
Do While Month(v) = Month(Cells(i + 1, 1).Value)
Resultat = Resultat + Cells(i + 1, 2).Value
i = i + 1
k = k + 1
Loop
Cells(i, 3).Value = Resultat / k
Resultat = 0
k = 0
Loop
End Sub

Merci à tous

ScSami · Accepted Answer

OUI, JE T'ENTENDS Je suis un peu comme l'autre (oui, tu sais... Notre Père qui êtes aux cieux... restez-y... ), j'ai une oreille un peu partout... un peu comme la NSA... Oui, la NSA est l'alternative moderne du Dieu des monothéistes La solution de Molenn est très bien je trouve! Pleinne de bon sens, pleinne d'infos (j'ai d'ailleurs appris deux/trois trucs [merci]). Bien sûr, faut l'adapter, mais ça me paraît encore pas trop compliqué non !? Sinon, je trouve ton code (Sub Test) un peu... "chelou" !!! Mais bon, je l'ai pas testé alors je dis rien... Moi et la "rentabilité" on est pas copain copain alors je pige pas le "formule". Mais qu'importe. Si j'ai saisi le "truc", il faut utiliser la première et la dernière valeur de chaque mois pour les utiliser dans l'équation et mettre le résultat dans la colonne C, disons, soit à coté de la première, soit à coté de la dernière donnée du mois. Ok... Alors, si c'est bien ça, il faut un truc du genre : Sub CalculerRentabilite() Dim ... varCompteur = 1 varMois = Month(Cells(varCompteur, 1).Value) varValeurA = Val(Cells(varCompteur, 2).Value) varPositionA = varCompteur Do While IsDate(Cells(varCompteur, 1).Value) If varMois <> Month(Cells(varCompteur, 1).Value) Cells(varPositionA, 2).Value = (Val(Cells(varCompteur - 1, 2).Value) - varValeurA) / varValeurA varValeurA = Val(Cells(varCompteur, 2).Value) varPositionA = varCompteur End If varCompteur = varCompteur + 1 Loop If Cells(1,1)<>"" Then Cells(varPositionA, 2).Value = ( Val(Cells(varCompteur - 1, 2).Value) - varValeurA) / varValeurA Enf If End Sub Enfin, de tête, là, sans tester (il se fait tard ) Dit nous si cette fois c'est "bon". Enjoy

( Si une réponse vous convient, cliquez sur le bouton "Réponse acceptée". )

Molenn · Answer

J'ai une question existentielle à poser : Tu es obligé de faire en code ? Parce qu'avec des formules dans Excel, tu peux faire ça automatiquement tu sais ? Sans taper une ligne de code. Une seule condition, que tes enregistrements soient triés par ordre chronologique des dates et, dans mon exemple, une même année (à toi de modifier pour tenir compte des dates d'années différentes)


Je te donne ce que j'ai fait : 

A               B     C 
Date            CA    Mois
03/03/1997      6     =Mois(A2)
17/03/1997      1     =Mois(A3)
31/03/1997      7     ...
07/04/1997      6  
21/04/1997      4   
05/05/1997      1  
19/05/1997      7  
02/06/1997      3   
16/06/1997      6  
30/06/1997      1 

J'ai donc crée un colonne supplémentaire appelée Mois, qui contient la formule =Mois(Ax) où x est le numéro de ligne.
Dans cette colonne s'affiche donc le numéro du mois pour chaque ligne.


Je vais me servir de la cellule F2 comme d'une zone de recherche. Je taperai le numéro du mois dont je veux calculer la rentabilité.
Dans la cellule G3, je rentre la formule suivante : =NB.SI(C:C;F2)
=> Cette formule va me donner le nombre de référence pour un mois donné.
Dans la cellule G4, je rentre la formule suivante : =EQUIV(F2;C:C;0)
=> Cette formule va afficher le numéro de la ligne où se trouve la toute première référence pour le mois cherché (d'où la nécessité que les dates soient triées par ordre chronologique). Il faut aussi que tu aies des en-têtes de colonne comme moi, sinon, il faut retrancher 1 au résultat obtenu.
Dans la cellule G5, je rentre la formule suivante : =G4+G3-1
=> J'obtiens ainsi le numéro de la dernière ligne avec le mois cherché.
Dans la cellule G7, je rentre la formule suivante:
 =(INDIRECT(ADRESSE(G5;2))-INDIRECT(ADRESSE(G4;2)))/INDIRECT(ADRESSE(G4;2))
=> Cette formule calcule ta rentabilité.

Voilà, c'est tout. Ta rentabilité sera calculée en fonction du numéro de mois que tu cherches (attention, je n'ai pas intégré le snotions d'erreur si tu cherches un mois qui n'est pas renseigné dans tes dates, je ne vais aps faire tout le boulot non plus ^^).
En créant une colonne, en rentrant 5 formules, j'obtiens donc ton résultat. Pas besoin de code.

Allez, on détaille juste un exemple :
Ton tableau est donc du type
A               B     C 
Date            CA    Mois
03/03/1997      6     3
17/03/1997      1     3
31/03/1997      7     3
07/04/1997      6     4
21/04/1997      4     4
05/05/1997      1     5
19/05/1997      7     5
02/06/1997      3     6
16/06/1997      6     6
30/06/1997      1     6

Dans la cellule F2, je tape 5 (je calcule donc la rentabilité pour le mois de Mai) :
=> En G4 s'affiche 2 (Il n'y a que 2 dates pour le mois de Mai)
=> En G5 s'affiche 7 (Premier N° de ligne avec le mois de Mai)
=> En G6 s'affiche 8 (Dernier N° de ligne avec le mois de Mai)
=> En G7 s'affiche 6, la rentabilité du mois de Mai ((7-1)/1)

Voilà voilà.

Molenn

vbaright · Answer

En fait je souhaite avoir toutes les rentabilit&#233;s mensuelles sur la colonne d'&#224; cot&#233;, c'est &#224; dire la colonne C.

Avec ta m&#233;thode je peux avoir qu'un aper&#231;u pour chaque mois... je ne peux pas stocker les r&#233;sultats obtenus...

 D'ou la necessit&#233; d'avoir une macro (enfin il me semble)

Est ce que quelqu'un peut m'aider sur le code? ScSami si tu m'entends...:)

merci bien Molenn

Molenn · Answer

"Avec ta méthode je peux avoir qu'un aperçu pour chaque mois... je ne peux pas stocker les résultats obtenus..."

Ben, c'est le principe même d'Excel et de tout tableur hein.
Là je t'ai donné un moyen simple de l'avoir par mois.

Si tu veux avoir toutes les rentabilités mensuelles dans une même colonne, ça signifie donc 12 lignes (et oui, 'y a 12 mois).
Et bien, au lieu de faire la formule des cellules G3 et G4 en fonction de F2 (qui est la cellule de recherche), tu définis leurs formules en dur, ce qui donnera : 
Dans la cellule G3, je rentre la formule suivante : =NB.SI(C:C;1)
pour le mois de Janvier, 2 pour le mois de février, etc ...
Dans la cellule G4, je rentre la formule suivante : =EQUIV(1;C:C;0)
etc ...
Et tu mets la formule de ta cellule G7 dans la colonne C.
En gros, tu crées un tableau. Tu considères mes 5 cellules comme les composantes d'un tableau fournissant une rentabilité.
Et tu construis ton tableau à 12 cellules.

Allez, comme je suis sympa, je te donne la réponse en simplifiant tout ça (j'utilisais 5 Cellules parce que c'est plus facile à comprendre, mais on peut condenser tout ça dans une seule formule) : 

Colonne C, toujours la formule =Mois(Ax)
Colonne D : Colonne E
janvier     Formule
Février     Formule
Mars        Formule
...

Tu comprends le principe ?
la formule pour le mois de mars est :
=(INDIRECT(ADRESSE((EQUIV(3;C:C;0)+NB.SI(C:C;3)-1);2))-INDIRECT(ADRESSE(EQUIV(3;C:C;0);2)))/INDIRECT(ADRESSE(EQUIV(3;C:C;0);2))

Pour le mois de mai est :
=(INDIRECT(ADRESSE((EQUIV(5;C:C;0)+NB.SI(C:C;5)-1);2))-INDIRECT(ADRESSE(EQUIV(5;C:C;0);2)))/INDIRECT(ADRESSE(EQUIV(5;C:C;0);2))

Te reste donc à recopier cette formule 12 fois en remplaçant le signe µ que je vais mettre dans la formule, par le numéro du mois :
=(INDIRECT(ADRESSE((EQUIV(µ;C:C;0)+NB.SI(C:C;µ)-1);2))-INDIRECT(ADRESSE(EQUIV(µ;C:C;0);2)))/INDIRECT(ADRESSE(EQUIV(µ;C:C;0);2))

Et voilà, tu as ton tableau avec les rentabilités de tous les mois de l'année en cours.

Molenn

vbaright · Answer

Merci ScSami 
le code sub Test que j'ai mis, est correct et pratique, il fonctionne bien
merci encore!!!!

Molenn merci &#233;galement ! ! !

Macro - Calcul de rentabilités mensuelles

5 réponses

Discussions similaires