L’aspect le plus déroutant des tableaux croisés dynamiques est peut-être les champs calculés par rapport aux éléments calculés. De nombreux utilisateurs de tableaux croisés dynamiques évitent simplement de traiter les champs et éléments calculés.

Cependant, ces fonctionnalités peuvent être utiles, et elles ne sont vraiment pas si compliquées une fois que vous avez compris leur fonctionnement.

■ Un champ calculé : un nouveau champ créé à partir d’autres champs du tableau croisé dynamique. Si la source de votre tableau croisé dynamique est un tableau de feuille de calcul, une alternative à l’utilisation d’un champ calculé consiste à ajouter une nouvelle colonne au tableau et à créer une formule pour effectuer le calcul souhaité.

Un champ calculé doit résider dans la zone Valeurs du tableau croisé dynamique. Vous ne pouvez pas utiliser un champ calculé dans la zone Colonnes, dans la zone Lignes ou dans la zone Filtre.

■ Un élément calculé : utilise le contenu d’autres éléments dans un champ du tableau croisé dynamique. Si la source de votre tableau croisé dynamique est un tableau de feuille de calcul, une alternative à l’utilisation d’un élément calculé consiste à insérer une ou plusieurs lignes et à écrire des formules qui utilisent des valeurs dans d’autres lignes. Un élément calculé doit résider dans la zone Colonnes, la zone Lignes ou la zone Filtres d’un tableau croisé dynamique. Vous ne pouvez pas utiliser un élément calculé dans la zone Valeurs.

Les formules utilisées pour créer des champs calculés et des éléments calculés ne sont pas des formules Excel standard. En d’autres termes, vous n’entrez pas les formules dans les cellules. Au lieu de cela, vous entrez ces formules dans une boîte de dialogue et elles sont stockées avec les données du tableau croisé dynamique.

Les exemples de cette section utilisent le tableau de feuille de calcul illustré à la figure suivante.

Le tableau se compose de 5 colonnes et 48 lignes. Chaque ligne décrit les informations sur les ventes mensuelles d’un commercial particulier. Par exemple, Paul est une représentante commerciale pour la région Nord et elle a vendu 239 unités en janvier pour des ventes totales de 23 040 $.

La figure suivante montre un tableau croisé dynamique créé à partir des données. Ce tableau croisé dynamique affiche les ventes (zone Valeurs), croisées par Mois (zone Lignes) et par Responsable (zone Colonnes).

Les exemples qui suivent créent

■ Un champ calculé, pour calculer les ventes moyennes par unité

■ Quatre éléments calculés, pour calculer la commission de vente trimestrielle

Création d’un champ calculé

Étant donné qu’un tableau croisé dynamique est un type spécial de plage, vous ne pouvez pas insérer de nouvelles lignes ou colonnes dans le tableau croisé dynamique, ce qui signifie que vous ne pouvez pas insérer de formules pour effectuer des calculs avec les données d’un tableau croisé dynamique. Cependant, vous pouvez créer des champs calculés pour un tableau croisé dynamique. Un champ calculé consiste en un calcul qui peut impliquer d’autres champs.

Un champ calculé est essentiellement un moyen d’afficher de nouvelles informations (dérivées d’autres champs) dans un tableau croisé dynamique. C’est une alternative à la création d’un nouveau champ de colonne dans vos données source. Dans de nombreux cas, il peut être plus facile d’insérer une nouvelle colonne dans la plage source avec une formule qui effectue le calcul souhaité. Un champ calculé est particulièrement utile lorsque les données proviennent d’une source difficile à manipuler, telle qu’une base de données externe.

Dans l’exemple des ventes, supposons que vous souhaitiez calculer le montant moyen des ventes par unité. Vous pouvez calculer cette valeur en divisant le champ Ventes par le champ Unités vendues. Le résultat affiche un nouveau champ (un champ calculé) pour le tableau croisé dynamique.

Utilisez la procédure suivante pour créer un champ calculé composé du champ Ventes divisé par le champ Unités vendues :

1. Sélectionnez n’importe quelle cellule dans le tableau croisé dynamique.
2. Choisissez Outils de tableau croisé dynamique / Analyser / Calculs / Champs, éléments et ensembles / Champ calculé. La boîte de dialogue Insérer un champ calculé s’affiche.
3. Saisissez un nom descriptif dans la zone Nom et spécifiez la formule dans la zone Formule. La formule peut utiliser des fonctions de feuille de calcul et d’autres champs de la source de données. Pour cet exemple, le nom du champ calculé est Prix unitaire moyen et la formule est

=Ventes/’Unités vendues’

4. Cliquez sur Ajouter pour ajouter ce nouveau champ.
5. Cliquez sur OK pour fermer la boîte de dialogue Insérer un champ calculé.

REMARQUE

