Comment filtrer des données dans Excel

J'ai récemment écrit un article sur l'utilisation des fonctions de synthèse dans Excel pour résumer facilement de grandes quantités de données, mais cet article tenait compte de toutes les données de la feuille de calcul. Et si vous voulez seulement regarder un sous-ensemble de données et résumer le sous-ensemble de données?

Dans Excel, vous pouvez créer des filtres sur les colonnes qui masqueront les lignes qui ne correspondent pas à votre filtre. En outre, vous pouvez également utiliser des fonctions spéciales dans Excel pour résumer les données en utilisant uniquement les données filtrées..

Dans cet article, je vais vous expliquer les étapes de la création de filtres dans Excel et de l'utilisation de fonctions intégrées pour résumer les données filtrées..

Créer des filtres simples dans Excel

Dans Excel, vous pouvez créer des filtres simples et des filtres complexes. Commençons par des filtres simples. Lorsque vous travaillez avec des filtres, vous devriez toujours avoir une ligne en haut qui est utilisée pour les étiquettes. Ce n'est pas obligatoire d'avoir cette ligne, mais cela facilite un peu le travail avec les filtres.

Ci-dessus, j'ai quelques fausses données et je veux créer un filtre sur le Ville colonne. Dans Excel, c'est très facile à faire. Allez-y et cliquez sur le Les données onglet dans le ruban, puis cliquez sur le Filtre bouton. Il n'est pas nécessaire de sélectionner les données sur la feuille ou de cliquer dans la première ligne.

Lorsque vous cliquez sur Filtre, chaque colonne de la première ligne se voit automatiquement dotée d'un petit bouton déroulant à l'extrême droite..

Maintenant, allez-y et cliquez sur la flèche de la liste déroulante Ville. Vous verrez quelques options que je vais expliquer ci-dessous..

En haut, vous pouvez rapidement trier toutes les lignes en fonction des valeurs de la colonne Ville. Notez que lorsque vous triez les données, la ligne entière sera déplacée, pas uniquement les valeurs de la colonne Ville. Cela garantira que vos données restent intactes comme avant..

En outre, il est judicieux d'ajouter une colonne au tout début appelée ID et de la numéroter de un à autant de lignes que vous avez dans votre feuille de calcul. De cette façon, vous pouvez toujours trier par colonne d'ID et récupérer vos données dans l'ordre où elles étaient à l'origine, si cela vous importe.

Comme vous pouvez le constater, toutes les données de la feuille de calcul sont maintenant triées en fonction des valeurs de la colonne Ville. Jusqu'à présent, aucune ligne n'est cachée. Examinons maintenant les cases à cocher situées au bas de la boîte de dialogue du filtre. Dans mon exemple, je n'ai que trois valeurs uniques dans la colonne Ville et ces trois valeurs apparaissent dans la liste..

Je suis allé de l'avant et décoché deux villes et laissé une cochée. Maintenant, je n'ai que 8 lignes de données montrant et le reste est caché. Vous pouvez facilement savoir que vous consultez des données filtrées si vous vérifiez les numéros de ligne à l'extrême gauche. En fonction du nombre de lignes masquées, vous verrez quelques lignes horizontales supplémentaires et la couleur des chiffres sera bleue..

Supposons maintenant que je souhaite filtrer une deuxième colonne afin de réduire davantage le nombre de résultats. Dans la colonne C, j'ai le nombre total de membres dans chaque famille et je veux voir uniquement les résultats pour les familles de plus de deux membres..

Continuez et cliquez sur la flèche de la colonne C dans la liste déroulante pour afficher les mêmes cases à cocher pour chaque valeur unique de la colonne. Cependant, dans ce cas, nous voulons cliquer sur Nombre Filtres puis cliquez sur Plus grand que. Comme vous pouvez le constater, de nombreuses autres options sont également disponibles..

Une nouvelle boîte de dialogue apparaîtra et vous pourrez saisir ici la valeur du filtre. Vous pouvez également ajouter plusieurs critères avec une fonction AND ou OR. Vous pouvez par exemple dire que vous voulez des lignes dont la valeur est supérieure à 2 et non égale à 5, par exemple.

Maintenant, je n'ai plus que 5 lignes de données: des familles uniquement de la Nouvelle-Orléans et comptant 3 membres ou plus. Assez facile? Notez que vous pouvez facilement effacer un filtre sur une colonne en cliquant sur le menu déroulant, puis sur le bouton Effacer le filtre de "Nom de colonne" lien.

Donc, c'est à peu près tout pour les filtres simples dans Excel. Ils sont très faciles à utiliser et les résultats sont assez simples. Regardons maintenant les filtres complexes en utilisant le Avancée filtres de dialogue.

Créer des filtres avancés dans Excel

Si vous souhaitez créer des filtres plus avancés, vous devez utiliser le Avancée filtre de dialogue. Par exemple, supposons que je souhaite voir toutes les familles vivant à la Nouvelle-Orléans avec plus de 2 membres dans leur famille. OU toutes les familles de Clarksville ayant plus de 3 membres dans leur famille ET seulement ceux avec un .EDU adresse e-mail de fin. Maintenant, vous ne pouvez pas faire cela avec un simple filtre.

Pour ce faire, nous devons configurer la feuille Excel un peu différemment. Continuez et insérez quelques lignes au-dessus de votre ensemble de données et copiez les étiquettes de titre exactement dans la première ligne, comme indiqué ci-dessous..

