Macro - Calcul de rentabilités mensuelles

Résolu
Signaler
Messages postés
10
Date d'inscription
jeudi 19 janvier 2006
Statut
Membre
Dernière intervention
3 février 2006
-
Messages postés
10
Date d'inscription
jeudi 19 janvier 2006
Statut
Membre
Dernière intervention
3 février 2006
-
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:

<OL>
<LI>

Sub Test()


<LI>

Dim i%, k%, Resultat As Single, v


<LI>

Do Until IsDate(Cells(i + 1, 1).Value)


<LI>

i = i + 1


<LI>

Loop


<LI>

Do While IsDate(Cells(i + 1, 1).Value)


<LI>

v = Cells(i + 1, 1).Value


<LI>

Do While Month(v) = Month(Cells(i + 1, 1).Value)


<LI>

Resultat = Resultat + Cells(i + 1, 2).Value


<LI>

i = i + 1


<LI>

k = k + 1


<LI>

Loop


<LI>

Cells(i, 3).Value = Resultat / k


<LI>

Resultat = 0


<LI>

k = 0


<LI>

Loop


<LI>

End Sub

</LI></OL>
Merci à tous

5 réponses

Messages postés
1488
Date d'inscription
mercredi 5 février 2003
Statut
Membre
Dernière intervention
3 décembre 2007
24
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

<hr size="2" width="100%">
( Si une réponse vous convient, cliquez sur le bouton "Réponse acceptée". )
3
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

Codes Sources 109 internautes nous ont dit merci ce mois-ci

Messages postés
797
Date d'inscription
mardi 7 juin 2005
Statut
Membre
Dernière intervention
23 février 2011
7
"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
3
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

Codes Sources 109 internautes nous ont dit merci ce mois-ci

Messages postés
797
Date d'inscription
mardi 7 juin 2005
Statut
Membre
Dernière intervention
23 février 2011
7
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
Messages postés
10
Date d'inscription
jeudi 19 janvier 2006
Statut
Membre
Dernière intervention
3 février 2006

En fait je souhaite avoir toutes les rentabilités mensuelles sur la colonne d'à coté, c'est à dire la colonne C.

Avec ta méthode je peux avoir qu'un aperçu pour chaque mois... je ne peux pas stocker les résultats obtenus...

D'ou la necessité 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
Messages postés
10
Date d'inscription
jeudi 19 janvier 2006
Statut
Membre
Dernière intervention
3 février 2006

Merci ScSami
le code sub Test que j'ai mis, est correct et pratique, il fonctionne bien
merci encore!!!!

Molenn merci également ! ! !