Utilisation du filtre avancé dans Excel pour résoudre le problème de Mort. Bien que les filtres réguliers soient devenus plus puissants, il arrive encore que le filtre avancé puisse faire des astuces que d'autres ne peuvent pas.
Regarder la vidéo
- Le filtre avancé est plus "avancé" que le filtre normal car:
- 1) Il peut copier dans une nouvelle plage
- 2) Vous pouvez créer des critères plus complexes tels que Champ 1 = A ou Champ 2 = A
- 3) C'est rapide
- Mort essaie de traiter 100 000 lignes dans VBA en parcourant des enregistrements ou en utilisant un tableau
- Il sera toujours plus rapide d'utiliser les fonctionnalités intégrées d'Excel que d'écrire votre propre code.
- Vous avez besoin d'une plage d'entrée, puis d'une plage de critères et / ou d'une plage de sortie
- Pour la plage d'entrée: une seule ligne d'en-têtes au-dessus des données
- Ajouter une ligne temporaire pour les en-têtes
- Pour la plage de sortie: une ligne d'en-têtes pour les colonnes que vous souhaitez extraire
- Pour la plage de critères: en-têtes de la ligne 1, valeurs commençant à la ligne 2
- Complication: les anciennes versions d'Excel ne permettraient pas à la plage de sortie d'être sur une autre feuille
- Si vous écrivez une macro qui pourrait être exécutée en 2003, utilisez une plage nommée pour la plage d'entrée afin de contourner
Transcription vidéo
Apprenez Excel à partir d'un podcast, épisode 2060: Filtre avancé Excel
Salut, bon retour sur le netcast, je suis Bill Jelen. La question d'aujourd'hui envoyée par Mort. Mort, il a 100 000 lignes de données et il s'intéresse aux colonnes A, B et D où la colonne C correspond à une année particulière. Donc, il veut qu'une personne entre un an, puis obtienne les colonnes A, B et D. Et Mort a un VBA où il utilise des tableaux pour le faire et j'ai dit: «Attendez une seconde, vous savez, le filtre avancé ferait cela un beaucoup mieux. » D'accord, et maintenant juste pour revoir, je suis retourné, j'ai regardé mes vidéos. Je n'ai pas couvert le filtre avancé depuis longtemps, nous devrions donc en parler.
Le filtre avancé nécessite une plage d'entrée, puis au moins l'une de celles-ci: une plage de critères ou une plage de sortie. Bien qu'aujourd'hui nous allons utiliser les deux. D'accord, la plage d'entrée correspond à vos données et vous devez avoir des en-têtes au-dessus des données. Donc, Mort n'a pas de titres et je vais donc insérer temporairement une ligne ici et faire comme Champ 1. Mort sait quelles sont ses données et il pourrait donc y mettre de vrais titres. Et nous n'utilisons rien qui s'appelle ces données dans les colonnes E à O, donc je n'ai pas à ajouter de titres là-bas, d'accord? Alors maintenant, A1 à D, 100000 devient ma plage d'entrée. Et puis la plage de sortie et la plage de critères - Eh bien, la plage de sortie n'est qu'une liste des en-têtes que vous voulez. Je vais donc mettre la plage de sortie ici et nous n'avons pas besoin du champ 3 donc je 'Je vais juste prendre ça sur le côté. Alors maintenant, cette plage ici, A1 à C1 devient ma plage de sortie qui indique à Excel quels champs je veux dans la plage d'entrée. Et, ils pourraient être dans un ordre différent si vous vouliez réorganiser les choses, comme si je veux d'abord le champ 4, puis le champ 1 puis le champ 2. Et encore une fois, ce serait de vrais titres comme le numéro de facture. Je ne sais tout simplement pas à quoi ressemblent les données de Mort.
Et puis, la plage de critères est un titre et la valeur souhaitée. Alors disons que j'essayais d'obtenir quelque chose en 2014. Cela devient la fourchette de critères comme ça. Très bien, juste un mot d'avertissement ici. Je suis dans Excel 2016 et il est possible de faire un filtre avancé entre deux feuilles dans Excel 2016, mais si vous revenez en arrière et que je ne me souviens pas de quel chemin est, peut-être 2003, je ne suis pas sûr. À un certain moment dans le passé, vous ne pouviez pas faire de filtre avancé d'une feuille à une autre, vous deviez donc venir ici et nommer votre plage d'entrée. Vous devrez créer un nom ici. MyName ou quelque chose comme ça, d'accord? Et ce serait de cette manière que vous seriez en mesure de réussir, d'accord. Pas nécessairement dans Excel 2016 mais encore une fois, je 'Je ne sais pas si Mort va l'exécuter dans les anciennes versions des données.
Très bien, alors revenons ici à Data, nous allons au filtre avancé, d'accord. Et nous allons copier vers un autre emplacement qui active notre plage de sortie là-bas. D'accord, donc la plage de liste, où sont les données? Parce que je suis dans Excel 2016, je vais pointer vers les données, au lieu d'utiliser la plage de noms - C'est donc ma plage d'entrée. La plage de critères est ces cellules juste là et ensuite, là où nous allons - en sortie, ce sera simplement ces trois cellules là. Et puis nous cliquons sur OK. Très bien, et BAM! C'est à quel point c'est rapide. Et si nous voulions une année différente? Si nous voulions une année différente, nous supprimerions les résultats, placerions en 2015, puis refaire un filtre avancé, Copiez dans un autre emplacement, cliquez sur OK et il y a tous les enregistrements 2015. Rapide comme l'éclair.
Bon maintenant, alors que je suis fan du filtre avancé dans Excel ordinaire, j'étais un grand fan du filtre avancé dans VBA, d'accord, parce que VBA rend le filtre avancé vraiment, vraiment, vraiment simple. D'accord, nous allons donc écrire du code ici pour Mort, en supposant que les données de Mort n'ont pas d'en-têtes et que nous allons devoir les ajouter temporairement, d'accord? Donc, je vais passer à VBA, Alt + F11 et nous allons l'exécuter à partir de la feuille de calcul qui contient les données. Donc: Dim WS As Worksheet, Set WS = ActiveSheet. Et puis, insérez la ligne 1 et ajoutez simplement quelques en-têtes: A, B, Année et D.Déterminez le nombre de lignes de données que nous avons aujourd'hui, puis à partir de la cellule A1 en sortant 4 colonnes jusqu'à la dernière ligne, nommez-le à être la plage d'entrée. D'accord, et c'est en fait le code de Mort ici, où il a demandé l'InputBox,obtient l'année qu'ils veulent et ensuite il demande quelle année ou ce qu'ils veulent nommer la nouvelle feuille, d'accord. Donc, il va en fait insérer une feuille à la volée, puis I- Dimension une nouvelle feuille, WSN, comme ActiveSheet. Je sais donc que WS est la feuille d'origine, WSN est la nouvelle feuille qui vient d'être ajoutée. Sur la nouvelle feuille, placez la plage de critères de sorte que sous la colonne E, il y a l'en-tête qui correspond à cet en-tête ici, puis, quelle que soit la réponse qu'ils nous ont donnée, va dans E2. La plage de sortie sera mes trois autres titres: A, B et D.Et encore, si vous ou Mort les changez en titres réels, ce qui est probablement une meilleure chose à faire que A, B, D, et vous aussi changez-les en de vrais titres, d'accord? Donc, tout cela n'est qu'un petit pré-travail ici. Cette ligne de code géniale fera tout le filtre avancé. Donc,à partir de InputRange, nous faisons un AdvancedFilter, nous allons copier. C'est notre choix de filtre en place ou copie. Le CriteriaRange est E1 à E2, le CopyToRange est A à C. Valeurs uniques -Non, nous voulons toutes les valeurs. D'accord, cette ligne de code fait toute la magie de la boucle sur tous les enregistrements ou remplace la boucle sur tous les enregistrements ou la création des tableaux. Et puis nous avons terminé, nous effacerons la plage de critères, puis supprimerons la ligne 1 de la feuille de calcul d'origine.Et puis nous avons terminé, nous effacerons la plage de critères, puis supprimerons la ligne 1 de la feuille de calcul d'origine.Et puis nous avons terminé, nous effacerons la plage de critères, puis supprimerons la ligne 1 de la feuille de calcul d'origine.
Bon, revenons ici à nos données. Nous allons faciliter son exécution, donc: Insérez, une forme et appelez ce filtre, accueil, centre, centre, plus grand, plus grand, plus grand, cliquez avec le bouton droit, attribuez une macro et affectez-le à MacroForMort. Très bien, alors on y va. Nous allons faire un test. Voyez que nous sommes sur la fiche technique, cliquez sur le filtre, quelle année voulons-nous? Nous voulons 2015. Comment est-ce que je veux l'appeler? Je veux l'appeler 2015, d'accord. Et BAM! Voilà c'est fait. C'est à quelle vitesse, c'est à quelle vitesse c'est.
Maintenant, puisque les données originales de Mort n'avaient pas d'en-têtes, peut-être que ces données ne devraient pas avoir d'en-têtes. Alors allons-y Alt + F11, ici nous voulons effacer la plage de critères. Nous allons également Rows (1) .Delete. D'accord, alors maintenant, la prochaine fois que nous en parlerons, cela supprimera ces en-têtes. Et nous allons juste - Plutôt que de tout exécuter rapidement, jetons un coup d'œil ici avec le 2014. Je vais donc sélectionner une cellule sur les données, Alt + F11, et je veux aller juste au point où nous faisons le filtre avancé. Nous pouvons donc regarder et voir ce que fait toute la macro ici. Nous allons donc cliquer sur Exécuter, et je veux obtenir 2014. 2014, d'accord. Et donc, appuyez sur F8, nous sommes sur le point de faire le filtre avancé. Nous pouvons revenir à Excel ici et voir ce qui s'est passé.
La première chose qui s'est produite - La première chose qui s'est produite est que nous avons ajouté une nouvelle ligne temporaire avec les en-têtes. Inséré cette feuille de calcul, construit une plage de critères avec un en-tête et quelle année ils ont entré, choisi les champs que nous voulons faire, puis de retour dans VBA, je vais exécuter la prochaine ligne de codes, c'est F8 qui fait le filtre avancé juste là . C'est incroyablement rapide et vous verrez que cela nous a en fait apporté tous les records. À partir de là, c'est juste un peu de nettoyage, supprimez ceci, supprimez ceci. Je vais revenir aux données et supprimer la ligne 1 et nous serons prêts à partir. Je vais donc laisser le reste de cette course, supprimer ce point d'arrêt, d'accord? Il y a donc le VBA. Pour moi, c'est à mon avis le moyen le plus rapide et le plus rapide.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
Très bien, eh bien, voilà. Je tiens à remercier Mort d'avoir envoyé cette question. Je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2060.xlsm
