[2014函中]第三讲作业E组提交贴

meililin

第一题：
1问

1. =SUMPRODUCT(LARGE(B3:D10,ROW(1:3)))

复制代码

2问(MMult第一次使用，完全根据公式套出结果来的)

1. =SUM(LARGE(MMULT(--(B2:D2=B2:D2),TRANSPOSE(B3:D10)),ROW(1:3)))

复制代码

第二题
非数组公式

1. =SUMPRODUCT(LARGE((B19:B28="男")*C19:C28,ROW(1:3)))/3

复制代码

数组公式

1. =AVERAGE(LARGE(IF(B19:B28="男",C19:C28),ROW(1:3)))

复制代码

第三题

1. =OFFSET(B34,IFERROR(MATCH(MIN(ABS(A35:A49-F33))+F33,A35:A49,),MATCH(-MIN(ABS(A35:A49-F33))+F33,A35:A49,)),)

复制代码

1. =OFFSET(C34,IFERROR(MATCH(MIN(ABS(A35:A49-F33))+F33,A35:A49,),MATCH(-MIN(ABS(A35:A49-F33))+F33,A35:A49,)),)

复制代码

第四题（空白单元格对trimmean没影响）

1. =TRIMMEAN(A55:A64,2/COUNT(A55:A64))

复制代码

hsl215

第一题，第一问

1. =SUMPRODUCT(LARGE(B3:D10,{1;2;3}))
   
2. =SUMPRODUCT(LARGE(B3:D10,ROW(1:3)))

复制代码

第二问

1. =SUMPRODUCT(LARGE(MMULT(B3:D10,{1;1;1}),{1;2;3}))
   
2. =SUMPRODUCT(LARGE(MMULT(B3:D10,ROW(1:3)^0),ROW(1:3)))

复制代码

第二题

1. =SUMPRODUCT(LARGE((B19:B28="男")*C19:C28,ROW(1:3)))/3
   
2. =SUMPRODUCT(LARGE((B19:B28="男")*C19:C28,{1,2,3}))/3

复制代码

第三题，公式1、2计算C2时需将B35:B49更改成C35:C49，公式3、4是可直接下拉，

1. =SUM((ABS(F33-A35:A49)=MIN(ABS(F33-A35:A49)))*B35:B49)
   
2. =LOOKUP(,0/(ABS(F33-A35:A49)=MIN(ABS(F33-A35:A49))),B35:B49)
   
3. =OFFSET(A$34,MATCH(MIN(ABS(F$33-A$35:A$49)),ABS(F$33-A$35:A$49),),ROW(A1))
   
4. =INDEX(B$35:C$49,MATCH(MIN(ABS(F$33-A$35:A$49)),ABS(F$33-A$35:A$49),),ROW(A1))

复制代码

第四题，空白单元格对TRINNEAN无影响，因用公式二计算出来的结果与公式一是一样的。

1. =TRIMMEAN(A55:A64,2/COUNT(A55:A64))
   
2. =(SUM(A55:A64)-MIN(A55:A64)-MAX(A55:A64))/(COUNT(A55:A64)-2)

复制代码

ldxhzy

1.1公式：
             =SUMPRODUCT(LARGE(B3:D10,ROW(1:3)))
1.2公式：
            

2.1公式：


3.1数组公式：
           =INDEX(B35:B49,MATCH(SMALL(ABS(A35:A49-F33),1),ABS(A35:A49-F33),0))
3.2数组公式：
           =INDEX(C35:C49,MATCH(SMALL(ABS(A35:A49-F33),1),ABS(A35:A49-F33),0))
4.1公式：
         =TRIMMEAN(A55:A64,2/COUNT(A55:A64))
4.2结论：
       空白单元格对TRINNEAN有影响,理由是，如果将公式 =TRIMMEAN(A55:A64,0) 所引用区域中的空白单元格填入 0 值，公式结果会有变化，说明该函数忽略了空白单元格，这样会使得求平均值 时的分母变小。

tt89004808

1. 1.=SUM(LARGE(B3:D10,ROW(1:3)))
   
2.    =SUM(LARGE(B3:B10+C3:C10+D3:D10,ROW(1:3)))
   
3. 2.=SUM(LARGE(C19:C28,ROW(1:3)))/3
   
4. 3.无法理解悬跨比
   
5. 4.=TRIMMEAN(A55:A64,2/COUNTA(A55:A64))
   
6. 空白单元格对TRINNEAN无影响

复制代码

十五的月亮

