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

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

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

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

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

1. За Excel в Windows отидете на Опции за > на файлове > формули или
   за Excel на Mac изберете менюто на Excel > Предпочитания > проверката за грешки. 

   В Excel 2007 изберете бутона Microsoft 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 вмъква грешка до формулата =SUM(D2:D4), когато се приложи това правило, защото клетките D5, D6 и D7 са съседни на клетките, към които препраща формулата, и клетката, съдържаща формулата (D8), и тези клетки съдържат данни, към които трябва да има препратка във формулата.

     

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

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

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

     

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

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

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

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

3. Ако преди това сте игнорирали грешки, можете отново да проверите за тези грешки, като направите следното: отидете на Опции за > на файл > формули. За Excel на Mac изберете менюто на Excel > Предпочитания > проверката за грешки.

   В секцията Проверка за грешки изберете Нулиране на игнорираните грешки > 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)
Коригиране на грешка #N/A	Excel показва тази грешка, когато не е налична стойност за функция или формула. Ако използвате функция като VLOOKUP, това, което се опитвате да търсите, има ли съвпадение в диапазона за търсене? Най-често няма. Опитайте да използвате IFERROR, за да избегнете #N/A. В този случай можете да използвате: =IFERROR(VLOOKUP(D2;$D$6:$E$8;2;TRUE);0)
Коригиране на грешка #NAME? грешка	Тази грешка се показва, когато Excel не разпознава текст във формула. Например име на диапазон или името на функция може да е написано неправилно. Забележка: Ако използвате функция, уверете се, че името на функцията е написано правилно. В този случай SUM е изписано неправилно. Премахнете "e" и Excel ще го коригира.
Коригиране на #NULL! грешка	Excel показва тази грешка, когато зададете сечение на две области, които не се пресичат. Операторът за сечение е знакът за интервал, който разделя препратки във формула. Забележка: Уверете се, че диапазоните са правилно разделени – областите C2:C3 и E4:E6 не се пресичат, така че въвеждането на формулата =SUM(C2:C3 E4:E6) връща #NULL! грешка. Поставянето на запетая между диапазоните C и E я коригира =SUM(C2:C3;E4:E6)
Коригиране на грешка #NUM! грешка	Excel показва тази грешка, когато дадена формула или функция съдържа невалидни числови стойности. Използвате функция, която се итераира, например IRR или RATE? Ако е така, #NUM! вероятно защото функцията не може да намери резултат. Вижте помощната тема за стъпките за разрешаване.
Коригиране на грешка #REF! грешка	Excel показва тази грешка, когато препратка към клетка не е валидна. Възможно е например да сте изтрили клетките, към които има препратки от други формули, или да сте поставили клетки, които сте преместили върху клетките, към които препращат други формули. Случайно да сте изтрили ред или колона? Изтрихме колона B в тази формула =SUM(A2;B2;C2) и вижте какво се случва. Използвайте Отмени (Ctrl+Z), за да отмените изтриването, повторно съставете формулата или използвайте препратка към непрекъснат диапазон като тази: =SUM(A2:C2), която ще се актуализира автоматично, когато колона B бъде изтрита.
Коригиране на грешка #VALUE! грешка	Excel може да покаже тази грешка, ако формулата ви включва клетки, които съдържат различни типове данни. Използвате ли математически оператори (+, -, *, /, ^) с различни типове данни? Ако е така, опитайте да използвате функция вместо това. В този случай =SUM(F2:F5) ще коригира проблема.

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

Тази лента с инструменти може да бъде премествана или закачвана както всяка друга лента с инструменти. Например можете да я закачите в долната част на прозореца. Лентата с инструменти следи за следните свойства на клетка: 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) е по-лесна за разбиране, когато можете да видите следните междинни резултати:

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

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

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

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

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

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

5. За да видите отново оценката, изберете Рестартиране.

6. За да приключите оценяването, изберете Затвори.