Somme glissante sur une colonne et condition

Bonjour,

Je poste un deuxième sujet dans la même journée car c'est chaud pour moi

Je cherche à faire une macro (ou peut-être qu'il existe une fonction excel??) pour calculer le nombre de fois que la somme de trois cellules glissantes sur ma colonne est égale à une certaine valeur. Et il faudrait que je puisse changer le nombre de cellules prises en compte dans la somme de même que la valeur seuil.

Je mets un fichier exemple pour que ça soit plus clair.

Un grand merci à la personne qui pourra m'aider, même une indication car je pourrai peut-être faire la macro avec quelques indices!

Salut,

Je te propose une solution par macro. A lancer par le bouton en place après avoir modifié tes données en B1 et B2.

Le contrôle en gris ne vaut que pour le premier exemple donné. Il m'a aidé pour vérifier ma macro. mais je l'ai laissé en place.

Amicalement.

104somme-v1.xlsm (24.33 Ko)

Bonjour Yvouille et merci pour votre réponse!

Je la vois au moment ou je dois partir donc je regarde ça dès mon retour!

Merci encore!

Salut Maroon,

Salut Yvouille,

Sans doute fort semblable à celle d'Yvouille

La macro démarre sur un changement de valeur en [B1:B2] et prend en compte automatiquement le nombre de colonnes de données en feuille 'COEF' pour délivrer le résultat en feuille 'SOMME'.

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim tTab, tCoef, iRow%, dblTot#, dblTemp#
'
If Not Intersect(Target, Range("B1:B2")) Is Nothing Then
    iRow = CInt(Range("B1").Value)
    dblTot = CDbl(Range("B2").Value)
    Range("A4").CurrentRegion.Delete shift:=xlUp
    With Worksheets("COEFF")
        iCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        tTab = .Range("A2").Resize(.Range("A" & Rows.Count).End(xlUp).Row - 1, iCol).Value
        tCoef = .Range("A1").Resize(2, iCol).Value
        For x = 1 To iCol
            tCoef(2, x) = 0
        Next
    End With
    For x = 1 To UBound(tTab, 2)
        For y = 1 To UBound(tTab, 1) - (iRow - 1)
            dblTemp = 0
            For Z = 0 To iRow - 1
                dblTemp = dblTemp + CDbl(tTab(y + Z, x))
            Next
            If dblTemp >= dblTot Then tCoef(2, x) = CInt(tCoef(2, x)) + 1
        Next
    Next
    Range("A4").Resize(iCol, 2).Value = WorksheetFunction.Transpose(tCoef)
    Range("A4").CurrentRegion.Borders.LineStyle = xlContinuous
    Range("A4").Resize(iCol, 1).Interior.ColorIndex = 15
End If
'
End Sub

A+

85maroon.xlsm (19.15 Ko)

Rebonjour Yvouille et curulis57 !

Mille merci pour votre aide.

Yvouille j'arrive à faire fonctionner ta macro et ça correspond exactement comme je vouais! Ca va beaucoup m'aider

Par contre curulis57 je n'arrive pas à activer la macro! Ca fait un moment que je n'ai pas travailler avec des macro donc c'est surement pour ça. Quand j'ouvre une nouvelle macro et que je copie le code ça me dit "end sub" attendu... Je n'ai pas l'habitude avec les événements Worksheet. Si ce n'est pas trop demander est-il possible d'avoir quelques commentaires d'explication dans le code??

Merci à toi pour ton aide!

Et Yvouille encore merci

Resalut Curulis57,

C'est bon ça fonctionne au top! En fait je n'avais jamais utiliser les événements worksheet selectionchange! Du coup je suis toujours preneur de quelques commentaires dans le code pour m'aider à comprendre!

Un grand merci à vous deux pour votre aide précieuse!

Salut Maroon,

Salut Yvouille,

quelques commentaires sur le code, comme demandé.

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim tTab, tCoef, iRow%, dblTot#, dblTemp#
'
If Not Intersect(Target, Range("B1:B2")) Is Nothing Then            'si une donnée change en [B1:B2]
    iRow = CInt(Range("B1").Value)                                  'nbre de lignes "glissées"
    dblTot = CDbl(Range("B2").Value)                                'seuil
    Range("A4").CurrentRegion.Delete shift:=xlUp                    'effacement calcul précédent
    With Worksheets("COEFF")                                        'prise d'info dans 'COEF'
        iCol = .Cells(1, Columns.Count).End(xlToLeft).Column        'nbre de colonnes de données
        tTab = .Range("A2").Resize(.Range("A" & Rows.Count).End(xlUp).Row - 1, iCol).Value      'données mises en tableau
        tCoef = .Range("A1").Resize(2, iCol).Value                  'mise en tableau des en-têtes tableau + 1 ligne pour les sommes recherchées...
        For x = 1 To iCol                                           '...lesquelles sont mises à 0 avant le calcul
            tCoef(2, x) = 0
        Next
    End With
    For x = 1 To UBound(tTab, 2)                                    'on parcourt les colonnes de tTab
        For y = 1 To UBound(tTab, 1) - (iRow - 1)                   'on parcourt les lignes de tTab jusqu'à X lignes avant la fin = iRow
            dblTemp = 0                                             'valeur-tampon mise à 0
            For Z = 0 To iRow - 1                                   'on additionne dans dblTemp le nbre de lignes "glissées"
                dblTemp = dblTemp + CDbl(tTab(y + Z, x))
            Next
            If dblTemp >= dblTot Then tCoef(2, x) = CInt(tCoef(2, x)) + 1       'si dblTemp >= seuil, tCOEF de la colonne + 1
        Next
    Next
    Range("A4").Resize(iCol, 2).Value = WorksheetFunction.Transpose(tCoef)      'le tableau tCOEF étant pris horizontalement dans 'COEF', on transpose le résultat verticalement
    Range("A4").CurrentRegion.Borders.LineStyle = xlContinuous                  'encadrement
    Range("A4").Resize(iCol, 1).Interior.ColorIndex = 15                        'coloration en-têtes
End If
'
End Sub

En parcourant les sujets du forum, je suis tombé sur une demande similaire où tu parlais de 18.000 lignes de données à calculer...

Si c'est pour ce calcul, nul doute que ça va flasher!

A+

Salut Curulis57,

Merci pour les commentaires je comprends un peu mieux certaines parties du code

Oui effectivement c'est un peu long pour le calcul mais je n'ai pas à le faire souvent donc ça me pose pas de problème!

Merci encore pour toute l'aide apportée!

Rebonjour Yvouille et Curulis57,

Oui c'est encore moi

Je vais peut-être abuser de votre générosité mais je tente quand même le coup parce que je suis un peu dans le rush.

En travaillant sur mes données je me suis rendu compte qu'en plus d'avoir besoin du nombre de fois que la somme glissante répond à la condition il faut aussi que je connaisse le nombre de cellules qui répondent à cette même condition.

Par exemple si la condition est respectée pour A2:A4 , A3:A5 et A8:A10 alors il faudrait que j'obtienne 3 comme avec le code que vous m'avez envoyé mais il faut aussi que j'ai une macro qui me donne 8 comme résultat c'est-à-dire A2,A3,A4,A5,A8,A9,A10.

J'essaye de voir comment adapter les codes que vous m'avez envoyé mais pour le moment j'y arrive pas. Ca me semble "un peu" plus compliquer que ma première demande, surtout le fait qu'on puisse avoir la(les) mêmes cellules dans deux plages différentes et qu'il ne faut pas les compter deux fois.

Faut-il utiliser l'objet "dictionnary" pour ça ? C'est la solution que je suis en train d'essayer pour le moment.

Si cette requête est trop compliquée à mettre en place dites le moi et je vais essayer d'obtenir les résultats autrement... même si je sais pas trop comment pour l'instant

Salut Maroon,

je te laisse le soin de vérifier les comptes de cellules mais je pense que c'est bon!

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim tTab, tCoef, iRow%, iIdx%, dblTot#, dblTemp#
'
If Not Intersect(Target, Range("B1:B2")) Is Nothing Then
    iRow = CInt(Range("B1").Value)
    dblTot = CDbl(Range("B2").Value)
    Range("A4").CurrentRegion.Delete shift:=xlUp
    With Worksheets("COEFF")
        iCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        tTab = .Range("A2").Resize(.Range("A" & Rows.Count).End(xlUp).Row - 1, iCol).Value
        Range("A5").Resize(iCol, 1).Value = WorksheetFunction.Transpose(.Range("A1").Resize(1, iCol))
        tCoef = Range("A5").Resize(iCol, 3).Value
    End With
    For x = 1 To UBound(tTab, 2)
        iIdx = 0
        For y = 1 To UBound(tTab, 1) - (iRow - 1)
            dblTemp = 0
            For Z = 0 To iRow - 1
                dblTemp = dblTemp + CDbl(tTab(y + Z, x))
            Next
            If dblTemp >= dblTot Then
                tCoef(x, 2) = CInt(tCoef(x, 2)) + 1
                tCoef(x, 3) = CInt(tCoef(x, 3)) + IIf(iIdx < y, iRow, (y + iRow - 1) - iIdx)
                iIdx = y + iRow - 1
            End If
        Next
    Next
    Range("A5").Resize(iCol, 3).Value = tCoef
    Range("A4").Resize(1, 3).Value = Array("", "Blocs", "Cel")
    Range("A4").CurrentRegion.Borders.LineStyle = xlContinuous
    Range("A5").Resize(iCol, 1).Interior.ColorIndex = 15
    Range("B4").Resize(1, 2).Interior.ColorIndex = 45
End If
'
End Sub

A+

83maroon.xlsm (23.97 Ko)

Bonjour Curulis57!

Merci pour ta réponse rapide, ça marche comme sur des roulettes!!

Un peu frustré parce que je ne comprends pas encore tout le code...

Juste une ultime demande:

Est-ce que c'est possible de séparer les deux codes avec quelques commentaire sur le deuxième? et aussi de le transformer en macro car je ne suis pas à l'aise avec la fonction Private Sub... J'aurais préféré un code que j'active manuellement et ça me permettra de l'insérer dans d'autres macro plus facilement.

Si c'est ok ça me permettra d'étudier tous ça jusqu'à ce que je comprenne!

Salut,

J'aurais préféré un code que j'active manuellement et ça me permettra de l'insérer dans d'autres macro plus facilement.

Tu pourrais utiliser le code que je t'ai fournis, non ?

Amicalement.

Salut Maroon,

le 2e code ?

Ben, le voilà !

tCoef(x, 3) = CInt(tCoef(x, 3)) + IIf(iIdx < y, iRow, (y + iRow - 1) - iIdx)
iIdx = y + iRow - 1

Pour ce que tu demandes, il faut refaire la 1ère quasi au complet 2 X, avec ou sans ces deux lignes.

ça me permettra de l'insérer dans d'autres macro

Que veux-tu dire ?

Explique ton idée, il y a sans doute moyen de faire simple et, bon Dieu, pourquoi activer manuellement quand tout peut être automatique?!

A+

@Yvouille, oui effectivement d'ailleurs pour le moment j'avais des choses urgentes à faire et c'est ton code que j'ai utilisé. Par contre j'aimerais comprendre celui que Curulis57 a fait pour moi car il me permet d'obtenir le deuxième résultat dont j'ai besoin et puis aussi parce que j'aimerais m'améliorer un peu dans le codage en VBA (même si actuellement c'est pas trop le moment ) et comme je comprends à peine 20% de son code...

