ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[原创] “0”活多变的公式与格式(24/10整理完成)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2010-8-19 13:43 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:拆分和提取
本帖最后由 wshcw 于 2013-12-31 09:39 编辑

俗话说“不吃三天饭嘴回生”,我不写十年的文章手发料,很不会写文章让你见笑了,错了请大家指正(文章9000多字),文章如下:


0”活多变的函数公式与格式


如果你问一个学前班或者一年级的小朋友,0表示什么?他会毫不犹豫的告诉你,0表示没有,比如草地上一只羊也没有,老师就叫我们用0表示。早上爸爸给我买了两个苹果,我吃了一个,弟弟也吃了一个,现在一个也没有,就用0表示。这样的例子小朋友还可以说得很多。
小朋友说的没错,0表示“没有”可能是0最早的意思吧,也就是0的本义。古时候的人最初完全没有数量这个概念,后来由于记事和分配生活用品等方面的需要,才逐渐产生了数的概念。比如捕获了一头野兽,就用1块石子代表。捕获了3头,就放3块石子。假如什么都没有捕获,当然是0头了。这样就产生了数,各国的人们也学会了用不同的符号表示不同的数字,但人们最后学会的是怎么表示0,因为其他的数字都比较好表示,所以后来有人把铜钱摆在空位上,以免弄错,这就表示0。不过多数人认为,"0"这一数学符号的发明应归功于公元6世纪的印度人。他们最早用黑点(·)表示零,后来逐渐变成了"0"
E氏函数家族中的“0”也真像小朋友所说的那样表示什么也没有吗?不然,0的活用与不用蕴藏着很多意想不到的玄机。到底有怎样的玄机呢?那我就0机一动开处方,虽然不算什么0当妙药”,闲话少说,E切从0开始,一起来看看0牙利齿吧!
一、活“0”活现

(一)简单文本求和中0的作用(+0或-0)

例子:将AA1:A10的数字相加,其中可能还有文本型的数字也需要相加。公式:
=SUMPRODUCTA1:A10+0
或者:
=SUMPRODUCTA1:A10-0
解析:初级用户会觉得+0,-0不就等于没有增加,没有减少嘛,为何要这样呢?是啊,要的就是这个效果,既要改变原数据的性质(文本转变为数值),又要准确计算,所以只有用+0,-0,这一“+”或“-”符号就是改变原数据的性质的。这一带符号的0犹如一个“小石头”,从后面抛出去将昏睡中的“大石头”(数字)砸醒。
参考文章:文本转数值的十一种方法(百度一下可查询到)


(二)“0”嶺先锋


