Discussion :

[Philippe Tulliez]

Bonjour Chris,
Effectivement j'avais le même message d'erreur lors de mes tests (#NOM?) ce qui est normal comme message d'erreur mais curieux pour le résultat positif qu'il engendre. A creuser.

[fredo38]

Bravo et merci pour ce tutoriel qui m'a permis de découvrir cette fonctionnalité de façon rapide et claire

[Philippe Tulliez]

Bonjour Frédéric,
Merci pour tes congratulations.
Philippe

Bravo et merci pour ce tutoriel qui m'a permis de découvrir cette fonctionnalité de façon rapide et claire

[Darkzinus]

Merci beaucoup pour ce tutoriel ! Autant le filtre de "base" est connu autant ce genre de filtre élaboré est assez bluffant en terme de possibilités !

[issoram]

Merci Philippe pour ce tutoriel.

Une fonctionnalité d'Excel que je ne connaissais pas et qui m'a l'air fort pratique.
Je teste tout ça de suite.

Suite du message: Je viens de finir le tutoriel, c'est super clair, merci encore et félicitations!
En somme c'est un équivalent du SELECT FROM WHERE Sql sur une table de données => super pratique.

Par contre en termes de performance, ça donne quoi sur des grosses tables avec filtres complexes?

[Philippe Tulliez]

Bonjour Darkzimus,

Merci beaucoup pour ce tutoriel ! Autant le filtre de "base" est connu autant ce genre de filtre élaboré est assez bluffant en terme de possibilités !

Merci pour ton appréciation qui me fait extrêmement plaisir.
Philippe

[Philippe Tulliez]

Bonjour Zeco,

Merci Philippe pour ce tutoriel.
Une fonctionnalité d'Excel que je ne connaissais pas et qui m'a l'air fort pratique.
Je teste tout ça de suite.
Suite du message: Je viens de finir le tutoriel, c'est super clair, merci encore et félicitations!
En somme c'est un équivalent du SELECT FROM WHERE Sql sur une table de données => super pratique.
Par contre en termes de performance, ça donne quoi sur des grosses tables avec filtres complexes?

Merci aussi pour ton gentil commentaire qui donne envie de continuer à partager ses connaissances.
Pour ce qui est des performances, je viens à l'instant de faire un test sur une liste de données de 65000 lignes et 4 colonnes ($A$1:$D$65001) avec un filtre portant sur une zone de critères de 2 lignes et 3 colonnes ($G$1:$I$3).
Sexe|Crit |Véhicule
...F...|FAUX|
.......|........|Opel
Le critère calculé est =ANNEE(C2)=1966

Test effectué à l'aide de la fonction qui est présente dans le classeur qui accompagne le tutoriel.
Dans cet exemple 10 038 lignes sont exportées avec création de la feuille qui reçoit les lignes exportées. Temps 2 secondes 32

[issoram]

Merci pour la réponse,

Si j'ai un peu de temps je ferais des comparatifs de performances vs l'utilisation de tableaux en VBA.

Et merci encore de partager (de ton temps et tes connaissances) et de contribuer à enrichir la bibliothèque des tutoriels.

[Didier Gonard]

Bonjour issoram;

Si j'ai un peu de temps je ferais des comparatifs de performances vs l'utilisation de tableaux en VBA.

Au niveau du contexte, via Excel, on passe en langage natif, les similitudes entre ll’organisation d'une feuille et les variables tableaux, plus le fait qu'on puisse dans les deux sens injecter l'un dans l'autre direct, laisse à penser que c'est, en partie, le mode de fonctionnement d'Excel.
Donc si on pilote Excel sur des fonctions natives via VBA, on doit gagner par rapport à monter une plage dans un tableau, exécuter du code et redescendre.

mes test pour ce fil vont dans le même sens que Philippe :

Sinon, dès que les données sont nombreuses tu as intérêt à éviter les boucles quel quelles soient car elles sont chronophages.

Dans un cas comme le tien passer par un filtre auto ou avancé, à un stade donné, pour récupérer les données voulues serait surement bien plus rapide.
Pour exemple, sur un fichier de données de villes de 146 727 lignes et 2 346 687 cellules, je récupère une table de 2329 lignes et 37 249 cellules en l'espace de moins d'une seconde avec une extraction vers autre emplacement d'un filtre avancé...

cordialement,

Didier

[Philippe Tulliez]

Bonjour,
Cette discussion sur le forum m'a inspiré un nouvel exemple dans l'utilisation des filtres avancés.
Dans un classeur Excel nous avons une liste de données ayant en colonne A le nom de différents services d'une entreprise et dans les autres colonnes d'autres données.
L'idée est d'envoyer un classeur contenant une feuille à chacun des services concernés avec les données qui lui sont propres.
Les zones :
de données en feuille [db] avec en colonne A le nom des services,
de critères cellules $C$1:$C$2 dans la feuille [Param]
La feuille [Param] contiendra également la liste des services qui aura été exportée sans les doublons par la même méthode advancedFilter
La procédure
Elle se déroulera en deux étapes dont la deuxième est subdivisée en trois parties.
Etape 1 : Création d'une liste unique basée sur la colonne 1 (Services).
Etape 2 : Dans une boucle
2.1 Création nouvelle feuille
2.2 Exportation vers nouvelle feuille
2.3 Déplacement de cette nouvelle feuille vers un nouveau classeur
Le code de la procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sub ExportDataByAdvancedFilter()
 ' Déclaration + Initialisation des variables
 Dim rngList As Range, rngData As Range, rngCriteria As Range, r As Long
 Set rngData = shtData.Range("A1").CurrentRegion
 With shtParam
  Set rngList = .Range("A1"): Set rngCriteria = .Range("C1:C2")
 End With
 ' Etape 1 - Création d'une liste unique basée sur la colonne 1
 With rngData
 .Resize(, 1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rngList, Unique:=True
 End With
 ' Etape 2 - Boucle sur la liste avec placement du critère
 '           Création nouvelle feuille
 '           Exportation vers nouvelle feuille
 '           Déplacement de cette nouvelle feuille vers un nouveau classeur
 For r = 1 To rngList.CurrentRegion.Rows.Count - 1
  rngCriteria.Cells(2, 1) = rngList.Offset(r) ' Insère le critère
  ' 2.1 - Création d'une feuille
  Sheets.Add before:=Sheets(1): Sheets(1).Name = rngList.Offset(r)
  ' 2.2 - Exportation vers nlle feuille suivant critère
  With rngData
  .AdvancedFilter xlFilterCopy, rngCriteria, Sheets(1).Range("A1")
  End With
  ' 2.3 - Déplacement de la feuille cers un nouveau classeur
  Sheets(1).Move
  '
  ' Ici code pour sauver classeur, Envoyer par email etc...
  '
 Next
End Sub

[Taltosm]

J'ai pas bien été rapide sur ce coup mais voila:
il suffit de rajouter:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 ' Déclaration + Initialisation des variables
 Dim rngList As Range, rngData As Range, rngCriteria As Range, r As Long, Fichier As String, Chemin As String

et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
  ' Ici code pour sauver classeur, Envoyer par email etc...
  '
Fichier = rngList.Offset(r)
Chemin = "C:\Users\moncompte\Documents\dossier\juin 2013\"
ActiveWorkbook.SaveAs Chemin & Fichier
ActiveWorkbook.Close SaveChanges:=True

pour qu'automatiquement mes classeurs s'enregistrent
je gagne un temps précieux.

ce qu'il me reste a faire c'est comprendre comment copier aussi une feuille supplémentaire et que les cacluls qu'elle contient fonctionnent

Merci de votre aide pour le boulot deja fourni !

[Taltosm]

Bonjour,

je remonte ce fil car j'achoppe toujours si la possibilité de rajouter une feuille contenant des calculs ayant pour source l'extraction de données.

si quelqu'un avait une idée cela serai fort aimable !!

Merci

[Philippe Tulliez]

Bonjour,
Je crois qu'il serait bon que tu ouvres un nouveau sujet de discussion et que tu expliques clairement ce que tu souhaites faire car sur cette discussion ouverte à la base pour présenter un tutoriel, tu déposes une partie de réponse qui correspond à une autre discussion Ventiler des lignes dans des classeurs séparés où manifestement le filtre élaboré t'apporte une partie de réponse.

[Malick]

Salut,

Excellent tutoriel que je viens de découvrir. Il me sera d'une grande utilité dans mon travail.

Merci Philippe.

[Philippe Tulliez]

Bonjour Malick,
Le Filtre avancé d'excel est en effet un outil qui quand on en a compris les possibilités offre des possibilités extraordinaire et l'exploiter en VBA permet de gagner de nombreuses lignes de codes.
Merci d'en avoir apprécié la lecture.
Philippe

[illight]

Mais il est génial ce truc

Comme la plupart qui l'ont remarqué, il est vrai que l'on passe souvent les données sous Access pour pouvoir effectuer ce genre de requêtes.

Ces filtres élaborés, inconnus pour moi, sont une mise d'information très précieuses.
Je l'ai lu un peu en diagonale, mais si ce genre de problème ressurgit, nul doute que je ferai bon usage de ton tuto

[Philippe Tulliez]

Bonjour Olivier,
En effet Le filtre élaboré d'excel fait partie de ces outils d'excel comme la consolidation, table de données, etc. sous-exploités par l'utilisateur qui souvent apprend d'une façon empirique ce logiciel.
Tous ces outils regorgent pourtant de possibilités insoupçonnées et surtout quand on utilise en VBA la méthode qui leur est associée. Cela évite au programmeur des dizaines voir plus de lignes inutiles (voir petit exemple pour le filtre avancé dans cette discussion)

Merci de ton appréciation pour la lecture de ce tutoriel et d'avoir pris le temps d'en rendre compte, cela fait plaisir.

[casefayere]

Bonjour Philippe,

Dire que je n'avais toujours pas lu cet excellent tutoriel, j'aurai loupé quelque chose, encore bravo. Je le garde sous le coude.

Bonne continuation et merci pour tes contributions.

[Pierre Fauconnier]

Tous ces outils regorgent pourtant de possibilités insoupçonnées et surtout quand on utilise en VBA la méthode qui leur est associée. Cela évite au programmeur des dizaines voir plus de lignes inutiles

Je plussoie au moins 10.000 fois

[Didier Gonard]

Bonjour,

Oui, c'est toujours le problème mis en lumière lors des formations, ne vaut-il pas mieux investir dans la découverte ou l’approfondissement de l'outil que l'on possède que d'acquérir le dernier né en pensant que "ça ira" mieux
C'est sûr que le retour sur investissement au niveau de la lecture de tutos de ce genre devrait enthousiasmer plus d'un dirigeant de société...

cordialement,

Didier