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

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

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

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

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

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

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

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

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

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

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

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 (или + 1 на Mac).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

10. Резултат

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

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

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

Пример с #VALUE!

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

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

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

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

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

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

Клетка с #VALUE!

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

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

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

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

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

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

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