如何基于单元格总分动态填充课程成果(CO)分数并符合规则？

需求回顾 #

有5项课程成果（CO）对应B-F列，每项分数规则：

• 当学科总分（H列）<5时，B-F列全填0
• 当总分>5时，每项分数取值为1或2，5项总和等于H列总分（满分10）

此前用RANDBETWEEN(1,2)填充B-E列、F列用=10-SUM(B6:E6)的方法，会出现F列分数超出1-2范围的问题，以下是优化方案：

分场景解决方案 #

场景1：总分<5时 #

直接在B-F列使用公式填充0：

=IF($H6<5,0,{后续逻辑})

场景2：总分>5时 #

核心逻辑：先给每项分配基础分1（总和5），剩余分数k = H6-5（范围1~5），随机将k项的分数从1改为2，最终总和正好等于H列总分。

方案1：Excel 365/2021动态数组版（推荐）

在B6单元格输入以下公式，自动填充至F列：

=IF($H6<5,0,LET(k,$H6-5,seq,SEQUENCE(,5),rand,RANDARRAY(,5),sorted,SORTBY(seq,rand),result,IF(seq<=k,2,1),SORTBY(result,sorted)))

公式说明：

• k：需要设置为2的CO项数量
• rand：生成5个随机数用于打乱顺序
• 通过两次SORTBY实现随机分配2的位置，确保结果符合总和要求且取值在1-2之间

方案2：旧版Excel兼容版

在B6单元格输入公式，向右拖动至F列：

=IF($H6<5,0,1+IF(COUNTIF($B$6:B6,2)<$H6-5,RANDBETWEEN(0,1),0))

公式说明：

• 先给当前单元格基础分1
• 统计已填充单元格中2的数量，若未达到k则随机决定是否改为2，否则保持1
• 刷新时按F9重新生成随机分布

原方案问题说明 #

原方案中B-E列随机生成1或2，F列用总分差值计算，当B-E列总和<8时F列会>2，总和>9时F列会<1，违反1-2的取值规则，而优化方案从根源上保证了所有项的取值范围和总和要求。

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