Vous pouvez afficher des informations sur un/plusieurs paramètres à l'aide du filtrage des données dans Excel.

Il existe deux outils à cet effet : AutoFilter et Advanced Filter. Ils ne suppriment pas, mais masquent les données qui ne remplissent pas les conditions. Le filtre automatique effectue les opérations les plus simples. Le filtre avancé a beaucoup plus d'options.

Filtre automatique et filtre avancé dans Excel

J'ai un tableau simple qui n'est ni formaté ni déclaré sous forme de liste. Vous pouvez activer le filtre automatique via le menu principal.

Si vous formatez la plage de données sous forme de tableau ou la déclarez sous forme de liste, le filtre automatique sera ajouté immédiatement.

L'utilisation d'un filtre automatique est simple : vous devez sélectionner l'entrée avec la valeur souhaitée. Par exemple, affichez les livraisons au magasin n°4. Cochez la condition de filtrage correspondante :

On voit immédiatement le résultat :

Caractéristiques de l'outil :

1. Le filtre automatique ne fonctionne que dans une plage incassable. Les différents tableaux d'une même feuille ne sont pas filtrés. Même s'ils ont le même type de données.
2. L'outil traite la ligne supérieure comme des en-têtes de colonnes - ces valeurs ne sont pas incluses dans le filtre.
3. Il est permis d'appliquer plusieurs conditions de filtrage à la fois. Mais chaque résultat précédent peut masquer les enregistrements nécessaires au filtre suivant.

Le filtre avancé a beaucoup plus d'options :

1. Vous pouvez définir autant de conditions de filtrage que nécessaire.
2. Les critères de sélection des données sont visibles.
3. Grâce au filtre avancé, l'utilisateur peut facilement trouver des valeurs uniques dans un tableau multiligne.



Comment créer un filtre avancé dans Excel

Un exemple prêt à l'emploi - comment utiliser un filtre avancé dans Excel :

Seules les lignes contenant la valeur « Moscou » sont restées dans le tableau d'origine. Pour annuler le filtrage, vous devez cliquer sur le bouton « Effacer » dans la section « Trier et filtrer ».

Comment utiliser le filtre avancé dans Excel

Envisageons d'utiliser un filtre avancé dans Excel pour sélectionner les lignes contenant les mots « Moscou » ou « Riazan ». Les conditions de filtrage doivent être dans la même colonne. Dans notre exemple - les uns en dessous des autres.

Remplir le menu de filtrage avancé :

On obtient un tableau avec des lignes sélectionnées selon un critère donné :

Sélectionnons les lignes qui contiennent la valeur « N° 1 » dans la colonne « Magasin » et « >1 000 000 de roubles » dans la colonne coût. Les critères de filtrage doivent figurer dans les colonnes appropriées du tableau des conditions. Sur une seule ligne.

Remplissez les paramètres de filtrage. Cliquez sur OK.

Laissons dans le tableau uniquement les lignes qui contiennent le mot « Riazan » dans la colonne « Région » ou la valeur « >10 000 000 de roubles » dans la colonne « Coût ». Les critères de sélection appartenant à des colonnes différentes, nous les plaçons sur des lignes différentes sous les rubriques correspondantes.

Utilisons l'outil « Filtre avancé » :

Cet outil peut fonctionner avec des formules, ce qui permet à l'utilisateur de résoudre presque tous les problèmes lors de la sélection de valeurs dans des tableaux.

Règles de base:

1. Le résultat de la formule est le critère de sélection.
2. La formule écrite renvoie VRAI ou FAUX.
3. La plage initiale est spécifiée à l'aide de références absolues et le critère de sélection (sous forme de formule) est spécifié à l'aide de références relatives.
4. Si TRUE est renvoyé, la ligne sera affichée une fois le filtre appliqué. FAUX - non.

Affichons les lignes contenant des quantités supérieures à la moyenne. Pour cela, outre la plaque avec les critères (dans la cellule I1), saisissez le nom « Plus grande quantité ». Ci-dessous la formule. Nous utilisons la fonction MOYENNE.

Sélectionnez n’importe quelle cellule de la plage source et appelez « Filtre avancé ». Nous indiquons I1:I2 comme critère de sélection (liens relatifs !).

Seules les lignes où les valeurs de la colonne « Quantité » sont supérieures à la moyenne restent dans le tableau.

Pour ne laisser que les lignes non répétitives dans le tableau, dans la fenêtre « Filtre avancé », cochez la case « Uniquement les enregistrements uniques ».

