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 :

Appliquez un filtre sur votre tableau (via l’onglet Données > Filtrer).
Sélectionnez une cellule où afficher la moyenne filtrée.
Entrez la formule =SOUS.TOTAL(101; plage) en remplaçant plage par la plage des données numériques.
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 :

Ajoutez une colonne avec une formule logique, par exemple : =SI(B1>100;1;0) Cela renverra 1 si la condition est remplie.
Filtrez sur la colonne auxiliaire.
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 :

Sélectionnez vos données et allez dans Insérer > Tableau croisé dynamique.
Placez les catégories dans les Lignes.
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

Formule	Utilité	Exemple
`SOUS.TOTAL(101; plage)`	Moyenne avec filtres	`=SOUS.TOTAL(101; C1:C10)`
`MOYENNE.SI`	Moyenne basée sur un critère	`=MOYENNE.SI(B1:B10; ">100"; C1:C10)`
`MOYENNE.SI.ENS`	Moyenne avec plusieurs critères	`=MOYENNE.SI.ENS(C1:C10; A1:A10; "Nord")`

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 :

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.
Ajouter une ligne de total :
- Cliquez sur le tableau.
- Allez dans l’onglet Création de tableau (ou Conception).
- Cochez Ligne des totaux.
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 :

Sélectionnez une cellule vide à l’intérieur ou à l’extérieur du tableau.
Entrez la formule =MOYENNE(Tableau1[NomColonne]) ou sélectionnez directement les données dans la colonne.
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 :

Insérez cette formule dans une cellule en dehors du tableau.
Appliquez un filtre sur le tableau (par exemple, filtrer une catégorie).
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 :

Sélectionnez vos données et allez dans Insérer > Tableau croisé dynamique.
Placez la colonne pour laquelle vous souhaitez calculer la moyenne dans Valeurs.
Cliquez sur la liste déroulante de la colonne dans Valeurs, puis sur Paramètres de champ de valeur.
Choisissez Moyenne et cliquez sur OK.
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 :

Ajoutez une nouvelle colonne dans le tableau avec un titre explicite (par exemple, Moyenne conditionnelle).
Entrez une formule pour calculer la moyenne conditionnelle.
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 :

Sélectionnez la colonne où la moyenne doit être mise en valeur.
Allez dans Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules.
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

Méthode	Utilité
Ligne de total du tableau	Moyenne rapide et intégrée dans le tableau
Formule `MOYENNE`	Moyenne statique ou dynamique avec nom de colonne
`SOUS.TOTAL`	Moyenne dynamique avec filtres
Tableau croisé dynamique	Calcul automatique dans une structure organisée
Colonne auxiliaire avec `MOYENNE.SI`	Moyenne conditionnelle ou avec plusieurs critères
Mise en forme conditionnelle	Mise en valeur des données relatives à la moyenne

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.