Comment utiliser SUBTOTAL avec AVERAGEIF dans Excel

Vous pouvez utiliser la formule suivante pour combiner les fonctions SOUS-TOTAL et MOYENNEIF dans Excel :

=AVERAGE(IF(SUBTOTAL(2,OFFSET(C2,ROW(C2:C11)-ROW(C2),0)),IF(B2:B11="Guard",C2:C11)))

Cette formule particulière vous permet de calculer la moyenne des valeurs dans la plage C2:C11 où la valeur correspondante dans la plage B2:B11 est égale à « Garde » même après que cette plage de cellules a été filtrée d’une manière ou d’une autre.

L’exemple suivant montre comment utiliser cette formule dans la pratique.

Exemple : Comment utiliser SUBTOTAL avec AVERAGEIF dans Excel

Supposons que nous disposions de l’ensemble de données suivant contenant des informations sur divers joueurs de basket-ball :

Filtrons ensuite les données pour afficher uniquement les lignes où se trouvent les joueurs de la conférence Ouest .

Pour ce faire, mettez en surbrillance la plage de cellules A1:C11 . Cliquez ensuite sur l’onglet Données le long du ruban supérieur et cliquez sur le bouton Filtrer .

Cliquez ensuite sur la flèche déroulante à côté de Conférence et assurez-vous que seule la case à côté de Ouest est cochée, puis cliquez sur OK :

Les données seront automatiquement filtrées pour afficher uniquement les lignes où la colonne Conférence est égale à Ouest :

Si nous essayons d’utiliser la fonction AVERAGEIF() pour calculer la moyenne des valeurs de la colonne Points où la valeur de la colonne Position est égale à « Guard », elle renverra en fait la moyenne des points pour les lignes égales à « Guard ».  » dans l’ensemble de données d’origine :

Au lieu de cela, nous devons utiliser la formule suivante :

=AVERAGE(IF(SUBTOTAL(2,OFFSET(C2,ROW(C2:C11)-ROW(C2),0)),IF(B2:B11="Guard",C2:C11)))

La capture d’écran suivante montre comment utiliser cette formule en pratique :

Cette formule renvoie la moyenne correcte de 21,25 .

Nous pouvons confirmer que cela est correct en calculant manuellement la moyenne des valeurs de points pour les lignes où la colonne Position est égale à « Garde » :

Moyenne des points des gardes : (12 + 28 + 30 + 15) / 4 = 21,25 .

Ressources additionnelles

Les didacticiels suivants expliquent comment effectuer d’autres opérations courantes dans Excel :

Comment supprimer des lignes filtrées dans Excel
Comment compter les lignes filtrées dans Excel
Comment additionner les lignes filtrées dans Excel