Gérer l’erreur SPILL

Lorsqu’une formule de tableau dynamique ne peut pas disposer l’intégralité de son résultat, Excel affiche #SPILL! dans la cellule d’ancrage. La formule est généralement correcte ; le problème vient du fait qu’un élément bloque l’emplacement où les résultats doivent s’afficher.

Imaginez que vous essayiez de garer un autobus sur une place déjà occupée par une voiture. L’autobus fonctionne parfaitement, mais la place est bloquée.

La cause la plus fréquente est qu’une ou plusieurs cellules de la plage de déploiement contiennent déjà des données. Si =UNIQUE(A2:A12) doit remplir C1:C3 mais que C2 contient une valeur indésirable, le déploiement est bloqué.

La solution est simple : sélectionnez la cellule d’ancrage, repérez le contour en pointillés de la plage de déploiement et effacez les cellules non vides qui se trouvent à l’intérieur.

Cliquez sur la cellule affichant #SPILL!. Excel trace une bordure en pointillés autour de la zone que le résultat souhaite occuper. Toute cellule non vide à l’intérieur de cette bordure est responsable du blocage.

Vous pouvez également cliquer sur le triangle d’avertissement à côté de la cellule et choisir Sélectionner les cellules obstructives. Excel sélectionne alors directement les cellules concernées afin que vous puissiez supprimer leur contenu.

Une plage de déploiement ne peut chevaucher aucune cellule fusionnée. Même une cellule fusionnée vide est considérée comme un obstacle, car le déploiement nécessite des cellules individuelles distinctes à remplir.

Si votre résultat arrive sur une barre de titre ou une étiquette fusionnée, annulez la fusion de ces cellules (onglet Accueil, bouton Fusionner et centrer) ; la formule pourra alors se déployer correctement.

Le déploiement échoue également s’il devait dépasser le bord de la feuille de calcul ou entrer dans un tableau. Par exemple, une formule située près de la dernière ligne et qui doit se déployer vers le bas n’a aucun espace disponible.

Déplacez la formule plus haut ou vers une colonne disposant d’espace en dessous. Vérifiez qu’il y a suffisamment de cellules vides dans la direction du déploiement.

Les tableaux Excel (créés avec Ctrl+T) n’autorisent pas le déploiement de tableaux dynamiques à l’intérieur de ceux-ci, car chaque cellule d’un tableau doit pouvoir être modifiée.

Si votre formule déployée se trouve dans une colonne de tableau, vous obtiendrez #SPILL!. Convertissez le tableau en plage normale (Création de tableau, Convertir en plage) ou déplacez la formule à l’extérieur du tableau.

指向整列的溢出公式也可能触发 #SPILL!，因为结果会变得非常庞大，可能与其他内容冲突或超出限制。

例如，=A:A*2 会尝试溢出一百万行。请将引用限制为实际数据范围，例如 =A2:A100*2，这样溢出范围的大小就可控且明确。

Placez le curseur sur le triangle d’avertissement d’une cellule #SPILL! et Excel vous explique exactement pourquoi. Les messages courants incluent La plage de propagation n’est pas vide, La plage de propagation contient des cellules fusionnées et La plage de propagation est trop grande.

Lisez toujours cette info-bulle en premier. Elle vous mène directement à la cause au lieu de vous laisser deviner, et chaque message correspond à l’une des corrections que vous venez d’apprendre.

Il est important de retenir qu’une erreur #SPILL! signifie rarement que la logique de votre formule est incorrecte. Le calcul a réussi ; seul le placement a échoué.

Une fois l’obstacle supprimé, la même formule, sans aucune modification, se propage parfaitement. Comparez cela à #VALUE! ou #REF!, qui signalent généralement un véritable problème dans la formule elle-même.

Vous saisissez =SORT(UNIQUE(A2:A20)) dans D1 et obtenez #SPILL!. Vous sélectionnez D1, voyez le contour en pointillés couvrir D1:D5 et remarquez une ancienne note dans D3.

Vous supprimez le contenu de D3. Instantanément, la formule propage la liste triée des valeurs distinctes vers le bas, dans D1:D5. Il n’a pas été nécessaire de modifier la formule.

Un peu de planification permet d’éviter la plupart des erreurs de propagation. Réservez à vos formules de tableaux dynamiques une colonne ou une zone vide où elles pourront s’étendre, et éloignez les étiquettes et les notes de la direction de propagation.

Lorsque vous faites référence à une autre propagation, utilisez l’opérateur #, comme dans =SUM(D1#), afin que vos formules s’adaptent lorsque les plages changent de taille. Créez vos résultats dans un espace dégagé et ouvert, et #SPILL! apparaîtra rarement.

Vous avez appris que #SPILL! est un problème de placement, et non une erreur de logique :

• Le plus souvent, une cellule de la plage de propagation n’est pas vide.
• Les cellules fusionnées et les tableaux empêchent la propagation.
• Évitez les références à des colonnes entières, qui rendent le résultat trop grand.
• Utilisez Sélectionner les cellules qui bloquent et l’info-bulle pour trouver la cause.

Le cours sur les tableaux dynamiques et la propagation est maintenant terminé. Vous pouvez à présent renvoyer plusieurs résultats à partir d’une seule formule et corriger les propagations en toute confiance.