求解：拆分-合并-求和-排序一步到位！！

hcm19522

I5:L11=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A5,"&","%"),"%",REPT(" ",99)),COLUMN(A1)*198-99,99))

N5:Q11{=SUM(IFERROR(MID(SUBSTITUTE(SUBSTITUTE($A$5:$A$11,"&",REPT(" ",99)),I5,),{1,2,3,4}*99-98,99)*$B$5:$B$11,))

E3:E11{=IFERROR(INDIRECT(TEXT(RIGHT(LARGE((COUNTIF(E$2:E2,I$5:L$11)=0)*N$5:Q$11*10^10+ROW($5:$11)/1%+COLUMN(I:L),1),4),"!R0C00"),),"")

G3:G11{=IF(E3="","",SUM(IFERROR(MID(SUBSTITUTE(SUBSTITUTE(A$5:A$11,"&",REPT(" ",99)),E3,),{1,2,3,4}*99-98,99)*B$5:B$11,)))

ydwszuibd

624134745 发表于 2022-7-11 16:23
这个题目我咋感觉似曾相识呀，我在1个群里写过答案呀。

哈哈哈，我也是群里看到的，但是具体忘记哪个群了，刚好用的上，但是有空行的时候公式就不适用了，所以再来EH上问问

ydwszuibd

hcm19522 发表于 2022-7-11 18:45
I5:L11=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A5,"&","%"),"%",REPT(" ",99)),COLUMN(A1)*198-99,99))

N5:Q1 ...

谢谢你哦，我希望不加辅助列的情况，不过老版本的函数应该很难一步到位

zpy2

select * from 拆分_合并_求和_排序 limit 3;cli_split_data~拆分_合并_求和_排序~&~字符串;create temp table bb as select *,regexp2('(.*)%(.*)',字符串,2) 品名,regexp2('(.*)%(.*)',字符串,1)/100.00 百分比 from 拆分_合并_求和_排序split;create temp table cc as select 品名,sum(百分比*数量) 含量 from bb group by 品名;select * from cc order by 含量 desc;

wintroop

Power Query 解法

MHanys

VBA的见附件 需求.7z (19.48 KB, 下载次数: 9)

2022-7-12 11:06 上传

点击文件名下载附件

ydwszuibd

=LET(a,FILTER(A5:B11,B5:B11<>""),b,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,EXPAND(TEXTSPLIT(INDEX(a,y,1),"%","&"),,3,INDEX(a,y,2))))),1),c,UNIQUE(INDEX(b,,2)),SORT(HSTACK(c,DROP(REDUCE("",c,LAMBDA(x,y,VSTACK(x,SUM((INDEX(b,,2)=y)*INDEX(b,,1)*INDEX(b,,3)/100)))),1)),2,-1))

ydwszuibd

=LET(b,DROP(REDUCE("",A4:A11,LAMBDA(x,y,VSTACK(x,LET(a,TEXTSPLIT(y,"%","&"),IF(ISERROR(a),"",HSTACK(INDEX(a,,2),INDEX(a,,1)*OFFSET(y,,1))))))),1),s,UNIQUE(INDEX(b,,1)), c,HSTACK(s,SCAN("",s,LAMBDA(x,y,SUM(IF(y=INDEX(b,,1),INDEX(b,,2),)/100)))),SORT(FILTER(c,INDEX(c,,1)<>""),2,-1))

高个子

Meteor-渣渣 发表于 2022-7-11 16:20
排序这个要求，为难函数真的是太为难了
解决问题建议选择一下更好的方法，比如vba

对于365来说，这个问题不是问题，刚好楼主也是365

tchh666

1. =LET(A,TEXTSPLIT(TEXTJOIN("&",,SUBSTITUTE(A5:A11,"%","%"&B5:B11&"%")),"%","&"),B,DROP(A,,2),SORT(UNIQUE(HSTACK(B,MAP(B,LAMBDA(X,SUM((B=X)*TAKE(A,,1)*INDEX(A,,2))))%)),2,-1))

复制代码