Optimisation macro recherche de valeurs sur deux critères
cletess
Messages postés
38
Date d'inscription
Statut
Membre
Dernière intervention
-
Patrice33740 Messages postés 8561 Date d'inscription Statut Membre Dernière intervention -
Patrice33740 Messages postés 8561 Date d'inscription Statut Membre Dernière intervention -
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:
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 !!
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 Integer
Dim j As Integer
For 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 j
Next i
End 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 !!
A voir également:
- Optimisation macro recherche de valeurs sur deux critères
- Optimisation pc - Accueil - Utilitaires
- Telecharger macro convertir chiffre en lettre excel - Télécharger - Tableur
- Recherche automatique des chaînes ne fonctionne pas - Guide
- Comment faire deux colonnes sur word - Guide
- Nombre de jours entre deux dates excel - Guide
5 réponses
Bonjour,
pas le top, mais pas mal:
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 - t
End Sub
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
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
Bonjour f894009,
Eh, oui !!!!
et en plus c'est bien plus rapide :
Cordialement
Patrice
Eh, oui !!!!
et en plus c'est bien plus rapide :
Sub test_vitesse()
t = Timer
Sheets("Tab2").Cells(2, 3).FormulaLocal = "=INDEX('Tab1'!E:E;SOMMEPROD(" & _
"('Tab1'!$B$2:$B$30000=A2)*('Tab1'!$I$2:$I$30000=B2)*LIGNE('Tab1'!$B$2:$B$30000)))"
Sheets("Tab2").Cells(2, 3).AutoFill Destination:=Sheets("Tab2").Range("C2:C490")
Sheets("Tab2").Range("C2:C490").Value = Sheets("Tab2").Range("C2:C490").Value
MsgBox "temps: " & Timer - t
End Sub
Cordialement
Patrice
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 !!!!!
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 !!!!!
Re,
Changement integer en Long, pas Double (nombre a virgule).
Pour les anomalies avec formules, donnez des exemples.
Changement integer en Long, pas Double (nombre a virgule).
Pour les anomalies avec formules, donnez des exemples.
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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 !
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 !