@Curulis57, ok je vois un peu mieux les différentes parties. Pour le passage en manuel c'est parce que ça me permettrait de changer les deux valeurs de références en même temps avant de lancer le calcul.

Le deuxième problème que je rencontre c'est avec la ligne

iCol = .Cells(1, Columns.Count).End(xlToLeft).Column

: comment faire si mes colonnes de données commence en A2 au lieu de B5: le "1" est remplacé par un "5" mais je ne vois pas par quoi remplacer "Columns.count"...

Ensuite je suis entrain de voir comment adapter le code car pour le moment il efface toute la feuille sur laquelle je veux copier les résultats alors qu'il y a des lignes (des entêtes) que je souhaite conserver. J'ai mis un extrait de mon fichier (car il est vraiment lourd! et les données sont "confidentielles" ) pour voir la disposition. Les données sont dans la feuille "HIST-1" et commence en "B5" et le résultats doivent être copier dans la colonne grise de la feuille "INDICATEUR" sans effacer les lignes 1 à 4.

Si tu peux m'aider...? en attendant je fait différentes tentatives de modification de ton code.

J'ai plusieurs feuilles "HIST-" dans le fichier d'origine c'est pourquoi j'ai copié le code d'Yvouille plusieurs fois.

Salut Maroon,

enfin, un fichier réaliste!

