[第103期]巧译电文[已总结]

delete_007

总结帖见20楼。
背景：
    我军方电台费尽艰辛，截获了敌军的一批电文。据情报显示，这批电文均为6位数字密码，是敌方人员登录QQ、微信、微博、EH论坛等等的凭证。为了尽快破译电文，获取密码，给予敌人毁灭性的打击，于是发动广大EH会员，群策群力，希望能以最快的速度完成任务。

说明：
  1.根据密码本翻译截获的电文
  2.所有译文均为6位数字，密码本无优先级，以译出6位数字为准
  3.无论如何都有部分字符无法翻译的电文是干扰电文，译文为空。如A9
  4.译文不足6位数字的电文是干扰电文，译文为空。如A5
  5.每条电文都不可能有多余的字符，所有字符都必须翻译
  例：我爱上EH玩EXCEL
            如颜色所示，可以翻译成738548，所有字符均有翻译且正好6位
         我爱上EH玩EXCEL
            如果按上述颜色翻译，为1298578，为7位数，不满足要求。

要求：
  1.纯函数题，不允许VBA、定义名称、辅助单元格
  2.不限定03版本，可使用高版本函数
  3.B2公式下拉至B13，或B2:B13多单元格数组公式
  4.公式完成之后，如无误，可以点击测试按钮进行测试                  
      提醒：测试程序并不完善，不能测试所有情况，只供参考
      本想更新测试程序，但可能会泄露答题思路，只好作罢，后期由我测试
  5.欢迎取巧

评分：
  1.公式长度（含等号）<=400字符，获1技术分
  2.公式长度（含等号）<=300字符，获2技术分
  3.公式长度（含等号）<=230字符，获3技术分
  4.精彩答案可获4技术分






竞赛日期：2014-3-8至2014-4-8


该贴已经同步到 delete_007的微博

丢丢表格

我来打头炮了,不知是否能响一下!
B2=TEXT(MAX(--TEXT(0&SUBSTITUTE(MMULT(SUMIF(F:F,MID(A2,MMULT(INT(MOD(ROW($1:$729)-1,3^{7,6,5,4,3,2})/3^{6,5,4,3,2,1})+1,N(ROW($1:$6)<COLUMN(B:G))),INT(MOD(ROW($1:$729)-1,3^{6,5,4,3,2,1})/3^{5,4,3,2,1,0}+1)),G:G),10^{5;4;3;2;1;0}),0,),"[<111111]!0")),"0;;")
  (连等号 计254 字)

补充内容 (2014-4-2 12:36):

1. =TEXT(MAX((MMULT(INT(MOD(ROW($1:$729)-1,3^{6,5,4,3,2,1})/3^{5,4,3,2,1,0}+1),ROW($1:$6)^0)=LEN(A2))*TEXT(0&SUBSTITUTE(MMULT(SUMIF(F:F,MID(A2,MMULT(INT(MOD(ROW($1:$729)-1,3^{7,6,5,4,3,2})/3^{6,5,4,3,2,1})+1,N(ROW($1:$6)<COLUMN(B:G))),INT(MOD(ROW($1:$729)-1,3^{6,5,4,3,2,1})/3^{5,4,3,2,1,0}+1)),G:G),10^{5;4;3;2;1;0}),0,),"[<111111]!0")),"0;;")

复制代码

chunlin1wang

1. =TEXT(MIN(MMULT(IFERROR(MOD(MATCH(MID(A2&1,1+MMULT(MOD(INT(ROW(1:729)/3^COLUMN(A:F)*3),3)+1,1+(ROW($1:$6)<COLUMN(A:F)))-LEN(A2),MOD(ROW(1:729)/3^COLUMN(A:F)*3,3)+1),F:F,)-2,9)+1,10^6),10^{5;4;3;2;1;0})),"[>999999] ")

复制代码

xzk235

B2数组公式，291字符。
先贴个答案出来再慢慢研究。

1. =TEXT(SUM(TEXT(SUBSTITUTE(MMULT(SUMIF($F$2:$F$19,MID(A2,MMULT(INT(MOD((ROW($A$1:$A$729)-1)/3^{5,4,3,2,1,0},3))+1,N(ROW($A$1:$A$6)<COLUMN($A$1:$F$1)))+1,INT(MOD((ROW($A$1:$A$729)-1)/3^{5,4,3,2,1,0},3))+{1,1,1,1,1,5}),$G$2:$G$19),10^{5;4;3;2;1;0}),0,),"[>111110]0;!0;!0;!0")/3),"[>111110]0;;")

复制代码

