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

VaLu à dit:

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​

Cliquez pour agrandir...

Gégé-45550 à dit:

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,

Cliquez pour agrandir...

goube à dit:

Une proposition.
Cordialement

Cliquez pour agrandir...

chris à dit:

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...

Cliquez pour agrandir...

alexga78 à dit:

Bonsoir à tous,

une tentative Power Query.

PowerQuery:

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.

Cliquez pour agrandir...

alexga78 à dit:

Bonsoir à tous,

une proposition à améliorer.

Bonne soirée.

Cliquez pour agrandir...

alexga78 à dit:

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 :

PowerQuery:

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.

Cliquez pour agrandir...