做excel发现现有的函数功能不够用,就自学了一下VBA写了第一个新函数:根据条件连接字符串
比如count()有countif()和countifs(),sum()有sumif()和sumifs(),但是concatenate()却没有类似concatenateifs()的函数,于是自己写了个替代品
(写的解释比较面向初学者,如果是只需要参考,完全可以跳过直接看代码块)
问题:我需要在选区中找出同时符合两个条件的值,比如
地区 | 鱼 | 调料 |
---|---|---|
沙滩 | 咸鱼 | 盐 |
海沟 | 深海鱼 | 酱油 |
海沟 | 深海鱼 | 芥末 |
火星 | 深海鱼 | 沙子 |
这其中地区和名称同时相同的视为同一种鱼,那么海沟的深海鱼既可以用酱油也可以用芥末,在新的表中需要将这一项合并,也就是说处理后的新表如下: | ||
地区 | 鱼 | 调料 |
– | – | – |
沙滩 | 咸鱼 | 盐 |
海沟 | 深海鱼 | 酱油芥末 |
火星 | 深海鱼 | 沙子 |
实现: | ||
首先排个序会比较清晰,将要统计的地区和鱼录入新表(我自己的做法是直接复制到新表,再去除重复值),然后对调料使用函数自动填充 | ||
自定义的函数concatenate_ifs()仿照countifs()来设定参数,countifs()的语法就不说了 | ||
使用参数 | 给它起个名字 | |
– | – | |
需要合并的字符串所在单元格列,即表1的调料列 | concatenate_range | |
范围1,即表1的地区列 | range1 | |
条件1,即表2要去匹配的地区单元格 | criteria1 | |
范围2,即表1的鱼列 | range2 | |
条件2,即表2要去匹配的鱼 | criteria2 |
函数的使用,在表2的调料一格填充:
=concatenate_ifs(表1!调料:调料,表1!地区:地区,表2!地区1,表1!鱼:鱼,表2!鱼1)
剩下的格子自动填充即可
自定义函数的VBA代码:
'思路:
'先在range1中查找criteria1,找到了就记录是第几行,存为rowNum
'在满足条件1的记录行中找满足条件2的行,即range2中查criteria2
'查到的行会同时满足条件1、2,将行中对应的内容拼起来
Function concatenate_ifs(concatenate_range As Range, range1 As Range, criteria1 As String, range2 As Range, criteria2 As String)
'range类型是单元格
'Stop
'Stop是用来调试的
Dim rowNum As Integer
rowNum = 1
Dim r1 As Range
Dim res As String
res = ""
For Each r1 In range1
If r1 = criteria1 Then
rowNum = r1.Row
If range2.Cells(rowNum) = criteria2 Then
res = res & concatenate_range.Cells(rowNum)
'使用&合并字符串
End If
End If
Next r1
concatenate_ifs = res
End Function
这样写缺点是效率太低了点,很卡,因为VBA引用单元格cells比数组和字典慢很多,于是优化,用数组来计算:
Function concatenate_ifs(concatenate_range As Range, range1 As Range, criteria1 As String, range2 As Range, criteria2 As String)
'Stop '断点调试
Dim rowNum As Integer
Dim ub As Integer
ub = WorksheetFunction.CountIf(range1, "<>")
'将循环次数缩减为有内容的单元格
'如果表内有空白值要删除换成ubound(range1)
Dim res As String
res = ""
Dim rr1(), rr2(), c_r()
rr1 = range1
rr2 = range2
c_r = concatenate_range
'要注意Excel将单元格导入数组会按照单元格本身的形式
'比如这里将整列导入数组,数组的样子也是一列,而不是一维数组
For rowNum = 1 To ub
If rr1(rowNum, 1) = criteria1 Then
If rr2(rowNum, 1) = criteria2 Then
If c_r(rowNum, 1) <> "" Then
res = res & c_r(rowNum, 1)
End If
End If
End If
Next rowNum
concatenate_ifs = res
End Function
使用数组的计算速度要快很多
针对要分析的数据的结构改进函数也能省略许多不需要计算的值
使用函数的时候选择的范围更精确一些也能提高计算速度