Excel : Comment utiliser la fonction matricielle conditionnelle

Publié le 16 novembre 2012
par Sophie Marchand M.Sc.

Cet article vous apprendra à faire une somme d’éléments présents dans une base de données selon divers critères. Grâce à une fonction matricielle conditionnelle, 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).

 

Fonction matricielle dans Excel

La fonction matricielle conditionnelle que nous allons voir fait partie de ce qu’Excel appelle les “Array Formulas” ou “Fonctions matricielles”. Ces formules ressemblent aux formules standards d’Excel mais exigent certaines manipulations. Pour écrire une formule matricielle, 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 données

Dans l’exemple suivant, pour utiliser efficacement notre fonction matricielle conditionnelle, 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 certains 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 fonction matricielle conditionnelle résultante, 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).

 

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

8 réflexions sur “Excel : Comment utiliser la fonction matricielle conditionnelle”

  1. 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)}

    1. 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

  2. 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.

    1. Bonjour Frédéric,

      En fait, la fonction somme.si se limite à un seul critère mais la fonction somme.si.ens (ou sumifs en anglais) peut inclure plusieurs critères. Voir ici: http://lecfomasque.com/2012/07/24/excel-sumifs-countifs/.

      Il y a aussi d’autres trucs alternatifs: http://lecfomasque.com/2011/06/13/astuce-excel-somme-si-sumif-avec-plusieurs-conditions/

      Ou encore: http://lecfomasque.com/2011/08/29/excel-un-truc-puissant-dont-vous-ne-pourrez-plus-vous-passer/

      Au plaisir!

      Sophie

  3. 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.

  4. Ping : Temp2 | Pearltrees

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut