Vba - Récupérer une moyenne de plusieurs plages Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Avatar du membre
Machin
Membre dévoué
Membre dévoué
Messages : 520
Inscrit le : 17 septembre 2015
Version d'Excel : 2007fr

Message par Machin » 9 novembre 2015, 20:43

Bonjour...A mon tour de demander de l'aide car je n'y vois pas bien clair.

J'ai un fichier dont je vais importer environ 3 fois par semaine, une date en entête et en dessous environ 2600 valeurs.
L'import est déjà...Macroté .Dans la feuille data, la colonne C est déjà occupée donc le prochain import se fera en colonne D etc etc

Ce qui m'interesse dans les données qui seront importées, c'est de récupérer les moyennes de plusieurs plage et de les reporter dans la feuille suivi sauf que ses plages, elles n'ont pas la même longueur une fois sur deux. D'ailleurs dans la feuille data les unités de temps sont des secondes et dans la feuille suivi, des minutes.

Donc je sais déjà de quelle ligne à quelle ligne vont ses plages (je les ai récupéré par formule pour les avoirs sous le coude) mais je ne sais pas comment optimier une boucle pour calculer la moyenne dans ses plages...

Rendez-vous dans la feuille suivi ou cela devrait être plus parlant mais je reste bien sur disponible pour plus de renseignement.



Globalement j'importe des données à la seconde dans la feuille data et je veux les condenser par leur moyenne dans la feuille suivi.
Je n'importe que trois colonnes dans la semaine et les données importées seront toujours dans la dernière colonne + 1...

Je ne sais pas si j'ai été clair. J'ai quelque compétence en maco mais quand c'est trop compliqué, mes neurones ne suivent plus.


Merci à ceux qui me donneront un coup de main


En faite, je viens de faire un calcul rapide, une plage fait 180 ligne, l'autre 120, celle d'après 180, l'autre 120...Hormis la premiere qui en fait 300..(si ça peut aider...)

et si ça peut aider à comprendre, ici une photo de la base de départ, des fractions de temps avec des paramètres à surveiller
Image
Cardiofr.xlsm
(93.75 Kio) Téléchargé 28 fois
Je suis direct mais juste. Et inversement.
Avatar du membre
Banzai64
Fanatique d'Excel
Fanatique d'Excel
Messages : 16'726
Appréciations reçues : 4
Inscrit le : 21 novembre 2010
Version d'Excel : 2003 FR (learning 2010 - 2013)

Message par Banzai64 » 9 novembre 2015, 21:07

Bonsoir

Je pose quand même la question
Tu veux exclusivement une macro ?

Sinon une formule en B3 et à tirer vers le bas
=MOYENNE(DECALER(INDIRECT("Data!A"&F2);;;1+F3-F2))
Image
Avatar du membre
Machin
Membre dévoué
Membre dévoué
Messages : 520
Inscrit le : 17 septembre 2015
Version d'Excel : 2007fr

Message par Machin » 9 novembre 2015, 21:21

Bonjour Banzaï, malheureusement pour vous oui. D'ailleurs j'espère que cette histoire de ligne n'induira personne en erreur, je les ai notées juste pour m'aider à concevoir ma macro mais après elle disparaîtront de la feuille.

Pour le moment j'en suis là :
Feuil2.Cells(3, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells([surligner]2[/surligner], dcf3), Feuil3.Cells([surligner]302[/surligner], dcf3))
Pour finaliser il faudrait que je copie une vingtaine de fois la ligne et changer les n° des lignes mais avec l'index +/- fixe je suis sur qu'il y a un vbaïste qui saura trouver le bon filon
Je suis direct mais juste. Et inversement.
Avatar du membre
Machin
Membre dévoué
Membre dévoué
Messages : 520
Inscrit le : 17 septembre 2015
Version d'Excel : 2007fr

Message par Machin » 9 novembre 2015, 22:06

Vu que la macro c'est pour hier, j'ai déjà fait ceci mais si quelqu'un sait comment l'optimiser je suis preneur
Sub import()
dcf3 = Feuil3.Cells(1, Columns.Count).End(xlToLeft).Column
Feuil2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = Feuil3.Cells(1, dcf3)
dcf2 = Feuil2.Cells(1, Columns.Count).End(xlToLeft).Column

