Chargement rapide de dictionaire en mémoire sur la base de cellules Excel

[elgringo123456]

Bonjour,

Je me permets de vous solliciter sur l'usage des dictionnaires.
Je finis par tout penser en dictionnaire car cela me facilite énormément la tâche et évite des ralentissements exponentielles dés qu'une taille est atteinte et d'éviter d'avoir des doubles boucles for ou plus inutiles.

La question que je me pose est la suivante : comment optimiser au mieux (au plus vite) le chargement en mémoire d'un dictionnaire sur la base de cellules d'un fichier excel ?

Pour info il y a en pièce jointe un fichier Excel à ce sujet mettant en évidence au final que la procédure "LoadindDataM1" est plus rapide que "LoadindDataM2" car à priori l'evaluation s'effectue sur une plage de la cellule et non cellule par cellule. Est-ce bien interpreté de ma part ?

Par contre je constate le chargement à la chaîne par la ligne "TableauxTMP = Range("A1:A100000").Value" est beaucoup beaucoup plus rapide que d'effectuer un chargement en dictionaire élement par élément (via ".Add")

La question que je me pose : est-il possible d'effectuer un chargement de dictionnaire "presque à la même vitesse" que le chargement d'un tableau sur la base d'une plage de cellules ?
En effet on peut tranférer tres rapidement de Dico à Dico via "Set" à priori. En fait je cherche une astuce qui me permettrait d'effectuer un ".Add" sur une plage de cellule et non de réaliser une boucle "élément par élément" chargeant les données.
En regardant de prêt je constate qu'au niveau de l'écriture (et non chargement en mémoire) il est possible de "pluger" le contenu total du dico en jouant avec ".Items" ("Transpose" eventuellement) et un Range de cellule.

Auriez-vous une petite idée à ce sujet ?

En pièce jointe j'ai fait exprès de raccourcir la taille du fichier et ne traiter que 10 000 lignes (limitation de la taille du fichier) mais histoire de constater la différence, il est mieux d'entendre l'étude à 100 000 lignes en générant par formules puis en figeant les données et étendant en VBA l'étude à 100 000 lignes.
Désolé de la gêne occasionnée.

Merci d'avance,
Cordialement.
ElGringo123456

 

[david84]

Re : Chargement rapide de dictionaire en mémoire sur la base de cellules Excel

Bonjour,
Ton exemple ne se prête pas vraiment à l'utilisation d'un dictionnaire car il n'y a pas de doublon, et donc autant le charger directement dans un tableau comme tu le fais dans LoadindDataM1.
D'ailleurs en l'état les 2 macros plantent lorsque tu places un doublon (logique).

Pour éviter le plantage tu dois donc préalablement tester si la clé n'est pas déjà associée à un élément de la collection :

For i = LBound(TableauxTMP) To UBound(TableauxTMP)        
     If Not DicoTMP.Exists(TableauxTMP(i, 1)) Then DicoTMP.Add TableauxTMP(i, 1), "1"
Next i

Pour info il y a en pièce jointe un fichier Excel à ce sujet mettant en évidence au final que la procédure "LoadindDataM1" est plus rapide que "LoadindDataM2" car à priori l'evaluation s'effectue sur une plage de la cellule et non cellule par cellule. Est-ce bien interpreté de ma part ?

C'est plus rapide parce que la plage de cellules est préalablement chargée dans un tableau VBA (array) :

TableauxTMP = Range("A1:A1048576").Value

En fait je cherche une astuce qui me permettrait d'effectuer un ".Add" sur une plage de cellule et non de réaliser une boucle "élément par élément" chargeant les données.

L'un des intérêts d'un dictionnaire est de charger une collection où chaque élément est lié à une clé qui lui est propre, donc pour ce faire il faut préalablement de chaque item soit vérifié, d'où l'utilisation impérative d'une boucle.

Tu peux éventuellement éviter cela en utilisant un module de classe où le dictionnaire disposerait d'une fonction te permettant de charger tout d'un coup mais cela n'éviterait pas de passer par une boucle, c'est simplement le module de classe qui s'en chargerait.

Attendons de voir la réaction d'autres personnes...

A+

 

[Efgé]

Re : Chargement rapide de dictionaire en mémoire sur la base de cellules Excel

Bonjour elgringo123456, Bonjour david84

Si je puis me permettre et tout en plussoyant les propos de david :
Le plantage rencontré en cas de doublon viens de ll'utilisation de

Dim DicoTMP As New Dictionary

qui oblige a vérifier la présence antérieur de la valeur.L'utilisation d'un objet créé supprime ce besoin de vérification

Sub LoadindDataM3()
Dim TableauxTMP As Variant
Dim i As Long
Dim DicoTMP As Object
Dim StartChrono As Double

StartChrono = Timer
Set DicoTMP = CreateObject("scripting.dictionary")
TableauxTMP = Range("A1:A20000").Value ' GAIN ++

For i = LBound(TableauxTMP) To UBound(TableauxTMP)
DicoTMP(TableauxTMP(i, 1)) = 1
Next i

Set TableauxTMP = Nothing
MsgBox "Temps : " & CStr(Timer - StartChrono) & " Secondes"
End Sub

Sinon, il n'est pas possible, a ma connaissance, de charger une plage directement en .Keys d'un dico.

Cordialement

 

[david84]

Re : Chargement rapide de dictionaire en mémoire sur la base de cellules Excel

Le plantage rencontré en cas de doublon viens de ll'utilisation de
Code :

Dim DicoTMP As New Dictionary

Bonjour Efgé,
non je ne crois pas. C'est plutôt la syntaxe

DicoTMP(TableauxTMP(i, 1)) = 1

qui permet de se passer de la méthode Exists du dictionnaire.

Sub LoadindDataM4()Dim TableauxTMP As Variant
Dim i As Long
Dim DicoTMP As New Dictionary 'ou DicoTMP as Dictionary
Dim StartChrono As Double


StartChrono = Timer
'Set DicoTMP = CreateObject("scripting.dictionary")
'Set DicoTMP = New Dictionary
TableauxTMP = Range("A1:A1048576").Value ' GAIN ++


For i = LBound(TableauxTMP) To UBound(TableauxTMP)
  DicoTMP(TableauxTMP(i, 1)) = 1
Next i


Set TableauxTMP = Nothing
MsgBox "Temps : " & CStr(Timer - StartChrono) & " Secondes"
End Sub

J'ai failli proposer cette syntaxe puis je me suis ravisé et repris l'utilisation de la méthode Add comme proposé par le demandeur.

A+

 

[elgringo123456]

Re : Chargement rapide de dictionaire en mémoire sur la base de cellules Excel

Hello david84, Efgé,

Tout d'abord je tiens à vous remercier de la réactivité.
Merci également du contenu des réponses.

Juste pour info je souhaitais optimiser à fond le temps de chargement d'un dico.

Merci encore et bonne journée
Cordialement.
ELgringo123456

 

[david84]

Re : Chargement rapide de dictionaire en mémoire sur la base de cellules Excel

Bonjour,

sur une plage de 1 048 576 lignes contenant 148 576 valeurs uniques :
- avec la syntaxe

For i = LBound(TableauxTMP) To UBound(TableauxTMP)  
   DicoTMP(TableauxTMP(i, 1)) = 1
Next i

le temps de traitement est d'environ 2,50 sec

-avec la syntaxe

For i = LBound(TableauxTMP) To UBound(TableauxTMP)        
   If Not DicoTMP.Exists(TableauxTMP(i, 1)) Then DicoTMP.Add TableauxTMP(i, 1), "1"
Next i

le temps de traitement est d'environ 2,70 sec

Tests faits sur Excel 2010 64 bits/Windows 10
A+

 

[BOISGONTIER]

Re : Chargement rapide de dictionaire en mémoire sur la base de cellules Excel

Bonjour,

>La question que je me pose est la suivante : comment optimiser au mieux (au plus vite) le chargement en mémoire d'un dictionnaire sur la base de cellules d'un fichier excel ?

Il ne faut pas rêver.

Même s'il existait une syntaxe pour alimenter un dictionnaire avec un champ comme on alimente un Array, le temps ne serait pas optimisé.

-Un dictionnaire n'est pas organisé comme un Array (une suite continue qui permet d'accéder à un élément par un indice très rapidement).
-Pour accéder par une clé aussi rapidement , c'est la technique du hash-code qui doit être utilisée: on calcule une adresse de rangement d'une clé et de son item par algorithme. Ceci permet de retrouver l'item d'une clé directement sans balayer toute une table.

A la création du dictionnaire, les couples clé+item doivent être traités un par un.

JB

 

[elgringo123456]

Re : Chargement rapide de dictionaire en mémoire sur la base de cellules Excel

Merci david84 & BOISGONTIER,

Vous m'avez fait un cqfd => pour identifier ma problématique et la résoudre
Je n'ai rien à dire de votre niveau si ce n'est que vous êtes des "Barbatrucs" en Excel

A bientôt
El Gringo 123456