Когато работите с данни и формули в Excel, непременно ще срещнете грешки.
За да се справи с грешки, Excel е предоставил полезна функция - функцията IFERROR.
Преди да навлезем в механиката на използване на функцията IFERROR в Excel, нека първо преминем през различните видове грешки, които можете да срещнете при работа с формули.
Видове грешки в Excel
Познаването на грешките в Excel ще ви помогне по -добре да идентифицирате възможната причина и най -добрия начин за справяне с тях.
По -долу са видовете грешки, които може да откриете в Excel.
#N/A Грешка
Това се нарича грешка „Стойността не е налична“.
Ще видите това, когато използвате формула за търсене и тя не може да намери стойността (следователно не е налична).
По -долу е даден пример, в който използвам формулата VLOOKUP, за да намеря цената на даден артикул, но той връща грешка, когато не може да намери този елемент в масива от таблици.
#DIV/0! Грешка
Вероятно ще видите тази грешка, когато числото е разделено на 0.
Това се нарича грешка при разделянето. В примера по -долу той дава #DIV/0! грешка, тъй като стойността на количеството (делителят във формулата) е 0.
#ЦЕННОСТ! Грешка
Грешката в стойността възниква, когато използвате неправилен тип данни във формула.
Например, в примера по -долу, когато се опитвам да добавя клетки, които имат числа и знак А, това дава грешка в стойността.
Това се случва, тъй като можете да добавяте само числови стойности, но вместо това се опитах да добавя число с текстов знак.
#REF! Грешка
Това се нарича референтна грешка и ще видите това, когато препратката във формулата вече не е валидна. Това може да е случаят, когато формулата се отнася до препратка към клетка и тази препратка към клетка не съществува (случва се, когато изтриете ред/колона или работен лист, посочен във формулата).
В примера по -долу, докато първоначалната формула беше = A2/B2, когато изтрих колона B, всички препратки към нея станаха #REF! и също така даде #REF! грешка в резултат на формулата.
#ИМЕ ГРЕШКА
Тази грешка вероятно е резултат от грешно написана функция.
Например, ако вместо VLOOKUP, по погрешка използвате VLOKUP, това ще даде грешка в името.
#NUM ГРЕШКА
Num грешка може да възникне, ако се опитате да изчислите много голяма стойност в Excel. Например = 187^549 ще върне грешка в числото.
Друга ситуация, при която можете да получите грешка NUM, е, когато дадете невалиден аргумент за число на формула. Например, ако изчислявате квадратния корен, ако число и дадете отрицателно число като аргумент, той ще върне грешка в числото.
Например, в случай на функция Square Root, ако дадете отрицателно число като аргумент, тя ще върне грешка в числото (както е показано по -долу).
Въпреки че съм показал само няколко примера тук, може да има много други причини, които могат да доведат до грешки в Excel. Когато получите грешки в Excel, не можете просто да го оставите там. Ако данните се използват допълнително при изчисления, трябва да се уверите, че грешките са обработени по правилния начин.
Функцията Excel IFERROR е чудесен начин за справяне с всички видове грешки в Excel.
Функция Excel IFERROR - Общ преглед
Използвайки функцията IFERROR, можете да посочите какво искате формулата да върне вместо грешката. Ако формулата не връща грешка, тогава се връща нейният собствен резултат.
Синтаксис на функцията IFERROR
= IFERROR (стойност, value_if_error)
Аргументи за въвеждане
- стойност - това е аргументът, който се проверява за грешка. В повечето случаи това е или формула, или препратка към клетка.
- value_if_error - това е стойността, която се връща, ако има грешка. Оценени са следните типове грешки: #N/A, #REF !, #DIV/0 !, #VALUE !, #NUM !, #NAME ?, и #NULL !.
Допълнителни бележки:
- Ако използвате „” като аргумент value_if_error, клетката не показва нищо в случай на грешка.
- Ако аргументът value или value_if_error се отнася за празна клетка, тя се третира като празна стойност на низ от функцията Excel IFERROR.
- Ако аргументът стойност е формула на масив, IFERROR ще върне масив от резултати за всеки елемент в диапазона, посочен в стойност.
Функция Excel IFERROR - Примери
Ето три примера за използване на функцията IFERROR в Excel.
Пример 1 - Връщане на празна клетка вместо грешка
Ако имате функции, които могат да върнат грешка, можете да я увиете във функцията IFERROR и да посочите празно като стойност за връщане в случай на грешка.
В примера, показан по -долу, резултатът в D4 е #DIV/0! грешка, тъй като делителят е 0.
В този случай можете да използвате следната формула, за да върнете празно вместо грозната DIV грешка.
= ГРЕШКА (A1/A2, ””)
Тази функция IFERROR ще провери дали изчислението води до грешка. Ако го направи, той просто връща празно място, както е посочено във формулата.
Тук можете също да посочите всеки друг низ или формула за показване вместо празно.
Например формулата по -долу ще върне текста „Грешка“, вместо празната клетка.
= IFERROR (A1/A2, "Грешка")
Забележка: Ако използвате Excel 2003 или предишна версия, няма да намерите функцията IFERROR в нея. В такива случаи трябва да използвате комбинацията от функция IF и функция ISERROR.
Пример 2 - Връща „Не е намерено“, когато VLOOKUP не може да намери стойност
Когато използвате функцията Excel VLOOKUP и тя не може да намери стойността за търсене в определения диапазон, тя ще върне грешката #N/A.
Например по -долу е набор от данни за имена на ученици и техните оценки. Използвал съм функцията VLOOKUP, за да извлека оценките на трима ученици (в D2, D3 и D4).
Докато формулата VLOOKUP в горния пример намира имената на първите двама ученици, тя не може да намери името на Джош в списъка и следователно връща грешката #N/A.
Тук можем да използваме функцията IFERROR, за да върнем празен или смислен текст вместо грешката.
По -долу е формулата, която ще върне „Не е намерено“ вместо грешката.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 12,2,0), „Не е намерено“)
Имайте предвид, че можете също да използвате IFNA вместо IFERROR с VLOOKUP. Докато IFERROR ще третира всички видове стойности на грешки, IFNA ще работи само върху грешките #N/A и няма да работи с други грешки.
Пример 3 - Върнете 0 в случай на грешка
Ако не посочите стойността за връщане от IFERROR в случай на грешка, тя автоматично ще върне 0.
Например, ако разделя 100 на 0, както е показано по -долу, това ще върне грешка.
Ако обаче използвам по -долу функцията IFERROR, тя би върнала 0 вместо това. Имайте предвид, че все още трябва да използвате запетая след първия аргумент.
Пример 4 - Използване на вложен IFERROR с VLOOKUP
Понякога, когато използвате VLOOKUP, може да се наложи да прегледате фрагментираната таблица от масиви. Да предположим например, че имате записи за транзакциите при продажби в 2 отделни работни листа и искате да потърсите номера на артикул и да видите неговата стойност.
За да направите това, трябва да използвате вложен IFERROR с VLOOKUP.
Да предположим, че имате набор от данни, както е показано по -долу:
В този случай, за да намерите резултата за Grace, трябва да използвате следната вложена формула IFERROR:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Not Found"))
Този вид вмъкване на формули гарантира, че получавате стойността от която и да е от таблицата и всяка върната грешка се обработва.
Имайте предвид, че в случай че таблиците са на един и същ работен лист, в пример от реалния живот, той вероятно ще бъде на различни работни листове.
Функция Excel IFERROR - ВИДЕО
- Excel И функция.
- Excel ИЛИ функция.
- Excel НЕ функция.
- Excel IF функция.
- Функция на IFS на Excel.
- Функция Excel FALSE.
- Функция Excel TRUE.
