Sommaire
Une erreur #SPILL se produit lorsqu'une plage de déversement est bloquée par quelque chose sur la feuille de calcul. La solution consiste généralement à effacer la zone de déversement de toute donnée obstruant. Voir ci-dessous pour plus d'informations et les étapes à suivre.
Explication
À propos des déversements et du #SPILL! Erreur
Avec l'introduction des tableaux dynamiques dans Excel, les formules qui renvoient plusieurs valeurs «déversent» ces valeurs directement sur la feuille de calcul. Le rectangle qui entoure les valeurs est appelé "plage de déversement". Lorsque les données changent, la plage de déversement augmente ou se contracte selon les besoins. Vous pouvez voir de nouvelles valeurs ajoutées ou des valeurs existantes disparaître.
Vidéo: déversement et plage de déversement
Une erreur #SPILL se produit lorsqu'une plage de déversement est bloquée par quelque chose sur la feuille de calcul. Parfois, cela est attendu. Par exemple, vous avez entré une formule, vous vous attendez à ce qu'elle déborde, mais les données existantes dans la feuille de calcul vous gênent. La solution consiste simplement à effacer la zone de déversement de toute donnée obstruant.
Parfois, cependant, l'erreur peut être inattendue et donc déroutante. Lisez ci-dessous comment cette erreur pourrait être causée et ce que vous pouvez faire pour la résoudre.
Le comportement en cas de déversement est natif
Il est important de comprendre que le comportement de déversement est automatique et natif. Dans Dynamic Excel (actuellement Office 365 Excel uniquement), toute formule, même une formule simple sans fonctions, peut renverser les résultats. Bien qu'il existe des moyens d'empêcher une formule de renvoyer plusieurs résultats, le débordement ne peut pas être désactivé avec un paramètre global.
De même, il n'y a pas d'option dans Excel pour "désactiver les erreurs #SPILL. Pour corriger une erreur #SPILL, vous devrez rechercher et résoudre la cause première du problème.
Fix # 1 - effacez la plage de déversement
C'est le cas le plus simple à résoudre. La formule doit déborder de plusieurs valeurs, mais à la place, elle renvoie #SPILL! parce qu'il y a quelque chose sur le chemin. Pour résoudre l'erreur, sélectionnez n'importe quelle cellule dans la plage de déversement afin que vous puissiez voir ses limites. Ensuite, déplacez les données de blocage vers un nouvel emplacement ou supprimez complètement les données. Notez que les cellules de la plage de déversement doivent être vides, faites donc attention aux cellules qui contiennent des caractères invisibles, comme des espaces.
Dans l'écran ci-dessous, le «x» bloque la plage de déversement:
Une fois que le "x" est supprimé, la fonction UNIQUE se répand normalement:
Correction n ° 2 - ajouter le caractère @
Avant les tableaux dynamiques, Excel appliquait silencieusement un comportement appelé «intersection implicite» pour garantir que certaines formules susceptibles de renvoyer plusieurs résultats ne renvoyaient qu'un seul résultat. Dans Excel de tableau non dynamique, ces formules renvoient un résultat d'aspect normal sans erreur. Cependant, dans certains cas, la même formule entrée dans Dynamic Excel peut générer une erreur #SPILL. Par exemple, dans l'écran ci-dessous, la cellule D5 contient cette formule, copiée vers le bas:
=$B$5:$B$10+3
Cette formule ne lèverait pas d'erreur dans, disons Excel 2016, car l'intersection implicite empêcherait la formule de renvoyer plusieurs résultats. Cependant, dans Dynamic Excel, la formule renvoie automatiquement plusieurs résultats sur la feuille de calcul et qui se bloquent les uns dans les autres, car la formule est copiée à partir de D5: D10.
Une solution consiste à utiliser le caractère @ pour activer l'intersection implicite comme ceci:
= @$B$5:$B$10+3
Avec cette modification, chaque formule renvoie à nouveau un seul résultat et l'erreur #SPILL disparaît.
Remarque: cela explique en partie pourquoi vous pourriez soudainement voir le caractère «@» apparaître dans les formules créées dans les anciennes versions d'Excel. Ceci est fait pour maintenir la compatibilité. Étant donné que les formules des anciennes versions d'Excel ne peuvent pas se répandre dans plusieurs cellules, le @ est ajouté pour garantir le même comportement lorsque la formule est ouverte dans Dynamic Excel.
Fix # 3 - Formule de tableau dynamique natif
Une autre (meilleure) façon de corriger l'erreur #SPILL indiquée ci-dessus consiste à utiliser une formule de tableau dynamique native dans D5 comme ceci:
=B5:B10+3
Dans Dynamic Excel, cette formule unique répandra les résultats dans la plage D5: D10, comme le montre la capture d'écran ci-dessous:
Notez qu'il n'est pas nécessaire d'utiliser une référence absolue.