Voici comment fonctionnent les filtres avancés. Vous devez d’abord taper vos critères dans les colonnes du haut, puis cliquer sur le bouton Avancée bouton sous Trier et filtrer sur le Les données languette.

Alors, que pouvons-nous taper exactement dans ces cellules? OK, commençons par notre exemple. Nous voulons seulement voir les données de la Nouvelle-Orléans ou de Clarksville, alors saisissons-les dans les cellules E2 et E3..

Lorsque vous tapez des valeurs sur différentes lignes, cela signifie OU. Nous souhaitons maintenant des familles de plus de deux membres de la Nouvelle-Orléans et des familles de plus de trois membres de Clarksville. Pour ce faire, tapez > 2 en C2 et > 3 en C3.

Puisque> 2 et New Orleans sont sur la même ligne, ce sera un opérateur AND. Il en va de même pour la ligne 3 ci-dessus. Enfin, nous ne voulons que les familles avec l'adresse e-mail de fin .EDU. Pour ce faire, il suffit de taper * .edu dans les deux D2 et D3. Le symbole * signifie n'importe quel nombre de caractères.

Une fois que vous avez fait cela, cliquez n’importe où dans votre jeu de données, puis cliquez sur le bouton Avancée bouton. le Liste RangéeLe champ déterminera automatiquement votre jeu de données puisque vous avez cliqué dessus avant de cliquer sur le bouton Avancé. Cliquez maintenant sur le petit bouton à droite de la fenêtre. Gamme de critères bouton.

Sélectionnez tout de A1 à E3, puis cliquez à nouveau sur le même bouton pour revenir à la boîte de dialogue Filtre avancé. Cliquez sur OK et vos données doivent maintenant être filtrées.!

Comme vous pouvez le constater, je n'ai maintenant que 3 résultats correspondant à tous ces critères. Notez que les étiquettes de la plage de critères doivent correspondre exactement aux étiquettes de l'ensemble de données pour que cela fonctionne..

Vous pouvez évidemment créer des requêtes beaucoup plus compliquées en utilisant cette méthode, alors jouez avec elle pour obtenir les résultats souhaités. Enfin, parlons de l’application des fonctions de sommation aux données filtrées..

Résumé des données filtrées

Maintenant, disons que je veux résumer le nombre de membres de la famille sur mes données filtrées, comment pourrais-je en arriver là? Eh bien, effacons notre filtre en cliquant sur le bouton Clair bouton dans le ruban. Ne vous inquiétez pas, il est très facile d'appliquer le filtre avancé à nouveau en cliquant simplement sur le bouton Avancé puis en cliquant à nouveau sur OK..

Au bas de notre jeu de données, ajoutons une cellule appelée Total puis ajoutez une fonction de somme pour résumer le nombre total de membres de la famille. Dans mon exemple, je viens de taper = SOMME (C7: C31).

Donc, si je regarde toutes les familles, j'ai 78 membres au total. Maintenant, allons de l'avant et réappliquez notre filtre avancé et voyons ce qui se passe.

Oups! Au lieu de montrer le nombre correct, 11, je vois toujours que le total est 78! Pourquoi donc? Eh bien, la fonction SUM n'ignore pas les lignes masquées, elle effectue donc le calcul en utilisant toutes les lignes. Heureusement, il y a quelques fonctions que vous pouvez utiliser pour ignorer les lignes cachées.

Le premier est TOTAL. Avant d'utiliser l'une de ces fonctions spéciales, vous devez effacer votre filtre, puis taper la fonction..

Une fois le filtre effacé, continuez et tapez = SOUS-TOTAL ( et vous devriez voir une liste déroulante apparaître avec un tas d'options. En utilisant cette fonction, vous choisissez d’abord le type de fonction de sommation que vous voulez utiliser en utilisant un nombre.

Dans notre exemple, je veux utiliser SOMME, donc je voudrais taper le numéro 9 ou simplement cliquer dessus dans le menu déroulant. Puis tapez une virgule et sélectionnez la plage de cellules.

Lorsque vous appuyez sur Entrée, vous devriez voir que la valeur de 78 est la même que précédemment. Cependant, si vous appliquez à nouveau le filtre, nous verrons 11!

Excellent! C'est exactement ce que nous voulons. Maintenant, vous pouvez ajuster vos filtres et la valeur reflétera toujours uniquement les lignes actuellement affichées..

La deuxième fonction qui fonctionne à peu près exactement de la même manière que la fonction SOUS-TOTAL est AGRÉGAT. La seule différence est qu'il existe un autre paramètre dans la fonction AGGREGATE dans lequel vous devez spécifier que vous souhaitez ignorer les lignes masquées..

Le premier paramètre est la fonction de sommation que vous souhaitez utiliser et, comme avec SUBTOTAL, 9 représente la fonction SOMME. La deuxième option est l'endroit où vous devez taper 5 pour ignorer les lignes cachées. Le dernier paramètre est le même et est la plage de cellules.

Vous pouvez également lire mon article sur les fonctions de résumé pour apprendre à utiliser la fonction AGGREGATE et d'autres fonctions telles que MODE, MEDIAN, AVERAGE, etc. de manière plus détaillée.

Espérons que cet article vous donne un bon point de départ pour créer et utiliser des filtres dans Excel. Si vous avez des questions, n'hésitez pas à poster un commentaire. Prendre plaisir!