Si vous lisez jusqu'au bout, apprenez à utiliser de tels fonctionnalité utile Excel, comme filtre. Maintenant sur un exemple très réel, je montrerai ce que c'est filtres Excel Et comment gagner du temps lorsque vous travaillez avec de grandes tables. C'est assez simple. À la fin de l'article, vous pouvez télécharger la table, sur l'exemple dont je envisage ici de travailler avec Excel Filtres.
Pourquoi avez-vous besoin de filtres dans des tables Excel
Et puis pour être capable de vite Sélectionnez uniquement les données dont vous avez besoin, cache Inutile cordes Les tables. Ainsi, le filtre permet sans délétion Les cordes de la table Excel se cachent temporairement.
Caché à l'aide du filtre des rangées de table ne disparaît pas. Il peut être introduit classiquement que leur hauteur devient égale à zéro (j'ai déjà raconté au changement de la hauteur des rangées et de la largeur des colonnes). Ainsi, les lignes restantes qui ne sont pas cachées par le filtre, comme si "collés". Ce qui est obtenu en conséquence est une table avec un filtre d'extension.
Les bases de données sont très pratiques pour stocker des informations, mais nous les créons afin de recevoir un certificat pour nous lorsque un besoin similaire se pose.
Par exemple, nous devons planifier des trains de chemin de fer qui quittent Moscou vendredi après quatre heures de l'après-midi, etc.
Rechercher information nécessaire Il est réalisé par la sélection de lignes qui satisfont à certains critères. Dans la plupart des cas, le critère de sélection est l'égalité du contenu de la cellule à une certaine valeur.
Outre la comparaison sur l'égalité, d'autres opérations de comparaison peuvent être utilisées lors de la sélection des enregistrements. Par exemple, plus, moins, plus ou égal, moins ou égal. L'utilisation de ces opérations vous permet de formuler un critère de demande moins strictement. Par exemple, si vous souhaitez trouver des informations sur une personne, le nom de famille de qui commence par "ku", puis comme critère que vous pouvez utiliser la règle "Le contenu de la cellule le nom de famille plus ou égal aux cellules et au contenu de le nom de famille est inférieur à L ".
Le processus d'échantillon de la table de lignes satisfaisant un critère spécifique (règle) est appelé filtrage et le critère (condition de sélection de chaîne) est un filtre.
- Afin de sélectionner une chaîne d'une table qui répond à une certaine condition, vous avez besoin:
- 1. Allouez la chaîne de table dans laquelle il existe des noms de colonne, ouvrez l'onglet Home -\u003e Édition et dans la liste Trier et filtrant, sélectionnez la commande Filtrer. En conséquence, les listes de distribution apparaîtront à côté du nom des colonnes de la table.
- 2. Ouvrez la liste en cliquant sur l'en-tête de la colonne et, dans la fenêtre Ouvrir, réinitialisez d'abord le commutateur (allouer tout), puis sélectionnez la valeur qui doit être utilisée comme critère de sélection.
À la suite de l'exécution des actions ci-dessus sur l'écran, seules les lignes qui satisfont à la condition de la demande resteront.
En tant que critère de sélection de colonne, qui contient des valeurs numériques, vous pouvez spécifier la plage dans laquelle les valeurs de cellule doivent être. Par exemple, pour obtenir une liste de dépenses, la somme comprise entre 100 p. Jusqu'à 1000 p., Il est nécessaire d'ouvrir la fenêtre de filtre pour la quantité de colonne, de sélectionner des filtres numériques -\u003e entre et dans la fenêtre de filtre automatique utilisateur qui apparaît, entrez les limites de la plage.
Vous pouvez afficher des informations sur un / plusieurs paramètres à l'aide de filtrage des données pour exceller.
Deux outils sont destinés à cet effet: autofiltre et filtre avancé. Ils ne suppriment pas, mais cachent des données qui ne conviennent pas à la condition. Le filtre automatique effectue les opérations les plus simples. Un filtre étendu a beaucoup plus d'opportunités.
Filtre automatique et filtre avancé dans Excel
Disponible table simple, non formaté et non annoncé par la liste. Vous pouvez activer le filtre automatique dans le menu principal.
Si vous formatez la plage de données sous forme de table ou déclarez une liste, le filtre automatique sera ajouté immédiatement.
Pour utiliser l'autofiltre simplement: vous devez mettre en évidence l'enregistrement avec la valeur souhaitée. Par exemple, pour afficher des livraisons au stockage №4. Nous mettons l'oiseau en face de la condition de filtrage correspondante:
Voir immédiatement le résultat:
Caractéristiques de l'outil:
- Le filtre automatique ne fonctionne que dans une plage inséparable. Différentes tables sur une feuille ne sont pas filtrées. Même s'ils ont les mêmes données.
- L'outil perçoit la ligne supérieure sous forme d'en-têtes de colonne - ces valeurs ne sont pas incluses dans le filtre.
- 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 prochain filtre.
Le filtre étendu a beaucoup plus de fonctionnalités:
- Vous pouvez demander autant de conditions de filtrage au besoin.
- Critères de sélection de données - en vue.
- En utilisant le filtre étendu, l'utilisateur trouve facilement significations uniques Dans une matrice multiligne.
Comment faire un filtre avancé dans Excel
Exemple prêt - Comment utiliser un filtre étendu dans Excel:
Seules les lignes contenant l'importance de "Moscou" sont restées dans la table source. Pour annuler le filtrage, vous devez cliquer sur le bouton "Effacer" dans la section "Trier et filtre".
Comment utiliser un filtre étendu dans Excel
Considérez l'utilisation d'un filtre étendu dans Excel afin de sélectionner des lignes contenant les mots «Moscou» ou «Ryazan». Les conditions de filtration doivent être dans une colonne. Dans notre exemple, l'autre.
Remplissez le menu du filtre étendu:
Nous obtenons une table avec sélectionnée par critère spécifié Lignes:
Nous allons prendre la sélection des lignes qui dans la colonne "Store" contiennent la valeur "№1" et dans la colonne COÛT - "\u003e 1 000 000 r.". Les critères de filtrage doivent être dans la plaque de colonne correspondante pour les conditions. Sur une ligne.
Remplissez les paramètres de filtrage. Cliquez sur OK.
Devons durer dans la table seulement ces lignes qui dans la colonne "Région" contiennent le mot "Ryazan" ou dans la colonne "Coût" - la valeur "\u003e 10 000 000 r.". Étant donné que les critères de sélection appartiennent à différentes colonnes, placez-les sur différentes lignes sous les titres appropriés.
Appliquez l'outil "Filtre avancé":
Cet outil est capable de travailler avec des formules, ce qui permet à l'utilisateur de résoudre presque toutes les tâches lors de la sélection des valeurs des tableaux.
Règles fondamentales:
- Le résultat de la formule est le critère de sélection.
- La formule enregistrée renvoie le résultat de la vérité ou de la fausse.
- La plage initiale est indiquée par des références absolues et le critère de sélection (sous forme de formule) utilise des relatifs.
- Si la valeur de la vérité est renvoyée, la chaîne sera affichée après l'application du filtre. Lie - non.
Afficher les cordes contenant le montant supérieur à la moyenne. Pour ce faire, de côté de la plaque avec des critères (dans la cellule I1), nous introduisons le nom "Le plus grand nombre". Ci-dessous - formule. Nous utilisons la fonction du SRVNA.
Sélectionnez une cellule dans la plage source et appelez le "filtre avancé". En tant que critère de sélection, nous indiquons I1: I2 (liens relatifs!).
Le tableau ne reste que ces lignes où les valeurs dans la colonne "nombre" au-dessus de la moyenne.
Pour laisser la table uniquement des lignes non répétées, dans la fenêtre "filtre étendu", mettez un oiseau devant "enregistrements uniques uniquement".
Cliquez sur OK. Les lignes répétitives seront cachées. Seules les entrées uniques resteront sur la feuille.
Parfois, les tables d'Excel contiennent une quantité de données assez importante, il peut s'agir, par exemple une liste des achetés par an fournitures. Et vous devez trouver entre eux que les données relatives à votre division. Comment faire?
Afin de choisir seulement une partie qui répond à votre condition de la masse totale des enregistrements, vous pouvez utiliser l'outil appelé filtre. Pour installer le filtre, mettez en surbrillance les en-têtes dans l'en-tête de la table ou dans l'ensemble de la table, puis sur l'onglet. le principal Cliquez sur le bouton Tri et filtre Et dans le menu qui apparaît, sélectionnez l'élément Filtre. Dans le coin inférieur droit des cellules avec les en-têtes des colonnes de table apparaissent des boutons avec une flèche directionnelle vers le bas, comme illustré à la Fig. 5.4.
Si vous cliquez sur ce bouton, un menu apparaîtra dans lequel vous pouvez spécifier le sens de tri de cette colonne, ainsi que les conditions de sélection (Fig. 5.5).
Si vous, par exemple, vous devez choisir uniquement dans la liste des noms commençant par la lettre avec, cliquez sur le bouton de la colonne Nom, dans le menu qui apparaît, ouvrez le sous-menu de filtres texte et sélectionnez l'élément. commencer avec. La fenêtre montrée à la Fig. 5.6. Sur le terrain à droite des mots commence par entrer la lettre C. Vous pouvez entrer dans une lettre, mais plusieurs. En conséquence, seuls les noms qui commencent par la combinaison de touches spécifiée seront sélectionnés et les entrées de table restantes seront masquées à partir de l'écran (Fig. 5.7).
Le bouton de la cellule avec la position de colonne, selon lequel le filtre est installé, change et accepte la vue correspondante. Si vous cliquez sur le bouton dans une colonne avec des données numériques, au lieu de l'élément. Filtres de texte L'élément de menu apparaît Filtres numériques. Si vous sélectionnez cet élément, vous pouvez spécifier une plage de valeurs de chiffres pour les entrées sélectionnées (Fig. 5.8). Vous pouvez sélectionner tous les enregistrements plus que la valeur que vous avez spécifiée ou moins.
Pour l'exemple considéré par nous (voir fig. 5.4), choisissez une liste de personnes nées après 1973. Pour ce faire, cliquez sur le bouton de la cellule E1 (année de naissance), dans le menu qui apparaît, révéler le sous-menu. Filtres numériques, comme le montre la Fig. 5.8, et choisissez un élément plus. La fenêtre montrée à la Fig. 5.9 Sur le terrain à droite du mot, entrez la valeur de 1973 et cliquez sur le bouton OK. En conséquence, seules les personnes nées après 1973 resteront sur la liste (Fig. 5.10).
Le filtre peut être changé à tout moment ou enlever du tout. Pour ce faire, cliquez sur le bouton et dans le menu qui apparaît, sélectionnez Supprimer le filtre de colonne.
Filtration données Excel Comprend deux filtres: autofiltre et filtre avancé. Supposons que vous ayez un gros jeu de données, mais à partir de tout le tableau, vous devez regarder ou mettre en évidence les données appartenant à une certaine date, personne spécifique, etc. Pour cela, il y a des filtres. Pour ceux qui sont d'abord confrontés à cet outil - le filtre ne supprime pas, mais cache les enregistrements qui ne répondent pas aux conditions de filtrage que vous les spécifiez.
Le premier - Autofilter, est destiné aux opérations les plus simples - en surbrillance des enregistrements avec une valeur spécifique (par exemple, seule l'attribution d'enregistrements relatifs à la James Le Bron), des données menées dans une plage spécifique (ou au-dessus de la moyenne ou de la première ou des dix premières). ou des cellules / polices d'une couleur spécifique (d'ailleurs, il est très pratique). En conséquence, il est très simple. Il vous suffit de mettre en évidence les données que vous souhaitez voir filtrer. Ensuite, la commande "Data" / "filtre". Sur chaque cellule supérieure de la table supérieure, la case à cocher Liste apparaîtra, il est déjà facile de gérer chaque équipe, il n'est pas nécessaire de maîtriser et d'expliquer, j'espère que vous n'avez pas besoin de continuer, seulement des nuances d'utilisation de la mise au point automatique:
1) ne fonctionne que avec une gamme inséparable. Deux liste différente Sur une feuille, il ne sera pas possible de filtrer.
2) La ligne supérieure de la table est automatiquement attribuée au titre et ne participe pas à la filtration.
3) Vous pouvez appliquer des filtres dans différentes colonnes, mais gardez à l'esprit que, en fonction de l'ordre de l'utilisation des filtres, certaines conditions peuvent ne pas être appliquées, car Les filtres précédents ont déjà caché les enregistrements nécessaires. Il n'y a pas de problème ici, ces enregistrements auraient été masqués, mais si vous souhaitez utiliser plusieurs séries de filtres, il est préférable de commencer par ces conditions qui ont la plus petite application.
Application pratique dans le travail: Par exemple, vous travaillez sur cette liste pour trouver une erreur ou vérifier les données. Après avoir appliqué l'autofiltre, vous pouvez alternativement passer la table entière, notant séquentiellement les données déjà visualisées. Les boutons "Clear" et "Appliquer répété" sont déterminés par la vue du tableau après l'application des conditions. Ensuite, après la fin du fonctionnement de la table, vous pouvez renvoyer les polices à la vue initiale sans changer les données elles-mêmes. Au fait, certains confondent la disparition de toutes les entrées de la table après avoir appliqué toutes les conditions. Eh bien, regardez attentif, vous avez demandé aux conditions dans lesquelles il n'y a pas d'entrées qui satisfont à ces conditions. Le fait que la table soit filtrée - mettant en évidence les numéros des lignes de table.
Maintenant, allez à un filtre étendu. Il est différent de l'autofiltrter plus ensemble mince, mais aussi un grand choix lors du filtrage des données. En particulier:
1) Spécifie autant de conditions que nécessaire.
2) Permet aux cellules avec des données uniques (non répétitives). Il est souvent nécessaire de travailler avec les données et l'option s'adapte parfaitement au problème.
3) Vous permet de copier le résultat du filtre dans un endroit séparé et non une touche de la matrice principale.
Donc, la principale différence de travail avec ce filtre est que nous préparons une table de conditions. C'est fait simplement. Les titres de la table principale sont copiés et insérés à la place commode pour nous (je propose au-dessus de la table principale). Cette table doit être autant de lignes pour que, après avoir déterminé les conditions, ne montez pas sur la table principale.
Exemples:
1) 'l *' - cellules commençant par l
2) '\u003e 5' - données plus de 5
Si vous supprimez la ligne de la table filtrée, ils seront supprimés sans créer les voisins. Ceux. Si la table est filtrée et affiche les lignes de 26-29 et 31-25, affectant toutes les lignes et leur retrait n'entraînera pas de lignes 30. Il est pratique, je l'utilise personnellement lorsque vous écrivez des macros. Quel avantage il donne - souvent nous obtenons des tables qui doivent être apportées dans la forme de travail, c'est-à-dire Supprimer, par exemple, des lignes vides. Ce que nous faisons: Appliquez le filtre à la table, affichant uniquement les lignes que nous n'avons pas besoin, puis retirez la table entière, y compris le titre. Les chaînes et le titre inutiles sont supprimés, tandis que la table n'a pas d'espaces et est une seule gamme. Et la chaîne d'en-tête peut ajouter des opérations de copie simples à partir de la zone téléchargée. Pourquoi est-il important lors de l'écriture de macros? On ne sait pas que la chaîne commence les données indésirables et il n'est pas clair que la chaîne de démarrage de la suppression, la suppression de la table entière permet de résoudre rapidement ce problème.
