XL 2010 Moyenne des 3 dernières valeurs en fonction de la date.

[AltusXL]

Bonjour à tous,

Je fais partis d'un atelier où chaque semaine, nous surveillons plusieurs points clés.
Afin de capitaliser le résultat de cette surveillance, j'ai donc créer un fichier excel.

Comme vous pourrez le voir dans ce fichier, 6 thèmes majeurs sont surveillés (contenant chacun plusieurs items notés avec la note de 1 ou 0), et 2 thèmes sont évalués par semaine.

Afin d'exploiter ces données, j'ai décider de calculer la valeur moyenne à l'année de chaque item (facile à faire) mais aussi de faire la valeur moyenne sur les 3 derniers passages (à peu près 2 mois glissant). Et c'est précisément là que j'ai besoin de votre aide.

Je ne parviens pas à trouver une formule capable de le faire, et mes connaissances en Excel sont limités à ce sujet. Savez-vous m'aidez ?

Merci à vous.

 

[mapomme]

Bonjour AltusXL et bienvenue sur XLD ,

Essayez la formule matricielle suivante dans la cellule BF18 puis recopier cette cellule vers le bas. Cette formule devrait calculer la moyenne des trois valeurs les plus récentes. On n'utilise pas la notion de valeur glissante sur 2 mois.

Remarque : cette formule traite aussi les cas où il y a moins de trois (0, 1 ou 2) cellules renseignées.

Formule matricielle :

=SI(NB(D18:BC18)=1;SOMME(10*MOD(GRANDE.VALEUR(SI(ESTNUM(D18:BC18);COLONNE(D18:BC18)+D18:BC18/10;"");LIGNE($1:$1));1))/1;SI(NB(D18:BC18)=2;SOMME(10*MOD(GRANDE.VALEUR(SI(ESTNUM(D18:BC18);COLONNE(D18:BC18)+D18:BC18/10;"");LIGNE($1:$2));1))/2;SI(NB(D18:BC18)>2;SOMME(10*MOD(GRANDE.VALEUR(SI(ESTNUM(D18:BC18);COLONNE(D18:BC18)+D18:BC18/10;"");LIGNE($1:$3));1))/3;"")))

Nota :

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

Edit : oublié de changer un LIGNE($1:$2) par LIGNE($1:$3) -> version v1a corrigée

 

[mapomme]

Re,

Une autre formule matricielle bien plus courte :

=SI(NB(D18:BC18)>0;SOMME(10*MOD(GRANDE.VALEUR(SI(ESTNUM(D18:BC18);COLONNE(D18:BC18)+D18:BC18/10;"");LIGNE(INDIRECT("1:" & MIN(3;NB(D18:BC18)))));1))/MIN(3;NB(D18:BC18));"")

 

[Jocelyn]

Bonjour le forum,
Bonjour AltusXL, mapomme ,

pour le fun encore un peu plus courte toujours en matricielle a mettre en BF18

=MOYENNE(DECALER($A18;;MAX(SI(ESTNUM(A18:BC18);COLONNE(A:BC)))-MIN((NB(D18:BC18)*2);7);;7))

Cordialement

 

[mapomme]

Re,

@Jocelyn :
Très belle (et concise) formule mais qui ne fait pas tout à fait la même chose que celle de ma pomme. En effet, pour fonctionner, ma formule ne nécessite pas que les trois dernières valeurs soient consécutives et séparées par deux cellules vides. Cependant, la configuration du tableau de AltusXL laisse présager que c'est sans doute le cas et donc ta formule est top.

 

[chris]

Bonjour à tous

A noter qu'AltusXL a posté sur moult forums mais n'a pas reparu...

 

[AltusXL]

Bonjour,

Merci à tous pour votre réactivité j'ai pu résoudre mon problème très rapidement (1h après le post) en utilisant vos formules et notamment celle-ci:

=MOYENNE(DECALER(BE18;0;-(COLONNE()-EQUIV(9^9;A18:BC18)+6);1;8))

J'ai testée celle de Jocelyn et effectivement, elle est efficace. Quant à la formule matricielle, cela correspond aussi à mon besoin, merci Mapomme.
@chris, effectivement je passe sur plusieurs forum, je ne connais pas forcément la popularité de tel ou tel forum excel sur le net, donc je maximise mes chances

Merci à tous en tous les cas, continuez ainsi. C'est grâce à vous que l'on avance.

Bonne journée.