Agrégations dans les formules
Cette rubrique présente les agrégations et fournit une vue d'ensemble des types d'agrégations autorisés dans PowerPivot pour Excel. PowerPivot pour Excel contient les outils suivants permettant de créer des agrégations :
Vous pouvez générer des tableaux croisés dynamiques et des graphiques croisés dynamiques reposant sur des données PowerPivot. Les tableaux croisés dynamiques Excel constituent un outil populaire pour regrouper et synthétiser les données dans des feuilles de calcul. PowerPivot intègre les fonctionnalités de tableau croisé dynamique d'Excel et offre plusieurs améliorations.
Vous pouvez utiliser le langage de formule DAX pour concevoir des agrégations personnalisées. DAX peut être utilisé pour créer des colonnes calculées dans les tables PowerPivot et pour créer des mesures dans les tableaux croisés dynamiques et les graphiques croisés dynamiques.
La dernière section de cette rubrique fournit des liens vers des informations plus détaillées sur le mode de génération des agrégations.
Présentation des agrégations
Les agrégations permettent de réduire, de synthétiser ou de regrouper des données. Lorsque vous démarrez avec des données brutes provenant de tables ou d'autres sources de données, elles sont souvent à deux dimensions, ce qui signifie qu'elles contiennent de nombreux détails, mais qu'elles n'ont pas été organisées ou regroupées. L'absence de résumés ou de structure peut rendre difficile l'identification de modèles dans les données. Par conséquent, une part importante du travail de l'analyste consiste à définir des agrégations qui simplifient, synthétisent ou résument des modèles en réponse à une question métier spécifique.
Choix de groupes pour l'agrégation
Lorsque vous agrégez des données, vous les regroupez par attributs, tels que le produit, le prix, la région ou la date, puis vous définissez une formule qui fonctionne sur toutes les données du groupe. Par exemple, lorsque vous créez un total pour une année, vous créez une agrégation. Si vous créez par la suite le ratio année en cours/année précédente et que vous présentez ces données sous forme de pourcentages, il s'agit d'un autre type d'agrégation.
Le choix du mode de regroupement des données est régi par la question métier. Par exemple, les agrégations peuvent répondre aux questions suivantes :
Nombres Combien de transactions ont été effectuées en un mois ?
Moyennes Quelles ont été les ventes moyennes du mois par vendeur ?
Valeurs minimales et maximales Quels ont été les cinq premiers secteurs de vente en termes d'unités vendues ?
Pour créer un calcul qui répondra à ces questions, vous devez avoir détaillé les données qui contiennent les nombres à comptabiliser ou à additionner, et ces données numériques doivent être associées d'une façon quelconque aux groupes que vous utiliserez pour organiser les résultats.
Si les données ne contiennent pas encore de valeurs que vous pouvez utiliser pour le regroupement, comme une catégorie de produits ou le nom de la région géographique où le magasin se trouve, vous pouvez introduire des groupes dans vos données en ajoutant des catégories. Lorsque vous créez des groupes dans Excel, vous devez taper ou sélectionner manuellement les groupes que vous souhaitez utiliser parmi les colonnes de votre feuille de calcul.
Toutefois, dans un système relationnel, les hiérarchies telles que les catégories de produits sont généralement stockées dans une autre table que la table de faits ou de valeurs. Habituellement, la table de catégories est liée aux données de faits par une clé quelconque. Par exemple, supposons que vous découvriez que vos données contiennent les ID des produits, mais pas les noms des produits, ni leurs catégories. Pour ajouter la catégorie à une feuille de calcul Excel à deux dimensions, vous devez copier la colonne contenant les noms de catégories. Toutefois, dans un classeur PowerPivot, vous pouvez importer la table des catégories de produits dans votre classeur, créer une relation entre la table contenant les données numériques et la liste des catégories de produits, puis utiliser les catégories pour regrouper les données. Pour plus d'informations, consultez Relations entre tables.
Choix d'une fonction pour l'agrégation
Après avoir identifié et ajouté les regroupements à utiliser, vous devez déterminer les fonctions mathématiques à utiliser pour l'agrégation. Le terme « agrégation » est fréquemment employé comme synonyme des opérations mathématiques ou statistiques utilisées dans les agrégations, telles que les sommes, les moyennes, les valeurs minimales ou les nombres. Toutefois, PowerPivot pour Excel vous permet de créer des formules personnalisées pour l'agrégation, en complément des agrégations standard fournies dans Excel.
Par exemple, à partir de l'ensemble de valeurs et de regroupements utilisé dans les exemples précédents, vous pouvez créer des agrégations personnalisées qui répondent aux questions suivantes :
Nombres filtrés Combien de transactions ont été effectuées en un mois, en excluant la période de maintenance de fin de mois ?
Ratios utilisant des moyennes dans le temps Quel est le taux d'augmentation ou de diminution des ventes par rapport à la même période de l'année précédente ?
Valeurs minimales et maximales groupées Quels sont les premiers secteurs de vente par catégorie de produit ou par vente promotionnelle ?
Ajout d'agrégations aux formules et aux tableaux croisés dynamiques
Lorsque vous avez une idée générale de la façon dont vos données doivent être regroupées pour être pertinentes, ainsi que des valeurs avec lesquelles vous souhaitez travailler, vous pouvez décider s'il faut générer un tableau croisé dynamique ou créer des calculs dans une table. PowerPivot pour Excel enrichit et améliore la capacité native d'Excel à créer des agrégations telles que les sommes, les comptages ou les moyennes. Vous pouvez créer des agrégations personnalisées dans PowerPivot, soit dans la fenêtre PowerPivot, soit dans la zone du tableau croisé dynamique Excel.
Dans une colonne calculée, vous pouvez créer des agrégations qui prennent en considération le contexte de ligne actuel pour récupérer les lignes associées d'une autre table, puis additionner, comptabiliser ou calculer la moyenne des valeurs des lignes associées.
Dans une mesure, vous pouvez créer des agrégations dynamiques qui utilisent à la fois les filtres définis dans la formule et les filtres imposés par la conception du tableau croisé dynamique et la sélection des segments, des en-têtes de colonne et des en-têtes de ligne.
Pour plus d'informations, consultez Générer des formules pour les calculs.
Ajout de regroupements à un tableau croisé dynamique
Lorsque vous concevez un tableau croisé dynamique, vous faites glisser les champs représentant des regroupements, des catégories ou des hiérarchies vers la section de colonnes et de lignes du tableau croisé dynamique pour regrouper les données. Vous faites ensuite glisser les champs qui contiennent des valeurs numériques vers la zone de valeurs pour permettre le comptage, le calcul de la moyenne ou l'addition de ces valeurs.
Si vous ajoutez des catégories à un tableau croisé dynamique, mais que les données de catégorie ne sont pas associées aux données de fait, vous risquez d'obtenir une erreur ou des résultats incongrus. PowerPivot pour Excel tentera généralement de résoudre le problème en détectant et suggérant automatiquement des relations. Pour plus d'informations, consultez Utilisation des relations dans les tableaux croisés dynamiques.
Vous pouvez également faire glisser des champs dans des segments de façon à sélectionner certains groupes de données pour l'affichage. Les segments constituent une nouvelle fonctionnalité disponible dans Excel et dans PowerPivot pour Excel qui vous permet de regrouper, de trier et de filtrer interactivement les résultats dans un tableau croisé dynamique.
Utilisation de regroupements dans une formule
Vous pouvez également utiliser des regroupements et des catégories pour agréger des données stockées dans des tables en créant des relations entre les tables, puis en concevant des formules qui exploitent ces relations pour rechercher des valeurs associées.
En d'autres termes, si vous souhaitez créer une formule qui regroupe des valeurs par catégorie, vous devez commencer par utiliser une relation pour connecter la table contenant les données de détail et les tables contenant les catégories, puis générer la formule.
Pour plus d'informations sur la génération de formules qui utilisent des recherches, consultez Relations et recherches dans les formules :
Utilisation de filtres dans les agrégations
Une nouvelle fonctionnalité de PowerPivot offre la possibilité d'appliquer des filtres à des colonnes et à des tables de données, non seulement dans l'interface utilisateur et dans un tableau croisé dynamique ou un graphique, mais également dans les formules que vous utilisez pour calculer des agrégations. Les filtres sont utilisables dans les formules à la fois dans les colonnes calculées et dans les mesures.
Par exemple, vous pouvez spécifier une table entière en tant qu'argument dans les nouvelles fonctions d'agrégation du langage DAX (Data Analysis Expression), au lieu de spécifier des valeurs à additionner ou à comptabiliser. Si vous n'appliquez aucun filtre à cette table, la fonction d'agrégation opérera sur toutes les valeurs figurant dans la colonne spécifiée de la table. Toutefois, dans DAX, vous pouvez créer un filtre dynamique ou statique sur la table afin que l'agrégation s'applique à un autre sous-ensemble de données selon la condition de filtre et le contexte actuel.
La combinaison de conditions et de filtres dans les formules vous permet de créer des agrégations qui changent selon les valeurs fournies dans les formules, ou selon la sélection de titres de lignes et d'en-têtes de colonne dans un tableau croisé dynamique.
Pour plus d'informations, consultez Filtrer des données dans des formules.
Comparaison entre les fonctions d'agrégation DAX et Excel
Le tableau suivant répertorie quelques-unes des fonctions d'agrégation standard fournies par Excel et fournit des liens pour l'implémentation de ces fonctions dans PowerPivot pour Excel. La version DAX de ces fonctions se comporte pour l'essentiel de la même manière que la version Excel, à quelques petites différences près en matière de syntaxe et de gestion de certains types de données.
Fonctions d'agrégation standard
Fonction |
Utilisation |
Retourne la moyenne (arithmétique) de tous les nombres d'une colonne. |
|
Retourne la moyenne (arithmétique) de toutes les valeurs d'une colonne. Gère des valeurs texte et non numériques. |
|
Compte le nombre de valeurs numériques dans une colonne. |
|
Compte le nombre de valeur d'une colonne qui ne sont pas vides. |
|
Retourne la plus grande valeur numérique d'une colonne. |
|
Retourne la plus grande valeur d'un jeu d'expressions évalué sur une table. |
|
Retourne la plus petite valeur numérique d'une colonne. |
|
Retourne la plus petite valeur d'un jeu d'expressions évalué sur une table. |
|
Additionne tous les nombres d'une colonne. |
Fonctions d'agrégation DAX
DAX inclut des fonctions d'agrégation qui vous permettent de spécifier une table sur laquelle l'agrégation doit être effectuée. Par conséquent, au lieu de simplement calculer la somme ou la moyenne des valeurs d'une colonne, ces fonctions vous permettent de créer une expression qui définit de manière dynamique les données à agréger.
Le tableau ci-après répertorie les fonctions d'agrégation disponibles dans DAX.
Fonction |
Utilisation |
Calcule la moyenne d'un ensemble d'expressions évaluées sur une table. |
|
Compte un ensemble d'expressions évaluées sur une table. |
|
Compte le nombre de valeurs vides dans une colonne. |
|
Compte le nombre total de lignes d'une table. |
|
Compte le nombre de lignes retourné par une fonction de table imbriquée, comme une fonction de filtre. |
|
Retourne la somme d'un jeu d'expressions évalué sur une table. |
Différences entre les fonctions DAX et les fonctions d'agrégation d'Excel
Bien que ces fonctions portent les mêmes noms que leurs équivalents Excel, elles utilisent le moteur PowerPivot VertiPaq et ont été réécrites pour pouvoir être utilisées avec des tables et des colonnes. Vous ne pouvez pas utiliser une formule DAX dans un classeur Excel et inversement. Elles ne peuvent être utilisées que dans la fenêtre PowerPivot et dans les tableaux croisés dynamiques reposant sur les données PowerPivot. Leur comportement peut aussi être légèrement différent. Pour plus d'informations, consultez les rubriques de référence des différentes fonctions.
La façon dont les colonnes sont évaluées dans une agrégation est également différente de la manière dont Excel gère les agrégations. Nous allons illustrer ce point à l'aide d'un exemple.
Supposons que vous souhaitez calculer la somme des valeurs de la colonne Amount de la table Sales. Vous créez à cet effet la formule suivante :
=SUM('Sales'[Amount])
Dans le cas le plus simple, la fonction obtient les valeurs d'une colonne non filtrée unique, et le résultat est le même que dans Excel qui ne fait toujours qu'additionner les valeurs de la colonne Amount. Toutefois, dans PowerPivot, la formule est interprétée comme « Obtenir la valeur dans Amount pour chaque ligne de la table Sales, puis additionner ces valeurs individuelles ». PowerPivot évalue chaque ligne sur laquelle l'agrégation est effectuée et calcule une valeur scalaire unique pour chaque ligne, puis effectue une agrégation sur ces valeurs. Par conséquent, le résultat d'une formule peut être différent si des filtres ont été appliqués à une table, ou si les valeurs sont calculées en fonction d'autres agrégations qui peuvent être filtrées. Pour plus d'informations, consultez Contexte dans les formules DAX.
Fonctions Time Intelligence DAX
Outre les nouvelles fonctions d'agrégation de table décrites dans la section précédente, DAX propose des fonctions d'agrégation qui fonctionnent avec les dates et heures que vous spécifiez pour offrir une fonctionnalité Time Intelligence intégrée. Ces fonctions utilisent des plages de dates pour obtenir des valeurs associées et agréger ces valeurs. Vous pouvez également comparer les valeurs de plusieurs plages de dates.
Le tableau ci-après répertorie les fonctions Time Intelligence utilisables pour l'agrégation.
Fonction |
Utilisation |
Calcule une valeur à la fin de la période donnée. |
|
Calcule une valeur à la fin de la période avant la période donnée. |
|
Calcule une valeur sur l'intervalle qui démarre le premier jour de la période et se termine à la date la plus récente dans la colonne de date spécifiée. |
Les autres fonctions de la section Fonctions Time Intelligence (Fonctions Time Intelligence (DAX)) sont des fonctions qui peuvent être utilisées pour extraire des dates ou des plages de dates personnalisées à utiliser dans l'agrégation. Par exemple, vous pouvez utiliser la fonction DATESINPERIOD pour retourner une plage de dates et utiliser cette plage de dates comme argument d'une autre fonction pour calculer une agrégation personnalisée juste pour ces dates.
Voir aussi
Concepts
Relations et recherches dans les formules