Les messages d'erreur liés aux formules incorrectes

L’article présent de la formation Excel traite le sujet des messages d’erreur qui s’affichent en cas d’utilisation incorrecte d'une formule. Il traite aussi le problème des formules correctement créées mais qui ne fonctionnent pas et comment le résoudre.
Messages d'erreurs excel


En cas d’erreur, vous pouvez remarquer que Excel n’affiche pas les valeurs d’erreur que nous avons vues dans l’article erreurs dans Excel : #N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM?, ou #NUL!.
Mais il affiche des messages d’erreur sous formes de boîtes de dialogue.

Comme vous pouvez aussi remarquer que lorsque vous utilisez une formule, elle ne fonctionne pas et aucun message d’erreur n’est émis.

Formule incompréhensible !

Quand Excel ne comprend pas la formule que vous avez créée, il affiche ce message d’erreur.

Excel - Message erreur formule incompréhensible


Et lorsque vous cliquez sur OK, Excel sélectionne la partie de votre formule où il rencontre le problème.
Essayez donc de réviser ce que vous avez tapé et assurez-vous que vous avez bien respecté la syntaxe de la fonction utilisée et que votre formule ne contient aucun élément inapproprié.

Dans l’exemple suivant, j’ai utilisé la fonction NB.SI pour chercher combien de fois le "Produit A" s’est vendu, mais Excel m’affiche la même fenêtre en dessus.

Je clique sur OK et je détecte que dans la partie sélectionnée j’ai tapé virgule au lieu de point-virgule dans ma formule.

Erreur sélectionnée par Excel


Problème de liaison externe

Excel affiche un message d’alerte lorsque vous essayez d’ouvrir un classeur qui contient une ou plusieurs formules qui utilisent des références de cellules externes, c’est-à-dire appartenant à un autre classeur.

Message détection de liaison externe


Cliquez sur Mettre à jour pour mettre à jour votre formule, ou bien cliquez sur Ne pas mettre à jour pour annuler toute liaison avec la source externe.

Si la source externe, c’est-à-dire le classeur qui contient la référence de la cellule liée à votre formule est supprimé, déplacé ou renommé, et quand vous ouvrez votre classeur contenant votre formule, Excel affiche un message d’erreur de liaison.

Message erreur de liaison externe


En cliquant sur Continuer vous allez donc ignorer cette liaison.

Et si vous choisissez Modifier les liaisons, Excel vous affiche la fenêtre suivante :

Fenêtre Modifier les liaisons


Dans la zone source, Excel affiche le nom du classeur en liaison et en bas son emplacement.

A droite de cette zone, utilisez la commande qui vous convient pour résoudre votre problème :
  • Mettre à jour les valeurs et Modifier la source : ces deux commandes vous permettent de sélectionner votre classeur qui contient la référence de la cellule utilisée dans votre formule, si ce classeur est renommé ou déplacé.
  • Ouvrir la source : Cette commande essaie de vous rouvrir le classeur en liaison.
  • Rompre la liaison : pour annuler toute liaison avec ce classeur et dans ce cas votre formule sera remplacée par le dernier résultat affiché.

Note : Vous pouvez aussi afficher la boîte de dialogue Modifier la liaison à partir de l’onglet Données en cliquant sur Modifier les liens.

La commande Modifier les liens - Excel


Astuce :
Vous pouvez mettre à jour la liaison avec la référence de la cellule externe en procédant ainsi :
  • Sélectionnez la cellule contenant votre formule.
  • Dans la barre de formule, cliquez sur la formule puis tapez Entrée.
  • Dans la boîte de dialogue qui s’affiche, sélectionnez le classeur externe en liaison.


Problème de parenthèse fermante

Lorsqu’on utilise des parenthèses dans notre formule, on oublie parfois de taper la parenthèse fermante.
Dans ce cas Excel affiche un message d’erreur et vous propose sa correction.

Message erreur de parenthèse fermante - Excel


Cliquez donc sur OK pour confirmer.

La fonction ou la formule ne fonctionne pas !

Cas n°1

Vous créez la formule et quand vous tapez Entrée, elle reste affichée sans effectuer de calcul.

Formule ne fonctionne pas - Excel


