Calculer des valeurs dans un tableau croisé dynamique

Pour calculer des valeurs dans un tableau croisé dynamique, vous pouvez utiliser tout ou partie des méthodes suivantes :

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

• Permettent de générer les tableaux et graphiques croisés dynamiques suivants. Si vous créez un graphique croisé dynamique à partir des données d’un tableau croisé dynamique, les valeurs incluses dans ce graphique reflèteront les calculs dans le rapport de tableau croisé dynamique associé.

• Dans le tableau croisé dynamique, le champ de colonne Mois fournit les éléments Mars et Avril. Le champ de ligne Région fournit les éléments Nord, Sud, Est et Ouest. La valeur située à l’intersection de la colonne Avril et de la ligne Nord représente le chiffres d’affaire total des enregistrements de la source de données qui correspondent à la valeur MoisAvril et à la valeur RégionNord.

• Dans un 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 une série qui affiche les éléments Mars, Avril et Mai en tant que séries représentées dans la légende. Un champ Valeurs intitulé Somme des ventes peut contenir des marques de données qui représentent le chiffre d’affaires total de chaque région pour chaque mois. Par exemple, une marque de données pourrait représenter, par sa position sur l’axe vertical (ordonnées), le total des ventes enregistrées au mois d’Avril dans la région Nord.

• Pour calculer les champs de valeurs, 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	Synthétise
  Somme	Somme des valeurs. Il s’agit de la fonction par défaut pour les données numériques.
  Nombre	Nombre des valeurs des données. La fonction de synthèse Nombre revient à utiliser la fonctionNBVAL. Il s’agit de la fonction par défaut pour les données autres que les nombres.
  Moyenne	Moyenne des valeurs.
  Max.	Valeur la plus élevée.
  Min.	Valeur la moins élevée.
  Produit	Produit des valeurs.
  Nb	Nombre des valeurs de données qui sont des nombres. La fonction de synthèse Nb revient à utiliser la fonction NB.
  Écartype	Estimation de l’écart type d’une population pour laquelle l’échantillon correspond à un sous-ensemble de la population entière.
  Écartypep	Écart type d’une population qui constitue la totalité des données à synthétiser.
  Var	Estimation de la variance d’une population pour laquelle l’échantillon correspond un sous-ensemble de la population entière.
  Varp	Variance d’une population qui constitue la totalité des données à synthétiser.

