Totaux différenciés dans TCD

Bonjour,

Je bute sur un problème d'ajout de totaux pour un tableau croisé dynamique.

Mon tableau croisé dynamique comporte :

• en ligne : le champ "nom d'espèce animale"
• en colonne : les champs "numéro de point" et "date de passage"
• en valeur : la somme du "nombre de contacts".

Je voudrais donc simplement qu'à la fin de mon TCD je visualise à la fois le total du nombre de contacts par ligne (ça c'est facile, ça se fait automatiquement) mais également le total du "nombre de contacts pondérés". Le tout sans faire apparaître à l'intérieur du tableau les valeurs pour chaque numéro de point et chaque date de passage des "contacts pondérés" car cela le rend illisible.

Je pensais pouvoir m'en sortir en ajoutant en valeur du TCD en plus de la somme du "nombre de contacts", la somme du "nombre de contacts pondérés" et en trouvant le moyen de conserver l'affichage du total du nombre de contacts pondérés, sans afficher au sein du corps du tableau toutes ces valeurs, mais malgré mes tentatives, je ne trouve pas la solution...

Quelqu'un aurait-il une astuce ?

Merci par avance,

Sandrine

Bonjour

Cela n'est pas possible : tout calcul ajouté à un TCD se fera pour chaque cas.

Selon la structure du TCD et qu'il est ou non filtré régulièrement sur des champs, on peut juxtaposer des calcul hors TCD

On peut également dans certains cas ne pas utiliser un TCD mais PowerQuery (en addon à partir de 2010 et intégré à partir de 2016)

Bonjour,

Merci pour votre réponse.

Dans ce cas, comment ajouter une colonne à mon TCD à la fin (colonne qui reprendrait une sorte de sommeprod indiquée dans la base ayant servi à construire le TCD) afin que ce soit dynamique : je veux dire par là que le nb de lignes de mon TCD sera amené à évoluer donc je ne sais pas comment préparer la formule de la colonne ajoutée.

Par ailleurs sera-t-il possible quand j'aurai réussi cette manip de renommer le tableau incluant le TCD ainsi qu'une ou deux colonnes supplémentaires en tableau structuré afin de pouvoir faire appel à lui par la suite?

merci par avance!

Cordialement,

Bonjour

Il est toujours très difficile de répondre sans contexte : à chaque cas des solutions différentes peuvent s'appliquer...

Non un tableau structuré ne peut englober un TCD d'autant que leur structure est par essence, différente.

Joins un exemple représentatif pour qu'on voit ce que l'on peut faire et mets ton profil à jour de la version que tu utilises.

Bonjour,

Merci. Je vous joins le fichier simplifié en question qui contient :

• dans l'onglet "base" la base de données avec les champs nombre de contacts, nb contacts/h, nb contacts pondere/h dont je voudrais voir les totaux apparaître dans mon TCD
• dans l'onglet "synthese" le TCD que j'ai construis et auquel je voudrais ajouter deux colonnes : somme de nb contacts/h et somme de nb contacts pondere/h

Sinon je pensais faire 2 voire 3 tableaux croisés dynamiques et les concaténer ensemble mais je ne vois pas comment faire sachant que le nb de colonnes (et de sous-colonnes) variera en fonction de la MAJ de la base. En gros si j'utilise une formule recherchev, je ne sais pas dans quelle colonne la mettre. Il faudrait peut-être construire une macro qui colle le 1er tableau, demande d'aller à la colonne suivante (je ne sais pas du tout comment faire) et là fasse un recherchev pour aller chercher le total correspondant à chaque ligne "taxon nom latin"...

Merci par avance pour votre aide

Sandrine

PS: j'utilise la version excel du pack office de 2013

Bonjour

1ere remarque il est préférable que la source du TCD soit le nom du tableau source

Pour ta version mets-le dans ton profil : cela permettra que tous tes messages le mentionnent.

Ci-joint les formules.

Cependant avec 2013, tu peux installer PowerQuery et donc faire des calculs et des tableaux assez semblables à un TCD en utilisant plusieurs tableaux sources...

bonjour,

Mille merci, je suis en train d'essayer de dépioter la formule que vous me proposez. Pour l'instant, je ne comprends pas tout dans :

=SI(ET(A7<>"";GAUCHE(A7;5)<>"total");SOMME.SI.ENS(Tableau1[Nb contacts / h];Tableau1[Taxon nom vernaculaire];B7);0)

La 1e partie de la formule permet de s'assurer qu'on est bien après la colonne total? mais dans ce cas, et sachant que le TCD qui est dans les colonnes A à AG aura un nb de colonnes variable selon les données de la base de départ, dans quelle case je dois mettre cette formule?

Pour la 2e partie de la formule, la somme.si.ens en fait ça additionne le "nb de contacts/h" correspondant au taxon qui est dans la colonne B7 de cet onglet. C'est bien ça?

Ok pour la source du TCD. Je ne connais pas Powerquery mais comme ce fichier de synthèse automatique sera partagé avec des collègues qui n'auront pas téléchargé powerquery, est-ce que ça ne risque pas de buguer s'ils exécutent chez eux les macros?

