Microsoft 365 Définir le rattachement hiérarchique

  • Initiateur de la discussion Initiateur de la discussion foufa
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

foufa

XLDnaute Nouveau
Bonjour,
J'ai trois colonnes : manager , collaborateur, centre de cout.

Je souhaite savoir quels sont les centres de couts de rattachement de chaque manager.
Exemple :
Karine est la manager de Paul qui est sur le centre de cout A
Paul est le manager de David sur le centre de cout B
Paul est le manager de Sophie sur le centre de cout C

Résultat attendu
Karine ses centres de couts A,B,C
Paul ses centres de couts B,C

En formule excel ou power query ou dax

Merci pour votre aide!!
 

Pièces jointes

Bonjour @foufa, le forum,

Avec Power Query.

PowerQuery:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.ColumnNames(A),
C = List.TransformMany(Table.ToRows(A), each {_{2}} &
    Table.ToColumns(Table.SelectRows(A, (x)=> Record.Field(x, B{0}) = _{1})){2}, (x,y)=> {x{0},y}),
D = Table.FromRows(C, {B{0}, B{2}})
in D

Bonne journée à tous.
 
Bonjour @foufa, le forum,

Avec Power Query.

PowerQuery:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.ColumnNames(A),
C = List.TransformMany(Table.ToRows(A), each {_{2}} &
    Table.ToColumns(Table.SelectRows(A, (x)=> Record.Field(x, B{0}) = _{1})){2}, (x,y)=> {x{0},y}),
D = Table.FromRows(C, {B{0}, B{2}})
in D
Bonjour alexga78,
Merci beaucoup pour votre aide. j'ai testé mais ça fonctionne qu'une partie.
Je l'adapté sur la colonne collaborateur au lieu de centre de cout.
j'ai des managers qui sont N+4 donc ils sont supposés voir jusqu'à leur N-4 mais cette formule m'affiche que jusqu'au N-2.
comment puis je l'adapter svp.
merci encore
 

Pièces jointes

Bonjour @foufa, le forum,

à tester

PowerQuery:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = {List.Combine, List.Transform, Record.FieldOrDefault, Table.Column},
C = Table.ColumnNames(A),
D = (x)=> B{1}(x, Text.Trim),
E = (x)=> List.RemoveItems(x, {""}),
F = ((x)=> Record.FromList(x{1}, D(x{0}))) (Table.ToColumns(Table.Group(A, C{0}, {"x", each D(B{3}(_,C{2}))}))),
G = Table.AddColumn(Table.FromColumns({B{3}(A,C{0})}, {C{0}}), C{2}, each E(List.Skip(B{0}(List.Generate(
    ()=> D({B{2}(_, C{0})}), each E(_) <> {}, each B{0}(B{1}(_, (x)=> B{2}(F,x, {""})))))))),
H = Table.Distinct(Table.ExpandListColumn(G, C{2}))
in H

Bonne journée
 
Bonjour,
J'ai trois colonnes : manager , collaborateur, centre de cout.

Je souhaite savoir quels sont les centres de couts de rattachement de chaque manager.
Exemple :
Karine est la manager de Paul qui est sur le centre de cout A
Paul est le manager de David sur le centre de cout B
Paul est le manager de Sophie sur le centre de cout C

Résultat attendu
Karine ses centres de couts A,B,C
Paul ses centres de couts B,C

En formule excel ou power query ou dax

Merci pour votre aide!!
bonjour,
ce que tu décris est un système "Multi-niveau".
l'idéal pour gérer ce système est un système dit "neuronal".
qui graphiquement représente un arbre orienté vers le bas.
S G D BG BD _ reduit.jpg

ce qui se traduit par cet arbre :
Sans titre.jpg

à partir de là , le principe est de noter les "index" ou "lien" qui relie chaque "Data".
soit tu utilises un fichier "txt".
soit tu utilises une feuille Excel.

1 = Karine ; S(x) ; G(x) ; D(x) ; BG(2) ; BD(x)
2 = Paul ; S(1) ; G(x) ; D(x) ; BG(3) ; BD(4)
3 = David ; S(2) ; G(x) ; D(4) ; BG(x) ; BD(x)
4 = Sophie ; S(2) ; G(3) ; D(x) ; BG(x) ; BD(x)

si tu analyses l'enregistrement ligne 3 :
nom = David
Supérieur (manager) = ligne 2 --> Paul
à son niveau = ligne 4 --> Sophie
Manager = non

@+JP
 
Dernière édition:
Bonsoir foufa, alexga78, mjpmjp,

Voici une solution VBA qui fonctionne par récursivité :
VB:
Dim resu(), tablo, n&, manag 'mémorise les variables

Sub Liste()
Dim i&
ReDim resu(1 To Rows.Count, 1 To 2)
tablo = [A2].CurrentRegion.Resize(, 3) 'matrice, plus rapide
n = 0
For i = 3 To UBound(tablo)
    n = n + 1
    manag = tablo(i, 1)
    resu(n, 1) = manag
    resu(n, 2) = tablo(i, 3)
    Recursive tablo(i, 2)
Next i
'---restitution---
With [E4]
    If n Then
        .Resize(n, 2) = resu
        .Resize(n, 2).Sort .Cells, xlAscending, .Columns(2), , xlAscending, Header:=xlNo 'tri sur 2 colonnes
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 2).ClearContents 'RAZ en dessous
    .CurrentRegion.Columns.AutoFit 'ajustement largeurs
End With
End Sub

Sub Recursive(colab)
Dim i&
For i = 3 To UBound(tablo)
    If tablo(i, 1) = colab Then
        n = n + 1
        resu(n, 1) = manag
        resu(n, 2) = tablo(i, 3)
        Recursive tablo(i, 2)
    End If
Next i
End Sub
Cliquez sur le bouton Liste.

A+
 

Pièces jointes

Dernière édition:
Avec cette variante les centres de coût de chaque manager sont triés et concaténés :
VB:
Dim tablo, resu(), d As Object, manag 'mémorise les variables

Sub Liste()
Dim i&, n&, nn&, s
tablo = [A2].CurrentRegion.Resize(, 3) 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 2)
Set d = CreateObject("Scripting.Dictionary")
For i = 3 To UBound(tablo)
    manag = tablo(i, 1)
    If d.exists(manag) Then
        n = d(manag)
        resu(n, 2) = resu(n, 2) & Chr(1) & tablo(i, 3)
    Else
        nn = nn + 1
        d(manag) = nn 'mémorise la ligne
        resu(nn, 1) = manag
        resu(nn, 2) = tablo(i, 3)
    End If
    Recursive tablo(i, 2)
Next i
'---restitution---
Application.ScreenUpdating = False
With [E4]
    If nn Then
        .Resize(nn, 2) = resu
        .Resize(nn, 2).Sort .Cells, xlAscending, Header:=xlNo 'tri sur 1 colonne
        For i = 1 To nn
            s = Split(.Cells(i, 2), Chr(1))
            tri s, 0, UBound(s)
            .Cells(i, 2) = Join(s, ", ") 'séparateur modifiable
        Next i
    End If
    .Offset(nn).Resize(Rows.Count - nn - .Row + 1, 2).ClearContents 'RAZ en dessous
    .CurrentRegion.Columns.AutoFit 'ajustement largeurs
End With
End Sub

Sub Recursive(colab)
Dim n&, i&
n = d(manag)
For i = 3 To UBound(tablo)
    If tablo(i, 1) = colab Then
        resu(n, 2) = resu(n, 2) & Chr(1) & tablo(i, 3)
        Recursive tablo(i, 2)
    End If
Next i
End Sub

