![e9b0c5e8bfee9ce7ca49a3a9e201f534.gif](https://img-blog.csdnimg.cn/img_convert/e9b0c5e8bfee9ce7ca49a3a9e201f534.gif)
今天给大家分享10个实用的EXCEL技巧。跟那些烂大街的什么提取生日不一样,可能会比较复杂,所以我会附上详细的函数解释、说明和思路。使用环境以EXCEL2007默认安装为准。强烈建议大家抛弃2003,因为新格式比旧格式优秀太多,这个放到最后讲。
统计不重复项数
![d1437292ad31352b59f2fdc8ea5d6140.gif](https://img-blog.csdnimg.cn/img_convert/d1437292ad31352b59f2fdc8ea5d6140.gif)
以前在开发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,最后相加。而如果参数只有一个,那就没得乘,直接变成简单的数组内元素相加,我们利用的就是这一点。
![e005ee303bd536d4682e4ff4c64da14d.png](https://img-blog.csdnimg.cn/img_convert/e005ee303bd536d4682e4ff4c64da14d.png)
接下来再来看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的个数有几个呢?四个。也即范围内不重复的项数。
NUMBERSTRING函数
快捷生成大写数字有时候需要生成大写数字,如果自己一个一个敲还是很烦的,其实EXCEL有这么个函数:
![dbc7c054de3cc31cb042c51e065cbbdb.png](https://img-blog.csdnimg.cn/img_convert/dbc7c054de3cc31cb042c51e065cbbdb.png)
NUMBERSTRING这个函数简直是本地化的典范,中文专用,第2个参数可以取1、2、3,效果直接在图上演示了,就不凑字数了。不过这个函数也有缺点:不支持小数。
![423b179061f1b71aa0d44c6df5882a0d.png](https://img-blog.csdnimg.cn/img_convert/423b179061f1b71aa0d44c6df5882a0d.png)
如果有小数的话,函数会自动四舍五入取整,注意,会四舍五入。一般情况下,我们的小数只有两位,可以用上图方式分别取出来,然后转成大写的伍和陆,后面自己手动接X角X分。或者直接把小数部分弄成整数,然后中间自己加“点”,变成一二