Excel

Excel текст в число формула: Преобразовать текст в число Excel, или как сделать ячейку текстового формата числовым форматом — MS Office Excel — Работа на компьютере: инструкции и советы — Образование, воспитание и обучение

Содержание

Преобразование чисел из текстового формата в числовой

Excel

Ввод и форматирование данных

Форматирование данных

Форматирование данных

Преобразование чисел из текстового формата в числовой

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

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

1. Выделите столбец

Выделите столбец с такими ячейками. Если вы не хотите преобразовывать весь столбец, можно выбрать одну или несколько ячеек. Ячейки должны находиться в одном и том же столбце, иначе этот процесс не будет работать. (Если такие ячейки есть в нескольких столбцах, см. раздел «Другие способы преобразования» ниже.)

2. Нажмите эту кнопку

Кнопка «столбцы» обычно применяется для разделения столбцов, но ее также можно использовать для преобразования столбца текста в числа. На вкладке Данные нажмите кнопку Текст по столбцам.

3. Нажмите кнопку «Применить».

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

4. Задайте формат

Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите нужный формат.

Примечание: Если вы по-прежнему видите формулы, которые не выводят числовые результаты, возможно, включен параметр

Показать формулы. Откройте вкладку Формулы и отключите параметр Показать формулы.

Другие способы преобразования

С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.

1. Вставьте новый столбец

Вставьте столбец рядом с ячейками, содержащими текст. В этом примере столбец E содержит числа, которые хранятся в виде текста. Столбец F является новым столбцом.

2. Примените функцию ЗНАЧЕН

В одной из ячеек нового столбца введите =ЗНАЧЕН() и укажите в скобках ссылку на ячейку, содержащую числа, которые хранятся в виде текста. В данном примере это ячейка E23.

3. Наведите указатель мыши

Теперь нужно заполнить формулу вниз. Вот как это сделать: Наведите указатель на правый нижний угол ячейки, чтобы он принял вид знака плюс (+).

4. Щелкните и перетащите вниз

Щелкните и перетащите вниз, чтобы заполнить формулу другими ячейками. После этого можно использовать этот новый столбец или скопировать и вставить эти новые значения в исходный столбец. Вот как это сделать: выделите ячейки с новой формулой. Нажмите клавиши CTRL+C. Щелкните первую ячейку исходного столбца. Затем на вкладке « Главная» щелкните стрелку под вставку и выберите команду «Специальная >значений».

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

  1. Выделите пустую ячейку, в которой нет этой проблемы, введите в нее цифру 1 и нажмите клавишу ВВОД.

  2. Нажмите клавиши CTRL+C, чтобы скопировать ячейку.

  3. org/ListItem»>

    Выделите ячейки с числами, которые сохранены как текст.

  4. На вкладке Главная щелкните Вставить и выберите пункт Специальная вставка.

  5. Щелкните умножить и нажмите кнопку ОК. Excel умножит каждую ячейку на 1, при этом преобразовав текст в числа.

  6. Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите нужный формат.

Статьи по теме

Замена формулы ее результатом
Десять основных способов очистки данных

Функция CLEAN

Преобразование чисел-как-текст в нормальные числа

102630 13. 08.2016 Скачать пример

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


Причем иногда такой индикатор не появляется (что гораздо хуже).

В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:

  • перестает нормально работать сортировка — «псевдочисла» выдавливаются вниз, а не располагаются по-порядку как положено:

  • функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:

  • при фильтрации псевдочисла отбираются ошибочно
  • многие другие функции Excel также перестают нормально работать:
  • и т. д.

Особенно забавно, что естественное желание просто изменить формат ячейки на числовой — не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells), меняете формат на Числовой (Number), жмете ОК — и ничего не происходит! Совсем!

Возможно, «это не баг, а фича», конечно, но нам от этого не легче. Так что давайте-к рассмотрим несколько способов исправить ситуацию — один из них вам обязательно поможет.

Способ 1. Зеленый уголок-индикатор

Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number):


Все числа в выделенном диапазоне будут преобразованы в полноценные.

Если зеленых уголков нет совсем, то проверьте — не выключены ли они в настройках вашего Excel (Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом).

Способ 2. Повторный ввод

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

F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.

Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.

Способ 3. Формула

Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:


Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.

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

Способ 4. Специальная вставка


Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили  зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:

  • в любую пустую ячейку введите 1
  • скопируйте ее
  • выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
  • щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
  • в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)


