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

水吉果果

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

1. =sum(a:a)

复制代码

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

opqazxc

【函初201203班]- B21-opqazxc
作业1

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

复制代码

作业2

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

复制代码

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

复制代码

作业3   因为没重复

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

复制代码

作业4  同3

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

复制代码

作业5

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

复制代码

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

复制代码

蓝火雨

【函初201203班】- B01-蓝火雨
作业一:B25单元格输入公式<=COUNTIFS(B12:B21,A25,E12:E21,">=60")+COUNTIFS(B12:B21,A25,F12:F21,">=60")+COUNTIFS(B12:B21,A25,G12:G21,">=60")>
作业二:B30单元格输入公式<=SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))>,B31单元格输入公式<=SUM(IF((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60),1,0))>按三键回车即可.
作业三I12单元格输入公式<=RANK(H12,$H$12:$H$21)>往下拉完成
作业四:J12单元格输入公式<=SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12))+1>往下拉完成.
作业五:B48单元格输入公式<=ROUND(SUMIF(C12:C21,"男",D12:D21)/COUNTIF(C12:C21,"男"),)> B49单元格输入公式<=ROUND(SUMPRODUCT((C12:C21="男")*(D12:D21))/COUNTIF(C12:C21,"男"),)>

Laphe

【作业一】统计表1中某考生及格的科目数：

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

复制代码

【作业二】至少用二种方法统计表1中1班级考生所有成绩都及格的考生人数:

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中总成绩的年级排名（美国式排名）

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

复制代码

【作业四】统计表1中总成绩的班级排名（美国式排名）

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

复制代码

【作业五】用二种方法，统计表1中1班男生的平均年龄，年龄4舍5入为整数：

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

复制代码

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

复制代码

--------------------------------------------------------------------------------------------------------------------------------------------------
【函初201203班]- B33-Laphe

965502110

【函初201203班】-B03-965502110

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

复制代码

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

复制代码

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

复制代码

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

复制代码

复制代码

石磊.

【函初201203班】- B05-石磊.

1. 作业一 =SUMPRODUCT((B12:B21=A25)*((E12:E21>=60)+(F12:F21>=60)+(G12:G21>=60)))
   
2. 作业二
   
3. =COUNTIFS(A12:A21,A12,E12:E21,">=60",F12:F21,">=60",G12:G21,">=60",G12:G21,">=60")
   
4. =SUMPRODUCT((A12:A21=A12)*(E12:E21>59)*(F12:F21>59)*(G12:G21>59))
   
5. 作业三 =RANK(H12,$H$12:$H$21)
   
6. 作业四=SUMPRODUCT(($H$12:$H$21>H12)*($A$12:$A$21=A12))+1
   
7. 作业五=SUMPRODUCT((A12:A21=1)*(C12:C21="男")*D12:D21)/SUMPRODUCT((C12:C21="男")*(A12:A21=1))
   
8. =SUMIFS(D12:D21,A12:A21,A12,C12:C21,"男")/COUNTIFS(A12:A21,1,C12:C21,C12)

复制代码

daichaodaichao

【函初201203班】-B11-daichaodaichao

1. 作业1.=SUMPRODUCT((B12:B21=A25)*((E12:E21>=60)+(F12:F21>=60)+(G12:G21>=60)))
   
2. 作业2.方法1.=COUNTIFS(A12:A21,"1",E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
   
3. 作业2.方法2.=SUMPRODUCT((A12:A21=A12)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
   
4. 作业3.方法1.=RANK(H12,$H$12:$H$21)下拉完成
   
5. 作业3.方法2.=SUMPRODUCT(($H$12:$H$21>H12)*1)+1下拉完成
   
6. 作业4.=SUMPRODUCT((($A$12:$A$21=A12)*($H$12:$H$21)>=(H12))*1)下拉完成
   
7. 作业5.方法1.=ROUND(AVERAGEIFS(D12:D21,A12:A21,"1",C12:C21,"男"),0)
   
8. 作业5.方法2.=ROUND(SUMIFS(D12:D21,A12:A21,"1",C12:C21,"男")/COUNTIFS(A12:A21,"1",C12:C21,"男"),0)

复制代码

hannanrenjie

【函数初级201203班】B26 hannanrenjie
作业一，刘宇各科成绩在E12:G12
=COUNTIF(E12:G12,">=60")
作业二，
方法1 ，=COUNTIFS(A12:A21,"=1",E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
方法2， =SUMPRODUCT((($A$12:$A$21=1)*($E$12:$E$21>=60)*(F12:F21>=60)*(G12:G21>=60)))
作业三，区域绝对引用，下拉完成时区域 不会改变。
=RANK(H12,$H$12:$H$21)
作业四，A12为1班，第二个公式直接写成“1”，答案一致，不知行不？
=SUMPRODUCT((($H$12:$H$21)*($A$12:$A$21=A12)>H12)*1)+1
=SUMPRODUCT((($H$12:$H$21)*($A$12:$A$21=1)>H12)*1)+1
作业五，方法2是笨法子，用总年龄再除人数
方法1，=AVERAGEIFS(D12:D21,A12:A21,A12,C12:C21,C12)
方法2， =SUMIFS(D12:D21,A12:A21,"1",C12:C21,"男")/COUNTIFS(A12:A21,"1",C12:C21,"男")

漫天独舞

1. 作业1、=SUMPRODUCT((B12:B21=A25)*(E12:G21>=60)*1)
   
2. 作业2、=COUNTIFS(E12:E21,">=60",F12:F21,">=60",G12:G21,">=60",A12:A21,A12)
   
3. =SUMPRODUCT((A12:A21=A12)*((E12:E21)*(F12:F21)*(G12:G21)>=60))
   
4. 作业3、=RANK($H12,$H$12:$H$21)
   
5. 作业4、=SUMPRODUCT((($H$12:$H$21)*($A$12:$A$21=A12)>H12)*1)+1
   
6. 作业5、=ROUNDUP(AVERAGEIF(C12:D21,"=男",D12:D21),0)
   
7. =ROUND(SUMIF(C12:C21,"男",D12:D21)/COUNTIF(C12:C21,"男"),0)

复制代码

552314637

【函初201203班]- B34-552314637
作业一　＝=COUNTIF(E12:G12,">60")
作业三　I12==RANK(H12,$H$12:$H$21)
其他的太难了，没做出来