这帖子原放在开放式竞赛区,因不易被大家浏览,特移至此处。本人觉得这方法对大家也许有益,特意介绍给大家分享:
关于ROW()+数据区域的方法说明:
早先在处理一平面表格数据时,要同时求出每行(列)的最值时,常用SUBTOTAL()+OFFSET()等三维取数方法,
此方法很好,但不是很直观,特别是OFFSET()取出的数用F9去查看时,经常看不到欲取数的全貌,这让初学者
很难理解,亦较难掌握,这对于不擅长空间想象的E友而言更是如此,这里介绍一种用二维取数法解决要同时求出
每行(列)的最值的方法(以下例子取自正式竞赛题 [第2期]按最后得分排序)
三维取数解答:
两个公式分别为:
=OFFSET(B9,11-MOD(LARGE(MMULT(SUBTOTAL({4,5,9},OFFSET(C$9,ROW(1:10),,,5)),{-1;-1;1})/1%+11-ROW(1:10),ROW(1:10)),100),)
=LARGE(MMULT(SUBTOTAL({4,5,9},OFFSET(C9,ROW(1:10),,,5)),{-1;-1;1}),ROW(1:10))
公式中OFFSET(C9,ROW(1:10),,,5)用F9查看是看不到取出的数的全貌的
ROW()+表格数据解答方法
两个公式分别为:
=INDEX(B10:B19,11-MOD(LARGE(MOD(MMULT(SMALL(ROW(1:10)/1%+C10:G19,ROW(1:10)*5-{3,2,1}),{1;1;1}),100)/1%+11-ROW(1:10),ROW()-37),100),)
=LARGE(MOD(MMULT(SMALL(ROW(1:10)/1%+C10:G19,ROW(1:10)*5-{3,2,1}),{1;1;1}),100),ROW(1:10))
其中核心公式为:SMALL(ROW(1:10)/1%+C10:G19,ROW(1:10)*5-{3,2,1})
A、直接取数:C10:G19
B、ROW(1:10)/1%+C10:G19,将C10:G19分别置于区间:[100,200),[200,300),[300,400),[400,500),[500,600),[600,7000),[7000,800),[800,900),[900,1000),[1000,1100)
结果为:{103,102,103,102,101;204,201,205,205,204;304,305,301,302,304;402,401,403,402,405;505,503,502,501,502;602,604,603,602,603;701,703,705,701,704;801,803,804,802,802;901,904,904,902,904;1001,1004,1005,1005,1003}
C、然后通过SMALL()按数值大小取出ROW(1:10)/1%+C10:G19数据中的排位分别为:
{2,3,4;7,8,9;12,13,14;17,18,19;22,23,24;27,28,29;32,33,34;37,38,39;42,43,44;47,48,49}
的值(原数据维数保持不变)
再后是程式化的步骤,不再赘述。
[ 本帖最后由 fangjianp 于 2011-4-5 10:06 编辑 ] |