Hoang-Quang Dao

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

Posts Tagged ‘Filtre’

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.

Publicités

Posted in Excel, Office | Tagué: , , | Leave a Comment »

Utiliser les opérateurs ET et OU avec un filtre avancé Excel

Posted by hoangquangdao sur 23 novembre 2010

Définition

Un filtrage de données est un affichage d’un sous-ensemble de données. C’est une tâche de routine pour les nombreux utilisateurs d’Excel.
Un filtre automatique permet de limiter les données affichées, mais il est justement limité car il dépend de données réelles. Le filtrage avancé d’Excel est une fonction qui nécessite un peu de configuration, mais est plus souple et plus puissant qu’un simple filtre automatique. Non seulement on peut utiliser une expression pour apparier des enregistrements, mais aussi en combiner en utilisant les opérateurs ET et OU, c’est ce que cet article va vous montrer.

La fonctionnalité de filtre avancé est composée de trois éléments :

  • Une plage de données où appliquer le filtre
  • Une plage de critères, où on spécifier les critères comme une formule
  • Enfin l’extraction de la gamme de données qui satisfait aux critères

Le filtre simple automatique

Avant d’entrer dans un exemple plus complexe, voici un exemple simple de filtre automatique en utilisant un ensemble de données d’un fichier de contacts client.

FILTRE01

Liste de contacts

Pour appliquer un filtre automatique

1. Sélectionnez les en-têtes de la première à la dernière colonne des données à filtre, ici A1:H1

FILTRE02

Donnees-Filtrer

2. Choisir alors Filtre automatique dans le menu Données dans Excel 2003, dans Excel 2007 et 2010, cliquez sur le menu Données du bandeau, puis cliquez sur Filtrer dans le groupe Trier et filtrer.

FILTRE03

Filtre simple critère

FILTRE04

Filtre simple résultat

Excel affiche une flèche de liste déroulante pour chaque colonne dans la sélection. En utilisant cette fonctionnalité, on peut effectuer des tâches simples de filtrage, tels que les contacts qui n’ont pas de « code fonction 2 ». C’est rapide et facile, mais parfois insuffisant. (Pour supprimer un filtre, il suffit de choisir « tous » de la même liste ou dans Excel 2010, « effacer la liste des filtres »)

Le filtre avancé avec l’expression ET

Maintenant un exemple de filtre avancé avec une expression, en affichant tous les contacts ayant un « code fonction 1 » de type « DG » ET un « code fonction 2 » de type « Développement ». Cela nécessite un filtrage à deux exigences – deux critères – et qui doivent être satisfaite toutes les deux. Un filtre automatique peut encore se faire, mais essayons donc un filtre avancé.

La plage de critères, dans ce cas, ne nécessite que deux colonnes: le « code fonction 1 » et le « code fonction 2 ». Pour une question d’ergonomie et de lecture, il est recommandé de recopier les entêtes et d’ajouter autant de ligne que de critères pour former une zone de plage de critères au-dessus des données réelles. Ce placement est efficace et facilement accessible.

Ensuite, il faut indiquer critères de filtrage. Excel intègre l’interprétation des formules également. Dans ce cas, les deux expressions sont de simples comparaisons. Les deux critères sont dans la même ligne (ligne 2). En plaçant les deux expressions dans la même rangée, Excel sait va appliquer l’opérateur implicite de combinaison ET sur les expressions.

FILTRE06

Filtre avancé

La gamme d’extraction est facultative. Pour copier les données vers un autre emplacement, il faut spécifier les colonnes que l’on veut extraire. Tout ce qui reste à faire maintenant est d’appliquer le filtre comme suit:

1. Cliquez sur n’importe quelle cellule dans la plage de données.

2. Cliquez sur le menu Données, puis cliquez sur Filtre | Filtre avancé. Dans Excel 2007 et 2010, cliquez sur l’onglet Données, puis cliquez sur Filtre dans le groupe Trier et filtrer.

3. Garder le réglage par défaut, filtrer la liste en place.

4. Excel sélectionne automatiquement la plage de données à filtrer.

5. Il n’y a qu’à spécifiez la plage de critères, A1:H2. Vous avez seulement besoin d’identifier les entêtes de colonne et la rangée des critères ou des lignes.

FILTRE07

Filtre avancé ET plage criètres

6. Cliquer sur OK.

FILTRE08

Filtre avancé ET résultat

Trois contacts sont de « code fonction 1 » de type « DG » ET de « code fonction 2 » de type « Développement. Pour retirer le filtre, cliquez sur le menu Données, puis cliquez sur Filtre | Afficher tous les, dans Excel 2007 et 2010 cliquez sur le menu Données, puis cliquez sur Filtre | Effacer.

Le filtre avancé avec l’expression OU

Pour le filtre avancé avec une expression OU, il faut placer les critères dans des rangées séparées. Ainsi afficher tous les contacts ayant un « code fonction 1 » de type « DG » OU un « code fonction 2 » de type « Développement ».

Après avoir réglé la plage de critères en déplaçant l’une des expressions dans une autre rangée, on applique de nouveau le filtre comme suit:

1. Cliquez sur n’importe quelle cellule dans la plage de données.

2. Cliquez sur le menu Données, puis cliquez sur Filtre | Filtre avancé. Dans Excel 2007 et 2010, cliquez sur l’onglet Données, puis cliquez sur Filtre dans le groupe Trier et filtrer.

3. Garder le réglage par défaut, filtrer la liste en place.

4. Excel sélectionne automatiquement la plage de données à filtrer.

5. Il n’y a qu’à spécifiez la plage de critères, A1:H3. Vous avez seulement besoin d’identifier les entêtes de colonne et la rangée des critères c’est-à-dire les 2 lignes en plus.

FILTRE09

Filtre avancé OU plage critères

6. Cliquer sur OK.

FILTRE10

Filre avancé OU résultat

De plus nombreux contacts répondent désormais à l’un ou l’autre des critères.

On peut utiliser un filtre avancé en une seule formule, mais en utilisant les opérateurs implicite ET et OU, cela ouvre la porte à des filtres très complexes, mais puissants. Il suffit de faire attention à ce queles critères soient placer dans au bon endroit.

Posted in Excel, Office | Tagué: , | Leave a Comment »