O zi buna prieteni!

În acest articol vom vorbi despre ce sunt erori în formuleexcela pe care le întâlnim când lucrăm cu electronice Foi de calcul Excel... Sunt mai mult decât sigur că toată lumea a văzut greșelile, dar știu mai puțin cum să scape corect de ele. Cu toate acestea, aceste cunoștințe sunt importante, deoarece vă vor asigura împotriva greșeli tipice sau te va ajuta rapid si fara panica sa scapi sau sa repari cel primit erori în formuleexcela.

Sunt multe de vorbit despre erori în Excel, dar să ne uităm la cele mai comune, vă voi spune de ce și cum apar, precum și cum să remediați erorile apărute. erori în formuleexcela pentru a afișa corect datele.

Ei bine, de fapt ce se întâmplă erori în formuleexcela:

1. Eroare #####. Acesta este unul dintre cele mai comune și simple erori în formulele Excel... Înseamnă un singur lucru, că lățimea coloanei nu este suficient de largă pentru a vă afișa pe deplin datele. Tratamentul acestei probleme este foarte simplu, deplasați cursorul mouse-ului peste marginea coloanei și, în timp ce țineți apăsat butonul din stânga, creșteți celula până când datele încep să fie afișate sau dublu click pe marginea coloanei va permite cea mai largă celulă din coloană.
2. Eroare #NAME?... Acest eroare (#NUME?) apare în formulele Excel numai atunci când editorul nu poate recunoaște textul din formulă (de exemplu, o eroare în numele funcției din cauza unei greșeli de scriere = SUM (A1: A4). erori în formuleexcela, trebuie să o citiți cu atenție și să corectați eroarea (A1: A4).
3. Eroare #VALUE!... Acest eroare (#VALUE!) este posibil să aveți un caz în care formula conține un argument, al cărui tip nu este potrivit pentru calculele dvs. De exemplu, în matematica sau formula dvs., valoarea textului= A1 + B1 + C1, unde C1 este textul. Tratamentul problemei este simplu, utilizați o formulă care ignoră celulele în care este prezent text sau pur și simplu eliminați valoare dată din celula C1.
4. Eroare # BUSINESS / 0... După cum puteți vedea din eroarea din formulă, pur și simplu v-ați înmulțit argumentul cu numărul 0, iar acest lucru nu se poate face pe baza reguli matematice... Pentru a remedia această eroare, trebuie să modificați numărul astfel încât să nu fie egal cu 0 sau să schimbați formula, de exemplu, una logică, pentru a evita o eroare. = DACA (A2 = 0; ””; A1 / A2)
5. Eroare # LINK!... Aceasta este una dintre cele mai frecvente și confuze erori din funcțiile Excel. Când vedeți această eroare, înseamnă că formula se referă la o celulă care nu mai există. Acest lucru este mai ales problematic atunci când lucrați cu cantități mari de date în și cantitate mare... Când vă editați tabelele astfel erori în formuleexcela nu trebuie să vă sperii, sunt foarte ușor de reparat, trebuie doar și totul se va întoarce la locul său sau, dacă este necesar, rescrieți manual formula, excluzând argumentul eronat din ea.

Sper că acest articol este despre ceea ce sunt erori în formuleexcelași corectându-le, ți-a devenit de folos și ai învățat ceva nou și interesant pentru tine.

Ne vedem în articole noi!

„De ce este lumea atât de aranjată încât oamenii care știu să trăiască pentru propria lor plăcere să nu aibă niciodată bani, iar cei care au bani habar n-au ce înseamnă „a arde o viață”?
D.B. Spectacol

Intru o formulă, dar în loc de rezultat, văd textul # N / A sau #####. Ce înseamnă?

Aceasta înseamnă că există o eroare în datele originale, formula în sine, formatare sau altfel. Mesajul de eroare conține o indicație despre locul exact care a avut loc. Mai jos vom analiza mesajele de eroare și cele mai frecvente cauze ale acestora:

Eroare #####

Cel mai adesea, această eroare înseamnă că coloana nu este suficient de largă pentru a afișa conținutul celulei. Pentru a rezolva problema, este suficient să măriți lățimea coloanei.

