Как превратить текст в число в Excel: функция ЧИСЛОЗНАЧ и альтернативы

Функция ЧИСЛОЗНАЧ (англ. VALUE) преобразует текстовое представление числа в реальное числовое значение, позволяя выполнять математические операции. Она необходима, когда данные импортированы из внешних источников (CSV, веб-сайты, базы данных) и отображаются как текст, из-за чего суммы и формулы выдают ошибку #ЗНАЧ!. Использование функции или альтернативных методов (умножение на 1, «Текст по столбцам») мгновенно решает проблему некорректных расчетов.

Зачем нужна функция ЧИСЛОЗНАЧ и как она работает

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

Основные сценарии использования:

• Импорт отчетов из банковских систем или 1С, где суммы сохранены как строки.
• Данные скопированы с веб-страниц с дополнительным форматированием.
• Числа содержат разделители тысяч (пробелы) или валютные символы, мешающие расчетам.

Синтаксис и примеры

Формула имеет простой вид: =ЧИСЛОЗНАЧ(текст) Где текст — ссылка на ячейку или строка в кавычках.

5 способов преобразовать текст в число без формул

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

1. Специальная вставка (Умножение на 1)

Самый быстрый способ для любого диапазона. Математическая операция заставляет Excel пересчитать формат ячеек.

1. В любую пустую ячейку впишите цифру 1.
2. Скопируйте эту ячейку (Ctrl+C).
3. Выделите диапазон с «текстовыми» числами.
4. Нажмите правой кнопкой мыши → Специальная вставка → выберите операцию Умножить → ОК.

2. Инструмент «Текст по столбцам»

Идеально для целых столбцов. Не требует создания вспомогательных ячеек.

1. Выделите проблемный столбец.
2. Перейдите на вкладку Данные → Текст по столбцам.
3. В мастере сразу нажмите Готово (настройки менять не нужно). Этот действие принудительно переопределяет формат данных.

3. Зеленый треугольник (Ошибка формата)

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

1. Выделите ячейки с предупреждением.
2. Нажмите на появившийся значок восклицательного знака рядом.
3. Выберите «Преобразовать в число».

4. Очистка через ПОДСТАВИТЬ (для сложных случаев)

Если в тексте есть буквы или лишние символы (например, «100 руб.»), сначала удалите их, затем примените ЧИСЛОЗНАЧ. Формула: =ЧИСЛОЗНАЧ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1; " руб."; ""); " "; "")) Логика: убираем слово «руб.», убираем пробелы, превращаем остаток в число.

5. Power Query (для регулярной работы)

Если вы постоянно загружаете одни и те же файлы:

1. Данные → Получить данные → Из файла.
2. В редакторе запросов выделите столбец и измените тип данных на Целое число или Десятичное число.
3. Нажмите Закрыть и загрузить.

Частые ошибки при работе с числами

• Ошибка #ЗНАЧ!: Возникает, если в ячейке кроме цифр есть скрытые символы (неразрывные пробелы, символы табуляции) или буквы.
  • Решение: Используйте функцию ПЕЧСИМВ для очистки непечатаемых знаков: =ЧИСЛОЗНАЧ(ПЕЧСИМВ(A1)).
• Число не суммируется после преобразования: Возможно, вы применили формулу, но не закрепили результат.
  • Решение: Скопируйте столбец с формулами и вставьте его обратно как Значения (через Специальную вставку).
• Дата превратилась в число (например, 44567): В Excel даты хранятся как числа.
  • Решение: Измените формат ячейки на «Дата» через главное меню или Ctrl+1.

FAQ

В чем разница между ЧИСЛОЗНАЧ и изменением формата ячейки? Простое изменение формата в меню «Главная» часто не работает, если Excel уже жестко определил содержимое как текст. Функция ЧИСЛОЗНАЧ или умножение на 1 принудительно меняют тип данных внутри ячейки.

Можно ли преобразовать сразу весь лист? Да, выделите весь лист (Ctrl+A), используйте метод «Специальная вставка» (умножить на 1) или «Текст по столбцам» для каждого столбца отдельно.

Как убрать апостроф перед числом? Апостроф (') — признак текстового формата. Он исчезнет автоматически после применения любого из описанных выше методов преобразования в число.