Comparaison de bdd utilisateurs
Bonjour,
Pour commencer je vais me présenter rapidement.
Je m'appelle Olivier, je suis informaticien junior et j'ai été recruté récemment pour une mission dans un groupe très connu de la grande distribution.
Pour présenter rapidement le contexte :
Mon travail consiste a analyser les différents référentiels utilisateurs afin d'étudier les anomalies des bases de données et d'en améliorer la qualité. ( compte mal renseigner / doublons ... )
Pour traiter cette demande, vu mon manque de connaissance/d'accès aux bases de données de l'entreprise, il a été décidé que je travaillerais a l'aide d'Excel (export bdd au format CSV).
Dans le cas des doublons, ce qui va nous intéresser dans ce poste, j'ai donc mit en place un fichier excel avec une concaténation de trois critères (Login/Matricule/date de naissance) par utilisateur au sein d'une même cellule, et ce pour les deux référentiels.
Ensuite j'applique une macro trouvé sur internet ( je ne pratique pas le VBA) qui permet de comparer la première colonne du premier onglet avec la première colonne du second onglet, et d'y appliquer une mise en forme qui me permet ensuite de filtrer, et de ressortir les différences d'une base de donnée a une autre.
Le problème rencontré est le suivant, lorsque je doit comparer les deux référentiels de base, contenant plus de 200 000 lignes chacun, la macro fait planter excel, celui ci se retrouve en mode " ne répond pas "
Sur des plus petits fichiers au bout d'une 30aines de minutes le logiciel revient a la normal et la demande est traitées, mais pour les plus gros, même après 24h de traitement sur mon PC personnel ( nettement plus puissant que ceux du boulot ) Excel ne répond toujours pas.
Voici la macro en question :
Sub Compare()
Dim Lig1 As Long, Derlig1 As Long, Derlig2 As Long, Cp As Variant
Dim Lig2 As Long
Derlig1 = Sheets("Feuil1").Range("A265535").End(xlUp).Row
Derlig2 = Sheets("feuil2").Range("A265535").End(xlUp).Row
With Sheets("Feuil2")
For Lig1 = 2 To Derlig1
Cp = Sheets("feuil1").Cells(Lig1, "A")
For Lig2 = 2 To Derlig2
If Cp = .Cells(Lig2, "A") Then
.Cells(Lig2, "A").Interior.ColorIndex = 9 'couleur valeurs similaires
End If
Next Lig2
Next Lig1
End With
End SubJe m'en remet donc a vous afin d'obtenir de l'aide sur une amélioration potentiel de la macro afin de la rendre plus légère, ou qu'elle travail différement pour améliorer la durée de traitement ( ou au moins pouvoir traiter le fichiers sans plantage même si c'est long )
Si vous avez d'autre piste, plutot qu'une macro excel, pour le traitement de ce genre d'informations je suis également prenneur de toutes informations pertinente.
Merci de votre aide
Pavé César !
Cordialement
Bonjour,
peux tu envoyer ici un exemple au plus proche (niveau structure-présentation) de tes vrais fichiers et expliquer dedans par des couleurs, ce que tu gardes, ce que tu élimines, etc etc...
Il y a assez de monde ici pour t'aider
P.
LeReclus a écrit :Si vous avez d'autre piste, plutot qu'une macro excel, pour le traitement de ce genre d'informations je suis également prenneur de toutes informations pertinente.
Avé !
Sans VBA ... tu peux comparer les onglets avec un NB.SI par exemple, qui te donneras si les valeurs sont présentes dans l' "autre" onglet. A mon sens c'est le plus simple.
Ensuite tu filtres ou tu mets en place une MFC.
Comme dit Patrick, un p'tit bout de fichier ne ferait pas de mal !
Hello Steelson,
notre ami écrit: "deux référentiels de base, contenant plus de 200 000 lignes chacun"
en formule ça va être lourd non ?
P.
Effectivement,
mais pas forcément rapide. Quoique, j'attends de voir.ma pomme a écrit :A mon sens c'est le plus simple.
Bien sûr, s'il faut faire un EQUIV en matriciel on va ramer ... dans ce cas un TCD pourrait faire l'affaire aussi avec ensuite une comparaison de 2 TCD ou une restriction du champ de comparaison si les listes sont triées.
Reste aussi un VBA optimisé, sans balayer toute la liste avec chaque référence de la seconde liste !
Les possibilités sont multiples, on pourrait même faire un concours de vitesse.
Bonjour,
une proposition qui fait l'hypothèse qu'il n'y a qu'une seule colonne et que l'on peut trier les 2 onglets. pour passer d'un temps d'exécution proportionnel à derlig1 * derlig2 à un temps proportionnel à derlig1 + derlig2
Sub Compare()
Dim c1 As Long, Derlig1 As Long, Derlig2 As Long, Cp As Variant
Dim c2 As Long,fin1,fin2,k1,k2
Set ws1 = Sheets("feuil1")
Set ws2 = Sheets("feuil2")
Application.ScreenUpdating = False
Derlig1 = ws1.Range("A265535").End(xlUp).Row
Derlig2 = ws2.Range("A265535").End(xlUp).Row
ws1.Range("A1:A" & Derlig1).Sort key1:=ws1.Range("A1"), order1:=xlAscending, Header:=xlYes
ws2.Range("A1:A" & Derlig2).Sort key1:=ws2.Range("A1"), order1:=xlAscending, Header:=xlYes
c1 = 2
c2 = 2
While Not (fin1 Or fin2)
k1 = ws1.Cells(c1, 1)
k2 = ws2.Cells(c2, 1)
If k1 = k2 Then
ws1.Cells(c1, "A").Interior.ColorIndex = 9 'couleur valeurs similaires
ws2.Cells(c2, "A").Interior.ColorIndex = 9 'couleur valeurs similaires
c1 = c1 + 1: c2 = c2 + 1
ElseIf k1 < k2 Then
c1 = c1 + 1
If c1 > Derlig1 Then fin1 = True
Else
c2 = c2 + 1
If c2 > Derlig2 Then fin2 = True
End If
Wend
Application.ScreenUpdating = True
End SubLe problème de la macro est bien ici :
For Lig1 = 2 To Derlig1
' ___________
For Lig2 = 2 To Derlig2
' _________________soit 200.000 x 200.000 = 40.000.000.000
avec un tri des items, on doit bien diviser par 1.000.000
edit : ben voilà, pendant qu'on jacquetait de la pluie et du beau temps, un coup d'acide sulfurique et hop c'est solutionné !
Steelson a écrit :Les possibilités sont multiples, on pourrait même faire un concours de vitesse.
tu as gagné
P.
Oula, je ne m'attendais pas a un soutiens aussi rapide ^^
Pour répondre aux demandes d'exemple de fichiers, je vous le poste en PJ de ce message afin de vous éclairez le temps que je relise les réponses et que je test le script proposé.
Onglet 3 : Les informations centralisé qui on besoin d'etre comparé, il faut aussi prendre en compte une potentiel colonne supplémentaire avec les dates de naissances (informations sensibles donc je n'ai mit que 2 exemples dont j'ai modifié les informations )
Onglet 1 et 2 : Les informations concaténées dans la colonne A afin de pouvoir comparer une seul cellule (je n'ai pas trouvé de macro qui prenais en compte plusieurs colonne pour la comparaison )
Bonjour à tous,
Utiliser la mémoire de l'ordinateur et segmenter le travail ne serait-il pas plus judicieux?
1) Une macro qui tri les éléments, et détermine la position des éléments qui commencent par 'a', des éléments qui commencent par 'b' etc...
2) faire la recherche lettre par lettre, ce qui devrait réduire le temps de calcul
3) charger en mémoire les valeurs dans un tableau, pour ne pas lire les valeurs dans la feuille
En effet si on sait que pour la première base, les éléments qui commencent par 'a' sont de la ligne 1 à 12.000, c'est carrément plus rapidement que 200.000 lignes à traiter...
Tu es magicien ?
h2so4 a écrit :Bonjour,
une proposition qui fait l'hypothèse qu'il n'y a qu'une seule colonne et que l'on peut trier les 2 onglets.
Sub Compare() Dim c1 As Long, Derlig1 As Long, Derlig2 As Long, Cp As Variant Dim c2 As Long,fin1,fin2,k1,k2 Set ws1 = Sheets("feuil1") Set ws2 = Sheets("feuil2") Application.ScreenUpdating = False Derlig1 = ws1.Range("A265535").End(xlUp).Row Derlig2 = ws2.Range("A265535").End(xlUp).Row ws1.Range("A1:A" & Derlig1).Sort key1:=ws1.Range("A1"), order1:=xlAscending, Header:=xlYes ws2.Range("A1:A" & Derlig2).Sort key1:=ws2.Range("A1"), order1:=xlAscending, Header:=xlYes c1 = 2 c2 = 2 While Not (fin1 Or fin2) k1 = ws1.Cells(c1, 1) k2 = ws2.Cells(c2, 1) If k1 = k2 Then ws1.Cells(c1, "A").Interior.ColorIndex = 9 'couleur valeurs similaires ws2.Cells(c2, "A").Interior.ColorIndex = 9 'couleur valeurs similaires c1 = c1 + 1: c2 = c2 + 1 ElseIf k1 < k2 Then c1 = c1 + 1 If c1 > Derlig1 Then fin1 = True Else c2 = c2 + 1 If c2 > Derlig2 Then fin2 = True End If Wend Application.ScreenUpdating = True End Sub
Tu n'a pas attaché le(s) fichier(s)
En effet, la taille limité a 300k m'avait refusé le fichier sans que je ne m'en rende compte, désolé.
J'avais oublié de supprimer les cellules vide contenant la formule de concaténation.
Sinon après test la macro de h2so4 fonctionne merveilleusement bien et très rapidement... Je suis sur le c....
Merci beaucoup.
Résolu alors
P.
Yep ma problématique est résolu !
Mon fichiers n'est pas forcément très propre avec mon système de concaténation dans une seul cellule pour comparer plus simplement, mais l'essentiel c'est que j'arrive a obtenir le résultat désiré !
Vu votre acceuil chaleureu, je reviendrais vers vous s'il s'avère que d'autre problématique apparaissent lors de mon projet de "Qualité des données"
Je vais éditer le titre pour annoncé la résolution du problème.
Je vous remercie tous de votre temps de réaction et de l'aide inestimable que vous m'avez apportée =)
Bonne fin de journée amis Excéliens :p
Cordialement
Edit : Je ne comprend pas par contre l'importance de "pouvoir trier les onglets" dans la macro proposé ?
LeReclus a écrit :Edit : Je ne comprend pas par contre l'importance de "pouvoir trier les onglets" dans la macro proposé ?
voici une explication succincte : (tu te dis informaticien junior, n'as-tu pas eu un cours sur les algorithmes sur les fusions appariement de fichiers ? l'algorithme utilisé est inspiré de l'algorithme de fusion de 2 fichiers séquentiels.
si les onglets ne sont pas triés, il faut passer en revue tous les éléments du premier onglet et les comparer à tous les éléments du second onglet -> le nombre d'éléments à parcourir est le nombre d'éléments du premier onglet * nombre d'éléments du second.
exemple
o1o2
B A
A R
D T
E D
on prend B on le compare à A,R,T,D (non trouvé)
on prend A on le compare à A,R,T,D (trouvé) on aurait pu arrêter la comparaison après l'avoir trouvé.
on prend D on le compare à A,R,T,D(trouvé)
on prend E on le compare à A,R,T,D(non trouvé)
(4x4 = 16 comparaisons)
si les listes sont triées on peut avancer et en parallèle sur les 2 listes, le nombre d'éléments à parcourir est le nombre d'éléments du premier onglet + le nombre d'éléments du second.
même exemple, mais liste triée
o1o2
A A
B D
D R
E T
on prend A de O1 et A de O2 on compare
égalité on a trouvé on passe aux éléments suivants sur O1 et O2
on prend B et D on compare
B<D, on prend l'élément suivant sur O1
on prend D et D on compare
égalité on a trouvé on passe aux éléments suivants sur O1 et O2
on prend E et R on compare
E<R on prend l'élement suivant sur O1
fin de liste -> traitement terminé.
(dans ce cas-ci, 4 comparaisons)
il n'est pas possible d'appliquer cet algorithme si les 2 listes ne sont pas triées.
(Je suis en effet informaticien (bac +4), mais j'ai effectué une voie de formation dite professionnelle, et lors de mes cursus de formations je n'ai jamais rencontrer d’algorithme ou de langage de programmation, a mon grand regret je suis orienté système et réseau uniquement)
Merci pour l'explication, j'ai bien peur de comprendre le principe et que du coup cela pose problème pour ma situation...
Qu'en est il si les données sont belles et bien triée (par ordre alphabétique de type : NomPrenom-Matricule-Datedenaissance) mais qu'elle ne sont pas alignées ?
En effet dans ma première colonne du premier onglet il y a des entrées qui ne se trouvent pas dans la seconde, et vice et versa.
Du coup par exemple, Jean Édouard sera ligne 50 sur mon premier onglet, mais ligne 62 sur le second onglet, étant donnée que plus tôt dans le fichiers certaines entrées était présentes dans une colonne mais pas dans l'autre.
Cela pose t-il problème pour la macro que tu as proposé, car elle semble bien fonctionner sur mon fichiers, mais j'ai peur que des erreurs s'y soit glissé parmis les 200K lignes et que je ne les constate pas a vu d’œil.
Edit : Je parle plus vite que je ne réfléchi désolé, mais je crois que j'ai pigé en faites... ça ne devrait pas posé problème dans mon cas vu que les données sont triées par ordre alphabétique et que une fois un "doublons" trouver la comparaison se fait sur la ligne suivante dans les deux onglet, et du coup il ne risque pas de louper une ligne.
LeReclus a écrit :Qu'en est il si les données sont belles et bien triée (par ordre alphabétique de type : NomPrenom-Matricule-Datedenaissance) mais qu'elle ne sont pas alignées ?
En effet dans ma première colonne du premier onglet il y a des entrées qui ne se trouvent pas dans la seconde, et vice et versa.
Du coup par exemple, Jean Édouard sera ligne 50 sur mon premier onglet, mais ligne 62 sur le second onglet, étant donnée que plus tôt dans le fichiers certaines entrées était présentes dans une colonne mais pas dans l'autre.
Cela pose t-il problème pour la macro que tu as proposé, car elle semble bien fonctionner sur mon fichiers, mais j'ai peur que des erreurs s'y soit glissé parmis les 200K lignes et que je ne les constate pas a vu d’œil.
cela ne pose pas de problème pour la macro. Je t'invite quand même à vérifier le résultat. (un bug est toujours possible)
J'ai éditer mon message du dessus.
Merci pour ton aide en tout cas (et surtout ta patience face a mon incompréhension), je t'en suis très reconnaissant =)
J'édite le titre de suite afin d'afficher la résolution de mon problème.