实用到爆的10个EXCEL技巧

大家好我是波导终结者，这次跟大家分享一下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.995+0.005=1.000，如果只保留两位小数会出现什么问题呢？1.00+0.10=1.00或者1.10。为什么会或者？一个是后台相加的实际值，一个是前台已经四舍五入过一次之后的值相加。如果前面已经四舍五入过了，精度损失，这两个数不可能兼得呀。

而当时的财务却要求：不将2位精度改为3位精度，同时结果既满足后台实际值，又满足前台可见值，而且还只能有一个结果。这明显就不可能。说白了那个财务懒得一逼，啥也不想做罢了；而且也蠢得一逼，连EXCEL函数都不懂得改。

3.查找某行或者某列的特定值

VLOOKUP这个函数，很多人都有听过，但经常有人用不明白。

这函数说白了，从某个区域内找到某个数，但是使用上却有以下几个要点：

1.VLOOKUP是竖着从参数2的范围内，找第一列值，如果想横着找，请用HLOOKUP。

2.参数3返回对应的，另一列的值。这个数字是范围内的第几列，而不是整个表格的第几列。当然你可以试试设为1……另外，这个数必须为正数，不能反着找。你可以把目标列复制一列，放后头隐藏起来。

3.参数4设为FALSE为精确匹配，TRUE为近似匹配。然而，近似匹配却有两个弱点，如上图▲

近似匹配时，第一列必须为升序排列，否则报错。数值的话好理解，字符串就会有些头痛。

另外，近似匹配很容易得到无法预料的效果。不管是字符串还是数字，它取的都是“相近”的值，而这个相近很容易得到你不想要的结果。所以一概建议大家使用精确匹配。

另外要注意，字符串前后有空格，或者查找数字但目标区域是字符串格式都会导致得到错误结果，一定要检查仔细。

4.VLOOKUP的高级应用

首先我们来看多重查找。比如现在东哥想找出所有不能拼命的员工，列成一个表，或者丁哥想把所有患重病的员工找出来，列成一个表。

这个需求我们当然可以直接用现成的筛选或者过滤来做，但是这样有时候会破坏原表格。而且有的领导不太会用EXCEL，到时候乱搞一通，显示结果乱了，咱又得背锅。

这里我们用添加辅助列的方式来做。辅助列也是学好EXCEL必备的方法，有点类似数学题里的辅助线。有的题不加，还能做，有的题不加还真的做不了。

A列和H列分别为公式文本。

首先看一下这个：(D2=$F$2)+B1。利用到了EXCEL里，TRUE为1，FALSE为0的特性。如果是男员工，则数字加1，如果不是，就一直保持之前的数字。而绝对引用和相对引用这些我真的不想再说一遍了。

这样，我们在B列就生成了一个数组，每个目标行的数值都会比之前的大1。

再来看：IFERROR(VLOOKUP(ROW(B1),B1:D$6,2,0),"无")。IFERROR只是为了防止、过滤报错结果，你可以填成空字符串，这样结果就直接可拷走。

ROW(B1)返回1，ROW(B2)返回2，往下拉类推。而查找1，就是找到第一个目标员工。

下拉之后，ROW(B2)返回2，B1:D$6变为B2:D$6，即从剩下的单元格中，查找第2个目标员工。以此类推。

第二个，通配符查找。刚才我提到过了，用近似匹配很难得到你想要的值，但是你想要模糊查找怎么办呢？

很简单，VLOOKUP支持通配符，比如我在后面加个问号，查找的就是“波导1”后面再跟一个字符的数值。问号代表一个，星号代表任意，这些DOS时代过来的了，不再多讲。

最后再来分享一下反向查找。刚才提过，VLOOKUP不支持反向查找，前提是不用其他函数做辅助处理。

这里我们用：VLOOKUP(C12,CHOOSE({2,1},C2:C6,D2:D6),2,FALSE)。核心要点在CHOOSE函数，说白了就是把第二列先返回，再返回第一列，则生成一个临时表，性别列排在名字列前面。

然后我们就找出第一个女员工了。

个人不推荐这么做，很容易乱，后面如果改个东西，函数就很麻烦，还是辅助列好用。

