0活多变的公式与格式.docx
《0活多变的公式与格式.docx》由会员分享，可在线阅读，更多相关《0活多变的公式与格式.docx（8页珍藏版）》请在冰豆网上搜索。
0活多变的公式与格式
“0”活多变的函数公式与格式
一、活“0”活现
（一）简单文本求和中0的作用（+0或-0）
例子：
将A列A1:
A10的数字相加，其中可能还有文本型的数字也需要相加。
公式：
=SUMPRODUCT（A1:
A10+0）
或者：
=SUMPRODUCT（A1:
A10-0）
解析：
初级用户会觉得+0,-0不就等于没有增加，没有减少嘛，为何要这样呢?
是啊，要的就是这个效果，既要改变原数据的性质（文本转变为数值），又要准确计算，所以只有用+0,-0，这一“+”或“-”符号就是改变原数据的性质的。
这一带符号的0犹如一个“小石头”，从后面抛出去将昏睡中的“大石头”（数字）砸醒。
参考文章:
文本转数值的十一种方法（XX一下可查询到）
（二）“0”嶺先锋
例①、如：
单元格A1中输入数字12304568579213（15位以下，文本或数值型均可）要将这个数的每一位相加，公式：
=SUM（--（0&MID（A1,COLUMN（1:
1）,1）））
解析:
因单元格字符串长度只有14位提取长度为1至256位的长度，所以从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”
单元格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
问题：
统计A1：
A5中非0数字（非0文本型数字和数值都算）有几个？
数组公式：
=COUNT（0/A1:
A5）
解析：
由于0和文字不能做除数，我们将违背这一原理，把A1:
A5作为除数，让0和文字出现错误值。
效果：
{0;0;#DIV/0!
;#VALUE!
;#VALUE!
}
按我兄弟顺溜的话来说，让他们（0和文字）都死球。
这活下来的“英雄”就是我们要数的“人”（非0数字个数）了。
于是我们让SUM，SUMPRODUCT，ISERR，ISERROR，ISNUMBER等几位大侠先“下岗”，只聘请数数高手“COUNT”大侠。
=COUNT（{0;0;#DIV/0!
;#VALUE!
;#VALUE!
}）
=2
五、隐居山“0”
如单元格A2中输入字符串”☯ABC❀wshcw中国云南大理abc♦OWY♥Excelhome☀”
问题:
如何提取汉字：
"中国云南大理"
公式：
=MID（LEFT（A2,MATCH（,0/（MID（A2,COLUMN（2:
2）,1）>="吖"）））,MATCH（,0/（MID（A2,COLUMN（2:
2）,1）>="吖"）,）,99）
没有用简写的原公式：
=MID（LEFT（A2,MATCH（0,0/（MID（A2,COLUMN（2:
2）,1）>="吖"）））,MATCH（0,0/（MID（A2,COLUMN（2:
2）,1）>="吖"）,0）,99）
解析：
1、公式中“0/（MID（A2,COLUMN（2:
2）,1）>="吖"）”由于汉字最小是"吖"，只要大小等于"吖"，就说明它是汉字，这部分的作用是将小于“吖”的字符经判断后作为分母（分母为0），继而出错（也就排除了小于“吖”的部分，换句话说，也就是牺牲非0的字符），由于分子为0，继而赢得汉字演变为0的胜利。
2、MATCH（,0/（MID（A2,COLUMN（2:
2）,1）>="吖"））这部分是定位最后一个汉字的位置，值得注意的是：
前一个英文“,”前省略了一个0，作用是定位最后一个0的位置。
3、MATCH（,0/（MID（A2,COLUMN（2:
2）,1）>="吖"）,）这部分是定位最前一个汉字的位置，值得注意的是：
最后一个反括号前“）”前省略了一个0，作用是定位最前一个0的位置。
MATCH（,0/（MID（A2,COLUMN（2:
2）,1）>="吖"））与MATCH（,0/（MID（A2,COLUMN（2:
2）,1）>="吖"）,）看上去只有一逗（“,”）之差,但“差以毫厘，谬以千里”。
前者：
目标远大，把潜力发挥到极限，后者因被眼前的”,”号所诱惑，目标只定位在眼前，目光短浅。
这函数也象人生一样，只有小智慧与大智慧的结合，才能使函数家族兴旺发达。
六、居高“0”上（0次方的用法）
例A2:
A7输入：
字符串
bbbccew-58人ＬＫ民
ＡＹＵBMMM主人123９６５
ABCR
（空白）
1BMMM主人-1
mc76yk中国
问题：
要将A2:
A7的单元格数据汇总求和。
公式：
=SUM（-TEXT（MID（A2:
A7&"@",COLUMN（1:
1）,MMULT（1-ISERR（-MID（A2:
A7&"a1",COLUMN（1:
1）,2））,ROW（1:
256）^0））,"-0%;0%;0;!
0"））
解析：
1、先算出每个单元格含有的数字个数，再按这个个数分别逐个提取。
2、公式中：
MMULT（1-ISERR（-MID（A2:
A7&"a1",COLUMN（1:
1）,2））,ROW（1:
256）^0）就是算出每个单元格含有的数字个数，那么”^0”为何爬得如此高呢？
这是因为ROW（1:
256）^0）是常量数组{1;……;1;1}的缩写。
是序列数1到256的0次幂，也就是256个1的数组。
这0次方的妙用是E友对EXCEL不断开拓创新的结果。
七、“0”的伪装（TRUE，FALSE）
例①A1单元格是785，B1单元格是358017。
如何从B1中将A1的7、8、5替换掉，在C1得出301
=SUM（MID（0&B1,LARGE（ISERR（FIND（MID（B1,COLUMN（1:
1）,1）,A1））*COLUMN（1:
1）,COLUMN（1:
1））+1,1）*10^COLUMN（1:
1））/10
公式分析：
1、FIND（MID（B1,COLUMN（1:
1）,1）,A1）
=FIND（MID（B1,{1,2,3,4,5,6,7,……,256},1）,A1）
=FIND（{"3","5","8","0","1","7","",……,""},785）
={#VALUE!
3,2,#VALUE!
#VALUE!
1,……,1}
用MID分解字符串，得到一个数组，大家已经很熟悉了，由B1单元格数字得到一个数组：
{"3","5","8","0","1","7","",……,""}。
然后用FIND查找数组中每个数据在A1单元格数字中的位置，先查找"3"，A1中没有3，那么结果是错误值#VALUE!
，接下来找"5"，在A1单元格数字的第3个位置，结果便是3，再找"8"，结果是2，依次找下去，当查找空值""时，结果都是1，这可以理解为用FIND找空值，空值永远在字符串第1个位置。
2、ISERR（FIND（MID（B1,COLUMN（1:
1）,1）,A1））
=ISERR（{#VALUE!
3,2,#VALUE!
#VALUE!
1,……,1}）
={TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,……,FALSE}
我们又遇到一个信息函数ISERR，它是检测一个数据是否为错误值（#N/A以外），如果是错误值返回TRUE，不是错误值返回FALSE，形象地理解为：
错的就是对的，对的成了错的，真是“真亦假来假亦真，假亦真来真亦假”。
3、ARGE（ISERR（FIND（MID（B1,COLUMN（1:
1）,1）,A1））*COLUMN（1:
1）,COLUMN（1:
1））+1
这一步是算出查找不到的第1至256个最大值。
运行后的效果：
{8,7,4,3,1,……,1}
代入公式：
=SUM（MID（0&B1,{8,7,4,3,1,……,1},1）*10^COLUMN（1:
1））/10
进一步提取得到：
=SUM（{"1","0","3","0"……"0"}*10^COLUMN（1:
1））/10
再运算：
=SUM（{10,0,3000,0,……,0}）/10
=301
例②A2:
A7单元格输入:
★★★★★★★★★欢迎光临我的XX空间★★★★★★★★★★
∽∽∽∽∽∽∽∽∽∽我的函数主题与大家分享∽∽∽∽∽∽∽∽∽∽
1235云南
【】☆‰℃丂丮云南大理镕DAW12
123ＯＰ4ABYTＱＲＴＯＮٷ
【】龥县丮云南大理丂☆‰℃%
问题：
要求取出单元格内的汉字字符串：
=MID（A2,MATCH（TRUE,MID（A2&"咗",ROW（$1:
$50）,1）>="吖",）,SUM（N（MID（A2,ROW（$1:
$50）,1）>="吖"）））
变通为：
=MID（A2,MATCH（1>0,MID（A2&"咗",ROW（$1:
$50）,1）>="吖",）,SUM（N（MID（A2,ROW（$1:
$50）,1）>="吖"）））
例③分数评级
假定考分>=85的为”A”,>=70的为”B”,>=60的为”C”其余的为”D”
则公式为（当然有好多公式可写，但这是本文需要这样写）:
=CHAR（（A1<=100）+（A1<85）+（A1<70）+（A1<60）+64）
解析:
假定A1中输入成绩80,则公式在运算中演变成:
=CHAR（TRUE+TRUE+FALSE+FALSE+64）,试中TRUE参与计算则为1，FALSE参与运算则0，由于我们知道大写字母从”A”开始，它的字符集数字代码是从65开始的。
因此当满足一个条件时是1，再加64刚好就是65，然后用CHAR函数返回字母。
八、瞒天过海（ISERR与ISERROR）
IS类函数的运用，诸如：
=ISERROR（#N/A）
=ISERROR（DATE（2006,1,9））
=ISERR（-"Good"）
例如：
将单元格A1:
A10求和:
150
#N/A
#VALUE!
#REF!
#DIV/0!
#NUM!
#NAME?
#NULL!
9
北京
对于不能求和的项目，系统显示#N/A，但这样说给上司算不出来，未免显得太菜了。
用什么方法，可以算出正确值呢？
对了，先来一招投石问路，对各单元格返回的值做一个判断，看看系统到底能不能作出正确的判断。
再来一招左右逢源（IF），对于满足的就显示原值，不满足（出错的）的，就干脆让它为0,（当然，这个0也能省略），岂不妙哉？
因此，常规的求和是绝对不能解决问题的，单元格区域中本身就是EXCEL认为的错误字符，所以可以结合IF和IS函数来使用。
大家可能已学习过，对于投石问路（IS类函数），共有九种变化，其中第三式（ISERROR）或第二式（ISERR）是比较常用的，可以使用。
因此，组合后的公式就变成：
公式:
=SUM（IF（ISERROR（A1:
A10+0）,,A1:
A10+0））
以上数字如为数值型,则可简化为:
=SUM（IF（ISERROR（A1:
A10）,,A1:
A10））
或者（干脆避开IS类函数）：
=SUMIF（A:
A,”<9E307”）
（注公式中的”,,”是将两个英文逗号间的0省略了，不省略就写为“,0,”）
ISERROR如果为真,说明真的出错,返回0,如果为假（没有出错）,返回原值.这里又是0和1的变戏法。
九、“0”的消失（遇到&""时）
0作为分母可以让0消失，TEXT中的条件参数也可以让0真正消失，T函数可以让数值型数字真正消失。
N函数则可以让字符变身为0。
空值（真空）当与数值大小比较时显示值为0，与字符比较时为空（””）例如：
单元格A2：
A10输入：
名称
A
A
B
C
B
C
A
D
B
要列所有的字母“A”，公式为：
=INDEX（A:
A,SMALL（IF（$A$2:
$A$10="A",ROW（$2:
$10）,4^8）,ROW（A1）））
查询区域中只有3个A，当往下填充时显示为0，因65536行为空，所以返回数值0，当公式末尾加上”&""”后，返回文本””，所以0消失。
完美公式如下：
=INDEX（A:
A,SMALL（IF（$A$2:
$A$10="A",ROW（$2:
$10）,4^8）,ROW（A1）））&""
十、“0”蛋的安全
例①单元格A1输入:
大理aw白族ws自00124.36hcw治州
问题：
想在B1单元格中提取出数字00124.36
公式：
=MID（LOOKUP（1,-（1&MID（A1,MIN（FIND（ROW（$1:
$10）-1,A1&1/17））,ROW（$1:
$15））））,3,15）
解析：
常规的公式如：
=-LOOKUP（1,-MID（A1,MIN（FIND（ROW（$1:
$10）-1,A1&1/17））,ROW（$1:
$15））），会使有效数字前面的0丢失，我们不得不聘请“1”来做“安全卫士”，防止“0”逃走，用“1&”之后，将它们一并提出来，由于数字是负值，后面又有1，所以从第3个字符提取到15位。
例②单元格A2:
A8输入：
数字串
593670
012690
12789.3648
0.36998
（空白）
12789.3648
（空白）
问题：
要求将各单元格的数字反转。
公式：
=RIGHT（REPLACE（SUM（（0&MID（SUBSTITUTE（A2,".",）&1,ROW（$1:
$15）,1））*10^ROW（$2:
$16））%,LEN（A2）-FIND（".",A2&"."）+2,,"."）,LEN（A2））
解析：
公式中的“&1”，就是怕原数字串的最后一个0丢失，因反转后，最后的0变成了最前面的，因数值前面的0无效而丢失，“&1”中的1就象护栏一样（根据问题的具体情况，有时用“1&”），防止边缘上的0滚蛋，滚蛋了不就成了“卖鸡蛋的跌倒—没有一个好的”，这“云南十八怪中的鸡蛋用草串着卖”也就怕滚蛋了。
十一、眼见为虚，验证为实
单元格格式的设置顺序excel默认:
"正数;负数;零;文本"，中间用英文分号相隔。
并且还可以设置颜色（颜色是TEXT函数不具备的），如单元格格式：
"[红色]我;[绿色]爱;[黄色]\exc\el;[蓝色]\ho\m\e"设置后分别输入"正数;负数;零;文本"试试，是不是很有趣哦！
输入正数时显示红色的"我";负数时显示绿色的"爱";零时显示黄色的"excel";文本时显示蓝色的"home"，显示结果不是真实存在的，是你的眼睛在欺骗你，它并没有改变其本身的值或字符，所以眼见为虚，验证为实。
是每个excel人所必须弄清的。
1、单元格格式中的"0"，往往是通配数字。
如：
输入19980823，要显示为日期格式，则单元格格式：
"0-00-00"（即月份和日期都是两位数，剩下的位数为年份），单元格格式也可以写成：
"0年00月00日"
2、单元格格式中的隐藏大法
①隐藏0值，单元格格式：
"[=]g"
②隐藏负值：
"G/通用格式;"
③隐藏小于0的值：
"G/通用格式;;"
④隐藏正值：
";-G/通用格式;0"
⑤隐藏0值和正值：
";-G/通用格式"
⑥隐藏数值：
";;;@"（仅显示文本）
另外，经测试，隐藏数值的单元格格式还可以写成：
""""（只输入""）,但缺陷是当输入负值时会显示"-"号没有完全隐藏负值。
⑦隐藏文本：
"[<>]G/通用格式;;0;"，也可以:
"G/通用格式;-G/通用格式;0;"
⑧全部隐藏：
";;;"
3、改变默认设置法：
例如：
学生成绩<60的为不及格，<85的为及格，>=85的为优秀，则单元格格式为：
"[<60]不及格;[<85]及格;优秀"，单元格格式还可以再加上颜色：
"[红色][<60]不及格;[黄色][<85]及格;[蓝色]优秀"，还可以按照条件只设置颜色,不显示等级，单元格格式：
"[红色][<60];[黄色][<85];[蓝色]"，如果中间再加一个等级，>=70且<85的为良，单元格格式就无能为力了，因为单元格格式最多能将数值分为三段，第四段是文本格式.
4、占位符（"\"和"!
"）
是指EXCEL规定有特殊含义或者说有特殊用途的字符而言的，当在单元格格式中（或者说在TEXT条件参数中）输入这些字符时，EXCEL赋予“她”特殊使命，当不需要“她”完成特殊使命，只作“平民”身份出现时，就用占位符（”\”和”!
”）命令“她”，使“她”显示出本来面目。
具有特殊使命的字符有：
A星期
B佛历年份
D日期
E科学计数,小"e"是年份（使用时要注意区分）
H小时
M月份和分钟
S秒
Y年份
@文本
#数字
0数字
如：
A1:
A3单元格中都输入40000（日期：
2009-7-6的序列数），单元格格式分别设置为：
e,m,d，是不是分别显示为：
2009（年），7（月），6（日）了，如果要将“她”还原为“平民”身份，就分别用\e,\m,\d（用!
e,!
m,!
d也是一样的），结果就显示字母本身了。
如上述，学生成绩<60的为不及格，<85的为及格，>=85的为优秀，单元格格式：
”[<60]不及格;[<85]及格;优秀”，就是人为的定义格式，当然还可以”[>=85]优秀;[<60]不及格;及格”和”[>=85]优秀;[>=60]及格;不及格”这些格式的顺序由用户自己定义，改变EXCEL的默认设置，也就是我们EXCEL人自己赋予“她”的用途。
由于这些字符EXCEL没有特殊含义或者说没有特殊用途，所以不需要用占位符。
EXCEL博大精深，仅就一个0，也只写了冰山一角，如果没有养成独立思考习惯，平时又不知道积累知识点，只会发一堆废铁（贴），缺乏探索创新，是永远成不了好钢的。
在困难面前誓不低头，世上才有了徒手攀岩的“蜘蛛人”，作为合格的EXCEL人应该具备“蜘蛛人”应有的品质，面对技术难题，从0开始，不畏艰险，敢于“亮剑”，勇攀“E”峰。
只要坚持，坚持，再坚持，才能享受到“举头红日白云低”的成功喜悦。
升级会员 