【函中20130301】第三讲C组作业上交帖【评分完成】

csmccdh

函中201301班]- C01-csmccdh上交作业：
第一题
方法一：=SUMPRODUCT(--(LEN(A3:A18)>=FIND(4,A3:A18&4))*B3:B18)
方法二：=SUMIF(A3:A18,"*"&4&"*",B3:B18)
方法三：=SUMPRODUCT(IFERROR(FIND(4,A3:A18)^0,),B3:B18)

第二题
方法一：=SUMPRODUCT((A$25:A$48=D25)*(B$25:B$48>LARGE(B$25:B$48,6)))
方法二：=SUMPRODUCT(--(A$25:A$48=D25),--(B$25:B$48>LARGE(B$25:B$48,6)))
方法三：=COUNTIFS(A$25:A$48,D25,B$25:B$48,">"&LARGE(B$25:B$48,6))

第三题
方法一：=SUMPRODUCT(--(A$54:A$60=D54),--(--B$54:B$60>LARGE(--B$54:B$60,6)))
方法二：=SUMPRODUCT((A$54:A$60=D54)*(--B$54:B$60>LARGE(--B$54:B$60,6)))

第四题
方法一：=SUMPRODUCT((A64:A70="A")+(A64:A70="C"),B64:B70)
方法二：=SUMPRODUCT(((A64:A70="A")+(A64:A70="C"))*ISNUMBER(B64:B70)*B64:B70)
方法三：=SUMPRODUCT(MMULT(--(A64:A70={"A","C"}),{1;1}),B64:B70)

水上漂123

【函中201301班】-C05-水上漂123
第一题

1. =SUMPRODUCT(ISNUMBER(FIND("04",$A$3:$A$18))*B3:B18)
   
2. =SUM(ISNUMBER(FIND("04",A3:A18))*B3:B18)数组

复制代码

第二题

1. =SUM(($A$25:$A$48=D25)*($B$25:$B$48>=LARGE($B$25:$B$48,5)))数组
   
2. =SUMPRODUCT(($B$25:$B$48>=LARGE($B$25:$B$48,5))*($A$25:$A$48=D25))

复制代码

第三题

1. =SUMPRODUCT(($A$54:$A$60=D54)*(--$B$54:$B$60>=LARGE(--$B$54:$B$60,5)))
   
2. =SUM(($A$54:$A$60=D54)*(--B54:B60>=LARGE(--$B$54:$B$60,5)))数组

复制代码

第四题

1. =SUMPRODUCT((($A$64:$A$70=$A$64)+($A$64:$A$70=$A$67)),B64:B70)
   
2. =SUM(($A$64:$A$70=$A$64)*(ISNUMBER($B$64:$B$70)*B64:B70),($A$64:$A$70=$A$67)*(ISNUMBER($B$64:$B$70)*B64:B70))数组

复制代码

zhs5483

【函中201301班】- C11-zhs5483
1. =SUMPRODUCT(B3:B18,LEN(A3:A18)-LEN(SUBSTITUTE(A3:A18,"4","",1)))
2.人数=SUMPRODUCT(($A$25:$A$48=D34)*($B$25:$B$48>=LARGE($B$25:$B$48,5)))
  总分=SUMPRODUCT(($A$25:$A$48=D34)*($B$25:$B$48>=LARGE($B$25:$B$48,5)),$B$25:$B$48)
3. =SUMPRODUCT((($A$54:$A$60=D54)*($B$54:$B$60>=LARGE($B$54:$B$60,5)))) 【函中201301班】- C11-zhs5483.zip (31.04 KB, 下载次数: 3)

2013-5-18 18:17 上传

点击文件名下载附件
4. =SUMPRODUCT((A64:A70="A")+(A64:A70="C"),B64:B70)

yjeeee

函中201301】--C07--yjeeee
第1题

1. 方法1
   
2. =SUMPRODUCT(1-ISERR(FIND(4,A3:A18)),B3:B18)
   
3. 方法2
   
4. =SUMPRODUCT(ISNUMBER(FIND(4,A3:A18))*B3:B18)

复制代码

第2题

1. 方法1
   
2. {=SUMPRODUCT(N(IF($B$25:$B$48>1,RANK($B$25:$B$48,$B$25:$B$48)<6,0)),N(($A$25:$A$48=D25)))}
   
3. 
   
4. 方法2
   
5. {=SUMPRODUCT(IF($B$25:$B$48>1,RANK($B$25:$B$48,$B$25:$B$48)<6,0)*($A$25:$A$48=D25))}

复制代码

第3题

1. =SUMPRODUCT(($B$54:$B$60*1>LARGE($B$54:$B$60*1,6))*($A$54:$A$60=D54))

复制代码

第4题

1. 方法1
   
2. =SUMPRODUCT(N(A64:A70<>"B"),--B64:B70)
   
3. 方法2
   
4. ＝SUMPRODUCT((A64:A70="A")+(A64:A70="C"),VALUE(B64:B70))

复制代码

孤单无所谓

【函中201301班】-C04孤单无所谓
1

1. =SUMPRODUCT(ISNUMBER(FIND("4",A3:A18))*B3:B18)

复制代码

2

1. =SUMPRODUCT((B$25:B$48>LARGE(B$25:B$48,6))*(A$25:A$48=D25))

复制代码

1. =SUMPRODUCT((B$25:B$48>LARGE(B$25:B$48,6))*(A$25:A$48=D25),B$25:B$48)

复制代码

3

1. =SUMPRODUCT((A$54:A$60=D54)*(--B$54:B$60>LARGE(--B$54:B$60,6)))

复制代码

4

1. =SUMPRODUCT((A64:A70<>"B")*1,B64:B70)

复制代码

云龙

1.

1. =SUMIF(A3:A18,"*4*",B3)

复制代码

1. =SUMPRODUCT(ISNUMBER(FIND(4,A3:A18))*B3:B18)

复制代码

2.

1. =SUMPRODUCT(($A$25:$A$48=D25)*($B$25:$B$48>LARGE($B$25:$B$48,6)))

复制代码

3.

1. =SUMPRODUCT((D54=$A$54:$A$60)*(-$B$54:$B$60<SMALL(-$B$54:$B$60,6)))

复制代码

4.

1. =SUMPRODUCT((A64:A70={"A","C"})*B64:B70)

复制代码

pang1688

【函中201301班】-c14-pang1688
1、

1. =SUMPRODUCT((LEN(SUBSTITUTE(A3:A18,SUBSTITUTE(A3:A18,"04",""),""))>1)*B3:B18)

复制代码

1. =SUMPRODUCT(ISNUMBER((FIND(4,A3:A18)))*1,B3:B18)

复制代码

2、不会
3、

1. =SUMPRODUCT((($A$54:$A$60=D54)*(RANK(--$B$54:$B$60,$F$54:$F$60)<=5))*1)

复制代码

4、

1. =SUMPRODUCT(((A64:A70="A")+(A64:A70="C")),(B64:B70))

复制代码

qqyyh

【函中201301班】-C06-qqyyh
1.=SUMPRODUCT((IF(ISERR(FIND("04",A3:A18)),"0","04")="04")*1,B3:B18)   数组公式
2.=SUMPRODUCT(($A$25:$A$48=D25)*(($B$25:$B$48)*1>LARGE($B$25:$B$48*1,6)))
3.=SUMPRODUCT(($A$54:$A$60=D54)*(($B$54:$B$60)*1>LARGE($B$54:$B$60*1,6)))
4.=SUMPRODUCT(((A64:A70=A64)+(A64:A70=A67)),B64:B70)

一江秋水

【函中201301班】-C03-一江秋水
作业1

1. =SUMPRODUCT(ISNUMBER(FIND(4,A3:A18))*B3:B18)

复制代码

作业2

1. =SUMPRODUCT((A$25:A$48=D25)*(B$25:B$48>LARGE(B$25:B$48,6)))

复制代码

1. =SUMPRODUCT((A$25:A$48=D25)*(B$25:B$48>LARGE(B$25:B$48,6))*B$25:B$48)

复制代码

作业3

1. =SUMPRODUCT((A$54:A$60=D54)*(((B$54:B$60)*1)>LARGE((B$54:B$60)*1,6)))

复制代码

作业4

1. =SUMPRODUCT((A64:A70={"a","c"})*ISNUMBER(B64:B70)*(B64:B70))

复制代码