Tout ce que vous devez savoir sur les constantes de tableau dans Excel

Les constantes de tableau dans Microsoft Excel sont des outils puissants pour effectuer plusieurs calculs avec une seule formule. L'utilisation de constantes de tableau dans vos feuilles de travail Excel évite la nécessité de formules longues ou répétées et élimine le risque de changements de données accidentelles.

Les gens qui ne connaissent pas les constantes de tableau les trouvent souvent intimidants en raison de leur syntaxe inhabituelle et de la nécessité d'un symbole spécial pour les entrer. Donc, dans ce guide, je vais les retirer à leurs os nus et vous montrer comment les utiliser dans des scénarios du monde réel. À la fin, vous serez un expert constant du tableau!

Constantes de tableau dans Excel: Définition

Une constante de tableau est un ensemble codé dur de valeurs statiques entrées dans une formule, enfermée en accolades bouclées. Dans un exemple simple, tapant:

={10,20,30,40}

dans la cellule A1 et en appuyant sur Entrée renvoie un tableau dynamique de 10 dans la cellule A1, 20 dans la cellule B1, 30 dans la cellule C1 et 40 dans la cellule D1.

Dans l'exemple ci-dessus, le résultat du tableau dynamique est renversé horizontalement (dans les colonnes) car les virgules sont utilisées entre chaque valeur dans la constante de tableau.

Un réseau dynamique est un tableau qui s'étend de la cellule où la formule est tapée dans les cellules adjacentes, également connue sous le nom de déversement. Bien que les constantes de tableau sont des ensembles statiques de valeurs, ils peuvent faire partie d'un tableau dynamique lorsqu'ils sont utilisés dans une formule avec ce comportement de déversement.

D'un autre côté, l'utilisation de demi-colons entre les valeurs de la constante de tableau renvoie un tableau dynamique vertical (en lignes):

={10;20;30;40}

Vous pouvez également combiner des virgules et des semi-colons dans une constante de tableau pour retourner un tableau dynamique bidimensionnel:

={10,20;30,40}

Les constantes de tableau fonctionnent différemment qu'auparavant

Les versions uniques d'Excel à partir de 2021, Excel pour Microsoft 365 et Excel pour le Web prennent en charge les formules de tableau dynamique. Cela signifie que lorsque vous tapez une constante de tableau dans une formule qui attend plusieurs valeurs et appuyez sur Entrée, le résultat se déverse dans les cellules adjacentes, avec seulement la cellule supérieure contenant une formule.

Cependant, dans les anciennes versions d'Excel qui ne prennent pas en charge les formules de tableau dynamique, vous devez sélectionner toutes les cellules où vous souhaitez que les résultats soient affichés, tapez la formule et appuyez sur Ctrl + Shift + Entrée pour le commettre. À ce stade, la même formule apparaît dans toutes les cellules que vous avez sélectionnées. Ceci est connu sous le nom de formule CSE ou d'héritage.

Tous les exemples utilisés dans cet article se réfèrent aux constantes de tableau dans les versions d'Excel qui prennent en charge les formules de tableau dynamique.

Utilisation de constantes de tableau avec des fonctions Excel

Les constantes de tableau sont les plus utiles dans Excel lorsqu'ils sont associés à des fonctions, car ils vous empêchent de saisir des formules multiples ou longues. Voici deux exemples de scénarios.

SUM et COUNFIF: Compter le nombre de valeurs dans un tableau répondent à plusieurs critères

Imaginez que vous avez cette table Excel, nommée T_taskloget vous voulez découvrir le nombre de tâches dont le statut est Complet, En attenteou Tangué.

Pour ce faire sans constantes de tableau, vous pouvez taper la formule suivante (divisée sur des lignes distinctes pour une lecture plus facile):

=COUNTIF(T_TaskLog(Status),"Complete")
+
COUNTIF(T_TaskLog(Status),"Pending")
+
COUNTIF(T_TaskLog(Status),"Pitched")

où les résultats de trois formules de countif distinctes sont additionnés à l'aide de l'opérateur plus (+).

Cependant, cette formule est longue et difficile à analyser. Au lieu de cela, vous pouvez utiliser des constantes de tableau pour regrouper tous les critères en un seul argument. Tout d'abord, tapez le nom de la fonction, une parenthèse d'ouverture et la plage à évaluer, suivie d'une virgule:

=COUNTIF(T_TaskLog(Status),

Maintenant, pour les critères, vous devez saisir les trois valeurs d'état en tant que constante de tableau, enfermée en accolades bouclées. Ensuite, ajoutez une parenthèse de clôture et appuyez sur Entrée:

=COUNTIF(T_TaskLog(Status),{"Complete","Pending","Pitched"})

À ce stade, Excel renvoie trois résultats distincts en tant que tableau dynamique – 3 pour Complet2 pour En attenteet 2 pour Tangué. C'est parce que vous n'avez pas encore dit à Excel d'ajouter les résultats ensemble.

Pour résoudre ce problème, enveloppez la formule entière à l'intérieur de la fonction de somme et appuyez sur Entrée:

=SUM(COUNTIF(T_TaskLog(Status),{"Complete","Pending","Pitched"}))

Ainsi, plutôt que d'utiliser trois formules de countif distinctes séparées par le signe plus pour compter les trois critères de texte, les forces constantes du tableau excellent pour effectuer le calcul trois fois – une pour chaque critère – et ajouter les résultats ensemble via la fonction de somme.

Grand: trouver le haut x Valeurs dans une plage

Dans cet exemple, disons que vous souhaitez retourner les trois principaux bénéfices de la table, nommés T_profitscomme un tableau séparé.

Une façon de le faire est d'utiliser la grande fonction dans trois cellules distinctes.

La saisie de cette formule dans la cellule E2 renvoie la plus grande valeur dans la colonne de profit:

=LARGE(T_Profits(Profit),1)

Ensuite, la saisie de cette formule dans la cellule E3 renvoie la deuxième plus grande valeur:

=LARGE(T_Profits(Profit),2)

Enfin, la saisie de cette formule dans la cellule E4 renvoie la troisième valeur la plus importante:

=LARGE(T_Profits(Profit),3)

Vous pouvez ensuite utiliser la fonction xlookup pour récupérer les identifiants des magasins où ces trois bénéfices les plus élevés ont été réalisés:

=XLOOKUP(E2:E4,T_Profits(Profit),T_Profits(Shop))

Cependant, la saisie de trois formules distinctes et non liées prend du temps et est sujette à l'erreur. Au lieu de cela, vous pouvez utiliser une constante de tableau avec la grande fonction pour obtenir le même résultat avec une seule formule:

=LARGE(T_Profits(Profit),{1;2;3})

Cette formule applique la grande fonction trois fois – la première fois pour retourner la valeur la plus importante, la deuxième fois pour retourner la deuxième valeur la plus importante et la troisième fois pour retourner la troisième valeur la plus importante – et renvoie les trois résultats en tant que tableau dynamique vertical car un point-virgule est utilisé entre chaque nombre dans le tableau constant.

De même, si vous souhaitez résumer les trois plus grands bénéfices sans utiliser de constantes de tableau, vous devrez saisir la grande fonction trois fois, séparée par le signe plus:

=LARGE(T_Profits(Profit),1)+LARGE(T_Profits(Profit),2)+LARGE(T_Profits(Profit),3)

Cependant, l'utilisation d'une constante de tableau avec la fonction de somme rend la formule beaucoup plus propre:

=SUM(LARGE(T_Profits(Profit),{1,2,3}))

De plus, si vous vouliez inclure le quatrième bénéfice le plus important dans le calcul, vous devez simplement ajouter une virgule et le numéro quatre à la formule, plutôt que de taper une fonction extra grande dans son intégralité:

=SUM(LARGE(T_Profits(Profit),{1,2,3,4}))

Nommer les constantes de tableau dans Excel

Si vous vous retrouvez à plusieurs reprises en utilisant le même ensemble de valeurs statiques dans Excel, nommer une constante de tableau vous évite de le taper manuellement à chaque fois.

Pour créer une constante de tableau nommée, dans l'onglet Formules du ruban, cliquez sur « Nom Manager ».

Ensuite, dans la boîte de dialogue Nom Manager, cliquez sur « Nouveau ».

Ensuite, dans le champ référence, tapez le signe égal, suivi d'une attelle bouclée d'ouverture:

={

Maintenant, tapez la constante de tableau, en se rappelant que pour créer un tableau horizontal (ligne), les valeurs doivent être séparées par des virgules et pour les tableaux verticaux (colonne), vous devez les séparer par des demi-colons. N'oubliez pas non plus que les valeurs de texte doivent être enfermées en doubles citations. Lorsque vous avez terminé, fermez les accolades bouclées:

={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}

Enfin, dans le champ Nom, saisissez un nom mémorable pour votre constante de tableau et cliquez sur « OK ».

Vous pouvez désormais utiliser cette constante de tableau dans votre classeur en tapant le signe égal suivi du nom que vous venez d'attribuer et en appuyant sur Entrée:

Comme pour les constantes de tableau sans nom, vous pouvez les utiliser dans des formules. Dans cet exemple, la constante de tableau nommée Cinq est:

={5,10,15,20}

Ainsi, la multiplication de la valeur dans la cellule A1 par cette constante de tableau nommée produit quatre résultats distincts:

=A1*Fives

Points à noter lors de l'utilisation de constantes de tableau dans Excel

Malgré les nombreux avantages de l'utilisation de constantes de tableau dans Excel, il y a certaines règles et limitations dont vous devriez être conscient:

• Les formules dynamiques de réseau ne peuvent pas se répandre dans des colonnes de table structurées, de sorte que les constantes de réseau qui produisent des résultats multi-cellules ne peuvent être utilisées que dans les cellules régulières. Cela dit, les constantes de tableau elles-mêmes peuvent fonctionner sur des données dans des tables.

• Les constantes de tableau ne peuvent contenir du texte que de doubles devis, des nombres simples (pas de symboles de devise ou de pourcentage de signes) ou des valeurs booléennes (vraies et fausses), séparées par les délimiteurs de virgule et de semi-colon. Ils ne peuvent pas contenir de références cellulaires, d'autres tableaux, formules ou des caractères génériques.

• Parce que vous ne pouvez pas référencer les cellules dans les constantes de tableau, toutes les valeurs doivent être codées durs (d'où leur nom, tableau constantes). En conséquence, les formules contenant des constantes de tableau sont moins flexibles que celles sans.

• Si vous prévoyez de partager votre classeur avec des personnes moins expérimentées dans Excel, méfiez-vous de l'effet de boîte noire que les constantes de réseau créent. C'est-à-dire que, car le processus de calcul dans les constantes de tableau est moins transparent que dans les formules standard, ceux qui ne sont pas familiers avec la logique du tableau peuvent avoir du mal à comprendre comment fonctionne la feuille de calcul.

• Dans certains cas, l'utilisation d'une fonction de tableau dynamique est plus efficace que l'utilisation d'une constante de tableau. Par exemple, plutôt que de saisir une constante telle que {1,2,3}, vous pouvez utiliser la fonction de séquence pour produire le même résultat.

• Étant donné que la façon dont les constantes de tableau sont gérées dans Excel ont radicalement changé lorsque des tableaux dynamiques ont été introduits, lorsqu'un fichier Excel créé dans une version moderne est ouvert dans une version plus ancienne, les tableaux renversés sont convertis en formules CSE.

---

Les constantes de tableau sont particulièrement pratiques lorsqu'elles sont utilisées comme arguments dans les fonctions Excel qui ne renvoient pas les tableaux dynamiques par défaut. Cependant, les fonctions dynamiques du tableau – disponibles dans les versions uniques d'Excel à partir de 2021, Excel pour Microsoft 365 et Excel pour le Web – sont conçues spécifiquement pour retourner plusieurs valeurs dans une gamme de cellules. Des exemples de ces fonctions spéciales incluent le filtre, le tri et le tri, unique et xlookup.