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

水吉果果

1、本贴是回贴仅作者可见，直接跟贴回复即可。
2、上交时直接贴公式，只上传附件的不予批改
请各位童鞋使用代表功能,就是一个<>的按钮里输入公式,效果如

1. =sum(a:a)

复制代码

直接回复答案者必须注明【函初201203班】-组学号-论坛ID,如【函初201203班]- D01:yanyupingsheng;
3、截止时间2012年12月26日20：00
4、请误重复占楼，非本班同学跟贴回复者扣分

MYHERE131

作业一：SUM((B12:B21=A25)*(E12:G21>60))
作业二：法一：SUM(($A$12:$A$21=1)*($E$12:$E$21>60)*($F$12:$F$21>60)*($G$12:$G$21>60))
            法二：SUMPRODUCT(($A$12:$A$21=1)*($E$12:$E$21>60)*($F$12:$F$21>60)*($G$12:$G$21>60))
作业三：RANK(H12,$H$12:$H$21)
作业四：SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12))+1
作业五：法一：SUM((A12:A21=1)*(C12:C21="男")*D12:D21)/SUM((A12:A21=1)*(C12:C21="男"))
            法二:SUMPRODUCT((A12:A21=1)*(C12:C21="男")*D12:D21)/SUMPRODUCT((A12:A21=1)*(C12:C21="男"))
            法三：AVERAGEIFS(D12:D21,A12:A21,1,C12:C21,"男")
【函初201203班】-D08-MYHERE131

MYHERE131

1. 以上用SUM的地方可以用SUMPRODUCT哦

复制代码

snow依磬min

1  =countif(区域，"科目")
2   =sunif(区域，"人数"）
   =sum(区域)
5    =average(区域）

再就业

【函初201203班]- D23-再就业：
作业1：

1. =COUNTIFS(B12:B21,"刘宇",E12:E21,">=60")+COUNTIFS(B12:B21,"刘宇",F12:F21,">=60")+COUNTIFS(B12:B21,"刘宇",G12:G21,">=60")

复制代码

作业2：

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

复制代码

作业3：

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

复制代码

作业4：

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

复制代码

作业5：

1. 方法1：=ROUND(AVERAGEIF(C12:C21,"男",D12:D21),0)
   
2. 方法2：=ROUND(SUMPRODUCT((C12:C21="男")*D12:D21)/COUNTIF(C12:C21,"男"),0)

复制代码

hwc2ycy

【函初201203班】-D25-hwc2ycy
第一题

1. =SUM(COUNTIFS($B$12:$B$21,A25,$E$12:$E$21,">=60"),COUNTIFS($B$12:$B$21,A25,$F$12:$F$21,">=60"),COUNTIFS($B$12:$B$21,A25,$G$12:$G$21,">=60"))

复制代码

1. =COUNTIF(OFFSET($B$11,MATCH(A25,$B$12:$B$21,0),3,,3),">=60")

复制代码

第二题

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

复制代码

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

复制代码

第三题
年级排名：

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

复制代码

第四题：

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

复制代码

第五题：

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

复制代码

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

复制代码

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

复制代码

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

复制代码

yanyupingsheng

【函初201203班】- D01:yanyupingsheng
作业一：

1. =COUNTIF(INDEX(E12:G21,MATCH(A25,B12:B21,0),0),">=60")

复制代码

作业二：法1、

1. =SUMPRODUCT((A12:A21=1)*(COUNTIF(OFFSET(E12:G12,ROW(A1:A10)-1,0),">=60")>=3))

复制代码

法二：

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

复制代码

作业五：法一、

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

复制代码

法二、

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

复制代码

丫头加油站

【函初201203班】-D35-丫头加油站
作业一：

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

复制代码

作业二：

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

复制代码

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

复制代码

作业三：

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

复制代码

下拉

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

复制代码

作业四：

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

复制代码

作业五：

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

复制代码

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

复制代码

初学者2

【函初201203班-D12 初学者2
作业一：COUNTIF(E12:G12,">60")
作业二：
法1：COUNTIFS(A12:A21,"=1",E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
法2：SUMPRODUCT(($A$12:$A$21=$A$12)*($E$12:$E$21>=60)*($F$12:$F$21>=60)*($G$12:$G$21>=60))
作业三：RANK(H12,$H$12:$H$21)
作业四：SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12))+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：AVERAGEIFS(D12:D21,C12:C21,"男",A12:A21,"=1")

xzsychenwei01

【函初201203班】- D17:xzsychenwei01

第1题

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

复制代码

第2题

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

复制代码

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

复制代码

第3题

1. 年级排名：=RANK(H12,$H$12:$H$21)
   

复制代码

第4题

1. 班级排名：=SUMPRODUCT(($A$12:$A$21=$A12)*($H$12:$H$21>$H12))+1
   
2. 
   

复制代码

第5题

1. 法1：=AVERAGEIF(A12:A21,"1",D12:D21)
   
2. 法2：=SUMPRODUCT((A12:A21=1)*(D12:D21))/COUNTIF(A12:A21,1)

复制代码