Par exemple =(A1+A2+A3)/3 ne donne aucun résultat.

Vérifiez le format défini pour cette cellule, s’il est texte, vous devez le modifier en standard.

Sélectionner Format Standard


Voir Format de cellule pour plus de détails.

Cas n°2

La formule ne se met pas à jour automatiquement lors de changement des données qu’elle utilise.

Vérifiez dans les options Excel si le calcul automatique est désactivé.

  • Cliquez sur Fichier puis Options.
  • Ensuite cliquez sur Formules et cochez Automatique sous Calcul du classeur.
Calcul de classeur automatique activé



Cas n°3

Vous pouvez remarquer que votre formule affiche 0 même si vous utilisiez des fonctions simples comme la fonction Somme.

Dans ce cas vérifiiez si votre formule contient une référence circulaire. C’est-à-dire que la formule fait référence à la cellule dans laquelle elle se trouve.

Dans cet exemple =SOMME(A1:A4) la référence A4 est une référence circulaire.

Référence circulaire


Pour résoudre ce problème, il faut donc rectifier la formule en laissant la référence de la plage de cellules comme ça A1:A3.

Comment savoir si ma feuille de calcul contient des références circulaires ?

Pour savoir si votre feuille de calcul contient des références circulaires, cliquez sur Vérification des erreurs sous l’onglet Formules.

Dans le menu qui apparaît, sélectionnez Références circulaires, Excel affiche une liste de ces références.

Cliquez sur la référence circulaire voulue et Excel la sélectionnera pour vous.

Menu Références circulaires - Excel


Note : lorsque la commande Références circulaires est désactivée, cela signifie qu’il n’existe aucune référence circulaire dans votre feuille.

Commande références circulaires désactivées



Les erreurs dans Excel

Dans cet article de la formation Excel, vous allez découvrir les types d'erreurs qui surviennent pendant votre travail sur un tableau et précisément lorsque vous utilisez des formules. Vous allez aussi voir deux méthodes pour rechercher les erreurs dans de grands tableaux Excel.
Erreurs excel


Nous avons vu dans les articles précédents les formules et les fonctions et comment les créer, nous avons parlé aussi de la syntaxe d’une fonction qui se compose d’éléments différents.

Alors, lorsque vous aurez mal utilisé une fonction ou une formule : son nom n’est pas écris correctement, une parenthèse qui manque, des arguments inappropriés ; Excel vous affichera un message d’erreur. Ce message est un texte spécial qui remplace le résultat que vous estimez avoir dans la cellule contenant votre formule.

Par exemple, Excel peut afficher #DIV/0! ; #Nom? … etc.

Ces expressions vous semblent bizarre ?! Ne vous en faites pas ! Il vous suffit tout simplement de connaître leurs significations et quand est-ce que se produisent-elles pour corriger les erreurs rencontrées.

Dans les lignes qui suivent, vous allez découvrir les types d’erreurs que peut afficher Excel.

Que remarquez-vous quand Excel affiche un message d’erreur ?

Prenez ce simple exemple :

La cellule A3 contient le message d’erreur #DIV/0! avec un tout petit triangle vert dans son coin supérieur gauche, et à sa côte s’affiche un losange jaune avec un point d’exclamation.

message erreur excel


Lorsque vous amenez le pointeur de la souris sur ce losange, Excel vous affiche une info-bulle qui vous indique le type d’erreur commise.

Pour notre exemple : Excel affiche donc :

La formule ou la fonction utilisée effectue une division par zéro ou par des cellules vides.

Info-bulle erreur excel


Si vous cliquez également sur ce losange, Excel affiche un menu où vous voyez la définition de l’erreur affichée avec d’autres options en rapport avec cette erreur.

Menu erreur excel


Remarquez en plus que la barre de formule affiche toujours la fonction utilisée sans être écrasée par le message d’erreur, ce qui vous rend facile sa rectification.

Barre de formule et erreur excel


Allons maintenant découvrir les types d’erreurs dans Excel.

Les types d’erreurs dans Excel

Voici les différents types d'erreurs susceptibles de se présenter lors de l’utilisation des formules Excel, et comment vous allez procéder pour résoudre les problèmes qui peuvent se poser.

Erreur #DIV/0! 

Comme vous l’avez constaté, cette erreur s'est produite lors d'une division par 0. Cherchez donc dans votre formule saisie la référence de la cellule qui renvoie une valeur nulle.

erreur #DIV

Erreur #NOM?

Cette erreur apparaît lorsque :

  • Vous n’avez pas saisi correctement le nom de votre formule :
Erreur #NOM



Dans cet exemple le nom de la fonction Moyenne est écrit avec un seul « n ».
  • La référence de la cellule utilisée dans la formule n’est pas bien écrite :
Erreur #NOM référence incorrect



Ici nous avons écrit E au lieu de E2.

Et ici nous n’avons pas bien saisi le nom de la plage des cellules utilisée : ListeMoyennes au lieu de ListeDesMoyennes

Erreur #NOM nom plage incorrect


Pour résoudre ce genre de problème, sélectionnez le nom de la plage de cellules dans la liste Zone de nom puis collez-le dans votre formule.

Erreur #Nom solution


Ou bien, sélectionnez le nom erroné dans votre formule puis cliquez sur l’onglet Formules, et dans le groupe Noms définis cliquez sur Dans une formule ensuite cliquez sur le nom de la plage de cellules convenable.

Erreur #Nom Solution utilisant le bouton Dans une formule


  • Il manque des guillemets pour des valeurs de type texte dans l’un des arguments d’une formule.
Erreur #NOM problème guillemets



Ici nous avons oublié les guillemets pour les valeurs Oui et Non.

  • Vous avez omis les deux points dans une référence de plage de cellules.

Regardez l’exemple suivant où nous avons supprimé les deux points dans la référence de la plage de cellules B2:B11

Erreur #NOM problèmes dans référence plage


Erreur #REF!

Elle est liée au problème avec des références de cellules utilisées dans une formule, lorsque ces références sont supprimées, déplacées ou remplacées.

Dans l’exemple suivant on a calculé le montant TTC, dans la colonne D, en multipliant le montant HT, dans la colonne C, par 1,2.

Formule pour calculer le Montant TTC


Et si on supprime la colonne C nous obtiendrons l’erreur #REF!

Erreur #REF


Remarquez aussi que la barre de formule indique qu’il y a une référence de cellule non valide.

Erreur #NOMBRE!

  • Cette erreur apparaît lorsque les valeurs numériques utilisées dans une formule ou une fonction ne sont pas valides.


Par exemple la fonction PETITE.VALEUR renvoie ici une erreur #NOMBRE!, parce que dans ses arguments nous avons défini la référence de la plage de cellules B2:B6 qui contient des valeurs textuelles au lieu d’insérer la référence de la plage de cellules qui contient des nombres.

Erreur #Nombre problème et solution


  • En plus, l’erreur #NOMBRE! s’affiche si le résultat produit par une formule renvoie un nombre trop grand ou trop petit. Le résultat obtenu doit être entre -1*10^307 et 1*10^307.


Erreur #Nombre problème nombre trop grand


  • L’erreur #NOMBRE! apparaît également si la fonction produit une itération.

Erreur #N/A

L’erreur #N/A se produit lorsqu’il manque une valeur pour une formule ou une fonction. Cette erreur apparaît le plus souvent avec les fonctions RECHRECHV, RECHERCHH, RECHERCH ou EQUIV. Nous traiterons ces fonctions après.

Erreur #N_A


Erreur #VALEUR!

Lorsque cette erreur survient, vérifiez un argument ou un élément de votre formule qui est inapproprié.

  • Par exemple si vous faites la somme d’une valeur numérique et d’une valeur textuelle: =150+ "a", l'erreur #VALEUR! survient.
Erreur #Valeur


  • l'erreur #VALEUR! se produit aussi si un argument d’une formule fait référence à une valeur textuelle ou à une cellule qui contient seulement un espace. 
    • Dans cet exemple, la division affiche une erreur car la cellule F6 est vide sauf d’un espace.


Erreur #Valeur problème d'espace dans une cellule


  • L’erreur #VALEUR! s’affiche aussi si la cellule référencée dans une formule renvoie elle aussi une valeur d’erreur.


