ExcelTip.Net留存知识帖 ---【注:附件之前被网盘供应商清空后,现已修复-现已修复-现已修复为本地下载!】
现在位置:首页 > 我的酷贴 > 函数与公式 > 求最长对称字符串 公式解释

求最长对称字符串 公式解释

作者:绿色风 分类: 时间:2022-08-18 浏览:100
楼主
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版不能显示这么多字符

免责声明

有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素, 经与ExcelTip.Net站长Apolloh商议并征得其同意, 现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示, 供有需要的人士查询使用,也慰缅曾经的论坛时代。 所示各个帖子的原作者如对版权有异议, 可与本人沟通提出,或于本站点留言,我们会尽快处理。 在此,感谢ExcelTip.Net站长Apolloh的支持,感谢本站点所有人**绿色风(QQ:79664738)**的支持与奉献,特此鸣谢!
------本人网名**KevinChengCW(QQ:1210618015)**原ExcelTip.Net总版主之一

评论列表
sitemap