Les formules matricielles

Les formules matricielles

 

Les formules matricielles sont des formules qui emploient une matrice de nombre pour retourner leur résultat. Nous utilisons, sans trop le savoir, plusieurs formules matricielles. La fonction SOMME.SI est un bon exemple. Il y a une opération de validation pour chaque cellule et ce sont uniquement les cellules qui respectent la condition qui seront additionnées. La fonction SOMMEPROD est un autre exemple de fonction matricielle.

Le hic, c’est qu’il n’existe pas de forme matricielle pour toutes les fonctions. Les fonctions MAX.SI ou encore GRANDE.VALEUR.SI n’existent pas. Pourtant, il se peut que vous vouliez trouver la plus grande valeur ou la Nième plus grande valeur d’une série qui respecte une condition. C’est là qu’il est important de connaitre comment transformer une fonction ordinaire en fonction matricielle.

Le fichier que vous pouvez télécharger en cliquant sur le lien ci-dessous contient une série de 1500 nombres de 1 à 10 000.

Fichier exemple formule matricielle

À côté de chaque nombre on retrouve une lettre (A, B, C ou D). Il est facile d’extraire la plus grande valeur de cette série avec la fonction MAX. On peut aussi extraire la Nième plus grande valeur avec la fonction GRANDE.VALEUR. Mais si on veut connaitre la 3e plus grande valeur qui est associée à la lettre D, on devra créer une nouvelle colonne, y mettre un test logique (SI) pour vérifier si c’est bien la lettre D, ensuite appliquer un filtre pour garder seulement les lignes qui passent le test et finalement trouver la valeur MAX.

Tout ça peut être fait en une seule opération avec l’aide de la fonction GRANDE.VALEUR qui utilisera une fonction SI en mode matriciel. En faisant cela, on fera comme si on avait ajouté le test logique sur chacune des 1500 lignes.

Pour réussir cela, on devra utiliser une fonction SI pour l’argument Matrice de la fonction GRANDE.VALEUR. L’argument (qui est le rang recherché; 1er, 2e, 3e, etc.) sera un chiffre normal. Ensuite… Roulement de tambour… On appuie sur Ctrl et Maj en même temps que la touche Entrée pour finaliser la fonction. La combinaison de touches avisera Excel qu’il doit effectuer la validation de la fonction SI de façon matricielle avant de retourner la Nième plus grande valeur.

En regardant dans la barre de formules, on peut voir qu’il y a une accolade «{» au début et une autre «}» à la fin. Ce symbole signifie que la fonction travaille en mode matriciel. On ne peut ajouter les accolades manuellement à une fonction. Il faut utiliser la combinaison de touches mentionnée précédemment pour que ça fonctionne.

Comme pour toute chose, la pratique vous permettra de devenir habile avec ce genre de fonction. Les chances sont fortes pour que votre premier essai ne soit pas un succès mais avec un peu de patience et quelques essais-erreurs, vous maîtriserez un nouvel aspect d’Excel. Bon plaisir!

About the author

Monsieur Excel administrator

Je suis utilisateur d'Excel depuis 2000 et formateur depuis 2012. Excel, j'en mange! Je crois que toutes les entreprises devraient avoir leur "spécialiste" Excel pour permettre d'analyser, manipuler, transformer et améliorer les données rapidement et efficacement. Trop de temps est perdu chaque jour à faire ces opérations de façon manuelle. La solution : Monsieur Excel!