Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2016 Moyenne des x dernières notes

nvsk89

XLDnaute Nouveau
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.

LundiMardiMercrediJeudiVendrediSamediDimanche
Pierre12401311
Paul115960
Nathan170181411
Sylvie151413110
Claire02111316
Sylvain1401412
Nicolas130171519

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.
 

Pièces jointes

  • Exemplesnotes.xlsx
    10.9 KB · Affichages: 14
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
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 des3dernières notes
 

danielco

XLDnaute Accro
Bonjour,

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

VB:
=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

XLDnaute Barbatruc
Repose en paix
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)
 

Pièces jointes

  • Exemplesnotes.xlsx
    21.4 KB · Affichages: 9

nvsk89

XLDnaute Nouveau
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

XLDnaute Accro
Oups.

VB:
=(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
 

nvsk89

XLDnaute Nouveau

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

XLDnaute Barbatruc
Supporter XLD
Bonsoir à tous,

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

La fonction s'appelle MoyenneN(xrg, xn)
  • 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:
VB:
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
 

Pièces jointes

  • nvsk89- Moyenne n notes- v1.xlsm
    17.8 KB · Affichages: 4
Dernière édition:

nvsk89

XLDnaute Nouveau
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 !
 
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
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 ?
 

mapomme

XLDnaute Barbatruc
Supporter XLD
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.
 

Pièces jointes

  • nvsk89- Moyenne n notes- v2.xlsm
    18.3 KB · Affichages: 4
Dernière édition:

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…