［练习题］补出缺失的数字

楼主 fangjianp	补出缺失的数字说明： 1、A2:G14是数据区域，以行为一组，每组最多有7个0——9的一位数 2、每组内的数可能有重复 3、找出该组内没有的数，如模拟答案填入依次I2:I14 4、如该组没有的数为2、5、4、6、0，则必须由小到大（增序）排列02456填入D列内要求： 1、不准用辅助列，VBA，定义名称 2、用函数解题，填入I2，然后下拉 3、Excel2003需通过 4、只能引用A2:G14内的数据结果正确，得技能分３分，答案精彩再加２分截至时间２０１１年６月５日补出缺失的数字.rar
2楼 Zaezhong	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("0123456789",A2,),B2,),C2,),D2,),E2,),F2,),G2,) =RIGHT(0&SUBSTITUTE(SUM((COUNTIF(A2:G2,COLUMN(A:J)-1)=0)(COLUMN(A:J)-1)10^(10-COLUMN(A:J))),0,),10-COUNT(0/COUNTIF(A2:G2,COLUMN(A:J)-1)))
3楼 laowuwyx	=IF(COUNTIF(A2:G2,0),"",0)&SUBSTITUTE(SUM(ISNA(MATCH(ROW($1:$9),A2:G2,))ROW($1:$9)10^(10-ROW($1:$9))),0,) 数组，传统解法。
4楼 sharprain	=IF(COUNTIF(A2:G2,0),"",0)&SUBSTITUTE(SUM((MMULT(N(ROW($1:$10)-1=$A2:$G2),ROW($1:$7)^0)=0)(ROW($1:$10)-1)10^(10-ROW($1:$10))),"0","") 数组公式下拉
5楼 fangjianp	我的预设解： =LEFT(0,MIN(A2:G2))&SUBSTITUTE(SUM((COUNTIF(A2:G2,ROW($1:$9))=0)ROW($1:$9)10^(9-ROW($1:$9))),0,) 公式长度：97
6楼 shyudo	=IF(ISNA(HLOOKUP(0,A2:G2,1,0)),"0","")&IF(ISNA(HLOOKUP(1,A2:G2,1,0)),"1","")&IF(ISNA(HLOOKUP(2,A2:G2,1,0)),"2","")&IF(ISNA(HLOOKUP(3,A2:G2,1,0)),"3","")&IF(ISNA(HLOOKUP(4,A2:G2,1,0)),"4","")&IF(ISNA(HLOOKUP(5,A2:G2,1,0)),"5","")&IF(ISNA(HLOOKUP(6,A2:G2,1,0)),"6","")&IF(ISNA(HLOOKUP(7,A2:G2,1,0)),"7","")&IF(ISNA(HLOOKUP(8,A2:G2,1,0)),"8","")&IF(ISNA(HLOOKUP(9,A2:G2,1,0)),"9","")
7楼 bluexuemei	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("0123456789",A2,),B2,),C2,),D2,),E2,),F2,),G2,)下拉
8楼落雪绽菊	{=LEFT(TEXT(SUMPRODUCT(IF(SMALL(--(COUNTIF(A2:G2,ROW($1:$10)-1)&(ROW($1:$10)-1)),ROW($1:$10))>9,0,SMALL(--(COUNTIF(A2:G2,ROW($1:$10)-1)&(ROW($1:$10)-1)),ROW($1:$10))),10^(10-ROW($1:$10))),REPT("0",10)),FREQUENCY(--(COUNTIF(A2:G2,ROW($1:$10)-1)&(ROW($1:$10)-1)),9))} 补出缺失的数字_交卷.rar
9楼 piny	108字元 =IF(COUNTIF(A2:G2,0),,0)&SUBSTITUTE(SUM(ISERR(0/COUNTIF(A2:G2,ROW($1:$9)))ROW($1:$9)10^(9-ROW($1:$9))),0,) 132字元 =IF(COUNTIF(A2:G2,0),,0)&SUBSTITUTE(SUM((MMULT(--ISERR(FIND(ROW($1:$9),A2:G2)),{1;1;1;1;1;1;1})=7)ROW($1:$9)10^(9-ROW($1:$9))),0,)
10楼 Violet_Universe	=IF(ISNA(MATCH(0,A2:G2,)),0,)&SUBSTITUTE(SUM(IF(ISNA(MATCH(ROW($1:$10)-1,A2:G2,)),ROW($1:$10)-1)*10^(11-ROW($1:$10))),0,)
11楼 XIAO_JUN	数组公式
12楼 XIAO_JUN	数组公式
13楼 liuguansky	I2 =IF(COUNTIF(A2:G2,0),,0)&SUBSTITUTE(MID(SUM((COUNTIF(A2:G2,ROW($1:$9))=0)*ROW($1:$9)/10^ROW($1:$9)),3,9),0,)
14楼健康快乐123	补出缺失的数字.rar
15楼 wqy88	补出缺失的数字2.zip 如果能将数组的值一次性取出就好了。终于找到解决办法了： I2=TEXT(SUM(MID("0123456789",LARGE(ISNA(MATCH(COLUMN(A:J)-1,A2:G2,0))(COLUMN(A:J)-1),COLUMN(A:J))+1,1)10^COLUMN(A:J))/10,REPT(0,SUM(ISNA(MATCH(COLUMN(A:J)-1,A2:G2,0))*1))) 数组公式 CTRL+SHIFT+ENTER 往下拉即可。
16楼 wjc2090742	似乎是比较成熟的题目了，写个长的。 =IF(AND(A2:G2),0,)&SUBSTITUTE(SUM(IF(COUNTIF(A2:G2,ROW($1:$9)),,ROW($1:$9))10^(9-ROW($1:$9))),0,) =IF(AND(A2:G2),0,)&SUBSTITUTE(123456789-SUM(A2:G2/COUNTIF(A2:G2,A2:G2&"")10^(9-A2:G2)),0,) =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("0123456789",A2,),B2,),C2,),D2,),E2,),F2,),G2,)
17楼 w83365040371	=MID(SUM(TEXT(SMALL(IF(COUNTIF(A2:G2,ROW($1:$10)-1),10,ROW($1:$10)-1),ROW($1:$10)),"[>9]!0;0")/10^ROW($1:$10)),3,10)
18楼涅磐86970	IF(AND(A2:G2),0,)&SUBSTITUTE(SUM(ISNA(MATCH(ROW($1:$9),A2:G2,))ROW($1:$9)10^(9-ROW($1:$9))),0,)
19楼 fangjianp	今日魅力值加完,明日再加
20楼 wcymiss	受老师委托，代为点评。这是一道比较老的题目了，答题的人也相对较多，而且因为题目附有模拟结果，参与答题的人都给出了正确的公式，只是繁简不同而已。公式1： =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("0123456789",A2,),B2,),C2,),D2,),E2,),F2,),G2,) 这个思路通俗易懂，但缺乏扩展性。公式长度125。公式2： =IF(AND(A2:G2),0,)&SUBSTITUTE(SUM((COUNTIF(A2:G2,ROW($1:$9))=0)ROW($1:$9)10^(9-ROW($1:$9))),0,) 相对比较成熟的定式了，公式分两部分，一部分是判断是否加0，一部分是先用countif过滤不符合条件的数字（0不考虑），10^及sum后，用substitute去0，两部分合成后即为公式结果。回帖的*部分用的都是这个思路，细节处理上稍有不同。本公式长度97。公式3： =IF(AND(A2:G2),0,)&SUBSTITUTE(123456789-SUM(A2:G2/COUNTIF(A2:G2,A2:G2&"")10^(9-A2:G2)),0,) 这个公式很少见，但思路非常棒。A2:G2/COUNTIF(A2:G2,A2:G2&"")的处理是为了去重，重复的数字，被按照重复次数来均分了，之后用10^及sum形成数值，用123456789来减。其余处理同公式2。公式长度91，为本题最短公式。
21楼 wcymiss	请版主对11楼补评技术分。