Исправление ошибки #ЗНАЧ! ошибка

#VALUE — это способ Excel сказать: "Что-то не так с тем, как введена формула. Возможно, что-то не так с ячейками, на которые вы ссылаетесь". Это ошибка очень распространенная, и иногда бывает трудно найти ее точную причину. Сведения на этой странице включают распространенные проблемы и решения ошибки.     

Используйте раскрывающийся список ниже или перейдите к одной из других областей:

• Проблемы с вычитанием
• Проблемы с пробелами и текстом
• Другие решения

Исправление ошибок определенных функций

Какую функцию вы используете?

СРЗНАЧ

См. статью Исправление ошибки #ЗНАЧ! в функции СРЗНАЧ или СУММ

СЦЕПИТЬ

См. статью Исправление ошибки #ЗНАЧ! в функции СЦЕПИТЬ

СЧЁТЕСЛИ, СЧЁТЕСЛИМН

См. статью Исправление ошибки #ЗНАЧ! в функциях СЧЁТЕСЛИ и СЧЁТЕСЛИМН

ДАТАЗНАЧ

См. статью Исправление ошибки #ЗНАЧ! в функции ДАТАЗНАЧ

ДНИ

См. статью Исправление ошибки #ЗНАЧ! в функции ДНИ

НАЙТИ, НАЙТИБ

См. статью Исправление ошибки #ЗНАЧ! в функциях НАЙТИ, НАЙТИБ, ПОИСК и ПОИСКБ

ЕСЛИ

См. статью Исправление ошибки #ЗНАЧ! в функции ЕСЛИ

ИНДЕКС, ПОИСКПОЗ

См. статью Исправление ошибки #ЗНАЧ! в функциях ИНДЕКС и ПОИСКПОЗ

ПОИСК, ПОИСКБ

См. статью Исправление ошибки #ЗНАЧ! в функциях НАЙТИ, НАЙТИБ, ПОИСК и ПОИСКБ

СУММ

См. статью Исправление ошибки #ЗНАЧ! в функции СРЗНАЧ или СУММ

СУММЕСЛИ, СУММЕСЛИМН

См. статью Исправление ошибки #ЗНАЧ! в функциях СУММЕСЛИ и СУММЕСЛИМН

СУММПРОИЗВ

См. статью Исправление ошибки #ЗНАЧ! в функции СУММПРОИЗВ

ВРЕМЗНАЧ

См. статью Исправление ошибки #ЗНАЧ! в функции ВРЕМЗНАЧ

ТРАНСП

См. статью Исправление ошибки #ЗНАЧ! в функции ТРАНСП

ВПР

См. статью Исправление ошибки #ЗНАЧ! в функции ВПР

* Другая функция

Ваша функция не отображается в этом списке? Попробуйте другие решения, перечисленные ниже.

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

Выполнение базового вычитания

Если вы раньше не работали в Excel, вероятно, вы неправильно вводите формулу вычитания. Это можно сделать двумя способами:

Вычтите одну ссылку на ячейку из другой

Введите два значения в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D2 содержит плановую сумму, а ячейка E2 — фактическую. F2 содержит формулу =D2-E2.

Или используйте функцию СУММ с положительными и отрицательными числами

Введите положительное значение в одной ячейке и отрицательное — в другой. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки. В этом примере ячейка D6 содержит плановую сумму, а ячейка E6 — фактическую как негативное число. F6 содержит формулу =СУММ(D6;E6).

Ошибка #ЗНАЧ! при базовом вычитании

Если используется Windows, ошибка #ЗНАЧ! может возникнуть даже при вводе самой обычной формулы вычитания. Проблему можно решить следующим образом.

1. Для начала выполните быструю проверку. В новой книге введите 2 в ячейке A1. Введите 4 в ячейке B1. Затем введите формулу =B1-A1 в ячейке C1. Если возникнет ошибка #ЗНАЧ! перейдите к следующему шагу. Если сообщение об ошибке не появилось, попробуйте другие решения на этой странице.

