Tableur EXCEL/Erreurs
Il existe différents types d'erreurs sur Excel. Nous traiterons ici les 8 erreurs les plus communes d'Excel ainsi que celle de la référence circulaire qui peuvent se résoudre de manières différentes en fonction de l'environnement de travail (PC ou Mac).
Certaines cellules contiennent des formules qui génèrent une erreur.
En général, c’est que la formule n’utilise pas la syntaxe définie. C’est-à-dire que les arguments ou les types de données attendus ne sont pas les bons ou ne correspondent pas. Ces valeurs d’erreur comprennent #REF!, #VALEUR!, #NOM?, #NOMBRE!, ######, #N/A, #NULL!, #DIV/0!, et sont détectables par un triangle vert qui s’affiche dans le coin supérieur gauche de la cellule.
Un autre type d’erreur peut être la référence circulaire : la formule fait, directement ou indirectement, référence à sa propre valeur. Lorsqu’une cellule affiche un code d’erreur, toutes les cellules qui utilisent son résultat affichent en cascade le même code d’erreur. Une seule formule erronée peut ainsi déclencher des centaines de codes d’erreur au sein des cellules qui dépendent de son résultat. Aussi pour trouver l’origine d’une erreur, nous pouvons utiliser les commandes de l’onglet Formules du ruban présentes au sein de la section nommée Audit de formules.
Ces valeurs peuvent être corrigées une par une dans les formules. Il est possible d’utiliser certaines règles pour détecter les erreurs dans les formules, comme le correcteur orthographique mais qui cherche à trouver les erreurs des données entrées dans les cellules. Ceci permet de localiser les erreurs les plus courantes, et elles peuvent être corrigées de deux façons différentes soit au fur et à mesure de leur apparition soit l’une après l’autre.
Même si lors de la vérification des erreurs dans la feuille de calcul certaines erreurs ont pu être ignorées.
| PC | MAC | ||
|---|---|---|---|
| Vérification des erreurs |
|
 |
Lorsque la vérification des erreurs est activée dans Excel, grâce à l’info-bulle de la cellule affichant l’erreur, vous pouvez cliquez sur « Afficher les étapes du calcul » permettant ainsi de choisir la résolution adaptée à votre demande.
Cette aide permet donc d'afficher les étapes du calcul, et de nous aider avec le mode "pas à pas" pour situer à quel moment précis l'erreur est faite
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
|
|---|---|
| Solution 2
MAC |
Modifiez la formule pour supprimer 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.
|
| Solution 2bis
PC |
Modifiez la formule pour supprimer 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.
|
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.
 |
Vérifiez que les dimensions de la matrice sont appropriées aux arguments de la fonction matricielle.
|
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.
|
|---|
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.
|
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.
sur Concepteur de formule. |
Modifiez la formule pour faire 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) |
|
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 |
|
|---|
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 |
|
|---|
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 |
|---|---|
|
|
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 |
|---|---|
|
|
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 |
|---|---|
|
|
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 |
|---|---|
|
|
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 |
|---|---|
|
|
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 |
|
|---|
L'erreur ######
[modifier | modifier le wikicode]
###### 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.
Si dans le dernier argument de la fonction la valeur VRAI est choisi alors l'erreur concernant les lignes et colonnes non triées prendra effet, cependant si la valeur FAUX est choisi alors la recherche dans une ligne ou colonne non triée ne fera pas apparaître d'erreur.
-> Pour figer vos cellules il suffit de cliquer sur F4 ou bien d'insérer des dollars ($) dans la plage de données sélectionnées.
- 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 |
|---|---|
|
|
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 de solution pour #NULL!:
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)
- SI(L2=0;" ";L1/L2) pour faire afficher une valeur vide
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 » |
Si la barre d’état affiche le mot « circulaire » suivi d’une référence à l’une des cellules contenues dans la référence circulaire. Sinon s’il n’y a pas de référence à une cellule, la feuille excel ne contient pas de référence circulaire.
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.
