Moyenne sans 0 sur cellules non consécutives

[oreyi19]

Bonjour,

Je souhaiterais calculer la moyenne de trois cellules non consécutives en excluant les 0.

Dans le cas de deux cellules j'ai réussi à créer cette formule =SI(ET(E34<>0;E35<>0);MOYENNE(E34:E35);SOMME(E34:E35))

Mais je n'arrive pas à prendre en compte 3 cellules donc comment calculer la moyenne sur uniquement les valeurs E26;E33;E40 selon les mêmes caractéristiques ?

Merci d'avance

 

[Jocelyn]

Re : Moyenne sans 0 sur cellules non consécutives

Bonjour le Forum,
Bonjour oreyi19,

Tu peux tester une formule de ce type :

=MOYENNE(SI((MOD(LIGNE(E26:E40);7)=5)*(E26:E40>0);E26:E40))

c'est une solution par formule matricielle, il faut valider la formule en appuyant simultanément sur CTRL, Maj (au dessus de CTRL) et entrée, si ta formule est bien validée excel va mettre de lui même le signe { devant le égal et le signe } en fin de formule.

Cordialement

 

[excfl]

Re : Moyenne sans 0 sur cellules non consécutives

Bonjour le forum,

=SOMME(E26:E40)/NB.SI(E26:E40;">0")

excfl

 

[oreyi19]

Re : Moyenne sans 0 sur cellules non consécutives

Merci pour vos réponses

Jocelyn ça fonctionne à merveille merci bcp !!

