Comment éviter les formules incorrectes

Comment éviter les formules incorrectes

Remarque : Nous faisons de notre mieux pour vous fournir le contenu d’aide le plus récent aussi rapidement que possible dans votre langue. Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez-vous nous indiquer en bas de page si ces informations vous ont aidé ? Voici l’article en anglais à des fins de référence aisée.

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. En pareil cas, il est recommandé de recommencer depuis le début.

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

Vous revenez à la cellule contenant la formule incorrecte, qui est en mode d'édition, puis Excel met en surbrillance l'endroit où il rencontre un problème. Si vous ne savez toujours pas quelle action faire de là et que vous voulez recommencer, vous pouvez appuyer de nouveau sur Échap ou cliquer sur le bouton Annuler dans la barre de formule, ce qui vous permet de quitter le mode édition.

Image du bouton Annuler dans la barre de formule

Si vous ne savez pas quoi faire à ce stade ou vous ignorez le type d’aide dont vous avez besoin, vous pouvez rechercher des questions semblables dans le forum de la Communauté Excel ou publier votre question.

Lien vers le Forum de la Communauté Excel

Pour avancer, vous pouvez consulter la liste de contrôle suivante qui décrit les étapes de dépannage permettant d’identifier les erreurs dans vos formules.

Excel renvoie différentes erreurs de hachage (#), telles que #VALUE!, #REF, #NUM, #N/A, #DIV/0!, #NAME? et #NULL!, pour indiquer que votre formule ne fonctionne pas correctement. Par exemple, le #VALUE! l'erreur est due à une mise en forme incorrecte ou à des types de données non pris en charge dans les arguments. Vous verrez 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 conseils de résolution des problèmes varient 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

Parmi les rubriques suivantes, reportez-vous à celle qui correspond à l’erreur que vous rencontrez :

Chaque fois que vous ouvrez une feuille de calcul contenant des formules qui font référence à des valeurs d'autres feuilles de calcul, vous êtes invité à mettre à jour les références ou à les conserver en l'absence.

Boîte de dialogue signalant des liens rompus dans Excel

Excel affiche la boîte de dialogue ci-dessus pour vérifier si les formules de la feuille de calcul active pointent toujours vers la valeur mise à jour la plus récente, au cas où la valeur référencée aurait été modifiée. 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. Dans Excel 2007, vous devez cliquer sur le Bouton Office > Options Excel. Bouton Office 2007

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

Important : Si c’est la première fois que vous avez affaire à des liens rompus dans les formules, si vous avez besoin d’un rappel sur la manière de corriger les liens rompus ou si vous ne savez pas s’il convient de 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 :

  • Vérifiez que la feuille de calcul Excel est définie pour afficher les formules. Pour cela, cliquez sur l'onglet formules , puis dans le groupe audit de formules , cliquez sur Afficher les formules.

    Conseil : Vous pouvez également utiliser le raccourci clavier Ctrl + ' (la touche située au-dessus de la touche TAB). Lorsque vous procédez ainsi, vos colonnes s'élargissent automatiquement pour afficher vos formules, mais ne vous inquiétez pas, lorsque rebasculez vers le mode d'affichage normal, les colonnes seront redimensionnées.

  • Si l’étape ci-dessus ne résout toujours pas votre problème, il est possible que la cellule soit mise en forme comme du 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 grande plage de cellules dans une colonne qui sont mises en forme comme du texte, vous pouvez sélectionner la plage, appliquer le format numérique de votre choix et accéder à Données > Convertir > 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 calculées si le calcul manuel est activé. Procédez comme suit pour vérifier si le calcul automatique est activé ou non :

  1. Cliquez sur l’onglet Fichier, cliquez sur Options, puis cliquez sur 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 un recalcul, une itération ou une précision de formule.

La présence d’une référence circulaire signifie qu’une formule fait référence à la cellule dans laquelle elle se trouve. Les deux seules manières de résoudre le problème consistent à déplacer la formule dans une autre cellule ou à remplacer la syntaxe de la formule par une syntaxe qui permet 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 fonction supprimer ou autoriser 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. Maintenant, si vous tapez 11/2, Excel affiche une date telle que 2-Nov ou 02/11/2009, et non le résultat de la division de 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 a besoin d'une parenthèse fermante pour que la fonction fonctionne correctement, vous devez donc vous assurer que toutes les parenthèses font partie d'une paire correspondante. Par exemple, la formule = si (B5<0), "non valide", B5 * 1.05) ne fonctionne pas, car il y a deux parenthèses fermantes, 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 contenant les références aux fonctions. Celle-ci est située sous la formule et s’affiche au fur et à mesure que vous tapez cette dernière.

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 incorrect, 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 mis en forme à l'aide de signes dollar ($) ou de 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 $1 000, entrez 1000 dans la formule.

Si vous utilisez des nombres mis en forme dans des arguments, vous obtiendrez des résultats de calcul inattendus, mais vous pouvez également voir le message d'erreur #NUM! . Par exemple, si vous entrez la formule = ABS (-2 134) pour rechercher la valeur absolue de-2134, Excel affiche le #NUM. erreur, car la fonction ABS n'accepte qu'un seul argument.

