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 :
http://www.polykromy.com/html/excel-tableaux-de-bord.html ou sur celui des 77 exercices :
http://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.