Le tutoriel montre comment utiliser un filtre avancé dans Excel et fournit un certain nombre d'exemples de plage de critères non triviaux pour créer un filtre sensible à la cas, trouver des correspondances et des différences entre deux colonnes, extraire des enregistrements qui correspondent à une liste plus petite, et plus encore.

Dans notre article précédent, nous avons discuté de différents aspects d'Excel Advanced Filter et comment l'utiliser pour filtrer les lignes avec et ainsi que ou logique. Maintenant que vous connaissez les bases, jetons un coup d'?il à des exemples de plage de critères plus complexes qui peuvent s'avérer utiles pour votre travail.

Configuration d'une gamme de critères basés sur des formules

étant donné que la plupart des gammes de critères, les exemples discutés dans ce tutoriel vont inclure diverses formules, commen?ons par définir les règles essentielles pour les établir correctement. Croyez-moi, ce petit morceau de théorie vous fera gagner beaucoup de temps et épargnera le mal de tête de dépannage de vos gammes de critères complexes qui incluent plusieurs conditions basées sur des formules.

• La formule que vous utilisez dans la gamme des critères doit évaluer à vrai ou fausse .
• La plage de critères doit contenir au moins 2 cellules: cellule de formule et cellule d'en-tête.
• La cellule d'en-tête des critères basés sur la formule doit être vide ou différente de l'un des en-têtes du tableau (plage de liste).
• Pour que la formule soit évaluée pour chaque ligne de la plage de liste, reportez-vous à la cellule la plus élevée avec des données en utilisant une référence relative comme A1.
• Pour que la formule soit évaluée uniquement pour une cellule spécifique ou une gamme de cellules , reportez-vous à cette cellule ou à la gamme en utilisant une référence absolue comme $ a 1 $.
• Lorsque vous référez la plage de liste dans la formule, utilisez toujours des références de cellules absolues.
• Lorsque vous fournissez plusieurs conditions, entrez tous les critères de la même ligne pour les rejoindre avec un et opérateur, et mettez chaque critère sur une ligne séparée pour les rejoindre avec l'opérateur OR .

Excel Exemples de gamme de critères de filtre avancé

Les exemples suivants vous apprendront comment créer vos propres filtres dans Excel pour gérer des taches plus complexes qui ne peuvent pas être effectuées à l'aide de l'autofilter Excel ordinaire.

Filtre de cas pour les valeurs de texte

En plus d'Excel Autofilter, l'outil de filtre avancé est insensible à la casse par nature, ce qui signifie qu'il ne distingue pas entre les caractères majuscules et les caractères minuscules lors du filtrage des valeurs de texte. Cependant, vous pouvez facilement effectuer une recherche sensible à la casse en utilisant la fonction exacte dans les critères de filtre avancés.

Par exemple, pour filtrer les lignes contenant des bananes , en ignorant la banane et la banane , entrez la formule suivante dans la gamme des critères:

=EXACT(B5, "Banana")

Où B est la colonne contenant les noms des éléments, et la ligne 5 est la première ligne de données.

Et puis, appliquez un filtre avancé Excel en cliquant sur le bouton avancé sur l'onglet Données et configurez la plage de liste et la plage de critères comme indiqué dans la capture d'écran ci-dessous. Veuillez faire attention que la plage de critères comprend 2 cellules - la cellule d'en-tête et la cellule de formule .

Note. L'image ci-dessus ainsi que toutes les autres captures d'écran de ce tutoriel montrent des formules dans les cellules de gamme des critères uniquement pour la clarté. Dans vos véritables feuilles de calcul, la cellule de formule doit retourner vrai ou fausse, selon que la première ligne de données correspond aux critères ou non:

Valeurs de filtre au-dessus ou en dessous de la moyenne dans une colonne

Lors du filtrage des valeurs numériques, vous pouvez souvent afficher uniquement les cellules qui sont supérieures ou inférieures à une valeur moyenne dans la colonne. Par exemple:

Pour filtrer les rangées avec sous-total au-dessus de la moyenne , utilisez la formule suivante dans la plage des critères:

=F5>AVERAGE($F$5:$F$50)

Pour filtrer les rangées avec sous-total en dessous de la moyenne , utilisez la formule suivante:

=F5<average></average>

Veuillez prêter attention que nous utilisons une référence relative pour nous référer à la cellule supérieure avec des données (F5), et des références absolues pour définir la gamme complète pour laquelle vous souhaitez calculer la moyenne, à l'exclusion de la tête de la colonne ($ f 5: $ f 50 $).

La capture d'écran suivante démontre la formule supérieure à la moyenne en action:

Ceux d'entre vous qui connaissent les filtres à numéros Excel peuvent se demander, pourquoi quelqu'un prendrait-il la peine d'utiliser un filtre avancé tandis que les filtres à numéro intégrés ont déjà les options supérieures et inférieures à la moyenne ? C'est vrai, mais les filtres Excel intégrés ne peuvent pas être utilisés avec la logique ou la logique!

Ainsi, pour prendre cet exemple plus loin, filtrons les lignes où les ventes de sous-total (colonne F) ou de septembre (colonne E) sont supérieures à la moyenne. Pour cela, configurez la plage des critères avec la logique ou en entrant chaque condition sur une ligne séparée. En conséquence, vous obtiendrez une liste d'éléments avec les valeurs supérieures à la moyenne dans la colonne E ou F:

Filtrez des rangées avec des blancs ou des non-blanks

Comme tout le monde le sait, le filtre Excel a une option intégrée pour filtrer les cellules vierges. En sélectionnant ou en désélectionnant la case (Blanks) dans le menu AutoFilter, vous ne pouvez afficher que les lignes qui ont des cellules vides ou non vides dans une ou plusieurs colonnes. Le problème est que le filtre Excel intégré pour les blancs ne peut fonctionner qu'avec la logique et.

Si vous souhaitez filtrer les cellules vierges ou non liées à la logique OR, ou utilisez les conditions vides / non liées à certains autres critères, configurez une gamme de critères de filtre avancée avec l'une des formules suivantes:

Filtre Blanks :

top_cell = ""

Filtrer les non-beroux:

top_cell ""

Filtrage des cellules vierges avec la logique ou

Pour filtrer les lignes qui ont une cellule vide dans la colonne A ou B, soit dans les deux colonnes, configurez la plage de critères de filtre avancée de cette manière:

• =A6=""
• =B6=""

Où 6 est la plus grande rangée de données.

Filtrage des cellules non chlantées avec ou aussi bien que et logique

Pour mieux comprendre comment le filtre avancé d'Excel fonctionne avec plusieurs critères, filtrez les lignes de notre tableau d'échantillons avec les conditions suivantes:

• Soit la région (colonne A) ou l'article (colonne B) doit être non tombée, et
• Le sous-total (colonne C) doit être supérieur à 900.

Pour le dire différemment, nous voulons afficher des lignes qui remplissent les conditions suivantes:

( Sous-total > 900 et région = non-Blanque) ou ( sous-total > 900 et élément = non-Blank)

Comme vous le savez déjà, dans la plage de critères de filtre avancée Excel, les conditions jointes à la logique et la logique doivent être saisies dans la même ligne, et les conditions jointes à la logique OR - sur différentes lignes:

étant donné qu'un critère de cet exemple est exprimé avec une formule (non-bliks) et l'autre comprend un opérateur de comparaison (sous-total> 900), permettez-moi de vous rappeler que:

• Les critères formés avec des opérateurs de comparaison doivent avoir des titres exactement égaux aux titres de la table, comme les critères sous-totaux dans la capture d'écran ci-dessus.
• Les critères basés sur les formules devraient avoir une cellule d'en-tête vierge ou une rubrique qui ne correspond à aucun des en-têtes de table, comme les critères non-Blanks dans la capture d'écran ci-dessus.

