ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 5582|回复: 3

[原创] 数字处理万金油 numbervalue 函数

[复制链接]

TA的精华主题

TA的得分主题

发表于 2019-6-10 22:01 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖已被收录到知识树中,索引项:NUMBERVALUE
本帖最后由 流浪铁匠 于 2019-6-10 22:05 编辑

在excel里,文本型数字和真数值是两种完全不同的数据类型,这点相信很多童鞋都已经清楚了
而在把文本型数字转化为数值时,函数公式里通常使用- -(一对负号,称为减负运算)(或者+0,*1)或value函数来处理
目前来说由于前者写法简单更为常见。不过,在一些特殊情况下,单独的减负运算不如value: a0.jpg

当数据录入不规范在日期或时间前录入多余空格时,value可以识别忽略多余空格直接转化为真日期但四则运算不行
这个情况下- - 需要配合trim清除多余空格才能解决(测试下除这类特殊情况,通常- - 优于value,因为简洁)

好了,关于这2个转化方式就聊到这里,因为这篇文章的重点不是这2个,而是value的衍生函数,2013新增的numbervalue
之所以要提及这个函数,是因为测试下这个函数的一些性质是前面这2个转化方式都不具备的,在一些特殊情况下有奇效
1)numbervalue自带清洗部分非打印字符的能力

a1.jpg

numbervalue可以直接清楚数字字符串两端的多余不可见字符char(9),char(13),char(10),注意只能清除存在单个非打印字符的情况
这个能力弱于clean(注意是文本函数需配合减负运算等把结果转化为数值),但对新手来说这个函数处理常规的软件导出数据足以

2)numbervalue自带将空格等少数不规范文本强制转化为0的能力
这个函数的性质很有意思,能把单元格内单独存在的空文本(""),空格(char(32)),半角逗号,百分号,char(9),char(10),char(13) 这7种直接识别为0

a3.jpg

如果对于excel有一定了解的伙伴都会知道,空文本与空格等在进行四则运算时都会报错,故存在数据源混合这类字符的单元格时要用函数批量转化为数值计算,
需要使用 0&,clean,text等把这些字符前进行处理才能直接计算,不过有了这个函数就可以直接统计了
a2.jpg

上面截图的a列数据由于存在多种不规范数据,直接减负运算必然报错
熟悉各种字符对运算影响的童鞋就会连续使用- -配合0&再配合clean(最简处理手段)的方式将这几种干扰因素解决,但新手肯定是不理解作用的,生搬硬套下数据源变化可能就不适合了
但numbervalue一个函数就可以直接处理这几种情况下的数据,直接转化为数值0

3)处理欧式数字格式
并不是所有国家的数字格式都是 千分位为逗号,小数位为点
欧洲部分国家的数字写法的千分位和小数位符号是有较大差异的,请参考截图(数字写法是百度的,仅用于函数功能说明,如有错漏敬请谅解)

a4.jpg

当然,对函数高手来说这个函数字符数过多是最大缺陷,但对于新手来说,这个函数可以清洗多种--与value无法直接解决的文本数字情况,而且新手不清楚哪种情况使用什么方式处理,因此还是有一定价值的

数值处理万金油--numbervalue.rar

15.1 KB, 下载次数: 216

评分

6

查看全部评分

TA的精华主题

TA的得分主题

发表于 2019-6-10 22:19 | 显示全部楼层
学习了,学习,谢谢

TA的精华主题

TA的得分主题

发表于 2020-11-16 13:40 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
学习了,一直以为这个和VALUE一样的

TA的精华主题

TA的得分主题

发表于 2020-11-16 16:25 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-6-12 23:19 , Processed in 0.040737 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表