您的位置:首页 >知识分享 >正文

excel 数字转换 常用数字类型与格式转换大全

导读 核心结论前置,excel 数字转换涵盖多种高频场景,主要包含文本型数字转数值、数值转文本、数值转中文大写、数值保留小数四舍五入、单位换

核心结论前置,excel 数字转换涵盖多种高频场景,主要包含文本型数字转数值、数值转文本、数值转中文大写、数值保留小数四舍五入、单位换算、科学计数还原、日期序列号转日期这几大类,最常见故障就是数字变成文本格式,单元格左上角带绿色三角,后续求和、下拉递增、透视表统计全部失效,这个是办公最高频问题,优先用分列或者 VALUE 函数转为真正数值。很多新手分不清显示格式和真实数据类型,单元格设置格式只是外观变化,底层数据类型没有改变,想要真正转换数据类型,必须使用函数或者分列工具,这点是核心要点,不同转换场景对应的工具和函数不一样,不能混用。

原因说明

excel 里面数据分为数值型和文本型,数值型可以参与四则运算、排序、汇总,文本型数字本质是文字,软件不识别成数字,计算会报错或者结果异常。VALUE 函数专门把文本形式的数字转为数值,TEXT 函数作用相反,把数值转为指定格式的文本,这两个是数字转换最核心的一对函数。分列是一键批量转换工具,不需要写公式,适合整列批量修复文本数字,是办公首选快捷方案。中文大写、单位换算属于二次格式转换,需要搭配对应的公式或者自定义单元格格式完成。

一 文本型数字转数值 最高频场景

适用情况,复制导入的数据,数字变成文本,求和显示 0,下拉不能递增

方法 1 分列一键转换

1 选中目标整列

2 点击数据选项卡,选择分列

3 第一步选分隔符号,直接点下一步,第二步继续下一步

4 第三步列数据格式选择常规,点击完成,整列文本数字自动转为数值

方法 2 VALUE 函数

公式 =VALUE (A2)

1 在辅助列输入公式,回车

2 下拉填充整列,核对后复制,选择性粘贴为数值,替换原列

方法 3 黄色感叹号转换

选中区域,点击单元格左上角黄色感叹号,选择转换为数字

二 数值转文本

适用场景,需要保留前导零,比如编号 00123,数值格式会自动把前面 0 去掉

公式 =TEXT (A2,"00000")

说明 TEXT 可以自定义格式代码,除了补零,也可以转成带千分位、百分比样式的文本

操作步骤

1 辅助列输入 TEXT 公式

2 下拉填充,之后选择性粘贴为数值

三 数值转中文大写

适用场景 财务票据,壹贰叁肆格式

普通整数大写 =TEXT (A2,"[dbnum2]")

如果需要完整人民币元角分,使用财务嵌套公式,支持末尾加元角分整字

补充 NUMBERSTRING 是隐藏函数,新版兼容性不稳定,不推荐作为财务首选

四 数值四舍五入转换

公式 =ROUND (A2,2),第二个参数代表保留小数位数,0 代表取整,这个是真实修改数值,不是单纯修改显示格式

五 科学计数法还原成普通数字

适用场景 长编号身份证号,excel 自动变成 1.23E+17 科学计数

解决办法,提前把整列设置为文本格式再粘贴,已经变成科学计数的数据,长号码精度已经丢失,无法完整还原,这个是重点注意事项

六 日期序列号转日期

excel 日期本质是数字序列号,1 代表 1900 年 1 月 1 日

公式 =TEXT (A2,"yyyy-mm-dd")

或者选中单元格,单元格格式改成日期格式即可

各类转换方案对比表格

转换类型推荐工具 / 函数核心特点
文本数字转数值分列 VALUE修复无法求和、无法递增
数值转文本TEXT保留前导零,自定义文本样式
数值转中文大写TEXT [dbnum2]财务壹贰叁大写
四舍五入转换ROUND真实修改数值精度
序列号转日期TEXT 单元格格式序列号转为日期显示

重点注意事项

单元格自定义格式只是改变显示外观,底层仍然是原来的数据类型,不属于真正的数据类型转换,后续公式运算依然读取原始数据。长身份证号码一旦自动转为科学计数,末尾几位数字会永久丢失,无法恢复,导入前必须提前设置整列为文本格式。TEXT 函数输出结果是文本,后续不能直接参与数值运算,如果还要计算,不能使用 TEXT 结果。批量转换前建议备份原始数据,避免转换错误无法回退。

FAQ

问题 1 excel 数字转换,为什么数字是文本不能求和

回答 excel 数字转换,文本型数字软件识别成文字,excel 数字转换可以用分列或者 VALUE 函数转成常规数值,之后就可以正常求和。

问题 2 excel 数字转换,TEXT 函数是把数字转成文本吗

回答 excel 数字转换,TEXT 函数作用就是把数值按指定格式转成文本,excel 数字转换输出结果不能直接做数值计算。

问题 3 excel 数字转换,身份证变成科学计数还能恢复吗

回答 excel 数字转换,长号码变成科学计数之后末尾精度已经丢失,excel 数字转换无法完整还原,后续录入要提前设置文本格式。

问题 4 excel 数字转换,单元格设置格式是不是真正转换数据类型

回答 excel 数字转换,单元格格式只是修改显示样式,excel 数字转换底层数据类型不变,真正转换类型需要分列或者 VALUE、TEXT 函数。

免责声明:本教程基于微软 Excel 主流版本编写,WPS 表格 excel 数字转换函数逻辑基本一致,不同版本函数识别细节可能存在小幅差异,操作前建议备份原始表格数据,本文仅作为学习参考,不承担因数字转换错误造成表格统计偏差相关责任。