2. В Windows откройте панель управления "Региональные стандарты".

   • Windows 10: нажмите кнопку "Пуск", введите "Регион" и выберите панель управления "Региональные стандарты".
   • Windows 8: на начальном экране введите регион, выберите "Параметры", а затем выберите пункт "Регион".
   • Windows 7. Нажмите кнопку "Пуск", введите "Регион", а затем выберите "Регион и язык".

3. На вкладке "Форматы " выберите "Дополнительные параметры".

4. Найдите пункт Разделитель элементов списка. Если в поле разделителя элементов списка указан знак "минус", замените его на что-то другое. Например, разделителем нередко выступает запятая. Также часто используется точка с запятой. Однако для вашего конкретного региона может подходить другой разделитель элементов списка.

5. Нажмите кнопку ОК.

6. Откройте книгу. Если ячейка содержит ошибку #VALUE!, дважды щелкните ее для редактирования.

7. Если там, где для вычитания должны быть знаки "минус", стоят запятые, замените их на знаки "минус".

8. Нажмите клавишу ВВОД.

9. Повторите эти действия для других ячеек, в которых возникает ошибка.

Вычитание дат

Вычтите одну ссылку на ячейку из другой

Введите две даты в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D10 содержит дату начала, а ячейка E10 — дату окончания. F10 содержит формулу =E10-D10.

Или используйте функцию РАЗНДАТ

Введите две даты в двух отдельных ячейках. В третьей ячейке используйте функцию РАЗНДАТ, чтобы найти разницу дат. Дополнительные сведения о функции РАЗНДАТ см. в статье Вычисление разницы двух дат.

Ошибка #ЗНАЧ! при вычитании дат в текстовом формате

Растяните столбец по ширине. Если значение выравнивается по правому краю — это дата. Но если оно выравнивается по левому краю, это значит, что в ячейке на самом деле не дата. Это текст. И Excel не распознает текст как дату. Ниже приведены некоторые решения, которые помогут решить эту проблему.

Проверка наличия начальных пробелов

1. Дважды щелкните дату, которая используется в формуле вычитания.
2. Разместите курсор в начале и посмотрите, можно ли выбрать один или несколько пробелов. Вот как выглядит выбранный пробел в начале ячейки:
   Если в ячейке обнаружена эта проблема, перейдите к следующему шагу. Если вы не видите один или несколько пробелов, перейдите к следующему разделу и проверьте параметры даты на компьютере.
3. Выделите столбец, содержащий дату, щелкнув его заголовок.
4. Выберите пункт "Текст данных> по столбцам".
5. Дважды нажмите кнопку "Далее".
6. На шаге 3 из 3 в мастере в разделе "Формат данных столбца" выберите "Дата".
7. Выберите формат даты и нажмите "Готово".
8. Повторите эти действия для других столбцов, чтобы убедиться, что они не содержат пробелы перед датами.

Проверка параметров даты на компьютере

Excel полагается на систему дат вашего компьютера. Если дата в ячейке введена в другой системе дат, Excel не распознает ее как настоящую дату.

Например, предположим, что компьютер отображает даты в формате мм.дд.гггг. Если ввести такую дату в ячейке, Excel распознает ее как дату, а вы сможете использовать ее в формуле вычитания. Однако если ввести дату в формате мм.дд.гг, Excel не распознает ее как дату. Вместо этого дата будет рассматриваться как текст.

Существует два решения этой проблемы: Вы можете изменить систему дат, которая используется на компьютере, чтобы она соответствовала системе дат, которая нужна в Excel. Или в Excel можно создать новый столбец и использовать функцию ДАТА, чтобы создать настоящую дату на основе даты в текстовом формате. Вот как это сделать, если система дат вашего компьютера — дд.мм.гггг, а в ячейке A1 записан текст 12/31/2017.

