XL 2016 tableau croisé dynamique automatique

[eric123]

Bonjour à tous,

Chaque mois, je dois traiter et analyser des données que je reporte dans un tableau croisé dynamique et un graphique.

La base de données peut faire plusieurs lignes (une centaine) et chaque mois le nombre de lignes est différente.

Je voudrais automatiser ces opérations c’est-à-dire avoir un classeur ou je colle mes données et cela créerai mon tableau croise dynamique sur une feuille et mon graphique sur l’autre.

Cela est-il possible via VBA ou autre ?

J’ai placé un fichier exemple sans le graphique

Bonne journée et par avance merci,

 

[djidji59430]

Bonjour à tous,

Une proposition :

 

[eric123]

Bonjour à tous,

Une proposition :

Bonjour djidji
si j'ajoute des lignes à feuille données, cela va donc les prendre en compte ? quelle est cette valeur "cpt"

 

[djidji59430]

T'as pas essayé ???
Tes données sont en tableau structuré, et le tcd est fait à partir de ce tableau.
Donc les plages sont dynamiques
cpt, c'est le nb de fois où est répété un client. Comme apparemment tu ne t'intéresses qu'aux clients qui ont plusieurs présentations, ça permet de filtrer.

 

[eric123]

Bonjour djidji ce n'étais pas vraiment mon attente, mais je te remercie de m'avoir répondu. J'ai enregistré entre temps une macro dans mon classeur ci-joint. en clair, je colle des données je fais "ctrl g" et cela tri mes données voulues en supprimant les colonnes des données non voulu (toujours les mêmes) cela me construit un tableaux croisé dynamique avec le tri voulu jusque la bien ! ensuite cela se complique... déjà a chaque fois cela m'ajoute une nouvelle feuille (non souhaitée) et quant je fais une nouvelle macro (ctrl p) pour construire un graphique sur la base du tableau croisé dynamique en copié collé sur une nouvelle feuille cela bug.

La premiere macro qui fonctionne en collant en feuille1 et "ctrl g" :
Sub TEMPLIER1()
'
' TEMPLIER1 Macro
' au boulot !
'
' Touche de raccourci du clavier: Ctrl+g
'
ActiveSheet.Paste
Columns("A:AM").Select
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("A:AM").EntireColumn.AutoFit
Columns("A:G").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("D:I").Select
Selection.Delete Shift:=xlToLeft
Columns("G:Y").Select
Selection.Delete Shift:=xlToLeft
Range("L14").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Feuil1!R1C1:R133C6", Version:=6).CreatePivotTable TableDestination:= _
"Feuil4!R3C1", TableName:="Tableau croisé dynamique2", DefaultVersion:=6
Sheets("Feuil4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("Tableau croisé dynamique2")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("Tableau croisé dynamique2").RepeatAllLabels _
xlRepeatLabels
With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
"Libellé analytique")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique2").PivotFields("Total heures prestées"), _
"Somme de Total heures prestées", xlSum
With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
"Libellé lieu")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
"Libellé type heure")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique2").PivotFields("Dont sous-traitance"), _
"Somme de Dont sous-traitance", xlSum
ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique2").PivotFields( _
"Dont heures de salariés Fictif"), "Somme de Dont heures de salariés Fictif", _
xlSum
Range("A33").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=270
Range("A285285").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=-342
ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Libellé lieu" _
).Orientation = xlHidden
End Sub


la deuxieme qui bug en feuille du tableau croisé dynamique ( ctrl p) :

Sub graphique()
'
' graphique Macro
'
' Touche de raccourci du clavier: Ctrl+p
'
Range("A3125").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Columns("A").Select
Columns("A").EntireColumn.AutoFit
Range("A1").Select
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Libellé type heure").Orientation = xlHidden
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Feuil6!$A$1:$D$50")
ActiveSheet.Shapes("Graphique 1").IncrementLeft 297
ActiveSheet.Shapes("Graphique 1").IncrementTop -193.8749606299
Columns("D").ColumnWidth = 11.71
Columns("C:C").ColumnWidth = 12.29
Columns("B:B").ColumnWidth = 13.43
Columns("A:A").ColumnWidth = 34.86
ActiveSheet.Shapes("Graphique 1").ScaleWidth 1.7254698382, msoFalse, _
msoScaleFromTopLeft
ActiveWindow.SmallScroll Down:=0
ActiveSheet.Shapes("Graphique 1").ScaleWidth 1.0350877171, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Graphique 1").ScaleWidth 1.014026884, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Graphique 1").ScaleWidth 1.1521613741, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Graphique 1").ScaleHeight 1.2465277778, msoFalse, _
msoScaleFromTopLeft
End Sub

le bug ou un des bugs:

ActiveChart.SetSourceData Source:=Range("Feuil6!$A$1:$D$50")

Un boss des macro peut il m'aider svp ?
Bonne soirée à toutes et tous,

 

