Home >> Logiciels >> Les filtres avancés / élaborés sous Excel

Les filtres avancés / élaborés sous Excel

Parfois, même s’ils sont bien perfectionnés, les filtres (automatiques) ne sont pas suffisants. Il nous faut des filtres avancés.

Exemple, si je veux toujours avec notre base Matériel extraire la filiale Marseille dans son intégralité, et toutes les autres filiales dont la somme est supérieure ou égale à 500 € je ne peux pas le réaliser en filtrant simplement.

PROCÉDURE DES FILTRES AVANCES

Il nous faut

  1. une plage à filtrer
  2. une zone de critère
  3. un filtrage sur place ou une zone de destination (pour afficher les résultats)

Personnellement je prête attention à toujours laisser 3 lignes entre chaque zone (Ici symbolisées par un cadre rose), maintenant ce n’est pas obligatoire mais j’ai vu que parfois on avait quelques erreurs avec les versions précédentes donc personnellement je continue.

elab 1

Pour une extraction spécifique on ne mettra que les titres de colonnes que l’on souhaite en titre d’extraction, mais les titres doivent être identiques à ceux de la base.

CRÉER SON FILTRE AVANCE OU ÉLABORÉ

Pour cela rendez-vous sur l’onglet Données, groupe Trier et Filtrer et choisir Avancés en ayant pris soin de cliquer dans sa base au préalable.

elab 2

Il récupère la plage que l’on peut aussi avoir nommée si on le souhaite (après l’avoir mis en surbrillance et Formule, Définir un nom)

elab 3

Maintenant grâce au petit bouton bleu blanc rouge on va définir la zone de critères sans laisser de ligne vierge en dessous.

elab 4

Voici ce que l’on obtient :

elab 5

FILTRER SUR PLACE DANS LA FEUILLE

Soit la plage de données, la plage de critère que j’explique juste après, et ici nous allons filtrer la liste sur place, ce qui signifie que nous aurons comme lors des filtres (automatiques) des lignes écrites en bleues et masquées. On peut aussi cocher extraction sans doublon, ici je sais que la base est petite et sans doublon donc non nécessaire.

Le filtre que vous voyez ici (impossible à faire avec les filtres simples):

elab 6Mais comment faut-il le lire ?

Je veux extraire toutes la filiale de Marseille sans autre condition, et toutes les filiales dont la somme et >= à 500 (€)

Placer des critères sur la même ligne équivaut à la fonction ET

Placer des critères en décalé sur plusieurs lignes équivaut à la fonction OU.

Voici le résultat du filtre avancé sur place :

On a bien tout Marseille quels que soient les montants.

On a toutes les autres filiales mais les montants sont >= à 500.

Vous pouvez vérifier avec la première capture d’écran de la base.

Voyons le résultat si les critères sont sur la même ligne (ce qui signifie seulement Marseille ET >=500)

elab 8

FILTRER VERS UNE AUTRE DESTINATION SUR LA FEUILLE

 

Lorsque l’on extrait vers une autre destination il suffit de copier la ligne de titre et la coller plus bas.

Si vous ne souhaitez que quelques données vous pouvez choisir les titres dont vous souhaitez faire apparaitre les données et dans l’ordre que vous souhaitez.

Ici je veux extraire uniquement la date d’achat, la filiale, le PHT, le type de matériel avec comme critère une date >O1/01/2014 et uniquement pour la filiale de Paris.

elab

Voici le résultat :

elab 10

Imaginez lorsque vous importez une base d’un quelconque logiciel qui contient ne serait–ce que 20 colonnes et que vous ne souhaitez que les noms, prénoms, adresses et villes, le gain de temps.

Vous avez remarqué que l’avantage de cette extraction partielle est la présentation dans un ordre différent pour les colonnes de celui de la base de données. À vous de les placer dans l’ordre souhaité avant l’extraction.

EXTRACTION EN FOURCHETTE

Si vous souhaitez faire un critère de fourchette comme entre deux dates il vous faut copier-coller cette étiquette de ligne deux fois et s’il faut 3 lignes entre la zone de critère et la base il faut aussi au moins une colonne si comme moi vous la placez à droite.

Voici sur une même impression écran, une fourchette de dates, l’extraction de toutes les étiquettes et les non retenus.

elab  11

EXTRACTIONS AVEC D’AUTRES CRITÈRES

