Calculer une moyenne à intervalle régulier

[bdavidnc]

Bonjour,
Je vais essayé de faire simple et complet.
J'ai en colonne A des dates sous la forme AAAAMM.
J'ai en colonne B les numéros de mois des dates de la colonne A (de 01 à 12) obtenus en faisant la formule droite(A;2). Ces numéros de mois se répètent puisque mes dates vont de 200201 à 200806.
J'ai en colonne C des valeurs pour chacune des dates.

Pour le moment, je n'ai pas beaucoup de données mais ces trois colonnes vont être alimentées au fil du temps et je voudrais automatiser le calcul des moyennes à partir des valeurs d'un même numéro de mois.

Par exemple, pour calculer la moyenne des valeurs des mois de janvier (numéro de mois 01), je voudrais qu'excel ne retienne que les valeurs de la colonne C dont le numéro de mois en colonne B est égal à 01.
Et ainsi de suite pour les mois de février à décembre (numéro 02 à 12).

Pour le moment, je peux faire moyenne() et sélectionner les cellules correspondantes mais je ne me vois pas le faire jusqu'à la 65 536ème ligne !

J'ai essayé avec sommeprod() et moyenne(si()) mais je n'y arrive pas. Peut-être ai-je mal rédigé ma formule.

Est-ce que cela est faisable à travers une formule d'excel ou est-ce que je dois passer par le language vba ?

Merci d'avance.
David

 

[Tibo]

Re : Calculer une moyenne à intervalle régulier

Bonjour,

Avec MOYENNE(SI, tu étais très près.

Il s'agit en fait d'une formule matricielle qui demande à être validée par la combinaison de touches
CTRL + MAJ + ENTREE

La formule donne ceci :

=MOYENNE(SI(B1:B25="01";C1:C25))

Plage de cellules à adapter.

Et surtout, ne pas oublier la combinaison de touches

Il restera peut-être un point à avoir : si ton fichier s'étent sur plusieurs années, la formule fera la moyenne des mois (janvier par exemple) de toutes les années.

N'hésite pas à repasser si problème adaptation

@+

 

[bdavidnc]

Re : Calculer une moyenne à intervalle régulier

Bonjour Tibo et merci pour ta réponse,

J'avais bien testé cette formule sous une formule matricielle mais je me retrouve avec des écarts par rapport à la formule moyenne (sélection des cellules).

J'ai mis en pièce jointe un fichier excel pour illustrer mon cas.

Il y a un décalage dans les données de la colonne C car en fait ces résultats correspondent à une moyenne centrée sur 12. Mais la formule moyenne ne prend pas en compte les cellules vides dans le calcul...

Si on prend l'exemple du numéro de mois "01" pour janvier en faisant moyenne(sélection des cellules) j'obtiens 0,0039. Par la formule moyenne(si()), j'obtiens 0,0028, soit un écart 0,0011.

En creusant un peu, on constate que la formule moyenne(si(() somme bien les données ayant le numéro de mois "01" mais divise cette somme par 7 au lieu de 5 alors que les autres cellules sont vides.

Il prend en compte des cellules vides au dénominateur. N'y a-t-il pas un moyen d'éviter ça.

Merci.
David

PS : Je veux effectivement avoir (pour les 12 mois) une moyenne de toutes les années qui se trouvent dans la base.

 

[Tibo]

Re : Calculer une moyenne à intervalle régulier

Bonjour,

Un essai pour la moyenne des janvier :

=MOYENNE(SI(MOD(LIGNE($C$2:$C$85);12)=2;SI($C$2:$C$85<>"";$C$2:$C$85)))

ou bien :

=MOYENNE(SI(DROITE($A$2:$A$85;2)="01";SI($C$2:$C$85<>"";$C$2:$C$85)))

Les deux formules sont matricielles, donc validation par CTRL + MAJ + ENTREE

Je te laisse tester

Bonne journée

@+

 

[abcd]

Re : Calculer une moyenne à intervalle régulier

Bonjour,

Autre possibilité :
=SOMME.SI(B1:B100;"01";C1:C100)/SOMMEPROD((B1:B100="01")*(C1:C100<>""))

abcd

 

[bdavidnc]

Re : Calculer une moyenne à intervalle régulier

Bonjour à vous deux,

J'ai testé les 3 formules et la seule qui m'ait donné les bons résultats est :
=MOYENNE(SI(DROITE($A$2:$A$65536;2)="01";SI($C$2:$C$65536<>"";$C$2:$C$65536)))

Par contre j'ai dû rajouter dans la formule SI($C$2:$C$65536<>0) (après SI($C$2:$C$65536<>"") pour retomber sur les bons résultats.

Merci beaucoup à vous deux.
David

 

[abcd]

Re : Calculer une moyenne à intervalle régulier

Bonjour,

Je ne te comprends pas lorsque tu écris que seule une formule, que tu aurais encore du corriger, renvoie le bon résultat.
Vois la pièce jointe, les trois formules renvoient 0.0039, idem à H6.

Mais il se peut que je n'ai rien compris à la question.

abcd

 

[bdavidnc]

Re : Calculer une moyenne à intervalle régulier

Bonjour,

effectivement tu as raison, pourtant quand je les ai écrite hier, je n'avais pas trouvé les mêmes résultats. Une erreur dans les formules que j'avais écrite sans doute.

Merci encore.
David