Qu'est-ce que l'erreur de déversement? Comment résoudre le #spill! Erreur dans Excel 365

Comme nous le savons tous, Office 365 est livré avec Excel 365 groupé avec. Microsoft a ajouté diverses nouvelles fonctionnalités pour Excel 365. Une telle fonctionnalité est Formules de tableau dynamique. Habituellement, la formule ne renvoyait qu'une seule valeur en conséquence à une cellule. Mais maintenant, avec cette nouvelle fonctionnalité en place, plusieurs valeurs peuvent être renvoyées.

Par exemple, dans les versions Excel 2019 et antérieures, disons que vous appliquez la formule = D2: D5 sur la cellule, le résultat serait limité à la première cellule.

Lorsque nous avons dû appliquer la formule sur toutes les cellules correspondantes, nous avons utilisé la notation du tableau (Ctrl + Shift + Entrée). Cependant, dans Excel 365, ce n'est pas le cas. Lorsque vous appliquez la même formule, les valeurs sont automatiquement renversées sur toutes les cellules correspondantes. Reportez-vous à l'image ci-dessous, pour plus de détails.

La zone des cellules dans lesquelles le résultat est renversé est appelé Plage de déversement. Reportez-vous à l'image ci-dessous

NOTE:

• Le Répandre est automatiquement activé avec des tableaux dynamiques (actuellement cette fonction est prise en charge uniquement dans Excel 365) et la fonctionnalité ne peut pas être désactivée.
• La fonction de déversement est activée sur toutes les formules avec ou sans fonctions.

Erreurs de déversement sont vus lorsque la formule est destinée à renvoyer plusieurs valeurs, cependant, les résultats ne peuvent pas être mis sur les cellules. L'erreur ressemble à ce qui suit:

Les raisons possibles de rencontrer l'erreur #spill sont:

• La plage de déversement contient une certaine valeur car les résultats ne peuvent pas être remplis dans les cellules
• La plage de déversement a fusionné les cellules.
• Lorsque de vieilles feuilles (créées en utilisant Excel 2016 ou plus tôt) avec des formules prenant en charge l'intersection implicite sont ouvertes dans Excel365.
• Lorsque vous appliquez la formule de tableau dynamique sur une table Excel.

Si vous voyez l'erreur #spill dans Excel, alors ne vous inquiétez pas. Dans cet article, nous démontrerons différentes façons d'identifier la cause profonde de ce problème et de chercher des moyens de réparer l'erreur #spill

Table des matières

• Identifiez ce qui cause l'erreur #spill
• Correction à suivre lorsque la plage de déversement n'est pas vide
• Réparer à suivre lorsque la plage de déversement a fusionné les cellules
• Réparer à suivre lorsque la plage de déversement dans le tableau
• Correction à suivre lorsque la plage de déversement est hors de mémoire
• Réparer à suivre lorsque la plage de déversement est inconnue
• Correction à suivre lorsque la plage de déversement est trop grande

Identifiez ce qui cause l'erreur #spill

Lorsque vous voyez une erreur de déversement, vérifiez d'abord pourquoi vous voyez l'erreur, pour le faire,

Étape 1: cliquez sur la cellule qui montre #RÉPANDRE! erreur

Étape 2: cliquez sur le Point d'exclamation comme indiqué ci-dessous

Étape 3: La première ligne nous dit ce qui cause l'erreur. Par exemple, dans ce cas, l'erreur est considérée comme la plage de déversement n'est pas vide

Correction à suivre lorsque la plage de déversement n'est pas vide

Suivez les correctifs ci-dessous lorsque vous voyez que le La gamme de déversement n'est pas vide

Correction 1: Supprimer les données qui bloquent la plage de déversement

S'il y a déjà des données dans les cellules de la plage de déversement, vous verrez une erreur #spill sur l'application de la formule.

Lorsque vous pouvez clairement voir les données qui bloquent la plage de déversement

Considérez l'exemple ci-dessous, lorsque vous appliquez la formule = d2: d5 sur les données, une erreur de déversement est lancée comme il y a Je suis là Dans la plage de déversements.

Afin de se débarrasser de l'erreur #spill, déplacez simplement les données ou supprimez les données de la plage de déversement.

Lorsque les données bloquant la plage de déversement sont cachées

Dans certains cas, les données qui bloquent la plage de déversement sont cachées et pas très évidentes comme le montre le cas 1. Considérez l'exemple ci-dessous,

Dans de tels cas, pour trouver la cellule qui bloque la plage de déversement, suivez les étapes ci-dessous:

Étape 1: cliquez sur la cellule qui montre #RÉPANDRE! erreur

Étape 2: cliquez sur le Point d'exclamation Comme indiqué ci-dessous, vous pouvez voir que l'erreur est due au La gamme de déversement n'est pas vide.

Étape 3: Dans la liste déroulante, cliquez sur Sélectionnez des cellules obstruées

Étape 4: La cellule qui bloque la plage de déversement est mise en évidence comme indiqué ci-dessous

Maintenant, que vous savez quelle cellule bloque, vérifiez ce qui entraîne exactement le problème.

Étape 5: Lors d'un examen attentif de la cellule, vous pouvez voir certaines données cachées dans les cellules.

Comme le montre l'image ci-dessus, il y a des données. Puisque la police a une couleur blanche, il n'est pas facile de reconnaître le blocage. Afin de vous débarrasser de l'erreur, supprimez les données de la cellule dans la plage de déversement.

Réparer 2: Supprimer le formatage du numéro personnalisé; ; ; appliqué sur la cellule

Parfois, lorsqu'un numéro personnalisé est en train de formater ; ; ; est appliqué sur une cellule, il y a des chances de voir l'erreur de déversement. Dans ces cas,

Étape 1: cliquez sur la cellule qui montre #RÉPANDRE! erreur

Étape 2: cliquez sur le Point d'exclamation comme indiqué ci-dessous.

Étape 3: Dans la liste déroulante, cliquez sur Sélectionnez des cellules obstruées

Étape 4: La cellule qui bloque la plage de déversement est mise en évidence comme indiqué ci-dessous

Étape 5: cliquez avec le bouton droit sur la cellule obstructrice.

Étape 6: Choisissez Cellules de format

Étape 7: La fenêtre des cellules du format s'ouvre. Aller au Nombre languette

Étape 8: Dans le volet gauche, sélectionnez Coutume

Étape 9: Dans le volet côté droit, modifiez le type de; ; ; pour Général

Étape 10: cliquez sur le D'accord bouton

Réparer à suivre lorsque la plage de déversement a fusionné les cellules

Si vous voyez que l'erreur est parce que le Spill Range a fusionné les cellules comme indiqué ci-dessous,

Étape 1: cliquez sur Sélectionnez des cellules obstruées de la liste déroulante

Étape 2: la cellule obstructive sera Souligné

Étape 3: sous le Maison Onglet, cliquez sur Fusion et centre

Étape 4: Dans la liste déroulante, sélectionnez Défendez-vous

Réparer à suivre quand Range de déversement dans la table

Les formules de tableau dynamique ne sont pas prises en charge dans les tables Excel. Si vous voyez l'erreur #spill sur une table Excel comme indiqué ci-dessous avec le message Range de déversement dans le tableau,

Étape 1: sélectionnez complètement le tableau

Étape 2: cliquez sur le Design de table Onglet de la barre de menu supérieure

Étape 3: Choisissez Convertir en gamme

Étape 4: Vous verrez une boîte de dialogue de confirmation apparaître, cliquez sur Oui

Correction à suivre lorsque la plage de déversement est hors de mémoire

Lorsque vous essayez d'identifier la cause de l'erreur #spill, si vous voyez que l'erreur indique Mémoire insuffisante, Ensuite, c'est parce que la formule de tableau dynamique que vous utilisez fait référence à une large gamme, dans de tels cas, Excel fonctionne Mémoire insuffisante provoquant une erreur de déversement. Pour surmonter l'erreur, on peut essayer de faire référence à une gamme plus petite.

Réparer à suivre lorsque la plage de déversement est inconnue

Cette erreur est observée lorsque la taille du tableau renversé change et Excel n'est pas en mesure d'établir la taille de la plage renversée. Généralement, lorsque vous utilisez des fonctions aléatoires comme Randarray, Rand ou Randbetween avec des fonctions de tableau dynamique telles que la séquence, cette erreur est vue.

Pour mieux le comprendre, considérons l'exemple ci-dessous, disons que la séquence de fonction (Randbetween (1 100)) est utilisée. Ici, Randbetween génère un entier aléatoire qui est supérieur ou égal à 1 et inférieur ou égal à 100. Et la séquence génère des nombres séquentiels (par exemple (5) génère 1,2,3,4,5). Cependant, Randbetween est une fonction volatile et continue de changer sa valeur à chaque fois qu'une feuille Excel est ouverte ou modifiée. Parce que, de cela, la fonction de séquence ne sera pas en mesure de déterminer la taille du tableau qu'il doit générer. Il ne saurait pas combien de valeurs à générer et lance ainsi une erreur de déversement.

Lorsque vous identifiez la cause de l'erreur, vous voyez La gamme de déversement est inconnue

Afin de corriger ce type d'erreur, Essayez d'avoir une formule différente qui convient à votre besoin.

Correction à suivre lorsque la plage de déversement est trop grande

Disons que vous identifiez la cause et que vous remarquez que l'erreur est vue parce que le La gamme de déversement est trop grande comme indiqué ci-dessous.

Lorsque le tableau dynamique n'était pas en place, il y avait quelque chose appelé l'intersection implicite dans Excel qui a forcé de retourner un seul résultat même lorsque la formule avait un potentiel de renvoyer plusieurs résultats. Considérez un exemple, si la formule = B: b * 5% est appliqué sur les versions Excel 2019 ou antérieures, avec l'intersection implicite en place, le résultat serait le suivant:

Cependant, lorsque la même formule est utilisée dans Excel 365, vous voyez l'erreur suivante

Afin de résoudre ce problème, essayez les correctifs suivants

Correction 1: Appliquer l'intersection implicite à l'aide de l'opérateur @

Quand on dit = B: b, Le tableau dynamique référenra l'ensemble de la colonne B. Au lieu de cela, nous pouvons forcer Excel à imposer l'intersection implicite à l'aide de l'opérateur @

Changer la formule en = @ B: b * 5%

Parce que l'intersection implicite est ajoutée, la formule sera appliquée à une seule cellule. Afin d'étendre la formule,

1. Cliquez simplement sur le point comme indiqué ci-dessous

2. Faites-le glisser sur les cellules au besoin. Cela appliquera la même formule à ces cellules.

Correction 2: Au lieu de faire référence à la colonne, reportez-vous à la plage

Dans la formule, = B: b * 5% , Nous faisons référence à la colonne B. Référez-vous à une gamme particulière, = B2: b4 * 5%

C'est tout

Nous espérons que cet article a été informatif.

Veuillez aimer et commenter si vous avez pu résoudre le problème avec les méthodes ci-dessus.

Merci pour la lecture.