Откриване на грешки във формули

Формулите понякога могат да връщат грешни стойности, както и непредвидени резултати. Следват някои инструменти, които можете да използвате, за да намерите и да изследвате причините за тези грешки и да намерите решения.

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

Връзка към форума на общността на Excel

Научете как да въведете проста формула

Формулите са математически изрази, които извършват изчисления със стойности в работния лист. Всяка формула започва със знак за равенство (=). Например следващата формула събира 3 с 1:

=3+1

Една формула може да съдържа всички или някои от следните елементи: функции, препратки, оператори и константи.

Части от формула

Части от формула

  1. Функции: включени с Excel, функциите са изградени формули, които извършват определени изчисления. Например функцията PI() връща стойността на числото пи: 3,142...

  2. Препратките: адресират отделни клетки или диапазони от клетки. A2 връща стойността в клетката A2.

  3. Константи: стойности от числа или текст, въведени директно във формула, например 2.

  4. Оператори: Операторът ^ (карета) повдига число на степен, а * (звездичка) умножава. Използвайте + и – за събиране или изваждане на стойности, а "/" за делене.

    Забележка : Някои функции изискват така наречените аргументи. Аргументи са стойностите, които определени функции използват, за да изпълняват своите изчисления. Когато са необходими, аргументите се поставят между скобите на функцията (). Функцията PI не изисква никакви аргументи, затова в скобите няма нищо. Някои функции изискват задължително един или повече аргументи и дават възможност за допълнителни аргументи. Трябва да използвате запетая за разделяне на аргументите или точка и запетая (;) в зависимост от настройките ви за местоположение.

Например функцията SUM изисква задължително само един аргумент, но може да има общо 255 аргумента.

Функция SUM

=SUM(A1:A10) е пример за един аргумент.

=SUM(A1:A10; C1:C10) е пример за няколко аргумента.

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

Задължително е

Още информация

Всяка формула да започва със знак за равенство (=)

Ако пропуснете знака за равенство, това, което въведете, може да се покаже като текст или дата. Ако например въведете SUM(A1:A10), Excel показва текстовия низ SUM(A1:A10) и не извършва изчислението. Ако въведете 11/2, Excel показва датата 2 ное (ако се приеме, че форматът на клетката е Общи) вместо да раздели 11 на 2.

Отварящите и затварящите кръгли скоби да си съответстват

Уверете се, че всички кръгли скоби са част от съответстващи си двойки (отваряща и затваряща скоба). Когато използвате функция във формула, за всяка кръгла скоба е важно да е на правилното място, за да работи функцията правилно. Например формулата =IF(B5<0),"Невалидно";B5*1,05) няма да работи, защото има две затварящи кръгли скоби и само една отваряща, когато трябва да има само по една от двете. Формулата трябва да изглежда така: =IF(B5<0;"Невалидно";B5*1,05).

Да се използва двоеточие за указване на диапазон

Когато препращате към диапазон от клетки, използвайте двоеточие (:) за отделяне на препратката към първата клетка в диапазона и препратката към последната клетка в диапазона. Например =SUM(A1:A5), а не = SUM (A1 A5), което ще върне грешка #NULL!.

Въведете всички задължителни аргументи

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

Да са въведени правилните типове аргументи

Някои функции, като например SUM, изискват числови аргументи. Други функции, като например REPLACE, изискват текстова стойност за поне един от своите аргументи. Ако използвате погрешен тип данни като аргумент, Excel може да върне неочаквани резултати или да покаже грешка.

Да няма влагане на повече от 64 функции

Можете да вкарате, или вложите, до 64 нива на функции в дадена функция.

Имената на други листове да са заградени в единични кавички