1. Создайте такую формулу: =ДАТА(ПРАВСИМВ(A1;4);ЛЕВСИМВ(A1;2);ПСТР(A1;4;2))
2. Результат будет 31.12.2017.
3. Чтобы использовать формат дд.мм.гг, нажмите клавиши CTRL+1 (или + 1 на компьютере Mac).
4. Выберите другой языковой стандарт, в котором используется формат дд.мм.гг, например Немецкий (Германия). После применения формата результат будет 31.12.2017 , причем это настоящая дата, а не ее текстовая запись.

Проблемы с пробелами и текстом

Удаление пробелов, которые вызывают ошибку #ЗНАЧ!

Часто ошибка #ЗНАЧ! возникает, потому что формула ссылается на другие ячейки, содержащие пробелы или (что еще сложнее) скрытые пробелы. Из-за этих пробелов ячейка может выглядеть пустой, хотя на самом деле таковой не является. 

1. Выберите ячейки, на которые указывают ссылки

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

2. Найдите и замените

На вкладке "Главная" выберите "Найти" & "Заменить>".

3. Удалите пробелы

В поле "Найти " введите один пробел. Затем в поле Заменить удалите все, что там может быть.

4. Замените одно или все вхождения

Если вы уверены, что следует удалить все пробелы в столбце, нажмите кнопку "Заменить все". Если вы хотите выполнить обход и заменить пробелы в индивидуальном порядке, вы можете сначала нажать "Найти далее ", а затем выбрать "Заменить ", когда будете уверены, что пространство вам не нужно. После этого ошибка #ЗНАЧ! может исчезнуть. Если нет — перейдите к следующему шагу.

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

Иногда ячейки могут выглядеть пустыми, хотя на самом деле таковыми не являются: скрытые символы, отличные от пробелов. Например, это может происходить из-за одинарных кавычек в ячейке. Чтобы удалить эти символы из столбца, включите фильтр, выбрав пункты "Сортировка на главную>" & "Фильтр>".

6. Установите фильтр

" Щелкните стрелку фильтра и снимите флажок "Выделить все". Затем установите флажок Пустые.

7. Установите все флажки без названия

Установите флажки для проверки, напротив которых ничего не указано, как на этом рисунке.

8. Выделите пустые ячейки и удалите их

Если Excel вернет пустые ячейки, выделите их. Затем нажмите клавишу DELETE. Все скрытые символы в ячейках будут удалены.

9. Очистите фильтр

Нажмите стрелку фильтра , а затем выберите Очистить фильтр от..., чтобы были видны все ячейки.

10. Результат

Если бы #VALUE виновником были пробелы! были пробелы, вместо ошибки отобразится результат формулы, как показано в нашем примере. Если нет — повторите эти действия для других ячеек, на которые ссылается формула. Или попробуйте другие решения на этой странице.

Примечание

В этом примере обратите внимание, что ячейка E4 содержит зеленый треугольник, а число выравнивается по левому краю. Это значит, что число имеет текстовый формат. Это может вызвать проблемы в дальнейшем. Если вы заметили эту проблему, рекомендуем преобразовать числа из текстового формата в числовой.

Поиск текста и специальных знаков

Ошибку #ЗНАЧ! могут вызвать текст и специальные знаки в ячейке. Но иногда сложно понять, в каких именно ячейках они присутствуют. Решение: Использование функции ЕТЕКСТ для проверки ячеек. Обратите внимание, что функция ЕТЕКСТ не устраняет ошибку, она просто находит ячейки, которые могут ее вызывать.

Пример с ошибкой #ЗНАЧ!

Вот пример формулы с #VALUE! . Ошибка, скорее всего, возникает из-за ячейки E2. Специальный символ отображается в виде маленькой ячейки после "00". Или, как показано на следующем рисунке, вы можете использовать функцию ЕТЕКСТ в отдельном столбце для проверки текста.