Erreur #Valeur problème erreur dans référence


  • Cette erreur se produit également s’il y a une erreur dans la syntaxe de la fonction utilisée.
  • Une autre cause qui fait afficher cette erreur est que la formule matricielle entrée est incorrecte.
  • On a fourni une plage de valeurs à un opérateur ou une fonction, alors qu’elle exige une valeur unique.

Erreur #NUL! :

Cette erreur se produit lorsque vous spécifiez deux zones qui ne se coupent pas.
Par exemple =SOMME(B2:B5 C2:C5)

Erreur #NUL


Dans ce cas, ajoutez un point-virgule entre les deux références des deux plages de cellules pour que la fonction effectue le calcul souhaité.

Erreur #NUL Solution


L’erreur existe, mais Excel n’affiche aucun message !

Ça pourrait vous arriver parfois, quand vous remarquez que le triangle vert et le losange Jaune apparaissent dans une cellule et que celle-ci ne contient aucun message d’erreur.

Cette erreur "silencieuse" peut survenir lorsque Excel détecte que dans une plage de cellules, il y a une cellule qui contient une formule différente d'une formule qui se répète dans les autres cellules de cette plage.

Erreur silencieuse Excel


Par exemple, dans ce tableau la cellule F6 contient une formule de somme, alors que les autres cellules de la même plage de cellules utilisent la fonction MOYENNE

Erreur existante sans message


Comment chercher les erreurs dans une feuille Excel ?

Si vous voulez vous assurer que vous avez corrigé toutes les erreurs survenues, surtout si vous travaillez sur de grands tableaux qui contiennent plusieurs formules ou fonctions, vous pouvez procéder de deux façons :
  • Cliquez sur une cellule de votre tableau puis sous l’onglet Accueil et dans le groupe Edition, cliquez sur Rechercher et sélectionner puis cliquez sur Sélectionnez les cellules.
Menu Rechercher et Sélectionner dans Excel

    • Dans la boîte de dialogue qui apparaît, cochez Formules, ensuite laissez seulement le choix Erreurs coché puis cliquez sur OK .
Chercher Erreurs des formules



Les fonctions Excel : Définition, types et méthodes d’insertion

Dans cet article de la Formation Excel, vous allez découvrir ce qu’est une fonction Excel, les types des fonctions Excel et comment procéder pour insérer une fonction Excel en suivant 5 méthodes. L’article est enrichi d’exemples explicatifs avec un eBook cadeau à télécharger : Fonctions Excel 2016 en PDF.
Les fonctions Excel  2016


Vous avez vu dans l’article Formule Excel, qu’une fonction est une formule prédéfinie par Excel pour effectuer un calcul souhaité.

Et pour mieux expliquer cette définition, prenez l’exemple suivant qui représente une simple facture qu’on veut calculer son montant total.

Exemple de facture simple


  • Commencez donc par sélectionner la cellule D7 qui va afficher le résultat.
  • Tapez le signe égal = pour créer une formule, puis écrivez D3+D4+D5+D6. C’est-à-dire que vous allez calculer la somme des montants que contiennent ces cellules.
Formule simple pour calculer le total



Note : Remarquez que lorsque vous tapez la référence d’une de ces cellules, Excel colorie cette référence et sa cellule d’une même couleur.

  • Tapez enfin Entrée pour valider la formule et obtenir le résultat désiré.
Résultat de la formule addition



Note : Au lieu de taper à la main D3, D4, D5 et D6, vous pouvez les sélectionner une par une.

Sélection des références de cellules dans une formule


Imaginez maintenant que la colonne Total contient plusieurs cellules que vous devez saisir leurs références pour calculer leur somme, ce sera vraiment embarrassant !

La solution donc, consiste à utiliser une formule qui va vous permettre de réduire tout ce travail, de gagner du temps et d’éviter de tomber dans des erreurs à cause des fautes de frappe.

Pour cette raison, Excel fournit de nombreuses fonctions qui répondent aux besoins des utilisateurs, et parmi ces fonctions on trouve la fonction SOMME qui va vous aider dans votre cas.

