Comment calculer une moyenne dans une colonne selon des critères situés dans une autre colonne sous Excel ?
Dans de nombreux cas concrets sous Excel, il est fréquent de devoir calculer la moyenne des valeurs d’une colonne, regroupées ou filtrées en fonction des données correspondantes dans une autre colonne. Par exemple, vous souhaiterez peut-être déterminer les ventes moyennes par vendeur ou par région, comme le montre la capture d’écran ci-dessous. Ce type de calcul est couramment utilisé dans les rapports récapitulatifs, les analyses de performance et les revues de données. Dans cet article, nous vous présentons plusieurs méthodes efficaces pour obtenir ce résultat, afin que vous puissiez choisir l’approche la mieux adaptée à vos besoins et à votre niveau de maîtrise.
Calculer une moyenne dans une colonne en fonction de valeurs identiques dans une autre colonne à l’aide de formules
Calculer une moyenne dans une colonne en fonction de valeurs identiques dans une autre colonne avec Kutools pour Excel
Calculer une moyenne par groupe à l’aide de Tableau croisé dynamique
Automatiser le calcul de moyennes groupées à l’aide d’une macro VBA
Calculer une moyenne dans une colonne en fonction de valeurs identiques dans une autre colonne à l’aide de formules
L’une des méthodes les plus simples pour calculer la moyenne d’un groupe selon une autre colonne dans Excel consiste à utiliser des formules conditionnelles telles que MOYENNE.SI ou MOYENNE.SI.ENS. Cette approche s’avère particulièrement efficace lorsque vous souhaitez obtenir des résultats ciblés répondant à des critères précis, comme la moyenne des ventes par ville ou par vendeur.
1. Sélectionnez une cellule vide où vous souhaitez afficher le résultat, saisissez la formule suivante, puis appuyez sur Entrée :
=AVERAGEIF(B2:B13,E2,C2:C13)
Explication des paramètres : Dans la formule ci-dessus, B2:B13 correspond à la plage contenant les critères à vérifier (par exemple, la ville ou le vendeur), E2 est la valeur spécifique à laquelle vous souhaitez comparer (par exemple, « Owenton »), et C2:C13 correspond à la plage contenant les valeurs numériques dont vous souhaitez calculer la moyenne.
Après avoir appuyé sur Entrée, vous obtenez instantanément la moyenne du groupe indiqué en E2 (par exemple, les ventes moyennes pour « Owenton »).
Pour calculer la moyenne de chaque valeur unique de la colonne de critères, il vous suffit de modifier la valeur dans la cellule de critère (E2) en conséquence, ou de copier la formule vers le bas si vous disposez d’une liste d’entrées uniques.
Conseil pratique : Pour les grands jeux de données ou un grand nombre de groupes uniques, combinez cette formule avec une liste de valeurs uniques (obtenue via des outils comme « Supprimer les doublons » ou la fonction UNIQUE d’Excel dans Office 365 et Excel 2021) afin d’accélérer le calcul simultané de toutes les moyennes groupées. Vérifiez attentivement que les plages utilisées dans votre formule couvrent bien l’ensemble des données concernées et restent alignées lorsque vous la copiez.
Erreurs courantes et dépannage :
- Si vous obtenez une erreur #DIV/0!, assurez-vous que la valeur de vos critères figure bien dans la plage sélectionnée.
- Assurez-vous que votre plage numérique ne contient que des nombres valides ; les cellules vides ou contenant du texte risquent de fausser le calcul.
Calculer une moyenne dans une colonne en fonction de valeurs identiques dans une autre colonne avec Kutools pour Excel
Si vous souhaitez calculer automatiquement la moyenne de toutes les valeurs uniques d’une colonne sans avoir à saisir plusieurs fois des formules ni appliquer manuellement des filtres, Kutools pour Excel vous propose une solution simplifiée. Cette fonctionnalité s’avère particulièrement utile lorsque vous travaillez avec de longues listes ou des jeux de données complexes.
1. Sélectionnez toute la plage de données incluant à la fois la colonne de regroupement et la colonne numérique dont vous souhaitez calculer la moyenne, puis accédez à Kutools > Fusionner et scinder > Fusion avancée des lignes.
2. Dans la boîte de dialogue Combinez les lignes selon la colonne, procédez comme suit :
- Sélectionnez la colonne selon laquelle vous souhaitez regrouper (par exemple, Ville ou Vendeur), puis cliquez sur le bouton Clé primaire pour la définir comme champ de regroupement.
- Sélectionnez la colonne numérique dont vous souhaitez calculer la moyenne, puis cliquez sur Calculer > Moyenne.
Astuce : Pour toute autre colonne (comme les dates), vous pouvez préciser comment combiner leurs valeurs (par exemple, en les joignant par une virgule). - Cliquez sur OK pour exécuter l’opération.
Kutools regroupe instantanément les données selon la clé sélectionnée et affiche la moyenne de chaque groupe dans la colonne numérique.
Le traitement par lots de Kutools est idéal pour les utilisateurs qui analysent régulièrement des statistiques agrégées, telles que les rapports mensuels, les synthèses départementales ou tout autre calcul impliquant plusieurs groupes. En tant que complément, Kutools préserve intégralement la structure originale de vos données, et sa fonction d’aperçu vous permet de valider les regroupements avant toute modification.
Remarques et conseils :
- Assurez-vous qu’il n’y a aucune ligne vide dans votre sélection avant d’exécuter l’outil.
- Si vous avez besoin des moyennes groupées ailleurs, utilisez Copier et Coller pour transférer les résultats une fois le calcul effectué.
- Dans les jeux de données très volumineux, assurez-vous que les colonnes de regroupement et les colonnes numériques sont correctement attribuées pour éviter toute confusion.
Kutools pour Excel– Boostez Excel avec plus de 300 outils essentiels, pour gagner en rapidité et en simplicité, et profitez des fonctionnalités d’intelligence artificielle pour un traitement de données plus intelligent et une productivité accrue.Obtenez-le dès maintenant
Calculer une moyenne par groupe à l’aide de Tableau croisé dynamique
Les tableaux croisés dynamiques offrent un moyen puissant et intégré de résumer, regrouper et analyser des données — y compris le calcul de moyennes par groupe — sans avoir besoin de formules ni de modules complémentaires tiers. Idéale pour obtenir une vue interactive des moyennes et des totaux selon différentes catégories, cette technique s’adapte aussi bien aux petits qu’aux très grands jeux de données.
Comment configurer un Tableau croisé dynamique pour calculer des moyennes groupées :
- Sélectionnez n’importe quelle cellule de votre jeu de données, puis accédez à Insertion > Tableau croisé dynamique. Dans la boîte de dialogue qui s’ouvre, choisissez où vous souhaitez que le tableau croisé dynamique apparaisse (dans une nouvelle feuille ou dans une feuille de calcul existante), puis cliquez sur OK.
- Dans le volet Champs de tableau croisé dynamique, faites glisser la colonne selon laquelle vous souhaitez regrouper vers la zone Lignes (par exemple, « Ville » ou « Vendeur »).
- Faites glisser la colonne numérique dont vous souhaitez calculer la moyenne (par exemple, « Ventes ») dans la zone Valeurs. Par défaut, Excel calcule la somme ; pour la remplacer par la moyenne, cliquez sur le champ de valeur, sélectionnez Paramètres des champs de valeur, puis choisissez Moyenne.
Votre Tableau croisé dynamique affiche immédiatement la valeur moyenne par groupe. Vous pouvez rapidement filtrer, trier et mettre en forme le rapport selon vos besoins. Cette méthode est conviviale et évite les risques d’erreurs de formule.
Avantages : Interactif, gère efficacement les grands volumes de données et permet de résumer plusieurs statistiques simultanément.
Inconvénients : Le résultat s’affiche sous forme de tableau croisé dynamique, et non sous forme de liste simple ; une actualisation occasionnelle est nécessaire si les données source changent.
Conseil : Un double-clic sur n’importe quelle cellule de synthèse dans le tableau croisé dynamique ouvre une nouvelle feuille de calcul contenant les données sous-jacentes de ce groupe, facilitant ainsi l’audit des détails ou le dépannage des écarts.
Problèmes courants :
- Si vous n’obtenez pas la moyenne souhaitée, vérifiez que le champ de valeur est bien défini sur « Moyenne » dans les Paramètres des champs de valeur.
- Vérifiez vos données sources afin de repérer d’éventuelles lignes vides ou colonnes superflues susceptibles de perturber la mise en page de votre tableau croisé dynamique.
Automatiser le calcul de moyennes groupées à l’aide d’une macro VBA
Pour les utilisateurs qui doivent fréquemment calculer des moyennes pour de nombreux groupes ou souhaitent produire automatiquement un résumé, écrire une macro VBA permet d’économiser un temps considérable. VBA s’avère particulièrement utile lorsque vous prévoyez que la structure de vos données restera constante ou que vous souhaitez générer des rapports récapitulatifs répétés en un seul clic.
Avant de commencer, veillez à enregistrer votre classeur et à activer les macros. Voici comment procéder :
1. Cliquez sur Outils de développement > Visual Basic pour ouvrir l’éditeur VBA. Dans l’éditeur, cliquez sur Insertion > Module afin de créer un nouveau module de code. Collez le code ci-dessous dans le module :
Sub GroupAverageSummary()
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dict As Object
Dim groupCol As Range, valueCol As Range
Dim lastRow As Long
Dim i As Long
Dim groupKey As Variant
Dim sumArr As Object, countArr As Object
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcSheet = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
' Prompt user to select group (criteria) column
Set groupCol = Application.InputBox("Select the group (criteria) column:", xTitleId, Type:=8)
If groupCol Is Nothing Then Exit Sub
' Prompt user to select value column
Set valueCol = Application.InputBox("Select the value column to average:", xTitleId, Type:=8)
If valueCol Is Nothing Then Exit Sub
Set sumArr = CreateObject("Scripting.Dictionary")
Set countArr = CreateObject("Scripting.Dictionary")
For i = 1 To groupCol.Rows.Count
groupKey = groupCol.Cells(i, 1).Value
If groupKey <> "" And IsNumeric(valueCol.Cells(i, 1).Value) Then
If Not dict.Exists(groupKey) Then
dict.Add groupKey, 0
sumArr.Add groupKey, 0
countArr.Add groupKey, 0
End If
sumArr(groupKey) = sumArr(groupKey) + valueCol.Cells(i, 1).Value
countArr(groupKey) = countArr(groupKey) + 1
End If
Next
' Output result to a new worksheet
Set dstSheet = Worksheets.Add
dstSheet.Name = "Group Average Summary"
dstSheet.Cells(1, 1).Value = "Group"
dstSheet.Cells(1, 2).Value = "Average"
i = 2
For Each groupKey In dict.Keys
dstSheet.Cells(i, 1).Value = groupKey
dstSheet.Cells(i, 2).Value = sumArr(groupKey) / countArr(groupKey)
i = i + 1
Next
End Sub2. Après avoir inséré le code, fermez l’éditeur VBA. Retournez dans Excel, appuyez sur Alt+F8, sélectionnez GroupAverageSummary dans la liste, puis cliquez sur Exécuter. La macro vous demandera de choisir votre colonne de groupe (critères) et votre colonne de valeurs (numériques). Une fois ces sélections effectuées, elle générera automatiquement une nouvelle feuille de calcul intitulée « Group Average Summary », affichant chaque groupe unique accompagné de sa moyenne correspondante.
Notes concernant les paramètres et le fonctionnement :
- Vérifiez que vos colonnes de regroupement et de valeurs sont de même longueur et contiennent des données valides (évitez notamment les sélections partielles).
- Cette macro peut être adaptée pour gérer des regroupements plus avancés ou inclure des statistiques récapitulatives supplémentaires, selon vos besoins.
- Si votre feuille contient déjà un résumé « Group Average Summary » Nom de la feuille de calculd, la macro créera une feuille de calcul portant le nom par défaut « Nom de la feuille de calcul ».
Dépannage :
- Si vous voyez un message tel que « indice hors limites » ou similaire, assurez-vous que vos plages sélectionnées sont correctement alignées et situées sur la même feuille de calcul.
- Pour des résultats optimaux, veillez à ce que la colonne de valeurs ne contienne que des données numériques : toute cellule comportant du texte ou vide au sein de la plage numérique sera ignorée par la macro.
Cette macro est idéale pour le traitement par lots, la génération automatisée de rapports ou les situations où vous devez fréquemment résumer des jeux de données nouveaux ou mis à jour.
Démo : calculer la moyenne d’une colonne en fonction des valeurs identiques dans une autre colonne avec Kutools pour Excel
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels