Comment remplacer les erreurs de formule # par 0, une cellule vide ou un texte spécifique dans Excel ?

Les utilisateurs d'Excel rencontrent fréquemment des erreurs de formule telles que #DIV/0!, #VALEUR!, #REF!, #N/A, #NUM!, #NOM?, et #NULL! dans leurs tableaux de données ou résultats de calcul. Ces valeurs d'erreur n'affectent pas seulement la lisibilité des rapports, mais peuvent également impacter le traitement ultérieur des données, l'analyse et le partage. Souvent, pour une meilleure présentation des données ou pour la logique de calcul en aval, il devient nécessaire de remplacer toutes les erreurs ou certains types d'erreurs dans les feuilles de calcul par 0 (zéro), une cellule vide, ou une chaîne de texte spécifiée qui est plus facile à comprendre pour les autres.

Cet article fournit des solutions pratiques et faciles à suivre pour rechercher et remplacer les erreurs de formule # dans les cellules Excel. En utilisant un tableau représentatif ci-dessous, nous démontrerons comment remplacer efficacement ces valeurs d'erreur selon vos besoins et votre flux de travail.

Remplacer les erreurs de formule par 0, des valeurs spécifiques ou des cellules vides

• En utilisant la fonction SI.ERREUR
• En utilisant la fonction TYPE.ERREUR
• En utilisant la commande Atteindre
• En utilisant Kutools pour Excel
• En utilisant le code VBA

Remplacer les erreurs de formule # par 0, des valeurs spécifiques ou des cellules vides avec SI.ERREUR

Excel propose la fonction SI.ERREUR qui est spécialement conçue pour intercepter tous les types d'erreurs courants et vous permettre de les remplacer par n'importe quelle valeur ou message de votre choix. Cela peut simplifier la gestion des erreurs pendant les calculs et améliorer la clarté de la feuille de calcul.
Pour l'utiliser, entrez =SI.ERREUR(valeur; valeur_si_erreur) dans une cellule correspondante. Si valeur est une erreur, elle retournera votre valeur_si_erreurspécifiée; si valeur n'est pas une erreur, elle retourne simplement le résultat calculé.

Dans l'exemple ci-dessus, différents types d'erreurs de formule tels que #N/A ont été remplacés par une cellule vide, un 0 numérique, ou un message de texte personnalisé. Vous pouvez ajuster valeur_si_erreur selon vos besoins - comme illustré ci-dessous, entrez des valeurs réelles, une chaîne vide ("") pour une cellule vide, ou du texte descriptif si nécessaire :

