“0”的函数公式与格式.doc

“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）例子：将A列A1:A10的数字相加，其中可能还有文本型的数字也需要相加。公式：=SUMPRODUCT（A1:A10+0）或者：=SUMPRODUCT（A1:A10-0）解析：初级用户会觉得+0,-0不就等于没有增加，没有减少嘛，为何要这样呢?是啊，要的就是这个效果，既要改变原数据的性质（文本转变为数值），又要准确计算，所以只有用+0,-0，这一“+”或“-”符号就是改变原数据的性质的。这一带符号的0犹如一个“小石头”，从后面抛出去将昏睡中的“大石头”（数字）砸醒。参考文章:文本转数值的十一种方法（百度一下可查询到）（二）“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&519),ROW($1:$10),1)/10ROW($1:$10)&00,3,COUNT(FIND(ROW($1:$10)-1,A1)或者：=MID(SUM(MID(A1&567,SMALL(FIND(ROW($1:$10)-1,A1&567),ROW($1:$10),1)/10ROW($1:$10)&0,3,COUNT(FIND(ROW($1:$10)-1,A1)解析：”(0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&519),ROW($1:$10),1)”中，前面补0，是为了填补空值，这里不再赘述，式子：SUM(0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&519),ROW($1:$10),1)/10ROW($1:$10)&00中&”00”的作用有两个，一是防止计算出的0在最后被忽略；二是单元格中仅输入一个或多个0时，最后能提取到一个0。四、忘我(“0”)牺牲如：单元格A1:A5中有字符串，也有文本数字。01030#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中输入字符串”ABCwshcw中国云南大理abcOWYExcelhome”如果难提取汉字：”中国云南大理”公式：=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主人123ABCR（空白）1BMMM主人-1mc76yk 中国问题：要将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的1、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)*10COLUMN(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)*10COLUMN(1:1)/10进一步提取得到：=SUM(1,0,3,00*10COLUMN(1:1)/10再运算：=SUM(10,0,3000,0,0)/10=301例A2:A7单元格输入:欢迎光临我的百度空间我的函数主题与大家分享1235云南【】丂丮云南大理镕DAW121234ABYT【】龥县丮云南大理丂%问题：要求取出单元格内的汉字字符串：=MID(A2,MATCH(TRUE,MID(A2&咗,ROW($1:$50),1)=吖,),SUM(N(MID(A2,ROW($1:$50),1)=吖)变通为：=MID(A2,MATCH(10,MID(A2&咗,ROW($1:$50),1)=吖,),SUM(N(MID(A2,ROW($1:$50),1)=吖)例分数评级假定考分=85的为”A”,=70的为”B”, =60的为”C” 其余的为”D”则公式为（当然有好多公式可写，但这是本文需要这样写）:=CHAR(A1=100)+(A185)+(A170)+(A160)+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输入：名称AABCBCADB要列所有的字母“A”，公式为：=INDEX(A:A,SMALL(IF($A$2:$A$10=A,ROW($2:$10),48),ROW(A1)查询区域中只有3个A，当往下填充时显示为0，因65536行为空，所以返回数值0，当公式末尾加上” &”后，返回文本”，所以0消失。完美公式如下：=INDEX(A:A,SMALL(IF($A$2:$A$10=A,ROW($2:$10),48),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输入：数字串59367001269012789.36480.36998（空白）12789.3648（空白）问题：要求将各单元格的数字反转。公式：=RIGHT(REPLACE(SUM(0&MID(SUBSTITUTE(A2,.,)&1,ROW($1:$15),1)*10ROW($2:$16)%,LEN(A2)-FIND(.,A2&.)+2,.),LEN(A2)解析：公式中的“&1”，就是怕原数字串的最后一个0丢失，因反转后，最后的0变成了最前面的，因数值前面的0无效而丢失，“&1”中的1就象护栏一样（根据问题的具体情况，有时用“1&”），防止边缘上的0滚蛋，滚蛋了不就成了“卖鸡蛋的跌倒没有一个好的”，这“云南十八怪中的鸡蛋用草串着卖”也就怕滚蛋了。十一、眼见为虚，验证为实（单元格格式简单运用）单元格格式的设置顺序excel默认:正数;负数;零;文本，中间用英文分号相隔。并且还可以设置颜色（颜色是TEXT函数不具备的），如单元格格式：红色我;绿色爱;黄色excel;蓝色home设置后分别输入正数;负数;零;文本试试，是不是很有趣哦！输入正数时显示红色的我;负数时显示绿色的爱;零时显示黄色的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的为优秀，则单元格格式为