根据指定字符把单个单元格拆分到多行（公式，辅助列）

alan57

1. =REDUCE(G1:J1,C2:C27,LAMBDA(s,t,VSTACK(s,IFNA(HSTACK(OFFSET(t,,-2,,2),DROP(REDUCE("",TOCOL(TEXTSPLIT(t,";")&","&TEXTSPLIT(OFFSET(t,,1),,";")),LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,",")))),1)),OFFSET(t,,-2,,2)))))

复制代码

新函数

博爱风雨同舟

=LET(a,MMULT(N(ROW($2:$8)>COLUMN($A:$G)),LEN($C$2:$C$8)-LEN(SUBSTITUTE($C$2:$C$8,";",))+1),SWITCH({1,2,3},1,XLOOKUP(SEQUENCE(MAX(a)),a,A2:A8,"",1,-1),2,XLOOKUP(SEQUENCE(MAX(a)),a,B2:B8,"",1,-1),3,TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",1,C2:C8),";"))))

mjm04

alan57 发表于 2022-10-17 14:32
新函数

老师好

我也用循环的思路也了一下，函数能跑起来，但发现一个奇怪的问题：结果里有 #N/A 的报错；但我就是找不到原因在那里，麻烦帮我看下，谢谢

就好像变量里有个空值一样，就是找不到这个空值是在那里产生的，想不通

=LET(_标题,A1:C1,_x,TOCOL(REDUCE(,REGEXP(C2:C8,"[^;]+",3),VSTACK),3),REDUCE(_标题,_x,LAMBDA(a,b,VSTACK(a,HSTACK(FILTER(A2:B8,ISNUMBER(FIND(b,C2:C8))),b)))))

无关岁月

mjm04 发表于 2024-11-12 15:41
老师好

我也用循环的思路也了一下，函数能跑起来，但发现一个奇怪的问题：结果里有 #N/A 的报错；但我 ...

可以这样

1. =REDUCE(A1:C1,C2:C8,LAMBDA(_a,_b,VSTACK(_a,IFNA(HSTACK(OFFSET(_b,,{-2,-1}),TOCOL(REGEXP(_b,"[^;]+"))),OFFSET(_b,,{-2,-1})))))

复制代码

hugeinfo

=DROP(REDUCE("",C2:C8,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(OFFSET(y,,-2,,2),TEXTSPLIT(y,,";")),OFFSET(y,,-2,,2))))),1)

liujunwei4321

保持持续的关注

快乐王子238

=REDUCE({"课程类别","考核方式","上课地点"},C2:C8,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(OFFSET(y,,-2,,2),TEXTSPLIT(y,,";")),OFFSET(y,,-2,,2)))))

szl3178

1. =REDUCE(F1:H1,C2:C8,LAMBDA(x,y,LET(a,TEXTSPLIT(y,,";"),VSTACK(x,HSTACK(REPTARRAY(OFFSET(y,,-2,,2),ROWS(a)),a)))))

复制代码

mengrs

=HSTACK(TEXTSPLIT(CONCAT(REPT(A2:A8&";"&B2:B8&",",LEN(C2:C8)-LEN(SUBSTITUTE(C2:C8,";",""))+1)),";",",",1),TEXTSPLIT(CONCAT(C2:C8&";"),,";",1))

mykitami

=REDUCE(A1:C1,C2:C8,LAMBDA(x,y,VSTACK(x,IF(SEQUENCE(,3)<3,OFFSET(y,,,,-3),TEXTSPLIT(y,,";",1)))))