补充内容 (2014-3-25 09:41):
262字符
=TEXT(MAX(--(0&SUBSTITUTE(MMULT(SUMIF($F$2:$F$19,MID(A2,MMULT(INT(MOD((ROW($A$1:$A$729)-1)/3^{5,4,3,2,1,0},3))+1,N(ROW($A$1:$A$6)<COLUMN($A$1:$F$1)))+1,MOD((ROW($A$1:$A$729)-1)/3^{5,4,3,2,1,0},3)+{1,1,1,1,1,5}),$G$2:$G$19),10^{5;4;3;2;1;0}),0,))),"[>111110]0;;")
才发现补充内容没帖完....

wlc5201130

先上个长的吧 376字符，在慢慢简化。
=TEXT(MAX(--(0&SUBSTITUTE(MMULT(IFERROR(N(OFFSET(G$1,MATCH(MID(A2,LOOKUP(--LEFT(0&MMULT((MOD(INT((ROW($1:729)-1)/3^(6-COLUMN(A:F))),3)+1)*10^{5,4,3,2,1,0},ROW($1:$6)^0),COLUMN(A:F)),ROW($1:333333)-1,MMULT(--(0&MID(ROW($1:333333),COLUMN(A:F),1)),ROW($1:$6)^0)),MOD(INT((ROW($1:729)-1)/3^(6-COLUMN(A:F))),3)+1),F$2:F$19,),)),)*10^{5,4,3,2,1,0},ROW($1:$6)^0),0,))),"[>111111]0;")
数组
测试等了15分钟。真漫长，估计是我电脑问题吧。
简化下333字符
=TEXT(MAX(--(0&SUBSTITUTE(MMULT(IFERROR(N(OFFSET(G$1,MATCH(MID(A2,LEN(SUBSTITUTE(SUBSTITUTE(LEFT(0&MMULT((MOD(INT((ROW($1:729)-1)/3^(6-COLUMN(A:F))),3)+1)*10^{5,4,3,2,1,0},ROW($1:$6)^0),COLUMN(A:F)),2,"11"),3,"111")),MOD(INT((ROW($1:729)-1)/3^(6-COLUMN(A:F))),3)+1),F$2:F$19,),)),)*10^{5,4,3,2,1,0},ROW($1:$6)^0),0,))),"[>111111]0;")

329字符
=TEXT(MAX(--(0&SUBSTITUTE(MMULT(IFERROR(N(OFFSET(G$1,MATCH(MID(A2,LEN(SUBSTITUTE(SUBSTITUTE(LEFT(0&MMULT((MOD(INT((ROW($1:729)-1)/3^(6-COLUMN(A:F))),3)+1)*10^{5,4,3,2,1,0},ROW($1:$6)^0),COLUMN(A:F)),2,"11"),3,"111")),MOD(INT((ROW($1:729)-1)/3^(6-COLUMN(A:F))),3)+1),F:F,)-1,)),)*10^{5,4,3,2,1,0},ROW($1:$6)^0),0,))),"[>99999]0;")

317字符
=TEXT(MAX(--(0&SUBSTITUTE(MMULT(IFERROR(N(OFFSET(G$1,MATCH(MID(A2,LEN(SUBSTITUTE(SUBSTITUTE(LEFT(0&MMULT((MOD(INT(ROW($2:729)/3^(6-COLUMN(A:F))),3)+1)*10^{5,4,3,2,1,0},ROW($1:$6)^0),COLUMN(A:F)),2,11),3,111)),MOD(INT(ROW($2:729)/3^(6-COLUMN(A:F))),3)+1),F:F,)-1,)),)*10^{5,4,3,2,1,0},ROW($1:$6)^0),0,))),"[>99999]0;")

以上公式情况好像都有些问题，假如是“我我我我我我我我”超过7的话，还会显示6位的编码。故修改下。
又变成了389字符了，运算速度还行。
=TEXT(MAX((0&SUBSTITUTE(MMULT(IFERROR(N(OFFSET(G$1,MATCH(MID(A2,LEN(SUBSTITUTE(SUBSTITUTE(LEFT(0&MMULT((MOD(INT(ROW($1:729)/3^(6-COLUMN(A:F))),3)+1)*10^{5,4,3,2,1,0},ROW($1:$6)^0),COLUMN(A:F)),2,11),3,111)),MOD(INT(ROW($1:729)/3^(6-COLUMN(A:F))),3)+1),F:F,)-1,)),)*10^{5,4,3,2,1,0},ROW($1:$6)^0),0,))*(MMULT((MOD(INT(ROW($1:729)/3^(6-COLUMN(A:F))),3)+1),ROW($1:$6)^0)=LEN(A2))),"[>1E5]0;")

简化  316字符，速度比较慢
=TEXT(MAX((0&SUBSTITUTE(MMULT(IFERROR(N(OFFSET(G$1,MATCH(MID(A2,LEN(SUBSTITUTE(SUBSTITUTE(LEFT(0&ROW($111111:333333),COLUMN(A:F)),2,11),3,111)),MID(ROW($111111:333333),COLUMN(A:F),1)),F:F,)-1,)),)*10^{5,4,3,2,1,0},ROW($1:$6)^0),0,))*(MMULT(--MID(ROW($111111:333333),COLUMN(A:F),1),ROW($1:$6)^0)=LEN(A2))),"[>1E5]0;")

308字符 速度慢
=TEXT(MAX((0&SUBSTITUTE(MMULT(IFERROR(N(OFFSET(G$1,MATCH(MID(A2,LEN(SUBSTITUTE(SUBSTITUTE(LEFT(0&ROW(1:333333),COLUMN(A:F)),2,11),3,111)),--(0&MID(ROW(1:333333),COLUMN(A:F),1))),F:F,)-1,)),)*10^{5,4,3,2,1,0},ROW($1:$6)^0),0,))*(MMULT(--(0&MID(ROW(1:333333),COLUMN(A:F),1)),ROW($1:$6)^0)=LEN(A2))),"[>1E5]0;")



293字符
=TEXT(MAX((0&SUBSTITUTE(MMULT(IFERROR(N(OFFSET(G$1,MATCH(MID(A2,LEN(SUBSTITUTE(SUBSTITUTE(LEFT(0&ROW(A:A),COLUMN(A:F)),2,11),3,111)),--(0&MID(ROW(A:A),COLUMN(A:F),1))),F:F,)-1,)),)*10^{5,4,3,2,1,0},ROW($1:$6)^0),0,))*(MMULT(--(0&MID(ROW(A:A),COLUMN(A:F),1)),ROW($1:$6)^0)=LEN(A2))),"[>1E5]0;")
数组
速度更慢。公式应该没有问题的，不过下拉时提示。“试图计算1个或多个公式时，Excel资源不足，因此，无法计算这些公式”，关键引用了ROW(A:A)的关系。我用的是2007版本，不知道漫步满足要求。不满足就以下面的300字符为准吧。



正好300字符
=TEXT(MAX((0&SUBSTITUTE(MMULT(IFERROR(N(OFFSET(G$1,MATCH(MID(A2,LEN(SUBSTITUTE(SUBSTITUTE(LEFT(0&ROW(1:333333),COLUMN(A:F)),2,11),3,111)),0&MID(ROW(1:333333),COLUMN(A:F),1)),F:F,)-1,)),)*10^{5,4,3,2,1,0},ROW(1:6)^0),0,))*(MMULT(--(0&MID(ROW(1:333333),COLUMN(A:F),1)),ROW(1:6)^0)=LEN(A2))),"[>1E5]0;")
数组下拉。

人事难料

允许用自定义函数吗

pxiceplay

B2数组=TEXT(MAX(--(0&SUBSTITUTE(MMULT(IFERROR(MOD(MATCH(MID(A2,MMULT(INT(MOD((ROW($1:$729)-1)/3^COLUMN($A:$F)*3,3))+1,--(COLUMN($A:$F)>ROW($1:$6)))+1,INT(MOD((ROW($1:$729)-1)/3^COLUMN($A:$F)*3,3))+{1,1,1,1,1,4}),F$2:F$19,)-1,9)+1,0),10^(6-ROW($1:$6))),"0",))),"[>99999]0;")

密码本密码不能大于3位，译文只能按题目1－9顺序排列，否则公式无效。

jsxjd

2003有7层嵌套限制，据说高版本要宽松得多，只好“盲打拼凑”了一个9层嵌套。本可再缩一层，可还差一层，只好放弃。
B2数组公式，下拉，296 Chars

1. =TEXT(MAX((MMULT(MOD(INT((ROW($1:$729)-1)/3^COLUMN(A:F)*3),3)+1,ROW($1:$6)^0)=LEN(A2))*(0&SUBSTITUTE(MMULT(SUMIF(F$2:F$19,MID(A2,1+MMULT(MOD(INT((ROW($1:$729)-1)/3^COLUMN(A:F)*3),3)+1,N(ROW($1:$6)>COLUMN(A:F))),MOD(INT((ROW($1:$729)-1)/3^COLUMN(A:F)*3),3)+1),G$2),10^ROW($1:$6)),0,))),"[>1e5]0;")

复制代码

烦请7版测试一下！

delete_007

敌人已知悉我方截获其电文，现已更改密码，电文已成废纸，尚未译出者无须再费神了。

=========答题截止时间到，先开贴，总结评分稍候=========

delete_007

评分建议如下，如有任何疑问或问题请直接与我联系。

---