求各行最值之差的和

楼主 fangjianp	题目：求A2:D11每行最大值与最小值之差的和说明： 1、A2:D11为已知数据（请勿改动） 2、每一行为一组 3、求出每组的最大值与最小值之差，然后求所有这些差的和 4、E2为模拟答案 5、F2，……，M2为答题区域 6、数据可能多达几百行 7、所有的数据范围：1——99 要求： 1、不准用辅助列，VBA和定义名称 2、不准引用E2 3、用函数答题 4、Excel2003须通过请用尽可能多的方法求解，当给出不同解法在3种（包括3）以内，每种方法得1个技能分，当提供不同解法数多于3，则每增一种解法的2个技能分，提供方法最多者另加5个技能分截至时间：2011年6月15日所谓不同方法就是求每行最值的方法是不同的，至少用的函数的类型不一样（如：MAX()/MIN(),LARGE()/SMALL()，SUM()/SUMPRODUCT()……，每一对只能算一种),主要考虑的明显不同再比如：MOD(--(A&B),3) MOD(A/1%+B,3) *MOD(A100+B,3) 没什么方法上的不同，不能作为2种方法提供本题主要是希望大家能提供新的思路，在方法上思路上有所突破。** 求每行数据最值之差的和.rar
2楼 Zaezhong	算不算不同的我也不管了由楼主来判断吧~~ 1、 =SUMPRODUCT(SUBTOTAL({4,5},OFFSET(A1,ROW(1:10),,,4)){1,-1}) 2、 =SUMPRODUCT(SUBTOTAL({4,5},INDIRECT("R"&ROW(2:11)&"C1:R"&ROW(2:11)&"C4",)){1,-1}) 3、 =SUMPRODUCT(SMALL(10^ROW(2:11)+A2:D11,4ROW(1:10)-{3,0}){-1,1}) 4、 =SUMPRODUCT(LARGE(10^ROW(2:11)+A2:D11,4ROW(1:10)-{3,0}){1,-1}) 5、 =SUMPRODUCT(PERCENTILE(10^ROW(2:11)+A2:D11,ROW(1:10)/394-{4,1}/39){-1,1}) 6、 =SUM(N(OFFSET(A1,ROW(1:10),ROUND(MOD(SMALL(RANK(A2:D11,OFFSET(A1,{1;2;3;4;5;6;7;8;9;10},,,4))+10^ROW(1:10)+COLUMN(A:D)%,4ROW(1:10)-{3,0}),1)/1%,)-1)){1,-1}) round函数不能省略可能产生浮点错误 7、 =SUM(N(OFFSET(A1,ROW(1:10),MOD(SMALL(RANK(A2:D11,OFFSET(A1,ROW(1:10),,,4))+10^ROW(1:10)+COLUMN(A:D)%,4ROW(1:10)-{3,0}),1)/1%-0.99)){1,-1}) -0.99其实是0.01-1的结果 8、 =SUM(N(OFFSET(A1,ROW(1:10),RIGHT(SMALL(RANK(A2:D11,OFFSET(A1,ROW(1:10),,,4))+10^ROW(1:10)+COLUMN(A:D)%,4ROW(1:10)-{3,0}),2)-1)){1,-1}) 用right函数就没有浮点错误的问题了 9、 =SUM(N(OFFSET(A1,ROW(1:10),MOD(PERCENTILE(RANK(A2:D11,OFFSET(A1,ROW(1:10),,,4))+10^ROW(1:10)+COLUMN(A:D)%,ROW(1:10)/394-{4,1}/39),1)/1%-0.99)){1,-1}) 10、 =SUM(N(OFFSET(A1,ROW(1:10),RIGHT(PERCENTILE(RANK(A2:D11,OFFSET(A1,ROW(1:10),,,4))+10^ROW(1:10)+COLUMN(A:D)%,ROW(1:10)/394-{4,1}/39),2)-1)){1,-1}) 最后请楼主辛苦判断下可以算几种~~
3楼 laowuwyx	暂时想到两种。 =SUM(SUBTOTAL({4,5},OFFSET(A1:D1,ROW(1:10),)){1,-1}) =SUM(MOD(SMALL(ROW(1:10)/1%+A2:D11,ROW(1:10)4-{3,0}),100)*{-1,1})
4楼 bluexuemei	数组公式 =SUM(SUBTOTAL({4,5},OFFSET(A2:D2,ROW(1:10)-1,)){1,-1}) =SUM(MOD(SMALL(A2:D11+ROW(1:10)1000,(ROW(1:10)-1)4+{1,4}),1000){-1,1})
5楼 piny	不同解法之定義？ MMULT + SUBTOTAL + OFFSET =SUM(MMULT(SUBTOTAL({4,5},OFFSET(A1:D1,ROW(1:10),)),{1;-1})) =SUM(MMULT(SUBTOTAL({4,5},OFFSET(A1,ROW(1:10),,,4)),{1;-1})) SUBTOTAL + OFFSET =SUM(SUBTOTAL({4,5},OFFSET(A1:D1,ROW(1:10),)){1,-1}) =SUM(SUBTOTAL({4,5},OFFSET(A1,ROW(1:10),,,4)){1,-1}) SMALL(LARGE) + ROW100 + 區域 =SUM(SMALL(ROW(1:10)100+A2:D11,4ROW(1:10)-{3,0}){-1,1}) =SUM(LARGE(ROW(1:10)100+A2:D11,4ROW(1:10)-{3,0}){1,-1}) SMALL(LARGE) + ROW & 區域 =SUM(SMALL(--(ROW(1:10)&TEXT(A2:D11,"00")),4ROW(1:10)-{3,0}){-1,1}) =SUM(LARGE(--(ROW(1:10)&TEXT(A2:D11,"00")),4ROW(1:10)-{3,0}){1,-1}) SMALL(LARGE) + ROW +區域% =SUM(SMALL(ROW(1:10)+A2:D11%,4ROW(1:10)-{3,0}){-1,1})/1% =SUM(LARGE(ROW(1:10)+A2:D11%,4ROW(1:10)-{3,0})*{1,-1})/1%
6楼 rongjun	=SUMPRODUCT(SUBTOTAL({4,5},OFFSET(A1:D1,ROW(1:10),))*{1,-1})
7楼 liuguansky	=SUM(SUBTOTAL({4,5},OFFSET(A1:D1,ROW(1:10),)){1,-1}) =SUM(SMALL(A2:D11+ROW(1:10)/1%,ROW(1:10)4-{0,3}){1,-1}) =SUM(PERCENTILE(A2:D11+ROW(1:10)/1%,(ROW(1:10)4-{1,4})/39)*{1,-1})
8楼落雪绽菊	{=SUM(LARGE(LARGE(A2:D11+ROW(A2:D11)100,ROW(1:40)),(ROW(1:10)-1)4+1),-LARGE(LARGE(A2:D11+ROW(A2:D11)100,ROW(1:40)),ROW(1:10)4))}，菜鸟，只会这一种
9楼 angellbxsc	第一种：SUM(SUBTOTAL({4,5},OFFSET(A1,ROW(1:10),,,4)){1,-1}) 第二种：SUM(MMULT(--RIGHT(LARGE(ROW(1:10)/1%+A2:D11,{1,2,3,4}+4(ROW(1:10)-1)),2),{1;0;0;-1})) 第三种：SUM(--RIGHT(LARGE(ABS(MMULT(A2:D11,{1,1,1,0,0,0;-1,0,0,1,1,0;0,-1,0,-1,0,1;0,0,-1,0,-1,-1}))+ROW(1:10)/1%,6*(ROW(1:10)-1)+1),2)) 越来越长了。呵呵，要找到三种以上真是困难。
10楼 biaogewang	=SUMPRODUCT(SUBTOTAL(4,OFFSET($A$1:$D$1,ROW(1:10),0))-SUBTOTAL(5,OFFSET($A$1:$D$1,ROW(1:10),0)))
11楼卢子	想到两种而已 =SUM(SUBTOTAL({4,5},OFFSET(A1:D1,ROW(1:10),))*{1,-1}) =SUM(MMULT(SUBTOTAL({4,5},OFFSET(A1:D1,ROW(1:10),)),{1;-1}))
12楼 Ericall	=SUMPRODUCT(SUBTOTAL({4,5},OFFSET(A1:D1,ROW(1:10),)){1,-1}) =SUM(SUBTOTAL(4,OFFSET(A1:D1,ROW(1:11),))-SUBTOTAL(5,OFFSET(A1:D1,ROW(1:11),))) =SUM(LARGE(A2:D11%+ROW(2:11),ROW(1:10)4-{3,0}){1,-1})/1% =SUM(SMALL(A2:D11%+ROW(2:11),ROW(1:10)4-{3,0}){-1,1})/1% =SUM(PERCENTILE(A2:D11%+ROW(1:10),(ROW(1:10)4-{4,1})/39){-1,1})/1% =SUM(LARGE(A2:D11%+ROW(2:11),ROW(1:10)4-3)-SMALL(A2:D11%+ROW(2:11),ROW(1:10)*4-3))/1%
13楼 hopeson2010	1、=SUM(MMULT(SUBTOTAL({4,5},OFFSET(A1:D1,ROW(1:10),)),{1;-1})) 2、=SUM(RIGHT(SMALL(ROW(2:11)10^9+A2:D11,ROW(1:10)4-{3,0}),3){-1,1}) 3、=SUM(MID(TEXT(MMULT(MOD(SMALL(ROW(1:10)10^3+A2:D11,(ROW(1:10)-1)4+{1,2,3,4}),1000),10^{6;4;2;0}),"00000000"),{1,7},2){-1,1})
14楼 w83365040371	=SUM(SUBTOTAL({4,5},OFFSET(A1:D1,ROW($1:$10),)){1,-1}) =SUM(RIGHT(SMALL(A2:D11+ROW($1:$10)10^6,4ROW($1:$10)-{3,0}),6){-1,1}) =SUM(RIGHT(LARGE(A2:D11+ROW($1:$10)10^6,4ROW($1:$10)-{3,0}),6)*{1,-1})
15楼 ↑★菩子☆↓	我参观来的