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.
Un petit UP !
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 !
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
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 !