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

meililin

第一题：

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

复制代码

第二题：

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

复制代码

第三题：
合格

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

复制代码

不合格

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

复制代码

滴答滴

第一题：
=TEXT(SUM(($A$4:$A$21=$G5)*(B$4:B$21<"")),"0;;")
=DCOUNT($A$3:$E$21,H$3,$G$3:$G4)-SUM(H$3:H3)
=SUM(($A$4:$A$21=$G4)*ISNUMBER(B$4:B$21))
=COUNTIFS($A$4:$A$21,$G4,B$4:B$21,">0")
第二题:
=SUMPRODUCT((MONTH(A27:A42)=F27)*((B27:B42=G27)+(B27:B42=G28))*C27:C42)
或者数据库函数
=DSUM(A26:D42,C26,F30:G32)
(使用辅助列)

=DSUM(A26:C42,C26,F34:H36)
（调整条件表格结构）
见图

第三题：
分开公式
=SUM((A$49:A$70=E49)*(C$49:C$70>=60))
=SUM((A$49:A$70=E49)*(C$49:C$70<60))
一个公式
=SUM(N(($A$49:$A$70=$E49)*(INT($C$49:$C$70/60)=MOD(COLUMN(A1),2))))
高阶函数
=COUNTIFS($A$49:$A$70,$E49,$C$49:$C$70,CHAR(64-2*COLUMN(A1))&59.5)
=COUNTIFS($A$49:$A$70,$E49,$C$49:$C$70,CHAR(74-2*COLUMN())&59.5)
F49:G50区域数组公式下拉
=TRANSPOSE(FREQUENCY(IF(A$49:A$70=E49,-C$49:C$70),-59.5))

第四题：
业务数据表做辅助列I，公式I2=year(b2)&month(b2)
=MAX(SUMIFS(业务数据!D:D,业务数据!I:I,"*"&{2013,2014}&ROW(1:12)))
不用辅助列：
=MAX(SUMIFS(业务数据!D:D,业务数据!B:B,"<"&DATE(2013,ROW(2:25),1),业务数据!B:B,">"&DATE(2013,ROW(1:24),)))
=MAX(MMULT(-(TRANSPOSE(TEXT(业务数据!B2:B380,"em"))=TEXT(EOMONTH("2012-9",ROW(4:27)),"em")),-业务数据!D2:D380))

特别说明D202含有特殊文本，无法进行数字运算，需要差错后修正为数字，若源数据源不允许修改，可用substitute进行处理后再运算，公式为
=MAX(MMULT(-(TRANSPOSE(TEXT(业务数据!B2:B380,"em"))=TEXT(EOMONTH("2012-9",ROW(4:27)),"em")),-SUBSTITUTE(业务数据!D2:D380," ",)))

XMergeD

1

1. =COUNT(0/(($$A$$4:$$A$$21=$$G4)*($$B$$3:$$E$$3=H$$3)*$$B$$4:$$E$$21))

复制代码

2 先取消合并单元格，数组公式输入后再合并

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

复制代码

3 选中F49:G49 输入数组公式

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

复制代码

4
计算中。。。

tt89004808

1. 1.=COUNTIFS($A$4:$A$21,$G4,B$4:B$21,">0")
   
2. 2.=DSUM(A26:C42,C26,E26:G28)
   
3. E26 F26分别改为日期，E27 E28改为>=2014-3-1，F27 F28改为<=2014-3-31
   
4. 3.=COUNTIFS($C$49:$C$70,">=60",$A$49:$A$70,$E49)
   
5. =COUNTIFS($C$49:$C$70,"<60",$A$49:$A$70,$E49)
   
6. 4.=SUMIF(业务数据!H:H,ROW(1:12),业务数据!D:D)

复制代码

yqnh

E11:yqnh上次第五课作业，请学委老师指导
第一题

1. =COUNTIFS($$A$$4:$$A$$21,$$G4,B$$4:B$$21,">0")
   
2. 数组右拉下拉

复制代码

第二题
先将H27：H28合并单元格以消

1. =SUM((MONTH(A27:A42)=F27)*(B27:B42=TRANSPOSE(G27:G28))*C27:C42)数组

复制代码

第三题

1. F49：G49=IN最大DEX(TRANSPOSE(FREQUENCY(IF(A$$49:A$$70=E49,C$$49:C$$70),59)),IF({1,0},2,1))数组下拉

复制代码

第四题
先使用ISNUMBER判断D列D185、D189、D202不为数字
I列插入辅助列 i2==--SUBSTITUTE(D2,"&nbsp;","")下拉
J列插入辅助列 J= =TEXT(B2,"YYMM")
=MAX(SUMIF(业务数据!J2:J380,业务数据!J2:J380,业务数据!I2))

hsl215

1、数值型的

1. =IF(COUNT(1/(($A$4:$A$21=$G4)*B$4:B$21)),COUNT(1/(($A$4:$A$21=$G4)*B$4:B$21)),"")
   
2. =IF(DCOUNT($A$3:$E$21,H$3,$G$3:$G4)-SUM(H$3:H3),DCOUNT($A$3:$E$21,H$3,$G$3:$G4)-SUM(H$3:H3),"")

复制代码

文本型的

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

复制代码

2、

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

复制代码

数组

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

复制代码

数据库函数

日期	日期	类别	类别
>=2014/3/1	<2014/4/1	甲
>=2014/3/1	<2014/4/1		乙

1. =DSUM(A26:C42,C26,K26:N28)

复制代码

3、均为数组，两个函数

