Функция ЗНАЧЕН() в EXCEL
Функция ЗНАЧЕН () возвращает число (если преобразование текстовой строки прошло удачно) или ошибку #ЗНАЧ! (если преобразование не удалось).
Синтаксис функции
ЗНАЧЕН(текст)
Текст — Текст (текстовая строка) в кавычках или ссылка на ячейку, содержащую текст, который нужно преобразовать.
- Текст может быть в любом формате, допускаемом в Microsoft Excel для числа, даты или времени (см. ниже). Если текст не соответствует ни одному из этих форматов, то функция возвращает значение ошибки #ЗНАЧ!
- Обычно функцию ЗНАЧЕН () не требуется использовать в формулах, поскольку необходимые преобразования значений выполняются в Microsoft Excel автоматически. Эта функция предназначена для обеспечения совместимости с другими программами электронных таблиц.
В файле примера приведены разные варианты преобразований текстовых строк.
Как правило это числа, даты и время введенные в ячейки с текстовым форматом. При вводе этих значений в ячейки с форматом Общий проблем не возникает и преобразование происходит автоматически (функция ЗНАЧЕН () не нужна).
Функция ЗНАЧЕН () может потребоваться при преобразовании чисел из текстового формата, когда эти числа возвращаются функциями ЛЕВСИМВ() , ПСТР() и др. Но, в этом случае альтернативой может служить операция сложения с нулем. Например, формула =ЗНАЧЕН(ЛЕВСИМВ(«101 Далматинец»;3)) аналогична формуле =ЛЕВСИМВ(«101 Далматинец»;3)+0 ( подробнее здесь ).
Функции SUMIF, COUNTIF и COUNTBLANK возвращают значение "#VALUE!" Error
Формула, содержащая функции SUMIF, SUMIFS, COUNTIF, COUNTIFS или COUNTBLANK , может возвращать значение "#VALUE!". error in Microsoft Excel.
Это поведение также применяется к Dfunctions, таким как DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR и DVARP. Функции OFFSET и INDIRECT также имеют такое поведение.
Причина
Это происходит, когда формула, содержащая функцию, ссылается на ячейки в закрытой книге и ячейки вычисляются.
Если открыть указанную книгу, формула будет работать правильно.
Обходной путь
Чтобы обойти это поведение, используйте сочетание функций SUM и IF в формуле массива.
Примеры
Необходимо ввести каждую формулу в виде формулы массива. Чтобы ввести формулу массива Microsoft Excel для Windows, нажмите клавиши CTRL+SHIFT+ВВОД.
СУММЕСЛИ
Вместо того чтобы использовать формулу, аналогичную следующей:
Используйте следующую формулу:
СЧЁТЕСЛИ
Вместо того чтобы использовать формулу, аналогичную следующей:
используйте следующую формулу:
COUNTBLANK
Вместо того чтобы использовать формулу, аналогичную следующей:
используйте следующую формулу:
Когда следует использовать формулу массива SUM(IF()), используйте логическое И или ИЛИ, чтобы заменить функцию СУММЕСЛИМЕСЛИ или СЧЁТЕСЛИМЕСЛИ.
Статус
Такое поведение является особенностью данного продукта.
Дополнительная информация
Функция СУММЕСЛИ использует следующий синтаксис:
Справочные материалы
Для получения дополнительных сведений о мастере, который может помочь в создании этих функций, щелкните Microsoft Excel Help (Справка) в меню "Справка", введите суммирование значений, соответствующих условиям, с помощью мастера условной суммы в помощнике по Office или мастере ответов, а затем нажмите кнопку "Поиск", чтобы просмотреть раздел.
Для получения дополнительных сведений о формулах массива щелкните Microsoft Excel Справка в меню "Справка", введите сведения об использовании формул для вычисления значений на других листах и книгах в помощнике по Office или мастере ответов, а затем нажмите кнопку "Поиск", чтобы просмотреть раздел.
Как исправить ошибку #ЗНАЧ в Excel
При работе в Excel вы можете столкнуться с ошибкой #ЗНАЧ. В этой статье мы разберемся с несколькими ситуациями, которые приводят к этой ошибке и как ее исправить.
Ситуация 1. Формула СУММЕСЛИ на другой файл
Представим себе обычную ситуацию, когда у нас есть файл с базой данных, в которой представлены продажи за месяц и нам нужно в другом файле рассчитать сумму продаж за каждый месяц.
- СУММЕСЛИ()
- СУММЕСЛИМН()
- СЧЁТЕСЛИ()
- СЧЁТЕСЛИМН()
- СЧИТАТЬПУСТОТЫ()
- СМЕЩ()
- ДВССЫЛ()
Какие есть пути решения ситуации? Первый: перенести базу данных в файл, где формируются итоги, тогда ссылок на внешние книги не будет и ошибка пропадет. Второй: Использовать формулы СУММ() и ЕСЛИ() совместно в массиве. Это несколько сложно для новичков, поэтому на этом способе останавливаться подробно не буду. Третий: Использовать формулу ЕСЛИОШИБКА и прописать в случае ошибки напоминание, что необходимо открыть файл с базой данных для корректной работы. Четвертый: Просто открыть файл, на который ссылается формула и ошибка пропадет 😉
Ситуация 2. Вычитание/сложение дат
Ошибка #ЗНАЧ может появиться при вычитании/сложении дат. Это связано с тем, что формат одной из ячеек участвующих в формуле отличен от типа Дата. Обычно есть лишние пробелы, из-за которых Excel считает что в ячейке не дата, а текст и соответственно из даты текст вычесть/сложить невозможно.
Решение проблемы следующее: Выделите ячейки с датами и замените в них пробелы на «ничего». После этого ошибка исчезнет.
Ситуация 3. Лишние пробелы в числах
При копировании данных из различных источников, может сложиться ситуация, что в числах будут лишние пробелы, из-за них арифметические операции с числами будут невозможны и приведут к ошибке #ЗНАЧ. Решение проблемы, как и в ситуации 2 — замените пробелы на «ничего» и проблема пропадет.
Есть еще несколько ситуаций, которые могут привести к рассматриваемой ошибке, но они настолько редки, что я рассматривать их в этой статье не буду.