Ако формула препраща към стойности или клетки в други работни листове или работни книги и името на другата работна книга или работен лист съдържа интервали или небуквени знаци, трябва да поставите името в единични кавички (' ), като напр. ='Данни за тримесечието'!D3 или ='123'!A1.

Да поставяте удивителен знак (!) след името на работния лист, когато има препратка към него във формула

Например, за да върнете стойността от клетка D3 в работен лист с име "Тримесечни данни" в същата работна книга, използвайте формулата: ='Тримесечни данни'!D3.

Да включите пътя към външните работни книги

Уверете се, че всяка външна препратка съдържа име на работна книга и пътя към работната книга.

Препратката към работна книга включва името на работната книга и трябва да е затворено в квадратни скоби ([ИмеНаРаботнатаКнига.xlsx]). Препратката трябва също така да съдържа името на работния лист в работната книга.

Ако работната книга, към която искате да направите препратка, не е отворена в Excel, все още можете да включите препратка към нея във формула. Предоставяте пълния път до файла както в следния пример: =ROWS('C:\Моите документи\[Операции трм2.xlsx]Продажби'!A1:A8). Тази формула връща броя на редовете в диапазона, който включва клетките от A1 до A8 в другата работна книга (8).

Забележка : Ако пълният път съдържа знаци за интервал, както беше в предишния пример, трябва да оградите пътя в единични кавички (в началото на пътя и след името на работния лист, преди възклицателния знак).

Числата да са въведени без форматиране

Не форматирайте числата, когато ги въвеждате във формули. Ако например стойността, която искате да въведете, е $1,000, въведете във формулата 1000. Ако въведете запетая като част от числото, Excel я третира като знак за разделител. (Важи само в случаите, когато за разделител се използва запетая.) Ако искате числата да са показани с разделители на хилядите или милионите или със символи за валута, форматирайте клетките едва след като въведете числата.

Ако например искате да добавите 3100 към стойността в клетка A3 и въведете формулата =SUM(3,100,A3), Excel събира числата 3 и 100, след което добавя тази сума към стойността от клетка A3, вместо да добави 3100 към A3, което правилно трябва да се въведе =SUM(3100,A3). Например ако въведете формулата =ABS(-2,134), Excel показва грешка, защото функцията ABS приема само един аргумент: =ABS(-2134). (Важи само в случаите, когато за разделител се използва запетая.).

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

Грешки могат да се маркират и коригират по два начина: една по една (като например при правописна проверка) или веднага щом се появят в работния лист, когато въвеждате данни.

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

  1. Щракнете върху Файл > Опции >Формули.

    В Excel 2007 щракнете върху бутона Microsoft Office Изображение на бутона Office > Опции на Excel > Формули.

  2. Под Проверка за грешки отметнете квадратчето Разреши фонова проверка за грешки. Всяка грешка, която бъде намерена, ще бъде маркирана с триъгълник в горния ляв ъгъл на клетката.

    Клетка с проблем във формула
  3. За да промените цвета на триъгълника, който отбелязва мястото на възникване на грешка, в полето Означи грешките, като използваш този цвят изберете желания цвят.

  4. Под Правила за проверка на Excel отметнете или изчистете квадратчетата на някои от следните правила:

    • Клетки съдържащи формули, предизвикващи грешки: Формула не използва очаквания синтаксис, аргументи или типове данни. Стойностите на грешки включват #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!. Всяка от тези стойности на грешка има различни причини и се коригира по различни начини.

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

    • Несъвместима формула за изчисляема колона в таблици: Изчисляемата колона може да включва отделни формули, които са различни от основната формула на колоната, което създава изключение. Изключения на изчисляема колона се създават, когато правите едно от следните неща:

      • Въвеждате данни, които са различни от формула, в клетка на изчисляема колона.

      • Въвеждате формула в клетка на изчисляема колона и след това щраквате върху Ctrl +Z или Отмени Изображение на бутон в лентата с инструменти за бърз достъп.

      • Въвеждате нова формула в изчисляема колона, която вече съдържа едно или повече изключения.

      • Копирате данни в изчисляема колона, която не съответства на формулата на изчисляемата колона. Ако копираните данни съдържат формула, тази формула замества данните в изчисляемата колона.

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

    • Клетки, съдържащи години, представени с 2 цифри: Клетката съдържа текстова дата, която може да бъде отнесена погрешно към неправилен век, когато се използва във формули. Например датата във формулата =YEAR("1.1.31") би могла да се отнася до 1931 или 2031 г. Използвайте това правило, за да проверявате за нееднозначни текстови дати.

    • Числа, форматирани като текст или предхождани от апостроф: Клетката съдържа числа, съхранени като текст. Това обикновено се случва при данни, импортирани от други източници. Числата, съхранени като текст, могат да доведат до неочаквани резултати при сортиране и затова е най-добре да ги преобразувате в числа. "=SUM(A1:A10) се вижда като текст.

    • Формули, които не съответстват на другите формули в областта: Формулата не съответства на шаблона на другите формули около нея. В много случаи формули, които са съседни на други формули, се различават само по използваните препратки. В следващия пример на четири съседни формули, Excel показва грешка до формулата =SUM(A10:C10) в клетка D4, защото съседните формули нарастват през един ред, а тази нараства с 8 реда – Excel очаква формулата =SUM(A4:C4).

      Excel показва съобщение за грешка, когато дадена формула не съответства на схемата на съседните формули

      Ако препратките, използвани в една формула, не са съвместими с тези в съседните формули, Excel показва грешка.

    • Формули, които пропускат клетки в област: Възможно е формулата да не включва автоматично препратки към данните, които вмъквате между първоначалния диапазон от данни и клетката, която съдържа формулата. Това правило сравнява препратката във формула спрямо действителния диапазон от клетки, който е съседен на клетката, съдържаща формулата. Ако съседните клетки съдържат допълнителни стойности и не са празни, Excel показва грешка до формулата.

      Например Excel вмъква грешка до формулата =SUM(D2:D4), когато се приложи това правило, тъй като клетки D5, D6 и D7 са съседни на клетките, към които има препратка във формулата, и клетката, съдържаща формулата (D8), и тези клетки съдържат данни, към които трябва да препраща формулата.

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

    • Формули, препращащи към празни клетки: Формулата съдържа препратка към празна клетка. Това може да причини непредвидени резултати, както е показано в следния пример.

      Да предположим, че искате да изчислите средната стойност на числата в следващата колона от клетки. Ако третата клетка е празна, тя не се включва в изчислението и резултатът е 22,75. Ако третата клетка съдържа 0, резултатът е 18,2.

      Excel показва грешка, когато формула препраща към празни клетки
    • Въведените в таблицата данни са невалидни: Има грешка при проверка на данните в дадена таблица. Проверете настройката за проверка за клетката, като отидете на раздела Данни > групата Инструменти за данни > Проверка на данни.

  1. Изберете работния лист, който искате да проверите за грешки.

  2. Ако работният лист е изчислен ръчно, натиснете F9, за да го преизчислите.

    Ако диалоговият прозорец Проверяване за грешки не се показва, щракнете върху раздела Формули >Проверка на формули > бутона Проверяване за грешки.

  3. Ако преди това сте игнорирали някакви грешки, можете отново да извършите проверка за тези грешки, като направите следното: щракнете върху Файл > Опции > Формули.

    В раздела Проверка за грешки щракнете върху Изчисти игнорираните грешки > OK.

    Проверка за грешки

    Забележка : Нулирането на игнорирани грешки нулира всички грешки във всички листове на активната работна книга.

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

    Преместете прозореца "Проверка за грешки" точно под лентата за формули
  4. Щракнете върху един от бутоните за действия от дясната страна на диалоговия прозорец. Наличните действия са различни при всеки тип грешка.

  5. Щракнете върху Напред.

Забележка : Ако щракнете върху Игнорирай грешката, грешката се маркира, за да бъде игнорирана при всяка следваща проверка.

  1. Щракнете върху бутона Проверяване за грешки Икона на проверка за грешки , който се появява до клетката, и след това върху желаната опция. Наличните команди са различни за всеки тип грешка и първият запис описва грешката.

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

    Преместете прозореца "Проверка за грешки" точно под лентата за формули

Ако формула не може да изчисли правилно резултат, Excel показва стойност за грешка, като например #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, и #VALUE!. За всеки тип грешка има различни причини и различни решения.

Следващата таблица съдържа връзки към статии, които описват тези грешки в подробности, и кратко описание, за да започнете.

Тема

Описание

Коригиране на грешка ####

Excel показва тази грешка, когато колона не е достатъчно широка, за да покаже всички знаци в клетка, или клетка съдържа отрицателни стойности за дата или час.

Например формула, която изважда бъдеща дата от дата в миналото, като например =15.6.2008-1.7.2008, води до отрицателна стойност за дата.

Съвет : Опитайте да приложите автопобиране за клетката чрез двукратно щракване между заглавията на колоните. Ако ### се показва, защото Excel не може да покаже всички знаци, това ще го коригира.

Грешка с #

Коригиране на грешка #DIV/0!

Excel показва тази грешка, когато дадено число е разделено на нула (0) или на клетка, която не съдържа стойност.

Съвет : Добавяне на манипулатор на грешка, като напр. в следващия пример, който е =IF(C2;B2/C2;0)

За случаите на грешки може да се използва функция за обработване на грешки, например IF

Коригиране на грешка #N/A

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

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

Опитайте да използвате IFERROR, за да избегнете #N/A. В този случай можете да използвате:

=IFERROR(VLOOKUP(D2;$D$6:$E$8;2;TRUE);0)

Грешка #N/A

Коригиране на грешка #NAME?

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

Забележка : Ако използвате функция, уверете се, че името на функцията е написано правилно. В този случай SUM е изписано неправилно. Премахнете "e" и Excel ще я коригира.

Excel показва грешка #NAME?, когато в името на функцията има правописна грешка

Коригиране на грешка #NULL!

Excel показва тази грешка, когато зададете сечение на две области, които не се пресичат. Операторът за сечение е знакът за интервал, който разделя препратки във формула.

Забележка : Уверете се, че вашите диапазони са правилно разделени – областите C2:C3 и E4:E6 не се пресичат, така че въвеждането на формулата = SUM(C2: C3 E4:E6) връща грешка #NULL!. Поставянето на точка и запетая между C и E диапазоните ще я коригира – =SUM(C2:C3;E4:E6)

Грешка #NULL!

Коригиране на грешка #NUM!

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

Използвате функция за итерации, като напр. IRR или RATE? Ако е така, #NUM! грешката вероятно се дължи на това, че функцията не може да намери резултат. Вижте помощната тема за стъпките за отстраняване на грешката.

Коригиране на грешка #REF!

Excel показва тази грешка, когато препратка към клетка не е валидна. Например е възможно да сте изтрили клетките, към които сочи препратка от други формули, или да сте поставили клетки, които сте преместили върху клетките, към които сочи препратка от други формули.

Случайно да сте изтрили ред или колона? Изтрихме колона B в тази формула =SUM(A2;B2;C2) и вижте какво се случва.

Използвайте Отмени (Ctrl+Z), за да отмените изтриването, повторно съставете формулата или използвайте препратка към непрекъснат диапазон като тази: =SUM(A2:C2), която ще се актуализира автоматично, когато колона B бъде изтрита.

Excel показва грешка #REF!, когато препратка към клетка не е валидна

Коригиране на грешка #VALUE!

Excel може да покаже тази грешка, ако формулата ви включва клетки, които съдържат различни типове данни.

Използвате ли математически оператори (+, -, *, /, ^) с различни типове данни? Ако е така, опитайте да използвате функция вместо тях: В този случай =SUM(F2:F5) ще коригира проблема.

Грешка #VALUE!

Когато клетки не са видими в работен лист, можете да наблюдавате тези клетки и техните формули на лентата с инструменти в прозореца за наблюдение. Благодарение на прозореца за наблюдение е удобно да следите, проверявате или потвърждавате изчисления и резултати от формули в големи работни листове. С помощта на прозореца за наблюдение не е нужно постоянно да превъртате или да отивате до различни части на вашия работен лист.

Прозорецът "Наблюдение" позволява лесно да следите формули, използвани в работен лист

Тази лента с инструменти може да бъде премествана или закачвана както всяка друга лента с инструменти. Например можете да я закачите в долната част на прозореца. Лентата с инструменти следи за следните свойства на клетка: 1) Работна книга, 2) Лист, 3) Име (ако клетката съдържа съответен наименуван диапазон), 4) Адрес на клетка, 5) Стойност и 6) Формула.

