文字和数值的拆分

sbdk007

M老师都做出来了，我还没想到好思路

魚。。

满主的贴子，来来来，来个世界级的火车：
=SUMPRODUCT(TEXT(LEFT(TEXT(MID(SUBSTITUTE(A2:A6&"鱼",C1&"收","鱼"&REPT(" ",21)),COLUMN(A:TM),21),),20),"G/通用格式;;;!0")*C10)
+SUMPRODUCT(TEXT(LEFT(TEXT(MID(SUBSTITUTE(A2:A6&"鱼",C1&"放","鱼"&REPT(" ",21)&"-"),COLUMN(A:TM),21),),20),"G/通用格式;-G/通用格式;;!0")*C10)
+SUMPRODUCT(TEXT(LEFT(TEXT(MID(SUBSTITUTE(A2:A6&"鱼",D1&"收","鱼"&REPT(" ",21)),COLUMN(A:TM),21),),20),"G/通用格式;;;!0")*D10)
+SUMPRODUCT(TEXT(LEFT(TEXT(MID(SUBSTITUTE(A2:A6&"鱼",D1&"放","鱼"&REPT(" ",21)&"-"),COLUMN(A:TM),21),),20),"G/通用格式;-G/通用格式;;!0")*D10)
+SUMPRODUCT(TEXT(LEFT(TEXT(MID(SUBSTITUTE(A2:A6&"鱼",E1&"收","鱼"&REPT(" ",21)),COLUMN(A:TM),21),),20),"G/通用格式;;;!0")*E10)
+SUMPRODUCT(TEXT(LEFT(TEXT(MID(SUBSTITUTE(A2:A6&"鱼",E1&"放","鱼"&REPT(" ",21)&"-"),COLUMN(A:TM),21),),20),"G/通用格式;-G/通用格式;;!0")*E10)
+SUMPRODUCT(TEXT(LEFT(TEXT(MID(SUBSTITUTE(A2:A6&"鱼",F1&"收","鱼"&REPT(" ",21)),COLUMN(A:TM),21),),20),"G/通用格式;;;!0")*F10)
+SUMPRODUCT(TEXT(LEFT(TEXT(MID(SUBSTITUTE(A2:A6&"鱼",F1&"放","鱼"&REPT(" ",21)&"-"),COLUMN(A:TM),21),),20),"G/通用格式;-G/通用格式;;!0")*F10)



虽然非常得土，不过还是工整的，表骂我哈

nsxlyj

这个火车可以绕地球18圈=SUM(IFERROR(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&A3&A4&A5&A6,"甲",REPT(" ",199)&"甲"),"乙",REPT(" ",199)&"乙"),"丙",REPT(" ",199)&"丙"),"丁",REPT(" ",199)&"丁"),ROW(1:49)*199,199),C1:F1&"收",)*C10:F10,)+IFERROR(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&A3&A4&A5&A6,"甲",REPT(" ",199)&"甲"),"乙",REPT(" ",199)&"乙"),"丙",REPT(" ",199)&"丙"),"丁",REPT(" ",199)&"丁"),ROW(1:49)*199,199),C1:F1&"放",)*C10:F10*-1,))
缩一点
=SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&A3&A4&A5&A6,"甲",REPT(" ",199)&"甲"),"乙",REPT(" ",199)&"乙"),"丙",REPT(" ",199)&"丙"),"丁",REPT(" ",199)&"丁"),ROW(1:49)*199,199),C1:F1&"收",),C1:F1&"放","-")*C10:F10,))

libo5563

函数做不出来，降难度 pq做， 数据源 表变来变去，pq刷新 跟不上，只好做死数据了，水平欠啊

sbdk007

