Comment agréger des données dans Excel (plusieurs méthodes)

Excel est souvent l'outil de choix pour stocker et transformer des données, surtout si vous travaillez avec de grands jeux de données. Si votre quotidien consiste à analyser ou à interpréter des milliers de lignes, la tâche peut vite devenir écrasante ou, pire encore, très peu productive. Si vous travaillez en Finance ou en Ventes, vous aurez sans doute besoin d'identifier des tendances pour faire des prévisions. Pour cela, vous devrez agréger des plages de cellules ou résumer des données clés en limitant au maximum les erreurs et les formules cassées.

Dans cet article, vous apprendrez à agréger des données dans Excel avec la fonction AGGREGATE (AGREGAT) : d'abord une explication détaillée de la fonction AGGREGATE, puis un tour d'horizon de la syntaxe de la formule, et enfin un exemple pas à pas pour calculer la somme et la moyenne de données avec la formule AGGREGATE et avec Power Query dans Excel.

Qu'est-ce que la formule AGGREGATE dans Excel ?

Excel a introduit la fonction AGGREGATE dans sa version 2010. Vous pouvez l'utiliser pour agréger des données dans Excel à différentes fins, comme des sommes ou des moyennes, avec la possibilité d'ignorer les lignes masquées et les erreurs.

Syntaxe de la formule AGGREGATE

La fonction AGGREGATE d'Excel peut s'écrire de deux façons : sous forme de référence ou sous forme matricielle. Bonne nouvelle, vous n'avez pas à choisir, car Excel sélectionne automatiquement la bonne forme selon les paramètres que vous saisissez.

=AGGREGATE(function_num, options, ref1, [ref2], …)

ou

=AGGREGATE(function_num, options, array, [k])

Voici ce que représente chaque paramètre :

• function_num : Ce paramètre obligatoire est un nombre de 1 à 19 qui indique la fonction d'agrégation à exécuter.
• options : Autre paramètre obligatoire, un nombre de 0 à 7, qui indique les valeurs que la fonction doit ignorer.
• ref1 : Ce paramètre obligatoire désigne la plage de cellules à laquelle la fonction s'applique.
• [ref2] : Ce paramètre facultatif désigne des cellules supplémentaires. Vous pouvez inclure jusqu'à 253 plages, séparées par une virgule (,).
• array: Il désigne une matrice de valeurs ou une formule matricielle à laquelle la fonction s'applique.
• [k] : Ce paramètre n'est requis que par certaines fonctions, comme LARGE(array, k), SMALL(array, k), PERCENTILE.INC(array, k), PERCENTILE.EXC(array, k) et QUARTILE.EXC(array, quart).

Comment agréger des données dans Excel ?

Vous pouvez agréger des données dans Excel avec la forme référence ou la forme matricielle, comme indiqué plus haut. Ici, vous apprendrez à utiliser la forme référence de la formule AGGREGATE pour calculer la moyenne de données dans Excel.

Comment utiliser la fonction AGGREGATE dans Excel ?

Les fonctions d'agrégation vous aident à faire des calculs qui renvoient une seule valeur de synthèse. Les cinq plus courantes sont SUM (SOMME), AVERAGE (MOYENNE), COUNT (NB), MAX et MIN, également accessibles via le bouton "Somme automatique" de l'onglet Accueil. Ces fonctions restent toutefois basiques et n'acceptent pas de critères plus complexes. Voici comment utiliser la fonction AGGREGATE dans Excel pour obtenir des résultats précis.

Imaginons que vous deviez calculer le montant moyen des prêts accordés à vos clients, en ignorant les erreurs présentes dans la plage de cellules. Si vous appliquez la fonction AVERAGE à cette plage, vous obtiendrez une erreur. Voyons ce qui se passe si on la combine avec AGGREGATE.

1. Saisissez "=AGGREGATE(" dans la première cellule vide après votre jeu de données. Comme vous le voyez, Excel propose automatiquement les deux types de formule AGGREGATE. Ici, nous utiliserons la syntaxe de référence, car AVERAGE fait partie des fonctions (de 1 à 13) qui l'utilisent.

2. Cliquez sur le paramètre function_num : Excel affiche la liste des fonctions disponibles.

3. Comme nous voulons calculer la moyenne (AVERAGE), nous sélectionnons "1". Ensuite, tapez une virgule pour passer au paramètre suivant.

