Instructions et exemples de formules matricielles

Une formule de tableau est une formule qui peut effectuer plusieurs calculs sur un ou plusieurs éléments d'un tableau. Vous pouvez considérer un tableau comme une ligne ou une colonne de valeurs, ou une combinaison de lignes et de colonnes de valeurs. Les formules de tableau peuvent renvoyer soit plusieurs résultats, soit un seul résultat.

À partir de la mise à jour de septembre 2018 pour Microsoft 365, toute formule pouvant renvoyer plusieurs résultats les déverse automatiquement soit vers le bas, soit en travers dans les cellules voisines. Ce changement de comportement s'accompagne également de plusieurs nouvelles fonctions de tableau dynamique. Les formules de tableau dynamique, qu'elles utilisent des fonctions existantes ou les fonctions de tableau dynamique, ne doivent être saisies que dans une seule cellule, puis confirmées par la touche Entrée. Auparavant, les anciennes formules de tableau nécessitaient d'abord de sélectionner toute la plage de sortie, puis de confirmer la formule avec Ctrl+Shift+Enter. Elles sont communément appelées formules CSE.

Vous pouvez utiliser des formules de tableau pour effectuer des tâches complexes, par exemple :

  • Créez rapidement des ensembles de données échantillons.

  • Comptez le nombre de caractères contenus dans une plage de cellules.

  • Additionnez uniquement les nombres qui remplissent certaines conditions, comme les valeurs les plus basses d'une plage, ou les nombres qui se situent entre une limite supérieure et inférieure.

  • Faire la somme de chaque Nième valeur dans une plage de valeurs.

Les exemples suivants vous montrent comment créer des formules de tableau à cellules multiples et à cellule unique. Dans la mesure du possible, nous avons inclus des exemples avec certaines des fonctions de tableau dynamique, ainsi que des formules de tableau existantes saisies sous forme de tableaux dynamiques et traditionnels.

Téléchargez nos exemples

Téléchargez un classeur d'exemples contenant tous les exemples de formules de tableau présentés dans cet article..

Cet exercice vous montre comment utiliser des formules matricielles à plusieurs cellules et à cellule unique pour calculer un ensemble de chiffres de vente. La première série d’étapes utilise une formule à plusieurs cellules pour calculer un ensemble de sous-totaux. La seconde série utilise une formule à cellule unique pour calculer un total général.

  • Formule de tableau à plusieurs cellules

    Fonction de tableau à plusieurs cellules H10 =F10:F19*G10:G19 pour calculer le nombre de voitures vendues en prix unitaire

  • Ici, nous calculons les ventes totales de coupés et de berlines pour chaque vendeur en entrant =F10:F19*G10:G19 dans la cellule H10.

    Lorsque vous appuyez sur Entrée, vous verrez les résultats s'étendre aux cellules H10:H19. Remarquez que la plage de déversement est mise en évidence par une bordure lorsque vous sélectionnez une cellule de la plage de déversement. Vous pouvez également remarquer que les formules des cellules H10:H19 sont grisées. Elles ne sont là qu'à titre de référence, donc si vous voulez ajuster la formule, vous devrez sélectionner la cellule H10, où se trouve la formule principale.

  • Formule de réseau unicellulaire

    Formule de tableau à cellule unique pour calculer le total général avec =SUM(F10:F19*G10:G19)

    Dans la cellule H20 du classeur d'exemple, tapez ou copiez-collez =SUM(F10:F19*G10:G19) , puis appuyez sur Entrée.

    Dans ce cas, Excel multiplie les valeurs dans le tableau (la plage de cellules F10 à G19), puis utilise la fonction SUM pour additionner les totaux. Le résultat produit un total général de 1 590 000 dollars de ventes.

    Cet exemple illustre la puissance de ce type de formule. Par exemple, supposons que vous disposez de 1000 lignes de données. Vous pouvez additionner tout ou partie de ces données en créant une formule de tableau dans une seule cellule au lieu de faire glisser la formule vers le bas sur les 1000 lignes. Remarquez également que la formule à une cellule de la cellule H20 est totalement indépendante de la formule à plusieurs cellules (celle des cellules H10 à H19). C'est un autre avantage de l'utilisation des formules de tableau – la flexibilité. Vous pourriez modifier les autres formules de la colonne H sans affecter la formule de la colonne H20. Il peut également s'avérer utile de disposer de totaux indépendants comme celui-ci, car cela permet de valider l'exactitude de vos résultats.

  • Les formules de tableau dynamique offrent également ces avantages :

    • Cohérence    Si vous cliquez sur n'importe quelle cellule à partir de H10 vers le bas, vous obtenez la même formule. Cette cohérence peut être synonyme de plus grande précision.

    • Sécurité    Vous ne pouvez pas écraser un composant d'une formule de tableau à plusieurs cellules. Par exemple, cliquez sur la cellule H11 et appuyez sur Supprimer. Excel ne changera pas la sortie du tableau. Pour le modifier, vous devez sélectionner la cellule supérieure gauche du tableau, ou cellule H10.

    • Des fichiers de plus petite taille    Il est souvent possible d’utiliser une seule formule de tableau au lieu de plusieurs formules intermédiaires. Par exemple, l'exemple de vente de voitures utilise une formule de tableau pour calculer les résultats de la colonne E. Si vous aviez utilisé des formules standard telles que =F10*G10, F11*G11, F12*G12, etc., vous auriez utilisé 11 formules différentes pour calculer les mêmes résultats. Ce n'est pas un gros problème, mais que faire si vous avez des milliers de lignes à totaliser ? Alors cela peut faire une grande différence.

    • Efficacité    Les fonctions de tableau peuvent être un moyen efficace de construire des formules complexes. La formule du tableau =SUM(F10:F19*G10:G19) est la même que celle-ci : =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Renversement    Les formules de tableau dynamique se répandront automatiquement dans la plage de sortie. Si vos sources de données se trouvent dans un tableau Excel, les formules de votre tableau dynamique seront automatiquement redimensionnées lorsque vous ajouterez ou supprimerez des données.

    • #SPILL ! erreur    Les tableaux dynamiques ont introduit l'erreur #SPILL!ce qui indique que la plage de déversement prévue est bloquée pour une raison quelconque. Lorsque vous aurez résolu le blocage, la formule se déversera automatiquement.

Les constantes de tableau sont un composant des formules de tableau. Vous créez des constantes de tableau en saisissant une liste d'éléments, puis en entourant manuellement la liste d'accolades ({ }), comme ceci :

={1.2.3.4.5} ou ={«Janvier».«Février».«Mars»}

Si vous séparez les éléments à l’aide de virgules, vous créez un tableau horizontal (une ligne). Si vous séparez les éléments à l’aide de points-virgules, vous créez un tableau vertical (une colonne). Pour créer un tableau à deux dimensions, vous délimitez les éléments de chaque ligne par des virgules et vous délimitez chaque ligne par des points-virgules.

Les procédures suivantes vous permettront de vous entraîner à créer des constantes horizontales, verticales et en deux dimensions. Nous allons montrer des exemples d'utilisation de la fonction SEQUENCE pour générer automatiquement des constantes de tableau, ainsi que des constantes de tableau saisies manuellement.

  • Créer une constante horizontale

    Utilisez le classeur des exemples précédents ou créez un nouveau classeur. Sélectionnez une cellule vide et entrez =SEQUENCE(1,5). La fonction SEQUENCE construit un tableau de 1 ligne par 5 colonnes de la même manière que ={1.2.3.4.5}. Le résultat suivant s'affiche :

    Créer une constante de matrice horizontale avec =SEQUENCE(1,5) ou ={1,2,3,4,5}

  • Créer une constante verticale

    Sélectionnez une cellule vide avec un espace en dessous, et entrez =SEQUENCE(5), ou ={1;2;3;4;5}. Le résultat suivant s'affiche :

    Créer une constante de matrice verticale avec =SEQUENCE(5), or ={1;2;3;4;5}

  • Créer une constante à deux dimensions.

    Sélectionnez une cellule vide avec de l'espace à droite et en dessous, et entrez =SEQUENCE(3,4). Vous obtenez le résultat suivant :

    Créer une constante de matrice comprenant 3 lignes de 4 colonnes avec =SEQUENCE(3,4)

    Vous pouvez également saisir : ou ={1.2.3.4;5.6.7.8;9.10.11.12}, mais vous devrez faire attention à l'endroit où vous placez les points-virgules par rapport aux virgules.

    Comme vous pouvez le constater, l'option SEQUENCE offre des avantages considérables par rapport à la saisie manuelle des valeurs constantes de votre tableau. En premier lieu, cela vous permet de gagner du temps, mais cela peut également contribuer à réduire les erreurs dues à la saisie manuelle. Il est également plus facile à lire, d'autant plus que les points-virgules peuvent être difficiles à distinguer des séparateurs de virgules.

