[第64期]Excel-单一储存格寻找三连或四连

piny · 发表于 2010-7-26 06:25

要求
A1至A9000为1000至9999之四位数数字
定义连续数字：0123456789
若其中有三连数字，则返回其连续部分，位置顺序依旧
若其中有四连数字，则返回其本身
若其中仅一连数字或二连数字，则返回空白
若三连数字有一数重复，仅取其中任意一位
返回数为文字型态或数字型态皆可

举例
1235，返回123
2341，返回2341
2013，返回2013
1902，返回102
1012，返回012（或返回102）
B列为参考答案，三连数字若有重复值，其C列亦可为答案（任择一种为答案即可）

请在D1设计公式
公式可下拉
无VBA
无定义名称
无辅助格使用
请用2003版认识的函数
F1之值可方便验算
目前公式最短纪录，个人为2xx字元，谨与各位分享 ^^

第一次发题如有不符版规，烦请告知，很高兴在EH论坛遇到一群解题成痴，热心不落人后的朋友 ^^

奖赏1：财富30分&技术1分

若符合题意所有要求
1.仅在D1输入公式，公式可下拉
2.无VBA
3.无定义名称
4.无辅助格使用
5.请用2003版认识的函数
6.9000格需完全正确（即四连数返回本身，三连数返回其任意三连之数，惟顺序需依旧，二连数及一连数返回空白）
7.字元数不拘

奖赏2：符合奖赏1之情况，且字元数小于210：财富40分&技术2分

奖赏3：符合奖赏1之情况，且字元数小于180：财富50分&技术3分

奖赏1至3只能领取其中一种！

第一次出题，如果题意不明或有任何指教，烦请发短信告知！尚需请教要如何给正解者奖赏！

回复终止日：8月22日（之后回复者不给予奖赏，感谢各位参与！）

今日将结题，请参赛者勿再修正自己的意见，俾利小弟改考卷，烦请版主解除高亮，若参赛者仍想发抒自己的意见或想法，烦请另行回复，惟不列入评比，解答版目前制作中，谢谢！

恭禧所有参赛者全部符合题意，分别给予下述奖赏，详解请看附件（尚有疏漏将尽速补强）

奖赏1：财富30分&技术1分
sunya_0529
zhanhewang
gdfcx
fugb-2010
泓()

奖赏2：财富40分&技术2分
dengyf
涅磐86970
wjh619993018
sunbin200388
wjc2090742
kkitt
fjrlgj

奖赏3：财富50分&技术3分
wangg913
冻豆腐
东方之珠0

[ 本帖最后由冻豆腐于 2010-8-23 08:28 编辑 ]

dengyf · 发表于 2010-7-27 09:27

=IF(LARGE(FREQUENCY(IF(ISERR(FIND(ROW($1:$10)-1,A1)),,ROW($1:$10)),IF(ISERR(FIND(ROW($1:$10)-1,A1)),ROW($1:$10),)),2)>2,SUBSTITUTE(LEFT(SUM((0&MID(A1,LARGE(FIND(ROW($1:$10)-1,A1&5^19),ROW($1:$10)),1))*10^ROW($1:$10)),COUNT(FIND(ROW($1:$10)-1,A1))),MAX((FREQUENCY(IF(ISERR(FIND(ROW($1:$10)-1,A1)),,ROW($1:$10)),IF(ISERR(FIND(ROW($1:$10)-1,A1)),ROW($1:$10),))=1)*(ROW($1:$11)-1)-1),""),"")

被函数七层搞死了，只能铺开来了，近390字符了，汗死！！
精减一下
=IF(LARGE(FREQUENCY(ISNUMBER(FIND(ROW($1:$10)-1,A1))*ROW($1:$10),ISERR(FIND(ROW($1:$10)-1,A1))*ROW($1:$10)),2)>2,SUBSTITUTE(A1,MAX(IF(MMULT(SMALL(--MID(A1,{1;2;3;4},1),{1;2;3}+{1,0}),{1;-1})=1,-1,SMALL(--MID(A1,{1;2;3;4},1),{1;2;4}))),"",1),"")
二百四十多个字符

一天搞一个公式，现在公式把原来的思路全部舍弃了。
=SUBSTITUTE(A1,MIN(IF({3,3,2,2}=MMULT({1,1,1},N(MMULT(SMALL(--MID(A1,{1;2;3;4},1),{1;2;3}+{1,0}),{1;-1})={1,1,1,9;1,0,1,1;1,1,9,1})),SMALL(-TEXT(MID(A1&"a",{1;2;3;4;5},1),"-0;;0;!1"),{1,3,5,2}),A1)),"",1)
二百十个以内了

