Comment filtrer dans Excel par titre. Filtre avancé dans Excel : comment le faire et comment l'utiliser

08.12.2021

Système

Microsoft Excel est un tableur omniprésent et pratique.

La large fonctionnalité fait de ce programme le deuxième plus populaire après MS Word parmi tous les programmes bureautiques.

Il est utilisé par des économistes, des comptables, des scientifiques, des étudiants et des représentants d'autres professions qui ont besoin de traiter des données mathématiques.

L'une des fonctions les plus pratiques de ce programme est le filtrage des données. Voyons comment configurer et utiliser les filtres MS Excel.

Où dans les filtres Excel - leurs types

Il est facile de trouver des filtres dans ce programme - vous devez ouvrir le menu principal ou simplement maintenir les touches Ctrl + Maj + L enfoncées.

Fonctions de filtrage de base dans Excel :

sélection par couleur : permet de trier les données par police ou couleur de remplissage,
filtres de texte dans Excel : vous permettent de définir certaines conditions pour les chaînes, par exemple : inférieur à, supérieur à, égal, différent et autres, ainsi que de définir des conditions logiques - et, ou,
filtres numériques : triez par conditions numériques, par exemple, en dessous de la moyenne, les 10 premiers et autres,
manuel : la sélection peut être effectuée selon les critères que vous choisissez vous-même.

Il est facile de les utiliser. Il est nécessaire de sélectionner le tableau et de sélectionner la section avec des filtres dans le menu, puis de spécifier selon quel critère les données seront filtrées.

Comment utiliser le filtre avancé dans Excel - comment le personnaliser

Le filtre standard présente un inconvénient important - pour vous rappeler quels critères de sélection ont été utilisés, vous devez ouvrir le menu. Et d'autant plus qu'elle occasionne des désagréments lorsque plus d'un critère est fixé. De ce point de vue, le filtre avancé est plus pratique, qui est affiché dans un tableau séparé au-dessus des données.

INSTRUCTIONS VIDÉO

Procédure de réglage :

Créez une table avec des données pour un travail ultérieur avec elle. Il ne doit pas contenir de lignes vides.
Créez un tableau avec des conditions de sélection.
Exécutez le filtre avancé.

Regardons un exemple de personnalisation.
Nous avons un tableau avec les colonnes Produit, Quantité et Prix.

Par exemple, vous devez trier les lignes dont les noms de produits commencent par le mot « Ongles ». Plusieurs lignes sont soumises à cette condition.

Le tableau avec les conditions sera situé dans les cellules A1 : A2. Il est important d'indiquer le nom de la colonne où la sélection aura lieu (cellule A1) et le mot lui-même pour la sélection - Clous (cellule A2).

Il est plus pratique de le placer au-dessus des données ou sur le côté. Ce n'est pas non plus interdit en vertu de celui-ci, mais ce n'est pas toujours pratique, car il peut s'avérer nécessaire d'ajouter périodiquement des lignes supplémentaires. Indentez au moins une ligne vide entre les deux tableaux.

Ensuite, vous devez :

sélectionnez l'une des cellules,
ouvrez "Filtre avancé" le long du chemin : Données - Trier et filtrer - Avancé,
vérifiez ce qui est spécifié dans le champ "Plage initiale" - l'ensemble du tableau avec les informations doit y aller,
dans la "Plage de conditions" il faut définir les valeurs des cellules avec la condition de sélection, dans cet exemple il s'agit de la plage A1 : A2.

Après avoir cliqué sur le bouton "OK", les informations requises seront sélectionnées et seules les lignes avec le mot souhaité apparaîtront dans le tableau, dans notre cas, il s'agit de "Ongles". Les numéros de ligne restants deviendront bleus. Pour annuler un filtre spécifié, appuyez simplement sur CTRL + MAJ + L.

Il est également facile de mettre en place un filtrage sur les lignes contenant exactement le mot "Nails", sans tenir compte de la casse. Dans la plage B1 : B2, on placera une colonne avec le nouveau critère de sélection, sans oublier d'indiquer l'en-tête de la colonne dans laquelle le filtrage sera effectué. Dans la cellule B2, vous devez spécifier la formule suivante = "= Clous".

sélectionnez l'une des cellules du tableau,
ouvrir "Filtre avancé",
vérifier que l'ensemble du tableau avec les données est inclus dans la "Gamme d'origine",
dans la « Plage de conditions », indiquez B1 : B2.

Après avoir cliqué sur "OK", les données seront supprimées.

Ce sont les exemples les plus simples. travailler avec des filtres dans excel... Dans la version avancée, il est pratique de définir d'autres conditions de sélection, par exemple, la suppression avec le paramètre "OU", la suppression avec le paramètre "Ongles" et la valeur dans la colonne "Quantité"> 40.

Les informations d'un tableau peuvent être filtrées par une ou plusieurs colonnes. Prenons l'exemple d'un tableau avec les colonnes "Ville", "Mois" et "Ventes".

