Hoang-Quang Dao

Donner une énergie plus positive : vouloir proposer, partager, créer du lien… mon blog.

Additionner des valeurs dans une liste filtrée Excel

Posted by hoangquangdao sur 24 novembre 2010

Introduction

Le filtre est une fonctionnalité simple et puissante à utiliser. Si l’utilisation des filtres permet d’obtenir rapidement une gamme limitée des données, en obtenir un résultat d’enregistrement filtré est une autre affaire. Par exemple le calcul de la SOMME() d’une gamme de données filtrées. On peut avoir quelques surprises.

Voici un filtre d’un tableau de données qui représente une liste de contacts avec une colonne « nombre rdv » qui comptabilise le nombre de rendez-vous pris avec le contact. Les exemples montreront les différents résultats des sommes sur une liste filtrée. Le fonctionnement des filtres à proprement parler n’est pas abordé dans cet article, mais le un voici l’article pour en savoir plus sur ce sujet, lire « Utiliser les opérateurs ET et OU avec un filtre avancé Excel »

L’addition de valeurs filtrées

L’exemple ci-dessus montre ce qui se passe lorsque qu’on additionne les valeurs filtrées. On peut remarquer l’absence de lignes par la série de numéros de ligne tronquée causé par le filtre sélectionnant les lignes répondant au critère (ici le « code fonction 1 » de type « DG »).

SUMFILTER01

Somme-filtre-1

On constate facilement que le résultat n’est pas correct, la valeur est trop élevée, mais pourquoi? La fonction SOMME() est l’évaluation de toutes les valeurs de la gamme H9:H23, et pas seulement les valeurs filtrées, mais aussi les lignes cachées. La fonction SOMME() ne sait pas exclure des valeurs filtrées dans la gamme de référence.

La solution est beaucoup plus facile qu’on ne le pense ! Il suffit de cliquer ∑ et Excel entre automatiquement la fonction SOUS.TOTAL(), au lieu d’une fonction SOMME(). Cette fonction fait référence la liste entière, H9:H23, mais il évalue seulement les valeurs filtrées.

SUMFILTER02

Somme-filtre-2

A propos de la fonction SOUS.TOTAL()

Bien que la fonction SOUS.TOTAL() somme les références de la liste entière des valeurs dans la colonne H, il n’évalue pas seulement ceux de la liste filtrée. Le premier argument de la fonction, dans notre exemple la valeur 9, indique à Excel de faire la somme des valeurs de référence.

SUMFILTER03

Somme-filtre-3

Le tableau suivant répertorie les valeurs acceptables de cet argument :

Calcule les valeurs cachées Ignore les valeurs cachées Fonction
1 101 MOYENNE()
2 102 NB()
3 103 NBVAL()
4 104 MAX()
5 105 MIN()
6 106 PRODUIT()
7 107 ECARTYPE.STANDARD()
8 108 ECARTYPE.PEARSON()
9 109 SOMME()
10 110 VAR.S()
11 111 VAR.P.N()

On peut observer une bizarrerie pour la sous fonction SOMME de SOUS.TOTAL qui n’additionne que les valeurs filtrées que l’argument soit 9 ou 109. Il faut donc juste savoir que cela fonctionne comme ça pour la SOMME.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

 
%d blogueurs aiment cette page :