Formules de tableau dynamiques et comportement de tableau propagé

Les formules Excel qui retournent un ensemble de valeurs, également appelé tableau, retournent ces valeurs aux cellules adjacentes. Ce comportement est appelé spilling.

Les formules qui peuvent retourner des tableaux de taille variable sont appelées formules de tableau dynamique . Les formules qui retournent actuellement des tableaux qui sont correctement propagées peuvent être appelées formules de tableau propagées .

Voici quelques notes pour vous aider à comprendre et utiliser ces types de formules.

Que signifie le débordement ?

Remarque : Les formules matricielles plus anciennes, appelées formules de tableau héritées , retournent toujours un résultat de taille fixe : elles débordent toujours dans le même nombre de cellules. Le comportement de débordement décrit dans cette rubrique ne s’applique pas aux formules de tableau héritées.

Le débordement signifie qu’une formule a entraîné plusieurs valeurs et que ces valeurs ont été placées dans les cellules adjacentes. Par exemple, =SORT(D2:D11,1,-1), qui trie un tableau dans l’ordre décroissant, retourne un tableau correspondant de 10 lignes de haut. Toutefois, il vous suffit d’entrer la formule dans la cellule supérieure gauche, ou F2 dans ce cas, et elle se propagera automatiquement à la cellule F11.

Trier les valeurs dans les cellules D2:D11 avec =SORT(D2:D11,1,-1)

Points essentiels

Lorsque vous appuyez sur Entrée pour confirmer votre formule, Excel dimensionne dynamiquement la plage de sortie pour vous et place les résultats dans chaque cellule de cette plage.
Si vous écrivez une formule de tableau dynamique pour agir sur une liste de données, il peut être utile de la placer dans un tableau Excel, puis d’utiliser des références structurées pour faire référence aux données. Cela est dû au fait que les références structurées s’ajustent automatiquement à mesure que des lignes sont ajoutées ou supprimées de la table.
Les formules de tableau propagées ne sont pas prises en charge dans les tables Excel elles-mêmes. Vous devez donc les placer dans la grille en dehors du tableau. Les tables sont mieux adaptées à la conservation de lignes et de colonnes de données indépendantes.
Une fois que vous avez entré une formule de tableau propagée, lorsque vous sélectionnez une cellule dans la zone de débordement, Excel place une bordure en surbrillance autour de la plage. La bordure disparaît lorsque vous sélectionnez une cellule en dehors de la zone.
Seule la première cellule de la zone de débordement est modifiable. Si vous sélectionnez une autre cellule dans la zone de débordement, la formule est visible dans la barre de formule, mais le texte est sous forme « fantôme » et ne peut pas être modifié. Si vous devez mettre à jour la formule, vous devez sélectionner la cellule en haut à gauche dans la plage de tableaux, la modifier en fonction des besoins, puis Excel met automatiquement à jour le reste de la zone de débordement lorsque vous appuyez sur Entrée.
Chevauchement de formule : les formules matricielles ne peuvent pas être entrées si quelque chose bloque la plage de sortie. Et si cela se produit, Excel renvoie une erreur #PROPAGATION! indiquant qu’il y a un blocage. Si vous supprimez le blocage, la formule se propagera comme prévu. Dans l’exemple ci-dessous, la plage de sortie de la formule chevauche une autre plage avec des données et s’affiche avec une bordure pointillée qui chevauche des cellules avec des valeurs indiquant qu’elle ne peut pas se propager. Supprimez les données bloquantes ou copiez-les ailleurs, et la formule se propagera comme prévu.
Les formules de tableau héritées entrées via CTRL+MAJ+ENTRÉE (CSE) sont toujours prises en charge pour des raisons de compatibilité descendante, mais ne doivent plus être utilisées. Si vous le souhaitez, vous pouvez convertir des formules de tableau héritées en formules de tableau dynamique en localisant la première cellule de la plage de tableaux, copier le texte de la formule, supprimer toute la plage du tableau hérité, puis entrer à nouveau la formule dans la cellule supérieure gauche. Avant de mettre à niveau des formules de tableau héritées vers des formules de tableau dynamiques, vous devez connaître certaines différences de calculentre les deux.
La prise en charge par Excel des tableaux dynamiques entre des classeurs est limitée. Si vous fermez le classeur source, les formules de tableau dynamique liées renverront une erreur #REF!. lorsqu'ils sont actualisés.