Exemple 1

Si vous devez filtrer les données par une colonne avec des noms de villes par ordre alphabétique, vous devez sélectionner l'une des cellules de cette colonne, ouvrir Trier et filtrer et sélectionner le paramètre LO. En conséquence, les informations seront affichées en tenant compte de la première lettre du nom de la ville.

Pour obtenir des informations sur le principe inverse, il faut utiliser le paramètre "YA".

Exemple 2

Il est nécessaire de filtrer les informations par mois, et une ville avec un volume de ventes important devrait être plus élevée dans le tableau qu'une ville avec un volume de ventes inférieur. Pour résoudre le problème, vous devez sélectionner le paramètre de tri dans Trier et filtrer. Dans la fenêtre qui apparaît avec les paramètres, spécifiez "Trier par" - "Mois".

Ensuite, vous devez ajouter un deuxième niveau de tri. Pour cela, sélectionnez dans "Trier" - "Ajouter un niveau" et précisez la colonne "Ventes". Dans la colonne « Ordre » des paramètres, spécifiez « Descendant ». Après avoir cliqué sur "OK", la sélection des données s'effectuera selon les paramètres spécifiés.

INSTRUCTIONS VIDÉO

Pourquoi les filtres peuvent ne pas fonctionner dans Excel

Les utilisateurs ont souvent des difficultés à travailler avec un outil tel que des filtres. Ils sont généralement associés à une violation des règles d'utilisation de certains paramètres.

Le problème du filtrage par date est l'un des plus courants. Se produit après le déchargement des données du système comptable sous la forme d'un tableau. Lorsque vous essayez de filtrer des lignes par une colonne contenant des dates, le filtrage n'est pas effectué par date, mais par texte.

Solution:

sélectionnez la colonne avec les dates,
ouvrir l'onglet Excel dans le menu principal,
sélectionnez le bouton "Cellules", dans la liste déroulante sélectionnez le paramètre "Convertir le texte en date".

Les erreurs courantes des utilisateurs lorsqu'ils travaillent avec ce programme devraient également inclure :

