Ce didacticiel montre comment utiliser les fonctions Excel AVERAGEIF et AVERAGEIFS dans Excel et Google Sheets pour faire la moyenne des données qui répondent à certains critères.
Aperçu de la fonction AVERAGEIF
Vous pouvez utiliser la fonction MOYENNEIF dans Excel pour compter les cellules contenant une valeur spécifique, compter les cellules supérieures ou égales à une valeur, etc.
Pour utiliser la fonction de feuille de calcul Excel AVERAGEIF, sélectionnez une cellule et tapez :
(Remarquez comment les entrées de formule apparaissent)
Syntaxe et arguments de la fonction AVERAGEIF :
=AVERAGEIF (plage, critères, [plage_moyenne])
gamme - La plage de cellules à compter.
Critères - Les critères qui contrôlent quelles cellules doivent être comptées.
moyenne_plage - [optionnel] Les cellules à moyenner. Lorsqu'il est omis, la plage est utilisée.
Qu'est-ce que la fonction MOYENNEIF ?
La fonction MOYENNEIF est l'une des fonctions les plus anciennes utilisées dans les feuilles de calcul. Il est utilisé pour parcourir une plage de cellules en vérifiant un critère spécifique, puis en donnant la moyenne (c'est-à-dire la moyenne mathématique) si les valeurs d'une plage correspondent à ces valeurs. La fonction AVERAGEIF d'origine était limitée à un seul critère. Après 2007, la fonction AVERAGEIFS a été créée qui permet une multitude de critères. La plupart de l'utilisation générale reste la même entre les deux, mais il existe des différences critiques dans la syntaxe que nous aborderons tout au long de cet article.
Si vous ne l'avez pas déjà fait, vous pouvez consulter une grande partie de la structure et des exemples similaires dans l'article COUNTIFS .
Exemple de base
Considérons cette liste de ventes enregistrées, et nous voulons connaître le revenu moyen.
Parce que nous avions une dépense, la valeur négative, nous ne pouvons pas simplement faire une moyenne de base. Au lieu de cela, nous voulons faire la moyenne uniquement des valeurs supérieures à 0. Le "supérieur à 0" est ce qui sera notre critère dans une fonction AVERAGEIF. Notre formule pour énoncer ceci est
=MOYENNEIF(A2:A7, ">0")
Exemple à deux colonnes
Alors que la fonction d'origine AVERAGEIF a été conçue pour vous permettre d'appliquer un critère à la plage de nombres que vous souhaitez additionner, la plupart du temps, vous devrez appliquer un ou plusieurs critères à d'autres colonnes. Considérons ce tableau :
Maintenant, si nous utilisons la fonction d'origine AVERAGEIF pour savoir combien de bananes nous avons en moyenne. Nous allons mettre nos critères dans la cellule D1, et nous devrons donner la plage que nous voulons moyenne comme dernier argument, et donc notre formule serait
=MOYENNEIF(A2:A7, D1, B2:B7)
Cependant, lorsque les programmeurs ont finalement réalisé que les utilisateurs voulaient donner plus d'un critère, la fonction AVERAGEIFS a été créée. Afin de créer une structure qui fonctionnerait pour n'importe quel nombre de critères, AVERAGEIFS exige que la plage de somme soit répertoriée en premier. Dans notre exemple, cela signifie que la formule doit être
=MOYENNEIFS(B2:B7, A2:A7, D1)
REMARQUE : Ces deux formules obtiennent le même résultat et peuvent se ressembler. Faites donc très attention à la fonction utilisée pour vous assurer de répertorier tous les arguments dans le bon ordre.
Travailler avec des dates, plusieurs critères
Lorsque vous travaillez avec des dates dans une feuille de calcul, bien qu'il soit possible de saisir la date directement dans la formule, il est préférable d'avoir la date dans une cellule afin que vous puissiez simplement référencer la cellule dans une formule. Par exemple, cela aide l'ordinateur à savoir que vous souhaitez utiliser la date du 27/05/2020, et non le nombre 5 divisé par 27 divisé par 2022.
Regardons notre prochain tableau enregistrant le nombre de visiteurs sur un site toutes les deux semaines.
Nous pouvons spécifier les points de début et de fin de la plage que nous voulons regarder dans D2 et E2. Notre formule alors pour trouver la moyenne du nombre de visiteurs dans cette fourchette pourrait être :
=MOYENNEIFS(B2:B7, A2:A7, ">="&D2, A2:A7, "<="&E2)
Notez comment nous avons pu concaténer les comparaisons de « =" aux références de cellules pour créer les critères. De plus, même si les deux critères étaient appliqués à la même plage de cellules (A2:A7), vous devez écrire la plage deux fois, une fois pour chaque critère.
Plusieurs colonnes
Lorsque vous utilisez plusieurs critères, vous pouvez les appliquer à la même plage que nous l'avons fait avec l'exemple précédent, ou vous pouvez les appliquer à différentes plages. Combinons nos exemples de données dans ce tableau :
Nous avons configuré certaines cellules pour que l'utilisateur entre ce qu'il souhaite rechercher dans les cellules E2 à G2. Il nous faut donc une formule qui additionnera le nombre total de pommes cueillies en février. Notre formule ressemble à ceci :
=MOYENNEIFS(C2:C7, B2:B7, ">="&F2, B2:B7, "<="&G2, A2:A7, E2)
AVERAGEIFS avec logique de type OU
Jusqu'à présent, les exemples que nous avons utilisés ont tous été basés sur une comparaison ET, où nous recherchons des lignes qui répondent à tous nos critères. Maintenant, nous allons considérer le cas où vous souhaitez rechercher la possibilité d'une ligne répondant à l'un ou l'autre critère.
Regardons cette liste de ventes:
Nous aimerions additionner les ventes moyennes d'Adam et de Bob. Tout d'abord, une brève discussion sur la prise de moyennes. Si vous avez un nombre impair de choses telles que 3 entrées pour Adam et 2 pour Bob, vous ne pouvez pas simplement prendre la moyenne des ventes de chaque personne. C'est ce qu'on appelle prendre la moyenne des moyennes, et vous finissez par donner une pondération injuste à l'élément qui a peu d'entrées. Si tel est le cas avec vos données, vous devrez calculer une moyenne de manière « manuelle » : prenez la somme de tous vos articles divisée par le nombre de vos articles. Pour savoir comment procéder, vous pouvez consulter les articles ici :
Maintenant, si le nombre d'entrées est le même, comme dans notre tableau, vous avez plusieurs options. Le plus simple est d'additionner deux AVERAGEIFS ensemble, comme ça, puis de diviser par 2 (le nombre d'éléments dans notre liste)
=(MOYENNEIFS(B2:B7, A2:A7, "Adam")+MOYENNEIFS(B2:B7, A2:A7, "Bob"))/2
Ici, nous avons demandé à l'ordinateur de calculer nos scores individuels, puis nous les avons additionnés.
Notre prochaine option est bonne lorsque vous avez plus de plages de critères, de sorte que vous ne voulez pas avoir à réécrire la formule entière à plusieurs reprises. Dans la formule précédente, nous avons demandé manuellement à l'ordinateur d'ajouter deux AVERAGEIFS différents ensemble. Cependant, vous pouvez également le faire en écrivant vos critères dans un tableau, comme ceci :
=MOYENNE(MOYENNEIFS(B2:B7, A2:A7, {"Adam", "Bob"}))
Regardez comment le tableau est construit à l'intérieur des accolades. Lorsque l'ordinateur évaluera cette formule, il saura que nous voulons calculer une fonction AVERAGEIFS pour chaque élément de notre tableau, créant ainsi un tableau de nombres. La fonction MOYENNE externe prendra alors ce tableau de nombres et le transformera en un nombre unique. En parcourant l'évaluation de la formule, cela ressemblerait à ceci :
=MOYENNE(MOYENNEIFS(B2:B7, A2:A7, {"Adam", "Bob"})) =MOYENNE(13701, 21735) =17718
Nous obtenons le même résultat, mais nous avons pu écrire la formule un peu plus succinctement.
Traiter les blancs
Parfois, votre ensemble de données contient des cellules vides que vous devez rechercher ou éviter. La configuration des critères pour ceux-ci peut être un peu délicate, alors regardons un autre exemple.
Notez que la cellule A3 est vraiment vide, tandis que la cellule A5 a une formule renvoyant une chaîne de longueur nulle de "". Si nous voulons trouver la moyenne totale de vraiment cellules vides, nous utiliserions un critère de « =" et notre formule ressemblerait à ceci :
=MOYENNEIFS(B2:B7,A2:A7,"=")
D'un autre côté, si nous voulons obtenir la moyenne pour toutes les cellules qui semblent vides visuellement, nous allons changer les critères en "", et la formule ressemble à
=MOYENNEIFS(B2:B7,A2:A7,"")
Retournons les choses : et si vous voulez trouver la moyenne des cellules non vides ? Malheureusement, la conception actuelle ne vous permettra pas d'éviter la chaîne de longueur nulle. Vous pouvez utiliser un critère de "", mais comme vous pouvez le voir dans l'exemple, il inclut toujours la valeur de la ligne 5.
=MOYENNEIFS(B2:B7,A2:A7,"")
Si vous ne devez pas compter les cellules contenant des chaînes de longueur nulle, vous devrez envisager d'utiliser la fonction LEN dans un SUMPRODUCT.
AVERAGEIF dans Google Sheets
La fonction AVERAGEIF fonctionne exactement de la même manière dans Google Sheets que dans Excel :
