Optimisation macro recherche de valeurs sur deux critères Fermé

Question

Bonjour à tous,

Je travaille actuellement sur des bases de données assez conséquentes (+-300.000 lignes en général) avec trois colonnes d'importance ("tab1)": "Condition" (avec +- 500 valeurs différentes et connues) et pour chacune des valeurs de "condition", "vitesse" fluctue également (avec chaque fois 1 et 1 seul maximum qui m'intéresse). J'ai extrait pour chacune des 500 valeurs de condition son maximum de "vitesse" et je les ai placé dans un tableau en "Tab2".

La troisième colonne d'intérêt est la colonne "temps". J'ai besoin pour chaque maximum de vitesse par condition d'avoir sa valeur correspondante dans la colonne "temps".

J'ai donc cette petite macro, partiellement fonctionnelle:

Sub test()Dim i As IntegerDim j As IntegerFor i = 2 To Sheets("Tab2").UsedRange.Rows.Count()    For j = 2 To Sheets("Tab1").UsedRange.Rows.Count()        If Sheets("Tab1").Cells(j, 2) = Sheets("Tab2").Cells(i, 1) Then            If Sheets("Tab1").Cells(j, 9) = Sheets("Tab2").Cells(i, 2) Then                Sheets("Tab2").Cells(i, 3) = Sheets("Tab1").Cells(j, 5)            End If        End If    Next jNext iEnd Sub

avec en tab 1: colonne 3 = "condition", colonne 5 = "temps" et colonne 9 = "vitesse".
avec en tab 2: colonne 1 = chaque valeur de condition, colonne 2 = son maximum de vitesse correspondant et en colonne 3, mon inconnue (la valeur de "temps" correspondante.

Pour illustration, vous pouvez trouver un exemple abrégé de cette base de donnée sur le lien sécurisé ci-joint (30 000 lignes et 62 valeurs à trouver): https://transvol.sgsi.ucl.ac.be/download.php?id=3857775247fb7c75


La macro est fonctionnelle mais limitée. Déjà, je crois savoir qu'une variable integer est limitée à +- 30 000 valeurs (or j'en ai beaucoup plus). J'ai donc tenté de changer ça en "double", ça fonctionne mais c'est TRÈS lent (si ça ne fait pas planter excel).

Après cette longue entrée en matière, la question est la suivante: serait-il possible d'optimiser cette macro pour que i) mon ordi ne frôle pas l'auto-destruction à chaque application, et ii) son application ne prenne pas 30 minutes par base de données ?

 Mes connaissances en VBA se limitent à peu près au niveau de la macro présentée ici mais je serai ravi de recevoir conseils ou indications :)

Un tout grand merci d'avance !!

f894009 · Answer

Bonjour,

pas le top, mais pas mal:

Sub test_vitesse()    Dim NbOc As Integer, Lig As Integer, Cmp As Integer, Tcol_C    Dim Plage_Ref1 As Range, Plage_Suj2 As Range, Plage_Time1 As Range, Plage_Suj1 As Range    Dim cel As Range       Application.ScreenUpdating = False          t = Timer     Derlig2 = Worksheets("Tab2").Range("A" & Rows.Count).End(xlUp).Row    'tableau time    ReDim Tcol_C(Derlig2 - 1)    'mise en memoire plage Sujet    Set Plage_Suj2 = Worksheets("Tab2").Range("B2:B" & Derlig2)     With Worksheets("Tab1")        derlig1 = .Range("A" & Rows.Count).End(xlUp).Row        'mise en memoire des Plages Tab1        Set Plage_Ref1 = .Range("I2:I" & derlig1)        Set Plage_Time1 = .Range("E2:E" & derlig1)        Set Plage_Suj1 = .Range("B2:B" & derlig1)                Lig = 1        For Each cel In Plage_Suj2            'Recherche si existe            NbOc = Application.CountIf(Plage_Ref1, cel)            If NbOc > 0 Then                For Cmp = 1 To NbOc                    'recherche ligne                    Lig = .Columns("I").Find(cel, .Cells(Lig, "I"), , xlWhole).Row                    If Plage_Suj1(Lig - 1, 1) = cel.Offset(0, -1) Then                        'Ecriture Tableau                        Tcol_C(cel.Row - 2) = Plage_Time1(Lig - 1, 1)                        Lig = Lig - 1                        Exit For                    End If                Next Cmp            End If        Next cel    End With    Worksheets("Tab2").Range("C2:C" & Derlig2) = Application.Transpose(Tcol_C)    Application.ScreenUpdating = True        MsgBox "temps: " & Timer - tEnd Sub

Patrice33740 · Answer

Bonjour,

On peut obtenir le résultat avec une simple formule en C2 :
=INDEX('Tab1'!E:E;SOMMEPROD(('Tab1'!$B$2:$B$30000=A2)*('Tab1'!$I$2:$I$30000=B2)*LIGNE('Tab1'!$B$2:$B$30000)))
à reproduire vers le bas

cletess · Answer

Bonjour à tous les deux !

Tout d'abord, merci pour votre aide et la promptitude de votre réponse !

La macro de Mr "f894009" fonctionne à merveille (en 7 malheureuses secondes pour un fichier de 245 000 lignes) et rapporte les valeurs correctes ! Je vais me pencher à la compréhension de sa structure (ce n'est pas gagné). J'ai juste du changer la définition des variables de Integer en Double pour surpasser la limitation de capacité (sans implication pour l'efficacité de la macro, suis-je correct ?).

La formule de Patrice est tout à fait intéressante parce qu'elle traite le fichier en à peine 1 seconde et est d'une simplicité à ma portée (hahaha) mais de temps en temps rapporte une valeur erronée (une valeur de temps existante mais ne correspondant pas au pic de vitesse repris en tab2). D'où pourrait provenir de telles erreurs ?

Quoiqu'il en soit, un immense merci à tous les deux !!!!!

f894009 · Answer

Re,

Changement integer en Long, pas Double (nombre a virgule).

Pour les anomalies avec formules, donnez des exemples.

cletess · Answer

Re,

Alors voilà, pour la formule: j'ai réglé ce qui semble être le problème de base: je n'avais pas adapté la taille de la plage de référence et la colonne condition n'était pas ordonnée en ordre croissant. Çà semble régler la majeure partie du problème.

Juste 3 cellules (sur 490) ont maintenant une valeur différente et il ne s'agit pas d'un doublon bizarrement (1 pic maximum ne peut être atteint qu'une seule fois !). 

Pour exemple, voilà le fichier complet sur lequel j'ai appliqué les deux outils (les cellules 307,308 et 422 sont erronées avec la formule et je n'arrive pas à comprendre la logique de cette erreur). Elle fournit les valeurs de temps pour 3 vitesses loin de leur maximum respectif. (https://transvol.sgsi.ucl.ac.be/download.php?id=b8a3000a3d83ce99

 Changement en long effectué, je vais creuser les différents types de variables un peu plus en détail !

Merci pour votre aide !

Optimisation macro recherche de valeurs sur deux critères

5 réponses

Discussions similaires