=SUBSTITUTE(A1,MIN(IF({3,3,2,2}=MMULT({1,1,1},N(MMULT(LARGE(-MID(A1,{1;2;3;4},1),{1;2;3}+{1,0}),{-1;1})={1,1,1,9;1,0,1,1;1,1,9,1})),SMALL(-TEXT(MID(A1,ROW($1:$5),1),"-0;;0;!1"),{1,3,5,2}),A1)),,1)

[ 本帖最后由 dengyf 于 2010-7-30 12:58 编辑 ]

涅磐86970 · 发表于 2010-7-27 13:57

TEXT(IF(COUNT(FIND(MIN(--MID(A1,{1,2,3,4},1))+{1,2,3},A1))=3,A1,MAX((REPLACE(A1,{1,2,3,4},1,)=TEXT(SMALL({99,90,-9,9,-99,-90}+111*ROW($1:$8),ROW($1:$48)),"000"))*REPLACE(A1,{1,2,3,4},1,))),"000;;")
197

[ 本帖最后由涅磐86970 于 2010-7-31 08:37 编辑 ]

sunya_0529 · 发表于 2010-7-27 12:05

好久没做竞赛题了，参与一下，233个字符，数组公式：

=IF(DEVSQ(-MID(A1,{1,2,3,4},1))=5,A1,IF(DEVSQ(-MID(A1,{1,2,3},1))=2,LEFT(A1,3),IF(DEVSQ(-MID(A1,{2,3,4},1))=2,RIGHT(A1,3),IF(DEVSQ(-MID(A1,{1,2,4},1))=2,LEFT(A1,2)&RIGHT(A1),IF(DEVSQ(-MID(A1,{1,3,4},1))=2,LEFT(A1)&RIGHT(A1,2),"")))))

复制代码

另一个思路，236字符，数组公式：

=IF(DEVSQ(-MID(A2,{1,2,3,4},1))=5,A2,INDEX(TEXT(REPLACE(" "&A2,ROW($1:$5),1,""),"[<1000]000;"),MAX((MMULT((MID(REPLACE(A2,{1;2;3;4},1,""),{1,2,3},1)+MMULT(-MID(REPLACE(A2,{1;2;3;4},1,""),{1,2,3},1),{1;1;1})/3)^2,{1;1;1})=2)*{2;3;4;5})))

复制代码

继续期待更精彩的解法~~

[ 本帖最后由 sunya_0529 于 2010-7-31 16:33 编辑 ]

wangg913 · 发表于 2010-7-27 15:57

=IF(COUNT(FIND(1111,SUM(10^MID(A1,{1,2,3,4},1)))),A1,IF(COUNT(FIND(111+{0,1,10,100},SUM(10^MID(A1,{1,2,3,4},1)))),SUBSTITUTE(A1,-LOOKUP(,{3,4}-FIND({"010",2},SUM(10^-MID(A1,{1,2,3,4},1))%&0)),,1),""))

裤子累掉了，不知合不合格？长度200。
==============================================================
简化一下（显示的全是文本数字，应该也是合规的），长度158。
=IF(COUNT(FIND(111+{0,1,10,100},SUM(10^MID(A1,{1,2,3,4},1)))),SUBSTITUTE(A1,-LOOKUP(,{3,4,-9999}-FIND({"010",2,1111},SUM(10^-MID(A1,{1,2,3,4},1))%&0)),,1),"")

[ 本帖最后由 wangg913 于 2010-7-28 12:43 编辑 ]

冻豆腐 · 发表于 2010-7-28 22:41

好题目，参与一下。
=IF(MAX(MMULT(-ISERR(FIND(ROW($2:$9)-{2,1,0},A1)),{1;1;1})),"",SUBSTITUTE(A1,MAX((MMULT(LEN(SUBSTITUTE(A1,MOD(ROW($2:$11)-{1,2,0},11)-1,)),{9;1;1})={24,25,35})*ROW($1:$10))-1,,1))

[ 本帖最后由冻豆腐于 2010-7-29 22:24 编辑 ]

wjh619993018 · 发表于 2010-7-29 14:39

=LOOKUP(SUM((MMULT({1,1},LARGE(1+MID(A1,{1;2;3;4},1),{1,2,3;2,3,4})*{1;-1})={1;0})*{2,6,4;0,1,0}),{0;7;8;10;12},CHOOSE({1;2;2;2;3},"",SUBSTITUTE(A1,LARGE(--MID(A1,{1;2;3;4},1),{1;2;4;1}),,1),A1))

