La fonction AGREGAT Excel est très utile dès que vous travaillez avec des tableaux un peu complexes. Elle permet de réaliser des calculs comme une somme, une moyenne, un minimum ou un maximum. Le tout en ignorant certaines données.
La fonction AGREGAT permet de mieux contrôler ce qui est pris en compte dans le calcul.
Lorsqu’un tableau contient des filtres, des lignes masquées ou des erreurs, elle offre une solution fiable là où les fonctions classiques atteignent vite leurs limites.
La fonction AGREGAT dans Excel est très facile à utiliser et extrêmement utile. Elle permet par exemple d'ignorer les lignes qui sont masquées dans le calcul d'une somme ou d'une moyenne de plusieurs valeurs filtrées (avec des segments ou des filtres normaux).
Avis de notre formateur : Nicolas Parent
Dans cette vidéo, notre formateur Nicolas vous présente la fonction AGREGAT sur Excel. Une fonction idéale pour réaliser différents calculs en ignorant les erreurs et lignes masquées. Tout cela, même dans des tableaux filtrés !
5 informations à retenir sur la fonction SOUS.TOTAL Excel
- La fonction AGREGAT sur Excel permet de renvoyer des agrégats dans une base de données, constituant une version améliorée de la fonction SOUS.TOTAL, plus complète.
- Elle permet notamment de réaliser des calculs automatiques, même lorsque des filtres sont appliqués à la base de données.
- Lors de l'utilisation de la fonction AGREGAT, l'utilisateur sélectionne d'abord la fonction désirée (comme la somme ou la grande valeur) et indique ensuite les options appropriées, telles que l'ignorance des lignes masquées.
- Cette fonction est particulièrement utile pour obtenir des résultats dynamiques en fonction des filtres appliqués, garantissant une analyse précise des données.
- La fonction AGREGAT apparait comme l'une des fonctions les plus sous-estimées sur Excel, permettant des calculs sophistiqués en fonction des données filtrées dans un tableau.
Syntaxe de la fonction AGREGAT Excel
AGREGAT est particulièrement utile lorsque vos données contiennent des erreurs ou des sous-totaux. Elle est aussi très pratique dans des tableaux filtrés, lorsque vous souhaitez que le résultat s’adapte automatiquement à ce qui est affiché.
AGREGAT(No_fonction;Options;Date_début;[k])
| Argument | Description | |
|---|---|---|
| No_fonction (obligatoire) | Nombre qui indique la fonction à utiliser pour renvoyer un agrégat. | |
| Fonction | No_fonction | |
| MOYENNE | 1 | |
| NB, NBVAL, MAX, MIN, PRODUIT | 2, 3, 4, 5, 6 | |
| ECARTYPE.STANDARD, ECARTYPE.PEARSON | 7, 8 | |
| SOMME | 9 | |
| VAR.S, VAR.P.N, MEDIANE | 10, 11, 12 | |
| MODE.SIMPLE | 13 | |
| GRANDE.VALEUR, PETITE.VALEUR | 14, 15 | |
| CENTILE.INCLURE, QUARTILE.INCLURE | 16, 17 | |
| CENTILE.EXCLURE, QUARTILE.EXCLURE | 18, 19 | |
| Options (obligatoire) | Valeur numérique qui indique les valeurs à ignorer dans le calcul. | |
| Comportement | Option | |
| Ignorer les fonctions SOUS.TOTAL et AGREGAT imbriquées | 0 ou omis | |
| Ignorer les lignes masquées, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées | 1 | |
| Ignorer les valeurs d’erreur, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées | 2 | |
| Ignorer les lignes masquées, les valeurs d’erreur, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées | 3 | |
| Ne rien ignorer | 4 | |
| Ignorer les lignes masquées | 5 | |
| Ignorer les valeurs d’erreur | 6 | |
| Ignorer les lignes masquées et les valeurs d’erreur | 7 | |
| Matrice (obligatoire) | Colonne pour laquelle tu souhaites renvoyer un agrégat. | |
| [k] (facultatif) | Nombre qui indique la k-ième valeur à renvoyer. | |
Quel est mon niveau sur Excel ?
Testez gratuitement votre niveau avec notre quiz Excel (≈ 7 min). Accédez immédiatement à vos résultats, avec un corrigé détaillé et des ressources pour progresser.
Exemple 1 - Comment calculer la somme des ventes et renvoyer au plus grand nombre de client sur Excel
Ce tableau présente les ventes et le nombre de clients de deux magasins.
D’abord, tu veux calculer la somme des ventes (cellule B2) dont le calcul est dynamique en fonction du filtre appliqué (voir segment en haut à gauche).
Puis, tu veux renvoyer le 2e plus grand nombre de clients (cellule C2) dont le calcul est dynamique en fonction du filtre appliqué.
Remarques
- Tu peux insérer un segment uniquement si tes données sont mises sous forme de tableau.
- (1) 9correspond à la fonction SOMME et l'option 5permet d'ignorer les lignes masquées dans le calcul.
- (2) 14correspond à la fonction GRANDE.VALEUR et 2 permet de renvoyerla 2e plus grande valeuren fonction du filtre appliqué(segment en haut à gauche).
- Pour calculer des sous-totaux à l’intérieur du tableau, tu peux aller dans « Données », puis cliquer sur « Sous-total ».
Exercice AGREGAT Excel
Pour bien comprendre la fonction AGREGAT, le plus simple est de la tester.
Le fichier d’exercice ci-dessous vous permet de l’utiliser sur des tableaux avec filtres, lignes masquées et erreurs.
Vous verrez rapidement pourquoi cette fonction est si appréciée dans les fichiers professionnels.
Elle permet de garder des calculs fiables, même quand les données évoluent.
Nos exercices Excel pour tous les niveaux !
Progressez maintenant avec nos + 20 exercices gratuits ! Du niveau débutant à avancé, ils permettent de se former totalement gratuitement par la pratique ! Profitez des exercices gratuitement jusqu'à la fin du mois.
Nos meilleures pratiques avec la fonction AGREGAT sur Excel
En suivant ces meilleures pratiques, vous pouvez maximiser l'efficacité et l'exactitude de vos analyses de données lors de l'utilisation de la fonction AGREGAT dans Excel.
- Comprendre les types de fonctions disponibles : La fonction AGREGAT offre différents types de fonctions d'agrégation, allant de la somme à la médiane en passant par le produit et la variance. Avant d'utiliser AGREGAT, assurez-vous de choisir la fonction appropriée en fonction de l'objectif de votre analyse.
- Gérer les erreurs de calcul : Pour éviter les erreurs courantes comme #VALEUR! ou #NOMBRE!, assurez-vous que la plage de données sur laquelle vous appliquez AGREGAT ne contient que des valeurs numériques valides. Vous pouvez utiliser des fonctions de nettoyage de données pour éliminer les valeurs non numériques avant d'appliquer AGREGAT.
- Ignorer les valeurs masquées ou filtrées si nécessaire : Si vous travaillez avec des données filtrées ou masquées, utilisez les options appropriées d'ignorance des valeurs masquées dans AGREGAT pour exclure les valeurs filtrées de votre calcul. Cela garantit que seules les valeurs visibles sont prises en compte dans l'agrégation, ce qui peut être crucial pour obtenir des résultats précis lors de l'analyse de données filtrées.
- Préserver la flexibilité des références de plage : Utilisez des références de plage dynamiques plutôt que des plages de cellules statiques dans votre formule AGREGAT. Cela garantit que votre analyse reste flexible et s'adapte automatiquement aux changements de données, tels que l'ajout ou la suppression de lignes ou de colonnes.
- Vérifier les résultats : Après avoir appliqué AGREGAT, vérifiez toujours les résultats pour vous assurer de leur exactitude. Comparez les totaux obtenus avec d'autres méthodes de calcul ou vérifiez visuellement les données incluses dans la plage de référence pour vous assurer que l'agrégation correspond à vos attentes.
Les erreurs à éviter avec la fonction Excel AGREGAT
Ces erreurs courantes peuvent survenir lors de l'utilisation de la fonction AGREGAT dans Excel. Il est essentiel de comprendre ces erreurs et de les éviter en veillant à utiliser correctement les arguments de la fonction et à fournir des données valides en entrée.
| Erreur | Explication |
|---|---|
| #VALEUR! | Cette erreur se produit lorsque l'un des arguments fournis à la fonction AGREGAT n'est pas valide. Par exemple, si le type de fonction spécifié n'est pas compris entre 1 et 13, ou si la plage de référence contient des cellules vides ou des données non numériques, Excel renverra l'erreur #VALEUR!. Assurez-vous que tous les arguments de la fonction AGREGAT sont corrects et valides. |
| #NOMBRE! | L'erreur #NOMBRE! survient généralement lorsque la fonction AGREGAT est utilisée avec une plage de référence contenant des valeurs non numériques. Par exemple, si des cellules de texte ou des cellules vides sont incluses dans la plage de référence, Excel ne peut pas effectuer le calcul et renverra cette erreur. Assurez-vous donc que la plage de référence utilisée avec la fonction AGREGAT contient uniquement des valeurs numériques valides. |
| Référence incorrecte | Lorsque la référence utilisée avec la fonction AGREGAT est incorrecte, Excel renverra l'erreur #REF!. Cela peut se produire si la référence pointe vers une plage de cellules qui a été supprimée ou déplacée, ou si la référence contient une erreur de saisie, comme une faute de frappe dans le nom de la feuille ou de la cellule. |
FAQ - Questions fréquentes sur la fonction AGREGAT
La fonction AGREGAT permet de renvoyer un agrégat dans une base de données.
Contrairement à AGREGAT, la fonction SOUS.TOTAL prend en compte uniquement les lignes masquées manuellement. AGREGAT offre plus de flexibilité dans les options à prendre en compte dans le calcul.
L'équivalent de AGREGAT en anglais est la fonction AGGREGATE.
Oui, AGREGAT est particulièrement utile pour effectuer des calculs sur des données statistiques en offrant des opérations avancées telles que la médiane, le quartile, l'écart type, etc. Ces fonctions permettent d'analyser plus en profondeur la distribution des données et d'obtenir des mesures de dispersion plus précises que les fonctions d'agrégation traditionnelles.
Oui, AGREGAT peut être utilisé avec des fonctions matricielles et des tableaux structurés dans Excel. Cela permet une analyse avancée des données, y compris l'agrégation de données structurées ou filtrées dans des tableaux Excel, et l'application de fonctions d'agrégation sur des plages de données étendues ou complexes.
AGREGAT offre plusieurs options pour gérer les valeurs cachées ou filtrées. Vous pouvez choisir d'inclure toutes les valeurs visibles (option 1), d'ignorer les valeurs cachées (option 5) ou d'ignorer à la fois les valeurs cachées et les erreurs (option 7). Ces options offrent un contrôle précis sur la façon dont les valeurs masquées ou filtrées sont prises en compte dans le calcul de l'agrégation.
Contrairement aux fonctions d'agrégation traditionnelles comme la fonction SOMME ou la fonction MOYENNE, la fonction AGREGAT offre une flexibilité supplémentaire en permettant de choisir parmi 13 types d'opérations d'agrégation différents. Cela inclut des opérations plus avancées comme la médiane, le produit, le plus grand, le plus petit, etc. AGREGAT permet également d'ignorer les valeurs masquées ou filtrées, offrant ainsi un contrôle plus précis sur le calcul des résultats.
Aller plus loin avec les fonctions
Découvrez tous nos guides sur les fonctions Excel
Toutes nos fiches sur les Fonctions
Que ce soit pour des calculs financiers, l’analyse de données ou la gestion de projets, nos articles vous guideront à travers une variété de fonctions, avec des exemples concrets et des astuces pour les utiliser efficacement.
Et découvrez également notre livre Excel sur le même sujet
Maîtrisez les fonctions de A à Z sur Excel
Vous souhaitez exploiter pleinement les fonctions d'Excel et gagner du temps dans la réalisation de vos tâches ? Notre e-book est fait pour vous !
Il contient une fiche et des exercices pratiques pour chaque fonction Excel. C'est l'ouvrage idéal pour maîtriser les fonctions dont vous avez besoin, stimuler votre créativité et améliorer la qualité de vos fichiers.
Encore un doute ? Profitez d'un aperçu avec notre -> eBook Excel gratuit.
Progressez sur Excel avec Morpheus Formation
La fonction AGREGAT fait partie de ces outils qui montrent qu’on commence à bien maîtriser Excel.
Elle permet de créer des fichiers plus robustes et d’éviter beaucoup d’erreurs de calcul.
Dans notre formation Excel, nous vous accompagnons pour comprendre ce type de fonction en profondeur.
La formation est construite selon votre niveau, vos besoins et vos fichiers, avec un formateur expert à vos côtés.
L’objectif est simple : vous rendre autonome et à l’aise sur Excel, même avec des tableaux complexes.
Félicitations ! Votre ebook Excel offert !
/ CPF