Voici un exemple qui utilise des constantes de tableau dans le cadre d'une formule plus importante. Dans le classeur type, accédez à la feuille de calcul Constante dans une formule, ou créez une nouvelle feuille de calcul.

Dans la cellule D9, nous avons saisi =SEQUENCE(1,5,3,1), mais vous pouvez également saisir 3, 4, 5, 6 et 7 dans les cellules A9:H9. Cette sélection de chiffres n'a rien de spécial, nous avons simplement choisi un chiffre autre que 1-5 pour la différenciation.

Dans la cellule E11, entrez =SUM(D9:H9*SEQUENCE(1,5)) , ou =SUM(D9:H9*{1.2.3.4.5}). Les formules donnent 85.

Utiliser des constantes de matrice dans des formules. Dans cet exemple, nous avons utilisé =SUM(D9:H(*SEQUENCE(1,5))

La fonction SEQUENCE construit l'équivalent de la constante du tableau {1.2.3.4.5}. Étant donné qu' Excel effectue d'abord des opérations sur les expressions entre parenthèses, les deux éléments suivants qui entrent en jeu sont les valeurs des cellules de D9:H9 et l'opérateur de multiplication (*). À ce stade, la formule multiplie les valeurs dans le tableau stocké par les valeurs correspondantes dans la constante. Cela équivaut à :

= =SUM(D9*1,E9*2,F9*3,G9*4,H9*5), ou =SUM(3*1,4*2,5*3,6*4,7*5)

Finalement, la fonction SUM additionne les valeurs et renvoie 85.

Pour éviter d'utiliser le tableau stocké et garder l'opération entièrement en mémoire, vous pouvez le remplacer par une autre constante de tableau :

=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)) , ou =SUM({3.4.5.6.7}*{1.2.3.4.5})

Éléments que vous pouvez utiliser dans les constantes de tableau

  • Les constantes de tableau peuvent contenir des nombres, du texte, des valeurs logiques (telles que VRAI et FAUX) et des valeurs d'erreur telles que #N/A. Vous pouvez utiliser les nombres dans les formats entier, décimal et scientifique. Si vous incluez du texte, vous devez l'entourer de guillemets («texte»).

  • Les constantes de matrice ne peuvent pas contenir de tableaux, formules ou fonctions supplémentaires. En d’autres termes, elles ne peuvent contenir que du texte ou des chiffres séparés par des virgules ou des points-virgules. Excel affiche un message d’avertissement lorsque vous entrez une formule telle que {1.2.A1:D4} ou {1.2.SOMME(Q2:Z8)}. Par ailleurs, les valeurs numériques ne peuvent pas contenir de signes de pourcentage ou de dollar, de virgules ou de parenthèses.

L'une des meilleures façons d'utiliser les constantes de tableau est de les nommer. Les constantes nommées peuvent être beaucoup plus faciles à utiliser et peuvent dissimuler une partie de la complexité de vos formules aux autres utilisateurs. Pour nommer une constante de matrice et l’utiliser dans une formule, procédez comme suit :