Comment extraire les enregistrements supérieurs / inférieurs

Comme vous le savez probablement, les filtres à numéro d'Excel Back-In ont une option pour afficher les 10 premiers ou les 10 éléments inférieurs. Mais que se passe-t-il si vous avez besoin de filtrer les 3 premières valeurs des 3 ou inférieurs? Dans ce cas, Excel Advanced Filtre avec les formules suivantes est utile:

Extraire les éléments du top n:

top_cell > = grand ( plage , n)

Extraire les éléments inférieurs de N:

top_cell plage , n)

Par exemple, pour filtrer les 3 meilleurs sous-totaux, créez la gamme de critères avec cette formule:

=F5>=LARGE($F$5:$F$50,3)

Pour extraire les 3 sous-totaux inférieurs, utilisez cette formule:

=F5>=SMALL($F$5:$F$50,3)

Où F5 est la cellule la plus élevée avec des données dans la colonne sous-totale (à l'exclusion de la colonne).

La capture d'écran suivante montre la formule des 3 premières en action:

Note. Si la plage de liste contient quelques lignes avec les mêmes valeurs qui entrent dans la liste N supérieure / inférieure N, toutes ces lignes seront affichées, comme indiqué dans la capture d'écran ci-dessous:

Filtre pour les correspondances et les différences entre deux colonnes

L'un de nos articles précédents a expliqué une variété de fa?ons de comparer deux colonnes dans Excel et de trouver des matchs et des différences entre eux. En plus des formules Excel, des règles de formatage conditionnel et de l'outil de dissolvant en double couvert dans le didacticiel ci-dessus, vous pouvez également utiliser le filtre avancé d'Excel pour extraire des lignes qui ont des valeurs identiques ou différentes en deux colonnes ou plus. Pour ce faire, saisissez l'une des formules simples suivantes dans la plage des critères:

• Filtre pour les correspondances (doublons) en 2 colonnes: =B5=C5
• Filtre pour les différences (valeurs uniques) en 2 colonnes: =B5C5

Où B5 et C5 sont les cellules les plus élevées avec des données dans les deux colonnes que vous souhaitez comparer.

Note. L'outil de filtre avancé ne peut rechercher que des correspondances et des différences dans la même ligne . Pour trouver toutes les valeurs qui se trouvent dans la colonne A mais qui ne sont nulle part dans la colonne B, utilisez cette formule.

Filtre les lignes basées sur les éléments correspondants dans une liste

En supposant que vous ayez une grande table avec des centaines ou des milliers de lignes, et vous avez re?u une liste plus courte ne contenant que les éléments pertinents à un moment donné. La question est - comment trouvez-vous toutes les entrées de votre table qui sont ou non dans la liste plus petite?

Filtre les lignes qui correspondent aux éléments dans une liste

Pour trouver tous les éléments de la table source qui sont également présents dans une liste plus petite, en utilisant la formule Countif suivante:

Counttif ( list_to_match , top_data_cell)

En supposant que la liste plus petite se trouve dans la plage D2: D7, et les éléments de la table à comparer à cette liste sont dans la colonne B à partir de la ligne 10, la formule se déroule comme suit (veuillez remarquer l'utilisation de références absolues et relatives):

=COUNTIF($D$2:$D$7,B10)

Bien s?r, vous n'êtes pas confiné pour filtrer votre table avec un seul critère.

Par exemple, pour filtrer les lignes correspondant à la liste, mais pour la région du Nord uniquement, entrez deux critères dans la même ligne afin qu'ils travaillent avec la logique:

• Région: ="=North"
• Articles de correspondance: =COUNTIF($D$2:$D$7,B10)

Comme vous pouvez le voir dans la capture d'écran ci-dessous, il n'y a que deux enregistrements dans le tableau qui correspondent aux deux critères:

Note. Dans cet exemple, nous utilisons les critères de correspondance exacts pour les valeurs de texte: <em>="=North</em> " pour trouver uniquement les cellules qui sont exactement égales au texte spécifié. Si vous entrez dans les critères de la région simplement au nord (sans le signe égal et les doubles devis), Microsoft Excel trouvera tous les éléments qui commencent par le texte spécifié, par exemple nord-est ou nord-ouest . Pour plus d'informations, veuillez consulter Excel Advanced Filter pour les valeurs de texte.

Filtre les lignes qui ne correspondent pas aux éléments dans une liste

Pour trouver tous les éléments dans le tableau qui ne sont pas dans la liste plus petite, vérifiez si le résultat de notre formule COULF est égal à zéro:

Counttif ( list_to_match , top_data_cell) = 0

Par exemple, pour filtrer les éléments de la région nord du tableau qui apparaissent dans la liste, utilisez les critères suivants:

• Région: ="=North"
• Articles non correspondants: =COUNTIF($D$2:$D$7,B10)=0

Notes:

• Si la liste à correspondre réside dans une feuille de calcul différente, assurez-vous d'inclure le nom de la feuille dans la formule, par exemple =COUNTIF(Sheet2!$A$2:$A$7,B10) .
• Si vous souhaitez extraire les résultats sur une autre feuille, démarrez le filtre avancé à partir de la feuille de destination, comme expliqué sur la fa?on d'extraire les lignes filtrées vers une autre feuille de calcul.

Filtre pour les week-ends et les jours de semaine

Jusqu'à présent, nos exemples de plage de critères de filtre avancés ont traité principalement des valeurs numériques et de texte. Maintenant, il est temps de donner des indices à ceux d'entre vous qui opèrent à des dates.

Les filtres à date Excel intégrés offrent une large gamme d'options qui couvrent de nombreux scénarios. Beaucoup, mais pas tous! Par exemple, si on vous avait donné une liste de dates et que vous avez demandé de filtrer les jours de semaine et les week-ends, comment allez-vous y procéder?

Comme vous le savez probablement, Microsoft Excel offre une fonction spéciale en semaine qui renvoie le jour de la semaine correspondant à une date donnée. Et c'est cette fonction que nous allons utiliser dans la plage de critères de filtre avancée Excel.

Comment filtrer les week-ends dans Excel

En gardant à l'esprit que, dans les termes de la semaine, 1 signifie dimanche et 6 représente samedi, la formule pour filtrer les week-ends se déroule comme suit:

Ou (en semaine ( date ) = 7, jour de semaine ( date ) = 1)

Dans cet exemple, nous filtrons les dates de la colonne B en commen?ant par la ligne 5, donc notre formule de week-end prend la forme suivante:

=OR(WEEKDAY(B5)=7, WEEKDAY(B5)=1)

Comment filtrer les jours de semaine dans Excel

Pour filtrer les jours de semaine, modifiez la formule ci-dessus afin qu'elle laisse de c?té (dimanche) et 7 (samedi):

Et (en semaine ( date ) 7, en semaine ( date ) 1)

Pour notre tableau d'échantillons, la formule suivante fonctionnera un régal:

=AND(WEEKDAY(B5)7, WEEKDAY(B5)1)

De plus, vous pouvez ajouter une condition de plus pour filtrer les cellules vides: =B5""

Pour filtrer les dates de vos feuilles de calcul d'une autre manière, trouvez simplement la fonction de date pertinente et n'hésitez pas à l'utiliser dans votre plage de critères de filtre avancée.

Eh bien, c'est ainsi que vous utilisez le filtre avancé dans Excel avec des critères complexes. Bien s?r, vos options ne se limitent pas aux exemples discutés dans ce tutoriel, notre objectif était juste de vous donner quelques idées inspirantes qui vous mettront sur la bonne voie. En vous rappelant que la route de la ma?trise est pavée de pratique, vous pouvez télécharger nos exemples en utilisant le lien ci-dessous et les étendre ou les rétro-ingérez pour une meilleure compréhension. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique

Exemples de filtre avancé Excel (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!