Feuil2.Cells(3, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(2, dcf3), Feuil3.Cells(302, dcf3))
Feuil2.Cells(4, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(302, dcf3), Feuil3.Cells(422, dcf3))
Feuil2.Cells(5, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(422, dcf3), Feuil3.Cells(602, dcf3))
Feuil2.Cells(6, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(602, dcf3), Feuil3.Cells(722, dcf3))
Feuil2.Cells(7, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(722, dcf3), Feuil3.Cells(902, dcf3))
Feuil2.Cells(8, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(902, dcf3), Feuil3.Cells(1022, dcf3))
Feuil2.Cells(9, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(1022, dcf3), Feuil3.Cells(1201, dcf3))
Feuil2.Cells(10, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(1201, dcf3), Feuil3.Cells(1321, dcf3))
Feuil2.Cells(11, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(1321, dcf3), Feuil3.Cells(1501, dcf3))
Feuil2.Cells(12, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(1501, dcf3), Feuil3.Cells(1622, dcf3))
Feuil2.Cells(13, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(1622, dcf3), Feuil3.Cells(1802, dcf3))
Feuil2.Cells(14, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(1802, dcf3), Feuil3.Cells(1922, dcf3))
Feuil2.Cells(15, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(1922, dcf3), Feuil3.Cells(2102, dcf3))
Feuil2.Cells(16, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(2102, dcf3), Feuil3.Cells(2221, dcf3))
Feuil2.Cells(17, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(2221, dcf3), Feuil3.Cells(2402, dcf3))
Feuil2.Cells(18, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(2402, dcf3), Feuil3.Cells(2521, dcf3))
Feuil2.Cells(19, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(2521, dcf3), Feuil3.Cells(2701, dcf3))
Feuil2.Range(Columns(3), Columns(dcf2)).NumberFormat = "0"
Feuil2.Rows("1:1").NumberFormat = "m/d/yyyy"
End Sub
Je suis direct mais juste. Et inversement.
Avatar du membre
Klin89
Membre dévoué
Membre dévoué
Messages : 596
Appréciations reçues : 21
Inscrit le : 28 mai 2011
Version d'Excel : 2003 FR

Message par Klin89 » 9 novembre 2015, 22:38

Bonsoir Machin, le forum :)

Vite fait :
Option Explicit
Sub moyenne()
Dim a, i As Long, w
    a = Sheets("data").[a1].CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 2 To UBound(a, 1)
            'Si la clé n'existe pas
            If Not .exists(a(i, 2)) Then
                'on la crée en y associant 1 tableau de 3 éléments
                'on pourrait créer 1 tableau de 4 éléments
                'en y intégrant la clé
                .Item(a(i, 2)) = VBA.Array(1, a(i, 3), a(i, 3))
            Else
                'Rappel des éléments associés à la clé
                w = .Item(a(i, 2))
                w(0) = w(0) + 1    'le nombre d'occurences
                w(1) = w(1) + a(i, 3)    'le total
                w(2) = w(1) / w(0)    'la moyenne
                'mise en mémoire de la clé et des éléménts associés
                'soit le tableau W (3 éléments)
                .Item(a(i, 2)) = w
            End If
        Next
        'affectation de la colonne des moyennes
        a = Application.Index(.items, 0, 3)
    End With
    'Restitution
    Sheets("Suivi").[B2].Resize(UBound(a, 1), 1).Value = a
End Sub
On pourrait restituer 4 colonnes comme je le sous entendais.
Je vais me coucher ::D

A+ klin89
Modifié en dernier par Klin89 le 10 novembre 2015, 09:14, modifié 1 fois.
Avatar du membre
Banzai64
Fanatique d'Excel
Fanatique d'Excel
Messages : 16'726
Appréciations reçues : 4
Inscrit le : 21 novembre 2010
Version d'Excel : 2003 FR (learning 2010 - 2013)

Message par Banzai64 » 9 novembre 2015, 22:45

Bonsoir

Bonsoir Klin89

A vérifier
Sub import()
Dim Lignes, J As Long

  Lignes = Array(2, 302, 422, 602, 722, 902, 1022, 1201, 1321, 1501, 1622, 1802, 1922, 2102, 2221, 2402, 2521, 2701)

  dcf3 = Feuil3.Cells(1, Columns.Count).End(xlToLeft).Column
  Feuil2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = Feuil3.Cells(1, dcf3)
  dcf2 = Feuil2.Cells(1, Columns.Count).End(xlToLeft).Column
  With Feuil2
    For J = 3 To .Range("A" & Rows.Count).End(xlUp).Row
      .Cells(J, dcf2) = Application.WorksheetFunction.Average(Feuil3.Cells(Lignes(J - 3), dcf3), Feuil3.Cells(Lignes(J - 2), dcf3))
    Next J

    .Range(Columns(3), Columns(dcf2)).NumberFormat = "0"
    .Rows("1:1").NumberFormat = "m/d/yyyy"
  End With
