Comptage basé sur des critères dans une colonne filtrée (Microsoft Excel)

Marty a une grande feuille de travail qui répertorie tous les employés (passés et présents) de son entreprise. La feuille de travail suit diverses informations sur chaque employé, telles que le nom, l’adresse, le service, le sexe, le statut, etc.

Marty filtre souvent les données pour répondre à ses besoins. Il souhaiterait un moyen de déterminer le pourcentage d’employés qui sont des hommes et le pourcentage de femmes, en fonction des lignes affichées après le filtrage. La fonction SOUS-TOTAL peut travailler sur une colonne filtrée pour fournir différents décomptes, mais elle ne lui permettra pas de déterminer les décomptes en fonction du contenu (« M » ou « F ») de la colonne filtrée.

Une approche consiste à utiliser un tableau croisé dynamique pour déterminer les pourcentages.

Les tableaux croisés dynamiques sont relativement faciles à utiliser, en particulier pour répondre à une seule question comme celle-ci. Cependant, ils ne sont pas très bons pour vous permettre de voir des informations détaillées sur vos employés. Vous pouvez voir la réponse globale à la question homme / femme, mais vous ne pouvez pas en même temps voir les détails sur ces employés. Donc, je veux me concentrer sur l’utilisation de formules droites dans la réponse au problème de Marty.

Créer une formule pour obtenir les pourcentages souhaités est plus difficile qu’il n’y paraît au premier abord. Il est facile, par exemple, de déterminer les décomptes lorsque tous les enregistrements d’employés sont visibles. Vous pourriez, par exemple, simplement utiliser quelque chose comme ceci, en supposant que le sexe était dans la colonne C, pour déterminer quel pourcentage des enregistrements concerne les employés masculins:

Le problème est que vous filtrez les enregistrements par une colonne différente de la colonne C. Par exemple, vous pouvez le filtrer par ce qui se trouve dans la colonne d’état. La formule ci-dessus vous donnera toujours un pourcentage basé sur tous les enregistrements de la colonne de sexe, pas seulement ceux qui sont actuellement visibles en raison du filtrage.

Vous pourriez, à ce stade, penser que la fonction SUBTOTAL pourrait fonctionner, mais Marty a découvert que ce ne serait pas le cas. Encore une fois, si le sexe est dans la colonne C, vous pouvez mettre ce qui suit au bas de la colonne C:

Cela ne fonctionnera pas, cependant, pour les besoins souhaités. Le problème principal est que la fonction SOUS-TOTAL vous donne un nombre d’enregistrements visibles, mais il n’y a pas de discernement entre si ces enregistrements contiennent «M» ou «F» dans la colonne C. Le problème secondaire est que COUNTA compte tous les enregistrements, pas juste ceux affichés. Ainsi, la formule ne vous donne pas le pourcentage des enregistrements affichés qui contiennent « M » ou « F »

mais, à la place, un pourcentage du nombre d’enregistrements affichés par rapport au nombre total d’enregistrements.

Vous pouvez essayer d’utiliser une colonne d’aide si vous le souhaitez. Ajoutez simplement une colonne pour représenter le statut « M » ou « F » de chaque employé comme 1 ou 0.

Cela pourrait être fait avec une formule simple, comme celle-ci:

Cette formule suppose, encore une fois, que la colonne genre est C. En supposant que cette formule est placée dans la colonne X (votre colonne d’aide), vous pouvez alors utiliser deux fonctions SOUS-TOTAL, de cette manière:

La première utilisation de SUBTOTAL vous donne le nombre de lignes qui contiennent « M »

et la deuxième utilisation de SOUS-TOTAL vous donne le nombre total de lignes visibles. Le résultat de la formule est un pourcentage de travailleurs masculins dont les enregistrements sont visibles. Vous pouvez déterminer le pourcentage de travailleuses en soustrayant le résultat masculin de 1.

Si vous ne pouvez pas utiliser de colonne d’assistance (ou si vous préférez ne pas utiliser de colonne d’assistance), il est un peu plus délicat de contourner les lacunes des approches évoquées précédemment; il faut une formule un peu plus compliquée. Ce qui suit est une formule qui fonctionnera, mais j’ai divisé la formule unique en quatre lignes pour la rendre un peu plus facile à expliquer.

Je vais entrer dans les détails sur le fonctionnement de cette formule, alors soyez indulgents avec moi – cela prendra un certain temps. Jetons d’abord un coup d’œil à cette partie de la formule, qui couvre la fin de la première ligne et la totalité de la deuxième ligne:

L’utilisation de la fonction OFFSET repose ici sur 3 paramètres. Le premier correspond à un point de départ pour le calcul de la référence qui sera retournée (dans ce cas le point de départ est C2: C9999). Le deuxième paramètre est le nombre de lignes à décaler par rapport au début de cette plage définie dans le premier paramètre. Dans ce cas, les lignes à décaler sont définies en soustrayant le numéro de ligne le plus bas de la plage (qui renverra toujours la valeur 2) de la ligne en cours d’analyse. Ainsi, par exemple, si la ligne analysée est la ligne 10, la soustraction de 2 (la ligne de départ) de cela nous donne un décalage de 8 lignes à partir du début de la plage spécifiée dans le premier paramètre.

Le troisième paramètre est vide, il est donc défini par défaut sur 0. Il s’agit du nombre de colonnes à décaler par rapport à la première colonne de la plage spécifiée dans le premier paramètre. Enfin, le quatrième paramètre est le numéro 1, qui indique que vous souhaitez que OFFSET renvoie une plage qui ne dépasse qu’une cellule.

L’essentiel est que toute cette partie de la formule est incluse afin qu’elle renvoie une référence à une seule cellule de la colonne en cours d’analyse. Par souci d’explication dans cette formule, appelons ce qui est retourné « SingleCell ». En branchant ceci dans la formule originale, nous obtenons ceci:

La première fonction SOUS-TOTAL renvoie ensuite le résultat COUNTA (indiqué par la valeur 3 utilisée pour le premier paramètre) pour la cellule unique. L’effet est que SUBTOTAL renvoie un 0 ou un 1, selon que la cellule est visible ou non. (Si la cellule est filtrée hors des résultats, alors 0 est renvoyé. Si elle n’est pas filtrée – elle est visible – alors 1 est renvoyée.)

La partie suivante de la formule repose sur les fonctions ISNUMBER et SEARCH. Cette partie du code renvoie un 0 ou un 1 selon que la cellule contient ou non la lettre «M». Ce que vous obtenez, alors, est quelque chose qui se résume à ceci:

Dans le cas de cette seule ligne, SUMPRODUCT renverrait 0, ce qui signifie que la ligne «ne compte pas» dans le décompte global. Puisque SUMPRODUCT est une fonction basée sur un tableau, il calcule un produit basé sur la multiplication de chaque ligne de la plage d’origine, individuellement. Ainsi, il détermine un décompte de toutes les lignes qui satisfont aux deux conditions: la ligne est visible et la ligne contient la lettre « M. »

Ceci est finalement divisé par le résultat de la fonction SOUS-TOTAL finale, qui est le résultat COUNTA du nombre de lignes visibles. Le résultat final est le pourcentage du nombre de lignes visibles dont le caractère «M» est visible dans la colonne C – le résultat exact que Marty voulait.

Afin d’obtenir le pourcentage de femmes dans les lignes visibles, tout ce que vous avez à faire est de changer ce qui est recherché: changez « M » en « F » dans la formule, et tout devrait bien se passer.

Il y a cependant une mise en garde à mentionner. La fonction RECHERCHE ne fait pas la différence entre les majuscules et les minuscules. Ainsi, si vous utilisez « Homme » au lieu de « M » et « Femme » au lieu de « F » dans votre colonne de sexe (colonne C), la recherche de « Homme » dans la formule correspondra à chaque ligne car les cellules qui contiennent  » Femme « contiendra les caractères » homme « . Ainsi, il est préférable de s’en tenir à « M » et « F » ou, si vous devez utiliser « Homme » et « Femme », utilisez simplement « Femme » dans la formule et calculez le pourcentage d’enregistrements masculins égal à 1 moins le pourcentage féminin.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites WordTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / wordribbon-WordTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13550) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.