①、如:单元格A1中输入数字1230456857921315位以下,文本或数值型均可)要将这个数的每一位相加,公式:
=SUM(--(0&MID(A1,COLUMN(1:1),1)))
解析:因单元格字符串长度只有14位提取长度为1256位的长度,所以从15位开始,只能提取到空值。效果如下:
=SUMPRODUCT(--(0&{"1","2","3","0","4","5","6","8","5","7","9","2","1","3","",……,""}))
前面补0后的效果如下:
=SUMPRODUCT(--{"01","02","03","00","04","05","06","08","05","07","09","02","01","03","0",……,"0"})
此时没有空值,只有14个文本数字和文本0,前面加2个负号后,转化为数值,看效果:
=SUMPRODUCT({1,2,3,0,4,5,6,8,5,7,9,2,1,3,0,……,0})
没有空值,且全部为数值就可以相加了,结果为56
例②、单元格A1输入:123大理789,要将这个单元格的每一位数字相加,公式:
=SUMPRODUCT(--(0&MIDB(A1,COLUMN(1:1),1)))
与上例不同的是,MIDB会将每个双字节(如汉字就是双字节)字符按2计数,否则,函数MIDB会将每个字符按1计数。当只提取1个字节时,遇到汉字(双字节),只能提取到半个汉字(也就是空值),效果如下:
=SUMPRODUCT(--(0&{"1","2","3"," "," ……,""}))
0&后填补空值。
二、脱胎换骨—化“文”为“0
转10楼:
http://club.excelhome.net/viewthread.php?tid=614168&page=1#pid4144010

 

 文章零乱,可以点击左上角的“只看该作者,免得翻页麻烦。看如下动画:

只看该作者.gif

 

 

点评

多年后,重新来学校大理老师的力作!  发表于 2018-5-13 08:35

评分

23

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-8-19 13:51 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
   
站位学习!!!
感谢分享!

TA的精华主题

TA的得分主题

发表于 2010-8-19 13:53 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-8-19 13:56 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
占位。等下文。

TA的精华主题

TA的得分主题

发表于 2010-8-19 13:58 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-8-19 13:58 | 显示全部楼层
很通俗易懂!
谢谢wshcw版主分享函数心得!

TA的精华主题

TA的得分主题

发表于 2010-8-19 14:10 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-8-19 14:35 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-8-19 14:39 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-8-20 21:26 | 显示全部楼层
结1楼:

二、脱胎换骨—化“文”为“0

单元格A1输入123abcABC789,要将这个单元格的每一位数字相加,公式:
=SUMPRODUCT(--TEXT(MID(A1,COLUMN(1:1),1),"0;;0;\0"))
解析:由于字符串中有“abcABC”,是单字节字符,所以不能象上例那样用MIDB提取半个汉字的办法来处理。此时,我们仍用MID来提取的基础上,再请出“霸道,聪明”的TEXT函数,将非数字字符强行改为0,若为数值则不变。条件参数"0;;0;\0"中第一个0神通广大,代表了除0之外的任意正整数,也就是假0(是通配数值的0),第二个则是“苍蝇嘴巴狗鼻子—真0”,第三个0是强行做“变性”手术后的0
三、“0”补队员

①单元格A1输入数字12378945600123,如何将单元格内数字按顺序去重。
公式:
=MID(SUM((0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&5^19),ROW($1:$10)),1))/10^ROW($1:$10))&"00",3,COUNT(FIND(ROW($1:$10)-1,A1)))
或者:
=MID(SUM(MID(A1&56^7,SMALL(FIND(ROW($1:$10)-1,A1&56^7),ROW($1:$10)),1)/10^ROW($1:$10))&0,3,COUNT(FIND(ROW($1:$10)-1,A1)))
解析:
”(0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&5^19),ROW($1:$10)),1))”中,前面补0,是为了填补空值,这里不再赘述,式子:SUM((0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&5^19),ROW($1:$10)),1))/10^ROW($1:$10))&"00"&”00”的作用有两个,一是防止计算出的0在最后被忽略;二是单元格中仅输入一个或多个0时,最后能提取到一个0
四、忘我(0)牺牲

如:单元格A1:A5中有字符串,也有文本数字。
01
03
0
#VALUE!
大理789
问题:统计A1A5中非0数字(非0文本型数字和数值都算)有几个?
数组公式:
=COUNT(0/A1:A5)
解析:
由于0和文字不能做除数,我们将违背这一原理,把A1:A5作为除数,让0和文字出现错误值。效果:
{0;0;#DIV/0!;#VALUE!;#VALUE!}
按我兄弟顺溜的话来说,让他们(0和文字)都死球。这活下来的“英雄”就是我们要数的“人”(非0数字个数)了。于是我们让SUMSUMPRODUCTISERRISERRORISNUMBER等几位大侠先“下岗”,只聘请数数高手“COUNT”大侠。
=COUNT({0;0;#DIV/0!;#VALUE!;#VALUE!})
=2
转35楼:
http://club.excelhome.net/viewthread.php?tid=614168&page=3#pid4175592

[ 本帖最后由 wshcw 于 2010-8-30 00:31 编辑 ]

评分

3

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-11-23 08:03 , Processed in 0.047440 second(s), 10 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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