Microsoft 365 TCD multiples colonnes

[doudel]

Bonjour,
Je suis à la recherche d'une solution pour regrouper dans un seul TCD plusieurs colonnes qui contiennent le même type d'information et de faire la somme des quantités. Je parviens à faire un TCD pour chaque colonne mais je n'arrive pas à les réunir.

VILLE1	NB	VILLE2	NB2	VILLE3	NB3	VILLE4	NB4
Voiture	2​	Voiture	2​
Car	6​	Car	5​			Voiture	10​
Vélo	1​	Voiture	2​
Trottinette	6​			Car	3​

L'idée serait de lister toutes les données et de faire le totale de chaque donnée
Merci

 

[chris]

Bonjour

Il faut normaliser le tableau via une requête PowerQuery, intégré à Excel, puis construire le TCD sur la requête

 

[doudel]

Merci pour ta réponse, un petit exemple peut-être

 

[chris]

RE

Oui on attend un fichier exemple : je peux traiter ton image avec Photoshop mais pas avec Excel

 

[doudel]

RE

Oui on attend un fichier exemple : je peux traiter ton image avec Photoshop mais pas avec Excel

Et voila

 

[merinos]

Salut @doudel,

il suffit d'jouter un index dans Power Query.
....

 

[chris]

RE

Voili, voila

On pourrait faite la synthèse directement dans PowerQuery aussi


Je parlais des chênes sessile il y a peu...

 

[doudel]

RE

Voili, voila

On pourrait faite la synthèse directement dans PowerQuery aussi


Je parlais des chênes sessile il y a peu...

Le résultat est top mais j'ai du mal à comprendre la démarche, désolé

 

[chris]

RE

On décroise les colonnes, quel que soit le nombre de colonnes, et on recroise pour ne garder que l'essence et la densité : ce qui en fait une source normalisée utilisable en TCD

Tu as les étapes à droite quand tu affiches la requête dans PowerQuery

On pourrait terminer par une somme des densités par Année et Essence

 

[doudel]

RE

On décroise les colonnes, quel que soit le nombre de colonnes, et on recroise pour ne garder que l'essence et la densité : ce qui en fait une source normalisée utilisable en TCD

Tu as les étapes à droite quand tu affiches la requête dans PowerQuery

On pourrait terminer par une somme des densités par Année et Essence

Bonjour,
Merci Chri, tout semble fonctionner, j'ai rajouté quelques colonnes supplémentaires. Il faut, a priori rester vigilant sur le fait que toutes les colonnes doivent être renseignées sinon on obtient un décalage dans le tri des données.
J'ai reproduis la démarche à la lettre sans comprendre à 100 % ce que je faisais. Si je dois le reproduire dans un contexte un peu différent, je ne suis pas persuadé d'y parvenir.
Merci
A bientôt

 

[doudel]

RE

On décroise les colonnes, quel que soit le nombre de colonnes, et on recroise pour ne garder que l'essence et la densité : ce qui en fait une source normalisée utilisable en TCD

Tu as les étapes à droite quand tu affiches la requête dans PowerQuery

On pourrait terminer par une somme des densités par Année et Essence

Bonjour,
Je relance le sujet car j'aurais une petite question complémentaire. J'ai rajouté des colonnes que je souhaiterais intégrer dans mon TCD. Malheureusement ces colonnes comportent des données numériques dont certaines affichent des zéros. Or je souhaiterais que ces données soient conservées comme telles. Au niveau de l'étape du remplacement des données, peut-on cibler certains attributs (DENSITE1, DENSITE2, DENSITE3, DENSITE4) ?

