[第134期]数据整理（已总结）

swyrlbq

问题一
原以为TEXTJOIN是16版函数，CONCAT是13版函数，于是有了公式，

1. =CONCAT(IF((MID(A6,ROW($1:$35),1)<>MID(A6,ROW($2:$36),1))+(CODE(MID(A6,ROW($1:$35),1))>47),MID(A6,ROW($1:$35),1),""))

复制代码

因公式简单，怀疑CONCAT不是13版函数，经过查询，的确如此。
帖子发了，删除不了。继续探究吧。
想到13版前函数，用常规方法似乎无法实现，于是想到用迭代计算的方法。在365平台下，通过反复探索修改，测试成功。

先设置启用迭代计算，100次，输入公式

1. =IF(C6="00",LEFT(" "&A6,3),C6&MID(A6,SMALL(IF(MMULT(N(SUBSTITUTE(MID(A6,ROW($1:35)+{0,1},1),".",0)<"0"),{1;1})=2,99,ROW($1:35)),LEN(C6)),1))

复制代码

找个07版验证，不运算，多次寻找原因，似乎07版迭代计算不支持数组运算。
还想找个13版试验，安装卸载太麻烦，只好放弃。

又想探究问题2
搞出的公式，实在离要求太远，再没有思路优化了

1. =SUM(MOD(MMULT(SMALL(IF(MID(SUBSTITUTE("/"&B6,".",0),ROW($1:40),1)<"0",ROW($1:40)+IFERROR(MID(B6,ROW($1:40),COLUMN(A:I))/1,)),({1;3;5}+{0,1})*9),{-1;1}),1))

复制代码

虽然两个问题都不能及格，但是参与也就是学习，期待大佬的杰作。

cjc209

我建议 竞赛题应该出一些有实用价值的更好 纯粹为写函数而出题没有实际意义

cjc209

其实这两道题 一点实用价值都没有         

minchar

=LET(x,MID(A6,ROW($1:$50),1),CONCAT(IF((x=MID(A6,ROW($2:$51),1))*(IFERROR(CODE(x)<48,0)),"",x)))

丿丿丿戎

1. 第一题：=TEXT(,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","!-!"),"+","!+!"),"/","!/!"),"*","!*!"),"!!","*"))

复制代码

cinlo

第一题只会常规套路（166字符）：

1. =CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"-","-
   "),"
   -",),"+","+
   "),"
   +",),"/","/
   "),"
   /",),"*","*
   "),"
   *",))

复制代码

第二题（124字符）：

1. =SUM(MOD(-IMREAL(IMDIV(TEXT(,MID(SUBSTITUTE(ENCODEURL(B6:B8),"%2",REPT(" ",39)&"*"),COLUMN(A:C)*39-38,39))&"j","1+j"))*2,1))

复制代码

李逸雪

1. =TEXT(,TEXT(,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"*","**\\\*#\"),"/","*\\\*#\/\"),"+","*\+\"),"-","*\-\")))

复制代码

没想到code模式要多打个\

江山社稷

第一题

1. =SUBSTITUTE(TEXT(,TEXT(,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"*","!*""!"),"/",",!!!/"),"-","*!-!"),"+","*!+!"))),"/,",)

复制代码

晨光晞微

晨光晞微 发表于 2022-5-17 10:22

第一题12个SUBSTITUTE申请出战。

1. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"-","()"),")(",),"()","-"),"+","()"),")(",),"()","+"),"/","()"),")(",),"()","/"),"*","()"),")(",),"()","*")

复制代码

delimit-R

问题一公式1：

=SUBSTITUTE(LEFT(A6,FIND("-",A6))&SUBSTITUTE(LEFT(SUBSTITUTE(RIGHT(A6,30),"-",""),4),"//","/")&SUBSTITUTE(RIGHT(MID(A6,13-ROW(1:1),4),ROW(1:1)),"//","/")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A6,20),"***","*"),"**","*"),"--","-"),"//","/"),"--","-")

问题一公式2：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"---","-"),"--","-"),"***","*"),"**","*"),"--","-"),"////","/"),"///","/"),"//","/"),"+++","+")

新手学习，方法有点笨。
我想了下，有没有一种方法可以把四则运算符一次性地只保留唯一值，这样就非常快了。