今天碰到这么个问题,看起来好像并不是个难题,因为描述很清楚啊!请看截图:
是吧,看起来好像就是处理一些数字,一向比较喜欢吹牛B的老王赶紧趁着机会装X:
不就用SUBSTITUTE函数把0替换成空就好了么?请相信老王的实力!不是我吹牛,就没有我解决不了的问题!!!你要是不信尽管来问,要是解答不出来我认做他哥!
然后我看了下大牛群:
这些都是做EXCEL培训的高级讲师哦,我这小王想到的用SUBSTUITE去除0他们还不会想到?(嗯,这个时候我认作小王),然鹅,这种思路并不能解决问题。
你当我就这样放弃了?放弃了我可就连小王都当不了了,就只能当very little王了。我不甘心,我要集齐7个公式来召唤神龙。
- - - - - 待我召唤神龙- - - - -
天气转凉,神龙来多了可能会下大雨,所以我先召唤6条:
看到这些个公式如果你还有心情继续看下去我真的不拦你,毕竟我解答不出问题我可是认作他哥的。
下面就一一讲解各个方法的具体步骤:
方法1:
{=LEFT(A2,LOOKUP(10,0/MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,ROW(INDIRECT("1:"&LEN(A2)))))*1}
第一步:利用ROW函数和INDIRECT构造出一个数组,内容是1-8这个8个数,和ROW(1:8)一样的效果,只不过因为这里A2内容长度不确定,用LEN来取长度,所以公式稍微较长;
第二步:用MID()函数取A2分别从左往右取1位就相当于把原先的8位数分成了8个数,得到的结果就是这8个数的数组,乘以1是因为取出来是文本所以乘以1转换成数字;
第三步:用0除以这个数组,其中原数组中为0的结果为#DIV/0,而不为0的结果为0;
第四步:用LOOKUP(1,0/vector,result)这个非常经典的匹配方式来查找数组0/()中最后一个0,返回其对应的位置也就是这个构造的result数组,对A2这个返回结果是4,也就是6的位置;
第五步:根据第四步返回的位置,从左向右取对应的位数就是结果,最后乘以1是为了将文本转化为数字。
具体步骤可参见动图:
方法2:
{=LEFT(A2,SUM((RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2))))*1>0)*1))*1}
第一步:和方法一类似,用ROW+INDIRECT+LEN构造一个1-8的数组;
第二步:从右向左分别取1-8位,得到一些奇怪的结果,乘以1的话就成了数字和0组合的数组;
第三步:判断这个数组中各元素是否大于0,乘以1将结果转化为0或者1;
第四步:用SUM函数做个求和,结果就是大于0的个数,这个数其实就是原数据位数减去尾部0的个数,和方法一种LOOKUP得到的结果一样;
第五步:同方法一,用LEFT函数从左向右取第四步得到的位数,同样也要乘以1转化为数字。
具体步骤可参见动图:
方法3:
第一步:用”0.”连接A2强制将A2转换成了文本,看起来像个小数;
第二步:将一步中的文本乘以1得到真实的小数,这时候默认小数后面的0去除了;
第三步:将该小数中的”0.”替换为空,这时候就得到了A2去除尾部0的效果,乘以1就得到数字格式;
具体步骤可参见动图:
方法4:
=("0."&A2)*10^(LEN(("0."&A2)*1)-2)
第一步:同方法3的第一步,用”0.”连接A2得到一个文本形式的小数;
第二步:这个文本形式的小数乘以1得到真实的小数,默认小数尾部没有0,同 方法3的第二步,这里的结果是公式中LEN函数里的内容;
第三步:对第二步的结果用LEN函数取长度,减去2得到的是小数部门的长度;
第四步:第三步得到的结果作为10的指数,乘以第一步的结果就得到了所求结果。
具体步骤可参见动图:
方法5:
=A2/10^LEN(A2)*10^(LEN(A2/10^LEN(A2))-2)
第一步:用LEN函数获取A2的长度,作为10的指数;
第二步:用A2的值除以第一步的结果,得到的就是一个小数,小数部分刚好是需要的结果;
第三步:获取这个结果的长度,减去2是因为算上了“0.”的长度,这个值作为10的指数;
第四步:用第二步的结果乘以第三步的结果就得到了需要的结果
具体步骤可参见动图:
方法6:
=ZERO(A2)
第一步:咦?这个函数怎么这么短?有那么多短的公式你怎么讲了上面一堆复杂的;
第二步:嗯?我的电脑上怎么没有这个函数?
第三步:当然没有啊,这个是老王做的自定义函数;
第四步:为啥要自定义,碰到不好解决的问题你都可以尝试下VBA啊,而且如果你要是经常需要处理这个问题,自定义函数可是方便很多哦:
第五步:代码是啥?自己看
Function ZERO(TARGET As Range)
M = 1
Do
TEMP = TARGET Mod M
M = M * 10
Loop Until TEMP <> 0
ZERO = TARGET / M * 100
End Function
就不解释了,很简单的一段代码。
是不是心里有疑惑,这么多的方法真的是老王一个人做的么?蛤蛤蛤,不告诉你!!!
还有这么多方法其实思路是差不多的,你GET到了么?欢迎交流!
联系客服