= Table.ReplaceValue(#"Supprimer le tableau croisé dynamique des autres colonnes",0,null,Replacer.ReplaceValue,{"Valeur"})

Merci

 

[doudel]

Bonjour,
Je relance le sujet car j'aurais une petite question complémentaire. J'ai rajouté des colonnes que je souhaiterais intégrer dans mon TCD. Malheureusement ces colonnes comportent des données numériques dont certaines affichent des zéros. Or je souhaiterais que ces données soient conservées comme telles. Au niveau de l'étape du remplacement des données, peut-on cibler certains attributs (DENSITE1, DENSITE2, DENSITE3, DENSITE4) ?

= Table.ReplaceValue(#"Supprimer le tableau croisé dynamique des autres colonnes",0,null,Replacer.ReplaceValue,{"Valeur"})

Merci

J'ai tenté cela mais j'ai une erreur :

= Table.ReplaceValue(#"Supprimer le tableau croisé dynamique des autres colonnes",each[Valeur],each if[Attribut]="DENSITE1" Then (0,null),Replacer.ReplaceValue,{"Valeur"})

 

[doudel]

RE

On décroise les colonnes, quel que soit le nombre de colonnes, et on recroise pour ne garder que l'essence et la densité : ce qui en fait une source normalisée utilisable en TCD

Tu as les étapes à droite quand tu affiches la requête dans PowerQuery

On pourrait terminer par une somme des densités par Année et Essence

Cris, je n'arrive pas rajouter une colonne (Test) qui me permettrait d'ajouter une filtre en plus de l'année sans qu'il me crée des index supplémentaires. Les informations ne sont pas reprises dans toutes les lignes.
Merci

 

[job75]

Bonsoir doudel, chris, merinos,

En VBA cette solution est très classique :

Private Sub Worksheet_Activate()
Dim d As Object, ncol%, tablo, resu(), i&, j%, x$, v, nn&, n&
Set d = CreateObject("Scripting.Dictionary")
With Sheets("Feuil1").[A1].CurrentRegion
    ncol = .Columns.Count
    If ncol Mod 2 = 0 Then ncol = ncol + 1 'nombre impair
    tablo = .Resize(, ncol) 'matrice, plus rapide
End With
ReDim resu(1 To UBound(tablo) * ncol, 1 To 2)
For i = 2 To UBound(tablo)
    For j = 2 To ncol Step 2
        x = tablo(i, j)
        If x <> "" Then
            v = tablo(i, j + 1)
            If d.exists(x) Then
                nn = d(x) 'récupère la ligne
                If IsNumeric(CStr(v)) Then resu(nn, 2) = resu(nn, 2) + v
            Else
                n = n + 1
                d(x) = n 'mémorise la ligne
                resu(n, 1) = x
                resu(n, 2) = v
            End If
        End If
Next j, i
'---restitution---
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] '1ère cellule de destination
    If n Then
        .Resize(n, 2) = resu
        .Resize(n, 2).Sort .Cells, xlAscending, Header:=xlNo 'tri alphabétique
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 2).ClearContents 'RAZ en dessous
    .Resize(, 2).EntireColumn.AutoFit 'ajuste les largeurs
End With
End Sub

La macro est dans le code de la feuille "Résultat" et se déclenche quand on active la feuille.

A+

 

[job75]

Pour traiter le fichier du post #13 on utilisera ce fichier (2) et cette macro :

Private Sub Worksheet_Activate()
Dim d As Object, ncol%, tablo, resu(), i&, an, test$, x$, j%, y$, v, nn&, n&
Set d = CreateObject("Scripting.Dictionary")
With Sheets("Feuil1").[A1].CurrentRegion
    ncol = .Columns.Count
    If ncol Mod 2 = 1 Then ncol = ncol + 1 'nombre pair
    tablo = .Resize(, ncol) 'matrice, plus rapide
End With
ReDim resu(1 To UBound(tablo) * ncol, 1 To 4)
For i = 2 To UBound(tablo)
    an = tablo(i, 1): test = tablo(i, 2)
    x = an & Chr(1) & test & Chr(1)
    For j = 3 To ncol Step 2
        y = tablo(i, j)
        If y <> "" Then
            v = tablo(i, j + 1)
            If d.exists(x & y) Then
                nn = d(x & y) 'récupère la ligne
                If IsNumeric(CStr(v)) Then resu(nn, 4) = resu(nn, 4) + v
            Else
                n = n + 1
                d(x & y) = n 'mémorise la ligne
                resu(n, 1) = an
                resu(n, 2) = test
                resu(n, 3) = y
                resu(n, 4) = v
            End If
        End If
Next j, i
'---restitution---
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] '1ère cellule de destination
    If n Then
        .Resize(n, 4) = resu
        .Resize(n, 4).Sort .Cells(1, 3), xlAscending, .Cells, , xlAscending, .Cells(1, 2), xlAscending, Header:=xlNo 'tri sur 3 colonnes
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 4).ClearContents 'RAZ en dessous
    .Resize(, 4).EntireColumn.AutoFit 'ajuste les largeurs
End With
End Sub