Как исправить ошибку #VALUE! ошибка

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

Содержание
  1. Исправить ошибку для конкретной функции
  2. Проблемы с вычитанием
  3. Как выполнить базовое вычитание
  4. Вычесть ссылку на ячейку из другой
  5. Или используйте СУММ с положительными и отрицательными числами
  6. #VALUE! с базовым вычитанием
  7. Как вычитать даты
  8. Вычесть ссылку на ячейку из другой
  9. Или используйте функцию DATEDIF
  10. #VALUE! ошибка при вычитании дат, хранящихся в виде текста
  11. Проблемы с пробелами и текст
  12. Удалите пробелы, вызывающие #VALUE!
  13. 1. Выбрать ячейки, на которые есть ссылка
  14. 2. Найдите и замените
  15. 3. Заменить пробелы ничем
  16. 4. Заменить или заменить все
  17. 5. Включите фильтр
  18. 6. Установите фильтр
  19. 7. Установите все флажки без имени
  20. 8. Выделите пустые ячейки и удалите
  21. 9. Очистить фильтр
  22. 10. Результат
  23. Проверить наличие текста или специальных символов
  24. Пример с #VALUE!
  25. Тот же пример, с ISTEXT
  26. Попробуйте использовать функции вместо операций
  27. Другие решения, которые стоит попробовать
  28. Попытайтесь определить источник ошибки
  29. Выберите ошибку
  30. Щелкните Формулы> Вычислить формулу
  31. Замените #VALUE! ошибка чем-то другим
  32. Ячейка с #VALUE!
  33. Ошибка, скрытая ЕСЛИОШИБКОЙ
  34. Убедитесь, что доступны подключения для передачи данных
  35. Разместите вопрос на форуме сообщества Excel
  36. См. также

Исправить ошибку для конкретной функции

Какую функцию вы используете?
  • Какой функцией вы являетесь используя?
  • AVERAGE
  • CONCATENATE
  • COUNTIF, COUNTIFS
  • DATEVALUE
  • ДНЕЙ
  • НАЙТИ, НАЙТИ Б
  • ЕСЛИ
  • ИНДЕКС, СООТВЕТСТВИЕ
  • ПОИСК, ПОИСКБ
  • СУММ
  • СУММЕСЛИ, СУММЕСЛИ
  • СУММПРОИЗВ
  • ВРЕМЕННОЕ ЗНАЧЕНИЕ
  • TRANSPOSE
  • VLOOKUP
  • * Ничего из вышеперечисленного

См. дополнительную информацию в Исправьте #VALUE! ошибка в функциях AVERAGE или SUM

См. дополнительную информацию в разделе Исправьте #VALUE! ошибка в функции CONCATENATE

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

См. дополнительную информацию в Исправьте #VALUE! ошибка в функции DATEVALUE

См. дополнительную информацию в Исправьте #VALUE! ошибка в функции ДНЕЙ

См. дополнительную информацию в разделе Исправьте #VALUE! ошибка в функциях FIND/FINDB и SEARCH/SEARCHB

См. дополнительную информацию в разделе Исправьте #VALUE! ошибка в функции ЕСЛИ

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

См. дополнительную информацию в разделе Исправьте #VALUE! ошибка в функциях FIND/FINDB и SEARCH/SEARCHB

См. дополнительную информацию в разделе Исправьте #VALUE! ошибка в функциях AVERAGE или SUM

См. дополнительную информацию в разделе Исправьте #VALUE! ошибка в функции СУММЕСЛИ/СУММЕСЛИМН

См. дополнительную информацию в Исправьте #VALUE! ошибка в функции SUMPRODUCT

См. дополнительную информацию в Исправьте #VALUE! ошибка в функции TIMEVALUE

См. дополнительную информацию в разделе Исправьте #VALUE! ошибка в функции TRANSPOSE

См. дополнительную информацию в разделе Исправьте #VALUE! ошибка в функции VLOOKUP

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

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

Как выполнить базовое вычитание

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

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

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

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

Введите положительное значение в одну ячейку и отрицательное значение в другую. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки вместе. В этом примере ячейка D6 имеет запланированную сумму, а ячейка E6 имеет фактическую сумму как отрицательное число. F6 имеет формулу = СУММ (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. Нажмите ОК .

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

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

  8. Нажмите ENTER.

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

Как вычитать даты

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

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

Или используйте функцию DATEDIF

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

#VALUE! ошибка при вычитании дат, хранящихся в виде текста

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

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

  1. Дважды щелкните дату, которая используется в формуле вычитания.

  2. Поместите курсор в начало и посмотрите, можете ли вы выбрать одно или несколько пробелов. Вот как выглядит выделенное пространство в начале ячейки:

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

  3. Выберите столбец, содержит дату, щелкнув заголовок его столбца.

  4. Щелкните Данные > Текст в столбцы .

  5. Дважды нажмите Далее .

  6. На шаге 3 из 3 мастера в разделе Формат данных столбца нажмите Дата .

  7. Выберите формат даты и затем нажмите Готово .

  8. Повторите этот процесс для других столбцов, чтобы убедиться, что они не содержат ведущих пробелов перед датами.

Проверьте настройки даты вашего компьютера

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

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

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

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

  2. Результатом будет 31.12.2017 .

  3. Если вы хотите, чтобы формат выглядел как дд/мм/гг, нажмите CTRL + 1 (или + 1 на Mac).

  4. Выберите другой языковой стандарт, который использует формат дд/мм/гг, для например, английский (Великобритания) . Когда вы закончите применять формат, результатом будет 31/12/2017 , и это будет настоящая дата, а не текстовая дата.

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

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

Удалите пробелы, вызывающие #VALUE!

Часто #VALUE! происходит потому, что ваша формула относится к другим ячейкам, содержащим пробелы, или, что еще сложнее: к скрытым пробелам. Эти пробелы могут сделать ячейку выглядеть пустой, хотя на самом деле они не пустые.

1. Выбрать ячейки, на которые есть ссылка

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

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

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

3. Заменить пробелы ничем

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

4. Заменить или заменить все

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

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

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

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

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

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

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

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

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

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

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

10. Результат

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

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

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

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

Пример с #VALUE!

Вот пример формулы с #VALUE! ошибка. Вероятно, это связано с ячейкой E2. Существует специальный символ, который отображается в виде маленькой рамки после «00». Или, как показано на следующем рисунке, вы можете использовать функцию ISTEXT в отдельном столбце для проверки текста.

Тот же пример, с ISTEXT

Здесь функция ISTEXT была добавлена ​​в столбец F. Все ячейки в порядке, кроме той, которая имеет значение TRUE. Это означает, что в ячейке E2 есть текст. Чтобы решить эту проблему, вы можете удалить содержимое ячейки и повторно ввести значение 1865,00. Или вы также можете использовать функцию CLEAN для очистки символов или использовать функцию REPLACE для замены специальных символов другими значениями.

После использования CLEAN или REPLACE, вы захотите скопировать результат и использовать Home> Paste> Paste Special> Values ​​. Возможно, вам также придется преобразовать числа, сохраненные в виде текста, в числа.

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

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

Другие решения, которые стоит попробовать

Попытайтесь определить источник ошибки

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

Сначала выберите ячейку с #VALUE! ошибка.

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

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

Замените #VALUE! ошибка чем-то другим

Иногда вы просто хотите заменить #VALUE! ошибка с чем-то еще, например с вашим собственным текстом, нулем или пустой ячейкой. В этом случае вы можете добавить в формулу функцию ЕСЛИОШИБКА. ЕСЛИОШИБКА проверит наличие ошибки и, если да, заменит ее другим значением по вашему выбору.. Если ошибки нет, будет рассчитана исходная формула. ЕСЛИОШИБКА работает только в Excel 2007 и более поздних версиях. Для более ранних версий вы можете использовать IF (ISERROR ()).

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

Ячейка с #VALUE!

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

Ошибка, скрытая ЕСЛИОШИБКОЙ

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

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

Убедитесь, что доступны подключения для передачи данных

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

Разместите вопрос на форуме сообщества Excel

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

Разместите вопрос в Excel форум сообщества

См. также

Обзор формул в Excel

Как избежать неправильных формул

Оцените статью
Botgadget.ru
Добавить комментарий