Tout ce que vous devez savoir sur les déversements dans Excel

La plupart des formules dans Excel renvoient un résultat sur une seule cellule. Cependant, si une formule Excel renvoie un ensemble de valeurs, le résultat sera répandre vers les cellules voisines.

Les formules de débordement et de tableau dynamique ne sont pas prises en charge dans Excel 2019 ou version antérieure.

Les définitions que vous devez connaître en premier

Avant de vous montrer des exemples de déversement en action, voici les définitions de certains termes Excel associés dans ce guide :

• Effusion: Lorsque les formules Excel renvoient un ensemble de valeurs (également appelé tableau).
• Tableau: Un ensemble de données dans une plage de cellules. Par exemple, si les cellules A1 à A20 contiennent des données, A1:A20 correspond à la plage et les données contenues dans cette plage constituent le tableau.
• Fonction: Formule prédéfinie qui effectue un calcul lorsqu'elle est utilisée dans une formule.
• Formule: Une combinaison de fonctions, de références de cellules, de valeurs, d'opérateurs et de noms qui, lorsqu'ils sont utilisés ensemble après le signe égal (=), renvoie un résultat
• Formule de tableau dynamique : Une formule capable de renvoyer un tableau.
• Formule de tableau renversé : Une formule qui renvoie actuellement un tableau renversé.

Exemples de déversements

Tout d’abord, regardons un exemple simple de déversement dans Excel. Cette feuille de calcul Excel contient six équipes de quiz et leurs scores pour les semaines un à cinq.

Parce que

=B2:B7

est une formule matricielle dynamique, la saisir dans la cellule H2 renvoie un tableau réparti couvrant H2 à H7. Remarquez comment, lorsque je sélectionne la cellule H2, Excel place une bordure temporaire autour de ces cellules pour me rappeler qu'il s'agit d'un tableau dispersé.

Si je devais saisir des valeurs dans les cellules H3 à H7, cela interférerait et briserait le tableau renversé qu'Excel vient de créer, ce qui entraînerait un #SPILL ! erreur. Je parlerai davantage de #SPILL ! erreurs sous peu.

Maintenant, cependant, je souhaite utiliser de manière plus pratique ce type de résultat en utilisant la fonction OFFSET. Dans ce cas, je souhaite qu'Excel me dise ce que chaque équipe a marqué au cours d'une semaine donnée, en fonction du nombre que je tape dans la cellule I1.

Pour ce faire, dans la cellule I2, je vais taper

=OFFSET(A2,0,I1,6,1)

parce que je veux qu'Excel commence à la cellule A2, reste sur la même ligne, se déplace sur le nombre de colonnes que j'ai tapé dans la cellule I1 et renvoie un résultat de 6 lignes vers le bas et 1 colonne de diamètre. Étant donné qu'Excel renvoie un tableau de 6 lignes de hauteur, même si j'ai uniquement tapé la formule dans la cellule I2, le résultat s'étend des cellules I1 à I7.

Pour ranger mon tableau de récupération de données, je souhaite également ajouter les noms des équipes dans la colonne H. Pour ce faire, je vais taper

=A2:A7

dans la cellule H2, qui produit également un tableau renversé. J'ai également appliqué une mise en forme pour améliorer la lisibilité de la feuille de calcul.

L'un des avantages de l'utilisation de formules matricielles dynamiques telles que celles ci-dessus est que si les données devaient changer ou être réorganisées, les tableaux dispersés s'ajusteraient en conséquence.

Dans un dernier exemple, je vais utiliser la fonction XLOOKUP pour produire un tableau renversé. Mon objectif est d'afficher l'ensemble des résultats d'une certaine équipe sur toute la période de cinq semaines, l'équipe en question dépendant de la valeur de la cellule I1. Tout d'abord, je vais taper la formule du tableau dynamique

=B1:F1

dans la cellule H3 pour produire le tableau réparti des numéros de semaine dans les cellules H3 à L3.

Maintenant, je vais créer une liste déroulante dans la cellule I1 contenant les noms d'équipe répertoriés dans les cellules A2 à A7. Pour ce faire, je vais cliquer sur Données > Validation des données, choisir « Liste » dans le champ « Autoriser » et sélectionner les cellules A2 à A7 pour définir la source de la liste, avant de cliquer sur « OK ».

Enfin, je peux utiliser la fonction XLOOKUP pour renvoyer les scores de mon équipe sélectionnée sur les cinq semaines. Dans la cellule H3, je tape :

=XLOOKUP(I1,A2:A7,B2:F7)

car je veux qu'Excel prenne le nom de l'équipe dans la cellule I1, le fasse correspondre à un nom d'équipe compris dans la plage A2 à A7 et renvoie le tableau correspondant des colonnes B à F. Étant donné que le résultat a une largeur de cinq colonnes, il est affiché sous la forme d'un tableau répandu sur les cellules H3 à L3. Dans ce cas, j'ai sélectionné l'équipe Quizpicable Me dans ma liste déroulante dans la cellule I1.

Les autres fonctions qui entraînent généralement un tableau dispersé incluent FILTER, SORT, UNIQUE et RANDARRAY.

Référencer une plage renversée

Pour référencer une plage renversée, utilisez l’opérateur de plage renversée (#) après la plage dans la formule matricielle dynamique. En utilisant le même exemple que ci-dessus, je souhaite afficher un score total pour une équipe donnée en fonction du tableau réparti dans les cellules H3 à L3.

Alors, en tapant

=SUM(H3#)

dans la cellule L4 indique à Excel de additionner le tableau dans la plage déversée commençant à H3.

Réparez le #SPILL commun ! Erreurs

Certaines situations amèneront Excel à renvoyer le #SPILL ! erreur. Voici les éléments à surveiller si cela se produit dans votre feuille de calcul, ainsi que ce que vous pouvez faire pour résoudre le problème :

---

Si votre formule matricielle dynamique fait référence à une colonne entière, vous pouvez utiliser la fonction TRIMRANGE pour indiquer à Excel de réduire les lignes vides, en coupant essentiellement les données pour inclure uniquement les cellules nécessaires.