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

zjcat35

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

1. =sum(a:a)

复制代码

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

dididing

【函初201203班】-E20-dididing


作业一：

第一种最初的笨办法：

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

复制代码

第二种，觉得上面太麻烦了，试了一下另一个函数

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

复制代码

第三种：最后，在别人的提点下回忆了另另外一个函数，尝试使用

1. =COUNTIF(OFFSET(B11:G21,MATCH(A25,B12:B21,),3,1,3),">=60")

复制代码

作业二：

方法1：

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

复制代码

方法2：

1. =SUMPRODUCT((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：

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

复制代码

方法2：

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

复制代码

新月gg

【函初201203班】- E10-新月gg



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


=SUMPRODUCT(($A$12:$A$21=1)*(E12:E21>60)*(F12:F21>60)*(G12:G21>60))
=COUNTIFS(A12:A21,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)+1


=ROUND(AVERAGEIF(C12:C21,"男",D12:D21),0)
=ROUND(AVERAGE(SUMPRODUCT((C12:C21="男")*(D12:D21))*2)/10,0)

东_木

我是E36

1,想了三个办法...

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

复制代码

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

复制代码

1. =COUNT(OFFSET(B12,MATCH(A25,B12:B21,0)-1,4,1,3),">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,0)

复制代码

4,课堂上的解法了,直接抄过来.

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

复制代码

5.1,

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

复制代码

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

复制代码

lisa19860622

1. 题1

复制代码

【函数初级201203班】-E35-lisa19860622
题1

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

题5
方法一:

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

复制代码

=ROUND(SUMIF(C12:C21,C12,D12:D21)/COUNTIF(C12:C21,C12),0) 方法三: =ROUND(SUMPRODUCT((C12:C21=C12)*1,D12:D21)/COUNTIF(C12:C21,C12),0)

题2
方法一:

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

复制代码

yjeeee

【函初201203班】-E28-yjeeee

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

复制代码

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

复制代码

1. 作业3
   
2. =RANK(H12,$H$12:$H$21)

复制代码

1. 作业4
   
2. ＝

复制代码

1. 作业5
   
2. 方法1 =INT(SUMIFS(D12:D21,C12:C21,"男",A12:A21,"=1")/COUNTIFS(C12:C21,"男",A12:A21,"=1")+0.5)
   
3. 方法2 =INT(SUMPRODUCT((A12:A21=1)*(C12:C21="男")*D12:D21)/COUNTIFS(C12:C21,"男",A12:A21,"=1")+0.5)

复制代码

@wsm

作业一 统计表1中某考生及格的科目数：
=COUNTIFS(E12:G12,">60")
作业二 至少用二种方法统计表1中1班级考生所有成绩都及格的考生人数:
=COUNTIFS($A$12:$A$21,$A$12,$E$12:$E$21,">60",$F$12:$F$21,">60",$G$12:$G$21,">60")
=SUMPRODUCT(($A$12:$A$21=$A$12)*($E$12:$E$21>60)*($F$12:$F$21>60)*($G$12:$G$21>60))
作业三 统计表1中总成绩的年级排名（美国式排名）
=RANK(H12,$H$12:$H$21)
统计表1中总成绩的班级排名（美国式排名）
=SUMPRODUCT((($H$12:$H$21)*($A$12:$A$21=A12)>H12)*1)+1
作业五 用二种方法，统计表1中1班男生的平均年龄，年龄4舍5入为整数：
=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))
=SUMPRODUCT(($A$12:$A$21=$A$12)*($C$12:$C$21=$C$12),$D$12:$D$21)/COUNTIFS($A$12:$A$21,$A$12,$C$12:$C$21,$C$12)
因为我的是03板的，所以的的07板函数无法在作业里体现出来还请学委谅解！

ct126_ct

1. =SUMPRODUCT(($B$12:$B$21="刘宇")*($E$12:$G$21>=60))

复制代码

1. =SUMPRODUCT(($A$12:$A$21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))

复制代码

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

复制代码

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

复制代码

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

复制代码

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

复制代码

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

复制代码

【函数初级201203班】E31 ct126_ct.

张鹏887222

【函初201203班】- E14-张鹏887222
作业一：

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

复制代码

作业二：发1

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

复制代码

法2
作业三：

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

复制代码

作业四：

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

复制代码

作业五：
法1

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

复制代码

法2  

第一、二、四重新编辑了，谢谢学委。

xxdd6

【函初201203班】-e32-xxdd6{:181:}
作业一

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

复制代码

作业二

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

复制代码

1. 法3={SUM((A12:A21=1)*((E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60)*1))}

复制代码

作业三

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

复制代码

1. 法2=SUMPRODUCT(($H$12:$H$21>H13)*1)+1

复制代码

作业四

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

复制代码

作业五

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

复制代码

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

复制代码