Vous pouvez créer la formule manuellement en la saisissant ou en double-cliquant sur des éléments dans la zone de liste Champs. Double-cliquer sur un élément le transféré dans le champ Formule. Étant donné que le champ Unités vendues contient un espace, Excel ajoute des guillemets simples autour du nom du champ.

Après avoir créé le champ calculé, Excel l’ajoute à la zone Valeurs du tableau croisé dynamique. (Il apparaît également dans le volet Office Champs de tableau croisé dynamique.) Vous pouvez le traiter comme n’importe quel autre champ, à une exception près : vous ne pouvez pas le déplacer vers les zones Lignes, Colonnes ou Filtres. Il doit rester dans la zone Valeurs.

La figure 34.16 montre le tableau croisé dynamique après l’ajout du champ calculé. Le nouveau champ affichait Somme du prix unitaire moyen, mais j’ai raccourci cette étiquette en unité vendu.

Ce tableau croisé dynamique utilise un champ calculé.

POINTE

Les formules que vous développez peuvent également utiliser des fonctions de feuille de calcul, mais les fonctions ne peuvent pas faire référence à des cellules ou à des plages nommées.

Insertion d’un élément calculé

La section précédente décrit comment créer un champ calculé. Excel vous permet également de créer un élément calculé pour un champ de tableau croisé dynamique. Gardez à l’esprit qu’un champ calculé peut être une alternative à l’ajout d’un nouveau champ (colonne) à votre source de données. Un élément calculé, en revanche, est une alternative à l’ajout d’une nouvelle ligne à la source de données – une ligne contenant une formule faisant référence à d’autres lignes.

Dans cet exemple, vous créez quatre éléments calculés. Chaque poste représente la commission gagnée sur les ventes du trimestre, selon le barème suivant :

■ Trimestre 1 : 10 % des ventes de janvier, février et mars

■ Trimestre 2 : 11 % des ventes d’avril, mai et juin

■ Trimestre 3 : 12 % des ventes de juillet, août et septembre

■ Trimestre 4 : 12,5 % des ventes d’octobre, novembre et décembre

REMARQUE

Modifier les données sources pour obtenir ces informations nécessiterait d’insérer 16 nouvelles lignes, chacune avec des formules (quatre formules pour chaque commercial). Ainsi, pour cet exemple, la création de quatre éléments calculés peut être une tâche plus facile.

Pour créer un élément calculé afin de calculer la commission pour janvier, février et mars, procédez comme suit :

1. Déplacez le pointeur de cellule vers la zone Étiquettes de ligne ou Étiquettes de colonne du tableau croisé dynamique et choisissez Outils de tableau croisé dynamique ,Analyser ,Calculs , Champs, éléments et ensembles ensuite sur Élément calculé. La boîte de dialogue Insérer un élément calculé s’affiche.
2. Entrez un nom pour le nouvel élément dans le champ Nom et spécifiez la formule dans le champ Formule (voir Figure 34.17). La formule peut utiliser des éléments dans d’autres champs, mais elle ne peut pas utiliser les fonctions de feuille de calcul. Pour cet exemple, le nouvel élément est nommé Commission Qtr1 et la formule apparaît comme suit :

=10%*(Janvier+Février+Mars)

REMARQUE

Un élément calculé, contrairement à un champ calculé, n’apparaît pas dans le volet Office Champs de tableau croisé dynamique. Seuls les champs apparaissent dans la liste des champs.

MISE EN GARDE

Si vous utilisez un élément calculé dans votre tableau croisé dynamique, vous devrez peut-être désactiver l’affichage du total général pour les colonnes afin d’éviter le double comptage. Dans cet exemple, le total général inclut les éléments calculés, de sorte que les montants des commissions sont inclus dans les montants des ventes. Pour désactiver les totaux généraux, choisissez Outils de tableau croisé dynamique , Conception , Disposition ,Grand Totaux.

Une fois que vous avez créé les éléments calculés, ils apparaissent dans le tableau croisé dynamique.

le tableau croisé dynamique après avoir ajouté les quatre éléments calculés.

Notez que les éléments calculés sont ajoutés à la fin des éléments du mois. Vous pouvez réorganiser les éléments en sélectionnant la cellule et en faisant glisser sa bordure. Une autre option consiste à créer deux groupes (manuellement) : un pour les chiffres de vente et un pour les calculs de commission. le tableau croisé dynamique après la création des deux groupes et l’ajout des sous-totaux.

Comprendre les règles et les lacunes des calculs de tableau croisé dynamique

Il n’y a pas de meilleur moyen d’intégrer vos calculs dans un tableau croisé dynamique qu’en utilisant des champs calculés et des éléments calculés. Cependant, les champs calculés et les éléments calculés présentent leurs propres inconvénients. Il est important que vous compreniez ce qui se passe dans les coulisses lorsque vous utilisez ces calculs de tableaux croisés dynamiques, et il est encore plus important de connaître les limites  des champs calculés et des éléments calculés pour éviter les erreurs potentielles dans votre analyse de données.