1. =COUNT(1/((A$49:A$70=E49)*(C$49:C$70>=60)))
   
2. =COUNT(1/(A$49:A$70=E49))-F49

复制代码

一下函数右拉下拉

1. =IF(F$48="合格",COUNT(1/(($A$49:$A$70=$E49)*($C$49:$C$70>=60))),COUNT(1/($A$49:$A$70=$E49))-$F54)

复制代码

一个函数下拉

1. =IF({1,0},COUNT(1/((A$49:A$70=E49)*(C$49:C$70>=60))),COUNT(1/(A$49:A$70=E49))-F54)

复制代码

4、暂时只写出了带辅助列的公式，
H列的辅助列的公式为

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

复制代码

公式为

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

复制代码

注：以下是更新不用辅助列公式

1. <font color="#ff0000">=MAX(SUBTOTAL(9,OFFSET(业务数据!D1,SMALL(LARGE(MATCH(YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),)*(MATCH(YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),)=业务数据!A2:A380),ROW(1:19)),ROW(1:19)),,FREQUENCY(业务数据!B2:B380,{41305;41333;41364;41394;41425;41455;41486;41517;41547;41578;41608;41639;41670;41698;41729;41759;41790;41820}))))
   
2. =MAX(SUBTOTAL(9,OFFSET(业务数据!D1,SMALL(IF(FREQUENCY(MATCH(YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),),MATCH(YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),)),MATCH(YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),)),ROW(1:19)),,FREQUENCY(业务数据!B2:B380,{41305;41333;41364;41394;41425;41455;41486;41517;41547;41578;41608;41639;41670;41698;41729;41759;41790;41820}))))</font>

复制代码

不更改原始数据，不增加辅助列公式

1. <font color="#ff0000">=MAX(MMULT(--TRANSPOSE(MATCH(YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),)=SMALL(LARGE(MATCH(YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),)*(MATCH(YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),YEAR(业务数据!B2:B380)&MONTH(业务数据!B2:B380),)=业务数据!A2:A380),COLUMN(A:S)),COLUMN(A:S))),--SUBSTITUTE(业务数据!D2:D380," ",)))</font>

复制代码

chh0926

最近工作很忙，差点拖组织后腿，哈哈
第一题
=COUNT(OFFSET($A$3,MATCH($G4,$A$4:$A$21,0),MATCH(H$3,$B$3:$E$3,0),COUNTIF($A$4:$A$21,$G4),1))
=COUNTIF(INDIRECT("r"&MATCH($G4,$A:$A,0)&"c"&MATCH(H$3,$A$3:$E$3,0)&":r"&COUNTIF($A$4:$A$21,$G4)+MATCH($G4,$A:$A,0)-1&"c"&MATCH(H$3,$A$3:$E$3,0),FALSE),">"&0)
=COUNTIFS(B$4:B$21,">"&0,$A$4:$A$21,$G4)
{=SUM(($A$4:$A$21=$G4)*(B$4:B$21<>""))}
=SUMIF($A$4:$A$21,$G4,B$4:B$21)/AVERAGEIF($A$4:$A$21,$G4,B$4:B$21)
=SUMPRODUCT(($A$4:$A$21=$G4)*(B$4:B$21<>"")*1)
第二题
{=SUM((A27:A42>=41699)*(A27:A42<=41729)*(B27:B42={"甲","乙"})*C27:C42)}
{=SUM(IF((B27:B42="甲")+(B27:B42="乙")=1,C27:C42,0)*(MONTH(A27:A42)=3))}
{=SUM(SUMIFS(C27:C42,B27:B42,G27:G28,A27:A42,">="&41699,A27:A42,"<="&41729))}
=SUMIFS(C27:C42,A27:A42,">="&41699,A27:A42,"<="&41729,B27:B42,"甲")+SUMIFS(C27:C42,A27:A42,">="&41699,A27:A42,"<="&41729,B27:B42,"乙")
=SUMPRODUCT((MONTH(A27:A42)=F27)*(B27:B42={"甲","乙"})*C27:C42)
第三题
=COUNT(1/(($C$49:$C$70>=60)*($A$49:$A$70=$E49)))*(COLUMN()=6)+COUNT(1/(($C$49:$C$70<60)*($A$49:$A$70=$E49)))*(COLUMN()=7)
第四题
=MAX(MMULT(--(ROW(1:12)=TRANSPOSE(MONTH(业务数据!B2:B380))),({2013,2014}=YEAR(业务数据!B2:B380))*业务数据!C2:C380))

满坛皆为吾师

题一：

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

复制代码

题二：（合并单元格）

1. =SUMPRODUCT((MONTH(A27:A42)=3)*(B27:B42<>B35)*C27:C42)
   
2. =SUMPRODUCT(C27:C42*((MONTH(A$27:A$42)=3)*CODE(B$27:B$42)%>456))

复制代码

题三：

1. =COUNTIFS($C$49:$C$70,IF(F$48=$F$48,">=","<")&60,$A$49:$A$70,$E49) 右拉下拉

复制代码

题四：
数据排错

1. =MAX(MMULT(N(TRANSPOSE(TEXT(业务数据!B$2:B$380,"ym")=TRANSPOSE(TEXT(DATE(13,ROW(1:24),1),"ym")))),业务数据!D$2:D$380))
   
2. =MAX(MMULT(SUMIF(业务数据!B2:B380,"<"&EDATE(412&{44,14},ROW(1:25)),业务数据!D2:D380),{1;-1}))

复制代码

1. 
   

复制代码

xiaox0124

有劳学委。