Table des matières
Pour la grande majorité des utilisateurs d'Excel, lorsque le mot "filtrage des données" leur vient à l'esprit, seul le filtre classique habituel de l'onglet Données – Filtre (Données — Filtre):
Un tel filtre est une chose familière, sans aucun doute, et dans la plupart des cas, il fera l'affaire. Cependant, il existe des situations où vous devez filtrer par un grand nombre de conditions complexes dans plusieurs colonnes à la fois. Le filtre habituel ici n'est pas très pratique et je veux quelque chose de plus puissant. Un tel outil pourrait être filtre avancé, notamment avec une petite "finition à la lime" (selon la tradition).
Base
Pour commencer, insérez quelques lignes vides au-dessus de votre tableau de données et copiez-y l'en-tête du tableau - il s'agira d'une plage avec des conditions (surlignées en jaune pour plus de clarté) :
Il doit y avoir au moins une ligne vide entre les cellules jaunes et le tableau d'origine.
C'est dans les cellules jaunes qu'il faut saisir les critères (conditions) selon lesquels le filtrage sera alors effectué. Par exemple, si vous devez sélectionner des bananes dans le «Auchan» de Moscou dans le quartier III, les conditions ressembleront à ceci:
Pour filtrer, sélectionnez n'importe quelle cellule de la plage avec les données source, ouvrez l'onglet Données Et cliquez sur le En outre (Données — Avancé). Dans la fenêtre qui s'ouvre, une plage avec des données devrait déjà être saisie automatiquement et nous n'aurons qu'à spécifier la plage de conditions, c'est-à-dire A1:I2 :
Veuillez noter que la plage de conditions ne peut pas être attribuée "avec une marge", c'est-à-dire que vous ne pouvez pas sélectionner de lignes jaunes vides supplémentaires, car une cellule vide dans la plage de conditions est perçue par Excel comme l'absence d'un critère, et une cellule entière vide ligne comme une demande d'affichage indiscriminé de toutes les données.
Interrupteur Copiez le résultat à un autre emplacement vous permettra de filtrer la liste non pas directement sur cette feuille (comme avec un filtre normal), mais de décharger les lignes sélectionnées dans une autre plage, qui devra ensuite être spécifiée dans le champ Mettre le résultat dans la plage. Dans ce cas, on n'utilise pas cette fonction, on laisse Liste de filtres en place et cliquez sur OK. Les lignes sélectionnées seront affichées sur la feuille :
Ajout d'une macro
"Eh bien, où est la commodité ici?" vous demandez et vous aurez raison. Non seulement vous devez saisir des conditions dans les cellules jaunes avec vos mains, mais également ouvrir une boîte de dialogue, y saisir des plages, appuyer sur OK. Dommage, je suis d'accord ! Mais « tout change quand ils arrivent © » – les macros !
L'utilisation d'un filtre avancé peut être grandement accélérée et simplifiée à l'aide d'une simple macro qui exécutera automatiquement le filtre avancé lorsque des conditions sont saisies, c'est-à-dire en modifiant toute cellule jaune. Faites un clic droit sur l'onglet de la feuille courante et sélectionnez la commande Texte source (Code source). Dans la fenêtre qui s'ouvre, copiez et collez le code suivant :
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub Cette procédure s'exécute automatiquement lorsqu'une cellule de la feuille de calcul en cours est modifiée. Si l'adresse de la cellule modifiée tombe dans la plage jaune (A2:I5), alors cette macro supprime tous les filtres (le cas échéant) et réapplique le filtre étendu à la table de données source en commençant par A7, c'est-à-dire que tout sera filtré instantanément, immédiatement après avoir saisi la condition suivante :
Alors tout va beaucoup mieux, non ? 🙂
Implémentation de requêtes complexes
Maintenant que tout est filtré à la volée, on peut aller un peu plus loin dans les nuances et démonter les mécanismes des requêtes plus complexes dans le filtre avancé. En plus d'entrer des correspondances exactes, vous pouvez utiliser divers caractères génériques (* et ?) et des signes d'inégalité mathématiques dans une gamme de conditions pour implémenter une recherche approximative. La casse des caractères n'a pas d'importance. Pour plus de clarté, j'ai résumé toutes les options possibles dans un tableau :
| Critère | Résultat |
| gr* ou gr | toutes les cellules commençant par GrIe Groreille, Grpamplemousse, Granat et ainsi de suite |
| = oignon | toutes les cellules exactement et uniquement avec le mot Arc, c'est-à-dire la correspondance exacte |
| *liv* ou *liv | cellules contenant Surf comment souligner, c'est-à-dire ОSurfqui, Surfep, SelonSurf et ainsi de suite |
| =p*v | mots commençant par П et se terminant par В ie Пpremierв, Пd'etherв et ainsi de suite |
| comme | mots commençant par А et contenant en outre СIe Аla peauсin, Аnanaс, Asai et ainsi de suite |
| =*s | mots se terminant par С |
| =???? | toutes les cellules avec du texte de 4 caractères (lettres ou chiffres, espaces compris) |
| =m??????n | toutes les cellules avec un texte de 8 caractères commençant par М et se terminant par НIe Мandariн, М l’anxiété, accéder à un meilleur sommeil,н et ainsi de suite |
| =*n??a | tous les mots finissant par А, où est la 4ème lettre à partir de la fin НIe Faisceauнikа, Selonнozа et ainsi de suite |
| >=e | tous les mots commençant par Э, Ю or Я |
| <>*o* | tous les mots qui ne contiennent pas de lettre О |
| <>*vich | tous les mots sauf ceux se terminant par HIV (par exemple, filtrer les femmes par deuxième prénom) |
| = | toutes les cellules vides |
| <> | toutes les cellules non vides |
| > = 5000 | toutes les cellules avec une valeur supérieure ou égale à 5000 |
| 5 ou =5 | toutes les cellules avec la valeur 5 |
| > = 3/18/2013 | toutes les cellules dont la date est postérieure au 18 mars 2013 (inclus) |
Points subtils :
- Le signe * signifie n'importe quel nombre de caractères, et ? – n'importe quel caractère.
- La logique de traitement des requêtes textuelles et numériques est légèrement différente. Ainsi, par exemple, une cellule de condition avec le chiffre 5 ne signifie pas rechercher tous les nombres commençant par cinq, mais une cellule de condition avec la lettre B est égale à B*, c'est-à-dire qu'elle recherchera tout texte commençant par la lettre B.
- Si la requête de texte ne commence pas par le signe =, vous pouvez mettre mentalement * à la fin.
- Les dates doivent être saisies au format américain mois-jour-année et au moyen d'une fraction (même si vous disposez d'Excel et de paramètres régionaux).
Connecteurs logiques ET-OU
Les conditions écrites dans des cellules différentes, mais sur la même ligne, sont considérées comme interconnectées par un opérateur logique И (ET):
Ceux. filtrer des bananes pour moi au troisième trimestre, précisément à Moscou et en même temps chez Auchan.
Si vous avez besoin de lier des conditions avec un opérateur logique OR (OR), il suffit alors de les saisir sur des lignes différentes. Par exemple, si nous devons trouver toutes les commandes du directeur Volina pour les pêches de Moscou et toutes les commandes d'oignons au troisième trimestre à Samara, cela peut être spécifié dans une gamme de conditions comme suit :
Si vous devez imposer deux conditions ou plus sur une colonne, vous pouvez simplement dupliquer l'en-tête de colonne dans la plage de critères et saisir la deuxième, la troisième, etc. en dessous. termes. Ainsi, par exemple, vous pouvez sélectionner toutes les transactions de mars à mai :
En général, après avoir "fini avec un fichier", un filtre avancé s'avère être un outil assez décent, à certains endroits pas pire qu'un filtre automatique classique.
- Superfiltre sur les macros
- Que sont les macros, où et comment insérer le code de macro dans Visual Basic
- Tableaux intelligents dans Microsoft Excel