我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
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自定义函数,为常用函数建立函数库,让效率触手可及!
相关文章
我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
在工作中,可能许多朋友都会碰到一个情况,那就是工作簿和工作表数据的合并操作。如何将上百个工作簿快速合并到一个表格中,许多朋友可能会觉得不可思议。今天我们就来教大家学习一......
2023-01-08
今天在这里为你分享5个Excel文本函数,这些拆分和组合函数,你一定会用上的。①LEFT函数公式:=LEFT(A2,1)在Excel表格中,需要想要拆分汉字,想从哪里开始就从那哪里开始。首先选定单元格......
2023-01-08
相信大家也和我一样,才开始看到Excel可以当做翻译软件的时候会很好奇,这究竟是怎样做到的?其实,这个方法并不是很难,它是由一个函数公式而制作出来的,好了,首先我们一起来看看成......
2023-01-08
函数可以说是所用快捷方法中最为简单的一种方法,为什么很多人认为函数用起来很难了?主要是因为它拥有很长的函数公式,记不住。其实不管是学Excel函数,还是学习其他的一些快捷方法......
2023-01-08