【会计四期】第三讲上交作业贴（23-44号）

ccqq19850718 · 发表于 2010-6-6 11:38

[Post=1]

TRfqqjAW.rar (29.8 KB, 下载次数: 1)

ynwsqb · 发表于 2010-6-6 12:39

[Post=1]1、=IF(COUNTIF(C4:F4,">60")>=3,30,IF(COUNTIF(C4:F4,">60")>=3,20))+SUM(C4:F4)；2、=IF(C14*1=D14*1,"是","否")；3、=SUM(C22:G27)；4.1  =SUMPRODUCT((B31:B44="BB")*(D31:D44>1)*(E31:E44));4.2 =SUMPRODUCT((B31:B44="BB")*(E31:E44)*(D31:D44)+(B31:B44="CC")*(D31:D44)*(E31:E44));  可简化！4.3  =SUMPRODUCT((B31:B44="AA")*(E31:E44>200));5、=SUMIF(B51:E59,"??R*",C51:F59)；6、=SUMPRODUCT((J3:J22<-1)+(J3:J22>5)-(J3:J22=9))      =SUMPRODUCT(((J3:J22<-1)+(J3:J22>5)-(J3:J22=9))*J3:J22)7、=COUNTIF(K26:K35,"*中学")8、=SUMIF(K43:K69,K41&"*",L43:L69)[/Post]可尝试一下选做题！——dgxsdr

[此贴子已经被tkgg93于2010-6-9 9:02:38编辑过]

meg19850305 · 发表于 2010-6-6 14:50

[Power=1]1.=IF(COUNTIF(C4:F4,">60")=4,SUM(C4:F4)+30,IF(COUNTIF(C4:F4,">60")=3,SUM(C4:F4)+20,SUM(C4:F4)))；可简化！2.=IF(C14*1=D14*1,"是","否");3.=SUM(C22:C27,E22:E27,G22:G27);  可简化！ 4.1=SUMPRODUCT(($B$31:$B$44="BB")*($D$31:$D$44>1)*$E$31:$E$44);4.2{=SUMPRODUCT(($B$31:$B$44="BB")*($D$31:$D$44)*$E$31:$E$44)+SUMPRODUCT(($B$31:$B$44="CC")*($D$31:$D$44)*$E$31:$E$44)};  要简化！4.3{=SUMPRODUCT(($B$31:$B$44="AA")*($E$31:$E$44>200))}; 5.=SUMIF(B51:B59,"??R*",C51:C59)+SUMIF(E51:E58,"??R*",F51:F58);  可简化！6.=COUNTIF(J3:J22,"<-1")+COUNTIF(J3:J22,">5")-COUNTIF(J3:J22,"=9")=SUMPRODUCT(($J$3:$J$22<-1)*($J$3:$J$22))+SUMPRODUCT(($J$3:$J$22>5)*($J$3:$J$22<>9)*$J$3:$J$22)  可简化！ 7.=COUNTIF(K26:K35,"*中学");8.=SUM(VLOOKUP($K$41,$K$43:$L$48,2,0),VLOOKUP($K$41,$K$49:$L$54,2,0),VLOOKUP(K41,$K$56:$L$62,2,0),VLOOKUP($K$41,$K$63:$L$69,2,0));  不能算对！继续努力！——dgxsdr [/Power]

[此贴子已经被dgxsdr于2010-6-7 15:29:06编辑过]

YZ · 发表于 2010-6-6 15:27

提示: 作者被禁止或删除内容自动屏蔽

huihui2100 · 发表于 2010-6-6 15:32

[Power=1]作业1： 用本课所学函数为学生计算总分,G4单元格输入公式下拉完成        =SUM(C4:F4)  不对！   作业2： 不改变原表数据完成判断，同行两数据一致显示为"是"，不一致显示为"否"。         =IF(C14=D14,"是","否")  不对！作业3: 用SUM函数计算出A,B,C三组的产量总和 答案:1637      {=SUM(C22:C27,E22:E27,G22:G27)}    可简化！作业5: 计算2个部门销售空调的总量      下表是某公司2个业务部今天的销售情况，求今天2个部门共销售空调的数量。     共销售空调 =SUMIF(B51:E59,"KFR-*",C51)  要加油了！——dgxsdr [/Power]

[此贴子已经被dgxsdr于2010-6-7 15:37:47编辑过]

crz258824165 · 发表于 2010-6-6 16:10

[Power=1]

Bwcbomm5.rar (31.98 KB, 下载次数: 1)

hyetal · 发表于 2010-6-6 16:51

