Как массово преобразовать числа, сохраненные в виде текста, в числа в Excel?

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

Массовое преобразование последовательных чисел, сохраненных как текст, в числа в Excel
Массовое преобразование разрозненных чисел, сохраненных как текст, в числа в Excel
Массовое преобразование чисел, сохраненных как текст, в числа за несколько кликов
Формула Excel - Преобразование текста в числа с помощью функции VALUE
Код VBA - Используйте макрос для программного преобразования текста в числа

Массовое преобразование последовательных чисел, сохраненных как текст, в числа в Excel

Если числа, сохраненные как текст, являются смежными (расположены рядом друг с другом в одном или нескольких блоках) на вашем листе, Excel предоставляет простой инструмент для их быстрого преобразования в числа. Типичный сценарий для этого - когда импортированные данные, такие как столбцы чисел, оказываются обработанными как текст, о чем может свидетельствовать зеленый треугольник в углу каждой ячейки.

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

2. В появившемся меню выберите Преобразовать в число. Excel мгновенно преобразует все выбранные числа, отформатированные как текст, в правильные числовые значения.

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

Совет: Если кнопка не появилась, дважды проверьте, что ваши данные распознаются как текст (вы можете проверить форматирование в разделе Главная > Формат числа). Этот метод лучше всего подходит для непрерывных диапазонов; для несмежных ячеек смотрите следующие решения.

Массовое преобразование разрозненных чисел, сохраненных как текст, в числа в Excel

В случаях, когда числа, отформатированные как текст, распределены по всему листу (не в одном непрерывном диапазоне), их ручное преобразование по одному может занять много времени. Функция Специальная вставка в Excel позволяет одновременно преобразовать эти разбросанные ячейки.

1. Введите число «1» в любую пустую ячейку и скопируйте его, нажав Ctrl + C. Затем, удерживая клавишу Ctrl, выберите все несмежные ячейки, которые вы хотите преобразовать — щелкните каждую из них или перетащите для выделения по необходимости. При активном выделении нажмите Ctrl + Alt + V (это открывает диалоговое окно Специальная вставка).

2. В диалоговом окне Специальная вставка в разделе Операция выберите Умножить, затем нажмите OK. Смотрите скриншот:

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

Примечание: Функция Умножить работает только в том случае, если содержимое ячеек распознается как числовой текст (т.е. без нечисловых символов). Если ваши данные содержат лишние пробелы или нечисловые символы, сначала очистите данные. Этот метод особенно полезен при работе с разбросанными данными или когда кнопка «Преобразовать в число» недоступна.

Массовое преобразование чисел, сохраненных как текст, в числа с помощью Kutools for Excel

Утилита Convert between Text and Number из Kutools for Excel поможет вам легко преобразовать все числа, сохраненные как текст, в числа в диапазоне или нескольких диапазонах.

1. Выберите либо непрерывный блок ячеек, либо несколько диапазонов, которые вы хотите преобразовать, а затем нажмите Kutools > Текст > Convert between Text and Number.

2. В диалоговом окне Convert between Text and Number выберите опцию Текст в число. Нажмите кнопку OK для выполнения преобразования.

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

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

Формула Excel - Преобразование текста в числа с помощью функции VALUE

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

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

Шаги:

1. Предположим, у вас есть числа, отформатированные как текст в столбце A, начиная с ячейки A1. В соответствующей ячейке столбца B (например, B1) введите следующую формулу:

=VALUE(A1)

2. Нажмите Enter для подтверждения формулы. Затем, чтобы применить это преобразование ко всему вашему набору данных, перетащите маркер заполнения вниз от B1, чтобы охватить столько строк, сколько необходимо.

Совет: Если вы хотите заменить исходные ячейки преобразованными числами, после получения результатов скопируйте результаты в столбце B, щелкните правой кнопкой мыши исходный диапазон и выберите «Вставить значения», чтобы перезаписать текст фактическими числами. Будьте осторожны: сделайте резервную копию исходных данных перед вставкой, если это необходимо.

Меры предосторожности:

• Если ячейка содержит нечисловой текст, VALUE вернет ошибку #VALUE!. Убедитесь, что ваши данные содержат текст, представляющий только числа.
• Проблемы форматирования, такие как начальные/конечные пробелы, могут вызвать непредвиденные результаты. При необходимости сначала очистите данные с помощью функций TRIM или CLEAN.

Код VBA - Используйте макрос для программного преобразования текста в числа

Для более продвинутых пользователей или тех, кто работает с очень большими наборами данных, автоматизация процесса преобразования с помощью макроса VBA может значительно сэкономить время и повторяющиеся усилия. VBA позволяет программно преобразовать числа, сохраненные как текст, в реальные числа для всего выделенного фрагмента, столбца или листа. Это особенно полезно при работе с регулярно обновляемыми книгами или для создания повторяемых рабочих процессов.

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

Шаги использования макроса VBA:

1. Перейдите на вкладку Разработчик > Visual Basic. В открывшемся окне нажмите Вставить > Модуль и вставьте следующий код в окно модуля:

Sub ConvertTextNumbersToNumbers()
    Dim rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Please select the range to convert text to number", xTitleId, WorkRng.Address, Type:=8)
    For Each rng In WorkRng
        If IsNumeric(rng.Value) And VarType(rng.Value) = vbString Then
            rng.Value = Val(rng.Value)
        End If
    Next
End Sub

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

Советы и меры предосторожности:

• Этот макрос обрабатывает каждую ячейку только в том случае, если она представляет собой число в виде строки. Данные, такие как буквенный текст или ошибки, останутся без изменений.
• Всегда создавайте резервную копию вашего листа перед запуском макросов, чтобы вы могли восстановить исходные данные, если потребуется.
• Макрос можно настроить для целых столбцов или конкретных листов, изменив часть, которая задает WorkRng.
• Если в вашей книге уже включены макросы, этот подход работает без проблем. Если макросы отключены, включите их по запросу Excel.

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