Comment éviter les formules incorrectes

Les formules comportant plusieurs arguments utilisent des séparateurs de liste pour séparer leurs arguments. Le séparateur utilisé peut varier en fonction des paramètres régionaux du système d’exploitation et d’Excel. Les séparateurs de liste les plus courants sont les virgules « , » et le point-virgule « ; ».

Une formule s’arrête si l’une de ses fonctions utilise des délimiteurs incorrects.

Pour plus d’informations, consultez : erreurs de formule lorsque le séparateur de liste n’est pas défini correctement 

Excel génère diverses erreurs dièse (#) telles que #CNUM!, #REF!, #NUM, #N/A, #DIV/0!, #NOM?, et #NUL!, pour indiquer que quelque chose dans votre formule ne fonctionne pas correctement. Par exemple, le #CNUM! l’erreur est due à une mise en forme incorrecte ou à des types de données non pris en charge dans les arguments. Vous verrez également le #REF ! erreur si une formule fait référence à des cellules qui ont été supprimées ou remplacées par d’autres données. Les instructions de résolution des problèmes diffèrent pour chaque erreur.

Reportez-vous à l’une des rubriques suivantes correspondant à l’erreur dièse que vous voyez :

• Corriger une erreur #NOMBRE! erreur

• Corriger une erreur #VALEUR! erreur

• Corriger une erreur #N/A

• Corriger une erreur #DIV/0! erreur

• Corriger une erreur #REF! erreur

• Corriger une erreur #NOM! erreur

• Corriger une erreur #NULL! erreur

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

Excel affiche la boîte de dialogue ci-dessus pour s’assurer que les formules de la feuille de calcul active pointent toujours vers la valeur la plus mise à jour au cas où la valeur de référence changerait. 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, accédez à Fichier > Options > Options avancées > Général et désactivez la case à cocher Confirmer la mise à jour automatique des liens.

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

• Assurez-vous qu’Excel est configuré pour afficher les formules dans votre feuille de calcul. Pour ce faire, sélectionnez l’onglet Formules , puis dans le groupe Audit des 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 opération, vos colonnes s’élargissent automatiquement pour afficher vos formules, mais ne vous inquiétez pas, lorsque vous revenez à l’affichage normal, vos colonnes se redimensionneront.

• Si l’étape ci-dessus ne résout toujours pas le problème, il est possible que la cellule soit mise en forme sous forme de 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 au format texte, vous pouvez sélectionner la plage, appliquer le format numérique de votre choix et accéder à Données > Texte à la colonne > Terminer. Cela permet d’appliquer la mise en forme à toutes les cellules sélectionnées.

  

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

1. Sélectionnez l’onglet Fichier, sélectionnez Options, puis sélectionnez 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.

   

Pour plus d’informations sur les calculs, consultez Modifier le recalcul de 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 laquelle elle se trouve. Le correctif consiste à déplacer la formule vers une autre cellule ou à remplacer la syntaxe de formule par une autre qui évite 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 Supprimer ou autoriser une référence circulaire.

Pour plus d’informations sur les références circulaires, consultez 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, telle que le 2 novembre ou le 02/11/2009, au lieu de diviser 2 par 11.

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

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 fonctionnera pas, car elle contient deux parenthèses fermantes et 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)

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 incorrect, les fonctions peuvent retourner des résultats inattendus ou afficher une erreur #CNUM!.

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 mis en forme avec des signes dollar ($) ou des séparateurs décimaux (,) dans les formules, car les signes dollar indiquent des références absolues et les virgules sont des séparateurs d’arguments. Au lieu d’entrer 1000,00 €, entrez 1000 dans la formule.

Si vous utilisez des nombres mis en forme dans des arguments, vous obtenez des résultats de calcul inattendus, mais vous pouvez également voir l’erreur #NUM!. Par exemple, si vous entrez la formule =ABS(-2,134) pour trouver la valeur absolue de -2134, Excel affiche l’erreur #NUM! , car la fonction ABS n’accepte qu’un seul argument et voit les arguments -2 et 134 comme arguments distincts.

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 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 (*).

Toutefois, si vous utilisez des références de cellule, Excel renvoie un #NOM ? .

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, « Aujourd’hui est » a un espace avant le guillemet de fin pour fournir l’espace vide souhaité entre les mots « Aujourd’hui est » et « Lundi 30 mai ». Sans guillemets autour du texte, la formule peut afficher l’erreur #NOM?.

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

Par exemple, la formule =SI(SQRT(PI())<2, « Moins de deux! », « Plus de deux! ») a 3 niveaux de fonctions ; la fonction PI est imbriquée à l’intérieur de la fonction SQRT, qui à son tour est imbriquée à l’intérieur de 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 » dans votre classeur, tapez : =’Données trimestrielles’!D3. Si vous ne placez pas le nom de feuille entre guillemets, la formule risque de générer l’erreur #NOM?..

Vous pouvez également sélectionner les valeurs ou les 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 inclure une référence aux cellules A1 à A8 dans la feuille Ventes du classeur Operations T2 ouvert dans Excel, tapez : =[Operations T2.xlsx]Ventes!A1:A8. Sans les crochets, la formule affiche l’erreur #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).

Si vous divisez une cellule par une autre contenant la valeur zéro (0) ou aucune valeur, une erreur #DIV/0! est générée..

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 disposez de toutes les formules qui font référence à des données dans des cellules, des plages, des noms définis, des feuilles de calcul ou des classeurs, 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 retourne une erreur #REF!, sélectionnez la cellule avec le #REF! . Dans la barre de formule, sélectionnez #REF! et supprimez-le. Entrez ensuite à nouveau la plage de la formule.

• Si un nom défini est manquant et qu’une formule qui fait référence à ce nom retourne une erreur #NOM?, définissez un nouveau nom qui fait référence à la plage souhaitée ou modifiez la formule pour faire 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 retourne une erreur #REF! , il n’existe aucun moyen de résoudre ce problème. Malheureusement, une feuille de calcul qui a été supprimée ne peut pas être récupérée.

• 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électionnez Annuler, puis vérifiez que ces données ne sont pas perdues en remplaçant les formules qui font référence au classeur 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 le 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!) pour apparaître dans la cellule de destination, car les cellules qui contiennent les valeurs que vous avez utilisées dans la formule ne peuvent plus être référencées.

Vous pouvez éviter cette erreur en collant les valeurs résultantes 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 .

   

   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 dans une autre feuille de calcul ou classeur, sélectionnez un autre onglet de feuille de calcul ou basculez vers un autre classeur, puis sélectionnez la cellule supérieure gauche de la zone de collage.

4. Sous l’onglet Accueil, dans le groupe Presse-papiers, sélectionnez Coller , puis sélectionnez Coller les 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.

   

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.

   

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

   Le bouton Afficher n’est pas disponible la deuxième fois que la référence apparaît dans la formule, ou si la formule fait référence à une cellule d’un autre classeur.

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

   L’outil Évaluer la formule ne vous indique pas nécessairement pourquoi votre formule est rompue, mais il peut vous aider à indiquer 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.