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

26759761@qq.com

1、作业统一贴公式(需要过程的直接录屏).
2、跟帖不要重复占楼,要修改直接在原楼层编辑
3、作业截止时间：2014年9月4日晚18:00，过期不予批改
4、2014-9-3(下周三)开始评分,之后修改公式的,评分不改
5、评分标准:答案正确+15/+15,个别优秀的+1-5分.不贴公式直接上附件的,评分标准上,经验BB各扣5分.即+10/+10
6、论坛头衔不修改,不认为是201401函中E组同学,第四讲开课前,删帖.

满坛皆为吾师

第一题

1. =SUM(LARGE(B3:D10,{1,2,3}))
   
2. =SUM(LARGE(MMULT(B3:D10,{1;1;1}),{1,2,3}))

复制代码

第二题

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

复制代码

第三题

1. =VLOOKUP(F$33,A$35:C$49,ROW(A2)) 下拉
   
2. =LOOKUP(F$33,IF(E34="C1=",A$35:B$49,A$35:C$49)) 下拉，下面的也可以用IF嵌套

复制代码

1. C1=LOOKUP(F33,A35:B49)
   
2. C1=SUM(((F33-A35:A49)^2=MIN((F33-A35:A49)^2))*B35:B49)
   
3. C1=SUM(IF(((F33-A35:A49)^2=MIN((F33-A35:A49)^2)),B35:B49))
   
4. C1=LOOKUP(,0/((F33-A35:A49)^2=MIN((F33-A35:A49)^2)),B35:B49)
   
5. C1=OFFSET(B34,MATCH(MIN((F33-A35:A49)^2),(F33-A35:A49)^2,),)

复制代码

1. C2=LOOKUP(F33,A35:C49)
   
2. C2=SUM(((F33-A35:A49)^2=MIN((F33-A35:A49)^2))*C35:C49)
   
3. C2=SUM(IF(((F33-A35:A49)^2=MIN((F33-A35:A49)^2)),C35:C49))
   
4. C2=LOOKUP(,0/((F33-A35:A49)^2=MIN((F33-A35:A49)^2)),C35:C49)
   
5. C2=OFFSET(C34,MATCH(MIN((F33-A35:A49)^2),(F33-A35:A49)^2,),)

复制代码

第四题    要看TRIMMEAN第二参数的条件而定。

XMergeD

1
第一问数组

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

复制代码

第二问
非数组

1. =SUM(LARGE(MMULT(B3:D10,{1;1;1}),{1,2,3}))

复制代码

数组

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

复制代码

2数组

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

复制代码

3数组

1. =VLOOKUP(MAX((MIN(ABS(A$35:A$49-F$33))=ABS(A$35:A$49-F$33))*A$35:A$49),A$35:C$49,ROW(A2),)

复制代码

内存数组

1. =TRANSPOSE(MMULT(TRANSPOSE(N(MIN(ABS(A$35:A$49-F$33))=ABS(A$35:A$49-F$33))),B35:C49))

复制代码

1. =INDEX(B35:C49,MATCH(MIN(ABS(A35:A49-F33)),ABS(A35:A49-F33),),{1;2})

复制代码

4无影响，trimmean的第一个参数忽略文本和空值，第二个参数可以利用count函数只计算数值型的单元格。

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

复制代码

天龙九部

[201401函中]E17:天龙九部

1. SUM(LARGE(B3:D10,{1;2;3}))
   
2. SUM(LARGE(MMULT({1,1,1},TRANSPOSE(B3:D10)),{1;2;3}))

复制代码

或SUM(LARGE(MMULT(B3:D10,{1;1;1}),{1;2;3}))
第二题先取消答题处合并单元格,输入公式后,再用一合并单元格,格式刷刷下

1. AVERAGE(LARGE((B19:B28=B19)*C19:C28,{1;2;3}))

复制代码

不取消合并 SUMPRODUCT(LARGE((B19:B28=B19)*C19:C28,ROW(1:3)))/3
第三

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

复制代码

第四题,空白单元格是没影响的不参与计算

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

复制代码

一杯清荼

题1：
       所有业务数据的前三项总和：

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

复制代码

一季度前三名城市的综合：

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

复制代码

1. =SUM(LARGE(MMULT(B3:D10,{1;1;1}),ROW(1:3)))

复制代码

题2：

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

复制代码

题4：

1. =TRIMMEAN(A55:A64,2/COUNT(A55:A64)) <span style="line-height: 1.5;">  空白单元格是对TRINNEAN没有影响，会忽略空白单元格。</span>

复制代码

1. =TRIMMEAN(A55:A64,2/10)  <span style="line-height: 1.5;"> 空白单元格是对TRINNEAN就有影响。</span>

复制代码

题3：

1. =LOOKUP(F$33,A$35:A$49,IF(35-ROW(),B$35:B$49,C$35:C$49))

复制代码

1. =VLOOKUP(F$33,A35:C49,ROW(A2),1)

复制代码

1. =INDEX(B$35:C$49,MOD(MIN(ABS(F$33-A$35:A$49)/1%%%+ROW(A$1:A$15)),100),ROW(A1))

复制代码

1. =INDIRECT(CHAR(ROW(A98))&RIGHT(ROUND(MIN(ABS($F$33-A$35:A$49)+ROW(A$1:A$15)%%%),6),2)+34)

复制代码

sinowonder

1、{=SUM(LARGE(B3:D10,ROW(1:3)))}
     {=SUM(LARGE(B3:B10+C3:C10+D3:D10,ROW(1:3)))}

