Преобразование текстовых значений в числа в Excel¶
Преобразование текстовых значений в числа в Excel — это операция над данными электронной таблицы, в ходе которой значения, распознаваемые программой как текст, переводятся в числовой формат, что необходимо для выполнения арифметических вычислений, сортировки и построения диаграмм. В Microsoft Excel числа, поступившие из внешних источников (импорт из CSV, выгрузки из баз данных, данные с веб-страниц), часто хранятся в текстовом формате — об этом свидетельствует выравнивание по левому краю и зелёный треугольник в левом верхнем углу ячейки.
¶Причины появления текстовых чисел
Числовые значения могут храниться как текст по нескольким причинам:
- Неразрывный пробел или пробел перед числом. Наиболее частая причина: при импорте данных из 1С, сайтов или CSV-файлов в числах присутствуют символы неразрывного пробела (код 160), которые Excel не распознаёт как разделитель разрядов.
- Текстовый формат ячейки. Если ячейка предварительно установлена в формат «Текстовый», введённое в неё число сохраняется как строка.
- Нестандартный разделитель целой и дробной частей. В русской локализации Excel использует запятую как десятичный разделитель, а в англоязычных файлах — точку. Точка в числах с запятой распознаётся как текст.
- Лидирующие или завершающие пробелы, невидимые символы, символы валюты (₽, $, €).
- Данные, импортированные через Power Query или текстовый мастер с неверно заданным типом столбца.
¶Способы преобразования
¶Специальная вставка
Наиболее быстрый способ для одноразовой операции над столбцом:
- Выделить пустую ячейку, выполнить «Копировать» (Ctrl+C).
- Выделить диапазон с текстовыми числами.
- Открыть меню «Правка» → «Специальная вставка».
- В блоке «Действие» выбрать «Сложить», нажать «ОК».
При сложении нуля с текстовым числом Excel преобразует его в число. Метод не работает, если в ячейках присутствуют неразрывные пробелы — их необходимо удалить заранее.
¶Функция ЧИСЛОЗНАЧ
Функция ЧИСЛОЗНАЧ (английский аналог — VALUE) преобразует текстовое представление числа в числовое значение. Синтаксис: =ЧИСЛОЗНАЧ(текст). Функция корректно обрабатывает числа с учётом локали системы. Если преобразование невозможно, возвращается ошибка #ЗНАЧ!. Для обработки целого столбца формула помещается в соседнюю колонку и протягивается вниз.
¶Функция СЦЕПИТЬ с умножением на единицу
Умножение текстового значения на 1 (=A1*1) заставляет Excel выполнить неявное преобразование. Этот приём часто используется в связке с функцией ПОДСТАВИТЬ для удаления неразрывных пробелов:
=ЧИСЛОЗНАЧ(ПОДСТАВИТЬ(A1;СИМВОЛ(160);""))
¶Функция ЧИСЛОМ (NUMBERVALUE)
Функция ЧИСЛОМ (NUMBERVALUE) позволяет явно задать разделители целой и дробной частей независимо от региональных настроек:
=ЧИСЛОМ(A1;",";".")
Это полезно при работе с данными, импортированными из англоязычных источников, где в качестве десятичного разделителя используется точка.
¶Поиск и замена
Для удаления неразрывных пробелов и других мешающих символов применяется инструмент «Найти и заменить» (Ctrl+H). В поле «Найти» вводится неразрывный пробел (вставляется через Ctrl+Alt+Пробел или код Alt+0160 на цифровом блоке), в поле «Заменить на» — пустая строка. После удаления лишних символов текстовые значения автоматически распознаются как числа, если ячейки имеют формат «Общий» или «Числовой».
¶Преобразование через Power Query
При работе с большими массивами данных используется инструмент «Power Query» (в Excel 2016 и новее — вкладка «Данные» → «Получить данные»). В редакторе запросов столбцу можно явно назначить числовой тип данных, указав формат локали. Power Query автоматически обрабатывает неразрывные пробелы и нестандартные разделители при правильной настройке.
¶Функция ЕСЛИОШИБКА для массовой обработки
Для преобразования столбца с возможными некорректными значениями формула оборачивается в обработку ошибок:
=ЕСЛИОШИБКА(ЧИСЛОЗНАЧ(ПОДСТАВИТЬ(A1;СИМВОЛ(160);""));A1)
Такой подход позволяет безопасно обработать весь столбец, оставив нетекстовые значения без изменений.
¶Таблица соответствия методов
| Метод | Удаляет неразрывный пробел | Работает с точкой | Подходит для столбца |
|---|---|---|---|
| Специальная вставка | Нет | Нет | Да |
| ЧИСЛОЗНАЧ | Нет | Нет | Да |
| ЧИСЛОМ | Нет | Да | Да |
| Умножение на 1 | Нет | Нет | Да |
| Поиск и замена | Да | Нет | Да |
| Power Query | Да | Да | Да |
¶Настройка разделителя десятичных знаков
Разделитель целой и дробной частей в Excel определяется региональными настройками Windows. Для его изменения: «Файл» → «Параметры» → «Дополнительно» → снять флажок «Использовать системные разделители» и задать нужные символы. Изменение параметра влияет на то, как Excel интерпретирует вводимые и импортированные числа.
¶Особенности русской версии Excel
В русскоязычной версии Excel имена функций отличаются от английских: VALUE — ЧИСЛОЗНАЧ, NUMBERVALUE — ЧИСЛОМ, SUBSTITUTE — ПОДСТАВИТЬ, CHAR — СИМВОЛ. При работе с файлами, созданными в англоязычной версии, формулы автоматически адаптируются, однако вручную введённые англоязычные имена функций в русской версии не распознаются.
Источники:
- Microsoft Support — справка по функциям Excel
- «Excel 2019: Полный курс» Д. Холшевникова
- «Большая книга Excel» Дж. Уокен