Calculer des valeurs dans un rapport de tableau croisé dynamique

Excel 2010 vous permet de tirer parti d’options de calcul amélioré.
Essayer Office 365.

Dans les rapports de tableau croisé dynamique, vous pouvez utiliser les fonctions de synthèse dans les champs de valeur pour combiner les valeurs des données source sous-jacentes. Si les fonctions de résumé et les calculs personnalisés ne permettent pas d’obtenir les résultats souhaités, vous pouvez créer vos propres formules dans des champs calculés et des éléments calculés. Par exemple, vous pourriez ajouter un élément calculé à la formule relative à la commission sur ventes, qui pourrait être différente pour chaque région. Le rapport de tableau croisé dynamique contiendra automatiquement la commission dans les sous-totaux et les totaux généraux.

Que voulez-vous faire ?

En savoir plus sur le calcul des valeurs dans un rapport de tableau croisé dynamique

Créer une formule dans un rapport de tableau croisé dynamique

Afficher toutes les formules utilisées dans un rapport de tableau croisé dynamique

Modifier une formule dans un tableau croisé dynamique

Supprimer une formule d’un tableau croisé dynamique

En savoir plus sur le calcul des valeurs dans un rapport de tableau croisé dynamique

Les rapports de tableau croisé dynamique permettent de calculer des données. Apprenez-en davantage sur les méthodes de calcul disponibles, la manière dont les calculs sont affectés par le type des données sources et l’utilisation des formules dans les rapports de tableau croisé dynamique et de graphique croisé dynamique.

Méthodes de calcul disponibles

Pour calculer des valeurs dans un rapport de tableau croisé dynamique, vous pouvez utiliser les types de méthodes de calcul suivants :

  • Fonctions de synthèse dans les champs de valeur    Les données dans la zone Valeurs synthétisent les données sources sous-jacentes dans le rapport de tableau croisé dynamique. Par exemple, les données sources suivantes :

    Example of PivotTable source data

  • Produisent les rapports de tableau croisé dynamique et de graphique croisé dynamique suivants. Si vous créez un rapport de graphique croisé dynamique à partir des données dans un rapport de tableau croisé dynamique, les valeurs dans ce rapport de graphique croisé dynamique reflètent les calculs dans le rapport de tableau croisé dynamique associé.

    Example of PivotTable report

    Example of a PivotChart report

  • Dans le rapport de tableau croisé dynamique, le champ de colonne Mois propose les éléments Mars et Avril. Le champ de ligne Région propose les éléments Nord, Sud, Est et Ouest. La valeur située à l’intersection de la colonne Avril et de la ligne Nord correspond au total des recettes relevées dans les enregistrements des données sources ayant la valeur de MoisAvril et la valeur de RégionNord.

  • Dans un rapport de graphique croisé dynamique, le champ Région peut être un champ de catégorie qui affiche les catégories Nord, Sud, Est et Ouest. Le champ Mois peut être un champ de série qui affiche les éléments Mars, Avril et Mai comme série représentée dans la légende. Un champ de type Valeurs nommé Montant des ventes pourrait contenir des marqueurs de données représentant les recettes totales réalisées dans les différentes régions chaque mois. Par exemple, un marqueur de données pourrait représenter, par sa position sur l’axe vertical (ordonnées), le total des ventes réalisées en Avril dans la région Nord.

  • Pour calculer les champs de valeur, les fonctions de synthèse suivantes sont disponibles pour tous les types de données sources à l’exception des données sources OLAP (Online Analytical Processing).

Fonction

Pour synthétiser

Somme

La somme des valeurs. Il s’agit de la fonction par défaut pour les données numériques.

Nb

Nombre de valeurs de données. La fonction Nb est similaire à la fonction feuille de calcul NBVAL. Nb est la fonction par défaut lorsque les données ne sont pas de type numérique.

Moyenne

La moyenne des valeurs.

Max

La valeur la plus élevée.

Min

La valeur la moins élevée.

Produit

Le produit des valeurs.

Nbval

Nombre de valeurs de données qui sont de type numérique. La fonction Nbval est similaire à la fonction de feuille de calcul NB.

Ecarttype

Une estimation de l’écart type d’une population pour laquelle l’échantillon correspond à un sous-ensemble de la population entière.

Ecarttypep

L’écart type d’une population qui constitue la totalité des données à synthétiser.

Var

Une estimation de la variance d’une population pour laquelle l’échantillon correspond un sous-ensemble de la population entière.

Var.p

