【新手201202班】第四讲作业【26-50】号学员上交处（已开帖）

冠军欧洲2010

注意：
1、作业中的练习题都是以Excel2003版为准。   
2、交作业时不用上传附件，用回复贴直接答案。                              
3、截至上交时间为9月27日18：00。
4、上交作业必须注明学号，切记 。
5、上交作业时，不用回复可见，直接上交就行。

lucking551

XS45：lucking551
第一题：答案

1. =IF(((D8="男")*(E8<14)+(D8="女")*(E8<16)),"合格","不合格")

复制代码

第二题：答案
1：

1. =COUNTIF(C20:C40,"<60")

复制代码

2：

1. =SUMPRODUCT((D20:D40<90)*(D20:D40>80))

复制代码

3：

1. =SUMPRODUCT((A20:A40="1班")+(A20:A40="3班"))

复制代码

4：

1. =COUNTIF(B20:B40,"??")

复制代码

5：

1. =COUNTIF(B20:B40,"张*")

复制代码

6：

1. =SUMPRODUCT((C20:C40>60)*(D20:D40>60)*(E20:E40>60)*(F20:F40>60))

复制代码

7：

1. =SUM((LARGE(G20:G40,ROW(1:10))))

复制代码

数组公式
8：

1. =SUM(N(C20:C40>D20:D40))

复制代码

数组公式

第三题：答案

1. =SUM(D60:D65)

复制代码

第四题：答案
订单数：

1. =COUNTIF($D$77:$D$96,$F77)

复制代码

订单总额：

1. =SUMIF($D$77:$D$96,$F77,$C$77:$C$96)

复制代码

销售金额：

1. =IF(H77<60000,H77*10%,H77*15%)

复制代码

第五题：答案

1. =DSUM(B99:I115,G99,B117:D120)

复制代码

数组公式

第六题：答案

1. =SUMIF($B$123:$G$129,$B131,$C$123)

复制代码

第七题：答案

1. =YEAR(TODAY())-IF(LEN(B165)=15,"19"&MID(B165,7,2),MID(B165,7,4))

复制代码

选作第一题：答案

1. =SUM(1/COUNTIF(A142:A161,A142:A161))

复制代码

数组公式

选作第二题：答案

1. =YEAR(TODAY())-IF(LEN(B165)=15,"19"&MID(B165,7,2),MID(B165,7,4))

复制代码

liziyuliziyu7

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:}

zjf1969

学号：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)))
这次的题目有点难度，哎！