Je regarde ça pendant la nuit : si besoin, j'envoie un message!

A+

LOL oui j'aurais dû l'envoyer dès le départ!

Ok super merci encore pour ton aide!!

Salut Maroon,

Salut Yvouille,

premier jet de la deuxième phase.

J'ai déplacé la commande de la macro dans un bouton rouge dans 'INDICATEUR'.

Questions:

  • toutes les feuilles, autres que 'INDICATEUR', sont-elles à traiter en une fois dès la macro lancée ?
  • ces autres feuilles ont-elles strictement la même structure de données que 'HIST-1' ?
  • y a-t-il plus ou moins de villes dans ces feuilles et faut-il reproduire ces en-têtes en avançant dans les feuilles comme ici, la colonne [A] ?
  • je suppose que tu as en tête d'autres libellés que "Blocs" et "Cellules"... ? Les faut-il d'ailleurs ?
  • euh , y'avait quoi dans 'INDICATEUR' colonne ['B] . Pââti, au revoir!

Bref, mets-toi à la place de l'aveugle que je suis et qui ne connaît pas ton monde !

Un fichier-exemple de la disposition des résultats des différentes feuilles en 'INDICATEUR' serait vraiment chouette!

Private Sub cmdGO_Click()
'
Dim tTab, tCoef, iStep%, iRow%, iIdx%, dblTot#, dblTemp#
'
iStep = CInt(Range("C4").Value)
dblTot = CDbl(Range("C3").Value)
With Worksheets("HIST-1")
    iCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    iRow = Range("A" & Rows.Count).End(xlUp).Row
    tTab = .Range("B5").Resize(.Range("A" & Rows.Count).End(xlUp).Row - 1, iCol - 1).Value
    If iRow > 4 Then Range("A5").Resize(iRow, 4).Value = ""
    Range("A6").Resize(iCol - 1, 1).Value = WorksheetFunction.Transpose(.Range("B1").Resize(1, iCol - 1))
    tCoef = Range("A6").Resize(iCol - 1, 4).Value
