XL 2021 Vos avis SVP: Une fonction TRANSPOSE() à améliorer

[p'tit vieux]

Bonjour à tous,
Pour mes codes VBA, j'ai écris une première version de ma fonction Transpose() que je vais mettre à votre disposition dans sa version 1.0.0.
Toutefois, je voudrais votre avis sur l'intérêt de rajouter des options complémentaires à celle-ci.

Dans un premier temps j'ai écris une fonction Transpose() afin de lever la limite des 65536 lignes de la fonction "WorkSheetFunctions.Transpose" d'Excel.
Pour mes besoins j'ai ajouté les possibilités suivantes:
- Choisir la base départ du tableau (Base 0 ou 1)
- Pouvoir convertir un texte/valeur simple dans un tableau 2D T(1,1)

Rien d'exceptionel, mais pratique.

Donc je vous propose de me soumettre vos idées.

Par exemple, j'ai pensé à:
- Si on transmet T(0 to 0, 0 to N) ou T(1 to N, 1 to 1) Transpose() renvoie un tableau 1D T(0 to N) ou T(1 to N) suivant la base choisie
- Transposer ET ne renvoyer que les n premieres lignes
- Transposer ET ne renvoyer de la ligne n° X à n° Y

Voilà pour ces quelques idées.
A votre avis laquelle ou lesquelles de celles-ci ont-elles un intérêt.

Et vous, vos autres idées qui vous paraitraient utiles?
Ensuite suivant vos propositions ….
Je nommerai un 1er Ministre du développement
Mais non! Je publierai une nouvelle version (Pas sérieux ces P'tit Vieux)

Merci pour vos avis et suggestions.

ATTENTION!
Ici il n'est pas question de faire des recherches de texte/valeur ou autre truc du genre.

 

[p'tit vieux]

Je suis pas un dieu moi

Serait ce de la jalousie
PLIEEEEE !!!
A demain tous
Et merci Patrick

 

[patricktoulon]

non c'est du réalisme
je suis en constante recherche d’amélioration ou recherche d'autre solution
pour mon plaisir personnel

faire du code c'est assez facile finalement mais faire des algos qui ménage ta bécane ça l'est moins

 

[Dudu2]

Bonjour les retourneurs de tables,

J'ai fait un test sur un transpose 2D de 1 milion 47500 lignes sur 2 colonnes.et sans changement de base.

Le changement de base n'aura aucune influence sur les temps de traitement.

Je vous confirme cela demain et ferai un comparatif avec la fonction Excel.

N'oublie pas que pour un Application.Transpose(), tes UBound max sont de 65535.
Ceci dit 65535² = 4 294 836 225, t'auras le temps de préparer le déjeuner et sortir les petits enfants.
De plus tu as déjà un joli fichier de test comparatif en Post #43.

Je suis pas un dieu moi

Mais si voyons, quand @TooFatBoy parlait de 2ème dieu du VBA, il pensait évidemment à toi comme le 1er

, j'en suis sûr !

On plaisante bien sûr, car, à vrai dire, le seul miracle que commettent les dieux de ce forum, c'est quand ils réussissent à aider quelqu'un pour le sortir de la panade, et c'est déjà pas si mal.

 

[TooFatBoy]

Mais si voyons, quand @TooFatBoy parlait de 2ème dieu du VBA, il pensait évidemment à toi comme le 1er

, j'en suis sûr !

Va savoir... En tout cas, ce qui est sûr c'est que je ne pensais pas à moi.

à vrai dire, le seul miracle que commettent les dieux de ce forum, c'est quand ils réussissent à aider quelqu'un pour le sortir de la panade, et c'est déjà pas si mal.

 

[patricktoulon]

Bonjour à tous
je pense qu'après quelque années a bidouiller on arrive tous a plus moins trouver des solutions
depuis quelque temps je me penche sur la consommation en carburant et là c'est une autre histoire
on se rend compte que parfois on s’étale
et là récemment j'ai découvert le test benchmark indiqué par jurassic pork me semble til
depuis je passe toute mes solutions à la moulinette et on se rend compte que l'on peut faire mieux
et je m'applique à ça

et pour être plus précis dans le cas et sujet de cette discussion je suis allé jusqu'au bout des tests
voila ce que rend chez moi le verdict
grosso-modo au delà de 30000 lignes, on peu passer par la transpose perso en dessous c'est pas la peine
le application transpose dure 2 fois( voir plus selon le type de valeur) moins longtemps sur 30 000 lignes
alors me vient une idée car cet écart est exponentiel en terme de pourcentage
mettre une condition dans la fonction
en dessous passer par le transpose de vba et au dessus passer par le transpose perso
ça fait quoi 3/4 lignes de plus mais en terme de consommation et donc de rapidité on y gagnerait
ce n'est qu'un raisonnement

 

[patricktoulon]

re
les résultat chez moi pour un dim(1 to 30000, 1 to 5)
Attention c'est un tableau vide

IDnr  Name                            Count  Sum of tics  Percentage  Time sum
0     debut de la transposition VBA       1          237       0,14%     13 us
1     fin de la transposition VBA         1      166 259      99,86%     15 ms
      TOTAL                               2      166 496     100,00%     15 ms

Total time recorded:             15 ms

IDnr  Name                              Count  Sum of tics  Percentage  Time sum
0     debut de la transposition perso       1          128       0,08%     24 us
1     fin de la transposition perso         1      154 090      99,92%     17 ms
      TOTAL                                 2      154 218     100,00%     17 ms

Total time recorded:             17 ms

 

[Dudu2]

Bonjour @patricktoulon,

C'est quoi ces benchmarks de @jurassic pork dont tu parles ?

Le test comparatif que j'ai fait avec le fichier du Post #43 que je viens de modifier pour aller jusqu'au 10.000ème de seconde donne chez moi les résultats suivants, qui même s'ils varient d'une exécution à une autre, sont toujours en faveur du VBA.

Dimension 1​	Dimension 2​	EXCEL​	VBA​	% DIFF​
10000	5	0,0156	0,0117	-25,00%​
20000	5	0,0352	0,0273	-22,44%​
30000	5	0,0508	0,0391	-23,03%​
40000	5	0,0742	0,0547	-26,28%​

 

[patricktoulon]

Autrement dit que nous dit ce test :
on voit clairement que pour les tics la version VBA met le double donc utilise moins le proc à l'inverse de la perso
on voit clairement aussi que le pourcentage d'UC est plus important sur la vba

on a là un exemple concret du compromis entre taiter comme un buzard et laisser tranquille faire la chose mais qui sera plus longue

je vous l'accorde j'ai testé avec un tableau vide les résultats seraient autrement parlant avec plus de mie de pain dans les items

 

[patricktoulon]

Bonjour @patricktoulon,

C'est quoi ces benchmarks de @jurassic pork dont tu parles ?

Le test comparatif que j'ai fait avec le fichier du Post #43 que je viens de modifier pour aller jusqu'au 10.000ème de seconde donne chez moi les résultats suivants, qui même s'ils varient d'une exécution à une autre, sont toujours en faveur du VBA

Dimension 1​	Dimension 2​	VBA​	EXCEL​
10000​	5​	0,0156​	0,0117​
20000​	5​	0,0352​	0,0273​
30000​	5​	0,0508​	0,0431​
40000​	5​	0,0742​	0,0547​

re
oui tu test avec le timer qui est bien trop imprécis

 

[patricktoulon]

et si mes tests benchmark sont fait à chaque etapes de la fonction perso original simple
le test est toujours le même dim t(1 to 30 000 ,1 to 5)
c'est un tableau vide

Dim bench As cBenchmark
Sub test()
    Dim t(1 To 30000, 1 To 5)
     t2 = TransposeXV1(t)
  
End Sub


Function TransposeXV1(t)

    'patricktoulon    V 1.2 -- 08/07/2021
    Dim y&, i&, C&, t2
    Set bench2 = New cBenchmark
    bench2.TrackByName "test de dimension du tableau "

    On Error Resume Next
    y = UBound(t, 2)
    On Error GoTo 0
    bench2.TrackByName "fin test de dimension du tableau "

    If y = 0 Then
        bench2.TrackByName "redimensionnement 1D transposé 2D du tableau "
        ReDim t2(LBound(t) To UBound(t), 1)
        bench2.TrackByName "fin de redimensionnement 1D transposé 2D du tableau "

    Else
        bench2.TrackByName "redimensionnement 2D transposé 2D du tableau "
        ReDim t2(LBound(t, 2) To UBound(t, 2), LBound(t) To UBound(t))
        bench2.TrackByName "fin de redimensionnement 1D transposé 2D du tableau "
    End If
    bench2.TrackByName "début de la transposition du tableau "
    For i = LBound(t) To UBound(t)
        If y = 0 Then
            t2(i, 1) = t(i)
        Else
            For C = LBound(t, 2) To UBound(t, 2): t2(C, i) = t(i, C): Next
        End If
    Next
    bench2.TrackByName "Fin de la transposition du tableau "

    TransposeXV1 = t2
End Function

le resultat est assez parlant je trouve

IDnr  Name                                                  Count  Sum of tics  Percentage  Time sum
0     test de dimension du tableau                              1        2 000       1,63%    200 us
1     fin test de dimension du tableau                          1           62       0,05%   6200 ns
2     redimensionnement 2D transposé 2D du tableau              1           32       0,03%   3200 ns
3     fin de redimensionnement 1D transposé 2D du tableau       1        4 695       3,84%    470 us
4     début de la transposition du tableau                      1           48       0,04%   4800 ns
5     Fin de la transposition du tableau                        1      115 566      94,41%     12 ms
      TOTAL                                                     6      122 403     100,00%     12 ms

Total time recorded:             12 ms

 

[job75]

Bonjour le forum,

J'ai testé ma fonction du post #14 sur un tableau 10000 X 1000 rempli avec ALEA() :

Function TransposeX(x)
If Not IsArray(x) Then TransposeX = x: Exit Function
Dim a, Lb1, Ub1, Lb2, Ub2, e As Boolean, b(), i, j
a = x
Lb1 = LBound(a): Ub1 = UBound(a)
On Error Resume Next 'car erreur si a est de dimension 1
Lb2 = LBound(a, 2): Ub2 = UBound(a, 2)
If Err Then
    ReDim b(Lb1 To Ub1, 0 To 0)
    For i = Lb1 To Ub1
        b(i, 0) = a(i)
    Next i
Else
    ReDim b(Lb2 To Ub2, Lb1 To Ub1)
    For i = Lb2 To Ub2
        For j = Lb1 To Ub1
            b(i, j) = a(j, i)
    Next j, i
End If
TransposeX = b 'matrice
End Function

Sub TestTranspose()
Dim a, t, b
a = [A1:ALL10000]
t = Timer
b = Application.Transpose(a)
MsgBox Timer - t
End Sub

Sub TestTransposeX()
Dim a, t, b
a = [A1:ALL10000]
t = Timer
b = TransposeX(a)
MsgBox Timer - t
End Sub

La fonction VBA est chez moi 20% plus rapide que la fonction Excel 1,20 s et 0,95 s.

A+

 

[patricktoulon]

Bonjour @job75
oui c'est bien ça tout dépend de la quantité de donnée a transposer
la tu es sur 10 000 X 1000 forcement tu es au dessus des 30 0000 X5

 

[Dudu2]

oui tu test avec le timer qui est bien trop imprécis

C'est en effet imprécis, mais c'est représentatif car l'imprécision s'applique sur les 2 méthodes et on peut répéter le test 2 ou 3 fois pour un scénario pour vérifier.

Et quand la taille de la table devient plus importante, le gain moyen passe de 20% à 30% et continue d'augmenter.

Dimension 1​	Dimension 2​	EXCEL​	VBA​	% DIFF​
10000​	100​	0,4609​	0,3047​	-33,89%​
20000​	100​	0,9141​	0,6289​	-31,20%​
30000​	100​	1,4414​	0,9258​	-35,77%​
40000​	100​	1,8828​	1,1758​	-37,55%​

 

[patricktoulon]

@job75
test de transposition perso avec 1 to10 000, 1 to 1000) avec ma perso

IDnr  Name                                                  Count  Sum of tics  Percentage  Time sum
0     test de dimension du tableau                              1          535       0,01%     54 us
1     fin test de dimension du tableau                          1           94       0,00%   9400 ns
2     redimensionnement 2D transposé 2D du tableau              1           37       0,00%   3700 ns
3     fin de redimensionnement 2D transposé 2D du tableau       1      280 596       3,49%     28 ms
4     début de la transposition du tableau                      1           64       0,00%   6400 ns
5     Fin de la transposition du tableau                        1    7 766 274      96,50%    777 ms
      TOTAL                                                     6    8 047 600     100,00%    805 ms

Total time recorded:             805 ms

 

[job75]

J'ai retesté et les résultats sont en fait quasiment les mêmes, autour de 0,95 s.