Грешки във формулата в Excel и решения

„Грешките са възможност за подобрение“. Във всяка задача или система възникват грешки. Excel не прави изключение. Докато се опитвате да направите нещо с формула, ще срещнете различни видове грешки в Excel. Тези грешки могат да направят формулата/таблото за управление/отчетите абсолютни отпадъци. И ако не знаете защо е възникнал конкретен тип грешка, може да се наложи да отделите часове, за да ги разрешите.

Докато работя върху Excel, срещнах много грешки в Excel. С известна борба и търсения в Google заобиколих тези грешки. В тази статия ще обясня някои често срещани и досадни грешки в Excel, които възникват в Excel. Ще обсъдим защо възникват тези грешки и как да ги решим.

Какво представляват грешките във формулата на Excel
Докато се прилага формула, която води до дефинирани от Excel грешки (#NA, #VALUE, #NAME и т.н.), се нарича грешки във формулата на Excel. Тези грешки се улавят от excel и се отпечатват върху листовете. Причините за тези грешки могат да бъдат, недостъпни стойности, неправилни типове аргументи, разделяне на 0 и т.н. Те са лесни за улавяне и коригиране.

Логическите грешки не се улавят от Excel и те са най -трудни за поправяне. Непоследователността в данните, неправилното въвеждане на данни, човешките грешки и т.н. са чести причини за тези грешки. Те също могат да бъдат поправени, но отнемат време и усилия. По -добре е да подготвите данните си перфектно, преди да ги извършите.
Захващане на грешки във формулата на Excel:

В Excel има някои специални функции за улавяне и обработка на определен тип грешки (като ISNA функция). Но функцията ISERROR и функцията IFERROR са две функции, които могат да уловят всякакъв вид грешка в Excel (с изключение на логическа).

Грешка в Excel #NA
Грешка #NA възниква в Excel, когато не е намерена стойност. Това просто означава НЕ НАЛИЧЕН. Грешка #NA често се среща с функцията Excel VLOOKUP.

В горното изображение получаваме грешка #NA, когато търсим „Divya“ в колона А. Това е така, защото „Divya не е в списъка.

Решаване на #NA грешка

Ако сте сигурни, че стойността за търсене трябва да съществува в списъка за търсене, първото нещо, което трябва да направите, е да проверите стойността за търсене. Проверете дали стойностите за търсене са написани правилно. Ако не, поправете го.

Второ, можете да направите частично съвпадение с VLOOKUP или която и да е функция за търсене. Ако сте сигурни, че някои части от текста трябва да съвпадат, използвайте това.
Ако не сте сигурни, че стойността съществува или не, тя може да се използва за проверка дали дадена стойност съществува в списъка или не. На изображението по -горе можем да кажем, че Divya не е в списъка.

Ако искате да хванете грешката #NA и да отпечатате или да направите нещо друго, вместо да отпечатате грешка #NA, можете да използвате функцията Excel ISNA. Функцията ISNA връща TRUE, ако функция връща грешка #NA. Използвайки това, можем да избегнем грешка #NA. ISNA работи невероятно с функцията VLOOKUP. Проверете го тук.

Грешка в Excel #VALUE
#VALUE възниква, когато предоставеният аргумент не е от поддържан тип. Например, ако се опитате да добавите два текста с помощта на аритметичен оператор плюс (+), ще получите грешка #VALUE. Същото ще се случи, ако се опитате да получите година с невалиден формат на датата, използвайки функцията YEAR.

Как да поправя грешка #VALUE?

Първо потвърдете типа данни, за който се отнасяте. Ако вашата функция изисква число, уверете се, че се позовавате на число. Ако число е форматирано като текст, използвайте функцията VALUE, за да ги преобразувате в число. Ако дадена функция изисква текст (като функцията DATEVALUE) и визирате номер или тип дата, тогава ги преобразувайте в текст.

Ако очаквате, че може да има грешка #VALUE и искате да ги хванете, тогава можете да използвате ISERR, ISERROR или IFERROR, за да хванете и направите нещо друго.

Грешка в Excel #REF
"Ref" в #REF означава справка. Тази грешка възниква, когато формула се отнася до местоположение, което не съществува. Това се случва, когато изтрием клетки от диапазони, към които се отнася и формула.

В долния gif формулата за сумата се отнася до A2 и B2. Когато изтрия A2, формулата се превръща в грешка #REF.

Решаване на грешка в Excel #REF:
Най -доброто нещо е да внимавате, преди да изтриете клетки в данните. Уверете се, че никоя формула не се отнася за тази клетка.

Ако вече имате грешка #REF, след това проследете, след това я изтрийте от формулата.

Например, след като получите грешка #REF, формулата ви ще изглежда така.

= A2+#REF!

Можете просто да премахнете #REF! От формулата, за да получите формула без грешки. Ако искате да го направите групово, използвайте функцията за намиране и замяна. Натиснете CTRL+H, за да отворите функцията за намиране и замяна. В полето за търсене напишете #REF. Оставете кутията за смяна празна. Натиснете бутона Замени всички.

Ако искате да коригирате препратката към нова клетка, направете го ръчно и заменете #REF! С тази валидна справка.

Грешка в Excel #NAME
#NAME се среща в Excel, когато не може да идентифицира текст във формула. Например, ако сте написали неправилно името на функция, Excel ще покаже грешката #NAME. Ако формула се отнася до име, което не съществува в листа, тя ще покаже грешка #NAME.

В горното изображение клетката B2 има формула = POWERS (A2,2). POWERS не е валидна функция в Excel, следователно връща грешка #NAME.

В клетка B3 имаме = SUM (числа). SUM е валидна функция на excel, но "числови" именен диапазон не съществува на листа. Следователно Excel връща #NAME? Грешка.

Как да избегна грешка #NAME в Excel?

За да избегнете грешка #NAME в Excel, винаги изписвайте правилно имената на функциите. Можете да използвате предложенията на Excel, за да сте сигурни, че използвате валидна функция. Всеки път, когато въвеждаме знаци след знака за равенство, Excel показва функциите и именуваните диапазони на листа, започвайки от този знак/и. Превъртете надолу до името на функцията или името на диапазона в списъка с предложения, натиснете таб, за да използвате тази функция.

Excel #DIV/0! Грешка
Както подсказва името, тази грешка възниква, когато формулата води до деление на нула. Тази грешка може да възникне и когато изтриете някаква стойност от клетка, от която зависи формулата за разделяне.

Как да решим #DIV/0! Грешка.

Това може лесно да се реши, като внимавате с данните и поставите проверка дали формулата ще доведе до #DIV/0! грешка. Ето примерна формула за това.

= IF (B2 = 0, A2, A2/B2)

Ще имаме грешка DIV/0 само ако делителят е 0 или празно. Следователно проверяваме делителя (B2), ако е нула, отпечатайте A2 иначе разделете A2 с B2. Това ще работи и за празни клетки.

Грешка в Excel #NUM

Тази грешка възниква, когато номер не може да се покаже на екрана. Причината може да е, че номерът е твърде малък или твърде голям, за да се покаже. Друга причина може да бъде, че изчислението не може да се извърши с дадено число.

= SQRT (ABS (A3))

Това ще върне 4. Ако искате да получите отрицателната стойност, използвайте отрицателния знак преди функцията. Тогава, разбира се, можете да използвате функцията за обработка на грешки, разбира се.
За решаването на грешка #NUM имаме специална статия. Можете да проверите тук.

#NULL Грешка в Excel
Това е рядък вид грешка. #NULL грешка, причинена от неправилна препратка към клетка. Например, ако искате да дадете препратка към диапазон A2: A5 във функцията SUM, но по грешка въвеждате A2 A5. Това ще генерира грешка #NULL. Както можете да видите на изображението по -долу, формулата връща грешка #NULL.
Ако замените интервала с колона (:), грешката #NULL ще изчезне и ще получите сума от A2: A5. Ако замените интервала със запетая (,), ще получите сума от A2 и A5.

Как да реша грешката #NULL?

Както знаем, грешката #NULL е причинена от печатна грешка. За да избегнете това, опитайте да изберете диапазони с помощта на курсор, вместо да го въвеждате ръчно.

Ще има моменти, когато трябва да въведете адресния диапазон от клавиатурата. Винаги се грижете за колоната за свързващ символ (:) или запетая (,), за да избегнете.

Ако имате съществуваща грешка #NULL, проверете препратките. Най -вероятно сте пропуснали колона (:) или запетая (,) между две препратки към клетки. Заменете ги с подходящ символ и сте готови.
###### Грешка в Excel
Понякога си мислим, че тази грешка е причинена поради недостатъчно място за показване на стойност, но това не е така. В този случай можете просто да разширите ширината на клетката, за да видите стойността в клетката. Няма да го нарека грешка.
Всъщност тази грешка е причинена, когато се опитваме да покажем отрицателна стойност на времето (няма такова нещо като отрицателно време). Например, ако се опитаме да извадим 1 от 12:21:00 ч., Той ще върне ######. В excel първата дата е 1/1/1900 00:00. Ако се опитате да извадите времето, което минава преди това, Excel ще ви покаже грешка ######. Колкото повече разширявате ширината, толкова повече # ще получите. Разработих го в тази статия.

Как да избегнем ###### грешка в Excel?
Преди да правите аритметични изчисления в Excel с времева стойност, имайте предвид тези неща.

Минималната стойност на времето е 1/1/1900 00:00. Не можете да имате валидна дата преди това в Excel
1 е равно на 24 часа (1 ден) в Excel. Докато изваждате часове, минути и секунди, ги преобразувайте в еквивалентни стойности. Например, за да извадите 1 час от 12:21 ч., Трябва да извадите 1/24 от него.

Проследяване на грешки в Excel
Сега знаем какви са общите формули на Excel и защо се появяват. Обсъдихме също какво е възможно решение за всеки тип грешки в Excel.
Понякога в отчетите на Excel получаваме грешки и не можем да знаем откъде всъщност възниква грешката. Трудно е да се решат подобни грешки. За да проследи тези грешки, Excel предоставя функционалност за проследяване на грешки в раздела формула.

Обсъдих подробно проследяването на грешки в excel.

Решаване на логически грешки във формула

Логическите грешки са трудни за намиране и отстраняване. Excel не показва масаж, когато имате логическа грешка във вашата функция. Всичко изглежда добре. Но само вие знаете, че там нещо не е наред. Например, докато получавате процент от част от цялото, ще разделите част на общо (= (част/общо)*100). Винаги трябва да е равно или по -малко от 100%. Когато получите повече от 100%, знаете, че нещо не е наред.

Това беше проста логическа грешка. Но понякога вашите данни идват от няколко източника, в този случай става трудно да се решат логически проблеми. Един от начините е да оцените формулата си.

В раздела формула е налична опцията за оценка на формулата. Изберете формулата и кликнете върху нея. Всяка стъпка от вашето изчисление ще ви бъде показана, което води до крайния ви резултат. Тук можете да проверите къде е възникнал проблемът или къде изчислението се е объркало.

Можете също така да проследите зависими и прецеденти, за да видите от кои препратки зависи вашата формула и какви формули зависят от перикуларна клетка.

Така че да, момчета, това бяха някои често срещани типове грешки, с които се сблъсква всеки потребител на Excel. Научихме защо възниква всеки тип грешка и как можем да ги избегнем. Научихме и за специалната функция за обработка на грешки в excel. В тази статия имаме връзки към свързани страници, които обсъждат подробно проблема. Можете да ги проверите. Ако имате някакъв конкретен вид грешка, която ви дразни, споменете я в секцията за коментари по -долу. Ще получите решението по някакъв начин.