Moyenne 0 sur plusieurs feuilles

[gerson94]

Bonjour toutes et tous,
D'abord bonne année 2010.
Ma moyenne sur plusieurs feuilles (en cellule E6) dont la Feuil3 contient un "0" ne retourne aucun résultat. Merci de m'aider à améliorer ma formule.

Gerson

 

[job75]

Re : Moyenne 0 sur plusieurs feuilles

Bonsoir gerson94,

Feuil1:Feuil4!E6 n'a pas de sens.

Il faut définir chaque cellule à tester ou à moyenner par Feuil1!E6, Feuil2!E6, Feuil3!E6, Feuil4!E6.

A+

 

[ROGER2327]

Re : Moyenne 0 sur plusieurs feuilles

Bonsoir à tous

Pas d'accord avec vous, job75.

Feuil1:Feuil4!E6 n'a pas de sens.

[B][COLOR="DarkSlateGray"]=MOYENNE(Feuil1:Feuil4!E6)[/COLOR][/B]

fonctionne parfaitement dès lors que la formule n'est pas inscrite dans l'une des quatre cellules concernées.
Ce qui coince ici est la partie Feuil1:Feuil4!E6<>0.
Sauf à laisser la cellule vide au lieu d'y mettre 0, je ne vois pas de solution évidente pour ce point.​

ROGER2327
#2536

 

[job75]

Re : Moyenne 0 sur plusieurs feuilles

Bonsoir Roger, [Edit] et tous mes voeux pour 2010,

Eh bien merci, je n'avais jamais testé une expression comme Feuil1:Feuil4!E6.

C'était pourtant l'occasion de le faire ici plutôt que de dire des bêtises

J'aurai appris quelque chose aujourd'hui.

A+

 

[gerson94]

Re : Moyenne 0 sur plusieurs feuilles

Bonsoir à tous

Pas d'accord avec vous, job75.

[B][COLOR="DarkSlateGray"]=MOYENNE(Feuil1:Feuil4!E6)[/COLOR][/B]

fonctionne parfaitement dès lors que la formule n'est pas inscrite dans l'une des quatre cellules concernées.
Ce qui coince ici est la partie Feuil1:Feuil4!E6<>0.
Sauf à laisser la cellule vide au lieu d'y mettre 0, je ne vois pas de solution évidente pour ce point.​

ROGER2327
#2536

Bonsoir Job et Roger,
Merci d'avoir regardé mon pb.
Lorsque je fais la moyenne MOYENNE(Feuil1:Feuil4!E6) sans le "0" j'obtiens bien mon résultat soit 3,666. Or je souhaitais mettre "0" comme résultat dans ma feuil3. Sinon je le remplacerai par un tiret.

Gerson

 

[job75]

Re : Moyenne 0 sur plusieurs feuilles

Re,

Il y a cette solution, pas très élégante :

=SOMME(Feuil1:Feuil4!E6)/((Feuil1!E6<>0)+(Feuil2!E6<>0)+(Feuil3!E6<>0)+(Feuil4!E6<>0))

Edit : bien sûr le résultat ne sera pas correct si des cellules contiennent du texte, il faudrait alors compliquer la formule.

Bonne nuit.

 

[gerson94]

Re : Moyenne 0 sur plusieurs feuilles

Re,

Il y a cette solution, pas très élégante :

=SOMME(Feuil1:Feuil4!E6)/((Feuil1!E6<>0)+(Feuil2!E6<>0)+(Feuil3!E6<>0)+(Feuil4!E6<>0))

Edit : bien sûr le résultat ne sera pas correct si des cellules contiennent du texte, il faudrait alors compliquer la formule.

Bonne nuit.

Ca me donne 2,75 au lieu de 3,666.
J'ai essayé ca aussi :

=SOMME(Feuil1:Feuil4!E6)/((Feuil1!E6>0)+(Feuil2!E6>0)+(Feuil3!E6>0)+(Feuil4!E6>0))

sans succès

A+

 

[job75]

Re : Moyenne 0 sur plusieurs feuilles

Re,

Ca me donne 2,75 au lieu de 3,666.

Je ne comprends pas, voir le fichier joint.

Rebonne nuit.

 

[gerson94]

Re : Moyenne 0 sur plusieurs feuilles

Re,



Je ne comprends pas, voir le fichier joint.

Rebonne nuit.

Je repris directement la formule sur mon fichier de base et çà marche je te confirme. Je vais chercher une formule plus courte car je vais avoir toutes les semaines de l'année.
Merci.

Bonne nuit

Gerson

 

[Denis132]

Re : Moyenne 0 sur plusieurs feuilles

Bonsoir à tous,

Comme le 0 ne semble pas être le résultat d'une formule car tu as écrit:
"Sinon je le remplacerai par un tiret", ajoute seulement un apostrophe devant ton zéro.

@+

Denis

 

[job75]

Re : Moyenne 0 sur plusieurs feuilles

Bonjour le fil, le forum,

Je vais chercher une formule plus courte car je vais avoir toutes les semaines de l'année.

Avec 52 feuilles, je ne vois pas de solution simple par formule.

Alors voici une fonction VBA :

Function MOY(ref As Variant) As Double
Dim ws As Worksheet, v As Variant, s As Double, n As Integer
Application.Volatile
ref = ref.Address [COLOR="Red"]'l'argument doit être une référence[/COLOR]
For Each ws In Worksheets
  If ws.Name <> "Moyenne" Then
    v = ws.Range(ref)
    If IsNumeric(v) And v <> 0 Then s = s + v: n = n + 1
  End If
Next
MOY = s / n
End Function

Voir fichier joint.

A+

 

[job75]

Re : Moyenne 0 sur plusieurs feuilles

Re,

La fonction précédente était volatile.

Ceci présente un inconvénient : à la fermeture du fichier, un message demande l'enregistrement des modifications.

Voici une fonction non volatile, mais avec un argument pour forcer le recalcul si les cellules sont modifiées :

Function MOY(plage As Variant, ref As Variant) As Double [COLOR="Red"]'le 1er argument pour forcer le recalcul[/COLOR]
Dim ws As Worksheet, v As Variant, s As Double, n As Integer
ref = ref.Address [COLOR="red"]'le 2ème argument doit être une référence[/COLOR]
For Each ws In Worksheets
  If ws.Name <> "Moyenne" Then
    v = ws.Range(ref)
    If IsNumeric(v) And v <> 0 Then s = s + v: n = n + 1
  End If
Next
MOY = s / n
End Function

A+

 

[BOISGONTIER]

Re : Moyenne 0 sur plusieurs feuilles

Bonjour,

=SOMME(Feuil1:Feuil4!E6)/SOMMEPROD(NB.SI(INDIRECT("Feuil"&LIGNE(1:4)&"!E6");"<>0"))

JB
Formules Matricielles 3D

 

[job75]

Re : Moyenne 0 sur plusieurs feuilles

Bonjour JB, mes meilleurs voeux 2010,

Mais oui bien sûr, j'avais bien pensé à NB.SI mais pas à SOMMEPROD...

A+

 

[job75]

Re : Moyenne 0 sur plusieurs feuilles

Re,

Une variante de la formule de JB avec la fonction N() :

=SOMME(Feuil1:Feuil4!E6)/SOMMEPROD(SIGNE(N(INDIRECT("Feuil"&LIGNE(1:4)&"!E6"))))

Edit : noter que cette formule donne une moyenne correcte si des cellules contiennent du texte.

A+