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

Важно : Тази статия е преведена машинно – вижте отказа от отговорност. Английската версия на тази статия за справка можете да намерите тук .

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

Забележка : Тази тема съдържа техники, които могат да ви помогнат да коригирате грешки във формули. Не е изчерпателен списък на методи за коригиране на всеки възможен грешка във формула. За помощ за конкретни грешки можете да търсите въпросите ви във форума на Общността на 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.

Съвпадат всички отвори и затварящи кръгли скоби

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

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

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

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

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

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

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

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

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

Заграждайте имената на другите листове в единични кавички

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

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

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

Във външните работни книги е включен Пътят

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

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

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

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

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

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

Например ако искате да добавите 3100 към стойността в клетка A3, и въведете формула =SUM(3,100,A3), Excel Сумира числата 3 и 100 и след това добавя total стойността от A3, вместо да добавяте 3100 на А3, което ще бъде =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 цифри: клетката съдържа текстова дата, която може да бъде погрешно като грешен century, когато той се използва във формули. Например датата във формулата =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 показва грешка, когато формула пропуска клетки в диапазон
    • Unlocked клетки, съдържащи формули: формулата не е заключена за защита. По подразбиране всички клетки в работен лист са заключени, така че те не могат да бъдат променени, когато работният лист е защитен. Това може да ви помогне да избегнете неволно грешки като случайното им изтриване или промяна на формули. Тази грешка показва, че клетката е била зададена да бъде отключена, но лист не е защитен. Проверете, за да се уверите, че не искате клетката заключена или не.

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

      Да предположим, че искате да изчислите средната стойност на числата в следващата колона от клетки. Ако третата клетка е празна, тя не се включва в изчислението и резултатът е 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 показва тази грешка, когато колона не е достатъчно широка, за да покаже всички знаци в клетка, или клетка съдържа отрицателни стойности за дата или час.

Например формула, която изважда бъдеща дата от дата в миналото, като например = 06/15/2008-07/01/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 не разпознава текст във формула. Например е възможно име на диапазон или името на функцията да е изписано неправилно.

Забележка : Ако използвате функция, уверете се, че името на функцията е написано правилно. В този случай сума е написано правилно. Премахнете "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или Контрола + G на Mac) > Отиди на специални > формули.

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

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

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

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

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

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

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

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

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

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

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

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

Понякога разбиране как вложена формула изчислява крайния резултат е трудно, защото има няколко междинни изчисления и логически проверки. Въпреки това като използвате диалоговия прозорец Изчисляване на формула , можете да видите различните части на вложена формула, изчислени в реда, когато формулата се изчислява. For example, формулата =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 връща стойността на третия аргумент (аргументът value_if_false). Функцията SUM не се изчислява, защото е вторият аргумент за функцията IF (аргументът value_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.

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

Вж. също

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

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

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

Споделяне Facebook Facebook Twitter Twitter Имейл Имейл

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

Чудесно! Друга обратна връзка?

Как можем да подобрим това?

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

×