Cette approche convient particulièrement lorsque vous créez des formules et souhaitez vous assurer que les valeurs d'erreur ne sont pas affichées dans vos tableaux, rapports, tableaux de bord finaux, ou lors du transfert de données à d'autres. Un conseil pratique consiste à envelopper tout calcul complexe ou instable dans SI.ERREUR pour préserver la continuité de la feuille de calcul.
Gardez à l'esprit que si vous souhaitez traiter uniquement certains types d'erreurs (comme uniquement #N/A), envisagez d'utiliser SI.NA ou de combiner SI et ESTERR/ESTERREUR pour un traitement plus ciblé. Assurez-vous également de copier votre formule vers toutes les cellules pertinentes pour couvrir l'ensemble du jeu de données.

Remplacer les erreurs de formule # par des nombres spécifiques avec TYPE.ERREUR

La fonction TYPE.ERREUR est une autre fonctionnalité intégrée d'Excel, qui peut être utilisée pour identifier différentes valeurs d'erreur en retournant un numéro unique correspondant à chaque type d'erreur. Cela est particulièrement utile lorsque vous souhaitez distinguer entre les types d'erreurs pour une logique conditionnelle supplémentaire dans vos formules.
Dans l'exemple suivant, l'utilisation de TYPE.ERREUR dans une cellule vide à côté de l'erreur de formule retourne un code (de 1 à 8).

Utilisation du Poignée de Remplissage vous permet d'appliquer la formule TYPE.ERREUR sur une plage. Cependant, notez que TYPE.ERREUR est principalement utilisé pour analyser ou mapper les types d'erreurs, pas pour les remplacer directement. Vous devez généralement la combiner avec SI ou CHOISIR pour afficher des substitutions plus conviviales. De plus, mémoriser chaque code d'erreur peut nécessiter de se référer à la documentation ou au tableau ci-dessus.

Si votre scénario nécessite des remplacements personnalisés basés sur le type d'erreur, vous pouvez imbriquer TYPE.ERREUR dans une formule SI ou CHOISIR pour afficher des informations pertinentes pour chaque condition d'erreur.

Rechercher et remplacer les erreurs de formule # par 0, des valeurs spécifiques ou des cellules vides avec la commande Atteindre

Cette méthode convient aux utilisateurs souhaitant traiter par lots et remplacer directement les cellules d'erreur dans une zone existante, surtout après que les calculs soient finalisés. En utilisant la commande Atteindre Spécial d'Excel, vous pouvez localiser toutes les cellules contenant des erreurs dans une sélection et les remplacer en une fois.

1. Tout d'abord, sélectionnez la plage de la feuille de calcul contenant des erreurs potentielles de formule.

2. Appuyez sur F5 de votre clavier (ou Ctrl + G) pour faire apparaître la boîte de dialogue Atteindre.

3. Cliquez sur Spécial pour ouvrir la boîte des options Atteindre Spécial.

4. Choisissez uniquement l'option Formules, et assurez-vous que seul Erreurs est coché. Cette action ciblera toutes les cellules affichant des résultats d'erreur dans votre plage sélectionnée.

5. Cliquez sur OK, et Excel mettra automatiquement en surbrillance toutes ces cellules d'erreur.

6. Tapez directement 0, ou la valeur de remplacement choisie, et utilisez Ctrl + Entrée afin qu'Excel remplisse toutes les cellules d'erreur sélectionnées avec la valeur. 

Si vous voulez effacer complètement ces cellules d'erreur, il suffit d'appuyer sur la touche Suppr après la sélection pour laisser ces cellules vides.

Rechercher et remplacer les erreurs de formule # par 0, des valeurs spécifiques ou des cellules vides avec Kutools pour Excel

L'Assistant de Mise en forme des Conditions d'Erreur de Kutools pour Excel rationalise le processus de gestion des valeurs d'erreur. Avec cet outil, les utilisateurs peuvent remplacer de manière flexible toutes les erreurs ou certains types d'erreurs spécifiques par 0, des cellules vides, ou des messages personnalisés pour la présentation ou l'édition en aval. Cela est particulièrement pratique pour les non-experts en formules ou les utilisateurs manipulant de grands ensembles de données complexes.

1. Commencez par sélectionner la plage où vous souhaitez remplacer les valeurs d'erreur. Accédez ensuite au menu et cliquez sur Kutools > Plus > Assistant de Mise en forme des Conditions d'Erreur.

2. Dans la boîte de dialogue de l'Assistant de Mise en forme des Conditions d'Erreur, configurez vos préférences comme suit :

(1) Sous Types d'erreurs, choisissez d'appliquer l'action à Toutes les valeurs d'erreur, Uniquement la valeur d'erreur #N/A, ou Toutes les valeurs d'erreur sauf #N/A. Choisissez selon votre scénario.

(2) Dans la section Affichage des erreurs, sélectionnez Rien (cellule vide) si vous souhaitez afficher les erreurs comme étant vides.

Pour remplacer les erreurs par zéro ou un message, sélectionnez Message (texte) et entrez "0" ou un texte personnalisé dans le champ.

(3) Cliquez sur OK pour appliquer les modifications.

L'utilitaire traitera instantanément votre sélection, en remplaçant les valeurs d'erreur dans toute la zone selon votre configuration. Voici les résultats visuels ci-dessous :

Remplacer toutes les valeurs d'erreur par une cellule vide

Remplacer toutes les valeurs d'erreur par zéro

Remplacer toutes les valeurs d'erreur par un certain texte

L'Assistant de Mise en forme des Conditions d'Erreur dans Kutools pour Excel est très pratique pour les tâches de nettoyage répétitives. Vous pouvez également annuler rapidement (Ctrl + Z) les modifications si nécessaire. Passez toujours en revue votre sélection avant d'appliquer des opérations en masse, surtout sur de grands ensembles de données.

Remplacer toutes les valeurs d'erreur par 0, vide ou texte spécifié via le code VBA

Pour des scénarios avancés tels que l'automatisation du nettoyage pour de grandes feuilles de calcul ou la gestion répétée de substitutions d'erreurs spécifiques, l'utilisation d'une macro VBA simple peut économiser du temps et des efforts manuels. Ci-dessous, vous trouverez des instructions étape par étape pour utiliser VBA afin de remplacer en masse toutes les valeurs d'erreur dans une plage sélectionnée par votre alternative préférée - 0, une cellule vide, ou un message spécifique.
Cette approche est hautement scalable et convient aux utilisateurs familiers avec les opérations de base de macros.

1. Lancez l'éditeur Visual Basic for Applications (VBA) en cliquant sur Développeur > Visual Basic. Dans l'éditeur qui apparaît, cliquez sur Insérer > Module, puis copiez-collez le code suivant dans la fenêtre de module vierge :

Sub ReplaceErrorsWithValue()
    Dim WorkRng As Range
    Dim ReplaceWhat As String
    Dim Prompt As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select the range to process", xTitleId, WorkRng.Address, Type:=8)
    
    Prompt = "Enter the replacement value for errors:" & vbCrLf & "(Leave blank for empty cell; enter 0 or any text string as needed)"
    ReplaceWhat = Application.InputBox(Prompt, xTitleId, "", Type:=2)
    
    If Not WorkRng Is Nothing Then
        Dim cell As Range
        Application.ScreenUpdating = False
        
        For Each cell In WorkRng
            If IsError(cell.Value) Then
                cell.Value = ReplaceWhat
            End If
        Next
        
        Application.ScreenUpdating = True
    End If
End Sub

2. Puis exécutez la macro en cliquant sur le bouton ou en appuyant sur F5 dans la fenêtre VBA. Lorsque vous y êtes invité, sélectionnez la plage cible, puis spécifiez votre remplacement souhaité : laissez la zone de saisie vide pour effacer les cellules d'erreur (laisser vide), ou entrez "0" pour remplacer par des zéros, ou tapez votre texte de libellé personnalisé.

Rechercher et remplacer les erreurs de formule # par 0 ou vide avec Kutools pour Excel

Article connexe :

• Comment changer l'erreur #DIV/0! en un message lisible dans Excel ?