Забележка : Можете да имате само едно наблюдение за всяка клетка.

Добавяне на клетки към прозореца за наблюдение

  1. Изберете клетките, които искате да наблюдавате.

    За да изберете всички клетки в работен лист с формули, в раздела Начало, в групата Редактиране щракнете върху Намиране и избор (или можете да използвате Ctrl+G или Control+G на Mac) > Отиди на специални> Формули.

    Диалоговият прозорец "Специален избор на клетки"
  2. В раздела Формули, в групата Проверка на формули щракнете върху Прозорец за наблюдение.

  3. Щракнете върху Добави наблюдение.

    Щракнете върху "Добавяне на наблюдение", за да добавите наблюдение във вашата електронна таблица
  4. Уверете се, че сте избрали всички клетки, които искате да наблюдавате, и щракнете върху Добави.

    В "Добавяне на наблюдение" въведете диапазона от клетки, които да се наблюдават
  5. За да промените ширината на колона на прозореца за наблюдение, плъзнете границата от дясната страна на заглавието на колоната.

  6. За да покажете клетката, към която сочи запис в лентата с инструменти на прозореца за наблюдение, щракнете двукратно върху записа.

    Забележка : Клетките, които имат външни препратки към други работни книги, се показват в лентата с инструменти на прозореца за наблюдение само когато другите работни книги са отворени.