我也来开个火车
=SUM(MMULT(B8:T8+1,--TEXT(LEFT(SUBSTITUTE(ENCODEURL(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&A3&A4&A5&A6,"收",),"放","-"),C1:F1,REPT(" ",199)),ROW(1:19)*199-198,199))),"%",REPT(" ",19)),19),"[<>];;;!0"))*C10:F10)
再缩减一下，终于破200了
=SUM(TEXT(LEFT(SUBSTITUTE(ENCODEURL(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&A3&A4&A5&A6,"收",),"放","-"),C1:F1,REPT(" ",199)),ROW(1:19)*199-198,199))),"%",REPT(" ",19)),19),"@!0")*C10:F10)
再减减
=SUM(TEXT(LEFT(SUBSTITUTE(ENCODEURL(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2&A3&A4&A5&A6,"放","收-"),C1:F1&"收",REPT(" ",199)),ROW(1:19)*199-198,199))),"%",REPT(" ",19)),19),"@!0")*C10:F10)
=SUM(IFERROR(LEFT(SUBSTITUTE(ENCODEURL(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2&A3&A4&A5&A6,"放","收-"),C1:F1&"收",REPT(" ",199)),ROW(1:19)*199-198,199))),"%",REPT(" ",19)),19)*C10:F10,))

solextrade

连滚带爬，也写了个超级火车，可惜偶尔出错。自己都晕了，不找了：

=SUM(MMULT(C10:F10,TRANSPOSE(IFERROR((1-ISERR(FIND(LEFT(C1:F1,2),MID(CONCAT(A2:A6),TRANSPOSE(SMALL(IF(MMULT(1-ISERR(MID(1&CONCAT(A2:A6),ROW(1:199)+{0,1,2},1)%),{9;1;1})=9,ROW(1:199)),TRANSPOSE(ROW(1:199)))),6))))*TRANSPOSE(MID(SUBSTITUTE(SUBSTITUTE(CONCAT(A2:A6),"收",0),"放","-"),SMALL(IF(MMULT(1-ISERR(MID(CONCAT(A2:A6),ROW(1:199)+{0,1,2},1)%),{1;1;9})=9,ROW(2:200)),TRANSPOSE(ROW(1:199))),SMALL(IF(MMULT(1-ISERR(MID(SUBSTITUTE(CONCAT(A2:A6),".",0),ROW(1:199)+{0,1,2},1)%),{1;1;9})=2,ROW(3:201)),TRANSPOSE(ROW(1:199)))-SMALL(IF(MMULT(1-ISERR(MID(CONCAT(A2:A6),ROW(1:199)+{0,1,2},1)%),{1;1;9})=9,ROW(2:200)),TRANSPOSE(ROW(1:199))))),))))

静看流年RSSQ

确实是毫无思路。水平太次了。各位老师的答案都看不懂。。

最暴力的还是一楼老师的那个笨办法。暴力直观。  不过满版说了不能用EXCEL 没有的函数。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCAT(A2:A6),C1&"收","三"&C10&"千"),C1&"放","三"&-C10&"千"),D1&"收","三"&D10&"千"),D1&"放","三"&-D10&"千"),E1&"收","三"&E10&"千"),E1&"放","三"&-E10&"千"),F1&"收","三"&F10&"千"),F1&"放","三"&-F10&"千"),"三","+"),"千","*")

nsxlyj

nsxlyj 发表于 2019-11-5 15:30
这个火车可以绕地球18圈=SUM(IFERROR(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&A3& ...

我太难了
=SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&A3&A4&A5&A6,C1,"@"&C1),D1,"@"&D1),E1,"@"&E1),F1,"@"&F1),"@",REPT(" ",199)),ROW(1:49)*199,199),C1:F1&"收",),C1:F1&"放","-")*C10:F10,))
老师出的题到处都是坑，才发现居然还有个"学霸脸@_@"

再改一下，赶紧把“@”换成“！”
=SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&A3&A4&A5&A6,C1,"！"&C1),D1,"！"&D1),E1,"！"&E1),F1,"！"&F1),"！",REPT(" ",199)),ROW(1:49)*199,199),C1:F1&"收",),C1:F1&"放","-")*C10:F10,))

solextrade

nsxlyj 发表于 2019-11-5 17:34
我太难了
=SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ...

南老师的这个公式应该能算进标准答案里了吧，厉害！
等看看满版海版的公式了。

准提部林

哈~~ 哪是坑??? 是太平洋吧~~
咱2000, 題本身就是個大問題~~
浮不上..溺.....