La variance d’une population qui constitue la totalité des données à synthétiser.

  • Calculs personnalisés    Un calcul personnalisé affiche des valeurs basées sur d’autres éléments ou cellules dans la zone de données. Par exemple, vous pouvez afficher les valeurs dans le champ de données Montant des ventes sous la forme d’un pourcentage des ventes réalisées en Mars ou d’un total cumulé des éléments figurant dans le champ Mois.

    Les fonctions suivantes sont disponibles pour les calculs personnalisés dans des champs de valeurs.

Fonction

Résultat

Différence par rapport

Affiche les valeurs comme la différence par rapport à la valeur de l’élément de base dans le Champ de base.

% de

Affiche les valeurs sous forme de pourcentage de la valeur de l’élément de base dans le Champ de base.

Différence en % par rapport

Affiche les valeurs comme la différence en pourcentage par rapport à la valeur de l’élément de base dans le Champ de base.

Résultat cumulé par

Affiche les valeurs pour des éléments successifs dans le Champ de base sous forme de total cumulé.

% par ligne

Affiche les valeurs de chaque ligne ou catégorie sous forme de pourcentage du total de la ligne ou de la catégorie.

% par colonne

Affiche toutes les valeurs de chaque colonne ou série sous forme de pourcentage du total de la colonne ou des séries.

% du total

Affiche les valeurs sous forme de pourcentage du total général de toutes les données ou points de données du rapport.

Index

Calcule les valeurs de la façon suivante :

((valeur dans la cellule) x (Total général)) / ((Total général de la ligne) x (Total général de la colonne)).

Pour plus d’informations, voir Modifier la fonction de synthèse ou un calcul personnalisé d’un champ dans un rapport de tableau croisé dynamique.

  • Formules    Si les fonctions de synthèse et les calculs personnalisés ne permettent pas d’obtenir les résultats souhaités, vous pouvez créer vos propres formules dans des champs calculés et des éléments calculés. Par exemple, vous pourriez ajouter un élément calculé à la formule relative à la commission sur ventes, qui pourrait être différente pour chaque région. Le rapport contiendra automatiquement la commission dans les sous-totaux et les totaux généraux.

Haut de la page

Effet du type de données sources sur les calculs

Les calculs et les options disponibles dans un rapport varient suivant que les données source proviennent d’une base de données OLAP ou d’un autre type de source de données.

  • Calculs basés sur des sources de données OLAP    Pour les rapports créés à partir de cubes OLAP, les valeurs de synthèse sont calculées d’avance sur le serveur OLAP avant qu’Excel affiche les résultats. Vous ne pouvez pas modifier le mode de calcul de ces valeurs calculées d’avance dans le rapport de tableau croisé dynamique. Par exemple, vous ne pouvez pas modifier la fonction de synthèse utilisée pour calculer les champs de données ou les sous-totaux, ou ajouter des champs calculés ou des éléments calculés.

    Par ailleurs, si le serveur OLAP inclut des champs calculés (également appelés membres calculés), ils apparaissent dans la liste de champs de tableau croisé dynamique. Les champs calculés et les éléments calculés créés par des macros écrites dans Visual Basic pour Applications et enregistrées dans votre classeur apparaissent également. Vous ne pourrez toutefois pas les modifier. Si vous avez besoin d’autres types de calculs, contactez votre administrateur de base de données OLAP.

    Pour des données sources OLAP, vous pouvez inclure ou exclure les valeurs des éléments masqués lors des calculs de sous-totaux et de totaux généraux.

  • Calculs basés sur des données sources de type différent d’OLAP    Dans les rapports de tableau croisé dynamique basés sur d’autres types de données externes ou sur des données de feuille de calcul, Excel utilise la fonction de synthèse SOMME pour calculer les champs de valeurs contenant des données numériques, et la fonction de synthèse COMPTE pour calculer les champs de données contenant du texte. Vous pouvez choisir une fonction de résumé différente (telle que MOYENNE, MAX ou MIN) pour approfondir et personnaliser vos données. Vous pouvez également créer vos propres formules qui utilisent des éléments du rapport ou d’autres données de feuille de calcul, en créant un champ calculé ou un élément calculé dans un champ.

Haut de la page

Utilisation de formules dans des rapports de tableau croisé dynamique

Vous pouvez créer des formules uniquement dans les rapports basés sur des données sources d’un type autre que OLAP. Vous ne pouvez pas utiliser des formules dans les rapports basés sur une base de données OLAP. Lorsque vous utilisez des formules dans les rapports de tableau croisé dynamique, vous devez connaître les règles suivantes de syntaxe des formules et le comportement des formules :

  • Éléments de formule d’un tableau croisé dynamique    Dans les formules que vous créez pour les champs calculés et les éléments calculés, vous pouvez utiliser des opérateurs et des expressions de la même manière que pour les autres formules de feuilles de calcul. Vous pouvez utiliser des constantes et faire référence à des données du rapport, mais vous ne pouvez pas utiliser de références de cellules ou de noms définis. Vous ne pouvez pas utiliser de fonctions de feuille de calcul utilisant des références de cellules ou des noms définis comme arguments, et vous ne pouvez pas utiliser de fonctions de tableaux.

  • Noms de champ et d’élément    Excel utilise les noms de champ et d’élément pour identifier les éléments d’un rapport dans vos formules. Dans l’exemple suivant, les données dans la plage C3:C9 utilisent le nom du champ P. laitiers. Un élément calculé dans le champ Type estimant les ventes d’un nouveau produit sur la base des ventes de produits laitiers pourrait utiliser une formule telle que =P. laitiers * 115 %.

    Example PivotTable report

    Remarque   Dans un rapport de graphique croisé dynamique, les noms de champ s’affichent dans la liste de champs du tableau croisé dynamique et les noms d’élément dans la liste déroulante de chaque champ. Ne confondez pas ces noms avec ceux des info-bulles du graphique qui reflètent les noms des points de données et des séries.

  • Les formules agissent sur l’addition des sommes et non sur des enregistrements isolés    Les formules des champs calculés agissent sur les sommes des données sous-jacentes des champs de la formule. Par exemple, la formule de champ calculé =Ventes * 1,2 multiplie la somme des ventes pour chaque type et région par 1,2. Il ne multiplie pas chaque vente individuelle par 1,2, avant d’additionner les montants obtenus.

    Les formules pour les éléments calculés opèrent sur des enregistrements individuels. Par exemple, la formule d’élément calculé =P. laitiers * 115 % multiplie chaque vente P. laitiers par 115 %, après quoi les résultats des multiplications sont synthétisés dans la zone de valeurs.

  • Espaces, chiffres et symboles dans les noms    Dans un nom comportant plusieurs champs, les champs peuvent être définis dans n’importe quel ordre. Dans l’exemple ci-dessus, le nom des cellules C6:D6 peut être 'Avril Nord' ou 'Nord Avril'. Utilisez des apostrophes avant et après les noms comportant plusieurs mots, des chiffres ou des symboles.

  • Totaux    Les formules ne peuvent pas faire référence à des totaux (tels que Total Mars, Total Avril et Total général dans l’exemple).

  • Noms de champ dans les références à des éléments    Vous pouvez inclure le nom du champ dans une référence à un élément. Le nom de l’élément doit être inséré entre crochets (Région[Nord], par exemple). Utilisez ce format pour éviter les erreurs de type #NOM? lorsque deux éléments dans deux champs différents d’un rapport ont le même nom. Si, par exemple, un rapport comporte un élément appelé Viande dans le champ Type et un autre élément Viande dans le champ Catégorie, vous pouvez éviter les erreurs de type #NOM? en faisant référence aux éléments de la manière suivante : Type[Viande] et Catégorie[Viande].

  • Référence à des éléments à l’aide de leur emplacement    Vous pouvez faire référence à un élément en utilisant son emplacement dans le rapport, tel qu’il est trié et affiché. La valeur de Type[1] est P. laitiers et celle de Type[2] est Fruits de mer. L’élément auquel il est fait référence de cette façon peut changer lorsque les positions des éléments changent ou que différents éléments sont affichés ou masqués. Les éléments masqués ne sont pas répertoriés dans cet index.

    Vous pouvez faire référence à des éléments à l’aide de leur emplacement relatif. Les emplacements sont déterminés par rapport à l’élément calculé contenant la formule. Si Sud est la région active, Région[-1] est Nord. Si Nord est la région active, Région[+1] est Sud. Par exemple, un élément calculé pourrait utiliser la formule =Région[-1] * 3 %. Si l’emplacement indiqué se trouve avant le premier élément ou après le dernier élément du champ, la formule génère une erreur de type #REF.

Haut de la page

Utilisation de formules dans des rapports de graphique croisé dynamique

Pour utiliser des formules dans un rapport de graphique croisé dynamique, vous créez les formules du rapport de tableau croisé dynamique associé dans lequel vous pouvez visualiser les valeurs individuelles qui constituent vos données. Vous pouvez ensuite visualiser les résultats dans le rapport de graphique croisé dynamique.

Par exemple, le rapport de graphique croisé dynamique ci-dessous indique les ventes réalisées par chaque commercial pour chaque région :

PivotChart report showing sales for each salesperson per region

Pour obtenir un aperçu des ventes avec un accroissement de 10 %, vous pouvez créer un champ calculé dans le rapport de tableau croisé dynamique associé qui utilise une formule telle que =Ventes * 110 %.

Le résultat apparaît immédiatement dans le rapport de graphique croisé dynamique, comme illustré dans le graphique suivant :

PivotChart report showing sales increased by 10 percent per region

Pour voir un indicateur de données distinct pour les ventes dans la région Nord moins un coût de transport de 8 %, vous pourriez créer un élément calculé dans le champ Région avec une formule telle que =Nord – (Nord * 8 %).

Le graphique obtenu pourrait être le suivant :

PivotChart report with a calculated item.

Toutefois, un champ calculé créé dans le champ Vendeur, apparaîtrait sous forme de série représentée dans la légende et, dans le graphique, sous forme de point de données dans chaque catégorie.

Haut de la page

Créer une formule dans un rapport de tableau croisé dynamique

Important   Vous ne pouvez pas créer de formules dans un rapport de tableau croisé dynamique connecté à une source de données OLAP.

  1. Indiquez si vous pouvez placer un champ calculé ou un élément calculé dans un champ.

    • Optez pour un champ calculé quand vous voulez utiliser les données d’un autre champ dans votre formule.

    • Optez pour un élément calculé quand vous voulez que votre formule utilise des données d’un ou de plusieurs éléments spécifiques dans un champ.

  2. Effectuez l’une des opérations suivantes :

    Ajouter un champ calculé    

    1. Cliquez sur le rapport de tableau croisé dynamique.

    2. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Champ calculé.

    3. Dans la zone Nom, entrez un nom pour le champ.

    4. Dans la zone Formule, entrez la formule souhaitée pour le champ.

      Pour utiliser, dans la formule, des données provenant d’un autre champ, cliquez sur ce champ dans la zone Champs, puis cliquez sur Insérer des champs. Par exemple, pour calculer une commission de 15% sur chaque valeur du champ Ventes, vous pouvez entrer = Ventes * 15%.

    5. Cliquez sur Ajouter.

      Ajouter un champ calculé à un champ    

    6. Si les éléments contenus dans le champ sont regroupés, dans le groupe Groupe de l’onglet Options, cliquez sur Dissocier.

    7. Cliquez sur le champ dans lequel vous voulez ajouter l’élément calculé.

    8. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Élément calculé.

    9. Dans la zone Nom, tapez un nom pour l’élément calculé.

    10. Dans la zone Formule, entrez la formule souhaitée pour l’élément.

      Pour utiliser, dans la formule, les données d’un élément, cliquez sur cet élément dans la liste Éléments, puis cliquez sur Insérer un élément (l’élément doit provenir du même champ que l’élément calculé).

    11. Cliquez sur Ajouter.

    12. Si vous avez dissocié des éléments à l’étape 1, regroupez-les le cas échéant. Pour plus d’informations, voir Grouper les éléments dans un champ de tableau croisé dynamique.

  3. Pour les éléments calculés, vous pouvez entrer différentes formules cellule par cellule.

    Si, par exemple, un élément calculé appelé ComtéOrange a une formule =Oranges * 0,25 pour tous les mois, vous pouvez définir une autre formule pour juin, juillet et août (par exemple, =Oranges * 0,5).

    Procédez comme suit :

    1. Cliquez sur une cellule dont vous souhaitez modifier la formule.

      Pour modifier la formule de plusieurs cellules, cliquez sur les autres cellules tout en maintenant la touche Ctrl enfoncée.

    2. Dans la barre de formule, apportez les modifications souhaitées à la formule.

  4. Si vous avez plusieurs éléments calculés ou formules, ajustez l’ordre de calcul comme suit :

    1. Cliquez sur le rapport de tableau croisé dynamique.

    2. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Ordre de résolution.

    3. Cliquez sur une formule, puis sur Monter ou Descendre.

    4. Continuez jusqu’à ce que les formules soient affichées dans l’ordre de calcul souhaité.

Haut de la page

Afficher toutes les formules utilisées dans un rapport de tableau croisé dynamique

Pour afficher la liste de toutes les formules utilisées dans le rapport de tableau croisé dynamique actuel, procédez comme suit :

  1. Cliquez sur le rapport de tableau croisé dynamique.

  2. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Liste des formules.

Haut de la page

Modifier une formule dans un tableau croisé dynamique

  1. Indiquez si la formule se trouve dans un champ calculé ou dans un élément calculé. Si la formule se trouve dans un élément calculé, indiquez si elle est la seule pour cet élément calculé, en procédant comme suit :

    1. Cliquez sur le rapport de tableau croisé dynamique.

    2. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Liste des formules.

    3. Dans la liste des formules, recherchez la formule que vous voulez modifier dans la liste répertoriée sous Champ calculé ou sous Élément calculé.

      Quand il existe plusieurs formules pour un élément calculé, la formule par défaut entrée lors de la création de l’élément porte le nom de l’élément calculé, nom qui apparaît dans la colonne B. Pour les autres formules définies pour un élément calculé, la colonne B contient à la fois le nom de l’élément calculé et le nom des éléments se trouvant à cette intersection.

      Vous pouvez, par exemple, avoir une formule par défaut pour un élément calculé nommé MonÉlément et une autre formule pour cet élément, identifiée par Ventes de janvier de MonÉlément. Dans le rapport de tableau croisé dynamique, cette formule se trouverait dans la cellule Ventes de la ligne MonÉlément et de la colonne Janvier.

  2. Effectuez l’une des opérations suivantes :

    Modifier une formule de champ calculé   

    1. Cliquez sur le rapport de tableau croisé dynamique.

    2. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Champ calculé.

    3. Dans la zone Nom, sélectionnez le champ calculé dont vous souhaitez modifier la formule.

    4. Dans la zone Formule, modifiez la formule.

    5. Cliquez sur Modifier.

      Modifier une seule formule pour un élément calculé    

    6. Cliquez sur le champ contenant l’élément calculé.

    7. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Élément calculé.

    8. Dans la zone Nom, sélectionnez l’élément calculé.

    9. Dans la zone Formule, modifiez la formule.

    10. Cliquez sur Modifier.

      Modifier des formules individuelles pour des cellules spécifiques d’un élément calculé   

      Si, par exemple, un élément calculé appelé CalculOrange a une formule =Oranges * 0,25 pour tous les mois, vous pouvez définir une autre formule pour juin, juillet et août (par exemple, =Oranges * 0,5).

    11. Cliquez sur une cellule dont vous souhaitez modifier la formule.

      Pour modifier la formule de plusieurs cellules, cliquez sur les autres cellules tout en maintenant la touche Ctrl enfoncée.

    12. Dans la barre de formule, apportez les modifications souhaitées à la formule.

  3. Si vous avez plusieurs éléments calculés ou formules, ajustez l’ordre de calcul comme suit :

    1. Cliquez sur le rapport de tableau croisé dynamique.

    2. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Ordre de résolution.

    3. Cliquez sur une formule, puis sur Monter ou Descendre.

    4. Continuez jusqu’à ce que les formules soient affichées dans l’ordre de calcul souhaité.

Haut de la page

Supprimer une formule d’un tableau croisé dynamique

Conseil   Si vous ne voulez pas supprimer une formule de façon définitive, vous pouvez masquer le champ ou l’élément. Pour masquer un champ, faites-le glisser hors du rapport.

  1. Indiquez si la formule se trouve dans un champ calculé ou dans un élément calculé.

    Les champs calculés apparaissent dans la liste des champs du tableau croisé dynamique. Les éléments calculés apparaissent sous la forme d’éléments à l’intérieur d’autres champs.

  2. Effectuez l’une des opérations suivantes :

    Supprimer un champ calculé   

    1. Cliquez sur le rapport de tableau croisé dynamique.

    2. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Champ calculé.

    3. Dans la zone Nom, sélectionnez sur le champ que vous souhaitez supprimer.

    4. Cliquez ensuite sur Supprimer.

      Supprimer un élément calculé   

    5. Cliquez sur le champ contenant l’élément que vous voulez supprimer.

    6. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Élément calculé.

    7. Dans la zone Nom, sélectionnez l’élément que vous souhaitez supprimer.

    8. Cliquez ensuite sur Supprimer.

Haut de la page

S’applique à : Excel 2007



Ces informations vous ont-elles été utiles ?

Oui Non

Comment pouvons-nous nous améliorer ?

255 caractères restants

Afin de protéger votre vie privée, n’indiquez aucune information de contact dans vos commentaires. Lisez notre déclaration de confidentialité.

Nous vous remercions pour vos commentaires.

Changer la langue