Excel - faire une moyenne géométrique d'une liste de valeurs constituée à partir d'une formule

Question

Bonjour,

Je ne suis pas certaine que le titre soit explicite, je développe donc : j'ai un tableau avec en colonnes des notes par matière et par élève. Un même élève peut avoir plusieurs notes pour une même matière. Voilà un extrait de ce tableau :

Alexandre	14,5	Français
Alexandre	12	Français
Alexandre	16	Histoire
Alexandre	8	Histoire
Alexandre	10	Histoire
Alexandre	9	Mathématiques
Cécile	5	Français
Cécile	12	Français
Cécile	13	Français
Cécile	12	Histoire
Cécile	18	Mathématiques
Cécile	10,5	Mathématiques
Ludivine	15	Histoire
Ludivine	16	Histoire
Ludivine	9	Mathématiques
Ludivine	10	Mathématiques
Ludivine	8	Mathématiques
Ludivine	12	Français
Ludivine	14,5	Français

Il s'agit d'un extrait, il y a beaucoup plus de lignes.

Je dois calculer la moyenne géométrique des notes par élève et par matière de façon "automatisée". Je sais que je pourrais par exemple insérer une ligne après chaque groupement "élève + matière" (par exemple, en ligne 3) et calculer à chaque fois la moyenne géométrique des notes du dessus. Mais comme dit + haut, c'est un tableau à plus de 600 lignes, cela ne me parait pas adéquat.

J'ai pensé à un TCD mais les TCD ne permettent pas de faire des moyennes géométriques, uniquement des moyennes.

En gros, il faudrait que j'arrive à constituer de façon dynamique des plages de données à insérer dans un tableau de ce type :

	Français	Histoire	Mathématiques
Alexandre	=MOYENNE.GEOMETRIQUE(B1:B2)	=MOYENNE.GEOMETRIQUE(B3:B5)	=MOYENNE.GEOMETRIQUE(B6:B6)
Cécile	=MOYENNE.GEOMETRIQUE(B7:B9)	=MOYENNE.GEOMETRIQUE(B10:B10)	=MOYENNE.GEOMETRIQUE(B11:B12)
Ludivine	=MOYENNE.GEOMETRIQUE(B18:B19)	=MOYENNE.GEOMETRIQUE(B13:B14)	=MOYENNE.GEOMETRIQUE(B15:B17)

Dans chaque cellule du tableau, la ""PLAGE DE DONNEES" dans les formules =MOYENNE.GEOMETRIQUE(PLAGE DE DONNEES) seraient calculées automatiquement.

Peut-être utiliser un RECHERCHEV mais ça ne renvoie qu'une valeur, pas une plage de valeurs.

Je tourne en rond depuis des heures, je n'arrive pas à trouver comment en gros parcourir mon 1er tableau ci-dessus et constituer une plage de données à utiliser ensuite dans mes formules de moyenne géométrique.

Après réflexion, cela pourrait aussi revenir à une formule MOYENNE.GEOMETRIQUE.SI mais ça n'existe pas :-(

Si quelqu'un a une idée miraculeuse (ou pas!), je suis preneuse!

Merci par avance.

Sandrine

Answer 1

Bonjour,

En relisant ma réponse j'ai vu que je n'ai pas nommé la plage des notes ce qui n'est pas très cohérent vu que les plages des Prénoms et Matières sont renommées ! 😔

Le problème vous rencontrer pour modifier la formule vient du faite que dans la formule je profite d'une conversion implicite qu'Excel réalise automatiquement avec les 2 critères mais pas quand il n'y en a qu'un !

(Pl_Prenom=$E3) renvoie des VRAI ou des FAUX

(Pl_Mat=F$2) renvoie des VRAI ou des FAUX

mais

(Pl_Prenom=$E3) * (Pl_Mat=F$2) renvoie des 1 ou des 0 (1 correspond à VRAI et 0 à FAUX) !

d'ou mon "<>0" pour reconvertir en VRAI/FAUX

La formule simplifiée (et ajout de la Pl_Note correspondant à la plage B2:B20) donne :

=PRODUIT(SI((Pl_Mat=F$2);Pl_Note))^(1/SOMME((Pl_Mat=F$2)*1))

Le <>0 devient inutile mais on doit ajouter *1 pour convertir de manière explicite les VRAI/FAUX en 0/1

Answer 2

Autre solution compatible 2016

Formules un peu plus longues 😁

=SIERREUR(INDEX(Pl_Mat;PETITE.VALEUR(SI(FREQUENCE(SI(Pl_Mat<>"";EQUIV(Pl_Mat;Pl_Mat;0));LIGNE(Pl_Mat)-1);LIGNE(Pl_Mat)-1);COLONNES($F2:F2)));"")

=SIERREUR(INDEX(Pl_Prenom;PETITE.VALEUR(SI(FREQUENCE(SI(Pl_Prenom<>"";EQUIV(Pl_Prenom;Pl_Prenom;0));LIGNE(Pl_Prenom)-1);LIGNE(Pl_Prenom)-1);LIGNES(E$3:E3)));"")

=PRODUIT(SI((Pl_Prenom=$E3)*(Pl_Mat=F$2)<>0;$B$2:$B$20))^(1/SOMME((Pl_Prenom=$E3)*(Pl_Mat=F$2)))

Zone nommées :

Pl_Prenom => plage A2:A20

Pl_Mat => plage C2:C20

Attention ! Valider les formules avec Ctrl + Maj + Entrer (=> apparition des accolades)

Voir : Excel et les matrices (pas celles du cours de mathématiques)

Answer 3

Bonsoir,

Merci pour vos réponses.

Mais la fonction LET n'est pas disponible dans Excel 2016 :-(

Answer 4

Pour info avec la nouvelle fonction PIVOTER.PAR (voir Les nouvelles fonctions GROUPER.PAR (GROUPEBY) et PIVOTER.PAR (PIVOTBY)) en une seule formule (mais elle n'est pas encore disponible pour tous le monde) !

=PIVOTER.PAR(A2:A20;C2:C20;B2:B20;LAMBDA(v;PRODUIT(v)^(1/NB(v)));0;0;1;0;1)

Answer 5

Bonjour,

Une solution en 3 formules

=TRIER(UNIQUE(A2:A20))

=TRANSPOSE(TRIER(UNIQUE(C2:C20)))

=LET(m;FILTRE($B$2:$B$20;($A$2:$A$20=$E3)*($C$2:$C$20=F$2));PRODUIT(m)^(1/NB(m)))