Как в эксель заменить значение на цифры

Работа с данными в таблицах часто подразумевает необходимость преобразования текстовых значений (например, «123», «12 345», «$1 200,50») в числовой тип. Без этого большинство аналитических функций (суммирование, построение графиков, условное форматирование) работать не будут. В этой статье мы подробно разберём самые популярные способы замены значений на цифры в Microsoft Excel.

Содержание

1. Зачем преобразовывать текст в числа?
2. Функция VALUE
3. Умножение на 1 или прибавление 0
4. «Текст по столбцам» (Text to Columns)
5. Поиск и замена (Find & Replace)
6. Функция NUMBERVALUE
7. Power Query
8. Автоматизация через VBA
9. Полезные советы и типичные ошибки

Зачем преобразовывать текст в числа?

• Для корректного вычисления сумм, средних, максимальных и минимальных значений.
• Чтобы применять фильтры, сортировку и условное форматирование.
• Для построения диаграмм, где оси должны содержать числовые метки.
• Чтобы использовать функции SUMIF, VLOOKUP, INDEX/MATCH и др., которые сравнивают числа, а не строки.

Функция VALUE

Функция VALUE(text) принимает текстовое представление числа и возвращает его в числовом виде.

Пример

 A1: "1 234,56"
 B1: =VALUE(A1) // результат: 1234,56 (число)

Обратите внимание, что VALUE учитывает региональные настройки системы (разделитель десятичных знаков и группировок).

Пошаговый алгоритм

1. В соседний столбец введите формулу =VALUE(ячейка).
2. Протяните формулу вниз до конца диапазона.
3. Скопируйте полученные результаты как значения (правый клик → Специальная вставка → Значения).
4. Удалите исходный столбец, если он больше не нужен.

Умножение на 1 или прибавление 0

Самый простой способ «принудительно» превратить текст в число – выполнить арифметическую операцию, в которой участвует число.

Как это сделать

• Выделите диапазон, где находятся «числа‑строки».
• Нажмите Ctrl+H (поиск и замена).
• В поле «Найти» введите = (ничего не меняем), а в поле «Заменить на» введите =*1 или =+0.
• Нажмите Заменить всё. Excel автоматически преобразует все найденные ячейки в числовой тип.

Плюсы и минусы

• Плюс: мгновенно, без добавления новых столбцов.
• Минус: может изменить формулы, если они уже используют знак =, используйте с осторожностью.

«Текст по столбцам» (Text to Columns)

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

Шаги

1. Выделите столбец с «числами‑строками».
2. На ленте перейдите в Данные → Текст по столбцам.
3. Выберите С фиксированной шириной (или С разделителями – не важно, если разделителей нет) и нажмите Далее.
4. Нажмите Готово. Excel автоматически поменяет тип ячеек на Числовой.

Поиск и замена (Find & Replace)

Если в текстовых строках присутствуют лишние символы (пробелы, валютные знаки, кавычки), их удобно удалить с помощью Ctrl+H.

Пример: удаляем знак доллара и пробелы

 Найти: $ 
 Заменить на: (ничего)

После очистки Excel автоматически распознает оставшиеся цифры как числа (при условии, что в ячейках нет текстовых формул).

Функция NUMBERVALUE

Если ваш файл содержит числа в формате, отличающемся от системных настроек (например, запятая вместо точки как десятичный разделитель), используйте NUMBERVALUE.

Синтаксис

NUMBERVALUE(text, [decimal_separator], [group_separator])

 A1: "1.234,56"
 B1: =NUMBERVALUE(A1, ",", ".") // результат: 1234,56

Power Query

Для больших наборов данных и повторяющихся задач удобно использовать встроенный «Запрос и преобразование» (Power Query).

Алгоритм

1. Выделите диапазон → Данные → Из таблицы/диапазона. Откроется редактор Power Query.
2. Щёлкните правой кнопкой по столбцу → Тип данных → Число с фиксированной точкой (или Десятичное число).
3. Если в тексте присутствуют лишние символы, используйте Трансформировать → Заменить значения или Трансформировать → Формат → Удалить пробелы.
4. Нажмите Закрыть & загрузить. Данные вернутся в Excel уже в числовом виде.

Автоматизация через VBA

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

Пример кода

Sub ConvertTextToNumbers
 Dim rng As Range
 Set rng = Selection ' или указать диапазон явно, например: Range("A1:A100")
 
 Dim cell As Range
 For Each cell In rng
 If IsNumeric(cell.Value) Then
 cell.Value = CDbl(cell.Value) ' принудительно преобразуем в число
 End If
 Next cell
End Sub

Запуск макроса: Alt+F8 → выбрать ConvertTextToNumbers → Выполнить.

Полезные советы и типичные ошибки

• Проверьте формат ячейки. Даже после применения функций число может отображаться как текст, если ячейка имеет формат Текст. Установите формат Число (Ctrl+1 → Число).
• Не забывайте о локали. В разных региональных настройках используются разные разделители: точка vs запятая, пробел vs запятая в группировке. При необходимости используйте NUMBERVALUE с указанием разделителей.
• Пробелы и невидимые символы. Часто числа «залипают» из‑за неочевидных символов (неразрывный пробел, табуляция). Очистите их через TRIM (=TRIM(A1)) или заменой в Find & Replace (Ctrl+H → найти Alt+0160 → заменить на (ничего)).
• Не перезаписывайте формулы. При использовании Find & Replace с =*1 будьте уверены, что в выбранных ячейках нет формул, которые могут быть испорчены.
• Создавайте резервные копии. Перед массовым преобразованием всегда делайте копию листа – легко откатиться, если что‑то пошло не так.

Преобразование текстовых значений в числовые в Excel – задача, которую решают разными способами: от простых формул (VALUE, NUMBERVALUE) до мощных инструментов (Текст по столбцам, Power Query) и макросов. Выбор метода зависит от объёма данных, наличия специальных символов и частоты повторения задачи. Освоив перечисленные техники, вы сможете быстро и без ошибок подготовить любые данные к аналитической работе.