【函数初级201203班】第三讲C组作业上交帖[已开贴]

芒果冰激凌

【函初201203班]- C22-芒果冰激凌
作业一

1. =SUMPRODUCT((B12:B21=A25)*((E12:E21>=60)+(F12:F21>=60)+(G12:G21>=60)))

复制代码

作业二

1. =SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))

复制代码

1. =COUNTIFS(A12:A21,1,E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")

复制代码

作业三

1. =SUMPRODUCT((H$12:H$21>H12)*1)+1

复制代码

作业四

1. =SUMPRODUCT((H$12:H$21>H12)*(A$12:A$21=A12))+1

复制代码

作业五

1. =AVERAGEIFS(D12:D21,A12:A21,1,C12:C21,"男")

复制代码

1. =SUMPRODUCT((A12:A21=1)*(C12:C21="男")*D12:D21)/COUNTIFS(A12:A21,1,C12:C21,"男")

复制代码

一杯清荼

                                     【函初201203班】-C08-一杯清荼
作业1：

1. =SUMPRODUCT((B12:B21=A25)*(E12:G21>59))

复制代码

作业2：

1. =SUMPRODUCT(((A12:A21=1)*(E12:E21>59)*(F12:F21>59)*(G12:G21>59)))

复制代码

1. =COUNTIFS(A12:A21,1,E12:E21,">59",F12:F21,">59",G12:G21,">59")

复制代码

作业3：

1. =RANK(H12,$H$12:$H$21)

复制代码

1. =SUMPRODUCT(--(H$12:H$21>H12))+1

复制代码

1. =COUNTIF(H$12:H$21,">"&H12)+1

复制代码

作业4：

1. =SUMPRODUCT(((H$12:H$21)*(A$12:A$21=A12)>H12)*1)+1

复制代码

1. =SUMPRODUCT((A$12:A$21=A12)*(H$12:H$21>H12))+1

复制代码

1. =(A12=1)*(COUNTIFS(A$12:A$21,1,H$12:H$21,">"&H12))+(A12=2)*(COUNTIFS(A$12:A$21,2,H$12:H$21,">"&H12))+1

复制代码

作业5：

1. =SUM((A12:A21=1)*(C12:C21="男")*D12:D21)/COUNTIFS(A12:A21,1,C12:C21,"男")

复制代码

1. =SUMIFS(D12:D21,A12:A21,1,C12:C21,"男")/COUNTIFS(A12:A21,1,C12:C21,"男")

复制代码

1. =SUMIFS(D12:D21,A12:A21,1,C12:C21,"男")/SUMPRODUCT((A12:A21=1)*(C12:C21="男"))

复制代码

9201225

【函初201203班】-C12-9201225

1. =SUMPRODUCT(($H$12:$H$21>=H12)*1)
   
2. =SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>=H12))

复制代码

1. =SUMPRODUCT(($B$12:$B$21=A25)*(E12:G21>=60))

复制代码

1. =SUMPRODUCT(($A$12:$A$21=1)*($E$12:$E$21>=60)*($F$12:$F$21>=60)*($G$12:$G$21>=60))
   
2. =COUNTIFS($A$12:$A$21,1,$E$12:$E$21,">=60",$F$12:$F$21,">=60",$G$12:$G$21,">=60")

复制代码

1. =SUMPRODUCT(($H$12:$H$21>=H12)*1)

复制代码

1. =SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>=H12))

复制代码

1. =ROUND(AVERAGEIF($C$12:$C$21,"男",$D$12:$D$21),0)
   
2. =ROUND(SUMPRODUCT(($C$12:$C$21="男")*($D$12:$D$21))/COUNTIF($C$12:$C$21,"男"),0)
   
3. =ROUND(SUMIF(C12:C21,"男",D12:D21)/COUNTIF(C12:C21,"男"),0)

复制代码

lfwzh0059

【函初201203班】- c11-lfwzh0059
老师这次留的作业太难了，
只做对了一个
<作业三 =RANK(H12,$H$12:$H$21)>
作业一：=ADDRESS(MATCH(A25,B12:B21,0)+11,5,4)&":"&ADDRESS(MATCH(A25,B12:B21,0)+11,7,4)取到某考生的成绩区域，但是在countif(ADDRESS(MATCH(A25,B12:B21,0)+11,5,4)&":"&ADDRESS(MATCH(A25,B12:B21,0)+11,7,4),">=60")中怎么就出错了，实在想不出来了，老师教教，我继续努力，，其余的作业还没有想出来，我用的是03版，两种方法是不是针对07或者10版的呢？

seabearlin

学委，
    您好！

答案如下：
作业1：

1. =COUNTIF(E12:G12,">＝60")

复制代码

作业2：
法1：

1. =SUMPRODUCT((E12:E21>＝60)*(F12:F21>＝60)*(G12:G21>＝60)*(A12:A21=1))

复制代码

法2：

1. =SUMPRODUCT((E12:E21>＝60)*(F12:F21>＝60)*(G12:G21>＝60)*(MOD(A12:A21,2)=1))

复制代码

作业3：
法1：

1. =RANK(H12,$H$12:$H$21)

复制代码

法2：

1. =SUMPRODUCT(($H$12:$H$21>H12)*1)+1

复制代码

作业4：

1. =SUMPRODUCT(($H$12:$H$21>H12)*1*($A$12:$A$21=A12))+1

复制代码

作业5：
法1：

1. =ROUND(SUMPRODUCT((A12:A21=1)*(C12:C21=C12)*D12:D21)/SUMPRODUCT((A12:A21=1)*(C12:C21=C12)),0)

复制代码

法2：

1. {=AVERAGE(IF((A12:A21=1)*(C12:C21="男"),D12:D21))}

复制代码

以上请查阅，谢谢~

函初C27:seabearlin

pang1688

【函初201203班】-c30-pang1688
1、

1. =COUNTIF(INDEX(E:G,MATCH(A25,B12:B21,0)+11,),">60")

复制代码

2、

1. =SUMPRODUCT((A12:A21=1)*(E12:E21>60)*(F12:F21>60)*(G12:G21>60))

复制代码

1. =SUMPRODUCT((E12:E21>60)*(F12:F21>60)*(G12:G21>60))-SUMPRODUCT((A12:A21=2)*(E12:E21>60)*(F12:F21>60)*(G12:G21>60))

复制代码

3、

1. =RANK(H12,$H$12:$H$21)

复制代码

4
5、

1. {=ROUND(SUM((A12:A21=1)*(C12:C21="男")*D12:D21),0)/(SUM((A12:A21=1)*(C12:C21="男")))}

复制代码

1. {=(ROUND(SUM((A12:A21=1)*D12:D21),0)-(SUM((A12:A21=1)*(C12:C21="女")*D12:D21)))/(SUM((A12:A21=1)*(C12:C21="男")))}

复制代码

大白梨

积极啊-------------在哪看全部的正确答案呢

虎刺梅

等待老师讲作业。