Remarque : Vous pouvez mettre en forme le résultat de la formule avec des séparateurs décimaux et des symboles monétaires après avoir entré la formule à l'aide de nombres non mis en forme (constantes). Il n'est généralement pas judicieux de placer des constantes dans les formules, car elles peuvent être difficiles à trouver si vous devez les mettre à jour plus tard et qu'elles sont plus susceptibles d'être tapées incorrectement. Il est préférable de placer vos constantes dans les cellules, là où elles sont ouvertes et facilement référencées.

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ît dans la cellule est =2+3. Pour résoudre ce problème, modifiez le type de données Texte de la cellule en Général, comme ceci :

  1. Sélectionnez la cellule.

  2. Cliquez sur Accueil, cliquez sur la flèche en regard de Format de nombre (ou appuyez sur Ctrl+1), puis cliquez sur Standard.

  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 renverra une #NAME? .

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, «aujourd'hui est» dispose d'un espace avant le guillemet de fin pour indiquer l'espace à insérer entre les mots «nous sommes aujourd'hui» et «lundi, lundi 30». Sans guillemets entourant le texte, la formule peut afficher l' erreur #NAME.

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

Par exemple, la formule =SI(RACINE(PI())<2,"Moins de deux !","Plus de deux !") comporte 3 niveaux de fonctions : la fonction PI est imbriquée dans la fonction RACINE, qui est à son tour imbriquée 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» dans votre classeur, tapez: = 'données trimestrielles'! D3. Sans les guillemets entourant le nom de la feuille, la formule affiche l' erreur #NAME?.

Vous pouvez également cliquer sur les valeurs ou cellules dans 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 dans la feuille de calcul du classeur d'opérations du 2ème trimestre ouvert dans Excel, tapez: = [Operations Q2. xlsx] ventes. A1: A8. Sans les crochets, la formule affiche le #REF erreur..

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 : Pour obtenir le chemin d'accès d'un autre classeur, le plus simple est d'ouvrir l'autre classeur, à partir de votre classeur d'origine, de taper =, puis d'utiliser Alt + Tab pour accéder à l'autre classeur et de sélectionner n'importe quelle cellule de la feuille de votre choix. Puis fermez le classeur source. Votre formule est automatiquement mise à jour pour afficher le chemin d'accès complet et le nom de la feuille, ainsi que la syntaxe requise. Vous pouvez même copier-coller la trajectoire et l'utiliser où vous en avez besoin.

La Division d'une cellule par une autre cellule qui comporte zéro (0) ou aucune valeur génère une erreur #DIV/0!.

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

=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 renvoient une erreur #REF!, sélectionnez la cellule contenant le #REF. . Dans la barre de formule, sélectionnez #REF. et supprimez-le. Entrez ensuite la plage de la formule.

  • S'il manque un nom défini et qu'une formule faisant référence à ce nom renvoie une erreur de #NAME?, définissez un nouveau nom qui fait référence à la plage de votre choix ou modifiez la formule pour faire référence directement à la plage de cellules (par exemple, a2: D8).

  • S'il n'y a pas de feuille de calcul et qu'une formule fait référence à celle-ci renvoie une #REF! erreur: il n'est pas possible 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. Cliquez sur Annuler, puis vérifier que ces données ne sont pas égarées en remplaçant les formules qui font référence au classeur manquant par leurs résultats.

Parfois, lorsque vous copiez le contenu d’une cellule, vous voulez coller uniquement la valeur et non la formule sous-jacente qui s’affiche 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!) s’affiche 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ées.

Vous pouvez éviter cette erreur en collant dans les cellules de destination les valeurs résultant des formules sans la formule proprement dite.

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

  2. Dans l’onglet Accueil, dans le groupe Presse-papiers, cliquez sur 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 classeur, cliquez sur un autre onglet ou basculez vers un autre classeur, puis sélectionnez la cellule supérieure gauche de la zone de collage.

  4. Dans l' onglet Accueil , dans le groupe presse-papiers , cliquez sur coller Image du bouton , puis cliquez sur coller les valeurs, ou appuyez sur ALT > E > S > V > entrée pour Windows, ou sur la commande > v > v > entrée MacOS.

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. Cliquez sur Formules > Évaluer la formule.

    Groupe Vérification des formules dans l’onglet Formules

  3. Cliquez sur É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, cliquez sur Pas à pas détaillé pour afficher l’autre formule dans la zone Évaluation. Cliquez sur Pas à pas sortant pour revenir à la cellule précédente et la formule.

    Le bouton Pas à pas détaillé n’est pas disponible lorsque la référence apparaît pour la deuxième fois 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 indiquera pas nécessairement la raison pour laquelle votre formule est incorrecte, mais il peut vous aider à identifier l’emplacement concerné. 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 est modifiée. 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, obtenir une assistance dans la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le forum Excel User Voice.

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)

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 ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×