Tableur EXCEL/Erreurs

**Erreurs**
Leçon : Tableur EXCEL

Chapitre n^o 13
Chap. préc. :	PowerPivot
Chap. suiv. :	Analyse de scénario

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.

Les erreurs EXCEL

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.

Vérification des erreurs

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	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

Information supplémentaire pour l’utilisation d'Excel sur PC

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!

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

Solution 1 MAC/PC	Annulez vos modifications pour rétablir les cellules à leur état d’origine 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 Sélectionnez la cellule Dans l’onglet Formules, dans Fonctions, cliquez sur Concepteur de formule 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. Sélectionnez la cellule Dans l’onglet Formules, dans Fonctions, cliquez sur Concepteur de formule 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 Sélectionnez la cellule 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. Sélectionnez la cellule 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

Solution MAC	Solution PC
Vérifiez que les dimensions de la matrice sont appropriées aux arguments de la fonction matricielle. Sélectionnez la cellule Dans l’onglet Formules, dans Fonctions, cliquez sur Concepteur de formule Modifiez la formule dans la section Arguments de la fenêtre Concepteur de formule	Vérifiez que les dimensions de la matrice sont appropriées aux arguments de la fonction matricielle. Sélectionnez la cellule Dans l’onglet Formules, dans Fonctions, cliquez sur insérer une fonction insérer une fonction Modifiez la formule selon les arguments demandés pour la fonction choisie argument fonction somme.si.ens

L'erreur #VALEUR!

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

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

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

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

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

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?

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

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

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é

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

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)

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 (« »)

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!

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

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

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

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 × 10³⁰⁷ et 1 × 10³⁰⁷

L'erreur ######

###### 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

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

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

Les causes :

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.

Remarque

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
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

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!

Causes:

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

Solutions:

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:

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!

Causes:

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:

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

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 ?

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 »

Remarque

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 ?

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

Tableur EXCEL

PowerPivot

Analyse de scénario