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

  

  Remarque : 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.

Avant de commencer, décidez si vous voulez utiliser un champ calculé ou un élément calculé faisant partie d’un champ. Nous vous conseillons d’utiliser un champ calculé si vous souhaitez utiliser les données d’un autre champ dans votre formule. Utilisez un élément calculé si vous souhaitez que votre formule utilise des données provenant d’un ou de plusieurs éléments spécifiques dans un champ.

Pour les éléments calculés, vous pouvez entrer différentes formules cellule après cellule. Par exemple, si un élément calculé intitulé ComtéOrange est associé à la formule =Oranges * 0,25 pour tous les mois, vous pouvez remplacer la formule par =Oranges *0,5 pour Juin, Juillet et Août.

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 tableau croisé dynamique.

   Cela permet d’afficher les outils de tableau croisé dynamique, ainsi que les onglets Analyse et Création.

2. Dans l’onglet Analyse, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis cliquez sur Champ calculé.

   

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

4. Dans la zone Formule, entrez la formule associée au champ.

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

5. Cliquez sur Ajouter.

Ajouter un élément calculé à un champ

1. Cliquez sur le tableau croisé dynamique.

   Cela permet d’afficher les outils de tableau croisé dynamique, ainsi que les onglets Analyse et Création.

2. Si les éléments du champ sont groupés, dans l’onglet Analyse, dans le Groupe, cliquez sur Dissocier.

   

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

4. Dans l’onglet Analyse, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis cliquez sur Élément calculé.

   

5. Dans la zone Nom, entrez un nom pour l’élément calculé.

6. Dans la zone Formule, entrez la formule associée à l’élément.

   Pour utiliser les données d’un élément dans la formule, cliquez sur l’é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é).

7. Cliquez sur Ajouter.

Entrer différentes formules cellule après cellule pour les éléments calculés

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

   Pour modifier la formule pour plusieurs cellules, maintenez la touche Ctrl enfoncée et cliquez sur les autres cellules.

2. Dans la barre de formule, tapez les modifications à apporter à la formule.

Ajuster l’ordre de calcul pour plusieurs élément calculés ou formules

1. Cliquez sur le tableau croisé dynamique.

   Cela permet d’afficher les outils de tableau croisé dynamique, ainsi que les onglets Analyse et Création.

2. Dans l’onglet Analyse, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis cliquez sur Ordre de résolution.

   

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

4. Continuez jusqu’à ce que les formules se trouvent dans l’ordre de calcul souhaité.

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

1. Cliquez sur le tableau croisé dynamique.

   Cela permet d’afficher les outils de tableau croisé dynamique, ainsi que les onglets Analyse et Création.

2. Dans l’onglet Analyse, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis cliquez sur Liste des formules.

   

Avant de modifier une formule, déterminez si cette formule se trouve dans un champ calculé ou un élément calculé. Si la formule se trouve dans un élément calculé, déterminez également si la formule est la seule à être associée à l’élément calculé.

Pour les éléments calculés, vous pouvez modifier des formules individuelles associées à des cellules spécifiques d’un élément calculé. Par exemple, si un élément calculé intitulé CalcOrange est associé à la formule =Oranges * 0,25 pour tous les mois, vous pouvez remplacer la formule par =Oranges *0,5 pour Juin, Juillet et Août.

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

1. Cliquez sur le tableau croisé dynamique.

   Cela permet d’afficher les outils de tableau croisé dynamique, ainsi que les onglets Analyse et Création.

2. Dans l’onglet Analyse, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis cliquez sur Liste des formules.

   

3. Dans la liste des formules, recherchez la formule que vous voulez modifier sous Champ calculé ou Élément calculé.
   
   Lorsque plusieurs formules sont associées à un élément calculé, la formule par défaut qui a été entrée lors de la création de l’élément comporte le nom de l’élément calculé dans la colonne B. Pour les autres formules associées à un élément calculé, la colonne B contient le nom de l’élément calculé et les noms des éléments qui s’intersectent.

   Par exemple, une formule par défaut peut être associée à un élément calculé intitulé MonÉlément, et une autre formule peut être associée à l’élément sous le nom MonÉlément Ventes Janvier. Dans le tableau croisé dynamique, vous trouveriez cette formule dans la cellule Ventes, à l’intersection de la ligne MonÉlément et de la colonne Janvier.

4. Continuez en utilisant une des méthodes d’édition suivantes.

Modifier une formule de champ calculé

1. Cliquez sur le tableau croisé dynamique.

   Cela permet d’afficher les outils de tableau croisé dynamique, ainsi que les onglets Analyse et Création.

2. Dans l’onglet Analyse, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis cliquez sur Champ calculé.

   

3. Dans la zone Nom, sélectionnez le champ calculé pour lequel vous voulez modifier la formule.

4. Dans la zone Formule, modifiez la formule.

5. Cliquez sur Modifier.

Modifier une seule formule associée à un élément calculé

1. Cliquez sur le champ qui contient l’élément calculé.

2. Dans l’onglet Analyse, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis cliquez 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.

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

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

   Pour modifier la formule pour plusieurs cellules, maintenez la touche Ctrl enfoncée et cliquez sur les autres cellules.

2. Dans la barre de formule, tapez les modifications à apporter à la formule.

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

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, tandis que les éléments apparaissent en tant qu’éléments au sein d’autres champs.

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

   • Pour supprimer un champ calculé, cliquez n’importe où dans le tableau croisé dynamique.

   • Pour supprimer un élément calculé, dans le tableau croisé dynamique, cliquez sur le champ qui contient l’élément à supprimer.

     Cela permet d’afficher les outils de tableau croisé dynamique, ainsi que les onglets Analyse et Création.

3. Dans l’onglet Analyse, dans le groupe Calculs, cliquez sur Champs, éléments et jeux, puis cliquez sur Champ calculé ou sur Élément calculé.

   

4. Dans la zone Nom, sélectionnez le champ ou l’élément à supprimer.

5. Cliquez sur Supprimer.