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

p'tit vieux · 5 Décembre 2024

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.

Dudu2 · 11 Décembre 2024

Et pour l'appliquer au test comparatif...

jurassic pork · 12 Décembre 2024

Hello Dudu2,
ton code du microTimer ne se compile pas sous un excel 32 bits car tu as fait une erreur dans tes déclarations :
Le #If VBA7 ce n'est pas que pour le 64 bits , pour le 64 bits il faut faire en plus un #If Win64. Voici ton code corrigé :

VB:

#If VBA7 Then
   #If Win64 Then
       Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (Frequency As LongLong) As Long
       Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (TickCount As LongLong) As Long
   #Else
       Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (Frequency As Currency) As Long
       Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (TickCount As Currency) As Long
   #End If
#Else
    Private Declare Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (Frequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (TickCount As Currency) As Long
#End If

Ami calmant, J.P

Dudu2 · 12 Décembre 2024

Bonjour @jurassic pork,
Merci pour cette correction.

Une autre question que je me pose est la convertibilité du résultat d'un LongLong / Currency en Double.
Est-ce que le Double va exploser aux valeurs hautes d'un LonLong par exemple ?

Dudu2 · 12 Décembre 2024

En fait, il n'y a pas de convertibilité d'un LongLong ou d'un Currency en Double aux valeurs hautes.
Le Double perd des informations et curieusement la conversion inverse plante !

Donc retourner un Double pour les TICs est incertain car je ne sais pas jusqu'où le TickCount de QueryPerformanceCounter peut aller. A la rigueur on peut utiliser un Double pour retourner une différence de TICs dans le cadre d'une mesure de performance.

Dudu2 · 12 Décembre 2024

Ce qui amène à un nouveau Module_MicroTimer ci-dessous et un nouveau fichier de Benchmark.

Code:

Option Explicit

#If VBA7 Then
   #If Win64 Then
       Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (Frequency As LongLong) As Long
       Private Declare PtrSafe Function GetTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (TickCount As LongLong) As Long
   #Else
       Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (Frequency As Currency) As Long
       Private Declare PtrSafe Function GetTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (TickCount As Currency) As Long
   #End If
#Else
    Private Declare Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (Frequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (TickCount As Currency) As Long
#End If

Function MicroTimer(Optional ByRef TickCount As Variant) As Double
    ' Returns seconds.
    Dim Ticks1 As Variant
    Static Frequency As Variant

    ' Get frequency.
    If Frequency = 0 Then getFrequency Frequency

    ' Get ticks.
    GetTickCount Ticks1
    
    ' TickCount argument
    If Not IsMissing(TickCount) Then
        TickCount = Ticks1
    End If
    
    ' Seconds
    If Not Frequency = 0 Then MicroTimer = Ticks1 / Frequency
End Function

Function GetIntervalTickCount(StartTickCount As Variant, StopTickCount As Variant) As Double
#If Win64 Then
    'LongLong
    GetIntervalTickCount = CDbl(StopTickCount - StartTickCount)
#Else
    'Currency
    GetIntervalTickCount = CDbl(StopTickCount - StartTickCount) * 10000
#End If
End Function

patricktoulon · 12 Décembre 2024

re
@Dudu2
l'effet de bord est trop important pour juger
exemple
ordre 1

IDnr	Name	Count	Sum of tics	Percentage	Time sum	MicroTimer Tics	MicroTimer Time
0	Application.Transpose	1	8 778 951	40,11%	878 ms	8 778 516	878 ms
1	VBA TransposeNaturel @Dudu2	1	4 392 171	20,07%	439 ms	4 391 889	439 ms
2	VBA Transpose @patricktoulon	1	4 376 568	19,99%	438 ms	4 341 495	434 ms
3	VBA Transpose @mapomme	1	4 341 990	19,84%	434 ms	17 511 900	1,75 s
	TOTAL	4	21 889 680	100,00%	2,19 s

ordre 2

IDnr	Name	Count	Sum of tics	Percentage	Time sum	MicroTimer Tics	MicroTimer Time
0	VBA TransposeNaturel @Dudu2	1	4 354 599	19,88%	435 ms	4 354 119	435 ms
1	VBA Transpose @patricktoulon	1	4 378 622	19,99%	438 ms	4 378 343	438 ms
2	Application.Transpose	1	8 832 097	40,32%	883 ms	8 831 652	883 ms
3	VBA Transpose @mapomme	1	4 337 458	19,80%	434 ms	4 337 165	434 ms
	TOTAL	4	21 902 776	100,00%	2,19 s	21 901 279	2,19 s

ordre 3

IDnr	Name	Count	Sum of tics	Percentage	Time sum	MicroTimer Tics	MicroTimer Time
0	VBA Transpose @patricktoulon	1	4 375 665	19,93%	438 ms	4 375 369	438 ms
1	VBA TransposeNaturel @Dudu2	1	4 403 191	20,06%	440 ms	4 402 914	440 ms
2	Application.Transpose	1	8 831 880	40,23%	883 ms	8 831 586	883 ms
3	VBA Transpose @mapomme	1	4 340 188	19,77%	434 ms	4 339 795	434 ms
	TOTAL	4	21 950 924	100,00%	2,2 s	21 949 664	2,19 s

selon l'ordre et le nombre de lancement ça n'a plus de sens même si les chiffres sont proches
et avec ma version c'est pareil
ce test est valables seulement pour une action finalement et encore comme avec le premier est gentils ,je ne sais pas si on doit le Prendre en compte
d'ailleurs ( à mediter) si c'est effet de bord est due a la mémoire cela nous donne quelque indications sur l'utilisation de celle ci par nos diverses versions

d'ailleurs on constate que il y a un schéma qui se dessine dans les résultats (moins,plus,moins,plus)
peut être effectivement que le cache ne se vide pas avant d'avoir atteint un seuil

peut être ,il faudrait essayer un application.ontime..... et suivi end(extinction des feux ) entre chaque test pour voir si le cache réagi toujours de cette manière

ton truc s’arrête a l'unité "ms" tu descends pas jusqu'au moins les microsecondes"µs"

jurassic pork · 12 Décembre 2024

Hello patricktoulon,
c'est ce que j'ai constaté aussi , la précision n'est pas au rendez-vous, d'un lancement de test à un autre on a des différences. Mais il ne faut pas oublier que windows est multitâche donc on ne sait pas trop ce qu'il fait pendant la mesure. Plus le temps à mesurer est court plus on a des chances d'avoir beaucoup de différences entre 2 tests. Mais avec cBenchmark et microtimer on a quand même une assez bonne évaluation des temps > à la milliseconde. Je ne pense pas que l'on puisse beaucoup améliorer les mesures.
Ami calmant, J.P

mapomme · 12 Décembre 2024

Bonjour à tous ,

Pour compléter mon message #134 : un petit extrait d'un document (réf : https://www.utc.fr/~avalle/dossiers-pdf/Incertitudes-version2002.PDF) : Dans quel cas sommes-nous ?

jurassic pork · 12 Décembre 2024

Hello mapomme,
j'avais raté ton message #134. Il y a tellement de messages dans cette discussion

mapomme · 12 Décembre 2024

Re,
je complète :
Les différents algorithmes de tri sont évalués par l'évolution de leur nombre d'opérations à exécuter en fonction du nombre d'éléments à trier. On ne mesure pas un temps mais on évalue l'évolution d'un nombre en fonction de la quantité de données initiales. C'est donc indépendant du processeur et de son environnement.

Dans notre cas, on pourrait dire que si on a une matrice i lignes et j colonnes, le nombre d'opérations de toutes les méthodes est grosso-merdo proportionnel à i * j fois [ la lecture et l'affectation t(i,j)=t(j,i) ] auquel il faut rajouter le calcul sur les indices [somme élémentaire de deux termes] fois 2 (car deux indices).
et comme je disais, toutes les méthodes reviennent donc à une complexité similaire. Mais dont la mesure précise et exacte ne nous est pas accessible. Cette discussion a été très intéressante et vos recherches opiniâtres ont été suivies avec attention .

Dudu2 · 12 Décembre 2024

En fait j'ai ajouté l'ordre d'exécution des différentes versions à cause de mon bug d'indice du Post #156.
Mais ça ne sert à rien, sauf peut-être à démontrer que c'est inutile. Ce qui provoque les variations c'est bien sûr l'activité annexe de Windows. Il faudrait pouvoir s'attribuer l'exclusivité de l'usage de la CPU mais je n'ai pas trouvé d'API qui permette de le faire.

Le dernier but de ces manips était de montrer que le petit Module_MicroTimer est suffisant pour faire des mesures de base que la Classe cBenchmark fait de manière plus sophistiquée grâce à ses fonctions diverses.

Dudu2 · 12 Décembre 2024

Comment gagner les micro-secondes qui vont (peut-être) prolonger votre vie ?

Dans l'algorithme de @mapomme (et dans celui de @patricktoulon ), il y a un calcul dans l'indiçage d'un des tableaux (destination ou source) pour s'adapter à l'éventuel nouvel LBound du tableau transposé.

J'avais ça aussi dans mon code et j'ai vite remplacé les indices calculés par une variable qui évolue de + 1 dans la boucle car j'avais remarqué que c'était légèrement plus économe en CPU.

Alors j'ai modifié l'algorithme de @mapomme pour en faire une nouvelle variante l'algorithme de @mapommePlus ajoutée au test comparatif.

On peut alors vérifier que la méthode ajout de + 1 (qui en langage machine doit être un truc hypersonique) a de meilleures performances que l'indiçage calculé.

Répéter les test 2, 3 ou 4 fois pour avoir une meilleure idée.
Edit: On gagne en gros entre 2% et 3%

patricktoulon · 12 Décembre 2024

re
oui @Dudu2 +1 sera certainement plus rapide qu'un calcul
sauf que dans l'absolu cette fonction transpose n'est pas sensée modifier les bases
et mettre +1 c'est forcer le transpose en base 1
et même encore là on a un problème car ajouter +1 au deux extrémité d'une dimension qui est déjà en base 1
va forcément créer des problème
au début vous l'avez vu je le faisait conditionnellement if lbound= x then lbound=newbaseelse ....

il faut savoir que les additions logiques sont plus performantes
c'est pour cela que j'utilise "sgn" qui me donne l'indice en plus ou en moins en une même ligne et même instruction
autrement dit le +1 ou -1 ou +0 est calculé par sgn seulement deux fois

combien de différence y a t-il entre sgn(lbase-lbound(t)) et +1,qui justifierait de s'en priver
afin d'avoir un truc polyvalent

après tout ces tests je vais être honnête mis a part que avec ses api on descend jusqu'au nano
ça n'a pas très grand intérêt (a mon grand regret ) car j'ai trouvé que sa structure en terme de code était bien faite c'est un peu une déception je dois dire
comme quoi encore une fois tout ce qui brille n'est pas d'or

mapomme · 12 Décembre 2024

patricktoulon à dit:
c'est un peu une déception je dois dire

Ben non ! Allons, allons ! Faut pas voir ça de cette façon.

On est parti d'un code, on l'a testé dans tous les sens et on en est arrivé à penser que ça n'a pas un intérêt majeur malgré sa beauté intrinsèque et les promesses qu'il nous laissait entrevoir. Alors comme le dit le dicton, 'l'habit ne fait pas le moine'. Mais on a au moins révélé qui se cache sous ces habits monacaux et attrayants. La conclusion est tout aussi intéressante que celle qu'on espérait prouver et le chemin pour y parvenir a été fructueux.
Et surtout, vous avez fait chauffer vos neurones ; rien que pour ça, ça en valait la peine. Je dis 'vous' car pour ma part avec le peu de neurones qu'il me reste, je les ménage le plus possible (et je sais très bien le faire ).

A+

patricktoulon · 12 Décembre 2024

bon ben encore un test qui confirme le shema(moins/plus/moins/plus)
et là je le fait avec mon benchmark perso il est similaire au microtimer dans un module
sauf que j'ai le commentaire

VB:

Sub testbasechange()
    fois = Range("Tableau1").Cells(2, 3)
    newbase1 = Range("Tableau1").Cells(1, 4)

    StartExecTimeMonitoring "test sgn", report, debut, 1
    For i = 1 To fois
        b1 = Range("Tableau1").Cells(1, 2): b1 = b1 + Sgn(newbase - b1)
        b2 = Range("Tableau1").Cells(2, 2): b2 = b2 + Sgn(newbase - b2)
    Next

    StopExecTimeMonitoring "fin test sgn ", report, debut, 1
    MsgBox report

End Sub
Sub testbasechange2()
    fois = Range("Tableau1").Cells(2, 3)
    newbase1 = Range("Tableau1").Cells(1, 4)

    StartExecTimeMonitoring "test sgn", report, debut, 1
    For i = 1 To fois

        b1 = Range("Tableau1").Cells(1, 2): b1 = b1 + Sgn(newbase - b1)
        b2 = Range("Tableau1").Cells(2, 2): b2 = b2 + Sgn(newbase - b2)
        If newbase > b1 Then
            b1 = b1 + newbase
        Else
            b1 = b1 - newbase
        End If

        If newbase > b2 Then
            b2 = b2 + newbase
        Else
            b2 = b2 - newbase
        End If

    Next

    StopExecTimeMonitoring "fin test sgn ", report, debut, 1
    
    MsgBox report

End Sub

un coup le 2d prend le temps du premier l'autre coup c'est l'inverse et cela sans exception
donc en plus de l'erreur d'index dans le dico du cbenchmark
au niveau de ces api même dans une simple sub sans classe et tout le toutim , on a probablement un soucis en multi thread

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

p'tit vieux

XLDnaute Occasionnel

Dudu2

XLDnaute Barbatruc

Pièces jointes

jurassic pork

XLDnaute Occasionnel

Dudu2

XLDnaute Barbatruc

Dudu2

XLDnaute Barbatruc

Pièces jointes

Dudu2

XLDnaute Barbatruc

Pièces jointes

patricktoulon

XLDnaute Barbatruc

jurassic pork

XLDnaute Occasionnel

mapomme

XLDnaute Barbatruc

jurassic pork

XLDnaute Occasionnel

mapomme

XLDnaute Barbatruc

Dudu2

XLDnaute Barbatruc

Dudu2

XLDnaute Barbatruc

Pièces jointes

patricktoulon

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Discussions similaires