End Sub
Image
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'201
Appréciations reçues : 445
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 10 novembre 2015, 00:56

Bonsoir,

Une proposition à tester :
Sub Moyennes()
    Dim tm(), m%, i%, j%, k%
    With Worksheets("Suivi")
        m = .Cells(.Rows.Count, 1).End(xlUp).Row - 2
        ReDim tm(m, 3)
        tm(0, 0) = 1
        For i = 1 To m
            tm(i, 0) = .Cells(i + 2, 1) * 86400 + 1
            tm(i, 1) = tm(i, 0) - tm(i - 1, 0)
        Next i
    End With
    With Worksheets("Data")
        k = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For i = 1 To m
            For j = tm(i - 1, 0) + 1 To tm(i, 0)
                tm(i, 2) = tm(i, 2) + .Cells(j, k).Value
            Next j
            tm(i, 3) = tm(i, 2) / tm(i, 1)
        Next i
    End With
    With Worksheets("Suivi")
        j = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
        .Cells(1, j).Value = Worksheets("Data").Cells(1, k).Value
        For i = 1 To m
            .Cells(i + 2, j).Value = tm(i, 3)
        Next i
    End With
End Sub
Je crois que j'ai dû m'écarter très légèrement de ton découpage de lignes (à 1 près max.), ayant pensé (peut-être à tort) qu'il y avait une correspondance entre les moyennes par tranches et les valeurs de la colonnes cible...
Si c'était le cas, et si ce découpage est amené à changer, il pourrait également être défini par macro.

Cordialement
Machin_Cardiofr.xlsm
(98.07 Kio) Téléchargé 23 fois
Avatar du membre
Machin
Membre dévoué
Membre dévoué
Messages : 520
Inscrit le : 17 septembre 2015
Version d'Excel : 2007fr

Message par Machin » 10 novembre 2015, 08:47

Bonjour à toutes et à tous. Au petit matin j'ai testé toutes les macros et après vérification manuellement, les résultats obtenus par MFerrand ont l'air d'être plus pertinents que les miens (qui par défaut sont les mêmes que Banzaï car j'ai bien vu que vous vous êtes inspiré de mes n° de ligne donc par définition, l'erreur est pour moi).
Je crois que j'ai dû m'écarter très légèrement de ton découpage de lignes (à 1 près max.)
Mon découpage est/aurait du/doit être :
Première fraction de 5 minute
Deuxième fraction 2 minute
Troisième fraction 3 minute
Quatrième fraction 2 minute
Cinquième fraction 3 minutes
etc
(le tout sur une plage totale de 45 minutes...)

Mon découpage je l'ai fait avec équiv() pour trouver les N° de ligne mais si vous vous l'avez fait à chaque changement de cible, alors c'est plus pertinent.

Je joins une photo du découpage des plages qui indique les plages en terme de temps mais également avec le regroupement en terme de ligne.

Là j'avoue que je suis dans le flou sur qui à la meilleur découpage mais par défaut je fonctionne avec la macro de MFerrand.
Sans titre.jpg
Sans titre.jpg (73.06 Kio) Vu 377 fois
Sans titre.jpg
Sans titre.jpg (83.7 Kio) Vu 385 fois
Je suis direct mais juste. Et inversement.
Avatar du membre
Machin
Membre dévoué
Membre dévoué
Messages : 520
Inscrit le : 17 septembre 2015
Version d'Excel : 2007fr

Message par Machin » 10 novembre 2015, 09:44

Apparement je me suis trompé dans le découpage des lignes dès le début
Lignes = Array(2, 302, 422, 602, 722, 902, 1022, [surligner]1201[/surligner], [surligner]1321[/surligner], [surligner]1501[/surligner], 1622, 1802, 1922, 2102, [surligner]2221[/surligner], 2402, [surligner]2521[/surligner], 2701)
Vu que les largeurs de plages sont linéaires, il manque forcément une ou deux lignes qui très certainement justifie l'écart entre 114 et 111. Donc du coup, j'essaierai la macro de banzai avec les valeurs réctifiées pour le fun car je présume non sans crainte qu'on retombera sur les mêmes résultats que MFerrand.

(Pardon de vous avoir noyé d'information...)
Je suis direct mais juste. Et inversement.
Avatar du membre
Machin
Membre dévoué
Membre dévoué
Messages : 520
Inscrit le : 17 septembre 2015
Version d'Excel : 2007fr

Message par Machin » 10 novembre 2015, 11:57

Bon après comparaison et rectification des coordonées de lignes, une différence subsiste dans les tests que j'ai fait. Donc pour le moment je vais faire une pose pour reposer mon neurone
Je suis direct mais juste. Et inversement.
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message