XL 2016 Moyenne.SI avec 2 critères : "chaine de caractère" & "différent de vide ou erreur"

[mam721]

Bonjour à tous,

je coince pour l'utilisation de Moyenne.SI sur 2 critères.

Lorsqu'il y a un seul critère, j'utilise la formule :

=MOYENNE.SI($F4:$F950;"NBR jours";G4:G950)

J'ai bien une moyenne appliquée en fonction de ma chaîne de caractère "NBR Jours".

Le problème apparaît lorsqu'il y a des cellules vides. je ne peux pas faire de moyenne.
Je souhaiterais appliquer cette formule de AC à AP.

Pouvez vous me dire comment ignorer les cellules vides, #N/A ou #VALEUR pour établir une moyenne avec les nombres présents.

Question subsidiaire, si je filtre sur année, mois, Activité, est ce que la moyenne sera mise a jour en fonction de la sélection ? peut on faire en sorte que le filtre n'imapcte pas les colonnes AA à AR ? ou dois mettre mon tableau et graphique ailleurs ?
Est ce que l'on peut faire en sorte que la moyenne prennent en compte le filtre ?

Vous trouverez le fichier ci-joint.

 

[chris]

Bonjour

Utilises MOYENNE.SI.ENS quel que soit le nombre de conditions

Si les colonnes sont vides comme H cela ne peut fonctionner
En AC2 à étirer à droite

=SI(NB.SI.ENS(G4:G940;">0";$F4:$F940;"NBR jours")=0;"";MOYENNE.SI.ENS(G4:G940;$F4:$F940;"NBR jours"))

Cependant la formule de NBR Jours est fausse : il faudrait en G6

=SI(OU(G5="";$G4="");"";NB.JOURS.OUVRES($G4;G5;Calendrier!$A$2:$A$65))

à étirer à droite et ceci pour toutes les lignes

 

[djidji59430]

Bonjour à tous,

je reviens a la définition de la moyenne(arithmétique)
somme des valeurs / nombre de valeur
et ça donne :
=SOMMEPROD(SIERREUR(($F$4:$F$943="NBR jours")*(H$4:H$943);0))/SOMMEPROD(SIERREUR(($F$4:$F$943="ecart")*(H$4:H$943<>"");0))

Crdlmt

 

[job75]

Bonjour mam721,

Formule matricielle en AC2 :

=SIERREUR(MOYENNE(SI(($F4:$F950="NBR jours")*ESTNUM(G4:G950);G4:G950));"")

à valider par Ctrl+Maj+Entrée, idem en AC4.

ESTNUM élimine les valeurs non numériques, SIERREUR évite #DIV/0 si aucune valeur numérique.

Edit : pff quand j'ai posté les messages #2 et #3 n'étaient pas affichés, bonjour à tous !

A+

 

[mam721]

Bonjour messieurs,

@chris je n'ai pas encore tout rempli pour les colonnes, c'est pour cela qu'il y a quelques colonnes vides.
Merci pour ta remarque pour G6, j'ai modifié.

@djidji59430 Merci pour SommeProd. Comme je maîtrise moins SommeProd, je vais devoir regarder comment l'utiliser.

@job75 Merci pour ton aide, cela marche sur l'ensemble du tableau.


Par contre dès que je cherche à appliquer mon filtre sur année, activité, la moyenne ne change pas.

Dois-je passer par un TCD pour se faire ? Et donc modifier la formule ?
J'ai préparé un TCD au cas ou.

 

[job75]

Par contre dès que je cherche à appliquer mon filtre sur année, activité, la moyenne ne change pas.

Dans la feuille "Analyse" ajoutez la colonne auxiliaire Y "Affiché" avec la formule =SOUS.TOTAL(3;[@Étape])

Puis dans la feuille "Synthese" modifiez la formule matricielle en B2 :

=SIERREUR(MOYENNE(SI((Analyse!$F4:$F950="NBR jours")*ESTNUM(Analyse!G4:G950)*Analyse!$Y4:$Y950;Analyse!G4:G950));"")

Idem en B4, voyez ce fichier (2).

 

[mam721]

Bonjour @job75

Merci pour ton aide.