Voici comment l’utiliser :
  • Dans la cellule D7 tapez = et écrivez Somme puis ouvrez une parenthèse.
  • Sélectionnez les cellules de D3 à D6.
  • Fermez la parenthèse et validez enfin en tapant la touche Entrée.
Insérer fonction Somme



Voilà donc, la fonction SOMME a effectué facilement le même travail de la formule que vous avez tapée préalablement.

Syntaxe ou structure d’une fonction Excel

Revenez à l’exemple précédent et sélectionnez la cellule D7.
La formule que contient cette cellule est affichée dans la barre de formule :
=SOMME(D3 :D6)

Syntaxe de la fonction Somme


Comme vous le voyez, la fonction SOMME est composée des éléments suivants :
  • Le signe égal =
  • Le nom de la fonction : SOMME
  • Une parenthèse ouvrante et une parenthèse fermante.
  • Et une plage de cellules D3 :D6, c’est-à-dire les cellules D3,D4 ,D5, D6.

A partir de cet exemple, vous pouvez alors déduire la syntaxe générale d’une fonction Excel qui s’écrit de cette façon :
=Nom_Fonction(Arguments)

  • Une fonction doit toujours commencer par le signe égal =
  • Nom_Fonction : le nom de la fonction est prédéfini par Excel, vous n’avez donc qu’à le chercher parmi la liste des fonctions que contient la bibliothèque de fonctions Excel. Par exemple :
    • SOMME : qui renvoie la somme des nombres.
    • Abs : qui renvoie la valeur absolue d’un nombre.
    • Etc….

Vous pouvez télécharger : La Liste des fonctions Excel 2016 PDF pour consulter toutes les fonctions Excel triées par ordre alphabétiques.
  • ( ) : Les parenthèses.
  • Arguments : que ce soit un argument ou plusieurs, ce sont des paramètres nécessaires pour exécuter une fonction. Ils peuvent être des références de cellules, des nombres, du texte, des valeurs logiques (vrai ou faux), des tableaux, des dates et peuvent aussi être des formules ou des fonctions par exemple :

=SI(SOMME(D3 :D6)>1000 ;D7*10% ;D7*2%)

Dans cet exemple, nous avons une fonction SI qui contient parmi ses arguments la fonction SOMME.

On dit dans ce cas que la fonction SOMME est imbriquée dans la fonction SI.

Note : On peut avoir jusqu’à 7 nivaux de fonctions imbriquées.

Veuillez remarquer aussi que la fonction SI se compose de trois arguments séparés par des points-virgules :
  1. Argument 1 : SOMME(D3 :D6)>1000 
  2. Argument 2 : D7*10% 
  3. Argument 3 : D7*2%

Comment insérer une fonction Excel ?

Voici 5 méthodes pour ce faire :
Sélectionnez tout d’abord la cellule où vous voulez insérer votre fonction.

Méthode 1

  • Tapez le signe égal =
  • Puis tapez la première lettre du nom de votre fonction, Excel va vous afficher une liste des fonctions commençant par cette lettre.
  • Par exemple pour la fonction MOYENNE, quand vous tapez M, voici la liste qui apparaît :
Insértion d'une fonction excel

  • Vous pouvez donc cliquer sur le nom de la fonction MOYENNE dans la liste, puis entrer ses arguments. Et n’oubliez pas de fermer la parenthèse!

Méthode 2

  • Cliquez sur le bouton Insérer une fonction dans la barre de formule.
Bouton Insérer une fonction

  • Une boîte de dialogue s’affiche.
  • Dans la zone Sélectionnez une fonction cliquez sur le nom de la fonction désirée. 
Boite de dialogue Insérer fonction

  • S’il n’existe pas, allez dans la zone Recherchez une fonction et tapez le nom de votre fonction recherchée ou une phrase descriptive de cette fonction.
    • Par exemple si vous cherchez une fonction qui calcule le nombre de jours ouvrés d’une période définie, tapez nombre jours ouvrés puis cliquez sur Rechercher.
    • Dans la zone Sélectionnez une fonction, sélectionnez votre fonction : NB.JOURS.OUVRES.
Rechercher une fonction


Note : En bas de cette boîte de dialogue, vous avez la possibilité de cliquer sur Aide sur cette fonction pour savoir comment l’utiliser.
  • Cliquez sur OK enfin.

