正式竞赛题 + 测试小 V

丢丢表格

正式赛区链接
http://club.excelhome.net/thread-1447132-1-1.html
本来这个是送分题，可能是老师们未能理解题意吧，且多数公式都计算值的偏差，一个简单的小题显得艰难起来。

在这里再声明一下：
       未要求上附件的，只要帖上公式，无须另加附件， 测试公式时，不会用你们上的附件测试（也就是加辅助的写法归为错误公式）
       每位ID（老师）只能占一层楼，如需修正公式，请在原楼上修改或追加公式，不得再开楼层，否则扣除财富。

  我在 C 列 已写明计算方式，在这再举一例：
       假如入职是 2015/5/29  
       至 2018/5/29 起才满足 3 年 ， （以对年对月对日为界）        2018/12/31 至 2018/5/28   这 217 天 是按满3年档 7元计算，     217/365*7
        2018/5/28 至 2018/1/1  ,即（365-217=148）这 148 天 是未满 3年的 ，按 1年档次 5元计算  ， 148/365*5
      
   两段相加式、所以写成：
       148/365*5 + 217/365*7
        4.1616+2.0274
        =6.189
        四舍五入修约成 ：  6

  总之，要是今年是刚达新一档线的 ，就须分两段计算 （一段是未达新档年内天数的上一档标准计算值 + 另一段是 已达新档年内天数的新档标准计算值）

。

丢丢表格

Sub 测试()
' 公式写在 D2单元格
[e2] = ""
本年末 = VBA.CDate(Year(Date) & "-12-31")
[b2] = 本年末
本年初 = VBA.CDate("1-1")
For I = 1 To 6000
时界 = VBA.CDate(Year(Date) & -Month(Range("b2"))) + Day(Range("b2")) - 1

已达 = 本年末 - 时界 + 1     '测试 一
未达 = 365 - 已达

'未达 = 时界 - 本年初        '测试 二
'已达 = 365 - 未达

'未达 = 时界 - 本年初         '测试 三
'已达 =  = 本年末 - 时界 + 1

月数a = WorksheetFunction.Max(0, DateDiff("m", Range("b2"), 本年初))
月数b = DateDiff("m", Range("b2"), 本年末)
ak = Application.WorksheetFunction.Lookup(月数a, Range("h3:o4"))
bk = Application.WorksheetFunction.Lookup(月数b, Range("h3:o4"))
ss = Round((未达 * ak + 已达 * bk) / 365, 0)
[c2] = ss
If [d2] <> ss Then [e2] = "公式错误": End

[b2] = [b2] - 1
Next I
[e2] = "正确"
End Sub


请在D2 单元格 写公式
   请测试一下  注意：  把电脑日期 调到 2020 年 再试一次     

                  （只要 2018 年 能通过的，就算公式正确）

。

丢丢表格

由于闰年的关系，日期上有出入，所以做个测试。  
已达 = 本年末 - 时界 + 1        '测试 一
未达 = 365 - 已达

'未达 = 时界 - 本年初              '测试 二
'已达 = 365 - 未达

'未达 = 时界 - 本年初              '测试 三
'已达 =  = 本年末 - 时界 + 1


凡是在 2018年度时，上面三种测试 能通过一种的，就算正确公式 。

丢丢表格

测试小V  更新一下

Sub 测试()
' 公式写在 D2单元格
[e2] = ""
本年末 = VBA.CDate(Year(Date) & "-12-31")
[b2] = 本年末
本年初 = VBA.CDate("1-1")
r = InputBox("请输入测试（1，2，3）：0 退出测试")
For I = 1 To 6300
时界 = VBA.CDate(Year(Date) & -Month(Range("b2"))) + Day(Range("b2")) - 1

Select Case r
Case 0
End

Case 1
已达 = 本年末 - 时界 + 1     '测试 一
未达 = 365 - 已达

Case 2
未达 = 时界 - 本年初        '测试 二
已达 = 365 - 未达

Case 3
未达 = 时界 - 本年初         '测试 三
已达 = 本年末 - 时界 + 1

End Select

月数a = WorksheetFunction.Max(0, DateDiff("m", Range("b2"), 本年初))
月数b = DateDiff("m", Range("b2"), 本年末)
ak = Application.WorksheetFunction.Lookup(月数a, Range("h3:o4"))
bk = Application.WorksheetFunction.Lookup(月数b, Range("h3:o4"))
ss = Round((未达 * ak + 已达 * bk) / 365, 0)
[c2] = ss
If [d2] <> ss Then [e2] = "公式错误": End

[b2] = [b2] - 1
Next I
[e2] = "正确"
End Sub

micch

不懂代码，就是觉得比人工验证方便多了

mmlzh

公式太长，不及格

丢丢表格

欢迎各位大师 来大显神手 ~~~

jinlong506

丢丢表格 发表于 2018-11-30 11:18
欢迎各位大师 来大显神手 ~~~

我知道情况了

mmlzh

这最终还是思路加上点技巧的问题，思路对了拿分就容易了

anonym

感谢楼主提醒，本题对于边界值，还是需要仔细分析和处理。

不妨考虑特定情况，设定当前年份2020，入职日期2017/4/2。如果不考虑闰年，休假天数=(91*5+274*7)/365=6.501≈7；如果考虑闰年，休假天数=(92*5+274*7)/366=6.497≈6。

建议楼主还是考虑闰年的情况，否则还是不能做到精确计算。楼主的初衷是想简化，但它却造成了边界值的误差（其实精确值应该只有1个，3个case似乎有点冗余）。可以将当前年份是否为闰年（或者更直接，当前年份的总天数）作为已知条件。当然也可以要求答题者在同一个公式内加入闰平判断。

另，代码中关于变量“月数a”的计算似乎应该如下：

1. 月数a = WorksheetFunction.Max(0, DateDiff("m", Range("b2"), 本年初 - 1))

复制代码