Guide : Calculer une moyenne avec un filtre dans Excel

Excel offre plusieurs méthodes pour calculer une moyenne tout en tenant compte des filtres ou des conditions. Voici un guide détaillé pour y parvenir.

1. Calculer une moyenne avec des filtres appliqués

Lorsque vous appliquez un filtre dans Excel, la fonction SOUS.TOTAL permet de calculer une moyenne en tenant compte uniquement des lignes visibles.

Formule :

=SOUS.TOTAL(101; plage)

Explications :

• 101 : Indique à Excel de calculer une moyenne uniquement pour les lignes visibles.
• plage : La plage de cellules sur laquelle calculer la moyenne.

Étapes :

1. Appliquez un filtre sur votre tableau (via l’onglet Données > Filtrer).
2. Sélectionnez une cellule où afficher la moyenne filtrée.
3. Entrez la formule =SOUS.TOTAL(101; plage) en remplaçant plage par la plage des données numériques.
4. Ajustez le filtre : la moyenne se mettra à jour automatiquement pour refléter les données visibles.

2. Calculer une moyenne basée sur des critères (sans filtre)

Utilisez la fonction MOYENNE.SI pour calculer une moyenne basée sur un critère spécifique.

Formule :

=MOYENNE.SI(plage_critères; critère; plage_moyenne)

Explications :

• plage_critères : La plage contenant les critères (par exemple, catégories ou noms).
• critère : La condition pour inclure les lignes dans la moyenne (ex. : ">50", "Ventes").
• plage_moyenne : La plage des données numériques sur lesquelles calculer la moyenne.

Exemple :

Pour calculer la moyenne des ventes supérieures à 100 dans la colonne B :

=MOYENNE.SI(B1:B10; ">100"; C1:C10)

3. Calculer une moyenne avec plusieurs critères

Pour appliquer plusieurs conditions, utilisez MOYENNE.SI.ENS.

Formule :

=MOYENNE.SI.ENS(plage_moyenne; plage_critères1; critère1; plage_critères2; critère2; ...)

Exemple :

Pour calculer la moyenne des ventes dans la colonne C, où la région est “Nord” et le montant des ventes est supérieur à 100 :

=MOYENNE.SI.ENS(C1:C10; A1:A10; "Nord"; B1:B10; ">100")

4. Utiliser une colonne auxiliaire pour des moyennes filtrées avancées

Si vous souhaitez utiliser des critères complexes tout en appliquant un filtre, créez une colonne auxiliaire pour indiquer les lignes à inclure dans le calcul :

1. Ajoutez une colonne avec une formule logique, par exemple : =SI(B1>100;1;0) Cela renverra 1 si la condition est remplie.
2. Filtrez sur la colonne auxiliaire.
3. Utilisez SOUS.TOTAL pour calculer la moyenne des lignes visibles.

5. Moyenne dynamique avec filtres via Tableau croisé dynamique

Si vous travaillez avec un grand tableau, les Tableaux croisés dynamiques permettent de calculer des moyennes sur des sous-ensembles de données.

Étapes :

1. Sélectionnez vos données et allez dans Insérer > Tableau croisé dynamique.
2. Placez les catégories dans les Lignes.
3. Placez la plage numérique dans Valeurs et sélectionnez Paramètres de champ de valeur > Moyenne.

6. Problèmes courants et solutions

Problème 1 : Moyenne incorrecte avec des cellules vides

• Les cellules vides ne sont pas prises en compte dans le calcul. Pour les éviter, remplacez-les par 0 ou un autre substitut avant de calculer.

Problème 2 : Moyenne incorrecte avec des filtres

• Assurez-vous d’utiliser SOUS.TOTAL au lieu de MOYENNE si un filtre est appliqué.

Problème 3 : Critères mal interprétés

• Vérifiez les guillemets autour des critères textuels ou numériques (ex. : ">100").

Résumé des formules principales

Avec ces méthodes, vous pouvez calculer des moyennes précises en fonction des filtres ou des critères souhaités.

Comment intégrer une moyenne dans un tableau Excel

Voici différentes méthodes pour intégrer une moyenne dans un tableau Excel, avec des exemples pratiques.

1. Ajouter une ligne de total dans un tableau

Si vous utilisez un tableau formaté (via l’onglet Accueil > Mettre sous forme de tableau), vous pouvez facilement ajouter une ligne de total qui inclut la moyenne :

Étapes :

1. Créer un tableau formaté :
   • Sélectionnez vos données.
   • Allez dans l’onglet Accueil > Mettre sous forme de tableau.
   • Choisissez un style de tableau.
2. Ajouter une ligne de total :
   • Cliquez sur le tableau.
   • Allez dans l’onglet Création de tableau (ou Conception).
   • Cochez Ligne des totaux.
3. Changer le total en moyenne :
   • Cliquez sur la cellule de la colonne souhaitée dans la ligne des totaux.
   • Dans le menu déroulant, choisissez Moyenne.

2. Insérer une formule de moyenne dans une cellule du tableau

Vous pouvez insérer une formule MOYENNE ou SOUS.TOTAL dans une cellule du tableau ou en dehors du tableau.

