XL 2016 Performance et manipulation avec d’un variant

Tradman · 10 Avril 2021

Bonjour à tous,
J’ai un onglet avec 800 000 lignes et 162 colonnes sur lesquels je boucle pour faire plusieurs traitements. Pour gagner en efficacité, je charge le tout dans un array et je fais le traitement. Jusque là tout va bien et c’est même plutôt performant. Par contre lorsque je veux replacer cet array dans ma feuille, j’ai un message d’erreur comme quoi la mémoire est insuffisante.

Dim vData as variant

vData = range(« A1H800000 »).value2

Boucle sur le variant et traitement des données

Range.(« A1H800000 »).value2 = vData -> Erreur car mémoire insuffisante

J’ai trois questions :
1-) Est-ce que le fait d’avoir une machine plus puissante réglerait le problème ?
2-) Est-ce qu’il y a un moyen de faire une boucle pour replacer les données par tranches de 100000 lignes par exemple ?
3-) Est-ce que je suis rendu à un point où j’aurais besoin d’un autre outil plus performant?

Pour la deuxième partie du titre, est-il possible d’effectuer des opérations (Tri, déplacement de lignes, etc...) dans un variant (Mon vData dans mon exemple)? Comme vous pouvez l’imaginer, c’est un enjeu de performance pour moi. Mes tests avec un plus petit jeux de données m’a montré que je double presque ma performance en chargeant le tout dans un variant (~35 min de traitements plutôt que 60 min si je fais les manipulations directement sur la feuille Excel)

Merci beaucoup de votre aide !

Bernard_XLD · 10 Avril 2021

Bonjour Tradman, le forum

Tradman à dit:
J’ai un onglet avec 800 000 lignes et 162 colonnes

Ah, quand même !
pour répondre à tes questions:
1-non
2-oui, voir code joint, codé avec un tableau de variant.
mais comme les dimensions sont connues, on pourrait directement coder un tableau dimensionné avec un type de données spécifié genre Dim vData%(0 To 7, 1 To 100000)
selon le type de données à traiter, on pourrait prendre moins de place en mémoire et accélérer les process sur le tableau en mémoire si le type variant sature la mémoire physique, moins gênant si ton disque est un SSD d'oû la réponse 3.
3-c'est à toi de répondre à cette question, je ne connais ni ta configuration, ni tes données, ni tes besoins.

Tradman à dit:
Pour la deuxième partie du titre, est-il possible d’effectuer des opérations (Tri, déplacement de lignes, etc...) dans un variant (Mon vData dans mon exemple)?

oui, en terme de rapidité, c'est même conseillé sur une telle taille de données.

Bien cordialement, @+

VB:

Sub Traitement_vData()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    On Error GoTo Gere_Erreurs
    Dim vData(0 To 7), Compteur1 As Byte
    For Compteur1 = 0 To 7
        vData(Compteur1) = Range("A1").Offset(Compteur1 * 100000, 0).Range("A1:DH100000").Value
    Next Compteur1
    Rows("1:800000").Delete Shift:=xlUp
    For Compteur1 = 0 To 7
        Range("A1").Offset(Compteur1 * 100000, 0).Range("A1:DH100000").Value = vData(Compteur1)
    Next Compteur1
    Erase vData
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
    Exit Sub
Gere_Erreurs:
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
    MsgBox "Erreur numéro " & Err & " interceptée !" & Chr$(13) & "Type d'erreur : " & Error(Err), vbOKOnly + vbCritical, "J'ai glissé, chef !"
End Sub

Bernard_XLD · 10 Avril 2021

pour te donner une idée, sur ma config, avec des données type integer stockées dans le tableau variant, ça passe sans problème avec ce code sans rien saturer,
I7 4790K
16 Go de mémoire
W10 64 bits et Excel 365 64 bits

mapomme · 10 Avril 2021

Bonjour @Tradman , @Yeahou ,

Une autre manière de faire.
La différence avec @Yeahou est que je transfère le tableau sur la feuille non par blocs de lignes mais par blocs de colonnes. L'avantage que j'y vois est pour le traitement des données. Il n'y a qu'un seul tableau array classique et non pas un tableau de tableaux. Ça doit simplifier la gestion des indices pour le traitement des données. Sinon, pour le temps d'exécution, c'est le même à un epsilon près : environ 84 s.

Cliquer sur le bouton Hop! L'avancement de l'exécution se voit dans la barre de statut en bas.