Les sections suivantes mettent en évidence les règles relatives aux champs calculés et aux éléments calculés que vous rencontrerez très probablement lorsque vous travaillez avec des calculs de tableau croisé dynamique.

Se souvenir de l’ordre de priorité des opérateurs

Comme dans un tableur, vous pouvez utiliser n’importe quel opérateur dans vos formules de calcul, c’est-à-dire n’importe quel symbole représentant un calcul à effectuer (+, -, *, /, %, ^). De plus, tout comme dans un tableur, les calculs dans un tableau croisé dynamique suivent l’ordre de priorité des opérateurs. En d’autres termes, lorsque vous effectuez un calcul qui combine plusieurs opérateurs, comme dans (2+3) * 4/50%, Excel évalue et effectue le calcul dans un ordre spécifique. L’ordre des opérations pour Excel est le suivant :

• Évaluez les éléments entre parenthèses.
• Évaluer les plages (:).
• Évaluez les intersections (espaces).
• Évaluer les syndicats (,).
• Effectuez la négation (–).
• Convertir les pourcentages (%).
• Effectuez l’exponentiation (^).
• Effectuez la multiplication (*) et la division (/), qui ont la même

priorité.

• Effectuez l’addition (+) et la soustraction (–), qui ont la même priorité.
• Évaluez les opérateurs de texte (&).
• Effectuer des comparaisons (=, <, >, <>, <=, >=).

Considérez cet exemple de base. La bonne réponse à (2+3)*4 est 20.

Cependant, si vous omettez les parenthèses, de sorte que vous ayez 2+3*4, Excel effectue le calcul comme ceci : 3*4 = 12 + 2 = 14. L’ordre de priorité des opérateurs exige qu’Excel effectue la multiplication avant l’addition. Entrer 2+3*4 vous donne la mauvaise réponse. Étant donné qu’Excel évalue et effectue d’abord tous les calculs entre parenthèses, placer 2 + 3 entre parenthèses garantit la bonne réponse.

Voici un autre exemple largement démontré. Si vous entrez 10^2, qui représente l’exposant 10 à la seconde puissance sous forme de formule, Excel renvoie 100 comme réponse. Si vous entrez –10^2, vous vous attendez à ce que –100 soit le résultat, mais Excel renvoie à nouveau 100. La raison en est qu’Excel met la négation avant l’exponentiation, ce qui signifie qu’Excel convertit 10 en -10 avant d’effectuer l’exponentiation, calculant ainsi -10*-10, ce qui équivaut en effet à 100. Lorsque vous utilisez des parenthèses dans la formule, -(10^2), Excel calcule l’exposant avant annuler la réponse, vous donnant -100.Comprendre l’ordre des opérations vous aide à éviter les erreurs de calcul de vos données.

Utilisation de références de cellule et de plages nommées

Lorsque vous créez des calculs dans un tableau croisé dynamique, vous travaillez essentiellement dans le vide. Les seules données disponibles sont les données qui existent dans le cache pivot. Par conséquent, vous ne pouvez pas sortir des limites du cache croisé dynamique pour référencer des cellules ou des plages nommées dans votre formule.

Utilisation des fonctions de feuille de calcul

Lorsque vous créez des champs calculés ou des éléments calculés, Excel vous permet d’utiliser n’importe quelle fonction de feuille de calcul qui accepte des valeurs numériques comme arguments et renvoie des valeurs numériques comme résultat. Certaines des nombreuses fonctions qui entrent dans cette catégorie sont NB, MOYENNE, SI, ET, NON et OU.

Quelques exemples de fonctions que vous ne pouvez pas utiliser sont RECHERCHEV, INDEX, SOMME.SI,NB.SI, DROITE et GAUCHE. Encore une fois, ils sont tous impossibles à utiliser car ils nécessitent des références de tableau de cellules ou renvoient des valeurs textuelles comme résultat.

Utiliser des constantes

Vous pouvez utiliser n’importe quelle constante dans vos calculs de tableau croisé dynamique. Les constantes sont des valeurs statiques qui ne changent pas. Par exemple, dans la formule [Unités vendues]*5, 5 est une constante. Bien que la valeur des unités vendues puisse changer en fonction des données disponibles, 5 a toujours la même valeur.

Totaux de référence

Vos formules de calcul ne peuvent pas faire référence aux sous-totaux ou au total général d’un tableau croisé dynamique. Cela signifie que vous ne pouvez pas utiliser le résultat d’un sous-total ou d’un total général comme variable ou argument dans un champ calculé. Les mesures créées à l’aide du langage de formule DAX peuvent surmonter cette limitation.