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

libenwen2011

【函初201203班】B27-libenwen2011
作业1
=COUNT(E12:G12)
作业2
=COUNTIFS(E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
=COUNT(E12:E21)-COUNTIF(E12:E21,"<60")-COUNTIF(F12:F21,"<60")-COUNTIF(G12:G21,"<60")
作业3
=RANK(H12,$H$12:$H$21)
作业4
=SUMPRODUCT((A$12:A$21>A12)*1)+1
作业5
=AVERAGEIF(C12:C21,"男",D12:D21)
=SUMIF(C12:C21,"=男",D12:D21)/COUNTIF(C12:C21,"=男")

aiwenaixuexi

【函初201203班]- B37-aiwenaixuexi

作业1：<=COUNTIF(E12:G12,">60")>
作业2：<=SUMPRODUCT(($A$12:$A$21=1)*($E$12:$E$21>=60)*($F$12:$F$21>=60)*($G$12:$G$21>=60))>
作业3：<=RANK(H12,H$12:H$21,0)>
作业4：<=SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12))+1>

老师：作业5 不会做，不知道用什么公式能求出年级中一班、男同学的平均年龄。请指教谢谢!

ccentaurc2005

老师不好意思,用错号发啦!!!!!没办法删贴只好留在上面啦!!!!
【函初201203班]- B17-深埋雪中
作业一\

1. =COUNTIFS(OFFSET(B11,MATCH(B12:B21,A25,0),3,1,3),">=60")

复制代码

组合键

作业二
1\

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

复制代码

2\

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

复制代码

作业三\

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

复制代码

作业四\
作业五\
1\

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

复制代码

2\

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

复制代码

组合键

深埋雪中

【函初201203班]- B17-深埋雪中
作业一

1. =COUNTIFS(OFFSET(B11,MATCH(B12:B21,A25,0),3,1,3),">=60")

复制代码

组合键
作业二
1

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

复制代码

2

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

复制代码

作业三

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

复制代码

作业四
作业五
1

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

复制代码

2

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

复制代码

组合键

lw2294

【函初201203班]- B10-lw2294
第一题：

1. =SUMPRODUCT((B12:B21=A25)*(E12:G12>=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. =RANK(H12,$H$12:$H$21,0)

复制代码

第四题：

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

复制代码

第五题：

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

复制代码

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

复制代码

augustzhu1983

【函初201203班】-B29-augustzhu1983
作业一

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((A$12:A$21=A12)*(H$12:H$21>H12))+1

复制代码

作业五

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

复制代码

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

复制代码

汉娜

只会第三题：=RANK(H12,$H$12:$H$21)

另外，强烈要求学委讲作业！拜托~~~~~

M_BC

【函初201203班]- B15-M_BC

作业一：
B25=COUNTIF(D12:G12,">=60")

作业二：
方法1：B30=COUNTIFS(A12:A21，1，E12：E21，">=60",F12:F21,">=60",G12:G21,">=60")
方法2：不会

作业三：
B38=RANK(H12,$H$12:$H$21,)

作业四：
不会

作业五：
方法1：B48=ROUND(SUMIFS(D12:D21,A12:A21,1)/COUNTIF(A12:A21,1),)
方法2：B49=不会

PS.强烈要求讲作业~~~~

大白梨

【函初201203班]- B07-大白梨
作业一：=COUNTIF(A12:G12,">60")
作业二：法1  =COUNTIF(A12:G12,">60")
作业三：=RANK(H12,$H$12:$H$21)  下拉公式
作业四：不会
作业五：=AVERAGE(D12,D16,D21)

福将杨

【函初201203班】-B09-福将杨
作业一：SUMPRODUCT((B12:B21=A25)*((E12:E21>=60)+(F12:F21>=60)+(G12:G21>=60)))
作业二：做法1.COUNTIFS(A12:A21,"1",E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
            做法2.SUMPRODUCT((A12:A21=A12)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
作业三：SUMPRODUCT(($H$12:$H$21>H12)*1)+1
作业四：SUMPRODUCT(((A$12:A$21=A12)*(H$12:H$21)>=(H12))*1)
作业五：做法1.ROUND(AVERAGEIFS(D12:D21,A12:A21,"1",C12:C21,"男"),0)
            做法2.ROUND(SUMIFS(D12:D21,A12:A21,"1",C12:C21,"男")/COUNTIFS(A12:A21,"1",C12:C21,"男"),0)