实用到爆的10个EXCEL技巧    

服务软件 12-23 14:13:38 22 0

大家好我是波导终结者,这次跟大家分享一下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,或者积累了几年的数据突然损坏的时候,能不能救回来就在此一举了。


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

#618必领系列#天猫618超级红包上线!最高可领618元!!!
无线端戳这里领取
PC端戳这里领取
领取时间:5月29日-6月20日

实用到爆的10个EXCEL技巧   

网友评论