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

水吉果果

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

1. =sum(a:a)

复制代码

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

水吉果果

【函初201203班]- A18-水吉果果
作业一：

1. =SUMPRODUCT(($B$12:$B$21=A25)*($E$12:$G$21>=60))
   
2. =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. =COUNTIFS($A$12:$A$21,1,$E$12:$E$21,">=60",$F$12:$F$21,">=60",$G$12:$G$21,">=60")
   
2. =SUMPRODUCT(($A$12:$A$21=1)*($E$12:$E$21>=60)*($F$12:$F$21>=60)*($G$12:$G$21>=60))
   
3. =SUMIFS($A$12:$A$21,$A$12:$A$21,1,$E$12:$E$21,">=60",$F$12:$F$21,">=60",$G$12:$G$21,">=60")

复制代码

作业三：

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

复制代码

作业四：

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

复制代码

作业五：

1. =ROUND(AVERAGEIFS($D$12:$D$21,$A$12:$A$21,1,$C$12:$C$21,"男"),0)
   
2. =ROUND(SUMIFS($D$12:$D$21,$A$12:$A$21,1,$C$12:$C$21,"男")/COUNTIFS($A$12:$A$21,1,$C$12:$C$21,"男"),0)
   
3. =ROUND(SUMPRODUCT(($A$12:$A$21=1)*($C$12:$C$21="男")*$D$12:$D$21)/SUMPRODUCT(($A$12:$A$21=1)*($C$12:$C$21="男")),0)

复制代码

liziyuliziyu7

第一题：=SUMPRODUCT((B12:B21=A25)*(E12:G21>=60))
第二题：=SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
            =COUNTIFS(A12:A21,1,E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
第三题：=COUNTIF($H$12:$H$21,">="&H12)
第四题：=SUMPRODUCT(N(($A$12:$A$21=A12)*$H$12:$H$21>=H12))
第五题：=SUMPRODUCT((A12:A21=1)*    (C12:C21=C12)*D12:D21)/COUNTIFS(C12:C21,C12,A12:A21,1)
            =SUMIFS(D12:D21,C12:C21,C12,A12:A21,A12)/COUNTIFS(C12:C21,C12,A12:A21,A12)
【函初201203班]- A26-liziyuliziyu7

新华人家

【函初201203班]- A06-新华人家
作业一：

1. =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")

复制代码

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

复制代码

作业三：

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

复制代码

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

复制代码

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

复制代码

1. {=SUM(--($H$12:$H$21>H12))+1}

复制代码

作业四：

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

复制代码

作业五：

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

复制代码

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

复制代码

hongqiqqq

1. =COUNTIF(D12:G12,">=60")
   
2. 
   
3. =SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
   
4. =COUNTIFS(A12:A21,"=1",E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
   
5. 
   
6. =RANK(H12,H$12:H$21)
   
7. 
   
8. =SUMPRODUCT((((A$12:A$21=A12)*H$12:H$21)>H12)*1)+1
   
9. 
   
10. =AVERAGEIFS(D12:D21,A12:A21,"=1",C12:C21,"男")
    
11. =SUMPRODUCT((A12:A21=1)*1,(C12:C21="男")*1,D12:D21)/SUM((A12:A21=1)*(C12:C21="男"))(三键回车)

复制代码

【函初201203班]- A11-hongqiqqq

kouhogliang

【函初201203班]- A07-kouhogliang
作业一

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

复制代码

作业四

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

复制代码

作业五

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

复制代码

四舍五入
b48:b49选中 -> 右键->设置单元格格式->数字->数值->小数位数改为0->确定
方法比较笨,函数找到一个(ROUND),因为不是本课的,所以不敢用上

xuyouxm

【函初201203班】-A01-xuyouxm

作业一:

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

复制代码

1. =SUMPRODUCT((B12:B21=A25)*((E12:E21>=60)+(F12:F21>=60)+(G12:G21>=60)))

复制代码

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

复制代码

作业二:

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

复制代码

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

复制代码

作业三:

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

复制代码

作业四:

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

复制代码

作业五:

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

复制代码

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

复制代码

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

复制代码

1. =ROUND(AVERAGE(IF((A12:A21=1)*(C12:C21="男"),D12:D21)),0)
   
2. 
   
3. 数组

复制代码

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

复制代码

作业五:如果要四舍五入的话,可以用单元格格式进行设置,或者是在以上公式前面加ROUND函数也行.

lucking551

【函初201203班]- A24-lucking551

1、

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

复制代码

2、

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

复制代码

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

复制代码

3、

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

复制代码

4、

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

复制代码

5、

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

复制代码

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

复制代码

水上漂123

【函初201203班]- A09-水上漂123

1. 年级排名=SUMPRODUCT(($H$12:$H$21>H12)*1)+1
   
2. 班级排名=SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12))+1
   
3. 1、=SUMPRODUCT(($B$12:$B$21=A25)*(E12:G21>60))
   
4. 2、=SUMPRODUCT(($A$12:$A$21=1)*($E$12:$E$21>60)*($F$12:$F$21>60)*($G$12:$G$21>60))
   
5. =COUNTIFS($A$12:$A$21,"=1",$E$12:$E$21,">60",$F$12:$F$21,">60",$G$12:$G$21,">60")
   
6. 3、=RANK(H12,$H$12:$H$21)
   
7. 4、=SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12))+1
   
8. 5、=SUMPRODUCT(($A$12:$A$21=1)*($C$12:$C$21=$C$12)*$D$12:$D$21)/COUNTIFS($A$12:$A$21,$A$12,$C$12:$C$21,$C$12)
   
9. =SUMIFS($D$12:$D$21,$A$12:$A$21,"=1",$C$12:$C$21,"男")/SUMPRODUCT(($A$12:$A$21=1)*($C$12:$C$21="男"))
   

复制代码

laoda1228

第一题：

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

复制代码

第二题：空

第三题：

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

复制代码

第四题：空

第五题：

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

复制代码

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

复制代码