• Calculs personnalisés    Un calcul personnalisé affiche des valeurs en fonction d’autres éléments ou cellules figurant dans la zone de données. Par exemple, vous pourriez afficher des valeurs dans le champ de données Total des ventes sous forme de pourcentage des ventes enregistrées en mars, ou sous forme de total cumulé des éléments figurant dans le champs Mois.

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

  Fonction	Résultat
  Pas de calcul	Affiche la valeur qui est entrée dans le champ.
  % du total général	Affiche des valeurs sous forme de pourcentage du total général de toutes les valeurs 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 série.
  % du total de la ligne	Affiche la valeur 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 la forme d’un pourcentage de la valeur de l’élément de base dans le champ de base.
  % du total de la ligne parente	Calcule des valeurs comme suit : (valeur pour l’élément) / (valeur pour l’élément parent dans les lignes)
  % du total de la colonne parente	Calcule des valeurs comme suit : (valeur pour l’élément) / (valeur pour l’élément parent dans les colonnes)
  % du total parent	Calcule des valeurs comme suit : (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 la forme d’une différence de la valeur de l’élément de base dans le champ de base.
  Différence en % par rapport	Affiche les valeurs sous la forme d’une différence de pourcentage de la valeur de l’élément de base dans le champ de base.
  Résultat cumulé par	Affiche la valeur 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 en tant que total cumulé sous forme de 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 faible une valeur de classement supérieure.
  Index	Calcule des valeurs comme suit : ((valeur dans la cellule) x (total général des totaux généraux)) / ((total ligne générale) x (total colonne générale))

• Formules    Si les fonctions de synthèse et les calculs personnalisés ne vous permettent pas d’obtenir le résultat voulu, vous pouvez créer vos propres formules dans des champs calculés et des éléments calculés. Par exemple, vous pouvez ajouter un élément calculé qui inclut la formule permettant de calculer la commission sur les ventes, laquelle peut différer en fonction de la région. Le rapport inclura alors automatiquement la commission dans les sous-totaux et totaux généraux.

Les calculs et les options disponibles dans un rapport varient si les données proviennent d’une base de données OLAP ou d’une source de données non-OLAP.

• Calculs basés sur des données sources OLAP    Pour les tableaux croisés dynamiques qui ont été créés à partir de cubes OLAP, les valeurs synthétisées sont précalculées sur le serveur OLAP avant qu’Excel affiche les résultats. Vous ne pouvez pas modifier le mode de calcul de ces valeurs précalculées dans le tableau croisé dynamique. Par exemple, vous ne pouvez pas modifier la fonction de synthèse servant à calculer des champs de données ou des sous-totaux, ni ajouter de champs ou éléments calculés.

  De plus, si le serveur OLAP fournit des champs calculés, également appelés membres calculés, ces champs figureront dans la liste des champs du tableau croisé dynamique. Vous verrez également les champs calculés et les éléments calculés créés par des macros qui ont été écrites dans Visual Basic pour Applications (VBA) et stockées dans votre classeur, mais vous ne pourrez pas modifier ces champs ou éléments. Si vous avez besoin de types de calculs supplémentaires, contactez votre administrateur de base de données OLAP.

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

• Calculs basés sur des données sources non OLAP    Dans les 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 qui contiennent des données numériques, et la fonction de synthèse Nb pour calculer les champs de données qui contiennent du texte. Vous pouvez choisir une autre fonction de synthèse, par exemple, Moyenne, Max ou Min, pour analyser et personnaliser davantage vos données. Vous pouvez également créer des formules utilisant 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.

Vous ne pouvez créer des formules que dans les rapports basés sur des données sources 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 les tableaux croisés dynamiques, vous devez connaître les règles de syntaxe et le comportement des formules suivants :

• Éléments de formule de 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 vous le faites dans d’autres formules de feuille 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 qui nécessitent des références de cellules ou des noms définis en tant qu’arguments, et vous ne pouvez pas utiliser de fonctions de tableau.

• Noms de champs et d’éléments    Excel utilise des noms de champs et d’éléments pour identifier ces éléments d’un rapport dans vos formules. Dans l’exemple suivant, les données figurant dans la plage C3:C9 utilisent le nom de champ Produits laitiers. Un élément calculé dans le champ Type qui calcule une estimation des ventes pour un nouveau produit en fonction des ventes de produits laitiers peut utiliser une formule telle que =Produits laitiers * 115%.

  

  : Dans un graphique croisé dynamique, les noms des champs sont affichés dans la Liste de champs de tableau croisé dynamique, et les noms des éléments peuvent être consultés dans la liste déroulante de chaque champ. Ne confondez pas ces noms avec ceux qui sont affichés dans les info-bulles des graphiques, lesquels reflètent les noms des séries et des points de données.

• Les formules fonctionnent sur les totaux des sommes, et non sur des enregistrements individuels    Les formules associées aux champs calculés fonctionnent sur la somme des données sous-jacentes pour tous les champs inclus dans 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 ; elle ne multiplie pas les ventes de chaque type et région par 1,2 pour ensuite additionner les montants multipliés.

  Les formules associées aux éléments calculés fonctionnent sur les enregistrements individuels. Par exemple, la formule d’élément calculé =Produits laitiers *115% multiplie chaque vente individuelle de Produits laitiers par 115 %, pour ensuite synthétiser les montants multipliés dans la zone Valeurs.

• Espaces, nombres et symboles dans les noms    Dans un nom qui inclut plusieurs champs, les champs peuvent être classés dans n’importe quel ordre. Dans l’exemple ci-dessus, les cellules de la plage C6:D6 peuvent prendre indifféremment les valeurs 'Avril Nord' ou 'Nord Avril'. Utilisez des guillemets simples autour des noms qui sont composés de plusieurs mots ou qui incluent des nombres 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 des champs dans les références aux é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 placé entre crochets, par exemple, Région[Nord]. Utilisez ce format pour éviter les erreurs #NOM? lorsque deux éléments dans deux champs distincts d’un rapport portent le même nom. Par exemple, si un rapport comporte un élément intitulé Viande dans le champ Type et un autre élément intitulé Viande dans le champ Catégorie, vous pouvez éviter les erreurs #NOM? en faisant référence à l’élément sous la forme Type[Viande] et Catégorie[Viande].

• Référence aux éléments par position    Vous pouvez faire référence à un élément d’après sa position dans le rapport tel qu’il est trié et affiché actuellement. Type[1] fait référence à Produits laitiers et Type[2] fait référence à Poissons et fruits de mer. L’élément auquel il est fait référence de cette manière peut changer lorsque les positions des éléments changent ou lorsque 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 utiliser des positions relatives pour faire référence aux éléments. Les positions sont déterminées de manière relative par rapport à l’élément calculé qui contient la formule. Si Sud est la région actuelle, Région[-1] est Nord ; si Nord est la région actuelle, Région[+1] est Sud. Par exemple, un élément calculé peut utiliser la formule =Région[-1] * 3%. Si la position que vous attribuez se trouve avant le premier élément ou après le dernier élément dans le champ, la formule génère une erreur #REF! .

Pour utiliser des formules dans un graphique croisé dynamique, vous devez créer les formules dans le tableau croisé dynamique associé, où vous pouvez afficher les valeurs individuelles qui constituent vos données. Vous pouvez ensuite afficher les résultats de manière graphique dans le graphique croisé dynamique.

Par exemple, le graphique croisé dynamique suivant affiche les ventes de chaque commercial par région :

Pour découvrir à quoi ressembleraient les ventes si elles augmentaient de 10 %, vous pouvez créer dans le tableau croisé dynamique associé un champ calculé utilisant une formule telle que =Vente * 110%.

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

Pour afficher une marque de données distincte pour les ventes dans la région Nord amputées d’un coût de transport de 8 %, vous pouvez créer un élément calculé dans le champ Région avec une formule telle que =Nord – (Nord * 8%).

Le graphique qui en résulte ressemblerait à celui-ci :

Toutefois, un élément calculé créé dans le champ Commercial apparaîtrait en tant que série représentée dans la légende et apparaîtrait en tant que point de données dans chaque catégorie.