[Power=1]必选题：1、=SUM(CHOOSE(COUNTIF(C4:F4,">60")+1,0,0,0,20,30),C4:F4)  不错！还可虑到了全部小于60分的情况！2、=IF(C14*1=D14*1,"是","否")3、=SUM(B22:G27)4.1、=SUMPRODUCT((B31:B44="bb")*(D31:D44>1)*E31:E44)4.2、=SUMPRODUCT((B31:B44={"bb","cc"})*D31:D44*E31:E44)4.3、=SUMPRODUCT((B31:B44="aa")*(E31:E44>200))5、=SUMIF(B51:B59,"??R*",C51:C59)+SUMIF(E51:E58,"??R*",F51:F58)  可简化！6.1、=SUM(COUNTIF(J3:J22,"<-1"),COUNTIF(J3:J22,">5"),-COUNTIF(J3:J22,"=9"))6.2、=SUM(SUMIF(J3:J22,"<-1"),SUMIF(J3:J22,">5"),-SUMIF(J3:J22,"=9"))7、=COUNTIF(K26:K35,"*中学")8、=SUMPRODUCT((K43:K69=K41)*L43:L69)选做题：1、=SUM(IF(C3:C12>80000,1000,500))数组公式2、=SUM(SUMIF(C17:C31,{"A","B","C"},D17:D31)*{100,150,230})数组公式3、=SUM(COUNTIF(B35:B44,C35:C44))数组公式4.1、=SUMIF(C$48:N$48,C$60,C49:N49)4.2、=SUMIF(C$48:N$48,"<="&C$60,C49:N49)总体不错！——dgxsdr [/Power]

[此贴子已经被dgxsdr于2010-6-7 15:50:12编辑过]

wen191244510 · 发表于 2010-6-6 16:55

[Power=1]1、=IF((C4>60)+(D4>60)+(E4>60)+(F4>60)=4,SUM(C4:F4)+30,IF((C4>60)+(D4>60)+(E4>60)+(F4>60)=3,SUM(C4:F4,20),SUM(C4:F4)))下拉   这样不能算对！2、=IF(--C14=--D14,"是","否")下拉3、=SUM(B22:G27)4、=SUMPRODUCT((D31:D44>1)*E31:E44*(B31:B44=B33))、=SUMPRODUCT(((B31:B44=B33)+(B31:B44=B35))*D31:D44*E31:E44)、=SUMPRODUCT(((B31:B44=B31)*(E31:E44)>200)*1)5、=SUMIF(B51:B59,"??R*",C51)+SUMIF(E51:E58,"??R*",F51)  可简化！6、=COUNTIF(J3:J22,"<-1")+COUNTIF(J3:J22,">5")-COUNTIF(J3:J22,"=9")、（2）、=SUMIF(J3:J22,"<-1")+SUMIF(J3:J22,">5")-SUMIF(J3:J22,"=9")7、=COUNTIF(K26:K35,"*中学")8、=SUMPRODUCT((K43:K69=K41)*1,L43:L69)选做题：1、=SUM(IF(C3:C12>80000,1000,500))数组公式2、=SUM(SUMIF(C17:C31,G16:I16,D17:D31)*G17:I17)数组公式3、=COUNTA(B35:C44)-SUM(1/COUNTIF(B35:C44,B35:C44))数组公式  想得复杂了！4、（1）、=SUMIF($C$48:$N$48,$C$60,C49)下拉。（2）、=SUM(OFFSET(C49,,,,$C$60))下拉。总体不错！——dgxsdr[/Power]

[此贴子已经被dgxsdr于2010-6-7 16:20:38编辑过]

随缘wy · 发表于 2010-6-6 17:57

[Power=1]

k0fXJsQs.zip (33.11 KB, 下载次数: 1)

含 · 发表于 2010-6-6 22:41

02026交作业[Power=1]1、=SUM(C5:F5,IF(COUNTIF(C5:F5,">60")>3,30,IF(COUNTIF(C5:F5,">60")>2,20))) 2、=IF(--C14=--D14,"是","否")继续努力！——dgxsdr[/Power]

[此贴子已经被dgxsdr于2010-6-7 16:40:02编辑过]

		自动登录	找回密码
密码			注册

YZ YZ 当前离线积分 0 IP卡狗仔卡	发表于 2010-6-6 15:27 \| 显示全部楼层提示: 作者被禁止或删除内容自动屏蔽
YZ YZ 当前离线积分 0 IP卡狗仔卡
	回复使用道具举报显身卡

【会计四期】第三讲上交作业贴（23-44号）

03042 ynwsqb 交作业

02025交作业

03034

03031号

交作业咯 3033学员

03043 交作业