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

Применяется к
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel Web App

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

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

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

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

СРЗНАЧ

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

СЦЕПИТЬ

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

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

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

ДАТАЗНАЧ

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

ДНИ

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

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

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

ЕСЛИ

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

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

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

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

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

СУММ

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

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

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

СУММПРОИЗВ

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

ВРЕМЗНАЧ

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

ТРАНСП

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

ВПР

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

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

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

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

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

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

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

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

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

Ячейка D6 с $2000,00, ячейка E6 с $1500,00, ячейка F6 с формулой: =SUM(D6;E6) и результат $500,00 Введите положительное значение в одну ячейку и отрицательное значение в другой. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки. В этом примере ячейка 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 с 1/1/2016, ячейка E10 с 24.04.2016, ячейка F10 с формулой: =E10-D10 и результатом 114 Введите две даты в две отдельные ячейки. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D10 содержит дату начала, а ячейка E10 — дату окончания. F10 содержит формулу =E10-D10.

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

Ячейка D15 с 1/1/2016, ячейка E15 с 24.04.2016, ячейка F15 с формулой: =DATEDIF(D15;E15;d) и результат 114 Введите две даты в две отдельные ячейки. В третьей ячейке используйте функцию РАЗНДАТ, чтобы найти разницу дат. Дополнительные сведения о функции РАЗНДАТ см. в статье Вычисление разницы двух дат.

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

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

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

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

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

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

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

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

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

Примечание

Формула выше написана с использованием функций ДАТА, ПРАВСИМВ, ПСТР и ЛЕВСИМВ. Обратите внимание, что она написана с предположением, что текстовая дата содержит два символа для дней, два символа для месяцев и четыре символа года. Возможно, вам потребуется настроить формулу в соответствии с датой.

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

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

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

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

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

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

Вкладка На вкладке Главная выберите Найти & Нажмите кнопку>Заменить.

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

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

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

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

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

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

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

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

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

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

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

Выбранные отфильтрованные пустые ячейки Когда Excel возвращает пустые ячейки, выделите их. Затем нажмите клавишу DELETE. Это очищает все скрытые символы в ячейках.

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

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

10. Результат

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

Примечание

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

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

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

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

H4 с =E2+E3+E4+E5 и результатом #VALUE! Ниже приведен пример формулы с #VALUE! . Ошибка, скорее всего, возникает из-за ячейки E2. Специальный символ отображается в виде небольшого прямоугольника после "00". Или, как показано на следующем рисунке, можно использовать функцию ISTEXT в отдельном столбце, чтобы проверка для текста.

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

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

После использования CLEAN или REPLACE необходимо скопировать результат и использовать команду Вставка домой > Вставка > специальных значений>. Кроме того, может потребоваться преобразовать числа из текстового формата в числовой.

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

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

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

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

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

Ячейка H4 с формулой =E2+E3+E4+E5 и результатом #VALUE! Сначала выберите ячейку с #VALUE! .

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

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

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

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

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

IFERROR скрывает все ошибки, а не только #VALUE! . Ошибки не рекомендуется скрывать, так как они часто указывают на то, что какое-то значение нужно исправить, а не просто скрыть. Мы не рекомендуем использовать эту функцию, если вы не уверены, что формула работает так, как вам нужно.

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

Ячейка H4 с =E2+E3+E4+E5 и результатом #VALUE! Ниже приведен пример формулы с #VALUE! вызвана скрытым пробелом в ячейке E2.

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

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

К сожалению, вы видите, что IFERROR на самом деле не устраняет ошибку, а просто скрывает ее. Так что используйте ее, если точно уверены, что ошибку лучше скрыть, чем исправить.

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

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

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

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

Ссылка на форум сообщества Excel . Публикация вопроса на форуме сообщества Excel

См. также

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

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