Une constante PAS indique le nombre de colonnes à transférer pour chaque bloc. Sur ma config (win 10 64 bit - 16 Go RAM - proc AMD Ryzen 7 3700X - Excel 365 16 bit), PAS est fixé à 14. Le code est dans le module de la feuille "Feuil1".

nota : comme j'enregistre le fichier avant de le traiter, il faut d'abord enregistrer le fichier téléchargé sur le disque avant de lancer l'exécution (ou effacer la ligne ThisWorkbook.Save)

VB:

Const PAS = 14

Sub TEST()
Dim vData, i&, j&, j1&, j2, k&, n&, deb
   deb = Timer
   On Error GoTo FIN
   Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual: Application.EnableEvents = False
   'lecture des données sources
   ThisWorkbook.Save
   vData = Range("A1:dh800000").Value2
   Me.UsedRange.Clear
   'Boucle sur vData et traitement des données (pour exemple)
   For j = 1 To UBound(vData, 2)
      vData(1, j) = 1 & "_" & j
      vData(20, j) = 20 & "_" & j
      vData(UBound(vData), j) = 20 & "_" & j
   Next j
   'Boucle écriture
   For j = 1 To UBound(vData, 2) Step PAS
      j1 = j: j2 = j1 + PAS - 1
      If j2 > UBound(vData, 2) Then j2 = UBound(vData, 2)
      ReDim r(1 To UBound(vData), 1 To j2 - j1 + 1)
      Application.StatusBar = "colonnes de:  " & j1 & "  à  " & j2
      For i = 1 To UBound(vData): For k = j1 To j2
         r(i, k - j1 + 1) = vData(i, k)
      Next k, i
      Cells(1, j1).Resize(UBound(vData), UBound(r, 2)).Value2 = r
   Next j
   Application.Calculation = xlCalculationAutomatic: Application.EnableEvents = True: Application.StatusBar = False
   MsgBox "Durée = " & Format(Timer - deb, "# ##0\ sec."), vbInformation
   Exit Sub

FIN:
   Application.Calculation = xlCalculationAutomatic: Application.EnableEvents = True: Application.StatusBar = False
   MsgBox "Echec --> erreur: " & Err.Description
End Sub

mapomme · 10 Avril 2021

@Yeahou ,

Je suis curieux.

