Excel formules qui retournent un ensemble de valeurs, également appelé tableau, retournent ces valeurs aux cellules voisines. 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 se répandent avec succès peuvent être appelées formules de tableau renversées

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

Qu’est-ce que le déversement signifie ?

Remarque : Les formules matricielles plus anciennes, appelées formules matricielles 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.

Le débordement signifie qu’une formule a entraîné plusieurs valeurs et que ces valeurs ont été placées dans les cellules voisines. 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. Mais il vous suffit d’entrer la formule dans la cellule supérieure gauche, ou F2 dans ce cas, et elle se répand automatiquement dans la cellule F11.

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

Points clés

  • 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 une table 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 renversées ne sont pas prises en charge dans Excel tables elles-mêmes. Vous devez donc les placer dans la grille en dehors de la table. Les tables sont les mieux adaptées pour contenir des lignes et des colonnes de données indépendantes.

  • Une fois que vous entrez une formule de tableau renversée, lorsque vous sélectionnez une cellule dans la zone de débordement, Excel place une bordure mise en surbrillance autour de la plage. La bordure disparaît lorsque vous sélectionnez une cellule en dehors de la zone.

    Formule matricielle avec la plage de sortie mise en surbrillance avec une bordure bleue

  • Seule la première cellule de la zone de déversement 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 « fantôme » et ne peut pas être modifié. Si vous devez mettre à jour la formule, vous devez sélectionner la cellule supérieure 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.

    Image d’une formule de tableau 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 tableaux

  • Chevauchement des formules : les formules matricielles ne peuvent pas être entrées s’il y a quelque chose qui bloque la plage de sortie. et si cela se produit, Excel renvoie une erreur #SPILL! 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 répandre. Supprimez les données bloquantes ou copiez-les ailleurs, et la formule débordera comme prévu.

    Image d’un #SPILL! indiquant qu’une sortie de formule matricielle rencontre un blocage qui l’empêche de se répandre.

  • 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é de retour, mais ne doivent plus être utilisées. Si vous le souhaitez, vous pouvez convertir des formules matricielles 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 la plage entière du tableau hérité, puis entrer à nouveau la formule dans la cellule supérieure gauche. Avant de mettre à niveau des formules matricielles héritées vers des formules de tableau dynamiques, vous devez connaître certaines différences de calcul entre 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 FILTER

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 Office insiders

Ces informations vous ont-elles été utiles ?

Dans quelle mesure êtes-vous satisfait(e) de la qualité de la langue ?
Qu’est-ce qui a affecté votre expérience ?

Nous vous remercions de vos commentaires.

×