Une formule Excel array est une formule qui effectue des calculs sur les valeurs d'un ou plusieurs tableaux plutôt que sur une seule valeur de données. Dans les tableurs, un tableau est une plage ou une série de valeurs de données associées qui se trouvent généralement dans des cellules adjacentes d'une feuille de calcul.
Ces instructions s'appliquent à Excel 2019, 2016, 2013, 2010 et Excel pour Microsoft 365.
Que sont les formules matricielles ?
Les formules matricielles sont similaires aux formules régulières. Ils:
- Commencer par un signe égal (=)
- Utiliser la même syntaxe que les formules régulières
- Utiliser les mêmes opérateurs mathématiques
- Suivez le même ordre d'opération
Cependant, les formules matricielles sont entourées d'accolades { }. Et vous ne pouvez pas simplement les taper; vous devez les ajouter en appuyant sur les touches Ctrl, Shift et Enter après avoir saisi la formule dans un cellule ou cellules. Pour cette raison, une formule matricielle est parfois appelée formule CSE dans Excel.
Chaque fois que vous modifiez une formule matricielle, les accolades disparaissent. Pour les récupérer, appuyez à nouveau sur les touches Ctrl, Shift et Enter.
Il existe deux principaux types de formules matricielles:
- Formules matricielles à cellule unique qui effectuent plusieurs calculs dans une seule cellule de feuille de calcul
- Formules matricielles multi-cellules qui se trouvent dans plus d'une cellule de feuille de calcul
Comment créer une formule matricielle
- Entrez la formule dans une cellule.
- Maintenez enfoncées les touches Ctrl et Shift du clavier.
- Appuyez et relâchez la touche Entrée pour créer la formule matricielle.
- Relâchez les touches Ctrl et Shift.
- Si c'est fait correctement, des accolades entoureront la formule.
Formules de tableaux à cellule unique
Une formule matricielle à cellule unique utilise une fonction, telle que SUM, AVERAGE ou COUNT, pour combiner la sortie d'une formule matricielle à plusieurs cellules en une seule valeur dans une seule cellule. Voici un exemple:
{=SOMME(A1:A2B1:B2)}
La formule ci-dessus additionne le produit de A1B1 et A2B2, puis elle renvoie un seul résultat dans un cellule unique dans la feuille de calcul. Une autre façon de présenter cette formule est:
=(A1B1)+(A2B2)
Formules de tableaux multi-cellules
Comme leur nom l'indique, les formules matricielles à plusieurs cellules se trouvent dans plusieurs cellules de feuille de calcul et renvoient un tableau comme réponse. En d'autres termes, la même formule se trouve dans deux cellules ou plus et renvoie des réponses différentes dans chaque cellule.
Chaque copie, ou instance, de la formule matricielle effectue le même calcul dans chaque cellule qu'elle occupe, mais chacune utilise des données différentes. Par conséquent, chacun produit des résultats différents. Un exemple de formule matricielle à plusieurs cellules est:
{=A1:A2B1:B2}
Si la formule matricielle ci-dessus se trouve dans cellules C1 et C2 dans une feuille de calcul, les résultats seraient les suivants:
- Les données de A1 sont multipliées par les données de B1, et les résultats apparaissent dans cell C1.
- Les données de A2 sont multipliées par les données de B2, et les résultats apparaissent dans cell C2.
Formules matricielles et fonctions Excel
Vous pouvez utiliser de nombreuses fonctions intégrées d'Excel, telles que SUM, AVERAGE et COUNT , dans une formule matricielle. Il existe également quelques fonctions, telles que TRANSPOSE, qui doivent toujours être une formule matricielle pour fonctionner correctement. (La fonction TRANSPOSE copie les données d'une ligne dans une colonne ou vice versa.)
Vous pouvez également étendre l'utilité de nombreuses fonctions telles que INDEX et MATCH ou MAXet IF en les utilisant ensemble dans une formule matricielle.
Créer une formule de tableau simple à cellule unique
Les formules matricielles à cellule unique effectuent généralement d'abord un calcul multi-cellule, puis utilisent une fonction telle que AVERAGE ou SUM pour combiner la sortie du tableau en un seul résultat.
Ignorer les valeurs d'erreur lors de la recherche de données
Cette formule matricielle utilise les fonctions AVERAGE, IF et ISNUMBER pour trouver le valeur moyenne pour les données existantes tout en ignorant les valeurs d'erreur telles que DIV/0! et NAME?
Compter les cellules de données
Utilisez les fonctions SUM et IF dans une formule matricielle pour compter les cellules de données qui remplissent l'une des conditions suivantes; cette technique diffère de l'utilisation de la fonction COUNTIFS d'Excel, qui exige que toutes les conditions définies soient remplies avant de compter la cellule.
Trouver le plus grand nombre positif ou négatif
Cet exemple combine la fonction MAX et la fonction IF dans une formule matricielle qui trouvera la valeur la plus grande ou maximale pour une plage de données lorsqu'elles répondent à des critères précis. Ici, la plus grande valeur représente le temps le plus lent.
Trouver le plus petit nombre positif ou négatif
Similaire à l'exemple ci-dessus, vous pouvez combiner les fonctions MIN et IF dans une formule matricielle pour trouver la valeur la plus petite ou minimale pour une plage de données lorsqu'elle répond à des critères spécifiques.
Trouver la valeur moyenne ou médiane
La fonction MEDIAN dans Excel trouve la valeur médiane d'une liste de données. En la combinant avec la fonction IF dans une formule matricielle, vous pouvez trouver la valeur médiane pour différents groupes de données associées.
Créer une formule de recherche avec plusieurs critères
Cette formule matricielle implique l'imbrication des fonctions MATCH et INDEX pour trouver des informations spécifiques dans une base de données.
Faire une formule de recherche à gauche
La fonction VLOOKUP ne recherche généralement que les données situées dans les colonnes à droite, mais en la combinant avec la fonction CHOOSE, vous peut créer une formule de recherche à gauche qui recherchera des colonnes de données à gauche de l'argument Lookup_value.
Lorsque vous entrez un tableau comme argument pour une fonction qui ne contient généralement qu'une seule valeur ou référence de cellule, vous pouvez taper directement les accolades, plutôt que d'utiliser le Ctrl+Maj+Entréecombinaison de touches, comme dans l'exemple ci-dessus.
