adapter une formule qui fonctionne pour des cellules adjacentes à des cellules non adjacentes

didierrp

XLDnaute Nouveau
Bonjour,
J'ai grâce à ce forum une formule qui répond parfaitement a mes besoins mais elle a été conçue pour des cellules adjacentes, dans la formule d'exemple ci-dessous pour les cellules de A1 à A10 (A1:A10).
J'aimerai la meme formule pour des cellules non adjacentes par exemple B1 et B5 et B10. (B1;B5;B10)

J'ai essayé de l'adapter mais ne connaissant pas bien la syntaxe j'arrive a des erreurs.

Voici la formule:
=SIERREUR(MOYENNE.SI(A1:A10;"<>0");0)

Merci
 

didierrp

XLDnaute Nouveau
Merci mais ce serait trop simple....!!

Cela ne correspond pas à la formule que j'ai laissée en exemple et qui vérifie qu'au cas ou une (ou des) cellule est a 0 on ne compte pas cette cellule car c'est une mesure qui n'a pas été faite et elle ne rentre donc pas dans la moyenne.

Il faut qu'elle corresponde exactement à l'identique de la formule =SIERREUR(MOYENNE.SI(A1:A10;"<>0");0) mais remaniée pour des cellules non adjacentes..

Merci
 

JHA

XLDnaute Barbatruc
Bonjour à tous,

Il serait bien d'avoir un fichier exemple pour répondre à tes besoins, cela éviterait de tâtonner à chaque fois.
En supposant que tu dois prendre la valeur toutes les 5 lignes (10;15;20 etc..)
A essayer cette formule matricielle
Code:
=MOYENNE(SI((MOD(LIGNE(B10:B20);5)=0)*(B10:B20<>0);B10:B20))

Si tu n'as pas le même nombre de lignes entre chaque valeur (B1;B5;B10) alors là, dis nous ce que tu as entre ces valeurs avec un fichier exemple, on pourra peut être t'aider.

JHA
 

didierrp

XLDnaute Nouveau
Bon je tente une dernière explication après je ferai un fichier exemple..:
Prenons deux cellules non adjacentes au hasard: B13 et B18