J'ai changé de micro récemment et pour voir ce que ça donne, j'ai pris un processeur AMD (d'habitude je suis Intel).

Peux-tu exécuter mon fichier sur ta bécane et me rapporter le temps d'exécution ? D'avance, merci...

Bernard_XLD · 10 Avril 2021

mapomme à dit:
Peux-tu exécuter mon fichier sur ta bécane et me rapporter le temps d'exécution ? D'avance, merci...

Voili !

Bernard_XLD · 10 Avril 2021

mapomme à dit:
Excel 365 16 bit

je ne savais même pas cela possible !

Bernard_XLD · 10 Avril 2021

mapomme , tu as un émulateur win 3.1 sous win 10 64 ?

pour faire tourner Excel 4.0 365 ?

Tradman · 10 Avril 2021

Bonjour Yeahou,
Merci pour ton retour rapide. En regardant ton code, je vois que tu crées plusieurs variant dès le départ. Mon problème est que je dois travailler sur l’ensemble des données, donc je ne peux pas séparé les données pour le traitement. Ce que je souhaite c’est séparer mon variant lors de là réécriture pour prendre moins de mémoire et espérer que ça passe.
Pour les opérations sur un variant, c’est que dans mon traitement, j’aimerais pouvoir déplacer un ligne et/ou faire des classements et j’aimerais savoir comment m’y prendre sur un variant. Mes données sont des une série d’évènements que doit faire une personne. J’ai une personne par ligne et je dois traiter les déplacements chronologiquement. Une fois que j’ai traité le premier événement de la première personne, je dois la déplacé au moment où chronologiquement je devrai traiterm

mapomme à dit:
Bonjour @Tradman , @Yeahou ,

Une autre manière de faire.
La différence avec @Yeahou est que je transfère le tableau sur la feuille non par blocs de lignes mais par blocs de colonnes. L'avantage que j'y vois est pour le traitement des données. Il n'y a qu'un seul tableau array classique et non pas un tableau de tableaux. Ça doit simplifier la gestion des indices pour le traitement des données. Sinon, pour le temps d'exécution, c'est le même à un epsilon près : environ 84 s.

Cliquer sur le bouton Hop! L'avancement de l'exécution se voit dans la barre de statut en bas.

Une constante PAS indique le nombre de colonnes à transférer pour chaque bloc. Sur ma config (win 10 64 bit - 16 Go RAM - proc AMD Ryzen 7 3700X - Excel 365 16 bit), PAS est fixé à 14. Le code est dans le module de la feuille "Feuil1".

nota : comme j'enregistre le fichier avant de le traiter, il faut d'abord enregistrer le fichier téléchargé sur le disque avant de lancer l'exécution (ou effacer la ligne ThisWorkbook.Save)

VB:

Const PAS = 14 Sub TEST() Dim vData, i&, j&, j1&, j2, k&, n&, deb deb = Timer On Error GoTo FIN Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual: Application.EnableEvents = False 'lecture des données sources ThisWorkbook.Save vData = Range("A1:dh800000").Value2 Me.UsedRange.Clear 'Boucle sur vData et traitement des données (pour exemple) For j = 1 To UBound(vData, 2) vData(1, j) = 1 & "_" & j vData(20, j) = 20 & "_" & j vData(UBound(vData), j) = 20 & "_" & j Next j 'Boucle écriture For j = 1 To UBound(vData, 2) Step PAS j1 = j: j2 = j1 + PAS - 1 If j2 > UBound(vData, 2) Then j2 = UBound(vData, 2) ReDim r(1 To UBound(vData), 1 To j2 - j1 + 1) Application.StatusBar = "colonnes de: " & j1 & " à " & j2 For i = 1 To UBound(vData): For k = j1 To j2 r(i, k - j1 + 1) = vData(i, k) Next k, i Cells(1, j1).Resize(UBound(vData), UBound(r, 2)).Value2 = r Next j Application.Calculation = xlCalculationAutomatic: Application.EnableEvents = True: Application.StatusBar = False MsgBox "Durée = " & Format(Timer - deb, "# ##0\ sec."), vbInformation Exit Sub FIN: Application.Calculation = xlCalculationAutomatic: Application.EnableEvents = True: Application.StatusBar = False MsgBox "Echec --> erreur: " & Err.Description End Sub

Merci beaucoup je vais regarder ça avec attention !

Bernard_XLD · 10 Avril 2021

Tradman à dit:
Pour les opérations sur un variant, c’est que dans mon traitement, j’aimerais pouvoir déplacer un ligne et/ou faire des classements et j’aimerais savoir comment m’y prendre sur un variant

il faut coder un tri en mémoire et utiliser un autre variant ou un tableau dimensionné, donnes un fichier exemple de ce que tu veux avec quelques données significatives

Tradman à dit:
Ce que je souhaite c’est séparer mon variant lors de là réécriture pour prendre moins de mémoire et espérer que ça passe.

regardes le code de Mapomme, il devrait te convenir

Bernard_XLD · 10 Avril 2021

Tradman à dit:
je vois que tu crées plusieurs variant dès le départ. Mon problème est que je dois travailler sur l’ensemble des données, donc je ne peux pas séparé les données pour le traitement.

c'est plus compliqué à traiter mais pas infaisable. Vois d'abord si cela fonctionne avec un seul tableau pour le traitement et des sous tableaux pour l'écriture, ce sera plus simple à gérer.

mapomme · 10 Avril 2021

Yeahou à dit:
Voili !

Regarde la pièce jointe 1101787

Je me doutais bien qu'à Intel j'aurais dû rester fidèle . En même temps, ton proc est cadencé plus rapide.

mapomme · 10 Avril 2021

Yeahou à dit:
je ne savais même pas cela possible !

Si, si. Pourquoi j'ai fait cela? Je ne m'en souviens plus .
Peut-être une crainte de compatibilité ascendante ?

fanch55 · 10 Avril 2021

Salut à tous,
@mapomme , pour info
Le premier test sur le portable de ma femme datant de 2013
Le 2ème sur mon fixe tout refait à neuf de cette année ....
32 gb de mémoires, y'a pas photo

Bernard_XLD · 10 Avril 2021

mapomme à dit:
Si, si. Pourquoi j'ai fait cela? Je n'en sais rien

je te rassure, c'est du 32 bit tout à fait standard, pas du 16 bit !
c'est l'install 365 par défaut !

XL 2016 Performance et manipulation avec d’un variant

Tradman

XLDnaute Nouveau

Bernard_XLD

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

Tradman

XLDnaute Nouveau

Bernard_XLD

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

fanch55

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

Discussions similaires