Considérons une situation où les formules du tableau contiennent des valeurs d'erreur pré-attendues. Ils ne nécessitent pas de correction immédiate, mais la présentation des résultats doit être améliorée. Il existe plusieurs façons de masquer les valeurs d'erreur et les indicateurs d'erreur dans les cellules.
Les formules peuvent renvoyer des erreurs pour de nombreuses raisons. Par exemple, la formule =1/0 renvoie l'erreur #DIV/0! car la division par 0 n'est pas autorisée. Les valeurs d'erreur suivantes sont disponibles : #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! et #VALEUR !.
Conversion d'une erreur en valeur nulle et utilisation d'un format pour masquer la valeur
Pour masquer les valeurs d'erreur, vous pouvez les convertir, par exemple, en nombre 0, puis appliquer un format conditionnel pour masquer la valeur.
Création d'un exemple d'erreur
Ouvrir feuille claire ou en créer un nouveau.
Entrer 3
à la cellule B1, 0
- à la cellule C1 et à la formule =B1/C1- dans la cellule A1.
La cellule A1 affichera la valeur d'erreur #DIV/0!.
Sélectionnez la cellule A1 et appuyez sur F2 pour modifier la formule.
Après le signe égal (=), tapez ESLIOSHIBKA avec parenthèse ouvrante :
SIERREUR(
Déplacez le curseur à la fin de la formule.
Entrer ,0)
, c'est-à-dire une virgule suivie d'un zéro et d'une parenthèse fermante.
Formule =B1/C1 prendra la forme =SIERREUR(B1/C1,0).
Appuyez sur la touche Entrée pour terminer la modification de la formule.
Maintenant dans la cellule au lieu de l'erreur #DIV/0 ! la valeur 0 doit être affichée.
Appliquer un format conditionnel
Sélectionnez la cellule avec l'erreur et sur l'onglet domicile appuie sur le bouton Mise en forme conditionnelle.
Choisissez une équipe Créer une règle.
Dans la boîte de dialogue, sélectionnez une option.
Assurez-vous que la section de la première liste est sélectionnée Valeur de la cellule, et dans la seconde équivaut à. Ensuite, dans le champ de texte à droite, saisissez la valeur 0.
Cliquez sur le bouton Format.
Sur l'onglet Nombre sur la liste Formats numériques sélectionner un article (tous formats).
Dans le champ Taper Entrer ;;;
(trois points-virgules) et cliquez sur le bouton D'ACCORD. Cliquez sur le bouton D'ACCORD de nouveau.
La valeur 0 dans la cellule disparaîtra. C'est parce que le format personnalisé ;;;
demande de masquer tous les nombres dans la cellule. Cependant, la valeur réelle (0) est toujours stockée dans la cellule.
Masquer les valeurs d'erreur en changeant la couleur du texte en blanc
La procédure décrite ci-dessous vous permet de formater les cellules contenant des erreurs afin que le texte qu'elles contiennent s'affiche dans une police couleur blanche. Par conséquent, le texte d'erreur dans ces cellules devient invisible.
Sélectionnez la plage de cellules contenant la valeur d'erreur.
Sur l'onglet domicile dans un groupe modes Mise en forme conditionnelle et sélectionnez l'élément Gestion des règles.
Une boîte de dialogue apparaîtra Gestionnaire de règles de mise en forme conditionnelle.
Choisissez une équipe Créer une règle.
Une boîte de dialogue s'ouvrira Créer une règle de formatage.
Listé Sélectionnez le type de règle sélectionner un article Mettre en forme uniquement les cellules contenant.
Au chapitre Modifier la description de la règle sur la liste Mettre en forme uniquement les cellules qui remplissent la condition suivante sélectionner un article Erreurs.
Cliquez sur le bouton Format et ouvrir l'onglet Police de caractère.
Cliquez sur la flèche pour ouvrir la liste Couleur, et dans la rubrique Couleurs du thème choisissez le blanc.
Afficher un tiret, #N/A ou NA au lieu d'une valeur d'erreur
Dans certaines situations, vous souhaiterez peut-être que les cellules affichent une chaîne de texte au lieu de valeurs d'erreur, telles que "#N/A", un tiret ou "NA". Vous pouvez le faire en utilisant les fonctions ESLIOSHIBKA et ND, comme illustré dans l'exemple ci-dessous.
Description de la fonction
ESLIOSHIBKA. Vous pouvez utiliser cette fonction pour déterminer si une cellule contient ou renvoie une erreur dans une formule.
ND Cette fonction renvoie la chaîne "#N/A" dans la cellule. Syntaxe de la fonction : = ND().
Masquer les valeurs d'erreur dans un rapport de tableau croisé dynamique
Modifiez la façon dont les erreurs sont affichées. Dans un groupe Format cochez la case Pour l'affichage des erreurs. Entrez la valeur dans le champ que vous souhaitez afficher à la place des erreurs. Pour afficher les erreurs dans les champs vides, supprimez tout le texte du champ.
Cliquez sur le rapport de tableau croisé dynamique.
Un onglet apparaîtra Travailler avec des tableaux croisés dynamiques.
Excel 2016 et Excel 2013 : onglet Une analyse dans un groupe tableau croisé dynamique cliquez sur la flèche à côté de la commande Choix et sélectionnez l'élément Choix.
Excel 2010 et Excel 2007 : onglet Choix dans un groupe tableau croisé dynamique cliquez sur la flèche à côté de la commande Choix et sélectionnez l'élément Choix.
Ouvrir un onglet Balisage et format et effectuez l'une des actions suivantes ou les deux :
Après avoir saisi ou corrigé une formule, ainsi que lors de la modification d'une valeur d'une fonction, il arrive qu'une erreur de formule apparaisse, et non la valeur requise. Le total éditeur de tableur reconnaît sept principaux types d'erreurs de calcul. À quoi ressemblent les erreurs dans Excel et comment les corriger, nous analyserons ci-dessous.
Ci-dessous, nous présenterons une description des formules présentées dans l'image avec des informations détaillées pour chaque erreur.
1. #CAS!- "diviser par 0", se produit le plus souvent en essayant de diviser par zéro. C'est-à-dire que la formule intégrée dans la cellule, effectuant la fonction de division, tombe sur une cellule où, avec valeur zéro ou est "Vide". Pour résoudre le problème, vérifiez toutes les cellules impliquées dans le calcul et corrigez les valeurs non valides. La deuxième action menant à #DIV/O ! - il s'agit de la saisie de valeurs incorrectes dans certaines fonctions, telles que \u003d AVERAGE () si le calcul dans la plage de valeurs est 0. Le même résultat provoquera l'accès à des cellules vides par une formule qui nécessite des données spécifiques pour le calcul.
2. #N / A- "il n'y a pas de données". C'est ainsi qu'Excel signale les valeurs incompréhensibles pour la formule (fonction). La saisie de mauvais nombres dans la fonction déclenchera certainement cette erreur. Lorsqu'il apparaît, assurez-vous que toutes les cellules de saisie sont correctement remplies, et en particulier dans celles où la même inscription est allumée. Souvent vu lors de l'utilisation
3. #NOM? - "nom invalide", un indicateur du nom incorrect de la formule ou d'une partie de celle-ci. Le problème disparaît si vous vérifiez et corrigez tous les noms et noms qui accompagnent l'algorithme de calcul.
4. #VIDER!– "il y a une valeur vide dans la plage", un signal que des zones non sécantes sont écrites quelque part dans le calcul ou qu'un espace est placé entre les plages spécifiées. Erreur assez rare. L'entrée d'erreur peut ressembler à ceci :
SOMME(G10:G12 I8:J8)
Excel ne reconnaît pas ces commandes.
5. #NUMÉRO!- une erreur est causée par une formule contenant un nombre qui ne correspond pas aux limites de la plage indiquée.
6. #LIEN!- avertit que les cellules associées à cette formule ont disparu. Vérifiez si les cellules indiquées dans la formule ont probablement été supprimées.
7. #ÉVALUER!– Type d'argument mal sélectionné pour que la fonction fonctionne.
8. Bonus, erreur ##### - la largeur de la cellule n'est pas suffisante pour afficher le nombre entier
De plus, Excel émet un avertissement concernant une formule non valide. Le programme essaiera de vous dire exactement comment organiser la ponctuation (par exemple, les crochets). Si l'option proposée répond à vos besoins, cliquez sur "Oui". Si l'info-bulle nécessite un réglage manuel. Sélectionnez ensuite "Non" et réorganisez vous-même les crochets.
Erreurs dans Excel. Utilisation de la fonction ESTERREUR() pour Excel 2003
Eh bien aide à éliminer les erreurs dans la fonction Excel. Cela fonctionne en trouvant des erreurs dans les cellules, s'il trouve une erreur dans la formule, il renvoie VRAI et vice versa. En combinaison avec =IF(), cela permettra de remplacer la valeur si une erreur est trouvée.
Formule de travail : =IF(ERROR(expression),error,expression).
SI(ESTERREUR(A1/A2),"";A1/A2)
Explication : si une erreur est détectée lors de l'exécution de A1/A2, un vide ("") sera renvoyé. Si tout s'est déroulé correctement (c'est-à-dire ESTERREUR (A1/A2) = FAUX), alors A1/A2 est calculé.
Erreurs dans Excel.Utilisation de IFERROR() pour Excel 2007 et supérieur
L'une des raisons pour lesquelles je suis rapidement passé à Excel 2007 était SIERREUR() (le plus raison principale- c'est )
La fonction iferror contient les capacités des deux fonctions - ISERROR() et IF(), mais est disponible dans les nouvelles versions d'Excel, ce qui est très pratique
L'outil est activé de la manière suivante : =IFERREUR(valeur ; valeur sur erreur). Au lieu de "valeur", une expression de calcul/référence de cellule est mise, et au lieu de "valeur sur erreur", que faut-il retourner si une inexactitude survient, par exemple, si lors du calcul de A1/A2, #CAS! alors la formule ressemblera à ceci:
ESTERREUR(A1/A2 ; » »)
" télécharger ici
Listes et plages (5)
Macros (procédures VBA) (63)
Divers (39)
Bugs et problèmes Excel (3)
Comment afficher 0 au lieu d'une erreur dans une cellule avec une formule
Il existe des situations où de nombreuses formules sont créées sur des feuilles d'un classeur qui exécutent diverses tâches. En même temps, les formules ont été créées il y a longtemps, peut-être même sur vous. Et les formules renvoient des erreurs. Par exemple #DIV/0 ! (#DIV/0!) . Cette erreur se produit si une division par zéro se produit à l'intérieur de la formule : = A1 / B1 , où B1 est zéro ou vide. Mais il peut y avoir d'autres erreurs (#N/A, #VALUE!, etc.). Vous pouvez modifier la formule en ajoutant un contrôle d'erreur :
=SI(ISERR(A1 / B1),0, A1 / B1)
arguments:
=SI(EOSH(1 argument), 2 argument, 1 argument)
Ces formules fonctionneront dans n'importe quelle version d'Excel. Certes, la fonction EOS ne gérera pas l'erreur #N/A (#N/A). Pour traiter #N/A de la même manière, vous devez utiliser la fonction ESTERREUR :
=SI(ESTERREUR(A1 / B1), 0, A1 / B1)
=SI(ESTERREUR(A1 / B1),0, A1 / B1)
Cependant, plus loin dans le texte, j'utiliserai EOSH (car il est plus court) et de plus, il n'est pas toujours nécessaire de "ne pas voir" les erreurs #N/A.
Mais pour versions d'Excel 2007 et plus, une fonction légèrement plus optimisée peut être appliquée ESLIOSHIBKA (IFERREUR):
=SIERREUR(A1 / B1 ;0)
=SIERREUR(A1 / B1 ,0)
arguments:
=SIERREUR(1 arg; 2 arg)
1 argument : expression à évaluer
2 argument : la valeur ou l'expression à retourner à la cellule en cas d'erreur dans le premier argument.
Pourquoi ESLIERROW est-il meilleur et pourquoi est-ce que je l'appelle plus optimisé ? Analysons plus en détail la première formule :
=SI(ISH(A1 / B1), 0, A1 / B1)
Si nous calculons pas à pas, nous verrons que l'expression A1 / B1 (c'est-à-dire la division) est d'abord évaluée. Et si son résultat est une erreur, alors EOSH renverra TRUE (TRUE) , qui sera passé à IF (IF) . Et puis la fonction IF(IF) renverra la valeur du deuxième argument 0.
Mais si le résultat n'est pas erroné et qu'ISERR renvoie FALSE, alors la fonction réévaluera l'expression précédemment calculée : A1 / B1
Avec la formule ci-dessus, cela ne joue pas un rôle particulier. Mais si une formule telle que VLOOKUP est utilisée avec une vue de plusieurs milliers de lignes, le fait de calculer deux fois peut augmenter considérablement le temps nécessaire pour recalculer les formules.
La fonction SIERREUR évalue l'expression une fois, mémorise son résultat et s'il est erroné, elle renvoie ce qui a été écrit comme deuxième argument. S'il n'y a pas d'erreur, alors renvoie le résultat stocké de l'évaluation de l'expression à partir du premier argument. Celles. le calcul réel se produit une fois, ce qui n'affectera pratiquement pas la vitesse du recalcul global des formules.
Par conséquent, si vous avez Excel 2007 et supérieur et que le fichier ne sera pas utilisé dans plus premières versions- alors il est logique d'utiliser exactement ESLIOSHIBKA (IFERROR).
Pourquoi corriger les formules avec des erreurs ? Habituellement fait pour un affichage plus esthétique des données dans les rapports, surtout si les rapports sont ensuite envoyés à la direction.
Ainsi, il existe de telles formules sur la feuille, dont les erreurs doivent être traitées. S'il existe une ou deux formules de correction de ce type (oui, même 10-15), il n'y a presque aucun problème à remplacer manuellement. Mais s'il existe plusieurs dizaines voire centaines de telles formules, le problème devient presque universel :-). Cependant, le processus peut être simplifié en écrivant du code Visual Basic pour Application relativement simple.
Pour toutes les versions d'Excel :
| Sub IfIsErrNull() Const sToReturnVal As String = "0" , vbInformation, "www.site" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = " =" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" Si Gauche(s, 9)<>"IF(ISERR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err .Number Puis MsgBox "Formules traitées" |
Sub IfIsErrNull() Const sToReturnVal As String = "0" "s'il faut retourner vide au lieu de zéro "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "La plage sélectionnée ne contient aucune donnée", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid(s, 2 ) ss = " =" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" Si Gauche(s, 9)<>"IF(ISERR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err .Number Then MsgBox "Impossible de convertir la formule dans la cellule : " & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub
Pour les versions 2007 et supérieures
| Sub IfErrorNull() Const sToReturnVal As String = "0" "si nécessaire, retourne vide au lieu de zéro "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String , ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "La plage sélectionnée ne contient aucune donnée", vbInformation, "www.site" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = "=" & "IFERREUR(" & s & ", " & sToReturnVal & ")" Si gauche(s, 8)<>"IFERREUR(" Alors si rc.HasArray Alors rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Puis MsgBox "Impossible de convertir la formule dans la cellule : "& ss & vbNewLine & _ Err.Description, vbInformation, "www.site" Sinon MsgBox "Formules traitées", vbInformation, "www.site" End If End Sub |
Sub IfErrorNull() Const sToReturnVal As String = "0" "s'il faut retourner vide au lieu de zéro "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "La plage sélectionnée ne contient aucune donnée", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid(s, 2 ) ss = " =" & "SIERREUR(" & s & "," & sValRetour & ")" Si Gauche(s, 8)<>"IFERREUR(" Alors si rc.HasArray Alors rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Puis MsgBox "Impossible de convertir la formule dans la cellule : " & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub
Comment ça fonctionne
Si vous n'êtes pas familier avec les macros, il est préférable de lire d'abord comment les créer et les appeler : Qu'est-ce qu'une macro et où la trouver ? , car il peut arriver que vous fassiez tout correctement, mais vous oubliez d'activer les macros et rien ne fonctionnera.
Copiez le code ci-dessus, allez dans l'éditeur VBA ( autre+F11), créer un module standard ( Insérer -module) et collez simplement ce code dedans. Allez à celui que vous voulez Classeur Excel et sélectionnez toutes les cellules dont les formules doivent être converties de manière à ce qu'en cas d'erreur, elles renvoient zéro. Presse autre+F8, choisissez le code SiEstErrNull(ou alors SiErreurNull, selon celui que vous avez copié) et appuyez sur Cours.
Une fonction de gestion des erreurs sera ajoutée à toutes les formules dans les cellules sélectionnées. Les codes donnés prennent également en compte :
-si la formule a déjà utilisé la fonction SIERREUR ou SI(EOSH), alors une telle formule n'est pas traitée ;
-code traitera également correctement les fonctions de tableau ;
- vous pouvez sélectionner des cellules non adjacentes (via Ctrl).
Quel est l'inconvénient : les formules matricielles complexes et longues peuvent provoquer une erreur de code en raison de la particularité de ces formules et de leur traitement à partir de VBA. Dans ce cas, le code écrira sur l'impossibilité de continuer à travailler et mettra en évidence la cellule problématique. Par conséquent, je vous recommande fortement de faire des remplacements sur des copies de fichiers.
Si la valeur d'erreur doit être remplacée par vide au lieu de zéro, la chaîne
"Const sToReturnVal As String = """"""
Supprimer l'apostrophe ( " )
Il est également possible code donné appelez en appuyant sur un bouton (Comment créer un bouton pour appeler une macro sur une feuille) ou placez-le dans un complément (Comment créer votre propre complément ?), afin que vous puissiez l'appeler depuis n'importe quel fichier.
Et un petit ajout : essayez d'appliquer le code de manière réfléchie. Pas toujours le retour d'une erreur gêne. Par exemple, lorsque utilisation de VLOOKUP il est parfois utile de voir quelles valeurs n'ont pas été trouvées.
Je tiens également à noter qu'il est nécessaire de s'appliquer à des formules qui fonctionnent vraiment. Parce que si la formule renvoie #NAME!(#NAME!), cela signifie qu'un argument est écrit de manière incorrecte dans la formule et qu'il s'agit d'une erreur d'écriture de la formule et non d'une erreur dans le résultat du calcul. Il est préférable d'analyser de telles formules et de trouver une erreur afin d'éviter les erreurs logiques dans les calculs sur la feuille.
("Barre inférieure":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"droite","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"diapositive","texteffectslidedirection2":"droite","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:a lpha(opacity=60);","titlecss":"display:block; position:relative ; font:gras 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial ; color:#fff;","descriptioncss":"display:block; position:relative ; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial ; couleur :#fff ; margin-top:8px;","buttoncss":"display:block; position:relative ; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))
Date : 24 décembre 2015 Catégorie :Les erreurs dans Excel sont un compagnon indispensable de tous ceux qui. Lorsqu'une expression dans une cellule ne peut pas être calculée, le programme affiche un message d'erreur dans la cellule. Il commence par un "#" suivi du nom de l'erreur. Il n'y a pas lieu d'avoir peur de cela si vous êtes familier avec les fonctions d'Excel et savez comment suivre la logique la plus simple opérations mathématiques - vous pouvez facilement trouver et corriger l'erreur.
Si la cellule est entièrement remplie de signes dièse (#), il ne s'agit pas du tout d'une erreur. Il n'y a pas assez d'espace dans la cellule pour afficher le résultat. Augmentez la taille de la cellule ou diminuez la police afin que le résultat puisse être affiché.
Type d'erreur
Si l'erreur persiste, le déchiffrement aidera à la corriger :
| Erreur | La description |
| #DIV/0 ! | Une erreur se produit lors de la tentative de division par zéro |
| #NOM? | Le programme ne peut pas reconnaître le nom saisi. Par exemple, vous avez mal orthographié le nom de la fonction ou vous n'avez pas conclu chaîne de texte entre guillemets |
| #N / A | Données non disponibles. Par exemple, je n'ai trouvé aucune valeur |
| #VIDER! | Vous avez demandé une intersection de plages qui ne se chevauchent pas |
| #NUMÉRO! | Le problème réside dans l'une des valeurs numériques utilisées dans la formule. Par exemple, vous essayez d'extraire Racine carréeà partir d'un nombre négatif. En mathématiques classiques, cette opération n'a pas de sens. |
| #LIEN! | La formule contient une référence qui n'existe pas. Par exemple, vous avez supprimé la cellule à laquelle elle fait référence |
| #ÉVALUER! | La formule contient des composants non valides. Souvent, cette erreur se produit lorsque la syntaxe des formules est violée. |
Lorsqu'une cellule contient une formule avec une erreur, un marqueur apparaît à côté de celle-ci. En cliquant dessus, vous pouvez lire l'aide sur cette erreur. Vous pouvez également voir les étapes de calcul. Sélectionnez cet élément et le programme affichera une fenêtre où l'erreur sera soulignée. il La meilleure voie déterminer où l'erreur s'est produite.
De plus, vous pouvez vous passer de corriger ces erreurs, mais simplement celles-ci. Mais cela doit être approprié. Les erreurs ne doivent être contournées que si elles ne peuvent pas être corrigées. À autrement, les résultats du calcul peuvent être faussés.
Suivi des erreurs à travers les étapes de calcul Références circulaires dans Excel
Un autre type d'erreur est la référence circulaire. Cela se produit lorsque vous faites référence à une cellule dont la valeur dépend de celle dans laquelle vous écrivez la formule. Par exemple, dans une cellule A1 la formule s'écrit \u003d A2 + 1, et en A2écrire =A1 , il y aura une référence circulaire qui sera recalculée indéfiniment. Dans ce cas, le programme avertit de l'apparition d'une référence cyclique, arrête le calcul des "formules en boucle". Une double flèche apparaît sur le côté gauche des cellules. Vous devrez corriger l'erreur et refaire le calcul.
Parfois, une "boucle" complexe se produit lorsqu'une référence circulaire est formée avec plusieurs formules intermédiaires.
Pour traquer ces erreurs, exécutez Formules - Dépendances de formule - Vérification des erreurs - Références circulaires. Dans la liste déroulante, le programme affiche les adresses des cellules qui créent cycle sans fin. Il ne reste plus qu'à corriger les formules dans ces cellules.
Suivi des références circulaires Dans Excel, vous pouvez essayer de calculer le résultat de formules en boucle. Pour cela, cochez la case Fichier - Options - Formules - Activer les calculs itératifs. Dans le même bloc, vous pouvez définir quantité maximale itérations (erreurs de calcul) pour trouver l'équilibre et l'erreur admissible. Dans la plupart des cas, ce n'est pas nécessaire, je vous recommande donc de ne pas cocher cette case. Cependant, quand vous savez que les formules en boucle sont correctes et que leur calcul conduira à un résultat stable, pourquoi ne pas le faire ?
C'est tout sur les types d'erreurs dans Excel. Dans ce article court vous disposez de suffisamment d'informations pour traiter les erreurs les plus courantes dans Excel en analysant la valeur de retour. Mais lisez la longue liste d'erreurs dans ! Prêt à répondre à vos questions - écrivez dans les commentaires.
Dans le prochain article, je vais dire. Inutile de dire que les fonctions Excel sont « notre tout » ?
Je pense que non. Par conséquent, allez-y et lisez, ce sera le premier pas dans le monde des formules complexes avec les bons résultats !
Les questions sur les erreurs dans Excel sont les plus courantes, j'en reçois tous les jours, les forums thématiques et les services de réponse en sont remplis. Il est très facile de se tromper dans une formule Excel, surtout quand on travaille vite et avec grande quantité Les données. Il en résulte des calculs erronés, des managers mécontents, des pertes... Comment trouver l'erreur qui s'est glissée dans vos calculs ? Essayons de comprendre. Il n'y a pas d'outil ou d'algorithme unique pour trouver les erreurs, nous allons donc passer du simple au complexe.
Si la cellule contient une formule au lieu d'un résultat
Si vous avez écrit une formule dans une cellule, appuyé sur Entrée, et au lieu du résultat, la formule elle-même y est affichée, alors vous avez sélectionné format texte valeurs pour cette cellule. Comment réparer? Tout d'abord, étudiez et sélectionnez celui dont vous avez besoin, mais pas de texte, dans ce format, aucun calcul n'est effectué.
Maintenant sur le ruban, trouvez Accueil - Numéro, et dans la liste déroulante choisir le format de données approprié. Faites cela pour toutes les cellules où la formule est devenue du texte brut.
Si les formules de la feuille de calcul ne sont pas recalculées
Si vous modifiez les données source et que les formules de la feuille ne souhaitent en aucun cas être recalculées, vous avez désactivé le recalcul automatique des formules.
Pour résoudre ce problème, cliquez sur le ruban : Formules - Calculs - Options de calcul - Automatique. Maintenant, tout sera recalculé comme d'habitude.
N'oubliez pas que le recalcul automatique peut être désactivé exprès. Si vous avez un grand nombre de formules sur votre feuille, chaque modification les fait recalculer. En conséquence, travailler avec le document se transforme en un état d'attente chronique après chaque changement. Dans ce cas, vous devez convertir la conversion en mode manuel: Formules - Calculs - Options de calcul - Manuel. Apportez maintenant toutes les modifications aux données d'origine, le programme attendra. Lorsque toutes les modifications sont apportées - appuyez sur F9, toutes les formules mettront à jour les valeurs. Ou réactivez le recalcul automatique.
Si la cellule est remplie d'un signe dièse
Une autre situation classique, quand à la suite de calculs vous obtenez non pas un résultat, mais une cellule remplie de signes dièse :
En fait, ce n'est pas une erreur, car le programme vous indique que le résultat ne rentre pas dans la cellule, il vous suffit de le mettre à la bonne taille.
Le résultat du calcul est au mauvais format
Il arrive parfois qu'après avoir effectué des calculs, le résultat n'apparaisse pas sous la forme que vous attendez. Par exemple, nous avons ajouté deux nombres et, par conséquent, nous avons obtenu une date. Pourquoi ça arrive ? Il est probable que le format de données dans la cellule ait été précédemment défini sur "date". Changez-le simplement pour celui que vous voulez, et tout se passera comme vous le souhaitez.
Lorsque les liens externes ne sont pas disponibles
Si Formule Excel renvoie un mauvais résultat
Si vous avez écrit une formule et qu'elle renvoie un résultat manifestement incorrect, traitons de la logique de la formule. Vous pourriez faire une erreur banale dans les parenthèses, l'ordre des opérateurs. Étudiez et vérifiez si tout est correct dans le vôtre. Si c'est vrai, passez à l'étape suivante.
Si vous utilisez des fonctions, assurez-vous de connaître . Chacun d'eux a sa propre syntaxe. Vérifiez si vous avez correctement défini les paramètres de la formule en lisant l'aide correspondante. Appuyez sur F1 et dans la fenêtre "Aide d'Excel" de la recherche, écrivez votre fonction, par exemple "". Le programme affichera une liste des matériaux disponibles pour cette fonction. En règle générale, ils suffisent pour obtenir une image complète du travail de votre fonction. Les auteurs de l'aide présentent le matériel de manière très accessible, donnent des exemples d'utilisation.
Souvent, les utilisateurs spécifient de manière incorrecte les références de cellule dans les formules, c'est pourquoi ils obtiennent des résultats erronés. La première chose à faire pour tester des formules externes est d'activer l'affichage des formules dans les cellules. Pour cela, courez sur le ruban Formules - Dépendances de formule - Afficher les formules. Désormais, les cellules afficheront des formules, et non les résultats des calculs. Vous pouvez parcourir la feuille des yeux et vérifier si les liens corrects sont indiqués. Pour afficher à nouveau les résultats, exécutez à nouveau la même commande.
Pour simplifier le processus, vous pouvez inclure des flèches de lien. Vous pouvez facilement déterminer à quelles cellules une formule fait référence en cliquant sur Formules - Dépendances de formule - Influencer les cellules. Sur la feuille, des flèches bleues indiqueront à quelles données vous vous référez.
De même, vous pouvez voir les cellules où les formules font référence à une cellule donnée. Pour ce faire, nous effectuons : Formules - Dépendances de formule - Cellules dépendantes.
Gardez à l'esprit que dessiner des flèches dans des tableaux complexes prendra beaucoup de temps et de ressources machine. Pour supprimer les flèches, cliquez sur Formules - Dépendances de formule - Supprimer les flèches.
En règle générale, vérifier soigneusement les formules avec les outils énumérés ci-dessus résout le problème des résultats erronés. Nous recherchons un problème pour gagner!
Si une référence circulaire se produit dans Excel
Parfois, après avoir saisi une formule, le programme avertit qu'une référence circulaire a été saisie. Le calcul s'arrête. Cela signifie que la formule fait référence à une cellule, qui à son tour fait référence à la cellule dans laquelle vous saisissez la formule. Il s'avère, un cycle fermé de calculs, le programme devra calculer le résultat indéfiniment. Mais cela n'arrivera pas, vous serez averti et aurez la possibilité de résoudre le problème.
Souvent, les cellules se réfèrent les unes aux autres indirectement, c'est-à-dire pas directement, mais par des formules intermédiaires.
Pour trouver de telles formules "incorrectes", recherchez sur le ruban : Formules - Dépendances de formule - Vérification des erreurs - Erreurs cycliques. Ce menu ouvre une liste de cellules avec des formules en boucle. Cliquez sur n'importe lequel pour y placer le curseur et vérifier la formule.
Naturellement, les références circulaires sont éliminées en vérifiant et en corrigeant la logique de calcul. Cependant, dans certains cas, une référence circulaire ne sera pas une erreur. Autrement dit, ce système de formules doit encore être autorisé à calculer jusqu'à un état proche de l'équilibre, lorsqu'il n'y a pratiquement aucun changement. Certaines tâches d'ingénierie l'exigent. Heureusement, Excel le permet. Cette approche est appelée calcul itératif. Pour les activer, cliquez sur Fichier - Options - Formules, et cochez la case "Calculs itératifs". Installez-y :
- Limite d'itération- le nombre maximum d'itérations (cycles) qui seront réalisées avant un arrêt complet
- Erreur relative- changement minime valeurs cibles pour une itération, à laquelle le recalcul sera arrêté.
Autrement dit, la formule cyclique sera calculée jusqu'à ce que l'erreur relative soit atteinte, mais pas plus que le nombre limite d'itérations spécifié.
Erreurs Excel intégrées
Parfois, lors des calculs, des erreurs apparaissent qui commencent par le signe "#". Par exemple, "#N/A", "#NUMBER !", etc. ces erreurs sont moi, lisez ce post et essayez de comprendre la raison de votre erreur. Lorsque cela se produit, vous pouvez facilement tout réparer.
Si vous ne trouvez pas d'erreur dans une formule assez complexe, cliquez sur point d'exclamation près de la cellule et menu contextuel sélectionnez Afficher les étapes de calcul.
Une fenêtre apparaîtra à l'écran indiquant à quel moment du calcul une erreur se produit, elle sera soulignée dans la formule. il le droit chemin comprendre ce qui ne va pas.
Ce sont peut-être tous les principaux moyens de rechercher et de corriger les erreurs dans Excel. Nous avons examiné les problèmes les plus courants et comment les résoudre. Mais que faire lorsque des erreurs doivent être prévues immédiatement dans les calculs, je vous en parlerai dans un article. Ne perdez pas 5 minutes de votre temps sur cet article, le lire attentivement vous fera gagner beaucoup de temps à l'avenir.
J'attends avec impatience vos questions et commentaires sur ce post!