Un alt motiv pentru eroare este că celula conține o dată sau o oră cu valori negative.

Eroare # N/A

Această eroare apare dacă o funcție sau o formulă conține o referință la o valoare (sau valoarea în sine) care nu există. Cu alte cuvinte, un câmp obligatoriu nu este completat într-o funcție sau formulă sau există o referință la o celulă goală, în timp ce o valoare trebuie să fie prezentă în celulă. Cele mai frecvente motive sunt:

• Unul sau mai multe argumente necesare lipsesc din funcție.
• Funcțiile CĂUTARE V, CĂUTARE, CĂUTARE sau CĂUTARE indică valoare incorectă a argumentului searchup_value.
• CĂUTARE V, CĂUTARE sau POTRIVIRE sunt folosite pentru a căuta un tabel nesortat.
• În tabelul original, în locul datelor lipsă a fost introdusă valoarea # N / A sau ND ().

Eroare # DIV / 0!

Eroare # DIV / 0! Indică faptul că împărțirea la zero are loc în timpul calculului. Motivele pot fi următoarele:

• În formulă, împărțirea la zero este specificată în mod explicit (de exemplu, = 10/0).
• Într-o formulă sau funcție, divizorul este o referință la o celulă goală sau la o celulă care conține zero.

Eroare # EMPTY!

Această eroare apare atunci când două intervale care se suprapun sunt specificate în condițiile funcției, dar ele nu se suprapun de fapt.

Notă: În unele sarcini, utilizatorul trebuie să efectueze acțiuni numai cu acele celule care sunt prezente simultan în două intervale. Pentru a face acest lucru, intervalele de celule din funcție sunt separate nu printr-un punct și virgulă, ci printr-un spațiu (care se numește operator de intersecție).

• Dacă funcția nu trebuie să specifice intervale suprapuse (de exemplu, la însumare), este probabil ca un spațiu să fi fost folosit ca separator între intervale, și nu un punct și virgulă. Sau a fost folosit un spațiu în loc de două puncte la specificarea unui interval.
• Dacă intervalele care se suprapun trebuie să fie specificate într-o funcție, este probabil ca intervalele într-adevăr să nu aibă celule comune... Trebuie să verificați intervalele specificate în argumentele funcției.

Eroare #NUM!

Această eroare apare dacă într-o formulă sau funcție sunt specificate valori numerice incorecte. De exemplu:

• O funcție care ia un argument numeric specifică text sau o referință de celulă cu o valoare non-numerică.
• O funcție care iterează (un calcul care se repetă până când o anumită condiție este îndeplinită, cum ar fi IRR sau BET), nu poate calcula un rezultat (de exemplu, condiția specificată nu a fost îndeplinită).
• Rezultatul calculului este un număr prea mare sau prea mic pentru a fi utilizat în Excel. Vă reamintim că număr maximîn Excel, nu poate depăși 10307 și poate fi mai mic de minus 10307.

Un astfel de mesaj ne spune că referința celulei este nevalidă. Cel mai adesea, acest lucru poate indica faptul că celulele la care se face referire prin formulă au fost șterse sau alte valori au fost copiate în aceste celule.

Eroare #VALUE!

Această eroare apare atunci când este utilizat tipul greșit de argument sau operand. De exemplu, un utilizator poate face o greșeală în următoarele:

• În loc de un număr sau de o valoare logică (adevărat sau fals), a fost introdus text în formulă.
• După introducerea sau editarea unei formule matrice, apăsați ENTER în loc de CTRL + SHIFT + ENTER.
• O referință, o formulă sau o funcție este specificată ca o matrice.
• Un interval este specificat ca o condiție de funcție care necesită o singură valoare.

Mi-am dat seama ce înseamnă eroarea. Dar formula mea este foarte mare / funcționează cu multe date. Cum pot găsi locul unde a fost făcută greșeala?

Excel 2007 poate indica eroarea imediat sau vă poate ajuta să parcurgeți calculele pentru a găsi eroarea. Pentru a face acest lucru, faceți clic pe pictograma care a apărut lângă celula cu mesajul de eroare. Dacă formula constă dintr-o singură funcție sau motivul erorii este suficient de simplu, meniul contextual va conține elementul Sursă erori. După ce faceți clic pe acest articol, Excel vă va afișa automat o celulă cu date incorecte:

Dacă formula conține mai multe funcții, atunci în meniul contextual Apare Afișare pași de calcul. În fereastra care se deschide, puteți vedea ce calcul duce la eroare.

Am primit eroarea #NAME? și nu pot găsi motivul.

• Verificați ortografia numelui funcției (titlu). Pentru a face acest lucru, puteți apela Function Wizard (grupul Function Library din fila Formule) și selectați funcția dorită din lista propusă.
• Textul care nu este inclus în formulă a fost introdus într-o formulă. ghilimele duble... Să presupunem că rezultatul formulei a fost să fie un text cu un număr - de exemplu, Total 200 r. Dacă textul nu este cuprins între ghilimele duble atunci când introduceți textul Total într-o formulă, Excel interpretează datele ca un nume, ceea ce generează o eroare.

Notă: Un nume este un cuvânt, o expresie sau un alt set de caractere definit de utilizator care desemnează o celulă, un interval de celule, o formulă sau o constantă.

