怎样把42个学生的成绩数据，按照班平均分分成5个或者6个一组，用excel表格实现自动分组

Question

如题所述

Answer 1

按平均分是指分组后每个组的平均分与全班的平均分最接近吗？

本问题是否能用公式，比较悬。实测了规划求解，也很难得到满意的结果。但应该说规划求解是比较合适的方法，只要舍得花时间，可能的话，适当加上手工调整，也许会有比较满意的结果。且规划求解与给定的数据有很大的关系，也许你的实际数据正好遇上规划求解便于得到满意解，那就很幸运了。下面结合模拟的数据说明求解方法：

1. 建立数学模型。如图：

（1）A列为姓名，B列为成绩，在B44中计算出平均成绩：

B44=AVERAGE(B2:B43)

（2）C列为分组，用数字1～6代表1组到6组。先随便按人数分成均匀的7组，即用1～6填充。

（3）F列到K列，用公式按C列的分组引用A列的姓名：

F2=INDEX($A:$A,SMALL(IF($C$2:$C$43=--LEFT(F$1),ROW($2:$43),4^10),ROW(1:1)))&""

同时按Ctrl+Shift+Enter三键输入数组公式，右拉到K2，再一起下拉若干行，多于7行，图中拉到第13行。因为求解过程中，C列是可变单元格（以确定分组），某组的人数可能会超过7人的，所以要多拉些空行。

（4）L列到Q引用对应的成绩：

L2=IF(F2="","",VLOOKUP(F2,$A$2:$B$43,2,))

右拉到Q2，再L2:Q2一起下拉到第13行（与姓名列相同的行数）

（5）对各组人员计数和统计平均成绩：

F15=COUNT(0/(F2:F13<>""))

同时按Ctrl+Shift+Enter三键输入数组公式，右拉到中5

L16=AVERAGE(L2:L13)

右拉到Q16

（6）在R16中计算L16:Q16的标准差：

=STDEVP(L16:Q16)

这是本问题求解的关键，也是本问题的难度。如何选择一个参数来做规划求解的目标，使得分组后各组的平均成绩最接近，也就是最接近全班的平均成绩。目前想到的只有标准差，标准差反映数据的离散程度，数据越接近，标准差越小，所有数据完全相同时，标准差为0。但也因为标准差的计算是一个复杂的计算过程，所以也就导致本问题规划求解时比较难以得到满意的解。

2. 设置规划求解参数

数据选项卡——规划求解（如果没有，就需要在选项中加载），如图：

目标单元格：R16，目标：可选择目标值为0或选择最小值。

可变单元格：C2:C43

添加约束：

F15:K15＝7——每组7人

C2:C43＝INT——整数

C2:C43>=1——最小组号为1

C2:c43<=6——最大组号为6

求解方法，有3种，具体选用哪种，Excel也没有一个明确的说明，但一般说来，如果问题有解，总会有一种方法会是比较合适的，可以自己测试。经测试，本问题用演化才能求解。

选项中设置一个最大求解时间或最大求解次数。以防止求解进入死循环。本问题测试时，选择最大时间：480（秒——8分钟），确定返回求解对话框，按求解后开始求解。

3. ç»æ±‚解后，得到的结果如图。数次重新求解后均没得到一个满意的结果，是本人用规划求解中遇到的最不满意解的一次，约束每组人数为都没满足，可能与设置的求解时间有关。但如前所述，也许你的实际数据能幸运地得到满意解。

Answer 2

{=INDEX(OFFSET($A$1,1,1-MOD(COLUMN(A:A),2),42,1),43-MOD(LARGE($B$2:$B$43*10^4+44-ROW($B$2:$B$43),ROW(1:1)*6-IF(ROW(1:1)<4,5-INT((COLUMN(A:A)+1)/2)+1,INT((COLUMN(A:A)+1)/2)-1)),10^4))}公式可以调整成这样，是6组，每组7人

输入公式后按Ctrl+Alt+回车，三键结束，然后向后向下填充就可以。

组平均公式=IFERROR(AVERAGE(F2:F8),0)

本回答被网友采纳

Answer 3

想用代码还是想用数据透视表