XL 2021 Ramener les données de façon structurée sans TCD

[VaLu]

Bonjour à tous,

Je dois faire des budgets à partir de fichiers étrangement faits, sans être autorisée à changer fondamentalement le format.
Un petit fichier valant mieux qu'un long discours, je vous joins un court extrait:
La base vient d'un export d'ERP, onglet "Journal au 17.10.2024".

La trame du deuxième onglet ici "Charges ATT24-B25", sous forme de TCD, sert de base au montage des prévisions de fin d'année, et d'atterrissage (colonnes à coté).
Je trouve gênant d'utiliser un tcd, parce que les colonnes de critères adjacentes (O,Q,R dans mon fichier) sont fixes, donc si le tcd bouge, on perd la trace de ce qui concernait quoi.
Il y aurait t'il une possibilité de remonter les données relatives aux champs ci-dessous sous forme de tableau par exemple, dans l'onglet "Charges", à partir de formules, ou autres?
Je sèche...
Merci à vous,

Année

Mois

2023​

Total 2023

2024​

Total 2024

TABLE SIG

Compte

Libellé compte

tiers V2

1​

2​

3​

4​

5​

6​

1​

2​

3​

4​

5​

6​

 

[Gégé-45550]

Bonjour à tous,

Je dois faire des budgets à partir de fichiers étrangement faits, sans être autorisée à changer fondamentalement le format.
Un petit fichier valant mieux qu'un long discours, je vous joins un court extrait:
La base vient d'un export d'ERP, onglet "Journal au 17.10.2024".

La trame du deuxième onglet ici "Charges ATT24-B25", sous forme de TCD, sert de base au montage des prévisions de fin d'année, et d'atterrissage (colonnes à coté).
Je trouve gênant d'utiliser un tcd, parce que les colonnes de critères adjacentes (O,Q,R dans mon fichier) sont fixes, donc si le tcd bouge, on perd la trace de ce qui concernait quoi.
Il y aurait t'il une possibilité de remonter les données relatives aux champs ci-dessous sous forme de tableau par exemple, dans l'onglet "Charges", à partir de formules, ou autres?
Je sèche...
Merci à vous,

Année

Mois

2023​

Total 2023

2024​

Total 2024

TABLE SIG

Compte

Libellé compte

tiers V2

1​

2​

3​

4​

5​

6​

1​

2​

3​

4​

5​

6​

Bonjour,
Vous pourriez peut-être déjà convertir les données de l'onglet "Journal au 17.10.2024" en tableau structuré (voir PJ), ce qui vous permettra de trier, filtrer, insérer des segments, etc. selon vos besoins ?
Cordialement,

 

[goube]

Une proposition.
Cordialement

 

[chris]

Bonjour

C'est surtout les formules en O Q R qui ne sont pas correctes

Si on exploite un TCD on utilise un tableau avec des titre de lignes et une fonction LIREDONNEESTABCROISDYNAMIQUE qui permet de chercher précisément et non un égalité ligne à ligne qui n'a aucun sens dans un TCD

Mais avec des formule, à gauche et à droite du TCD difficile de bien cerner l'objectif et donc la solution la plus adaptée...

 

[alexga78]

Bonsoir à tous,

une tentative Power Query.

let
    A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    B = (x)=> List.Transform({11,4,9,10,8}, each x{_}),
    C = Table.FromRows(List.Transform(Table.ToRows(A), (x)=> {Text.From(x{0}) & "-"
        & Text.PadStart(Text.From (x{2}),2,"0")} & B(x)), {"x"} & B(Table.ColumnNames(A))),
    D = Table.Pivot(C, List.Sort(List.Distinct(C[x]),0), "x", "Solde", List.Sum),
    E = List.Accumulate(List.Sort(List.Distinct(A[Année]),0), D, (s,c)=> Table.AddColumn(s, Text.From(c) & "-Total",
        each List.Sum(Record.ToList(Record.SelectFields(_, List.Select(Table.ColumnNames(D), each Text.StartsWith(_, Text.From(c)))))))),
    F = Table.SelectColumns(E, let x = Table.ColumnNames(E), y = 4 in List.FirstN(x,y) & List.Sort(List.Skip(x,y), 0))
in
    F

Bonne soirée à tous.

 

[VaLu]

Bonjour,
Vous pourriez peut-être déjà convertir les données de l'onglet "Journal au 17.10.2024" en tableau structuré (voir PJ), ce qui vous permettra de trier, filtrer, insérer des segments, etc. selon vos besoins ?
Cordialement,

Bonjour,
Merci de votre réponse. Le bémol, c'est qu'avec le tableau, on se retrouve avec un niveau de détail trop important, toutes les opérations par tiers, les commentaires... Le but est d'avoir une vision "synthétique" pour faire les projections à côté. Et la difficulté, de ne pas trop changer visuellement le format d'origine, sinon, levée de boucliers...

 

[VaLu]

Une proposition.
Cordialement

Bonjour,
Merci de votre retour. L'inconvénient de ce format, c'est que pour les télécommunications orange par exemple, on se retrouve avec autant de lignes, que de mois. Sachant qu'il y en a énormément dans le fichier réel, ce serait difficilement gérable

 

[VaLu]

Bonjour

C'est surtout les formules en O Q R qui ne sont pas correctes

Si on exploite un TCD on utilise un tableau avec des titre de lignes et une fonction LIREDONNEESTABCROISDYNAMIQUE qui permet de chercher précisément et non un égalité ligne à ligne qui n'a aucun sens dans un TCD

Mais avec des formule, à gauche et à droite du TCD difficile de bien cerner l'objectif et donc la solution la plus adaptée...

On est bien d'accord...
Si c'était moi, j'aurais tout refait de A à Z (j'avais d'ailleurs commencé). Mais j'ai une hiérarchie très attachée à ce format.
En fait, le TCD sert de base à l'application des hypothèses: moyenne constatée sur le premier semestre, ou valeur du dernier mois, ou dépense exceptionnelle qui n'aura plus lieu, ou pourcentage du montant du 1er semestre...
Une usine à gaz sur une base instable...

 

[VaLu]

Bonsoir à tous,

une tentative Power Query.

let
    A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    B = (x)=> List.Transform({11,4,9,10,8}, each x{_}),
    C = Table.FromRows(List.Transform(Table.ToRows(A), (x)=> {Text.From(x{0}) & "-"
        & Text.PadStart(Text.From (x{2}),2,"0")} & B(x)), {"x"} & B(Table.ColumnNames(A))),
    D = Table.Pivot(C, List.Sort(List.Distinct(C[x]),0), "x", "Solde", List.Sum),
    E = List.Accumulate(List.Sort(List.Distinct(A[Année]),0), D, (s,c)=> Table.AddColumn(s, Text.From(c) & "-Total",
        each List.Sum(Record.ToList(Record.SelectFields(_, List.Select(Table.ColumnNames(D), each Text.StartsWith(_, Text.From(c)))))))),
    F = Table.SelectColumns(E, let x = Table.ColumnNames(E), y = 4 in List.FirstN(x,y) & List.Sort(List.Skip(x,y), 0))
in
    F

Bonne soirée à tous.

Bonjour,
C'est très intéressant, on se rapproche vraiment du format du TcD. Juste une question, comment filtrer les périodes affichées, pour ne garder (en affichage) que les 1er semestres 2023 et 2024 par exemple? Le segment ne fonctionnant pas dans ce cas.

 

[alexga78]

Bonsoir à tous,

une proposition à améliorer.

Bonne soirée.

 

[VaLu]

Bonsoir à tous,

une proposition à améliorer.

Bonne soirée.

J'aime beaucoup! Puis-je vous demander comment vous avez fait? Une concaténation de l'année et du mois, puis vous avez dépivoté les données?
Et pour le lien entre la table de sélection de la période et le tableau?

 

[alexga78]

J'aime beaucoup! Puis-je vous demander comment vous avez fait? Une concaténation de l'année et du mois, puis vous avez dépivoté les données?
Et pour le lien entre la table de sélection de la période et le tableau?

Bonjour VaLu, le forum,

en gros le code:
- sélectionne les colonnes désirées,
- ajoute une colonne Année-Mois,
- sélection des lignes désirées en fonction de la table t_mois (en créant une liste Année-Mois si le mois est non null),
- table pivotée par la colonne Année-Mois,
- calcul de la somme par année,
- tri des colonnes.

