【函中201401】第5讲D组作业上交贴

danny-fei

1、作业统一贴公式，需要时请写明操作步骤。
2、跟帖不要重复占楼,要修改直接在原楼层编辑
3、作业截止时间：2014年9月24日晚18:00，过期不予批改
4、2014-9-23(下周二)开始评分,之后修改公式的,评分不改
5、评分标准:答案正确+15/+15,个别优秀的+1-5分.

木偶fenzi

1

1. =COUNTIFS($$$$A$$$$4:$$$$A$$$$21,$$$$G4,B$$$$4:B$$$$21,">0")

复制代码

1. =SUM(($$$$A$$$$4:$$$$A$$$$21=$$$$G4)*ISNUMBER(B$$$$4:B$$$$21))

复制代码

1. =SUM(($$$$A$$$$4:$$$$A$$$$21=$$$$G4)*(LEN(B4:B21)>0))

复制代码

1. =COUNT(0/(($$$$A$$$$4:$$$$A$$$$21=$$$$G4)*LEN(B$$$$4:B$$$$21)>0))

复制代码

2

1. =SUMPRODUCT((MONTH(A27:A42)=F27)*((B27:B42=G27)+(B27:B42=G28))*C27:C42)

复制代码

1. =SUMPRODUCT((MONTH(A27:A42)=F27)*(B27:B42={"甲","乙"})*C27:C42)

复制代码

1. =SUM(SUMIFS(C27:C42,D27:D42,F27,B27:B42,{"甲","乙"}))

复制代码

3  选中区域数组公式

1. =COUNTIFS(A49:A70,E49:E52,C49:C70,{">59","<60"})

复制代码

4 只会辅助咧
辅助咧

1. =YEAR(B2)&MONTH(B2)

复制代码

1. =TEXT(B2,"yyyym")

复制代码

公式

1. =MAX(SUMIF(业务数据!J2:J380,{"2013","2014"}&ROW(1:12),业务数据!D2:D380))

复制代码

lmze2000

第1题：

1. =TEXT(COUNTIFS($A$4:$A$21,$G4,B$4:B$21,">0"),"0;;")

复制代码

1. =TEXT(SUM(($A$4:$A$21=$G4)*(B$4:B$21<>"")),"0;;")

复制代码

1. =TEXT(COUNTIFS($A$4:$A$21,$G4,OFFSET($A$3,1,MATCH(H$3,$B$3:$E$3),18),">0"),"0;;")

复制代码

第2题：

1. =SUMPRODUCT((MONTH(A27:A42)=F27)*C27:C42*(B27:B42={"甲","乙"}))

复制代码

第3题：

1. =COUNTIFS(A$49:A$70,E49,C$49:C$70,{">=60","<60"})

复制代码

区域数组
第4题：
H:H辅助列  =year(b2)&month(b2) 或者TEXT(B2,"yyyym")

1. =MAX(SUMIF(H:H,{"2013","2014"}&ROW(1:12),(D:D)))

复制代码

yuan1987

1、=IF(COUNTIFS($A$4:$A$21,$G4,B$4:B$21,">0")=0,"",COUNTIFS($A$4:$A$21,$G4,B$4:B$21,">0"))
2、
=SUMPRODUCT((MONTH(A27:A42)=F27)*OR(B27:B42=G27,B27:B42=G28)*(C27:C42))
3、=IF(COUNTIFS($A$49:$A$70,$E49,$C$49:$C$70,">=60")=0,"",COUNTIFS($A$49:$A$70,$E49,$C$49:$C$70,">=60"))
4、不会

《渐行渐远》

1. 1.=IF(COUNTIFS($A$4:$A$21,$G4,B$4:B$21,">0")=0,"",COUNTIFS($A$4:$A$21,$G4,B$4:B$21,">0"))
   
2. 2.=SUMPRODUCT((MONTH(A27:A42)=F27)*(B27:B42={"甲","乙"})*C27:C42)
   
