用下面这个公式可以基本解决:
=IF(A2="","",LOOKUP(VLOOKUP(B2,{"年",360;"月",30;"日",1},2,0)*A2,{0,3,30,90,180,360,720,1080},{"3天以下","3-30天","1-3个月","3-6个月","6-12个月","1-2年","2-3年","3年以上"}))
效果如图:
请注意着色部分的测试数据,也就是以年/月计但是不满1年/月的数据,可能在极端情况下会有些许误差,好处是,即便你输入100天、38月,也能正确计算
附件请下载参考
用VBA代码执行,代码如下:
Sub qujian()
Dim time As Double, i As Integer
i = 2
Do While Application.And(Cells(i, 5) <> "", Cells(i, 6) <> "")
time = Cells(i, 5).Value
Select Case Cells(i, 6)
Case "年"
Select Case time
Case Is > 3
Cells(i, 7) = "3年以上"
Case 2 To 3
Cells(i, 7) = "2-3年"
Case Is < 2
Cells(i, 7) = "1-2年"
End Select
Case "月"
Select Case time
Case Is > 6
Cells(i, 7) = "6-12个月"
Case 3 To 6
Cells(i, 7) = "3-6个月"
Case Is < 3
Cells(i, 7) = "1-3个月"
End Select
Case "日"
Select Case time
Case Is >= 3
Cells(i, 7) = "3-30天"
Case Is < 3
Cells(i, 7) = "3天以下"
End Select
End Select
i = i + 1
Loop
End Sub
效果如下(如果图片没有动画请点一下即可):