Comprendre l'erreur #N/B dans Excel
L'erreur #N/B dans Excel indique que la fonction ou la formule que vous utilisez n'a pas pu trouver la valeur qu'elle était censée trouver. C'est un signal d'alarme qui vous indique qu'il y a un problème avec vos données, votre formule, ou la manière dont vous les utilisez. Avant de paniquer, il est important de comprendre les causes les plus courantes de cette erreur.
Les causes les plus fréquentes de l'erreur #N/B
Voici une liste des raisons les plus courantes pour lesquelles vous pourriez rencontrer l'erreur #N/B :
- Valeur recherchée introuvable : C'est la cause la plus fréquente. Vous utilisez une fonction de recherche (comme RECHERCHEV, RECHERCHEH, INDEX/EQUIV) et la valeur que vous recherchez n'existe pas dans la plage de recherche.
- Erreur de frappe : Une simple faute de frappe dans la valeur recherchée ou dans les données de la plage de recherche peut entraîner une erreur #N/B. Vérifiez attentivement l'orthographe et la casse.
- Espaces superflus : Les espaces avant ou après une valeur peuvent rendre la recherche infructueuse. Excel considère "Pomme" et " Pomme" comme deux valeurs différentes.
- Types de données incompatibles : Si vous essayez de rechercher un nombre dans une colonne de texte ou vice versa, vous obtiendrez une erreur #N/B. Assurez-vous que les types de données correspondent.
- Référence de cellule incorrecte : Une référence de cellule incorrecte dans votre formule peut entraîner une recherche dans une plage de données erronée, ce qui aboutit à une erreur #N/B.
- Plage de recherche incorrecte : La plage de recherche spécifiée dans votre formule peut être trop petite ou mal définie, ce qui empêche la fonction de trouver la valeur recherchée.
- Données manquantes : Si la valeur que vous recherchez est censée exister mais a été supprimée ou n'a jamais été saisie, vous obtiendrez une erreur #N/B.
- Formule incorrecte : Bien que moins fréquente, une erreur dans la syntaxe de votre formule peut également provoquer une erreur #N/B.
Comment corriger l'erreur #N/B : Méthodes et Exemples
Maintenant que nous connaissons les causes potentielles, voyons comment corriger l'erreur #N/B étape par étape.
1. Vérification de la valeur recherchée
La première étape consiste à vérifier attentivement la valeur que vous recherchez. Assurez-vous qu'elle est correctement orthographiée, qu'elle ne contient pas d'espaces superflus et qu'elle correspond au type de données de la colonne de recherche.
Exemple :
Supposons que vous utilisiez la fonction RECHERCHEV pour trouver le prix d'un produit à partir de son code. La formule est la suivante :
=RECHERCHEV(A2;B2:C10;2;FAUX)
Où : * A2 contient le code du produit à rechercher. * B2:C10 est la plage de recherche contenant les codes produits (colonne B) et les prix (colonne C). * 2 indique que vous voulez récupérer la valeur de la deuxième colonne de la plage de recherche (le prix). * FAUX indique que vous voulez une correspondance exacte.
Si la cellule A2 contient le code "ABC123", vérifiez que ce code existe bien dans la colonne B (B2:B10). Si vous trouvez "ABC123 " (avec un espace à la fin) ou "abc123" (avec des minuscules), vous devez corriger la valeur dans A2 ou dans la colonne B.
2. Vérification de la plage de recherche
Assurez-vous que la plage de recherche spécifiée dans votre formule est correcte et qu'elle contient toutes les données nécessaires. Vérifiez également que la colonne contenant la valeur à renvoyer est bien incluse dans la plage de recherche.
Exemple (suite) :
Dans l'exemple précédent, vérifiez que la plage B2:C10 couvre bien toutes les lignes contenant des codes produits et des prix. Si vous avez ajouté de nouveaux produits après la ligne 10, vous devrez modifier la plage en B2:C[numéro de la dernière ligne].
3. Utilisation de la fonction ESTERREUR ou SIERREUR
Pour masquer l'erreur #N/B et afficher un message plus convivial, vous pouvez utiliser les fonctions ESTERREUR ou SIERREUR. SIERREUR est plus simple d'utilisation.
Exemple :
=SIERREUR(RECHERCHEV(A2;B2:C10;2;FAUX);"Produit non trouvé")
Cette formule affiche "Produit non trouvé" si la fonction RECHERCHEV renvoie une erreur #N/B.
4. Vérification des types de données
Vérifiez que les types de données de la valeur recherchée et de la colonne de recherche sont compatibles. Si vous recherchez un nombre, assurez-vous que la colonne de recherche contient des nombres et non du texte.
Exemple :
Si la colonne B (codes produits) est formatée en texte et que la cellule A2 (code produit à rechercher) est formatée en nombre, Excel ne trouvera pas de correspondance. Sélectionnez la colonne B et formatez-la en nombre (onglet Accueil > Nombre > Nombre).
5. Utilisation de la fonction SUPPRESPACE
Pour supprimer les espaces superflus avant ou après une valeur, utilisez la fonction SUPPRESPACE.
Exemple :
=RECHERCHEV(SUPPRESPACE(A2);B2:C10;2;FAUX)
Cette formule supprime les espaces superflus de la cellule A2 avant d'effectuer la recherche.
Vous pouvez également nettoyer la colonne B en utilisant une colonne auxiliaire : en colonne D, vous mettez la formule =SUPPRESPACE(B2) et vous recopiez vers le bas. Ensuite, vous faites un copier/coller spécial "Valeurs" de la colonne D vers la colonne B, puis vous supprimez la colonne D.
6. Utilisation de la correspondance approximative (à éviter si possible)
Si vous utilisez la correspondance approximative (dernier argument de RECHERCHEV à VRAI), assurez-vous que la colonne de recherche est triée par ordre croissant. La correspondance approximative peut renvoyer des résultats inattendus si la colonne n'est pas triée.
Important : La correspondance approximative est déconseillée dans la plupart des cas, car elle peut renvoyer des résultats erronés si la valeur recherchée n'existe pas exactement. Préférez la correspondance exacte (FAUX) et corrigez les erreurs de données.
7. Utilisation de INDEX et EQUIV au lieu de RECHERCHEV
Bien que RECHERCHEV soit populaire, INDEX et EQUIV offrent plus de flexibilité et peuvent être plus performantes dans certains cas. De plus, INDEX/EQUIV est moins sensible aux insertions/suppressions de colonnes.
Exemple :
=INDEX(C2:C10;EQUIV(A2;B2:B10;0))
Cette formule fait la même chose que =RECHERCHEV(A2;B2:C10;2;FAUX).
8. Vérification des références de cellules
Vérifiez que les références de cellules dans votre formule sont correctes et qu'elles pointent vers les bonnes plages de données.
Exemple :
Si vous copiez une formule vers le bas, assurez-vous que les références de cellules absolues (avec le signe $) sont utilisées correctement pour éviter que les plages de recherche ne se décalent.
9. Gestion des données manquantes
Si vous savez que certaines valeurs peuvent être manquantes, vous pouvez utiliser la fonction SI.NON.VIDE ou la fonction SIERREUR pour afficher une valeur par défaut ou un message spécifique.
Exemple :
=SI.NON.VIDE(RECHERCHEV(A2;B2:C10;2;FAUX);"Valeur manquante")
Cette formule affiche "Valeur manquante" si la cellule renvoyée par RECHERCHEV est vide.
Bonnes pratiques pour éviter l'erreur #N/B
Voici quelques conseils pour éviter de rencontrer l'erreur #N/B à l'avenir :
- Validez vos données : Utilisez la validation des données pour limiter les valeurs que les utilisateurs peuvent saisir dans les cellules, ce qui réduit les risques d'erreurs de frappe et de données incorrectes.
- Nettoyez vos données régulièrement : Utilisez les fonctions SUPPRESPACE, MAJUSCULE, MINUSCULE et NOMPROPRE pour uniformiser vos données et supprimer les incohérences.
- Utilisez des noms de plages : Au lieu d'utiliser des références de cellules, utilisez des noms de plages pour rendre vos formules plus lisibles et moins sujettes aux erreurs.
- Testez vos formules : Avant de déployer une formule à grande échelle, testez-la avec différentes valeurs pour vous assurer qu'elle fonctionne correctement.
- Documentez vos formules : Ajoutez des commentaires à vos formules pour expliquer leur fonctionnement et faciliter la maintenance.
Alternatives à la correction de l'erreur #N/B
Bien que la correction de l'erreur #N/B soit la solution la plus courante, il existe des alternatives, notamment l'utilisation de Power Query pour gérer les données manquantes ou l'utilisation de macros VBA pour automatiser la recherche et la correction des erreurs.
Utilisation de Power Query
Power Query peut être utilisé pour fusionner des données de différentes sources et gérer les valeurs manquantes. Vous pouvez utiliser Power Query pour remplacer les valeurs manquantes par une valeur par défaut ou pour supprimer les lignes contenant des valeurs manquantes.
Utilisation de macros VBA
Les macros VBA peuvent être utilisées pour automatiser la recherche et la correction des erreurs #N/B. Vous pouvez créer une macro qui parcourt toutes les cellules contenant des formules et remplace les erreurs #N/B par une valeur par défaut ou un message spécifique.
Conclusion
L'erreur #N/B peut être frustrante, mais elle est rarement insurmontable. En comprenant les causes potentielles et en suivant les étapes de dépannage décrites dans cet article, vous pouvez rapidement identifier et corriger l'erreur. N'oubliez pas de valider vos données, de nettoyer vos données régulièrement et de tester vos formules avant de les déployer à grande échelle. Avec un peu de patience et de méthode, vous pouvez maîtriser l'erreur #N/B et optimiser vos feuilles de calcul Excel.