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

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

Използвайте падащото меню по-долу или преминете към една от другите области:

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

Коя функция използвате?

AVERAGE

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

CONCATENATE

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

COUNTIF, COUNTIFS

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

DATEVALUE

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

DAYS

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

FIND, FINDB

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

IF

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

INDEX, MATCH

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

SEARCH, SEARCHB

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

SUM

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

SUMIF, SUMIFS

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

SUMPRODUCT

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

TIMEVALUE

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

TRANSPOSE

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

VLOOKUP

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

* Нито едно от горните.

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

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

Как да правите основно изваждане

Ако сте начинаещ в 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).

#VALUE! с основно изваждане

Ако използвате 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 вижте Изчисляване на разликата между две дати.

Грешка #VALUE! при изваждане на дати, съхранени като текст

Направете колоната с дата по-широка. Ако вашата дата е подравнена вдясно, то това е дата. Ако обаче тя е подравнена отляво, това означава, че датата всъщност не е дата. Тя е текст. И 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 (или Изображение на иконата на бутона MAC Command + 1 на Mac).
  4. Изберете друга езикова променлива, в която например се използва формат дд/мм/гггг, например Английски (Обединеното кралство). Когато приключите с прилагането на формата, резултатът е 31/12/2017 г. и това е истинска дата, а не текстова дата.

Забележка

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

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

Премахване на интервали, които предизвикват #VALUE!

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

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

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

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

Home tab > Търсене & избор > на заместване В раздела "Начало" изберете "Търсене" & изберете>"Заместване".

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! #VALUE!. Например вместо =A2+B2+C2 въведете =SUM(A2:C2). Или вместо =A2*B2 въведете =PRODUCT(A2,B2).

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

Откриване на източника на грешката

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

Клетка H4 с формула =E2+E3+E4+E5 и резултат #VALUE! Първо изберете клетката с #VALUE! грешка.

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

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

Заместване на грешката #VALUE! с нещо друго

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

Предупреждение

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

Клетка с #VALUE!

Клетка H4 с =E2+E3+E4+E5 и резултат #VALUE! Ето пример за формула, която има #VALUE! поради скрит интервал в клетка E2:

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

Клетка H4 с =IFERROR(E2+E3+E4+E5,--) И ето същата формула с IFERROR, добавена към формулата. Можете да прочетете тази формула по следния начин: "Изчисли формулата, но ако има някаква грешка, я замести с две тирета." Обърнете внимание, че можете също да използвате "", за да не се показва нищо вместо двете тирета. Или можете да поставите свой собствен заместващ текст, като например: "Пълна грешка".

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

Уверете се, че има връзки с данни

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

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

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

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

Вж. също

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

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