Comparer des lignes pour compter les doublons
Bonjour à tous.
Voilà mon soucis, j'ai un fichier de base de donnée excel avec des lignes remplies de critères caractérisant la référence se trouvant dans la première case ( colonne A) de cette même ligne. Au total j'ai 27 colonnes comportant les 26 critères possibles + la colonne (A) de la référence (mais tous les critères ne sont pas remplis à chaque fois), et ça sur 1700 lignes ...
je vous joins une version de mon fichier pour que ça soit plus claire :
Le but est de savoir combien de critères (sur les 26 colonnes) sont similaire entre plusieurs références de la colonne A (autrement dit entre plusieurs lignes).
Ensuite pour ce qui est de ce que je veux obtenir, l'idéal me parait impossible mais d'autres solutions pourraient faire l'affaire :
- L'idéal : Peut-être avec des filtres, il faudrait que le fichier me ressorte les lignes comportant le plus de similitudes dans tous le tableau. Autrement dit que chaque ligne soit comparée à toutes les autres, et qu'en résultat il filtre où trie des plus similaires aux moins similaires. J'ai déjà du mal à imaginer comment les résultats pourraient être présentés et que ça reste cohérent mais bon on sait jamais !
- Sinon, même principe mais juste pour comparer 1 ligne avec toutes les autres. Savoir en sélectionnant une ligne, quelles sont les lignes les plus similaire si possible aussi en triant ou filtrant (ou pas, tant que y a une solution)
-En gros soit une macro qui vient me donner directement la solution en calculant/comparant tout le tableau, soit une macro où une formule qui vient me donner le résultat pour une ligne, en comparant cette ligne à toutes les autres et me disant pour chaque ligne le nombre de similitudes avec la ligne recherchée.
Voilà, merci d'avance à ceux qui prendront du temps, et bon courage !
Pour compter le nombre de lignes qui ont la même valeur, c'est la fonction nb.si()
Pour compter le nombre de lignes qui ont les mêmes valeurs (recherche multi-critères), c'est la fonction nb.si.ens()
Avec ces fonctions, et des petits + entre les fonctions, il doit être possible de créer simplement un ou plusieurs indicateurs de redondances.
Sinon, après, on peut faire les choses proprement et calculer des ki², mais c'est sans doute de l'overkill.
Bonjour Peuwi et merci pour ta réponse.
En effet avec nb.si je peux comparer une ligne avec toutes les autres, dans la dernière colonne du tableau, en tirant la formule suivante :
=NB.SI($3:$3;B2)+NB.SI($3:$3;C2)+NB.SI($3:$3;D2)+NB.SI($3:$3;E2)+NB.SI($3:$3;F2)+NB.SI($3:$3;G2)+NB.SI($3:$3;H2)+NB.SI($3:$3;I2)+NB.SI($3:$3;J2)+NB.SI($3:$3;K2)+NB.SI($3:$3;L2)+NB.SI($3:$3;M2)+NB.SI($3:$3;N2)+NB.SI($3:$3;O2)+NB.SI($3:$3;P2)+NB.SI($3:$3;Q2)+NB.SI($3:$3;R2)+NB.SI($3:$3;S2)+NB.SI($3:$3;T2)+NB.SI($3:$3;U2)+NB.SI($3:$3;V2)+NB.SI($3:$3;W2)+NB.SI($3:$3;X2)+NB.SI($3:$3;Y2)+NB.SI($3:$3;Z2)+NB.SI($3:$3;AA2)
Ici je compare la ligne 3.
Maintenant ce que j'aimerais pouvoir faire, c'est une macro qui viendrait appliquer cette formule pour toutes les lignes : première colonne avec la formule (tirée jusqu'en bas) comparant la première ligne a toutes les autres, dans la 2eme colonne la formule qui compare la 2eme ligne avec toutes les autres, 3eme colonne pour la 3eme lignes etc... Pour obtenir une matrice comme l'image ci-jointe
Je suis un néophyte en matière de macro mais il me semble que ça doit pouvoir se faire ! Si quelqu’un a des pistes je prend.
Merci d'avance
Thomas
J'ai trouvé une solution bien différente mais plus efficace, que j'ai adapté à mon fichier.
Merci à Yvouille pour la solution qu'il avait apporté ici : https://forum.excel-pratique.com/excel/comparer-plusieurs-lignes-a-plusieurs-lignes-et-afficher-des-t46589.html
Voilà sa solution adaptée à mon fichier :
La différence majeure est que toutes mes données sont sur un même tableau & dans une même feuille et je veux comparer chaque lignes les unes avec les autres, alors que la solution originale compare 2 tableaux dans 2 feuilles différentes. Du coup j'ai juste dupliqué mon tableau et c'est bon.
Dans le fichier que je joins j'ai appliqué la macro à 100 lignes et ça fonctionne sans trop ramer, mais j'ai essayé sur 250 et +, et là, Excel plante à coup sûr.
Alors je voudrais savoir si plutôt que de dupliquer mon tableau sur 2 feuilles je ferais pas mieux d'adapter la macro sur une seule feuille ? Elle devrait être moins lourde et du coup faire moins planter Excel non ?
Et je suis en train d'essayer d’intégrer une soustraction des cases vides à la variable compteur. Dans Excel la formule à soustraire sur la ligne 2 c'est =26-NBVAL(B2:AA2)
Alors il me semble que ça doit ressembler à ça : Compteur = Compteur - (26 - (Application.CountA([Cells(i, 2):Cells(i, 27])))
Mais déjà je ne suis pas sûr qu'elle soit juste, mais je ne suis pas sûr non plus de l'endroit où la placer (j'aurais dit soit à la place de Compteur = 0 ou alors juste après). Parce quand j'essaye ça me dit dépassement de capacité en me surlignant la ligne Compteur = Compteur + 1
J'ai aussi essayer en rentrant la variable Nbvide (ci-après les parties du codes affectées):
Sub xx()
Dim i As Integer, j As Integer, k As Integer
Dim DerLig_1 As Integer, DerLig_2 As Integer, Nbvide As Integer, Compteur As Byte
Dim DerCol As Integer, Réserve_1 As String, Réserve_2 As Byte
Nbvide = 26 - (Application.CountA([Cells(i, 2):Cells(i, 27]))
Compteur = 0
Compteur = Compteur - NbvideLa macro ne bug pas mais ça ne fonctionne pas non plus
Et sinon par curiosité je me demande qu'elle est la différence entre ces 2 lignes :
DerLig_1 = .Range("A" & Rows.Count).End(xlUp).Row
DerLig_2 = Range("A" & Rows.Count).End(xlUp).Row
Qu'est ce que ça change de mettre .Range au lieu de Range ? Déjà j'imagine qu'avoir 2 variables pour les lignes (i et J / derlig_1 et derlig_2) c'est dû au croisement de 2 tableaux venant de 2 feuilles différentes, donc j'imagine que c'est lié ?
Merci d'avance à ceux qui pourront m'aider
Cordialement.
Thomas
Salut Thomas,
Je rebondis sur ta demande en privé.
Parfois, afin d’expliquer un problème, il vaut mieux un fichier avec 10-20 lignes représentatives et avec des données faciles à repérer, plutôt qu’un fichier de 10'000 lignes avec des données à foutre la migraine même aux garçons.
Peux-tu alors nous fournir un fichier de cet ordre dans lequel tu placerais des références dans la colonne A du genre 1, 2, 3, etc. ou A1, A2, B1, B2, etc. et des références correspondantes dans les colonnes B à AA ? Il ne resterait alors plus qu’à nous expliquer – peut-être par un exemple sur une feuille séparée – le résultat attendu ?
Ceci nous permettrait de mieux comprendre ce que tu as voulu dire par ton texte ci-dessous :
ThomasJ a écrit :Le but est de savoir combien de critères (sur les 26 colonnes) sont similaire entre plusieurs références de la colonne A (autrement dit entre plusieurs lignes).
Ensuite pour ce qui est de ce que je veux obtenir, l'idéal me parait impossible mais d'autres solutions pourraient faire l'affaire :
- L'idéal : Peut-être avec des filtres, il faudrait que le fichier me ressorte les lignes comportant le plus de similitudes dans tous le tableau. Autrement dit que chaque ligne soit comparée à toutes les autres, et qu'en résultat il filtre où trie des plus similaires aux moins similaires. J'ai déjà du mal à imaginer comment les résultats pourraient être présentés et que ça reste cohérent mais bon on sait jamais !
Cordialement.
Salut Yvouille et merci,
Du coup voilà mon fichier un peu plus détaillé, les infos sont dans feuil2 :
J'ai surtout changé les étiquettes de colonnes et de lignes, auxquelles j'ai rajouté quelques commentaires pour qu'on comprenne de quoi il s'agit :
Mon fichier prend place dans un contexte de logistique de production, on vient retracer toute la "recette" (qu'on appelle Nomenclature) des produits finis (Ici, des poêles).
Donc pour chaque poêle dont la référence est inscrite dans la colonne A (Ref N°1, Ref°2 ...) on a sa recette qui est déclinée sur toute la ligne, depuis la matière première jusqu'au produit fini.
Donc dans chaque colonne se trouve des suites de chiffres (ou références) correspondantes à des pièces (que je n'ai pas changé ici). Cf mes commentaires dans le fichier : des poignées, des emballages, des produits de revêtement anti-adhésif, peinture ...
L'objectif de la macro est de déterminer quelles sont les poêles les plus similaires entre elles (en comparant leur recette) afin de réduire notre diversité en "fusionnant" des poêles très similaire, afin de simplifier nos contraintes de production. (moins de diversité, plus de facilité !)
Voilà qui devrait clarifier le fond.
Pour la forme, comme je le disais dans mon premier post, je ne savais même pas vraiment comment les résultats pouvaient être présentés, c'est pourquoi j'avais proposé une sorte de tableau à double entrée venant croiser chaque références les unes avec les autres :
Mais depuis que j'ai vu ta solution Yvouille, j'ai arrêter de chercher car c'est ce qu'il me faut : Avoir à la fin de mon tableau, pour chaque ligne, les références les plus similaires à celle présente en colonne A, et qui sont en plus triées de la plus similaire à la moins similaire, c'est parfait. Et cette partie la marche parfaitement avec ta macro adaptée à mon fichier.
Donc maintenant ce que je cherche c'est :
- Comment alléger un peu la macro qui fait planter Excel quand je dépasse 150 lignes (si c'est possible) : avoir toutes les données sur une seule feuille? (vu que de toute façon c'est les mêmes donnés que j'ai dupliquées pour m'adapter à la macro) Ou définir le périmètre de mon tableau dans la macro ? (j'ai vu dans un cours VBA du site que ça facilitait les calcules)
- "Soustraire" les cases vides du tableau aux résultats de la variable "compteur":
Par exemple pour la lignes 3 (Ref N°2, voir les parties encadrées en rouge) le résultat de la macro nous dit qu'elle a 17 similitudes avec la ref N°19. Or en réalité elles n'ont que 5 pièces en commun, et 12 case vides situées dans les même colonnes, et qui sont considérées comme similaire dans la macro.
Donc mon idée était de simplement soustraire le nombre de cases vides, mais je me rend compte que ce n'est pas si simple.
Ca marche seulement si les 2 refs comparées ont les même cases vides aux même endroits (ce qui est le cas dans mon exemple)
Donc à part trouver un moyen de dire à la macro que 2 cases vides ne sont pas égales, ou que quand les 2 cases comparées sont vide ca n'ajoute rien au compteur, j'ai pas d'autres solutions.
Voilà, bon courage
Salut,
Dans le fichier ci-joint, j’ai placé quelques données spéciales dans les plages H3:03 et E49:K50 de la Feuil1 afin de pouvoir faire quelques contrôles (à savoir contrôler si les premières et les dernières lignes sont bien traitées correctement).
J’ai ensuite réduit ce fichier à 50 lignes afin de faire mes essais dans un temps acceptable. Si je lance la macro placée derrière le bouton « Go » en G1 de la Feuil1, il faut environ 8 secondes sur ma machine assez lente afin de traiter ces 50 lignes.
Ces lignes sont traitées de la manière suivante : Une ligne après l’autre est comparée avec les suivantes (par exemple s'il n'y avait que 4 références : Réf. 1 est comparée avec Réf. 2, Réf. 3 et Réf. 4, puis Réf. 2 est comparée avec Réf. 3 et Réf. 4, puis Réf. 3 est comparée avec Réf. 4). Si plus de 6 occurrences sont trouvées entre deux lignes, elles sont reprises les deux sur la Feuil2 et le nombre s’occurrences est inscrit dans la colonne AB de la Feuil2 (Mais on pourrait décider de reporter ces lignes "Si plus de 5 occurrences sont trouvées" ou "Si plus de 8 occurrences sont trouvées", etc. Par contre ce choix n'a pas d'influence sur la durée de traitement total de la macro). La première ligne comparée avec les suivantes est inscrite en vert, les suivantes en noir.
Par exemple : la Réf. 1, est traitée et on trouve plus de 6 occurrences avec les Réf. 2 et Réf. 3. La Réf. 1 est inscrite sur la Feuil2, ELLE EST MARQUEE EN VERT ET SA NOMENCLATURE COMPLETE EST REPRISE. Les Réf. 2 et Réf. 3 sont quant à elles inscrites en-dessous, en noir, et seuls les occurrences correspondant à la nomenclature de la Réf. 1 sont indiquées (Sur la Feuil2, en B3 et B4, il n’y a pas de concordance avec B2. En H3, il y a une concordance avec H2, mais pas en H4. En I3 et I4, il y a des concordances avec I2. Etc., etc. Toujours sur la Feuil2, je constate par exemple qu’il y a 12 concordances entre les Réf. 1 et Réf. 2 et 11 concordances entre les Réf. 1 et Réf. 3).
On pourrait facilement faire apparaitre sur la Feuil2 des références ayant moins de concordances entre elles, mais tu aurais alors un nombre de résultats tellement grand que ça deviendrait selon moi ingérable.
Afin de mieux voir la différence entre ces résultats, on pourrait par exemple placer des bordures avant chaque nouvelle ligne verte, ou inscrire les lignes vertes avec une police bien plus grosse ou que sais-je d’autre. Ou alors on pourrait éventuellement trouver un système de filtre qui permettrait de ne voir que certains résultats. Mais ça fait partie des détails à régler par la suite.
A chaque lancement de la macro, les données en places sur la Feuil2 sont à chaque fois effacées et reprises à zéro.
Et voici le point sensible : Si tu dois traiter 1600 lignes, ça risque de ramer passablement, bien que ça dépende énormément de la machine sur laquelle tu lances cette macro. Mais d’un autre côté, si tu dois traiter ces données qu’une seule fois ou exceptionnellement, est-ce bien grave si le traitement dure plusieurs minutes, voire 1 ou 2 heures ? Toujours si ce traitement ne doit être fait qu’exceptionnellement, tu pourrais par exemple - sans oublier de désactiver la mise en veille automatique - lancer la macro avec tes 1600 données, partir boire l’apéro avec les copains et revenir après déjeuner (saut si tu es Suisse, auquel cas tu reviendras après diner
Par contre, si tu ne peux pas attendre aussi longtemps, il faudra revoir la chose (par exemple que j’essaie cette macro à mon travail, sur une machine plus rapide).
Amicalement.
Magnifique
Déjà la macro est bien plus légère et même si je la lance sur bcp de lignes (+ de 200) elle met du temps mais ne fait pas freeze Excel comme avant
J'ai donc laissé tourner la bête sur 1000 références entre midi et deux et ça a bien dû mettre 2h mais ça a aboutit à un résultat cohérent. Et de toute façon, comme tu l'as fait remarqué, la macro n'a pas vocation à tourner tous les jours au contraire, donc que ça prenne du temps tant que ça y va, on s'en fou
Mais revenons-en à la présentation générale des résultats, c'est juste parfait : pas de problèmes pour les cellules vides qui ne sont plus prises en compte, tous les compteurs sont dans la même colonne (ce qui est bien plus commode qu'avant), et on a de façon visuelle et immédiate les points communs et les points de différenciations de plusieurs références déjà filtrées comme similaires (grâce au minimum de 6 intégré dans la macro) !
Franchement bravo pour ta solution directement opérationnelle.
J'ai réussi sans problèmes à adapter pour ce qui est de la présentation (font size un peu plus gros et bold) et même pour le minimum. A voir plus tard pour insérer des filtres mais pas sur que ça soit nécessaire.
Encore
Cordialement.
Thomas