Si je fais =MOYENNE(B13;B18) au cas ou en B13 ou en B18 la cellule affiche 0 (ce qui veut dire que la mesure n'a pas été faite) la moyenne est erronée car elle tient compte d'une cellule dont la valeur est 0 . Donc je cherche la bonne formule pour éviter ce problème.

Est-ce que c'est comprehensible ou sinon en effet je ferai un fichier exemple.
Par avance merci
 

job75

XLDnaute Barbatruc
Bonjour,

Voyez le fichier joint et cette fonction VBA :
Code:
Function MoyenneSiNonNul(r As Range)
Dim s#, n
For Each r In r
  If IsNumeric(CStr(r)) Then _
    If r <> 0 Then s = s + r: n = n + 1
Next
MoyenneSiNonNul = s / n
End Function
A+
 

Pièces jointes

  • MoyenneSiNonNul(1).xlsm
    20.1 KB · Affichages: 30

job75

XLDnaute Barbatruc
Re,

S'il y a des plages illimitées la fonction précédente prend du temps...

Il vaut donc mieux utiliser :
Code:
Function MoyenneSiNonNul(r As Range)
Dim s#, n
For Each r In Intersect(r, r.Parent.UsedRange)
  If IsNumeric(CStr(r)) Then _
    If r <> 0 Then s = s + r: n = n + 1
Next
MoyenneSiNonNul = s / n
End Function
Fichier (2).

A+
 

Pièces jointes

  • MoyenneSiNonNul(2).xlsm
    20.8 KB · Affichages: 28

ROGER2327

XLDnaute Barbatruc
Bonsoir à tous.

En l'absence de précision sur le besoin réel, un petit classeur hasardeux...

Bonne soirée.


ℝOGER2327
#8416


Samedi 21 As 144 (Saint Possible, schizophrène - fête Suprême Quarte)
3 Frimaire An CCXXV, 7,2197h - chicorée
2016-W47-3T17:19:38Z
 

Pièces jointes

  • Moyenne sous conditions.xlsx
    16.2 KB · Affichages: 43

job75

XLDnaute Barbatruc
Bonjour à tous,

Fichier (2) pour le 2ème onglet.

Edit : fichier (2 bis) avec MOYENNE.SI.ENS en B12 (à partir d'Excel 2007 comme SIERREUR).

Bonne journée.
 

Pièces jointes

  • Fichier de Roger2327(2).xlsx
    19.5 KB · Affichages: 46
  • Fichier de Roger2327(2 bis).xlsx
    19.4 KB · Affichages: 34
Dernière édition:

didierrp

XLDnaute Nouveau
WWWWHAOUUUUU.... Vraiment un grand merci pour toutes vos réponses, je ne m'attendais pas a tant... !!

Toutes vos solutions convenaient a mon problème , j'ai pris la solution proposée par JHA parce qu'elle était assez simple a mettre en oeuvre et surtout parce que je comprenais à peu près la syntaxe!! ... je l'ai adaptée à mon utilisation dans une première feuille (il y en aura d'autres..!)

Comme la formule entrainait une erreur au cas ou toutes les cellules incriminées étaient à 0 (dans le cas ou il n'y ai pas eu de mesures ce jour là) et que ce n'est pas très beau comme presentation...! je l'ai accommodé d'un SIERREUR en m'inspirant de la formule que j'avais donné dans le 1er post et qui fonctionne très bien dans le cas de cellules adjacentes..

Voilà donc ma formule finale: =SIERREUR(MOYENNE(SI(P14=0;$D$1;P14);SI(P18=0;$D$1;P18));0) (on peut bien évidemment rajouter d'autres cellules )

Au cas ou vous pensiez que l'on peut faire plus simple n'hésitez pas a proposer ...

Encore merci à vous tous
 

job75

XLDnaute Barbatruc
Re,

Si l'on veut éviter de faire appel à la cellule D1 (qui ne doit pas contenir un nombre) :
Code:
=SIERREUR(SOMME(P14;P18)/NB(1/P14;1/P18);"")
Et cette formule "pèse" 37 octets de moins en mémoire.

Edit 1 : pourquoi utilisez-vous =SIERREUR(xxx;0) ? Une moyenne peut être égale à zéro !

Edit 2 : la fonction NB est limitée à 256 arguments (séparés par des ";"), comme la fonction MOYENNE.

Pas de problème avec SOMME puisqu'on peut entrer des références multiples entre parenthèses.

A+
 
Dernière édition:

job75

XLDnaute Barbatruc
Bonjour le fil, le forum,

Non, c'est un maximum de 255 arguments que la fonction NB peut accepter.

Fichier joint avec cette belle petite formule pour la moyenne :
Code:
=SOMME((A1;A3;A5;A7;A9;A11;A13;A15;A17;A19;A21;A23;A25;A27;A29;A31;A33;A35;A37;A39;A41;A43;A45;A47;A49;A51;A53;A55;A57;A59;A61;A63;A65;A67;A69;A71;A73;A75;A77;A79;A81;A83;A85;A87;A89;A91;A93;A95;A97;A99;A101;A103;A105;A107;A109;A111;A113;A115;A117;A119;A121;A123;A125;A127;A129;A131;A133;A135;A137;A139;A141;A143;A145;A147;A149;A151;A153;A155;A157;A159;A161;A163;A165;A167;A169;A171;A173;A175;A177;A179;A181;A183;A185;A187;A189;A191;A193;A195;A197;A199;A201;A203;A205;A207;A209;A211;A213;A215;A217;A219;A221;A223;A225;A227;A229;A231;A233;A235;A237;A239;A241;A243;A245;A247;A249;A251;A253;A255;A257;A259;A261;A263;A265;A267;A269;A271;A273;A275;A277;A279;A281;A283;A285;A287;A289;A291;A293;A295;A297;A299;A301;A303;A305;A307;A309;A311;A313;A315;A317;A319;A321;A323;A325;A327;A329;A331;A333;A335;A337;A339;A341;A343;A345;A347;A349;A351;A353;A355;A357;A359;A361;A363;A365;A367;A369;A371;A373;A375;A377;A379;A381;A383;A385;A387;A389;A391;A393;A395;A397;A399;A401;A403;A405;A407;A409;A411;A413;A415;A417;A419;A421;A423;A425;A427;A429;A431;A433;A435;A437;A439;A441;A443;A445;A447;A449;A451;A453;A455;A457;A459;A461;A463;A465;A467;A469;A471;A473;A475;A477;A479;A481;A483;A485;A487;A489;A491;A493;A495;A497;A499;A501;A503;A505;A507;A509))/NB(1/A1;1/A3;1/A5;1/A7;1/A9;1/A11;1/A13;1/A15;1/A17;1/A19;1/A21;1/A23;1/A25;1/A27;1/A29;1/A31;1/A33;1/A35;1/A37;1/A39;1/A41;1/A43;1/A45;1/A47;1/A49;1/A51;1/A53;1/A55;1/A57;1/A59;1/A61;1/A63;1/A65;1/A67;1/A69;1/A71;1/A73;1/A75;1/A77;1/A79;1/A81;1/A83;1/A85;1/A87;1/A89;1/A91;1/A93;1/A95;1/A97;1/A99;1/A101;1/A103;1/A105;1/A107;1/A109;1/A111;1/A113;1/A115;1/A117;1/A119;1/A121;1/A123;1/A125;1/A127;1/A129;1/A131;1/A133;1/A135;1/A137;1/A139;1/A141;1/A143;1/A145;1/A147;1/A149;1/A151;1/A153;1/A155;1/A157;1/A159;1/A161;1/A163;1/A165;1/A167;1/A169;1/A171;1/A173;1/A175;1/A177;1/A179;1/A181;1/A183;1/A185;1/A187;1/A189;1/A191;1/A193;1/A195;1/A197;1/A199;1/A201;1/A203;1/A205;1/A207;1/A209;1/A211;1/A213;1/A215;1/A217;1/A219;1/A221;1/A223;1/A225;1/A227;1/A229;1/A231;1/A233;1/A235;1/A237;1/A239;1/A241;1/A243;1/A245;1/A247;1/A249;1/A251;1/A253;1/A255;1/A257;1/A259;1/A261;1/A263;1/A265;1/A267;1/A269;1/A271;1/A273;1/A275;1/A277;1/A279;1/A281;1/A283;1/A285;1/A287;1/A289;1/A291;1/A293;1/A295;1/A297;1/A299;1/A301;1/A303;1/A305;1/A307;1/A309;1/A311;1/A313;1/A315;1/A317;1/A319;1/A321;1/A323;1/A325;1/A327;1/A329;1/A331;1/A333;1/A335;1/A337;1/A339;1/A341;1/A343;1/A345;1/A347;1/A349;1/A351;1/A353;1/A355;1/A357;1/A359;1/A361;1/A363;1/A365;1/A367;1/A369;1/A371;1/A373;1/A375;1/A377;1/A379;1/A381;1/A383;1/A385;1/A387;1/A389;1/A391;1/A393;1/A395;1/A397;1/A399;1/A401;1/A403;1/A405;1/A407;1/A409;1/A411;1/A413;1/A415;1/A417;1/A419;1/A421;1/A423;1/A425;1/A427;1/A429;1/A431;1/A433;1/A435;1/A437;1/A439;1/A441;1/A443;1/A445;1/A447;1/A449;1/A451;1/A453;1/A455;1/A457;1/A459;1/A461;1/A463;1/A465;1/A467;1/A469;1/A471;1/A473;1/A475;1/A477;1/A479;1/A481;1/A483;1/A485;1/A487;1/A489;1/A491;1/A493;1/A495;1/A497;1/A499;1/A501;1/A503;1/A505;1/A507;1/A509)
Bonne journée.
 

Pièces jointes

  • Test NB(1).xlsx
    18.6 KB · Affichages: 41

Discussions similaires

Statistiques des forums

Discussions
311 733
Messages
2 082 019
Membres
101 872
dernier inscrit
Colin T