美文网首页EXCEL数据结构和算法分析excel的一些小技巧教程
Excel表格中格式转换的这些套路,你都get了吗?

Excel表格中格式转换的这些套路,你都get了吗?

作者: 猴子数据分析 | 来源:发表于2019-07-27 10:22 被阅读13次

    经常会遇到朋友问我这样的问题:

    为什么Excel表格里的金额无法合计?

    考勤系统导出的报表日期如何修改显示形式?

    明明是数字,为什么计算就报错呢?

    下面我们一起看下处理这种格式问题的办法。

    问题1:如何将文本型数字转换为数值格式?

    下面是淘宝婴儿数据,“购买数量”虽然看上去是数字,但其实是文本格式。

    文本格式用于计算会发上什么情况呢?

    下面图片中对“购买数量”这一列求和,结果就是错误的。

    再比如,直接用对文本格式的列做“描述统计分析”,就会报下面的错误

    这是因为只有数值格式才能进行计算,而这一列是文本格式,无法进行计算。这时候,就需要将这一列转换为数值格式。

    下面介绍3种将文本转换为数值格式的方法。

    方法1)使用“数据”-“分列”功能

    方法2)使用value函数

    新建一列作为辅助列,用于存放转换后的数值。在这个案例数据上,我新建的列是F列。在这一列的F2单元格里输入下图中的value函数:=value(D2),表示让这一单元格的值等于D2单元格的值。

    输入公式后按回车键,可以看到数值结果(数值格式在单元格靠右)

    把鼠标放置在F2单元格右下角,当出现十字标志时双击即可完成整列公式填充。然后用求和函数就可以看到结果啦。

    方法3)让文本型的数字乘以1或者加减0

    如下图,借助辅助单元格F2,输入数字1,然后复制1所在的单元格。

    选中D列数字区域,然后点击鼠标右键:“选择性粘贴”-“乘”;单击“确定”

    问题2:如何将数字格式转为文本格式?

    如果数字有大小、多少的概念时,我们将数字处理成数值格式;但有的数字本身并无大小、多少含义,只是一串数字代码,那么就需要将数字保留成文本格式。

    比如,工作中经常遇到输入的是18位的身份证号码,Excel却显示的是指数形式,当再次双击查看编辑单元格时,后3位数字已经显示为000。

    这是因为Excel对于数值,只能保留15位的精确度,所以,对于身份证的输入只能将单元格改为文本格式才能显示正确。

    这个案例里的“用户名”、“商品类别”列里的数字并无大小含义,无需运算处理,所以我们要将其处理为文本格式。

    方法1)应用分列工具

    首先,选中数据区域;其次,选择“数据”选项卡的“分列”功能

    方法2)text函数转换法

    借助辅助列F列,在F2单元格输入=text(C2,"@")   其中@代表文本格式。下面图片是把“商品类别”的数字处理为文本格式。

    方法3)巧用文本连接符&,用&去连接一个空值的方法完成转换

    问题3:怎样让“假”日期改成“真”日期格式?

    什么是假日期呢?

    日期的本质是数字,标准格式的“真”日期,能够和数字进行加减运算,也可以通过右键单元格来改变日期显示形式,但”假日期”做不到。

    从下图的操作来看,“假”日期是无法通过“单元格格式设置”来更改显示形式的,示例处一直显示#######

    下面介绍两种更改日期格式的操作方法

    方法1)使用“数据”-“分列”功能

    方法2)使用自定义格式格式的方法

    下图步骤4的地方手动输入编码“0000-00-00”。其中0在日期格式中代表占位符

    点击“完成”之后结果如下

    4.总结

    上面介绍了工作中经常遇到的格式转换问题:

    1)文本型数字无法用于计算,需要转换为数值格式

    2)没有大小含义的数字(比如身份证号、编码),需要转为文本格式

    3)“假”日期格式无法通过右键单元格来改变日期显示形式

    上面这些格式问题,都可以使用Excel的分列功能解决。

    推荐:如何提高逻辑思维能力?

    相关文章

      网友评论

        本文标题:Excel表格中格式转换的这些套路,你都get了吗?

        本文链接:https://www.haomeiwen.com/subject/srpsrctx.html