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 数字转换函数逻辑基本一致,不同版本函数识别细节可能存在小幅差异,操作前建议备份原始表格数据,本文仅作为学习参考,不承担因数字转换错误造成表格统计偏差相关责任。