l'absence d'en-têtes pour les colonnes (sans eux, le filtrage, le tri et un certain nombre d'autres paramètres importants ne fonctionneront pas),
la présence de lignes et de colonnes vides dans le tableau avec des données (cela confond le système de tri, Excel perçoit les informations comme deux tableaux différents indépendants l'un de l'autre),
placer plusieurs tableaux sur une page (il est plus pratique de placer chaque tableau sur une feuille séparée),
placer des données de même type dans plusieurs colonnes,
placer des données sur plusieurs feuilles, par exemple, par mois ou par années (la quantité de travail peut être immédiatement multipliée par le nombre de feuilles avec des informations).

Et une autre erreur critique qui ne permet pas d'utiliser pleinement les capacités d'Excel est l'utilisation d'un produit sans licence.

Il est impossible de garantir son bon fonctionnement, de plus, des erreurs apparaîtront constamment. Si vous avez l'intention d'utiliser cet outil pour traiter des informations mathématiques de manière continue, achetez la version complète du programme.

De nombreux utilisateurs de PC sont familiers avec une suite de produits pour travailler avec différents types de documents appelés Microsoft Office. Parmi les programmes de cette société se trouve MS Excel. Cet utilitaire est conçu pour fonctionner avec des feuilles de calcul.

Le programme contient le soi-disant filtre avancé dans Excel. Il est conçu pour la facilité d'utilisation et la création de table. Il sera discuté dans notre article. Nous vous expliquerons ci-dessous comment le fabriquer et comment l'utiliser.

Quelle est cette fonction ? La description

Que signifie le filtre avancé dans Excel ? Il s'agit d'une fonction qui permet de différencier les données sélectionnées (par colonnes dans Excel) par rapport aux besoins saisis.

Par exemple, si nous avons une feuille de calcul avec des informations sur tous les élèves de l'école (taille, poids, année, sexe, etc.), alors nous pouvons facilement sélectionner parmi eux, disons, tous les garçons avec une taille de 160 sur 8 classer. Cela peut être fait en utilisant la fonction "Filtre avancé" dans Excel. Nous en parlerons en détail ci-dessous.

Que signifie filtre automatique ?

Quelles sont les caractéristiques du filtre régulier et avancé ? Tout d'abord, parlons du premier. L'autofiltre (normal) est conçu pour différencier les données selon des critères prédéfinis, dont le nombre est déterminé. Il est utilisé lorsque vous avez une petite feuille de calcul, par colonnes et paramètres donnés. Si nous devons définir un nombre illimité de critères de filtrage, nous devons sélectionner Avancé.

Comment le faire correctement ?

Comment faire un filtre avancé dans Excel ? Pour expliquer clairement comment la procédure se déroule et comment elle est effectuée, considérons un exemple.

Instructions de filtrage avancé des feuilles de calcul :

Vous devez créer de l'espace au-dessus de la table principale. Les résultats du filtrage s'y trouveront. Il devrait y avoir suffisamment d'espace pour la table finie. Une ligne supplémentaire est également requise. Il séparera la table filtrée de la table principale.
Copiez l'intégralité de l'en-tête (noms des colonnes) du tableau principal dans la toute première ligne de l'espace libéré.
Saisissez les données requises pour le filtrage dans la colonne requise. Notez que l'entrée doit ressembler à ceci : = "= valeur filtrée".
Vous devez maintenant vous rendre dans la section "Données". Dans la zone de filtrage (icône en forme d'entonnoir) sélectionnez "Avancé" (situé en fin de liste à droite du signe correspondant).
Ensuite, dans la fenêtre contextuelle, vous devez entrer les paramètres du filtre avancé dans Excel. La "Plage de condition" et la "Plage source" sont renseignées automatiquement si la cellule au début de la feuille de calcul a été sélectionnée. Sinon, vous devrez les saisir vous-même.
Cliquez sur D'accord. Vous quitterez les paramètres de filtrage avancés.

Après les étapes effectuées, seuls les enregistrements pour la valeur de délimitation spécifiée resteront dans la table principale. Pour annuler la dernière action (filtrage), vous devez cliquer sur le bouton "Effacer", qui se trouve dans la section "Données".

Travailler avec le filtre avancé dans Excel

Comment utiliser le filtre avancé dans Excel ? Pour comprendre, nous reviendrons sur cette fonction. Supposons que nous ayons une colonne avec des villes dans notre feuille de calcul. Avant cela, nous avons déjà effectué un filtrage étendu pour la ville de Rostov. Maintenant, dans le même document, nous voulons ajouter à ceux reçus pour la ville de Rostov ces enregistrements qui correspondent à la ville de Samara.

Cela nécessite:

Placez les conditions de démarcation (= "- Samara") sous la requête précédente (= "= Rostov").
Appelez le menu des filtres avancés (la section "Données", l'onglet "Filtrage et tri", sélectionnez-y "Avancé").
Cliquez sur OK. Cela fermera le filtrage avancé dans Excel. Un tableau prêt à l'emploi apparaîtra à l'écran, composé d'enregistrements dans lesquels la ville de Samara ou Rostov est indiquée.

Lors du travail, le point très important suivant doit être pris en compte. Tous les critères saisis ne doivent se trouver que dans les colonnes filtrées. Au dessus de la table principale.

Une fois que vous avez appris la méthode de filtrage avancée de base, vous pouvez commencer à explorer toutes les fonctionnalités de cette fonction. Cela fait référence à l'utilisation de formules.

Filtrage avancé. Règles de base pour l'utilisation d'Excel au travail

Conditions d'utilisation:

Les critères de sélection sont les résultats de la formule originale.
Le résultat ne peut être que deux valeurs : "TRUE" ou "FALSE".
Les références absolues indiquent la plage d'origine de la table filtrée.
Les résultats de la formule n'afficheront que les lignes qui reçoivent un total de VRAI. Les valeurs des lignes reçues à la suite de la formule "FAUX" ne seront pas affichées.

À l'aide de ces règles, vous pouvez créer des formules avec des opérandes et des valeurs valides.

Exemple dans Excel 2010

Examinons un exemple de filtre avancé dans Excel 2010 et l'utilisation de formules qu'il contient. Par exemple, délimitons les valeurs d'une colonne avec des données numériques par le résultat de la valeur moyenne (plus ou moins).

Instructions pour travailler avec le filtre avancé dans Excel par la valeur moyenne d'une colonne :

Tout d'abord, vous devez choisir une formule pour la moyenne des données de la colonne. Pour ce faire, vous devez sélectionner la plage de résultats de l'enregistrement initial au dernier. Additionnez-les et divisez par le nombre de lignes (nombre d'enregistrements).
Après cela, sélectionnez l'élément "Avancé" dans la section "Filtrage et tri". Entrez les données requises ici (par plages).
Cliquez ensuite sur OK. Le résultat sera une table qui contient des enregistrements qui ne dépassent pas la moyenne pour une colonne donnée.

Ainsi, vous pouvez utiliser le filtrage avancé de différentes manières. Cela peut être fait en élaborant différents types de formules.

L'essentiel est de ne pas oublier d'écrire les critères de filtrage au dessus du tableau principal dans la colonne par laquelle la délimitation sera faite.

Attention! Lors de l'exécution, les enregistrements n'ayant pas passé le critère de filtrage ne sont pas supprimés. Ils ne se montrent tout simplement pas (ne se montrent pas). Le filtrage avancé est conçu pour que vous puissiez copier le résultat et le coller ailleurs. Après cela, vous devez cliquer sur "Effacer" dans la section "Filtrage et tri". Ensuite, votre table d'origine reviendra à l'écran.

Filtre automatique. Exemple d'utilisation

Un filtre automatique est un outil courant. Elle ne peut être appliquée qu'en spécifiant des paramètres précis. Par exemple, affichez toutes les valeurs du tableau qui dépassent 1000 (< 1000), или показать точные данные, как было рассмотрено в примере с городами.

Il est utilisé beaucoup plus souvent que l'avancé. Cependant, les options de filtrage proposées par Autofilter sont souvent insuffisantes.

Avantages et inconvénients du filtre avancé dans Excel

Étant donné que le filtrage avancé s'ajoute au filtrage régulier, son utilisation présente des avantages et des inconvénients. Nous allons maintenant les considérer.

Avantages du filtrage avancé :

vous pouvez utiliser des formules.

Inconvénients du filtrage avancé :

pour effectuer cette opération, il est nécessaire de consacrer plus de temps au déroulement avec l'introduction de la plage et des données initiales ;
pour utiliser le filtrage avancé à l'aide de formules, vous devez connaître et être capable d'utiliser les règles de la logique et de formuler des équations dans MS Excel.

Malgré la présence d'inconvénients, cette fonction a encore plus de possibilités que le simple filtrage automatique. Bien qu'avec ce dernier, il n'est pas nécessaire de saisir quelque chose de votre propre, à l'exception du critère par lequel l'identification des valeurs sera effectuée.

Filtrage par deux critères distincts. Comment y remédier ?

Le filtrage avancé vous permet d'utiliser différentes formules dans votre feuille de calcul. Si vous devez le différencier d'abord par un critère, puis par un autre (séparément l'un pour l'autre), vous devez :

Créez un emplacement pour saisir le paramètre de filtre. Il est plus pratique de laisser cet espace au dessus du tableau principal et de ne pas oublier de recopier l'entête (noms des colonnes) pour ne pas se perdre dans quelle colonne saisir ce critère.
Saisissez l'indicateur souhaité pour le filtrage. Par exemple, tous les enregistrements dont les valeurs de colonne sont supérieures à 1000 (> 1000).
Allez dans l'onglet "Données". Dans la section "Filtrage et tri", sélectionnez l'élément "Avancé".
Dans la fenêtre qui s'ouvre, précisez les plages des valeurs considérées et la cellule avec la valeur du critère considéré.
Cliquez sur D'accord. Après cela, le tableau filtré par le critère spécifié sera affiché.
Copiez le résultat de la régularisation. Collez le tableau filtré sur le côté quelque part sur la même feuille Excel. Vous pouvez utiliser une autre page.
Sélectionnez "Effacer". Ce bouton est situé dans l'onglet "Données" de la section "Filtrage et tri". Après avoir cliqué dessus, le tableau filtré reviendra à sa forme d'origine. Et vous pouvez travailler avec elle.
Ensuite, vous devez réallouer de l'espace libre pour la table, qui sera filtré.
Ensuite, vous devez copier l'en-tête (noms des colonnes) du champ principal et les déplacer vers la première ligne de l'espace libéré pour la structure filtrée.
Allez dans l'onglet "Données". Dans la section "Filtrage et tri", sélectionnez "Avancé".
Dans la fenêtre qui s'ouvre, sélectionnez la plage d'enregistrements (colonnes) par laquelle le filtrage sera effectué.
Ajoutez l'adresse de la cellule dans laquelle le critère de différenciation est écrit, par exemple, "Ville d'Odessa".
Cliquez sur D'accord. Après cela, le filtrage par la valeur "Odessa" aura lieu.
Copiez le tableau filtré et collez-le soit sur une autre feuille du document, soit sur la même page, mais loin de la principale.
Cliquez à nouveau sur « Effacer ». Tout est prêt. Vous avez maintenant trois tables. La principale, filtrée par une valeur (> 1000), ainsi que celle filtrée par une autre valeur (Odessa).

une petite conclusion

Malgré le fait qu'à première vue cette fonction puisse sembler compliquée, elle est en fait assez simple. Tout ce que vous devez savoir, c'est la gamme de conditions de filtrage avancées dans Excel. Cependant, il ne faut pas oublier ce qui suit. Ne confondez pas les filtres réguliers et étendus. Il est également recommandé d'apprendre les règles de logique et les formules dans Excel, puis travailler avec le filtre devient simple.

Le filtrage des données Excel comprend deux filtres : AutoFilter et Advanced Filter. Supposons que vous ayez un grand ensemble de données, mais que vous ayez besoin d'afficher ou de sélectionner des données concernant une date spécifique, une personne spécifique, etc. Il existe des filtres pour cela. Pour ceux qui rencontrent cet outil pour la première fois, le filtre ne supprime pas, mais masque les enregistrements qui ne répondent pas aux conditions de filtrage que vous spécifiez pour eux.

Le premier est un filtre automatique, destiné aux opérations les plus simples - mettre en évidence les enregistrements avec une valeur spécifique (par exemple, ne mettre en évidence que les enregistrements liés à LeBron James), les données situées dans une certaine plage (ou au-dessus de la moyenne ou des dix premiers) ou des cellules / polices d'une certaine couleur (très pratique d'ailleurs). En conséquence, il est très facile à utiliser. Il vous suffit de sélectionner les données que vous souhaitez voir filtrées. Puis la commande "Données" / "Filtre". Une zone de liste apparaîtra sur chaque cellule du haut du tableau du haut, là il est déjà facile de comprendre chaque commande, c'est simple à apprendre et à expliquer, j'espère, il n'y a pas besoin de plus, seulement les nuances d'utilisation du filtre automatique :

1) Fonctionne uniquement avec une plage de non-rupture. Il ne sera plus possible de filtrer deux listes différentes sur une même feuille.

2) La première ligne du tableau est automatiquement affectée comme en-tête et ne participe pas au filtrage.

3) Vous pouvez appliquer n'importe quel filtre dans différentes colonnes, mais gardez à l'esprit que selon l'ordre dans lequel les filtres sont appliqués, certaines conditions peuvent ne pas être appliquées. les filtres précédents ont déjà masqué les entrées requises. Il n'y a pas de problème ici, ces entrées seraient de toute façon masquées, mais si vous souhaitez utiliser plusieurs ensembles de filtres, il est préférable de commencer par les conditions qui ont le moins d'application.