3. =SUM(IF(MONTH(A27:A42)=F27,C27:C42)*(B27:B42={"甲","乙"}))
   
4. =SUM(IF((MONTH(A27:A42)=F27)*(B27:B42={"甲","乙"}),C27:C42))
   
5. 3.=COUNTIFS($A$49:$A$70,$E49,$C$49:$C$70,CHOOSE(LEN(F$48)-1,">=","<")&60)
   
6. 4.=MAX(SUMIF(业务数据!H:H,ROW(1:12),业务数据!D:D))

复制代码

Eric_f765

老师，只会做2题，期待答案，老师辛苦了。
老师，第二题做出来了，不知道对不对，修改右边格式后可以用DSUM，老师可以看下附件。谢谢。

1，=COUNTIF($A$4:$A$21,$G4)-COUNTIFS($A$4:$A$21,$G4,B$4:B$21,"")
2，=SUMIFS($C$27:$C$42,$B$27:$B$42,"甲",$D$27:$D$42,"3")+SUMIFS($C$27:$C$42,$B$27:$B$42,"乙",$D$27:$D$42,"3")
      =DSUM(B26:D42,C26,F26:G28)
3，=IF(F$48="合格",COUNTIFS($A$49:$A$70,$E49,$C$49:$C$70,">=60"),COUNTIFS($A$49:$A$70,$E49,$C$49:$C$70,"<60"))

天棋

第一题：=TEXT(COUNT(0/(($A$4:$A$21=$G11)*B$4:B$21)),"0;;")
              =IF(COUNTIFS($A$3:$A$21,$G17,B$3:B$21,">0"),COUNTIFS($A$3:$A$21,$G17,B$3:B$21,">0"),"")
第二题：=SUMPRODUCT((MONTH(A27:A42)=F27)*((B27:B42=G27)+(B27:B42=G28))*C27:C42)
第三题：=INDEX(COUNTIFS($A$49:$A$70,$E55,$C$49:$C$70,{"<60",">=60"}),8-COLUMN())
第四题：=MAX(SUMIF(业务数据!B:B,">="&DATE(2013,ROW(1:20),1),业务数据!D:D)-SUMIF(业务数据!B:B,">="&DATE(2013,ROW(2:21),1),业务数据!D:D))

zhangrongmei

1,=SUMPRODUCT(($A$4:$A$21=$G4)*($B$3:$E$3=B$3)*(B$4:B$21<>""))
2,=SUM(SUMIFS(C27:C42,D27:D42,F27,B27:B42,{"甲","乙"}))=SUM(SUMIFS(C27:C42,D27:D42,F27,B27:B42,{"甲","乙"}))加个行辅助列D列
3，=SUM(($A$49:$A$70=$E49)*(IF($F$48=F$48,$C$49:$C$70>=60,$C$49:$C$70<60)))加三键
4，=MAX(业务数据!D2:D380)

C.Deny

D08：C.Deny上交第五讲作业
1.=IF(SUMPRODUCT(($A$4:$A$21=$G4)*(B$4:B$21<>""))=0,"",SUMPRODUCT(($A$4:$A$21=$G4)*(B$4:B$21<>"")))下拉右拉
=TEXT(MMULT(--(G4:G7=TRANSPOSE(A4:A21)),(B4:E21<>"")*COLUMN(A:A)),"0;;;")区域数组
2.=SUMPRODUCT((MONTH(A27:A42)=F27)*(B27:B42={"甲","乙"})*C27:C42)
3.=COUNTIFS($A$49:$A$70,E49:E52,$C$49:$C$70,{">=60","<60"})*{1,1}区域数组
4.先修改D202数据源，=MAX(MMULT(--(ROW(1:12)=TRANSPOSE(MONTH(业务数据!B2:B380))),(YEAR(业务数据!B2:B380)={2013,2014})*业务数据!D2:D380))数组

tgy0451

交作业喽!