A | B | C | |
1 | 时间 | 人员 | 销售金额 |
2 | 12 | 甲、乙、丙 | 100,200,300 |
3 | 15 | 丁、戊、己 | 300,500,600 |
4 | 16 | 庚、辛、乙 | 900,700,500 |
5 | 17 | 丁、戊、甲 | 400,200,100 |
6 | 18 | 乙、丙、己 | 200,200,200 |
7 | 19 | 丁、辛、乙 | 300,400,800 |
我想统计乙这些天的销售金额总计,怎么办?
设置公式
'声明函数Statistics,类型为Long型。包扩两个必选参数,第一个为数据区域,区域中包括待计算的对象名称和对应的数据,第一个为计算对象。
Function Statistics(Data As Range, Object As Range) As Long
Application.Volatile
Dim arr() As String, arr2() As String
For j = 1 To Data.Rows.Count
arr = Split(Data.Rows(j).Cells(1), "、")
arr2 = Split(Data.Rows(j).Cells(2), ",")
For i = 0 To UBound(arr)
If Object = arr(i) Then Statistics = Statistics + arr2(i): Exit For
Next
Next
End Function
在d15单元格输入“乙”,在e15单元格输入“=Statistics(C3:D8,D15)”。
E15=1700
来研究这个函数split
返回一个下标从零开始的一维数组,它包含指定数目的子字符串。用法:
传回数组 = Split(原始字串, 要找的字串, 拆成几个数组)
默认返回一维数组,以指定字符分割,
T=split("F: aa.txt","")
则:T(0)="F:";T(1)="a";T(2)="a.txt" ;T(UBound(T))=a.txt
我们对自定义函数做一些注解:
Function Statistics(Data As Range, Object As Range) As Long
Application.Volatile '声明为易失性函数
Dim arr() As String, arr2() As String '声明两个数组
For j = 1 To Data.Rows.Count '遍历数据区域第一列
arr = Split(Data.Rows(j).Cells(1), "、") '将数据区域第一列的单元格转换成数组并赋予数组arr
arr2 = Split(Data.Rows(j).Cells(2), ",") '将数据区域第二列的单元格转换成数组并赋予数组arr2
For i = 0 To UBound(arr) '将单元格转换成数组,然后遍历数组的每一个元素
'如果计算对象等数组arr的某个值,那么将数组arr2中对应的值累加到函数的返回值中
If Object = arr(i) Then Statistics = Statistics + arr2(i): Exit For
Next
Next
End Function
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24345566/viewspace-1053550/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24345566/viewspace-1053550/