Statistiques sur les filtres automatiques




Comment appliquer des fonctions statistiques aux résultats affichés par un filtre automatique ? Effectivement les fonctions classiques (SOMME(), MOYENNE(), MAX(), MIN()…) ne conviennent pas dans un tel cas puisqu'elles retournent un résultat sur l'ensemble des données et pas sur le résultat du filtre.
La solution passe par la maîtrise de la fonction =SOUS.TOTAL()

La syntaxe de la fonction sous.total() est :

=SOUS.TOTAL( fonction ; plage )

fonction désigne alors la fonction statistique que vous souhaitez appliquer aux résultats de votre filtre, elle est indiquée par un numéro

 compris entre 1 et 11, comme l'indique le tableau ci-dessous.
plage désigne l'adresse contenant les données à analyser.

Ainsi dans la seconde image ci contre :
La fonction :

=SOUS.TOTAL( 9 ; b2:b11 )

saisie en B13, Nous permettra d'obtenir la somme des prix uniquement pour la région "Aquitaine"


 Moyenne1 101
 Nb2 102
 Nbval3 103
 Max 4 104
 Min 5 105
 Produit 6 106
 Ecartype7 107
 EcartypeP 8 108
 Somme 9 109
 Var 10 110
 Var.P11 111

Si vous indiquez le code sur 3 chiffres par exemple 109 pour somme et non 9 alors Excel comprendra qu'il doit ignorer d'éventuelles lignes masquées dans votre liste pour la réalisation de la statistique.



Compter et compter distinctement !




Lorsque vous travaillez sur une liste de données Excel, comment connaître le nombre de valeurs distinctes contenu dans un champ ?
Par exemple dans le champ "Pays" ci-joint à combien de pays différents fait on référence ?

La fonction de comptage =NBVAL( plage ) peut vous donner le nombre total de lignes (ici 10), pour la formule =NBVAL(B2:B11).
La fonction =NB.SI( plage ; critère ) peut vous retourner le nombre de fois où un pays est présent, (ici 4) pour l'expression = NB.SI(B2:B11;"Espagne"), c'est justement à partir de cette fonction que se trouve une solution possible.

La formule  =SOMME( 1 / NB.SI(B2:B11;B2:B11) ) répond à cette question, la fonction =NB.SI( ) comptant ici les valeurs de sa propre matrice. Attention toutefois la valeur retournée étant une matrice, vous devez valider la formule par SHIFT + CTRL + Entrée.
On voit alors qu'il est fait référence à 5 pays différents dans ce champ, et la formule étant matriciel elle apparaît entre { }.
Merci à Michel, un anonyme internaute pour m'avoir suggéré la réponse à ce problème, sur laquelle je butais depuis longtemps.



top