[201401函中]E组第五讲作业提交贴

26759761@qq.com

1、作业统一贴公式(需要过程的直接录屏).
2、跟帖不要重复占楼,要修改直接在原楼层编辑
3、评分标准:答案正确+15/+15,个别优秀的+1-10分.不贴公式直接上附件的,评分标准上,经验BB各扣5分.即+10/+10

天龙九部

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

复制代码

SUMPRODUCT((MONTH(A27:A42)=F27)*(B27:B42=G27)*C27:C42)+SUMPRODUCT((MONTH(A27:A42)=F27)*(B27:B42=G28)*C27:C42)
COUNTIFS($A$49:$A$70,$E49,$C$49:$C$70,LOOKUP(F$48,{"不合格","合格"},{"<60";">=60"}))
IF((MOD(MONTH(业务数据!B2:B380),2)=1),MAX(业务数据!D2:D380),)

sinowonder

1、=COUNTIFS($A$4:$A$21,$G10,B$4:B$21,">0")，自定义单元格格式为：0;0;;
2、=SUMPRODUCT(C27:C42*(MONTH(A27:A42)=F27)*((B27:B42=G27)+(B27:B42=G28)))
3、=COUNTIFS($A$49:$A$70,$E49,$C$49:$C$70,IF(F$48="合格",">=60","<60"))
4、{=MAX(SUMIF(业务数据!B:B,">"&EDATE(41274,ROW(1:20)-1),业务数据!D1)-SUMIF(业务数据!B:B,">"&EDATE(41274,ROW(1:20)),业务数据!D1))}

76868850

1.1=COUNTIFS($A$3:$A$21,$G4,B$3:B$21,">0")
    =SUMPRODUCT(($A$4:$A$21=$G4)*(B$4:B$21<>""))
    =DCOUNT($A$3:$E$21,B$3,$G$3:$G4)
2=SUMPRODUCT((MONTH(A27:A42)=F27)*C27:C42)-SUMPRODUCT((MONTH(A27:A42)=F27)* (B27:B42="丙")*C27:C42)
3、合格=COUNTIFS($A$49:$A$70,E49,$C$49:$C$70,">=60")
    不合格=COUNTIFS($A$49:$A$70,E49,$C$49:$C$70,"<60")
4、=LARGE(SUMIF(业务数据!H:H,ROW(1:12),业务数据!D:D),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=SUMPRODUCT(((MONTH(A27:A42)&B27:B42=F27&G27)+(MONTH(A27:A42)&B27:B42=F27&G28))*C27:C42)
3=SUMPRODUCT(($A$49:$A$70=$E49)*(LOOKUP($C$49:$C$70,{0;60},{"不合格";"合格"})=F$48))
4=MAX(IFERROR(SUMIF(OFFSET(业务数据!D1,IFERROR(LOOKUP(DATE(ROW(2013:2014),COLUMN(A:L),0),业务数据!B2:B500,ROW(2:500)),0),,LOOKUP(DATE(ROW(2013:2014),COLUMN(B:M),0),业务数据!B2:B500,ROW(2:500))-IFERROR(LOOKUP(DATE(ROW(2013:2014),COLUMN(A:L),0),业务数据!B2:B500,ROW(2:500)),0)),">0"),0))
或=MAX(MMULT(TRANSPOSE(业务数据!D2:D380),--(TEXT(业务数据!D2:D380,"e年m月")=TEXT(EDATE("2012-12-1",COLUMN(A:X)),"e年m月"))))
或=MAX(MMULT(COLUMN(A:NO)^0,TRANSPOSE((INDEX({2013,2014}&ROW(1:12),MOD(ROW(1:24)-1,12)+1,MOD(CEILING(ROW(1:24),12)/12-1,2)+1)=TRANSPOSE(TEXT(业务数据!D2:D380,"yyyym")))*TRANSPOSE(业务数据!D2:D380)))

十五的月亮

1. 1.=TEXT(COUNTIFS($A$4:$A$21,$G4,B$4:B$21,">1"),"0;;;")
   
2. 2.=SUMIFS(C27:C42,D27:D42,F27,B27:B42,"甲")+SUMIFS(C27:C42,D27:D42,F27,B27:B42,"乙")
   
3. =SUM(SUMIFS(C27:C42,D27:D42,F27,B27:B42,{"甲";"乙"}))
   
4. 3.=COUNTIFS(A49:A70,E49:E52,C49:C70,{">=60","<60"})
   
5. 4.加辅助列=MAX(SUMIFS(业务数据!D2:D380,业务数据!I2:I380,{"2014","2013"},业务数据!H2:H380,ROW(1:12)))

复制代码

只会简单的

ldxhzy

第1题：
         H4公式： （下拉、右拉）
                    =TEXT(SUMPRODUCT(--($A$4:$A$21=$G4),--(LEN(B$4:B$21)>0)),"##")   
         或 H4:H7数组公式：  （右拉）
                     =TRANSPOSE(COUNTIFS($A$4:$A$21,TRANSPOSE($G$4:$G$7),B4:B21,">0"))   
第2题：
         =SUMPRODUCT(--(MONTH(A27:A42)=F27),C27:C42)
第3题：
         F49公式：=COUNTIFS($A$49:$A$70,E49,$C$49:$C$70,">=60")     （下拉）
         G49公式：=COUNTIFS($A$49:$A$70,E49,$C$49:$C$70,"<60")    （下拉 ）
第4题：

eaglexs

E04:eaglexs
要出差，先交作业：
第一题，数组公式右拉下拉：

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

复制代码

第二题：
合并单元格中用数组公式无效，换sumproduct：

1. =SUMPRODUCT((MONTH(A27:A42)=F27)*((B27:B42=INDIRECT("g27"))+(B27:B42=INDIRECT("g28")))*C27:C42)

复制代码

第三题，数组公式：

1. =SUM(($A$49:$A$70=$E49)*IF(COLUMN(A1)=1,$C$49:$C$70>=60,$C$49:$C$70<60))

复制代码

第四题还没完成，先交了以后再补

一杯清荼

题1：

1. =TEXT(COUNT(0/(($$$$A$$$$4:$$$$A$$$$21=$$$$G4)*B$$$$4:B$$$$21)),"0;;")

复制代码

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

复制代码

题2：

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

复制代码

1. =SUMPRODUCT(MMULT(SUMIFS(C27:C42,A27:A42,">=41"&{699,730},B27:B42,G27:G28)*{1,-1},{1;1}))

复制代码

题3：

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

复制代码

题4：

1. =MAX(SUMIFS(业务数据!D:D,业务数据!B:B,">"&DATE(201&{3,4},ROW(1:11),),业务数据!B:B,"<"&DATE(201&{3,4},ROW(2:12),1)))
   

复制代码

ANDY0393

浏览器又抽风了，多了n个$,我改了多回还这样，学委原谅我吧，谢谢
第1题：

1. 普通公式，右拉下拉：
   
2. =SUMPRODUCT(($A$4:$A$21=$G4)*(B$4:B$21<>""))

复制代码

1. 数组公式，右拉下拉：
   
2. =COUNT(0/(($A$4:$A$21=$G4)*(B$4:B$21<>"")))多单元格数组公式
   
3. =MMULT(N(G4:G7=TRANSPOSE(A4:A21)),N(B4:E21<>""))

复制代码

第2题：

1. =SUMPRODUCT((MONTH(A27:A42)=3)*(B27:B42<>"丙")*C27:C42)
   
2. =SUMPRODUCT((MONTH(A27:A42)=3)*(B27:B42={"甲","乙"})*C27:C42)

复制代码

第3题：

1. F49：G49输入多单元格数组公式，下拉
   
2. =COUNTIF(INDIRECT("C"&MATCH(E49,A49:A70,)+48&":C"&COUNTIF(A49:A70,"<="&E49)+48),{">=60","<60"})

复制代码

第4题：

1. 数组公式(原来数据源多个坑，汗）
   
2. =MAX(MMULT(N((MOD(ROW(12:35),12)+1&2012+INT(ROW(12:35)/12))=TRANSPOSE(MONTH(业务数据!B2:B380)&YEAR(业务数据!B2:B380))),--(SUBSTITUTE(业务数据!D2:D380," ",))))

复制代码

D202特殊字符，公式复制时可能会报错，请学委谅解下，嘿嘿