Comment éviter les formules incorrectes

Comment éviter les formules incorrectes

Si Excel ne peut pas résoudre une formule que vous tentez de créer, vous pouvez recevoir un message d’erreur comme celui-ci :

Image de la boîte de dialogue « Problème avec cette formule » d’Excel

Malheureusement, cela signifie qu’Excel ne peut pas comprendre ce que vous essayez de faire. Vous voudrez peut-être recommencer depuis le début.

Commencez par sélectionner OK ou appuyez sur Échap pour fermer le message d’erreur.

Vous revenirez à la cellule avec la formule insurbable (qui sera en mode Édition) et Excel surlignera l’emplacement où il a un problème. Si vous ne savez toujours pas quoi faire et souhaitez recommencer depuis le début, appuyez de nouveau sur Échap ou sélectionnez le bouton Annuler dans la barre de formule, ce qui vous permet de sortir du mode Édition.

Image du bouton Annuler dans la barre de formule

Si vous voulez avancer, la liste de contrôle suivante fournit des étapes de dépannage pour vous aider à déterminer ce qui n’a pas pu se passer.

Remarque : Si vous utilisez unOffice pour le web, il est possible que vous ne voyez pas les mêmes erreurs ou que les solutions ne s’appliquent pas.

Excel envoie plusieurs erreurs (#), telles que #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? et #NULL!, pour indiquer qu’un problème dans votre formule ne fonctionne pas correctement. Par exemple, l'#VALUE! est provoquée par une mise en forme incorrecte ou des types de données non pris en place dans les arguments. Vous verrez également le #REF ! si une formule fait référence à des cellules qui ont été supprimées ou remplacées par d’autres données. Les conseils de dépannage diffèrent pour chaque erreur.

Remarque : #### n’indique pas une erreur liée à une formule. Cela signifie simplement que la colonne n’est pas suffisamment large pour afficher l’ensemble du contenu de la cellule. Faites simplement glisser la colonne pour l’agrandir, ou accédez à Accueil > Format > Ajuster la largeur de colonne.

Image de Accueil > Format > Ajustement automatique de largeur de colonne

Reportez-vous à l’une des rubriques suivantes correspondant à l’erreur de livre que vous voyez :

Chaque fois que vous ouvrez une feuille de calcul contenant des formules faisant référence à des valeurs d’autres feuilles de calcul, vous êtes invité à mettre à jour les références ou à les laisser telles quel.

Boîte de dialogue signalant des liens rompus dans Excel

Excel affiche la boîte de dialogue ci-dessus pour vous assurer que les formules de la feuille de calcul actuelle pointent toujours vers la valeur la plus mise à jour au cas où la valeur référencé avait changé. Vous pouvez choisir de mettre à jour les références ou d’ignorer cette étape si vous n’avez pas envie de procéder à la mise à jour. Même si vous choisissez de ne pas mettre à jour les références, vous pouvez toujours choisir de mettre à jour les liens de la feuille de calcul plus tard.

Vous pouvez également choisir de désactiver l’affichage de la boîte de dialogue au démarrage. Pour ce faire, dans Options > fichiers et> options avancées > général, puis videz la zone Demander à mettre à jour les liens automatiques.

Image de l’option Confirmer la mise à jour automatique des liens

Important : Si c’est la première fois que vous travaillez avec des liens rompus dans les formules, si vous avez besoin d’une actualisation sur la résolution des liens rompus ou si vous ne savez pas s’il faut mettre à jour les références, voir Contrôler la mise à jour des références externes (liens).

Si la formule n’affiche pas la valeur, procédez comme suit :

  • Assurez-vous qu’Excel est prêt à afficher les formules dans votre feuille de calcul. Pour ce faire, sélectionnez l’onglet Formules, puis dans le groupe Audit de formules, sélectionnez Afficher les formules.

    Conseil : Vous pouvez également utiliser le raccourci clavier Ctrl+' (la touche au-dessus de la touche Tab). Lorsque vous effectuez cette procédure, vos colonnes s’élargissent automatiquement de manière à afficher vos formules, mais ne vous inquiétez pas, lorsque vous basculez à nouveau vers l’affichage normal, vos colonnes sont re taillenées.

  • Si l’étape ci-dessus ne résout toujours pas le problème, il est possible que la cellule soit mise en forme en tant que texte. Vous pouvez cliquer avec le bouton droit sur la cellule et sélectionner Format de cellule > Standard (ou Ctrl+1), puis appuyer sur F2 > Entrée pour modifier le format.

  • Si vous avez une colonne avec une grande plage de cellules qui sont formatées en tant que texte, vous pouvez sélectionner la plage, appliquer le format numérique de votre choix, puis aller à Données > De texte à colonne > Terminer. Cela permet d’appliquer la mise en forme à toutes les cellules sélectionnées.

    Image de la boîte de dialogue Données > Convertir

Lorsqu’une formule n’est pas calculée, vous devez vérifier si le calcul automatique est activé dans Excel. Les formules ne sont pas calculer si le calcul manuel est activé. Suivez ces étapes pour vérifier si le calcul automatique est effectué.

  1. Sélectionnez l’onglet Fichier, Sélectionnez Options,puis la catégorie Formules.

  2. Dans la section Mode de calcul, sous Calcul du classeur, vérifiez que l’option Automatique est sélectionnée.

    Image des options Calcul automatique et Calcul manuel

Pour plus d’informations sur les calculs, voir Modifier le recalcul de la formule, l’itération ou la précision.

Une référence circulaire se produit lorsqu’une formule fait référence à la cellule dans qui elle se trouve. Pour résoudre ce problème, vous pouvez soit déplacer la formule vers une autre cellule, soit modifier la syntaxe de la formule afin d’éviter les références circulaires. Toutefois, dans certains scénarios, vous aurez besoin de références circulaires, car elles permettent à vos fonctions d’itérer (se répéter) tant qu’une condition précise n’est pas remplie. Dans ce cas, vous devez activer la suppression ou l’ouverture d’une référence circulaire.

Pour plus d’informations sur les références circulaires, voir Supprimer ou autoriser une référence circulaire.

Si votre entrée ne commence pas par un signe égal, il ne s’agit pas d’une formule et elle ne sera pas calculée. Il s’agit d’une erreur classique.

Par exemple, lorsque vous tapez SOMME(A1:A10), Excel affiche la chaîne de texte SOMME(A1:A10) au lieu d’un résultat de formule. Si vous tapez 2/11,Excel affiche une date (par exemple, 11-nov ou 11/02/2009) au lieu de diviser 11 par 2.

Pour éviter ces résultats inattendus, faites toujours précéder la fonction d’un signe égal. Par exemple, tapez : =SOMME(A1:A10) et =11/2.

Lorsque vous utilisez une fonction dans une formule, chaque parenthèse ouvrante doit avoir une parenthèse fermante pour que la fonction s’exécute correctement. Vérifiez si toutes les parenthèses font effectivement partie d’une paire. Par exemple, la formule =SI(B5<0);"Non valide »;B5*1,05) ne fonctionne pas, car elle contient deux parenthèses fermants, mais une seule parenthèse ouvrante. La formule correcte se présente comme suit : =SI(B5<0;"Non valide";B5*1,05).

Les fonctions Excel utilisent des arguments dont vous devez spécifier la valeur pour qu’elles opèrent. Seules quelques fonctions (telles que PI ou AUJOURDHUI) ne prennent aucun argument. Vérifiez la syntaxe de la formule qui s’affiche lorsque vous commencez à saisir la fonction pour vous assurer qu’elle contient les arguments requis.

Par exemple, la fonction MAJUSCULE n’accepte qu’une seule chaîne de texte ou référence de cellule comme argument : =MAJUSCULE("bonjour") ou =MAJUSCULE(C2)

Remarque : Les arguments de la fonction sont répertoriés dans une barre d’outils flottante de référence de fonction sous la formule lorsque vous la tapez.

Capture d’écran de la barre d’outils des références aux fonctions

En outre, certaines fonctions, telles que SOMME, requièrent des arguments uniquement numériques, tandis que d’autres, telles que REMPLACER, requièrent une valeur de texte pour au moins l’un de leurs arguments. Si vous utilisez un type de données erroné, les fonctions peuvent renvoyer des résultats inattendus ou afficher une erreur #VALUE!.

Si vous avez besoin rechercher rapidement la syntaxe d’une fonction particulière, consultez la liste des fonctions Excel (par catégorie).

N’entrez pas de nombres au format dollar ($) ni de séparateurs décimaux (,) dans les formules, car les signes dollar indiquent les références absolues et les virgules sont les séparateurs d’arguments. Au lieu d’entrer 1 000 $,entrez 1 000 dans la formule.

Si vous utilisez des nombres formatés dans des arguments, vous obtenez des résultats de calcul inattendus, mais le message d’erreur #NUM! Par exemple, si vous entrez la formule =ABS(-2,134) pour trouver la valeur absolue de -2134, Excel affiche la #NUM! car la fonction ABS n’accepte qu’un seul argument et considère les -2 et 134 comme des arguments distincts.

Remarque : Vous pouvez formater le résultat de la formule avec des séparateurs de décimale et des symboles monétaires après avoir entré la formule à l’aide de nombres nonformés (constantes). Il n’est généralement pas bon de placer des constantes dans les formules, car elles peuvent être difficiles à trouver si vous avez besoin d’une mise à jour ultérieurement et qu’elles sont plus sujettes à des erreurs de type. Il est préférable de placer vos constantes dans des cellules, où elles sont ouvertes et facilement référencés.

Votre formule peut ne pas renvoyer les résultats escomptés si le type de données de la cellule ne peut pas être utilisé dans des calculs. Par exemple, si vous entrez une formule simple =2+3 dans une cellule au format texte, Excel ne peut pas calculer les données que vous avez entrées. Tout ce qui apparaîtra dans la cellule est =2+3. Pour résoudre ce problème, remplacez le type de données Texte de la cellule par Général, comme ceci :

  1. Sélectionnez la cellule.

  2. Sélectionnez Accueil, puis sélectionnez la flèche pour développer le groupe Format de nombre ou Format de nombre (ou appuyez sur Ctrl+1). Sélectionnez ensuite Général.

  3. Appuyez sur F2 pour mettre la cellule en mode d’édition, puis appuyez sur Entrée pour accepter la formule.

Si vous entrez une date dans une cellule qui utilise le type de données Nombre, elle risque d’apparaître sous forme d’une valeur de date numérique au lieu d’une date. Pour afficher un nombre sous forme d’une date, sélectionnez le format Date dans la galerie Format de nombre.

Il est assez courant d’utiliser x comme opérateur de multiplication dans une formule, mais Excel n’accepte que l’astérisque (*). Si vous utilisez des constantes dans votre formule, Excel affiche un message d’erreur, et peut corriger la formule automatiquement pour vous en remplaçant le x par un astérisque (*).

Message invitant à remplacer x par * dans une multiplication

Toutefois, si vous utilisez des références de cellule, Excel retourne un #NAME ? comme erreur.

Erreur #NOM? lors de l’utilisation de x au lieu de * avec des références de cellules dans le cas d’une multiplication

Si vous créez une formule qui contient du texte, placez le texte entre guillemets.

Par exemple, la formule ="Nous sommes aujourd’hui le " & TEXTE(AUJOURDHUI(),"jjjj, mmmm jj") associe le texte « Nous sommes aujourd’hui le » aux résultats des fonctions TEXTE et AUJOURDHUI, et renvoie quelque chose ressemblant à Nous sommes aujourd’hui le lundi 30 mai.

Dans la formule, « Nous avons aujourd’hui le " contient un espace avant les guillemets de fin afin de fournir l’espace vide voulu entre « Nous le pouvons » et « Lundi 30 mai ». Si le texte ne contient pas de guillemets, la formule risque de présenter l'#NAME ?.

Vous pouvez combiner (ou imbriquer) jusqu’à 64 niveaux de fonctions à l’intérieur d’une formule.

Par exemple, la formule =SI(PI())<2;"Moins de deux ! »;"Plus de deux ! ») possède 3 niveaux de fonctions ; la fonction PI est imbrique dans la fonction OUQRT,qui est à son tour imbrique dans la fonction SI.

Lorsque vous tapez une référence à des valeurs ou des cellules d’une autre feuille de calcul et que le nom de cette feuille contient un caractère non alphabétique (par exemple, un espace), placez une apostrophe (') de part et d’autre du nom.

Par exemple, pour renvoyer la valeur de la cellule D3 dans une feuille de calcul nommée Données trimestrielles de votre feuille de calcul, tapez : ='Données trimestrielles'! D3. Sans les guillemets autour du nom de la feuille, la formule affiche l'#NAME ?.

Vous pouvez également sélectionner les valeurs ou cellules d’une autre feuille pour y faire référence dans votre formule. Excel place automatiquement les noms de feuilles entre des guillemets.

Lorsque vous tapez une référence à des valeurs ou des cellules dans un autre classeur, placez le nom du classeur entre des crochets ([]), suivi du nom de la feuille de calcul contenant les valeurs ou cellules.

Par exemple, pour faire référence aux cellules A1 à A8 de la feuille Ventes du groupe Opérations T2 ouvert dans Excel, tapez : =[T2 Operations.xlsx]Ventes! A1:A8. Sans les crochets, la formule affiche le #REF!.

Si le classeur n’est pas ouvert dans Excel, tapez le chemin d’accès complet au fichier.

Par exemple, =LIGNES('C:\Mes documents\[Operations T2.xlsx T2]Ventes'!A1:A8).

Remarque : Si le chemin d’accès complet contient des espaces, utilisez des guillemets simples (au début du chemin d’accès et après le nom de la feuille de calcul, avant le point d’exclamation).

Conseil : Le moyen le plus simple d’obtenir le chemin d’accès vers l’autre workbook consiste à ouvrir l’autre workbook, puis à partir de votre workbook d’origine, à taper =, puis à utiliser Alt+Tab pour passer à l’autre workbook. Sélectionnez la cellule de votre choix dans la feuille, puis fermez le feuille de travail source. Votre formule est automatiquement mise à jour pour afficher le chemin d’accès complet du fichier et le nom de la feuille ainsi que la syntaxe requise. Vous pouvez même copier et coller le chemin d’accès et l’utiliser où vous le souhaitez.

Si vous divisez une cellule par une autre qui a zéro (0) ou aucune valeur, une erreur #DIV/0!.

Pour éviter cette erreur, vous pouvez entrer votre formule directement et tester l’existence d’un dénominateur, comme suit : Vous pouvez utiliser : 

=SI(B1,A1/B1,0)

Cela signifie SI(B1 existe, diviser A1 par B1, sinon renvoyer 0).

Vérifiez toujours si vous avez des formules qui font référence à des données dans des cellules, des plages, des noms définis, des feuilles de calcul ou des feuilles de calcul avant de supprimer quoi que ce soit. Vous pouvez ensuite remplacer ces formules par leurs résultats avant de supprimer les données référencées.

Si vous ne pouvez pas remplacer les formules par leurs résultats, consultez les informations suivantes sur les erreurs et solutions possibles :

  • Si une formule fait référence à des cellules qui ont été supprimées ou remplacées par d’autres données, et si elle renvoie une erreur #REF!, sélectionnez la cellule avec la #REF! comme erreur. Dans la barre de formule, sélectionnez #REF! et supprimez-le. Entrez à nouveau la plage de la formule.

  • Si un nom défini est manquant et qu’une formule qui fait référence à ce nom renvoie une #NAME ?, définissez un nouveau nom qui fait référence à la plage de votre choix, ou modifiez la formule pour qu’elle référence directement la plage de cellules (par exemple, A2:D8).

  • Si une feuille de calcul est manquante et qu’une formule qui y fait référence renvoie une #REF! car il n’existe aucun moyen de résoudre ce problème, malheureusement. Une feuille de calcul qui a été supprimée n’est pas récupérable.

  • Si un classeur est manquant, toute formule y faisant référence reste inchangée jusqu’à ce que vous mettiez à jour la formule.

    Par exemple, si votre formule est =[Classeur1.xlsx]Feuil1'!A1 et que Classeur1.xlsx n’existe plus, les valeurs référencées dans ce classeur restent disponibles. Toutefois, si vous modifiez et enregistrez une formule qui fait référence à ce classeur, Excel affiche la boîte de dialogue Mettre à jour les valeurs et vous invite à entrer un nom de fichier. SélectionnezAnnuler, puis assurez-vous que ces données ne sont pas perdues en remplaçant les formules qui font référence au workbook manquant par les résultats de la formule.

Parfois, lorsque vous copiez le contenu d’une cellule, vous souhaitez coller uniquement la valeur et non la formule sous-jacente affichée dans la barre de formule.

Par exemple, il se peut que vous vouliez copier la valeur résultant d’une formule vers une cellule figurant dans une autre feuille de calcul. Ou peut-être voulez-vous supprimer les valeurs que vous avez utilisées dans une formule après avoir copié la valeur qui en résulte vers une autre cellule figurant dans la même feuille de calcul. Ces deux actions ont pour effet qu’une erreur de référence de cellule non valide (#REF!) à apparaître dans la cellule de destination, car les cellules contenant les valeurs que vous avez utilisées dans la formule ne peuvent plus être référencés.

Vous pouvez éviter cette erreur en y pastant les valeurs résultant des formules, sans la formule, dans les cellules de destination.

  1. Dans une feuille de calcul, sélectionnez les cellules contenant les valeurs résultant d’une formule, que vous voulez copier.

  2. Sous l’onglet Accueil, dans le groupe Presse-papiers,sélectionnez Copier Image du bouton .

    Image du ruban Excel

    Raccourci clavier : appuyez sur Ctrl+C.

  3. Sélectionnez la cellule supérieure gauche de la zone de collage.

    Conseil : Pour déplacer ou copier une sélection vers une autre feuille de calcul ou un autre classez, sélectionnez un autre onglet ou basculez vers un autre classez, puis sélectionnez la cellule supérieure gauche de la zone de coller.

  4. Dans l’onglet Accueil, dans le groupe Presse-papiers, sélectionnez Coller des Image du bouton , puis Coller des valeurs, ou appuyez sur Alt > E > S > V > Entrée pour Windows ou Option > Commande > V > V > Entrée sur un Mac.

Pour comprendre la manière dont une formule complexe ou imbriquée calcule le résultat final, vous pouvez évaluer cette formule.

  1. Sélectionnez la formule à évaluer.

  2. Sélectionnez Formules > Évaluer la formule.

    Groupe Audit de formules sous l’onglet Formules

  3. Sélectionnez Évaluer pour examiner la valeur de la référence soulignée. Le résultat de l’évaluation apparaît en italique.

    Boîte de dialogue Évaluer la formule

  4. Si la partie soulignée de la formule est une référence à une autre formule, sélectionnez Pas à pas pour afficher l’autre formule dans la zone Évaluation. Sélectionnez Pas à pas sortant pour revenir à la cellule et la formule précédentes.

    Le bouton Pas à pas avant n’est pas disponible la deuxième fois que la référence s’affiche dans la formule, ou si la formule fait référence à une cellule d’un autre document.

  5. Poursuivez jusqu’à ce que toutes les parties de la formule soient évaluées.

    L’outil Évaluer la formule ne vous indiquera pas nécessairement la raison pour laquelle votre formule est rompue, mais il peut vous aider à déterminer où. Cela peut être un outil très utile dans les grandes formules dans lesquelles il pourrait être autrement difficile de détecter le problème.

    Remarques : 

    • Certaines parties des fonctions SI et CHOISIR ne sont pas évaluées et l’erreur #N/A peut apparaître dans la zone Évaluation.

    • Les références vides apparaissent sous la forme de valeurs zéro (0) dans la zone Évaluation.

    • Certaines fonctions sont recalculées chaque fois que la feuille de calcul change. En raison de celles-ci (notamment les fonctions ALEA, ZONES, INDEX, DECALER, CELLULE, INDIRECT, LIGNES, COLONNES, MAINTENANT, AUJOURD’HUI et ALEA.ENTRE.BORNES), la boîte de dialogue Évaluer la formule peut afficher des résultats différents des résultats réels dans la cellule de la feuille de calcul.

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel ou obtenir une assistance dans la communauté Answers.

Voir aussi

Vue d’ensemble des formules dans Excel

Détecter les erreurs dans les formules

Fonctions Excel (par ordre alphabétique)

Fonctions Excel (par catégorie)

Besoin d’aide ?

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

×