Désactivation de #SPILL! Erreurs (Microsoft Excel)

L’entreprise de Jan est récemment passée à Office 365 ProPlus. Avec cela est venu un #SPILL! erreur lors de l’utilisation de VLOOKUP, qu’il utilise beaucoup. Il ne peut plus utiliser RECHERCHEV avec un filtre. Jan sait qu’il serait facile de trier et / ou de supprimer le # N / A qui provenait du précédent RECHERCHEV, mais cette erreur lui cause beaucoup de temps et d’efforts supplémentaires. Il se demande s’il existe un moyen de désactiver cette «fonctionnalité».

Dans un passé très proche, Microsoft a changé la façon dont il calcule les feuilles de calcul.

C’était un énorme changement, et vous en avez peut-être lu ailleurs. Voici un article qui fournit d’excellentes informations sur le changement:

L’article est assez long; vous voudrez réserver un peu de temps pour digérer les informations qu’il contient. Si vous créez beaucoup de formules dans Excel, vous voudrez le faire, cependant – la modification du programme signifie que vous devez le comprendre, éventuellement.

Essentiellement, Microsoft a supprimé le concept de fonctions de tableau (même si elles continueront de fonctionner), permettant à la place à presque toutes les fonctions, y compris VLOOKUP, de renvoyer un tableau de valeurs. Si le tableau des valeurs renvoyées ne rentre pas dans l’espace disponible, vous obtenez le nouveau #SPILL! Erreur.

Honnêtement, la réponse n’est pas de désactiver #SPILL! les erreurs; il n’y a vraiment aucun moyen de le faire. La réponse est de comprendre ce que fait maintenant Excel pendant qu’il calcule, puis de modifier vos formules en conséquence.

Regardons un exemple. Supposons que vous ayez une feuille de calcul qui répertorie les articles et leurs prix dans un simple ensemble de données à deux colonnes. Ensuite, à droite de cela, vous entrez certains articles et utilisez une fonction RECHERCHEV pour renvoyer les prix associés à chacun de ces articles. Lorsque vous ouvrez ce classeur dans une ancienne version d’Excel (2019 ou antérieure), vous obtenez d’excellents résultats. (Voir la figure 1.)

Figure 1. Une simple formule RECHERCHEV dans Excel 2010.

Cette capture d’écran a été prise à l’aide d’un système Excel 2010, mais elle fonctionnerait de la même manière si vous la regardiez dans Excel 2016 ou même Excel 2019. Notez dans cet exemple que la formule RECHERCHEV dans la cellule F2 (affichée dans la barre de formule car la cellule F2 est sélectionné) est copié dans la plage F2: F8. Vous obtenez les résultats souhaités car la fonction RECHERCHEV renvoie une valeur unique de la table.

Voyons maintenant ce qui se passe si vous créez le même classeur, en utilisant les mêmes formules, dans la version d’Excel fournie avec Office 365. Dans ce cas, vous verrez des erreurs. (Voir la figure 2.)

Figure 2. La même formule VLOOKUP simple dans la dernière version d’Excel.

Remarquez le #SPILL! les erreurs. Cette erreur se produit car la formule RECHERCHEV peut désormais renvoyer plus d’une valeur unique. En fait, lorsque vous utilisez une plage de cellules dans le tout premier paramètre de RECHERCHEV, il renverra désormais une valeur pour chaque cellule de cette plage. Ainsi, l’utilisation de la plage E2: E8 pour le premier paramètre signifie que RECHERCHEV renvoie 7 valeurs. En d’autres termes, il renvoie automatiquement un tableau de valeurs. Si ces valeurs ne peuvent pas toutes être affichées, vous obtenez le #SPILL! Erreur. C’est pourquoi vous voyez le #SPILL! erreur dans les cellules F2: F7; il y a des choses en dessous qui empêchent toutes les valeurs renvoyées dans ces formules d’être affichées. Vous ne voyez pas l’erreur dans la cellule F8 car rien en dessous de cette cellule n’arrête l’affichage.

Alors, comment résolvez-vous cela? Il existe en fait trois façons de résoudre ce problème.

Dans cet exemple particulier, le moyen le plus simple serait de simplement supprimer tout ce qui se trouve dans les cellules F3: F8. Cela permet à la formule de la cellule F2 de « déborder »

correctement au reste des cellules en dessous.

La deuxième approche serait de changer la formule dans la cellule F2, donc cela ressemble à ceci:

Notez l’utilisation du symbole @ juste avant le premier paramètre. Cela indique à Excel que vous souhaitez que la formule RECHERCHEV renvoie une seule valeur. L’autre façon d’ajuster la formule serait de la faire ressembler à ceci dans la cellule F2:

Vous pouvez maintenant copier l’une ou l’autre formule de la cellule F2 dans la plage complète de F2: F8 et vous n’aurez pas de problème. Pourquoi? Parce que (encore une fois) VLOOKUP dans ces instances ne renvoie qu’une seule valeur, pas un tableau de valeurs.

Le résultat est que la meilleure façon de changer vos formules est soit (1)

assurez-vous que rien ne bloque l’affichage du tableau complet de valeurs que vous demandez à RECHERCHEV de renvoyer ou (2) modifiez le premier paramètre afin qu’il ne fasse référence qu’à une seule cellule.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13750) s’applique à Microsoft Excel Excel dans Office 365.