Un excel tableau formule est une formule qui effectue des calculs sur les valeurs d'un ou de 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 normalement stockées dans des cellules adjacentes d'une feuille de calcul. Ce guide explique comment utiliser des formules matricielles dans toutes les versions modernes d'Excel.
Ce didacticiel est compatible avec la plupart des versions d'Excel, notamment 2007, 2010, 2013, 2016 et Office 365.
Que sont les formules de tableau?
Les formules de tableau sont similaires aux formules régulières:
- Ils commencent par un signe égal ( = )
- Utilisez la même syntaxe que les formules ordinaires
- Utilisez les mêmes opérateurs mathématiques
- Suivez le même ordre d'opération
Dans Excel, les formules matricielles sont entourées d'accolades. { } - ces accolades ne peuvent pas être simplement tapées; ils doivent être ajoutés à une formule en appuyant sur la touche Ctrl , Décalage , et Entrer clés après avoir tapé la formule dans une cellule ou des cellules. Pour cette raison, une formule matricielle est parfois appelée un CSE formule dans Excel.
Une exception à cette règle est lorsque des accolades sont utilisées pour entrer un tableau en tant qu'argument pour une fonction qui ne contient normalement qu'une seule valeur ou une seule référence de cellule. Par exemple, dans le tutoriel ci-dessous, qui utilise VLOOKUP et le CHOISIR fonction pour créer une formule de recherche de gauche, un tableau est créé pour le CHOISIR les fonctions Index_num argument en tapant les accolades autour du tableau entré.
Étapes pour créer une formule de tableau
- Entrez la formule.
- Maintenez la Ctrl et Décalage touches du clavier.
- Appuyez et relâchez le Entrer clé pour créer la formule matricielle.
- Relâchez leCtrl et Décalage clés.
Si cela est fait correctement, la formule sera entourée d'accolades et chaque cellule contenant la formule contiendra un résultat différent.
Modification d'une formule de tableau
Chaque fois qu'une formule matricielle est modifiée, les accolades disparaissent autour de la formule matricielle. Pour les récupérer, il faut entrer la formule du tableau en appuyant sur la touche Ctrl, Décalage, et Entrer clés à nouveau, tout comme lors de la création de la formule matricielle.
Types de formules matricielles
Il existe deux types principaux de formules matricielles:
- Formules de matrice de cellules uniques qui effectuent plusieurs calculs dans une seule cellule de la feuille de calcul.
- Formules de tableau multicellulaires situées dans plusieurs cellules de la feuille de calcul.
Formules à matrice unique
Ce type de formule matricielle utilise une fonction, telle que SOMME, MOYENNE, ou COMPTER, pour combiner la sortie d’une formule matricielle multi-cellules en une seule valeur dans une seule cellule. Un exemple de formule de tableau de cellules unique serait:
{= SOMME (A1: A2 * B1: B2)}
Cette formule additionne le produit de A1 * B1 et A2 * B2, puis il renvoie un seul résultat dans une seule cellule de la feuille de calcul. Une autre façon d'écrire la formule ci-dessus serait:
Formules multi-cellules
Comme leur nom l'indique, ces formules de tableau se trouvent dans plusieurs cellules de la feuille de calcul et renvoient un tableau en tant que réponse. En d'autres termes, la même formule est située dans deux cellules ou plus et renvoie des réponses différentes dans chaque cellule.
Chaque copie, ou exemple, de la formule matricielle, effectue le même calcul dans chaque cellule qu’elle habite, mais chaque instance de la formule utilise des données différentes dans ses calculs. Par conséquent, chaque instance produit des résultats différents. Un exemple de formule de tableau de cellules multiples ressemblerait à ceci:
{= A1: A2 * B1: B2}
Si l'exemple ci-dessus est situé dans cellules C1 et C2 dans une feuille de calcul, les résultats seraient les suivants:
- Les données en A1 est multiplié par les données de B1et les résultats sont stockés dans cellule C1.
- Les données en A2 est multiplié par les données de B2et les résultats sont stockés dans cellule C2.
Utilisation de tableaux pour transposer des lignes et des colonnes
le TRANSPOSER La fonction est utilisée pour copier des données d’une ligne dans une colonne et inversement. Cette fonction est l’une des rares fonctions dans Excel qui doit toujours être utilisée comme formule matricielle.
Formules de tableaux et fonctions Excel
De nombreuses fonctions intégrées d'Excel, telles que SOMME, MOYENNE, et COMPTER, peut également être utilisé dans une formule matricielle. Il existe également quelques fonctions, telles que TRANSPOSER fonction, elle doit toujours être entrée sous forme de tableau pour fonctionner correctement. L’utilité de nombreuses fonctions telles que INDICE et RENCONTRE ou MAX et SI peut être étendu en les utilisant ensemble dans une formule matricielle.
Exemples de formules de tableaux dans Excel:
01 sur 08Créer une formule de tableau de cellules unique simple
Les formules à une seule cellule effectuent normalement d’abord un calcul multicellulaire, puis utilisent une fonction telle que MOYENNE ou SUM pour combiner la sortie du tableau en un seul résultat.
02 sur 08Ignorer les valeurs d'erreur lors de la recherche de données
Cette formule matricielle utilise le MOYENNE, SI, et ISNUMBER fonctions pour trouver la valeur moyenne des données existantes en ignorant les valeurs d'erreur telles que # DIV / 0! et #PRÉNOM?
03 de 08Compter les cellules de données
Utilisez le SOMME etSI fonctions dans une formule matricielle pour compter les cellules de données qui remplissent l'une de plusieurs conditions; cette technique diffère de l'utilisation d'Excel PAYTIFS fonction, qui exige que toutes les conditions définies soient remplies avant que la cellule ne soit comptée.
04 de 08Trouver le plus grand nombre positif ou négatif
Cet exemple combine les MAX fonction etSI fonction dans une formule matricielle qui trouvera la valeur la plus grande ou la plus grande pour une plage de données lorsque des critères spécifiques sont remplis.
05 de 08Trouver le plus petit nombre positif ou négatif
Semblable à l'exemple ci-dessus, celui-ci combine les MIN fonction et si fonctionne dans une formule matricielle afin de trouver la plus petite valeur ou la valeur minimale pour une plage de données lorsque des critères spécifiques sont remplis.
06 de 08Trouver la valeur moyenne ou médiane
le MÉDIAN function dans Excel trouve la valeur moyenne pour une liste de données. En le combinant avec le SI fonction dans une formule matricielle, la valeur médiane pour différents groupes de données connexes peut être trouvée.
07 de 08Faire une formule de recherche avec plusieurs critères
Cette formule matricielle implique l’imbrication du RENCONTRE et INDICE fonctions pour trouver des informations spécifiques dans une base de données.
08 de 08Faire une formule de recherche à gauche
le VLOOKUP normalement, la fonction ne recherche que les données situées dans les colonnes à droite, mais en les combinant avec le CHOISIR fonction, vous pouvez créer une formule de recherche à gauche qui recherchera des colonnes de données à gauche de la Lookup_value argument.
