杨玉兴 发表于 2021-11-17 09:22:00

D列和E列按A列同名称求和

如题，D列和E列按A列同名称求和，请帮忙解决一下

赵定仙 发表于 2021-12-3 01:19:59

=SUM(($A$2:$A$442=$H2)*IF(ISNUMBER(FIND("kg",D$2:D$442)),NUMBERVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(D$2:D$442,"kg",REPT(" ",20)),"g",REPT(" ",20)),1,20)))*1000+NUMBERVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(D$2:D$442,"kg",REPT(" ",20)),"g",REPT(" ",20)),21,20))),--LEFT(D$2:D$442,LEN(D$2:D$442)-1)))

数组公式，右拉下拉

莫笑腊酒浑 发表于 2021-12-10 23:36:30

感谢感谢，

林向荣 发表于 2022-1-9 23:34:58

=SUM(($A$2:$A$442=$H2)*IF(ISNUMBER(FIND("kg",D$2:D$442)),MMULT(NUMBERVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D$2:D$442,"kg",REPT(" ",20)),"g",REPT(" ",20)),"条",REPT(" ",20)),"个",REPT(" ",20)),{1,21},20))),{1000;1}),--LEFT(D$2:D$442,LEN(D$2:D$442)-1)))

本来想简化一下公式，发现你除了KG,G还有条和个。。。

寡言呱唧 发表于 2022-1-23 02:53:34

这些问题，本来是可以在表单的设计上规避的，非要搞成一坨，何必为难自己呢，

提刀夜行 发表于 2022-2-6 18:14:27

没有困难，创造困难

华山再现 发表于 2022-2-19 14:19:47

这是系统里面导出来的，

海军欧巴 发表于 2022-2-23 11:40:35

=SUM(MMULT(IFERROR(SUBSTITUTE(MID(SUBSTITUTE(IF(ISNUMBER(FIND("kg",$D$2:$D$442)),$D$2:$D$442,"kg"&$D$2:$D$442),"kg",REPT(" ",99)),COLUMN(A:B)*99-98,99),"g",)*{1000,1},),ROW($1:$2)^0)*($A$2:$A$442=H2))
页: [1]
查看完整版本: D列和E列按A列同名称求和