Voici un exemple qui utilise des constantes de matrice dans le cadre d’une formule plus grande. Dans l’exemple de classeur, accédez à la constante dans une feuille de calcul de formule ou créez une feuille de calcul.

Dans la cellule D9, nous avons entré = Sequence (1 ; 5 ; 3 ; 1), mais vous pouvez également entrer 3, 4, 5, 6 et 7 dans les cellules A9 : H9. Il n’y a rien de particulier sur cette sélection de nombre, nous avons choisi un autre élément que 1-5 pour la différenciation.

Dans la cellule E11, entrez = somme (D9 : H9 * Sequence (1, 5))ou = somme (D9 : H9 * {1, 2, 3, 4, 5}). Les formules renvoient 85.

La fonction SEQUENCE génère l’équivalent de la constante matricielle {1, 2, 3, 4, 5}. Dans la mesure où Excel effectue d’abord des opérations sur des expressions placées entre parenthèses, les deux éléments suivants qui entrent en jeu sont les valeurs de cellule dans la cellule 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 à :

= Somme (D9 * 1, E9 * 2, F9 * 3, G9 * 4,-H9 * 5)ou = somme (3 * 1 ; 4 * 2 ; 5 * 3 ; 5)

Enfin, la fonction somme additionne les valeurs et renvoie 85.

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

= Somme (séquence (1 ; 5 ; 3 ; 1) * séquence (1, 5))ou = somme ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})

Éléments que vous pouvez utiliser dans des constantes de matrice

• Les constantes matricielles peuvent contenir des nombres, du texte, des valeurs logiques (comme vrai et faux) et des valeurs d’erreur telles que #N/A. Vous pouvez utiliser des nombres sous forme de nombres entiers, décimaux et scientifiques. Si vous incluez du texte, vous devez le placer entre 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.

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 des formules de tableau pour créer un tableau à partir d’un tableau existant.

  Entrée = séquence (3 ; 6 ; 10 ; 10); ou = {10 ; 20 ; 30 ; 40 ; 50 ; 60 ; 70 ; 80 90100110120 ; 130140150160170180}

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

  Entrez = D9 #ou = D9 : i11 dans une cellule vide. Un tableau de cellules de 3 x 6 s’affiche avec les mêmes valeurs que celles affichées dans la cellule D9 : D11. Le signe # est appelé l’ opérateur de plage renverséeet il est Excel’s de faire référence à la plage de la matrice entière au lieu de la taper.

  

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

  Vous pouvez obtenir les résultats d’une formule de tableau renversé et les convertir en parties de composants. Sélectionnez la cellule D9, puis appuyez sur F2 pour passer en mode d’édition. Ensuite, appuyez sur F9 pour convertir les références de cellule en valeurs, puis sur Excel dans une constante matricielle. Lorsque vous appuyez sur 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 d’une plage de cellules. Cela inclut les espaces.

  

  = SOMME (NBCAR (C9 : C13))

  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 alors ces valeurs et affiche le résultat (66). Pour obtenir le nombre moyen de caractères, vous pouvez utiliser les éléments suivants :

  = MOYENNE (FONCTION LEN (C9 : C13))

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

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

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

  Examinons la formule d’un peu plus près, en commençant par les éléments intérieurs et en procédant vers l’extérieur. La fonction NBCAR renvoie la longueur de chaque élément de la plage de cellules D2 : D6. La fonction max calcule la plus grande valeur parmi les éléments, ce qui correspond à la chaîne de texte la plus longue dans la cellule D3.

  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 (FONCTION LEN (C9 : C13)

  et cette chaîne réside dans le tableau suivant :

  NBCAR (C9 : C13)

  L’argument match type dans le cas présent est 0. Le type de match peut être une valeur de 1, 0 ou-1.

  • 1 : renvoie la plus grande valeur qui est inférieure ou égale à la valeur valeur de recherche.
  • 0 : renvoie la première valeur exactement égale à la valeur de recherche.
  • -1 : renvoie la plus petite valeur supérieure ou égale à la valeur de recherche spécifiée.
  • Si vous omettez de spécifier un type de correspondance, Excel prend la valeur 1.

  Enfin, la fonction index accepte les arguments suivants : un tableau et un numéro de ligne et de colonne dans ce tableau. La plage de cellules C9 : C13 fournit la matrice, la fonction EQUIV 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 souhaitez obtenir le contenu de la chaîne de texte la plus petite, remplacez MAX dans l’exemple ci-dessus par min.

• 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, où un tableau d’exemples de données dans les cellules B9 : B18has a été créé avec : = ent (ALEAT(10 ; 1) * 100). Notez que ALEAT est une fonction volatile, de sorte que vous obtenez un nouvel ensemble de nombres aléatoires chaque fois qu’Excel calcule.

  

  Entrez = petit (B9 #, séquence (D9), = petit (B9 : B18 ; {1 ; 2 ; 3})

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

  = SOMME (PETITE (B9, ORDRE (D9))

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

• Chercher les n valeurs les plus grandes dans une plage

  Pour rechercher les valeurs les plus élevées dans une plage, vous pouvez remplacer la fonction petite en fonction de la fonction grande. Par ailleurs, l’exemple suivant utilise les fonctions LIGNE et INDIRECT.

  Entrez = grande grande (B9 #, ligne (indirect (« 1:3 »))) ou = grande (B9 : B18 ; ligne (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 zone 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 désormais 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 (c’est la 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. Vous pouvez aussi facilement utiliser une séquence :

  = SEQUENCE (10)

  Examinons la formule que vous avez utilisée auparavant, = grande grande (B9 #, ligne (indirecte (« 1:3 »))), en partant des parenthèses internes et en travaillant 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 un tableau de colonnes à trois cellules. La fonction grande. valeur 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. Pour trouver plus de valeurs, vous devez ajouter une plus grande plage de cellules à la fonction INDIRECT. Pour finir, comme avec les petits exemples, vous pouvez utiliser cette formule avec d’autres fonctions, telles que somme et moyenne.

Gestion des erreurs