Excel formules qui retournent un jeu de valeurs, également appelé matrice, retournent ces valeurs aux cellules voisines. Ce comportement est appelé spilling.

Les formules qui peuvent renvoyer des tableaux de taille variable sont appelées formules de tableau dynamique. Les formules qui retournent actuellement des tableaux qui ont été renversés peuvent être appelées formules de tableaux renversés. 

Voici quelques remarques pour vous aider à comprendre et utiliser ce type de formules. 

Que signifie la spill ?

Remarque : Les formules de tableau plus anciennes, appelées formules de tableau héritées, retournent toujours un résultat de taille fixe: elles se répandent 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.

Spill signifie qu’une formule a produit plusieurs valeurs et que ces valeurs ont été placées dans les cellules voisines. Par exemple, =TRIER(D2:D11;1;-1), qui trie une matrice dans l’ordre décroit, retourne un tableau correspondant de 10 lignes en hauteur. Mais il vous suffit d’entrer la formule dans la cellule supérieure gauche, ou F2 dans ce cas, et elle se répandra automatiquement vers le bas jusqu’à la cellule F11.

Trier les valeurs dans les cellules D2:D11 avec =TRIER(D2:D11;1;-1)

Points clés

  • Lorsque vous appuyez sur Entrée pour confirmer votre formule, Excel resserra 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. En fait, les références structurées s’ajustent automatiquement à mesure que des lignes sont ajoutées ou supprimées du tableau.

  • Les formules de tableaux renversés ne sont pas Excel dans les tableaux proprement dits, vous devez les placer dans la grille en dehors de la table. Les tables sont idéales pour tenir des lignes et des colonnes de données indépendantes.

  • Lorsque vous entrez une formule de tableau renversé, lorsque vous sélectionnez n’importe quelle cellule dans la zone de débordement, Excel une bordure en surbrillant autour de la plage. La bordure disparaît lorsque vous sélectionnez une cellule en dehors de la zone.

    Formule de tableau avec la plage de sortie mise en évidence avec une bordure bleue

  • Seule la première cellule dans la zone de débordement est modifiable. Si vous sélectionnez une autre cellule dans la zone de débordement, la formule sera visible dans la barre de formule, mais le texte est « fantôme » et ne peut pas être modifié. Si vous avez besoin de mettre à jour la formule, vous devez sélectionner la cellule supérieure gauche dans la plage de tableau, la modifier selon vos besoins, puis Excel met automatiquement à jour le reste de la zone de débordement lorsque vous appuyez sur Entrée.

    Image d’une formule ma matrice fantôme, ce qui signifie qu’elle n’est pas modifiable, car il ne s’agit pas de la première cellule de la plage de tableau

  • Chevauchement des formules - Les formules de tableau ne peuvent pas être entrées si la plage de sortie est bloqué. Et si cela se produit, Excel un message d#SPILL’erreur 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 en pointillés qui chevauche des cellules avec des valeurs indiquant qu’elle ne peut pas se spiller. Supprimez les données de blocage ou copiez-les ailleurs pour que la formule se répande comme prévu.

    Image d’un #SPILL! indiquant qu’une formule de tableau en sortie rencontre un blocage qui l’empêche de se spiller.

  • Les formules de tableau héritées entrées via CTRL+CSE sont toujours pris en charge pour des raisons de compatibilité arrière, mais ne doivent plus être utilisées. Si vous le souhaitez, vous pouvez convertir les formules de tableau héritées en formules de tableau dynamiques en localisant la première cellule dans la plage de tableau, copier le texte de la formule, supprimer la plage entière du tableau hérité, puis resserrez 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.

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

Fonction FILTRE

Fonction TABLEAU.ALEAT

Fonction SEQUENCE

Fonction TRI

Fonction TRIERPAR

Fonction UNIQUE

#SPILL ! erreurs dans Excel

Opérateur d'intersection implicite : @

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 ?

Avez-vous d’autres commentaires ? (Facultatif)

Nous vous remercions pour vos commentaires.

×