• Formula sau funcția utilizează un nume care nu a fost definit. Verifică asta prenume există. În fila Formule, în grupul Celule denumite, selectați Manager nume și vedeți dacă numele dat este în listă. Dacă acest nume nu este prezent, îl puteți adăuga alegând Creare nume.
• A existat o greșeală de ortografie a numelui. Pentru a verifica dacă numele sunt corecte în bara de formule, apăsați F3, faceți clic pe numele dorit, apoi faceți clic pe OK.
• Formula sau funcția utilizează o referință la o altă foaie care nu este cuprinsă între ghilimele simple. Dacă numele unei alte foi sau cărți conține caractere nealfabetice sau un spațiu, atunci acest nume trebuie inclus în apostrofe (").

eu am bază imensă date despre clienți – cumpărători. În fiecare zi, în baza de date se adaugă o coloană care arată valoarea veniturilor de la fiecare client. Dacă cumpărătorul nu a cumpărat nimic, în celulă se pune # N/A. Cum însumez o coloană dacă există astfel de date?

Cel mai simplu mod de a rezolva această problemă este schimbarea principiului de completare a bazei de date și nu completarea celulelor pentru clienții care nu au făcut achiziții.

Dar dacă masa principală nu poate fi schimbată (prin motive diferite), puteți încerca să rezolvați această problemă folosind funcția BDSUMM.

Să presupunem că tabelul original arată astfel:

Folosind funcția BDSUMM, putem selecta pentru însumare acele celule care nu conțin valoarea # N / A. Funcția BDSUMM are următoarea sintaxă:

BDSUMM (bază de date, câmp, condiții), unde

• baza de date este intervalul de celule asupra cărora se va acționa.
• câmpul este coloana din care vor fi însumate valorile.
• condițiile sunt un set de condiții care trebuie îndeplinite pentru valoarea unei celule pentru ca valoarea respectivă să fie însumată.

Adică, în funcție, trebuie să setăm însumarea primei coloane a tabelului nostru, cu condiția ca valoarea celulei acestei coloane să nu fie egală cu # N / A. Pentru a face acest lucru, creați mai întâi o condiție - în orice loc convenabil pe foaia de lucru, scrieți condiția # N / A și etichetați această celulă în același mod ca și coloana bazei de date de care avem nevoie.

BDSUMM (C2: C18; 1; E2: E3) unde

• C2: C18 este masa noastră,
• E2: E3 este condiția noastră.

Vă rugăm să rețineți că intervalele bazei de date și condițiile trebuie să conțină anteturi de coloană! În acest caz, antetul condiției trebuie să se potrivească cu antetul coloanei pe care se efectuează calculele!

Rezultatul nostru este listat mai jos de tabel:

Dacă baza noastră de date conține erori tipuri diferite, este suficient să le adăugați la condiții (și, în consecință, să extindeți gama de condiții din formulă), pentru a obține rezultatul dorit.

Această metodă poate fi folosită și pentru funcțiile COUNT, BCOUNT, DMAX, DMIN, BDPROIZVED și altele asemenea.

După introducerea sau corectarea unei formule, precum și la modificarea oricărei valori a unei funcții, se întâmplă să apară o eroare de formulă, și nu valoarea necesară. În total, editorul de foi de calcul recunoaște șapte tipuri principale de astfel de calcule incorecte. Mai jos vom analiza cum arată erorile în Excel și cum să le remediam.

Mai jos va prezentam o descriere a formulelor prezentate in poza cu informatii detaliate pentru fiecare greseala.

1. #CAZ!- „diviziunea la 0”, apare cel mai adesea când se încearcă împărțirea la zero. Adică, formula încorporată în celulă, care îndeplinește funcția de divizare, dă peste o celulă în care cu valoare zero sau este „Gol”. Pentru a remedia problema, verificați toate celulele implicate în calcule și corectați toate valorile nevalide. A doua acțiune care duce la # DIV / O! - aceasta este introducerea unor valori incorecte în unele funcții, cum ar fi = MEDIE (), dacă calculul în intervalul de valori este 0. Același rezultat va provoca celule goale, care sunt accesate printr-o formulă care necesită date specifice de calculat.
2. # N / A- "nu există date". Așa marchează Excel valori pe care formula (funcția) nu le poate înțelege. Introducând numere nepotrivite în funcție, veți arunca cu siguranță această eroare. Când apare, asigurați-vă că toate celulele de intrare sunt completate corect, și mai ales în cele în care este aprinsă aceeași inscripție. Frecvent atunci când este utilizat
3. #NUME? - „Nume invalid”, un indicator al denumirii incorecte a unei formule sau a unei părți a acesteia. Problema dispare dacă verificați și corectați toate numele și numele care însoțesc algoritmul de calcul.
4. # GOL!- „valoare goală în interval”, un semnal că undeva în calcul sunt scrise zone care nu se intersectează sau se pune un decalaj între intervalele specificate. O greșeală destul de rară. O intrare eronată poate arăta astfel:

SUMA (G10: G12 I8: J8)

Excel nu recunoaște astfel de comenzi.
5. #NUMĂR!- o eroare este cauzată de o formulă care conține un număr care nu corespunde intervalului specificat.
6. #LEGĂTURĂ!- avertizează că celulele asociate cu această formulă au dispărut. Verificați dacă celulele indicate în formulă au fost șterse.
7. #VALOARE!- tipul de argument pentru funcție este selectat incorect.

8. Bonus, greseala ##### - lățimea celulei nu este suficientă pentru a afișa numărul întreg

În plus, Excel emite un avertisment despre o formulă incorectă. Programul va încerca să vă spună exact cum să aranjați semnele de punctuație (de exemplu, paranteze). Dacă opțiunea propusă îndeplinește cerințele dvs., faceți clic pe „Da”. Dacă solicitarea necesită o ajustare manuală. Apoi alegeți „Nu” și rearanjați singuri parantezele.

Erori în Excel. Utilizarea funcției ISERROR () pentru Excel 2003

Funcția ajută la eliminarea corectă a erorilor în Excel. Funcționează prin găsirea erorilor în celule, dacă găsește o eroare într-o formulă, returnează TRUE și invers. Combinat cu = IF (), va înlocui valoarea dacă este găsită o eroare.

Formula de lucru: = IF (EROARE (expresie); eroare; expresie).

DACĂ (ISEROARE (A1 / A2); ””; A1 / A2)

Explicație: dacă se găsește o eroare în timpul executării A1 / A2, va fi returnat un ("") gol. Dacă totul a mers corect (adică ISERROR (A1 / A2) = FALSE), atunci se calculează A1 / A2.

Erori în Excel.Se aplică IFERROR () pentru Excel 2007 și versiuni ulterioare

Unul dintre motivele pentru care am trecut rapid la Excel 2007 a fost IFERROR () (cel mai mult Motivul principal- aceasta )

Funcția de eroare if conține atât capabilitățile ISERROR () cât și IF (), dar este disponibilă în versiunile mai noi de Excel, ceea ce este foarte convenabil

Instrumentul este activat după cum urmează: = IFERROR (valoare; valoare la eroare). În loc de „valoare”, se pune o expresie calculată/referință de celulă și în loc de „valoare la eroare” - ce ar trebui returnat atunci când apare o inexactitate, de exemplu, dacă în timpul calculului A1 / A2 #CAZ! atunci formula va arăta astfel:

ESLIOSHIBKA (A1 / A2; "")

"Descarcă aici

Liste și intervale (5)
Macrocomenzi (proceduri VBA) (63)
Diverse (39)
Erori și erori Excel (3)

Cum să afișați 0 în loc de o eroare într-o celulă cu o formulă

Sunt situații când în registru de lucru sunt multe formule create pe foile care îndeplinesc diverse sarcini. În același timp, formulele au fost create cândva, poate chiar și pe tine. Și formulele returnează erori. De exemplu # DIV / 0! (# DIV / 0!). Această eroare apare dacă împărțirea la zero are loc în interiorul unei formule: = A1 / B1, unde B1 conține zero sau gol. Dar pot exista și alte erori (# N/A, #VALOARE! Etc.). Puteți schimba formula adăugând o verificare a erorilor:

= IF (ISERR (A1 / B1), 0, A1 / B1)
argumente:
= IF (EOSH (1 argument), 2 argument, 1 argument)
Aceste formule vor funcționa în orice versiune de Excel. Adevărat, funcția EOS nu va gestiona eroarea # N / A (# N / A). Pentru a procesa și # N / A, trebuie să utilizați funcția ISERROR:
= DACĂ (ISEROARE (A1 / B1), 0, A1 / B1)
= DACĂ (ISEROARE (A1 / B1), 0, A1 / B1)
Totuși, mai departe în text voi folosi EOS (pentru că este mai scurt) și, în plus, nu este întotdeauna necesar să „nu vezi” eroarea # N/A.
Dar pentru versiuni Excel 2007 și mai sus, poate fi aplicată o funcție puțin mai optimizată DACA EROARE:
= DACĂ EROARE (A1 / B1,0)
= DACĂ EROARE (A1 / B1,0)
argumente:
= IFEROARE (1 argument, 2 argument)

1 argument: expresie de evaluat
Argumentul 2: valoarea sau expresia care trebuie returnată în celulă dacă apare o eroare în primul argument.

De ce este IFERROR mai bun și eu îl numesc mai optimizat? Să aruncăm o privire mai atentă la prima formulă:
= IF (EOSH (A1 / B1); 0; A1 / B1)
Dacă calculăm pas cu pas, vom vedea că mai întâi se calculează expresia A1 / B1 (adică diviziunea). Și dacă rezultatul său este o eroare, atunci EOSH va returna TRUE, care va fi transmis IF. Și apoi funcția IF va returna valoarea din al doilea argument 0.
Dar dacă rezultatul nu este eronat și ISERR returnează FALSE, atunci funcția va recalcula expresia calculată anterior: A1 / B1
Cu formula de mai sus, aceasta nu joacă un rol special. Dar dacă o formulă precum CĂUTARE V este folosită cu privire la câteva mii de linii, atunci calculul de două ori poate crește semnificativ timpul de recalculare al formulelor.
Funcția IFERROR evaluează expresia o dată, își amintește rezultatul și, dacă este eronată, returnează ceea ce este scris ca al doilea argument. Dacă nu există nicio eroare, atunci returnează rezultatul stocat al evaluării expresiei din primul argument. Acestea. calculul are loc de fapt o singură dată, ceea ce practic nu va afecta viteza recalculării generale a formulelor.
Prin urmare, dacă aveți Excel 2007 și versiuni ulterioare și fișierul nu va fi folosit pentru mai mult versiuni timpurii- atunci are sens să folosiți IFERROR.

De ce corectați formulele cu erori? Se face de obicei pentru o afișare mai estetică a datelor din rapoarte, mai ales dacă rapoartele sunt apoi trimise conducerii.

Deci, există astfel de formule pe foaie, ale căror erori trebuie procesate. Dacă există una sau două formule similare pentru corectare (da, chiar 10-15), atunci aproape că nu există probleme de înlocuit manual. Dar dacă există câteva zeci sau chiar sute de astfel de formule, problema devine aproape universală la scară :-). Cu toate acestea, procesul poate fi simplificat prin scriere relativ cod simplu Visual Basic pentru Aplicare.
Pentru toate versiunile de 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 &") "If Left (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 .Număr Apoi MsgBox „Formulele procesate”

Sub IfIsErrNull () Const sToReturnVal As String = "0" "dacă este necesar să returnați gol în loc de zero" 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 „Intervalul selectat nu conține date”, vbInformation, „www..HasFormula Then s = rc.Formula s = Mid (s, 2) ss =" = "&" IF (ISERR ("& s &"), "& sToReturnVal &", "& s &") "If Left (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 .Număr Apoi MsgBox „Nu se poate transforma formula în celulă:” & ss & vbNewLine & _ Err.Description, vbInformation, „www..site” End If End Sub

Pentru versiunile 2007 și mai mari

Sub IfErrorNull () Const sToReturnVal As String = "0" „dacă este necesar să se întoarcă gol în loc de zero „Const sToReturnVal As String =" "" "" " Dim rr As Range, rc As Range Dim s As String, ss As String La eroare Reluare Următorul set rr = Intersect (Selection, ActiveSheet.UsedRange) Dacă rr este nimic, atunci MsgBox „Intervalul selectat nu conține date”, vbInformation, "www.site" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid (s, 2) ss = "=" & "IFERROR (" & s & ", „& sToReturnVal &”) „Dacă a rămas (s, 8)<>"IFEROARE ("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 Apoi MsgBox „Nu se poate converti formula în celulă:”& ss & vbNewLine & _ Err.Description, vbInformation, „www.site” Else MsgBox „Formulele procesate”, vbInformation, „www.site” End If End Sub

Sub IfErrorNull () Const sToReturnVal As String = "0" "dacă este necesar să returnați gol în loc de zero" 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 „Intervalul selectat nu conține date”, vbInformation, „www..HasFormula Then s = rc.Formula s = Mid (s, 2) ss =" = "&" IFERROR ("& s &", "& sToReturnVal &") "Dacă a rămas (s, 8)<>"IFEROARE ("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 Apoi MsgBox „Nu se poate transforma formula în celulă:” & ss & vbNewLine & _ Err.Description, vbInformation, „www..site” End If End Sub

Cum functioneaza
Dacă nu sunteți familiarizat cu macrocomenzi, atunci mai întâi este mai bine să citiți cum să le creați și să le numiți: Ce este o macrocomandă și unde o pot găsi? de cand se poate întâmpla să faceți totul bine, dar uitați să activați macrocomenzi și nimic nu va funcționa.

Copiați codul de mai sus, accesați editorul VBA ( Alt+F11), creați un modul standard ( Introduce -Modul) și doar lipiți acest cod în el. Du-te la dreapta Caiet de lucru Excelși selectați toate celulele, formulele în care trebuie transformate, astfel încât în ​​cazul unei erori să returneze zero. presa Alt+F8, alege codul IfIsErrNull(sau IfErrorNull, în funcție de care ați copiat) și apăsați A executa.
O funcție de tratare a erorilor va fi adăugată la toate formulele din celulele selectate. Codurile date iau în considerare și:
- dacă formula a aplicat deja funcția IFERROR sau IF (EOSH), atunci o astfel de formulă nu este procesată;
-codul va procesa corect si functiile matricei;
- puteți selecta celule neadiacente (prin Ctrl).
Care este dezavantajul: formulele matrice complexe și lungi pot provoca o eroare de cod, datorită particularității acestor formule și procesării lor din VBA. În acest caz, codul va scrie despre imposibilitatea de a continua lucrul și va evidenția celula cu probleme. Prin urmare, vă recomand cu căldură să faceți înlocuiri pe copiile fișierelor.
Dacă valoarea erorii trebuie înlocuită cu goală și nu zero, atunci aveți nevoie de linie

„Const sToReturnVal As String =" "" "" "

Eliminați apostroful ( " )

Poti de asemenea codul dat apelați-l apăsând un buton (Cum să creați un buton pentru a apela o macro pe o foaie) sau plasați-l într-un add-in (Cum să vă creați propriul add-in?), astfel încât să îl puteți apela din orice fișier.

Și un mic plus: încercați să aplicați codul cu grijă. Nu întotdeauna împiedică returnarea unei erori. De exemplu, pentru folosind VLOOKUP uneori este util să vedem care valori nu au fost găsite.
De asemenea, vreau să remarc că ar trebui aplicat formulelor cu adevărat funcționale. Pentru că dacă formula returnează #NUME! (#NUME!), înseamnă că un argument este scris incorect în formulă și aceasta este o eroare în scrierea formulei și nu o eroare în rezultatul calculului. Este mai bine să analizați astfel de formule și să găsiți eroarea pentru a evita erorile logice ale calculelor de pe foaie.

A ajutat articolul? Distribuie link-ul prietenilor tăi! Lecții video

("Bara de jos" :( "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomstânga", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" stânga "," texteffectslide " : 30, „texteffectdelay”: 500, „texteffectseparate”: false, „texteffect1”: „diapozitiv”, „texteffectslidedirection1”: „dreapta”, „texteffectslidedistance1”: 120, „texteffecteasing1”: „easeOutCubic”, „texteffect6001”: , "texteffectdelay1": 1000, "texteffect2": "diapozitiv", "texteffectslidedirection2": "dreapta", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "text:texteffectdelay "002" :" afișare: bloc; umplutură: 12px; text-align: stânga; "," textbgcss ":" afișare: bloc; poziție: absolut; sus: 0px; stânga: 0px; lățime: 100%; înălțime: 100% ; fundal -culoare: # 333333; opacitate: 0,6; filtru: a lpha (opacitate = 60); "," titlecss ":" display: block; poziție: relativă; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; culoare: #fff; "," descriptioncss ":" afișare: bloc; poziție: relativă; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; culoare: #fff; margin-top: 8px; "," butoncss ":" afișare: bloc; poziție: relativă; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" dimensiunea fontului: 12px; "," descriptioncssresponsive ":" afișare: niciunul! important; "," buttoncssresponsive ": „”, „addgooglefonts”: false, „googlefonts”: „”, „textleftrightpercentforstatic”: 40))

În cazul calculelor eronate, formulele afișează mai multe tipuri de erori în loc de valori. Consideră-le la exemple practiceîn procesul de lucru a formulelor care au dat rezultate eronate ale calculelor.

Erori de formulă Excel afișate în celule

V această lecție va descrie valorile de eroare ale formulelor pe care celulele le pot conține. Cunoscând semnificația fiecărui cod (de exemplu: #VALOARE !, # DIV / 0 !, #NUMĂR !, # N / A !, #NUME !, #NU !, #REF!), vă puteți da seama cu ușurință cum să faceți găsiți o eroare în formulă și eliminați-o.

Cum să eliminați # DIV / 0 în Excel

După cum se vede la împărțirea printr-o celulă cu valoare goală programul îl percepe ca împărțire cu 0. Ca rezultat, scoate valoarea: # DIV / 0! Puteți verifica acest lucru cu ajutorul unui indiciu.

În altele calcule aritmetice(înmulțire, adunare, scădere) celulă goală este de asemenea nulă.



Rezultatul unui calcul eronat este #NUM!

Număr nevalid: # NUMBER! Este o eroare de incapacitate de a efectua un calcul într-o formulă.

Câteva exemple practice:

Eroare: #NUM! apare când valoare numerică prea mare sau prea mic. Același fel eroare dată poate apărea atunci când încercați să obțineți o rădăcină dintr-un număr negativ. De exemplu, = ROOT (-25).

În celula A1 - de asemenea număr mare(10 ^ 1000). Excel nu poate funcționa cu așa ceva numere mari.

Celula A2 are aceeași problemă cu numerele mari. Aparent 1000 numar mic, dar returnarea factorilor sale are ca rezultat o valoare numerică prea mare pe care Excel nu o poate gestiona.

În celula A3 - Rădăcină pătrată nu poate fi cu un număr negativ, dar programul este afișat rezultatul dat aceeasi eroare.

Cum să eliminați ND în Excel

Valoarea nu este disponibilă: # N/A! - înseamnă că valoarea nu este disponibilă pentru formula:

Formula scrisă în B1: = CĂUTARE („Maxim”; A1: A4) caută conținutul text „Maxim” în intervalul de celule A1: A4. Conținutul a fost găsit în a doua celulă A2. Prin urmare, funcția returnează rezultatul 2. A doua formulă caută conținutul text „Andrey”, apoi intervalul A1: A4 nu conține astfel de valori. Prin urmare, funcția returnează eroarea # N / A (fără date).

Eroare #NAME! în Excel

Aparțin categoriei erorilor în funcțiile de scriere. Nume nevalid: #NAME! - înseamnă că Excel nu a recunoscut textul scris în formulă (numele funcției = SUM () îi este necunoscut, este scris cu o eroare). Acesta este rezultatul unei erori de sintaxă în timpul scrierii numelui funcției. De exemplu:

Eroare # EMPTY! în Excel

Set gol: # GOL! - acestea sunt erori ale operatorului de intersecție a mulțimilor. În Excel, există așa ceva ca intersecția mulțimilor. Se aplică la primire rapida date din tabele mari la cererea punctului de intersecție a intervalului vertical și orizontal de celule. Dacă intervalele nu se suprapun, programul afișează o valoare eronată - # NULL! Operatorul de intersecție set este un singur spațiu. Ele separă intervalele verticale și orizontale specificate în argumentele funcției.

În acest caz, intersecția intervalelor este celula C3 și funcția își afișează valoarea.

Argumentele date în funcție: = SUM (B4: D4 B2: B3) - nu formează o intersecție. Prin urmare, funcția dă o valoare cu o eroare - # NULL!

#LEGĂTURĂ! - eroarea referințelor celulelor Excel

V acest exemplu eroarea a apărut când formula a fost copiată incorect. Avem 3 game de celule: A1: A3, B1: B4, C1: C2.

Sub primul interval din celula A4, introduceți formula de însumare: = SUM (A1: A3). Și apoi copiați aceeași formulă sub al doilea interval, în celula B5. Formula, ca și mai înainte, însumează doar 3 celule B2: B4, ocolind valoarea primului B1.

Când aceeași formulă a fost copiată sub al treilea interval, funcția a returnat eroarea #REFERENCE! În celula C3. Deoarece pot exista doar 2 celule deasupra celulei C3 și nu 3 (așa cum este cerut de formula originală).

Notă. V în acest caz cel mai convenabil este să apăsați o combinație de fierbinte tastele ALT+ =. Apoi va fi inserată funcția de însumare și va determina automat numărul de celule de însumare.

Cum se remediază VALUE în Excel

#VALOARE! - eroare de valoare. Dacă încercăm să adăugăm un număr și un cuvânt în Excel, rezultatul va fi o #VALOARE! Un fapt interesant este că, dacă am încercat să adăugăm două celule, în care valoarea primei este un număr, iar a doua este un text folosind funcția = SUM (), atunci nu va apărea o eroare, iar textul va lua valoarea 0 în timpul calculului. De exemplu:

Grile într-o celulă Excel

Rând de hash în loc de valoarea celulei ###### - această valoare nu este o eroare. Sunt doar informații că lățimea coloanei este prea îngustă pentru a găzdui conținutul celulei afișat corect. Trebuie doar să extindeți coloana. De exemplu, face dublu click cu butonul stâng al mouse-ului pe marginea titlurilor de coloană ale acestei celule.

Deci hash (######) în loc de valorile celulei pot fi văzute atunci când data este negativă. De exemplu, încercăm să luăm de la veche întâlnire noua data... Și ca rezultat al calculului, formatul celulelor este „Dată” (nu „General”).

Un format de celulă incorect poate afișa și o serie de caractere hash (######) în loc de valori.