Cliquez sur OK. Les lignes en double seront masquées. Seules les entrées uniques resteront sur la feuille.

Le filtre avancé d'Excel offre de plus grandes capacités de gestion des données de feuilles de calcul. Son réglage est plus complexe, mais son fonctionnement est beaucoup plus efficace.

En utilisant un filtre standard, un utilisateur de Microsoft Excel ne peut pas résoudre toutes les tâches assignées. Il n'y a pas d'affichage visuel des conditions de filtrage appliquées. Il n'est pas possible d'appliquer plus de deux critères de sélection. Vous ne pouvez pas filtrer les valeurs en double pour ne conserver que les entrées uniques. Et les critères eux-mêmes sont schématiques et simples. La fonctionnalité du filtre avancé est beaucoup plus riche. Examinons de plus près ses capacités.

Comment faire un filtre avancé dans Excel ?

Le filtre avancé vous permet de filtrer les données selon un ensemble illimité de conditions. Grâce à l'outil, l'utilisateur peut :

1. fixer plus de deux critères de sélection ;
2. copier le résultat du filtrage sur une autre feuille ;
3. définir une condition de toute complexité à l'aide de formules ;
4. extraire des valeurs uniques.

L'algorithme d'utilisation d'un filtre avancé est simple :

Le tableau du haut est le résultat du filtrage. La plaque inférieure avec les conditions est donnée côte à côte pour plus de clarté.



Comment utiliser le filtre avancé dans Excel ?

Pour annuler l'action d'un filtre avancé, placez le curseur n'importe où dans le tableau et appuyez sur la combinaison de touches Ctrl + Maj + L ou « Données » - « Tri et filtre » - « Effacer ».

À l'aide de l'outil « Filtre avancé », nous trouverons des informations sur les valeurs qui contiennent le mot « Set ».

Nous ajouterons des critères au tableau des conditions. Par exemple, ceux-ci :

Dans ce cas, le programme recherchera toutes les informations sur les produits dont les noms contiennent le mot « Set ».

Vous pouvez utiliser le signe « = » pour trouver la valeur exacte. Ajoutons les critères suivants au tableau des conditions :

Excel interprète le signe « = » comme un signal : l'utilisateur va désormais saisir une formule. Pour que le programme fonctionne correctement, la barre de formule doit contenir une entrée telle que : ="= Ensemble de cellules de la région 6."

Après avoir utilisé le « Filtre avancé » :

Filtrons maintenant la table source en utilisant la condition « OU » pour différentes colonnes. L'opérateur « OU » est également disponible dans l'outil AutoFilter. Mais là, il peut être utilisé dans une seule colonne.

Dans le tableau des conditions nous saisirons les critères de sélection : ="= Ensemble de la région 6ème." (dans la colonne « Nom ») et ="

Veuillez noter que les critères doivent être écrits sous les titres appropriés sur DIFFÉRENTES lignes.

Résultat de la sélection :

Le filtre avancé vous permet d'utiliser des formules comme critères. Regardons un exemple.

Sélection de la ligne avec la dette maximale : =MAX(Table1[Dette]).

Ainsi, on obtient les mêmes résultats qu'après avoir effectué plusieurs filtres sur une seule feuille Excel.

Comment créer plusieurs filtres dans Excel ?

Créons un filtre basé sur plusieurs valeurs. Pour ce faire, nous saisissons plusieurs critères de sélection des données dans le tableau des conditions :

Utilisons l'outil « Filtre avancé » :

Maintenant, à partir du tableau avec les données sélectionnées, nous allons extraire de nouvelles informations sélectionnées selon d'autres critères. Par exemple, uniquement les expéditions pour 2014.

Nous entrons un nouveau critère dans le tableau des conditions et utilisons l'outil de filtrage. La plage initiale est un tableau avec des données sélectionnées selon le critère précédent. C'est ainsi que vous filtrez sur plusieurs colonnes.

Pour utiliser plusieurs filtres, vous pouvez créer plusieurs tables de conditions sur de nouvelles feuilles. La méthode de mise en œuvre dépend de la tâche définie par l'utilisateur.

Comment filtrer par ligne dans Excel ?

Méthodes standard - pas question. Microsoft Excel sélectionne les données uniquement dans les colonnes. Nous devons donc rechercher d’autres solutions.

Voici des exemples de critères de chaîne pour un filtre avancé dans Excel :

Pour donner un exemple du fonctionnement d’un filtre de lignes dans Excel, créons un tableau.

