Différence entre des années dans un TCD

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 :
Animation1

On peut ensuite construire un tableau croisé dynamique (TCD) sur cette liste pour arriver à ce résultat:

Animation2

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é...
Animation3

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).

Animation7

Le résultat final ressemble donc à ceci :

Animation8

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).

Animation11

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

Pour marque-pages : Permaliens.

19 réponses à Différence entre des années dans un TCD

  1. HANDELSMAN dit :

    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.

  2. mohamed dit :

    merci

  3. Abderrahim dit :

    INTERESSANT A TESTER; MERCI

  4. Antoine dit :

    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

  5. Gaetan Mourmant dit :

    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

  6. Zeb dit :

    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

  7. Gaetan Mourmant dit :

    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

  8. Zeb dit :

    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.

  9. Rikrik dit :

    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%

  10. Gaetan Mourmant dit :

    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

  11. Richard dit :

    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

  12. Gaetan dit :

    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

  13. KR dit :

    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

  14. Gaetan dit :

    Bonjour KR,
    Pourriez vous m'envoyer votre fichier pour être certain de bien comprendre la question.
    Là, c'est un peu flou.
    Merci
    Gaetan

  15. Mrfreeze117 dit :

    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'

  16. Gaetan dit :

    Il faut passer par élément calculé à la place de champ calculé.

  17. JVD dit :

    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.

  18. Gaetan dit :

    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

  19. roch2a dit :

    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.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *


La période de vérification reCAPTCHA a expiré. Veuillez recharger la page.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.