当前位置:首页 > 办公 > Excel教程 > 正文内容

Excel内置函数不够用?这4个超爽的自定义函数,轻松提升工作效率!

酷网3个月前 (10-24)Excel教程41

Hello大家好,我是解题宝宝!

今天在这里在给大家分享几个高频的自定义函数。

在不规则的合并单元格中汇总 数据;

快速提取文本单元格中的数据;

根据指定颜色,进行快速求和;

在一组数据中,快速汇总前N大的数值;

好了,废话不多说,快来和我一起看下这几个高频的自定义函数如何使用吧~

01  不规则合并单元格汇总数据

这是工作中最常见的一个操作,不过Excel中内置函数对于合并单元格的计算非常吃力,而且不友好,如下,需要统计不同销售员的销售额汇总。

在这里我们创建一个「gather自定义函数」,代码如下:

接着我们就可以选中所有的合并单元格,然后输入自定义公式=gather(C2),按Ctrl+Enter回车,一键完成统计,超级爽!

这里的思路也非常简单,公式所在的单元格处理合并状态,它的行数决定了左边被合计的区域行数,所以使用Resize重置了高度,不明白也没关闭,会套用就行。

02  分段快速提取数值

另外一个高频的需求就是从文本单元格中提取指定位置的数值,最常见的就是在销售表中,经常会看到文字+数字混合在一起。

如果使用Excel内置功能来处理非常麻烦。如下:

这里的数据唯一的规律文本+数据交错,想使用内置函数来提取超级困难,而且兼容性不高,由于VBA支持正则,我们来简单创建一个Separate函数,代码如下:

函数有2个参数,第一个是单元格位置,第二个是返回的位置,例如这里我们想提取第2个数值,操作会非常简单。

输入公式:=Separate(B2, 2),然后向下填充即可。

稍微改造下公式,变成=Separate($B2, COLUMN(A1)),并且将公式向右填充,这样就可以提取出文本里的所有数值了。

至于原理,其实也不难,利用正则表达式从杂乱的数据中将数值类型的数据匹配出来。

当然除了提取数据、你还可以改造下函数,变成可以提取文本、英文、手机号等等,这些都可以去尝试下哦。

03  按照单元格背景色求和

在Excel中SUMIF函数可以按照某个条件进行求和,但是不能按照颜色进行求和,这个其实也是一个非常高频的需求。

例如对以下的绿色单元格进行求和,如果要使用内置办法,除了使用查找手动统计,没有其他快捷技巧。

而使用VBA就非常简单了,定义一个SumIFColor函数,总共有3个参数,第1个参数为「条件区域」,第2个参数为「指定颜色单元格」,第3个参数为「统计区域」,代码如下:

使用也非常简单,直接录入公式=SumIFColor(B2:B10,B4),一键就可以完成指定颜色的单元格求和汇总。

如果条件区域和求和区域不一致,补充多一个求和区域即可。

04  汇总前N大值

汇总前N大的数值也是一个非常常见的操作,Excel内置函数就必须要使用数组才能完成了,而且公式非常复杂。

例如要统计前 3 名销售员的总销售额,如下:

同样我们创建一个自定义函数SumTop3,默认计算前3名,传入第2个参数可以指定要查找前几名,代码如下:

使用起来也非常简单,如果要计算前3名,第2个参数可以省略。

直接录入公式=SumTop3(B2:C10),如下:

如果改成=SumTop3(B2:C10,4),就可以汇总前4名的数据了,非常简单,但是效率是不是非常高。

好了,以上就是今天要给大家分享的几个高频的Excel自定义函数,为常用函数建立函数库,让效率触手可及!

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/40379.html

分享给朋友:

相关文章

使用lookup函数计算销售提成

使用lookup函数计算销售提成

大家伙好,今天讨论下这样的问题,一个公司的业务提成计算方法如下: 现在需要根据各业务员的销售业绩计算他们的提成工资。如下 业务员销售业绩提成点数提成 张飞1000 赵云2000 曹操2001 荀彧5000 关羽1000...

使用VLOOKUP函数查找返回多列数据的方法

VLOOKUP函数单条件查找学会了,VLOOKUP函数多条件查找也学会了,都是输入一个公式然后向下复制填充一列的形式,如果有多列数据需要根据查找值调取对应数据,那么如何输入一个公式就能搞定一个区域的数据调用呢?本文通过实...

Excel函数公式大全 Excel函数公式使用教程

Excel函数公式大全 Excel函数公式使用教程

  在我们的日常工作中,总是经常会用到Excel表格,简单的记录一些内容对我们来讲没有难度,可当我们需要应用函数公式处理数据和表格的时候,相信大多数人就不会了。针对这个问题,下面,小编...

使用Windows命令合并CSV文件

使用Windows命令合并CSV文件

有时候,我们需要将同一文件夹中的多个CSV文件或TXT文件合并到一个文件中。我们可以一个个打开这些文件,复制粘贴,这是最原始的方法。我们可以编写程序,例如使用Excel VBA来帮助我们完成。其实,还有一种方法,就是利用...

Excel表格怎么把间隔行列删除?方方格子教你快速删除间隔行列数据

Excel表格怎么把间隔行列删除?方方格子教你快速删除间隔行列数据

  Excel表格怎么把间隔行列删除?许多小伙伴在使用Excel办公软件录入一些数据的时候习惯空一行或者一列,但是在完成数据录入之后数据没有那么美观,这种情况下就要针对空行数据进行删除...

使用Excel计算常见平均值的方法

在对Excel数据进行分析时,经常需要计算数据的平均值。在不同场合对于平均值的计算往往会有不同的限制,。 图1 使用AVERAGEIFS()函数获得平均值 提示 AVERAGEIFS()函数用于返回满足多重条件的所有单...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。