Le filtre avancé est appelé ainsi car il possède des capacités étendues et véritablement illimitées et uniques par rapport au filtre automatique. Poursuivant notre connaissance des outils MS Excel pour travailler avec des tables de base de données, dans cet article...

Nous examinerons de plus près le fonctionnement du filtre avancé. Comme un filtre automatique, un filtre avancé dans Excel est conçu pour masquer une partie des informations de la vue de l'utilisateur afin de rendre la partie restante plus facile à percevoir ou pour imprimer uniquement les enregistrements de base de données sélectionnés.

Avec le filtre avancé, vous pouvez filtrer n'importe quoi dans le tableau !!! La limite des capacités de cet outil n'est que l'imagination de l'utilisateur !

Vous lisez le quatrième article d'une série d'articles sur la création de bases de données dans MS Exceller et les organisations traitement d'informations.

Vous pouvez découvrir comment procéder dans les articles précédents de la série.

Filtre n°2 – filtre avancé !

Pour commencer, nous devons créer un autre tableau au-dessus de la base de données dans lequel nous indiquerons les données qui constituent les critères de sélection du filtre avancé. (Parfois, il est plus pratique de placer un tel tableau sur une nouvelle feuille.)

Vous souvenez-vous que lorsque nous avons créé l'exemple de base de données, nous avons laissé quelques lignes vides en haut du tableau, indiquant qu'elles seraient nécessaires plus tard lors de l'analyse des données ? Ce moment est venu.

1. Ouvrez le fichier dans MS Excel.

2. Lors de la création d'un tableau de critères de sélection d'un filtre avancé, je vous recommande d'agir d'abord selon une formule, sans penser aux tâches à résoudre. Copie Tous en-têtes de champ de colonne des cellules A7...F7 aux cellules A1...F1 – pièce à usiner Prêt pour le tableau des critères ! Vous pouvez commencer le travail principal.

Pour un fonctionnement stable et sans erreur du filtre, il doit y avoir au moins une ligne vide entre le tableau des critères de sélection et le tableau de la base de données !

Continuons à étudier le filtre avancé dans Excel, en résolvant des problèmes pratiques en utilisant l'exemple de travail avec la base de données DB2 "Production de structures métalliques par site n°2".

Tâche n°5 :

Afficher des informations sur la production de poutres pour toutes les commandes pour toute la période.

1. Nous notons le paramètre de filtrage - le mot « poutre » dans la colonne « Produit » du tableau supérieur des critères de sélection.

2. Nous activons (« cliquons avec la souris ») n'importe quelle cellule à l'intérieur de la table de la base de données - cela remplira automatiquement la fenêtre « Plage source » dans la boîte de dialogue déroulante « Filtre avancé ».

3. Nous activons le filtre avancé dans Excel 2003 via le menu principal du programme. Sélectionnez : « Données » - « Filtre » - « Filtre avancé ».

4. Dans la fenêtre « Filtre avancé » qui apparaît, remplissez les fenêtres comme indiqué dans la capture d'écran sous ce texte.

Le filtre avancé vous permet de filtrer la liste sur place, mais peut également copier le résultat du filtrage vers un autre emplacement spécifié par l'utilisateur.

5. Le résultat du filtre avancé est affiché dans la capture d'écran suivante. Le filtre avancé a montré tous les enregistrements de la base de données contenant le mot « poutre » dans la colonne « Produit » - tâche terminée.

La casse des lettres n'affecte pas les résultats du filtrage !

Règles pour le « travail » conjoint de plusieurs conditions de sélection

Le filtre avancé d'Excel vous permet de filtrer selon diverses combinaisons très complexes de conditions de filtrage. Vous pouvez définir plusieurs conditions différentes pour plusieurs colonnes, vous pouvez définir plusieurs conditions pour une colonne ou vous pouvez définir des paramètres de sélection à l'aide d'une formule - il existe de nombreuses options !

Mais il n’y a que deux règles principales ! Tous les autres cas sont des combinaisons différentes de ces deux règles.

1. Conditions de sélection affichées sur une seule ligne dans une ou plusieurs colonnes différentes du tableau de critères, demandez au filtre avancé d'afficher les lignes pour lesquelles tout sans exception ces conditions. (Les paramètres de sélection sont reliés par des opérateurs logiques « ET ».)

2. Conditions de sélection affichées sur différentes lignes dans une ou plusieurs colonnes du tableau de critères, demandez au filtre avancé d'afficher toutes les lignes pour lesquelles au moins un de ces conditions. (Les paramètres de sélection sont reliés par des opérateurs logiques "OU".)

Illustrons avec des exemples le fonctionnement des règles ci-dessus et montrons comment fonctionne le filtre avancé dans Excel lors de la résolution de problèmes complexes.

Puisque nous avons examiné en détail la séquence d'actions de l'utilisateur lors de l'activation d'un filtre avancé dans l'exemple précédent, seules les conditions de sélection et les résultats finaux du filtrage seront affichés ci-dessous.

Tâche n°6 :

Filtrez les informations sur les plaques pesant moins de 0,1 tonne dans l’ensemble de la base de données.

Grâce au filtre avancé, toutes les plaques de base de données d'une masse inférieure à 0,1 tonne sont affichées (illustration de la règle n°1).

Tâche n°7 :

Affichez des informations sur toutes les plaques disponibles dans la base de données, ainsi que sur tout autre produit pesant plus d'une tonne.

Le problème est résolu - les enregistrements sont affichés pour toutes les plaques de la base de données et pour tous les produits pesant plus de 1 tonne (illustration de la règle n° 2).

Pour annuler l'action d'un filtre avancé, vous devez exécuter la commande dans le menu principal du programmeExceller"Données" - "Filtre" - "Tout afficher".

Je voudrais attirer votre attention sur la nécessité de surveiller attentivement l'exactitude de la spécification de la plage initiale de la base de données et de la plage de la table de conditions dans la boîte de dialogue déroulante « Filtre avancé » !

Plus précisément, dans le dernier exemple, vous devrez spécifier : « Plage de conditions : $A$1 : $ F$3 »!

S'il y a des lignes complètement vides dans la plage de conditions ou dans votre base de données, le filtre avancé ne fonctionnera pas !

Résultats.

Vous pouvez utiliser des caractères génériques et des symboles mathématiques courants lors de la saisie de termes de recherche :

• * - n'importe quel nombre de n'importe quels caractères
• ? – n'importe quel symbole
• = - égal
• < — меньше
• > - plus
• <= — меньше или равно
• >= - supérieur ou égal à
• <>- inégal

Le filtre avancé d’Excel est flexible et informatif à utiliser. Les critères de sélection sont toujours sous les yeux de l'utilisateur sous la forme d'un tableau de critères de sélection, ce qui est sans doute très pratique.

S'il y a beaucoup d'enregistrements et que dans les bases de données réelles, il existe des milliers d'enregistrements uniques, alors filtrer ceux dont vous avez besoin devient problématique à l'aide d'un filtre automatique, car vous devez faire défiler la liste déroulante. Le filtre étendu ne présente pas cet inconvénient.

« Creusez plus profondément » dans les capacités de cet outil par vous-même - cela vaut la peine de le comprendre ! L'efficacité accrue de votre travail couvrira plusieurs fois le temps consacré aux études !

Suite du sujet stocker et gérer de grandes quantités d’informations- dans les articles suivants de la série.

je t'en supplie respectueux travail de l'auteur s'abonner pour les annonces d'articles dans la fenêtre située à la fin de chaque article ou dans la fenêtre en haut de page !

Chers lecteurs, écrivez vos questions et commentaires dans les commentaires en bas de page.

Et puis pour pouvoir rapide sélectionnez uniquement les données dont vous avez besoin, cache inutile lignes les tables. Ainsi le filtre permet sans suppression les lignes du tableau Excel les masquent simplement temporairement.