A partir de la boîte de dialogue Insérer une fonction, vous pouvez aussi sélectionner le nom de  la fonction souhaitée en sélectionnant d'abord la catégorie à laquelle elle appartient.
  • Par exemple la fonction ARRONDI appartient à la catégorie Math & trigo.
Choisir type de fonction



  • Lorsque vous cliquez sur OK, Excel affiche une autre boîte où vous allez entrer les arguments de votre fonction choisie:
Insérer Arguments de la fonction



Méthode 3

Sous l’onglet Formules et dans le groupe Bibliothèque de fonctions vous pouvez :
  • Soit cliquer sur Insérer une fonction et suivre les mêmes démarches expliquées dans la Méthode 2.
Insérer fonction à partir de la bibliothèques des fonctions

  • Soit cliquer sur Récentes qui affiche la liste des dernières fonctions utilisées et une commande Insérer une fonction pour insérer une nouvelle fonction.
Insérer les fonctions récentes

  • Soit choisir votre fonction dans l’une des catégories disponibles dans cette bibliothèque.
    • Exemple :
    • Cliquez sur la catégorie Texte et dans la liste qui s’affiche, cliquez sur DEVISE, cette fonction qui permet de convertir un nombre en texte en utilisant le format monétaire.
    • Et dans la boite de dialogue qui apparaît, entrez les arguments nécessaires puis cliquez sur OK.
Utilisation de la fonction Devise



Méthode 4

  • Sous le groupe Accueil et dans le groupe Edition cliquez sur la flèche à côté du symbole de la fonction SOMME pour afficher la liste des fonctions les plus utilisées: Somme, Moyenne, NB, Max, Min
  • Cliquez donc sur celle que vous voulez insérer.
  • Ou bien, cliquez sur Autres fonctions pour chercher la fonction qui ne s'affiche pas dans cette liste et que vous voulez utiliser puis suivez les étapes décrites dans la méthode 2.
Insérer une fonction à partir du groue Edition



Méthode 5

  • Tapez le signe = dans la cellule qui va contenir votre fonction.
  • Dans la zone Nom à gauche de la barre de formule, Excel affiche la liste des dernières fonctions que vous avez utilisées.
  • Sélectionnez donc la fonction souhaitée ou bien cliquez sur Autres fonctions et comme vous l’avez vu dans la Méthode 2, insérez la fonction que vous voulez.
Insérer fonction à partir de la zone Nom de la cellule



Les types de fonctions

Sous l’onglet Formules, Excel regroupe toutes les fonctions qu’il contient et les classe en catégories.

Catégorie des fonctions Excel


Dans la boîte de dialogue Insérer une fonction, vous trouvez aussi toutes ces catégories dans la liste Sélectionnez une catégorie :

Liste des catégories des fonctions Excel


  • Finances
  • Logique
  • Texte
  • Date & Heure
  • Recherche & Référence
  • Maths & Trigonométrie
  • Statistiques
  • Ingénierie
  • Cube
  • Informations
  • Compatibilité
  • Web
  • Base de donnés


  • Et des fonctions personnalisées qui sont des fonctions que vous avez créées vous-mêmes où vous les avez ajoutées à Excel en installant des compléments.
    • Par exemple, en cliquant sur Insérer une fonction et dans la boîte de dialogue qui apparaît, je clique sur la catégorie Personnalisées.
    • Dans la zone Sélectionnez une fonction apparaissent mes fonctions que j’ai ajoutées.
    • Je choisis alors la fonction ConvNumberLetter qui me permet de convertir un nombre en lettres.
    • Dans la fenêtre Arguments de la fonction, et dans la zone Nombre je sélectionne la cellule qui contient le nombre à convertir.
Insérer fonction convertir nombre en lettres

    • Si c’est un nombre monétaire, je précise la devise en tapant par exemple 1 pour l’Euro.
    • Je clique sur Ok et voici le résultat obtenu.

    Convertir un nombre monétaire en lettres



    Ceci donc est une vue générale sur les fonctions Excel, dans les prochains articles, je vais vous sélectionner des exemples de fonctions et vous expliquer comment les utiliser.