Application pratique au travail : par exemple, vous travaillez sur cette liste pour trouver une erreur ou vérifier des données. Après avoir appliqué le filtre automatique, vous pouvez parcourir l'ensemble du tableau un par un, en marquant séquentiellement les données déjà consultées. Les boutons « Effacer » et « Réappliquer » déterminent l'apparence du tableau après l'application des conditions. Ensuite, après avoir terminé le travail avec la table, vous pouvez ramener les polices à leur forme d'origine sans modifier les données elles-mêmes. Soit dit en passant, certains sont confus par le fait que tous les enregistrements de la table disparaissent après l'application de conditions. Eh bien, regardez de plus près, vous avez défini des conditions dans lesquelles aucun enregistrement ne remplit ces conditions. Le fait que le tableau soit filtré est que les numéros de ligne du tableau sont surlignés en bleu.

Passons maintenant au filtre avancé. Il diffère du filtre automatique par un réglage plus fin, mais aussi par un choix plus large lors du filtrage des données. En particulier:

1) Définit autant de conditions que nécessaire.

2) Permet de sélectionner des cellules avec des données uniques (non répétitives). Cela est souvent nécessaire lorsque vous travaillez avec des données et l'option fait un excellent travail avec le problème.

3) Vous permet de copier le résultat du filtre dans un endroit séparé sans toucher au tableau principal.

