Leçons de niveau 15

Tableur EXCEL/Erreurs

Une page de Wikiversité.
Sauter à la navigation Sauter à la recherche
Début de la boite de navigation du chapitre
Erreurs
Icône de la faculté
Chapitre no 13
Leçon : Tableur EXCEL
Chap. préc. :PowerPivot
Chap. suiv. :Analyse de scénario
fin de la boite de navigation du chapitre
Icon falscher Titel.svg
En raison de limitations techniques, la typographie souhaitable du titre, « Tableur EXCEL : Erreurs
Tableur EXCEL/Erreurs
 », n'a pu être restituée correctement ci-dessus.
Photo face erreur excel




Erreurs Excel




PC MAC
Vérification des erreurs
  • Sélectionnez la feuille de calcul pour laquelle vous souhaitez vérifier les erreurs.
  • Si la feuille de calcul est calculée manuellement, appuyez sur F9 pour la recalculer maintenant.
  • Sous l’onglet Formules, dans le groupe Audit de formules, cliquez sur Vérification des erreurs.


Vérification erreurs
  • Si des erreurs sont trouvées alors la boîte de dialogue Vérification des erreurs s'affiche.
  • Si des erreurs ont été ignorées, vous pouvez les revoir.
boite dialogue vérification des erreurs


  • Sélectionnez la feuille de calcul pour laquelle vous souhaitez vérifier les erreurs.
  • Si la feuille de calcul est calculée manuellement, cliquer sur Recalculer la feuille dans le groupe Calcul de l'onglet Formules
  • Sous l'onglet Formules, dans le groupe Formules d'audit, cliquez sur Rechercher les erreurs.


Repérer des erreurs Mac
  • Si des erreurs sont trouvées alors la boîte de dialogue Vérification des erreurs s'affiche.
  • Si des erreurs ont été ignorées, vous pouvez les revoir.


boite de dialogue erreurs





Sommaire

L'erreur #REF![modifier | modifier le wikicode]

L'erreur #REF peut avoir plusieurs causes. Généralement, elle apparaît lorsqu'une formule contient une cellule non référencée, non valable.


La formule contient une ou plusieurs références à des cellules qui ont été déplacées, supprimées ou remplacées[modifier | modifier le wikicode]

Solution 1

MAC/PC

Annulez vos modifications pour rétablir les cellules à leur état d’origine
  1. Dans la barre d’outils standard, cliquez sur Annuler (Ctrl+Z) immédiatement après avoir supprimé ou collé les cellules
Solution 2

MAC

Modifiez la formule pour supprimer les références aux cellules supprimées
  1. Sélectionnez la cellule
  2. Dans l’onglet Formules, dans Fonctions, cliquez sur Concepteur de formule
  3. Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule

Modifiez la référence de plage pour y inclure soit la ligne, soit la colonne contenant la formule.

  1. Sélectionnez la cellule
  2. Dans l’onglet Formules, dans Fonctions, cliquez sur Concepteur de formule
  3. Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule
Solution 2bis

PC

Modifiez la formule pour supprimer les références aux cellules supprimées
  1. Sélectionnez la cellule
  2. Dans la barre de saisie de la fonction, mettre à jour la plage de cellule afin d'éliminer les références aux cellules supprimées

Modifiez la référence de plage pour y inclure soit la ligne, soit la colonne contenant la formule.

  1. Sélectionnez la cellule
  2. Dans la barre de saisie de la fonction, modifiez la référence de la place afin d'y inclure soit la ligne, soit la colonne contenant la formule


Une matrice non valide est utilisée dans l’une des fonctions matricielles, telle que INVERSMAT, PRODUITMAT et DETERMAT[modifier | modifier le wikicode]

Solution MAC Solution PC
Vérifiez que les dimensions de la matrice sont appropriées aux arguments de la fonction matricielle.
  1. Sélectionnez la cellule
  2. Dans l’onglet Formules, dans Fonctions, cliquez sur Concepteur de formule
  3. Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule


Capture d’écran 2015-02-12 à 13.26.07.png
Vérifiez que les dimensions de la matrice sont appropriées aux arguments de la fonction matricielle.
  1. Sélectionnez la cellule
  2. Dans l’onglet Formules, dans Fonctions, cliquez sur insérer une fonction


insérer une fonction


  1. Modifiez la formule selon les arguments demandés pour la fonction choisie


argument fonction somme.si.ens



L'erreur #VALEUR![modifier | modifier le wikicode]

Cette erreur s’affiche lorsque la formule inclut des cellules qui contiennent des types de données non identiques.

En général, l’info-bulle nous amène des précisions concernant l’erreur et affiche « Le type de données d’une valeur utilisée dans la formule est incorrect ». Cette erreur peut se résoudre en faisant des modifications simples dans la formule.


Il manque un argument nécessaire dans la formule[modifier | modifier le wikicode]

Solution MAC/PC Vérifiez que la formule ou fonction est correcte pour l’opérande ou l’argument nécessaire et que les cellules auxquelles la formule fait référence contiennent des valeurs valides.
  • Excel ne peut convertir le texte dans le type de donnée approprié. 
  • Par exemple, si la cellule A5 contient un nombre et que la cellule A6 contient le texte « Non disponible », la formule =A5+A6 renvoie l’erreur #VALEUR !. Utiliser la fonction SOMME ajouter les deux valeurs, car cette fonction ne prend pas en compte les données textuelles
  • =SOMME(A5 :A6)


L'entrée de la formule matricielle est incorrecte[modifier | modifier le wikicode]

Solution MAC
Il est nécessaire d’utiliser la procédure suivante pour entrer la formule matricielle

Sélectionnez la cellule ou plage de cellules contenant la formule matricielle, puis appuyez sur la combinaison de touches CTRL+U pour modifier la formule puis faites retour (CMD+RETOUR sur MAC)


Une référence de cellule, une formule ou une fonction est entrée en tant que constante matricielle[modifier | modifier le wikicode]

Solution MAC/PC
Vérifiez que la constante matrice ne contient pas de référence de cellule, de formule ou de fonction.
  • Les constantes matricielles ne peuvent pas contenir de références de cellules, de lignes ou de colonnes de longueur inégale, de formules, de fonctions ou le caractère spécial dollar ($), parenthèses (()) ou pourcent (%).


Une plage de valeurs est fournie à un opérateur ou une fonction qui exige une valeur unique[modifier | modifier le wikicode]

Solution MAC Solution PC
Modifiez la formule pour faire référence à une

valeur unique.

  • Sélectionnez la formule
  • Dans l’onglet Formules, dans Fonctions, cliquez

sur Concepteur de formule.

Modifiez la formule pour faire référence à une valeur unique.
  • Sélectionnez la formule
  • Dans la barre de saisie de la fonction, modifiez de telle sorte que la formule fasse référence à une valeur unique


Autre causes principales[modifier | modifier le wikicode]

Causes Solutions MAC/PC
La formule contient des éléments de cellule sous format texte et la formule effectue des calculs avec des opérateurs mathématiques (division, addition, multiplication, soustraction)
  • Respectez les arguments demandés selon la fonction utilisée (s’aider de l’info-bulle ou de l’aide en ligne excel si besoin) : Par ex, pour des opérations mathématiques n’utilisez que des nombres
  • Respectez la casse: position des virgules ou point virgules ou deux-points ;
  • Opter pour l’utilisation des fonctions que des opérateurs mathématiques afin de simplifier les calculs et éviter les erreurs ;


L'erreur #NOM?[modifier | modifier le wikicode]

Cette erreur apparaît lorsque la formule utilisée ne reconnaît par le texte contenu au sein de celle-ci.

On distingue plusieurs causes qui peuvent être à l’origine de l’erreur #NOM?


La formule utilise une fonction personnalisée qui n’est pas disponible[modifier | modifier le wikicode]

Il faut vérifier que le classeur contenant la fonction personnalisée est ouvert et que celle-ci fonctionne correctement.

Solution MAC
  • Après avoir,sélectionné la cellule, allez dans l’onglet Formules puis cliquer sur Concepteur de formule
  • Modifiez ensuite la formule dans la section Arguments de la fenêtre Concepteur de formule.


La formule contient une référence à une cellule ou à une plage nommée qui n’est pas définie[modifier | modifier le wikicode]

Il faut vérifier que la cellule ou la plage nommée est correctement définie.

