Calculer des valeurs dans un rapport de tableau croisé dynamique

Dans les rapports de tableaux croisés dynamiques (rapport de tableau croisé dynamique : rapport interactif sous forme de tableau croisé Microsoft Excel qui synthétise et analyse les données, telles que des enregistrements de base de données, de diverses sources, y compris celles externes à Excel.), vous pouvez utiliser les fonctions de synthèse (fonction de synthèse : type de calcul regroupant des données sources dans un rapport de tableau croisé dynamique ou une table de consolidation, ou lorsque vous insérez des sous-totaux automatiques dans une liste ou une base de données. Somme, Nbval et Moyenne sont des exemples de fonctions de synthèse.) dans les champs (champ : dans un rapport de tableau ou de graphique croisé dynamique, catégorie de données issue d’un champ situé dans les données de la source. Les rapports de tableau croisé dynamique comportent des champs de ligne, de colonne, de page et de données. Les rapports de graphique croisé dynamique comportent des champs de série, de catégorie, de page et de données.) de valeurs pour combiner des valeurs à partir des données source (données sources : liste ou table utilisée pour créer un rapport de tableau ou de graphique croisé dynamique. Les données sources peuvent provenir d’une liste ou d’une plage Microsoft Excel, d’une base de données ou d’un cube externe, ou d’un autre rapport de tableau croisé dynamique.) sous-jacentes. 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 (formule : suite de valeurs, références de cellule, noms, fonctions ou opérateurs dans une cellule permettant de générer une nouvelle valeur. Une formule commence toujours par le signe égal (=).) dans des champs calculés (champ calculé : champ d’un rapport de tableau croisé dynamique ou de graphique croisé dynamique qui utilise une formule que vous avez créée. Les champs calculés peuvent effectuer des calculs à partir du contenu d’autres champs du rapport de tableau croisé dynamique ou de graphique croisé dynamique.) et des éléments calculés (élément calculé : élément d’un champ d’un tableau croisé dynamique ou d’un graphique croisé dynamique qui utilise une formule que vous avez créée. Les éléments calculés peuvent effectuer des calculs à partir du contenu d’autres éléments d’un même champ d’un rapport de tableau croisé dynamique ou de graphique croisé dynamique.). Par exemple, vous pourriez ajouter un élément calculé avec la formule relative à la commission sur ventes, qui pourrait être différente pour chaque région. Le rapport de tableau croisé dynamique inclura automatiquement la commission dans les sous-totaux et les totaux généraux.

Que voulez-vous faire ?


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

Les rapports de tableaux croisés dynamiques fournissent plusieurs types de calculs. Découvrez les méthodes de calcul disponibles, comment le type de données source affecte les calculs et comment vous pouvez utiliser des formules dans des rapports de tableaux croisés dynamiques et de graphiques croisés dynamiques.

Méthodes de calcul disponibles

Pour calculer des valeurs dans un rapport de tableau croisé dynamique, vous pouvez utilisez une ou plusieurs des méthodes de calcul suivantes :

  • Fonctions de synthèse dans les champs de valeurs    Les données de la zone Valeurs synthétisent les données source sous-jacentes du rapport de tableau croisé dynamique. Par exemple, les données source suivantes :
Exemple de données source de tableau croisé dynamique
Exemple de rapport de tableau croisé dynamique Exemple de rapport de graphique croisé dynamique
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 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 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.

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

Fonction Résultat
Aucun calcul Affiche la valeur qui est entrée dans le champ.
% du total général Affiche les valeurs sous forme de pourcentage du total général de toutes les données ou points de données du rapport.
% du total de la colonne Affiche toutes les valeurs de chaque colonne ou série sous forme de pourcentage du total de la colonne ou de la série.
% du total de la ligne Affiche les valeurs de chaque ligne ou catégorie sous forme de pourcentage du total de la ligne ou de la catégorie.
% de Affiche les valeurs sous forme de pourcentage de la valeur de l’élément de base dans le Champ de base.
% du total de la ligne parente

Calcule les valeurs de la façon suivante :

(valeur pour l’élément) / (valeur pour l’élément parent dans les lignes)

% du total de la colonne parente

Calcule les valeurs de la façon suivante :

(valeur pour l’élément) / (valeur pour l’élément parent dans les colonnes)

% du total parent

Calcule les valeurs de la façon suivante :

(valeur pour l’élément) / (valeur pour l’élément parent du Champ de base sélectionné)

Différence par rapport Affiche les valeurs sous forme de différence par rapport à la valeur de l’élément de base dans le Champ de base.
Différence en % par rapport Affiche les valeurs sous forme de 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é.
% résultat cumulé par Calcule la valeur pour des éléments successifs dans le Champ de base qui s’affichent comme total cumulé en tant que pourcentage.
Classer du plus petit au plus grand Affiche le rang des valeurs sélectionnées dans un champ spécifique, en attribuant au plus petit élément dans le champ la valeur 1 et à chaque valeur plus élevée une valeur de classement supérieure.
Classer du plus grand au plus petit Affiche le rang des valeurs sélectionnées dans un champ spécifique, en attribuant au plus grand élément dans le champ la valeur 1 et à chaque valeur plus petite une valeur de classement supérieure.
Index

Calcule les valeurs de la façon suivante :

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

  • 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é avec la formule relative à la commission sur ventes, qui pourrait être différente pour chaque région. Le rapport inclura automatiquement la commission dans les sous-totaux et les totaux généraux.

Haut de la page Haut de la page

Effet du type de données source 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 base de données.

En outre, si le serveur OLAP fournit des champs calculés, appelés aussi membres calculés, ils apparaîtront dans la liste de champs du tableau croisé dynamique. Apparaîtront également tous les champs calculés et éléments calculés créés par des macros qui ont été écrites dans Visual Basic pour Applications (VBA (Visual Basic pour Applications) : version macrolangage de Microsoft Visual Basic qui sert à programmer des applications Windows. VBA est fourni avec plusieurs programmes Microsoft.) et stockées dans votre classeur, mais vous ne pourrez pas modifier ces champs ou éléments. Si des types de calculs supplémentaires sont nécessaires, contactez votre administrateur de base de données OLAP.

   Pour des données source 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 source non-OLAP     Dans des rapports de tableaux croisés dynamiques 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 NB pour calculer les champs de données contenant du texte. Vous pouvez choisir une fonction de synthèse 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 Haut de la page

Utilisation de formules dans les rapports de tableaux croisés dynamiques

Vous pouvez créer des formules uniquement dans des rapports basés sur des données source non-OLAP. Vous ne pouvez pas utiliser de formules dans les rapports basés sur une base de données OLAP. Lorsque vous utilisez des formules dans un rapport de tableau croisé dynamique, vous devez maîtriser les comportements et règles de syntaxe suivants relatifs aux formules :

Exemple de rapport de tableau croisé dynamique

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 opèrent sur les totaux des sommes et non sur les enregistrements individuels    Les formules des champs calculés opèrent sur la somme des données sous-jacentes des champs de la formule. Par exemple, la formule du champ calculé =Ventes * 1,2 multiplie par 1,2 la somme des ventes pour chaque type et région ; elle ne multiplie pas par 1,2 le montant de chaque vente avant de les additionner.

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 individuelle de produits laitiers par 115 %, après quoi les montants multipliés sont synthétisés dans la zone 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 la position des éléments change ou que différents éléments sont affichés ou masqués. Les éléments masqués ne sont pas pris en compte 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 la région en cours est Sud, Région[-1] est Nord ; si la région en cours est Nord, 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 Haut de la page

Utilisation de formules dans les rapports de graphiques croisés dynamiques

Pour utiliser des formules dans un rapport de graphique croisé dynamique, créez les formules dans le rapport de tableau croisé dynamique associé, dans lequel vous pouvez voir les valeurs individuelles constituant vos données, puis vous pouvez afficher les résultats sous forme graphique dans le rapport de graphique croisé dynamique.

Par exemple, le rapport de graphique croisé dynamique ci-dessous indique le chiffre d’affaires réalisé par chaque commercial pour chaque région :

Rapport de graphique croisé dynamique indiquant le chiffre d’affaires réalisé par chaque commercial pour chaque région

Pour afficher un résultat de ventes dans lequel celles-ci auraient augmenté de 10 pour cent, vous pourriez créer un champ calculé dans le rapport de tableau croisé dynamique utilisant une formule telle que =Ventes * 110%.

Le résultat s’affiche immédiatement dans le rapport de graphique croisé dynamique, comme illustré dans le graphique suivant :

Rapport de graphique croisé dynamique représentant une augmentation de 10 pour cent du chiffre d’affaires par région

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 résultant serait le suivant :

Rapport de graphique dynamique avec un élément calculé.

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

Haut de la page Haut de la page

Créer des formules 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 (Online Analytical Processing) (OLAP : technologie de base de données optimisée pour effectuer des requêtes et des rapports au lieu de traiter des transactions. Les données OLAP sont organisées par niveaux hiérarchiques et stockées dans des cubes au lieu de tables.).

Avant de démarrer, décidez si vous souhaitez placer un champ calculé ou un élément calculé dans un champ. Optez pour un champ calculé si vous voulez utiliser les données d’un autre champ dans votre formule. Optez pour un élément calculé si vous voulez que votre formule utilise des données d’un ou de plusieurs éléments (élément : sous-catégorie d’un champ de rapports de tableau ou de graphique croisé dynamique. Par exemple, le champ « Mois » peut comporter des éléments tels que « Janvier », « Février », etc.) spécifiques dans un champ.

Pour les éléments calculés, vous pouvez entrer différentes formules pour chaque 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).

Si vous avez plusieurs éléments calculés ou formules, vous pouvez ajuster l’ordre de calcul.

Ajouter un champ calculé

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

Cela permet d’afficher les Outils de tableau croisé dynamique, avec les onglets Options et Création.

  1. Sous l’onglet Options, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis sur Champ calculé.

Image du Ruban Excel

  1. Dans la zone Nom, entrez un nom pour le champ.
  2. 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%.

  1. Cliquez sur Ajouter.

Haut de la page Haut de la page

Ajouter un champ calculé à un champ

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

Cela permet d’afficher les Outils de tableau croisé dynamique, avec les onglets Options et Création.

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

Image du Ruban Excel

  1. Cliquez sur le champ dans lequel vous voulez ajouter l’élément calculé.
  2. Sous l’onglet Options, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis sur Élément calculé.

Image du Ruban Excel

  1. Dans la zone Nom, tapez un nom pour l’élément calculé.
  2. 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é).

  1. Cliquez sur Ajouter.

Haut de la page Haut de la page

Entrer différentes formules pour chaque cellule d’élément calculé

  1. Cliquez sur une cellule pour laquelle 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.

  1. Dans la barre de formule (barre de formule : barre située en haut de la fenêtre Microsoft Excel qui permet d’entrer ou de modifier des valeurs ou des formules dans des cellules ou des graphiques. Affiche la valeur constante ou la formule enregistrée dans la cellule active.), apportez les modifications souhaitées à la formule.

Haut de la page Haut de la page

Modifier l’ordre de calcul pour plusieurs éléments calculés ou formules

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

Cela permet d’afficher les Outils de tableau croisé dynamique, avec les onglets Options et Création.

  1. Sous l’onglet Options, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis sur Ordre de résolution.

Image du Ruban Excel

  1. Cliquez sur une formule, puis sur Monter ou Descendre.
  2. Continuez jusqu’à ce que les formules soient affichées dans l’ordre de calcul souhaité.

Haut de la page Haut de la page

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

Vous pouvez afficher la liste de toutes les formules utilisées dans le rapport de tableau croisé dynamique en cours.

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

Cela permet d’afficher les Outils de tableau croisé dynamique, avec les onglets Options et Création.

  1. Sous l’onglet Options, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis sur Liste des formules.

Image du Ruban Excel

Haut de la page Haut de la page

Modifier une formule de tableau croisé dynamique

Avant de modifier une formule, déterminez si elle se trouve dans un champ calculé ou dans un élément calculé. Si la formule se trouve dans un élément calculé, déterminez également si elle est la seule formule pour cet élément calculé.

Pour les éléments calculés, vous pouvez modifier des formules individuelles pour des cellules spécifiques d’un élément calculé. Si, par exemple, un élément calculé appelé CalcOrange 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).

Déterminer si une formule se trouve dans un champ calculé ou dans un élément calculé

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

Cela permet d’afficher les Outils de tableau croisé dynamique, avec les onglets Options et Création.

  1. Sous l’onglet Options, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis sur Liste des formules.

Image du Ruban Excel

  1. Dans la liste de formules, repérez la formule à modifier répertoriée sous Champ calculé ou É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 (élément : sous-catégorie d’un champ de rapports de tableau ou de graphique croisé dynamique. Par exemple, le champ « Mois » peut comporter des éléments tels que « Janvier », « Février », etc.) 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.

  1. Appliquez l’une des méthodes suivantes.

Modifier une formule de champ calculé

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

Cela permet d’afficher les Outils de tableau croisé dynamique, avec les onglets Options et Création.

  1. Sous l’onglet Options, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis sur Champ calculé.

Image du Ruban Excel

  1. Dans la zone Nom, sélectionnez le champ calculé dont vous souhaitez modifier la formule.
  2. Dans la zone Formule, modifiez la formule.
  3. Cliquez sur Modifier.

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

  1. Cliquez sur le champ contenant l’élément calculé.
  2. Sous l’onglet Options, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis sur Élément calculé.

Image du Ruban Excel

  1. Dans la zone Nom, sélectionnez l’élément calculé.
  2. Dans la zone Formule, modifiez la formule.
  3. Cliquez sur Modifier.

Modifier une formule individuelle pour une cellule spécifique d’un élément calculé

  1. Cliquez sur une cellule pour laquelle 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.

  1. Dans la barre de formule (barre de formule : barre située en haut de la fenêtre Microsoft Excel qui permet d’entrer ou de modifier des valeurs ou des formules dans des cellules ou des graphiques. Affiche la valeur constante ou la formule enregistrée dans la cellule active.), apportez les modifications souhaitées à la formule.

Tip    Si vous avez plusieurs éléments calculés ou formules, vous pouvez ajuster l’ordre de calcul: Pour plus d’informations, voir Modifier l’ordre de calcul pour plusieurs éléments calculés ou formules.

Haut de la page Haut de la page

Supprimer une formule de tableau croisé dynamique

Remarque    Lorsque vous supprimez une formule d’un tableau croisé dynamique, celle-ci est supprimée de façon définitive.Si vous ne voulez pas supprimer une formule de façon définitive, vous pouvez masquer le champ ou l’élément en le faisant glisser hors du rapport de tableau croisé dynamique.

  1. Déterminez 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ément : sous-catégorie d’un champ de rapports de tableau ou de graphique croisé dynamique. Par exemple, le champ « Mois » peut comporter des éléments tels que « Janvier », « Février », etc.) à l’intérieur d’autres champs.

  1. Effectuez l’une des actions suivantes :
    • Pour supprimer un champ calculé, cliquez dans le rapport de tableau croisé dynamique.
    • Pour supprimer un élément calculé, dans le rapport de tableau croisé dynamique, cliquez sur le champ contenant l’élément que vous voulez supprimer.

Cela permet d’afficher les Outils de tableau croisé dynamique, avec les onglets Options et Création.

  1. Sous l’onglet Options, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis sur Champ calculé ou Élément calculé.

Image du Ruban Excel

  1. Dans la zone Nom, sélectionnez le champ ou l’élément que vous souhaitez supprimer.
  2. Cliquez sur Supprimer.

Haut de la page Haut de la page

 
 
S'applique à :
Excel 2010