XL 2016 Moyenne des x dernières notes

[nvsk89]

Bonjour,

Je possède un tableau comme suit, avec des étudiants qui passent des examens, mais pas au même jour de la semaine. Ils ont chacun quatre notes et un zéro.

	Lundi	Mardi	Mercredi	Jeudi	Vendredi	Samedi	Dimanche
Pierre	12	4		0	13	11
Paul	11		5	9		6	0
Nathan	17	0	18		14	11
Sylvie		15		14	13	11	0
Claire	0	2		11		13	16
Sylvain	1	4	0		14		12
Nicolas	13		0	17	15	19

Je voudrais créer une formule me permettant d'avoir la moyenne de leur x premières notes en négligeant à la fois les zéro ainsi que les cellules vides (exemple ci-joint).

EXEMPLE : si je tape 3 dans ma cellule variable, cela me donnerait pour Pierre la moyenne de 12, et 4 (lundi et mardi, jeudi étant un 0)

J'arrive à faire une moyenne en décalant de x cases et en négligeant les 0 avec "MOYENNE.SI" mais ce n'est pas exactement le résultat que je cherche car cela ne me donne pas nécessairement le bon nombre de notes si les 3 premières notes dépassent des x premières colonnes.

Merci infiniment pour votre aide.

 

[mapomme]

Bonjour,

premières notes

Qu'est à dire ?

Les premières notes sont celles qui sont les plus petites ? (ordre croissant)
Les premières notes sont celles qui sont les plus grandes? (ordre décroissant)
Les premières notes sont celles sont sur l’échelle débutant à Lundi et se terminant le dimanche ?

D'autant plus que dans le fichier vous écrivez :

Moyenne des

3

dernières notes

 

[nvsk89]

Bonjour Mapomme,

Les premières notes sont sur l'échelle débutant de Lundi et se terminant le dimanche, de gauche à droite donc !

 

[danielco]

Bonjour,

D'après ton exemple (pas celui du classeur), avec le nombre de notes en M1 :

=INDEX($A2:$H2;AGREGAT(14;6;COLONNE(B2:H2)/(B2:H2>0);1))+SI(M1>1;INDEX($A2:$H2;AGREGAT(14;6;COLONNE(B2:H2)/(B2:H2>0);2)))+SI(M1>2;INDEX($A2:$H2;AGREGAT(14;6;COLONNE(B2:H2)/(B2:H2>0);3)))+SI(M1>3;INDEX($A2:$H2;AGREGAT(14;6;COLONNE(B2:H2)/(B2:H2>0);4)))/M1

Valable pour M1<5. Il faut compléter la formule jusqu'à 7 (j'ai eu la flemme...)

Daniel

 

[Hasco]

Bonjour,

Tel que j'ai compris. Les 3 premières notes qui ne sont ni 0 ni vides.

=MOYENNE(INDEX(B2:H2;;AGREGAT(15;6;COLONNE(B2:H2)-1/(B2:H2>0);{1.2.3})))

Sur excel 2016, validation par CTRL+MAJ+ENTRER necessaire ?

Pour avoir les 3 dernières, remplacer le 15 de Agregat par 14

Dans le fichier j'ai laissé pour démonstation les notes prises en comptes (F12:H19)

 

[nvsk89]

Bonjour et merci beaucoup pour vos réponses et votre temps, vraiment.

