积分公式计算

briancai20

各位，我有个HR的表格，需要完善公式，自动计算C列的积分。B列为姓名。 E列后面是填入课时。 每节课大概1.5到2课时。会根据实际填入。主要是最后一个我不知道如何增加公式，求高手协助。
谢谢！等待高手出现

积分的规则如下：
     每参加1课次培训  3积分   
    （1小时=1课时，）
总次数累计达20课时，年底一次性奖励5积分
总次数累计达30课时，年底一次性奖励10积分
总次数累计达40课时，年底一次性奖励15积分
总次数累计达50课时，年底一次性奖励20积分
总次数累计达60课时，年底一次性奖励35积分
达到60课时后，每增加1课次，再奖励2积分

briancai20

excel表格见附档。

briancai20

请问有高手可以解决此问题吗？？

excel助手

=IFERROR(LOOKUP(SUM(E3:AU3),{20,5;30,10;40,15;50,20;60,35}),0)+SUM(E3:AU3)*3+TEXT(SUM(E3:AU3)-60,"0;\0")*2
公式计算.zip (43.65 KB, 下载次数: 4)

2016-1-6 15:35 上传

点击文件名下载附件

briancai20

excel助手 发表于 2016-1-6 15:35
=IFERROR(LOOKUP(SUM(E3:AU3),{20,5;30,10;40,15;50,20;60,35}),0)+SUM(E3:AU3)*3+TEXT(SUM(E3:AU3)-60,"0; ...

60是课时，不是课次。 后面是超过60课时按照原有每课次3积分的基础上每课次+2积分。

excel助手

briancai20 发表于 2016-1-6 16:01
60是课时，不是课次。 后面是超过60课时按照原有每课次3积分的基础上每课次+2积分。

是的，你试一下对不对。

grf1973

前两步可以
=COUNT(E3:AU3)*3+VLOOKUP(SUM(E3:AU3),{0,0;20,5;30,10;40,15;50,20;60,35},2)
最后一步静等高手。
VBA可以吗？

grf1973

用VBA做的公式，可直接调用。

1. Function jf(rng As Range)
   
2.     kc = Application.WorksheetFunction.Count(rng)   '课次
   
3.     ks = Application.WorksheetFunction.Sum(rng)   '课时
   
4.     arr = [{0,0;20,5;30,10;40,15;50,20;60,35}]   '课时换积分规则
   
5.     jf = Application.WorksheetFunction.VLookup(ks, arr, 2)
   
6.     brr = rng
   
7.     If ks > 60 Then
   
8.         For j = 1 To UBound(brr, 2)         '找到60课时的点
   
9.             xks = xks + brr(1, j)
   
10.             If xks >= 60 Then Exit For
    
11.         Next
    
12.         If j < UBound(brr, 2) Then     '计算60课时后的课次
    
13.             For k = j + 1 To UBound(brr, 2)
    
14.                 If brr(1, k) > 0 Then xkc = xkc + 1
    
15.             Next
    
16.         End If
    
17.     End If
    
18.     jf = jf + kc * 3 + xkc * 2
    
19. End Function

复制代码

grf1973

C3=jf(E3:AU3)