Ainsi, la principale différence dans le travail avec ce filtre est que nous devons d'abord préparer un tableau de conditions. Cela se fait simplement. Les en-têtes du tableau principal sont copiés et insérés à un endroit qui nous convient (je suggère au-dessus du tableau principal). Il devrait y avoir tellement de lignes dans ce tableau pour qu'après avoir déterminé les conditions, vous n'entriez pas dans le tableau principal.

Exemples de conditions :

1) 'L *' - cellules commençant par L

2) '> 5' - données supérieures à 5

Si vous supprimez des lignes de la table filtrée, elles seront supprimées sans emmener de voisins avec vous. Celles. si le tableau est filtré et affiche les lignes 26-29 et 31-25, sélectionner toutes les lignes et les supprimer ne supprimera pas la ligne 30. C'est pratique, personnellement je l'utilise souvent lors de l'écriture de macros. Quel est l'avantage de cela - nous obtenons souvent des tables qui doivent être mises en forme de travail, c'est-à-dire supprimer, par exemple des lignes vides. Ce que nous faisons : filtrer le tableau en affichant uniquement les lignes dont nous n'avons pas besoin, puis supprimer le tableau entier, y compris le titre. Les lignes et les titres inutiles sont supprimés, et le tableau n'a pas d'espaces et est une seule plage. Une ligne d'en-tête peut être ajoutée avec de simples opérations de copie à partir d'une zone pré-préparée. Pourquoi est-ce important lors de l'écriture de macros ? On ne sait pas à partir de quelle ligne les données indésirables commencent et il n'est pas clair de quelle ligne commencer à supprimer, la suppression de la table entière permet de résoudre rapidement ce problème.

Lorsque vous travaillez avec des tableaux, il est souvent nécessaire de "collecter en tas" un certain nombre de données du même type, par exemple toutes les personnes portant le nom de famille "Ivanov", ou tous les prix compris entre 500 et 1 000 roubles. Bien entendu, MS Excel propose de nombreux outils pour de telles opérations, mais tout d'abord, lorsqu'il s'agit de trier et de filtrer, nous pensons à Filtres de données MS Excel.

Comment activer le filtre de données dans MS Excel ?