Formule :

• Si votre tableau est nommé (par exemple, Tableau1) et la colonne à moyenne est appelée Ventes : =MOYENNE(Tableau1[Ventes])

Étapes :

1. Sélectionnez une cellule vide à l’intérieur ou à l’extérieur du tableau.
2. Entrez la formule =MOYENNE(Tableau1[NomColonne]) ou sélectionnez directement les données dans la colonne.
3. Appuyez sur Entrée pour afficher la moyenne.

3. Ajouter une moyenne dynamique avec un filtre

Pour intégrer une moyenne qui se met à jour dynamiquement en fonction des filtres appliqués au tableau, utilisez la fonction SOUS.TOTAL.

Formule :

=SOUS.TOTAL(101; Tableau1[Ventes])

Étapes :

1. Insérez cette formule dans une cellule en dehors du tableau.
2. Appliquez un filtre sur le tableau (par exemple, filtrer une catégorie).
3. La moyenne affichée se mettra à jour pour tenir compte uniquement des lignes visibles.

4. Utiliser un tableau croisé dynamique

Un tableau croisé dynamique permet de calculer et intégrer des moyennes automatiquement dans une structure bien organisée.

Étapes :

1. Sélectionnez vos données et allez dans Insérer > Tableau croisé dynamique.
2. Placez la colonne pour laquelle vous souhaitez calculer la moyenne dans Valeurs.
3. Cliquez sur la liste déroulante de la colonne dans Valeurs, puis sur Paramètres de champ de valeur.
4. Choisissez Moyenne et cliquez sur OK.
5. Ajoutez d’autres colonnes comme Lignes ou Colonnes pour organiser vos données.

5. Ajouter une colonne auxiliaire avec une moyenne conditionnelle

Si vous voulez intégrer une moyenne conditionnelle directement dans le tableau, ajoutez une colonne calculée avec MOYENNE.SI ou MOYENNE.SI.ENS.

Formule :

• Par exemple, pour calculer une moyenne des ventes supérieures à 100 : =MOYENNE.SI(Tableau1[Ventes]; ">100")

Étapes :

1. Ajoutez une nouvelle colonne dans le tableau avec un titre explicite (par exemple, Moyenne conditionnelle).
2. Entrez une formule pour calculer la moyenne conditionnelle.
3. La moyenne s’appliquera dynamiquement aux lignes du tableau.

6. Intégrer la moyenne avec mise en forme conditionnelle

Pour mettre en avant la moyenne dans le tableau, utilisez la mise en forme conditionnelle.

Étapes :

1. Sélectionnez la colonne où la moyenne doit être mise en valeur.
2. Allez dans Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules.
3. Ajoutez une règle personnalisée avec une formule : =B2>MOYENNE($B$2:$B$10) Cette règle mettra en surbrillance les valeurs supérieures à la moyenne.

Résumé des méthodes

Synthèse des étapes pour créer un calculateur automatisé de moyenne dans Excel :

1. Création des données

• Préparer un tableau avec :
  • Une colonne contenant des valeurs numériques.
  • Une colonne pour les catégories (facultatif, utile pour des critères supplémentaires).
  • Une colonne critère d’inclusion (exemple : “Yes” pour inclure dans la moyenne, “No” pour exclure).

2. Ajout des formules dynamiques

• Insérer une formule conditionnelle pour vérifier les lignes à inclure dans la moyenne :
  • Exemple : =IF(C2="Yes", A2, "")
  • Cette formule place les valeurs dans une nouvelle colonne uniquement si elles répondent au critère.

3. Calculer la moyenne finale

• Utiliser une formule pour calculer la moyenne des valeurs filtrées :
  • Exemple en anglais : =AVERAGEIF(C2:C11, "Yes", A2:A11)
  • Cela calcule la moyenne uniquement pour les lignes marquées “Yes”.

4. Mise en forme et personnalisation

• Ajouter un en-tête pour les colonnes avec :
  • Texte en gras.
  • Couleur d’arrière-plan (par exemple : bleu clair pour les en-têtes, jaune pour les résultats).
• Mettre en évidence la cellule contenant la moyenne finale (texte en gras rouge et fond coloré).

5. Test et sauvegarde

• Vérifier les formules pour éviter les erreurs (#NAME?, #DIV/0!).
• Enregistrer le fichier Excel avec un titre clair, comme Average_Calculator.xlsx.

Contenu du fichier généré :

• Une moyenne dynamique basée sur la colonne “Include in Average”.
• Un formatage clair pour identifier les résultats.
• Un calcul automatisé prêt à s’adapter à toute modification des données.

Ce processus garantit une moyenne correcte et actualisée en fonction de critères définis par l’utilisateur.

Autres articles

Série d’exercices corrigés - Excel Perfectionnement

Voici une série d’exercices conçus pour perfectionner vos compétences Excel....

Read more

Utiliser les fonctions pour afficher la date...

Excel propose plusieurs fonctions pour insérer ou manipuler la date...

Read more

Comment convertir des textes en chiffres dans...

Lorsque des nombres sont stockés sous forme de texte dans...

Read more