J'ai l'impression toutefois que ni l'une ni l'autre ne fonctionne (par exemple dans l'exemple de Hasco la moyenne de Sylvain est 1 alors que les 3 notes sont 1, 4 et 14).

Peut être me suis-je mal exprimé : je voudrais obtenir une formule MOYENNE.SI(PLAGE;<>0) dans lequel la taille de la plage serait les x premières cellules non vides en partant de lundi. Ainsi dans mon tableau d'exemple :

- Pour Pierre, les 3 premières cellules non vides sont 12, 4 et 0, donc je souhaite obtenir la moyenne de 12 et 4 (soit 8);
- Pour Paul, les 3 premières cellules non vides sont 11, 5 et 9 donc je souhaite obtenir la moyenne de 11, 5 et 9 (soit 8,33);
- etc.

L'idée serait que le "3" de mon exemple ci-dessus soit une variable. Pour le résumer à sa plus simple expression, je souhaiterais parvenir à trouver la formule pour faire une plage variable en fonction du nombre de cellules non vides.

 

[danielco]

Oups.

=(INDEX($A2:$H2;AGREGAT(14;6;COLONNE(B2:H2)/(B2:H2>0);1))+SI($M$1>1;INDEX($A2:$H2;AGREGAT(14;6;COLONNE(B2:H2)/(B2:H2>0);2)))+SI($M$1>2;INDEX($A2:$H2;AGREGAT(14;6;COLONNE(B2:H2)/(B2:H2>0);3)))+SI($M$1>3;INDEX($A2:$H2;AGREGAT(14;6;COLONNE(B2:H2)/(B2:H2>0);4))))/$M$1

Le nombre de notes à prendre en compte est en M1

Daniel

 

[Hasco]

par exemple dans l'exemple de Hasco la moyenne de Sylvain est 1 alors que les 3 notes sont 1, 4 et 14

Re,

Voici ce que j'obtiens : (Sylvain : 6.3333)

Validez la formule par CTRL+MAJ+ENTREE

 

[nvsk89]

Bonjour,

Tel que j'ai compris. Les 3 premières notes qui ne sont ni 0 ni vides.


Sur excel 2016, validation par CTRL+MAJ+ENTRER necessaire ?

Pour avoir les 3 dernières, remplacer le 15 de Agregat par 14

Dans le fichier j'ai laissé pour démonstation les notes prises en comptes (F12:H19)

C'est très étrange car lorsque j'ouvre votre fichier et que je mets "activer la modification", les moyennes se changent. J'ai bien validé la formule en matricielle mais je tombe sur d'autres chiffres que vous....

En tout état de cause, cette formule ne me permet pas exactement d'arriver au résultat que je recherche puisqu'il ne repose pas sur la variable en jaune dans mon fichier. N'existe-t-il pas une formule qui permette d'obtenir une plage de données sur le modèle (CELLULE1:CELLULEX) ou X serait le nombre de cellules à décaler pour que la plage contienne Y cellules non vides ?

 

[mapomme]

Bonsoir à tous,

Avec une fonction personnalisée plus facilement modifiable jusqu'à ce que tout soit clair.

La fonction s'appelle MoyenneN(xrg, xn) où

• xrg est la plage horizontale
• xn est le nombre de valeurs servant à calculer la moyenne

S'il n'y a pas au moins xn valeurs dans la plage, alors MoyenneN retourne l'erreur #N/A
Sinon, s'il n'y a que des zéros dans les xn premières valeurs, alors MoyenneN retourne l'erreur #DIV/0!

Le code de la fonction dans module1:

Function MoyenneN(xrg As Range, xn&)
Dim v, j&, n&, m&, s&
   v = xrg.Value
   For j = 1 To UBound(v, 2)
      If v(1, j) <> "" Then n = n + 1: v(1, n) = v(1, j)
   Next j
   If n < xn Then MoyenneN = CVErr(xlErrNA): Exit Function
   For j = 1 To xn
      If v(1, j) <> 0 Then m = m + 1: s = s + v(1, j)
   Next j
   If m = 0 Then MoyenneN = CVErr(xlErrDiv0): Exit Function
   MoyenneN = s / m
End Function

 

[nvsk89]

Bonsoir à tous,

Avec une fonction personnalisée plus facilement modifiable jusqu'à ce que tout soit clair.

La fonction s'appelle MoyenneN(xrg, xn) où

• xrg est la plage horizontale
• xn est le nombre de valeurs servant à calculer la moyenne

S'il n'y a pas au moins xn valeurs dans la plage, alors MoyenneN retourne l'erreur #N/A
Sinon, s'il n'y a que des zéros dans les xn premières valeurs, alors MoyenneN retourne l'erreur #DIV/0!

Le code de la fonction dans module1:

Function MoyenneN(xrg As Range, xn&)
Dim v, j&, n&, m&, s&
   v = xrg.Value
   For j = 1 To UBound(v, 2)
      If v(1, j) <> "" Then n = n + 1: v(1, n) = v(1, j)
   Next j
   If n < xn Then MoyenneN = CVErr(xlErrNA): Exit Function
   For j = 1 To xn
      If v(1, j) <> 0 Then m = m + 1: s = s + v(1, j)
   Next j
   If m = 0 Then MoyenneN = CVErr(xlErrDiv0): Exit Function
   MoyenneN = s / m
End Function

Bonjour Mapomme,

Merci infiniment c'est presque exactement ce que je recherchais ! J'ai simplement deux questions complémentaires :

1/ [ERREUR DE MA PART, LA FONCTION MARCHE PARFAITEMENT BIEN SUR CE PREMIER POINT]

2/ Je souhaiterais également avoir le pourcentage de cellules <>0 parmi les cellules non vides de l'intervalle. J'essaye de trouver comment le faire en déchiffrant ta fonction mais je suis trop peu expert pour y parvenir.

Merci vraiment pour cette solution qui me fait faire un pas de géant dans mon projet !

 

[mapomme]

1/ En testant la fonction sur mon fichier, je remarque que lorsque les x premières cellules non vides sont des 0, alors la moyenne n'est pas 0 mais celles des x cellules suivantes dont la valeur est différente de zéro. Or je souhaiterais que si les x premières cellules non vides soient des 0 que la moyenne soit bel et bien 0.

Quand les x premières cellules sont égales à zéro, la fonction renvoie #DIV/0!(du moins c'est comme cela la fonction a été codée) sinon renvoyer un fichier avec un exemple du comportement indésirable.

2/ Je souhaiterais également avoir le pourcentage de cellules <>0 parmi les cellules non vides de l'intervalle.

Sur la plage de la ligne complète ou bien sur la plage comprenant uniquement les xn première valeurs ?

 

[nvsk89]

Sur la plage de la ligne complète ou bien sur la plage comprenant uniquement les xn première valeurs ?

Sur la plage comprenant les xn première valeurs pardon.

 

[mapomme]

Re,

J'ai ajouté un 3ème paramètre optionnel à la fonction : MoyenneN(xrg , xn, xRatioNonNul)

• si le paramètre xRatioNonNul est absent, alors on retourne la moyenne (comme avant)
• si le paramètre est présent (il peut valoir n'importe quoi), alors on retourne le nombre de cellule différente de 0 parmi les xn premières valeurs

Voir formule en C13 à recopier vers le bas.