J'ai récemment écrit un article sur l'utilisation des 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 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 guider à travers les étapes pour créer des filtres dans Excel et également utiliser des 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 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 obligatoire d'avoir cette rangée, mais cela facilite le travail avec les filtres.

Ci-dessus, j'ai quelques fausses données et je veux créer un filtre sur la colonne Ville . Dans Excel, c'est vraiment facile à faire. Allez-y et cliquez sur l'onglet Données dans le ruban, puis cliquez 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 rangée non plus.

Lorsque vous cliquez sur Filtrer, chaque colonne de la première rangée comporte automatiquement un petit bouton déroulant ajouté tout à fait à droite.

Maintenant, allez-y et cliquez sur la flèche déroulante dans la colonne 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 de 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 le numéroter d'un à autant 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'à l'origine, si c'est important pour vous.

Comme vous pouvez le voir, 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 décoché 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 chiffres sera bleue.

Maintenant, disons que je veux filtrer sur une deuxième colonne pour réduire encore le nombre de 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. Cependant, dans ce cas, nous voulons 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 taper ici la valeur du 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 la 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. Jetons un coup d'œil aux filtres complexes en utilisant la boîte de dialogue Filtres avancés .

Créer des filtres avancés dans Excel

Si vous souhaitez créer des filtres plus avancés, vous devez utiliser la boîte de dialogue 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 OU toutes les familles à Clarksville avec plus de 3 membres dans leur famille ET seulement celles avec une adresse e-mail .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 et filtrer dans 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 la Nouvelle-Orléans ou de Clarksville, alors nous allons les saisir dans les cellules E2 et E3.

Lorsque vous tapez des valeurs sur différentes 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 > 2 dans C2 et > 3 dans C3.

Puisque> 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 * .edu dans 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 le bouton Avancé . Le champ List Rang e déterminera automatiquement votre jeu de données depuis que vous avez cliqué dessus avant de cliquer sur le bouton Avancé. Maintenant, cliquez sur le petit bouton situé à droite du bouton Gamme 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 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 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 d'appliquer des fonctions de sommation aux données filtrées.

Résumer les 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 faire cela? Eh bien, effaçons notre filtre en cliquant sur le bouton Effacer dans 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, j'ai juste tapé = SUM (C7: C31) .

Donc, si je regarde toutes les 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.

Le premier 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 en tapant = SUBTOTAL ( et vous verrez apparaître une liste déroulante avec un tas d'options.) En utilisant 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 SUM, donc je tape le numéro 9 ou je clique dessus dans la liste déroulante. 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 seulement les rangées qui sont actuellement montrées.

La deuxième fonction qui fonctionne à peu près exactement de la même manière que la fonction SUBTOTAL est AGGREGATE . 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 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 également lire mon article sur les fonctions de résumé pour apprendre à utiliser 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. Prendre plaisir!

Comment sauvegarder vos messages texte dans Dropbox ou Google Drive sur Android

Vous ne savez jamais quand vous allez devoir référencer un ancien message texte avec un élément d'information important, et Le moyen le plus simple de vous assurer de ne jamais perdre quoi que ce soit est de faire marche arrière. Heureusement, il est très facile de transférer tous les messages texte de votre téléphone Android vers le nuage.

(how-to)

Comment choisir le service d'abonnement musical qui vous convient

Il semble que l'année dernière, les services de diffusion en continu sont devenus une douzaine, avec de grands noms comme Jay-Z, Dr. Dre et Tim Cook essayent tous de détrôner l'actuel roi du chaos musical «à volonté», Spotify. Avec autant de streamers sub-pars qui inondent le marché, comment pouvez-vous savoir ce qui vous rapportera le meilleur (et la bande passante) pour votre argent?

(how-to)