XL 2016 TCD avec source de données dynamique

[Newworld92]

Cher experts,

J'ai eu beau chercher dans les discussions existantes je n'ai pas trouvé de solution claire à mon problème qui, je pense, ne doit pas être très compliqué .

Je joins le fichier XL que j'utilise.

Contexte:
Mon fichier XL contient une feuille nommée "Rawdata" qui contient 10 lignes blanches destinées à donner à l'utilisateur des explications sur l'utilisation à en faire.
La ligne N°11 contient l'entête de mon tableau de données.
Les lignes suivantes N°12 à xx contiennent les données.
Le nombre de lignes à partir de la ligne 12 va varier selon les jours (c'est là que mon problème va se poser).

Mon fichier XL contient une feuille nommée "PIVOT table" qui contient un TCD nommé "TCD1" (à toutes fins utiles).
J'ai construit mon TCD manuellement.
Il fonctionne bien.

Objectif:
Maintenant, je souhaiterais rafraîchir mon TCD par macro (bouton "PREPARE" du fichier).
Avant de rafraîchir le TCD, l'utilisateur peut vouloir vider la feuille Rawdata à partir de la ligne 12 pour y mettre de nouvelles données.
Or lorsqu'on vide la feuille Rawdata, la source de données du TCD affiche "Rawdata!$A$11:$M$11" et semble ne se met pas à jour automatiquement lorsqu'on colle de nouvelles lignes dans Rawdata (après avoir supprimées les anciennes).
Bref, vous verrez que dans un premier temps, j'affiche un popup pour demander à l'utilisateur de saisir un mois et une année (vous pouvez laisser la valeur affichée par défaut pour tester).
Mon problème est que la Source de données du TCD ne se met pas à jour.

Dites-moi si je ne suis pas clair.

Merci d'avance pour votre aide.

 

[JM27]

bonjour
a tester
nota: cela va fonctionner si tu écris dans les zones de texte au dessous de la ligne 12 , et non pas dans les cellules

 

[JHA]

Bonjour à tous,

J'ai nommé "Plage" une plage dynamique pour le TCD

=DECALER(Rawdata!$A$11;;;EQUIV("zzz";Rawdata!$A$11:$A$1000;1);EQUIV("zzz";Rawdata!$11:$11;1))

A essayer
JHA

 

[Newworld92]

bonjour
a tester

Bonjour et merci à toi.
Je crois que cela fonctionne. Je vais éprouver ta solution mais ça m'a l'air bien parti.
Si j'ai bien compris ta modification, tu as fait en sorte de rafraîchir le TCD juste en ouvrant la feuille du TCD.
Comment as-tu fait pour appeler la Source de données DonnéesTCD et t'arranger pour qu'elle corresponde aux lignes de la feuille Rawdata ?
Je te pose la question pour pouvoir faire la même chose sur d'autres fichiers.
Encore merci.

 

[Newworld92]

Bonjour à tous,

J'ai nommé "Plage" une plage dynamique pour le TCD

=DECALER(Rawdata!$A$11;;;EQUIV("zzz";Rawdata!$A$11:$A$1000;1);EQUIV("zzz";Rawdata!$11:$11;1))

A essayer
JHA

Bonjour et merci à toi.
Comme JM27 tu as donné un nom à la Source de données.
Mais comment fais-tu le lien entre ce nom et la feuille Rawdata ?
quand j'essaie de faire la même chose dans un autre fichier, il me dit que plage n'est pas défini.
Egalement, par rapport à ta formule, que signifie zzz ?
Encore merci.

 

[JHA]

Bonjour à tous,

Pour le lien de la plage nommée, j'ai cliqué dans le TCD puis dans le ruban "outils de tableau croisé dynamique" onglet analyse changer la source de données et tu mets le nom "plage".
le "zzz" dans la formule sert à rechercher la dernière valeur texte des lignes et des colonnes. Comme il ne trouve pas "zzz" alors il indique la dernière donnée trouvée.

JHA

 

[chris]

Bonjour à tous

Je ne comprend pas le problème...

Si le tableau source est sous forme de tableau structuré il n'y a rien à faire à part actualiser le TCD basé sur le tableau structuré donc le vidage puis remplissage importe alors peu.
Il est préférable cependant de supprimer et non vider les lignes du tableau avant d'en coller d'autres pour éviter des vides.

Apprendre à utiliser les tableaux structurés d'Excel : création, manipulations et avantages, un tutoriel de Pierre Fauconnier

Salut.Je vous propose mon nouveau tutoriel: Les tableaux structurés Excel.Tout savoir sur la création, la manipulation et les avantages des tableaux structurés Excel, aussi appelés tables de données. Les tableaux structurés vous aident à gérer et analyser vos données de façon...

excel.developpez.com

Par ailleurs ce type de tableau à double entrée complique inutilement le TCD : on peut le normaliser via PowerQuery intégré à Excel (et filtrer les éventuels vides) : un simple Données, Actaliser Tout
suffit alors...

En cas d'ajout de colonne(s) à la source, rien à changer non plus.

 

[Newworld92]

JHA,
Lorsque je fais la manip' que tu décris, pour affecter le nom plage, Excel me renvoie le message "La référence n'est pas valide".
Ne faut-il pas au préalable définir une zone de la feuille Rawdata en tant que "plage" ? (ci-joint une copie écran).

 

[Newworld92]

bonjour
a tester
nota: cela va fonctionner si tu écris dans les zones de texte au dessus de la ligne 12 , et non pas dans les cellules

Egalement, je ne comprends pas la distinction que tu fais entre zone de texte et cellule ?
Je ne vois pas de différence quand je regarde dans le fichier.

 

[chris]

RE

Multipost sur au moins 3 forums... où le demandeur ne consulte pas les réponses ou n'a pas la politesse d'y répondre...

 

[JM27]

bonjour

=DECALER(Rawdata!$A$11;;;NBVAL(Rawdata!$A:$A);13)

la fonction NbVal ( qui est intégrée dans le nom DonnéesTCD) compte le nombre de valeurs dans la colonneA , si tu inscrit du texte dans les cellules colonne A de A1 à A10 , celles ci seront comptées. et la zone DonnéesTCD comptera les données dans ces cellules et la zone sera d'autant plus grande; par compte le données inscrites dans la zone de texte ne seront pas comptées.

 

[Newworld92]

RE

Multipost sur au moins 3 forums... où le demandeur ne consulte pas les réponses ou n'a pas la politesse d'y répondre...

Bonjour Chris,
Oui j'ai posté ma demande sur différents forums.
Je te remercie infiniment pour ta réponse qui me semble très intéressante.
Je ne connais pas PowerQuery et ne préfère donc pas mettre entre les mains de mes utilisateurs une solution que je ne maîtrise pas.
Je garde cependant précieusement ton fichier.
Encore merci à toi.
Bien cordialement.

 

[Newworld92]

bonjour

=DECALER(Rawdata!$A$11;;;NBVAL(Rawdata!$A:$A);13)

la fonction NbVal ( qui est intégrée dans le nom DonnéesTCD) compte le nombre de valeurs dans la colonneA , si tu inscrit du texte dans les cellules colonne A de A1 à A10 , celles ci seront comptées. et la zone DonnéesTCD comptera les données dans ces cellules et la zone sera d'autant plus grande; par compte le données inscrites dans la zone de texte ne seront pas comptées.

Bonjour JM27,
Merci. Je crois comprendre ta mise en garde.
Je n'ai pas prévu de mettre des données dans les cellules des lignes de 1 à 10. Uniquement dans les zones de texte donc tout devrait bien se passer.
Par contre, je ne parviens toujours pas à affecter le nom DonnéesTCD à la Source de données.
Peux-tu me détailler la manip' stp ?
DonnéesTCD a l'air de bien se caler sur les lignes 11 jusqu'à la dernière renseignée de la feuille Rawdata.
Comment as-tu fait pour que ça marche ?
Merci d'avance pour ton aide.

 

[JM27]

bonjour
comme te l'as dit JHA dans le post#6

Clic dans les données du TCD( feuille Pivot table)
puis

Changer la source de données

Et documenter la plage par le nom que tu as défini ( qui est une plage dynamique)
( attention à l'orthographe du nom)

Pour le lien de la plage nommée, j'ai cliqué dans le TCD puis dans le ruban "outils de tableau croisé dynamique" onglet analyse changer la source de données et tu mets le nom "plage".

 

[chris]

RE

Même si on ne passe pas par PowerQuery pour normaliser le tableau, mettre sous forme de tableau résoudrait le problème dont vous discutez depuis n échanges.

C'est une aberration de ne pas les utiliser sur 2016 : cela existe depuis plus de 16 ans !
La solution de nommage, on faisait cela avant...

Tu devrais vraiment lire le lien que j'ai donné. Tu aurais gagné du temps...