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

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

#VALUE е начин Excel да каже "Нещо не е наред с начина, по който е въведена вашата формула. Или нещо не е наред клетките, към които правите препратка." Тази грешка е много обща и понякога е трудно да се намери точната причина за това. Информацията на тази страница показва често срещани проблеми и решения за грешката. Може да се наложи да изпробвате едно или повече от решенията, за да коригирате своята конкретна грешка.

Коригиране на грешката за определена функция

Вижте повече информация в Коригиране на грешка #VALUE! във функцията IF

Не виждате функцията си в този списък? Опитайте другите решения, изброени по-долу.

Проблеми с изваждане

Ако сте начинаещ в Excel, може да въвеждате формула за изваждане неправилно. Има два начина да направите това:

Изваждане на препратка към клетка от друга

Клетка D2 с $2000,00, клетка E2 с $1500,00, клетка F2 с формула: =D2-E2 и резултат $500,00

Въведете две стойности в две отделни клетки. В трета клетка извадете едната препратка към клетка от другата. В този пример в клетка D2 е бюджетираната сума, а в клетка E2 е действителната сума. В F2 е формулата = D2-E2.

Или използвайте SUM с положителни и отрицателни числа

Клетка D6 с $2000,00, клетка E6 с $1500,00, клетка F6 с формула: =SUM(D6;E6) и резултат $500,00

Въведете положителна стойност в една клетка, а отрицателна стойност в друга. В трета клетка използвайте функцията SUM, за да съберете двете клетки. В този пример в клетка D6 е бюджетираната сума, а в клетка E6 е действителната сума като отрицателно число. В F6 е формулата =SUM(D6;E6).

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

  1. Първо направете бърз тест. В нова работна книга въведете 2 в клетка A1. Въведете 4 в клетка B1. След това в C1 въведете формулата =B1-A1. Ако получите грешка #VALUE!, преминете към следващата стъпка. Ако не получите тази грешка, опитайте други решения на тази страница.

  2. В Windows отворете контролния панел за вашия регион.

    • Windows 10: Щракнете върху Старт, въведете Регион и след това щракнете върху контролния панел Регион.

    • Windows 8: В началния екран въведете Регион, щракнете върху Настройки и след това щракнете върху Регион.

    • Windows 7: Щракнете върху Старт и след това въведете Региони щракнете върху Регион и език.

  3. В раздела Формати щракнете върху Допълнителни настройки.

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

  5. Щракнете върху OK.

  6. Отворете своята работна книга. Ако една клетка съдържа грешка #VALUE!, щракнете двукратно, за да я редактирате.

  7. Ако има запетаи там, където трябва да има знаци минус за изваждане, ги сменете на знаци минус.

  8. Натиснете клавиша ENTER.

  9. Повтаряйте това за други клетки, в които имат тази грешка.

Изваждане на препратка към клетка от друга

Клетка D10 с 1.1.2016, клетка E10 с 24.4.2016, клетка F10 с формула: =E10-D10 и резултат 114

Въведете две дати в две отделни клетки. В трета клетка извадете едната препратка към клетка от другата. В този пример в клетка D10 е началната дата, а в клетка E10 е крайната дата. В F10 е формулата =E10-D10.

Или използвайте функцията DATEDIF

Клетка D15 с 1.1.2016, клетка E15 с 24.4.2016, клетка F15 с формула: =DATEDIF(D15;E15;"d") и резултат 114

Въведете две дати в две отделни клетки. В трета клетка използвайте функцията DATEDIF, за да намерите разликата в датите. За повече информация относно функцията DATEDIF вижте Изчисляване на разликата между две дати.

Направете колоната с дата по-широка. Ако вашата дата е подравнена вдясно, то това е дата. Ако обаче тя е подравнена отляво, това означава, че датата всъщност не е дата. Тя е текст. И Excel няма да разпознае текста като дата. Ето някои решения, които могат да помогнат за този проблем.

Проверка за водещи интервали

  1. Щракнете двукратно върху дата, която се използва във формула за изваждане.

  2. Поставете курсора в началото и вижте дали можете да изберете един или повече интервали. Ето как изглежда избран интервал в началото на клетка: Клетка с интервал, избрана преди 1.1.2016 г.

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

  3. Изберете колоната, която съдържа датата, като щракнете върху заглавката на нейната колона.

  4. Щракнете върху Данни > Текст в колони.

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

  6. В стъпка 3 от 3 в съветника, под Формат на данните в колоната щракнете върху Дата.

  7. Избор на формат на дата и след това щракнете върху Готово.

  8. Повторете този процес за другите колони, за да се гарантира, че те не съдържат начални интервали преди датите.

Проверете настройките за дата на вашия компютър

Excel използва системата за датиране на компютъра ви. Ако някоя дата в клетка не е въведена със същата система за датиране, то Excel няма да я разпознае като истинска дата.

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

Има две решения на този проблем: Можете да промените системата на датиране, която компютърът ви използва, така че да съответства на системата за датиране, с която искате да въвеждате в Excel. Или в Excel можете да създадете нова колона и да използвате функцията DATE, за да създадете истинска дата въз основа на датата, съхранена като текст. Ето как да направите това, ако допуснем, че системата за датиране е мм/дд/гггг, а вашата текстова дата е 31/12/2017 в клетка A1:

  1. Създайте формула като тази: =DATE(RIGHT(A1;4);MID(A1;4;2);LEFT(A1;2))

  2. Резултатът би бил 12/31/2017.

  3. Ако искате форматът да се появява като дд/мм/гг, натиснете CTRL+1 (или Изображение на иконата за бутона Command на MAC + 1 на Mac).

  4. Изберете друга езикова променлива, в която например се използва формат дд/мм/гггг, например Английски (Обединеното кралство). Когато свършите с прилагането на формата, резултатът ще е 31/12/2017 ще е истинска дата, а не текстова дата.

Забележка : Формулата по-горе е записана с функции DATE, RIGHT, MID и LEFT. Обърнете внимание, че тя е написана с допускането, че текстовата дата има два знака за дни, два знака за месец и четири знака за година. Може да е необходимо да персонализирате формулата, за да отговаря на вашата дата.

Проблеми с интервали и текст

Често #VALUE! възниква, тъй като вашата формула препраща към други клетки, които съдържат интервали, или дори по-сложно – скрити интервали. Тези интервали могат да направят една клетка да изглежда празна, когато не е празна.

1. Изберете клетките от препратките

Избрана колона

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

2. Търсете и заместете

Раздел "Начало" > Намиране и избор > Заместване

В раздела Начало щракнете върху Намиране и избор > Заместване.

3. Заместете интервали с нищо

Намери кое поле съдържа интервал, замести със, съдържащо нищо

В Търси въведете единичен интервал. След това в полето Замести с изтрийте всичко, което може да е там.

4. "Замести" или "Замести всички"

Бутон "Замести всички"

Ако сте уверени, че всички интервали в колоната трябва да бъдат премахнати, щракнете върху Замести всички. Ако желаете да правите заместванията на интервали с нищо едно по едно, можете първо да щракнете върху Намери следващ, а след това да щракнете върху Замести, когато се уверите, че интервалът не е необходим. Когато сте готови, грешката #VALUE! може да е отстранена. Ако не, продължете със следващата стъпка.

5. Включете филтъра

Начало > Сортиране и филтриране > Филтър

Понякога има скрити знаци, различни от интервали, които могат да направят една клетка да изглежда като празна, когато не е наистина празна. Това може да се получи при единични апострофи в клетката. За да се отървете от тези знаци в дадена колона, включете филтъра, като отидете на Начало > Сортиране и филтриране > Филтър.

6. Задайте филтъра

Меню "Филтър" с неизбрано квадратче за отметка "Избери всички", избрано квадратче за отметка (Празни места)

Щракнете върху стрелката за филтър Стрелка за филтриране и след това премахнете отметката от Избери всички. След това поставете отметка в квадратчето Празни места.

7. Изберете всички ненаименувани отметки

Избрано е ненаименувано квадратче за отметка

Изберете всички квадратчета, до които няма нищо, като това.

8. Изберете празните клетки и изтрийте

Избрани филтрирани празни клетки

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

9. Изчистете филтъра

Меню "Филтър", "Изчистване на филтъра от..."

Щракнете върху стрелката за филтър Стрелка за филтриране и след това върху Изчисти филтъра от..., така че всички клетки да са видими.

10. Резултат

Грешката #VALUE! изчезва и е заместена с резултата от формулата. Зелена триъгълник в клетка E4

Ако интервалите са виновник за вашата грешка #VALUE!, тогава се надяваме, че вашата грешка е заместена от резултата от формулата, както е показано тук в нашия пример. Ако не, повторете този процес за други клетки, към които вашата формула препраща. Или опитайте други решения на тази страница.

Забележка : В този пример забележете, че в клетка E4 има зелен триъгълник, а числото е подравнено отляво. Това означава, че числото е съхранено като текст. Това може да доведе до още проблеми по-късно. Ако видите този проблем, ви препоръчваме да конвертирате числата, съхранени като текст, в числа.