Sub tri(a, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
    Do While a(g) < ref: g = g + 1: Loop
    Do While ref < a(d): d = d - 1: Loop
    If g <= d Then
      temp = a(g): a(g) = a(d): a(d) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub
 

Pièces jointes

Dernière édition:
Bonjour le forum,

Une solution avec 2 tableaux structurés et une macro évènementielle, le code de la feuille :
VB:
Dim tablo, resu(), d As Object, manag 'mémorise les variables

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i&, n&, nn&, s
tablo = [Tableau1].Resize(, 3) 'tableau structuré, matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 2)
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(tablo)
    manag = tablo(i, 1)
    If d.exists(manag) Then
        n = d(manag)
        resu(n, 2) = resu(n, 2) & Chr(1) & tablo(i, 3)
    Else
        nn = nn + 1
        d(manag) = nn 'mémorise la ligne
        resu(nn, 1) = manag
        resu(nn, 2) = tablo(i, 3)
    End If
    Recursive tablo(i, 2)
Next i
'---restitution---
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
With [Tableau2] 'tableau structuré
    If Not .ListObject.DataBodyRange Is Nothing Then .Delete xlUp 'RAZ
    If nn Then
        .Resize(nn, 2) = resu
        .Resize(nn, 2).Sort .Cells, xlAscending, Header:=xlYes 'tri sur 1 colonne
        For i = 1 To nn
            s = Split(.Cells(i, 2), Chr(1))
            If UBound(s) > 0 Then tri s, 0, UBound(s)
            .Cells(i, 2) = Join(s, ", ") 'séparateur modifiable
        Next i
    End If
    .EntireColumn.AutoFit 'ajustement largeurs
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

Sub Recursive(colab)
Dim n&, i&
n = d(manag)
For i = 1 To UBound(tablo)
    If colab <> "" And tablo(i, 1) = colab Then
        resu(n, 2) = resu(n, 2) & Chr(1) & tablo(i, 3)
        Recursive tablo(i, 2)
    End If
Next i
End Sub

Sub tri(a, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
    Do While a(g) < ref: g = g + 1: Loop
    Do While ref < a(d): d = d - 1: Loop
    If g <= d Then
      temp = a(g): a(g) = a(d): a(d) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub
Edit : j'ai oublié de noter que ce post est mon 42000 ième message...

A+
 

Pièces jointes

Dernière édition:
Bonjour
perso quand j'entends hiérarchie j'entends DOM
le dom permet une hierachie recursive ilimité
alors çà peut se faire avec un htmldocument ou xmldocument virtuel

donc comme ca vite fait

VB:
Option Explicit
Sub test()
    Dim Rng As Range, HtmlDoc As Object, lId, lID2, i&, e, MaN, Collab, CentreCout, elem
    Set Rng = Range("A3:c" & Cells(Rows.Count, 1).End(xlUp).Row)
    Set HtmlDoc = CreateObject("htmlfile")
    'creation de la hiérarchie HTML
    HtmlDoc.body.innerhtml = "<body></body>"
    For i = 2 To Rng.Rows.Count
        
        'colonne MANAGER
        lId = UCase(Replace(Rng.Cells(i, 1), " ", "_"))
        Set e = HtmlDoc.getelementbyid(lId)
        If e Is Nothing Then
            Set MaN = HtmlDoc.body.appendchild(HtmlDoc.createelement("p"))
            MaN.setattribute "id", lId
            MaN.setattribute "class", UCase(Replace(Rng.Cells(1, 1), " ", "_"))
             Else
            Set MaN = e
            MaN.setattribute "class", UCase(Replace(Rng.Cells(1, 1), " ", "_")) & " " & UCase(Replace(Rng.Cells(1, 2), " ", "_"))
        End If
        'colonne centre de cout
        Set CentreCout = HtmlDoc.createelement("a")
        CentreCout.innerhtml = Rng.Cells(i, 3)
        CentreCout.setattribute "class", "cout"
        MaN.appendchild CentreCout
        
        'colonne collaborateur
        lID2 = UCase(Replace(Rng.Cells(i, 2), " ", "_"))
        Set e = HtmlDoc.getelementbyid(lID2)
        If e Is Nothing Then
            Set Collab = HtmlDoc.createelement("p")
            Collab.setattribute "id", lID2
            Collab.setattribute "class", UCase(Rng.Cells(1, 2))
            MaN.appendchild Collab
        Else
            Set Collab = e
        End If
        
    Next
    'voir le html obtenu
    Debug.Print HtmlDoc.body.innerhtml
    
    'taratata
    'a partir d'ici on pourrait construire un tableau  avec les donnée comme je le fait avec texte
        Dim texte$
    For Each elem In HtmlDoc.all
        If elem.getattribute("class") Like "*MANAGER*" Then
            For Each e In elem.getelementsbytagname("A")
                texte = texte & elem.ID & "-" & e.innertext & vbCrLf
              
            Next
        End If
    Next
    MsgBox texte
End Sub
a partir du commentaire taratata il te sera facile de remplacer l'alimentation de la variable texte par la construction progressive d'une variable tableau
demonstration:
demo3.gif

et en prime on a le htmlcode de l'arborescence valide dans la console
pas de méprise possible avec le DOM
si tu opte pour cet option je te ferais le tableau de result
Patrick
 

Pièces jointes

Re
Mervi de ton retour.
J'ai laissé tombé. Je voulais juste tester pour participer et je n'ai pas réussi à le faire avec un fichier
(40 modules et 90 macros lol)
Sinon ça a marché avec d'autres fichiers...
Merci
Bonne fin de soirée
Cordialement
Jean marie
 
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

Réponses
5
Affichages
420
Retour