Merci !

PS ! j'ai bien accès à mon profil mais je ne vois pas où rajouter la version d'excel...

Re-,

En fait je vois bien que ET(A7<>"";GAUCHE(A7;5)<>"total") permet d'arrêter le calcul à la fin des lignes du tableau. Donc je peux faire glisser la formule jusqu'à la ligne 100 histoire d'être tranquille par rapport au nb de lignes de mon tableau.

Par contre la difficulté est que le nb de colonnes de mon TCD va forcément bouger (en fonction de ma base, le nb de points et de dates de passage évoluera à la baisse ou à la hausse). Et en + je ne peux pas faire glisser la formule sur les colonnes plus à droite car il me faut intégrer la 2e colonne supplémentaire (total du nb de contacts /h pondérés). j'ai l'impression qu'il me faudrait une macro pour sélectionner la colonne après le tableau et y intégrer la formule, non ?

RE

Soit laisser un nombre de colonnes suffisant entre TCD et formules et masquer/démasquer les colonnes par VBA

soit placer les calculs à gauche du TCD

Pour PowerQuery il faudrait effectivement qu'il soit installé sur les postes utilisant le classeur

Merci. Dans ce cas, et afin d'éviter tout bug, je vais opter pour laisser 500 colonnes vides avant mon calcul.

Pourriez-vous me tuyauter pour trouver la formule magique qui permet de masquer les colonnes vides? j'avoue que je ne travaille pas pour l'instant avec vba, je créé mes macros en enregistrant la suite d'actions sous excel...

merci !

Bonjour

A mettre dans le module de la feuille synthèse

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Set Masque = Range("C:AR") 'les colonnes masquées
    If Not Intersect(Target.TableRange2, Masque) Is Nothing Then _
        Intersect(Target.TableRange2, Masque).EntireColumn.Hidden = False
End Sub

Cela prévoit un élargissement du TCD mais pas un rétrécissement.

RE

Ceci gère les 2 cas

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Set Masque = Range("C:AR") ''les colonnes masquées à ajuster aux besoins
    Masque.EntireColumn.Hidden = True
    If Not Intersect(Target.TableRange2, Masque) Is Nothing Then _
        Intersect(Target.TableRange2, Masque).EntireColumn.Hidden = False
End Sub

Merci. Si cela permet d'ajuster le TCD c'est parfait. Par contre j'ai du mal à comprendre le script :

Set Masque = Range("C:AR")

=> c'est là que je mets mes colonnes de la 3è à celle précédant ma formule finale?

Masque.EntireColumn.Hidden = True

=> je ne comprends pas le sens

If Not Intersect(Target.TableRange2, Masque) Is Nothing Then _

Intersect(Target.TableRange2, Masque).EntireColumn.Hidden = False

=> c'est compliqué : c'est ces deux lignes qui associent les deux totaux au reste du TCD? Si mon TCD a un nom, où est-ce que je dois l'indiquer?

Merci par avance !

Désolée pour mes questions successives

RE

sandrine_7 a écrit :

Merci. Si cela permet d'ajuster le TCD c'est parfait. Par contre j'ai du mal à comprendre le script :

Set Masque = Range("C:AR")

=> c'est là que je mets mes colonnes de la 3è à celle précédant ma formule finale?

