[Optimisation] code VBA transposition lignes vers colonnes

Staple1600

XLDnaute Barbatruc
Bonsoir,

Est-ce que vous voyez un moyen d'optimiser le temps de traitement de la macro de transposition?
(ou un autre type de syntaxe)
J'ai ajouté une seconde macro pour créer les conditions de tests
(Le classeur de test doit contenir deux feuilles)
VB:
Sub TransposeLIG_COL()
Dim a As Variant, b As Variant
Dim i&, j&, k&
Dim t0 As Double
'Heure départ
t0 = Timer
Application.ScreenUpdating = False
' passage en calcul sur ordre
Application.Calculation = xlCalculationManual
'si error, on saute à FIN: pour remettre le calcul en automatique
On Error GoTo FIN
a = ActiveSheet.Cells(1).CurrentRegion.Value
ReDim b(1 To UBound(a, 1) * (UBound(a, 2) - 2), 1 To 4)
For i = 2 To UBound(a, 1)
  For j = 3 To UBound(a, 2)
    k = k + 1
    b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(1, j): b(k, 4) = a(i, j)
  Next j
Next i
Sheets(2).Cells(1).Resize(UBound(b, 1), UBound(b, 2)).Value = b
FIN:
If Err.Number > 0 Then MsgBox "Erreur n° " & Err.Number & vbLf & Err.Description
Application.Calculation = xlCalculationAutomatic
MsgBox Format(Timer - t0, "0.0 \ sec."), vbInformation, "Temps éxécution macro"
'crédits code: Peter_SSs, ma pomme
End Sub
Sub CreationDonnees()
'macro pour générer des données de test
Application.ScreenUpdating = False
[C1] = 1: [A2:B2] = Array(100002, "DATA2")
[C1:N1].DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
[B2:B30000].DataSeries Rowcol:=xlColumns, Type:=xlAutoFill, Date:=xlDay, Trend:=False
[A2:A30000].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
[C2:N30000] = "=RANDBETWEEN(1,500)": [C2:N30000] = [C2:N30000].Value
End Sub
PS: la macro de transposition est issue de mes archives (glanée sur le web anglophone)

Sur mon PC de test, le MsgBox affiche entre 9 à 10 secondes
(version Excel pour le test: 2013)
NB: Dans la réalité, le nombre de ligne peut aller jusqu'à plus ou moins 60 000 lignes.

Merci à ceux qui prendront le temps de s’intéresser à la question ;)
 

ThomasR

XLDnaute Occasionnel
Bonsoir ThormasR

Tu poses la question à qui?
A moi ou Laurent950?

Si c'est à moi puisque Application.Enable n'était pas dans le code initial, logiquement j'aurais avoir des temps proches des votres, non?
(je parle avec le PC avec Excel 2013)
Bonjour à toi, car si les performances sont pourri à l écriture dans la sheet. (Il faudrait voir le taux d occupation de la RAM et du cpu pour voir ce qui pêche). Mais si c'est cpu le fait de stopper le contrôle des évènements excel avant d'écrire devrait booster son exécution.
 

Staple1600

XLDnaute Barbatruc
Re

Laurent950
J'ai essayé ton code de test
VB:
Sub TestZ()
Dim varArray            As Variant
Dim varTemp             As Variant
Dim pas                 As Integer: pas = 9
Dim sh As Worksheet
Set sh = Worksheets(2)
' Base de donnée récupérer
With Worksheets("Feuil1")
.Range("A2:E30") = "=ROW()^COLUMN()-1"
.Range("A2:E30") = .Range("A2:E30").Value
varArray = .Range("A2:E30")
End With
' Découpage en bloc de 10
For i = 1 To 30 Step 10
pas = 9 + i
varTemp = Application.Index(varArray, Evaluate("Row(" & i & ":" & pas & ")"), Application.Transpose(Evaluate("Row(1:" & UBound(varArray, 2) & ")")))
sh.Cells(sh.Cells(65536, 1).End(xlUp).Row + 1, 1).Resize(UBound(varTemp, 1), UBound(varTemp, 2)).Value = varTemp
Next i
End Sub
Et la restitution est identique à la source (où j'ai pas tout compris) ?
Et j'ai une erreur REF sur la dernière ligne.
 

ThomasR

XLDnaute Occasionnel
Staple1600
Conclusion :
exemple 1 poste #40
Données récupéré depuis la feuilles excel est stocké dans un tableau = pas d'incompatibilité de type
- > varArray = Remplis directement depuis la feuille excel = OK
varTemp = Application.Index(varArray, Evaluate("Row(" & i & ":" & pas & ")"), Application.Transpose(Evaluate("Row(1:" & UBound(varArray, 2) & ")")))
sh.Cells(sh.Cells(65536, 1).End(xlUp).Row + 1, 1).Resize(UBound(varTemp, 1), UBound(varTemp, 2)).Value = varTemp


Données remplis directement depuis une variable tableau vers la variable tableau cible = incompatibilité de type
- b() remplis avec a() = incompatibilté de type
la ligne de code qui bloque est :
Transpose a ses limite. Sur le net y a un mec qui l a réécrit afin de pouvoir travailler sur des plus grosses variable.
 

ThomasR

XLDnaute Occasionnel
Plutôt que ça
VB:
Cells(sh.Cells(65536, 1).End(xlUp).Row + 1
Il vaut mieux écrire ça
Code:
Cells(sh.Cells(application.cells.rows.count, 1).End(xlUp).Row + 1,
Il y a la même chose pour les colonnes.
Cela permet de travailler avec toutes les versions sans se limiter à 65000
 

ThomasR

XLDnaute Occasionnel
Re

je m'aperçois que j'ai zappé des réponses (c'est normal avec écran si petit)
Donc bonsoir à zebanx, Roblochon.

Plus se déroule le fil, plus je m'inquiété du pourquoi d'un Excel 2013/W10 plus lent que des versions antérieures d'Excel tournant sur des OS anciens.
C'est par ce que l'on code de plus en plus mal qu'il faut de plus en plus de cpu et RAM.
Regarde ce que tu pouvais faire avec windows XP 500mo vs windows 10 (env 15go) regarde le poids du clavier Google sous Android c'est plus lourd que windows 98.
Donc rien étonnant qu'à fonctionnalité égale les anciens OS et app soit plus performante.

Aujourd'hui c'est le hardware qui rattrape le coup à base de SSD.
 

laurent950

XLDnaute Accro
Et la restitution est identique à la source (où j'ai pas tout compris) ?
C'est un exemple qui découpe la variable tableau par bloc, c'est effectivement identique à la source, mais cela est pour
exemple d'une variable tableau de 30 lignes sur 5 colonnes, celle-ci et recopier dans la feuille excel en 3 fois soit
avec cette boucle :
Les 10 premières lignes par 5 colonnes
Les 20 Prochaine lignes par 5 colonnes
ect.
Mais cela n'a pas l'air de fonctionné dans se gros fichier de plus de plus de 48 000 lignes donc je peux pas faire le test
de la restitution de la variable tableau b() à recopier en plusieurs fois dans la feuilles excel en bloc de 5 000 lignes par le nombres
total de colonnes et voir si il y a un gain de rapidité
mais cela ne fonctionne pas ?
 

Staple1600

XLDnaute Barbatruc
Re

Pour ce soir, je coupe le son et je vais rejoindre les bras de Morphée.
Merci à tous pour vos contributions, avis et conseils.

Laurent950
La nuit portant conseille, on y verra plus clair demain.

ThomasR
J'aimais bien 98SE (que'on pouvait réinstaller à l'envie en 30 minutes avec une disquette de boot optimisée
Modboot souvenir, souvenir ;
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Joyeuses Pâques à tous,
Un p'ti code pour une écriture par paquet. On pourra comparer avec le code de Laurent quand il sera fonctionnel. Comme subodoré, plus le paquet est gros, plus c'est rapide.

C'est parce qu'on code de plus en plus mal qu'il faut de plus en plus de cpu et RAM.
Je pencherai pour le contraire. Quand j'ai commencé à coder, la mémoire était une chose rare et les algorithmes en tenaient compte. On économisait l'espace mémoire. Puis avec le développement de l'industrie électronique, l'accroissement des capacités des mémoires volatiles ou non, la rapidité des composants et la baisse des coûts, l'optimisation semblait être devenue inutile. On pouvait rajouter de la mémoire, changer de processeur tous les deux ans, etc... La programmation s'est sans doute moins soucié des ressources disponibles. La rapidité d'un code pouvait compter sur l'amélioration continue des performances des processeurs. Donc je dirai plutôt : C'est parce qu'on dispose de plus en plus de CPU et RAM qu'on code de plus en plus "à l'arrache" (et je me m'exclus pas dans l'affaire)

(Voyez dans une des macros du fichier joint : On lit un tableau (en mémoire) de 30 000 fois 14 colonnes et on écrit un tableau (en mémoire) de 30 000 fois 12 fois 4 éléments. Qui d'entre nous se soucie de la taille utilisée?)
[NOSTALGIE]Combien de CPC 64 m'aurait-il fallu pour contenir ces deux tableau ?[\NOSTALGIE]
 

Pièces jointes

  • Staple1600- Speedy Gonzales- v2.xlsm
    49.8 KB · Affichages: 3
Dernière édition:

Staple1600

XLDnaute Barbatruc
Re


mapomme
En attendant de pouvoir être devant un PC plus fringant
(voici les résultats sur le vieux bouzin et Excel 2003)
Test Staple
0,6 sec. Données stocké Variable tableau b()
11,5 sec. Transfert dans feuilles excel
Test ventiler ma pomme
11,4 sec. Transfert dans feuilles excel
Test Laurent950
J'ai une erreur (faut que j'aille voir pourquoi)
Test ma pomme par paquet
2 500 Nombre de lignes par paquet
11,7 sec. Transfert dans feuilles excel

PS1: Il y avait un petit souci pour la restitution (que j'ai corigé ci-dessous)
For j = 3 To UBound(t, 2) n = n + 1: r(n, 1) = t(i, 1): r(n, 2) = t(i, 2): r(n, 3) = t(1, j): r(n, 4) = t(i, j) Next j

PS2: J'ai donné plus haut dans le fil le temps pouu l'export direct en CSV sur Excel2003
(code de ThomasR)
 
Dernière édition:

Efgé

XLDnaute Barbatruc
Salut à tous, bonjour aux autres (c) Pierrot

Ma modeste pierre à l'édifice
Sur mon pc maison la macro du post 1 met 2,5 secondes tout compris....
Ma config
  • Microsoft Windows 10 Famille
  • Version 10.0.17134
  • Fabricant HP
  • Modèle HP Pavilion Desktop PC 570-p0xx
  • Type PC à base de x64
  • Processeur Intel(R) Core(TM) i5-7400 CPU @ 3.00GHz, 3001 MHz, 4 cœur(s), 4 processeur(s) logique(s)
  • Version du BIOS/Date AMI F.20, 01/11/2017
  • Version SMBIOS 3.0
  • Rôle de la plateforme Bureau
  • Couche d’abstraction matérielle Version = "10.0.17134.619"
  • Mémoire physique (RAM) installée 8,00 Go
  • Mémoire physique totale 7,89 Go
  • Mémoire physique disponible 4,31 Go
  • Mémoire virtuelle totale 9,14 Go
  • Mémoire virtuelle disponible 5,12 Go
  • Espace pour le fichier d’échange 1,25 Go

Je ne comprend pas les différences de traitement...

Cordialement
 

Discussions similaires

Réponses
11
Affichages
236
Réponses
23
Affichages
1 K