我在一所大学工作,最近我创建了一个电子表格,将百分比等级转换为等效的字母等级,然后我为每个等级分配一个学分,这是一种加权等级的方法。
表格(A 列 - C 列)代表一名学生的高中成绩单。换句话说,就是他们 9 年级至 12 年级的所有成绩。
- A 列是每门课程的百分比成绩
- B 列是将百分比等级转换为字母等级;
- C 列(信用)是我赋予特定结果的权重(例如 1 或 0.5);
“学分”值是我用来为某些标记比其他标记分配更多(或更少)权重的一种方法,即使字母等级可能相同。
我想要实现自动化的下一个步骤是按字母等级对“学分”列进行总计。F2:F13
我有一个所有可能的字母等级的列表。我想G2:G13
列出每个字母等级的总学分(C 列)。
例如,如果我有以下数据
A | 乙 | C |
---|---|---|
数字 | 信 | 信用 |
96 | A | 1 |
95 | A | 1 |
90 | A- | 1 |
81 | B- | 0.5 |
95 | A | 0.5 |
那么我的总数将是
F | G |
---|---|
成绩 | 总学分 |
A | 2.5 |
A- | 1 |
B+ | 0 |
乙 | 0 |
B- | 0.5 |
答案1
G2 中按学分计算的公式
=SUMIF(B:B,F2,C:C)
# If you have Office 365 you can use the following
# formula in G2 rather than copying the previous one down
=SUMIF(B:B,F2:F13,C:C)
如果忽略学分,则 G2 中的公式
=COUNTIF(B:B,F2)
# If you have Office 365 you can use the following
# formula in G2 rather than copying the previous one down
=COUNTIF(B:B,F2:F13)
* 归功于斯科特·克雷纳注意到单一公式方法。
SUMIF 函数
您使用求和函数函数对满足您指定条件的范围内的值进行求和。
句法: SUMIF(range, criteria, [sum_range])
COUNTIF 函数
使用计数,统计函数之一,用于计算符合条件的单元格数量
句法: COUNTIF(range, criteria)
额外的
除了使用嵌套的 IF 函数来计算字母等级外,您还可以使用 IFS 函数或带有 XMATCH 的查找表
IFS 代替嵌套 IF
=IFS(A2>92,"A", A2>89,"A-",
A2>86,"B+", A2>83,"B", A2>79,"B-",
A2>76,"C+", A2>72,"C", A2>69,"C-",
A2>66,"D+", A2>62,"D", A2>59,"D-",
TRUE,"F" )
XMATCH 代替 IF/IFS
=INDEX(grade, XMATCH(D2,pct,-1))
# Named Ranges
# grade=A2:A13 pct=B2:B13