Cet article vous aidera à comprendre toutes les causes des erreurs #SPILL ainsi que les solutions pour les corriger dans Excel 365.
#RÉPANDRE! est un nouveau type d'erreur Excel qui se produit principalement lorsqu'une formule qui produit plusieurs résultats de calcul tente d'afficher ses sorties dans une plage de débordement mais que cette plage contient déjà d'autres données.
Les données bloquantes peuvent être n'importe quoi, y compris une valeur de texte, des cellules fusionnées, un espace simple ou même lorsqu'il n'y a pas assez de place pour renvoyer les résultats. La solution est simple, effacez la plage de toutes les données bloquantes ou sélectionnez un tableau vide de cellules qui ne contient aucun type de données.
Une erreur de déversement se produit généralement lors du calcul de formules matricielles dynamiques, car la formule matricielle dynamique est celle qui génère les résultats dans plusieurs cellules ou un tableau. Examinons plus en détail et comprenons ce qui déclenche cette erreur dans Excel et comment la résoudre.
Qu'est-ce qui cause une erreur de déversement ?
Depuis le lancement des tableaux dynamiques en 2018, les formules Excel peuvent gérer plusieurs valeurs à la fois et renvoyer des résultats dans plusieurs cellules. Les tableaux dynamiques sont des tableaux redimensionnables qui permettent aux formules de renvoyer plusieurs résultats à une plage de cellules de la feuille de calcul en fonction d'une formule entrée dans une seule cellule.
Lorsqu'une formule matricielle dynamique renvoie plusieurs résultats, ces résultats se répandent automatiquement dans les cellules voisines. Ce comportement est appelé « Déversement » dans Excel. Et la plage de cellules dans laquelle les résultats se répandent est appelée « plage de déversement ». La plage de déversement s'étendra ou se contractera automatiquement en fonction des valeurs source.
Si une formule essaie de remplir une plage de déversement avec plusieurs résultats mais est bloquée par quelque chose sur cette plage, une erreur #SPILL se produit.
Excel dispose désormais de 9 fonctions qui utilisent la fonctionnalité Dynamic Array pour résoudre les problèmes, notamment :
- SÉQUENCE
- FILTRE
- TRANSPOSER
- SORTE
- TRIER PAR
- RANDONNÉE
- UNIQUE
- XRECHERCHE
- XMATCH
Les formules matricielles dynamiques ne sont disponibles que dans 'Excel 365' et elles ne sont actuellement prises en charge par aucun des logiciels Excel hors ligne (c'est-à-dire Microsoft Excel 2016, 2019).
Les erreurs de déversement ne sont pas seulement causées par l'obstruction des données, il existe plusieurs raisons pour lesquelles vous pouvez obtenir une erreur #Spill. Laissez-nous explorer les différentes situations où vous pourriez rencontrer le #SPILL ! erreur et comment les corriger.
La plage de déversement n'est pas vide
L'une des principales causes d'erreur de déversement est que la plage de déversement n'est pas vide. Par exemple, si vous essayez d'afficher 10 résultats, mais s'il y a des données dans l'une des cellules de la zone de déversement, la formule renvoie un #SPILL ! Erreur.
Exemple 1:
Dans l'exemple ci-dessous, nous avons entré la fonction TRANSPOSE dans la cellule C2 pour convertir la plage verticale de cellules (B2:B5) en une plage horizontale (C2:F2). Au lieu de basculer la colonne sur une ligne, Excel nous montre le #SPILL ! Erreur.
Et lorsque vous cliquez sur la cellule de formule, vous verrez une bordure en pointillés bleus indiquant la zone/plage de déversement (C2:F2) qui est nécessaire pour afficher les résultats comme indiqué ci-dessous. En outre, vous remarquerez un panneau d'avertissement jaune avec un point d'exclamation dessus.
Pour comprendre la raison de l'erreur, cliquez sur l'icône d'avertissement à côté de l'erreur et voyez le message dans la première ligne surligné en gris. Comme vous pouvez le voir, il est indiqué ici « La plage de déversement n'est pas vide ».
Le problème ici est que les cellules de la plage de débordement D2 et E2 ont des caractères de texte (pas vides), d'où l'erreur.
Solution:
La solution est simple, soit effacer les données (soit déplacer, soit supprimer) situées dans la plage de déversement ou déplacer la formule vers un autre emplacement où il n'y a pas d'obstruction.
Dès que vous supprimez ou déplacez le blocage, Excel remplira automatiquement les cellules avec les résultats de la formule. Ici, lorsque nous effaçons le texte dans D2 et E2, la formule transpose la colonne en ligne comme prévu.
Exemple 2 :
Dans l'exemple ci-dessous, bien que la plage de déversement semble vide, la formule affiche toujours le Spill ! Erreur. C'est parce que le déversement n'est pas réellement vide, il a un caractère d'espace invisible dans l'une des cellules.
Il est difficile de localiser les espaces ou tout autre caractère invisible caché dans ce qui semble être des cellules vides. Pour trouver de telles cellules contenant des données indésirables, cliquez sur le flotteur d'erreur (panneau d'avertissement) et sélectionnez « Sélectionner les cellules obstruantes » dans le menu et cela vous amènera à la cellule qui contient les données obstruantes.
Comme vous pouvez le voir, dans la capture d'écran ci-dessous, la cellule E2 comporte deux espaces. Lorsque vous effacez ces données, vous obtiendrez la sortie appropriée.
Parfois, le caractère invisible peut être un texte formaté avec la même couleur de police que la couleur de remplissage de la cellule ou une valeur de cellule personnalisée formatée avec le code numérique ;;;. Lorsque vous personnalisez la mise en forme d'une valeur de cellule avec ;;;, tout ce qui se trouve dans cette cellule sera masqué, quelle que soit la couleur de la police ou la couleur de la cellule.
La plage de déversement contient des cellules fusionnées
Parfois, le #SPILL ! erreur se produit lorsque la plage de débordement contient les cellules fusionnées. La formule matricielle dynamique ne fonctionne pas avec les cellules fusionnées. Pour résoudre ce problème, tout ce que vous avez à faire est d'annuler la fusion des cellules de la plage de débordement ou de déplacer la formule vers une autre plage qui n'a pas de cellules fusionnées.
Dans l'exemple ci-dessous, même si la plage de déversement est vide (C2:CC8), la formule renvoie l'erreur de déversement. C'est parce que les cellules C4 et C5 sont fusionnées.
Pour vous assurer que les cellules fusionnées sont la raison pour laquelle vous obtenez l'erreur, cliquez sur lepanneau d'avertissement et vérifiez la cause - « La plage de déversement a fusionné la cellule ».
Solution:
Pour annuler la fusion des cellules, sélectionnez les cellules fusionnées, puis dans l'onglet « Accueil », cliquez sur le bouton « Fusionner et centrer » et sélectionnez « Annuler la fusion des cellules ».
Si vous avez du mal à localiser les cellules fusionnées dans votre grande feuille de calcul, cliquez sur l'option "Sélectionner les cellules obstruantes" dans le menu des panneaux d'avertissement pour accéder aux cellules fusionnées.
Plage de déversement dans le tableau
Les formules matricielles renversées ne sont pas prises en charge dans les tableaux Excel. La formule matricielle dynamique ne doit être saisie que dans une seule cellule individuelle. Si vous entrez une formule matricielle renversée dans une table ou lorsque la zone de renversement tombe dans une table, vous obtiendrez l'erreur de renversement. Lorsque cela se produit, essayez de convertir le tableau en une plage normale ou déplacez la formule en dehors du tableau.
Par exemple, lorsque nous saisissons la formule de plage renversée suivante dans un tableau Excel, nous obtiendrions une erreur de déversement dans chaque cellule du tableau, pas seulement dans la cellule de formule. C'est parce qu'Excel copie automatiquement toute formule entrée dans un tableau dans chaque cellule de la colonne du tableau.
En outre, vous obtiendrez une erreur de débordement lorsqu'une formule essaie de renverser les résultats dans un tableau. Dans la capture d'écran ci-dessous, la zone de déversement se trouve dans le tableau existant, nous obtenons donc une erreur de déversement.
Pour confirmer la cause de cette erreur, cliquez sur le signe d'avertissement et voyez la raison de l'erreur - « Plage de déversement dans le tableau »
Solution:
Pour corriger l'erreur, vous devrez rétablir le tableau Excel dans la plage. Pour ce faire, cliquez avec le bouton droit n'importe où dans le tableau, cliquez sur « Tableau », puis sélectionnez l'option « Convertir en plage ». Vous pouvez également cliquer avec le bouton gauche n'importe où dans le tableau, puis accéder à l'onglet « Conception du tableau » et sélectionner l'option « Convertir en plage ».
La portée du déversement est inconnue
Si Excel n'a pas pu établir la taille du tableau renversé, il déclenchera l'erreur de déversement. Parfois, la formule permet à un tableau dynamique de se redimensionner entre chaque passe de calcul. Si la taille du tableau dynamique continue de changer pendant les calculs et ne s'équilibre pas, cela provoquera le #SPILL! Erreur.
Ce type d'erreur de déversement est généralement déclenché lors de l'utilisation de fonctions volatiles telles que les fonctions RAND, RANDARRAY, RANDBETWEEN, OFFSET et INDIRECT.
Par exemple, lorsque nous utilisons la formule ci-dessous dans la cellule B3, nous obtenons l'erreur de déversement :
=SEQUENCE(RANDBETWEEN(1, 500))Dans l'exemple, la fonction RANDBETWEEN renvoie un entier aléatoire compris entre les nombres 1 et 500, et sa sortie change continuellement. Et la fonction SEQUENCE ne sait pas combien de valeurs produire dans un tableau de débordement. D'où l'erreur #SPILL.
Vous pouvez également confirmer la cause de l'erreur en cliquant sur le signe d'avertissement - « La plage de déversement est inconnue ».
Solution:
Pour corriger l'erreur de cette formule, votre seul choix est d'utiliser une formule différente pour votre calcul.
La plage de déversement est trop grande
Parfois, vous pouvez exécuter une formule qui génère une plage déversée trop grande pour que la feuille de calcul puisse la gérer, et elle peut s'étendre au-delà des bords de la feuille de calcul. Lorsque cela se produit, vous pouvez obtenir #SPILL ! Erreur. Pour résoudre ce problème, vous pouvez essayer de référencer une plage spécifique ou une cellule au lieu de colonnes entières ou d'utiliser le caractère '@' pour activer l'intersection implicite
Dans l'exemple ci-dessous, nous essayons de calculer 20 % des chiffres de vente dans la colonne A et de renvoyer les résultats dans la colonne B, mais à la place, nous obtenons une erreur de déversement.
La formule en B3 calcule 20 % de la valeur en A3, puis 20 % de la valeur en A4, et ainsi de suite. Il produit plus d'un million de résultats (1 048 576) et les renverse tous dans la colonne B à partir de la cellule B3, mais il atteindra la fin de la feuille de calcul. Il n'y a pas assez d'espace pour afficher toutes les sorties, par conséquent, nous obtenons une erreur #SPILL.
Comme vous pouvez le voir, la cause de cette erreur est que le - « La plage de déversement est trop grande ».
Solutions:
Pour résoudre ce problème, essayez de modifier la colonne entière avec une plage pertinente ou une référence à une seule cellule, ou ajoutez l'opérateur @ pour effectuer une intersection implicite.
Correction 1: vous pouvez essayer de faire référence à des plages plutôt qu'à des colonnes entières. Ici, nous changeons toute la plage A:A avec A3:A11 dans la formule, et la formule remplira automatiquement la plage avec les résultats.
Correction 2: Remplacez toute la colonne par la référence de cellule sur la même ligne (A3), puis copiez la formule dans la plage à l'aide de la poignée de recopie.
Correction 3: Vous pouvez également essayer d'ajouter l'opérateur @ avant la référence pour effectuer une intersection implicite. Cela affichera la sortie dans la cellule de formule uniquement.
Ensuite, copiez la formule de la cellule B3 dans le reste de la plage.
Noter: Lorsque vous modifiez une formule renversée, vous ne pouvez modifier que la première cellule de la zone/plage renversée. Vous pouvez voir la formule dans d'autres cellules de la plage de déversement, mais elles seront grisées et ne pourront pas être mises à jour.
Mémoire insuffisante
Si vous exécutez une formule matricielle renversée qui entraîne un manque de mémoire dans Excel, cela peut déclencher l'erreur #SPILL. Dans ces circonstances, essayez de référencer un tableau ou une plage plus petit.
Non reconnu / Repli
Vous pouvez également obtenir une erreur de déversement même lorsqu'Excel ne reconnaît pas ou ne peut pas réconcilier la cause de l'erreur. Dans de tels cas, vérifiez votre formule et assurez-vous que tous les paramètres des fonctions sont corrects.
Désormais, vous connaissez toutes les causes et solutions du #SPILL ! erreurs dans Excel 365.