Il n’est pas rare d’utiliser Excel pour créer de petites bases de données. Par exemple, vous pouvez conserver une liste des membres de votre club d’éleveurs de caniches dans Excel, ou vous pouvez l’utiliser pour maintenir une liste de vos contacts commerciaux actifs. Dans ces cas, vous vous demandez peut-être comment obtenir un décompte du nombre d’enregistrements qui répondent à plusieurs critères.

Supposons que vous analysiez votre liste de membres et que vous vouliez déterminer le nombre d’enregistrements dans lesquels la colonne de genre contient « F »

et la colonne de ville contient une ville particulière, telle que «Norwood». Ceci, bien sûr, serait utile car cela répondrait à la question brûlante de savoir combien de femmes membres de votre groupe vivent à Norwood.

Excel comprend un certain nombre de fonctions de feuille de calcul pratiques pour déterminer le nombre d’enregistrements dans une liste. Comment vous pouvez les utiliser dans une situation où deux critères doivent être remplis peut ne pas être immédiatement évident.

Examinons six façons spécifiques d’atteindre l’objectif souhaité des membres féminins de Norwood. (Supposons que la colonne C est la colonne de genre et la colonne F est la colonne de ville.)

La première façon de résoudre le problème consiste à utiliser la fonction COUNTIFS. Si votre colonne de genre est la colonne C et la colonne de votre ville est la colonne F, vous pouvez utiliser la formule suivante:

=COUNTIFS(C1:C500,"F",F1:F500,"Norwood")

Il recherche dans la colonne de genre © toutes les cellules contenant « F » et la colonne de ville (F) pour toutes les cellules contenant « Norwood ». Le résultat est le nombre d’enregistrements qui satisfont aux deux critères.

Une deuxième approche consiste à utiliser la fonction DCOUNTA. Cette fonction vous permet de définir un ensemble de critères et d’utiliser ces critères comme base pour analyser une liste de données. Comme toutes les fonctions de données dans Excel, DCOUNTA repose sur trois paramètres: la plage de données, la colonne à utiliser dans les comparaisons et la plage de critères. Pour utiliser la fonction, configurez un tableau de critères dans une zone inutilisée de votre feuille de calcul. Par exemple, vous pouvez configurer les éléments suivants dans les cellules AA1 à AB2: (voir la figure 1.)

image

Figure 1. Le tableau des critères de la fonction DCOUNTA.

Ensuite, en supposant que votre table de données d’origine se trouve dans les cellules A1: K500 (évidemment un grand club d’éleveurs de caniches), vous pouvez utiliser ce qui suit pour déterminer le nombre:

=DCOUNTA(A1:K500,1,AA1:AB2)

Le résultat est un décompte qui répond aux critères que vous avez spécifiés dans AA1: AB2.

Notez également que les noms que vous avez utilisés dans AA1 et AB1 doivent correspondre exactement aux étiquettes que vous avez utilisées dans vos enregistrements de table. Lorsqu’ils le font, le contenu de la colonne Sexe (colonne C) doit être «F» et le contenu de la colonne Ville (colonne F) doit être «Norwood» pour que l’enregistrement soit ajouté au décompte.

La troisième solution consiste à utiliser une formule matricielle pour renvoyer une seule réponse.

La formule matricielle utilise de manière intéressante la fonction SOMME et un peu d’arithmétique booléenne pour déterminer si un enregistrement doit être compté.

Considérez ce qui suit:

=SUM((C2:C500="F")*(F2:F500="Norwood"))

Tapez simplement la formule ci-dessus dans une cellule, puis terminez-la en appuyant sur Ctrl + Maj + Entrée; cela permet à Excel de savoir que vous entrez une formule matricielle. La formule fonctionne car elle compare le contenu de chaque ligne du tableau, à son tour, selon les critères spécifiés dans la formule. Il compare d’abord le contenu de la colonne C avec « F »; s’il correspond, alors la comparaison renvoie True, qui est la valeur numérique 1.

Le contenu de la colonne F est ensuite comparé à « Norwood ». Si cette comparaison est vraie, alors 1 est renvoyé. Ainsi, 1 1 serait égal à 1, et ceci est ajouté à la somme du tableau. Si l’une ou l’autre des comparaisons est False, la valeur numérique 0 est renvoyée et 1 0 est égal à 0 (comme 0 0 et 0 1), ce qui n’affecte pas le SUM en cours.

Une quatrième approche étroitement liée consiste à utiliser la fonction SUMPRODUCT, mais pas dans une formule matricielle. Vous pouvez simplement utiliser ce qui suit dans n’importe quelle cellule où vous souhaitez savoir si les deux critères sont remplis:

SUMPRODUCT((C2:C500="F")*(F2:F500="Norwood"))

N’oubliez pas qu’il ne s’agit pas d’une formule matricielle, vous n’avez donc pas besoin d’appuyer sur Ctrl + Maj + Entrée. La formule fonctionne, encore une fois, grâce à la magie des mathématiques booléennes.

Une cinquième solution possible, qui est un peu plus manuelle que celles déjà évoquées, consiste à utiliser la fonction de filtre automatique avec un sous-total. En supposant que vos enregistrements de données se trouvent dans A1: K500, avec des étiquettes de colonne dans la ligne 1, vous suivrez ces étapes:

  1. Sélectionnez n’importe quelle cellule du tableau de données.

  2. Affichez l’onglet Données du ruban.

  3. Cliquez sur l’outil Filtre dans le groupe Trier et filtrer. Excel doit afficher les indicateurs déroulants du filtre automatique à côté de chaque étiquette de colonne dans la ligne 1.

  4. À l’aide de l’indicateur déroulant de la colonne sexe (colonne C), choisissez F. Votre liste est automatiquement filtrée pour n’afficher que les membres féminins.

  5. À l’aide de l’indicateur déroulant de la colonne de la ville (colonne F), choisissez Norwood. Votre liste s’affiche automatiquement pour n’afficher que les membres féminins vivant à Norwood.

  6. Au bas du tableau de données (ligne 501), dans n’importe quelle colonne souhaitée, entrez la formule suivante:

=SUBTOTAL(3,C2:C500)

Cette formule entraîne la fonction SUBTOTAL à appliquer la fonction COUNTA pour dériver un sous-total. En d’autres termes, il renvoie un décompte de tous les enregistrements affichés par le filtrage; c’est le décompte souhaité.

Une sixième approche consiste à utiliser l’assistant de somme conditionnelle pour trouver une formule pour vous. (L’Assistant Somme conditionnelle est disponible en tant que complément Excel pour Excel 2007 et les versions antérieures; il est activé par défaut sur la plupart des systèmes. Il a été supprimé d’Excel 2010.) Suivez ces étapes pour utiliser l’Assistant Somme conditionnelle:

  1. Sélectionnez une cellule quelque part dans les données que vous souhaitez analyser.

  2. Affichez l’onglet Formules du ruban.

  3. Dans le groupe Solutions (côté droit du ruban), cliquez sur Somme conditionnelle. Excel affiche la première étape de l’Assistant Somme conditionnelle. La gamme complète de vos données devrait déjà être affichée dans la boîte de dialogue. (Voir la figure 2.)

  4. Cliquez sur Suivant. Excel affiche l’étape suivante de l’assistant.

  5. Dans la liste déroulante Colonne à additionner, choisissez Sexe.

  6. Dans la liste déroulante Colonne, choisissez Sexe.

  7. Dans la liste déroulante Est, choisissez le signe égal.

  8. Dans la liste déroulante Cette valeur, choisissez F.

  9. Cliquez sur Ajouter. La condition que vous avez spécifiée apparaît maintenant dans la boîte de dialogue.

  10. Dans la liste déroulante Colonne, choisissez Ville.

  11. Dans la liste déroulante Est, choisissez le signe égal.

  12. Dans la liste déroulante Cette valeur, choisissez Norwood.

  13. Cliquez sur Ajouter. La deuxième condition apparaît maintenant dans la boîte de dialogue.

  14. Cliquez sur Suivant. Excel affiche la troisième étape de l’assistant.

  15. Choisissez Copier uniquement la formule dans une seule cellule.

  16. Cliquez sur Suivant. Excel affiche la quatrième (et dernière) étape de l’assistant.

  17. Dans la feuille de calcul, cliquez sur la cellule dans laquelle vous souhaitez contenir le résultat de votre formule.

  18. Cliquez sur Terminer.

Le résultat est une formule, adaptée aux conditions que vous avez spécifiées, dans la cellule que vous avez sélectionnée à l’étape 1.

Il existe sans aucun doute d’innombrables autres solutions possibles que vous pourriez utiliser pour déterminer le nombre d’enregistrements. Celles-ci, cependant, sont le «choix du lot» et vous permettent de déterminer la réponse rapidement et facilement.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (7759) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

lien: / excel-Counting_Records_Matching_Multiple_Criteria [Comptage des enregistrements correspondant à plusieurs critères].