code un peu plus rapide :

let
    a = each Excel.CurrentWorkbook(){[Name=_]}[Content],
    b = (x)=> List.Transform({11,4,9,10,8}, each x{_}),
    c = Table.FromRows(List.Transform(Table.ToRows(a("Table1")), (x)=> {Text.From(x{0}) & "-"
        & Text.PadStart(Text.From(x{2}),2,"0")} & b(x)), {"x"} & b(Table.ColumnNames(a("Table1")))),
    d = List.TransformMany( List.Buffer(List.Distinct(List.Transform(c[x], each Text.BeforeDelimiter(_,"-")))),
         (x)=> let x = Table.Buffer(a("t_mois")) in List.RemoveNulls(Table.AddColumn(x, "x", (y)=>
         if Record.Field(y, Table.ColumnNames(x){1}) <> null then Text.PadStart(Text.From(Table.PositionOf(x, y) + 1), 2,"0") else null)[x]), (x,y)=> x & "-" & y),
    e = Table.SelectRows(c, each List.Contains(d, [x])),
    f = Table.Pivot(e, List.Sort(List.Distinct(e[x]),0), "x", "Solde", List.Sum),
    g = List.Accumulate(List.Sort(List.Distinct(a("Table1")[Année]),0), f, (s,c)=> Table.AddColumn(s, Text.From(c) & "-Total",
        each List.Sum(Record.ToList(Record.SelectFields(_, List.Select(Table.ColumnNames(f), each Text.StartsWith(_, Text.From(c)))))))),
    h = Table.SelectColumns(g, let x = Table.ColumnNames(g) in List.FirstN(x,4) & List.Sort(List.Skip(x,4), 0))
in
    h

Bonne journée à tous.

 

[VaLu]

Bonjour VaLu, le forum,

en gros le code:
- sélectionne les colonnes désirées,
- ajoute une colonne Année-Mois,
- sélection des lignes désirées en fonction de la table t_mois (en créant une liste Année-Mois si le mois est non null),
- table pivotée par la colonne Année-Mois,
- calcul de la somme par année,
- tri des colonnes.

code un peu plus rapide :

let
    a = each Excel.CurrentWorkbook(){[Name=_]}[Content],
    b = (x)=> List.Transform({11,4,9,10,8}, each x{_}),
    c = Table.FromRows(List.Transform(Table.ToRows(a("Table1")), (x)=> {Text.From(x{0}) & "-"
        & Text.PadStart(Text.From(x{2}),2,"0")} & b(x)), {"x"} & b(Table.ColumnNames(a("Table1")))),
    d = List.TransformMany( List.Buffer(List.Distinct(List.Transform(c[x], each Text.BeforeDelimiter(_,"-")))),
         (x)=> let x = Table.Buffer(a("t_mois")) in List.RemoveNulls(Table.AddColumn(x, "x", (y)=>
         if Record.Field(y, Table.ColumnNames(x){1}) <> null then Text.PadStart(Text.From(Table.PositionOf(x, y) + 1), 2,"0") else null)[x]), (x,y)=> x & "-" & y),
    e = Table.SelectRows(c, each List.Contains(d, [x])),
    f = Table.Pivot(e, List.Sort(List.Distinct(e[x]),0), "x", "Solde", List.Sum),
    g = List.Accumulate(List.Sort(List.Distinct(a("Table1")[Année]),0), f, (s,c)=> Table.AddColumn(s, Text.From(c) & "-Total",
        each List.Sum(Record.ToList(Record.SelectFields(_, List.Select(Table.ColumnNames(f), each Text.StartsWith(_, Text.From(c)))))))),
    h = Table.SelectColumns(g, let x = Table.ColumnNames(g) in List.FirstN(x,4) & List.Sort(List.Skip(x,4), 0))
in
    h

Bonne journée à tous.

Bonjour,
Je ne connaissais pas du tout cette possibilité de table pour piloter un tableau, je vais essayer de décortiquer tout ça pour l'appliquer à mon fichier complet.
Merci beaucoup!