Création de TCD avec plusieurs feuilles
Bonjour à tous,
Après de nombreuses recherches, je me pose de nouveau une question :
Comment faire un tableau croisé dynamique à partir de plusieurs feuilles ayant des colonnes en commun (Niveau, famille et type et Total HT) mais aussi d'autres colonnes plus spécifiques à la feuille (ex : surface, volume....) ?
Dans l'attente de vos retours ou idées.
Bonne journée.
Bonjour,
Mets des données dans ton fichier et précise par un exemple le TCD que tu souhaites !...
Cdlt.
PowerQuery et PowerPivot font très bien le travail sans recourir aux macros ... je pencherais plutôt vers ce genre de solution ...
Oups je me suis trompé de fichier, j'ai chargé la version vierge. Je vous réponds depuis mon téléphone je vous le renvoie dès que j'ai accès à mon ordinateur
En attendant serai-ce possible que vous m'expliquiez PowerQuery et PowerPivot succinctement ?
ce sont des compléments d'excel qui permettent de faire des tcd avec plusieurs tableaux sources et/ou qui permettent aussi d'importer plusieurs sources de données dans un même tableau ...
ça demande un peu de travail au début mais c'est puissant ...
Enfin, ça dépend aussi de ce que vous cherchez et de la nature de vos données ...
Bonjour à tous
PowerQuery est intégré à partir de 2016, en complément sur 2010 et 2013
PowerPivot est intégré à partir de 2013 (versions pro puis toutes versions sur 365) , en complément sur 2010
Re,
En complément, 2 liens à consulter :
Tu es normalement en weekend !...
Cdlt.
Merci beaucoup pour tous ces superbes informations.
Je vous mets en pièce jointe le fichier cette fois-ci rempli
Je n'ai pas encore eu le temps de vous expliquer en détail mon problème.
Je travaille sur un projet de fin d'études.
Ce fichier Excel est une extraction depuis un logiciel de CAO / DAO (Revit).
L'objectif est de pouvoir réalisé une étude économique à partir des quantités extraite de Revit. Les quantitatifs extraits ce tri dans chaque feuille en fonction de catégories (Murs, portes, fenêtre, fondations,...).
Etant donné que les extractions varient d'un projet à un autre je cherche une solution qui fonctionne et cela peu importe la quantité de données extraite.
Pour revenir au sujet du fichier joint, vous trouverez dans la première feuille des exemples de TCD.
Vous remarquez que chaque TCD reprend les colonnes : Niveaux, Famille et type et Total HT.
En effet les autres colonnes ne m’intéressent pas pour le moment pour, faire une synthèse.
Je souhaiterais avoir un seul tableau possédant en haut un filtre par niveaux pour ne laisser apparaître qu'un niveau à la fois ou tous les niveaux comme cela est déjà le cas dans chaque TCD individuel.
Ensuite j'obtiens une synthèse des éléments avec à côté les Totaux HT.
Et pour finir en bas du tableau il me faudrait un total global.
En somme, c'est déjà ce que fait chaque TCD individuel. Mais je souhaite un tableau faisant tout d'un coup.
Je ne sais pas si mais explications sont assez claires
Pour revenir sur Power Query et Power Pivot je souhaite savoir si le résultat est "lisible" pour n'importe quelle utilisateur d'Excel n'ayant pas forcément activé ces plugins dans leur Excel. Etant donnée que le fichier est un document de travail servant pour l'ensemble de mes collègues je souhaite un usage le plus simple possible.
Effectivement, powerQuery et Pivot seront peut être difficiles à mettre en place dans ces conditions.
Du coup, retour au bon vieux VBA ...
L'idée est de générer un tableau de synthèse à partir duquel tu génère ton TCD.
Regarde sur le fichier en pièce jointe ...
ps : j'ai mis tes tableaux qui n'en n'étaient pas sous forme de tableau ( Menu Accueil, Mettre sous forme de tableau )
Bonjour
Déjà le principal souci pour utiliser PowerQuery ou PowerPivot est que les données ne respectent pas la logique des tables
- Les colonnes ne sont pas les mêmes d'un onglet à l'autre
- il y a des
lignes entièrement vides - sous-totaux
- totaux
Cela nécessite donc du code pour normaliser si on veut exploiter correctement...
C'est ce que fait en partie le code de xenondeele...
Si le parc Office est hétérogène, cela complique effectivement le problème...
A noter que dans 365, PowerQuery est intégré et actif de base
Bonjour,
Le code de xenondeele est effectivement une très bonne piste de travail.
J'aurai néanmoins quelques questions :
- Les tableaux peuvent-ils se créer automatiquement en fonction de la quantité de données ?
- Comment avez-vous fait pour nommer les tableaux et les colonnes ?
En effet si les tableaux sont créés "à la main" je doit donc recréer mes tableaux à chaque fois que je fais un nouvel export puisque je n'aurai pas toujours le même nombre de lignes.
ps : je n'ai pas besoin de la colonne nombres mais cela ne change pas grand-chose au code
Pour répondre à 78chris. Comme cela peut souvent être le cas le parc Office est très hétérogène (365, 2016, 2010,...) je suis donc obligé de passer par le vba.
Bonsoir,
Je me suis penché sur la solution de xenodeele et j'ai finalement compris le code.
Je vient de le tester et voici le résultat que j'obtient :
Auriez-vous une idée d'où je me suis trompé ?
Autre observation sur le code si je relance le code sur le fichier que vous m'avez envoyé, le tableau de synthèse ne se met pas à jour mais "colle" le résultat à la suite.
Dans l'attente de votre réponse.
Bonjour
Il est possible de faire 2 procédures :
- l'une pour normaliser tes onglets :
supprimer totaux, sous-totaux, lignes vides et mettre sous forme de tableau (à noter qu'un tableau structuré ne doit contenir aucune ligne vide ni au milieu ni à la fin) - Cependant l'onglet Pièces ne contient pas Famille et type : faut-il oublier cet onglet, y a t-il d'autres cas ?
- L'onglet Fondations ne contient rien dans la colonne Niveau
- vider le tableau de synthèse
Bonsoir,
Est-ce que cela pourrait convenir si je décoche l'option "(Vides)" dans les filtres comme pour l'image suivante :
L'onglet "pièce" est effectivement à exclure.
Concernant l'onglet "fondations" c'est du à une erreur au niveau des données exporté mais normalement cette colonne est toujours rempli.
Pourriez-vous éventuellement m'aider pour la rédaction du code des deux procédures dont vous me parlé ?
Voici le code que j'ai commencé à rédiger pour la deuxième procédure :
Sub construire_tableau()
Dim dl As Integer, f As Worksheet
' dl = dernière ligne tableau de synthèse
Application.ScreenUpdating = False
On Error Resume Next
For Each f In Sheets
If InStr(1, f.Name, "ABAK") And f.Index <> 1 Then
' on cherche toutes les feuilles qui contiennent le mot ABAK
If Worksheet.Name <> "ABAK_Pièces" Then
dl = Sheets("00_ABAK_Synthese").Range("A1").CurrentRegion.Rows.Count + 1
f.Select
' j'ai nommé les tableaux comme les feuilles
' feuille "ABAK_Fenêtres" --> tableau "ABAK_Fenêtres" --> colonne "ABAK_Fenêtres[Niveau]"
Range(f.Name & "[Niveau]").Copy
Sheets("00_ABAK_Synthese").Range("A" & dl).PasteSpecial xlPasteValues
Range(f.Name & "[Famille et type]").Copy
Sheets("00_ABAK_Synthese").Range("B" & dl).PasteSpecial xlPasteValues
'Range(f.Name & "[Nombre]").Copy
' il n' y pas de colonne Nombre dans tous les tableaux
'If Err.Number = "1004" Then
'Application.CutCopyMode = False
'Else
'Sheets("00_ABAK_Synthese").Range("C" & dl).PasteSpecial xlPasteValues
'End If
Range(f.Name & "[Total HT]").Copy
Sheets("00_ABAK_Synthese").Range("D" & dl).PasteSpecial xlPasteValues
End If
End If
Application.CutCopyMode = False
Next
Application.ScreenUpdating = True
End Subbonsoir
ton code est bien ...
si tu veux copier les cellules dans e tableau de synthèse il te faut garder un tableau avec juste la ligne de titre sur la feuille tableau synthèse ... et normalement ça marche ...
RE
Si on travaille avec des tableaux structurés il est préférable de coder des ListObjects
Intégrer des sous-totaux et totaux dans un TCD est un non sens... d'où ma proposition de normalisation.
J'ai exclu les feuilles en les nommant (en cas de déplacement l'index peut changer) mais il serait judicieux de prévoir une liste d'exclusions dans un onglet du classeur pour rendre le code plus souple.
Option Explicit
Sub Normalisation()
Dim feuille As Worksheet, fin As Range, Derlig As Long, Dercol As Integer
For Each feuille In ThisWorkbook.Worksheets
With feuille
If InStr(.Name, "ABAK") And .Name <> "ABAK_Pièces" And .Name <> "00_ABAK_Synthese" And .Name <> "00_ABAK_Page de garde" Then
.Activate
.Rows("4").EntireRow.Delete
Set fin = .Columns(1).Find("Total général")
If Not fin Is Nothing Then fin.EntireRow.Delete
Derlig = .Cells(.Rows.Count, 1).End(xlUp).Row
Dercol = .Cells(3, .Columns.Count).End(xlToLeft).Column
.ListObjects.Add(xlSrcRange, Range(Cells(3, 1), Cells(Derlig, Dercol)), , xlYes).Name = .Name
.ListObjects(1).TableStyle = "TableStyleLight9"
.ListObjects(1).Range.AutoFilter Field:=2, Criteria1:="="
If Not .ListObjects(1).DataBodyRange Is Nothing Then .Range(Replace(.Name, "-", "_")).EntireRow.Delete
.ListObjects(1).HeaderRowRange.Cells(1, 1).Select
.ShowAllData
End If
End With
Next feuille
End Sub
Sub Synthèse()
Dim feuille As Worksheet, x As Long
With Worksheets("00_ABAK_Synthese")
.Activate
If Not .ListObjects(1).DataBodyRange Is Nothing Then .ListObjects(1).DataBodyRange.Delete
End With
For Each feuille In ThisWorkbook.Worksheets
With feuille
If InStr(.Name, "ABAK") And .Name <> "ABAK_Pièces" And .Name <> "00_ABAK_Synthese" And .Name <> "00_ABAK_Page de garde" Then
If ActiveSheet.ListObjects(1).DataBodyRange Is Nothing Then x = 2 Else x = ActiveSheet.ListObjects(1).DataBodyRange.Rows.Count + 2
If Not .ListObjects(1).DataBodyRange Is Nothing Then
Union(.ListObjects(1).ListColumns("Niveau").DataBodyRange, .ListObjects(1).ListColumns("Famille et type").DataBodyRange, .ListObjects(1).ListColumns("Total HT").DataBodyRange).Copy
ActiveSheet.ListObjects(1).Range.Cells(x, 1).PasteSpecial Paste:=xlPasteValues
End If
End If
End With
Next feuille
End SubCela suppose d'avoir créé une feuille nommée 00_ABAK_Synthese avec un tableau structuré de 3 colonnes :
Niveau, Famille et type, Total HT.
C'est ce tableau structuré qui servira de source au TCD
Bonsoir,
J'obtiens un code d'erreur :
Je vous mets mon fichier...
RE
Le code est fait pour travailler sur un classeur où les onglets n'ont pas été mis sous forme de tableaux
Là tu as déjà des tableaux qui plus est plein de lignes vides et comme déjà dit dans mon mail de 17:14 : un tableau structuré ne doit contenir aucune ligne vide ni au milieu ni à la fin
Le tableau structuré a des règles. Si on ne les respecte pas, il perd 99% de ses avantages...
Il faudra ajouter au début du code de normalisation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManualet rétablir à la fin
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = TrueBonsoir,
Je pense toucher au but.
Voici mon fichier mis à jour. Vous y trouverez deux boutons dans la feuille "00_ABAK_Synthèse".
Le premier bouton lance une procédure qui nettoie le tableau de synthèse avant de le créer.
Ce code fonctionne parfaitement.
Le second lui supprime les lignes vierge ainsi que les ligne "inutile" (Totaux et sous-totaux).
Malheureusement un code d'erreur s'affiche lorsque la boucle "tant que" sort du tableau.
Auriez-vous un solution à cela ?
RE
le code que j'ai posté fonctionne parfaitement sur ton tableau initial.
J'ai précisé n fois la logique des tableaux structurés : prêchant dans le désert, je vais passer à autre chose.
Bye