[ 本帖最后由 wjh619993018 于 2010-7-30 11:14 编辑 ]

sunbin200388 · 发表于 2010-7-30 19:39

先用个最笨的办法实现，超长马拉松，后面再想办法简化或者其它思路。
=IF(SUM(--(LARGE(--MID(A1,ROW($1:$4),1),ROW($1:$3))-LARGE(--MID(A1,ROW($1:$4),1),ROW($2:$4))=1))>1,CHOOSE(MATCH(SUM((--(LARGE(--MID(A1,ROW($1:$4),1),ROW($1:$3))-LARGE(--MID(A1,ROW($1:$4),1),ROW($2:$4))=1))*10^{0;1;2}),{11,101,110,111}),SUBSTITUTE(A1,MIN(--MID(A1,ROW($1:$4),1)),"",1),IF(LARGE(--MID(A1,ROW($1:$4),1),2)=LARGE(--MID(A1,ROW($1:$4),1),3),SUBSTITUTE(A1,SMALL(--MID(A1,ROW($1:$4),1),2),"",1),""),SUBSTITUTE(A1,MAX(--MID(A1,ROW($1:$4),1)),"",1),A1),"")

花了点时间想了下，终于将公式缩减到210个字符以下：
=IF(DEVSQ(-MID(A1,{1;2;3;4},1))=5,A1,IF(ISERR(FIND(111,SUM((FIND(ROW($1:$10)-1,A1&5^19)<5)*10^ROW($1:$10)))),"",REPLACE(A1,MATCH(6,MATCH(MOD(REPLACE(A1,{1;2;3;4},1,""),111),{12,21,102,9,90,99},)),1,"")))

=IF(DEVSQ(-MID(A1,{1;2;3;4},1))=5,A1,IF(ISERR(FIND(111,SUM(ISNUMBER(FIND(ROW($1:$10)-1,A1))*10^ROW($1:$10)))),"",REPLACE(A1,MATCH(6,MATCH(MOD(REPLACE(A1,{1;2;3;4},1,""),111),{12,21,102,9,90,99},)),1,"")))
两个公式思路都差不多，只是在判断是否存在连续数的方法上略有不同，不知道对不对。

[ 本帖最后由 sunbin200388 于 2010-8-3 20:17 编辑 ]

zhanhewang · 发表于 2010-7-30 22:54

长时间没空回家。今见此题，试做一个。
267个字符=IF(ISERR(FIND(111,SUM(ISNUMBER(FIND(ROW($1:$10)-1,A1))*10^ROW($1:$10)))),"",MIN(--REPLACE(0&A1,(FIND(MID(A1,{1,2,3,4},1),A1)<>{1,2,3,4}+(ABS(MID(A1,{1,2,3,4},1)-10.5+FIND(111,10^12+SUM(ISNUMBER(FIND({0;1;2;3;4;5;6;7;8;9},A1))*10^ROW($1:$10))))>1.5))*{1,2,3,4}+1,1,)))

gdfcx · 发表于 2010-8-2 12:50

太长了，545字符：
=IF(MAX(MMULT(N(ISNUMBER(FIND({0,1,2,3}+ROW($1:$7)-1,A1))),{1;1;1;1}))=4,A1,"")&IF((MAX(MMULT(N(ISNUMBER(FIND({0,1,2,3}+ROW($1:$7)-1,A1))),{1;1;1;1}))=3)*(MAX(MMULT(N(ISNUMBER(FIND({0,1,2}+ROW($1:$8)-1,A1))),{1;1;1}))=3),IF(OR(MMULT(N(MID(A1,{1;2;3;4},1)=MID(A1,{1,2,3,4},1)),{1;1;1;1})=2),SUBSTITUTE(A1,MAX(IF(MMULT(N(MID(A1,{1;2;3;4},1)=MID(A1,{1,2,3,4},1)),{1;1;1;1})=2,--MID(A1,{1;2;3;4},1))),"",2),SUBSTITUTE(A1,LOOKUP(1,0/(MMULT(--ISNUMBER(MATCH(MID(A1,{1;2;3;4},1)+{1,-1},--MID(A1,{1;2;3;4},1),)),{1;1})=0),--MID(A1,{1;2;3;4},1)),"")),"")

		自动登录	找回密码
密码			免费注册

[Excel 函数与公式] [第64期]Excel-单一储存格寻找三连或四连

本帖子中包含更多资源

评分

评分

评分

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

评分

本帖子中包含更多资源

评分

本帖子中包含更多资源

评分

浏览过的版块