Définitions
Dans son sens mathématique une matrice est une collection ordonnée d'éléments. Autrement dit... un tableau. Excel traite ces collections sous forme d'un tableau interne ou array. Les fonctions matricielles, qui effectuent des calculs sur ces tableaux internes, renvoient soit une valeur soit un tableau de valeurs.Le calcul matriciel permet de faire en une seule opération des calculs qui demanderaient normalement des étapes intermédiaires et notamment la création de colonnes supplémentaires.
Ces formules matricielles permettent par exemple :
- De calculer la moyenne du logarithme des valeurs de la plage A1:B10 sans créer un tableau intermédiaire contenant les valeurs de ces logarithmes. La matrice est ici l'ensemble des valeurs contenues dans A1:B10
- De renvoyer dans deux cellules adjacentes les paramètres a et b décrivant l'équation d'une droite de régression y=ax+b. Cette fonction travaille avec deux matrices contenant respectivement les valeurs des x et des y.
- De rechercher des données et d'effectuer des calculs sur des valeurs répondant à plusieurs conditions simultanément. Cette utilisation ouvre des possiblités considérables, notamment pour analyser des données. Elle n'est pour ainsi dire pas documentée dans l'aide d'excel.
Formules matricielles intégrées d'excel
Certaines fonctions intégrées d'excel sont des fonctions matricielles. C'est le cas notamment de nombreuses fonctions statistiques. Les particularités de chacune de ces fonctions ne seront pas décrites ici, l'aide en ligne est en général bien faite une fois que l'on a compris le principe.Prenons un exemple : vous disposez en A1:A10 de mesures du taux de sucre dans des melons et, en B1:B10 du nombre d'heures d'ensoleillement la semaine précédent la cueillette. Vous cherchez à déterminer si ces valeurs sont reliées entre elles par une relation linéaire, de type y=ax+b.
La fonction DROITEREG a deux paramètres obligatoires : référence de la plage contenant les valeurs de Y, référence la plage contenant les X.
Le troisième paramètre, optionnel, permet de récupérer des calculs statistiques supplémentaires. Oublions le ici.
Si je tape dans une cellule =DROITEREG(B1:B10;A1:A10) je récupère une seule valeur, la pente de la droite, c'est à dire le a de y=ax+b.
Pour récupérer a et b, il faut dans l'ordre :
- sélectionner deux cellules adjacentes, par exemple D1:E1
- taper la formule
- Utiliser la validation matricielle (dite entre nous validation chirogourdiste !) : appuyer en même temps sur les touches Ctrl, majuscule et Enter
Attention : entrer manuellement les accolades ne transforme pas une formule standard en formule matricielle. Il ne faut pas non plus comme dans word essayer d'entrer d'abord les accolades puis la formule à l'intérieur. C'est l'ensemble de la formule qui est matricielle : on ne peut pas avoir dans une cellule {=ligne()}-2 par exemple...
Modification d'une formule matricielle
Si vous cliquez dans une cellule et que vous voyez des accolades de part et d'autre de la formule qu'elle contient, c'est que cette cellule fait partie d'une plage dans laquelle une formule matricielle a été entrée. Toutes les cellules de cette plage sont liées entre elles : vous ne pouvez pas effacer, modifier ou supprimer une cellule isolément et si vous essayez vous aurez un message d'erreur vous disant que vous ne pouvez pas modifier une partie de matrice. Il faut sélectionner l'ensemble de la plage matricielle (dans notre exemple D1:E1), cliquer dans la barre de formule modifier et revalider avec la combinaison Ctrl+Shift+enter de façon à ce que la formule soit modifiée dans l'ensemble de la plage. Vous pouvez en revanche sans problème utiliser les références à une seule cellule d'une plage matricielle pour faire d'autres calculs dans la feuille.De même, si vous supprimez une valeur ou une cellule à laquelle fait référence une formule matricielle, vous obtiendrez une valeur d'erreur. Dans notre exemple, vous ne pouvez pas par exemple effacer la valeur en A10, mais en revanche vous pouvez la modifier.
Calculs matriciels
En dehors des fonctions matricielles intégrées d'excel, on peut créer soi même des formules matricielles pour effectuer des calculs sur des plages de données. Ceci ne requiert pas de macros ou de langage VBA. Toutes les formules matricielles doivent être validées par Ctrl+Maj+Enter. L'oubli de cette "validation chirgourdiste" est une source fréquente d'erreurs et aucun message d'alerte ne vous l'indique.
Attention : on ne peut pas dans une formule matricielle faire référence à une ligne ou à une colonne entière.Quand une cellule matricielle fait appel à plusieurs matrices, il faut impérativement que les matrices aient la même taille.
Il y a d'innombrables situations dans excel où l'on a besoin de travailler sur une plage de données et qui sont résolues avec une formule matricielle. Les quelques exemples ci-dessous vous donnent un aperçu des problèmes qu'il est possible de résoudre. La lecture des nombreux forums, sites web et livres consacrés à excel vous fournira une multitude d'autres exemples.
Entrer une même formule dans une plage de données en une seule fois
Si vous avez des nombres en A1:A10 et que vous voulez entrer pour chacun leur partie entière, vous pouvez écrire en B1 =ENT(A1) et tirer cette formule vers le bas. Vous pouvez aussi sélectionner la plage B1:B10, taper=ENT(B1:B10) et faire une validation matricielle Ctrl+Maj+Ent. L'intérêt de cette approche, outre que c'est assez rapide, c'est de lier ensemble toute une plage de données en y insérant la même formule. C'est une excellente protection si vous voulez empêcher qu'une formule soit modifiée, effacée ou remplacée par une valeur "en dur". Dans notre exemple, il n'est pas non plus possible par la suite de supprimer une des lignes 1 à 10 ou d'en insérer une entre ces lignes.Astuce : si vous voulez empêcher sur une feuille l'insertion ou la suppression de lignes entre les lignes 3 et 7, sélectionnez par exemple la plage AA3:AA7 (la colonne est à votre choix !) et tapez ="" puis validez par Ctrl+Maj+Enter. On ne voit rien dans ces cellules mais les lignes sont "protégées". De même vous entrerez une formule matricielle en B12:F12 pour protéger les colonnes B à F. Rien ne vous empêche bien sur de tapez {="protégé !"} si vous voulez visualiser ce qui l'est.
Numéroter les lignes d'un tableau
Vous avez un tableau de résultats en F12:K30. Vous voulez numéroter les lignes de ce tableau en commençant à 1 (sur la ligne 12) . Sélectionnez F12:F30 et entrez avec une validation matricielle :{=ligne()-11}. Votre tableau sera ainsi en plus protégé contre des délétions ou insertions de lignes malencontreuses.Calculs utilisant une matrice et renvoyant une valeur
Pour calculer la moyenne de la partie entière des nombres écrits en A1:A10, vous pouvez bien sur écrire en B1:B10 la valeur de la partie entière correspondante puis écrire en C1=MOYENNE(B1:B10).Si en C1 vous écrivez = MOYENNE(ENT(A1:A10)) en validant par Enter cette formule, vous obtenez #VALEUR!. Mais si vous validez par Ctrl+Maj+Enter, excel commence par calculer pour chaque valeur de la plage A1:A10 sa partie entière et fait ensuite la moyenne de ces valeurs. Si vous n'avez pas besoin de voir la partie entière de chacune de vos valeurs, il est donc intéressant d'utiliser une fonction matricielle.
Calculs utilisant plusieurs matrices et renvoyant une valeur.
Opérations sur des cellules répondant à plusieurs conditions : Cas particulier de la fonction SOMMEPROD
Excel est un outil puissant pour analyser des données. Les fonctions intégrées SI, SOMME.SI et NB.SI permettent d'évaluer une condition et de faire des calculs en fonction du résultat. Il est très fréquent cependant que l'on ait besoin de tester plusieurs conditions simultanément, ou de tester si une condition est vérifiée sur une plage de données, ce qui nécessite de recourir à des fonctions matricielles. Cette utilisation, qui n'est pourtant pas intuitive, n'est pour ainsi dire pas documentée dans l'aide d'excel. Elle se confond pour l'essentiel avec l'usage avancé de la fonction SOMMEPROD qui est une fonction très particulière d'excel : c'est une fonction matricielle qui n'a PAS BESOIN d'être validée de facon "chirogourdiste" avec Ctrl+maj+enter. Dans son usage classique, cette fonction qui reçoit des plages comme arguments, effectue une somme de produits : =SOMMEPROD(A1:A1;B1:B10) est équivalent à A1*B1+A2*B2+ ...+A10*B10. Elle donc équivalente à la formule matricielle {=SOMME(A1:A10*B1:B10)}. Mais SOMMEPROD permet d'effectuer bien d'autres choses que de simples additions de produits. Par exemple, on peut avec SOMMEPROD travailler sur des plages contenant du texte, tester des conditions multiples, travailler avec des classeurs fermés. Vous utiliserez cette fonction pour calculer par exemple le chiffre d'affaire de la vente des pommes entre juin et décembre, ou après le 12 juillet, ou par Pierre et Jeanne mais pas Luc...
Ces différentes utiolisations sont décrites en détail et avec des exemples sur cette page sur la fonction sommeprod
Extraire une valeur dans un tableau en fonction de deux critères
Dans un tableau contenant en colonne A des prénoms, en B des noms et en C des âges, comment récupérer l'âge de Laurent Legrand sachant qu'il y a d'autres Laurent et d'autres Legrand dans le tableau ?
Si dans la colonne D on concatène prénoms et noms, la combinaison des fonctions INDEX et EQUIV nous permet aisément de récupérer l'âge de laurent Legrand :
=INDEX(age;EQUIV("Laurent Legrand";prenomnom;0)) avec des plages nommées prenom, nom, age et prenomnom pour la concaténation.
Mais on peut éviter cela avec une formule matricielle :
{=INDEX(age;EQUIV("Laurent Legrand";prenom&" "&nom;0))}
Excel prend le premier élément de la plage nommée prenom" et le concatène avec le premier de la plage nommée nom puis fait de même pour chaque élément des deux plages. S'il n'y a pas le même nombre d'éléments dans les plages noms et prénoms, une valeur d'erreur est retournée. Attention, excel ne construit pas une matrice de toutes les associations possibles d'un prénom et d'un nom ! Il associe toujours le premier élément de la matrice 1 avec le premier de la matrice 2, le second avec le second et ainsi de suite. Les résultats de cette concaténation sont placés dans une matrice qui est passée en paramètre dans la fonction EQUIV. Comme la concaténation n'est pas écrite "en dur" mais uniquement stockée de façon temporaire, il est indispensable de valider cette formule de façon matricielle, faute de quoi elle renvoie #VALEUR!.
La dernière ligne utilisée dans un tableau
Il y a de très nombreuses façons de connaitre la dernière ligne utilisée dans une colonne, par formule ou par macro. Mais, dans un tableau où toutes les valeurs ne sont pas entrées, comment connaître la dernière ligne contenant une cellule non vide dans une des colonnes ?
Prenons le tableau ci-dessous situé en A1:C5 ou les x représentent une donnée quelconque et - des cellules vides
x x -
- - -
- x x
x - -
- - -
la formule matricielle {=Max(Ligne(1:5)*(A1:C5<>""))} renvoie le résultat attendu. Pourquoi ?
(A1:C5<>"") évalue chaque cellule de la plage et renvoie VRAI ou FAUX sous forme d'un tableau interne
VRAI VRAI FAUX
FAUX FAUX FAUX
FAUX VRAI VRAI
VRAI FAUX FAUX
FAUX FAUX FAUX
Ligne(1:5) renvoie une matrice contenant les N° de ligne
Ligne(1:5)*(A1:C5<>"") multiplie chaque valeur VRAI ou FAUX par le N° de la ligne correspondante :
1 1 0
0 0 0
0 3 3
4 0 0
0 0 0
MAX renvoie la plus grande valeur de ce tableau, ici 4 qui correspond bien au numéro de la dernière ligne contenant une valeur.
Avantages et inconvénients des formules matricielles.
L'avantage essentiel des formules matricielles est évidemment qu'elles permettent de traiter en une seule opération des plages de cellules ou des collections de valeurs. Elles étendent considérablement le champ des possibilités de calcul d'excel. L'inconvénient majeur de ces formules est qu'elles sont TRES gourmandes en ressources. Si vous traitez des plages de 5000 cellules, vous vous rendrez vite compte que les temps de calcul peuvent devenir prohibitifs. Un cas classique est le suivant : une formule matricielle fait référence à la plage A1:A5000. A chaque modification d'une seule valeur de la plage A1:A5000, la formule matricielle est recalculée. Si ceci se produit souvent, le classeur peut devenir inutilisable. Pensez à utiliser les modes de calculs manuels ou sur ordre pour limiter ces inconvénients...
Utiliser les formules matricielles en VBA
Il est fréquent d'utiliser des fonctions de feuille de calcul dans une macro. Ceci se fait de différentes manières :
- En utilisant l'équivalent VBA de la fonction : Worksheets("Sheet1" ).Range("A1" )= sum(B1:B10,C1:C10). Dans ce cas il faut impérativement utiliser le nom VBA (anglais) de la fonction et la virgule comme séparateur d'arguments.
- Toutes les fonctions de calcul intégrées de la feuille de calcul n'ont pas leur équivalent direct en VBA. Dans ce cas, on peut faire appel à la propriété .formula : Worksheets("Sheet1" ).Range("A1" ).formula= "=sum(B1:B10,C1:C10)". Ici aussi, il faut écrire le nom anglais de la fonction et la virgule comme séparateur d'arguments.
- En utilisant la propriété .formulalocal : Worksheets("Sheet1" ).Range("A1" ).FormulaLocal = "=somme(B1:B10;C1:C10). Dans ce cas, on utilise le point virgule comme séparateur d'argument et le nom de la fonction dans la langue locale.
Worksheets("Sheet1" ).Range("D1:E1" ).formulaArray = "=LINEST(B1:B10,A1:A10)"
Astuce : utilisez l'enregistreur de macro pour connaître le nom de la fonction utilisée en anglais.
Cette page a été vue 2417 fois.