Calculer des valeurs dans un rapport de tableau croisé dynamique

Try Office 2010 In Excel 2010, you can take advantage of improved calculation options.
Lisez un article ou essayez Office 2010 !

Dans un rapport de tableau croisé dynamique, 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é à 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 ci-dessous :

  • 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.
Compte 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.

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 sous forme de 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 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é.
% 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 des totaux généraux)) / ((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 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 qui ont été créés par des macros é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 champs s’affichent dans la liste de champs du tableau croisé dynamique et les noms d’éléments dans la liste déroulante de chaque champ. Ne confondez pas ces noms avec ceux affichés par les info-bulles du graphique, qui correspondent aux noms des points de données et des séries.

  • Les formules opèrent sur les totaux des sommes et non sur des enregistrements isolés    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 croisé 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 (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.).

  1. Déterminez si vous souhaitez placer un champ calculé ou un élément calculé dans un champ.
  2. Effectuez l’une des actions suivantes.

Pour 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%.

  1. Cliquez sur Ajouter.

Pour ajouter un champ calculé à un champ    

  1. Si les éléments contenus dans le champ sont regroupés, dans le groupe Groupe de l’onglet Options, cliquez sur Dissocier.
  2. Cliquez sur le champ dans lequel vous voulez ajouter l’élément calculé.
  3. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Élément calculé.
  4. Dans la zone Nom, tapez un nom pour l’élément calculé.
  5. 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.
  2. 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
  1. 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 la 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 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.
  1. 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 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 Haut de la page

Modifier une formule de tableau croisé dynamique

  1. Déterminez 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é, déterminez 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 (é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. Effectuez l’une des actions suivantes :

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

Pour modifier une seule formule pour un élément calculé    

  1. Cliquez sur le champ contenant l’élément calculé.
  2. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Élément calculé.
  3. Dans la zone Nom, sélectionnez l’élément calculé.
  4. Dans la zone Formule, modifiez la formule.
  5. Cliquez sur Modifier.

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

Si, par exemple, un élément calculé appelé CalculOrange a la 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).

  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.
  1. 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 Haut de la page

Supprimer une formule de tableau croisé dynamique

 Conseil   Si vous ne voulez pas supprimer la 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. 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é    

  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.

Pour supprimer un élément calculé    

  1. Cliquez sur le champ qui contient l’élément que vous voulez supprimer.
  2. Dans le groupe Outils de l’onglet Options, cliquez sur Formules, puis sur Élément calculé.
  3. Dans la zone Nom, sélectionnez l’élément que vous souhaitez supprimer.
  4. Cliquez ensuite sur Supprimer.

Haut de la page Haut de la page

 
 
S'applique à :
Excel 2007