4. Cliquez sur le paramètre options et sélectionnez "6" pour ignorer les erreurs. Tapez une virgule pour ajouter le paramètre suivant.

5. Cliquez sur le paramètre ref1 et sélectionnez à la souris la plage à calculer. Ajoutez la parenthèse fermante pour terminer la formule, puis appuyez sur "Entrée".

6. Comme vous le voyez, la fonction AGGREGATE a renvoyé la moyenne (AVERAGE) des valeurs de la plage sélectionnée, sans aucune erreur.

Maintenant que vous avez vu à quel point il est simple d'agréger des données avec la fonction AGGREGATE pour calculer une moyenne, voyons comment agréger des données dans Power Query.

Comment agréger des données avec Power Query dans Excel ?

Si vous devez synthétiser des données provenant de plusieurs sources, Power Query dans Excel est la meilleure option. Vous verrez ici comment ajouter une "Nouvelle source" dans Power Query et appliquer les fonctions d'agrégation pour faire des calculs simples comme SUM ou AVERAGE.

1. Allez dans Données > Obtenir des données > À partir d'un tableau ou d'une plage.

2. Vous pouvez aussi sélectionner cette option directement depuis le groupe du ruban, comme ci-dessous.

3. Excel affiche la boîte de dialogue "Créer un tableau" pour que vous confirmiez la plage à modifier dans Power Query.

4. Dans l'Éditeur Power Query, allez dans Accueil > Nouvelle source > Fichier pour ajouter des données provenant d'un autre fichier.

5. Sélectionnez le fichier dont vous voulez importer les données, puis cliquez sur "Importer".

6. Dans la fenêtre "Navigateur", vous trouverez un dossier contenant les nouvelles données à importer dans Power Query. Sélectionnez la nouvelle table source et cliquez sur "OK".

7. Allez dans Transformer > Regrouper par.

8. Une nouvelle fenêtre s'ouvre, dans laquelle vous indiquez les colonnes à regrouper. Pour utiliser les fonctions d'agrégation, cliquez sur l'option "Avancé".

9. Sélectionnez dans le menu déroulant la première colonne à regrouper. Ici, j'ai choisi "LOAN AMOUNT". Pour ajouter d'autres colonnes, cliquez sur le bouton "Ajouter un regroupement" en dessous.

10. Pour agréger les données avec le calcul de votre choix, saisissez un nom dans "Nouveau nom de colonne", sélectionnez l'"Opération", puis la "Colonne" contenant les valeurs à calculer. Cliquez sur le bouton "Ajouter une agrégation" pour ajouter autant d'opérations que nécessaire.

11. Une fois vos critères d'agrégation choisis, cliquez sur "OK".

12. Comme ci-dessous, Power Query affiche automatiquement le résultat de toutes les fonctions d'agrégation sélectionnées. De plus, le volet "Paramètres d'une requête", à droite, liste toutes les actions, c'est-à-dire les étapes, suivies pour obtenir ce résultat.

Conclusion

L'un des grands atouts d'Excel est sa large palette de formules et de fonctions de calcul, qui aident à transformer et à traiter de gros volumes de données rapidement et simplement. Même si Power Query peut demander une meilleure maîtrise d'Excel, les fonctions de base d'AGGREGATE permettent déjà des opérations puissantes, impossibles à réaliser à la main.

Vous devriez maintenant être à l'aise avec la formule AGGREGATE d'Excel et ses fonctions de base, et savoir l'appliquer selon ce que vous devez calculer.

Pour en savoir plus sur les fonctionnalités de traitement et de transformation des données qu'offre Excel, consultez l'un des articles suivants :

• Comment consolider des données dans Excel (étape par étape)
• Combiner plusieurs fichiers CSV en un seul fichier Excel (plusieurs méthodes)
• Comment combiner plusieurs colonnes Excel en une seule ?

AGGREGATE n'est pas le seul moyen de synthétiser une plage. Pour des totaux qui dépendent d'une condition, essayez SUMIF dans Excel. Pour regrouper et totaliser un grand tableau sans écrire de formules, créez un tableau croisé dynamique dans Excel. Et si les chiffres dont vous avez besoin sont répartis dans plusieurs classeurs, vous pouvez consolider des fichiers Excel automatiquement avec Sheetgo, une plateforme d'automatisation des données basée sur l'IA qui tient les données combinées à jour. Essayez Sheetgo gratuitement avec vos propres fichiers.