VLOOKUP可以嵌套非常多函数，根据使用场景来实际操作比较直观，有需要的可以关注点赞，留个言。

5.数据透视表

首先我们来看一下这张表。只是演示效果，所以就随便打了一些数据。

之前开发ERP的时候，对于报表就有一个非常强烈的需求：数据透视表。源头就是EXCEL的这个功能。

当然有人可能会问，为什么不让他们自己拉EXCEL呢？呃，是这样的，当时数据轻轻松上亿条，EXCEL怕是……

回到正题，我们选定一个范围的数据之后，点击插入，数据透视表，确定。

简单的拖拉，我们就能得到这么一张汇总表：所有男鞋、女鞋、配件分别求和。

再简单的拖拉，又能生成另一张表：按年汇总，品名列成小项，可折叠。如果把品名和年份位置对调，就是品名汇总，年份折叠。

这玩艺儿用来应付那些一会儿要看这个表，一会儿要看那个表的领导非常好用。我总不可能天天蹲着给你做表格吧？给你一个数据透视，自己玩去。

如果想要开发控件，我当年用的是DevExpress，非常强大，别无二选。就是有点贵，不过方法总是有的你懂的~

6.几种排名方法

这个之前有人问过，今天把几种情况一起写了。

首先是顺位排名，也就是不管前面有没有并列，真实反应该人的名次。

这个很好解决，EXCEL自带RANK函数。但如果我们要让并列的人不占用名次，或者说不管并列多少名，不让排名数字有空档呢？比如100个人里，99个都考了100分，则考了98分的人，是第100名，还是第2名？

这里我要事先说一下，此处的前提是不对数据进行排序，我们要在不动到之前数据的前提下来做。不要问我为什么，一问就说明你还没经历职场……如果能排序，那也没啥好讲的了~

函数不难：=SUMPRODUCT(($B$2:$B$7>B7)/COUNTIF($B$2:$B$7,$B$2:$B$7))+1。思路跟第1节的去重是一样的，不再重复解释。

那如果有多个数值，在并列的时候需要做第二次排序呢？

函数如下：RANK(B2,$B$2:$B$7)+SUMPRODUCT(($B$2:$B$7=B2)*($C$2:$C$7>C2))。

思路也很简单，先取得真实名次，然后数出与其分数并列、第二排序列大于它的单元格个数，也就是这一格需要往后退（名次数值加上）的数值了。

还是这个函数。记住SUMPRODUCT这个函数哦。

7.制作下拉菜单

有的时候，一些场合我们并不需要让用户自由输入，而是希望有个下拉菜单，提供现成的选项直接选项，这样既快捷，又避免输入错误，不使用VBA控件可以实现吗？

可以的，这个功能在EXCEL2007里叫“数据有效性”，2010之后的版本叫“数据验证”。在数据有效性功能内，选择“序列”，并且指定之前输好的固定值，就可以了。

很典型的一个应用，省份选择，我们在寄、收快递的时候，都会让你选一个菜单。

那么，如果要做多级菜单呢？比如省、市、区这样的？当然也可以。

不过要先说一句，EXCEL2007做这个比较麻烦，我只是告诉大家旧版本的实现方法。有用新版本的朋友肯定是更方便了。

一级菜单的做法不变，第二级菜单的话，我们需要先把它的下级预填出来，比如“北京”下面有某几个区。

然后，以“北京”为头选中这个区域，公式，定义名称。在这里，2007版只能一个一个来，并且不能自动排除表头，所以我们得一个个手动。如果更高版本的朋友就有福了，EXCEL提供更多选项，可以直接指定表头，把整个区域一次性做进去。

8.用录制宏完成高级功能

上一节提到2007里，公式需要一个个手动点，而更高版本可以整个区域生成。那么我们有没有办法在旧版本里批量做呢？有的。

请先记住一句话：所有功能，其本质都是宏（VBA代码），我们可以录制、编辑，实现自己的高级功能。

首先，我们需要调出“开发工具”选项卡。因为一般人用不到，出于安全考虑，默认是不显示的。

然后，我们把刚才的单次操作录制成一个宏。录制方法也很简单，先切换到开发工具，点击录制宏。

