源数据提取问题

s971005

低版本：
D2:
=IF(COUNTIF(G$1:G1,INDEX($A$1:$A$5,MAX(IF(ISNUMBER(FIND(H2,$B$2:$B$5)),ROW($A$2:$A$5),0))))>=1,"",INDEX($A$1:$A$5,MAX(IF(ISNUMBER(FIND(H2,$B$2:$B$5)),ROW($A$2:$A$5),0))))

E2:
=FILTERXML("<b><a>"&TEXTJOIN("</a><a>",,SUBSTITUTE(B2:B5,"，","</a><a>"))&"</a></b>","//a")

冬不暖

s971005 发表于 2023-10-10 17:51
低版本：
D2:
=IF(COUNTIF(G$1:G1,INDEX($A$1:$A$5,MAX(IF(ISNUMBER(FIND(H2,$B$2:$B$5)),ROW($A$2:$A$5) ...

第一个有bug 需要优化

冬不暖

浮生若梦~~~ 发表于 2023-10-10 15:11
来个不空的=IFERROR(INDEX(A:A,SMALL(IF(LEN(B$2:B$5)-LEN(SUBSTITUTE(B$2:B$5,"，",))+1>=COLUMN(A:Z),ROW ...

嘻嘻 谢谢浮生大佬 空的拖个火车

1. =IFERROR(VLOOKUP(ROW(A1),IF({1,0},MMULT(N(ROW($1:$4)>=COLUMN(A:D)),LEN(B$2:B$5)-LEN(SUBSTITUTE(ASC(B$2:B$5),",",))+1)-LEN(B$2:B$5)+LEN(SUBSTITUTE(ASC(B$2:B$5),",",)),A$2:A$5),2,),"")

复制代码

evluan

zpy2 发表于 2023-5-18 16:51
//select * from 品牌机型一行拆分多行;
cli_split_data~品牌机型一行拆分多行~，~机型;
select udf_cle ...

手机还可以玩sql，牛逼

一杯绿茶来了

365
=REDUCE(A1:B1,A2:A5,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TEXTSPLIT(OFFSET(y,,1),,"，")),""))))

evluan

df = df.with_columns(pl.col('机型').str.split('，').alias('B')).explode('B').select(['品牌', 'B']).with_row_count('序')
df1 = df.select(['序', '品牌']).with_columns(pl.col('品牌').cumcount().over('品牌').alias('A')).filter(pl.col('A') == 0)
df = df1.join(df, on='序', how='outer').select(['品牌', 'B'])