赋分制下excel函数的探索

冰皮小饼干

现在很多省份都开始实行“3+1+2”的赋分制，所以我想给自己研究一个模板，方便自己在工作中处理成绩单。



背景情况如下：

• 化学，生物，地理，历史这四门科目需要赋分计算，其他科目不需要。
• 这是转换规则（我在附件的Excel表的sheet2中有粘贴这个等级表格）
• 这是赋分的公式，及变形公式
  

需求如下：

• 在“赋分”列中，自动匹配等级，并引用相应等级中的区间上下限分数和该科目原始分里的上下限分数（比如，N列判断为A等级，就使用U11：X11中的数据，判断为B等级，就使用U12:X12中的数据……以此类推）
• 对化学，政治，生物，地理四科进行赋分的时候，最好不在原始成绩单（A:Q列）中增减列，因为我需要在后续使用这个模板的时候方便直接粘贴原始成绩单来更新数据。
• 在更新了A:Q列（原始成绩单数据）后，赋分列就可以自动更新分数，这样就能保证将此sheet直接复制一份，再进行简单的删减不必要的数据列后，快速得到处理好的成绩单来进行后续的统计。
• 如果有更好的方法来设计W列开始的辅助列，就更好了。
  

小tips:

• 如果算出来某个等级的下限人数分数并列，超出了这个等级阶段所需的人数，也按这个等级走（比如说，按总人数的15%算出来A等级应该是175个人，但A等级最低分并列的人太多，有10个，就全按A等级算）——先按这个规则来吧，不然不确定因素太多，公式就没法写了。
  

如果有其他不确定因素的影响公式的话，就请在回复中留言。
附件是我研究了一半的模板，请查收。

part98

你表格里面的赋分公式都是错的，排第一的不是应该100分么
化级=MID("ABCDE",XMATCH(L2,PERCENTILE.INC($L$2:$L$1174,{85;50;15;2;0}%),-1),1)
化赋=LET(h,$L$2:$L$1174,s,PERCENTILE.INC(h,{85;50;15;2;0}%),t,XMATCH(L2,s,-1),ROUND(TREND(INDEX({100,86;85,71;70,56;55,41;40,30},t),INDEX(HSTACK(VSTACK(MAX(h),DROP(MAXIFS(h,h,"<"&s),-1)),MINIFS(h,h,">="&s)),t),L2),2))
可以直接省掉所有辅助列

冰皮小饼干

请教一下大佬，
1. 这个里面的s,t,h分别代表什么呀？
2. 如果说成绩单里有部分同学的个别科目未参加考试，但考了其他科目，所以不能把这个同学从成绩单里删掉。那能不能实现只对有分数的同学的成绩进行赋分，因为这种情况下，单科的总参考人数有变化，相应的各个等级的人数也有变化？
3. 能不能实现判断等级的最低分人数多的话，就分到下一个等级？——比如说像示例里74等级人数较多，就分到B等级
     或者说在总分降序的前提下，就按比例来严格划分人数——比如算下来A等级按比例算下来是175.95个人，那等级划分就在总分降序的情况下，分176或者175个人，而不用管并列？
   求大佬帮忙，我知道我这些需求挺麻烦，就算如果目前没办法全部实现，能实现一部分我也真的非常感激了！！！我还在研究你那个公式的本质逻辑。

冰皮小饼干

我自己摸索着倒是有一个分级公式，但如果有同学的单科缺考的话，还是会自动判断为“A”等级，求大佬们帮我看看

part98

冰皮小饼干 发表于 2024-7-24 16:13
请教一下大佬，
1. 这个里面的s,t,h分别代表什么呀？
2. 如果说成绩单里有部分同学的个别科目未参加 ...

s，t，h可以是任何字母或者中文，只是为了缩短公式用了let函数命名，至于代表什么，你自己分段去理解就好了
公式要的是统一性，而不是你认为的觉得多了就要放下一级。多或者少的判定标准又是什么。
该科目缺考是按零分算，还是参与赋分，那是你要明确的规则。
单科的赋分，你考虑总分降序，你觉得合理么。

part98

重复了，见楼上

冰皮小饼干

part98 发表于 2024-7-24 16:47
s，t，h可以是任何字母或者中文，只是为了缩短公式用了let函数命名，至于代表什么，你自己分段去理解就好 ...

非常感谢大佬的解释。
那个字母我再研究一下哈。
这个“多了”，或者“少了”，之前是这样想的，比如说算下来划分到A等级的分数中中，最低分80分就有10名同学，那么这10名同学都划入A等级的话，可能算下来A等级的人数就比15%要多1-2个百分点，所以就想着只取到上一个分数。em……我能理解你说的“公式要的就是统一性“，所以就考虑第二种方案，严格按比例来，这样算下来，比如说A等级是175.95个人的话，取第175名就刚好，这样等级的人数占比就不会太偏离。但同样都是80分，为什么甲同学是A等级的赋分，乙同学是B等级的赋分，所以就考虑到了用总分降序，总分高的就是上一个等级，总分低的就是下一个等级。
不知道我解释清楚了没

part98

冰皮小饼干 发表于 2024-7-24 17:45
非常感谢大佬的解释。
那个字母我再研究一下哈。
这个“多了”，或者“少了”，之前是这样想的，比如说 ...

标准的赋分规则是一定不会考虑用总分降序的，同分肯定是同一标准。
你看到的比例只是做分数的分界点的。最终出来的赋分和原始分的对照表，肯定是存在多个赋分对应一个原始分的情况，但是不可能有一个原始分对应两个赋分的。
别纠结人数，去深入学习下PERCENTILE函数

冰皮小饼干

part98 发表于 2024-7-24 18:39
标准的赋分规则是一定不会考虑用总分降序的，同分肯定是同一标准。
你看到的比例只是做分数的分界点的。 ...

好哒好哒！太感谢啦！！