【新手201202班】第四讲作业【26-50】号学员上交处（已开帖）
本帖最后由 冠军欧洲2010 于 2012-10-9 11:00 编辑注意：
1、作业中的练习题都是以Excel2003版为准。
2、交作业时不用上传附件，用回复贴直接答案。
3、截至上交时间为9月27日18：00。
4、上交作业必须注明学号，切记 。
5、上交作业时，不用回复可见，直接上交就行。
本帖最后由 lucking551 于 2012-9-25 21:17 编辑
XS45：lucking551
第一题：答案=IF(((D8="男")*(E8<14)+(D8="女")*(E8<16)),"合格","不合格")第二题：答案
1：=COUNTIF(C20:C40,"<60")2：=SUMPRODUCT((D20:D40<90)*(D20:D40>80))3：=SUMPRODUCT((A20:A40="1班")+(A20:A40="3班"))4：=COUNTIF(B20:B40,"??")5：=COUNTIF(B20:B40,"张*")6：=SUMPRODUCT((C20:C40>60)*(D20:D40>60)*(E20:E40>60)*(F20:F40>60))7：=SUM((LARGE(G20:G40,ROW(1:10))))数组公式
8：=SUM(N(C20:C40>D20:D40))数组公式
第三题：答案
=SUM(D60:D65)第四题：答案
订单数：=COUNTIF($D$77:$D$96,$F77)订单总额：=SUMIF($D$77:$D$96,$F77,$C$77:$C$96)销售金额：=IF(H77<60000,H77*10%,H77*15%)第五题：答案
=DSUM(B99:I115,G99,B117:D120)数组公式
第六题：答案=SUMIF($B$123:$G$129,$B131,$C$123)第七题：答案=YEAR(TODAY())-IF(LEN(B165)=15,"19"&MID(B165,7,2),MID(B165,7,4))选作第一题：答案=SUM(1/COUNTIF(A142:A161,A142:A161))数组公式
选作第二题：答案=YEAR(TODAY())-IF(LEN(B165)=15,"19"&MID(B165,7,2),MID(B165,7,4)) 本帖最后由 liziyuliziyu7 于 2012-9-24 19:21 编辑
xs31:liziyuliziyu7
1.=IF(E8<16-(D8="男")*2,"","不")&"合格"
2.=COUNTIF(C20:C40,"<60")
=SUM(COUNTIF(D20:D40,">"&{80,89})*{1,-1})
=SUM(COUNTIF(A20:A40,{"1班","3班"}))
=COUNTIF(B20:B40,"??")
=COUNTIF(B20:B40,"张*")
=SUMPRODUCT(N(C20:C40>60),N(D20:D40>60),N(E20:E40>60),N(F20:F40>60))
=SUMPRODUCT(LARGE(G20:G40,ROW(1:10)))
=SUMPRODUCT(N(C20:C40-D20:D40>0))
3.=SUMIF($B61:$B66,"<>小计",D61)
4.=COUNTIF(D$77:D$96,F77)
=SUMIF(D$77:D$96,F77,C$77)
=TEXT(H77-60000,"[>0]!0!.15;!0!.1")*H77
5.地区 销售员 商品
深圳
A00001
钢笔
=DSUM(B99:I115,6,B117:D120)
6.=SUMIF(B$123:F$129,B131,C$123)
7.=DATEDIF(TEXT(MID(B137,7,8),"#-00-00"),TODAY(),"y")
8.数组=SUM(1/COUNTIF(A142:A161,A142:A161))
数组=SUM(N(MATCH(A142:A161,A142:A161,)=ROW(A142:A161)-141))
9.=DATEDIF(TEXT(MID(B165,7,11)-500,"0-00-00,"),TODAY(),"y") 学号：38 深蓝色的海洋
一、=IF(OR(AND(D8="男",E8<14),AND(D8="女",E8<16)),"合格","不合格")
二、 1、=COUNTIF(C20:C40,"<60")
2、=COUNTIF(D20:D40,"<90")-COUNTIF(D20:D40,"<80")
3、=COUNTIF(A20:A40,"<>2班")
=COUNTIF(A20:A40,"1班")+COUNTIF(A20:A40,"3班")
4、=COUNTIF(B20:B40,"??")
5、=COUNTIF(B20:B40,"张*")
6、｛=SUM((C20:C40>=60)*(D20:D40>=60)*(E20:E40>=60)*(F20:F40>=60))｝
7、=SUMIF(G20:G40,">="&LARGE(G20:G40,10))
8、｛=SUM(N(C20:C40>D20:D40))｝
｛=SUM((C20:C40>D20:D41)*1)｝
三、不会做
四、订单数:=COUNTIF($D$77:$D$96,F77)
订单总额:=SUMIF($D$77:$D$96,$F77,$C$77:$C$96)
销售奖金:=IF(H77>=60000,H77*0.15,H77*0.1)
五、=DSUM(B99:I115,G99,B117:D120)
六、=SUMIF($B$123:$F$129,$B131,$C$123:$G$129)
七、=DATEDIF(DATE(MID(B137,7,4),MID(B137,11,2),MID(B137,13,2)),TODAY(),"y")
选做题部分：
求A列字符串的个数，重复的只计算一次。
=｛SUM(1/COUNTIF(A142:A161,A142:A161))｝
根据身份证号求年龄（有15位和18位身份证号，要求用同一个公式）
=DATEDIF(IF(LEN(B165)=18,DATE(MID(B165,7,4),MID(B165,11,2),MID(B165,13,2)),DATE(MID(B165,4,2),MID(B165,6,2),MID(B165,8,2))),TODAY(),"y")
备注:第二个身份证号多了一位数
学号：XS34:破浪锥冰
第一题：=IF(D8="男",IF(E8<14,"合格","不合格"),IF(E8<16,"合格","不合格"))
第二题：1.=COUNTIF(C20:C40,"<60")
2.=COUNTIF(D20:D40,"<89")-COUNTIF(D20:D40,"<80")
3.=COUNTIF(A20:A40,"=1班")+COUNTIF(A20:A40,"=3班")
4.{=SUM((LEN(B20:B40)=2)*1)}
5.{=SUM((MID(B20:B40,1,1)="张")*1)}
6.{=SUM((C20:C40>=60)*(D20:D40>=60)*(E20:E40>=60)*(F20:F40>=60))}
7.{=SUM(LARGE(G20:G40,ROW(1:10)))}
8.{=SUM((C20:C40>D20:D40)*1)}
第三题 =SUM(D61:D65)
第四题1.=COUNTIF(D77:D96,F77)
2.=SUMIF(D77:D96,F77,C77:C96)
3.=IF(SUMIF(D77:D96,F77,C77:C96)<60000,SUMIF(D77:D96,F77,C77:C96)*10%,(SUMIF(D77:D96,F77,C77:C96))*15%)
第五题：
=DSUM(B99:I115,G99,B117:B118)+DSUM(B99:I115,G99,B119:B120)+DSUM(B99:I115,G99,C117:C118)-DSUM(B99:I115,G99,B117:C118)-DSUM(B99:I115,G99,C117:D118)-DSUM(B99:I115,G99,B119:C120)
第六题：=SUMIF(B123:B129,B131,C123:C129)+SUMIF(D123:D129,B131,E123:E129)+SUMIF(F123:F129,B131,G123:G129)
第七题：=DATEDIF(TEXT(MID(B137,7,8),"#-00-00"),TODAY(),"y")
第八题1，不会
2.=IF(LEN(B165)=15,DATEDIF(TEXT(19&""&MID(B165,7,6),"#-00-00"),TODAY(),"Y"),DATEDIF(TEXT(MID(B165,7,8),"#-00-00"),TODAY(),"Y"))
终于完成了{:201:} 学号：XS48:zjf1969 xs39:闪闪星儿
1、根据下列要求返回结果
答：=IF(OR(AND(D8="男",E8<14),AND(D8="女",E8<16)),"合格","不合格")
2、根据下表完成下列题目
答：1、求语文成绩不及格的人数？=COUNTIF(C20:C40,"<60")
2、求数学80-89分之间的人数？=COUNTIF(D20:D40,"<90")-COUNTIF(D20:D40,"<80")
3、统计1班和3班总人数=COUNTIF(A20:A40,"1班")+COUNTIF(A20:A40,"3班")
4、统计名字为两个字的人数=COUNTIF(B20:B40,"??")
5、统计姓张的人数=COUNTIF(B20:B40,"张*")
6、求四门科目成绩全部及格的人数？｛=SUM((C20:C40>=60)*(D20:D40>=60)*(E20:E40>=60)*(F20:F40>=60))｝
7、求班级前十名（按总分进行排名）的总分之和？=SUM(LARGE(G20:G40,{1,2,3,4,5,6,7,8,9,10}))
8、求语文分数高于数学分数的同学个数？{=SUM((C20:C40>D20:D40)*1)}
3、用一个函数完成小计行
答：=SUM(D61:D65)
4、计算销售奖金等（订单总额小于60000的提成比例为10%，超过为15%）
G77中输入“=COUNTIF(D77:D96,F77)”，H77中输入“ =SUMIF(D77:D96,F77,C77:C96) ”，I77中输入 “=IF(H77>60000,H77*15%,H77*10%)”，下拉填充
5、根据下列要求在B117:D120区域构建条件并用数据库函数完成统计
答：条件区域：B117，地区，C117，销售员，D117，商品，B118，深圳，C119，A00001，D120，钢笔，销量F118输入 “=DSUM(B99:I115,G99,B117:D120)”
7、根据18位身份证号求年龄（周岁）
答：C137输入“=DATEDIF(TEXT(MID(B137,7,8),"#-00-00"),TODAY(),"y")”
学号47号雨诗露
1、根据下列要求返回结果
=IF(AND(D8="男",E8<14),"合格",IF(AND(D8="女",E8<16),"合格","不合格"))
2、根据下表完成下列题目
1、求语文成绩不及格的人数？ =COUNTIF($C$20:$C$40,"<60")
2、求数学80-89分之间的人数？=COUNTIF($D$20:$D$40,"<89")-COUNTIF($D$20:$D$40,"<=80")
3、统计1班和3班总人数？=COUNTIF($A$20:$A$40,"1班")+COUNTIF($A$20:$A$40,"3班")
4、统计名字为两个字的人数? =COUNTIF($B$20:$B$40,"??")
5、统计姓张的人数? =COUNTIF($B$20:$B$40,"张*")
6、求四门科目成绩全部及格的人数？
7、求班级前十名（按总分进行排名）的总分之和？
8、求语文分数高于数学分数的同学个数？
3、用一个函数完成小计行
4、计算销售奖金等（订单总额小于60000的提成比例为10%，超过为15%）
=COUNTIF($D$77:$D$96,F77)
=SUMIF($D$77:$D$96,F77,$C$77:$C$96)
=IF(H77<60000,H77*0.1,H77*0.15)
5、根据下列要求在B117:D120区域构建条件并用数据库函数完成统计
地区 销售员 商品
深圳 A00001 钢笔
=DSUM($B$99:$I$115,6,$B$117:$B$118)
=DSUM($B$99:$G$115,6,$C$117:$C$118)
=DSUM($B$99:$I$115,6,$D$117:$D$118)
6、统计对应项目的总数
=SUMIF($B$123:$F$129,B131,$C$123:$G$129)
7、根据18位身份证号求年龄（周岁）
=2012-MID(B137,7,4)
根据身份证号求年龄（有15位和18位身份证号，要求用同一个公式）
=2012-IF(LEN(B165)=18,MID(B165,7,4),IF(LEN(B165)=19,MID(B165,7,4),19&MID(B165,7,2)))
这次的题目有点难度，哎！
页:
[1]