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.
Habitué de travailler avec Excel et besoin de vous mettre à jour avec les nouvelles fonctionnalités qu’offre maintenant Excel 365 ? Pour concevoir des solutions en phase avec les développements technologiques actuels, jetez un œil à nos formations sur Excel 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.
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.
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.
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 :
La 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é.
