Sommaire
3.2 Erreurs #N/A, #VALEUR!, #DIV/0!, #CALC! : comprendre et corriger
Un message d'erreur n'est pas une panne : c'est Excel qui indique précisément ce qui ne va pas. Chaque code a une cause typique, et dans un fichier RH ou d'atelier les mêmes situations reviennent sans cesse. Ce chapitre les classe, du plus fréquent au plus rare, avec le réflexe de correction.
Huit codes, huit causes typiques
#N/A
Valeur cherchée introuvable
#VALEUR!
Type de donnée inattendu (texte au lieu d'une date…)
#DIV/0!
Division par zéro ou par une cellule vide
#REF!
Cellule référencée supprimée ou remplacée
#NOM?
Nom de fonction ou de plage mal tapé, texte sans guillemets
#CALC!
FILTRE sans résultat et sans argument si_vide
#NOMBRE!
Argument numérique hors des valeurs admises
#####
Colonne trop étroite (pas une erreur de calcul)
#N/A : la recherche n'a rien trouvé
#N/A est le code des fonctions de recherche : RECHERCHEV avec FAUX, EQUIV avec le type 0 et RECHERCHEX sans argument si_non_trouvé le renvoient quand la valeur cherchée est absente. Avant de le masquer, il faut savoir pourquoi elle est absente.
| Cause | Comment la repérer | Correction |
|---|---|---|
| Le matricule n'existe pas dans la liste de référence | Recherche manuelle (filtre) dans tPersonnel | Ajouter la personne, ou corriger la saisie |
| Nombre d'un côté, texte de l'autre | Alignement différent des deux valeurs, format de cellule | Harmoniser le format à la source |
| Espace parasite en fin de valeur | F2 sur la cellule : le curseur s'arrête après une espace | Nettoyer la colonne importée |
| Plage de recherche trop courte | La formule pointe A2:A50 alors que la liste a grandi | Utiliser un tableau et des références structurées |
Une fois la cause comprise, un #N/A légitime (une personne arrivée hier, pas encore saisie dans la liste) peut s'afficher proprement : argument si_non_trouvé de RECHERCHEX, ou SIERREUR autour d'INDEX/EQUIV, avec un message explicite comme « Matricule inconnu » plutôt qu'une cellule vide qui passerait inaperçue.
#VALEUR! : un type de donnée inattendu
#VALEUR! apparaît quand une fonction reçoit une donnée qu'elle ne sait pas traiter. Dans les fichiers de ce parcours, trois situations dominent :
- Date stockée en texte : NB.JOURS.OUVRES renvoie
#VALEUR!si un argument n'est pas une date valide. Cas typique : une colonne de dates collée depuis un export ou un courriel. - Plages de tailles différentes dans SOMME.SI.ENS ou NB.SI.ENS : la plage additionnée et les plages de critères doivent avoir la même taille. L'éditeur consacre une page entière à ce cas.
- Chaîne de week-end invalide dans NB.JOURS.OUVRES.INTL : autre chose que sept caractères 0 ou 1.
#DIV/0! et #####, les deux faux drames
#DIV/0! signale une division par zéro, ou par une cellule vide. Dans un tableau de bord, c'est souvent normal en début de période : un taux dont le dénominateur (heures théoriques, effectif) n'est pas encore renseigné.
Cette écriture vaut mieux qu'un SIERREUR général : elle ne traite que le cas prévu (dénominateur nul) et laisse apparaître toute autre erreur.
##### n'est pas une erreur de calcul : la colonne est trop étroite pour afficher le contenu, très souvent une date ou un nombre au format long. Il suffit d'élargir la colonne.
#REF! et #NOM? : la formule elle-même est cassée
Ces deux codes ne parlent pas des données mais de la formule. Les masquer avec SIERREUR serait une faute : le calcul est faux pour toutes les lignes.
#REF! — une référence n'existe plus
Selon la documentation de l'éditeur, l'erreur se produit le plus souvent quand des cellules référencées par des formules sont supprimées ou remplacées : une colonne supprimée dans la liste du personnel, des cellules déplacées. Elle apparaît aussi quand un numéro de ligne ou de colonne demandé à RECHERCHEV ou INDEX dépasse la plage.
Correction : Ctrl+Z si la suppression vient d'avoir lieu ; sinon, réécrire la référence. Prévention : les références structurées d'un tableau et les fonctions qui désignent la colonne renvoyée par son nom (RECHERCHEX, INDEX/EQUIV) résistent mieux aux modifications de structure.
#NOM? — Excel ne reconnaît pas un nom
- faute de frappe dans le nom de la fonction (
=NB.SI.EN(…)) ou d'un nom de plage ; - texte sans guillemets dans un critère (
Montageau lieu de"Montage") ; - deux-points manquant dans une plage (
A2A50) ; - fonction inconnue de la version installée : une formule écrite avec une fonction récente ne fonctionne pas sur un poste qui ne la propose pas (RECHERCHEX, FILTRE et UNIQUE sont absentes d'Excel 2016 et 2019).
#CALC! et #NOMBRE! : les erreurs des fonctions récentes et des arguments
#CALC! est propre aux fonctions de tableau dynamique. Le cas le plus courant : FILTRE ne trouve aucune ligne et l'argument si_vide a été omis. L'éditeur l'explique par le fait qu'Excel ne gère pas encore les tableaux vides. Correction : =FILTRE(…;…;"Aucune ligne").
#NOMBRE! signale un argument numérique hors des valeurs admises. Deux exemples documentés dans les fonctions de ce parcours :
- JOURSEM avec un type_retour qui n'existe pas (par exemple 4) ;
- NB.JOURS.OUVRES.INTL avec une date hors de la plage prise en charge par Excel, souvent le signe d'une saisie aberrante (année à deux chiffres mal interprétée, date en texte convertie de travers).
Les erreurs silencieuses, plus dangereuses que les messages
Les erreurs les plus coûteuses n'affichent aucun code : la formule renvoie un résultat plausible, mais faux. Celles qui reviennent le plus dans les fichiers RH et d'atelier :
| Erreur silencieuse | Conséquence | Parade |
|---|---|---|
| RECHERCHEV sans 4e argument | Correspondance approximative : le service d'une autre personne | Toujours FAUX, ou RECHERCHEX |
| Arguments de SOMME.SI recopiés dans SOMME.SI.ENS | Total faux, ou #VALEUR! | Plage à additionner en premier |
| Critère pointant sur une cellule vide | NB.SI.ENS la traite comme 0 | Contrôler que la cellule de paramètre est remplie |
Plage écrite en dur (F2:F50) | Les nouvelles lignes ne sont pas comptées | Tableau et références structurées |
| Tableau croisé non actualisé | Chiffres du mois précédent présentés comme actuels | Ctrl+Alt+F5 avant diffusion |
| SIERREUR posé trop tôt | Une vraie erreur (#REF!) devient une cellule vide | Vérifier la formule avant de l'envelopper |
Le meilleur garde-fou reste le contrôle croisé : vérifier à la main une ou deux lignes après chaque nouvelle formule, et comparer un total obtenu par deux chemins (formule NB.SI.ENS et tableau croisé, par exemple). Deux résultats identiques par deux méthodes différentes valent toutes les relectures.
Diagnostiquer une erreur en trois questions
Sources
- Documentation de l'éditeur : « Comment corriger une erreur #REF! », « Comment corriger une erreur #NOM? », « Corriger une erreur #N/A », « Correction d'une erreur #VALEUR! dans la fonction SOMME.SI/SOMME.SI.ENS », « Comment corriger un #DIV/0! erreur », « Comment corriger une erreur ##### », « Comment corriger un #CALC! erreur » ; fonctions SIERREUR, JOURSEM, NB.JOURS.OUVRES(.INTL), FILTRE — support.microsoft.com
- Formation indépendante, non affiliée à Microsoft. Excel est une marque déposée de son éditeur.
À retenir
- #N/A : valeur cherchée absente ; vérifier format, espaces et plage avant de masquer.
- #VALEUR! : souvent une date en texte ou des plages de tailles différentes.
- #DIV/0! se traite par
SI(B2=0;"";…); ##### = colonne trop étroite. - #REF! et #NOM? : la formule est cassée, on la corrige, on ne la masque jamais.
- #CALC! : FILTRE sans résultat et sans si_vide ; #NOMBRE! : argument hors limites.
- Les erreurs silencieuses se détectent par contrôle croisé de deux méthodes.