Les lignes du tableau masquées par un filtre ne disparaissent pas. Vous pouvez grossièrement imaginer que leur hauteur devienne nulle (j'ai déjà parlé de changer la hauteur des lignes et la largeur des colonnes). Ainsi, les lignes restantes qui ne sont pas masquées par le filtre sont en quelque sorte « collées ensemble ». Le résultat est un tableau avec un filtre appliqué.

Extérieurement, un tableau avec un filtre dans Excel ressemble à n'importe quel autre, mais en haut, des flèches spéciales apparaissent dans chaque colonne. Vous pouvez voir un exemple de tableau avec un filtre appliqué dans l'image ci-dessous.

Voyons maintenant comment ajouter des filtres au tableau.

Comment faire (appliquer) un filtre sur un tableau Excel

C'est très simple! Disons que nous avons un tableau avec des en-têtes. Puisqu'un filtre sur un tableau Excel est appliqué à une certaine partie du tableau, cette même partie doit d'abord être souligner. Excel lui-même ne devinera jamais ce que vous voulez, alors sélectionnez les cellules requises dans la ligne d'en-tête du tableau, comme indiqué dans la figure ci-dessous.

[Cliquez sur l'image pour l'agrandir]

Note:

Si vous sélectionnez la ligne ENTIÈRE, Excel définira automatiquement un filtre sur TOUTES les colonnes qui contiennent au moins quelque chose. S'il n'y a rien dans la colonne, alors le filtre n'est pas appliqué.

Après avoir sélectionné les colonnes requises, vous devez réellement installer le filtre. Le moyen le plus simple consiste à accéder à l’onglet du ruban Accueil / Filtrer et trier et à cliquer sur Filtrer. Voici une capture d'écran d'Office 2010 ; dans d'autres versions d'Excel, le filtre est appliqué au tableau de la même manière.

[Cliquez sur l'image pour l'agrandir]

Après avoir cliqué sur ce bouton, un filtre sera ajouté aux cellules sélectionnées.

Si vous ne sélectionnez pas plusieurs cellules, mais cliquez simplement sur une, le filtre est ajouté à la ligne ENTIÈRE, comme si vous l'aviez sélectionné.

Dans mon exemple, sur les trois colonnes affichées dans l'image, le filtre ne sera ajouté qu'à deux, dont les cellules ont été préalablement sélectionnées. Vous pouvez voir le résultat dans la capture d'écran ci-dessous.

[Cliquez sur l'image pour l'agrandir]

L'exemple présenté d'activation d'un filtre sur une table est le plus simple. Voyons voir comment ça fonctionne.

Comment utiliser un filtre dans Excel

Immédiatement après avoir activé le filtre, le tableau ne changera pas (sans compter les flèches qui apparaissent dans les en-têtes de colonnes). Pour filtrer certaines des données dont vous avez besoin, cliquez sur la flèche dans la colonne sur laquelle vous souhaitez filtrer. En pratique, cela ressemble à la figure ci-dessous.

[Cliquez sur l'image pour l'agrandir]

Signification du filtre :

est qu'Excel ne laissera que les lignes du tableau qui dans CETTE colonne (avec le filtre configuré) contiennent une cellule avec la valeur sélectionnée. Les autres lignes seront masquées.

Pour supprimer le filtrage (sans supprimer le filtre !), cochez simplement toutes les cases. Le même effet se produira si vous supprimez complètement le filtre - le tableau reprendra sa forme originale.

Appliquer plusieurs filtres à un tableau Excel

Les filtres d'un tableau Excel peuvent être combinés. L'application de plusieurs filtres s'effectue selon le principe logique « ET ». Qu'est-ce que cela signifie, regardons un exemple.

Si nous avons plusieurs colonnes dans le tableau, nous pouvons alors d'abord définir un filtre, par exemple sur la première colonne. Dans ce cas, certaines lignes seront masquées conformément à la condition du filtre. Ensuite, nous mettrons en place un filtre dans la colonne suivante. Dans ce cas restant après le premier filtre les lignes seront en plus filtré. En termes simples, 2 filtres seront appliqués simultanément à chaque ligne - sur la première colonne et sur la seconde.

À mesure que chaque filtre suivant est appliqué, le nombre de lignes affichées dans le tableau qui satisfont aux conditions de TOUS les filtres diminuera. Ainsi, en appliquant séquentiellement plusieurs filtres, vous pouvez rapidement faire une petite sélection parmi un immense tableau comportant des milliers de lignes.

Vous pouvez effectuer d'autres opérations avec l'échantillon résultant. Par exemple, vous pouvez copier ces lignes et les déplacer vers une autre table. Ou, au contraire, vous pouvez filtrer les lignes que vous souhaitez supprimer complètement ! Et puis, en supprimant le filtre après les avoir supprimés, vous obtiendrez un tableau débarrassé des données inutiles.

Regardez maintenant la vidéo dans laquelle je montre un exemple réel de filtrage de données sur une liste de prix de plus de 15 000 lignes.

Résumons-le

Si vous devez travailler avec de grands tableaux dans Excel et que vous devez pouvoir sélectionner rapidement certaines données, l'utilisation de filtres vous fera gagner beaucoup de temps. Plus le tableau est grand, plus l’utilisation de filtres est bénéfique.

L'exemple donné est tiré de ma formation Microsoft Excel. J'aborde l'utilisation de filtres avec des conditions de sélection plus complexes dans mes cours.

Vous pouvez télécharger le fichier, en utilisant l'exemple dont j'ai examiné le travail avec des filtres