Agrégations dans Power Pivot

Important :  Cet article a été traduit automatiquement, voir l’avertissement. Vous pouvez consulter la version en anglais de cet article ici.

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. Un travail important de la modélisation des données 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.

Agrégations plus courantes, tels que ceux qui utilisent moyenne, COUNT, DISTINCTCOUNT, MAX, MINou somme peuvent être créées dans une mesure automatiquement à l'aide de somme automatique. Autres types d'agrégations, tels que AVERAGEX, COUNTX, COUNTROWSou SUMXrenvoient une table et exigent une formule créée à l'aide d'Expressions DAX (Data Analysis).

Fonctionnement des agrégations dans Power Pivot

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 a-t-on 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 contient pas déjà les valeurs que vous pouvez utiliser pour le regroupement, par exemple une catégorie de produit ou le nom de la région géographique où se trouve le magasin, vous souhaiterez peut-être présenter les groupes à vos données en ajoutant des catégories. Lorsque vous créez des groupes dans Excel, vous devez manuellement tapez ou sélectionnez les groupes que vous souhaitez utiliser parmi les colonnes dans votre feuille de calcul. Toutefois, dans un système relationnel, hiérarchies tels que des catégories pour les produits sont souvent stockés dans une autre table dans le fait ou le tableau de valeur. En règle générale, la table category est liée aux données de faits moyen d'un type de clé. Par exemple, supposons que vous recherchez que vos données contiennent des ID de produit, mais pas les noms de produits ou leurs catégories. Pour ajouter la catégorie à une feuille de calcul Excel plate, vous devez copier dans la colonne contenant les noms de catégorie. Avec Power Pivot, vous pouvez importer la table de catégorie de produit pour votre modèle de données, créer une relation entre la table avec les données numériques et de la liste Catégorie de produit et ensuite utiliser les catégories pour regrouper les données. Pour plus d'informations, voir créer une relation 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, Power Pivot vous permet de créer des formules personnalisées pour l’agrégation, en complément des agrégations standard fournies dans Power Pivot et 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 a-t-on 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. Power Pivot 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 Power Pivot, soit dans la fenêtre Power Pivot, soit dans la zone du tableau croisé dynamique Excel.

  • Dans une colonne calculée, créez 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 additionnez, comptabilisez ou calculez la moyenne des valeurs des lignes associées.

  • Dans une mesure, vous pouvez créer des agrégations dynamiques qui utilisent les filtres définis dans la formule, filtres et imposées par la conception de tableau croisé dynamique et la sélection des segments, des en-têtes de colonnes et des en-têtes de lignes. Mesures à l'aide des agrégations standards peuvent être créés dans Power Pivot en utilisant la somme automatique ou en créant une formule. Vous pouvez également créer des mesures implicites à l'aide des agrégations standards dans un tableau croisé dynamique dans Excel.

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. Power Pivot 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 vous permettent 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 création de formules utilisant des recherches, voir Recherches dans les formules PowerPivot.

Utilisation de filtres dans les agrégations

Une nouvelle fonctionnalité de Power Pivot consiste à appliquer des filtres à des colonnes et des tables de données, non seulement dans l'interface utilisateur et au sein d'un tableau croisé dynamique ou un graphique, mais également dans les formules très qui vous permet de calculer les agrégations. Filtres peuvent être utilisés dans les formules dans des colonnes calculées et s.

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, voir 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 Power Pivot. 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

Utilisez

AVERAGE

Retourne la moyenne (arithmétique) de tous les nombres d’une colonne.

AVERAGEA

Retourne la moyenne (arithmétique) de toutes les valeurs d’une colonne. Gère des valeurs texte et non numériques.

COUNT

Compte le nombre de valeurs numériques dans une colonne.

COUNTA

Compte le nombre de valeur d’une colonne qui ne sont pas vides.

MAX

Retourne la valeur numérique la plus grande dans une colonne.

MAXX

Retourne la plus grande valeur d’un jeu d’expressions évalué sur une table.

MIN

Retourne la plus petite valeur numérique dans une colonne.

MINX

Retourne la plus petite valeur d’un jeu d’expressions évalué sur une table.

SUM

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

Utilisez

AVERAGEX

Calcule la moyenne d’un ensemble d’expressions évaluées sur une table.

COUNTAX

Compte un ensemble d’expressions évaluées sur une table.

COUNTBLANK

Compte le nombre de valeurs vides dans une colonne.

COUNTX

Compte le nombre total de lignes d’une table.

COUNTROWS

Compte le nombre de lignes retourné par une fonction de table imbriquée, comme une fonction de filtre.

SUMX

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 d’analyse en mémoire de Power Pivot 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 Power Pivot et dans les tableaux croisés dynamiques reposant sur les données Power Pivot. 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. Dans Power Pivot, toutefois, la formule est interprétée comme « Obtenir la valeur dans Amount pour chaque ligne de la table Sales, puis additionner ces valeurs individuelles ». Power Pivot é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 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

Utilisez

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

Calcule une valeur à la fin de la période donnée.

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

Calcule une valeur à la fin de la période avant la période donnée.

TOTALMTD

TOTALYTD

TOTALQTD

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

Remarque : Avertissement traduction automatique : cet article a été traduit par un ordinateur, sans intervention humaine. Microsoft propose cette traduction automatique pour offrir aux personnes ne maîtrisant pas l’anglais l’accès au contenu relatif aux produits, services et technologies Microsoft. Comme cet article a été traduit automatiquement, il risque de contenir des erreurs de grammaire, de syntaxe ou de terminologie.

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×