[Toutes versions] Tutoriel - Les filtres avancés ou élaborés d'excel

[Philippe Tulliez]

Bonjour,
J'ai le plaisir de vous annoncer mon nouveau tutoriel traitant des filtres avancés ou élaborés

Outil puissant et finalement très peu connu par les utilisateurs le filtre élaboré permet de filtrer des données avec plus de possibilités que le filtre simple dont on atteint très vite ses limites.

En plus de filtrer les données sur place, il permet l'exportation de celles-ci vers une autre feuille ou un autre classeur. Son exploitation en VBA offre de belles perspectives de développement.

J'espère que la lecture de ce tutoriel vous permettra de découvrir cet outil ou d'en apprendre plus sur ses possibilités.

[Pierre Fauconnier]

Bonsoir Philippe,

Un nouveau tuto sur Excel, c'est toujours une bonne chose.

Le filtre avancé, que ce soit pour un filtre sur place ou pour une extraction, est effectivement un outil un peu méconnu, peut-être parce que sa mise en place n'est pas intuitive.

Bravo pour ce tutoriel qui permet de le prendre en mains pas à pas, et qui aborde en plus l'aspect VBA de l'outil. Cela conforte l'idée émise dans ma signature "VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA..."...

Je suis juste très impatient de découvrir ton prochain sujet.

Amitiés

[Philippe Tulliez]

Bonjour Pierre,
Merci pour ton commentaire qui fait chaud au coeur et merci encore pour ton aide de ces derniers jours.
Les autres tutos vont suivre, certainement un sur les fonctions base de données avant la fin de l'année.
Amitiés

[Zodette]

Je donne de la formation et, en effet, le filtre avancé est méconnu. Il est un outil incontournable pour ceux qui manipulent des données.

Merci, j'ai pu apprendre comment identifier les vides ou non vides ainsi que de préciser la casse.

Encore MERCI!

[Philippe Tulliez]

Bonjour Odette,
Merci pour ce message qui me fait très plaisir.
Bonne journée

[Philippe JOCHMANS]

Salut Philippe

Je suis loin d'être un pro d'Excel, c'est pourquoi ce tuto m'a apprit encore pas mal de choses.

J'ai trop tendance avec Excel à ouvrir l'éditeur VB et à faire une petite routine pour avoir ce que je veux ou à tout basculer sur Access pour faire une requête. C'est pourquoi ce genre de tuto est très utile.



Philippe

[Philippe Tulliez]

Bonjour Philippe,
Merci pour ce retour.
J'ajouterais que je suis dans le même cas que toi par rapport à Access où je réalise sur Excel ce que je pourrais faire certainement plus facilement avec Access.
Bonne journée
Philippe

[Ormonth]

Salut Philippe,

Même constat en tant que formateur, le filtre avancé est méconnu et quand il l'est c'est généralement sur ses possibilités les plus basiques.

Ce tuto très complet et très clair, toutes versions qui plus est, est donc bienvenu

Didier

[Philippe Tulliez]

Bonjour Didier,
Merci pour ton appréciation qui donne envie de continuer à partager nos connaissances.
Philippe

[Phanloga]

Bon sang ! Mais c'est bien sûr !!!

J'utilisais cet outil, mais je ne connaissais pas la moitié du quart du millième des possibilités offertes !!

Un grand merci.

[78chris]

Bonjour

Excellent tuto, clair et complet. Félicitations.

Pour ma part, dans les calculs je préfère utiliser le nom du champ comme dans une BD :

Code :

=ANNEE(Naissance) me parait plus logique que =ANNEE(C6)

mais cela a l'inconvénient de renvoyer une valeur d'erreur plutôt que VRAI ou FAUX. Cela peut gêner certains même si cela ne change rien pour la bonne marche du filtre.

[Zodette]

Voici,
Je me sers du filtre avancé pour extraire les vins à commander:

Colonne H "En stock"
Colonne G "Stock minimum"

Alors mon critère est: =H5<G5

Le filtre avancé est très utile pour comparer la valeur de 2 champs dans un enregistrement.

Voir fichier

Odette

[Golork]

Bonjour, j'ai actuellement développé une application qui effectue une série de filtres automatique (en VBA) sur un gros fichier Excel de plus de 190 000 lignes (export client) et en calcul la somme ou le nombre d'une colonne définie.

La routine me permet de créer 5 tableaux, de lignes et colonnes fixes, mais voilà le temps de traitement est assez long : ~300 sec pour les 5 tableaux (je ne compte pas ici l'ouverture de l'export client).
J'ai bien contrôlé dans mon code d'éviter les ".Select", grouper les instructions avec des "With", désactiver le calcul auto et la mise à jour de l'écran.

Ma question : est-ce que le filtre élaboré me permettrait de gagner en performance par rapport au filtre automatique ?

Merci pour ton tutos que je regarderais quand j'aurais un peu plus de temps

[Philippe Tulliez]

Bonjour Philippe,

Envoyé par Phanloga

Bon sang ! Mais c'est bien sûr !!!
J'utilisais cet outil, mais je ne connaissais pas la moitié du quart du millième des possibilités offertes !!
Un grand merci.

et merci pour ton appréciation.

[Philippe Tulliez]

Bonjour Chris,

Envoyé par 78chris

