Moyenne avec insertion de nouvelle colonne
Résolu
smayot
Messages postés
14
Statut
Membre
-
smayot -
smayot -
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 :)
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 :)
A voir également:
- Moyenne avec insertion de nouvelle colonne
- Formule moyenne excel plusieurs colonnes - Guide
- Darkino nouvelle adresse - Guide
- Déplacer colonne excel - Guide
- Trier colonne excel - Guide
- Colonne word - Guide
8 réponses
En effet.
Il ne faut pas ajouter une colonne après AV mais insérer une colonne avant AV.
... et bienvenue au nouveau membre !
Il ne faut pas ajouter une colonne après AV mais insérer une colonne avant AV.
... et bienvenue au nouveau membre !
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) !
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) !
Bonjour Raymond,
Comme en insérant une colonne en B ou C : la formule qui était en D1 est décalée en E1 et est devenue
=SOMME(A1:D1)., je me suis dit que l'on pouvait appliquer ce principe sur toute la formule. Ce qui est pénible est que chaque fois que je vais insérer une colonne, je vais devoir écrire la formule à la main. C'est long et source d'erreur.
J'ai d'ailleurs aussi le problème si je veux supprimer une colonne puisque du coup, il me manque une référence.
Eric a proposé une solution, à laquelle je ne comprends malheureusement pas grand chose. Je l'ai essayée, mais elle fait fusionner deux lignes par deux lignes.
Arggh.
Tu l'auras compris, je suis instit, et je ne sais jamais à l'avance combien je vais avoir de notes.
Le problème ne se pose pas quand on n'a pas de moyennes pondérées : il doit bien y avoir une solution...
Comme en insérant une colonne en B ou C : la formule qui était en D1 est décalée en E1 et est devenue
=SOMME(A1:D1)., je me suis dit que l'on pouvait appliquer ce principe sur toute la formule. Ce qui est pénible est que chaque fois que je vais insérer une colonne, je vais devoir écrire la formule à la main. C'est long et source d'erreur.
J'ai d'ailleurs aussi le problème si je veux supprimer une colonne puisque du coup, il me manque une référence.
Eric a proposé une solution, à laquelle je ne comprends malheureusement pas grand chose. Je l'ai essayée, mais elle fait fusionner deux lignes par deux lignes.
Arggh.
Tu l'auras compris, je suis instit, et je ne sais jamais à l'avance combien je vais avoir de notes.
Le problème ne se pose pas quand on n'a pas de moyennes pondérées : il doit bien y avoir une solution...
Bonjour.
J'ai du mal à comprendre pourquoi tu as cru devoir te lancer dans tes formules aussi compliquées.
La fonction MOYENNE() a été créée justement pour tenir compte de colonnes (ou de lignes) non renseignées.
Tu nous envoies ton tableau de notes (sans oublier les coefficients) en changeant le nom des élèves et en indiquant quel est le maximum de notes qui peuvent être attribuées ; nous te proposerons une ou des solutions possibles.
Tu peux utliser https://www.cjoint.com/ ou http//cijoint.fr/ pour créer ton lien, que tu vas coller dans ton message CCM.
J'ai du mal à comprendre pourquoi tu as cru devoir te lancer dans tes formules aussi compliquées.
La fonction MOYENNE() a été créée justement pour tenir compte de colonnes (ou de lignes) non renseignées.
Tu nous envoies ton tableau de notes (sans oublier les coefficients) en changeant le nom des élèves et en indiquant quel est le maximum de notes qui peuvent être attribuées ; nous te proposerons une ou des solutions possibles.
Tu peux utliser https://www.cjoint.com/ ou http//cijoint.fr/ pour créer ton lien, que tu vas coller dans ton message CCM.
C'est bien la retraite, mais certains viennent perturber tes journées...
Voici le lien :
https://www.cjoint.com/?kuxaz71o4h
Disons que je prévois au maximum 20 notes pour la matière présentée, les autres matières ayant 4 ou 5 notes max.
Merci d'avance pour m'aider à être moins ignare...
Voici le lien :
https://www.cjoint.com/?kuxaz71o4h
Disons que je prévois au maximum 20 notes pour la matière présentée, les autres matières ayant 4 ou 5 notes max.
Merci d'avance pour m'aider à être moins ignare...
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
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
Bonsoir Eric,
merci pour ta réponse.
Malheureusement, elle dépasse largement mes piètres compétences... Je l'ai tout de même essayée, mais ai abouti à un résultat qui n'était pas celui attendu : cela fait fusionner des lignes entre elles.
Je vais bien finir par trouver une solution...
Je crois qu'on peut mettresur le forum un lien vers un fichier Excel (ce qui me permettrait de montrer concrètement où est le problème), mais je ne sais pas comment faire... le boulet !
merci pour ta réponse.
Malheureusement, elle dépasse largement mes piètres compétences... Je l'ai tout de même essayée, mais ai abouti à un résultat qui n'était pas celui attendu : cela fait fusionner des lignes entre elles.
Je vais bien finir par trouver une solution...
Je crois qu'on peut mettresur le forum un lien vers un fichier Excel (ce qui me permettrait de montrer concrètement où est le problème), mais je ne sais pas comment faire... le boulet !
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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.
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.
Pas vraiment, c'était une façon de ne prendre en compte le coef que s'il y a une note de saisie.
Ma formule modifiée avec l'edit que j'avais fait :
=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)))/SOMMEPROD(DECALER($AT$2;0;0;1;NB($AT$2:$AW$2))*(DECALER(AT3;0;0;1;NB($AT$2:$AW$2))<>""));1))
avec les coef en ligne 2, les notes en ligne 3, à recopier vers le bas si besoin.
J'ai l'impression que c'est bon mais ça mérite un contrôle approfondi...
Si vraiment ça fusionne les lignes je ferai breveter ;-)
ex: [http://www.cijoint.fr/cjlink.php?file=cj200910/cijpEZc709.xls Moyenne pondérée.xls
eric
Ma formule modifiée avec l'edit que j'avais fait :
=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)))/SOMMEPROD(DECALER($AT$2;0;0;1;NB($AT$2:$AW$2))*(DECALER(AT3;0;0;1;NB($AT$2:$AW$2))<>""));1))
avec les coef en ligne 2, les notes en ligne 3, à recopier vers le bas si besoin.
J'ai l'impression que c'est bon mais ça mérite un contrôle approfondi...
Si vraiment ça fusionne les lignes je ferai breveter ;-)
ex: [http://www.cijoint.fr/cjlink.php?file=cj200910/cijpEZc709.xls Moyenne pondérée.xls
eric
Bonjour Raymond,
ça cogite pendant que je dors (décalage horaire oblige), en me disant que le lendemain je comprendrai peut-être mieux les choses... entre toi et Eric, j'en ai pour la journée à comprendre :))
Je me mets dessus et je reviens vers vous pour vous dire si j'ai enfin pigé...
En tout cas, merci infiniment pour ce temps passé.
ça cogite pendant que je dors (décalage horaire oblige), en me disant que le lendemain je comprendrai peut-être mieux les choses... entre toi et Eric, j'en ai pour la journée à comprendre :))
Je me mets dessus et je reviens vers vous pour vous dire si j'ai enfin pigé...
En tout cas, merci infiniment pour ce temps passé.
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
► 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 !
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
► 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 !
Au point où j'en suis, je ne crains plus le ridicule :
1) comment est-ce que je fais pour donner à la plage B2:J2 le nom "pond" ?
2) j'ai le même problème qu'avec la formule proposée par Eric : si tu insères une nouvelle colonne et que tu lui affectes un coeff, les moyennes changent toutes avant même d'avoir reçu une note. Je pense que le problème est résolu dès que la note est renseignée, mais si la cellule reste vide, la moyenne a quand même changé (lors de l'affectation du coeff) alors qu'elle ne devrait pas. Il s'agit juste, dans ce cas, d'une note non renseignée, et la moyenne ne doit pas changer.
Ma formule du début n'était pas de moi, certes, mais quand je n'avais pas beaucoup de notes, je pouvais la faire à la main. ça marchait ! Je crois que je vais reprendre la bonne vieille calculatrice ;)
1) comment est-ce que je fais pour donner à la plage B2:J2 le nom "pond" ?
2) j'ai le même problème qu'avec la formule proposée par Eric : si tu insères une nouvelle colonne et que tu lui affectes un coeff, les moyennes changent toutes avant même d'avoir reçu une note. Je pense que le problème est résolu dès que la note est renseignée, mais si la cellule reste vide, la moyenne a quand même changé (lors de l'affectation du coeff) alors qu'elle ne devrait pas. Il s'agit juste, dans ce cas, d'une note non renseignée, et la moyenne ne doit pas changer.
Ma formule du début n'était pas de moi, certes, mais quand je n'avais pas beaucoup de notes, je pouvais la faire à la main. ça marchait ! Je crois que je vais reprendre la bonne vieille calculatrice ;)
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
voir post 22
eric
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.
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.
merci pour cette réponse et votre sympathie.
En cherchant, j'avais en effet constaté que ça ne marche pas quand l'insertion se fait à droite de la dernière colonne (logique d'ailleurs...)
En revanche, je ne parviens toujours pas à résoudre le problème sur les cellules comportant des dollars :
Avec ma formule actuelle :
=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))
Si j'insère une ou plusieurs colonnes entre AT et AV, j'obtiens bien ensuite
=SI(NB(AT3:AW3)=0 au début de ma formule, mais je n'obtiens pas la fin de la formule : +NB(AW3)*$AW$2
Je dois l'écrire à la main...
Je ne sais pas si mes explications sont très claires. J'espère que cela suffira.
Bien cordialement