按总分数分班

dongfangxielong

     

将表1的人员分十个班，每班50人，按总分蛇形分班，如我表2所示那样，并尽量兼顾男女的均衡

jerrike

1. =WRAPROWS(SORTBY(Sheet1!A2:A501,Sheet1!B2:B501,-1,Sheet1!R2:R501,-1),10)

复制代码

=VSTACK(WRAPROWS(FILTER(Sheet1!A:A,Sheet1!B:B="女"),10),WRAPROWS(FILTER(Sheet1!A:A,Sheet1!B:B="男"),10))

=WRAPROWS(Sheet1!A2:A501,10)

3190496160

均衡分班的帖子论坛很多的呀

arnosue

1. =XLOOKUP(IF(ISEVEN(ROW(A1)),ROW(A1)*10-(COLUMN(A1)-1),(ROW(A1)-1)*10+COLUMN(A1)),Sheet1!$S:$S,Sheet1!$A:$A)

复制代码

捣鼓了好半天，在Sheet1增加了辅助列，也只是按照排名要求分班，如何保证每个班22个男生，28个女生，属实难住我了。静待坛内大佬传授真经。

一杯绿茶来了

=IFERROR(INDEX(Sheet1!$A:$A,MOD(LARGE(Sheet1!$R$2:$R$501/1%%%+ROW($2:$501),(ROW(A1)-1)*10+COLUMN(A1)+MOD(ROW(A2),2)*((10+1)-COLUMN(A1)*2)),1000)),"")

ericxzhou

Python+Pandas:
import pandas as pd
import numpy as np

df = pd.read_excel('pandas exercise/sample391.xlsx')
l1 = [i for i in range(1, 11)]
l2 = [j for j in range(10, 0, -1)]
l = np.array([l1 + l2] * (len(df) // 20)).flatten().tolist() + (l1 + l2)[:(len(df) % 20)]
df['班级'] = pd.Series(l, name='班级').astype(str) + '班'
df.iloc[:, -2:].head(25)