7.1 Les formules matricielles
05-Sept-2022
5 mins
TABLE DES MATIÈRES
Les formules matricielles sont de puissantes formules qui vous permettent d’effectuer des calculs complexes souvent impossibles avec des fonctions de feuille de calcul standard. Elles sont également appelées Ctrl-Maj-Entrée ou formules CSE, car vous devez appuyer sur Ctrl+Maj+Entrée pour les entrer.
Vous pouvez notamment utiliser des formules matricielles pour :
- Compter le nombre de caractères d’une plage de cellules.
- Additionner les nombres qui remplissent certaines conditions, telles que les valeurs les plus faibles d’une plage ou les nombres compris entre une limite supérieure et une limite inférieure ou encore additionner chaque nième valeur dans une plage de valeurs.
Excel fournit deux types de formules matricielles : celles qui effectuent plusieurs calculs pour générer un résultat unique (voir l’exemple ci-dessous) et celles qui calculent plusieurs résultats. Certaines fonctions de feuille de calcul renvoient des tableaux de valeurs ou nécessitent un tableau de valeurs comme argument.
Exemple de formule matricielle qui calcule un seul résultat :
Ce type de formule matricielle peut simplifier un modèle de feuille de calcul en remplaçant plusieurs formules différentes par une seule formule matricielle.
- Cliquez sur la cellule dans laquelle vous voulez entrer la formule matricielle.
- Entrez la formule à utiliser. Les formules matricielles utilisent une syntaxe de formule standard. Tout commence par un signe égal (=), et vous pouvez utiliser l’une des fonctions Excel intégrées dans vos formules matricielles.
- Par exemple, cette formule calcule la valeur totale d’une matrice contenant le montant épargné et place le résultat dans la cellule en regard de Valeur totale épargnée.
7.1.1 L’exemple MaxSI
L’enjeu sera de trouver dans une colonne la/les valeurs correspondant à la valeur maximale correspondant, particulièrement d’une date. Pour illustrer, vous travailler dans différents villages dans lesquels votre ONG a construit différentes structures (entrepôts, latrines, points d’eau, etc.) que vous visitez régulièrement. En effet, il est important pour vous de vérifier l’état de fonctionnalité de l’infrastructure. Celle-ci ont toutes un identifiant unique pour vous faciliter l’entrée de données. Cela ressemble à ça :
Vous avez donc 3 colonnes, pour vos infrastructures (ID), la date de visite, et la variable d’intérêt dans cet exemple à télécharger ici.
Etant donné que vos équipes visitent régulièrement ces villages, vous avez donc pour chaque infrastructure (id) plusieurs états de fonctionnalités. Vous voulez obtenir dans votre table de référence (où chaque infrastructure est listée), l’état de fonctionnalité à la date la plus récente.
Vous allez utiliser pour ça la combinaison de max et si (cette fonction existe dans les dernières versions d’excel, à partir de 2019 ou office 365 sous le nom MAX.SI.ENS)
Cette fonction va permettre de trouver dynamiquement la dernière date et son équivalent (c’est-à-dire la variable correspondant dans la colonne B (fonctionnalité). Pour ça, la syntaxe sera la suivante :
=MAX(SI([@Infrastructure]=visite[Code];visite[Date]))
Mais attention ! Si vous entrez la formule uniquement de cette façon-ci, vous n’aurez pas le bon résultat, vous devrez entrer la formule en utilisant Control + Alt + Entrée, de façon à créer une formule matricielle, pour vous retrouver avec la première date correspondant à chaque infrastructure, comme suit :
N’oubliez pas de toujours formater vos données en table, pour faciliter le référencement, et l’écriture de vos formules.
Vous pouvez télécharger ce fichier (version finale) pour vérifier la syntaxe de la formule le cas échéant.
7.1.2 L’exemple INDEX / EQUIV matriciel
Quand vous essayez de trouver une valeur unique dans un tableau correspondant à plusieurs critères, il peut être difficile de trouver la bonne formule pour cela. D’autant plus que quand vous essayer d’utiliser INDEX EQUIV pour trouver une donnée dans votre base de données avec de multiples conditions, vous allez expérimenter des difficultés. En effet, si l’on reprend l’exemple d’une base de données de suivi d’infrastructures, avec de multiples visites par infrastructures, par unités de temps, vous ne pourriez pas trouver avec un simple « INDEX/EQUIV » l’état de fonctionnalité d’une infrastructure spécifique, pour une date donnée. Imaginons par exemple que nous voulions trouver l’état de fonctionnalité équivalent à l’infrastructure ID-3 à la dernière date de visite.
Télechargez le fichier exemple ici.
Il faut donc que la formule :
- Trouve dans la liste des infrastructures l’ID correspondante (par exemple ID-1),
- Trouve ensuite la date de la dernière visite qui corresponde,
- Et renvoie ensuite l’état équivalent à ces deux coordonnées.
Le problème de la fonction INDEX/EQUIV classique sera que votre formule va « s’arrêter » a la première ligne où elle trouvera le code infrastructure (donc ID-1), puis trouver la date de dernière visite, et va ensuite renvoyer une erreur puisque la date de dernière visite n’est jamais l’entrée correspondant au premier code (sauf dans le cas singulier où vous avez une seule entrée).
Vous aurez donc un renvoi de code d’erreur, quel que soit la syntaxe que vous utiliseriez. Il faut donc utiliser
Pour que cette formule fonctionne, vous devrez donc utiliser une formule matricielle (lien avec autres sections). Mais il va falloir adapter un peu la syntaxe de la formule pour cela.
En effet, on va écrire la formule de façon booléenne, c’est-à-dire en lui demandant de chercher une correspondance exacte (1 donc en langage booléen) avec tous les critères qui nous intéressent, ce qui équivaut à :
= INDEX(Colonne où on cherche la valeur ; EQUIV(1 ; (critère 1 = X)*(critère 2 = Y)* etc…)
Et la formule va donc chercher la cellule qui correspondra à tous les critères de façon à obtenir :
= INDEX(Colonne où on cherche la valeur ; EQUIV(1 ;1-1*1*1 etc…)
Dans notre fichier exemple, cela veut dire que l’on va utiliser la syntaxe suivante :
= INDEX(visite[Etat];EQUIV(1;(@[Date de dernière visite]=visite[Date])*([@Infrastructure]=visite[Code]);0))
Ce qui vous permettra de trouver le résultat unique correspondant la date de la dernière visite pour un code d’infrastructure donné, ce que vous pouvez vérifier dans ce fichier.