Moyenne sur 7 jours glissants...Avec des totaux entre

Bonjour le forum !

Je viens en quête de conseils !

Pour le boulot, j'ai développé pas mal de petites applis en cours d'interfaçage; et à mes heures perdues je code pour que ces applis puissent être RaZ chaque année en fonction des nouvelles dates (jours ouvrés, fériés, etc...).

Jusqu'ici, tout va bien !

Ca se complique pour une chose pour laquelle je galère pas mal à trouver la solution.

J'ai deux feuilles (toutes choses égales par ailleurs); dont les jours sont disposés comme suit :

Mois de Mars Total Total Total

Semaine 10 xxx xxx xxx

01/03/2015

02/03/2015

03/03/2015

04/03/2015

05/03/2015

Semaine 13 Total Total Total

28/03/2015

29/03/2015

Mois d'Avril Total Total Total

01/04/2015

Certes, cette dispo est très pratique pour visualiser les données et les compartimentées, mais quelle galère c'est pour le traitement.

J'ai réussi à gruger à l'origine en utilisant des noms (macros qui génèrent des noms de plage APRES une saisie manuelles des références de cellules, donc bien galère); mais s'il faut perenniser le truc ça sera pas viable du tout.

Alors pour l'histoire des noms, j'ai pensé à un :

For n = 1 To 55
For Each cell In F1.Range("C4:C" & derlig1bis)
If cell.Offset(-1, 0).Interior.Color = 16315374 Then
Sheets("Feuil120").Range("b1:b" & n) = cell.Address
If cell.Interior.Color = 16315374 Then
Sheets("Feuil120").Range("A1:A" & n) = cell.Offset(-1, 0).Address
End If
End If
Next
Next

Pour pouvoir récupérer les adresses de début/fin de plage. Je l'ai pas encore essayé; je me suis sûrement gourré dans l'imbrication, mais rien de sorcier.

Par contre, le problème c'est que dans le cas présent, la feuille 2 (Prévisionnel) doit tirer ses prévisions de la feuille 1 (Historique), en faisant une moyenne des 10 derniers jours glissants. Et là, je bloque; cette disposition m'horripilant au possible. Donc avant de craquer et de virer mes chères semaines, j'aimerais bénéficier de vos conseils !

Le fichier est joint

Merci d'avance !

Poutous poutous

PS : J'ai volontairement réduit le fichier à 2 mois, car bien trop gros sinon (Près de 1,5mo pour 2 feuilles avec pratiquement aucune formule ni macro !); et j'ai du le 7zippé. Mea culpa.

Bonjour,

Je viens de télécharger ton fichier mais il y des protections dans le thisworkbook.

Je n'arrive pas à faire quoique ce soi.

Cordialement

ddetp88

Mince... Mea Culpa, j'ai sûrement oublié de faire sauter la protection.

Essaie "MLY10", si c'est pas ça, je re-uperai le fichier une fois au boulot.

Désolé du dérangement !

Non, rien!

voici ce que j'ai à l'écran. Je pense que c'est le cas pour tous sinon je ne comprends plus!

Bon courage.

ddetp88

image1

Bonjour SoumZoum,

Un essai, non pas par VBA, mais par formule. La formule est une formule matricielle en cellule C16 de la feuille Prévisionnel (à recopier vers le bas):

=SI(NB($C$5:C16) < 10;"";SI($C16="";"";MOYENNE.SI.ENS(DECALER(Réalisé!G16;-PETITE.VALEUR(SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16));10);0;PETITE.VALEUR(SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16));10)+1;1);DECALER($C16;-PETITE.VALEUR(SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16));10);0;PETITE.VALEUR(SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16));10)+1;1);">1")))

Elle fait la moyenne des 9 valeurs de la feuille Réalisé précédant la cellule G16 et de G16 soit 10 valeurs. Si on veut veut moyenner 7 valeurs, alors remplacer la valeur 10 par 7 dans la formule.

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.

Magnifique ! Ca marche, c'est parfait !

A un détail près Je suis un peu une bille au niveau des formules, donc malgré toute ma bonne volonté j'arrive pas à lui faire moyenner la colonne "M". J'ai essayé de modifier le premier DECALER, en remplaçant G16 par M16, mais rien y fait ; je me doute que ça doit se gérer à ce niveau là, mais la gueule de bois fait que j'ai beaucoup de mal.

Tu crois qu'il serait possible de me dire quel terme je dois modifier ?

En tout cas merci !

Bonsoir SoumZoum,

Et pourtant c'est ce qu'il faut faire !

Dans la formule, pour prendre les valeurs de la colonne M (et non G) il faut remplacer Réalisé!G16 par Réalisé!M16.

Es-tu certain d'avoir validé la formule par Ctrl+Maj+Entrée après avoir fait la modification ?

Si ce n'est pas ça, publier un bout du fichier en cause pour examen...

Explication de la formule: (fichier v2):

On part de la cellule C16. On va prendre la plage de la colonne C qui part de C16 (en remontant) et qui comprend 15 valeurs (15 pour pour prendre assez de cellules pour avoir à coup sûr au moins 10 valeurs non vides). Ce qui est fait par la référence à : $C4:$C16

Sur cette plage (d'où la formule matricielle), on applique une condition qui va renvoyer le décalage de la cellule par rapport à C16 si la cellule n'est pas vide et qui renvoie FAUX si la cellule est vide : SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16)). Si C16 n'est pas vide, on renvoie LIGNE() (=16 puisque la formule est sur la ligne 16) moins ligne(16) soit 0, si C15 n'est pas vide on renvoie ligne() (=16 puisque la formule est sur la ligne 16) moins ligne(15) soit 1.

On obtient donc une matrice du type {FAUX;11;10;FAUX;8;7;6;5;4;FAUX;2;1;0}.

A partir de cette matrice, on va regarder le décalage minimum qui correspond exactement à 10 valeurs non vides. Pour cela, on recherche la dixième plus petite valeur de décalage dans la précédente matrice soit PETITE.VALEUR($C4:$C16;10) PETITE.VALEUR(SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16));10). Petite.valeur ne tenant pas compte des valeurs FAUX) cette formule renvoie 11.

Maintenant il faut moyenner les 10 valeurs qui se trouvent en colonne M de la feuille "Réalisé". La plage à moyenner part de M16, remonte vers le haut et s'arrête à la cellule qui est décaleé de 11 positions vers le haut à partir de M16. A partir de M16, on décale de -11 cellules, et on prend en tout 12 cellules (les cellules décalées de -1 à -11 plus la celulle M16).

Soit : DECALER(Réalisé!M16;-11;0;11+1;1) (on décale de -11 lignes, 0 colonne, on prend 12 cellules en ligne, et on reste sur la même colonne)

On utilise ensuite la formule Moyenne.si pour ne retenir que les valeurs correspondant à une date. Si dans la colonne C il y a une date alors la valeur est >1.

On utilise la formule: Moyenne.si(DECALER(Réalisé!M16;-11;0;11+1;1) ; DECALER(Réalisé!C16;-11;0;11+1;1) ; ">1")

Si la ligne de la plage au dessus de C16 est >1 alors on suppose que c'est une date et on prend la valeur correspondante de la plage de la feuille "Réalisé" dans la calcul de la moyenne, sinon on ne la prend pas.

En remplaçant la formule qui donne le décalage 11 dans la précédente formule, on aboutit à :

moyenne.si(DECALER(Réalisé!M16;-PETITE.VALEUR(SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16));10);0;PETITE.VALEUR(SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16));10)+1;1) ; DECALER(Réalisé!C16;-PETITE.VALEUR(SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16));10);0;PETITE.VALEUR(SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16));10)+1;1) ; ">1")

Les deux premières conditions s'assurent 1) qu'on a bien au moins 10 valeurs et 2) que si dans la colonne C la cellule est vide (semaine ou mois), alors on affiche vide.

=SI(NB($C$5:C16) < 10;"";SI($C16="";"";.........

Le fichier v2 contient une formule raccourcie qui utilise un nom dynamique decalage.

  • se placer sur la cellule C16 de la feuille Prévisionnel (primordial)
    choisir le menu Formule / Définir un nom
    indiquer dans la zone Nom : decalage
    et dans la zone Fait référence à coller =PETITE.VALEUR(SI($C4:$C16<>"";LIGNE()-LIGNE($C4:$C16));10)

La formule se simplifie en :

=SI(NB($C$5:C16) < 10;"";SI($C16="";"";MOYENNE.SI.ENS(DECALER(Réalisé!M16;-Decalage;0;Decalage+1;1);DECALER($C16;-Decalage;0;Decalage+1;1);">1")))

Bonjour !

Tout d'abord je te remercie énorrrrrrrrmément pour l'explication très détaillée. J'avoue ne pas avoir tout compris, mais ça me donne les bases pour m'en servir et à force de pratique, ça rentrera sûrement

Alors, j'ai reessayé la formule; malheureusement, elle n'affiche rien. Après m'être rendu compte qu'il fallait valider la formule matricielle sur une case et la recopier-incrémenter vers le bas, ALLELUJAH ! Ca marche

Merci encore énormément, au top !! Un helpeur comme il les faudrait tous !

Rechercher des sujets similaires à "moyenne jours glissants totaux entre"