[Résolu] Optimisation de synthèse de données sur plusieurs pages

[Compress]

Bonjour messieurs les forumeurs, au plaisir de vous retrouver aujourd'hui pour un nouveau problème :

Je vous transmet un fichier contenant 3 pages par soucis de confidentialité j'ai recréé des données types.

Mon objectif :
- à partir des feuilles du mois M-1 et du mois M (2 feuilles séparées), je voudrai consolider une feuille synthèse dans laquelle je ferai le bilan des données du mois M (obtenue par différence entre M et M-1 car le mois M représente les infos cumulées)

Plus précisément ici, nous avons pour simplifier une colonne code pour l'affectation qui peut varier d'un mois à l'autre (ex. : nouvelle affectation). Par contre on conserve chaque mois les données des anciennes affectations.
Puis si l'affectation ne bouge pas les heures se cumulent d'un mois sur l'autre.

J'aurai donc besoin qui me remonte pour un identifiant donné sa dernière affectation provenant du mois M et son bilan d'heures pour le mois M qui consiste en la différence du mois M avec le mois M-1.

Le problème est que j'ai environ 6000 lignes par feuilles. J'ai tenté plusieurs macros.

Ma première idée était de faire une boucle .find sur la feuille M puis cumulé les heures trouvées et stocké le code d'affectation de la dernière ligne qui est toujours l'officiel pour un identifiant donné. Puis de rechercher cet identifiant dans M-1 cumuler les lignes trouvées et faire la différence mais en temps je tourne à plus d'une heure de calcul. Puis les copier dans l'onglet synthèse.

Ma deuxième idée, faire deux TCD pour chaque feuille de mois afin d'avoir les heures cumulées pour chaque identifiant puis de faire des boucle .find sur les 2 TCD afin de retrouver pour chaque identifiant la différence de ses heures puis de tout réécrire dans l'onglet synthèse.

Mais là encore le temps de traitement est trop long.

Auriez-vous des idées pour réaliser ces tâches rapidement avec macro ou sans mais rapidement ? Franchement là je sèche, ça fait deux jours que je tourne mon code dans tous les sens mais je n'arrive jamais en dessous d'une heure...

Merci par avance les forumeurs.

 

[Paritec]

Re : Optimisation de synthèse de données sur plusieurs pages

Bonsoir Compress le forum
peux tu nous mettre un peu d'explication dans la feuille résultat STP car de 9 lignes en feuille 1 et 11 lignes en feuille 2 on retrouve 7 lignes en feuille 3 ????
on regroupe par quoi??? Col A Col B ??? je pense Col B mais je retourne sur le fichier, je pense avoir compris ton besoin on regroupe par identifiant et on soustrait? M-1 de M en fait en tapant la demande je pense comprendre
confirmes moi stp
a+
papou

 

[Compress]

Re : Optimisation de synthèse de données sur plusieurs pages

Merci Paritec pour l'intérêt !

Je joins de nouveau le fichier avec une explication pour chaque ligne.

Cordialement.

 

[Paritec]

Re : Optimisation de synthèse de données sur plusieurs pages

Bonsoir Compress le forum
ton fichier en retour, tu ouvres tu cliques sur le bouton traiter.
Maintenant a toi de copier coller tes listes de 6000 lignes et de tester, pas sur d'avoir compris pour ta colonne A pour la B la C c'est sur mais la A j'ai pris la dernière ref ?????
a+
papou

 

[Compress]

Re : Optimisation de synthèse de données sur plusieurs pages

Ah le scripting.dictionnary, vu le résultat je suis sûr que ça marchera sur mon fichier, j'analyse ton code et je l'applique à mon fichier qui a quelques colonnes en plus mais le fonctionnement général est compris.

Ouah quelle rapidité d'exécution cet objet faut que je m'y mette. J'étais sur de l'array mais beaucoup trop longs.

Merci énormément je reviens vers toi demain car je pars en déplacement dans une heure. Je ferai ça dans le TGV. Merci pour le code au passage qui me donne un bon exemple concret d'utilisation de cet objet.

Bonne fin de journée à toi et aux autres forumeurs !

 

[Paritec]

Re : Optimisation de synthèse de données sur plusieurs pages

Re Compress le forum
si tu as des colonnes en plus pourquoi ne pas avoir pondu un exemple représentatif de ton fichier??
là je Compress moins? (comprend )
a+
papou

 

[Compress]

Re : Optimisation de synthèse de données sur plusieurs pages

Bonjour Paritec, le forum, désolé de mon retard j'étais en déplacement.

Les colonnes en plus ne posent pas de problème il n'y a pas de traitement à effectuer dessus et j'ai pu adapté ton code avec plaisir vu que tout fonctionne en 24 sec !!!!!

Impressionnant j'en ai également profité pour aller sur le site de Jacques BOISGONTIER à propos des dictionnaires et en faire une petite formation.

Il me reste un seul problème.

Dans les données à traiter du fichier réel tout comme l'exemple de fichier joint (plus haut), la première colonne correspond à des codes qui ne doivent pas être considéré comme des chiffres mais comme du texte.

Dans la macro que tu m'as faite, tu peux vérifier que dans l'onglet synth la première colonne à transférer le texte en nombre. L'array a typé à sa sauce et je n'arrive pas à le forcer en texte sur le rendu.

Je te transmets mon code adapté du tien.

Option Explicit
Option Base 1

Sub TriHeure()

    Dim i&, a&, fin&, aa As Variant, bb As Variant, MonDico As Object, dd As Variant, cc As Variant, x&, t$
    Application.ScreenUpdating = 0
    t = Timer
    With Sheets("H_M-1")
        aa = .Range("A2:L" & .Range("A65536").End(xlUp).Row)
    End With
    Set MonDico = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(aa)
        If Not MonDico.Exists(aa(i, 5)) Then MonDico.Add aa(i, 5), aa(i, 5)
    Next i
    With Sheets("H_M")
        cc = .Range("A2:L" & .Range("A65536").End(xlUp).Row)
    End With
    For i = 1 To UBound(cc)
        If Not MonDico.Exists(cc(i, 5)) Then MonDico.Add cc(i, 5), cc(i, 5)
    Next i
    bb = Application.Transpose(MonDico.Items)
    ReDim dd(UBound(bb), 5)
    For i = 1 To UBound(bb)
        dd(i, 4) = bb(i, 1)
    Next i
    For i = 1 To UBound(dd)
        For a = 1 To UBound(aa)
            If dd(i, 4) = aa(a, 5) Then dd(i, 5) = dd(i, 5) - aa(a, 11): dd(i, 1) = CStr(aa(a, 1)): dd(i, 2) = aa(a, 4): dd(i, 3) = aa(a, 8)
        Next a
        For x = 1 To UBound(cc)
            If dd(i, 4) = cc(x, 5) Then dd(i, 5) = dd(i, 5) + cc(x, 11): dd(i, 1) = CStr(cc(x, 1)): dd(i, 2) = cc(x, 4): dd(i, 3) = cc(x, 8)
        Next x
    Next i
    With Sheets("Synthèse_HT")
        .Range("A2:C10000").Clear
        .Range("A2").Resize(UBound(dd), 5) = dd
    End With
    Application.ScreenUpdating = 1
    MsgBox "Traitement effectué en " & Format(Timer - t, "0.00s")
End Sub

Le problème se situe au niveau de la colonne 1 de l'array d et des colonnes 1 des array aa et cc.

Si tu as une idée ou d'autres forumeurs, n'hésitez pas.

 

[Paritec]

Re : Optimisation de synthèse de données sur plusieurs pages

bonjour Compress le forum
tu peux pas nous remettre un petit fichier avec tes commentaires dedans STP?
a+
Papou

 

[Compress]

Re : Optimisation de synthèse de données sur plusieurs pages

Dans le fichier joint j'ai surligné une ligne qui pose problème. Le calcul se fait bien niveau des heures, par contre le 099 se transforme lors de l'écriture dans l'onglet synth en 99.

D'où l'utilisation dans mon code du CStr() mais _ça ne change rien au rendu.

Merci d'avance.

 

[Paritec]

Re : Optimisation de synthèse de données sur plusieurs pages

Re Bonjour Compress le forum
bah yaka demander
a+
papou

 

[Compress]

Re : Optimisation de synthèse de données sur plusieurs pages

Merci Paritec mon idée était donc la bonne sauf que maintenant c'est la deuxième colonne des identifiants qui est convertie en nombre alors qu'elle devrait rester en texte ?

Cordialement.

 

[Paritec]

Re : Optimisation de synthèse de données sur plusieurs pages

Re Compress le forum,
si tu es sur que c'est tout on va modifier ou alors j'attends la prochaine modif???
a+
papou

 

[Paritec]

Re : Optimisation de synthèse de données sur plusieurs pages

Re compress le forum
voilà
a+
Papou

 

[Compress]

Re : Optimisation de synthèse de données sur plusieurs pages

La seule contrainte est que le code et l'identifiant doivent être traité en texte. Tout le reste est validé conforme PARFAIT !

Comme plus haut dans mon code j'avais tenté :

For i = 1 To UBound(dd)
        For a = 1 To UBound(aa)
            If dd(i, 4) = aa(a, 5) Then dd(i, 5) = dd(i, 5) - aa(a, 11): dd(i, 1) = CStr(aa(a, 1)): dd(i, 2) = aa(a, 4): dd(i, 3) = aa(a, 8)
        Next a
        For x = 1 To UBound(cc)
            If dd(i, 4) = cc(x, 5) Then dd(i, 5) = dd(i, 5) + cc(x, 11): dd(i, 1) = CStr(cc(x, 1)): dd(i, 2) = cc(x, 4): dd(i, 3) = cc(x, 8)
        Next x
    Next i

 

[Compress]

Re : Optimisation de synthèse de données sur plusieurs pages

Quel débile de pas y avoir penser par moi-même !!!

Merci Paritec, ça fonctionne parfaitement. Problème résolu !

Bonne journée.