Somme cellule non contiguë et <>0

alexandre33260

XLDnaute Occasionnel

• 14 Janvier 2015

• #16

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...

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 14 Janvier 2015

• #17

Re : Somme cellule non contiguë et <>0

Re, bonjour vgendron, Roger,

alexandre33260 à dit:

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)

Cliquez pour agrandir...

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

A+

 

Vote positif 0 Vote négatif

Vilain

XLDnaute Accro

• 14 Janvier 2015

• #18

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

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 14 Janvier 2015

• #19

Re : Somme cellule non contiguë et <>0

Re,

Un petit coup de VBA ne fait jamais de mal :

Code:

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+

 

Pièces jointes

• MaMoyenne.xls

  36 KB · Affichages: 34

Vote positif 0 Vote négatif

alexandre33260

XLDnaute Occasionnel

• 14 Janvier 2015

• #20

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.

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 15 Janvier 2015

• #21

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 :

Code:

=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+

 

Pièces jointes

• Moyenne(1).xls

  27.5 KB · Affichages: 19
• Moyenne(1).xls

  27.5 KB · Affichages: 19
• Moyenne(1).xls

  27.5 KB · Affichages: 16

Dernière édition: 15 Janvier 2015

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 15 Janvier 2015

• #22

Re : Somme cellule non contiguë et <>0

Re,

Une formule plus simple mais matricielle :

Code:

=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+

 

Pièces jointes

• Moyenne(2).xls

  27 KB · Affichages: 17

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 15 Janvier 2015

• #23

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+

 

Pièces jointes

• Moyenne(3).xls

  27.5 KB · Affichages: 19

Vote positif 0 Vote négatif

alexandre33260

XLDnaute Occasionnel

• 16 Janvier 2015

• #24

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.

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 16 Janvier 2015

• #25

Re : Somme cellule non contiguë et <>0

Bonjour alexandre33260, le forum,

alexandre33260 à dit:

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

Cliquez pour agrandir...

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.

 

Pièces jointes

• Moyenne(4).xls

  27.5 KB · Affichages: 14

Dernière édition: 16 Janvier 2015

Vote positif 0 Vote négatif