然后你就正常操作。操作完了之后，点击止录制。

这时候切换到VBA界面，我们便可以看到刚才的代码，竟然只有两行，比鼠标点击的次数还要少。

但是这时候我们并没有办法直接用，因为我们要做批量。比如，它这里的“北京”是写死的，我们必须让代码自动取值，等等。

把代码稍加改造，这里我范例只有三个，列从8到10，所以循环的下标就从8到10。取得表头的名称之后，指定表身的部分即可。

最后我们点击这个“播放”键，运行这个改造过后的宏。

执行完之后，我们来看一下名称管理器，确认一下有没有哪里写错。

用代码的好处是什么呢？可以把很多批量的操作简化掉。比如我们在二级菜单的基础上，要做三级菜单。就算是新版本，你也得一个一个区域框选，因为一个省有N个市，一个市又有M个县，这样就需要N*M次操作。而通过代码，把数据布好局之后，只需要点一下，不管来多少数据，我们都不需要一次一次手动操作了。

9.制作自定义函数

既然用到了一点VBA，那么最强大的是什么呢？当然是自定义函数了。

自定义函数你就可以脱离EXCEL内置函数的限制，几乎想做什么就能做什么。具体要怎么干，就看每个人需求了。

这里简单跟大家分享一下。首先，函数一定要写在模块里面，函数一定要写在模块里面，函数一定要写在模块里面。

第二，函数前面加Public以供外部调用。

第三，VBA用bdzjz_1 = s这样的方式来返回值（其他语言比较常见的是return xxx）

第四，VBA的语法是弱属性，变量可以不声明类型。

函数写好之后我们可以来测试一下。在表格中键入等号，后面跟自定义函数名，如果成功的话可以看到完整函数名的提示。

这里只是简单的将参数1和参数2中间连接起“住在”，最主要的还是知道自定义函数的方法。因为到了需要自定义函数阶段的时候，都是需求各异。

10.为什么叫你们抛弃2003格式

之所以聊到这个，主要是前段时间某群里有某人是这么说的：2003和2007的格式其实就是改个后辍骗人而已，内容是完全一样的，我的2003改个后辍就能打开2007的文件。

这句话犯了几个很严重的错误：

1.Office 2007最大的进步就是格式上的进步。旧的2003格式太易损坏，一旦出问题修复率几乎为零。

2.2003能打开2007的文档，只是因为现在很多集成安装包带了兼容插件。就算如此，也只是能兼容常用内容，一些新特性根本用不了，也保存不了。

3.微软没傻到单纯依靠后辍名来判断文件类型，事实上，大部分软件都没这么傻~

不信我们来看看xls文件和xlsx文件的文件头，虽然看不懂，但是“明显不一样”这一点是可以确定的。

而且在xlsx的文件头，我们可以看到xml这样的字样。有经验的朋友应该很熟悉了，对吧？

简单来说，旧的2003格式是紧实的16进制内容，一旦损坏基本就没救了，有救过的朋友应该都感同身受。

而2007格式呢？

我们用WinRAR强行打开xlsx文件来看看，对没错，用WinRAR强行打开xlsx文件。

2007的格式都以XML，说简陋点就是文本形式存储，然后用弱校验的类ZIP压缩。如果有损坏，则只会造成很少的数据损失。

更通俗点来讲，2007的格式相当于一个记事本文件，或者视频文件。一小点地方坏了，结果就是一点乱码或者花屏。

而2003格式一旦坏了，就像你安装游戏的时候，安装文件损坏……惨遭GG。

微软给2003出兼容补丁是实属无奈，没想到十几年后，还有人抱着极易损坏的旧版本和旧格式不放。但我也明白，不是每个人都能换上最新版或者上Office365，所以折中一下用2007版本来演示，格式问题这是底限。我不是诅咒你们，但是万一辛辛苦苦做了几个月的PPT，或者积累了几年的数据突然损坏的时候，能不能救回来就在此一举了。

好的，感谢大家观看，我是波导终结者，喜欢的朋友请点个关注和赞吧，有什么疑问欢迎留言，我们下期再见。