XL 2019 TCD avec Filtres

[jper]

Bonjour,
Je réalise une compilation de fichier de résultats de vente qui est dans l'onglet compilation.
Je dois créer le TCD qui est dans l'onglet TCD_NP.
Le réaliser sans filtre sur le champ Magasin commercial tel qu'il est je n'ai pas de problème.
Je dois par contre réaliser un TCD en filtrant des magasins (onglet Magasin), les magasins analyser sont cochés
Je réalise une boucle sur cet onglet (en réalité cet onglet est dans un autre fichier)
mais je n'arrive pas à avoir un résultat probant.

D'avance merci pour votre aide.
Bonne journée,
Jean-François

For CptM = 2 To NligMagRel
                    Workbooks(FicMacro).Activate
                    Workbooks(FicMacro).Sheets(SheetListMagasins).Select
                    Sheets(SheetListMagasins).Range("B" & CptM).Select
                    ChxMag = Selection.Value
                    If ChxMag <> "" Then
                        Workbooks(FicMacro).Activate                                      ' Se positionner sur le fichier de compilation
                        Workbooks(FicMacro).Sheets(SheetListMagasins).Select
                        Sheets(SheetListMagasins).Range("A" & CptM).Select  ' Selection des lignes des noms de Fichiers
                        Mag = Selection.Value
                        
                        Workbooks(FicRec).Activate                                      ' Se positionner sur le fichier de compilation
                        Workbooks(FicRec).Sheets("Media_NP").Select
                        With ActiveSheet.PivotTables("TCD Tot Mag").PivotFields("Magasin Commercial")
                            .PivotItems(Mag).Visible = False
                        End With
                    End If

 

[Hasco]

Bonjour,

Faites le à partir de power query. Power query est très capable d'aller chercher et filtrer votre liste de magasins dans un autre fichier puis d'associer les magasins filtrés à vos données. Cette source peut alors servir à établir votre TCD.

Au passage, je notez que vous nous parlez de macro, mais que vous nous donnez un fichier SANS macro !

Cordialement

 

[jper]

Bonjour,
Désolé, comme ma macro complète est dans un autre fichier, j'avais commencer par la copier, mais comme il fallait épurer la base pour la transmettre, j'ai oublier de la remettre.
Je vous transmets le fichier de macro qui a déjà évoluer depuis ce matin.
Je regarde ce que vous m'avez transmis et je vous dit ce qu'il en ait.
Cordialement,
Jean-François

 

[jper]

Bonjour,
Merci pour le résultat, je ne suis pas familier avec Power Query, y a t il un tuto expliquant sa construction pour ensuite la reproduire en VB.
D'avance merci,
Cordialement,
Jean-François

 

[Hasco]

Re,

L'avantage pour vous c'est qu'il n'y aurait pas besoin de vba ou seulement pour mettre à jour le tableau sur un évènement particulier de feuille et/ou classeur.

Mettez les deux classeurs ci-joint dans le même répertoire. Puis ouvrez le classeur 'TCD_Magasins.xlsx'.
vous y verrez entre autre les explications suivantes.

Onglet de ruban 'Données' Bouton 'Requêtes et connexions'
Dans le panneau qui s'ouvre à droite de votre espace de travail excel, vous avez trois noms de requêtes:
T_Datas est une requête qui ne fait que récupérer les données du tableau "T_Datas' de ce classeur.
T_Magasins_X est une requête qui sélectionne les données idoines du tableau "T_Magasins" du classeur distant, dont le chemin est dans la cellule nommée 'Fichier_Magasins' de ce classeur.
Datas_TCD associe les données de T_Magasins_X et T_Datas par fusion des deux.
Faites un click-droit sur l'une d'elles puis 'Modifier' pour voir l'éditeur de requête Power Query s'ouvrir et comment elles sont construites.

Cordialement

 

[jper]

Bonjour,
Je te remercie pour toutes ces explications.
Maintenant c'est beaucoup plus claire, je pense que le plus simple est de mettre l'onglet Magasins qui se trouve dans le fichier distant dans le mettre fichier que celui qui contient la base initiale et le ou les TCD
Cordialement,
Jean-François

 

[jper]

