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

魔魔

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

1. =sum(a:a)

复制代码

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

nuf2002

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

复制代码

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")
   

复制代码

1. 作业三： =RANK(H12,$H$12:$H$21)，下拉；

复制代码

1. 作业四： =SUMPRODUCT((($A$12:$A$21=A12)*$H$12:$H$21>=H12)+0) 下拉；

复制代码

1. 作业五：
   
2. 法1 =ROUND(AVERAGEIFS(D12:D21,A12:A21,"=1",C12:C21,"男"),0)；
   
3. 法2 =ROUND((SUMIFS(D12:D21,A12:A21,"=1",C12:C21,"男")/COUNTIFS(A12:A21,"=1",C12:C21,"男")),0)

复制代码

【函初201203班】- C05-nuf2002
谢谢老师和学委！

zac0226

【函初201203班]-c15-zac0226
作业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))

复制代码

方法2 不会
作业3

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

复制代码

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

复制代码

作业4

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

复制代码

作业5 方法1 方法2 不会

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

复制代码

zhj12043580

1=COUNTIFS(B12:B21,A25,E12:E21,">=60")+COUNTIFS(B12:B21,A25,F12:F21,">=60")+COUNTIFS(B12:B21,A25,G12:G21,">=60")
2=COUNTIFS(E12:E21,">=60",F12:F21,">=60",G12:G21,">=60",A12:A21,1)
2=SUMPRODUCT((A12:A21=1)*1,(E12:E21>=60)*1,(F12:F21>=60)*1,(G12:G21>=60)*1)
3=RANK(H12,$H$12:$H$21)
4=SUMPRODUCT(($H$12:$H$21>H12)*($A$12:$A$21=A12))+1
5=ROUND(AVERAGEIF(C12:C21,"男",D12:D21),0)
5=ROUND(SUMIF(C12:C21,"男",D12:D21)/COUNTIF(C12:C21,"男"),0)
好难啊!
【函初201203班】- c04-zhj

小志

1. 【函初201203班】- C06-小志
   
2. 第一次用这个模式交作业，老是弄不好，还请学委谅解。。
   
3. 学委辛苦了。
   
4. 1、=SUM(1*(IF(B12:B21=A25,D12:G21,0)>=60))

复制代码

2、

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")

复制代码

3、

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

复制代码

4、

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

复制代码

5、

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

复制代码

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

复制代码

xcslp

【函数初级201203班】-c37-xcslp
作业1、=COUNTA(E11:G11)
作业2、=SUM((A12:A21=1)*(COUNTIF(OFFSET(E12:G12,ROW(E1:E10),),">=60")=3))
             =SUMPRODUCT(($A$12:$A$21=1)*($E$12:$E$21>=60)*($F$12:$F$21>=60)*($G$12:$G$21>=60))
           =SUM(($A$12:$A$21=1)*($H$12:$H$21>=180))
作业3、=SUMPRODUCT((H$12:H$21>H12)*1)+1
作业4、=SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12))+1
作业5、=AVERAGE(IF(($A$12:$A$21=1)*($C$12:$C$21="男"),$D$12:$D$21))
           =SUM(($A$12:$A$21=A12)*($C$12:$C$21=$C$12)*$D$12:$D$21)/SUM(($A$12:$A$21=A12)*($C$12:$C$21=$C$12))

Joyce_chen

【函数初级201203班】-C21-Joyce_chen

1. 作业 1：=SUMPRODUCT((B12:B21=A25)*(E12:G21>=60))
   
2. 作业 2：a\=SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
   
3. b\=COUNTIFS(A12:A21,1,E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
   
4. c\{=SUM(IF((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60),1,0))}
   
5. 作业 3：=RANK(H12,$H$12:$H$21)
   
6. 作业 4：=SUMPRODUCT(COUNTIF(A12,$A$12:$A$21)*(($H$12:$H$21>H12)*1))+1
   
7. 
   
8. 作业 5：a\=ROUND(AVERAGEIFS(D12:D21,C12:C21,"男"),0)
   
9. b\{=AVERAGE(IF((A12:A21=1)*(C12:C21="男"),D12:D21))} 设备单元格格式，数值小数点位数为0

复制代码

虎刺梅

【函初201203班】- C19-虎刺梅
作业一：=COUNTIF(E12:G12,">60")
作业二：
      方法一：=SUMPRODUCT((A12:A21=A12)*(E12:E21>60)*(F12:F21>60)*(G12:G21>60))
         方法二：=COUNTIFS(E12:E21,">=60",F12:F21,">=60",G12:G21,">=60",A12:A21,1)三键结束
作业三：=RANK(H12,$H$12:$H$21)
作业四：=SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12)*1)+1
作业五：
      方法一：=AVERAGEIFS(D12:D21,A12:A21,"1",C12:C21,"男")
         方法二：=AVERAGE(IF((A12:A21=A12)*(C12:C21=C12),D12:D21))三键结束

百度加论坛，终于做好了，希望是正确的。

shandianzhiwu

【函初201203班】- C10-shandianzhiwu
题1
=SUM(IF(VLOOKUP(A25,B12:G21,4,0)>=60,1,0),IF(VLOOKUP(A25,B12:G21,5,0)>=60,1,0),IF(VLOOKUP(A25,B12:G21,6,0)>=60,1,0))

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

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

题4
不会

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

liaoxingchao

【【函初201203班】- C25-liaoxingchao

第一题：=COUNTIF(E12:G12,">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(A12:A21,"1",E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
第三题：=SUMPRODUCT((H$12:H$21>H12)*1)+1
第四题：=SUMPRODUCT((($H$12:$H$21)*($A$12:$A$21=A12)>H12)*1)+1
第五题：1、=SUMPRODUCT(MOD(A12:A21,2)*(C12:C21="男")*D12:D21)/SUMPRODUCT(MOD(A12:A21,2)*(C12:C21="男"))
              2、=ROUND(SUMIF(C12:C21,"男",D12:D21)/COUNTIF(C12:C21,"男"),0)