数据源: 要求:当姓名的成绩,从上到下,求和,大于等于7时,返回其对应的日期。 举例,如张三,成绩从上到下求和,在第5行时,等于7,则返回2018/1/4. 假如,没有等于7的,就取大于7的最小值对应的日期。 函数辅助列(最简单) 1、sumif配合相对引用,创建辅助列。公式如下: =IF(SUMIF(C$2:C2,'张三',B$2:B2)>=7,'是','') 2、利用match精确查找,返回第一条数据的特性,配合index得出结果。 公式如下: =INDEX(A:A,MATCH('是',D:D,0)) 数组公式:思路其实和辅助列的思路一样,都同样运用了sumif配合相对引用的操作。 公式如下: =INDEX(A:A,MATCH(TRUE,SUMIF(OFFSET(C1,,,ROW(B1:B10)),'张三',B1:B10)>=7,)) 解释: 1、SUMIF(OFFSET(C1,,,ROW(B1:B10)),'张三',B1:B10) offset实行相对引用求和,类似上面的辅助列公式的结果。 F9抹黑公式,返回下列数组: {0;1;3;3;7;7;13;20;28;28} 2、将1中数组中的元素分别和7进行大小比较,得出true/false。 SUMIF(OFFSET(C1,,,ROW(B1:B10)),'张三',B1:B10)>=7 返回下面的数组: {FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE} 3、用match查找true,找到张三成绩大于等于7的第一个位置。 MATCH(TRUE,SUMIF(OFFSET(C1,,,ROW(B1:B10)),'张三',B1:B10)>=7,) 返回:5 4、index得出结果: VBA方法: 从下到下,累加张三的成绩,一旦大于等于7,返回对应的日期,退出循环。 Sub vfvfvf() Dim i As Integer Dim s For i = 2 To 10 '遍历数据 If Cells(i, 3) = '张三' Then '对张三的成绩求和 s = s Cells(i, 2) '将成绩返回给变量s If s >= 7 Then '如果成绩大于等于7 Range('h2') = Cells(i, 1) '返回日期,输出到H2单元格 Exit For '退出循环,不再遍历数据 End If End If Next End Sub 总结:三种方法,思路基本都是一样的。个人认为,VBA比函数好写多了。真正简单的反而是VBA。 文件下载: 链接: https://pan.baidu.com/s/1kWK6hnT 密码: vbiv |
|