求最长对称字符串（先求对称再求最长）的公式解释

donghan

测试题 求最长对称字符串（该题目同61期函数题目的要求有差异）


http://www.exceltip.net/post-8589-11056-lastpage.html


经过一段时间的答题，目前比较简洁的公式为（要考虑没有对称字符串的情况）
多单元格数组公式：
=INDEX(A:A,MMULT(INT(LARGE((MMULT(FIND(MID(A1:A8,COLUMN(A:BH),1),RIGHT(A1:A8,COLUMN(A:BH))&A1:A8),ROW(1:60)^0)=60)*LEN(A1:A8)-ROW()%,ROW()^{0,1,0})*10^{0,2,-2}+{2,0,1}),{100;-1;-100}))&""

现在我将该公式的思路解释一下：
1、取子字符串
MID(A1:A8,COLUMN(A:BH),1)
将每个字符串由左开始拆分成60个单个字符的字符数组，不足60个字符的时候，则 MID 返回空文本 ("")。
运行结果如下：
{"1","2","3","2","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","";"1","#","@","#","1","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","";"A","2","1","2","4","3","4","3","4","2","1","2","a","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","";"A","2","1","2","4","3","3","4","2","1","2","A","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","";"龙","隐","洞","里","洞","隐","龙","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","";"8","7","8","9","6","5","4","6","9","7","9","7","9","8","7","9","7","9","8","7","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","";"3","4","1","2","4","3","3","4","2","1","4","3","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","";"W","W","8","3","4","3","8","W","W","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""}

RIGHT(A1:A8,COLUMN(A:BH))
从每个字符串的右端开始取子字符串，字符串长度由1到60，不足60个字符的时候，则 RIGHT 返回所有文本。
运行结果如下：
{"1","21","321","2321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321","12321";"1","11","#11","@#11","[email=#@#11]#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11","1#@#11";"a","2a","12a","212a","4212a","34212a","434212a","3434212a","43434212a","243434212a","1243434212a","21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a","A21243434212a";"A","2A","12A","212A","4212A","34212A","334212A","4334212A","24334212A","124334212A","2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A","A2124334212A[/email]";"龙","隐龙","洞隐龙","里洞隐龙","洞里洞隐龙","隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙","龙隐洞里洞隐龙";"7","87","987","7987","97987","797987","8797987","98797987","798797987","9798797987","79798797987","979798797987","6979798797987","46979798797987","546979798797987","6546979798797987","96546979798797987","896546979798797987","7896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987","87896546979798797987";"3","43","143","2143","42143","342143","3342143","43342143","243342143","1243342143","41243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143","341243342143";"W","WW","8WW","38WW","438WW","3438WW","83438WW","W83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW","WW83438WW"}

2、确定每个字符是否是对称出现的
FIND(MID(A1:A8,COLUMN(A:BH),1),RIGHT(A1:A8,COLUMN(A:BH))&A1:A8)
如果FIND结果为1，说明该字符是对称出现的，否则不是；为了防止FIND时出现找不到值的情况，通过&A1:A8进行容错处理。
注意：MID(A1:A8,COLUMN(A:BH),1)结果是空文本 ("")时，则 FIND 会匹配搜索串中的首字符，即编号 1 的字符。

3、确定整个字符串是否为对称字符串
MMULT(FIND(MID(A1:A8,COLUMN(A:BH),1),RIGHT(A1:A8,COLUMN(A:BH))&A1:A8),ROW(1:60)^0)=60
因为如果为对称字符串，每个字符FIND的结果都为1，即有60个1，通过MMULT求和后，等于60的即为对称字符串。
运行结果如下：
{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}

4、求出对称字符串的长度
(MMULT(FIND(MID(A1:A8,COLUMN(A:BH),1),RIGHT(A1:A8,COLUMN(A:BH))&A1:A8),ROW(1:60)^0)=60)*LEN(A1:A8)
非对称字符串的长度结果为0，运行结果如下：
{5;0;0;12;7;0;12;9}

5、加入位置信息
(MMULT(FIND(MID(A1:A8,COLUMN(A:BH),1),RIGHT(A1:A8,COLUMN(A:BH))&A1:A8),ROW(1:60)^0)=60)*LEN(A1:A8)-ROW(1:8)%
运行结果如下：
{4.99;-0.02;-0.03;11.96;6.95;-0.06;11.93;8.92}

6、分别求出带位置信息的最长的字符串和从长到短第1~8个字符串的长度,为了处理当没有对称字符串时的情况，增加第三个数字信息进行对称判定
LARGE((MMULT(FIND(MID(A1:A8,COLUMN(A:BH),1),RIGHT(A1:A8,COLUMN(A:BH))&A1:A8),ROW(1:60)^0)=60)*LEN(A1:A8)-ROW(1:8)%,ROW(1:8)^{0,1,0})
运行结果如下：
{11.96,11.96,11.96;11.96,11.93,11.96;11.96,8.92,11.96;11.96,6.95,11.96;11.96,4.99,11.96;11.96,-0.02,11.96;11.96,-0.03,11.96;11.96,-0.06,11.96}

7、通过数值处理，去掉最长字符串的位置信息，然后将数值增加2后扩大100倍，将带位置信息的第1~8个字符串的长度扩大-100倍,第三个数值当有对称字符串时取整后为1，没有时为0，然后扩大-100倍。
INT(LARGE((MMULT(FIND(MID(A1:A8,COLUMN(A:BH),1),RIGHT(A1:A8,COLUMN(A:BH))&A1:A8),ROW(1:60)^0)=60)*LEN(A1:A8)-ROW()%,ROW()^{0,1,0})*10^{0,2,-2}+{2,0,1})*{100,-1,-100}
运行结果如下：
{1300,-1196,-100;1300,-1193,-100;1300,-892,-100;1300,-695,-100;1300,-499,-100;1300,2,-100;1300,3,-100;1300,6,-100}

8、通过MMULT分别求和，得出最长的对称字符串的位置信息（最长的对称字符串的位置信息在1：8范围内，非最长的对称字符串的位置信息在100以后）。
MMULT(INT(LARGE((MMULT(FIND(MID(A1:A8,COLUMN(A:BH),1),RIGHT(A1:A8,COLUMN(A:BH))&A1:A8),ROW(1:60)^0)=60)*LEN(A1:A8)-ROW(1:8)%,ROW(1:8)^{0,1,0})*10^{0,2,-2}+{2,0,1})*{100,-1,-100},{1;1;1})
运行结果如下：
{4;7;308;505;701;1202;1203;1206}

7~8步骤可以简化如下：
MMULT(INT(LARGE((MMULT(FIND(MID(A1:A8,COLUMN(A:BH),1),RIGHT(A1:A8,COLUMN(A:BH))&A1:A8),ROW(1:60)^0)=60)*LEN(A1:A8)-ROW(1:8)%,ROW(1:8)^{0,1,0})*10^{0,2,-2}+{2,0,1}),{100;-1;-100})

9、通过INDEX函数返回最长的对称字符串，&""对0值进行处理，使显示为空。
=INDEX(A:A,MMULT(INT(LARGE((MMULT(FIND(MID(A1:A8,COLUMN(A:BH),1),RIGHT(A1:A8,COLUMN(A:BH))&A1:A8),ROW(1:60)^0)=60)*LEN(A1:A8)-ROW(1:8)%,ROW(1:8)^{0,1,0})*10^{0,2,-2}+{2,0,1}),{100;-1;-100}))&""

最后把ROW(1:8)简化为ROW().

其它公式：
1、wangg913 的数组公式：
=INDIRECT("a"&SUM(INT(LARGE((MMULT((CODE(RIGHT(1&A$1:A$8&1,COLUMN(A:U)))-CODE(MID(1&A$1:A$8,COLUMN(A:U),1)&1))^2,ROW(1:21))=0)*LEN(A$1:A$8)-ROW($1:$8)%,ROW()^{0,1,0})*10^{0,2,-2}+{2,0,1})*{100,-1,-100}))&""
下拉
2、冻豆腐 的数组公式：
=INDIRECT("A"&RIGHT(9*10^6-MMULT(LARGE((MMULT(1-(CODE(MID(0&A$1:A$8&0,COLUMN(A:BJ),1)&0)=CODE(RIGHT(0&A$1:A$8&0,COLUMN(A:BJ)))),ROW(4:65))=0)*LEN(A$1:A$8&0)/1%-ROW($1:$8),ROW()^{0,1,1}),{10;-10;1}^5),2))&""
下拉

3、我简化的另一个数组公式：
=INDIRECT("a"&SUM(INT(LARGE((MMULT(FIND(MID(A$1:A$8,COLUMN(A:BH),1),RIGHT(A$1:A$8,COLUMN(A:BH))&A$1:A$8),ROW(1:60)^0)=60)*LEN(A$1:A$8)-ROW($1:$8)%,ROW()^{0,1,0})*10^{0,2,-2}+{2,0,1})*{100,-1,-100}))&""
下拉
本题目公式得到了wangg913 兄的大力支持才能最后简化至187字符，在此表示感谢

[ 本帖最后由 donghan 于 2010-7-19 17:29 编辑 ]

donghan

发现公式有问题或者有新思路新解法的可在此跟帖

wangg913

你的公式，如果数据源区域没有对称字符串试试？
只有两维数组应该不能判定，应该必须是三维。
即：
第一列是 最大值  large(,1)
第二列   large(,{1;2;3;4;5;6;7;8})
第三列判断是否有对称字串.

[ 本帖最后由 wangg913 于 2010-7-17 16:00 编辑 ]

donghan

还是大头厉害，一眼就看出问题来了，我还真是忘记考虑没有对称的时候了，有时间再想想
目前看来至少用三个数字信息才能解决，公式已更新

[ 本帖最后由 donghan 于 2010-7-19 09:18 编辑 ]

tmd068

头都看大了，还是没得结果；大头一看，马上就有结果了。佩服佩服！