Générer des formules pour les calculs
Cette rubrique décrit les aspects fondamentaux de la génération d'une formule dans PowerPivot pour Excel, donne un exemple de création d'une colonne calculée et décrit comment utiliser des tables. Cette rubrique contient les sections suivantes :
Mesures et colonnes calculées
Concepts de base des formules
Utilisation de tables et de colonnes
Identification et résolution des erreurs dans les formules
Après avoir lu cette rubrique, consultez les rubriques suivantes pour plus d'informations :
Mesures et colonnes calculées
Dans un classeur PowerPivot, vous pouvez utiliser des formules dans des colonnes calculées et des mesures :
Une colonne calculée est une colonne que vous ajoutez à une table PowerPivot existante. Au lieu de coller ou d'importer des valeurs dans la colonne, vous créez une formule DAX (Data Analysis Expressions) qui définit les valeurs de colonne. Si vous incluez la table PowerPivot dans un tableau croisé dynamique (ou un graphique croisé dynamique), la colonne calculée peut être utilisée comme toute autre colonne de données.
Une mesure est une formule créée spécifiquement pour être utilisée dans un tableau croisé dynamique (ou un graphique croisé dynamique) qui utilise des données PowerPivot. Les mesures peuvent reposer sur des fonctions d'agrégation standard, comme COUNT ou SUM, ou vous pouvez définir votre propre formule à l'aide de DAX. Une mesure est utilisée dans la zone Valeurs d'un tableau croisé dynamique. Si vous souhaitez placer des résultats calculés dans une zone différente d'un tableau croisé dynamique, utilisez à la place une colonne calculée.
Pour plus d'informations, consultez « Colonnes et mesures calculées » dans Vue d'ensemble du langage DAX (Data Analysis Expressions).
Concepts de base des formules
PowerPivot pour Excel inclut DAX, un nouveau langage de formule pour la création de calculs personnalisés. DAX permet aux utilisateurs de définir des calculs personnalisés dans les tables PowerPivot et dans les tableaux croisés dynamiques Excel. DAX inclut quelques-unes des fonctions utilisées dans les formules Excel, ainsi que des fonctions supplémentaires conçues pour l'utilisation de données relationnelles et l'agrégation dynamique. Pour plus d'informations, consultez Vue d'ensemble du langage DAX (Data Analysis Expressions).
Les formules peuvent être complexes, mais le tableau suivant répertorie les formules de base qui peuvent être utilisées dans une colonne calculée PowerPivot.
Formule |
Description |
=TODAY() |
Insère la date du jour dans chaque ligne de la colonne. |
=3 |
Insère la valeur 3 dans chaque ligne de la colonne. |
=[Column1] + [Column2] |
Ajoute les valeurs dans la même ligne de [Column1] et [Column2] et place les résultats dans la même ligne de la colonne calculée. |
Vous pouvez générer des formules PowerPivot pour des colonnes calculées de la même façon que vous créez des formules dans Microsoft Excel. Vous créez des formules pour les mesures à l'aide de l'une des boîtes de dialogue suivantes : Boîte de dialogue Paramètres de mesure (agrégation standard) ou Boîte de dialogue Paramètres de mesure (agrégation personnalisée).
Utilisez les étapes suivantes lorsque vous générez une formule :
Chaque formule doit commencer par un signe égal.
Vous pouvez taper ou sélectionner un nom de fonction ou taper une expression.
Commencez par taper les premières lettres de la fonction ou du nom que vous recherchez pour que la saisie semi-automatique affiche une liste des fonctions, tables et colonnes disponibles. Appuyez sur TAB pour ajouter un élément de la liste de saisie semi-automatique dans la formule.
Cliquez sur le bouton Fx pour afficher une liste de fonctions disponibles. Pour sélectionner une fonction dans la liste déroulante, utilisez les touches de direction pour mettre en surbrillance celle de votre choix, puis cliquez sur OK pour l'ajouter à la formule.
Fournissez les arguments de la fonction en les sélectionnant dans une liste déroulante de tables et colonnes possibles ou en tapant des valeurs.
Recherchez les éventuelles erreurs de syntaxe : vérifiez que toutes les parenthèses sont fermées, et que les références aux colonnes, tables et valeurs sont correctes.
Appuyez sur ENTRÉE pour accepter la formule.
[!REMARQUE]
Dans une colonne calculée, dès que vous acceptez la formule, la colonne se remplit de valeurs. Dans une mesure, le fait d'appuyer sur Entrée enregistre la définition de la mesure, et si la mesure est nouvelle, PowerPivot ajoute automatiquement la mesure à la zone Valeurs du tableau croisé dynamique.
Créer une formule simple
L'exemple suivant indique comment créer une colonne calculée avec une formule simple, à partir des données suivantes :
SalesDate |
Subcategory |
Product |
Sales |
Quantity |
|---|---|---|---|---|
1/5/2009 |
Accessories |
Carrying Case |
254995 |
68 |
1/5/2009 |
Accessories |
Mini Battery Charger |
1099.56 |
44 |
1/5/2009 |
Digital |
Slim Digital |
6512 |
44 |
1/6/2009 |
Accessories |
Telephoto Conversion Lens |
1662.5 |
18 |
1/6/2009 |
Accessories |
Tripod |
938.34 |
18 |
1/6/2009 |
Accessories |
USB Cable |
1230.25 |
26 |
Pour créer une colonne calculée avec une formule simple |
|
Conseils pour l'utilisation de la saisie semi-automatique
Vous pouvez utiliser la saisie semi-automatique des formules au milieu d'une formule existante avec les fonctions imbriquées. Le texte immédiatement avant le point d'insertion est utilisé pour afficher des valeurs dans la liste déroulante, et tout le texte après le point d'insertion reste inchangé.
PowerPivot n'ajoute pas la parenthèse fermante des fonctions, ni ne met automatiquement en correspondance les parenthèses. Vous devez vous assurer que chaque fonction est syntaxiquement correcte, faute de quoi vous ne pourrez pas enregistrer ni utiliser la formule. PowerPivot met en surbrillance les parenthèses, ce qui vous permet de voir plus facilement si elles sont fermées comme il se doit.
Pour plus d'informations sur l'utilisation de la saisie semi-automatique, consultez Colonnes calculées et Mesures dans PowerPivot.
Utilisation de tables et de colonnes
Les tables PowerPivot sont similaires aux tables Excel, mais différentes dans la manière dont elles fonctionnent avec les données et les formules :
Les formules fonctionnent uniquement avec les tables et les colonnes, et non avec les cellules individuelles, les références de plage ou les tableaux.
Les formules peuvent utiliser des relations pour obtenir des valeurs à partir de tables associées. Les valeurs récupérées sont toujours associées à la valeur de ligne actuelle.
Vous ne pouvez pas coller des formules DAX (Data Analysis Expressions) dans un classeur Excel et vice versa.
Vous ne pouvez pas avoir de données irrégulières ou « déséquilibrées », comme vous le pouvez dans une feuille de calcul Excel. Chaque ligne d'une table doit contenir le même nombre de colonnes. Toutefois, certaines colonnes peuvent comporter des valeurs vides. Les tables de données Microsoft Excel et PowerPivot ne sont pas interchangeables, mais vous pouvez établir une liaison avec les tableaux Excel depuis PowerPivot et coller les données Excel dans PowerPivot. Pour plus d'informations, consultez Ajouter des données à l'aide de tables liées Excel et Copier et coller des données dans PowerPivot.
Référence aux tables et aux colonnes dans les formules et les expressions
Vous pouvez faire référence à toute table et colonne à l'aide de son nom. Par exemple, la formule suivante indique comment faire référence aux colonnes de deux tables à l'aide du nom complet :
=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])
Lorsqu'une formule est évaluée, PowerPivot pour Excel vérifie en premier la syntaxe générale, puis vérifie les noms des colonnes et des tables que vous fournissez par rapport aux colonnes et tables possibles dans le contexte actuel. Si le nom est ambigu ou si la colonne/la table est introuvable, vous obtiendrez une erreur dans votre formule (une #chaîne ERROR au lieu d'une valeur de données dans les cellules où l'erreur se produit). Pour plus d'informations sur les exigences d'affectation de noms pour les tables, les colonnes et les autres objets, consultez « Exigences concernant l'affectation des noms » dans Spécification de syntaxe DAX pour PowerPivot.
[!REMARQUE]
Le contexte est une fonctionnalité importante de classeurs PowerPivot qui vous permet de générer des formules dynamiques. Le contexte est déterminé par les tables dans le classeur, les relations entre les tables et tous les filtres qui ont été appliqués. Pour plus d'informations, consultez Contexte dans les formules DAX.
Relations entre les tables
Les tables peuvent être associées à d'autres tables. En créant des relations, vous avez la possibilité de rechercher des données dans une autre table et d'utiliser les valeurs associées pour effectuer des calculs complexes. Par exemple, vous pouvez utiliser une colonne calculée pour rechercher tous les enregistrements de navigation associés au revendeur actuel, puis additionner les coûts d'expédition pour chacun. L'effet est similaire à une requête paramétrable : vous pouvez calculer une somme différente pour chaque ligne de la table actuelle.
De nombreuses fonctions DAX requièrent l'existence d'une relation entre les tables ou entre plusieurs tables, afin de localiser les colonnes que vous avez référencées et de retourner des résultats qui ont un sens. D'autres fonctions essaient d'identifier la relation ; toutefois, lorsque cela est possible, créez une relation pour optimiser les résultats. Pour plus d'informations, consultez les rubriques suivantes :
Lorsque vous travaillez avec des tableaux croisés dynamiques, il est particulièrement important de connecter toutes les tables utilisées dans le tableau croisé dynamique, afin de pouvoir calculer correctement les données de synthèse. Pour plus d'informations, consultez Utilisation des relations dans les tableaux croisés dynamiques.
Identification et résolution des erreurs dans les formules
Si vous obtenez une erreur lorsque vous définissez une colonne calculée, la formule peut contenir une erreur syntaxique ou une erreur sémantique.
Les erreurs syntactiques sont les plus faciles à résoudre. Elles impliquent en général une virgule ou une parenthèse manquante. Pour obtenir de l'aide sur la syntaxe d'une fonction, consultez Référence des fonctions DAX.
L'autre type d'erreur se produit lorsque la syntaxe est correcte, mais que la valeur ou la colonne référencée n'a pas de sens dans le contexte de la formule. De telles erreurs sémantiques peuvent être provoquées par l'un des problèmes suivants :
La formule fait référence à une colonne, une table ou une fonction non existante.
La formule semble être correcte, mais lorsque le moteur de données PowerPivot extrait les données, il détecte une incompatibilité de type et génère une erreur.
La formule passe un nombre ou un type de paramètres incorrect à une fonction.
La formule fait référence à une colonne différente qui comporte une erreur, et par conséquent, ses valeurs ne sont pas valides.
La formule fait référence à une colonne qui n'a pas été traitée. Cela peut arriver si vous avez modifié le classeur en mode manuel, effectué des modifications et n'avez jamais actualisé les données ou mis à jour les calculs.
Dans les quatre premiers cas, DAX signale la colonne entière qui contient la formule non valide. Dans le dernier cas, DAX grise la colonne pour indiquer que la colonne se trouve dans un état non traité.
Voir aussi
Concepts
Ajouter des calculs à vos rapports, graphiques et tableaux croisés dynamiques