大家好我是波导终结者,这次跟大家分享一下10个实用的EXCEL技巧。跟那些烂大街的什么提取生日不一样,可能会比较复杂,所以我会附上详细的函数解释、说明和思路。
使用环境以EXCEL2007默认安装为准。强烈建议大家抛弃2003,因为新格式比旧格式优秀太多,这个放到最后讲。
1.统计不重复项数
以前在开发ERP的时候,曾经有一个需求,就是从庞大的数据中统计出SKU。当时研究了半天,最后用Hashtable然后取其个数实现了,这个属于编程范畴,就有点扯远了。
那如果我们在EXCEL中需要这么做,用什么函数可以做到呢?毕竟工具所限,不太可能用哈希表。
方法很简单:
=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))
这个方法用到了两个函数,一个是大家很熟悉的COUNTIF,另一个是没怎么见过的SUMPRODUCT。
先来讲SUMPRODUCT,这个函数拆开来看就是SUM和PRODUCT,即“把乘积求和”。
它接受的参数,是N个数组(重要),每个参数数组的大小必须是一样的,然后这个函数就会把对应的项先相乘,最后相加。
比如SUMPRODUCT(A1:A5,B1:B5),那么就会计算A1*B1,然后是A2*B2……一直到A5*B5,最后相加。
而如果参数只有一个,那就没得乘,直接变成简单的数组内元素相加,我们利用的就是这一点。
接下来再来看COUNTIF。COUNTIF一般的应用我们见过挺多,但是COUNTIF(B2:B15,B2:B15)这是个什么操作,条件竟然是个区域,而且与值域一样?
对于这样的写法,COUNTIF会返回一个数组,里面存储着B2在B2:B15中的个数,B3在B2:B15中的个数……类推。
这样一来,这个值在范围内出现过N次,它在数组里也就会返回N次值,值还是为N。比如B2的“波导一”,它出现过3次,并且也被数到3次。
而1/COUNTIF(B2:B15,B2:B15)则会将1除以这个数组内的每个N,作为一个新的数组返回。这样,“波导一”出现3次,在数组里就会有3个1/3,“波导三”出现2次,就会有2个1/2……
大家发现了吧,N个的1/N相加,结果肯定是1。然后1的个数有几个呢?四个。也即范围内不重复的项数。
2.快捷生成大写数字
有时候需要生成大写数字,如果自己一个一个敲还是很烦的,其实EXCEL有这么个函数:
NUMBERSTRING这个函数简直是本地化的典范,中文专用,第2个参数可以取1、2、3,效果直接在图上演示了,就不凑字数了。
不过这个函数也有缺点:不支持小数。
如果有小数的话,函数会自动四舍五入取整,注意,会四舍五入。
一般情况下,我们的小数只有两位,可以用上图方式分别取出来,然后转成大写的伍和陆,后面自己手动接X角X分。
或者直接把小数部分弄成整数,然后中间自己加“点”,变成一二三四点五六。
具体方法还有很多,看实际需求再具体改函数。
写这点也是有感而发。一个是之前初入职场的时候手动写过这种函数,现在回头来看蠢死了。
另一个就是提醒大家四舍五入一定要注意。以前我开发ERP的时候,就和公司里的财务扯过蛋。
之前公司里的折扣都是2位数,后来扩展到3位数了,这时候问题出现:你要全程保持可见数值的精度,就得全程保留3位小数,这很好理解吧。
举个最简单的例子:0.99