Entrez dans la matrice

Pourquoi et comment utiliser le calcul matriciel ?

La notion de formule matricielle, dans Excel ou dans GoogleSheet, peut couvrir des réalités bien différentes. 

Il faut distinguer :

• Les matrices au sens mathématique du terme, pour lesquelles les tableurs proposent des formules spécifiques. Par exemple la formule DETERMAT pour calculer le déterminant d'une matrice. Ce n'est pas l'objet de cet article.

• Les formules matricielles proposées par les tableurs pour réaliser des calculs combinant plusieurs plages de cellules, avec ou sans critères. Par exemple SOMME.SI.ENS qui permet de faire le somme de valeurs associées à des critères. Ces formules fonctionnent très bien dans la majorité des cas mais : elles ont des limites, elles ont une syntaxe propre à respecter (avec des paramètres à renseigner dans un ordre précis) et sont déclinées selon les besoins en plusieurs fonctions à connaître.

• Les formulations matricielles qui consistent à utiliser les formules les plus courantes (SOMME, SI...), non pas avec une simple plage mais avec des produits de plusieurs plages contenant des valeurs ou des critères de sélection. C'est ce type de formulation que je développe dans le fichier joint.

Vous ne trouverez pas de documentation ad hoc sur la formulation matricielle mais des références nombreuses diluées dans l'ensemble de la documentation sur les tableurs et dans les forums d'utilisateurs. Ce n'est pas un outil en tant que tel mais une technique, une méthodologie, qui vous ouvrira à une utilisation plus avancée des fonctions des tableurs. C'est un peu la 3ème dimension des fonctions, au delà du cadre restreint des lignes et des colonnes. 

Dans le fichier joint vous trouverez pas moins de 25 exemples d'utilisation des formulations matricielles, classés par difficulté croissante. Ce catalogue n'est pas exhaustif;  la seule limite sera votre imagination et votre persévérance à combiner les pratiques apprises à l'issue de ce "voyage" en terres matricielles.

Télécharger le fichier d'exemples (fichier .xlsx sans macro)

Pourquoi utiliser des formulations matricielles ?

Lorsque vous devez travailler vite et produire un résultat synthétique sans passer par des outils comme le Tableau Croisée Dynamique (TCD) ou les requêtes Power Query, les formulations matricielles offrent un avantage. Elles s'intègrent sans difficulté dans des tableaux existants ou des formats prédéfinis (template).

Comment se construit une formulation matricielle ?

Une formulation matricielle combine des fonctions courantes qui vont chacune avoir un rôle précis :

• Les fonctions de transformation permettent de reformater une valeur ou un critère, notamment les fonctions de texte (TEXTE, GAUCHE, DROITE, STXT, TROUVE...)

• Les formules de condition permettent de transformer des évaluations en 1 ou en 0 et peuvent être multipliées entre elle pour renvoyer une matrice de sélection composée de 1 ou de 0. Ce sont les fonctions SI, N, SI.MULTIPLES, SI.CONDITIONS, SIERREUR, ESTNUM...

• Les fonctions d'agrégation vont faire la synthèse des conditions et des valeurs. Ce sont des fonctions très usuelles comme SOMME, MAX, MIN mais aussi des fonctions de texte comme CONCAT ou JOINDRE.TEXTE. La fonction d'agrégation est obligatoire; les fonctions de condition et de transformation sont complémentaires.

Par exemple, la syntaxe d'une formulation matricielle numérique, dans un cas complexe combinant ces trois rôles, pourra ressembler à ceci : 

= Agrégation ( Condition ( Transformation ( plage1 ) ) x Condition ( plage2  ) x Transformation ( plage3 ) x plage4 )

Dans le produit contenu par la fonction d'agrégation, les facteurs de Conditions renvoient une plage de sélection composée de 1 et de 0 et les autres facteurs une plage de valeurs. Dans le résultat ne sont agrégées que les valeurs multipliées par 1.

Dans le fichier d'exemples, je prends le soin de faire la décomposition matricielle de chaque formule pour comprendre ce qui se passe "à l'intérieur" du calcul.

Une vidéo de démonstration serait trop longue. Je préfère livrer ce fichier d'exemples à destination de ceux qui ont la curiosité et l'habitude de chercher des solutions à leurs problématiques de calculs. C'est un fichier en constante évolution que je construis depuis plus de vingt ans. Je l'utilise régulièrement pour initier et former des collègues ou des collaborateurs. Si vous avez des questions ou un challenge à me soumettre, n'hésitez pas à m'écrire à contact@xavierdupeux.fr.

Télécharger le fichier d'exemples (fichier .xlsx sans macro)

Peut-on utiliser les formulations matricielles en Visual Basic ?

Il est tout à fait possible d'utiliser des formules matricielles à l'intérieur d'un code en Visual Basic. Cela offre l'avantage d'obtenir simplement et rapidement des valeurs de synthèse utiles sans passer par des boucles ou des routines complexes.

Exemple d'application :

• j'ai une liste de 1000 chantiers, dont 600 en France

• je vais créer une boucle Do-Loop qui va scanner tous les chantiers (ligne par ligne)

• j'ai besoin que mon programme réagisse d'une certaine manière quand il atteint le dernier chantier en France parmi les 1000.

• pour cela je vais utiliser un compteur et j'ai besoin de connaitre à l'avance le nombre total de chantiers en France (600)

• avant le démarrage de ma boucle, je vais calculer le nombre total de chantiers en France et l'enregistrer dans une variable.

Il suffit d'utiliser la fonction Evaluate() qui permet d'obtenir directement le résultat d'une fonction Excel.

Exemple (avec des références à un tableau structuré nommé Tableau) :

ma_variable  = Evaluate ( [ SUM(N(Tableau[Pays]="France")*Tableau[PIB]) ] )

Si vous avez besoin de variabiliser les critères, vous pouvez remplacer les crochets par des guillemets mais cela vous obligent à rigoureusement doubler les guillemets qui encadrent les valeurs de texte dans la construction de votre chaîne de fonction.

ma_variable = Evaluate ( " SUM(N(" & nom_tableau & "[" & nom_colonne_pays & "]= """ & instance_pays & """)*" & nom_tableau & "[" & nom_colonne_pib & "]) " )