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

didierrp · 23 Novembre 2016

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

djidji59430 · 23 Novembre 2016

Bonjour,

=MOYENNE(B1;B5;B10)

didierrp · 23 Novembre 2016

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 · 23 Novembre 2016

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 · 23 Novembre 2016

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

JHA · 23 Novembre 2016

Bonjour à tous,

Avec des conditions à répétition.

JHA

Jacky67 · 23 Novembre 2016

didierrp à dit:
Bon je tente une dernière explication
Est-ce que c'est comprehensible ou sinon en effet je ferai un fichier exemple..

Re...
Il serait sage AMHA..., afin de ne pas monter une usine à gaz... de disposer ces données de façon à pouvoir les utiliser par une fonction tel que déjà proposé

job75 · 23 Novembre 2016

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+

job75 · 23 Novembre 2016

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+

ROGER2327 · 23 Novembre 2016

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

job75 · 23 Novembre 2016

Bonsoir Roger,

Toujours très sympa de mettre des fichiers à notre disposition, merci beaucoup.

J'interviens juste sur le 1er onglet.

Bonne fin de soirée.

job75 · 24 Novembre 2016

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.

didierrp · 24 Novembre 2016

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 · 24 Novembre 2016

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+

job75 · 25 Novembre 2016

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.

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

didierrp

XLDnaute Nouveau

djidji59430

XLDnaute Barbatruc

didierrp

XLDnaute Nouveau

JHA

XLDnaute Barbatruc

didierrp

XLDnaute Nouveau

JHA

XLDnaute Barbatruc

Pièces jointes

Jacky67

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

ROGER2327

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

didierrp

XLDnaute Nouveau

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

Discussions similaires