On continue dans la série des TCDs, avec une manipulation un peu particulière, mais très utile.
L'objectif de ce post est de calculer les différences entre deux années dans un TCD.
Supposons une petite table de données structurée comme suit :
On peut ensuite construire un tableau croisé dynamique (TCD) sur cette liste pour arriver à ce résultat:
Note : Pour construire le TCD, utilisez le menu Données (XL 2003) ou l'onglet Insertion - Tableaux croisés dynamiques (XL 2007/2010). Pour apprendre à utiliser les TCDs, vous pouvez aussi vous procurer mon cd de formation sur ce sujet.
Maintenant, nous voulons pouvoir afficher l'évolution de 2008 à 2009 sur ce même TCD.
Voici la manipulation :
Se positionner sur 2008 ou 2009, puis choisir dans la barre de formules du TCD (XL 2003) ou dans l'onglet Options (XL 2007/2010), Formules, Elément calculé...
Il ne reste plus qu'à ajouter la formule.
ATTENTION, il est fortement conseillé de cliquer sur les éléments plutôt que de les ajouter 'à la main', car sinon, ils pourraient être considérés comme des nombres et non des éléments du champ (par ex., ='2009' - '2008' est différent de ='2009'-2008).
Le résultat final ressemble donc à ceci :
Bien sûr, vous pouvez améliorer le tableau en
- supprimant les totaux qui sont ici sans intérêt (clic droit sur le tcd, puis options et décocher Totaux) et parfois inexact (une somme de % n'est pas une bonne idée),
- en y incorporant d'autres champs,
- ou encore des formules plus complexes (ici un calcul de % d'évolution).
Et voilà!
Si vous avez déjà utilisé cette fonctionnalité dans d'autres circonstances, n'hésitez pas à partager votre expérience ici.
A bientôt
Gaëtan Mourmant
Bonjour,
L’élément calculé est irremplaçable lorsqu’on a besoin de cette fonctionnalité.
Toutefois, attention, avoir des éléments calculé dans un tableau croisé dynamique présente deux inconvénients que je connaisse :
– Il ralentit les calculs du fichier tout entier lorsque la base est volumineuse
– il bride ou perturbe certaines autres fonctionnalités de ce même tableau croisé dynamique (mais je ne me souviens plus lesquelles ! Les totaux peut être ?)
Moralité : si certains problèmes apparaissent, souvenez vous que vous avez des éléments calculés dans un TcD de votre fichier.
Merci à Gaetan Mourmant, un des meilleurs spécialistes mondiaux.
merci
INTERESSANT A TESTER; MERCI
Bonjour,
J’encourage tous ceux qui adorent les TCD à aller encore plus loin en travaillant sur la formule SOMMEPROD.
Cette formule permet de faire tous les calculs possibles et imaginables (ex: nombre de voitures rouges de la marque X en version diesel vendues de telle date à telle date à un prix supérieur à 5000€ etc).
Seul inconvénient par rapport TCD: l’actualisation est “live” alors que le TCD est “figé” tant qu’on n’a pas cliqué sur le bouton actualiser).
Sans doute Gaëtan pourrait-il nous faire une super démo vidéo!
Bien à vous,
Antoine
Bonjour Antoine,
Le seul inconvénient de SommeProd, dont je suis aussi un grand adepte peut être lié à sa vitesse de recalcul quand on travaille sur une base de données.
L’autre différence entre sommeprod et tcd, c’est la gestion des nouveux items dans une base. Avec un TCD, ils sont automatiquement insérés dans le tableau, avec un sommeprod, il faut les insérer manuellement.
Je parle de sommeprod en vidéo dans mon dernier cd :
https://www.polykromy.com/html/excel-tableaux-de-bord.html ou sur celui des 77 exercices :
https://www.polykromy.com/html/poly_main_77.html
Cordialement
Gaetan
Bonjour,
Après tant de temps, je me permets de poster une toute petite question.
Est-on limité dans la taille du fichier ?
J’ai une erreur excel 2003 pour lecalcul de la diférence mensuelle des quantité de production, cause :
Imposible d’effectuer l’opération en raison d’un nombre excessifs d’enregistrements.
Merci.
Zeb
Excel 2003 a un maximum de 65 532 lignes. Si vous excédez ce nombre, ca peut certainement expliquer le problème.
Essayez de voir si sur 2007 ce problème pourrait être résolu.
Cordialement
Gaetan Mourmant
Merci,
Effectivement, je pense que ca doit être un truc dans le genre.
En fait suivant les colonnes d’en-tête sélectionnées, comme j’ai besoin d’affiner la sélection avec 4 champs et que j’ai déjà 20,000 lignes.. Ca devrait faire dans les 80.000 lignes…
J’ai aussi tester avec une seule sélection et ca a eu fonctionné.
Merci pour tes expliquations.
j’ai testé et c’est top mais lorsque je veux recopier la formule a toutes les lignes du tableau, cela ne fonctionne pas, je dois faire ligne par ligne. Le problème est que chaque mois j’ai 150 lignes à faire sur 3 tableaux différents.
Peut-on m’éclairer ?
Exemple :
2012 2013 evolution
xxxx 100 150 +50%
aaaa 50 20 +50%
bbbb 30 150 +50%
A Rikirk
Si vous utilisez un tableau croisé dynamique connecté à une table de données (onglet accueil, mise en forme de table), l’ajout de nouvelles lignes devrait automatiquement être inclus dans le tcd.
Vous pouvez m’envoyer un exemple sur mon adresse contact@polykromy.com
Cordialement
Gaetan Mourmant
Bonjour
Merci beaucoup pour cette aide
Je souhaite mettre le format en % pour un élément correspondant à un % d’évolution.
Quand je choisi le format % celui ci s’applique à tous les champs.
Comment faire pour remédier à ce problème
Cordialement
En sélectionnant seulement une cellule, est ce que ca fonctionne?
La mise en forme se fait sur un champ au complet, c’est le plus simple, en général.
Sinon, vous pouvez m’envoyer votre fichier pour que je regarde directement. (contact@polykromy.com)
Cordialement
Gaetan Mourmant
Bonjour,
Bien que ce poste ne soit plus très récent j’espère avoir une réponse.
J’ai créée un TCD avec en colonne les mois et ligne différent indicateur du P&L et ce que j’aimerai faire c’est calcul du taux de variation d’un mois année n par rapport à n-1. Seulement cela ne semble pas être possible en colonne mais seulement en ligne avec le calcul des “éléments calculés”. Je vous serais éternellement reconnaissante si pouviez m’aider.
Cordialement,
KR
Bonjour KR,
Pourriez vous m’envoyer votre fichier pour être certain de bien comprendre la question.
Là, c’est un peu flou.
Merci
Gaetan
Merci beaucoup pour cet éclaircissement mais quand est-il quand on souhaite faire un champ calculé sur le nombre de vente. exemple: ‘Nombre de vente 2009’- ‘nombre de vente 2008’
Il faut passer par élément calculé à la place de champ calculé.
Bonjour Gaetan,
J’ignore si vous lisez encore les commentaires de ce post, mais je tente ma chance.
J’ai un fichier assez lourd, de 50000 lignes et 20 colonnes, qui retraces les commissions que nous percevons.
Une des colonnes est l’exercice comptable de la commission.
J’extrais le données de N et N-1 pour faire un comparatif, j’ai donc dans mon exemple dans la colonne “Exercice comptable”, Exercice 2016 ou Exercice 2017.
Je créé un TCD avec l’exercice comptable en colonne, les clients en lignes et en valeurs mes commissions.
L’exercice comptable se subdivise donc en 2 colonnes, Exercice 2016 et Exercice 2017.
Je cherche à faire la différence des commissions entre 2017 et 2016, par client.
L’élément calculé répond parfaitement à mon besoin, toutefois lorsque je le lance, le fichier se bloque car je pense que le fichier est trop volumineux.
J’ai essayé de le laisser tourner une nuit entière mais rien n’y fait.
J’ai essayé de passer par un champ calculé en dissociant dans ma base de données l’exercice 2016 de l’exercice 2017 et les affectant sur 2 colonnes distinces, mais ça ne fonctionne pas non plus.
Les exercice étant sur des lignes séparées, le champs calculé Exercice 2017 – Exercice 2016 me donne des montants à 0.
Actuellement je copie en valeurs les données de mon TCD pour pouvoir calculer mes différences, mais la solution est loin d’être optimale.
Avez vous une idée?
Merci d’avance pour le temps que vous pourrez m’accorder.
Bien cordialement.
Bonjour,
Avez vous essayé en mettant une seule colonne pour l’année?
Autre possibilité si le problème tient à un temps de calcul interne au fichier, on peut se connecter au fichier via une connexion externe.
Vous pouvez m’envoyer le fichier par email en enlevant les données confidentielles pour que je regarde plus dans le détail.
Gaetan
Bonjour,
Je reviens sur ce sujet
Serait-il possible de transposer ce code en vba pour excel 2010?
Ceci pour rendre dynamique le choix des années à comparer, années variable sur un TCD généré par macro vba.
Je précise que dans mon TCD les cellules qui contiennent les années sont toujours aux mêmes endroits : exemple en B11 2016 et en B13 2017.
Mais je pourrais avoir B11 2015 et B13 2016
Comment écrire la formule dans le genre : “=(((‘annee1’ -‘annee2′ )/’annee2’)*100 )” ?
Jai essayé comme suivant:
Dim annee1 As Integer
Dim annee2 As Integer
annee1 = Range(“B13”).Value
annee2 = Range(“B11”).Value
ActiveSheet.PivotTables(“Tableau croisé dynamique18”).PivotFields( _
“Annee de mandatement”).CalculatedItems.Add “Taux de Variation N/N-1”, _
“=(((‘annee1’ -‘annee2′ )/’annee2’)*100 )”, True ‘*100
Sans succès.
Merci de votre aide.