Solution MAC/PC
  • Allez dans le menu Insertion, cliquez sur Nom, puis sur Définir.
  • Vérifiez que la cellule ou la plage est bien dans la liste. Sinon il faut la définir en cliquant sur Ajouter. 


La formule contient un nom de fonction mal orthographié    [modifier | modifier le wikicode]

Il faut modifier la formule pour corriger l’orthographe. 

Solution MAC Solution PC
  • Après avoir sélectionné la cellule, allez dans l’onglet Formules, puis cliquez sur Concepteur de formules
  • Modifiez la formule dans la section Arguments
  • Sélectionnez le nom de la fonction dans la barre de formule « Fx= » puis appuyez sur F3
  • Sélectionnez le nom de la formule souhaité puis cliquez sur OK.


Les deux points (:) sont manquant à la référence d’une plage[modifier | modifier le wikicode]

Il faut vérifier que toutes les références de plage de la formule utilisent les deux points (:).

Par exemple, l’argument =SOMME(A1:C10) 


La référence d’une feuille ou d’un classeur contenue dans une formule contient une erreur (caractère non alphabétique, espace, non utilisation de guillemets simples)    [modifier | modifier le wikicode]

Il faut modifier la formule pour orthographiée correctement la référence du classeur ou de la feuille.    

Solution MAC Solution PC
  • Après avoir sélectionné la cellule, allez dans l’onglet Formules puis cliquer sur Concepteur de formule
  • Modifiez ensuite la formule dans la section Arguments de la fenêtre Concepteur de formule en utilisant des guillemets simples (‘) pour faire référence à la feuille ou au classeur.    
  • Double-cliquez sur la cellule puis vérifier que la référence à la feuille ou au classeur soit correctement orthographié.
  • Il faut notamment vérifier que de part et d’autre de la référence de la feuille ou du classeur il y ait des guillemets simples (' ').


Le texte entré dans une formule n’est pas mis entre double guillemets (« »)    [modifier | modifier le wikicode]

Excel interprète cette entrée comme une référence alors qu’il s’agit en réalité d’un texte. 

Il faut modifier la formule pour placer le texte entre double guillemets « ».

Solution MAC Solution PC
  • Après avoir sélectionné la cellule, allez dans l’onglet Formules, puis cliquez sur Concepteur de formule.
  • Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule en mettant des double guillemet entre le texte tapé.
  • Double-cliquez sur la cellule puis vérifier que le texte tapé soit mis entre double guillemets.


L'erreur #NOMBRE![modifier | modifier le wikicode]

Cette erreur survient lorsqu’une formule ou une fonction contient des valeurs numériques non valides.

On distingue plusieurs causes qui peuvent être à l'origine de l'erreur #NOMBRE!


Un argument non numérique est utilisé dans une fonction qui nécessite un argument numérique[modifier | modifier le wikicode]

Il faut vérifier que les arguments utilisés dans la fonction sont des nombres. 

Solution MAC Solution PC
  • Après avoir sélectionné la cellule, allez dans l’onglet Formules, puis cliquez sur Concepteur de formule
  • Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule
  • Supprimez tous les caractères non numériques de l’argument numérique dans la formule (n’oubliez pas les espaces)    
  • Après avoir sélectionné la cellule, cliquez dans la barre de formule "Fx=" puis appuyez sur F3
  • Supprimez tous les caractères non numériques de l'argument numérique dans la formule (n'oubliez pas les espaces)    


Une fonction qui se réitère, telle que TRI ou TAUX, n’arrive pas à trouver de résultat[modifier | modifier le wikicode]

Il faut modifier le nombre de fois qu’Excel itère les formules.   

Remarque :

Plus le nombre d'itérations est élevé, plus la durée nécessaire à l'exécution des calculs d'une feuille par Excel est importante.    

Solution MAC Solution PC
  • Dans le menu Excel, cliquez sur Préférences.
  • Sous Formules et listes, cliquez sur Calcul Bouton Préférences de calcul puis, sous Itération, activez la case à cocher Limiter l'itération.
  • Dans la zone Itérations maximales, tapez le nombre d'itérations que vous voulez définir comme nombre maximal de fois où Excel effectue de nouveau les calculs.
  • Dans la zone Modification maximale, tapez le montant que vous voulez définir comme montant maximal de modification entre les résultats de calcul. Plus le nombre est petit, plus le résultat est précis et plus la durée nécessaire à l'exécution des calculs d'une feuille par Excel est importante.    
  • Dans le menu Outils, cliquez sur Options, puis sur l’onglet Calcul.
  • Activez la case à cocher Itération.
  • Pour définir le nombre maximal de fois où Excel recalcule, tapez le nombre d’itérations dans la zone Nb maximal d’itérations.
  • Pour définir l’écart maximal entre les résultats du calcul, tapez le chiffre dans la zone Ecart maximal. Plus le nombre est petit, plus le résultat est précis et plus la durée nécessaire à l'exécution des calculs d'une feuille par Excel est importante.    


La formule produit un nombre qui est trop grand ou trop petit pour être représenté dans Excel    [modifier | modifier le wikicode]

Il faut modifier la formule afin que son résultat puisse être représenté dans Excel.

Solution MAC/PC
  • Excel peut représenter des valeurs comprises entre -1 × 10307 et 1 × 10307


L'erreur ######[modifier | modifier le wikicode]

Erreurmm.png

###### est une erreur d'affichage d'une valeur numérique, celle-ci apparaît lorsqu'une colonne est trop étroite pour afficher la totalité d'une donnée numérique.

Afin de corriger cette erreur, il suffit tout simplement d'élargir la colonne dans laquelle apparaît l'erreur.


Vidéo[modifier | modifier le wikicode]

Pour vous aider dans cette démarche voici une vidéo de tutorat qui vous permettra de mieux comprendre la modification à effectuer.

https://www.youtube.com/watch?v=mBu4V5jmOPY&feature=share

  • Remarque :

Que ce soit sur MAC ou bien PC la solution reste la même.


L'erreur #N/A ou NA()[modifier | modifier le wikicode]

L'erreur #N/A ou NA() survient lorsqu'une donnée nécessaire au bon fonctionnement de la formule est manquante.


Les causes :[modifier | modifier le wikicode]

Les causes de cette erreur sont nombreuses et diverses, celle-ci apparaît donc lorsque :

  • Les cellules référencées dans la formule contiennent déjà des données erronées #N/A ou NA().
  • Une erreur lors de la sélection de la plage de cellules, celles-ci n'étant pas figée, ou bien lorsque la valeur_cherchée dans une ligne ou colonne est mal triée pour les fonctions RECHERCHEV, RECHERCHEH ou INDEX.



  • La dimension des plages de cellules sélectionnées dans la formule ne sont pas les mêmes..

Exemple : Dans une formule =SOMMEPROD((A1:A6)*(A1:A5)) ici la matrice renvoie une erreur #N/A car les plages de cellules sélectionnées ne sont pas de même dimension. Afin de la corriger, il faut sélectionner la même plage de cellules donc soit de A1:A5 ou de A1:A6 dans notre exemple.

Solution MAC Solution PC
  • Dans l'onglet Formules, dans Fonctions, cliquez sur Concepteur de formule
  • Modifier la formule dans la section Arguments de la fenêtre Concepteur de formule
  • Dans l'onglet Formules, dans Vérification des formules, cliquez sur Vérification des erreurs
  • Cliquez sur Aide sur cette erreur et suivez les indications


Vidéo[modifier | modifier le wikicode]

Pour vous aider dans cette démarche voici une vidéo de tutorat qui vous permettra de mieux comprendre la modification à effectuer.

https://www.youtube.com/watch?v=YGXMoEt363A&feature=share


L'erreur #NULL![modifier | modifier le wikicode]

Causes:[modifier | modifier le wikicode]

  • Intersection de plages de cellules n'étant pas adjacentes
  • Séparateur non défini entre les rangs


Solutions:[modifier | modifier le wikicode]

  • Il fait bien vérifier que les deux points (:) séparent la première cellule de la plage sélectionnée, de la dernière.

Par exemple: C1:C12 comprend la plage de cellules allant de la C1 à la C12

  • Si deux zone de se coupent pas, on peut utiliser l'opérateur d'union (;)

Par exemple: Si on fait la somme entre deux plages de cellules: =SOMME(D3:D6;C3:C6)


Exemple:[modifier | modifier le wikicode]

Si on entre la formule suivante: SOMME(D3:D6 C3:C6), #NUL! est renvoyé car il n'y a pas de séparateur entre les rangs 1 et 2. Afin de corriger cette erreur il faut placer un point-virgule (;) entre les deux plages sélectionnés.

Exemple d'erreur #NULL! :

exemple d'erreur #null!

Exemple de solution pour #NULL!:

erreur #null! solution


L'erreur #DIV/O![modifier | modifier le wikicode]

Causes:[modifier | modifier le wikicode]

L'erreur #DIV/0! survient lorsqu'un nombre est divisé par zéro (0), comme pour la division simple = 23÷0.

Dans le cas d'une formule, elle survient lorsqu'on fait référence a une cellule contenant 0 ou une cellule vide.


Solutions:[modifier | modifier le wikicode]

Afin de rectifier cette erreur il faut vérifier que:

  • le diviseur de la formule n'est ni zéro (0), ni une cellule vide
  • modifier la référence de la cellule dans la formule de façon a ce qu'elle ne soit ni vide, ni contenant la valeur zéro (0)
  • entrer #N/A dans la cellule non référencée comme diviseur, ce qui remplacera le résultat de la formule par #N/A, signifiant que le diviseur est indisponible

Si l'erreur persiste, on peut utiliser la formule SI afin d'interrompre l’affichage de cette erreur. La formule SI va afficher 0 ou cellule vide au lieu de l'erreur #DIV/0.

Par exemple: Si nous avons l'opération =L1/L2

  • SI(L2=0;0;L1/L2) pour faire afficher zéro (0)
erreur #Div/0!


  • SI(L2=0;" ";L1/L2) pour faire afficher une valeur vide
Erreur #DIV/0! bis


Les erreurs de référence circulaire[modifier | modifier le wikicode]

Lorsque l’un des classeurs excel est ouvert et s’il contient une référence circulaire, un message d’avertissement de référence circulaire s’affiche.

Exemple d’une « référence circulaire » : formule dans C3 = C3+1.

L’implication de la cellule elle-même dans la formule de la cellule implique une répétition infinie à Excel et donc Excel arrête le processus et nous affiche un avertissement pour éviter une « boucle sans fin ».


Comment rechercher et supprimer des références circulaires ?[modifier | modifier le wikicode]

En général, vous pouvez rechercher et supprimez la référence circulaire. Mais sinon, excel peut également calculer une fois les cellules de la référence circulaire, ou il arrête automatiquement le calcul après 100 itérations ou bien lorsque l’écart qui existe entre les valeurs de la référence circulaire est supérieur à 0.001 entre les itérations.

Pour rechercher et supprimer une référence circulaire, vous pouvez vous aider du bouton Vérification des erreurs qui se trouve dans la section Audit de formules de l’onglet formules.  Puis en cliquant sur références circulaires, vous pouvez cliquez sur la première cellule répertoriée dans le sous-menu. Ensuite, Il y a deux cas possibles :

Cas 1 Soit vous pouvez déterminer la formule de la cellule et savoir si elle est la cause d’une référence circulaire
Cas 2 Soit vous ne pouvez pas savoir et il est nécessaire de cliquer sur la cellule suivante dans le sous-menu « références circulaires »




Pour supprimer une référence circulaire, il faut continuer à regarder toutes les cellules afin de corriger la référence circulaire jusqu'à ce que la barre d'état n'affiche plus le mot «Circulaire»


Comment autoriser et utiliser une référence circulaire ?[modifier | modifier le wikicode]

Par défaut, les références circulaires sont indisponibles dans Excel et c’est pour cela que nous avons précédemment expliqué comment les trouver et les supprimer.

Pourtant, il est possible d’autoriser et d’utiliser ces références circulaires tout en les paramétrant.

Dans l’onglet Fichier, puis options, options Excel cliquez sur le menu Formules. Ici, nous voulons autoriser l’itération des références circulaires donc il faut cocher la case « Activer le calcul itératif ». Cette case nous permet de paramétrer le nombre maximal d’itérations et l’écart maximal entre les itérations. Par défaut, Excel met 100 itérations maximales pour un écart maximal de 0,001. Selon vos besoins, vous pouvez appliquez l’itération 1 fois, 2 fois … jusqu’à 100.


Photo dos erreur excel