excel - Excel/Python：如何按字母顺序对名称进行分组，以使每个组的字母数量相等？

Question

我有一个大学的 879 名学生的虚拟 Excel 表，我正在与它一起在 python 中进行一些数据分析。此 excel 表具有各种列，例如：

1. 学生姓名
2. 部分
3. 出席率百分比
4. 报名号

但是学生的名字没有按字母顺序正确排列。

我想明智地平均分配学生“部分”，以便在每个部分中有相同数量的学生，他们的名字的每个起始字母。

我尝试按字母顺序对 Excel 中的数据进行排序，但它按字母顺序对整个列进行排序，这是我不希望的。相反，我希望数据以“部分”的方式排列，以便每个部分都有学生的名字以每个字母开头，或者如果该特定字母的所有名称在前面的部分中已经用尽，则没有学生。所有部分的学生人数都相等（或几乎相等）。

For example:
the dataset 12 sections with 879 students:
Section A has 74 students
Section B has 74 students
Section C has 74 students
Section D has 73 students
Section E has 73 students
Section F has 73 students
Section G has 73 students
Section H has 73 students
Section I has 73 students
Section J has 73 students
Section K has 73 students
Section L has 73 students

Number of students having first character A is 89
Number of students having first character B is 47
Number of students having first character C is 7
    :         :            :              :
    :         :            :              :  
Number of students having first character Y is 1
Number of students having first character Z is 2

我的目标：

Section A will have: 
 - 7 students whose name start with A (89/12 = 7 students)
 - 3 students whose name start with B (47/12 = 3 students) 
 - 1 student whose name start with C  (As 7<12, so cant put all students in all sections, so 1)
    :         :            :             
    :         :            :                

 - 1 student whose name start with Y   
 - 1 student whose name start with Z

Section B will have: 
- 7 students whose name start with A
  :    :      :     :     :    :
  :    :      :     :     :    :
- 0 students whose name start with Y  (As no student left with alphabet starting with Y)
- 1 student whose name start with Z

同样，其他部分将具有如下分布：

有没有办法使用 Excel 或 Python Pandas 库查询来实现这一点？
这是我的 Excel 表

标签： excelpandasgroupingalphabetical

Answer 1

这是一个暂定的解决方案。根据特定名称在以相同字母开头的所有名称中的出现次数分配节号，与每个节中应以该字母开头的名称数进行比较。：

=LET(names,E5:E883,
rows,ROWS(names),
sections,12,
seq,SEQUENCE(rows,1,0),
startrow,XLOOKUP(LEFT(names,1)&"*",names,seq,,2),
counts,COUNTIF(names,LEFT(names,1)&"*"),
countspersection,counts/sections,
occurrence,seq-startrow,
section,QUOTIENT(occurrence+0.5,countspersection),
section)

然后按分配的节号排序：

=LET(names,E5:E883,
rows,ROWS(names),
sections,12,
seq,SEQUENCE(rows,1,0),
startrow,XLOOKUP(LEFT(names,1)&"*",names,seq,,2),
counts,COUNTIF(names,LEFT(names,1)&"*"),
countspersection,counts/sections,
occurrence,seq-startrow,
section,QUOTIENT(occurrence+0.5,countspersection),
SORTBY(names,section,1,names,1))

因为学生人数只能是整数，所以每个部分的大小会有所不同，具体取决于分配的数字有多少被低估，有多少与理论值相比被高估（例如每个部分应该有7.4个A）部分，但实际上只能有 7 或 8 个）。我对此进行了一些分析，小组规模下降如下：

让每个字母的组大小和数字都完全正确是非常困难的——我认为你需要一种迭代方法来更进一步。