| 楼主 donghan |
测试题 求最长对称字符串（要考虑没有对称字符串的情况） 经过一段时间的答题，目前比较简洁的公式为 多单元格数组公式： =INDEX(A:A,MMULT(INT(LARGE((MMULT(FIND(MID(A1:A8,COLUMN(A:BG),1),RIGHT(A1:A8,COLUMN(A:BG))&A1:A8),ROW(1:59)^0)=59)*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字符，在此表示感谢 |
| 2楼 wenshui2006 |
 太强了,,第二步的运行结果都可以显示出来,,,牛,,, |
| 3楼 mjgdxx |
虽然看不懂，但还是留个脚印 |
| 4楼 donghan |
运行结果是用07版显示的，03版不能显示这么多字符 |