1、=SUMPRODUCT(LARGE(B3:D10,ROW(1:3)))
=SUM(LARGE(MMULT(B3:D10,TRANSPOSE((B3:D3)^0)),ROW(1:3)))
2、=SUMPRODUCT(LARGE((B19:B28="男")*C19:C28,ROW(1:3)))/3
=AVERAGE(LARGE((B19:B28="男")*C19:C28,ROW(1:3)))
3、=LOOKUP(F33,A35:B49)
=LOOKUP(F33,A35:A49,C35:C49)
4、=TRIMMEAN(A55:A64,2/COUNT(A55:A64))  可以忽略空值

chh0926

最近太忙，来晚了
第一题
1、=SUM(LARGE(B3:D10,ROW(1:3)))
2、=SUM(LARGE(B3:B10+C3:C10+D3:D10,ROW(1:3)))
第二题
懒人用数组
=AVERAGE(LARGE((B19:B28="男")*C19:C28,ROW(1:3)))
可惜合并单元格不让用，换sumproduct
=SUMPRODUCT(LARGE((B19:B28="男")*C19:C28,ROW(1:3)))/3
第三题
1、=INDEX(B:B,SUM((MIN(ABS(A35:A49/F33-1))=ABS(A35:A49/F33-1))*ROW(A35:A49)))
2、=INDEX(C:C,SUM((MIN(ABS(A35:A49/F33-1))=ABS(A35:A49/F33-1))*ROW(A35:A49)))
第四题
默认情况下逻辑值、文本、空值不计入平均值运算，0计入运算
不考虑空值公式可以如下
=TRIMMEAN(A55:A64,2/COUNT(A55:A64))
考虑空值可以将序列做下变换
=TRIMMEAN(A55:A64*1,2/(COUNTA(A55:A64)+COUNTBLANK(A55:A64)))

yqnh

E11：yqnh 上交第三课作业，请学委老师指点
谢谢老师指点
第一题1确实理解上有问题
应该为

• =SUM(LARGE(B3:D10,{1,2,3}))
  

第二题答题时，已取消合并单元格
一
1、前三项总和

1. =SUM(SUBTOTAL(9,OFFSET(B2:D2,ROW(1:8),)))

复制代码

2、前三名

1. =SUM(LARGE(SUBTOTAL(9,OFFSET(B2:D2,ROW(1:8),)),ROW(1:3)))

复制代码

以上数组
二、

1. =AVERAGE(LARGE((B19:B28="男")*C19:C28,ROW(1:3)))

复制代码

三、
C1：

1. =INDEX(B35:B49,MATCH(MIN(ABS(A35:A49-F33)),ABS(A35:A49-F33),))

复制代码

C2：

1. =INDEX(C35:C49,MATCH(MIN(ABS(A35:A49-F33)),ABS(A35:A49-F33),))

复制代码

四、

1. =TRIMMEAN(A55:A64,2/COUNT(A55:A64))
   
2. trimmean第二参数percent分母通过count,可以忽略空值，得出正确数据，如采用2/10，会对最后平均产生影响

复制代码

xiaox0124

1. =SUMPRODUCT(B3:D10*(B3:D10>LARGE(B3:D10,4)))
   
2. =SUMPRODUCT(((B3:B10+C3:C10+D3:D10)>LARGE((B3:B10+C3:C10+D3:D10),4))*(B3:B10+C3:C10+D3:D10))
   
3. =AVERAGE(LARGE((B19:B28=B19)*C19:C28,ROW(1:3))) 三键
   
4. =OFFSET($A$35,MATCH(MIN(ABS($A$35:$A$49-$F$33)),ABS($A$35:$A$49-$F$33),)-1,ROW(A1),1,1) 三键 下拉
   
5. =TRIMMEAN(A55:A64,2/COUNT(A55:A64))
   
6. =TRIMMEAN((A55:A56,A58:A61,A63:A64),2/COUNT(A55:A64)) 没影响

复制代码

eaglexs

学委、组长很关心，作业交迟了，抱歉

E04:eaglexs第三讲作业：
第一题：
1、所有业务数据的前三项总和
=SUM(LARGE(B3:D10,ROW(1:3)))
2、一季度前三名城市的综合
不用辅助列做不出，期待答案

第二题：
男同学前三名平均成绩
=AVERAGE(LARGE(IF(B19:B28="男",C19:C28),ROW(1:3)))
=SUM(LARGE(IF(B19:B28="男",C19:C28),ROW(1:3)))/3

第三题：
=INDEX($B$35:$C$49,MATCH(MIN(ABS($A$35:$A$49-$F$33)),ABS($A$35:$A$49-$F$33),),ROW(A1))
三键，下拉

第四题：
=TRIMMEAN(A55:A64,2/COUNT(A55:A64))
如果空白单元格个数不计算在总数内，不会影响计算结果