Moyenne avec insertion de nouvelle colonne

Résolu/Fermé
smayot
Messages postés
14
Date d'inscription
dimanche 18 octobre 2009
Statut
Membre
Dernière intervention
23 octobre 2009
- 18 oct. 2009 à 15:50
 smayot - 25 oct. 2009 à 18:59
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 :)

8 réponses

Raymond PENTIER
Messages postés
56491
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
14 août 2022
17 198
19 oct. 2009 à 06:18
En effet.
Il ne faut pas ajouter une colonne après AV mais insérer une colonne avant AV.

... et bienvenue au nouveau membre !
0
Bonjour Raymond,

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
0
Raymond PENTIER
Messages postés
56491
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
14 août 2022
17 198
20 oct. 2009 à 02:43
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) !
0
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...
0
Raymond PENTIER
Messages postés
56491
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
14 août 2022
17 198 > smayot
20 oct. 2009 à 22:54
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.
0
smayot
Messages postés
14
Date d'inscription
dimanche 18 octobre 2009
Statut
Membre
Dernière intervention
23 octobre 2009
> Raymond PENTIER
Messages postés
56491
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
14 août 2022

20 oct. 2009 à 23:03
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...
0
eriiic
Messages postés
24430
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 août 2022
7 105
20 oct. 2009 à 07:52
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
0
smayot
Messages postés
14
Date d'inscription
dimanche 18 octobre 2009
Statut
Membre
Dernière intervention
23 octobre 2009

20 oct. 2009 à 22:45
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 !
0
dobo69
Messages postés
1584
Date d'inscription
vendredi 24 juillet 2009
Statut
Membre
Dernière intervention
30 juin 2013
789
20 oct. 2009 à 23:09
bonsoir,

il faut déposer le fichier ICI et copier-coller le lien donné dans ton prochain post
0
smayot
Messages postés
14
Date d'inscription
dimanche 18 octobre 2009
Statut
Membre
Dernière intervention
23 octobre 2009

21 oct. 2009 à 10:04
Bonjour,

j'ai déposé le fichier sur le poste 8.
Merci pour votre aide :)
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
Raymond PENTIER
Messages postés
56491
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
14 août 2022
17 198
20 oct. 2009 à 23:17
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.
0
eriiic
Messages postés
24430
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 août 2022
7 105
20 oct. 2009 à 23:44
Salut raymond,

nb(AT) peut aussi être =0 s'il n'y a pas de saisie, c'est sa raison d'être je pense
eric
0
Raymond PENTIER
Messages postés
56491
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
14 août 2022
17 198 > eriiic
Messages postés
24430
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 août 2022

21 oct. 2009 à 00:28
Exact, Eric.
Mais en l'occurrence cette précaution est superflue, non ?
Amitiés.
0
eriiic
Messages postés
24430
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 août 2022
7 105 > Raymond PENTIER
Messages postés
56491
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
14 août 2022

21 oct. 2009 à 00:54
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
0
smayot
Messages postés
14
Date d'inscription
dimanche 18 octobre 2009
Statut
Membre
Dernière intervention
23 octobre 2009
> eriiic
Messages postés
24430
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 août 2022

21 oct. 2009 à 10:14
Bonjour Eric,

merci pour ta réponse.
Sauf erreur de ma part, ça ne marche toujours pas : si j'insère une colonne entre AU et AV, AV devient AW, et la moyenne qui était alors de 9,3 passe à 11 : pourquoi ? Aucune idée... mais le résultat est là :)
0
eriiic
Messages postés
24430
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 août 2022
7 105 > smayot
Messages postés
14
Date d'inscription
dimanche 18 octobre 2009
Statut
Membre
Dernière intervention
23 octobre 2009

21 oct. 2009 à 19:10
Re,

Tu parlais au début d'insérer en AW (en fin)... Mais tu peux aussi insérer au milieu (pas devant par contre, faut pas pousser...)
Le principe sera le même : si tu insères une colonne c'est que tu veux ajouter des notes. Tout sera d'équerre à partir du moment où TOUT les coef auront été saisis (ne serait-ce que 1 mais il faut un chiffre).
voir post 4 : 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.
Autre règle à respecter : la cellule après le dernier coef doit toujours être vide.

Je n'avais pas vu que tu avais déposé un exemple (d'ailleurs c'est plus simple en B qu'en AT...), j'ai adapté la formule à ton fichier.
J'ai mis ma formule en M sur qcq lignes (tu peux la tirer vers le bas) que tu puisses comparer et contrôler.

eric
0
Raymond PENTIER
Messages postés
56491
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
14 août 2022
17 198
20 oct. 2009 à 23:58
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 !
0
smayot
Messages postés
14
Date d'inscription
dimanche 18 octobre 2009
Statut
Membre
Dernière intervention
23 octobre 2009

21 oct. 2009 à 23:18
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 ;)
0
eriiic
Messages postés
24430
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
11 août 2022
7 105
22 oct. 2009 à 00:05
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
0
Raymond PENTIER
Messages postés
56491
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
14 août 2022
17 198
22 oct. 2009 à 03:05
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.
0
smayot
Messages postés
14
Date d'inscription
dimanche 18 octobre 2009
Statut
Membre
Dernière intervention
23 octobre 2009

22 oct. 2009 à 23:03
Bonsoir Raymond,

je n'ai pas essayé de mélanger les deux formules : c'est déjà suffisamment complexe pour moi comme ça...

Ma version Excel est Excel 97-2003.
0
Bonjour Raymond,

j'ai dû avoir un pb sur mon ordi car je me demandais pourquoi tu ne répondais pas alors que ma réponse n'est elle-même pas apparue : je suis sous Excel 2003.
0