基于优先/备选志愿的Excel课程分配方案及函数实现咨询

需求回顾 #

现有160名学生从7门设有人数上限的课程中选课：

• 课程1-7的人数上限分别为30、23、29、24、25、23、22
• 每名学生填写2个权重相同的优先志愿（标记为1）和2个权重相同的备选志愿（标记为R）
• 分配规则：优先在不超过课程上限的前提下满足优先志愿，若优先志愿均已满额，则分配备选志愿

需要实现：

1. J列：生成学生最终分配的课程
2. K列：标记该分配志愿的类型（1/R）
3. N列：统计各课程的实际选课人数（需符合O列的上限要求）

函数解决方案（假设数据结构） #

假设你的Excel表格结构如下：

• A1:A7：课程名称（课程1至课程7）
• O1:O7：对应课程的人数上限
• B列、C列：学生的两个优先志愿
• D列、E列：学生的两个备选志愿
• 学生数据从第2行开始（第1行为表头）

1. J列：分配最终课程（单元格J2） #

使用LET函数简化嵌套逻辑，逐行判断志愿优先级与课程剩余名额：

=LET(
    pref1, B2, pref2, C2, alt1, D2, alt2, E2,
    cap_pref1, XLOOKUP(pref1, $A$1:$A$7, $O$1:$O$7),
    cap_pref2, XLOOKUP(pref2, $A$1:$A$7, $O$1:$O$7),
    cap_alt1, XLOOKUP(alt1, $A$1:$A$7, $O$1:$O$7),
    cap_alt2, XLOOKUP(alt2, $A$1:$A$7, $O$1:$O$7),
    count_pref1, COUNTIF($J$1:J1, pref1),
    count_pref2, COUNTIF($J$1:J1, pref2),
    count_alt1, COUNTIF($J$1:J1, alt1),
    count_alt2, COUNTIF($J$1:J1, alt2),
    IF(count_pref1 < cap_pref1, pref1,
        IF(count_pref2 < cap_pref2, pref2,
            IF(count_alt1 < cap_alt1, alt1,
                IF(count_alt2 < cap_alt2, alt2, "无可用课程")
            )
        )
    )
)

将公式下拉至所有学生行即可。公式逻辑：

• 先检查第一个优先志愿的已分配人数是否小于上限，是则分配该课程
• 若第一个优先志愿已满，检查第二个优先志愿
• 优先志愿均满额时，依次检查两个备选志愿
• 所有志愿均满额时返回"无可用课程"（可根据需求调整提示文本）

2. K列：标记志愿类型（单元格K2） #

根据J列的分配结果，匹配对应志愿类型：

=LET(
    assigned_course, J2,
    IF(OR(assigned_course=B2, assigned_course=C2), "1",
        IF(OR(assigned_course=D2, assigned_course=E2), "R", "")
    )
)

下拉公式至所有学生行，若分配结果为"无可用课程"则返回空值。

3. N列：统计各课程实际人数（单元格N2） #

使用COUNTIF统计J列中对应课程的出现次数：

=COUNTIF($J:$J, A2)

下拉公式至N7，即可得到每门课程的实际选课人数，该数值会自动匹配J列的分配结果，且不会超过O列的上限。

关键说明 #

• 公式依赖逐行计算：需按学生顺序下拉公式，因为COUNTIF($J$1:J1, ...)仅统计当前行之前的已分配人数，确保名额判断的实时性
• 若使用旧版Excel（无LET函数），可拆解为嵌套IF公式，逻辑一致但可读性稍差：

  =IF(COUNTIF($J$1:J1,B2)<XLOOKUP(B2,$A$1:$A$7,$O$1:$O$7),B2,IF(COUNTIF($J$1:J1,C2)<XLOOKUP(C2,$A$1:$A$7,$O$1:$O$7),C2,IF(COUNTIF($J$1:J1,D2)<XLOOKUP(D2,$A$1:$A$7,$O$1:$O$7),D2,IF(COUNTIF($J$1:J1,E2)<XLOOKUP(E2,$A$1:$A$7,$O$1:$O$7),E2,"无可用课程"))))
  

内容的提问来源于stack exchange，提问作者Soctopus