IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

La copie des formules sur très grand tableur trop lente


Sujet :

Macros et VBA Excel

  1. #1
    Membre habitué
    Homme Profil pro
    Chargé d'affaire
    Inscrit en
    septembre 2019
    Messages
    116
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Chargé d'affaire

    Informations forums :
    Inscription : septembre 2019
    Messages : 116
    Points : 140
    Points
    140
    Par défaut La copie des formules sur très grand tableur trop lente
    Bonjour,
    j'utilise un programme en VBA pour recopier des formules toutes simples, pour après une fois terminé compter les doublons les plus sortis,
    ci-dessous une image de mon tableau et le code qui fonctionne très bien sur une petite plage, mais qui est trop long en exécution pour ce que je veux faire,

    je l'ai lancé hier soir avec un pas de 10, et arrêté ce matin,
    puis j'ai modifié le pas de 10 en 1 ce matin, et arrêté à l'instant,

    le problème est le suivant :
    une colonne de 184 756 lignes qui contiennent une formule type en A22 (pour information les lettres sont des références de colonnes),
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =A1&A2&A3&A4&A5&A6&A7&A8&A9&A10
    je copie la colonne sur la droite, une première fois en formule, puis une deuxième fois en tant que valeur pour gagner de la mémoire,
    puis je recommence de gauche à droite pour un total de 14 484 colonnes.
    y-a-t-il une autre solution pour ce fichier trop grand ?
    ou bien dois-je le scinder en plusieurs éléments ?
    merci pour votre aide.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    Sub CopieFormules()
        With Application
                .DisplayAlerts = False
                .ScreenUpdating = False
                .Interactive = False
        End With
     
        Sheets("AscDiz").Select
        Dim compteur As Long
        Dim i As Long
     
        i = 1
        For compteur = 1 To 184756
            Range("A22:A184777").Copy
            Range(Cells(22, 2), Cells(22, 2 + i)).PasteSpecial Paste:=xlPasteFormulas
            Range(Cells(22, 2), Cells(22, 2 + i)).Copy
            Range(Cells(22, 2), Cells(22, 2 + i)).PasteSpecial Paste:=xlPasteValues
            i = i + 1
        Next compteur
     
        Application.CutCopyMode = False
        Range("A1").Select
     
        With Application
            .DisplayAlerts = True
            .ScreenUpdating = True
            .Interactive = True
        End With
     
    End Sub
    Pièce jointe 503196
    Images attachées Images attachées  

  2. #2
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    janvier 2010
    Messages
    10 695
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : janvier 2010
    Messages : 10 695
    Points : 25 532
    Points
    25 532
    Billets dans le blog
    32
    Par défaut
    Bonjour,
    Il n'y a aucune raison d'utiliser une boucle pour écrire une formule sur plusieurs lignes

    A lire ces deux billets sur le sujet

    Et pour remplacer une formule par la valeur qu'elle renvoie, il y a plus simple qu'un Copier/Collage spécial-Valeur
    A lire ce billet VBA Excel - Comment remplacer des formules par leur résultat ?
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Ma dernière contribution : VBA - Les macros complémentaires

  3. #3
    Membre habitué
    Homme Profil pro
    Chargé d'affaire
    Inscrit en
    septembre 2019
    Messages
    116
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Chargé d'affaire

    Informations forums :
    Inscription : septembre 2019
    Messages : 116
    Points : 140
    Points
    140
    Par défaut
    Bonjour,
    merci pour votre réponse,
    je vais étudier les 3 liens.

  4. #4
    Membre habitué
    Homme Profil pro
    Chargé d'affaire
    Inscrit en
    septembre 2019
    Messages
    116
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Chargé d'affaire

    Informations forums :
    Inscription : septembre 2019
    Messages : 116
    Points : 140
    Points
    140
    Par défaut
    Citation Envoyé par Philippe Tulliez Voir le message
    Bonjour,
    Il n'y a aucune raison d'utiliser une boucle pour écrire une formule sur plusieurs lignes

    A lire ces deux billets sur le sujet

    Et pour remplacer une formule par la valeur qu'elle renvoie, il y a plus simple qu'un Copier/Collage spécial-Valeur
    A lire ce billet VBA Excel - Comment remplacer des formules par leur résultat ?
    Bonjour à tous,
    tout d'abord, j'avais fait une erreur sur le compteur de la boucle,
    j'avais mis 184756 qui correspondent aux lignes alors que je copiais de colonnes en colonnes

    je viens d'étudier les liens cités ci-dessus,
    et j'ai refait ma procédure,
    en enlevant les "=" (égale) sur les formules (soit 184756 au total), mon fichier avait déjà perdu un tiers de sa taille, et s'ouvre beaucoup plus vite
    par contre je suis obligé de faire quand même une boucle pour copier ligne par ligne,
    d'une part parce que cela prends trop de mémoire, et d'autre part il n'y a pas 2 formules identiques sur les 184756 lignes,
    je lance mon programme ce soir, et je vous tiendrai informés.
    je pense que cela ira très très bien.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    Sub CopieFormulesRev1()
        Dim compteur As Long
        Dim i As Long
        i = i + 0
    With Application
                .DisplayAlerts = False
                .ScreenUpdating = False
                .Interactive = False
    End With
     
        For compteur = 1 To 184756 ' Ligne 22 à Ligne 184777
            With ThisWorkbook.Worksheets("AscDiz")
             .Range(Cells(22 + i, 1), Cells(22 + i, 14485)).Formula = "=" & Cells(22 + i, 1)  ' insertion de la formule inscrite sur colonne A
             .Range(Cells(22 + i, 1), Cells(22 + i, 14485)).Value = .Range(Cells(22 + i, 1), Cells(22 + i, 14485)).Value    ' copie en tant que valeur
            End With
            i = i + 1
        Next compteur
     
    Range("A1").Select
     
    With Application
            .DisplayAlerts = True
            .ScreenUpdating = True
            .Interactive = True
    End With
    End Sub

  5. #5
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    janvier 2010
    Messages
    10 695
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : janvier 2010
    Messages : 10 695
    Points : 25 532
    Points
    25 532
    Billets dans le blog
    32
    Par défaut
    Bonjour,
    par contre je suis obligé de faire quand même une boucle pour copier ligne par ligne,
    d'une part parce que cela prends trop de mémoire, et d'autre part il n'y a pas 2 formules identiques sur les 184756 lignes,
    Il n'est pas normal d'avoir des lignes avec des formules différentes dans une liste de données. Il faut réfléchir à une formule qui réponde à tous les cas
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Ma dernière contribution : VBA - Les macros complémentaires

  6. #6
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 263
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 263
    Points : 3 804
    Points
    3 804
    Par défaut
    Bonjour à tous,

    ton .Value=.Value, tu devrais le faire sur la plage complète en une fois.
    Tu gagneras des milliers d'écritures pour pas cher.
    De la même façon, tu pourrais lire en une fois les variables d'entrées dans une variable tableau, calculer en vba le résultat dans un autre tableau que tu colles en une fois à la fin. Même plus besoin du .Value=.Value
    Tu tomberas à qq minutes...
    Bon, si tu as 14000 colonnes à faire, tu peux découper par tranche de 1000, ça ne fera que 14 écritures.
    eric

  7. #7
    Membre habitué
    Homme Profil pro
    Chargé d'affaire
    Inscrit en
    septembre 2019
    Messages
    116
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Chargé d'affaire

    Informations forums :
    Inscription : septembre 2019
    Messages : 116
    Points : 140
    Points
    140
    Par défaut
    Citation Envoyé par Philippe Tulliez Voir le message
    Bonjour,

    Il n'est pas normal d'avoir des lignes avec des formules différentes dans une liste de données. Il faut réfléchir à une formule qui réponde à tous les cas
    Bonjour,
    le programme que j'avais lancé hier soir à 21h00, je viens de l'arrêter,
    en regardant le gestionnaire des tâches, la mémoire était arrivée à 98% (en haut du tableau) avec une taille qui arrivait à 128 000 Mo,

    en ce qui concerne les formules différentes elles se trouvent uniquement sur la première colonne ("A"), ce sont des combinaisons
    le résultat donne 184756 combinaisons que je devais copier sur la totalité des colonnes,
    puis compter les doublons avec le programme de Jacques Boigontier en utilisant le Dictionary d'Excel.

  8. #8
    Membre habitué
    Homme Profil pro
    Chargé d'affaire
    Inscrit en
    septembre 2019
    Messages
    116
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Chargé d'affaire

    Informations forums :
    Inscription : septembre 2019
    Messages : 116
    Points : 140
    Points
    140
    Par défaut
    Citation Envoyé par eriiic Voir le message
    Bonjour à tous,

    ton .Value=.Value, tu devrais le faire sur la plage complète en une fois.
    Tu gagneras des milliers d'écritures pour pas cher.
    De la même façon, tu pourrais lire en une fois les variables d'entrées dans une variable tableau, calculer en vba le résultat dans un autre tableau que tu colles en une fois à la fin. Même plus besoin du .Value=.Value
    Tu tomberas à qq minutes...
    Bon, si tu as 14000 colonnes à faire, tu peux découper par tranche de 1000, ça ne fera que 14 écritures.
    eric
    Bonjour,
    Avant de passer par .Value=.Value, je dois passer par la copie des formules, et c'est parce que il y a trop de formules actives que le programme est lent,
    surtout au fur et à mesure que le tableau augmente colonne par colonne.

    Oui, c'était ma première idée, de scinder le tableau en plusieurs fichiers et copier par pavés de 500 Lignes x 500 Colonnes

  9. #9
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 263
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 263
    Points : 3 804
    Points
    3 804
    Par défaut
    Bonjour,

    je dois passer par la copie des formules
    Non, tu n'es pas obligé. Tu peux faire tes concaténations sur des tableaux entiers et coller tout d'un coup.
    Ecrire une par une des dizaines de milliers de cellules 2 fois, tu n'arriveras jamais à le faire rapidement.
    eric

  10. #10
    Expert éminent
    Homme Profil pro
    Inscrit en
    août 2010
    Messages
    3 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : août 2010
    Messages : 3 453
    Points : 6 865
    Points
    6 865
    Par défaut
    Bonjour,

    Eric a parfaitement raison , travailler sur une si grande plage doit se faire sur un tableau en mémoire pour un gain significatif de temps !
    Maintenant, si tu persistes dans ta façon de faire, tu peux dans ce cas empêcher le calcul automatique en début de code et le rétablir à la fin tu gagnera un peu
    C'est quoi cette instruction :
    Toutes les variables numériques sont initialisées à 0 au lancement de la procédure donc 0 + 0 = 0

  11. #11
    Membre habitué
    Homme Profil pro
    Chargé d'affaire
    Inscrit en
    septembre 2019
    Messages
    116
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Chargé d'affaire

    Informations forums :
    Inscription : septembre 2019
    Messages : 116
    Points : 140
    Points
    140
    Par défaut
    Bonjour à tous,

    j'avais déjà essayé de copier en une seule fois,
    mais Excel se fige pendant des heures,

    ce matin, j'ai lancé la procédure sur 10 lignes,
    puis 100 lignes, cela mets environ 1mn30,
    en tout 5 fois et j'ai copié le résultat sur un nouveau classeur,
    soit un total de 510 lignes, ce qui donne un fichier d'une taille de 107 Mo, et il me reste à extraire encore 178000 Lignes,
    oui, je sais, c'est un truc de malade, mais je ne me rendrais pas aussi facilement,

    Grâce à Philippe Tulliez, j'ai éliminé l'activation des formules en enlevant le signe "=",
    ce qui me permet d'ouvrir mon fichier et d'exécuter les procédures beaucoup plus rapidement
    et Grâce à Jacques Boigontier, de lister la plage sur une colonne,
    à propos Jacques, en copiant ta procédure sur mon fichier, le programme bloque sur Mondico "Objet requis"
    mais si je prends ton fichier Classeur1-1.xlsm, et j'y ajoute mes données, le programme ne bloque pas.

    https://www.developpez.net/forums/d2.../#post11133966

    Nom : liste Doublons Objet Requis.jpg
Affichages : 112
Taille : 68,9 Ko

    je vais essayer de m'y prendre autrement,
    je vais extraire ligne par ligne, et l'envoyer directement dans une base Access,

    Merci à tous.

  12. #12
    Membre habitué
    Homme Profil pro
    Chargé d'affaire
    Inscrit en
    septembre 2019
    Messages
    116
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Chargé d'affaire

    Informations forums :
    Inscription : septembre 2019
    Messages : 116
    Points : 140
    Points
    140
    Par défaut
    Citation Envoyé par Theze Voir le message
    Bonjour,

    Eric a parfaitement raison , travailler sur une si grande plage doit se faire sur un tableau en mémoire pour un gain significatif de temps !
    Maintenant, si tu persistes dans ta façon de faire, tu peux dans ce cas empêcher le calcul automatique en début de code et le rétablir à la fin tu gagnera un peu
    C'est quoi cette instruction :
    Toutes les variables numériques sont initialisées à 0 au lancement de la procédure donc 0 + 0 = 0
    oui, effectivement, j'ai fait une petite erreur en déclarant la valeur de i = 0,
    sans m'en rendre compte j'avais écrits i = i + 0,
    et cela revient au même.
    et comme qui dirait l'autre, "seul le résultat compte"
    à propos qui a dit cela ? je ne sais pas

  13. #13
    Expert éminent
    Homme Profil pro
    Inscrit en
    août 2010
    Messages
    3 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : août 2010
    Messages : 3 453
    Points : 6 865
    Points
    6 865
    Par défaut
    Bonjour,

    le programme bloque sur Mondico "Objet requis"
    Le dictionnaire doit être déclaré en objet donc, c'est :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    Dim Mondico As Object
    et non :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    Dim Mondico As String

  14. #14
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 263
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 263
    Points : 3 804
    Points
    3 804
    Par défaut
    Bonjour à tous,

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    For Each c in Range(...)
    tu continues à lire presque 3 millions de cellules, cellule par cellule. Normal que ça reste très lent.

    Un exemple. Tu as 3 colonnes de données en A:B à partir de la ligne 2 et tu veux 2 colonnes de résultat en E:F :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    Sub test()
        Dim datas, result(), lig As Long, col As Long
        ' lecture par plage complète
        datas = [A2:C2].Resize(Cells(Rows.Count, 1).End(xlUp).Row).Value
        ' traitement en mémoire, on utilise le tableau datas au lieu de la feuille
        ReDim result(1 To UBound(datas), 1 To 2)
        For lig = 1 To UBound(datas)
            result(lig, 1) = datas(lig, 1) & datas(lig, 2)
            result(lig, 2) = datas(lig, 2) & datas(lig, 3)
        Next lig
        ' écriture par plage complète
        [E2].Resize(UBound(result, 1), UBound(result, 2)) = result
    End Sub
    teste sur 180000 lignes et tu auras une idée du gain.
    eric

    PS : si tu utilises trop de mémoire, excel va utiliser la mémoire virtuelle extrèmement lente (sur le disque dur). Dans ce cas travaille par blocs de données (boucle pour x colonnes) pour découper le travail.

  15. #15
    Expert éminent
    Avatar de MarcelG
    Homme Profil pro
    Développeur informatique
    Inscrit en
    juillet 2009
    Messages
    3 382
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Maine et Loire (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Finance

    Informations forums :
    Inscription : juillet 2009
    Messages : 3 382
    Points : 6 993
    Points
    6 993
    Billets dans le blog
    7
    Par défaut
    Bonjour à vous, et Bonjour au Forum,

    J'ai eu plusieurs échanges à ce sujet, notamment avec l'ami mercatog.

    Pour ma part, je reste sur une déclaration en .
    (Ne pas omettre d'activer alors la Référence
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Microsoft.Scripting Runtime
    )

    Cette déclaration permet, notamment, une meilleure appréhension des méthodes et propriétés.

    Bien Cordialement.

    Marcel

    Dernier billet:
    Suppression des doublons d'un tableau structuré, gestion d'un array

    Pas de messagerie personnelle pour vos questions, s'il vous plaît. La réponse peut servir aux autres membres. Merci.


  16. #16
    Membre habitué
    Homme Profil pro
    Chargé d'affaire
    Inscrit en
    septembre 2019
    Messages
    116
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : Chargé d'affaire

    Informations forums :
    Inscription : septembre 2019
    Messages : 116
    Points : 140
    Points
    140
    Par défaut
    Citation Envoyé par Theze Voir le message
    Bonjour à tous,

    effectivement, cela fonctionne avec la déclaration comme object,
    et je pense que la différence entre les 2 fichiers est que dans mon fichier je dois obligatoirement déclarer les variables avec Option Explicit
    merci pour l'aide

    Citation Envoyé par eriiic Voir le message
    je viens de faire un essai avec 14 000 ligne, et c'est du rapide,
    je vais étudier cela, comprendre et essayer de l'adapter
    merci beaucoup.

    Citation Envoyé par MarcelG Voir le message
    merci aussi pour la réponse,
    mais je maitrise encore moins le dictionary, même si j'ai compris un tout petit peu.

Discussions similaires

  1. Optimisation des opérations sur les grands nombres, algorithme de Knuth
    Par Jackyzgood dans le forum Algorithmes et structures de données
    Réponses: 8
    Dernier message: 21/10/2010, 20h27
  2. Importation d'un onglet et copie des formules de cellules
    Par jbggg dans le forum Macros et VBA Excel
    Réponses: 5
    Dernier message: 01/12/2009, 09h32
  3. Générer des nombres premiers trés grands
    Par Midou45 dans le forum Mathématiques
    Réponses: 9
    Dernier message: 04/05/2008, 00h20
  4. Copie des fichier sur un serveur
    Par randriamanana dans le forum ASP.NET
    Réponses: 11
    Dernier message: 07/09/2007, 15h26
  5. comment désactiver le copy des fichiers sur lan
    Par z7e7z dans le forum Développement
    Réponses: 4
    Dernier message: 09/01/2006, 10h10

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo