J’ai récemment écrit un article sur comment utiliser les fonctions de synthèse dans Excel pour synthétiser facilement de grandes quantités de données, mais que l’article a pris en compte toutes les données dans la feuille de calcul. Que faire si vous ne voulez regarder un sous-ensemble de données et de résumer le sous-ensemble de données?
Dans Excel, vous pouvez créer des filtres sur les colonnes qui permet de masquer 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 aussi en utilisant les fonctions intégrées de résumer ce que les données filtrées.
Créer des Filtres dans Excel
Dans Excel, vous pouvez créer des filtres et des filtres complexes. Nous allons commencer avec de simples filtres. Lorsque vous travaillez avec des filtres, vous devriez toujours avoir une ligne en haut qui est utilisé pour les étiquettes. Ce n’est pas une obligation d’avoir cette ligne, mais il permet de travailler avec des filtres un peu plus facile.
Ci-dessus, j’ai quelques fausses données et je veux créer un filtre sur la Ville de la colonne. Dans Excel, c’est très facile à faire. Allez-y et cliquez sur les Données de l’onglet dans le ruban, puis cliquez sur le Filtre bouton. Vous n’avez pas à sélectionner les données sur la feuille ou cliquez dans la première ligne.
Lorsque vous cliquez sur Filtre, chaque colonne dans la première ligne sera automatiquement avoir un petit bouton de la liste déroulante ajouté à la droite.
Maintenant, allez-y et cliquez sur la flèche de menu déroulant dans la colonne Ville. Vous verrez un couple de différentes options, que je vais expliquer ci-dessous.
En haut, vous pouvez trier rapidement toutes les lignes par les valeurs de la colonne Ville. Notez que lorsque vous triez les données, il vous permettra de déplacer la totalité de la ligne, et pas seulement les valeurs dans la colonne Ville. Cela permettra d’assurer que vos données restent intactes, tout comme il était avant.
Aussi, c’est une bonne idée d’ajouter une colonne à l’avant, appelé ID et le numéro de l’un à l’cependant, le nombre de lignes que vous avez dans votre feuille de calcul. 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’il a été à l’origine, si c’est important pour vous.
Comme vous pouvez le voir, toutes les données dans la feuille de calcul est maintenant triée en fonction des valeurs dans la colonne Ville. Jusqu’à présent, pas de lignes sont masquées. Maintenant, nous allons prendre un regard sur les cases à cocher en bas de la boîte de dialogue du filtre. Dans mon exemple, je n’ai que trois valeurs uniques dans la Ville de la colonne et ceux de trois apparaître dans la liste.
Je suis allé de l’avant et non deux villes et à gauche on a vérifié. Maintenant je n’ai que 8 lignes de données montrant et le reste sont cachés. Vous pouvez facilement faire vous cherchez à des données filtrées si vous vérifiez les numéros de ligne à l’extrême gauche. En fonction du nombre de lignes sont masquées, vous verrez un peu plus de lignes horizontales et la couleur des chiffres sera bleu.
Maintenant, disons que je veux filtre sur une deuxième colonne à réduire davantage le nombre de résultats. Dans la colonne C, j’ai le nombre total de membres de chaque famille et je veux seulement voir les résultats pour les familles avec plus de deux membres.
Allez-y et cliquez sur la flèche de menu déroulant dans la Colonne C, et vous verrez les mêmes cases à cocher pour chaque valeur unique dans la colonne. Toutefois, dans ce cas, nous voulons cliquer sur le Numéro de Filtres , puis cliquez sur Plus De. Comme vous pouvez le voir, il ya un tas d’autres options aussi.
Une nouvelle boîte de dialogue s’ouvre et ici, vous pouvez taper la valeur pour le filtre. Vous pouvez également ajouter plus d’un avec les critères ET les ou de OU de la fonction. Vous pourriez dire que vous voulez que les lignes dont la valeur est supérieure à 2 et n’est pas égal à 5, par exemple.
Maintenant je suis en bas, à seulement 5 lignes de données: les familles uniquement à partir 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 et en cliquant ensuite sur le Effacer le Filtre De la Colonne “Nom” lien.
Donc c’est sur il pour de simples filtres dans Excel. Ils sont très faciles à utiliser et les résultats sont assez straight-forward. Maintenant, nous allons jeter un oeil à des filtres complexes à l’aide de l’ Avancée des filtres de boîte de dialogue.
Créer des Filtres dans Excel
Si vous souhaitez créer plus de filtres avancés, vous devez utiliser l’ Avancé de la boîte de dialogue du filtre. Par exemple, disons que je voulais voir toutes les familles qui vivent à la Nouvelle-Orléans avec plus de 2 membres de leur famille OU de toutes les familles à Clarksville avec plus de 3 membres au sein de leur famille ET seulement ceux avec un .EDU se terminant adresse e-mail. Maintenant, vous ne pouvez pas faire cela avec un simple filtre.
Pour ce faire, nous avons besoin pour l’installation de la feuille Excel un peu différemment. Aller de l’avant et insérer quelques lignes au-dessus de votre ensemble de données et de copier l’en-tête des étiquettes exactement dans la première ligne comme indiqué ci-dessous.
Voici maintenant comment les filtres avancés de travail. Vous avez tout d’abord le type de vos critères dans les colonnes en haut, puis cliquez sur le Avancée bouton de Tri Et de Filtre sur les Données de l’onglet.
Qu’est-ce exactement peut-on saisir dans ces cellules? OK, donc, nous allons commencer avec notre exemple. Nous voulons seulement voir les données de la Nouvelle-Orléans ou de Clarksville, alors laissez-du type de ceux que les cellules E2 et E3.
Lorsque vous tapez les valeurs dans des lignes différentes, il signifie OU. Maintenant, nous voulons de la Nouvelle-Orléans familles avec plus de deux membres et de Clarksville les familles avec plus de 3 membres. Pour ce faire, tapez >2 C2 et >3 en C3.
Depuis >2 et la Nouvelle-Orléans sont sur la même ligne, il sera un ET l’opérateur. La même chose est vraie pour la ligne 3 ci-dessus. Enfin, nous voulons que les familles avec .EDU se terminant adresse e-mail. 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 faites cela, cliquez n’importe où dans votre jeu de données et puis cliquez sur l’ Avancée de bouton. La Liste de Range champ sera automatiquement comprendre votre jeu de données depuis que vous avez cliqué avant de cliquer sur le bouton Avancé. Maintenant, cliquez sur le petit bouton à droite de la plage de Critères bouton.
Sélectionnez tout, de A1 à l’E3, puis cliquez sur le même bouton à nouveau pour revenir à la boîte de dialogue Filtre Avancé. Cliquez sur OK et vos données doivent maintenant être filtré!
Comme vous pouvez le voir, maintenant j’ai seulement 3 résultats qui correspondent à tous ces critères. Notez que les étiquettes de la plage de critères doivent correspondre exactement avec les étiquettes pour le jeu de données dans l’ordre pour que cela fonctionne.
Vous pouvez bien évidemment créer beaucoup plus compliqué requêtes à l’aide de cette méthode, pour jouer avec elle pour obtenir les résultats souhaités. Enfin, parlons de l’application de la sommation des fonctions de données filtrées.
Résumant Les Données Filtrées
Maintenant, disons que je voudrais résumer le nombre de membres de la famille sur mes données filtrées, comment aurais-je eu à faire cela? Eh bien, disons-le clairement notre filtre en cliquant sur le 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 cliquez sur OK à nouveau.
À la fin de notre jeu de données, nous allons ajouter une cellule dite Totale , puis ajouter une fonction somme pour additionner le total des membres de la famille. Dans mon exemple, j’ai juste tapé =SOMME(C7:C31).
Donc, si je regarde toutes les familles, j’ai 78 membres au total. Maintenant, nous allons aller de l’avant et de réappliquer notre filtre Avancé et voir ce qui se passe.
Oups! Au lieu de montrer le nombre exact, le 11, je vois encore le total est de 78! Pourquoi est-ce? Ainsi, la fonction SOMME à ne pas ignorer les lignes cachées, de sorte qu’il est encore en train de faire le calcul à l’aide de toutes les lignes. Heureusement, il ya un couple de fonctions que vous pouvez utiliser pour ignorer les lignes cachées.
La première est la sous-total. Avant d’utiliser une de ces fonctions spéciales, vous souhaitez effacer votre filtre, puis tapez dans la fonction.
Une fois le filtre nettoyé, allez-y et tapez =sous-total( et vous devriez voir une liste déroulante apparaît avec un tas d’options. En utilisant cette fonction, vous devez d’abord choisir le type de sommation de la fonction que vous souhaitez utiliser à l’aide d’un numéro.
Dans notre exemple, je veux utiliser la SOMME, donc, je voudrais saisissez le numéro 9 ou il suffit de cliquer dessus dans la liste déroulante. Ensuite, tapez une virgule et sélectionnez la plage de cellules.
Lorsque vous appuyez sur entrée, vous devriez voir la valeur de 78 est le même que précédemment. Cependant, maintenant, si vous appliquez le filtre de nouveau, nous allons voir 11!
Excellent! C’est exactement ce que nous voulons. Vous pouvez maintenant régler vos filtres et la valeur sera toujours refléter uniquement les lignes qui sont actuellement à l’affiche.
La deuxième fonction qui fonctionne à peu près exactement le même que le sous-total de la fonction est GLOBALE. La seule différence est qu’il y a un autre paramètre dans la fonction d’AGRÉGATION où vous devez spécifier que vous voulez ignorer les lignes cachées.
Le premier paramètre est la somme de la fonction que vous souhaitez utiliser, puis comme sous-total, 9 représente la fonction SOMME. La deuxième option est l’endroit où vous devez taper dans les 5 à 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 synthèse pour apprendre à utiliser la fonction d’AGRÉGATION et d’autres fonctions comme le MODE, la MÉDIANE, la MOYENNE, etc. plus en détail.
Nous espérons que cet article vous donne un bon point de départ pour la création et l’utilisation des filtres dans Excel. Si vous avez des questions, n’hésitez pas à poster un commentaire. Profitez-en!