Somme cellule non contiguë et <>0

[alexandre33260]

Bonjour, je cherche la formule pour additionner des cellules non contiguë et dont la valeur est différente de zéro.
Dans l'exemple joint, en A10, je dois avoir 11 mais si je modifie la valeur de A6, la moyenne doit changer bien évidemment....
Merci pour votre aide.
PS : j'ai essayé plein de formules, mais je n'ai rien trouvé. (si cela était possible de le faire sans Macros.
Le top est SOMME.SI(Plage;"<>0") mais malheureusement, ce n'est pas sur une plage.....

 

[alexandre33260]

Re : Somme cellule non contiguë et <>0

Le fichier joint est un exemple, sur mon fichier final, un coup la cellule est paire, la suivante est impaire...
Il me faudrait une formule qui fasse la moyenne de certaine cellules qui je définirais en fonction de mon fichier comme par exemple A2 puis A4 puis A6 puis A8 puis G3 puis G9 puis K5 (par exemple) puis faire la moyenne de ces cellules mais si une de ces cellules est égale à 0, alors ne pas en tenir compte et passer à la suivante...

 

[job75]

Re : Somme cellule non contiguë et <>0

Re, bonjour vgendron, Roger,

Il me faudrait une formule qui fasse la moyenne de certaine cellules qui je définirais en fonction de mon fichier comme par exemple A2 puis A4 puis A6 puis A8 puis G3 puis G9 puis K5 (par exemple)

Alors définissez-les une fois pour toutes nom d'une pipe, ça fait 2 heures que vous tournez en rond.

A+

 

[Vilain]

Re : Somme cellule non contiguë et <>0

Le problème c'est qu'on ne connaît pas les critères qui exclus certaines cellules du calcul

Edit : Job, tu es plus râleur que moi

 

[job75]

Re : Somme cellule non contiguë et <>0

Re,

Un petit coup de VBA ne fait jamais de mal :

Function MaMoyenne(Plage As Range)
Dim c As Range, s#, n&
For Each c In Plage
  If c <> 0 And IsNumeric(c) Then s = s + c: n = n + 1
Next
If n Then MaMoyenne = s / n
End Function

Fichier joint.

A+

 

[alexandre33260]

Re : Somme cellule non contiguë et <>0

Merci Job75, cela fonctionne parfaitement. C'est en VBA mais bon tant pis. Non pas que je déteste VBA, c'est simplement que je n'y comprends rien et donc ne suis maitre de rien.
Mais encore merci, j'ai intégré ce code dans mon projet et cela fonctionne merveilleusement bien.

 

[job75]

Re : Somme cellule non contiguë et <>0

Bonjour alexandre33260, le fil,

On peut se passer de VBA.

Faire en E14 la liste des adresses de toutes les cellules dont on cherche la moyenne.

Formule en E16 :

=SOMME((A2;A4;A6;A8;G3;G9;K5))/SOMMEPROD(N(ESTNUM(CHERCHE(ADRESSE(LIGNE(A1:L10);COLONNE(A1:L10);4);E14)/A1:L10)))

C'est nettement plus compliqué que la solution VBA...

A+

 

[job75]

Re : Somme cellule non contiguë et <>0

Re,

Une formule plus simple mais matricielle :

=MOYENNE(SI(ESTNUM(CHERCHE(ADRESSE(LIGNE(A1:L10);COLONNE(A1:L10);4);E14)/A1:L10);A1:L10))

A valider par Ctrl+Maj+Entrée.

Fichier (2).

A+

 

[job75]

Re : Somme cellule non contiguë et <>0

Re,

Ah oui, si les adresses peuvent dépasser la colonne Z il faut absolument les faire précéder d'un séparateur (tiret).

Voyez ce fichier (3).

A+

 

[alexandre33260]

Re : Somme cellule non contiguë et <>0

Merci job75 effectivement, cela fonctionne sans VBA mais la formule n'est pas simple, je ne comprends pas qu"une formule simple ne soit pas possible, peut-être dans les versions futures d'Excel.
Pour le moment, je garde la version donnée précédemment en VBA, ça fonctionne parfaitement.
Merci beaucoup pour le coup de main.

 

[job75]

Re : Somme cellule non contiguë et <>0

Bonjour alexandre33260, le forum,

je ne comprends pas qu'une formule simple ne soit pas possible, peut-être dans les versions futures d'Excel.

Sûrement pas, les fonctions avec des critères comme NB.SI SOMME.SI MOYENNE.SI fonctionnent et ne fonctionneront toujours qu'avec des plages de cellules contigües.

Edit : il faut toujours des séparateurs si les colonnes dépassent Z ou les lignes 9, fichier (4).

Bonne journée.