Bonjour
Excellent tuto, clair et complet. Félicitations.

Merci pour ton appréciation.

Pour ma part, dans les calculs je préfère utiliser le nom du champ comme dans une BD :

Code :

=ANNEE(Naissance) me parait plus logique que =ANNEE(C6)

mais cela a l'inconvénient de renvoyer une valeur d'erreur plutôt que VRAI ou FAUX. Cela peut gêner certains même si cela ne change rien pour la bonne marche du filtre.

Tout dépend à quelles cellules fait référence le nom Naissance et si cette référence est relative ou absolue (Cependant après tests, je n'ai pas vraiment de résultats concluants).
J'utilise abondamment les références nommées et bien que j'utilise les références nommées pour les zones Data, Critères et Export je n'ai pas voulu ajouté de la complexité à ce tutoriel en y insérant des noms se trouvant à l'intérieur de la zone Data.
Philippe

[Philippe Tulliez]

Bonjour Odette,

Envoyé par Zodette

Voici,
Je me sers du filtre avancé pour extraire les vins à commander:
Colonne H "En stock"
Colonne G "Stock minimum"
Alors mon critère est: =H5<G5
Le filtre avancé est très utile pour comparer la valeur de 2 champs dans un enregistrement.
Voir fichier
Odette

Merci de nous faire partager par des exemples, les possibilités des filtres élaborés .
Philippe

[Philippe Tulliez]

Bonjour Fabien,

Envoyé par Golork

Bonjour, j'ai actuellement développé une application qui effectue une série de filtres automatique (en VBA) sur un gros fichier Excel de plus de 190 000 lignes (export client) et en calcul la somme ou le nombre d'une colonne définie.
La routine me permet de créer 5 tableaux, de lignes et colonnes fixes, mais voilà le temps de traitement est assez long : ~300 sec pour les 5 tableaux (je ne compte pas ici l'ouverture de l'export client).
J'ai bien contrôlé dans mon code d'éviter les ".Select", grouper les instructions avec des "With", désactiver le calcul auto et la mise à jour de l'écran.
Ma question : est-ce que le filtre élaboré me permettrait de gagner en performance par rapport au filtre automatique ?
Merci pour ton tutos que je regarderais quand j'aurais un peu plus de temps

Je n'ai pas poussé les tests jusqu'à ce nombre de lignes mais la fonction VBA que je donne en exemple dans le fichier qui accompagne le tutoriel te permettra de le vérifier facilement et j'espère que tu nous feras parvenir en retour le temps que cette fonction aura mis pour exporter les données.
La fonction

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
' Module contenant les procédures utilisées dans le classeur illustrant le tutoriel Filtres avancés d'excel
Function ExportByFilter(znData As Range, znCriteria As Range, Optional znExport As Range) As Long
 ' Author : Philippe Tulliez http://philippe.tulliez.be
 ' Date : 01/11/2012
 ' Version : 1.0
 ' Procédure d'exportation basée sur le filtre élaboré
 ' Valeur renvoyée : Nombre d'enregistrements exporté
 ' znData     ' Table de données
 ' znCriteria ' Zone des critères
 ' [znExport]   ' Zone d'exportation (si vide Exporte tout, en créant une feuille)
 If znExport Is Nothing Then ' Création de la feuille d'export et coloration en rouge
  Worksheets.Add before:=Sheets(1)
  With Worksheets(1): ActiveCell = .Range("A1"): .Tab.Color = vbRed: End With
  Set znExport = ActiveCell
 End If
 znData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=znCriteria, CopyToRange:=znExport
 ExportByFilter = znExport.CurrentRegion.Rows.Count - 1
End Function

[EDIT]
Je viens d'effectuer un test sur une zone data de 3 colonnes et 272 769 lignes.
Le nombre exporté (répondant aux critères) est de 3 732 lignes en créant une feuille pour l'exportation.
Temps moins d'une seconde.

[78chris]

Bonjour

Envoyé par Philippe Tulliez

Bonjour Chris,

Tout dépend à quelles cellules fait référence le nom Naissance et si cette référence est relative ou absolue (Cependant après tests, je n'ai pas vraiment de résultats concluants).
J'utilise abondamment les références nommées et bien que j'utilise les références nommées pour les zones Data, Critères et Export je n'ai pas voulu ajouté de la complexité à ce tutoriel en y insérant des noms se trouvant à l'intérieur de la zone Data.
Philippe

C'est juste le titre de la colonne concernée (j'ai repris ton exemple) : rien de nommé en plus.

[Philippe Tulliez]

Bonjour Chris,
Si tu as un classeur exemple, cela m'intéresse parce-que je viens de faire un test et je n'arrive pas à reproduire un résultat correct en faisant référence à l'étiquette de colonne dans un critère calculé.
Peut-être as-tu cette option qui permet de faire référence à l'étiquette de colonne dans une formule ?
Je ne retrouve plus où se trouve cette option en 2010 mais en 2003 c'est
OUTILS/OPTIONS-[CALCUL] cocher Accepter les étiquettes dans les formules

[78chris]

Bonjour Philippe

Exemple ci-joint.

Je ne modifie pas d'option : cela marche nativement.
Par contre si le titre contient un espace ou un retour (alt entrée) cela ne marche pas...