moyenne par mois defaillante

[patineur]

bonjour,

voila un petit moment que je suis perdu avec mes moyennes.

J'ai des données sur tous les jours de l'année sauf quelques uns,
je souhaiterai faire une moyenne sur chaque mois.
le problème est que la moyenne qu'excel effectue il me compte même les cases vides et donc elle n'est pas valide.

ci joint le fichier, voir les problèmes dans l'onglet humidité mensuel.

 

[job75]

Re : moyenne par mois defaillante

Bonjour patineur, bienvenue sur XLD,

Dans le 1er et 3ème SOMMEPROD test à rajouter :

($K$4:$K$1000<>"")

Donc la bonne formule :

=SI(SOMMEPROD((ANNEE($J$4:$J$1000)=C$18)*(MOIS($J$4:$J$1000)=$A19)*($K$4:$K$1000<>""))=0;0;SOMMEPROD((ANNEE($J$4:$J$1000)=C$18)*((MOIS($J$4:$J$1000)=$A19)*$K$4:$K$1000))/SOMMEPROD((ANNEE($J$4:$J$1000)=C$18)*(MOIS($J$4:$J$1000)=$A19)*($K$4:$K$1000<>"")))

Edit : le 0.4650 que vous indiquez est faux.

A+

 

[ROGER2327]

Re : moyenne par mois defaillante

Bonjour patineur

Êtes-vous certain qu'on doive trouver 0.4650 ? Je trouve 0.475941176470588 pour avril 2009 (colonne K) avec la formule

[COLOR="DarkSlateGray"][B]=SI((SOMMEPROD((ANNEE($J$4:$J$1000)=E$18)*((MOIS($J$4:$J$1000)=$A22)*1)))=0;0;
SOMMEPROD((ANNEE($J$4:$J$1000)=E$18)*((MOIS($J$4:$J$1000)=$A22)*$K$4:$K$1000))/
(SOMMEPROD([COLOR="Sienna"]($K$4:$K$1000<>0)[/COLOR]*(ANNEE($J$4:$J$1000)=E$18)*
((MOIS($J$4:$J$1000)=$A22)*1))))[/B][/COLOR]

mais il se peut que je n'aie pas compris la question.​

ROGER2327
#2237
_
__________________
Bonjour job75. Je vois que nous sommes d'accord, mais quelles pertes de temps avec ces questions approximatives !

 

[Monique]

Re : moyenne par mois defaillante

Bonjour,

Ou bien comme ça :
(formule matricielle, à valider par ctrl, maj et entrée

[FONT=Verdana]=SI(SOMME((ANNEE($J$4:$J$1000)=E$18)*(MOIS($J$4:$J$1000)=$A19)*($K$4:$K$1000>0))=0;"";MOYENNE(SI((ANNEE($J$4:$J$1000)=E$18)*(MOIS($J$4:$J$1000)=$A19)*($K$4:$K$1000>0);$K$4:$K$1000)))[/FONT]

Ou bien en mettant une mise en forme conditionnelle pour masquer les #DIV/0!

[FONT=Verdana][FONT=Verdana]=MOYENNE(SI((ANNEE($J$4:$J$1000)=E$18)*(MOIS($J$4:$J$1000)=$A19)*($K$4:$K$1000>0);$K$4:$K$1000))[/FONT]
[/FONT]

 

[job75]

Re : moyenne par mois defaillante

Re, salut Roger, Monique,

Roger, il me semble qu'il faut aussi mettre le test ($K$4:$K$1000<>"") dans le 1er SOMMEPROD, car des mois peuvent (peut-être) exister sans aucune valeur à moyenner..

De plus, il vaut mieux mettre <>"" au cas où (peut-être) des valeurs 0 sont à moyenner...

A+

 

[patineur]

Re : moyenne par mois defaillante

bonjour à vous et merci,

je me suis trompé, les 0.4650 sont la moyenne sur le mois de mars, sur avril il s'agit bien de 0.4596

merci pour vos réponse, je vais de ce pas essayer vos propositions.

par contre je trouve la formule un peu compliqué, y atil une qui simplifie le calcul?

merci par avance.

 

[job75]

Re : moyenne par mois defaillante

Re,

sur avril il s'agit bien de 0.4596

Pas du tout...

A+

 

[ROGER2327]

Re : moyenne par mois defaillante

Re...

Re, salut Roger, Monique,

Roger, il me semble qu'il faut aussi mettre le test ($K$4:$K$1000<>"") dans le 1er SOMMEPROD, car des mois peuvent (peut-être) exister sans aucune valeur à moyenner..

De plus, il vaut mieux mettre <>"" au cas où (peut-être) des valeurs 0 sont à moyenner...

A+

Entièrement d'accord.
Je n'avais fait que le minimum pour obtenir le résultat dans le cas particulier signalé par notre ami.
Ne trouvant pas le résultat annoncé, j'attendais ses précisions pour continuer : j'ai encore la naïveté de penser qu'une question est réfléchie avant d'être posée... Je suis probablement devenu vieux.
​

ROGER2327
#2238

 

[patineur]

Re : moyenne par mois defaillante

je fatigue, autant pour moi, j'ai un peu modifier le fichier pour qu'il prenne le moins de place possible et je n'ai pas fait attention à ce que je faisait

c'est bien 0.4759.

autant pour moi, dsl pour ces problemes de communication

en tout cas merci, ca fonctionne pour moi maintenant.

 

[job75]

Re : moyenne par mois defaillante

Re, je m'étais absenté.

par contre je trouve la formule un peu compliqué, y atil une qui simplifie le calcul?

Deux manières de simplifier la formule.

1) Fichier (2). Sélectionner C19 => Menu Insertion-Nom-Définir => définir le nom N par la formule :

=SOMMEPROD((ANNEE('humidité mensuel'!$J$4:$J$1000)='humidité mensuel'!C$18)*(MOIS('humidité mensuel'!$J$4:$J$1000)='humidité mensuel'!$A19)*('humidité mensuel'!$K$4:$K$1000<>""))

Puis formule en C19 :

=SI([COLOR="Red"]N[/COLOR];SOMMEPROD((ANNEE($J$4:$J$1000)=C$18)*((MOIS($J$4:$J$1000)=$A19)*$K$4:$K$1000))/[COLOR="red"]N[/COLOR];"")

Il paraît mieux de mettre "" plutôt que 0 quand il n'y a pas de valeurs à moyenner.

2) Fichier (3). Comme Monique l'a signalé, utiliser une Mise en forme conditionnelle (MFC).

Sélectionner le tableau C19:E30 (C19 étant la cellule active).

Menu Format-Cellule => MFC => La formule est : =ESTERR(C19) => Format => Police couleur blanche.

Puis formule plus simple en C19 :

=SOMMEPROD((ANNEE($J$4:$J$1000)=C$18)*((MOIS($J$4:$J$1000)=$A19)*$K$4:$K$1000))/SOMMEPROD((ANNEE($J$4:$J$1000)=C$18)*(MOIS($J$4:$J$1000)=$A19)*($K$4:$K$1000<>""))

A+

 

[Monique]

Re : moyenne par mois defaillante

Re,

La formule Moyenne(si(()))
Une autre (c'est la même) avec plages nommées (ça accélère le recalcul), on modifie le bois par une liste de validation
Formule nommée entièrement (accélère le recalcul et à valider simplement par "Entrée")

J'ai enlevé les liaisons.