本帖最后由 hjj0451 于 2012-8-29 16:53 编辑
感谢Wangg913版主采用本题，题目难度中等，关键是方法。
常常我们会碰到需要=index(ref_1，SMALL(IF(x=f(x),row(...),4^8),row(a1)))&""来解决的问题，对于这种常见的问题一般都采用了常规方法。一旦内存数组x是通过计算得到的并且长度很长(见8楼、10楼、16楼公式)，这样的公式无论从效率上还是从美学上都不是一个好公式。学习或参加本次竞赛，你将不再需要写这种公式。在这里,得到x一般不是难点，但如何直接通过x内部数量关系直接提炼出row(…)，而不再调用一次x？
一、主要解题能力及精彩部分提示：
依次把最大与非最大进行比较处理后得到所需要的行信息。
3\11\19楼的TRUNC、4楼的ROUNDUP以及6&-1日期、6楼的IF(COLUMN(A:S)>1,99,ROW(1:99))、6\18楼的MOD(负数,100)、9\12\16\19楼的TEXT处理方法。主要考察：
1.数组熟练运用及加权处理能力,主要是细节技巧处理。
2.数学函数TRUNC、ROUNDUP、MOD、ROUND、INT以及TEXT的综合灵活应用。
二、解体过程及思路分析：
step 1:取得A1:A19各行的偶数字符"个数":
这一步比较简单，思路都类似了，用MMULT取出,不同的是有的方法直接求个数，有的方法是间接反应个数，但其数量级与需要用的行信息无关。得到的内存数组计为MMULT(a)，其最大值可能不止一个，最后的目的是取得其中最大数字的行位置。
主要有：
MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1))+1,ROW(1:15)^0) (3/4/18楼)
MMULT(MOD(1&MID(A1:A19,COLUMN(A:O),1),2),ROW(1:15)^0)
MMULT(N(-1^(1&MID(A$1:A19,COLUMN(A:O),1))=1),ROW(1:15)^0)
MMULT(1-MOD(1&MID(A1:A19,COLUMN(A:O),1),2),ROW(1:15)^0) (6/12/14楼)
MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1)),ROW(1:15)^0) (11/19/20楼,非1即-1)
step 2:对MMULT(a)和行号进行加权处理:
为了在后面提取必要的行号信息，先要把MMULT(a)与行号糅合，并且最后还能分离，必须加权处理。
要么是赋予MMULT(a)比行号大个级别的权重，要么是小个级别的权重。本题原始数据最多19行，权重级别选为*100(即/1%)或/100(即%)就行了，以区域数组为例，主要有：
MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1))+1,ROW(1:15)^0)/1%-ROW() (3/4楼)
MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1))+1,ROW(1:15)^0)*50-ROW() (4楼)
MMULT(1-MOD(1&MID(A1:A19,COLUMN(A:O),1),2),ROW(1:15)^0)/1%-IF(COLUMN(A:S)>1,99,ROW(1:19)) (6楼)
ROW($1:$19)%-MMULT(1-MOD(1&MID(A$1:A$19,COLUMN(A:O),1),2),ROW(1:15)^0) (12楼)
MMULT(1-MOD(1&MID(A1:A19,COLUMN(A:O),1),2),ROW(1:15)^0)-ROW(1:19)% (14楼)
MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1)),ROW(1:15)^0)/1%-ROW(1:19) (19楼)
4楼：由于-1^(1&MID(A1:A19,COLUMN(A:O),1))+1要么是0要么是2，法2比法1都统一少加了100的整数倍，要提取的行信息在后面的2位，没有影响。 6楼：针对IF(COLUMN(A:S)>1,99,ROW())，扩展得到19列，第1列为行号，第2-19列都是99。
为什么要扩展19列，IF({0,1,1,1},99,ROW())为什么不行，来分析一下。
后面会知道公式必须在含最多偶数字符的行取值，否则会出错。
假设A1:A19含最多偶数字符的有M个，此公式的IF公式N列，则M*N>=19(其中M∈[0,19],整数)。
假设A1:A19全空或全奇,MMULT(a)都为0，M为0,此时区域数组取第1到19最大值都在第1列取值，此时扩展多少列都没关系，由于MOD(-1,100)=99,MOD(-2,100)=98,公式容错……
其余情况M>=1,由M*N>=19得到N>=19即可满足要求，即取COLUMN(A:S)。
12楼：与3、4楼的方法相反，反过来减，得到一序列负数，其个位反映含偶字符的大小，小数点后第1-2位反映其所在行信息。
以上公式的说明中，若采取一个单元格数组公式下拉的方式，需要把row()改为row($1:$19)，下同。
step 3:取出MMULT(a)中的多个值组合以便下一步比较处理剥离出MMULT(a)中的最大值的行号信息:
有LARGE法和SMALL法。
这里主要采取依次提取其中第1-1大、第1-2大、第1-3大、第1-4大……的值 (4楼)
根据需要也可提取其中第1-1大、第2-1大、第3-1大、第4-1大……的值 (3楼)
或者第1-1-1大、第1-2-1大、第1-3-1大、第1-4-1大……的值
如果取负后，则是1-1小、第2-1小、第3-1小、第4-1小……的值 (12楼)
值得一提的是6楼的处理，只需依次提取其中第1大、第2大、第3大、第4大……的值，它们都位于含偶字符最多的行对应的1-19列。
LARGE或SMALL第2参数利用ROW(A1)^{0,1}下拉或row()^{1,0}区域数组来得到，主要有：
LARGE(MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1))+1,ROW(1:15)^0)/1%-ROW(),ROW()^{1,0}) (3楼、4楼)
LARGE(MMULT(N(-1^(1&MID(A$1:A19,COLUMN(A:O),1))=1),ROW(1:15)^0)-ROW()%,ROW()^{0,1,0})
LARGE(MMULT(1-MOD(1&MID(A1:A19,COLUMN(A:O),1),2),ROW(1:15)^0)/1%-IF(COLUMN(A:S)>1,99,ROW()),ROW()) (6楼)
SMALL(ROW($1:$19)%-MMULT(1-MOD(1&MID(A$1:A$19,COLUMN(A:O),1),2),ROW(1:15)^0),ROW(A1)^{1,0}) (12楼)
LARGE(MMULT(1-MOD(1&MID(A1:A19,COLUMN(A:O),1),2),ROW(1:15)^0)-ROW(1:19)%,ROW(1:19)^{0,1}) (14楼)
LARGE(MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1)),ROW(1:15)^0)/1%-ROW(1:19),ROW(1:19)^{0,1}) (19楼)
具体结果见上面的分步单元格解析。
step 4:对上面得到的信息进行数学舍入处理，主要是针对最高位，然后两个数相减取行号。
6楼公式不再需要相减这一步。
这里主要有TRUNC法、ROUNDUP法、ROUND法、INT法、ABS法、MOD法以及TEXT法，主要是利用TEXT的强制赋值(4楼、12楼、14楼)、舍入取整(12楼、14楼)或去负号(12楼)功能。
3楼：TRUNC(LARGE(MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1))+1,ROW(1:15)^0)/1%-ROW(),ROW()^{1,0}),{0,-2})
A1:A19不是全空或全无偶时，LARGE取的第2列的最大值列都只取百位，第1列的第1、2、3…19大值不变。
具体过程见附件答案解释里的分步计算，这里要顺带提及的是各方法在全无偶时返回全空的方法。
第1列减第2列，得到行信息对于100的"补数",再用100来减得到所需行信息。正因为采用100-，当全无偶时会返回行号101、102、...从而返回全空。
4楼：ROUND(LARGE(MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1))+1,ROW(1:15)^0)*50-ROW(),ROW()^{0,1}),{-2,0})
不同于3楼的直接舍十位与个位，这里是向上入百位，再一减直接得到了所需行号。这里没有100-这一步，
round(-1,-2)=0、round(-1,0)=-1、round(-2,0)=-1，text(,"0;-21")把第2列的负数变为-21，0或正数不变。
再一减得到行号21而返回全空。
4楼：ROUNDUP(LARGE(MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1))+1,ROW(1:15)^0)/1%-ROW(),ROW()^{0,1}),{-2,0})
全无偶时，由于roundup(-1,-2)=-100、roundup(-1,0)=-1，一减再ABS达到返回全空效果。其他情况ROUNDUP(,-2)等同于ROUND(,-2),因为原始数据只有 19行，所处理的数字的十个位数最小是81。
4楼：ROUND(6&LARGE(MMULT(-1^(1&MID(A1:A19,COLUMN(A:T),1))+1,ROW(1:20)^0)/1%-ROW(),ROW()^{0,1}),{-2,0})
与上面的ROUND法原理一致，不同的是利用6&-1这样得到的日期的系列值而不需要TEXT。
不是无全偶字符的情况与上同，为全偶字符时，6&-1得到41061，6&-2得到41062，再进行round(,-2)取差求绝对值返回足够大的行号而返回全空。
6楼:-ABS(LARGE(MMULT(1-MOD(1&MID(A1:A19,COLUMN(A:O),1),2),ROW(1:15)^0)*100-IF(COLUMN(A:S)>1,99,ROW(1:19)),ROW(1:19)))
此公式IF部分的分析见STEP 2。
取负后在MOD(-x,100)得到行号，MOD(-499,100)=1、MOD(-401,100)=99、……
加ABS也是为了适应无偶情况，以避免负负得正取不到其相对于100的"补"。
12楼：TEXT(SMALL(ROW(1:19)%-MMULT(1-MOD(1&MID(A1:A19,COLUMN(A:O),1),2),ROW(1:15)^0),ROW(1:19)^{1,0}),{"0.00","1;0"})
其思路和3楼方法异曲同工。
TEXT(,{"0.00","1;0"}),第1列的参数这里保持数据愿貌，第2列的参数"1;0"一是丢掉负数的负号、二是进行负数取整，达到上取效果。
当全无偶字符时，ROW(1:19)%-MMULT(1-MOD(1&MID(A1:A19,COLUMN(A:O),1),2),ROW(1:15)^0),ROW(1:19)^{1,0})等于ROW(1:19)%
TEXT(,"1;0")强制赋值1，最后返回行号1xx而容错。
14楼：TEXT(LARGE(MMULT(1-MOD(1&MID(A1:A19,COLUMN(A:O),1),2),ROW(1:15)^0)-ROW(1:19)%,ROW(1:19)^{0,1}),{"0;16",".00"})
原理与12楼相似，不再分析。
19楼：TEXT(TRUNC(LARGE(MMULT(-1^(1&MID(A1:A19,COLUMN(A:O),1)),ROW(1:15)^0)/1%-ROW(1:19),ROW(1:19)^{0,1}),{-2,0}),"[=-1500]1")
TRUNC同三楼，TEXT部分处理全无偶情况，
20楼：用Hlookup依次查找各行小于等于20的最大值，思路很独到。
其他不符的答案我就不解析了，但其思路也不泛闪光之处。
step 5:区域数组INDEX所得行号的原始数据。
所取行号在原始数据区域之外返回""。
三、分步演示结果见附件。
答案汇总及分步解析.rar
(26.78 KB, 下载次数: 71)
|
楼主: hjj0451
[复制链接]
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
楼主
发表于 2012-8-29 20:13
发表于 2012-8-30 12:48
