Formule Excel avec glissement partiel

Bonsoir tout le monde, bonne année 2022 !!

Je cale sur une formule sur excel sans passer par VBA

colonne a colonne B colonne C

a1 : -5 b1 = a1 - moyenne(a1:a5)

a2 : -6 b2 = a2 - moyenne (a1:a5) c2 = a2 - moyenne (a2:a6)

a3 : -5 b3 == a3 - moyenne (a1:a5) c3 == a3 - moyenne(a2:a6)

a4 : -5 b4= a4 - moyenne (a1:a5) c4 == a4 - moyenne (a2:a6)

a5 : -6 b5= a5 - moyenne (a1:a5) c5 == a5 - moyenne (a2:a6)

a6 : -8 c6 == a6 - moyenne (a2:a6)

a7 : -9

...

je souhaite tirer la formule de B1 vers B5 , avec la première valeur qui bouge mais avec la plage de moyenne (a1:a5) qui reste fixe

puis je veux faire glisser cela vers la colonne C , ou tout sera décalé de une ligne et idem la plage de moyenne ne doit pas bouger

je ne sais pas si je suis claire, ....c est peut être pas difficile mais je galère grave ....

en effet le modele est sur une plage de 5 données mais en fait dans mon fichier c est 1500 data

si qq un peut m aider

Mille merci

AD

Bonjour et meilleurs voeux,

je ne sais pas si je suis claire

Pas vraiment, avec un bout de fichier joint et quelques exemples, ça le serait davantage.

Cdlt

Bonjour Arturo83

Merci pour ta réponse !

En effet, mon schéma est pas ultra clair ... quand je l'ai écrit la mise en forme n'était pas la même

Je joins un fichier exemple, en espérant que cela soit plus clair

Merci bcp

Bonjour,

J'ai changé le titre des colonnes C D E etc, en appliquant le format de cellule personnalisée suivant:

"obs 1-" 0

Pour ajouter d'autres titres de colonnes, il suffit de sélectionner la dernière cellule de la ligne 1 et de tirer vers la droite.

Puis pour les colonnes, formule en C2:

=SI(LIGNE()>C$1+1;"";B2-MOYENNE(INDIRECT("B"&LIGNE()&":B"&C$1+1)))

à tirer vers le bas jusqu'à la ligne 200.

Pour les colonnes suivantes, exemple pour la colonne D, sélectionner la cellule C2, et la recopier en D3 et tirer vers le bas et ainsi de suite pour les autres colonnes .

Si j'ai bien tout compris.

CDlt

Pièce jointe supprimée le 09/06/2025

Merci beaucoup Arturo,

Ça fonctionne presque bien !! tu as super bien compris !!

J

e n'arrive pas à générer la formule suivante pour une autre application (je vois que j'ai des lacunes en excel :-(((((

"obs 1-" 0

Si je fais le calcul de manière individuelle pour chaque ligne de la colonne C, le résultat que j'obtiens est bon pour la première ligne mais ensuite il est de plus en plus petit ! Je pense que quelque chose s accumule et dans la formule quand tu tires et qui rend le resultat de plus en plus éloigné

J ai mis le fichier avec le calcul individuel pour montrer la différence de résultat

Et là, suis-je plus près de la solution?

**********************************************************************************************************************************

Je n'arrive pas à générer la formule suivante pour une autre application (je vois que j'ai des lacunes en excel :-(((((

"obs 1-" 0

Ce n'est pas une formule, mais un format de cellule personnalisée obtenu comme ceci:

l'avantage, c'est que la cellule n'est pas vu comme du texte (de par la présence de "obs1-") mais comme une cellule numérique, (elle ne voit que le chiffre 100) ce qui permet de faire des opérations avec cette valeur, chose qu'on ne pourrait pas faire avec la cellule au format texte. Ainsi on peut intégrer cette valeur dans une formule pour récupérer le N° de la dernière ligne à prendre en compte, exemple pour la formule en C2:

=SI(LIGNE()>C$1+1;"";$B2-MOYENNE(INDIRECT("B" &COLONNE()-1 & ":B"&C$1+1))), ici on fixe le N° de la dernière ligne à la valeur de la cellule C1(soit 100) à laquelle on ajoute 1 pour trouver la 100ème ligne hors ligne de titre.

Est-ce plus clair?

Cdlt

Pièce jointe supprimée le 09/06/2025

Cher Arturo

Vraiment mille merci !!!!! Oui c'est correct ça marche

C'est nickel

ça m'aide énormément !!!!

La difficulté maintenant est d'arriver à la refaire ALONE

Cdt

AD

Bonjour,

Si ça vous tire souci, voici une proposition en VBA qui se chargera de faire le travail à votre place, elle s'occupe de tout, après que vous ayez saisi vos données en colonnes A et B, vous n'avez plus qu'à cliquer sur le bouton "Moyenne glissante".

Cdlt

Pièce jointe supprimée le 09/06/2025

Bonjour Arturo

Merci beaucoup pour le programme en VBA, c'est quelque chose que je maîtrise mal

Je vais regarder cela pour progresser

Si j'ai des soucis je me permettrai de relancer un sujet

Bonne soirée

AD

Je viens de jeter un oeil sur le VBA

Sub Moyenne()
Dim i As Long, NbLig As Long, DerCol As Long
Application.ScreenUpdating = False
NbLig = Range("A" & Rows.Count).End(xlUp).Row
DerCol = Range("A1").End(xlToRight).Column
If DerCol > 2 Then Range(Cells(1, 3), Cells(DerCol, DerCol)).ClearContents
'Création titres de colonnes
Range(Cells(1, "C"), Cells(1, NbLig + 1)).NumberFormat = """obs 1-"" 0"
Range("C1").FormulaR1C1 = "100"
Range(Cells(1, "D"), Cells(1, NbLig - 98)).FormulaR1C1 = "=RC[-1]+1"
For i = 1 To NbLig - 100
Range(Cells(i + 1, i + 2), Cells(i + 100, i + 2)).FormulaR1C1 = "=IF(ROW()>R1C+1,"""",RC2-AVERAGE(INDIRECT(""B"" &COLUMN()-1 & "":B""&R1C+1)))"
Next i
End Sub

Comment je peux faire si je veux augmenter le nombre de données entrantes dans la premiere colonne (passer de 200 à 10000 suivant les cas), puis augmenter la taille de ma plage moyenne qui est de 100 et que je voudrais faire varier (exemple 1500 ou 2000). Je suppose que je dois modifier les éléments surlignés en jaune ????

Cordialement

AD

Bonjour,

Comment je peux faire si je veux augmenter le nombre de données entrantes dans la premiere colonne (passer de 200 à 10000 suivant les cas)

Mettez le nombre de données que vous voulez, le programme détecte le nombre de ligne occupées.

**********************************************************************************

puis augmenter la taille de ma plage moyenne qui est de 100 et que je voudrais faire varier (exemple 1500 ou 2000).

Dans le code suivant, j'ai mis une ligne(3ème ligne) qui vous demande de saisir la "TAILLE" de la plage des moyennes, (par défaut j'ai mis 100, mettez ce que vous voulez, de préférence la taille que vous compter utiliser le plus souvent), les calculs s'effectueront en fonction de ce que vous aurez saisi dans la boîte de dialogue.

Sub Moyenne()
    Dim i As Long, NbLig As Long, DerCol As Long
    Application.ScreenUpdating = False
    Taille = InputBox("Saisissez la taille de la plage des moyennes", "Moyennes glissantes", 100)
    NbLig = Range("A" & Rows.Count).End(xlUp).Row
    DerCol = Range("A1").End(xlToRight).Column
    If DerCol > 2 Then Range(Cells(1, 3), Cells(DerCol, DerCol)).ClearContents
    'Création titres de colonnes
    Range(Cells(1, "C"), Cells(1, NbLig + 1)).NumberFormat = """obs 1-"" 0"
    Range("C1").FormulaR1C1 = Taille
    Range(Cells(1, "D"), Cells(1, NbLig - Taille - 2)).FormulaR1C1 = "=RC[-1]+1"
    For i = 1 To NbLig - Taille
        Range(Cells(i + 1, i + 2), Cells(i + Taille, i + 2)).FormulaR1C1 = "=IF(ROW()>R1C+1,"""",RC2-AVERAGE(INDIRECT(""B"" &COLUMN()-1 & "":B""&R1C+1)))"
    Next i
End Sub

Recopiez ce code à la place de l'autre et dites-moi si ça vous convient.

CDlt

Ca fonctionne super !

le seul hic ... quand je mets la globalité des données que j'ai 10000 et que la plage de moyenne est de 1500 ... cela fait trop de données et ça tourne dans le vide

Cdt

Bonjour,

Eh oui, c'est une quantité énorme, alors essayez ceci:

Sub Moyenne()
    Dim i As Long, NbLig As Long, DerCol As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Taille = InputBox("Saisissez la taille de la plage des moyennes", "Moyennes glissantes", 100)
    NbLig = Range("A" & Rows.Count).End(xlUp).Row
    DerCol = Range("A1").End(xlToRight).Column
    If DerCol > 2 Then Range(Cells(1, 3), Cells(DerCol, DerCol)).ClearContents
    'Création titres de colonnes
    Range(Cells(1, "C"), Cells(1, NbLig + 1)).NumberFormat = """obs 1-"" 0"
    Range("C1").FormulaR1C1 = Taille
    Range(Cells(1, "D"), Cells(1, NbLig - Taille - 2)).FormulaR1C1 = "=RC[-1]+1"
    For i = 1 To NbLig - Taille
        Range(Cells(i + 1, i + 2), Cells(i + Taille, i + 2)).FormulaR1C1 = "=IF(ROW()>R1C+1,"""",RC2-AVERAGE(INDIRECT(""B"" &COLUMN()-1 & "":B""&R1C+1)))"
    Next i
    Application.Calculation = xlCalculationAutomatic
    Range(Cells(2, 3), Cells(NbLig, DerCol)).Value = Range(Cells(2, 3), Cells(NbLig, DerCol)).Value
End Sub

Cdlt

Bonsoir,

Malheureusement ça mouline toujours ...

je vous joins l'exemple complet de ce que je souhaite faire ce sera plus clair, je pensais pouvoir y arriver seule mais non !!

j ai reussi a faire la premiere etape sans macro mais mon fichier excel fait 400000ko

je peux arriver a faire la seconde etape que vous trouverez dans le fichier dans une seconde feuille, sachant que j'ai besoin de toutes les matrices calculées

je ne pense pas qu il soit possible de faire une vba sans que cela plante

cdt

AD

Sachant que ma base compléte fait 10000données et que dois faire une moyenne glissante de 1500

en fait je pense qu il doit etre possible de le faire en divisant le calcul global en 4

si j ai 10000données et que je dois faire des moyennes de plage 1500, faire sur la premiere feuille de l observation 1500 à 3000), puis sur une seconde de 3001 à 4500, ect pour alleger la feuille excel

ou alors puisque j'ai deux etapes de calcul limiter de 1500 à 2500 puis 2501 à 3500 ect ....

pensez vous que cela soit possible en modifiant la formule de base

finalement comment faudrait il faire pour que si je divise mes calculs commencer le premier calcul non pas à l'observation 1 mais 2000 par exmple

Bonjour,

Avant de commencer par réfléchir sur vos dernières propositions de solutions pour alléger le nombre de calculs, je voudrais trouver une solution un peu plus rapide tout en conservant l'ensemble des données.

Il faut tout de même réaliser que sur un tableau de 10000 lignes et 10000 colonnes, il faut faire le calcul de moyennes glissantes sur 1500 valeurs par colonnes

soit 10000 *1500 = 15 000 000 formules, cela mérite bien d'avoir un peu de patience pour obtenir un résultat dans des temps corrects.

j'ai légèrement modifier la macro pour qu'elle fige les valeurs de chaque colonne au fur et à mesure de l'avancement des calculs, j'arrive en un temps de 330 secondes (~5mn, le temps de lancer la macro et d'aller boire un café) pensez-vous que cela puisse vous convenir?

Sub Moyenne() ' durée 330 secondes
    Dim i As Long, NbLig As Long, DerCol As Long
    Application.ScreenUpdating = False
    Taille = InputBox("Saisissez la taille de la plage des moyennes", "Moyennes glissantes", 100)
    Deb = Timer
    NbLig = Range("A" & Rows.Count).End(xlUp).Row
    DerCol = Range("A1").End(xlToRight).Column
    If DerCol > 2 Then Range(Cells(1, 3), Cells(DerCol, DerCol)).ClearContents
    'Création titres de colonnes
    Range(Cells(1, "C"), Cells(1, NbLig + 1)).NumberFormat = """obs 1-"" 0"
    Range("C1").FormulaR1C1 = Taille
    Range(Cells(1, "D"), Cells(1, NbLig - Taille + 2)).FormulaR1C1 = "=RC[-1]+1"
    For i = 1 To NbLig - Taille
        Range(Cells(i + 1, i + 2), Cells(i + Taille, i + 2)).FormulaR1C1 = "=IF(ROW()>R1C+1,"""",RC2-AVERAGE(INDIRECT(""B"" &COLUMN()-1 & "":B""&R1C+1)))"
        Range(Cells(i + 1, i + 2), Cells(i + Taille, i + 2)).Value = Range(Cells(i + 1, i + 2), Cells(i + Taille, i + 2)).Value
    Next i
    MsgBox "Temps d'exécution:=" & Timer - Deb & " Sec"
End Sub

Cdlt

bonjour

j ai change la macro , ca fonctinone pour une plage moyenne de 100 moins ou plus mais si je mets 500 1000 ou 1500

cela écrit erreur d'execution 1004 et débogage me renvoie sur cette ligne de la macro :

Range(Cells(1, "D"), Cells(1, NbLig - Taille + 2)).FormulaR1C1 = "=RC[-1]+1"

cdt

AD