实用到爆的10个EXCEL技巧

2019-12-21 15:59:12 704点赞 9720收藏 461评论

大家好我是波导终结者,这次跟大家分享一下10个实用的EXCEL技巧。跟那些烂大街的什么提取生日不一样,可能会比较复杂,所以我会附上详细的函数解释、说明和思路。

使用环境以EXCEL2007默认安装为准。强烈建议大家抛弃2003,因为新格式比旧格式优秀太多,这个放到最后讲。

1.统计不重复项数

实用到爆的10个EXCEL技巧

以前在开发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,最后相加。

而如果参数只有一个,那就没得乘,直接变成简单的数组内元素相加,我们利用的就是这一点。

实用到爆的10个EXCEL技巧

接下来再来看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有这么个函数:

实用到爆的10个EXCEL技巧

NUMBERSTRING这个函数简直是本地化的典范,中文专用,第2个参数可以取1、2、3,效果直接在图上演示了,就不凑字数了。

不过这个函数也有缺点:不支持小数。

实用到爆的10个EXCEL技巧

如果有小数的话,函数会自动四舍五入取整,注意,会四舍五入。

一般情况下,我们的小数只有两位,可以用上图方式分别取出来,然后转成大写的伍和陆,后面自己手动接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这个函数,很多人都有听过,但经常有人用不明白。

实用到爆的10个EXCEL技巧

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

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

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

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

实用到爆的10个EXCEL技巧

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

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

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

4.VLOOKUP的高级应用

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

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

实用到爆的10个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个目标员工。以此类推。

实用到爆的10个EXCEL技巧

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

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

实用到爆的10个EXCEL技巧

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

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

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

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

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

5.数据透视表

实用到爆的10个EXCEL技巧

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

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

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

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

实用到爆的10个EXCEL技巧

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

实用到爆的10个EXCEL技巧

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

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

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

6.几种排名方法

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

实用到爆的10个EXCEL技巧

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

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

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

实用到爆的10个EXCEL技巧

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

实用到爆的10个EXCEL技巧

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

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

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

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

7.制作下拉菜单

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

实用到爆的10个EXCEL技巧

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

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

实用到爆的10个EXCEL技巧

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

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

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

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

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

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

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

实用到爆的10个EXCEL技巧

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

实用到爆的10个EXCEL技巧

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

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

实用到爆的10个EXCEL技巧

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

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

实用到爆的10个EXCEL技巧

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

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

实用到爆的10个EXCEL技巧

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

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

9.制作自定义函数

实用到爆的10个EXCEL技巧

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

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

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

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

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

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

实用到爆的10个EXCEL技巧

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

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

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

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

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

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

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

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

实用到爆的10个EXCEL技巧

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

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

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

而2007格式呢?

实用到爆的10个EXCEL技巧

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

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

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

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

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

实用到爆的10个EXCEL技巧

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

展开 收起

Office 365 家庭版

Office 365 家庭版

249元起

微软 (Microsoft) Office 家庭学生版 2016 激活密钥 Mac专用 正版办公软件 一次购买 永久使用 非商业使用

微软 (Microsoft) Office 家庭学生版 2016 激活密钥 Mac专用 正版办公软件 一次购买 永久使用 非商业使用

749元起

《1Password》 ios密码管理软件

《1Password》 ios密码管理软件

暂无报价

《vegas pro 14 edit》视频编辑软件

《vegas pro 14 edit》视频编辑软件

132元起

《Agenda》Mac数字版软件

《Agenda》Mac数字版软件

暂无报价

《Money Pro》 记账理财软件

《Money Pro》 记账理财软件

6元起

用友软件 好会计财务软件专业版追加会计角色1用户/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

用友软件 好会计财务软件专业版追加会计角色1用户/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

301元起

用友财务软件 好会计云财务软件 畅捷通用友T3网页版 专业版试用30天

用友财务软件 好会计云财务软件 畅捷通用友T3网页版 专业版试用30天

1元起

微软 Office 365 家庭版 1年订阅 电子下载版 即买即用 6账号共享30台设备 Windows PC/Mac/平板/手机均适用

微软 Office 365 家庭版 1年订阅 电子下载版 即买即用 6账号共享30台设备 Windows PC/Mac/平板/手机均适用

498元起

微软 Office 365 个人版 1年订阅 电子下载版 即买即用 1账号登录5台设备 Windows PC/Mac/平板/手机均适用

微软 Office 365 个人版 1年订阅 电子下载版 即买即用 1账号登录5台设备 Windows PC/Mac/平板/手机均适用

398元起

ps cc2019 零基础自学 视频教程

ps cc2019 零基础自学 视频教程

19.9元起

用友财务软件 好会计普及版 畅捷通T3在线版会计记账软件 专业版30天试用

用友财务软件 好会计普及版 畅捷通T3在线版会计记账软件 专业版30天试用

498元起

远平条码 YPLABEL免驱不干胶标签条码打印软件

远平条码 YPLABEL免驱不干胶标签条码打印软件

400元起

《Lossless Photo Squeeze(无损图片瘦身)》

《Lossless Photo Squeeze(无损图片瘦身)》

暂无报价

用友财务软件 好会计专业版追加账套/年 畅捷通用友T3网页版 专业版试用30天

用友财务软件 好会计专业版追加账套/年 畅捷通用友T3网页版 专业版试用30天

50元起

用友财务软件 好会计专业版追加1个会计角色/年 畅捷通用友T3网页版 专业版试用30天

用友财务软件 好会计专业版追加1个会计角色/年 畅捷通用友T3网页版 专业版试用30天

300元起
461评论

发表评论请 登录
  • 最新
  • 最热
评论举报

请选择举报理由

相关文章推荐

更多精彩文章
更多精彩文章
最新文章 热门文章
9.7K
扫一下,分享更方便,购买更轻松