Bonjour,
Je commence à comprendre l'utilisation de Power Query.
J'ai rapatrier mon onglet Magasin dans mon fichier de compilation.
Je suis en train de construire les query en VB, je déclare ma base de donnée en VB, et quand je construit ma requête à la main j'ai un 3ème ligne qui apparait c'est en tête promus (voir copie d'écran de la construction)

j'ai la ligne suivante comme requête

= Table.TransformColumnTypes(#"En-têtes promus",{{"Magasin Commercial", type text}, {"Libellé Produit", type text}, {"Code vente (EAN)", Int64.Type}, {"Fournisseur", type text}, {"Date", type date}, {"Quantité Vendue", Int64.Type}, {"Montant HT", type number}, {"Période", type text}})

J'ai mis mon code pour construire ma requête, elle se construit bien dans les requêtes mais j'ai une erreur.
J'ai fait une copie d'ecran du message d'erreur.

D'avance merci pour votre aide et bon WE

Cordialement,

Jean-François

' Création des Tableau Croisé dynamique
Sub TCD()

    Dim PlageMag As Range
    Dim Oper As Single
    Dim CptM As Integer
    Dim ChxMag As String
    Dim J As Integer
    Dim Mag As String
    
    Workbooks(FicRec).Activate
    Workbooks(FicRec).Sheets(SheetCompilation).Select
    Range(Cells(1, 1), Cells(NbligFicMagasin, 8)).Name = "Base_TCD"
    
    Windows(FicRec).Activate

    ActiveWorkbook.Queries.Add Name:="T_Compilation", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Base_TCD""]}[Content]," & Chr(13) & "" & Chr(10) & " #""En-têtes promus""  #""En-têtes promus""  #""Type modifié"" = Table.TransformColumnTypes(Source,{{""Magasin Commercial"", type text}, {""Libellé Produit"", type text}, {""Code vente (EAN)"", Int64.Type}, {""Fournisseur"", type text}, {""Date"", type date}, {""Quantité Vendue"", Int64.Type}, {""Montant HT"", type number}, {""Périod" & _
        "e"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""

 

[Hasco]

Bonjour,

Oui ! Le message dit bien ce qu'il veut dire. Power Query attend un signe égal après le nom

#"En-têtes promus"
Généralement l'étape complète s'écrit:

#"En-têtes promus" = Table.PromoteHeaders(Source),
Cette étape n'est nécessaire que lorsque la première ligne de données de la source doit servir d'entête de table. Première ligne d'un .csv par exemple.

Avant de faire ça par vba, faites le manuellement, ouvrez l'éditeur avancé de power query et vous aurez votre requête. Après c'est une question de doublement des guillemets

Personnellement j'ai pas bien compris l'intérêt de construire ça en vba.
M'enfin c'est votre joujou.

Ce qui est certain, c'est que sur des images, on ne peut pas travailler.

Bon week-end de potassage

 

[jper]

Bonjour,
Merci pour tes conseils, çà fonctionne.
Bon week-end

 

[jper]

Bonjour,
J'ai réussi à créer toutes mes requêtes automatiquement en VBA.
Par contre je butte sur la construction de la requête qui permet de faire le TCD final.
J'ai vu dans la construction de la requête il y y T_Data développée ainsi que les colonnes supprimées.
Si vous pouvez m'expliquer la construction de cette requête je vous en serait reconnaissant.
Cordialement,
Jean-François

 

[Hasco]

Re,

Euh franchement là !
Pour savoir ce que fait une étape de requête, il suffit de se positionner sur la précédente, d'examiner l'état de panneau de visualisation, puis de passer à la suivante et de voir les changements.

Requête DATA_TCD​

Source :

=Table.NestedJoin(T_Magasins_X, {"Listes totales des magasins"}, T_Datas, {"Magasin Commercial"}, "T_Datas", JoinKind.LeftOuter)

Unit la requête T_Magasins_X à la requête T_Datas en liant les deux sur ce quelles ont en commun, à savoir la dénomination des magasins. Et ce dans une relation de 1 à plusieurs. Pour un magasin de T_Magasins_X il peut y avoir de 0 à N magasin correspondant dans T_DATAS.
Cela crée une colonne dont chaque ligne comporte la TABLE des magasins de T_DATAS correspondants à la valeur de clef de la ligne en cours (colonne 'Listes totales des magasins')

Ces tables, on veut en voir les lignes c'est pourquoi est faite l'étape 'T_Datas développé'
cliquez sur l'étape source, cliquez sur la double-flèche à droite de la colonne T_Datas et vous obtiendrez :

Liste des colonnes des Tables de T_Datas, en cliquant sur Ok cela 'Développe' les tables.

Quant à l'étape de suppressions des colonnes, elle ne fait que supprimer les colonne devenues inutiles ou en doublons {"Listes totales des magasins", "Media NP", "Media P"}

 

[jper]

Bonjour,
Désolé de t'avoir embêter, mais j'ai réussi et tout fonctionne comme je le voulais.
Bonne journée,
Jean-François