Значение в экселе: Функция ЗНАЧЕН() в EXCEL. Примеры и описание

Функция ЗНАЧЕН — Служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

В этой статье описаны синтаксис формулы и использование значения
 в Microsoft Excel.

Описание

Преобразует строку текста, отображающую число, в число.

Синтаксис

ЗНАЧЕН(текст)

Аргументы функции ЗНАЧЕН описаны ниже.

Примечания

• Текст может быть в любом формате, допускаемом в Microsoft Excel для числа, даты или времени. Если текст не соответствует ни одному из этих форматов, функция ЗНАЧЕН возвращает значение ошибки #ЗНАЧ!.

• Обычно функцию ЗНАЧЕН не требуется использовать в формулах, поскольку необходимые преобразования значений выполняются в Microsoft Excel автоматически. Эта функция предназначена для обеспечения совместимости с другими программами электронных таблиц.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функция ЗНАЧЕН в Excel для преобразования текста в число

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

Примеры функции ЗНАЧЕН в Excel и особенности ее использования

Данная функция имеет следующую синтаксическую запись:

=ЗНАЧЕН(текст)

Единственный аргумент — текст является обязательным для заполнения. То есть, данная функция принимает на вход текстовые данные, которые могут быть представлены в виде текстовой строки, введенной в кавычках (например, ЗНАЧЕН(“9 300 ₽”) или ссылки на ячейку таблицы, в которой содержится текст.

Примечания:

1. Формат текста, принимаемого в качестве параметра функции ЗНАЧЕН, должен быть одним из предусмотренных в Excel (время, дата, число, денежный). Результатом выполнения функции будет являться ошибка #ЗНАЧ!, если исходный текст не соответствует одному из этих типов данных.
2. На практике функция ЗНАЧЕН используется довольно редко, поскольку в Excel реализован механизм автоматического преобразования значений подобно некоторым языкам программирования, в которых присутствует неявное преобразование типов данных. Эта функция реализована с целью совместимости с прочими программными продуктами, поддерживающими работу с таблицами.
3. Функция полезна в случаях, когда числовые значения внесены в ячейки с установленным текстовым форматом данных. Также в Excel можно использовать надстройку, выполняющую поиск чисел, отформатированных в качестве текста и последующее преобразование их в числовой формат.
4. Чаще всего функция ЗНАЧЕН используется для преобразования результатов работы функций ПСТР и ЛЕВСИМВ (ПРАВСИМВ), однако для корректного отображения к результатам вычислений данных функций можно просто прибавить 0 (нуль). Подробнее рассмотрим это в одном из примеров.



Примеры использования функции ЗНАЧЕН в Excel

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

Часть таблицы выглядит следующим образом:

Для расчета общего количества представленных 5 наименований продукции используем следующую формулу:

Функция СУММ принимает числовые значения и производит их суммирование. В данном примере наименование и количество единиц некоторых товаров записаны в одной строке. Функция ПРАВСИМВ «отрезает» часть строки кроме двух последних символов, отображающих числовое значение – количество товаров. С помощью функции ЗНАЧЕН, принимающей в качестве параметра результат работы функции ПРАВСИМ, мы производим прямое преобразование выделенных символов в числовое значение.

В результате получим следующее:

То есть, в данной части таблицы представлены 265 единиц товаров.

Как преобразовать денежный формат в числовое значение

Пример 2. Данные о зарплатах сотрудников некоторого предприятия представлены в таблице Excel. Предприятие приобрело программный продукт, база данных которого не поддерживает денежный формат данных (только числовой). Было принято решение создать отдельную таблицу, в которой вся информация о зарплате представлена в виде числовых значений.

Изначально таблица выглядит следующим образом:

Для конвертирования денежных данных в числовые была использована функция ЗНАЧЕН. Расчет выполняется для каждого сотрудника по-отдельности. Приведем пример использования для сотрудника с фамилией Иванов:

=ЗНАЧЕН(B2)

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

Функция ЗНАЧЕН и формат времени

Пример 3. Самолет вылетел согласно расписанию в 13:40 и должен был совершить посадку в конечном пункте перелета в 17:20. Во время полета произошел инцидент, в связи которым рейс был задержан на определенное время. Самолет приземлился в 19:13. Необходимо определить время задержки в часах и минутах. Данные о времени вылета, расчетного и фактического времени прибытия занесены в ячейки таблицы формата «Дата».

Таблица имеет следующий вид:

Для решения задачи запишем следующую формулу:

Примечание: функция ЗНАЧЕН использована для явного преобразования дат в числа в коде времени Excel. Фактически, записи «=ЗНАЧЕН(C2)-ЗНАЧЕН(B2)» и «C2-B2» являются эквивалентными, поскольку Excel выполняет неявное преобразование. Прямое преобразование имеет практический смысл при использовании полученных значений в других программных продуктах, не поддерживающих форматы даты и времени Excel.

В результате будет получено число в коде времени Excel:

Для получения искомого значения необходимо преобразовать формат данных ячейки в «Время»:

То есть, самолет опоздал на 1 час и 53 минуты.

Как исправить #ЗНАЧ! error

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

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

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



• Какую функцию вы используете?
• СРЕДНЕЕ
• СЦЕПИТЬ
• СЧЁТЕСЛИ, СЧЁТЕСЛИ
• ДАТАЗНАЧ
• ДНЕЙ
• НАЙТИ, НАЙТИБД
• ЕСЛИ
• ИНДЕКС, СОВПАДЕНИЕ
• ПОИСК, ПОИСКB
• СУММА
• СУММЕСЛИМН, СУММЕСЛИМН
• СУММПРОДУКТ
• ЗНАЧЕНИЕ ВРЕМЕНИ
• ТРАНСПОЗИЦИЯ
• ВПР
• * Ничего из вышеперечисленного

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

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

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

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

Дополнительные сведения см. в разделе Исправление ошибки #ЗНАЧ! ошибка в функции ДНЕЙ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Введите положительное значение в одну ячейку и отрицательное значение в другую. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки вместе. В этом примере ячейка D6 содержит бюджетную сумму, а ячейка E6 имеет фактическую сумму в виде отрицательного числа. F6 имеет формулу =СУММ(D6,E6) .

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

1. Сначала проведите быстрый тест. В новой книге введите 2 в ячейку A1. Введите 4 в ячейку B1. Затем в C1 введите эту формулу =B1-A1 . Если вы получаете #ЗНАЧ! ошибка, перейдите к следующему шагу. Если ошибка не возникает, попробуйте другие решения на этой странице.

2. В Windows откройте панель управления регионом.

   • Windows 10: нажмите Пуск , введите Регион , а затем щелкните панель управления Регион .

   • Windows 8: на начальном экране введите Region , щелкните Settings , а затем щелкните Region .

   • Windows 7: нажмите Start и введите Регион , а затем щелкните Регион и язык .

3. На вкладке Форматы щелкните Дополнительные настройки .

4. Найдите разделитель списка . Если разделитель списка установлен на знак минус, измените его на что-то другое. Например, запятая является общим разделителем списка. Также распространена точка с запятой. Однако другой разделитель списка может быть более подходящим для вашего конкретного региона.

5. Щелкните OK .

6. Откройте книгу. Если ячейка содержит #ЗНАЧ! ошибка, дважды щелкните, чтобы изменить ее.

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

8. Нажмите ВВОД.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Создайте такую ​​формулу: =ДАТА(ПРАВО(A1,4),СРЕДИНА(A1,4,2),ЛЕВО(A1,2))

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

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

4. org/ListItem»>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

10. Результат

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

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

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

Пример с #ЗНАЧ!

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

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

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

После использования CLEAN или REPLACE вы захотите скопировать результат и использовать Главная > Вставить > Специальная вставка > Значения . Возможно, вам также придется преобразовать числа, хранящиеся в виде текста, в числа.

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

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

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

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

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

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

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

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

Ячейка с #ЗНАЧ!

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

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

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

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

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

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

Задайте вопрос на форуме сообщества Excel

См. также

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

Как избежать неработающих формул

Функция

ЗНАЧЕНИЕ — служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно… Меньше

В этой статье описаны синтаксис формулы и использование ЗНАЧЕНИЕ
функция в Microsoft Excel.

Описание

Преобразует текстовую строку, представляющую число, в число.

Синтаксис

ЗНАЧЕНИЕ(текст)

Синтаксис функции ЗНАЧ имеет следующие аргументы:

Замечания

• org/ListItem»>

  Текст может быть в любом из форматов постоянных чисел, даты или времени, распознаваемых Microsoft Excel. Если текст не в одном из этих форматов, ЗНАЧ возвращает ошибку #ЗНАЧ! значение ошибки.

• Как правило, вам не нужно использовать функцию ЗНАЧЕНИЕ в формуле, поскольку Excel автоматически преобразует текст в числа по мере необходимости. Эта функция предусмотрена для совместимости с другими программами для работы с электронными таблицами.

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.