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

cixi1632003

01.=COUNTIF(E12:G12,">60")
     复制代码
02.=COUNTIFS(A12:A21,"1",E12:E21,">60",F12:F21,">60",G12:G21,">60")
     复制代码
03.=RANK(H12,H$12:H$21)
     复制代码
【函初201203班]- A30-cixi1632003

zx_wl

【函初201203班]- A23-zx_wl
作业一：=SUMPRODUCT((B12:B21=A25)*(E12:E21>=60))+SUMPRODUCT((B12:B21=A25)*(F12:F21>=60))+SUMPRODUCT((B12:B21=A25)*(G12:G21>=60))
作业二：方法一、=COUNTIFS(A12:A21,1,E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
            方法二、=SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
作业三：=RANK(H12,$H$12:$H$21)
作业四：=IF(A12=1,SUMPRODUCT((H12<$H$12:$H$21)*($A$12:$A$21=1))+1,SUMPRODUCT((H12<$H$12:$H$21)*($A$12:$A$21=2))+1)
作业五：方法一、=ROUND(SUMPRODUCT((C12:C21="男")*D12:D21)/COUNTIF(C12:C21,"男"),)
            方法二、=ROUND(AVERAGEIF(C12:C21,"=男",D12:D21),)

huanran2000

【函初201203班】-A21-HUANRAN2000
作业一：=COUNTIFS(B12:B21,A25,E12:E21,">60")+COUNTIFS(B12:B21,A25,F12:F21,">60")+COUNTIFS(B12:B21,A25,G12:G21,">60")
            =SUMPRODUCT((B12:B21=A25)*((E12:E21>60)+(F12:F21>60)+(G12:G21>60)))
作业二：=COUNTIFS(A12:A21,1,E12:E21,">60",F12:F21,">60",G12:G21,">60")
             =SUMPRODUCT((A12:A21=A12)*(E12:E21>60)*(F12:F21>60)*(G12:G21>60))
作业三：=RANK(H12,$H$12:$H$21)
作业四：=SUMPRODUCT(($H$12:$H$21>H12)*($A$12:$A$21=A12))+1
作业五：=ROUND(AVERAGEIF(C12:C21,"男",D12:D21),0)
            =ROUNDUP(AVERAGEIF(C12:C21,"男",D12:D21),0)
            =ROUND(SUMIF(C12:C21,"男",D12:D21)/COUNTIF(C12:C21,"男"),0)
            =ROUNDUP(SUMIF(C12:C21,"男",D12:D21)/COUNTIF(C12:C21,"男"),0)

wawadai

1、=COUNTIFS(B12:B21,A25,E12:G12,"&gt,60")
2、=COUNTIFS($E$12:$G$12,">60")
3、=RANK(H12,$H$12:$H$21)
5、=AVERAGEIFS(D12:D21,A12:A21,"一",C12:C21,"男")

风花雪月8888

作业一   =COUNTIF(D12:G12,">=60")
作业二   =COUNTIFS(A12:A21,"1",E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
            =SUMPRODUCT((MOD(A12:A21,2)=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
作业三   =RANK(H12,$H$12:$H$21)
作业四   =SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12))+1
作业五   =SUMPRODUCT((MOD(A12:A21,2)=1)*(C12:C21="男")*D12:D21)/SUMPRODUCT((MOD(A12:A21,2)=1)*(C12:C21="男"))
            =SUMPRODUCT((MOD(A12:A21,2)=1)*(C12:C21="男")*D12:D21)/COUNTIFS(A12:A21,"1",C12:C21,"男")

                                              函初201203班]- A28-风花雪月8888

jerryzhouah

1. 第一题：=COUNTIFS(B12:B21,A25,E12:E21,">60")+COUNTIFS(B12:B21,A25,F12:F21,">60")+COUNTIFS(B12:B21,A25,G12:G21,">60");

复制代码

1. 第二题：=SUMIFS(A12:A21,A12:A21,1,E12:E21,">60",F12:F21,">60",G12:G21,">60");

复制代码

1. 第三题：=RANK(H12,$H$12:$H$21);

复制代码

1. 第四题：   ;

复制代码

1. 第五题：=SUMIFS(D12:D21,A12:A21,1,C12:C21,"男")/SUMIFS(A12:A21,A12:A21,1,C12:C21,"男");
   
2. =AVERAGEIFS(D12:D21,A12:A21,1,C12:C21,"男");

复制代码

jinglingshu

[函初201203班]-A25-jinglingshu

作业一

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

复制代码

作业二

1. =SUMPRODUCT(N(MMULT(N(E12:G21>={60,60,60}),{1;1;1})=3))

复制代码

     只能做到这步了，分班的语句实在不会加啦。
作业三

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

复制代码

作业四  

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

复制代码

作业五  

1. =ROUND(SUMPRODUCT(($C$12:$C21="男")*$D$12:$D$21)/COUNTIF(A:A,"1"),0)

复制代码

     

闪闪星儿

【函初201203班]- A29-闪闪星儿


作业一：

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

复制代码

作业二：
1、

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

复制代码

2、

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)

复制代码

2、

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

复制代码

sprite_chen

【函初201203班]- A02-sprite_chen

1、=SUMPRODUCT((B12:B21=A25)*(E12:G21>=60))

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

3、=RANK(H12,$H$12:$H$21)

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

5、（1）.=AVERAGEIFS(D12:D21,A12:A21,"=1",C12:C21,"=男")
（2）.=SUMIFS(D12:D21,A12:A21,"=1",C12:C21,"=男")/COUNTIFS(A12:A21,"=1",C12:C21,"=男")

禾火亦

===============截止至此，作个记号==============