Премахване на клетки от прозореца за наблюдение

  1. Ако лентата с инструменти на прозореца за наблюдение не се вижда, в раздела Формули, в групата Проверка на формули щракнете върху Прозорец за наблюдение.

  2. Изберете клетките, които искате да премахнете.

    За да изберете няколко клетки, натиснете клавиша CTRL и след това щракнете върху клетките.

  3. Щракнете върху Изтрий наблюдението.

    Изтриване на наблюдението

Понякога разбирането как вложена формула изчислява крайния резултат е трудно, тъй като има няколко междинни изчисления и логически проверки. Като използвате диалоговия прозорец Изчисляване на формула обаче, можете да видите различните части на вложената формула, изчислени в реда, в който се изчислява формулата. Например формулата =IF(AVERAGE(D2:D5)>50;SUM(E2:E5);0) е по-лесна за разбиране, когато можете да видите следните междинни резултати:

"Изчисляване на формула" ви помага да видите как се изчисляват различните части на една вложена формула

В диалоговия прозорец "Изчисляване на формула"

Описание

=IF(AVERAGE(D2:D5)>50;SUM(E2:E5);0)

Първоначално се показва вложената формула. Функциите AVERAGE и SUM са вложени във функцията IF.

Диапазонът от клетки D2: D5 съдържа стойностите 55, 35, 45 и 25 и следователно резултатът от функцията AVERAGE(D2:D5) е 40.