End With
For x = 1 To UBound(tTab, 2)
    iIdx = 0
    For y = 1 To UBound(tTab, 1) - (iStep - 1)
        dblTemp = 0
        For Z = 0 To iStep - 1
            dblTemp = dblTemp + CDbl(tTab(y + Z, x))
        Next
        If dblTemp >= dblTot Then
            tCoef(x, 3) = CInt(tCoef(x, 3)) + 1
            tCoef(x, 4) = CInt(tCoef(x, 4)) + IIf(iIdx < y, iStep, (y + iStep - 1) - iIdx)
            iIdx = y + iStep - 1
        End If
    Next
Next
Range("A6").Resize(iCol - 1, 4).Value = tCoef
Range("C5").Resize(1, 2).Value = Array("Blocs", "Cellules")
Range("A5").Resize(iCol - 1, 4).Borders.LineStyle = xlContinuous
Range("C5").Resize(1, 2).Interior.ColorIndex = 45
'
End Sub

A+

14extrait-donnees.xlsm (744.31 Ko)

Salut Curulis57,

Franchement super!! C'est pas pour faire de la flatterie mais je suis impressionné et en plus ça me rend en gros service

Pour répondre à tes questions:

- toutes les feuilles, autres que 'INDICATEUR', sont-elles à traiter en une fois dès la macro lancée ?

