EXCEL : La fonction SOMME.SI en version matricielle dynamique

Publié le 17 janvier 2022
par Sophie Marchand M.Sc., CPA, CGA, MVP
SOMME.SI avec #

EXCEL : La fonction SOMME.SI en version matricielle dynamique

Je vous ai déjà présenté, dans un article précédent, les nouvelles fonctions matricielles dynamiques dynamiques d’Excel, avec tous leurs avantages et leurs inconvénients. Aujourd’hui, je veux vous donner quelques conseils sur l’usage de fonctions traditionnelles d’Excel, lorsqu’utilisées conjointement avec les nouvelles fonctions matricielles dynamiques. Il existe en effet, des façons de les utiliser, pour leur donner à elles aussi, un comportement dynamique. Dans cet article, nous verrons, plus particulièrement, comment créer une fonction matricielle SOMME.SI, qui s’ajustera en fonction de la taille de la plage générée par la fonction matricielle UNIQUE.

 

La fonction matricielle UNIQUE

Dans l’exemple suivant, j’utilise la fonction matricielle dynamique UNIQUE, pour générer la liste de tous  les représentants, sans doublon. J’ai nommé ma table “Ventes”. Ce faisant, j’utilise la fonction =UNIQUE(Ventes[Rep]). Ceci génère la liste des représentants uniques. En ce moment, cette liste possède donc 3 éléments.

 

Fonction matricielle UNIQUE

 

Habitué de travailler avec Excel et besoin de vous mettre à jour avec les nouvelles fonctionnalités qu’offre maintenant Office 365 ? Pour concevoir des solutions en phase avec les développements technologiques actuels, jetez un œil à nos formations sur Office 365.

 

Les fonctions traditionnelles qui accompagnent les nouvelles fonctions matricielles dans Excel

Au-delà de générer la liste des représentants uniques, j’aimerais afficher les ventes par représentant. Je cherche donc une formule qui me permettra de faire la somme des ventes par représentant et qui s’ajustera également, lorsque j’ajouterai ou que je supprimerai des représentants, dans ma base de données initiale.

 

La fonction Excel SOMME.SI avec la fonction matricielle UNIQUE

 

Utilisation conventionnelle

Si j’utilise la fonction SOMME.SI, de façon usuelle, j’obtiendrai les totaux attendus par représentant.

 

Fonction Excel SOMME.SI

 

Utilisation moderne (avec le #)

Par contre, à l’ajout d’un nouveau représentant, la formule ne sera pas copiée sur la nouvelle ligne. À droite, vous pouvez observer que Léon apparaît bien dans ma liste de représentants, puisque j’ai utilisé la fonction matricielle dynamique UNIQUE pour générer cette liste. Par contre, le total des ventes de Léon n’est pas affiché, car j’ai utilisé la fonction SOMME.SI de façon “conventionnelle”. Il me faudrait donc ajouter manuellement la formule sur cette nouvelle ligne, ce qui n’est pas souhaitable. En effet, cela ne permettrait pas de générer des résultats dynamiques. L’usage même de la fonction matricielle dynamique UNIQUE se verrait alors plutôt limitatif. Nous souhaitons plutôt un comportement comme celui de gauche où les ventes de Léon ont été calculées et affichées automatiquement. Nous cherchons donc à recréer une fonction matricielle SOMME.SI.

 

Fonctions Excel

 

Pour y parvenir, il suffit de modifier légèrement la façon de construire notre fonction SOMME.SI. Plutôt que de référer le paramètre “critère” à une cellule précise, on le fera plutôt pointer sur la première cellule de la fonction matricielle dynamique UNIQUE. Dans ce cas-ci, il s’agit de la cellule B20. Et on s’assurera de faire suivre la référence cellulaire d’un symbole #. Ceci indique à la fonction SOMME.SI d’utiliser la valeur de la cellule de la liste de représentants qui correspond à la ligne en question. De même, la plage des résultats fournis par cette fonction matricielle SOMME.SI sera elle aussi dynamique. Elle s’allongera ou se rétrécira en fonction du nombre d’éléments générés par la fonction UNIQUE. C’est exactement le résultat recherché ! Nous avons donc ici une fonction matricielle SOMME.SI.

 

SOMME.SI avec #

 

La seule chose à éviter maintenant, c’est d’insérer des informations dans les cellules sous ces listes. On veut, en effet, éviter d’obtenir des messages d’erreurs #EPARS!.

 

Formation complémentaire

Afin de mettre à jour vos connaissances sur les fonctions et fonctionnalités les plus récentes d’Excel, via O365, nous vous recommandons notre formation Excel O365 – Bienvenue dans l’ère moderne!.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Le CFO masqué - Commentaires d'apprenants ayant suivi la formation : Excel O365 - Bienvenue dans l'ère moderne

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

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.

Scroll to Top