calcul de moyenne sur une ligne en fonction de critères d'une autre ligne

zaouiii

XLDnaute Nouveau
Bonjour,

j'essaie de calculer la moyenne du diamètre de tiges de bambou (ligne $C4:$AK4) en fonction de leur âge (de 0 à 5 ans sur la ligne $C5:$AK5) et ce pour plusieurs ensembles de lignes (correspondant à différentes pousses de bambou).
Mais dans chaque touffe de bambou, toutes les catégories d'ages ne sont pas systématiquement représentées (par exemple il n'y a pas de tiges age 5) et j'obtiens un message d'erreur #DIV/0! et comme je veux calculer ensuite la moyenne des valeurs pour tous les âges ça bloque la fin du calcul.
Je dois répéter ce calcul pour un grand nombre de touffes (qui ne sont pas toutes dans le fichier, loin de là sans quoi il serait trop lourd!) donc je ne peux pas me permettre d'avoir à effacer tous les #DIV/0!.
D'autre part, mon équipe utilise une ancienne version de excel (2003) dans laquelle la formule que j'avais essayé (AVERAGEIF) n'existe pas...


comment faire
le fichier en pièce jointe...
merci d'avance pour votre aide ca ferait gagner un temps précieux à tout mon équipe!
 

Pièces jointes

  • envoi forum excel_calcul bambou.xls
    36.5 KB · Affichages: 223
  • envoi forum excel_calcul bambou.xls
    36.5 KB · Affichages: 237
  • envoi forum excel_calcul bambou.xls
    36.5 KB · Affichages: 229

tototiti2008

XLDnaute Barbatruc
Re : calcul de moyenne sur une ligne en fonction de critères d'une autre ligne

Bonjour zaouiiii,

pour AVERAGEIF, tu peux le remplacer par SUMIF()/COUNTIF

en AN2 :

=SOMME.SI($C3:$AK3;0;$C2:$AK2)/NB.SI(C3:AK3;0)
je te laisse traduire le nom des fonctions...
 

Monique

Nous a quitté
Repose en paix
Re : calcul de moyenne sur une ligne en fonction de critères d'une autre ligne

Bonjour,

Plus pratique avec un format nombre personnalisé en AN1:AS1 :
[<2]0" year"_s;0" years"

En AN2
=MOYENNE(SI($C3:$AK3=AN$1;$C2:$AK2))
Càd =AVERAGE(IF(groupe de conditions; Plage))
Formule matricielle à valider par ctrl, maj et entrée

En AN4
=SOMMEPROD(($C4:$AK4="X")*($C3:$AK3=AN$1))

AN5 : comme AN2 mais au conditionnel
=SI(SOMME(($C4:$AK4="x")*($C3:$AK3=AN$1));MOYENNE(SI($C4:$AK4="x";SI($C3:$AK3=AN$1;$C2:$AK2)));"")
Formule matricielle à valider par ctrl, maj et entrée

En AN7
=NB.SI($C7:$AK7;AN$1)

En AN10
=SI(NB.SI($C11:$AK11;AN$1);MOYENNE(SI($C11:$AK11=AN$1;$C10:$AK10));"")
Formule matricielle à valider par ctrl, maj et entrée

Etc… voir le fichier
 

Pièces jointes

  • MoyenneSiZaouiiii.zip
    7.1 KB · Affichages: 76

zaouiii

XLDnaute Nouveau
Re : calcul de moyenne sur une ligne en fonction de critères d'une autre ligne

Merci Monique, ca m'a l'air pas mal, mais je n'arrive pas à faire le format spécial en
AN1:ASI
Peux tu m'expliquer comment je dois l'entrer?
merci d'avance...
Zaouiii
 

Monique

Nous a quitté
Repose en paix
Re : calcul de moyenne sur une ligne en fonction de critères d'une autre ligne

Re,

Barre de menu - Format - Cellule
Onglet "Nombre"
A gauche, dans "Catégorie", choisir "Personnalisée"
Et dans la zone "Type", tu tapes
[<2]0" year"_s;0" years"
Puis OK

Sur Excel 2007, je ne sais pas
 

zaouiii

XLDnaute Nouveau
Re : calcul de moyenne sur une ligne en fonction de critères d'une autre ligne

Encore merci Monique,

J'ai quand même encore deux petits problèmes:
- sur AN2 le résultat est étrange (= 4,16 alors que les diamètres a l'age 0 font tous autour de 20 cm) je pense que c'est parce qu'excel prend également en compte les cases vides mais je ne sais pas comment régler le problème.
- sur la ligne 2, lorsqu'un age n'est pas représenté, j'obtiens un message d'erreur ce qui n'est pas le cas sur la ligne 5. j'ai essayé d'utiliser a peu pres la meme formule sur la ligne 1 mais ma formule est toujours fausse (oui je suis un oeu une bras cassé d'excel...)

Merci d'avance ...

Aurélie
 

Monique

Nous a quitté
Repose en paix
Re : calcul de moyenne sur une ligne en fonction de critères d'une autre ligne

Bonjour,

Je n’avais pas vu tout ça

Sur AN2 le résultat est étrange
(= 4,16 alors que les diamètres a l'age 0 font tous autour de 20 cm)
je pense que c'est parce qu'excel prend également en compte
les cases vides mais je ne sais pas comment régler le problème.

Il manquait un critère, Excel prend une cellule vide pour un 0.
En AN2 :
=MOYENNE(SI($C3:$AK3=AN$1;SI(ESTNUM($C3:$AK3);$C2:$AK2)))

Sur la ligne 2, lorsqu'un age n'est pas représenté, j'obtiens un message d'erreur
ce qui n'est pas le cas sur la ligne 5.
j'ai essayé d'utiliser a peu pres la meme formule sur la ligne 1
mais ma formule est toujours fausse

AN2 au conditionnel :
=SI(SOMME(($C3:$AK3=AN$1)*(ESTNUM($C3:$AK3)));MOYENNE(SI($C3:$AK3=AN$1;SI(ESTNUM($C3:$AK3);$C2:$AK2)));"")
 

zaouiii

XLDnaute Nouveau
Re : calcul de moyenne sur une ligne en fonction de critères d'une autre ligne

Bonjour,

toujours sur la base du même fichier mais plus complet (en pièce jointe) je cherche maintenant à tracer une droite avec pour axe des abscices l'âge moyen des touffes (colonne AU) et pour ordonnée la circumférence moyenne des tiges dans cette touffe (colonne AV). or si je prend les colonnes en entier, les cellules vides sont comptées comme des 0 ce qui modifie la courbe.
Comme je dois répéter ca pour un grand nombres de feuilles, j'aimerai ne pas avoir à enlever toutes les cellules vides.
Comment faire?

Merci d'avance

Aurélie
 

Pièces jointes

  • Copy of MoyenneSiZaouiiii.xls
    29.5 KB · Affichages: 103

Monique

Nous a quitté
Repose en paix
Re : calcul de moyenne sur une ligne en fonction de critères d'une autre ligne

Bonjour,

Je ne me souviens pas avoir vu ton message !

Tu peux mettre dans les cellules vides =NA()
Dans la cellule, ça donne #N/A mais on peut les masquer par un format conditionnel
Format - Mise en forme conditionnelle
Choisir "La formule est" et taper
=ESTNA(AU3)
Cliquer ensuite sur "Format" et dans l'onglet "Police", choisir la couleur blanche

Dans les graphiques, le #N/A n'est pas pris en compte.
 

Pièces jointes

  • MoyenneSiZaouiiiiV1.zip
    7.8 KB · Affichages: 55

Discussions similaires