По-сути, мы выполняем то же самое, что и в прошлом способе — умножение содержимого ячеек на единицу — но не формулами, а напрямую из буфера.


Способ 5. Текст по столбцам

Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data). На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.

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


После нажатия на

Готово Excel преобразует наш текст в нормальные числа.

Способ 6. Макрос

Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующий код: 

Sub Convert_Text_to_Numbers()
    Selection.NumberFormat = "General"
    Selection.Value = Selection.Value
End Sub

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros), выбрать наш макрос в списке, нажать кнопку Выполнить (Run) — и моментально преобразовать псевдочисла в полноценные.

Также можно добавить этот макрос в личную книгу макросов, чтобы использовать позднее в любом файле.

P.S.

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Ссылки по теме

  • Деление слипшегося текста по столбцам
  • Вычисления без формул специальной вставкой
  • Преобразование текста в числа с помощью надстройки PLEX

Функция ЗНАЧЕН в Excel для преобразования текста в число

Функция ЗНАЧЕН в Excel выполняет операцию преобразования строки в числовое значение в тех случаях, где это возможно. Данная функция возвращает число в случае удачно выполненного процесса преобразования или код ошибки #ЗНАЧ!, если преобразование текста в число невозможно.

Примеры функции ЗНАЧЕН в Excel и особенности ее использования

Данная функция имеет следующую синтаксическую запись:

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