Pour définir un filtre pour les colonnes du tableau, sélectionnez l'en-tête du tableau avec la souris, puis dans l'onglet "Accueil" sélectionner, et plus loin "Filtre"... Des boutons fléchés vers le bas apparaissent dans le coin droit des cellules d'en-tête de colonne. Cliquez dessus et vous verrez une liste très solide de divers filtres intégrés.

Essayons de comprendre cette variété.

Filtrer par couleur... Fonctionne uniquement si certaines des cellules du tableau sont surlignées en couleur (par exemple, si les cellules avec des revenus sont marquées en vert et avec des dépenses en rouge, elles peuvent être très facilement filtrées par couleur).
Le résultat de l'application du filtre par couleur

Filtres numériques et de texte MS Excel
Filtres de nombres/textes (selon que la colonne contient des nombres ou du texte). Le groupe de filtres le plus grand et le plus polyvalent. Lorsque vous sélectionnez un élément, l'outil "Custom AutoFilter" se lance, offrant des options de filtrage pour tous les goûts et toutes les couleurs. Pour les comprendre, c'est assez simple, il suffit de regarder les exemples ci-dessous :
Filtrez toutes les valeurs du tableau qui sont inférieures à 160.

Filtrer toutes les valeurs du tableau avec des prix inférieurs à 160 ET en même temps plus de 80

Filtrez toutes les valeurs du tableau qui ne sont pas égales à 180 et 200
Filtre simple... Il n'y a rien à penser - décochez les cases devant les données à filtrer et cliquez sur "OK". En conséquence, seules les informations nécessaires restent sur la feuille.
Le filtre le plus simple dans MS Excel - ce que vous choisissez est filtré

Comment désactiver le filtre dans la feuille de calcul MS Excel ?

Réinitialiser le filtre dans MS Excel est encore plus simple que de le définir - cliquez à nouveau sur la flèche à droite des cellules d'en-tête du tableau et dans le menu contextuel qui apparaît, sélectionnez "Supprimer le filtre de la colonne".

Si vous souhaitez supprimer complètement le filtre du tableau, répétez simplement les mêmes étapes qu'au tout début - dans l'onglet "Accueil" choisir Outil de tri et de filtrage où appuyez à nouveau "Filtre".

Félicitations, les filtres de données dans la feuille de calcul Excel sont maintenant complètement désactivés.

Le principal inconvénient de la norme ( ) - c'est le manque d'informations visuelles sur le filtre actuellement appliqué : il faut à chaque fois aller dans le menu filtre pour se souvenir des critères de sélection des enregistrements. Ceci est particulièrement gênant lorsque plusieurs critères sont appliqués. Le filtre étendu n'a pas cet inconvénient - tous les critères sont placés dans un tableau séparé au-dessus des enregistrements filtrés.

Algorithme de création Filtre avancé Facile:

Créez une table à laquelle le filtre sera appliqué (table source) ;
Nous créons un tableau avec des critères (avec des conditions de sélection) ;
Lancer Filtre avancé.

Laissez entrer la gamme UNE 7 : C 83 il y a une table source avec une liste de produits contenant des champs (colonnes) Produit, Quantité et Prix(voir fichier exemple). Le tableau ne doit pas contenir de lignes et de colonnes vides, sinon Filtre avancé(et normal) ne fonctionnera pas correctement.

Tâche 1 (commence ...)

Mettons en place un filtre pour sélectionner les lignes qui contiennent des valeurs dans le nom du produit départ du mot Ongles... Cette condition de sélection est satisfaite par les lignes avec des marchandises clous 20 mm, Clous 10 mm, Clous 10 mm et Ongles.

UNE 1 : A2 A2 indiquons le mot Ongles.

Noter: Structure des critères de Filtre avancé est clairement défini et coïncide avec la structure des critères pour les fonctions BDSUMM (), BSCHET (), etc.

Généralement, les critères Filtre avancé placés au-dessus de la table à laquelle le filtre est appliqué, mais ils peuvent également être placés sur le côté de la table. Evitez de placer un tableau avec des critères sous le tableau d'origine, bien que ce ne soit pas interdit, mais ce n'est pas toujours pratique. de nouvelles lignes peuvent être ajoutées à la table d'origine.

ATTENTION!
Assurez-vous qu'il y a au moins une ligne vide entre le tableau avec les valeurs des conditions de sélection et le tableau d'origine (cela facilitera le travail avec Filtre avancé).

Filtre avancé:

appeler Filtre avancé();
dans le champ Gamme d'origine UNE 7 : C 83 );
dans le champ Gamme de conditions UNE1 : A2 .

Si vous le souhaitez, les lignes sélectionnées peuvent être copiées dans une autre table en plaçant le commutateur sur la position Copiez le résultat dans un autre emplacement... Mais nous ne le ferons pas ici.

Appuyez sur le bouton OK et le filtre sera appliqué - seules les lignes contenant le nom dans la colonne Produit resteront dans le tableau clous 20 mm, Clous 10 mm, Clous 50 mm et Ongles... Le reste des lignes sera masqué.

Les numéros de ligne sélectionnés seront surlignés en bleu.

Pour annuler l'action de filtrage, sélectionnez n'importe quelle cellule du tableau et appuyez sur CTRL + MAJ + L(le titre sera appliqué, et l'action Filtre avancé sera annulé) ou appuyez sur la touche menu Dégager ().

Problème 2 (correspond exactement)

Mettons en place un filtre pour sélectionner les lignes qui ont dans la colonne Produit exactement contient le mot Ongles... Cette condition de sélection n'est remplie que par les lignes avec des marchandises. ongles et Ongles(non pris en compte). Les valeurs clous 20 mm, Clous 10 mm, Clous 50 mm ne sera pas pris en compte.

Placer la plaque avec la condition de sélection dans la plage B1 : B2 ... La plaque doit également contenir le nom de l'en-tête de la colonne par laquelle la sélection sera effectuée. Comme critère dans une cellule B2 nous indiquons la formule = "= Ongles".

Tout est maintenant prêt à fonctionner avec Filtre avancé:

sélectionnez n'importe quelle cellule du tableau (ce n'est pas nécessaire, mais cela accélérera le remplissage des paramètres de filtre);
appeler Filtre avancé ( Données / Trier et filtrer / Avancé);
dans le champ Gamme d'origine assurez-vous d'inclure une plage de cellules de tableau avec des titres ( UNE 7 : C 83 );
dans le champ Gamme de conditions indiquer les cellules contenant le tableau avec le critère, c'est-à-dire intervalle B1: B2 .
Cliquez sur OK

Appliquer Filtre avancé avec des critères aussi simples, il n'y a pas de sens particulier, puisque s'acquitte facilement de ces tâches Filtre automatique... Considérons des tâches de filtrage plus complexes.

Si vous ne spécifiez pas = "= Nails" comme critère, mais simplement Ongles, alors tous les enregistrements contenant des noms seront affichés départ du mot Ongles ( Ongles 80mm, Ongles2). Pour afficher des lignes avec un produit, contenant sur parole ongles, Par example, Nouveaux ongles, vous devez spécifier = "= * Clous" comme critère ou simplement * Ongles où* est et signifie n'importe quelle séquence de caractères.

Problème 3 (condition OU pour une colonne)

Mettons en place un filtre pour sélectionner les lignes qui ont une valeur commençant par le mot dans la colonne Produit OnglesOU Fond d'écran.

Les critères de sélection dans ce cas doivent être placés sous l'en-tête de colonne approprié ( Produit) et doivent être situés l'un en dessous de l'autre dans la même colonne (voir figure ci-dessous). Placez la table dans la gamme C1 : C3 .

La fenêtre avec les paramètres de filtrage avancés et le tableau avec les données filtrées ressembleront à ceci.

Après avoir cliqué sur OK, tous les enregistrements contenant dans la colonne Produit des produits Ongles OU Fond d'écran.

Problème 4 (condition I)

exactement contenir dans la colonne Produit des produits Ongles, et dans la colonne Quantité valeur> 40. Dans ce cas, les critères de sélection doivent être placés sous les rubriques appropriées (Produit et Quantité) et doivent être situés sur une même ligne. Les conditions de sélection doivent être écrites dans un format spécial : ="= Ongles" et = "> 40". Placer la plaque avec la condition de sélection dans la plage E1 : F2 .

Après avoir cliqué sur le bouton OK, tous les enregistrements contenus dans la colonne seront affichés Produit des produits Ongles avec quantité> 40.

CONSEILS: Lors de la modification des critères de sélection, il est préférable de créer un tableau avec les critères à chaque fois et après avoir appelé le filtre, il suffit de modifier le lien vers eux.

Noter: Si vous deviez effacer les paramètres du filtre avancé ( Données / Trier et filtrer / Effacer), puis avant d'appeler le filtre, sélectionnez n'importe quelle cellule du tableau - EXCEL insèrera automatiquement un lien vers la plage occupée par le tableau (s'il y a des lignes vides dans le tableau, un lien ne sera pas inséré vers le tableau entier, mais uniquement à la première ligne vide).

Problème 5 (condition OU pour différentes colonnes)

Les tâches précédentes pourraient, si vous le souhaitez, être résolues de la manière habituelle. Le même problème ne peut pas être résolu par un filtre conventionnel.

Nous sélectionnons uniquement les lignes du tableau qui exactement contenir dans la colonne Produit des produits Ongles, OU qui dans la colonne Quantité contenir une valeur > 40. Les critères de sélection dans ce cas doivent être placés sous les rubriques appropriées (Produit et Quantité) et doivent être situés sur des lignes différentes... Les conditions de sélection doivent être écrites dans un format spécial : = "> 40" et ="= Ongles". Placer la plaque avec la condition de sélection dans la plage E4 : F6 .

Après avoir cliqué sur le bouton OK, les enregistrements seront affichés contenant dans la colonne Produit des produits Ongles OU valeur > 40 (pour tout produit).

Tâche 6 (Conditions de sélection créées à la suite de l'application de la formule)

Vrai pouvoir Filtre avancé se manifeste lorsqu'il est utilisé comme conditions de sélection pour les formules.

Il existe deux options pour spécifier les conditions de sélection de ligne :

saisir directement des valeurs pour le critère (voir tâches ci-dessus);
former un critère basé sur les résultats de la formule.

Considérez les critères donnés par la formule. La formule spécifiée comme critère de sélection doit renvoyer un résultat VRAI ou FAUX.

Par exemple, affichons des lignes contenant un produit qui n'apparaît qu'une seule fois dans le tableau. Pour cela, entrez dans la cellule H2 formule = COUNTIF (Feuille1! $ A $ 8: $ A $ 83; A8) = 1 et en H1 au lieu de diriger nous allons saisir un texte explicatif, par exemple, Valeurs non répétitives... En vigueur Filtre avancé en spécifiant comme plage de conditions de cellule H1 : H2 .

Notez que la plage de recherche des valeurs est saisie à l'aide et que le critère de la fonction COUNTIF () est saisi avec une référence relative. Ceci est nécessaire car lors de l'application Filtre avancé EXCEL verra que A8 est une référence relative et naviguera dans la colonne Produit un enregistrement à la fois et renverra VRAI ou FAUX. Si la valeur de retour est TRUE, la ligne correspondante dans le tableau sera affichée. Si la valeur de retour est FALSE, la chaîne ne sera pas affichée après l'application du filtre.

Exemples d'autres formules du fichier d'exemple :

Affiche les lignes avec des prix supérieurs au 3e prix le plus élevé du tableau. = C8> GRAND (8 $ CA : 83,5 $ CA) Cet exemple démontre clairement la sournoiserie de la fonction LARGE (). Si vous triez la colonne AVEC (prix), on obtient : 750 ; 700 ; 700 ; 700 ; 620, 620, 160, ... Dans la compréhension humaine, "3ème prix le plus élevé" correspond à 620, et dans la compréhension de la fonction LE PLUS GRAND () - 700 ... De ce fait, ce ne sont pas 4 lignes qui seront affichées, mais une seule (750) ;
Sortie de chaîne sensible au REGISTRE = CONSEIL ("clous"; A8)... Seules les lignes seront affichées là où l'article ongles introduit en utilisant des lettres minuscules;
Affichage des lignes avec un prix supérieur à la moyenne = C8> MOYEN (8 $ CA : 83 $ CA);

ATTENTION!
Application Filtre avancé annule le filtre appliqué à la table ( Données / Trier et filtrer / Filtrer).

Problème 7 (Les conditions de sélection contiennent des formules et des critères usuels)

Considérons maintenant une autre table de exemple de fichier sur la feuille de calcul Tâche 7.

Dans la colonne Produit le nom du produit est indiqué, et dans la colonne Type d'élément- son genre.

La tâche consiste à afficher des marchandises pour un type de produit donné avec un prix inférieur à la moyenne. C'est-à-dire que nous avons 3 critères : le premier critère définit le produit, le second - son type, et le troisième critère (sous la forme d'une formule) définit le prix en dessous de la moyenne.

Placez les critères aux lignes 6 et 7. Entrez le produit et le type de produit requis. Pour un type de produit donné, calculons la moyenne et affichons-la pour plus de clarté dans une cellule distincte F7. En principe, la formule peut être saisie directement dans la formule de test de la cellule C7.

2 produits sur 4 (d'un type de produit donné) seront affichés.

Tâche 7.1. (Les 2 valeurs sur la même ligne sont-elles les mêmes ?)

Il existe un tableau dans lequel l'année de fabrication et l'année d'achat de la voiture sont indiquées.

Vous souhaitez afficher uniquement les lignes dans lesquelles l'année d'émission coïncide avec l'année d'achat. Cela peut être fait en utilisant la formule élémentaire = B10 = C10.

Problème 8 (Un symbole est-il un nombre ?)

Disons que nous avons un tableau répertoriant les différents types d'ongles.

Vous souhaitez filtrer uniquement les lignes qui ont ongles de 1 pouce, ongles de 2 pouces etc. des produits Clous inoxydables, Clous chromés etc. ne doit pas être filtré.

La façon la plus simple de le faire est de définir la condition selon laquelle un nombre doit être placé après le mot Nails en tant que filtre. Cela peut être fait en utilisant la formule = ISNUMBER (- MID (A11; DLSTR ($ A $ 8) +2; 1))

La formule découpe dans le nom du produit 1 caractère après le mot Nails (espace compris). Si ce caractère est un nombre (chiffre), alors la formule renvoie VRAI et la chaîne est sortie, sinon la chaîne n'est pas sortie. La colonne F montre comment fonctionne la formule, c'est-à-dire il peut être testé avant le lancement Filtre avancé.