en revanche, ta formule excfl ne fonctionne pas, ça me calcule un nombre incorrect (pour la moyenne des valeurs 7,195 ; 4,757 ; 0 j'obtiens 6,517 au lieu des 5,976 de la formule de Jocelyn

 

[excfl]

Re : Moyenne sans 0 sur cellules non consécutives

Re,

Tu peux constater dans le fichier joint que j'obtiens bien une moyenne de 5,976 avec ton exemple.

excfl

 

[oreyi19]

Re : Moyenne sans 0 sur cellules non consécutives

Erratum !

Ta formule ne fonctionne pas Jocelyn quand j'essai de l'appliquer à 3 autres cellules E48 ; E 56 et E62

si j'écris =MOYENNE(SI((MOD(LIGNE(E48:E62);7)=5)*(E48:E62>0);E48:E62))
ça me donne #DIV/0! car dans ce cas E48=7,021 ; E56=0 ; E62=4,628

 

[oreyi19]

Re : Moyenne sans 0 sur cellules non consécutives

Effectivement excfl
mais si je sélectionne précisement ces cellules, c'est que toute la colonne est remplie de données.
Donc si dans ton fichier, je rajoute des nombres, ça mofidie le résultat

 

[excfl]

Re : Moyenne sans 0 sur cellules non consécutives

Re,

Tu pourrais peut-etre constater que ma formule fonctionne ?

excfl

 

[R@chid]

Re : Moyenne sans 0 sur cellules non consécutives

Bonjour @ tous,

Erratum !
Ta formule ne fonctionne pas Jocelyn quand j'essai de l'appliquer à 3 autres cellules E48 ; E 56 et E62
si j'écris =MOYENNE(SI((MOD(LIGNE(E48:E62);7)=5)*(E48:E62>0);E48:E62))
ça me donne #DIV/0! car dans ce cas E48=7,021 ; E56=0 ; E62=4,628

Il faut comprendre la formule de Jocelyn (que je salue au passage) pour que tu puisses l'adapter à d'autres plages...

Re,
Tu pourrais peut-etre constater que ma formule fonctionne ?
excfl

Mais si les plages contient d'autres valeurs et ne veut pas prendre en compte que ces 3 cellules ça ne va pas fonctionner,
@ + +

 

[Jocelyn]

Re : Moyenne sans 0 sur cellules non consécutives

re,
Bonjour excfl,

A oreyi19, la formule avec mod() prends les valeurs de toutes les lignes dont le reste de la division du numéro de ligne par 7 est egal a 5, si tu doit changer et que comme dans ton dernier exemple le pas n'est pas le même ou alors n'est pas toujours égal tu peux tester celle ci des formules

=MOYENNE(SI(((LIGNE(E23:E41)=26)+(LIGNE(E23:E41)=33)+(LIGNE(E23:E41)=40))*(E23:E41>0);E23:E41))

toujours une formule matricielle a valider comme la précédente

ici par les parties (LIGNE(E23:E41)=26) on indique le numéro de ligne a prendre en compte ici c'est la ligne 26 donc il suffit de changer les numéro de ligne

EDIT : Bonjour L'ami Rachid

 

[excfl]

Re : Moyenne sans 0 sur cellules non consécutives

Re,

si les plages contient d'autres valeurs et ne veut pas prendre en compte que ces 3 cellules ça ne va pas fonctionner,

C'est exact. Donc emploi d'un "subterfuge".

=SOMME(E26;E33;E40/NB.SI(H13:H15;">0")

excfl

 

[R@chid]

Re : Moyenne sans 0 sur cellules non consécutives

Bonjour @ tous,
Pour la plage E48:E62, tu peux juste changer le 5 dans la formule de Jocelyn par 6 et tout ira bien,

=MOYENNE(SI((MOD(LIGNE(E48:E62);7)=6)*(E48:E62>0);E48:E62))

@ valider toujours par Ctrl+Maj+Entree
@ + +

 

[oreyi19]

Re : Moyenne sans 0 sur cellules non consécutives

Pour plus de compréhension de vous trois, je vous donne l'exemple de mon fichier.

En fait, il s'agit d'un reporting mensuel (donc 12 colonnes de ce type) et sur 9 feuilles (plusieurs vendeurs)

Je vous ai indiqué les moyennes sans prise en compte de 0 que je souhaiterai calculer, les autres cellules vont accueillir des données (0 ou >0) qui serviront au calcul de mes moyennes

 

[Misange]

Re : Moyenne sans 0 sur cellules non consécutives

Bonjour @ tous
Oreyi, vu a complexité de ton tableau, et le nombre de formules différentes qu'il faudrait concocter pour répondre à ta demande, je m'orienterais pour ma part vers une autre approche, consistant à NE PAS saisir les 0.
Si ceux-ci sont eux-même issus d'une formule (difficile à savoir avec un tableau sans aucune donnée...) il faut traiter cela en amont : genre :
=si(truc>0;truc;"")
et ensuite faire de "bêtes" moyennes. Faire des moyennes conditionnelles avec des plages discontinues a toujours été lourdingue et ça le reste même avec les dernières versions. Si tu te retrouves à faire 350 matricielles pour obtenir ce que tu veux, pense à acheter du gel contre les contractures digitales (maladie des chirogourdistes)

 

[oreyi19]

Re : Moyenne sans 0 sur cellules non consécutives

Bonjour à tous,

J'ai finalement appliqué ta formule suivante Jocelyn, que j'ai comprise et que j'applique à tous mes cas de figures et sur un nombre plus ou moins grand de cellules.

=MOYENNE(SI(((LIGNE(E23:E41)=26)+(LIGNE(E23:E41)=33)+(LIGNE(E23:E41)=40))*(E23:E41>0);E23:E41))

toujours une formule matricielle a valider comme la précédente

ici par les parties (LIGNE(E23:E41)=26) on indique le numéro de ligne a prendre en compte ici c'est la ligne 26 donc il suffit de changer les numéro de ligne

En revanche, là je suis tombée sur un cas où mes 4 valeurs sont égales à 0 donc ça m'écrit #DIV/0!
Je travaille actuellement sur cette formule :

=MOYENNE(SI(((LIGNE(E14:E17)=14)+(LIGNE(E14:E17)=15)+(LIGNE(E14:E17)=16)+(LIGNE(E14:E17)=17))*(E14:E17>0);E14:E17))

Je ne suis pas assez douée pour rajouter une autre conditionnelle à cette formule complexe de manière à écrire 0 si tous les paramètres sont 0.

Pourriez vous m'aider à trouver une alternative ?

ps: je vous promets que j'arrete de vous embeter apres ça