楼主
fangjianp |
求各组数据的众数之和
说明：
1、A2:I8为原始数据区域，这些数在1-9范围内，区域内数据可以引用
2、每行为一组数据，数据是无序的
3、K2:K8为该数据所在行的A:I的众数
4、求每组数据的众数之和
5、公式写在M2单元格中
6、若这一组数据中不存在众数，则按0处理
7、一组数据只取一个众数，即是排序在前的那个数，如：{1,2,2,3,3},只能取2，这与用MODE()函数求得的众数是一致的
8、如在Excel2003中能使用,可考虑适当加魅力值
要求：
1、不用VBA、辅助列、定义名称
2、公式写在M2单元
3、K2:K8数据不能引用
4、数据可能非常多，可能会多于100组(请勿用SUM(MODE(A2:I2)+MODE(A3:I3)+……+MODE(A8:I8))不具扩展性的方法）
5、函数解题
6、公式长度350以内：得技能分10分；答案精彩再加5分
截至日期：2011年6月30日
 各组数据众数之和1.rar
|
2楼
rongjun |
- =SUM(--RIGHT(LARGE(--(ROW(1:7)&COUNTIF(OFFSET(A1:I1,ROW(1:7),),A2:I8)&9-COLUMN(A:I)&(COUNTIF(OFFSET(A1:I1,ROW(1:7),),A2:I8)>1)*A2:I8),ROW(1:7)*9-8)))
|
3楼
wangg913 |
- =SUM(MOD(0&TEXT(RIGHT(SMALL((ROW(2:8)&COUNTIF(OFFSET(A1:I1,ROW(1:7),),A2:I8)&COLUMN(A:I))*10+A2:I8,ROW(1:7)*9),3),"[>200];"),10))
数组公式
验证了一下，上面的公式不对。
上一层楼的也不对。
不知这个对不对？
- =-SUM(-MID(-RIGHT(SMALL((ROW(2:8)&COUNTIF(OFFSET(A1:I1,ROW(1:7),),A2:I8)-1&10-COLUMN(A:I))*10+A2:I8,ROW(1:7)*9),3)&0,4,1))
- =SUM(--MID(-RIGHT(SMALL((ROW(2:8)&COUNTIF(OFFSET(A1:I1,ROW(1:7),),A2:I8)-1&9-COLUMN(A:I))+A2:I8%,ROW(1:7)*9),5)&0,6,1))
|
4楼
Zaezhong |
- =SUM(N(OFFSET(A1,ROW(1:7),9-RIGHT(SMALL(--(ROW(2:8)&COUNTIF(OFFSET(A1,ROW(1:7),,,9),A2:I8)-COLUMN(A:I)%),ROW(1:7)*9))))*(-RIGHT(INT(SMALL(--(ROW(2:8)&COUNTIF(OFFSET(A1,ROW(1:7),,,9),A2:I8)-COLUMN(A:I)%),ROW(1:7)*9)))<0))
- =SUM(N(OFFSET(A1,ROW(1:7),9-RIGHT(TEXT(SMALL(--(ROW(2:8)&COUNTIF(OFFSET(A1,{1;2;3;4;5;6;7},,,9),A2:I8)-COLUMN(A:I)%),ROW(1:7)*9),"0.00"))))*(-RIGHT(INT(SMALL(--(ROW(2:8)&COUNTIF(OFFSET(A1,ROW(1:7),,,9),A2:I8)-COLUMN(A:I)%),ROW(1:7)*9)))<0))
加个Text便于理解，如果是每行只有9个数字的情况可以不加Text |
5楼
sharprain |
- =SUM(--LEFT(MMULT(IF(LEN(SUBSTITUTE(MMULT(A2:I8,10^ROW(1:9)),A2:I8,""))<9,A2:I8,),10^(9-ROW(1:9)))))
第二行的众数是1吧？
数组公式 |
6楼
wcymiss |
- =SUM(--RIGHT(LARGE(ROW(1:7)*1000+COUNTIF(OFFSET(A1,ROW(1:7),,,9),A2:I8)*100+100-COLUMN(A:I)*10+A2:I8,64-ROW(1:7)*9)*(MMULT(10^A2:I8,ROW(1:9)^0)<>1111111110)))
|
7楼
bluexuemei |
献丑了！，包括等号348字符。- =SUM(--RIGHT(TEXT(SMALL(ROW(1:7)/1%%-FIND(COLUMN(A:I),MMULT(A2:I8*10^(9-COLUMN(A:I)),ROW(1:9)^0)&1/17)*10+(SMALL(ROW(1:7)*10+COUNTIF(OFFSET(A2:I2,ROW(1:7)-1,),COLUMN(A:I)),ROW(1:7)*9)=ROW(1:7)*10+COUNTIF(OFFSET(A2:I2,ROW(1:7)-1,),COLUMN(A:I)))*(COUNTIF(OFFSET(A2:I2,ROW(1:7)-1,),COLUMN(A:I))>1)/1%+COLUMN(A:I),ROW(1:7)*9)-ROW(1:7)/1%%,"[>0]0;!0")))
|
8楼
鬼狐 |
=SUM(MOD(SMALL(TEXT(COUNTIF(OFFSET(A1:I1,ROW(1:7),),A2:I8)/1%+(10-COLUMN(A:I))*10+A2:I8,"[<200]!0;0")+ROW(2:8)/1%%,ROW(1:7)*9),10)) |