Moyenne avec insertion de nouvelle colonne Résolu/Fermé

Question

Bonjour,

Je fais des moyennes sous Excel, pondérées, avec une formule me permettant de gérer les champs vides (de manière à ne pas avoir un "zéro" quand il n'y a pas de note dans une cellule).
Voici le type de formule :

=SI(NB(AT3:AV3)=0;"";ARRONDI(SOMMEPROD($AT$2:$AV$2;AT3:AV3)/(NB(AT3)*$AT$2+NB(AU3)*$AU$2+NB(AV3)*$AV$2);1))

Voici mon problème :

Si je veux ajouter une nouvelle colonne (par exemple ici AW), je dois taper manuellement la suite de la formule : +NB(AW3)*$AW$2 (sans compter la modif en début de formule).
Je dois faire cela chaque fois que j'ajoute une colonne.

Il faudrait que la formule gère d'elle même l'ajout ou la suppression de colonnes.
Je pense que le problème peut-être résolu en enlevant des $ que j'ai dû mettre en trop, mais n'étant pas pro en la matière, je crains de saper la formule...

A mon avis, problème résolu en 2 minutes par qqun d'un peu expérimenté...

Merci pour votre aide :)

Raymond PENTIER · Answer

En effet.
Il ne faut pas ajouter une colonne après AV mais insérer une colonne avant AV.

... et bienvenue au nouveau membre !

Raymond PENTIER · Answer

Tu as 
en D1 la formule =SOMME(A1:C1)
en D2 la formule =SOMME(A2:C2)
en D3 la formule =SOMME(A3:C3)
en A4 la formule =SOMME(A1:A3)
en B4 la formule =SOMME(B1:B3)
en C4 la formule =SOMME(C1:C3)
Tes 3 premières formules deviendront fausses si tu inséres une colonne en A ou en D.
Tes 3 dernières formules deviendront fausses si tu inséres une ligne en 1 ou en 4.
Par contre tu peux insérer une colonne en B ou C : la formule qui était en D1 est décalée en E1 et est devenue
=SOMME(A1:D1).
Tu peux insérer une ligne en 2 ou 3 : la formule qui était en A4 est décalée en A5 et est devenue
=SOMME(A1:A4).

Le caractère $ sert à bloquer une référence. Tu sembles le savoir.
Mais ta question suivante ressemble à une plaisanterie ! Tu écris une formule du style =A+B+C et tu voudrais qu'Excel vienne, comme ça, te rajouter un terme pour obtenir =A+B+C+D ? Oui, il faut l'écrire à la main, car ce que tu souhaites (naïvement je suppose) relève du domaine de l'intelligence artificielle (robotique) !

eriiic · Answer

Bonjour tout le monde,

Tu peux t'en tirer avec decaler() et en te reservant la colonne W vide.
=SI(NB(AT3:AW3)=0;"";ARRONDI(SOMMEPROD(DECALER($AT$2;0;0;1;NB($AT$2:$AW$2))*DECALER(AT3;0;0;1;NB($AT$2:$AW$2)))/SOMME(DECALER($AT$2;0;0;1;NB(AT3:AW3)));1))

Si tu insères une colonne en AW elle sera prise en compte à l'ajout du coeff en ligne 2. Ensuite tu peux insérer en AX, etc.
Une note vide n'est pas prise en compte, un 0 est pris en compte.
Contrôle et adapte si besoin. Le 4ème paramètre de decaler() indique la largeur de la sélection.

eric

edit: j'ai été peut-être un peu vite sur la somme des coef en cas d'absence de note. Tester avec SOMMEPROD(DECALER($AT$2;0;0;1;NB(AT2:AW2))*(DECALER(AT3;0;0;1;NB($AT$2:$AW$2))<>"")) pour le dénominateur.
S'il y a des anomalies donner un exemple précis (coefs, notes, moyenne)
eric

dobo69 · Answer

bonsoir,

il faut déposer le fichier ICI et copier-coller le lien donné dans ton prochain post

Raymond PENTIER · Answer

Salut, smayot.

Je t'ai écrit précédemment "J'ai du mal à comprendre pourquoi tu as cru devoir te lancer dans tes formules aussi compliquées" ; maintenant je suis persuadé que cette formule alambiquée n'est pas de toi : celui qui l'a créée avait une bonne raison d'écrire 
(NB(AT3)*$AT$2+NB(AU3)*$AU$2+...) 
alors que, dans ton cas, il suffit d'écrire 
($AT$2+$AU$+...) 
pour la simple raison que NB(AT3)=1 tout comme NB(AU3) et NB(AV3) !

Fais un test : efface les notes des colonnes I et J : Tes moyennes en colonne K se calculent normalement. Cela veut bien dire que si tu avais dès le début fait un tableau avec 20 notes au lieu de 9 les moyennes seraient exactes quelque soit le nombre d'exercices notés.

Raymond PENTIER · Answer

Encore moi. Je retire ce que j'ai écrit au post 7 : 
La fonction MOYENNE() ne peut pas s'appliquer dans le cas de moyennes pondérées.

Travaillons à partir de ton fichier.
* Supposons que tu aies 7 notes, et que tu en prévoies au maximum 9.
Nous effaçons les cellules I1:J29. La formule en K3 sera 
=SI(NB(B3:J3)=0;"";ARRONDI(SOMMEPROD($B$2:$J$2;B3:J3)/SOMME($B$2:$J$2);1))
et si tu avais pris soin de donner à la plage B2:J2 le nom "pond" la formule s'écrirait 
=SI(NB(B3:J3)=0;"";ARRONDI(SOMMEPROD(pond;B3:J3)/SOMME(pond);1))
Quand tu renseignes la colonne I, tout se recalcule sans aucune intervention de ta part.
* Afin d'éviter l'affichage en ligne 30 de #DIV/0! il faut remplacer en B30 la formule 
=MOYENNE(B3:B29) par =SI(B2="";"";MOYENNE(B3:B29))
* Pour une meilleure esthétique, affecte à la colonne K et à la ligne 30 le format nombre avec 1 décimale ; tristan aura donc une moyenne affichée de 18,0 et pas de 18.
https://www.cjoint.com/?kvazw2E5jm

&#9658; Ceci dit, je développe une certaine méthode, mais cela ne saurait te faire négliger celle proposée par eriiic au post 4 !

eriiic · Answer

PS: essaie d'écrire à la fin en disant à qui tu t'adresses que la lecture soit chronologique. Ca sera plus facile à suivre...
voir post 22
eric

Raymond PENTIER · Answer

Bonsoir, smayot.

Je t'ai indiqué en fin du post 12 qu'eriiic et moi sommes partis sur deux méthodes DIFFERENTES !
Tu ne dois surtout pas essayer de les mélanger, ce n'est pas fait pour ...
Lui, il essaie de t'accompagner dans ta démarche d'insérer des colonnes.
Moi, je veux te forcer à prévoir dès le début un nombre de colonnes suffisant.
Je te suggère de faire une copie de ton tableau de notes, et d'en modifier une suivant les conseils d'eriiic, l'autre suivant mes recommandations ; tu pourras ains tester celle où tu te sens le plus à l'aise, et l'adopter.

Pour répondre à ta question, j'ai besoin de savoir si tu utilises Excel 97-2003 ou Excel 2007.

Moyenne avec insertion de nouvelle colonne

8 réponses

Discussions similaires