On peut également poser un critère avec un * (astérisque) comme ici I*, E* ce qui nous donne uniquement les écrans et les imprimantes.

elab 11

Vous pouvez aussi écrire de cette manière d’autres critères :

*LL* : qui contient

elab 12

Le « ? » remplace un caractère

elab 13

Si la valeur (le texte inscrit dans les cellules) à filtrer contient déjà un des caractères « ? » ou « * », il faut les faire précéder du caractère « »

Voici deux autres exemples pour trouver des champs non vides ou vides.

Extraire les champs non vides : tapez = » »

elab 14

Regardez la barre de formule car on ne voit rien dans la zone de critère puisque l’on met une formule qui dit = champ vide.

Au contraire, extraire les champs vides : tapez dans la zone de critère = »= »

 elab 15

Quelques autres :

=             Égal

>             Supérieur

>             Inférieur

>=          Supérieur ou égal

<=          Inférieur ou égal

<>          Différent

<>*E     Ne se termine pas par E

*E           Contenant la lettre E

E             commençant par la lettre E

???e      Dont la quatrième lettre est E

PC ?       Tous les noms commençant par PC (FIXE et PORTABLE)

 

EXTRACTION SUR UNE AUTRE FEUILLE DU CLASSEUR EXCEL

 

Si vous souhaitez extraire les données directement sur une nouvelle feuille du classeur (ici la feuille s’appelle « autre feuille ») il faut mettre votre zone de critères sur cette « autre feuille », votre zone de destination aussi évidemment et surtout vous devez débuter votre filtre en étant dans une cellule de cette « autre feuille ».

Une fois la boite de dialogue devant vous, vous allez définir la plage sur la feuille « Base Matériel », vous définissez votre ligne de critères sur « autre feuille » ainsi que la zone de destination et OK.

elab 16

Le résultat apparait alors bien sur votre nouvelle feuille.

Il vous suffit de supprimer les lignes de critères si vous avez terminé afin d’avoir votre base seule ou au contraire vous en servir pour définir d’autres zones de destination sur cette feuille et extraire différentes données, de trier…

elab 17

NOMMER UNE PLAGE, UNE ZONE

Vous pouvez aussi nommer votre base par Formule définir un nom et au lieu d’aller la chercher sans cesse et coller ce nom.

elab 18

Puis lorsque vous lancez le filtre à partir de la seconde feuille cliquez sur le carré bleu blanc rouge, ensuite sur l’onglet formules puis utiliser dans formule et choisissez le nom de votre base.

elab 19

Faites de même pour la zone de critères et d’extraction. Attention toutefois les zones de critères peuvent changer donc vérifiez tout de même.

elab 20

CHAMP CALCULE

Imaginons que nous souhaitons extraire tous les achats de 2014.

On peut le faire effectivement avec une fourchette donc deux champs.

Par contre les filtres avancés nous permettent de créer un champ calculé.

Commencer par noter en titre le mot ANNEE puisque c’est sur l’année que l’on va travailler.

Ensuite dans la cellule en dessous mettez la formule :

= ANNEE(première cellule de la colonne qui contient les dates)=2014

Soit pour notre exemple

=ANNEE(C2)=2014

elab21

Évidemment la formule renvoie FAUX puisque la cellule contient 2013.

Faites votre extraction sur place. Résultat :

elab 22

Vous souhaitez 2014 OU 2015 placez vos formules ainsi :

elab23

Résultat que j’ai trié pour faciliter la lecture.

elab 24

En 2014 et 2015 mais seulement pour le mois de février pour chacun d’eux. Toujours basé sur C2.

elab 25

Résultat :

elab 26

Les cellules renvoient toujours faux dans cet exemple car en C2 la date est 08/04/2013 donc ni en février ni en 2014 ou 2015.

EN LIEN AVEC CE TUTO

Vous pouvez poursuivre l’approfondissement de vos analyses grâce :

About Patricia DEVISSCHERE

Ancienne Formatrice, je propose des astuces et tutoriels afin de faciliter et de mieux utiliser l'outil Bureautique. Former trainer, I offer tips and tutorials to facilitate and better use of Office tools.

A lire également

Comment vérifier et tester vos ISO et clé USB bootable ?

Nous allons voir dans ce billet comment tester et vérifier un ISO bootable ou une …