Oui ça serait bien que toutes les feuilles soient traitées en même temps, par contre dans ce fichier je ne mets que les résultats du nombre de cellules et pas celui du nombre de blocs. Du coup j'ai retiré la ligne

tCoef(x, 3) = CInt(tCoef(x, 3)) + 1

- ces autres feuilles ont-elles strictement la même structure de données que 'HIST-1' ?

Oui elles ont exactement la même structure c'est d'ailleurs pour ça que j'ai préféré t'envoyer un seul onglet parce que le fichier est lourd et je n'ai d'ailleurs mis que 4300 lignes sur les 9000 à 14000 lignes de chacune de 15 feuilles du fichier de base !

- y a-t-il plus ou moins de villes dans ces feuilles et faut-il reproduire ces en-têtes en avançant dans les feuilles comme ici, la colonne [A] ?

Il y a le même nombre de ville par contre les en-têtes Périodes (HIST-1 à HIST-15), Durée, Seuil et N sont déjà renseignées; et la colonne [A] n'est présente qu'une seule fois.

- je suppose que tu as en tête d'autres libellés que "Blocs" et "Cellules"... ? Les faut-il d'ailleurs ?

Non en faite il ne les faut pas c'était pour l'exemple j'ai réussi à les retirer et aussi à copier les résultats "cellules" en colonne [C] à la place de "blocs" puis à faire remonter la plage des villes d'une ligne juste en dessous des en-têtes, par contre je n'arrive pas à faire remonter les résultats "cellules" d'une ligne du coup il y a un décalage.

- euh , y'avait quoi dans 'INDICATEUR' colonne ['B] . Pââti, au revoir!

LOL oui il y avait des "indices" mais en faite j'en ai pas vraiment besoin ici donc je les ai retirés.

J'avais l'intention de copier ton code pour chaque feuille comme j'ai fait avec le code de Yvouille mais j'ai du mal à comprendre comment les données sont dimensionnées et positionnées sur le tableau c'est d'ailleurs pour ça que je n'ai pas encore réussi à faire remonter les résultats d'une ligne... Je continue à essayer mais si tu as prévu de me proposer quelque chose pour l'ensemble des feuilles je suis preneur Je mais le fichier avec toutes les feuilles en PJ quand même. J'ai laissé 1000 lignes par feuille.

Je voulais savoir si c'est possible d'avoir quelques commentaires comme la dernière fois? Surtout que j'ai du mal à comprendre comment les données sont "ciblées" et les résultats copiés dans une zone précise... Et aussi est-ce que les deux valeur seuils peuvent être prises en compte dans la colonne concernée, c-à-d B3:B4, C3:C4, D3:D4 etc...? mais ça c'est un détail si c'est pas possible.

Bref, mets-toi à la place de l'aveugle que je suis et qui ne connaît pas ton monde !

Le fichier était trop volumineux donc cette fois-ci j'ai seulement gardé 50 lignes par feuille mais c'est la même structure que le fichier de base.

Salut Maroon,

je te réponds rapidement car le temps se resserre.

Ne passe pas ton temps à reproduire le code dans toutes les feuilles : inutile!!

J'intégrerai l'ensemble des feuilles dans une seule et même boucle commandée par le bouton.

Je n'ai pas encore regardé le fichier : bien illustré comme demandé?

Où as-tu besoin des valeurs-blocs?

Style télégramme - Stop

A+

Rechercher des sujets similaires à "somme glissante colonne condition"