Этот же пример с функцией ЕТЕКСТ

Здесь функция ЕТЕКСТ была добавлена в столбец F. Все ячейки подойдут, кроме той ячейки, для которой задано значение ИСТИНА. Это значит, что ячейка E2 содержит текст. Чтобы решить эту проблему, можно просто удалить содержимое ячейки и еще раз ввести число 1865,00. Вы также можете использовать функцию ПЕЧСИМВ, чтобы убрать символы, или функцию ЗАМЕНИТЬ, чтобы заменить специальные знаки на другие значения.

После использования функций ПЕЧСИМВ или ЗАМЕНИТЬ, скопируйте результат и вставьте на > домашнюю > страницу > специальные значения. Кроме того, может потребоваться преобразовать числа из текстового формата в числовой.

Использование функций вместо операций

Формулы с математическими операциями, такими как + и *, могут не вычислять ячейки, содержащие текст или пробелы. В таком случае попробуйте использовать вместо них функцию. Функции часто игнорируют текстовые значения и вычисляют все как числа, устраняя #VALUE! . Например, вместо =A2+B2+C2 введите =СУММ(A2:C2). Или вместо =A2*B2 введите =ПРОИЗВЕД(A2,B2).

Другие решения

Поиск источника ошибки

Выберите ошибку

Сначала выделите ячейку с #VALUE! .

Щелкните "Формулы" Вычислить формулу >

Выберите формулы,>вычислить>, вычислить. Excel обработает каждую часть формулы по отдельности. В данном случае формула =E2+E3+E4+E5 выдает ошибку из-за скрытого пробела в ячейке E2. Пробела не видно, если смотреть на ячейку E2. Но его можно увидеть здесь. Он отображается как " ".

Замена ошибки #ЗНАЧ! другим значением

Иногда вам может быть нужно вместо ошибки #ЗНАЧ! выводить что-то свое, например собственный текст, ноль или пустую ячейку. В этом случае можно добавить в формулу функцию ЕСЛИОШИБКА. Функция ЕСЛИОШИБКА проверяет наличие ошибки и, если да, заменяет ее другим значением по вашему выбору. Если ошибки нет, вычисляется исходная формула.

Ячейка с ошибкой #ЗНАЧ!

Вот пример формулы с #VALUE! вызвана скрытым пробелом в ячейке E2.

Ошибка, скрытая функцией ЕСЛИОШИБКА

А вот та же формула с добавлением в формулу функции ЕСЛИОШИБКА. Ее можно прочитать как "Вычислить формулу, но если возникнет какая-либо ошибка, заменить ее двумя дефисами." Помните, что также можно использовать "", чтобы ничего не отображать вместо двух дефисов. Или вы можете подставить свой текст, например: "Ошибка суммирования".

К сожалению, как вы видите, функция ЕСЛИОШИБКА не устраняет ошибку, а только скрывает ее. Так что используйте ее, если точно уверены, что ошибку лучше скрыть, чем исправить.

Проверка подключений к данным

В какой-то момент подключение для передачи данных могло стать недоступным. Чтобы исправить ошибку, восстановите подключение или, если это возможно, импортируйте данные. Если у вас нет доступа к подключению, попросите автора книги создать для вас новый файл. В идеале новый файл должен содержать только значения и не содержать связей. Это можно сделать путем копирования всех ячеек и вставки только в качестве значений. Чтобы вставить только значения, они могут выбрать команду " Домашняя>вставка>" и "Вставить специальные>значения". При этом будут удалены все формулы и подключения, а ошибки #ЗНАЧ! исчезнут.

Использование форума сообщества, посвященного Excel

Если вам не помогли эти рекомендации, поищите похожие вопросы на форуме сообщества, посвященном Excel, или опубликуйте там свой собственный.

Задать вопрос на форуме сообщества, посвященном Excel

См. также

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул