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

xiaox0124

D05-xiaox0124

1. =SUMPRODUCT((B12:B21=A25)*(E12:G12>=60))

复制代码

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

复制代码

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

复制代码

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

复制代码

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

复制代码

stone1969

【第三讲】作业D03stone1969(1109590007)
作业一
=COUNTIFS(E12:G12,">60")
作业二
法1
=COUNTIFS($A$12:$A$21,$A$12,$E$12:$E$21,">60",$F$12:$F$21,">60",$G$12:$G$21,">60")

法2
=SUMPRODUCT(($A$12:$A$21=$A$12)*($E$12:$E$21>60)*($F$12:$F$21>60)*($G$12:$G$21>60))

作业三
统计表1中总成绩的年级排名（美国式排名）
=RANK(H12,$H$12:$H$21)
作业四
=SUMPRODUCT((($H$12:$H$21)*($A$12:$A$21=A12)>H12)*1)+1
作业五
法1=SUMPRODUCT(($A$12:$A$21=$A$12)*($C$12:$C$21=$C$12),$D$12:$D$21)/SUMPRODUCT(($A$12:$A$21=$A$12)*($C$12:$C$21=$C$12))
法2、=SUMPRODUCT(($A$12:$A$21=$A$12)*($C$12:$C$21=$C$12),$D$12:$D$21)/COUNTIFS($A$12:$A$21,$A$12,$C$12:$C$21,$C$12)

tbcdmp

函初201203班]- D13:tbcdmp
只会第三题
=RANK(H12,$H$12:$H$21)

xsl343866865

[函初201203班]-D24-xsl343866865
作业一：

1. =COUNTIFS(A12:A21,1,E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
   
2. {=SUM((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))}

复制代码

作业三：

1. =SUMPRODUCT(($H$12:$H$21>H12)*1)+1
   
2. {=SUM(($H$12:$H$21>H12)*1)+1}
   
3. =RANK(H12,$H$12:$H$21)

复制代码

作业四：

1. {=SUM(--(IF($A$12:$A$21=A12,$H$12:$H$21,0)>H12))+1}
   
2. =SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12)*1)+1
   
3. {=SUM(($A$12:$A$21=A12)*($H$12:$H$21>H12)*1)+1}

复制代码

作业五：

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

复制代码

xiaobai83

作业一：不会。
作业二：
法１：

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

复制代码

法２：

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

复制代码

作业三：

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

复制代码

作业四：不会。
作业五：
法１：

1. =ROUND(AVERAGEIF(C12:C21,"男",D12:D21),0)

复制代码

法２：

1. =ROUND((SUMIF(C12:C21,"男",D12:D21)/COUNTIF(C12:C21,"男")),0)

复制代码

wangjianff

作业1：
<=SUMPRODUCT((B12:B21=A25)*((E12:E21>=60)+(F12:F21>=60)+(G12:G21>=60)))>
作业2：法1：
<{=COUNT(0/((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60)))}>
法2：
<=SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))>
法3:
<=COUNTIFS(A12:A21,1,E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")>
作业3：
<=RANK(H12,H$12:H$21,)>
作业4:
<=SUMPRODUCT((A$12:A$21=A12)*(H$12:H$21>H12))+1>
作业5：法1：
<=AVERAGEIFS(D12:D21,A12:A21,1,C12:C21,"男")>
法2：
<=SUMPRODUCT((A12:A21=1)*(C12:C21="男")*D12:D21)/SUMPRODUCT((A12:A21=1)*(C12:C21="男"))>
【函数初级201203班】-D31-wangjianff

lion_zjy

【函初201203班]- D014:lion_zjy
作业一: =COUNTIF(E8:G8,">=60")   =COUNTIF(E8:G8,">=60")
作业二:方法一:=COUNTIFS(E8:E17,">=60",F8:F17,">=60",G8:G17,">=60",A8:A17,"=1")
       方法二:=SUMPRODUCT((A8:A17=A8)*(E8:E17>=60)*(F8:F17>=60)*(G8:G17>=60))
作业三:方法一:=RANK(H8,$H$8:$H$17)  方法二:=SUMPRODUCT(--($H$8:$H$16>H8))+1
作业四:=SUMPRODUCT(((($A$8:$A$17=A8)*$H$8:$H$17)>H8)*1)+1
作业五: 方法一:=ROUND((AVERAGEIF(A8:A17,"=1",D8:D17)),0)
        方法二:=MROUND(AVERAGEIF($A$8:$A$17,"1",D8:D17),1)

beasour

目前看来还未开贴，本人才疏学浅，到现在才做出来一些题，答案如下：
作业一： C25中输入  =COUNTIFS(E12:G12,">=60")
作业二：B30中输入   =SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
            只会一种方法，见谅。
作业三：B38中输入   =RANK(H12,$H$12:$H$21)
             只会一种方法，见谅。
作业四：不会
作业五：B48中输入   =ROUND(AVERAGEIFS(D12:D21,A12:A21,1,C12:C21,"男"),0)
            B49中输入   =ROUND(SUMIFS(D12:D21,A12:A21,1,C12:C21,"男")/COUNTIFS(A12:A21,1,C12:C21,"男"),0)