Allez dans Formules > Noms définis > Définir le nom. Dans la case Nom, tapez Quarter1. Dans la case Réfère à, entrez la constante suivante (n'oubliez pas de taper les accolades manuellement) :

={"Janvier"."Février"."Mars"}

La boîte de dialogue doit maintenant ressembler à ceci :

Ajouter une constante de matrice nommée à partir de Formules > Noms définis > Gestionnaire de noms > Nouveau

Cliquez sur OK, puis sélectionnez une ligne comportant trois cellules vides et saisissez =Quarter1.

Le résultat suivant s'affiche :

Utiliser une constante de matrice nommée, comme =Quarter1, où Quarter1 a été défini comme ={"January","February","March"}

Si vous voulez que les résultats se répandent verticalement au lieu d'horizontalement, vous pouvez utiliser =TRANSPOSE(Quarter1).

Si vous souhaitez afficher une liste de 12 mois, comme celle que vous pourriez utiliser pour établir un état financier, vous pouvez vous baser sur l'année en cours avec la fonction SEQUENCE. L'intérêt de cette fonction est que, même si seul le mois est affiché, il y a une date valide derrière, que vous pouvez utiliser dans d'autres calculs.. Vous trouverez ces exemples sur les feuilles de calcul Constante de tableau nommée et Ensemble de données d'échantillon rapide du classeur d'exemples.

=TEXTE(DATE( ANNÉE(AUJOURD'HUI()),SEQUENCE(1,12),1), «mmm»)

Utiliser une combinaison de TEXTE, DATE, ANNÉE, JOUR, et de fonctions SEQUENCE pour créer une liste dynamique de 12 mois

Elle utilise la fonction DATE pour créer une date basée sur l'année en cours, SEQUENCE crée un tableau constant de 1 à 12 pour les mois de janvier à décembre, puis la fonction TEXT convertit le format d'affichage en «mmm» (Jan, Feb, Mar, etc.). Si vous voulez afficher le nom complet du mois, par exemple janvier, vous utiliserez «mmmm».

Lorsque vous utilisez une constante nommée comme formule de tableau, n'oubliez pas d'entrer le signe égal, comme dans =Quarter1, et pas seulement Quarter1. Si vous ne le faites pas, Excel interprète le tableau comme une chaîne de texte et votre formule ne fonctionnera pas comme prévu. Enfin, n'oubliez pas que vous pouvez utiliser des combinaisons de fonctions, de texte et de chiffres. Tout dépend du degré de créativité que vous souhaitez atteindre.

Les exemples suivants montrent de quelle manière vous pouvez mettre les constantes de matrice à contribution dans des formules de tableau. Certains des exemples utilisent la fonction TRANSPOSE pour convertir les lignes en colonnes et vice versa.

  • Multiple chaque élément dans un tableau

    Entrez =SEQUENCE(1,12)*2, ou ={1.2.3.4;5.6.7.8;9.10.11.12}*2

    Vous pouvez également diviser avec (/), ajouter avec (+) et soustraire avec (-).

  • Mettre au carré les éléments d'un tableau

    Entrez =SEQUENCE(1,12)^2, ou ={1.2.3.4;5.6.7.8;9.10.11.12}^2

  • Trouver la racine carrée des éléments au carré dans un tableau

    Enter =SQRT(SEQUENCE(1,12)^2), ou =SQRT({1.2.3.4;5.6.7.8;9.10.11.12}^2)

  • Transpose une ligne unidimensionnelle

    Entrez =TRANSPOSE(SEQUENCE(1,5)), ou =TRANSPOSE({1.2.3.4.5})

    Même si vous avez saisi une constante de tableau horizontale, la fonction TRANSPOSE convertit la constante de tableau en une colonne.

  • Transposer une colonne unidimensionnelle

    Entrez =TRANSPOSE(SEQUENCE(5,1)), ou =TRANSPOSE({1;2;3;4;5})

    Même si vous avez entré une constante de tableau verticale, la fonction TRANSPOSE convertit la constante en une ligne.

  • Transposer une constante en deux dimensions

    Entrez =TRANSPOSE(SEQUENCE(3,4)), ou =TRANSPOSE({1.2.3.4;5.6.7.8;9.10.11.12})

    La fonction TRANSPOSE convertit chaque ligne en une série de colonnes.

Cette section présente des exemples de formules de tableau de base.

  • Créer un tableau à partir de valeurs existantes

    L'exemple suivant explique comment utiliser les formules de tableau pour créer un nouveau tableau à partir d'un tableau existant.

    Entrez =SEQUENCE(3,6,10,10), ou ={10.20.30.40.50.60;70.80.90.100.110.120;130.140.150.160.170.180}

    Assurez-vous de taper { (accolade ouvrante) avant de taper 10, et } (accolade fermante) après avoir tapé 180, car vous créez un tableau de chiffres.

    Ensuite, entrez =D9#, ou=D9:I11 dans une cellule vide. Un tableau de 3 x 6 cellules apparaît avec les mêmes valeurs que vous voyez en D9:D11. Le signe # est appelé l'opérateur de plage renversée, et c'est la façon dont Excel fait référence à la plage entière du tableau au lieu de devoir la taper.

    Utiliser l’opérateur de plage propagée (#) pour référencer un tableau existant

  • Créer une constante de tableau à partir de valeurs existantes

    Vous pouvez prendre les résultats d'une formule de tableau renversée et la convertir en ses composants. Sélectionnez la cellule D9, puis appuyez sur F2 pour passer en mode édition. Ensuite, appuyez sur F9 pour convertir les références de cellules en valeurs, qu 'Excel convertit ensuite en une constante de tableau. Lorsque vous appuyez sur la touche Entrée, la formule =D9# doit maintenant être ={10.20.30;40.50.60;70.80.90}.

  • Compter les caractères dans une plage de cellules

    L'exemple suivant vous montre comment compter le nombre de caractères dans une plage de cellules. Cela inclut les espaces.

    Compter le nombre total de caractères dans une plage, et d’autres tableaux pour l’utilisation de chaînes de texte

    =SUM(LEN(C9:C13))

    Dans ce cas, la fonction LEN renvoie la longueur de chaque chaîne de texte dans chacune des cellules de la plage. La fonction SUM additionne ensuite ces valeurs et affiche le résultat (66). Si vous vouliez obtenir le nombre moyen de caractères, vous pourriez utiliser :

    =MOYENNE(LEN(C9:C13))

  • Contenu de la cellule la plus longue de la plage C9:C13

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    Cette formule ne fonctionne que lorsqu'une plage de données contient une seule colonne de cellules.

    Examinons de plus près la formule, en commençant par les éléments internes et en allant vers l'extérieur. La fonction LEN renvoie la longueur de chacun des éléments de la plage de cellules D2:D6. La fonction MAX calcule la plus grande valeur parmi ces éléments, ce qui correspond à la chaîne de texte la plus longue, qui se trouve dans la cellule D3.

    C'est là que les choses deviennent un peu complexes. La fonction MATCH calcule le décalage (la position relative) de la cellule qui contient la chaîne de texte la plus longue. Pour ce faire, il a besoin de trois arguments : une valeur de recherche, un tableau de recherche et un type de correspondance. La fonction MATCH recherche la valeur de référence spécifiée dans le tableau de référence. Dans ce cas, la valeur recherchée est la chaîne de texte la plus longue :

    MAX(LEN(C9:C13)

    et cette chaîne se trouve dans ce tableau :

    LEN(C9:C13)

    Dans ce cas, l'argument du type de correspondance est 0. Le type de correspondance peut être une valeur 1, 0 ou –1.

    • 1 – renvoie la plus grande valeur qui est inférieure ou égale à la valeur de recherche.

    • 0 – renvoie la première valeur exactement égale à la valeur de recherche.

    • –1 – renvoie la plus petite valeur qui est supérieure ou égale à la valeur de consultation spécifiée.

    • Si vous omettez un type de correspondance, Excel suppose 1.

    Enfin, la fonction INDEX prend les arguments suivants : un tableau, et un numéro de ligne et de colonne dans ce tableau. La plage de cellules C9:C13 fournit le tableau, la fonction MATCH fournit l'adresse de la cellule, et l'argument final (1) spécifie que la valeur provient de la première colonne du tableau.

    Si vous vouliez obtenir le contenu de la plus petite chaîne de texte, vous remplaceriez MAX dans l'exemple ci-dessus par MIN.

  • Trouver les n plus petites valeurs d'un intervalle

    Cet exemple montre comment trouver les trois plus petites valeurs dans une plage de cellules, où un tableau de données échantillons dans les cellules B9:B18 a été créé avec : =INT(RANDARRAY (10,1)*100). Notez que RANDARRAY est une fonction volatile, vous obtiendrez donc un nouvel ensemble de nombres aléatoires à chaque calcul d'Excel.

    Formule de tableau Excel pour trouver la plus petite valeur : =SMALL(B9#,SEQUENCE(D9))

    Entrez =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1;2;3})

    Cette formule utilise une constante de tableau pour évaluer trois fois la fonction SMALL et renvoyer les 3 plus petits membres du tableau contenus dans les cellules B9:B18, où 3 est une valeur variable dans la cellule D9. Pour trouver plus de valeurs, vous pouvez augmenter la valeur de la fonction SEQUENCE, ou ajouter plus d'arguments à la constante. Vous pouvez également utiliser des fonctions supplémentaires avec cette formule, telles que SOMME ou MOYENNE. Par exemple :

    =SUM(PETIT(B9#,SÉQUENCE(D9))

    =MOYENNE(PETITE(B9#,SÉQUENCE(D9))

  • Trouver les n plus grandes valeurs d'un intervalle

    Pour trouver les plus grandes valeurs d'une plage, vous pouvez remplacer la fonction SMALL par la fonction LARGE. En outre, l'exemple suivant utilise les fonctions de LIGNE et INDIRECT.

    Entrez =LARGE(B9#,LIGNE(INDIRECT(«1:3»))) ou =LARGE(B9:B18,LIGNE(INDIRECT(«1:3»)))

    À ce stade, il peut être utile de connaître un peu les fonctions LIGNE et INDIRECT. Vous pouvez utiliser la fonction LIGNE ou créer un tableau d’entiers consécutifs. Par exemple, sélectionnez un vide et entrez :

    =LIGNE(1:10)

    La formule crée une colonne de 10 entiers consécutifs. Pour repérer un problème potentiel, insérez une ligne au-dessus de la plage qui contient la formule de tableau (c’est-à-dire, au-dessus de la ligne 1). Excel ajuste les références de ligne, et la formule génère maintenant des entiers de 2 à 11. Pour résoudre ce problème, vous ajoutez la fonction INDIRECT à la formule :

    =LIGNE(INDIRECT(«1:10»))

    La fonction INDIRECT utilise des chaînes de texte comme arguments (c'est pourquoi la plage 1:10 est entourée de guillemets). Excel n'ajuste pas les valeurs du texte lorsque vous insérez des lignes ou déplacez la formule du tableau. En conséquence, la fonction LIGNE génère toujours le tableau d'entiers que vous souhaitez. Vous pourriez tout aussi bien utiliser SEQUENCE :

    =SEQUENCE(10)

    Examinons la formule que vous avez utilisée précédemment – =LARGE(B9#,ROW(INDIRECT(«1:3»))) – en partant des parenthèses intérieures et en allant vers l'extérieur : La fonction INDIRECT renvoie un ensemble de valeurs de texte, dans ce cas les valeurs 1 à 3. La fonction LIGNE génère à son tour un tableau de colonnes à trois cellules. La fonction LARGE utilise les valeurs de la plage de cellules B9:B18, et elle est évaluée trois fois, une fois pour chaque référence renvoyée par la fonction LIGNE. Si vous voulez trouver plus de valeurs, vous ajoutez une plage de cellules plus grande à la fonction INDIRECT. Enfin, comme pour les exemples de PETIT, vous pouvez utiliser cette formule avec d'autres fonctions, telles que SOMME et MOYENNE.

  • Additionner une plage qui contient des valeurs d'erreur

    La fonction SUM d 'Excel ne fonctionne pas lorsque vous essayez d'additionner une plage qui contient une valeur d'erreur, telle que #VALUE ! ou #N/A. Cet exemple vous montre comment additionner les valeurs d'une plage nommée Données qui contient des erreurs :

    Utilisez des tableaux pour gérer des erreurs. Par exemple, =SUM(IF(ISERROR(Data),"",Data) fera la somme des données nommées de la plage même si elles contiennent des erreurs, comme #VALUE! ou #NA!.

  • =SOMME(SI(ESTERREUR(Données),"",Données))

    La formule crée un tableau contenant les valeurs d’origine sans les valeurs d’erreur. En partant des fonctions internes et en travaillant vers l'extérieur, la fonction ISERROR recherche les erreurs dans la plage de cellules ( Données ). La fonction SI renvoie une valeur spécifique si une condition que vous spécifiez est évaluée à VRAI et une autre valeur si elle est évaluée à FAUX. Dans ce cas, la fonction renvoie des chaînes vides ("") pour toutes les valeurs d’erreur car elles sont VRAIES, et renvoie les valeurs restantes de la plage (Données) car elles prennent la valeur FAUX, ce qui signifie qu’elles ne contiennent pas de valeurs d’erreur. La fonction SUM calcule ensuite le total du tableau filtré.

  • Compter le nombre de valeurs d'erreur dans une plage

    Cet exemple est similaire à la formule précédente, mais il renvoie le nombre de valeurs d'erreur dans une plage nommée Data au lieu de les filtrer :

    =SOMME(SI(ESTERREUR(Données),1,0))

    Cette formule crée un tableau qui contient la valeur 1 pour les cellules contenant des erreurs et la valeur 0 pour les cellules n’en contenant pas. Vous pouvez simplifier la formule et obtenir le même résultat en supprimant le troisième argument de la fonction SI, comme ceci :

    =SOMME(SI(ESTERREUR(Données),1))

    Si vous ne spécifiez pas l’argument, la fonction SI renvoie FAUX si une cellule ne contient pas de valeur d’erreur. Vous pouvez aller encore plus loin dans la simplification de la formule :

    =SOMME(SI(ESTERREUR(Données)*1))

    Cette version fonctionne car VRAI*1=1 et FAUX*1=0.

Il est possible que vous deviez additionner des valeurs basées sur des conditions.

Vous pouvez utiliser des tableaux pour calculer en fonction de certaines conditions. =SUM(IF(Sales>0,Sales)) fera la somme de toutes les valeurs supérieures à 0 dans une plage appelée Ventes.

Par exemple, cette formule de tableau fait la somme des seuls nombres entiers positifs d'une plage appelée Ventes, qui représente les cellules E9:E24 dans l'exemple ci-dessus :

=SOMME(SI(Ventes>0,Ventes))

La fonction SI crée un tableau de valeurs positives et fausses. La fonction SOMME ignore essentiellement les fausses valeurs car 0+0=0. La plage de cellules que vous utilisez dans cette formule peut se composer d’un nombre indéfini de lignes et de colonnes.

Vous pouvez également additionner des valeurs répondant à plusieurs conditions. Par exemple, cette formule de tableau calcule les valeurs supérieures à 0 ET inférieures à 2500 :

=SOMME((Ventes>0)*(Ventes<2500)*(Ventes))

N’oubliez pas que cette formule renvoie une erreur si la plage contient une ou plusieurs cellules non numériques.

Vous pouvez également créer des formules de tableau utilisant un type ou une condition OU. Par exemple, vous pouvez additionner les valeurs qui sont supérieures à 0 OU inférieures à 2500 :

=SOMME( SI((Ventes>0)+(Ventes<2500),Ventes))

Vous ne pouvez pas utiliser les fonctions ET et OU directement dans des formules de tableau car ces fonctions renvoient un résultat unique (VRAI ou FAUX) et que les fonctions de tableau nécessitent des tableaux de résultats. Vous pouvez contourner le problème en utilisant la logique présentée dans la formule précédente. En d'autres termes, vous effectuez des opérations mathématiques, telles que l'addition ou la multiplication, sur des valeurs qui remplissent la condition OU ET.

Cet exemple indique comment supprimer les zéros d’une plage lorsque vous devez calculer la moyenne des valeurs de cette plage. La formule utilise une plage de données nommée Ventes :

=MOYENNE(SI(Ventes<>0,Ventes))

La fonction SI crée un tableau de valeurs qui ne sont pas égales à 0, puis transmet ces valeurs à la fonction MOYENNE.

Cette formule de tableau compare les valeurs de deux plages de cellules nommées MesDonnées et VosDonnées et renvoie le nombre de différences entre les deux. Si les contenus des deux plages sont identiques, la formule renvoie 0. Pour utiliser cette formule, les plages de cellules doivent être de la même taille et de la même dimension. Par exemple, si Mes Données est une plage de 3 lignes sur 5 colonnes, Vos Données doit également être de 3 lignes sur 5 colonnes :

=SOMME( SI(MesDonnées=VosDonnées,0,1))

La formule crée un nouveau tableau de la même taille que les plages que vous comparez. La fonction SI remplit le tableau avec la valeur 0 et la valeur 1 (0 pour les non-concordances et 1 pour les cellules identiques). La fonction SOMME renvoie alors la somme des valeurs du tableau.

Vous pouvez simplifier la formule comme suit :

=SOMME(1*(MesDonnées<>VosDonnées))

Comme la formule qui compte les valeurs d'erreur dans une plage, cette formule fonctionne parce que VRAI*1=1, et FAUX*1=0.

Cette formule de tableau renvoie le numéro de ligne de la valeur maximale d’une plage de colonnes uniques nommée Données :

=MIN(SI(Données=MAX(Données),LIGNE(Données),""))

La fonction SI crée un nouveau tableau qui correspond à la plage nommée Données. Si une cellule correspondante contient la valeur maximale de la plage, le tableau contient le numéro de ligne. Sinon, le tableau contient une chaîne vide (""). La fonction MIN utilise le nouveau tableau comme second argument et renvoie la valeur la plus petite, correspondant au numéro de ligne de la valeur maximale dans la plage Données. Si la plage nommée Données contient des valeurs maximales identiques, la formule renvoie la ligne de la première valeur.

Si vous souhaitez renvoyer l’adresse de la cellule réelle d’une valeur maximale, utilisez cette formule :

=ADRESSE(MIN(SI(Données=MAX(Données),LIGNE(Données),"")),COLONNE(Données))

Vous trouverez des exemples similaires dans le classeur type, sur la feuille de travail Différences entre les ensembles de données.

Cet exercice vous montre comment utiliser des formules matricielles à plusieurs cellules et à cellule unique pour calculer un ensemble de chiffres de vente. La première série d’étapes utilise une formule à plusieurs cellules pour calculer un ensemble de sous-totaux. La seconde série utilise une formule à cellule unique pour calculer un total général.

  • Formule de tableau à plusieurs cellules

Copiez l'intégralité du tableau ci-dessous et collez-le dans la cellule A1 d'une feuille de calcul vierge.

Personne chargée des ventes

Type de voiture

Nombre de ventes

Prix unitaire

Ventes totales

Jacques

Berline

5

33000

Coupé

4

37000

Osorio

Berline

6

24000

Coupé

8

21000

Thierry

Berline

3

29000

Coupé

1

31000

Antoine

Berline

9

24000

Coupé

5

37000

Durrer

Berline

6

33000

Coupé

8

31000

Formule (total général)

Total général

'=SOMME(C2:C11*D2:D11)

=SOMME(C2:C11*D2:D11)

  1. Pour voir les ventes totales de coupés et de berlines pour chaque vendeur, sélectionnez les cellules E2:E11, entrez la formule =C2:C11*D2:D11 , puis appuyez sur Ctrl+Shift+Enter.

  2. Pour afficher le total général de toutes les ventes, sélectionnez la cellule F11, entrez la formule =SUM(C2:C11*D2:D11) , puis appuyez sur Ctrl+Shift+Enter.

Lorsque vous appuyez sur Ctrl+Shift+Enter , Excel entoure la formule d'accolades( { }) et insère une instance de la formule dans chaque cellule de la plage sélectionnée. Cela se produit très rapidement, de sorte que ce que vous voyez dans la colonne E correspond au montant total des ventes pour chaque type de voiture par vendeur. Si vous sélectionnez E2, puis E3, E4, et ainsi de suite, vous constaterez que la même formule est affichée : {=C2:C11*D2:D11}

Las totaux dans la colonne E sont calculés par une formule matricielle

  • Créer une formule de tableau à cellule unique

Dans la cellule D13 du classeur, tapez la formule suivante, puis appuyez sur Ctrl+Shift+Enter:

=SOMME(C2:C11*D2:D11)

Dans ce cas, Excel multiplie les valeurs du tableau (la plage de cellules C2 à D11), puis utilise la fonction SOMME pour additionner les totaux. Le résultat produit un total général de 1 590 000 dollars de ventes. Cet exemple illustre la puissance de ce type de formule. Par exemple, supposons que vous disposez de 1000 lignes de données. Vous pouvez additionner tout ou partie de ces données en créant une formule de tableau dans une seule cellule au lieu de faire glisser la formule vers le bas sur les 1000 lignes.

Remarquez également que la formule à une cellule de la cellule D13 est totalement indépendante de la formule à plusieurs cellules (celle des cellules E2 à E11). C'est un autre avantage de l'utilisation des formules de tableau – la flexibilité. Vous pourriez modifier les formules de la colonne E ou supprimer complètement cette colonne, sans affecter la formule de D13.

Les formules de tableau offrent également les avantages suivants :

  • Cohérence    Si vous cliquez sur n’importe quelle cellule comprise entre E2 et celles placées plus bas, vous voyez la même formule. Cette cohérence peut être synonyme de plus grande précision.

  • Sécurité    Vous ne pouvez pas remplacer un composant d’une formule de tableau à plusieurs cellules. Par exemple, cliquez sur la cellule E3 et appuyez sur Supprimer. Vous devez soit sélectionner toute la plage de cellules (E2 à E11) et modifier la formule pour tout le tableau, soit laisser le tableau en l’état. Comme mesure de sécurité supplémentaire, vous devez appuyer sur Ctrl+Shift+Enter pour confirmer toute modification de la formule.

  • Des fichiers de plus petite taille    Il est souvent possible d’utiliser une seule formule de tableau au lieu de plusieurs formules intermédiaires. Par exemple, le classeur utilise une formule de tableau pour calculer les résultats de la colonne E. Avec des formules standard (telles que =C2*D2, C3*D3, C4*D4…), il vous en aurait fallu 11 différentes pour calculer les mêmes résultats.

En général, les formules de tableau utilisent la syntaxe des formules standard. Elles commencent toutes par un signe égal et vous pouvez y insérer n’importe quelle fonction intégrée d’Excel. La principale différence est que, lorsque vous utilisez une formule de tableau, vous appuyez sur Ctrl+Shift+Enter pour saisir votre formule. Excel met ensuite votre formule matricielle entre accolades — si vous tapez les accolades manuellement, votre formule sera convertie en chaîne de texte et ne fonctionnera pas.

Les fonctions de tableau peuvent être un moyen efficace de construire des formules complexes. La formule de tableau =SOMME(C2:C11*D2:D11) équivaut à celle-ci : =SOMME(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Important : Appuyez sur Ctrl+Shift+Enter chaque fois que vous devez saisir une formule de tableau. Cette règle s’applique aussi bien aux formules à cellule unique qu’à plusieurs cellules.

Lorsque que vous utilisez des formules à plusieurs cellules, vous devez également respecter les règles suivantes :

  • Sélectionnez la plage de cellules qui contiendra vos résultats avant d’entrer la formule. Vous l’avez fait lorsque vous avez créé la formule de tableau à plusieurs cellules, en sélectionnant les cellules E2 à E11.

  • Vous ne pouvez pas modifier le contenu d’une cellule individuelle dans une formule de tableau. Pour le vérifier, sélectionnez la cellule E3 dans le classeur et appuyez sur Suppr. Excel affiche un message indiquant qu’il est impossible de modifier une partie d’un tableau.

  • Vous pouvez déplacer ou supprimer une formule de tableau entière, mais vous ne pouvez pas en déplacer ou en supprimer une partie. En d’autres termes, pour réduire une formule de tableau, vous devez d’abord supprimer la formule existante et recommencer.

  • Pour supprimer une formule de tableau, sélectionnez l'ensemble de la plage de formules (par exemple, E2:E11 ), puis appuyez sur Supprimer.

  • Vous ne pouvez pas insérer de cellules vides dans une formule de tableau à plusieurs cellules, ni en supprimer.

Il peut parfois s’avérer nécessaire de développer une formule de tableau. Sélectionnez la première cellule de la plage de tableaux existante, et continuez jusqu'à ce que vous ayez sélectionné la plage entière à laquelle vous voulez étendre la formule. Appuyez sur F2 pour modifier la formule, puis appuyez sur CTRL+SHIFT+ENTER pour confirmer la formule une fois que vous avez ajusté la plage de formules. La clé est de sélectionner la plage entière, en commençant par la cellule en haut à gauche du tableau. La cellule en haut à gauche est celle qui est modifiée.

Les formules de tableau sont très pratiques, mais elles peuvent malheureusement présenter quelques inconvénients :

  • Vous pouvez parfois oublier d'appuyer sur Ctrl+Shift+Enter. Cela peut arriver même aux utilisateurs d’Excel les plus expérimentés. Souvenez-vous d’utiliser cette combinaison de touches chaque fois que vous entrez ou modifiez une formule matricielle.

  • D’autres utilisateurs de votre classeur peuvent ne pas comprendre vos formules. Dans la pratique, les formules de tableau ne sont généralement pas expliquées. Par conséquent, si d'autres personnes doivent modifier vos classeurs, vous devez soit éviter les formules de tableau, soit vous assurer que ces personnes connaissent les formules de tableau et comprennent comment les modifier, le cas échéant.

  • Selon la vitesse de traitement et la mémoire de votre ordinateur, les formules des grands tableaux peuvent ralentir les calculs.

Les constantes de matrice sont un composant des formules de tableau. Vous pouvez créer des constantes de matrice en entrant une liste d’éléments et en mettant cette liste entre accolades ({ }), comme ceci :

={1.2.3.4.5}

Vous savez maintenant que vous devez appuyer sur Ctrl+Shift+Enter lorsque vous créez des formules de tableau. Comme les constantes de tableau sont un composant des formules de tableau, vous entourez les constantes d'accolades en les tapant manuellement. Vous utilisez ensuite Ctrl+Shift+Enter pour saisir la formule entière.

Si vous séparez les éléments à l’aide de virgules, vous créez un tableau horizontal (une ligne). Si vous séparez les éléments à l’aide de points-virgules, vous créez un tableau vertical (une colonne). Pour créer un tableau à deux dimensions, vous délimitez les éléments dans chaque ligne à l’aide de virgules et délimitez chaque ligne à l’aide de points-virgules.

Voici un tableau sur une seule ligne : {1.2.3.4}. Voici une matrice dans une seule colonne : {1;2;3;4}. Et voici une matrice de deux lignes et quatre colonnes : {1.2.3.4;5.6.7.8}. Dans la matrice à deux lignes, la première ligne est 1, 2, 3 et 4, et la seconde ligne est 5, 6, 7 et 8. Un seul point-virgule sépare les deux lignes, entre 4 et 5.

À l’instar des formules de tableau, vous pouvez utiliser des constantes de matrice avec la plupart des fonctions intégrées fournies par Excel. Les sections suivantes expliquent comment créer chaque type de constante et comment utiliser ces constantes avec des fonctions dans Excel.

Les procédures suivantes vous permettront de vous familiariser avec la création de constantes horizontales, verticales et à deux dimensions.

Créer une constante horizontale

  1. Dans une feuille de calcul blanche, sélectionnez les cellules A1 à E1.

  2. Dans la barre de formule, saisissez la formule suivante, puis appuyez sur Ctrl+Shift+Enter:

    ={1.2.3.4.5}

    Dans ce cas, vous devez taper les accolades ouvrantes et fermantes ( { } ), et Excel ajoutera le deuxième ensemble pour vous.

    Le résultat suivant s'affiche.

    Constante de matrice horizontale dans une formule

Créer une constante verticale

  1. Dans votre classeur, sélectionnez une colonne de cinq cellules.

  2. Dans la barre de formule, saisissez la formule suivante, puis appuyez sur Ctrl+Shift+Enter:

    ={1;2;3;4;5}

    Vous obtenez le résultat suivant.

    Constante de matrice verticale dans une formule matricielle

Créer une constante

  1. Dans votre classeur, sélectionnez un bloc de cellules de quatre colonnes de largeur et trois lignes de hauteur.

  2. Dans la barre de formule, saisissez la formule suivante, puis appuyez sur Ctrl+Shift+Enter:

    ={1.2.3.4;5.6.7.8;9.10.11.12}

    Vous obtenez le résultat suivant :

    Constante de matrice à deux dimensions dans une formule matricielle

Utiliser des constantes dans les formules

Voici un exemple simple qui fait appel à des constates :

  1. Dans l’exemple de classeur, créez une feuille de calcul.

  2. Dans la cellule A1, tapez 3, puis tapez 4 dans B1, 5 dans C1, 6 dans D1 et 7 dans E1.

  3. Dans la cellule A3, tapez la formule suivante, puis appuyez sur Ctrl+Shift+Enter:

    =SOMME(A1:E1*{1.2.3.4.5})

    Notez qu’Excel entoure la constante d’un jeu supplémentaire de parenthèses, car vous l’avez entrée en tant que formule de tableau.

    Formule matricielle avec constante de matrice

    La valeur 85 apparaît dans la cellule A3.

La section suivante explique comment fonctionne la formule.

La formule que vous venez d’utiliser contient différentes parties.

Syntaxe de la formule matricielle avec constante de matrice

1. Fonction

2. Tableau stocké

3. Opérateur

4. Constante de matrice

Le dernier élément qui se trouve à l’intérieur des parenthèses est la constante de matrice : {1.2.3.4.5}. Souvenez-vous qu’Excel n’entoure pas les constantes de matrice d’accolades ; c’est vous qui devez les taper. Also remember that after you add a constant to an array formula, you press Ctrl+Shift+Enter to enter the formula.

Étant donné qu’Excel effectue des opérations sur les expressions entre parenthèses en premier, les deux éléments suivants qui entrent ensuite en jeu sont les valeurs stockées dans le classeur (A1:E1) et l’opérateur. À ce stade, la formule multiplie les valeurs dans le tableau stocké par les valeurs correspondantes dans la constante. Cela équivaut à :

=SOMME(A1*1,B1*2,C1*3,D1*4,E1*5)

Pour finir, la fonction SOMME additionne les valeurs et la somme 85 apparaît dans la cellule A3.

Pour éviter d’utiliser le tableau stocké et conserver l’opération entièrement en mémoire, remplacez le tableau stocké par une autre constante de matrice :

=SOMME({3.4.5.6.7}*{1.2.3.4.5})

Pour essayer, copiez la fonction, sélectionnez une cellule vide dans votre classeur, collez la formule dans la barre de formule, puis appuyez sur Ctrl+Shift+Enter. Vous obtiendrez le même résultat qu’à l’exercice précédent qui utilisait la formule de tableau :

=SOMME(A1:E1*{1.2.3.4.5})

Les constantes de matrice peuvent contenir des chiffres, du texte, des valeurs logiques (telles que VRAI et FAUX), et des valeurs d’erreur (telles que #N/A). Vous pouvez utiliser des chiffres aux formats Entier, Décimal et Scientifique. Si vous y insérez du texte, vous devez le mettre entre guillemets (").

Les constantes de matrice ne peuvent pas contenir de tableaux, formules ou fonctions supplémentaires. En d’autres termes, elles ne peuvent contenir que du texte ou des chiffres séparés par des virgules ou des points-virgules. Excel affiche un message d’avertissement lorsque vous entrez une formule telle que {1.2.A1:D4} ou {1.2.SOMME(Q2:Z8)}. Par ailleurs, les valeurs numériques ne peuvent pas contenir de signes de pourcentage ou de dollar, de virgules ou de parenthèses.

L'une des meilleures façons d'utiliser les constantes de tableau est de les nommer. Les constantes nommées peuvent être beaucoup plus faciles à utiliser et peuvent dissimuler une partie de la complexité de vos formules aux autres utilisateurs. Pour nommer une constante de matrice et l’utiliser dans une formule, procédez comme suit :

  1. Sous l’onglet Formules, dans le groupe Noms définis, cliquez sur Définir un nom.
    La boîte de dialogue Définir le nom apparaît.

  2. Dans la zone Nom, tapez Trimestre1.

  3. Dans la zone Fait référence à, entrez la constante suivante (n’oubliez pas de taper les accolades) :

    ={"Janvier"."Février"."Mars"}

    Le contenu de la boîte de dialogue doit ressembler à ceci :

    Boîte de dialogue Modifier le nom, avec formule

  4. Cliquez sur OK, puis sélectionnez une ligne de trois cellules vides.

  5. Tapez la formule suivante, puis appuyez sur Ctrl+Shift+Enter.

    =Trimestre1

    Vous obtenez le résultat suivant.

    Tableau nommé entré en tant que formule

Lorsque vous utilisez une constante nommée en tant que formule de tableau, n’oubliez pas le signe égal. Si vous l’omettez, Excel interprétera le tableau comme une chaîne de texte et votre formule ne fonctionnera pas comme prévu. Enfin, rappelez-vous que vous pouvez utiliser des combinaisons de texte et de chiffres.

Recherchez les problèmes suivants lorsque vos constantes de matrice ne fonctionnent pas.

  • Certains éléments ne sont peut-être pas séparés à l’aide du caractère approprié. Si vous omettez une virgule ou un point-virgule, ou si vous en mettez un au mauvais endroit, la constante du tableau peut ne pas être créée correctement, ou vous pouvez voir un message d'avertissement.

  • Vous pouvez avoir sélectionné une plage de cellules qui ne correspond pas au nombre d’éléments de votre constante. Par exemple, si vous sélectionnez une colonne de six cellules à utiliser dans une constante de cinq cellules, la valeur d’erreur #N/A apparaît dans la cellule vide. À l’inverse, si vous sélectionnez un nombre insuffisant de cellules, Excel omet les valeurs qui ne possèdent pas de cellule correspondante.

Les exemples suivants montrent de quelle manière vous pouvez mettre les constantes de matrice à contribution dans des formules de tableau. Certains des exemples utilisent la fonction TRANSPOSE pour convertir les lignes en colonnes et vice versa.

Multiplier chaque élément dans un tableau

  1. Créez une feuille de calcul, puis sélectionnez un bloc de cellules vides de quatre colonnes de largeur sur trois lignes de hauteur.

  2. Tapez la formule suivante, puis appuyez sur Ctrl+Shift+Enter:

    ={1.2.3.4;5.6.7.8;9.10.11.12}*2

Mettre au carré les éléments dans un tableau

  1. Sélectionnez un bloc de cellules vides de quatre colonnes de largeur sur trois lignes de hauteur.

  2. Tapez la formule de tableau suivante, puis appuyez sur Ctrl+Shift+Enter:

    ={1.2.3.4;5.6.7.8;9.10.11.12}*{1.2.3.4;5.6.7.8;9.10.11.12}

    Vous pouvez aussi entrer cette formule de tableau, qui utilise l’opérateur circonflexe (^):

    ={1.2.3.4;5.6.7.8;9.10.11.12}^2

Transposer une ligne à une dimension

  1. Sélectionnez une colonne de cinq cellules vides.

  2. Tapez la formule suivante, puis appuyez sur Ctrl+Shift+Enter:

    =TRANSPOSE({1.2.3.4.5})

    Même si vous avez entré une constante de matrice horizontale, la fonction TRANSPOSE convertit la constante de matrice en une colonne.

Transposer une colonne à une dimension

  1. Sélectionnez une ligne de cinq cellules vides.

  2. Entrez la formule suivante, puis appuyez sur Ctrl+Shift+Enter:

    =TRANSPOSE({1;2;3;4;5})

Même si vous avez entré une constante de matrice verticale, la fonction TRANSPOSE convertit la constante en une ligne.

Transposer une constante à deux dimensions

  1. Sélectionnez un bloc de cellules de trois colonnes de large sur quatre lignes de hauteur.

  2. Entrez la constante suivante, puis appuyez sur Ctrl+Shift+Enter:

    =TRANSPOSE({1.2.3.4;5.6.7.8;9.10.11.12})

    La fonction TRANSPOSE convertit chaque colonne en une série de colonnes.

Cette section présente des exemples de formules de tableau de base.

Créer des tableaux et des constantes à partir de valeurs existantes

L’exemple suivant explique comment utiliser des formules de tableau pour créer des liens entre des plages de cellules situées dans des feuilles de calcul différentes. Cela montre également comment créer une constante de matrice à partir du même ensemble de valeurs.

Créer un tableau à partir de valeurs existantes

  1. Dans une feuille de calcul dans Excel, sélectionnez les cellules C8:E10, puis entrez cette formule :

    ={10.20.30;40.50.60;70.80.90}

    N’oubliez pas de taper { (accolade ouvrante) avant de taper 10 et } (accolade fermante) après avoir tapé 90, car vous créez un tableau de nombres.

  2. Appuyez sur Ctrl+Shift+Enter , ce qui permet de saisir ce tableau de chiffres dans la plage de cellules C8:E10 en utilisant une formule de tableau. Dans votre feuille de calcul, les cellules C8 à E10 doivent ressembler à ceci :

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Sélectionnez la plage de cellules C1 à E3.

  4. Entrez la formule suivante dans la barre de formule, puis appuyez sur Ctrl+Shift+Enter :

    =C8:E10

    Un tableau 3x3 de cellules apparaît dans les cellules C1 à E3 avec les mêmes valeurs que celles que vous voyez dans les cellules C8 à E10.

Créer une constante de tableau à partir de valeurs existantes

  1. Avec les cellules C1:C3 sélectionnées, appuyez sur F2 pour passer en mode édition. 

  2. Appuyez sur F9 pour convertir les références des cellules en valeurs. Excel convertit les valeurs en une constante de tableau. La formule devrait maintenant être ={10.20.30;40.50.60;70.80.90}.

  3. Appuyez sur Ctrl+Shift+Enter pour entrer la constante de tableau comme une formule de tableau.

Compter les caractères dans une plage de cellules

L’exemple suivant vous montre comment compter le nombre de caractères, espaces inclus, dans une plage de cellules.

  1. Copiez l’intégralité de ce tableau et collez-le dans une feuille de calcul dans la cellule A1.

    Données

    Voici des

    cellules qui,

    ensemble,

    forment

    une phrase.

    Nombre total de caractères dans A2:A6

    =SOMME(NBCAR(A2:A6))

    Contenu de la cellule la plus longue (A3)

    =INDEX(A2:A6;EQUIV(MAX(NBCAR(A2:A6));NBCAR(A2:A6);0);1)

  2. Sélectionnez la cellule A8, puis appuyez sur Ctrl+Shift+Enter pour afficher le nombre total de caractères dans les cellules A2:A6 (66).

  3. Sélectionnez la cellule A10, puis appuyez sur Ctrl+Shift+Enter pour afficher le contenu de la plus longue des cellules A2:A6 (cellule A3).

La formule suivante est utilisée dans la cellule A8 pour compter le nombre total de caractères (66) dans les cellules A2 à A6.

=SOMME(NBCAR(A2:A6))

Dans ce cas, la fonction NBCAR renvoie la longueur de chaque chaîne de texte dans chacune des cellules de la plage. La fonction SOMME additionne ensuite ces valeurs et affiche le résultat (66).

Chercher les n valeurs les plus petites dans une plage

Cet exemple montre comment trouver les trois valeurs les plus petites dans une plage de cellules.

  1. Entrez des nombres aléatoires dans les cellules A1:A11.

  2. Sélectionnez les cellules C1 à C3. Cet ensemble de cellules contiendra les résultats renvoyés par la formule du tableau.

  3. Entrez la formule suivante, puis appuyez sur Ctrl+Shift+Enter:

    =SMALL(A1:A11,{1;2;3})

Cette formule utilise une constante de tableau pour évaluer trois fois la fonction SMALL et renvoyer le plus petit (1), le deuxième plus petit (2) et le troisième plus petit (3) membres du tableau contenu dans les cellules A1:A10. Pour trouver d'autres valeurs, vous ajoutez d'autres arguments à la constante. Vous pouvez également utiliser des fonctions supplémentaires avec cette formule, telles que SOMME ou MOYENNE. Par exemple :

=SOMME(PETITE1:A10,{1.2.3})

=MOYENNE(PETITE(A1:A10,{1.2.3})

Chercher les n valeurs les plus grandes dans une plage

Pour trouver les plus grandes valeurs d’une plage, vous pouvez remplacer la fonction PETITE.VALEUR par la fonction GRANDE.VALEUR. Par ailleurs, l’exemple suivant utilise les fonctions LIGNE et INDIRECT.

  1. Sélectionnez les cellules D1 à D3.

  2. Dans la barre de formule, entrez cette formule, puis appuyez sur Ctrl+Shift+Enter:

    =LARGE(A1:A10,ROW(INDIRECT(«1:3»)))

À ce stade, il peut être utile d’en savoir un peu sur les fonctions LIGNE et INDIRECT. Vous pouvez utiliser la fonction LIGNE ou créer un tableau d’entiers consécutifs. Par exemple, sélectionnez une colonne vide de 10 cellules dans votre classeur, saisissez cette formule de tableau, puis appuyez sur Ctrl+Shift+Enter:

=LIGNE(1:10)

La formule crée une colonne de 10 entiers consécutifs. Pour repérer un problème potentiel, insérez une ligne au-dessus de la plage qui contient la formule de tableau (c’est-à-dire, au-dessus de la ligne 1). Excel ajuste les références de ligne, et la formule génère des entiers de 2 à 11. Pour corriger ce problème, ajoutez la fonction INDIRECT à la formule :

=LIGNE(INDIRECT("1:10"))

La fonction INDIRECT utilise des chaînes de texte comme arguments (raison pour laquelle la plage 1:10 est entourée de guillemets). Excel n’ajuste pas les valeurs de texte lorsque vous insérez des lignes ou déplacez la formule de tableau d’une manière ou d’une autre. En conséquence, la fonction LIGNE génère toujours le tableau d’entiers que vous voulez.

Examinons la formule que vous avez utilisée précédemment – =LARGE(A5:A14,LIGNE(INDIRECT( «1:3»))) – en partant des parenthèses intérieures et en allant vers l'extérieur : La fonction INDIRECT renvoie un ensemble de valeurs de texte, dans ce cas les valeurs 1 à 3. La fonction ,LIGNE génère à son tour un réseau colonnaire à trois cellules. La fonction LARGE utilise les valeurs de la plage de cellules A5:A14, et elle est évaluée trois fois, une fois pour chaque référence renvoyée par la fonction LIGNE. Les valeurs 3200, 2700 et 2000 sont renvoyées dans le tableau à trois cellules en colonne. Si vous voulez trouver plus de valeurs, vous ajoutez une plage de cellules plus grande à la fonction INDIRECT.

Comme dans les exemples précédents, vous pouvez utiliser cette formule avec d'autres fonctions, telles que SOMME et MOYENNE.

Trouver la chaîne de texte la plus longue dans une plage de cellules

Revenez à l'exemple précédent de chaîne de texte, saisissez la formule suivante dans une cellule vide, puis appuyez sur Ctrl+Shift+Enter:

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

Le texte «tas de cellules qui» apparaît.

Examinons de plus près la formule, en commençant par les éléments internes et en allant vers l'extérieur. La fonction LEN renvoie la longueur de chacun des éléments de la plage de cellules A2:A6. La fonction MAX calcule la plus grande valeur parmi ces éléments, ce qui correspond à la chaîne de texte la plus longue, qui se trouve dans la cellule A3.

C’est ici que les choses se compliquent un peu. La fonction EQUIV calcule le décalage (position relative) de la cellule qui contient la chaîne de texte la plus longue. Pour ce faire, elle a besoin de trois arguments : unevaleur de recherche, un tableau de recherche et un type de correspondance. La fonction EQUIV recherche la valeur de recherche spécifiée dans le tableau de recherche. Dans notre exemple, la valeur de recherche est la chaîne de texte la plus longue :

(MAX(LEN(A2:A6))

et cette chaîne se trouve dans ce tableau :

LEN(A2:A6)

L’argument du type de correspondance est 0. Le type de correspondance peut être composé d’une valeur 1, 0 ou -1. Si vous spécifiez 1, EQUIV renvoie la plus grande valeur qui est inférieure ou égale à la valeur de recherche. Si vous spécifiez 0, la fonction EQUIV renvoie la première valeur exactement égale à la valeur de recherche. Si vous spécifiez -1, la fonction EQUIV recherche la plus petite valeur qui est supérieure ou égale à la valeur de recherche. Si vous omettez de spécifier un type de correspondance, Excel prend la valeur 1.

Pour finir, la fonction INDEX prend les arguments suivants : un tableau, ainsi qu’un numéro de ligne et de colonne au sein de ce tableau. La plage de cellules A2:A6 fournit le tableau, la fonction MATCH fournit l'adresse de la cellule, et l'argument final (1) spécifie que la valeur provient de la première colonne du tableau.

Cette section présente des exemples de formules de tableau avancées.

Additionner une plage qui contient des valeurs d’erreur

La fonction SOMME d’Excel ne fonctionne pas si vous tentez d’additionner une plage contenant une valeur d’erreur, telle que #N/A. Cet exemple vous montre comment additionner les valeurs d’une plage, nommée Données, contenant des erreurs.

=SOMME(SI(ESTERREUR(Données),"",Données))

La formule crée un tableau contenant les valeurs d’origine sans les valeurs d’erreur. En partant des fonctions intérieures et en procédant vers l’extérieur, la fonction ESTERREUR recherche les erreurs dans la plage de données (Données). La fonction SI renvoie une valeur spécifique si une condition que vous spécifiez prend la valeur VRAI et une autre valeur si la condition prend la valeur FAUX. Dans ce cas, la fonction renvoie des chaînes vides ("") pour toutes les valeurs d’erreur car elles sont VRAIES, et renvoie les valeurs restantes de la plage (Données) car elles prennent la valeur FAUX, ce qui signifie qu’elles ne contiennent pas de valeurs d’erreur. La fonction SOMME calcule ensuite le total pour le tableau filtré.

Compter le nombre de valeurs d’erreur dans une plage

Cet exemple est semblable à la formule précédente, à ceci près qu’il renvoie le nombre de valeurs d’erreurs dans une plage nommée Données au lieu de les filtrer :

=SOMME(SI(ESTERREUR(Données),1,0))

Cette formule crée un tableau qui contient la valeur 1 pour les cellules contenant des erreurs et la valeur 0 pour les cellules n’en contenant pas. Vous pouvez simplifier la formule et obtenir le même résultat en supprimant le troisième argument pour la fonction SI, comme ceci :

=SOMME(SI(ESTERREUR(Données),1))

Si vous ne spécifiez pas l’argument, la fonction SI renvoie FAUX si une cellule ne contient pas de valeur d’erreur. Vous pouvez aller encore plus loin dans la simplification de la formule :

=SOMME(SI(ESTERREUR(Données)*1))

Cette version fonctionne car VRAI*1=1 et FAUX*1=0.

Additionner des valeurs basées sur des conditions

Il est possible que vous deviez additionner des valeurs basées sur des conditions. Par exemple, cette formule de tableau additionne seulement les entiers positifs d’une plage nommée Ventes :

=SOMME(SI(Ventes>0,Ventes))

La fonction SI crée un tableau de valeurs positives et de valeurs fausses. La fonction SOMME ignore surtout les valeurs fausses car 0+0=0. La plage de cellules que vous utilisez dans cette formule peut se composer d’un nombre indéfini de lignes et de colonnes.

Vous pouvez également additionner des valeurs répondant à plusieurs conditions. Par exemple, cette formule de tableau calcule des valeurs supérieures à 0 et inférieures ou égales à 5 :

=SOMME((Ventes>0)*(Ventes<=5)*(Ventes))

N’oubliez pas que cette formule renvoie une erreur si la plage contient une ou plusieurs cellules non numériques.

Vous pouvez également créer des formules de tableau utilisant un type ou une condition OU. Par exemple, vous pouvez additionner des valeurs inférieures à 5 et supérieures à 15 :

=SOMME(SI((Ventes<5)+(Ventes>15),Ventes))

La fonction SI recherche toutes les valeurs inférieures à 5 et supérieures à 15, puis passe ces valeurs à la fonction SOMME.

Vous ne pouvez pas utiliser les fonctions ET et OU directement dans des formules de tableau car ces fonctions renvoient un résultat unique (VRAI ou FAUX) et que les fonctions de tableau nécessitent des tableaux de résultats. Il est possible de contourner le problème en utilisant la logique présentée dans la formule précédente. En d’autres termes, vous effectuez des opérations mathématiques, telles que l’addition ou la multiplication, sur des valeurs remplissant la condition OU ou ET.

Calculer une moyenne excluant des zéros

Cet exemple indique comment supprimer les zéros d’une plage lorsque vous devez calculer la moyenne des valeurs de cette plage. La formule utilise une plage de données nommée Ventes :

=MOYENNE(SI(Ventes<>0,Ventes))

La fonction SI crée un tableau de valeurs différentes de 0, puis passe ces valeurs à la fonction MOYENNE.

Compter le nombre de différences entre deux plages de cellules

Cette formule de tableau compare les valeurs de deux plages de cellules nommées MesDonnées et VosDonnées et renvoie le nombre de différences entre les deux. Si les contenus des deux plages sont identiques, la formule renvoie 0. Pour utiliser cette formule, les plages de cellules doivent être de la même taille et de la même dimension (par exemple, si MesDonnées est une plage de 3 lignes par 5 colonnes, VosDonnées doit également être une plage de 3 lignes par 5 colonnes) :  :

=SOMME( SI(MesDonnées=VosDonnées,0,1))

La formule crée un tableau de la taille des plages que vous comparez. La fonction SI remplit le tableau avec des valeurs 0 et 1 (0 pour les cellules différentes et 1 pour les cellules identiques). La fonction SOMME renvoie ensuite la somme des valeurs du tableau.

Vous pouvez simplifier la formule de la façon suivante :

=SOMME(1*(MesDonnées<>VosDonnées))

À l’instar de la formule qui compte les valeurs d’erreur dans une plage, cette formule fonctionne car VRAI*1=1 et FAUX*1=0.

Chercher l’emplacement de la valeur maximale dans une plage

Cette formule de tableau renvoie le numéro de ligne de la valeur maximale d’une plage de colonnes uniques nommée Données :

=MIN(SI(Données=MAX(Données),LIGNE(Données),""))

La fonction SI crée un tableau correspondant à la plage nommée Données. Si une cellule correspondante contient la valeur maximale de la plage, le tableau contient le numéro de la ligne. Dans le cas contraire, le tableau contient une chaîne vide (""). La fonction MIN utilise le nouveau tableau comme second argument et renvoie la valeur la plus petite, correspondant au numéro de ligne de la valeur maximale dans la plage Données. Si la plage nommée Données contient des valeurs maximales identiques, la formule renvoie la ligne de la première valeur.

Si vous souhaitez renvoyer l’adresse de la cellule réelle d’une valeur maximale, utilisez cette formule :

=ADRESSE(MIN(SI(Données=MAX(Données),LIGNE(Données),"")),COLONNE(Données))

Remerciements

Certaines parties de cet article sont basées sur une série de chroniques Excel Power User écrites par Colin Wilcox, et adaptées des chapitres 14 et 15 d' Excel 2002 Formulas, un livre écrit par John Walkenbach, un ancien MVP Excel.

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel ou obtenir une assistance dans la communauté Answers.

Voir aussi

Tableaux dynamiques et comportement des tableaux renversés

Formules de tableaux dynamiques vs. formules de tableaux CSE traditionnelles

Fonction FILTRE

Fonction TABLEAU.ALEAT

Fonction SEQUENCE

Fonction TRI

Fonction TRIERPAR

Fonction UNIQUE

#SPILL ! erreurs dans Excel

Opérateur d'intersection implicite : @

Vue d’ensemble des formules

Besoin d’aide ?

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoindre Microsoft Insider

Ces informations vous ont-elles été utiles ?

Dans quelle mesure êtes-vous satisfait(e) de la qualité de la traduction ?
Qu’est-ce qui a affecté votre expérience ?

Nous vous remercions pour vos commentaires.

×