ЗНАЧЕН

Что делает эта функция?

Эта функция преобразует фрагмент текста, который похож на число, в фактическое число.

Если число в середине длинного фрагмента текста, его нужно будет извлечь, используя другие текстовые функции, такие как ПОИСК, ПСТР, НАЙТИ, ЗАМЕНИТЬ, ЛЕВСИМВ, ПРАВСИМВ.

Синтаксис

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

Форматирование

Специального форматирования не требуется.

Результат будет показан в виде числового значения на основе исходного текста.

Если знак % включен в текст, результатом будет десятичная дробь, которая может затем быть отформатирована в процентах.

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

То же самое применимо для других форматов.

Пример применения функции

Извлечь количество процентов из текста сложно, не зная заранее, сколько в нем знаков. Это может быть от 1 цифры (5%) до 4 цифр с запятой (12,25%).

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

Основная проблема заключается в расчете длины числа для его извлечения.

Если при извлечении предположить максимальную длину из четырех цифр и знака %, когда процент только одна цифра, при обычном извлечении по маске “?????%” в выражение попадут буквы.

Чтобы обойти проблему, можно использовать функцию ПОДСТАВИТЬ, чтобы увеличить количество пробелов между словами в тексте. Теперь при извлечении по маске

?????%

любые лишние символы будут пробелами, которые функция ЗНАЧЕН проигнорирует.

Формула ниже аналогична формуле на картинке и извлечет из ячейки A1 проценты длиной от 1 до 5 знаков, включая запятую:

=ЗНАЧЕН(ПСТР(" "&ПОДСТАВИТЬ(A1;" ";" ");ПОИСК("?????%";" "&ПОДСТАВИТЬ(A1;" ";" "));6))

Как функция ЗНАЧЕН помогает работать с числами в тексте

Функция ЗНАЧЕН в Excel преобразует текст в число, если это возможно. Это особенно полезно, когда данные представлены как текст (например, “123”, “005”, “1 000”), но вам нужно выполнить арифметические действия — сложение, умножение, процент и т.д. Без предварительного преобразования Excel не будет воспринимать такие значения как числа, а результат формулы окажется неверным или вернёт ошибку.

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

Полезные формулы с функцией ЗНАЧЕН

1. Округлить проценты внутри текстовой строки:

=СЖПРОБЕЛЫ(ЗАМЕНИТЬ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10));МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(48:57));ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10)));""));6;ТЕКСТ(ОКРУГЛ(ЗНАЧЕН(ПСТР(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10));МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(48:57));ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10)));""));6));2);"0%")))

Например, строка “Путину доверяют 81,87% россиян” станет “Путину доверяют 82% россиян”.

2. Извлечь первое число и посчитать НДС 20%:

="НДС: "&ТЕКСТ(ЗНАЧЕН(ПСТР(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10));МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(48:57));ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10)));""));9)/5;"0")&" руб."

Если A1 = “Стоимость товара – 225,500 руб.”, результат: НДС: 45100 руб.

3. Прибавить 5 к числу в начале текста

Для значений типа “30 см.”.

=СЖПРОБЕЛЫ(ЗАМЕНИТЬ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";2));1;3;ТЕКСТ(ЗНАЧЕН(ЛЕВСИМВ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";2));3))+5;"0")&" "))

Если A1 = “30 см”, результат: 35 см

4. Увеличить число в конце текста на 15%:

=ЗАМЕНИТЬ(A1;ДЛСТР(A1)-2;3;ТЕКСТ(ЗНАЧЕН(ПРАВСИМВ(A1;3))*1.15;"0"))

Если A1 = “Сумма045”, результат: Сумма52