Единственный аргумент — текст является обязательным для заполнения. То есть, данная функция принимает на вход текстовые данные, которые могут быть представлены в виде текстовой строки, введенной в кавычках (например, ЗНАЧЕН(“9 300 ₽”) или ссылки на ячейку таблицы, в которой содержится текст.

Примечания:

  1. Формат текста, принимаемого в качестве параметра функции ЗНАЧЕН, должен быть одним из предусмотренных в Excel (время, дата, число, денежный). Результатом выполнения функции будет являться ошибка #ЗНАЧ!, если исходный текст не соответствует одному из этих типов данных.
  2. На практике функция ЗНАЧЕН используется довольно редко, поскольку в Excel реализован механизм автоматического преобразования значений подобно некоторым языкам программирования, в которых присутствует неявное преобразование типов данных. Эта функция реализована с целью совместимости с прочими программными продуктами, поддерживающими работу с таблицами.
  3. Функция полезна в случаях, когда числовые значения внесены в ячейки с установленным текстовым форматом данных. Также в Excel можно использовать надстройку, выполняющую поиск чисел, отформатированных в качестве текста и последующее преобразование их в числовой формат.
  4. Чаще всего функция ЗНАЧЕН используется для преобразования результатов работы функций ПСТР и ЛЕВСИМВ (ПРАВСИМВ), однако для корректного отображения к результатам вычислений данных функций можно просто прибавить 0 (нуль). Подробнее рассмотрим это в одном из примеров.



Примеры использования функции ЗНАЧЕН в Excel

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

Часть таблицы выглядит следующим образом:

Для расчета общего количества представленных 5 наименований продукции используем следующую формулу:

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

В результате получим следующее:

То есть, в данной части таблицы представлены 265 единиц товаров.

Как преобразовать денежный формат в числовое значение

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

Изначально таблица выглядит следующим образом:

Для конвертирования денежных данных в числовые была использована функция ЗНАЧЕН. Расчет выполняется для каждого сотрудника по-отдельности. Приведем пример использования для сотрудника с фамилией Иванов:

=ЗНАЧЕН(B2)

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

Функция ЗНАЧЕН и формат времени

Пример 3. Самолет вылетел согласно расписанию в 13:40 и должен был совершить посадку в конечном пункте перелета в 17:20. Во время полета произошел инцидент, в связи которым рейс был задержан на определенное время. Самолет приземлился в 19:13. Необходимо определить время задержки в часах и минутах. Данные о времени вылета, расчетного и фактического времени прибытия занесены в ячейки таблицы формата «Дата».

Таблица имеет следующий вид:

Для решения задачи запишем следующую формулу:

Примечание: функция ЗНАЧЕН использована для явного преобразования дат в числа в коде времени Excel. Фактически, записи «=ЗНАЧЕН(C2)-ЗНАЧЕН(B2)» и «C2-B2» являются эквивалентными, поскольку Excel выполняет неявное преобразование. Прямое преобразование имеет практический смысл при использовании полученных значений в других программных продуктах, не поддерживающих форматы даты и времени Excel.

В результате будет получено число в коде времени Excel:

Для получения искомого значения необходимо преобразовать формат данных ячейки в «Время»:

То есть, самолет опоздал на 1 час и 53 минуты.

Преобразование чисел, сохраненных в виде текста, в числа

Excel

Введите и отформатируйте данные

Формат данных

Формат данных

Преобразование чисел, сохраненных в виде текста, в числа

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно…Меньше

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

1. Выберите столбец

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

2. Нажмите эту кнопку

Кнопка «Текст в столбцы» обычно используется для разделения столбца, но ее также можно использовать для преобразования одного столбца текста в числа. На вкладке Данные щелкните Текст в столбцы .

3. Нажмите «Применить».

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

4. Установите формат

Нажмите CTRL + 1 (или + 1 на Mac). Затем выберите любой формат.

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

Другие способы преобразования:

Вы можете использовать функцию ЗНАЧ для возврата только числового значения текста.

1. Вставить новый столбец

Вставить новый столбец рядом с ячейками с текстом. В этом примере столбец E содержит текст, хранящийся в виде чисел. Столбец F — новый столбец.

2. Используйте функцию ЗНАЧЕНИЕ

В одной из ячеек нового столбца введите =ЗНАЧ() и в круглых скобках введите ссылку на ячейку, содержащую текст, хранящийся в виде чисел. В этом примере это ячейка Е23 .

3. Оставьте курсор здесь

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

4. Щелкните и перетащите вниз

Щелкните и перетащите вниз, чтобы заполнить формулой другие ячейки. После этого вы можете использовать этот новый столбец или скопировать и вставить эти новые значения в исходный столбец. Вот как это сделать: Выберите ячейки с новой формулой. Нажмите CTRL + C. Щелкните первую ячейку исходного столбца. Потом на Вкладка Главная щелкните стрелку под Вставить , а затем щелкните Специальная вставка > Значения .

Если описанные выше шаги не сработали, вы можете использовать этот метод, который можно использовать, если вы пытаетесь преобразовать более одного столбца текста.

  1. Выберите пустую ячейку, в которой нет этой проблемы, введите в нее число 1 и нажмите Enter.

  2. Нажмите CTRL + C, чтобы скопировать ячейку.

  3. org/ListItem»>

    Выберите ячейки, в которых числа сохранены в виде текста.

  4. На вкладке Главная нажмите Вставить > Специальная вставка .

  5. Нажмите Умножьте и нажмите OK . Excel умножает каждую ячейку на 1 и при этом преобразует текст в числа.

  6. Нажмите CTRL + 1 (или + 1 на Mac). Затем выберите любой формат.

Похожие темы

Заменить формулу ее результатом
Десять лучших способов очистить ваши данные
Функция ОЧИСТКИ

7 способов преобразования текста в числа в Microsoft Excel

Этот пост покажет вам все способы преобразования текста в числа в Microsoft Excel.

Довольно часто в Excel возникает проблема с числами, которые были введены или отформатированы как текстовые значения.

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

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

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

Как идентифицировать числа, введенные в виде текста

Как узнать, были ли данные вашего номера введены в виде текста?

Есть несколько простых способов сказать!

Выровнено по левому краю

Первый способ определить, есть ли у вас текстовые значения вместо числовых, — это способ выравнивания данных в ячейке.

По умолчанию текст выравнивается по левому краю, а числа выравниваются по правому краю.

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

Проверка ошибок зеленого треугольника

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

Любые ячейки, помеченные как ошибочные, будут отмечены маленьким зеленым треугольником слева от ячейки, визуально указывающим на ошибку.

При выборе такой ячейки появится маленький предупреждающий значок.

Когда вы нажмете на это, он покажет вам, что это за ошибка. В этом случае вы можете увидеть ошибку Number Stored as Text .

Вы можете полностью отключить эту проверку ошибок или указать, какие ошибки следует помечать.

Перейдите на вкладку Файл и выберите Опции внизу. Это откроет меню Параметры Excel .

В разделе Формула меню Параметры Excel ➜ Проверка ошибок .

Снимите флажок Включить фоновую проверку ошибок , чтобы отключить эту функцию. Здесь вы также можете настроить цвет, используемый для обозначения ошибок.

Применен текстовый формат

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

Существует быстрый способ определить, применялось ли к ячейке форматирование текста.

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

Предшествующий апостроф

Другой способ ввода чисел в виде текста — использование предшествующего апострофа.

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

Это означает, что если вы видите передний апостроф в строке формул, вы знаете, что данные являются текстовым значением.

Использование функции ISTEXT

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

 ISTEXT (ввод)
  • ввод — это значение, ячейка или диапазон, который вы хотите проверить, является ли он текстом.

ИСТЕКСТ 9Функция 0168 вернет TRUE , если значение является текстом, и FALSE в противном случае.

 = ISTEXT ( B3 )

Здесь вы можете увидеть, как функция ISTEXT используется для определения того, содержат ли ячейки текст. Функция возвращает TRUE , когда находит текстовое значение.

 ISNUMBER (ввод)
  • ввод — это значение, ячейка или диапазон, который вы хотите проверить, является ли он числом.

Точно так же вы можете проверить, является ли значение числом, используя IНОМЕР функция. Он принимает один вход и возвращает ИСТИНА , если значение является числом, и ЛОЖЬ в противном случае.

Здесь те же данные проверяются с помощью функции ISNUMBER . Функция возвращает ЛОЖЬ , когда не находит число.

Строка состояния показывает только количество

Еще один способ быстро проверить диапазон ячеек, чтобы увидеть, все ли они текстовые, — использовать строку состояния.

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

Если все числа введены как текстовые значения, то эти базовые статистические данные не будут рассчитываться. Вместо этого вы будете видеть только количество ячеек в строке состояния.

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

Преобразование текста в число с проверкой на наличие ошибок

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

Проверка ошибок также позволит вам преобразовать эти текстовые значения в правильные числа.

Щелкните значок Ошибка и выберите Преобразовать в число из вариантов. Затем Excel преобразует каждую ячейку в выбранном диапазоне в число.

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

Преобразование текста в число с преобразованием текста в столбец

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

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

Затем вы можете выбрать, как форматировать результаты, и здесь вы можете выбрать общий формат для вывода.

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

  1. Выберите ячейки, содержащие текст, который вы хотите преобразовать в число.
  2. Перейдите на вкладку Данные .
  3. Щелкните команду Text to Column на вкладке Data Tools .
  1. Выберите Delimited в параметрах Исходный тип данных .
  2. Нажмите кнопку Далее .
  1. Нажмите кнопку Далее еще раз на шаге 2 мастера преобразования текста в столбцы. Вы можете оставить параметры по умолчанию.
  1. Выбрать Общие из параметра Формат данных столбца .
  2. Выберите пункт назначения , если вы хотите поместить преобразованные значения в новое место. В противном случае вы можете сохранить значение по умолчанию, которое должно быть активной ячейкой, это заменит текст числовыми значениями.
  3. Нажмите кнопку Готово .

Это преобразует ваши текстовые значения в числовые значения.

Команда Text to Column обычно используется для разделения данных, разделенных запятыми или другими разделителями, на несколько столбцов.

Любые данные с разделителями будут текстовыми данными, поэтому, когда Excel разбивает данные на несколько столбцов, он также преобразует числовые данные в числа для дополнительного удобства.

Это тоже верно, даже если делить нечего!

Таким образом, мастер Text to Column можно использовать для преобразования числовых текстовых значений в числа.

Преобразование текста в число с помощью умножения на 1

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

Вы можете использовать это, чтобы преобразовать текст в числа, умножив их на 1. Поскольку вы умножаете на 1, это не изменит числа, но преобразует их.

 = B3 * 1

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

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

Преобразование текста в число с помощью специальной вставки Multiply

Умножение на 1 — отличный прием для преобразования текста в значения, но вы можете не захотеть использовать для этого формулу.

Хорошая новость: есть простой способ умножить текст на 1 без использования формулы.

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

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

  1. Введите 1 в любую ячейку на листе. 1 даже не обязательно должно быть числовым значением и может быть текстовым значением.
  2. Скопируйте ячейку, содержащую 1 , используя Ctrl + C на клавиатуре или , щелкните правой кнопкой мыши и выберите Копировать .
  3. Выберите ячейки с текстовыми значениями для преобразования.
  1. Нажмите Ctrl + Alt + V на клавиатуре или щелкните правой кнопкой мыши и выберите Специальная вставка . Это откроет меню Paste Special .
  2. Выберите Значения под Вставьте параметры и выберите Умножить в параметрах Операция .
  3. Нажмите кнопку OK .

Это умножит все значения на 1, которое было значением в скопированной ячейке. В результате текст преобразуется в значения, но поскольку вы умножаете на 1, числа не изменятся.

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

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

Функция ЗНАЧ принимает текстовое значение и возвращает текстовое значение в виде числа.

 = ЗНАЧЕНИЕ ( текст )
  • текст — это текстовое значение, которое вы хотите преобразовать в числовое значение.

Если текст содержит какие-либо нечисловые символы, функция ЗНАЧ вернет #ЗНАЧ! Ошибка . Он не извлекает числа из текста, он может только преобразовывать числа, введенные в виде текста, в числа.

 = ЗНАЧЕНИЕ ( B3 )

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

Преобразование текста в числа с помощью Power Query

Power Query — это замечательный инструмент для преобразования данных любого типа. Его, безусловно, можно использовать и для преобразования текста в числа.

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

Но в Power Query есть простой способ преобразования типов данных, включая текст, в числа.

Выполните следующие действия после импорта данных в Power Query.

Щелкните значок типа данных слева от заголовка столбца для значений, которые вы хотите преобразовать.

Каждый столбец в редакторе Power Query имеет значок типа данных, который отображает текущий тип данных столбца. Если столбцу не был назначен тип данных, показанный значок будет ABC123.

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

В этом случае вы можете выбрать в меню опцию Целое число .

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

 = Table.TransformColumnTypes(Source,{{"Text", Int64.Type}})

Вы увидите, что к запросу был применен новый шаг Changed Type , и он будет использовать приведенную выше формулу кода M.

Затем вы можете нажать Кнопка «Закрыть и загрузить » на вкладке «Главная », чтобы сохранить результаты и загрузить данные в Excel.

Возможно, вам даже не потребуется применять этот шаг преобразования типа данных к вашему запросу. Когда вы впервые импортируете данные в Power Query, он обычно угадывает тип данных и применяет преобразование за вас!

Преобразование текста в числа с помощью Power Pivot

Если вы хотите проанализировать или обобщить данные после преобразования их из текста в числа, вам может потребоваться выполнить преобразование в Power Pivot.

Power Pivot — это надстройка, позволяющая эффективно анализировать миллионы строк в модели данных с помощью сводных таблиц.

С помощью Power Pivot вы можете создавать вычисления на уровне строк, используя язык формул DAX для создания новых столбцов в ваших наборах данных. Затем вы можете получить доступ к этим новым столбцам в сводных таблицах, подключенных к модели данных.

В этом случае формула DAX, которую вы можете использовать для преобразования текста в числа, точно такая же, как и формула в сетке.

Выполните следующие действия в надстройке Power Pivot, чтобы создать новый вычисляемый столбец, преобразующий текст в числа.

Выберите любую ячейку под заголовком «Добавить столбец».

 =ЗНАЧ(ЧислаТекста[Текст])

Вставьте приведенную выше формулу. TextNumbers — это имя таблицы данных в Power Pivot, а Text — это имя столбца, который вы хотите преобразовать.

Дважды щелкните заголовок столбца, чтобы изменить имя.

Нажмите значок «Сохранить» и закройте надстройку Power Pivot.

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

  1. Перейдите на вкладку Вставка .
  2. Нажмите кнопку сводной таблицы .
  3. Выберите From Data Model в доступных опциях.

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

Выводы

Существует множество причин, по которым у вас могут быть данные, содержащие числа, отформатированные как текст.

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

К счастью, существует множество простых способов преобразования текста в числа, таких как проверка ошибок, специальная вставка, базовое умножение и функция ЗНАЧЕНИЕ . Это все простые способы преобразования текста внутри сетки.

Если вы импортируете данные с помощью Power Query или Power Pivot, вы можете выполнить преобразование в каждом из этих инструментов. Оба имеют методы, доступные для преобразования текста в числа.

Используете ли вы какой-либо из этих методов для преобразования текстовых значений в числа? Знаете ли вы какие-нибудь другие интересные способы выполнения этого действия? Позвольте мне знать в комментариях ниже!

Об авторе

Джон является Microsoft MVP и квалифицированным актуарием с более чем 15-летним опытом. Он работал в различных отраслях, включая страхование, рекламные технологии и совсем недавно в консалтинге Power Platform. Он умеет решать проблемы и страстно любит использовать технологии для повышения эффективности бизнеса.

Преобразование текста в числа в Excel

Обычно числа хранятся в виде текста в Excel. Это приводит к неправильным вычислениям при использовании этих ячеек в функциях Excel, таких как СУММ и СРЗНАЧ (поскольку эти функции игнорируют ячейки, содержащие текстовые значения). В таких случаях вам необходимо преобразовать ячейки, содержащие числа в виде текста, обратно в числа.

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

  1. Использование ‘ (апостроф) перед номером.
    • Многие люди вводят апостроф перед числом, чтобы сделать его текстовым. Иногда это также происходит, когда вы загружаете данные из базы данных. Хотя это заставляет числа отображаться без апострофа, это влияет на ячейку, заставляя ее обрабатывать числа как текст.
  2. Получение чисел по формуле (например, LEFT, RIGHT или MID)
    • Если вы извлекаете числовую часть текстовой строки (или даже часть числа) с помощью функций ТЕКСТ, результатом будет число в текстовом формате.

Теперь давайте посмотрим, как решать такие случаи.

В этом уроке вы узнаете, как преобразовать текст в числа в Excel.

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

  • Использование опции «Преобразовать в число».
  • Измените формат с Текст на Общий/Число.
  • Использование специальной вставки.
  • Использование текста в столбцах.
  • Использование комбинации функций VALUE, TRIM и CLEAN.

Преобразование текста в числа с помощью параметра «Преобразовать в число»

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

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

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

Это мгновенно преобразует все числа, сохраненные в виде текста, обратно в числа. Вы заметите, что числа выравниваются по правому краю после преобразования (в то время как они были выровнены по левому краю при сохранении в виде текста).

Преобразование текста в числа путем изменения формата ячейки

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

Вот шаги:

  • Выберите все ячейки, которые вы хотите преобразовать из текста в числа.
  • Перейти на главную -> номер. В раскрывающемся списке Формат числа выберите Общий.

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

 Читайте также: Как преобразовать серийные номера в даты в Excel

Преобразование текста в числа с помощью специальной опции вставки

Чтобы преобразовать текст в числа с помощью специальной опции вставки:

  • Введите 1 в любую пустую ячейку на листе. Убедитесь, что он отформатирован как число (т. е. выровнен по правому краю ячейки).
  • Скопируйте ячейку, содержащую 1.
  • Выберите ячейки, которые вы хотите преобразовать из текста в числа.
  • Щелкните правой кнопкой мыши и выберите «Специальная вставка».
  • В диалоговом окне «Специальная вставка» выберите «Умножение» в категории «Операция».
  • Нажмите OK.

Преобразование текста в числа с использованием текста в столбец

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

Вот шаги:

  • Выберите все ячейки, которые вы хотите преобразовать из текста в числа.
  • Перейдите в «Данные» -> «Инструменты данных» -> «Текст в столбцы».
  • В мастере преобразования текста в столбец:
    • На шаге 1: выберите «С разделителями» и нажмите «Далее».
    • На шаге 2: выберите Tab в качестве разделителя и нажмите «Далее».
    • На шаге 3: убедитесь, что в формате данных столбца выбрано значение «Общий». Вы также можете указать место назначения, где вы хотите получить результат. Если вы ничего не укажете, он заменит исходный набор данных.

Хотя результирующие ячейки по-прежнему могут быть в текстовом формате, а числа по-прежнему выровнены по левому краю, теперь это будет работать в таких функциях, как СУММ и СРЗНАЧ.

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

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

  • Функция ЗНАЧ преобразует любой текст, представляющий число, обратно в число.
  • Функция TRIM удаляет все начальные и конечные пробелы.
  • Функция CLEAN удаляет лишние пробелы и непечатаемые символы, которые могут проникнуть внутрь при импорте данных или загрузке из базы данных.

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

 =ЗНАЧ(ОТРЕЗАТЬ(ОЧИСТИТЬ(A1)))

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

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

Вам также могут понравиться следующие учебники по Excel:

  • Умножение в Excel с помощью специальной вставки.
  • Преобразование чисел в текст в Excel
  • Преобразование формул в значения с помощью специальной вставки.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *