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
- CRÉER SON FILTRE AVANCE OU ÉLABORÉ
- FILTRER SUR PLACE DANS LA FEUILLE
- FILTRER VERS UNE AUTRE DESTINATION SUR LA FEUILLE
- EXTRACTION EN FOURCHETTE
- EXTRACTIONS AVEC D’AUTRES CRITÈRES
- EXTRACTION SUR UNE AUTRE FEUILLE DU CLASSEUR EXCEL
- NOMMER UNE PLAGE, UNE ZONE
- CHAMP CALCULE
- EN LIEN AVEC CE TUTO
PROCÉDURE DES FILTRES AVANCES
Il nous faut
- une plage à filtrer
- une zone de critère
- 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.
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.
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)
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.
Voici ce que l’on obtient :
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):
Mais 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)
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.
Voici le résultat :
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.
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.
Vous pouvez aussi écrire de cette manière d’autres critères :
*LL* : qui contient
Le « ? » remplace un caractère
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 = » »
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 = »= »
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.
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…
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.
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.
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.
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
Évidemment la formule renvoie FAUX puisque la cellule contient 2013.
Faites votre extraction sur place. Résultat :
Vous souhaitez 2014 OU 2015 placez vos formules ainsi :
Résultat que j’ai trié pour faciliter la lecture.
En 2014 et 2015 mais seulement pour le mois de février pour chacun d’eux. Toujours basé sur C2.
Résultat :
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 :