Текст и специални знаци в клетка може да предизвикат грешка #VALUE!. Понякога обаче е трудно да видите в кои клетки са тези проблеми. Решение: Използвайте функцията ISTEXT, за да проверите клетките. Обърнете внимание, че ISTEXT не отстранява грешката, а само намира клетките, които може да са причина за грешката.

Пример с #VALUE!

H4 с =E2+E3+E4+E5 и резултат #VALUE!

Ето пример за формула, в която има грешка #VALUE!. Това вероятно се дължи на клетка E2. Има специален знак, който се появява като квадратче след "00". Или, както следващата картина показва, може да използвате функцията ISTEXT в отделна колона, за да проверите за текст.

Същия пример, с ISTEXT

Клетка F2 с =ISTEXT(E2) и резултат TRUE

Тук функцията ISTEXT е добавена в колона F. Всички клетки са наред с изключение на тази със стойност TRUE. Това означава, че клетка E2 съдържа текст. За да решите този проблем, можете да изтриете съдържанието на клетката и да въведете отново стойността 1865,00. Или можете също да използвате функцията CLEAN, за да изчистите знаците, или да използвате функцията REPLACE, за да заместите специалните знаци с други стойности.

След като използвате CLEAN или REPLACE, ще искате да копирате резултата и да използвате Начало > Постави > Специално поставяне > Стойности. Може също да се наложи да преобразувате числа, съхранени като текст, в числа.

Възможно е формулите с математически операции, като например + и *, да не успеят да изчислят клетки, които съдържат текст или интервали. В този случай опитайте да използвате функция вместо тях. Функциите често ще игнорират текстовите стойности и ще изчислят всичко като числа, като премахнат грешката #VALUE!. Например вместо =A2+B2+C2, въведете =SUM(A2:C2). Или вместо =A2*B2 въведете =PRODUCT(A2,B2).

Други решения, които да изпробвате

Изберете грешката

Клетка H4 с формула =E2+E3+E4+E5 и резултат #VALUE!

Първо изберете клетката с грешка #VALUE!.

Щракнете върху "Формули > Изчисляване на формула".

Диалогов прозорец с "Изчисляване на формула" с " "+E3+E4+E5

Щракнете върху Формули > Изчисляване на формула > Оценяване. Excel ще премине през частите на формулата поотделно. В този случай формулата =E2+E3+E4+E5 се прекъсва поради скрит интервал в клетка E2. Не можете да видите интервала, като погледнете в клетка E2. Обаче можете да го видите тук. Той се показва като " ".

Понякога искате да заместите грешката #VALUE с нещо друго, като напр. ваш собствен текст, нула или празна клетка. В този случай можете да добавите функцията IFERROR във вашата формула. IFERROR ще провери дали има грешка, и ако е така, ще замести с друга стойност по ваш избор. Ако няма грешка, оригиналната формула се изчислява. IFERROR ще работи само в Excel 2007 и по-нови версии. За по-стари версии можете да използвате IF(ISERROR()).

Предупреждение : IFERROR ще скрие всички грешки, а не само грешката #VALUE!. Скриването на грешки не се препоръчва, защото грешката често е знак, че нещо трябва да бъде коригирано, а не скрито. Ние не препоръчваме използването на тази функция, ако не сте абсолютно сигурни, че формулата ви работи по желания от вас начин.

Клетка с #VALUE!

Клетка H4 с =E2+E3+E4+E5 и резултат #VALUE!

Ето пример за формула, която има грешка #VALUE! поради скрит интервал в клетка E2:

Грешка, скрита от IFERROR

Клетка H4 с =IFERROR(E2+E3+E4+E5;"--")

И ето същата формула с IFERROR, добавена към формулата. Можете да прочетете тази формула по следния начин: "Изчисли формулата, но ако има някаква грешка, я замести с две тирета." Обърнете внимание, че можете също да използвате "", за да не се показва нищо вместо двете тирета. Или можете да поставите свой собствен заместващ текст, като например: "Пълна грешка".

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

Вашата връзка с данни може да стане достъпна в някакъв момент. За да коригирате това, възстановете връзката с данни или, ако е възможно, помислете за импортиране на данните. Ако нямате достъп до връзката, помолете създателя на работната книга да направи нов файл вместо вас. В новия файл в идеалния случай ще има само стойности и няма да има връзки. Той може да направи това чрез копиране на всички клетки и поставяне само като стойности. За да постави само като стойности, може да щракне върху Начало > Постави > Специално поставяне > Стойности. Това премахва всички формули и връзки и следователно ще премахне всички грешки #VALUE!.

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

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

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

Вж. също

Общ преглед на формулите в Excel

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

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

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

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

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

×