=IF(40>50;SUM(E2:E5);0)

Диапазонът от клетки D2: D5 съдържа стойностите 55, 35, 45 и 25 и следователно резултатът от функцията AVERAGE(D2:D5) е 40.

=IF(False;SUM(E2:E5);0)

Тъй като 40 не е по-голямо от 50, изразът в първия аргумент от функцията IF (аргументът логически_тест) е False.

Функцията IF връща стойността на третия аргумент (аргумент стойност_ако_невярно). Функцията SUM не се изчислява, понеже тя е вторият аргумент за функцията IF (аргумент стойност_ако_вярно) и се връща само ако изразът е True.

  1. Изберете клетката, която искате да изчислите. Може да се изчислява само по една клетка в даден момент.

  2. Изберете раздела Формули > Проверка на формули > Изчисляване на формула.

  3. Щракнете върху Изчисли, за да изследвате стойността на подчертаната препратка. Резултатът от изчислението се показва с курсив.

    Ако подчертаната част от формулата е препратка към друга формула, щракнете върху Стъпка навътре, за да се покаже другата формула в полето Изчисляване. Щракнете върху Стъпка навън, за да се върнете към предишната клетка и формула.

    Бутонът Стъпка навътре не е достъпен за дадена препратка при второто й появяване във формулата или ако формулата препраща към клетка в отделна работна книга.

  4. Продължете да щракате върху Изчисли, докато всяка част от формулата бъде изчислена.

  5. За да видите изчислението отново, щракнете върху Повторно стартиране.

  6. За да приключите изчислението, щракнете върху Затвори.

Забележки : 

  • Някои части от формули, които използват функциите IF и CHOOSE, не се изчисляват – в тези случаи #N/A се показва в полето Изчисляване.

  • Ако дадена препратка е празна, в полето Изчисляване се показва нулева стойност (0).

  • Следните функции се преизчисляват при всяка промяна в работния лист и това може да стане причина диалоговият прозорец Изчисляване на формула да ви показва резултати, които са различни от това, което се вижда в клетката: RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY, RANDBETWEEN.

Имате конкретен въпрос?

Публикувайте въпрос във форума на общността на Excel

Помогнете ни да подобрим на Excel

Имате ли предложения как можем да подобрим следващата версия на Excel? Ако е така, вижте темите в Гласът на потребителите на Excel.

Вж. също

Показване на зависимостите между формули и клетки

Видео: Коригиране на повредени формули (Excel 2010)

Начини за избягване на повредени формули

Разширете уменията си
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×