Oui tu pars de C (le minimum et tu vas jusqu'où cela te parait possible d'avoir des données

Masque.EntireColumn.Hidden = True

=> je ne comprends pas le sens

On masque toutes les colonnes définies pour Masque (si jamais le TCD a rétréci ou si on ne l'a pas fait à main)

If Not Intersect(Target.TableRange2, Masque) Is Nothing Then _

Intersect(Target.TableRange2, Masque).EntireColumn.Hidden = False

=> c'est compliqué : c'est ces deux lignes qui associent les deux totaux au reste du TCD? Si mon TCD a un nom, où est-ce que je dois l'indiquer?

Quand le TCD se met à jour il déclenche l’événement PivotTableUpdate : cette ligne vérifie s'il y a une partie du TCD et des colonnes de la zone Masque qui se chevauchent (Intersection) et si oui démasque ces colonnes

Le code suppose qu'il y a un seul TCD dans cet onglet donc le nom importe peu.

Merci, je comprends mieux la formule. Par contre, j'ai un message qui apparaît "erreur de compilation" dès que je lance la macro dans laquelle j'ai mis le script que vous proposiez :

Sub Macromasque()

'

' Macromasque Macro

'

Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Set masque = Range("C:GJH") ''les colonnes masquées à ajuster aux besoins

masque.EntireColumn.Hidden = True

If Not Intersect(Target.TableRange2, masque) Is Nothing Then _

Intersect(Target.TableRange2, masque).EntireColumn.Hidden = False

'

End Sub

Je ne dois pas bien comprendre comment modifier la syntaxe pour faire du script une macro; j'ai enlevé Private Sub devant Worksheet Pivot table update pour pouvoir nommer la macro plus haut mais cela bugue... Comment faudrait-il que je formule le texte de la macro?

Merci encore

RE

Il faut mettre le code que j'ai donné à 17h33et seulement cela dans le module de la feuille Synthèse, pas dans un module normal

C'est une macro événementielle qui se déclenche à l'actualisation du TCD de cette feuille.

Dans la liste des objets à gauche de l'éditeur VBA (feuilles et ThisWorkbook) : tu double cliques sur le nom de la feuille synthèse et tu colles le codes à droite

Oh, merci ça marche ! je ne connaissais pas la possibilité de mettre un script dans le module d'une feuille. Les deux colonnes du fond se sont bien affichées à côté du TCd, c'est super. Est-ce qu'elles sont véritablement intégrées au tableau croisé dynamique ou seulement adjointes?

Il ne me manque plus que 2 choses pour mettre en forme le tableau :

• lui faire comprendre que sur la dernière ligne il doit me mettre un total pour les deux dernières colonnes ajoutées
• et qu'il doit reprendre la même mise en forme pour ces deux colonnes (y compris le total) par rapport à la mise en forme du TCD.

Bonjour,

Je sèche un peu pour la finalisation de la mise en forme du TCD auquel j'ai ajouté 2 colonnes :

Je voudrais ajouter le total en bas des 2 colonnes ajoutées. La difficulté est que la case est occupée par une formule, donc si j'actualise le TCD avec un nb de lignes différent, ça ne marchera pas.

Je me disais donc que je copierai les valeurs du TCD et des deux colonnes finales dans une nouvelle feuille afin d'en faire un tableau intégré avec une mise en forme particulière. Mais ça ne marche pas car j'ai en colonnes des sous-colonnes (les numéros de points puis les dates de passage) donc il ne reconnaît pas les entêtes des colonnes. bref c'est un bazar sans nom.

Auriez-vous une idée ?

Dans l'immédiat je pensais essayer de faire 3 TCD dans 3 feuilles distinctes qui seront masquées, chacun comportant en total une des 3 colonnes : "nb de contacts", "nb de contacts/h" et "nb de contacts / h pondérés". Et ensuite j'essaierai de trouver le moyen de les concaténer, mais ça me semble du rafistolage un peu douteux...

Rebonjour,

Je bute encore pour finaliser mon TCD qui a déjà bien avancé.

En fait, il me reste à attribuer la mise en forme du début du TCD aux deux dernières colonnes. Car quand je copie les valeurs des deux dernières colonnes à partir de deux autres TCD, il perd la mise en forme. Il faudrait que je puisse attribuer aux cellules copiées la même mise en forme que les cellules situées dans le TCD, sachant que l'en-tête, les valeurs et le total sont chacun dans une mise en forme différentes et que bien sûr le nb de lignes peut varier.

Je pense que je devrai m'en sortir en attribuant une mise en forme conditionnelle à ces deux colonnes, mais mes essais ne marchent pas. En fait il faudrait que tout ce qui est dans le colonne GCJ ait la même mise en forme que ce qu'il y a dans la colonne A (à ceci près qu'en colonne A il y a du texte et pas des nombres).

Ce doit être possible? en l'inscrivant dans le module de la feuille? (je reprécise qu'en colonne A, comme ce sera un TCD qui apparaîtra, par défaut il n'y a rien dans la feuille avant le lancement de la macro)

Merci par avance,

Bonjour

sandrine_7 a écrit :

Oh, merci ça marche ! je ne connaissais pas la possibilité de mettre un script dans le module d'une feuille. Les deux colonnes du fond se sont bien affichées à côté du TCd, c'est super. Est-ce qu'elles sont véritablement intégrées au tableau croisé dynamique ou seulement adjointes? Adjointes

Il ne me manque plus que 2 choses pour mettre en forme le tableau :

• lui faire comprendre que sur la dernière ligne il doit me mettre un total pour les deux dernières colonnes ajoutées
• et qu'il doit reprendre la même mise en forme pour ces deux colonnes (y compris le total) par rapport à la mise en forme du TCD.

=> je ne sais pas si c'est possible car en dernière ligne on a tjs la formule de base. Peut-être à ce stade vaut-il mieux que je fasse une macro pour copier les valeurs du TCD et des deux colonnes adjacentes dans une autre feuille et que j'en fasse un tableau intégré pour pouvoir afficher le total en ligne? C'est dommage car c'est plus simple d'avoir un TCD mais ça me semble difficile de lui faire afficher le total...Non?

Pour afficher le total il suffit de modifier les formules sur ce modèle

=SI(A7="";0;SI(GAUCHE(A7;5)<>"total";SOMME.SI.ENS(Tableau1[Nb contacts / h];Tableau1[Taxon nom vernaculaire];B7);SOMME(AS$6:AS6)))

En fait, il me reste à attribuer la mise en forme du début du TCD aux deux dernières colonnes. Car quand je copie les valeurs des deux dernières colonnes à partir de deux autres TCD, il perd la mise en forme.

Sur l'exemple que j'ai de TCD seul la ligne des totaux nécessite une Mise en forme. Faisable en 3 MFC.

Evite les copier coller... et des TCD inutiles dans la mesure ou, ici, ils n'apportent rien par rapport aux formules pour compléter le 1er TCD