J'ai récemment écrit un article sur comment utiliser les fonctions de résumé 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. Que faire 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 des 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 guider à travers les étapes de création de filtres dans Excel et également à l'aide de fonctions 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 avec des filtres simples. Lorsque vous travaillez avec des filtres, vous devez toujours avoir une ligne en haut utilisée pour les étiquettes. Ce n'est pas une obligation d'avoir cette ligne, mais cela facilite le travail avec les filtres.
Ci-dessus, j'ai de fausses données et je souhaitez créer un filtre dans la colonne Ville. Dans Excel, c'est vraiment facile à faire. Cliquez sur l'onglet Donnéesdu ruban, puis sur le bouton Filtre. Vous n'avez pas besoin de sélectionner les données sur la feuille ou de cliquer dans la première ligne non plus.
Lorsque vous cliquez sur Filtrer, chaque colonne dans la première rangée aura automatiquement un petit bouton déroulant ajouté à droite.
Maintenant allez-y et cliquez sur la flèche de la liste déroulante dans le Colonne de la ville. Vous verrez quelques options différentes, que je vais expliquer ci-dessous.
En haut, vous pouvez rapidement trier toutes les lignes par les valeurs dans la colonne Ville. Notez que lorsque vous triez les données, elles déplacent toute la ligne, pas seulement les valeurs de la colonne Ville. Cela garantira que vos données restent intactes comme avant.
Aussi, c'est une bonne idée d'ajouter une colonne à l'avant même appelée ID et de la numéroter d'un à autant de lignes que vous avez dans votre feuille de travail. De cette façon, vous pouvez toujours trier par la colonne ID et récupérer vos données dans le même ordre qu'à l'origine, si cela est important pour vous.
Comme vous pouvez le constater, toutes les données de la feuille de calcul sont désormais triées en fonction des valeurs de la colonne Ville. Jusqu'à présent, aucune ligne n'est cachée. Jetons maintenant un coup d'œil aux cases à cocher en 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 non cochée deux villes et laissé un vérifié. Maintenant, je n'ai plus que 8 lignes de données et le reste est caché. Vous pouvez facilement dire que vous regardez les données filtrées si vous vérifiez les numéros de ligne à l'extrême gauche. Selon le nombre de lignes cachées, vous verrez quelques lignes horizontales supplémentaires et la couleur des nombres sera bleue.
Maintenant, disons que je veux filtrer sur une deuxième colonne pour réduire encore le nombre des résultats. Dans la colonne C, j'ai le nombre total de membres dans chaque famille et je veux voir seulement les résultats pour les familles de plus de deux membres.
Allez-y et cliquez sur la flèche déroulante dans la colonne C et vous verrez les mêmes cases à cocher pour chaque valeur unique dans la colonne. Toutefois, dans ce cas, nous souhaitons cliquer sur Filtres de nombre, puis sur Supérieur à. Comme vous pouvez le voir, il y a beaucoup d'autres options aussi.
Une nouvelle boîte de dialogue apparaîtra et vous pouvez entrer la valeur pour le filtre. Vous pouvez également ajouter plusieurs critères avec une fonction AND ou OR. Vous pourriez dire que vous voulez des lignes où la valeur est supérieure à 2 et non égale à 5, par exemple.
Maintenant je suis à seulement 5 rangées de données: familles seulement de la Nouvelle Orléans et avec 3 membres ou plus. Assez facile? Notez que vous pouvez facilement effacer un filtre sur une colonne en cliquant sur la liste déroulante, puis en cliquant sur le lien Effacer le filtre de "Nom de colonne".
Voilà à peu près tout pour des filtres simples dans Excel. Ils sont très faciles à utiliser et les résultats sont plutôt simples. Voyons maintenant les filtres complexes en utilisant la boîte de dialogue Avancé.
Créer des filtres avancés dans Excel
Si vous voulez créer des filtres plus avancés, vous devez utiliser la boîte de dialogue de filtre Avancé. Par exemple, disons que je voulais voir toutes les familles qui vivent à la Nouvelle-Orléans avec plus de 2 membres dans leur famille OUtoutes les familles à Clarksville avec plus de 3 membres dans leur famille ETuniquement celles avec une adresse e-mail de fin .EDU. Maintenant, vous ne pouvez pas faire cela avec un simple filtre.
Pour ce faire, nous devons configurer la feuille Excel un peu différemment. Allez-y et insérez quelques lignes au-dessus de votre ensemble de données et copiez les étiquettes d'en-tête exactement dans la première ligne comme indiqué ci-dessous.
Maintenant Voici comment fonctionnent les filtres avancés. Vous devez d'abord taper vos critères dans les colonnes en haut, puis cliquer sur le bouton Avancésous Trier & amp; Filtrezsur l'onglet Données
Alors, que pouvons-nous taper exactement dans ces cellules? OK, alors commençons avec notre exemple. Nous voulons seulement voir les données de New Orleans ou de Clarksville, alors tapons celles-ci dans les cellules E2 et E3.
Lorsque vous tapez des valeurs sur différents lignes, cela signifie OU. Maintenant, nous voulons des familles de la Nouvelle-Orléans avec plus de deux membres et des familles de Clarksville avec plus de 3 membres. Pour ce faire, tapez & gt; 2dans C2 et & gt; 3dans C3.
Puisque & gt; 2 et la Nouvelle-Orléans sont sur la même ligne, ce sera un opérateur AND. La même chose est vraie pour la rangée 3 ci-dessus. Enfin, nous voulons seulement les familles avec l'adresse e-mail .EDU se terminant. Pour ce faire, tapez simplement *. Edudans D2 et D3. Le symbole * signifie n'importe quel nombre de caractères.
Une fois que vous faites cela, cliquez n'importe où dans votre jeu de données puis cliquez sur Avancé. Le champ List Range 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 situé à droite du bouton Plage de critères.
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 devraient maintenant être filtrées!
Comme vous pouvez le voir, maintenant je n'ai que 3 résultats qui correspondent à 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 pour obtenir les résultats souhaités. Enfin, parlons de l'application des fonctions de sommation aux données filtrées.
Résumer les données filtrées
Supposons maintenant que je veux résumer le nombre de membres de la famille sur mes données filtrées, comment eu à faire ça? Eh bien, effaçons notre filtre en cliquant sur le bouton Effacerdans le ruban. Ne vous inquiétez pas, il est très facile d'appliquer à nouveau le filtre avancé en cliquant simplement sur le bouton Avancé et en cliquant à nouveau sur OK.
Au bas de notre ensemble de données, ajoutons une cellule appelée Total, puis ajoutons une fonction sum pour additionner le nombre total de membres de la famille. Dans mon exemple, je viens de taper = SOMME (C7: C31)
Donc si je regarde tout familles, j'ai 78 membres au total. Maintenant, allons-y et réappliquer notre filtre avancé et voir ce qui se passe.
Oups! Au lieu de montrer le nombre correct, 11, je vois encore le total est de 78! Pourquoi donc? Eh bien, la fonction SUM n'ignore pas les lignes cachées, donc elle fait toujours le calcul en utilisant toutes les lignes. Heureusement, il existe quelques fonctions que vous pouvez utiliser pour ignorer les lignes cachées.
La première est SUBTOTAL. 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 = SUBTOTAL (et vous devriez voir une liste déroulante apparaître avec un tas d'options.Avec cette fonction, vous devez d'abord choisir 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 chiffre 9 ou cliquer dessus dans la liste déroulante, puis taper une virgule et sélectionner la plage de cellules.
Lorsque vous appuyez sur enter, vous devriez voir que la valeur de 78 est la même que précédemment, mais 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 les lignes qui sont en train de s'afficher.
La deuxième fonction qui fonctionne à peu près exactement de la même manière que la fonction SUBTOTAL est AGGREGATE. La différence est qu'il y a un autre paramètre dans la fonction AGGREGATE où vous devez spécifier que vous voulez ignorer les lignes cachées.
Le premier paramètre est la fonction de sommation que vous voulez utiliser et comme pour SUBTOTAL, 9 représente la fonction SUM. La deuxième option est 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 aussi lire mon article sur les fonctions de résumé pour apprendre comment utilisez la fonction AGGREGATE et d'autres fonctions comme MODE, MEDIAN, MOYENNE, etc. plus en détail.
J'espère 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. Profitez-en!