2、{=AVERAGE(LARGE(IF(B19:B28="男",C19:C28),ROW(1:3)))}

3、c1=LOOKUP($F$33,$A$35:B$49)
     c2=INDEX(C35:C49,MATCH(MIN(ABS($F$33-A35:A49)),ABS($F$33-A35:A49),0))

4、=TRIMMEAN(A55:A64,2/COUNT(A55:A64))
     =(SUM(A55:A64)-MAX(A55:A64)-MIN(A55:A64))/(COUNT(A55:A64)-2)
     结论：空白单元格不影响结果。

76868850

1、
1.1 =SUM(LARGE(B3:D10,ROW(1:3))) 数组公式
1.2=SUM(LARGE(OFFSET(B2,ROW(1:8),3,),ROW(1:3)))数组公式
2、=AVERAGE(LARGE(IF(B19:B28="男",C19:C28),ROW(1:3)))数组公式
3、C1=LOOKUP(F33,A35:B49)
     C2=OFFSET(C34,MATCH(MIN(ABS(A35:A49-F33)),ABS(A35:A49-F33),),)数组公式
4、=TRIMMEAN(A55:A64,2/COUNT(A55:A64)) 空白单元格对TRINNEAN没有影响

瑾瑄

1.(1)=SUMPRODUCT(LARGE(B3:D10,ROW(1:3)));
   (2)=MMULT(COLUMN(A:C)^0,LARGE(B3:B10+C3:C10+D3:D10,ROW(1:3)));
2.=SUMPRODUCT(LARGE((B19:B28="男")*C19:C28,ROW(1:3)))/3:
3.={OFFSET($A$34,MATCH(MIN(ABS(G$33-A$35:A$49)),ABS(G$33-A$35:A$49),),ROW(A1),)}
={MIN(IF(ABS(G$33-A$35:A$49)=MIN(ABS(G$33-A$35:A$49)),IF(F34="C1=",B$35:B$49,C$35:C$49)))}
={INDEX(IF(F34="C1=",B$35:B$49,C$35:C$49),MATCH(MIN(ABS(G$33-A$35:A$49)),ABS(G$33-A$35:A$49),))}
={INDIRECT(CHAR(ROW(A98))&(ROW(A$34)+MATCH(MIN(ABS(G$33-A$35:A$49)),ABS(G$33-A$35:A$49),)))}
=LOOKUP(,0/(ABS(G$33-A$35:A$49)=MIN(ABS(G$33-A$35:A$49))),IF(F34="C1=",B$35:B$49,C$35:C$49))
4.=SUMPRODUCT((SUBTOTAL({9,4,5},A55:A64)*{1,-1,-1}))/6
=TRIMMEAN(A55:A64,2/COUNT(A55:A64))
结论是:无影响

ANDY0393

题一：

1. =SUM(LARGE(B3:D10,{1,2,3}))
   
2. =SUM((B3:D10>LARGE(B3:D10,4))*B3:D10)

复制代码

1. =SUM(LARGE(SUBTOTAL(9,OFFSET(B2:D2,ROW(1:8),)),{1,2,3}))
   
2. =SUM(LARGE(MMULT(B3:D10,ROW(1:3)^0),{1,2,3}))

复制代码

题二：

1. =AVERAGE(LARGE((B19:B28="男")*C19:C28,{1,2,3}))
   
2. =SUM(((B19:B28="男")*C19:C28>LARGE(IF(B19:B28="男",C19:C28),4))*C19:C28)/3

复制代码

题三：

1. =INDEX(OFFSET(A$35:A$49,,ROW(A1)),MATCH(MIN(ABS(A$35:A$49-F$33)),ABS(A$35:A$49-F$33),))
   
2. =INDIRECT(TEXT(MOD(MIN(ABS(A$35:A$49-F$33)/1%%%+ROW($35:$49)),100),"R00"&"C"&ROW(A2)),)

复制代码

1. 多单元格数组公式
   
2. =LOOKUP(,0/(MIN(ABS(A35:A49-F33))=ABS(A35:A49-F33)),OFFSET(B35:B49,,{0;1}))

复制代码

题四：

1. =TRIMMEAN(A55:A64,2/COUNT(A55:A64))
   
2. =AVERAGE(IF((A55:A64>MIN(A55:A64))*(A55:A64<MAX(A55:A64)),A55:A64))
   
3. =(SUM(A55:A64)-MAX(A55:A64)-MIN(A55:A64))/(COUNT(A55:A64)-2)

复制代码

空白单元格不对TRINNEAN有影响

滴答滴

第一题：
=SUM(LARGE(B3:D10,{1,2,3}))
=SUM(LARGE(MMULT(B3:D10,{1;1;1}),{1,2,3}))
第二题：
=SUMPRODUCT(LARGE((B19:B28="男")*C19:C28,ROW(1:3)))/3
=SUM(MMULT(LARGE((B19:B28="男")*C19:C28,ROW(1:3)),1))/3

第三题：
c1=LOOKUP(,0/((F33-A35:A49)^2=MIN((F33-A35:A49)^2)),B35:B49)
或
=OFFSET(B34,MATCH(MIN((F33-A35:A49)^2),(F33-A35:A49)^2,),)
c2=LOOKUP(,0/((F33-A35:A49)^2=MIN((F33-A35:A49)^2)),C35:C49)
或
=OFFSET(C34,MATCH(MIN((F33-A35:A49)^2),(F33-A35:A49)^2,),)
第四题：
=TRIMMEAN(A55:A64,1/4)
trimmean会忽略空白单元格，类似于average忽略空单元格