Excel内置函数不够用这4个超爽的自定义函数轻松提升工作效率
最新回答
冰依湄凌
2024-09-26 03:38:46
今天在这里在给大家分享几个高频的自定义函数。
在不规则的合并单元格中汇总 数据；
快速提取文本单元格中的数据；
根据指定颜色，进行快速求和；
在一组数据中，快速汇总前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自定义函数，为常用函数建立函数库，让效率触手可及！
热门标签