[eric123]

Bonjour à tous personne pour m'aider svp ?
Bien a vous

 

[D.D.]

Bonjour djidji
si j'ajoute des lignes à feuille données, cela va donc les prendre en compte ? quelle est cette valeur "cpt"

Bonjour

Pourquoi ne pas prendre les colonnes complètes au lieu d'une sélection pou faire le TCD ?
Donc au lieu de

donnees!$A$1:$F$14​

mettre

donnees!$A:$F​

et cela devrait faire l'affaire.

 

[chris]

Bonjour à tous

Chaque mois, je dois traiter et analyser des données que je reporte dans un tableau croisé dynamique et un graphique.

La base de données peut faire plusieurs lignes (une centaine) et chaque mois le nombre de lignes est différente.

Je voudrais automatiser ces opérations c’est-à-dire avoir un classeur ou je colle mes données et cela créerai mon tableau croise dynamique sur une feuille et mon graphique sur l’autre.

Cela est-il possible via VBA ou autre ?

Sur tous les forums Excel, les spécialitses des TCD te diront qu'un TCD ne se crée pas en VBA.

On crée et peaufine manuellement le TCD et on n'utilise éventuellement VBA pour en modifier la source au fil du temps.

Si besoin de remanier la source, puisque tu as 2016 avec PowerQuery intégré, le faire par PowerQuery.

Exemple ci-joint : modifier le chemin sur l'onglet paramètres puis Données, Actualiser tout (ou actualiser le TCD)

 

[eric123]

Bonjour chris,
Merci beaucoup pour ce retour qui pourrai très bien me convenir.
Juste une chose, je ne suis pas un pro des fichiers sources, je ne comprend pas bien comment tu fais pour changer de fichier source pour mettre à jour le TCB et le TCB Graph. peut tu m'en dire plus stp ?

Bonne journée

 

[chris]

Re

Comme indiqué j'utilise une requête PowerQuery comme source du TCD/GCD

Données, REquêtes et connexions t'affiche un volet : clic droit sur le requête, Modifier : cela ouvre PowerQuery où tu peux voir à droite les étapes de la requête

Le B A BA ici http://www.excel-formations.fr/Trucs_astuces/PQ01.php

 

[eric123]

Bonjour chris,
Merci beaucoup pour ce retour qui pourrai très bien me convenir.
Juste une chose, je ne suis pas un pro des fichiers sources, je ne comprend pas bien comment tu fais pour changer de fichier source pour mettre à jour le TCB et le TCB Graph. peut tu m'en dire plus stp ?

Bonne journée

 

[eric123]

Bonjour chris,
pourtant, voir les PJ, avec l'enregistreur de macro j'y suis arrivé. Certes je n'arrive pas à faire un doublon TCD et GCD viable mais si on cherche uniquement plusieurs tris des calculs et une mise en forme cela fonctionne.

 

[Staple1600]

Bonjour le fil

[aparté]

Sur tous les forums Excel, les spécialitses des TCD te diront qu'un TCD ne se crée pas en VBA.

[/aparté]
Pas sur tous (notamment certains anglophones)
Moi-même, pour éviter d'aggraver mes TMS, me suis concocter une petite routine générique pour créer un certain type de TCD.
(Mais j'avoue que c'est pas évident à finaliser)

Mais c'est vrai que depuis PQ, j'ai changé de routine
(Ce qui du coup va pas être tip top niveau TMS)

EDITION: Bonjour chris
PS: J'aime bien PQ (dommage qu'il ne soit pas facilement pilotable en VBA)
Rapport à mes TMS

 

[dysorthographie]

bonjour,
perso voila ce que j'avais proposé sur Développez.com!

[XL-2010] VBA : Créer un TCD sur chaque onglet d' un document - Macros et VBA Excel

Je souhaiterais créer automatiquement un Tableau croisé dynamique dans chaque onglet de mon document Excel. Jai x onglets avec des tableaux de taille variable. Sur chaque onglet, je voudrais ajouter un TCD.

www.developpez.net

 

[chris]

Bonjour chris,
pourtant, voir les PJ, avec l'enregistreur de macro j'y suis arrivé. Certes je n'arrive pas à faire un doublon TCD et GCD viable mais si on cherche uniquement plusieurs tris des calculs et une mise en forme cela fonctionne.

Tu confonds impossibilité et mauvaise pratique...

Je n'ai pour ma part jamais parlé d'impossibilité...

En plus le code, assez pourri de 190 lignes, suppose d'avoir copié les données du classeur externe dans le classeur contenant les macros ou l'inverse...

Le remaniement de la source n'étant pas ici indispensable, le(s) TCD peu(ve)t se baser directement sur le fichier fermé...

Mais je te laisse à tes certitudes et ne te proposerai plus rien qui puisse les ébranler. Bonne continuation.

Bonjour à Staple et dysorthographie