例如数据源有两家商场有几个合同期,现在要匹配这家商场,账期202006,也就是20年6月属于哪个合同周期,账期201902,19年2月属于哪个合同周期,还要按不同的商场,用什么公式来匹配上去呢,求大神解答,谢...
先把b列分列,日期分别在B,C列
=TEXT(INDEX(B:B,SMALL(IF(($A$2:$A$100=E2)*(DATE(LEFT(F2,4),MONTH(MID(F2,5,2)),30)>=$B$2:$B$100)*(DATE(LEFT(F2,4),MONTH(MID(F2,5,2)),30)<=$C$2:$C$100),ROW($A$2:$A$100),65536),1)),"YYYY-MM-DD")&"-"&TEXT(INDEX(C:C,SMALL(IF(($A$2:$A$100=E2)*(DATE(LEFT(F2,4),MONTH(MID(F2,5,2)),30)>=$B$2:$B$100)*(DATE(LEFT(F2,4),MONTH(MID(F2,5,2)),30)<=$C$2:$C$100),ROW($A$2:$A$100),65536),1)),"YYYY-MM-DD")
数组公式,需要同时按 ctrl shift 回车键
公式复杂,是因为账期的写法不是标准日期格式
'自定义公式-按ALT+11-插入-模块(合同期)
Function 合同期(a As Range, b As Range)
For i = 1 To b.Rows.Count
If b.Cells(i, 1) = "" Then Exit For
If a.Cells(1, 1) = b.Cells(i, 1) Then
c = WorksheetFunction.Substitute(b.Cells(i, 2), "/", "")
d = Split(c, "-", -1, 1)
e = a.Cells(1, 2) & "01"
If e >= d(0) And e <= d(1) Then 合同期 = b.Cells(i, 2)
End If
Next
End Function
本回答被提问者采纳