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

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Ошибка #ЗНАЧ! в 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 с формулой =СУММ(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. Откройте книгу. Если ячейка содержит ошибку #ЗНАЧ!, щелкните дважды, чтобы отредактировать ячейку.

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

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

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

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

Ячейка D10 со значением 01.01.2016, ячейка E10 со значением 24.04.2016, ячейка F10 с формулой =E10-D10 и результатом 114

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

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

Ячейка D15 со значением 01.01.2016, ячейка E15 со значением 24.04.2016, ячейка F15 с формулой =РАЗНДАТ(D15;E15;"d") и результатом 114

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

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

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

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

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

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

  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 (или Изображение значка кнопки команд в Mac + 1 на Mac).

  4. Выберите другой языковой стандарт, в котором используется формат дд.мм.гг, например Немецкий (Германия). После применения формата результат будет 31.12.2017, причем это будет настоящая дата, а не ее текстовая запись.

Примечание: Приведенная выше формула написана с помощью функций даты, прав, MIDи Left . Обратите внимание, что оно написано с учетом того, что в текстовой дате содержится два знака для дней, два знака для месяцев и четыре знака для года. Возможно, вам потребуется настроить формулу в соответствии с датой.

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

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

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

Выделен столбец

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

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

Вкладка "Главная" > Найти и выделить > Заменить

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

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

Поле "Найти" с пробелом, поле "Заменить на" с пустым значением

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

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

Кнопка "Заменить все"

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

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

Главная > Сортировка и фильтр > Фильтр

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

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

Меню "Фильтр", в котором снят флажок "Выбрать все" и установлен флажок "(Пустые)"

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

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

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

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

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

Выделены отфильтрованные пустые ячейки

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

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

Меню "Фильтр", команда "Удалить фильтр с"

Щелкните стрелку фильтра Стрелка фильтра и выберите команду Удалить фильтр из... для отображения всех ячеек.

10. Результат

Вместо ошибки #ЗНАЧ! появился результат вычисления формулы. Зеленый треугольник в ячейке E4

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

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

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

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

Ячейка H4 с формулой =E2+E3+E4+E5 и результатом #ЗНАЧ!

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

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

Ячейка F2 с формулой =ЕТЕКСТ(F2) и результатом ИСТИНА

Здесь функция ЕОШИБКА была добавлена в столбец F. Все ячейки являются подмножеством, за исключением того, что оно имеет значение истина. Это означает, что ячейка E2 содержит текст. Чтобы устранить эту проблему, вы можете удалить содержимое ячейки и ввести значение 1865,00. Кроме того, можно использовать функцию Clean для удаления знаков или замены специальных знаков другими значениями с помощью функции заменить .

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

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

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

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

Ячейка H4 с формулой =E2+E3+E4+E5 и результатом #ЗНАЧ!

Сначала выделите ячейку с ошибкой #ЗНАЧ!.

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

Диалоговое окно "Вычисление формулы" с формулой " "+E3+E4+E5

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

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

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

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

Ячейка H4 с формулой =E2+E3+E4+E5 и результатом #ЗНАЧ!

Вот пример формулы, в которой ошибка #ЗНАЧ! вызвана скрытым пробелом в ячейке E2.

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

Ячейка H4 с формулой =ЕСЛИОШИБКА(E2+E3+E4+E5;"--")

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

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

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

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

Ссылка на форум сообщества Excel

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

См. также

Общие сведения о формулах в Excel

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

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×