Ошибка #ЗНАЧ в Excel: причины и способы устранения
Будь то простая ошибка форматирования или более сложная проблема синтаксиса, знание того, как исправить ошибки #ЗНАЧ в Excel, важно для всех, кто хочет создавать безошибочные электронные таблицы и обеспечивать точность своих данных.
При работе с Excel часто возникают ошибки. Одной из самых неприятных ошибок является #ЗНАЧ, которая возникает, когда формула или функция не может обработать данные. Эта ошибка может быть вызвана несколькими факторами, включая неправильный синтаксис, несовпадающие ссылки на ячейки или скрытые символы. В этой статье мы рассмотрим различные причины ошибки #ЗНАЧ и дадим советы, как ее исправить.
Когда возникает ошибка #ЗНАЧ в Excel?
Значение! Ошибка в Excel обычно возникает по следующим причинам:
- Неожиданный тип данных. Если функции Excel требуется определенный тип данных, например число или текст, а ячейка содержит другой тип данных.
- Неправильный синтаксис формулы. Когда аргументы функции неверны, отсутствуют или расположены в неправильном порядке.
- Пространства. Если формула ссылается на ячейку, содержащую пробел. Визуально такие ячейки выглядят абсолютно пустыми.
- Невидимые персонажи. Иногда ячейки могут содержать скрытые или непечатаемые символы, препятствующие правильному расчету формулы.
- Даты в текстовом формате. Когда даты вводятся в формате, который Excel не понимает, они обрабатываются как текстовые строки, а не как действительные даты.
- Диапазоны имеют несовместимые размеры. Когда формула ссылается на несколько диапазонов разного размера или формы, Excel не может вычислить формулу и выдает ошибку.
Как видите, ошибка #ЗНАЧ в Excel может быть вызвана множеством факторов. Поняв первопричину, вам будет легче найти правильное средство для ее устранения.
Как устранить и исправить ошибку #ЗНАЧ в Excel
Как только вы определите причину ошибки, выполните соответствующие шаги по устранению неполадок, чтобы решить проблему.
Проверьте, допустим ли тип данных
Чтобы избежать ошибки #ЗНАЧ в Excel, убедитесь, что тип данных в указанной ячейке правильный. Если для формулы или функции требуются числовые данные, убедитесь, что ячейка содержит число, а не текст.
Типичным примером являются математические операции, такие как сложение и умножение. Когда одно из суммируемых или умножаемых значений не является числовым, возникает ошибка #ЗНАЧ:
Чтобы исправить ошибку, вы можете использовать один из следующих вариантов:
- Введите недостающие числовые значения.
- Используйте функции Excel, которые автоматически игнорируют текстовые значения.
- Напишите оператор IF, который соответствует вашей бизнес-логике.
В этом примере вы можете использовать функцию ПРОДУКТ:
=ПРОИЗВЕД(B3, C3)
Если одна из указанных ячеек содержит текст, логическое значение или пуста, эта ячейка игнорируется. Результат будет таким, как если бы вы умножили другое значение на 1.
В качестве альтернативы вы можете построить оператор IF следующим образом:
= ЕСЛИ (И (ЧИСЛО (B3), ЧИСЛО (C3)), B3 * C3, 0)
Эта формула умножает две ячейки, только если оба значения являются числовыми, и возвращает ноль, если какая-либо ячейка содержит нечисловое значение. Для этого конкретного случая это имеет смысл.
Удалить пробелы и скрытые символы
В некоторых формулах ячейка с ошибочными пробелами или невидимыми символами также может вызвать ошибку #ЗНАЧ! ошибка, как показано на скриншоте ниже:
Визуально такие ячейки, как D3, B7 и C14, могут казаться совершенно пустыми. Однако они содержат один или несколько пробелов или непечатаемых символов. В Excel символ пробела считается текстом, и он потенциально может вызвать ошибку #ЗНАЧ! ошибка. На самом деле это просто еще один случай предыдущего примера, поэтому его можно исправить аналогичным образом:
- Убедитесь, что проблемные ячейки действительно пусты. Для этого выберите ячейку и нажмите клавишу Delete, чтобы удалить из нее все скрытые символы.
- Используйте функцию Excel, которая игнорирует текстовые значения, например функцию СУММ, вместо арифметической операции сложения.
Убедитесь, что указанные диапазоны совместимы
Многие функции Excel, которые принимают несколько диапазонов в своих аргументах, требуют, чтобы эти диапазоны были одного размера и формы. В противном случае формула вызывает ошибку #ЗНАЧ.
Например, функция ФИЛЬТР динамического массива приводит к ошибке #ЗНАЧ, когда аргументы включения и массива имеют несовместимые размеры. Например:
=ФИЛЬТР(A3:B20, A3:A22=”Яблоко”)
Как только ссылки на диапазон изменены соответствующим образом, ошибка исчезает:
=ФИЛЬТР(A3:B20, A3:A20=”Яблоко”)
Убедитесь, что даты не хранятся в виде текста
В Excel даты обычно хранятся в виде числовых значений. Однако некоторые даты на листе могут храниться в виде текстовых строк. Когда это произойдет, Excel вернет ошибку #ЗНАЧ! ошибка, если вы попытаетесь выполнить вычисления или операции с этими датами, так как текстовые значения нельзя складывать, вычитать или как-то еще вычислять.
Чтобы решить эту проблему, вам необходимо преобразовать даты в текстовом формате в действительные даты Excel.
Проверить синтаксис формулы
Другой возможной причиной ошибки #ЗНАЧ в Excel может быть синтаксическая ошибка в вашей формуле. Инструменты аудита формул Excel могут помочь вам выявить и исправить такие проблемы.
- Выберите ячейку с формулой, которая выдает ошибку #ЗНАЧ.
- На вкладке «Формулы» в группе «Аудит формул» нажмите «Оценить формулу» или «Проверка ошибок».
Excel будет проходить формулу по одному разделу за раз, показывая результат каждого шага. Если есть синтаксическая ошибка, Excel выделит конкретную часть формулы, вызывающую ошибку. Обнаружив синтаксическую ошибку, исправьте ее и повторно оцените формулу, чтобы убедиться, что теперь она работает должным образом.
Например, рассмотрим следующую формулу в наборе данных ниже:
=СОРТИРОВКА(ВЫБОР(A3:B20, 3))
Ошибка #VALUE возникает из-за того, что аргумент col_num (3) функции CHOOSECOLS больше, чем общее количество столбцов в указанном массиве (2).
Установка для последнего аргумента значения 2 решает проблему и возвращает желаемый результат — столбец «Итого», отсортированный от меньшего к большему:
Ошибка #ЗНАЧ в Excel XLOOKUP и VLOOKUP
Функция ВПР и ее современный преемник КСПР обычно используются в Excel для поиска и извлечения совпадающих данных. Однако при определенных обстоятельствах обе функции могут выдавать ошибку #ЗНАЧ.
Одной из распространенных причин ошибки #VALUE в XLOOKUP является несопоставимость размеров поискового и возвращаемого массивов. Например, вы не можете выполнять поиск в горизонтальном массиве и возвращать значения из вертикального массива. Кроме того, массив поиска не может быть больше или меньше возвращаемого массива. При наличии несоответствия размеров этих массивов функция XLOOKUP не сможет выполнить поиск и вернет ошибку #ЗНАЧ.
Например, приведенная ниже формула XLOOKUP возвращает ошибку #VALUE, поскольку массивы поиска и возврата содержат разное количество строк:
=XПРОСМОТР(D3, A3:A20, B3:B22)
Настройка ссылки return_array устраняет ошибку:
=XПРОСМОТР(D3, A3:A20, B3:B20)
В функции ВПР две распространенные причины ошибки #ЗНАЧ – это когда искомое значение превышает 255 символов и аргумент col_index_num меньше 1. Дополнительные сведения см. в разделе Ошибка #ЗНАЧ в функции ВПР.
Избавьтесь от ошибки #ЗНАЧ с помощью функции ЕСЛИОШИБКА
Чтобы устранить ошибку #ЗНАЧ на листах Excel, вы можете использовать функцию ЕСЛИОШИБКА в Excel 2007–365 или комбинацию ЕСЛИ ОШИБКА в более ранних версиях.
Предположим, вы используете формулу DATEDIF, чтобы найти разницу между датами в B3 и C3:
=РАЗНДАТ(B3, C3, “г”)
Если одна или обе даты неверны, формула выдаст ошибку #ЗНАЧ. Чтобы это исправить, оберните основную формулу функцией ЕСЛИОШИБКА следующим образом:
=ЕСЛИОШИБКА(РАЗНДАТ(B3, C3, “d”), “Недопустимая дата!”)
Если Excel не распознает указанное значение ячейки как дату, функция ЕСЛИОШИБКА явно укажет на это.
Кончик. Чтобы быстро найти все ошибки #VALUE на листе, вы можете использовать функцию «Перейти к специальному» или диалоговое окно «Найти и заменить». Мы подробно обсудили эти параметры при обнаружении ошибок #NAME. Для ошибок #VALUE действия практически такие же.
Вот как можно обнаружить и исправить ошибку #ЗНАЧ в Excel. Поняв причины и используя соответствующие методы устранения неполадок, вы сможете быстро вернуть свою электронную таблицу в нужное русло.
Практическая рабочая тетрадь для скачивания
Ошибка #ЗНАЧ в Excel – примеры (файл .xlsx)