Discussion :

[julienhgr2]

Bonjour,

Je m'y perds avec les fonction recursive. C'est un concept pas si facile a maitriser. J'espère que vous pourrez m'aider a débuter
Voici ce que j'ai Un tableau avec 3 colonnes
Colonne A : Identifiant
Colonne B : Identifiant du père
Colonne C : Texte

Cela me permet de construire un arbre. Un parents avec X enfants (ou 0) et 1 enfant avec 1 seul parent (ou 0 pour le premier)

Je voudrais lorsque je sélectionne un enregistrement que la macro m'affiche les parents de l'enregistrement


Exemple . j'ai l'enregistrement dont le texte est "D" et dont le père est " C "; et le père de "C" est "B"; et le père de "B" est "A"
Si je selectionne "D" je voudrais que ma macro me retourne "A|B|C|D"

Comment faire le plus simplement possible pour remonter un arbre ?
Le problème est bien different de descendre un arbre car ici je n'ai qu'un parent (ou 0) pour un enfant.

Merci beaucoup,
Julien

[Pierre Fauconnier]

Salut

Voici un exemple de remontée d'arbre, sur base d'un tableau nommé Tableau1. La condition de sortie est que l'élément n'ait pas de parent, ce qui signifie que l'on a remonté tout l'arbre.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Function getParents(Element, Optional Parents As String) As String
  Dim P As String
 
  P = Evaluate("index(tableau1[Parent],match(""" & Element & """,tableau1[Element],0))")
  If P <> "" Then
    If Parents = "" Then Parents = P Else Parents = P & "|" & Parents
    Parents = getParents(P, Parents)
  End If
  getParents = Parents
End Function

L'élément P est placé en premier dans la concaténation pour respecter l'ordre Grand-Parent|Parent|Enfant sur la ligne If Parents = "" Then Parents = P Else Parents = P & "|" & Parents. Si tu voulais l'arbre dans l'ordre Enfant|Parent|Grand-Parent, il suffirait d'inverser les opérandes de la concaténation à la ligne If Parents = "" Then Parents = P Else Parents = Parents & "|" & P

Pour rendre la formule dynamique dans le tableau Excel, il faut ajouter Application.Volatile en début de fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Function getParents(Element, Optional Parents As String) As String
  Dim P As String
 
  Application.Volatile
  P = Evaluate("index(tableau1[Parent],match(""" & Element & """,tableau1[Element],0))")
  If P <> "" Then
    If Parents = "" Then Parents = P Else Parents = P & "|" & Parents
    Parents = getParents(P, Parents)
  End If
  getParents = Parents
End Function

[julienhgr2]

Merci beaucoup pour ta reponse rapide.

EN fait je ne m'etais pas bien exprimé.
Mes données ne sont pas dans un tableau mais dans une feuille excel.
Comment l'adapté sans utiliser la formatage d'un tableau ?

Merci beaucoup.

[Pierre Fauconnier]

En adaptant la fonction EVALUATE...

[julienhgr2]

J'ai essayé mais je n'y arrive pas. Désolé je débute un peu.

Je ne sais pas comment utiliser la fonction match dans mon cas. Je ne suis pas sur de savoir sur quoi l'utiliser.
Je voudrais pouvoir specifier une feuille et une zone de rechercher

je sais le faire en parcourant mon tableau et un testant les valeurs à chaque fois…. mais j'imagine que c'est moins propre et moins rapide

d'autant que je voudrais mon tableau de départ est comme ceci :

ID	ID parent	Texte
1		Jean
2	1	Paul
3	2	Pierre
4	2	Julien

merci beaucoup beaucoup pour me guider

[Pierre Fauconnier]

Pas de soucis.

Voici une fonction récursive qui travaille sur une plage. Evidemment, si ta plage grandit, tu devras adapter les paramètres dans l'appel de la fonction. Ceci dit, ce n'est pour moi pas logique de ne pas travailler avec une table de données et cela relève à mon sens du défaut de conception.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Function getParents1(Element, ElementsColumn As Range, ParentsColumn As Range, Optional Parents As String) As String
  Dim P As String
 
  Application.Volatile
  P = Application.Index(ParentsColumn, Application.Match(Element, ElementsColumn, 0))
  If P <> "" Then
    If Parents = "" Then Parents = P Else Parents = P & "|" & Parents
    Parents = getParents1(P, ElementsColumn, ParentsColumn, Parents)
  End If
  getParents1 = Parents
End Function

Tu dois utiliser cela en Excel ou en VBA?

[julienhgr2]

J'utilise du VBA uniquement.

Voici ce que j'ai maintenant :

dans ma fonction principale :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MsgBox getParents1(Cells(Target.Row, Target.Column + 2).Value, ThisWorkbook.Worksheets("listeMC").Range("A1:A" & Worksheets("listeMC").[A65000].End(xlUp).Row), ThisWorkbook.Worksheets("listeMC").Range("B1:B" & Worksheets("listeMC").[A65000].End(xlUp).Row))

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Function getParents1(Element, ElementsColumn As Range, ParentsColumn As Range, Optional Parents As String) As String
  Dim P As String
 
  Application.Volatile
  P = Application.Index(ParentsColumn, Application.Match(Element, ElementsColumn, 0))
  If P <> "" Then
    If Parents = "" Then Parents = P Else Parents = P & "|" & Parents
    Parents = getParents1(P, ElementsColumn, ParentsColumn, Parents)
  End If
  getParents1 = Parents
End Function

mais cela ne fonctionne pas :-(

Incompatibilité de type ici : P = Application.Index(ParentsColumn, Application.Match(Element, ElementsColumn, 0))

[Pierre Fauconnier]

Une récursive peut être remplacée par une boucle, bien que j'apprécie la récursive pour son élégance. Donc, si tu n'aimes pas les récursives, voici une fonction qui, à la place de s'appeler elle-même, utilise une boucle (sur base de la première fonction que je t'ai donnée tout à l'heure)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function getParents2(Element) As String
  Dim Parents As String
  Dim P As String
 
  Application.Volatile
  P = Element
  Do While P <> ""
    If P <> Element Then
      If Parents = "" Then Parents = P Else Parents = P & "|" & Parents
    End If
    P = Evaluate("index(tableau1[Parent],match(""" & P & """,tableau1[Element],0))")
  Loop
  getParents2 = Parents
End Function

[Pierre Fauconnier]

La ligne MsgBox getParents1(Cells(Target.Row, Target.Column + 2).Value, ThisWorkbook.Worksheets("listeMC").Range("A1:A" & Worksheets("listeMC").[A65000].End(xlUp).Row), ThisWorkbook.Worksheets("listeMC").Range("B1:B" & Worksheets("listeMC").[A65000].End(xlUp).Row)) est inexploitable.

Dans ton code, tu aurais intérêt à utiliser deux variables de type Range, à les valoriser, puis à utilser MsgBox

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
dim ColIndex as range, ColMatch as range, SearchValue as ...
 
SearchValue = cells(...)
set colindex= thisworkbook.worksheets(...)...
set colmatch = thisworkbook.worksheets(...)...

Je n'ai pas typé la variable searchValue car je ne sais pas ce que tu recherches (Nombre, texte, date, ...)... Il faut que la valeur cherchée soit du même type que ce tu trouves comme valeurs dans la plage ColMatch.

Mets une copie d'écran de tes données pour que ce soit plus simple à comprendre, et n'hésite pas à créer des lignes de code pour valoriser tes variables. Cela te permettra de bien vérifier que tout est ok lorsque tu appelles la fonction.

[julienhgr2]

Merci pour ton aide. Je voudrais aussi y arriver avec la récursivité car je trouve également cela plus propre et plus "universelle". Mais je ne comprends pas ce qui bloque. Cela dit je ne comprends pas bien comment marche cette fonction :


Voici mon tableau:

Element	Parent	Texte
336	57	batiement economique
335	57	batiment politique militaire
338	57	batiment prive
337	57	batiment religieux
347	337	cathedrale
58	335	chateau
353	337	cloitre
60	337	couvent
61	337	eglise
62	338	maison
348	337	monastere
63	336	moulin
64	335	palais
349	57	partie
65	57	piece
69	335	prison
71	57	ruine
72	57	tente
334	57	ville
57		Architecture

Voici le code que j'ai:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Function getParents1(element As String, Optional Parents As String) As String
  Dim P As String
 
  Dim ElementsColumn As Range, ParentsColumn As Range
  Set ElementsColumn = ThisWorkbook.Worksheets("listeMC").Range("A1:A" & Worksheets("listeMC").[A65000].End(xlUp).Row)
  Set ParentsColumn = ThisWorkbook.Worksheets("listeMC").Range("B1:B" & Worksheets("listeMC").[A65000].End(xlUp).Row)
 
 
  Application.Volatile
  P = Application.Index(ParentsColumn, Application.Match(element, ElementsColumn, 0))
  If P <> "" Then
    If Parents = "" Then Parents = P Else Parents = P & "|" & Parents
    Parents = getParents1(P, Parents)
  End If
  getParents1 = Parents
End Function

Je voudrais par exemple en appelant ma fonction avec un ID disons le 62, qu'elle me retourne :

Architecture|batiment prive|maison

À force d'être dedans je ne vois plus rien :/

[unparia]

Bonjour

Aucune récursivité, ni itération ni fonction personnelle n'est à priori nécessaire, pour autant qu'aucun identifiant présent en colonne des parents ne soit absent de la colonne des ID et vice-versa.
Peux-tu nous confirmer que tel est bien le cas ?

[Pierre Fauconnier]

Bonjour

Aucune récursivité, ni itération ni fonction personnelle n'est à priori nécessaire[...]

J'ai donné deux solutions, une récursive et l'autre non. Pour ce qui est de la fonction personnelle, elle permet simplement un code plus propre dans le respect des bonnes pratiques, et permet de tester cette fonction indépendamment du reste du process. Après, on l'intègre "comme une brique logicielle" dans le process qui nécessite la récupération de l'arbre.

On peut toujours se passer de fonction et créer des procs de 300 lignes. C'est juste bordélique et non professionnel, en plus du fait que cela ne permet ni de tester facilement le code, ni de le réutiliser facilement ailleurs, dans le même projet ou un autre.

Cela dit, créer un arbre en vba sans itération, je ne vois pas trop.

[Pierre Fauconnier]

Merci pour ton aide. Je voudrais aussi y arriver avec la récursivité car je trouve également cela plus propre et plus "universelle"[...]

Dans ma première réponse, j'avais laissé Element sans le typer car je ne savais pas quelle donnée tu allais rechercher. Si tu le types en string, alors, les données de la colonne de l'élément doivent être en string. Tu dois aussi modifier la colonne qui contient la valeur qui sera renvoyée dans la chaine de l'arbre, car la fonction que je t'ai donnée travaillait sur deux colonnes, et toi tu travailles sur trois.

De plus, dans les fonctions que je t'ai données, je t'ai donné l'arborescence parente, en excluant l'élément. Or, de ton exemple, tu souhaites avoir l'élément en finale. Il faut donc l'ajouter à un moment donné. Vu que la chaine est reconstituée de droite à gauche, il convient de valoriser la chaine Parents avec la valeur correspondant à l'élément.

Pour comprendre comment ça fonctionne, place un point d'arrêt sur la première ligne de la fonction puis appelle-là. Tu pourras alors avec F8 passer sur chaque ligne, visualiser les variables locales (qui changent à chaque appel de la fonction, car chaque fonction appelée possède son propre jeu de variable). Intéresse-toi aussi à la pile des appels qui te montrera comment la fonction s'appelle à chaque passage.

Voici une plage Excel et la fonction adaptée à ton cas. exécute-là pas à pas pour bien comprendre chaque étape.

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Function getParents1(element As Long, Optional Parents As String) As String
  Dim ParentText As String, Value As Long, Index As Long
 
  Dim ElementsColumn As Range, ValuesColumn As Range, ParentsColumn As Range
  Set ElementsColumn = ThisWorkbook.Worksheets("listeMC").Range("A2:A" & Worksheets("listeMC").[A65000].End(xlUp).Row)
  Set ValuesColumn = ThisWorkbook.Worksheets("listeMC").Range("b2:b" & Worksheets("listeMC").[A65000].End(xlUp).Row)
  Set ParentsColumn = ThisWorkbook.Worksheets("listeMC").Range("c2:c" & Worksheets("listeMC").[A65000].End(xlUp).Row)
 
 
  Application.Volatile
 
  Index = Application.Match(element, ElementsColumn, 0)
  Value = Application.Index(ValuesColumn, Index)
  ParentText = Application.Index(ParentsColumn, Index)
  If Parents = "" Then Parents = ParentText Else Parents = ParentText & "|" & Parents
  If Value <> 0 Then Parents = getParents1(Value, Parents)
  getParents1 = Parents
End Function

Tu peux bien sûr utiliser la fonction dans un process VBA. Je ne l'ai illustré en Excel que pour l'exemple.

[unparia]

On peut toujours se passer de fonction et créer des procs de 300 lignes. C'est juste bordélique et non professionnel, en plus du fait que cela ne permet ni de tester facilement le code, ni de le réutiliser facilement ailleurs, dans le même projet ou un autre.

Je ne pense pas que la création de "procs" soit nécessaire. Une simple formule tirée vers le bas devrait suffire à mon sens

créer un arbre sans itération, je ne vois pas trop.

Il ne s'agit pas de créer, mais d'afficher l'arborescence, telle qu'elle existe à chaque niveau "enfant"

Je vais attendre que julienhgr2 réponde à la question que je lui ai posée.

[Pierre Fauconnier]

[...]Une simple formule tirée vers le bas devrait suffire[...]

Question:

[...]
Tu dois utiliser cela en Excel ou en VBA?

Réponse:

J'utilise du VBA uniquement[...]

Donc, pour ce qui est d'une formule tirée vers le bas, tu voudras bien lire et comprendre la demande?


Si c'est uniquement en Excel, on n'a effectivement pas besoin de fonction perso récursive, ni de faire de grands mystères et de grands tralalas.

[...]
Aucune récursivité, ni itération [...]

Par contre, tu es dans l'erreur, car il y a bien une itération puisque chaque ligne est calculée. A ma connaissance, il n'y a pas de possibilité de récréer une arborescence sans itération ( = un calcul sur chaque ligne, qu'il se fasse en boucle ou en récursive en VBA ou au sein d'une formule Excel sur chaque ligne du tableau). Si on prend la ligne de l'élément 62, elle se sert de la formule de la ligne de l'élément 338 qui se sert de la formule de la ligne de l'élément 57 etc... Ne pas appeler cela une itération, c'est jouer inutilement sur les mots et les concepts et égarer le lecteur. On a bien la notion d'étapes propres à la récursivité ou à l'utilisation de boucles et inévitablement liée à la notion d'arborescence.


@julienhgr2

Que ce soit en Excel ou en VBA, tu gagnerais à travailler avec des tables de données. Ta formule s'adapterait aux ajouts, et ton code VBA aussi... Ici, l'ajout de la ligne "Mairie" ne nécessite aucune modification ou adaptation de formule.



Que ce soit avec ou sans table de données, tu pourrais en VBA te servir de la colonne D formulée en Excel pour éviter la fonction récursive en VBA. Obtenir l'arbre complet d'un élément s'effectuerait avec une combinaison INDEX/MATCH en VBA (avec ou sans fonction perso)

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Function getTree(Element As Long) As String
  getTree = Application.Index(Range("t_batiments[Arbre]"), Application.Match(Element, Range("t_batiments[Element]"), 0))
End Function

[julienhgr2]

Merci beaucoup, J'ai appris pleins de choses grace a toi (et en cherchant par moi même aussi)


Je m'en étais sorti comme ça. Mais c'est moins propre :

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Function getParents2(element As String, Optional Parents As String) As String
  Dim P As String
  Dim Ptexte As String
 
  Dim ElementsColumn As Range, ParentsColumn As Range
  Set ElementsColumn = ThisWorkbook.Worksheets("listeMC").Range("A1:A" & Worksheets("listeMC").[A65000].End(xlUp).Row)
  Set ParentsColumn = ThisWorkbook.Worksheets("listeMC").Range("B1:B" & Worksheets("listeMC").[A65000].End(xlUp).Row)
  Set TextColumn = ThisWorkbook.Worksheets("listeMC").Range("C1:C" & Worksheets("listeMC").[A65000].End(xlUp).Row)
 
 
  For Each cell In ElementsColumn
      If cell.Value = element Then
             Exit For
      End If
  Next cell
 
  P = ParentsColumn(cell.Row)
  Ptexte = TextColumn(cell.Row)
 
 If P <> "" Then
    If Parents = "" Then Parents = Ptexte Else Parents = Ptexte & "|" & Parents
    Parents = getParents1(P, Parents)
  End If
 
If P = "" Then
    Parents = Ptexte & "|" & Parents
End If
 
getParents1 = Parents
 
End Function

[Pierre Fauconnier]

Bonne continuation et bon travail