Contact: 514-605-7112 / info@lecfomasque.com

Excel: Compter le nombre de cellules selon divers critères

Publié le : 16 novembre 2012

Cet article vous apprendra à faire une somme d’éléments présents dans une base de données selon divers critères. Grâce à la formule utilisée, vous pourrez insérer des critères uniques et des critères multiples. Dans l’exemple utilisé ci-bas, vous aurez une base de données comprenant les ventes réalisées par vendeur, par mois et par type de client. La formule utilisée vous permettra de calculer le nombre de ventes effectuées pour un mois donné (critère unique), pour 2 des vendeurs (critère multiple: vendeur A ou vendeur B).

.

ARRAY FORMULA

.

La formule que nous allons voir fait partie de ce qu’Excel appelle les « Array Formulas ». Ces formules ressemblent aux formules standards d’Excel mais exigent certaines manipulations.

.

Pour écrire une formule « Array Formula », vous devez écrire la formule dans la barre de formules mais avant de sortir de la barre de formules (donc avant d’appuyer sur « Enter »), vous devez cliquer sur CSE (Ctrl+Shift+Enter). Vous verrez ainsi des accolades apparaîtrent au début et à la fin de la formule.

.

.

NOMMER DES PLAGES DE CELLULES

.

Dans l’exemple suivant, vous aurez besoin de nommer des plages de cellules. Vous devrez nommer la colonne mois « MOIS », la colonne vendeur « Vendeur », la colonne client « Client » et la colonne ventes « Ventes ». Si vous avez besoin de vous rafraîchir la mémoire sur la façon de nommer des plages de cellules, consultez à nouveau l’article Astuce Excel: Nommer une plage de cellules.

.

COMPTER LE NOMBRE DE CELLULES SELON DIVERS CRITÈRES

.

Dans l’exemple suivant, vous avez une base de données comprenant les ventes réalisées par vendeur, par mois et par type de client. La question est: « Combien de ventes ont été effectuées en février par Roger et Josée »? Bien sûr, la base de données est petite donc on pourrait calculer le tout manuellement mais imaginez une base de données comprenant des millions de lignes…

.

On peut traduire la question ainsi: « Faites le compte des ventes qui ont eu lieu en février et qui ont été réalisées soit par Roger, soit par Josée ». On comprend donc que le critère mois (février) est un critère unique et que le critère (vendeur) est un critère multiple (soit Roger, soit Josée).

.

Ainsi, si vous jetez un coup d’oeil à la formule, vous verrez que l’on fait la somme des cellules dont le mois est égal à D4, i.e. février, et que l’on sépare cette portion de la formule par un * et un IF (ou « Si » en français) pour signifier que le prochain critère (vendeur) est un critère multiple, soit il est égal à C5 (Roger) ou soit il est égal à E5 (Josée).

.

.


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse JMV ,

    Bonjour,
    Petite précision : le « SI » (IF) n’est pas du tout requis dans la formule. Exemple :

    {=SOMME((Mois=$D$4)*((Vendeur=$C$5)+(Vendeur=$E$5)))}

    et si on voulait la somme du montant des ventes, on aurait :

    {=SOMME((Mois=$D$4)*((Vendeur=$C$5)+(Vendeur=$E$5))*Ventes)}

    • réponse moncherwatson ,

      Bonjour JVM,

      Merci pour votre commentaire.

      Vous me devancez un peu puisque c’est ce que je souhaitais aborder dans un deuxième article!

      Au plaisir,

      Sophie

    • réponse Maklen ,

      Bonjour,
      Avec Excel 2007, il est plus aisé d’utiliser la fonction sommes.si ou sumifs que cette formule matricielle décrite ci-haut.

      • réponse moncherwatson ,

        Bonjour Maklen,

        Merci pour votre commentaire. En effet, il est aussi possible d’utiliser les sumifs, countifs et averageifs comme je l’ai déjà démontré dans des articles précédents. C’est une question de goût!

        Au plaisir,

        Sophie

      • réponse Frédéric Pelletier ,

        À moins de ne pas connaître suffisamment la fonction Somme.Si, il me semble que cette formule est limitée à un seul critère. Je trouvais donc intéressant d’utiliser les « array formulas » lorsque plus d’un critères entrent en interaction.

        Merci.

      • réponse Piet ,

        Sum if est peut être plus simple, mais dans le cas d’une Max ou Min par exemple, le Max If n’existe pas… 😉 Donc ce tuto vient à point nommé.. ; -)

        merci pour l’info.

        • réponse Temp2 | Pearltrees ,

          […] protéger la feuille Excel. Pour ce faire, référez-vous à Truc Excel : Protégez vos données. Compter le nombre de cellules selon divers critères (LeCFOmasqué) Cet article vous apprendra à faire une somme d’éléments présents dans une base de données […]

          Laisser un commentaire