如图所示,把A列的数据,按照班级分别复制到其他列。A列的数据是可变的,意思就是我把一组数据复制A列,BCD列就自动分别出现一二三班的数据,CBD列的公式要怎么写?
C2=IF(ROW(A1)+MATCH(C$1,$A:$A,0)<MATCH(D$1,$A:$A,0),OFFSET(INDIRECT("A"&MATCH(C$1,$A:$A,0)),MIN(ROW(A1),MATCH(D$1,$A:$A,0)-2),,1,1),"")
下拉到足够位置再右拉复制到E列;
F2右侧已无班级,作为最后一个班级,需要另写公式:
F2=IF(OFFSET(INDIRECT("A"&MATCH(F$1,$A:$A,0)),ROW(F1),,1,1)<>"",OFFSET(INDIRECT("A"&MATCH(F$1,$A:$A,0)),ROW(F1),,1,1),"")
把图片点开放大看完整演示过程。
主要原理:以C列为例,先判断C1“一班”和D1“二班”分别在A列的行号a和b,a+ROW(A1)不断下拉变大,当与b一样大说明到界限了,停止引用;在这之前,以A列“一班”所在单元格为原点、利用OFFSET向下移动,移动的距离是MIN(ROW(A1),b-2),用MIN函数可以控制移动的最大范围;最后一列就比较简单了,没有这些判断,只要向下移动到不为空即可,如果为空则强制显示为空。
具体还是要自己试着操作一下就明白了。
-------------------我是分割线-----------------------------------
就必须完全自动是吗?好吧。以下为追答,按追问修改:
C1=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,ROW($A$1:$A$100)/(ISNUMBER(SEARCH("班",$A$1:$A$100))),COLUMN()-2)),"")
//默认按100行数据处理的,可以按需求修改,下同
C2=IFERROR(IF(D$1<>"",IF(ROW(A1)+MATCH(C$1,$A:$A,0)<MATCH(D$1,$A:$A,0),OFFSET(INDIRECT("A"&MATCH(C$1,$A:$A,0)),MIN(ROW(A1),MATCH(D$1,$A:$A,0)-2),,1,1),""),IF(OFFSET(INDIRECT("A"&MATCH(C$1,$A:$A,0)),ROW(C1),,1,1)<>"",OFFSET(INDIRECT("A"&MATCH(C$1,$A:$A,0)),ROW(C1),,1,1),"")),"")
C2公式下拉填充到C25(仅演示,实际按你的数据规模需要),然后选中C1:C25右拉填充公式至N列(仅演示,实际按你的数据规模需要继续右拉),结束。
这个方法是必须先在C1D1E1F1手动填入一班二班三班四班等班级名字才可以吗,问题是如果有非常多班级且班级名字没有规律,还是很麻烦
追答已经追答了,写在原答案后面,可以看一下。
公式=IFERROR(LET(t,TEXTJOIN(",",,A1:A22),r,TEXTSPLIT(t,ROW(1:10)-1,",",1),TRANSPOSE(HSTACK(r,TEXTSPLIT(t,",",r,1)))),""),适合excel365版本。
1.用LET函数,对TEXTJOIN函数把A1:A22区域的数据合,并用逗号隔开“,”行到一行数据,并定义为变量t;
2.通过TEXTSPLIT函数把变量t进行拆分,以数值作为分隔符,即ROW(1:10)-1就是0—9的数字,作为行分隔符拆分,以“,”为列分隔符拆分,把空格忽略,即1,这个最后得到的结果就是一列为“一班”,”二班“,“三班”,“四班”的数据,并定义为变量r。
3.TEXTSPLIT(t,",",r,1)就是把“一班”,”二班“,“三班”,“四班”的数据作为列分隔符进行拆分,得到四行数值数据。
4.TRANSPOSE(HSTACK(r,TEXTSPLIT(t,",",r,1))),HSTACK函数是把r和TEXTSPLIT(t,",",r,1)的数据横向堆叠,再通过TRANSPOSE函数转置,即得到如下图所示: