Arborescence, regroupement par groupe

Bonjour,

Je suis un nouvel arrivant et je bute sur un projet Excel et ou ACCESS:

Je dispose d'un fichier à 2 colonnes (590 000 lignes) Mon objectif est de recenser pour chaque ligne l'ensemble des cas associés

En gros si je prends une valeur dans la colonne A, je veux connaitre l'ensemble des valeurs qui y sont associées,c'est à dire chercher la valeur associée dans la colonne B ,prendre cette valeur et trouver à quelles valeurs cette dernière est associée depuis la colonne A... etc

A terme cela permet d'affecter une valeur à un ensemble de données ayant un rapport entre eux

Bonjour,

Il faudrait que tu t'expliques un peu mieux et que tu dises où inscrire les valeurs, d'où elles viennent, et pourquoi.

A coté de 600 en C2 je vois un 2

Hors, à coté de 600 en B13 je lis un 1 (???)

eric

Le fichier de départ est un fichier de cartographie dont le traitement ne peut être réalisé par les outils standards. Après une petite moulinette dans ACCESS, je me retrouve avec un fichier à deux colonnes. Chaque ligne correspond à l'intersection de deux segments

:le 1 avec le 2 , le 2 avec le 3 , le 1 avec le 600 , le 600 avec le 300...

L'objectif est de réunir l'ensemble des éléments appartenant à la même arborescence:

voila comment interpréter le fichier donné en exemple:

le 1 est lié au 2 , la réciproque est donc vraie, le 600 fait partie de l'arborescence incluant le 1, les 2,3,4,300,600

Le problème est simple comment faire en sorte que le cheminement

1=>600 =>300 => 150 soit pris en compte et que directement je puisse savoir que le tronçon 150 ou la valeur 150 appartient au même lot que la valeur 1

le 240 et 241 appartiennent à un autre groupe

La piste des recherchev est problématique car un segment ou une entité (dans mon exemple le 1) va être connecté à plusieurs troncons (couple 1-2;1-2;1-4;1-600) je perds donc des informations essentielles

)

Une fois que l'ensemble des tronçons (soit la colonne A) s'est vu affecté l'ensemble des numéros de tronçons auxquels il est relié (par voie directe ou indirecte). Je pourrai créer un identifiant unique spécifique à l'arborescence . Et le restituer dans mon fichier carto via une simple recherchev

Bonjour,

Tu as besoin de colonnes C:G ou elles ne sont là que pour l'explication ?

eric

edit:

J'ai fait un truc mais au vu du résultat une interrogation subsiste.

Pour l'instant j’établis la liste de tous les segments auquels j'attribue un ID.

Dans Résultat!I15 pourquoi 150 et non pas 1 puisqu'un lien est établi vers cet ID ?

Si c'est bien 1 la réponse (sinon éclairci), 3 choix pour retourner le résultat :

• liste de tous les segments (le terme n'est peut-être pas adequat) sur 1 colonne avec l'ID en B
• liste de tous les ID avec leurs segments sur la ligne en plusieurs colonnes
• panachage des 2 : liste de tous les segments et leurs segments reliés. Ce qui correspond à ta demande mais multiplie le nombre de lignes (est-ce utile d'avoir 4 lignes pour le segment 1 ?)

Autre question : l'ID doit-il être le n° du segment de plus faible indice. Dans ce cas pour optimiser il faudrait trier en premier lieu.

En attendant ci-joint le résultat brut

Voici un exemple plus parlant car les chiffres n'étaient pas clair, prenons plutôt un arbre généalogique

J'ai modifié un peu le code, mais tu n'as pas répondu aux questions.

Regarde ce que ça donne

eric

Rebonjour,

Voici quelques réponses

-En fait les colonnes C et G sont là pour expliquer mais dans les faits elles ne sont pas indispensables

Concernant les lignes en colonne A qui semblent se répèter je ne peux y couper car elles définissent l'ensemble des situations où il existe des liens entredifférents éléments de la liste. En supprimer dans le fichier en entrée se ferait au risque d'une perte d'information

En sortie cependant , l'idéal et d'avoir chaque élément/individu une seule fois avec son groupe d'affectation (mais cette suppression des doublons peut se faire ultérieurement.

Concernant l'ID j'ai pris la valeur du plus faible indice comme un PPGC (Plus petit dénominateur commun). Mais cet ID peut être généré aléatoirement peu importe du moment que les éléments partageant un lien de parenté ont un ID permettant de les identifier/filtrer/réunir instantanément.

Rebonjour,

Voici quelques réponses

-En fait les colonnes C et G sont là pour expliquer mais dans les faits elles ne sont pas indispensables

Concernant les lignes en colonne A qui semblent se répèter je ne peux y couper car elles définissent l'ensemble des situations où il existe des liens entredifférents éléments de la liste. En supprimer dans le fichier en entrée se ferait au risque d'une perte d'information

En sortie cependant , l'idéal et d'avoir chaque élément/individu une seule fois avec son groupe d'affectation (mais cette suppression des doublons peut se faire ultérieurement.

Concernant l'ID j'ai pris la valeur du plus faible indice comme un PPGC (Plus petit dénominateur commun). Mais cet ID peut être généré aléatoirement peu importe du moment que les éléments partageant un lien de parenté ont un ID permettant de les identifier/filtrer/réunir instantanément.

Ok. Et le résultat fourni actuellement tu en penses quoi ?

eric

Bonjour

Tout d'abord je tiens à te remercier pour ta réponse qui fonctionne parfaitement, ça fait deux semaines que j'essaye sans essais via ACCESS les recherches V , 15 à 20 étapes intermédiaires.... Toute tentative de réaliser celà par un logiciel SIG met 1 à 5 heures et le logiciel est stupide et arrive à affecter des éléments au mauvais groupe quand il se superpose ( sans qu'en fait il y ait réellement un contact) (imaginez une route enjambant une autoroute)

Excel semble réaliser l'opération comme une fleur à partir de ton modèle

Si je remplace les colonnes A et B par une fraction (juste 30 000 lignes) de mon ensemble de données cela semble fonctionner (mais la vérification sera possible que sur le fichier final)

Je creuse un peu afin d'appliquer le modèle sur une plus grande quantité de donnée:

La macro semble fonctionner parfaitement jusqu'à 200 000 lignes environ (et avec une rapidité hallucinante), au delà, une incompatibilité de type semble intervenir (pop up) et là je bloque . Mais ça ne vient apparemment pas de la nature des éléments renseignés dans les colonnes

Il s'agit peut être d'une limitation due à la puissance de mon PC ou sa RAM ou à vba, je tenterais avec le i7 de mon boulot.

Essaie sur un autre micro mais excel a du mal avec les très gros fichiers.

Avec 200000 lignes tu atteins peut-être ses limites. Au delà de 5Mo sa réaction devient un peu aléatoire...

A voir.

Annonce le message d'erreur et la ligne de code en cause.

eric

Le fichier fait entre 6 et 7 Mo . Concernant l'incompatibilité de type, elle n'affiche pas de code debuggage au niveau du pop up. il ne me reste plus qu'à espérer que mon i7 et ou une autre version d'excel fonctionne.

Après test (merci teamviewer) ça ne marche pas sur le i7 et il n'y a toujours pas de message d'erreur.

En dernier recours, je peux passer par libreoffice ou open office (mais je crains que cela soit bien pire.

Concernant Libreoffice , ça bloque également mais voici le message obtenu en sélectionnant cette ligne:

Dict(data(lig, 1)) = "Groupe " & nb

Erreur d'exécution BASIC.

'91'

Type: com.sun.star.uno.RuntimeException

Message: [automation bridge] unexpected exception in IUnknownWrapper_Impl::getValue ! Message :

A tête reposée j'ai fait quelques nouveaux tests,

En supprimant les doublons, (Exemple: couple 2;3 / 3;2) je tombe en dessous des 200 000 lignes pour un fichier de 2,5 Mo .... et ça plante toujours.

Chose étrange: 200 000 lignes du fichier original fonctionne; 200 000 lignes d'un fichier nettoyé ne fonctionne pas.

Je suppose que l'incompatibilité de type doit provenir d'un blocage en raison d'un trop grand nombre de groupe à générer puisque ce nombre reste constant entre la version initiale et la version sans doublon du schmilblick

Bonjour,

Pour libre office les macro sont souvent incompatibles. Je ne connais pas son basic pour adapter.

Je pense qu'il va falloir réfléchir à faire autrement, je ne sais pas encore comment.

C'est possible d'avoir ton fichier de données ? Le zipper, le déposer sur cjoint.com et donner le lien fourni, en MP si confidentiel.

C'est combien le nombre de lignes maxi ? 600000 ou ça peut être encore plus ?

eric

Bonjour

Tut trouveras les deux versions du fichier par ce lien

1° ce qui sort d'access

2° après un léger nettoyage

Concernant le nombre de ligne il s'agit de données à l'échelle d'un département, elles peuvent augmenter à la hausse où évoluer à la baisse selon les restructurations sur le réseau.

Bonjour,

Ah mais ce sont vraiment des nombres et non des noms.

Déjà je remet id as Long au lieux de as String, ça sera plus rapide et moins de mémoire utilisée.

Bonne nouvelle, les 600000 lignes sont traitées correctement, c'est le collage qui pose problème. Sans doute .Transpose() qui doit être limité à 65536 lignes et on en atteint 104420.

Rien d'insurmontable je pense mais je vais être occupé cette am, peut-être ce soir.

A+

eric

Merci, merci et encore merci,

Autant Excel en niveau de base ou avancé, j'arrive à m'en sortir autant les macros et la programmation, je me suis arrété à la programmation Casio ou au script avancé sur actionnaz... Et pour résoudre mon problème avec mes maigres connaissances, les formules imbriquées les unes dans les autres ne sont d'aucun secours...

La version modifiée, prend seulement 1s de plus.

A contrôler mais ça parait correct.

Je ne met que le n° de groupe, si vraiment tu préfères une chaine comme "Groupe 1" il n'y a pas grand chose à modifier.

eric

Bonjour, et merci encore pour ton travail de tous les instantsje viens de réaliser quelques tests avec la version 2 des données que tu m'as envoyé et techniquement la macro marche mais le résultat n'est pas encore complet (les tronçons ayant des liens ne sont pas tous ensemble mais par tranche). "La macro réunit les branches de l'arbre et non l'arbre en entier"

Je pense que ce trou vient du fonctionnement de la macro) :

- elle regarde une ligne et regarde si plus haut dans son analyse elle a déjà affecté un groupe à un élément directement lié à ce dernier

Si le fichier n'a pas de lien existant, un groupe est créé.

(Exemple) Groupe 1 (2-3,3-4,) => 2,3,4 appartiennent à ce groupe

Il tombe sur une valeur 250 inconnue au bataillon associée à 249, il va donc créer un groupe n°25 Il continue son chemin et 10 000 lignes plus tard, il trouve 250-4 et là la macro n'a pas l'automatisme de se dire," il existe un lien entre le groupe 1 et 25, je peux les fusionner. "

illustration directe dans le fichier que tu m'as remis sur la base des éléments transmis via cjoint. le tronçon 94822 et 94823 sont liés dans le tableau initial il se retrouvent pourtant dans deux groupes distincts le 21425 et le 12447

Bonjour,

ok, compris.

Je réfléchi comment aborder le problème demain.

eric

Bonjour,

Tiens, j'avais posté une version que j'ai dû oublier de valider.

Donc voici la dernière. Résultat sur le gros fichier en 10 s, sachant que la précédente mettait 22 min

Il y a des trous dans les n° de groupe, je ne pense pas que ce soit un pb pour toi.

J'ai ajouté un bouton de tri, et une zone de filtrage au cas où, le filtre automatique étant au-delà de ses limites.

Reste à voir si le résultat est correct et s'il n'y a plus de trous dans la raquette.

eric