Dans les versions récentes d'Excel, vous avez peut-être rencontré le vexant #SPILL ! erreur lors de l'utilisation de formules. Ne t'inquiète pas; tu n'es pas seul. Ce guide complet vous expliquera ce que le #SPILL ! L'erreur est la raison pour laquelle elle se produit et propose des solutions pratiques pour la résoudre.

Qu’est-ce que #SPILL ! erreur?
Les causes du #SPILL! erreur et les solutions
La plage de déversement n’est pas vide
La plage de déversement a une cellule de fusion
Plage de déversement dans le tableau
La plage de déversement est trop grande
La portée du déversement est inconnue
Les autres causes

Vidéo : #SPILL ! Erreur

Qu’est-ce que #SPILL ! erreur?

Le #SPILL! L'erreur est un problème courant qui survient à partir des versions Excel 365 et 2021.

Le #SPILL! L'erreur ne se produit pas seulement avec les formules matricielles, mais avec toute formule qui renvoie plusieurs résultats et rencontre un obstacle dans sa plage de sortie.

De manière générale, le #SPILL! une erreur se produit généralement lorsque la plage de débordement contient des cellules non vides. Effacer les données de ces cellules non vides résoudra souvent le problème et permettra à la formule de fonctionner correctement. Cependant, outre cette cause commune, il existe d’autres facteurs qui peuvent conduire à cette erreur. Approfondissons les raisons du #SPILL ! erreur et explorez les solutions correspondantes.

Les causes du #SPILL! erreur et les solutions

Décomposons les différentes causes du #SPILL ! erreur et fournissez des solutions étape par étape pour chaque scénario.

La plage de déversement n’est pas vide

Cause: Si la plage de débordement dans laquelle Excel a l'intention de placer les résultats n'est pas vide, cela peut conduire au #SPILL ! erreur.

Mise en situation : Imaginez que vous ayez une formule simple comme =A2:A5. Si une cellule de la plage cible (par exemple, C4) est occupée, vous rencontrerez l'erreur.

Solution: Assurez-vous que chaque cellule de la plage de déversement est vide. Dans l'exemple, sélectionnez la cellule C4 et appuyez sur Supprimer touche pour l'effacer.

Résultat:

La plage de déversement a une cellule de fusion

Cause: Si la plage de débordement dans laquelle Excel a l'intention de placer les résultats contient des cellules de fusion, cela peut conduire au #SPILL ! erreur.

Mise en situation : Imaginez que vous ayez une formule simple comme =A2:A5. Si la plage cible contient des cellules de fusion (par exemple, les cellules C3 et C4 sont fusionnées), vous rencontrerez l'erreur.

Solution: Annulez la fusion de toutes les cellules fusionnées dans la plage cible avant d'appliquer la formule. Dans l'exemple, sélectionnez les cellules de fusion (C3:C4) et cliquez sur Accueil > Fusionner et centrer pour annuler la fusion des cellules.

Résultat:

Plage de déversement dans le tableau

Cause: Si la plage de déversement se trouve à l'intérieur d'un tableau Excel, cela peut conduire au #SPILL ! erreur.

Mise en situation : Vous disposez d'un tableau Excel (A1:B6) et vous souhaitez trier les données de la colonne A et renverser le résultat dans la colonne B, vous rencontrerez l'erreur.

Solutions:

1. Renversez les résultats en dehors du tableau Excel.

   

2. Convertissez le tableau en plage.

   Sélectionnez n'importe quelle cellule du tableau, cliquez sur Table Design > Convertir en plage.

   

Résultat:

La plage de déversement est trop grande

Cause: Lorsque la formule génère plus de résultats que prévu, elle peut dépasser l'espace disponible.

Mise en situation : Pour utiliser une formule RECHERCHEV =VLOOKUP(A:A,A:C,2,FALSE) pour renvoyer toutes les valeurs de la colonne B de la plage A:C en faisant correspondre la première colonne, vous rencontrerez l'erreur.

Explication: La formule déclenche un #SPILL ! erreur car Excel tente de récupérer les données de la colonne entière A: A, ce qui entraîne un nombre stupéfiant de 1,048,576 XNUMX XNUMX résultats, qui dépasse les limites de la capacité de la grille Excel.

Solutions:

1. Faites référence à une plage spécifique plutôt qu'à une colonne entière.

   Dans cet exemple, en utilisant la formule ci-dessous :

   =VLOOKUP(A2:A6,A:C,2,FALSE)

   

2. Calculez une seule cellule et copiez la formule.

   Dans cet exemple, utilisez la formule ci-dessous pour extraire le premier nom de famille :

   =VLOOKUP(A2,A:C,2,FALSE) 

   Faites ensuite glisser la poignée de remplissage automatique vers le bas pour copier la formule et obtenir d'autres résultats.

   

3. Utilisation de l'opérateur @ pour appliquer une intersection implicite.

   Dans cet exemple, en utilisant la formule ci-dessous :

   =VLOOKUP(@A:A,A:C,2,FALSE)

   Faites ensuite glisser la poignée de remplissage automatique vers le bas pour copier la formule et obtenir d'autres résultats.

   

La portée du déversement est inconnue

Cause: Excel ne sait pas combien de cellules la formule doit occuper.

Mise en situation : Lorsque vous utilisez une formule telle que =SEQUENCE(RANDBETWEEN(1,1000)) pour générer une séquence, où la longueur de la séquence dépend du résultat de la formule RANDBETWEEN(1,1000), vous pouvez rencontrer l'erreur #SPILL#.

Explication: Dans ce cas précis, la formule =SEQUENCE(RANDBETWEEN(1,1000)) est utilisée pour générer une séquence de nombres. Cependant, la longueur de cette séquence dépend du résultat de la formule RANDBETWEEN(1,1000), qui génère un nombre aléatoire compris entre 1 et 1000. Le résultat de RANDBETWEEN étant dynamique et imprévisible, Excel ne peut pas prédéterminer le nombre exact de séquences. cellules nécessaires pour afficher la séquence. En conséquence, la formule renvoie l'erreur #SPILL#.

Solutions: Aucun.

Les autres causes

Outre les scénarios ci-dessus, Microsoft a identifié deux causes potentielles supplémentaires pour le #SPILL ! erreur:

• Mémoire insuffisante

  Cause: Si Excel manque de mémoire en raison de calculs approfondis.

  Solution: Réduisez la taille des données ou simplifiez vos calculs.

• Méconnu

  Cause: Lorsqu'Excel ne parvient pas à identifier une raison spécifique pour le #SPILL ! erreur.

  Solution: Vérifiez votre formule, recherchez les fautes de frappe ou demandez de l'aide à la communauté Excel.

Le #SPILL! L'erreur peut être un obstacle difficile à surmonter, mais armé de ces informations et solutions, vous pouvez naviguer en toute confiance dans les dernières versions d'Excel et vous attaquer de front à cette erreur. N'oubliez pas que la pratique rend parfait, et bientôt, vous serez un #SPILL ! outil de dépannage d'erreurs extraordinaire. Joyeux Excel-ing !

Pour d’autres stratégies Excel révolutionnaires qui peuvent améliorer la gestion de vos données, explorez plus en détail ici..