左边的变成右边的数据

杨杨1991315

一表可以用公式变成表二的样式吗？

yfan17

1. =SUBSTITUTE(INDEX('1表'!A:A,AGGREGATE(15,6,ROW('1表'!A$1:A$54)/ISNUMBER(FIND("姓      名",'1表'!A$1:A$54)),ROW(A1))),"姓      名：","")

复制代码

yfan17

1. =INDEX('1表'!A:A,AGGREGATE(15,6,ROW('1表'!A$1:A$54)+1/ISNUMBER(FIND("姓      名",'1表'!A$1:A$54)),ROW(A1)))

复制代码

hugeinfo

=SUBSTITUTE(INDEX(A:A,SMALL(IF(ISNUMBER(FIND("姓      名",$A$1:$A$54)),ROW($1:$54)),ROW(A1))),"姓      名：","")

=SUBSTITUTE(INDEX(A:A,SMALL(IF(ISNUMBER(FIND("姓      名",$A$1:$A$54)),ROW($1:$54)+1),ROW(A1))),"姓      名：","")

mykitami

=LET(a,TOCOL(A1:A999,1),b,SCAN(0,a,LAMBDA(x,y,IF(LEFT(y)="姓",x+1,x))),REDUCE({"计划门店","",""},UNIQUE(b),LAMBDA(x,y,LET(c,FILTER(a,b=y),VSTACK(x,IF(SEQUENCE(,3)=1,TEXTAFTER(FILTER(a,(b=y)*(LEFT(a,2)="计划")),":"),HSTACK("",TEXTAFTER(INDEX(c,1,1),"："),INDEX(c,2,1))))))))

quqiyuan

vba供参考，没那么烧脑

boyuanoffice

1. =LET(tab,A1:A54,n,IF(LEFT(tab)="预","",SCAN(0,tab,LAMBDA(x,y,IF(LEFT(y)="姓",x+1,x)))),DROP(REDUCE("",SEQUENCE(MAX(n)),LAMBDA(x,y,LET(a,TEXTAFTER(FILTER(tab,n=y),"：",-1,,1),VSTACK(x,CHOOSE({1,2,3},DROP(a,2),INDEX(a,1),INDEX(a,2)))))),1))

复制代码

如果只提取姓名和电话, 下面这个:

1. =REDUCE({"姓名","电话"},A1:A54,LAMBDA(x,y,IF(LEFT(y)="姓",VSTACK(x,HSTACK(TEXTAFTER(y,"："),OFFSET(y,1,))),x)))

复制代码

leepoison

=TEXTSPLIT(CONCAT(REDUCE("计划门店+姓名+电话-",TEXTSPLIT(CONCAT(A1:A54&" "),,"姓      名：",TRUE),LAMBDA(x,y,VSTACK(x,TRANSPOSE(REGEXP(y,"计划门店:[一-龟]+"))&"+"&INDEX(TEXTSPLIT(y,," ",TRUE),1)&"+"&INDEX(TEXTSPLIT(y,," ",TRUE),2)&"-")))),"+","-",TRUE)

我的城市你来过

=LET(x,LOOKUP(FILTER(ROW($A$1:$A$54),ISNUMBER(FIND("计划门店",$A$1:$A$54))),FILTER(ROW($A$1:$A$54),ISNUMBER(FIND("名：",$A$1:$A$54)))),HSTACK(FILTER($A$1:$A$54,ISNUMBER(FIND("计划门店",$A$1:$A$54))),CHOOSEROWS($A$1:$A$54,x),CHOOSEROWS($A$1:$A$54,x+1)))

静听溪流

重复发帖了吧   昨天好像看到个类似的帖子