Notre magasin

Rue Albert 1er, 7

6810 Pin - Chiny

(/fax: 00 32 (0)61/32.00.15

Le cours EXCEL d'YBET Informatique

Comptabilité CIEL 2009, la référence pour les entreprises

FORMATIONS

Le MAGASIN YBET

PRODUITS et SERVICES

Formation INTERNET

Activités et présentation

Tutorial Access

Plan d'accès à Chiny

14. EXCEL: Filtre élaboré

1. Introduction - 2. Préparation d'un filtre élaboré - 3. Filtrages élaborés simple - 4. Filtrage multicritère (ligne) 5. Caractères de remplacement - 6. Champ vide, champ non vide - 7. Multicritères (colonnes) - 8. Filtrage élaboré: critère calculé - 9. Fonction Sous-total() - 10. Comparaison des méthodes de filtrage Excel.

Dans un précédent chapitre, nous avons vus le tri et le filtrage d'une liste sous Excel et les sous-totaux. Chaque possibilité a ses avantages et ses défauts lorsque l'on recherche des enregistrements particuliers:

1. le tri ne permet que de trier les enregistrements, mais ne permet de faire directement des calculs comme critère de tris.

2. le filtre automatique permet de filtrer les enregistrements dans chaque colonne. Par contre, le filtrage sur une même colonne est limité à 2 critères (suivant les fonctions logiques ET, OU). Même si on peux effectivement faire un calcul (formule éventuellement) sur les zones filtrées, la limitation des critères de tri rend l'utilisation parfois compliquée.
3. Les Sous-totaux permettent des calculs mais suivant une seule formule (sauf en utilisant manuellement la fonction sous-total). De plus, le filtrage suivant des critères spécifiques nécessite de repasser par un tri et de recréer le sous-total. De plus, les calculs intermédiaires

La solution finale reste les filtres élaborées. Même si ce n'est pas la solution parfaite (on pourrait également utiliser les listes Excel), le filtrage va être beaucoup plus "élaboré" mais plus complexe puisque le nombre de critères est infini. La solution idéale passe par un mélange de ces fonctions, même si l'utilisation d'Access permettrait une solution plus facile.

2. Préparation d'un filtre élaboré.

Encodons les données suivantes:

Cette petite liste de produits va nous permettre de tester les listes élaborées sans trop de problèmes.

Insérons quelques lignes au-dessus de cette liste, et copions les en-têtes de colonnes sur la première ligne. Une remarque concernant la position des listes de critères. La majorité des cours Excel imposent de positionner cette liste de tris sur la première ligne. Ce n'est pas obligatoire, mais conseillé. Ceci facilite la mise en place des filtres.

Les critères de filtres se mettront juste en dessous de notre première ligne.

3. Filtres élaborés simples

Commençons par un petit filtre élaboré tout simple. Nous souhaitons uniquement les produits de la famille ordinateur compatible. Commençons par copier PC compatible dans la cellule B2 (en dessous de notre titre de critère). Le plus simple est d'utiliser le copier-coller.

Dans le menu Données, sélectionnons Filtrer ->filtre élaboré.

La fenêtre de critère élaboré apparaît:

• Filtrer la liste sur place filtre directement dans la zone de cellules Excel où sont placées les données de départ. Par contre "Copier vers un autre emplacement" permet de créer la liste triée à un autre endroit de la même feuille
• Plages: représente la zone où se trouvent les données qui doivent être filtrées (ici en vert).
• Zone de critères désigne la zones ou vous avez inséré vos critères de filtrage (ici en bleu).
• Extraction sans doublon permet de ne pas afficher les données en doubles.

Essayons pour vérifier notre résultat.

Remarquez que les lignes de résultats du filtre élaboré sont notées en bleu. Nous venons de faire un premier filtre, bien que le filtrage automatique aurait donné le même résultat.

Pour réafficher l'ensemble de la liste, utilisez la commande "Afficher tout" du menu Données -> Filtrer, même si ce n'est pas nécessaire pour créer un nouveau filtre.

Comme deuxième filtrage, nous recherchons les produits informatiques de la famille ordinateur compatible ET dont le stock est de 10. Nous allons tout simplement compléter le filtre en rajoutant 10 dans la cellule C2 (en dessous de l'en-tête "quantité" dans la zone de critère). De nouveau, exécutons la même commande de filtrage en choisissant les mêmes zones.

 Le filtre affiche les composants de la famille "Ordinateur Compatible" ET dont la quantité = 10

Supposons que nous souhaitions afficher les ordinateurs compatibles dont la quantité en stock est >5. Nous allons modifier le critère dans la cellule C2 par >5. Vous pouvez essayer les mêmes commandes avec comme critères: <5, <>10, ...

4. Filtre élaboré multicritères (lignes)

Jusqu'ici, nous avons utilisé 1 seul critère de filtre par colonne. Nous allons maintenant utiliser plusieurs critères simultanés. Supposons que nous souhaitons afficher les PC à base de Sempron 2600 et 2800. Reprenons notre zone de critères. Dans la cellule A2, tapons Sempron 2600, dans la cellule A3, tapons Sempron 2800. Reprenons notre commande Filtres élaborés, gardons les mêmes cellules comme plage, mais sélectionnons maintenant les cellules A1 à A3. Le filtrage fonctionne de la même manière sauf que nous affichons les lignes dont le produit est Sempron 2600 et Sempron 2800.

Une petite remarque, nous n'avons pas dans ce cas sélectionné toutes les colonnes de critères. Ceci n'est pas nécessaire puisque le filtre ne s'applique qu'à la colonne "Produits".

Pouvons nous mélanger des critères qui n'on rien à voire entre eux? Oui.

Supposons que nous souhaitons afficher les Sempron 2600 et les produits dont le stock est supérieur à 5. Le critère devient:

Remarquez que les 2 critères sont sur 2 lignes différentes du filtrage élaboré pour utiliser entre ces 2 lignes la fonction logique OU.

5. Caractères de remplacement.

Pour créer votre filtres élaborés, vous pouvez également utiliser des caractères génériques. Ils sont les mêmes qu'en DOS:

• * remplace un ensemble de caractères (l'* en fin n'est pas nécessaire).
• ? remplace 1 caractère quelconque dans une zone de texte
• Attention, le filtre élaboré fait la distinction entre les majuscules et les minuscules.

Supposons que nous souhaitons visualise tous les produits commençant par SEM. Le filtre devient

Comme autres exemples de caractères génériques:

• *e: affiche tous les enregistrements contenant la lettre e
• e: filtre tous les enregistrements commençant par la lettre e
• ??e tous les enregistrements dont la troisième lettre est e
• Sempron 2?00: affiche tous les enregistrements Sempron 2600, Sempron 2800, ...

6. Champ vide, champ non vide.

Pour filtrer les champs dont le contenu est vide, tapez dans la zone de critère ="="

Pour filtrer les enregistrements non vides: tapez =""

Remarque: Excel n'affiche pas ces caractères dans les cellules lorsque vous avez tapé la valeur. Par contre, la barre de formule affiche la formule correcte.

7. Filtre élaboré multicritère (colonnes)

Commençons par rajouter quelques colonnes dans notre tableau de données de départ: Date de création du code. Cette nouvelle colonne va nous permettre de définir plusieurs critères sur un même champ dans cet exercice.

Supposons que nous souhaitons filtrer les produits inscrits entre le 01/01/2002 et le 31/12/2004

Nous allons tout simplement mettre 2 colonnes avec le même champ que le champ à filtrer et mettre sur la même ligne (fonction logique ET) les 2 critères:

Ces critères peuvent être cumulés en ligne (OU) ou en colonne (ET).

8. Critères calculés.

C'est peut-être le réel avantage d'un filtre élaboré, l'utilisation de critères calculés, même si son utilisation pose souvent quelques problèmes.

8.1 Filtres élaborés avec des calculs sur des dates

Supposons que nous souhaitons filtrer sur les produits datant de 2005. Nous pourrions faire un filtre entre 2 dates comme ci-dessus mais nous allons utiliser les fonctions de dates d'Excel pour le filtrage. La fonction qui récupère l'année est =annee(numero).

Dans une des cellules de la première ligne, tapez année. Dans la cellule en dessous, tapez: =annee(F6)=valeur où F6 est la première cellule de la colonne de la zones de liste qui contient les données dates. Valeur est la valeur recherchée. La réponse doit toujours être une valeur logique  (Vrai ou faux).

Créez maintenant le filtre élaboré suivant, Excel n'affiche que les produits inscrits en 2005.

Nous pouvons rajouter des critères. Nous souhaitons les produits sortis en juillet 2005. Nous allons rajouter un autre critère: celui du mois. Comme nous souhaitons mois = 07 ET Année = 2005, les 2 critères du filtrage doivent être sur la même ligne. Dans la cellule D1, tapons le mot mois. Dans la cellule D2, =MOIS(F6)=7

8.2. Filtres élaborés avec des moyennes

L'utilisation de critères calculés dans les filtres élaborés va nous permettre également de faire des filtrages sur des calculs. Prenons par exemple une moyenne: nous souhaitons afficher tous les produits dont le prix de vente est supérieur au prix moyen.  Commençons par créer notre critère.

Dans la cellule E1, tapons le terme valeur. Dans la cellule E2, tapons la fonction logique =E7>moyenne(E7:E11). Créez ensuite un filtre élaboré avec comme zone de critère les cellules E1:E2. Le filtre n'affiche plus que les produits dont la valeur est supérieure à la moyenne

Quasiment toutes les filtres calculés sont possibles, tant que le critère est une condition. Comme autres exemples:

Remarquez que pour la comparaison, la référence de la cellule contenant l'année est sur une autre feuille. Sinon, le filtre élaboré n'affiche rien!

8.3. Filtre élaboré avec de simple calcul

Nous pouvons également utiliser les 4 opérations comme filtrage.

=C6*E6>1000 est permis. Il vérifie si le stock d'un produit est supérieur à 1000 euro.

9. Fonction Sous-total()

Nous venons de voire comment créer des filtres, mais est-il possible de créer des fonctions sur les résultats obtenus suivant une colonne?

Oui et non. En premier, si vous insérez la fonction en bas de votre liste avant de faire le filtre, la fonction ne tient pas compte du filtre. Dans l'exemple par exemple ci-dessous, la somme des prix???

Par contre, vous pouvez insérer la fonction avec le filtre en fonction (pas toutes). Si vous cliquez sur le bouton de fonction , Excel va modifier la fonction en sous-total. Le premier argument (un chiffre de 1 à 11) reprend la fonction standard effective.

	no_fonction	Fonction		Excel 2003 uniquement
	1	MOYENNE		101
	2	NB	(nombre de cellule avec des chiffres)	102
	3	NBVAL	(nombre de cellules avec des valeurs (textes, chiffres, ...)	103
	4	MAX		104
	5	MIN		105
	6	PRODUIT		106
	7	ECARTYPE		107
Remarque: il est souvent nécessaire de corriger la plage de cellule de la fonction manuellement, sous peine d'oublier des cellules.	8	ECARTYPEP		108
	9	SOMME		109
	10	VAR		110
	11	VAR.P		111

Ceci n'est donc pas valable pour les fonctions personnelles, ... Lorsque le filtre est enlevé, le sous-total se comporte comme la fonction standard.

La version Excel 2003 a modifié la fonction sous.total() en rajoutant des options. Les numéros de fonctions entre 1 et 11 inclut les valeurs masquées (comme les autres versions), les numéros de fonctions entre 101 et 111 (nouvelle fonctionnalité) ignore les lignes masquées. Ces valeurs masquées sont liées à la commande ligne du menu Format: Masquer qui permet de ne pas afficher des lignes de valeurs.

10. Comparaison des méthodes de filtrages.

L'utilisation des filtres élaborés permet de multiplier le nombre de critères par rapport aux filtres automatiques. Ce type de filtrage permet également de faire des critères calculés. Par contre, l'utilisation de fonctions Excel dans ces résultats passe par la commande sous-total(), limitée à quelques opérations possibles (éventuellement utiliser la commande sous-totaux du menu Donnée). Par contre, les résultats intermédiaires sont supprimés lorsque vous modifiez le filtre.

L'utilisation de la commande sous-totaux du menu Données permet de faire plus d'opérations, mais la fonction doit rester la même dans l'ensemble des groupes de données. Les résultats des calculs disparaissent avec la suppression de la commande sous-totaux.

La commande LISTE, apparue avec Excel 2003 permet de faire les sous-totaux de manière plus facile, selon les 11 fonctions définies, différentes d'une colonne à l'autre si nécessaire. Ici aussi les calculs disparaissent avec la suppression de la liste ou restent si vous convertissez en plage avec les calculs affichés. Dans un sens, cette nouvelle commande n'est que l'adaptation des commandes des anciennes versions.

Dans tous les cas, EXCEL montre ses limites lorsqu'il s'agit de faire des calculs dans des listes de données importantes. L'utilisation d'une base de donnée (Etats Access par exemple) est largement préférable.

Une dernière remarque: si vous souhaitez garder les calculs sur des filtres ou des sous-totaux particuliers, vous pouvez sélectionner la zone souhaitée et copier les valeurs. Les calculs avec fonctions Excel standards sont transposées directement. Par contre, dans un filtre élaboré, si vous copiez des plages, les résultats des fonctions sous.total() sont transférées en valeurs.

YBET Informatique: formations informatiques débutants ou avancés
dans notre salle de cours à Chiny ou en entreprise: Windows, office et gestion