Excel综合操作题参考答案

一、采购情况表1.略2.略3.选中区域H3:H18，输入“=E3:E18*F3:F18*G3:G18”，然后同时按组合键“Shift+Ctrl+Enter”，公式编辑栏显示{=E3:E18*F3:F18*G3:G18}。4.a.在Sheet1表的G25单元格：=DAVERAGE(A2:H18,E2,J4:L5)b.在Sheet1表的G26单元格：=DCOUNT(A2:H18,B2,J9:L10)5.a.在Sheet2表的B14单元格：=COUNTBLANK(B3:E12)b.在Sheet2表的B15单元格：=COUNTIF(B3:E12,Y)6.在Sheet3表的B22单元格：=ISTEXT(B21)7.关键是创建条件区域！条件区域中的关键字段务必采用复制源数据中的关键字段的方法！条件区域如下图所示。其它操作略。8.数据透视表布局如下图所示。二、电话号码升级1.略2.在Sheet5表的B1单元格：=OR(AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)0),MOD(YEAR(TODAY()),400)=0)3.在Sheet1表的D2单元格：=YEAR(2013-5-1)-YEAR(C2)，然后双击填充柄。4.在Sheet1表的G2单元格：=REPLACE(F2,5,0,8)，然后双击填充柄。5.在Sheet1表的H2单元格：=AND(B2=男,D2=40)，然后双击填充柄。6.在Sheet2表的B2单元格：=COUNTIF(Sheet1!B2:B37,男)在Sheet2表的B3单元格：=COUNTIF(Sheet1!D2:D37,=40)7.注意：创建条件区域时一定要仔细检查条件区域的关键字与源数据区域的关键字保持一致！条件区域如下图所示。其它操作略。8.数据透视图布局如下图所示。三、房产销售1.略2.略3.略4.略5.在Sheet2表的B2单元格：=SUMIF(Sheet1!$K$3:$K$26,A2,Sheet1!$I$3:$I$26)，然后双击填充柄。6.在Sheet2表的C2单元格：=RANK.EQ(B2,$B$2:$B$6,0)，然后双击填充柄。7.略8.数据透视图布局如下图所示。四、公务员考试成绩表1.略2.略创建数据透视图的同时会生成数据透视表，此表保存在Sheet4工作表内3.在Sheet1表的H3单元格：=IF(G3=博士研究生,博士,IF(G3=硕士研究生,硕士,IF(G3=本科,学士,无))),然后双击填充柄。4.选中区域J3:J18，输入“=I3:I18/3*0.6”，然后同时按组合键Shift+Ctrl+Enter”，公式编辑栏显示{=I3:I18/3*0.6}。选中区域L3:L18，输入“=K3:K18*0.4”，然后同时按组合键Shift+Ctrl+Enter”，公式编辑栏显示{=K3:K18*0.4}。选中区域M3:M18，输入“=K3:K18+L3:L18”，然后同时按组合键Shift+Ctrl+Enter”，公式编辑栏显示{=K3:K18+L3:L18}。5.将Sheet1中区域A1:N18复制到Sheet2中，起始单元格为A1。在Sheet2表中单击区域J3:J18区域中的任一单元格并激活编辑栏，公式两边的大括号将消失，然后将公式改为“=I3:I18/2*0.6”，然后同时按组合键Shift+Ctrl+Enter”，公式编辑栏显示{=I3:I18/2*0.6}。6.在Sheet1表的N3单元格：=RANK.EQ(M3,$M$3:$M$18,0)，然后双击填充柄。7.略8.略五、教材订购情况表1.略2.略3.选中区域I3:I52，输入“=G3:G52*H3:H52”，然后同时按组合键“Shift+Ctrl+Enter”，公式编辑栏显示{=G3:G52*H3:H52}。4.在Sheet1表的L2单元格：=COUNTIF(D3:D52,高等教育出版社)。在Sheet1表的L3单元格：=COUNTIF(G3:G52,110)-COUNTIF(G3:G52,=850)5.在Sheet1表的L8单元格：=SUMIF($A$3:$A$52,K8,$I$3:$I$52)，然后双击填充柄。6.在Sheet2表的B2单元格：=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)0),MOD(A2,400)=0),闰年,平年)，然后双击填充柄。7.略8.略六、停车情况记录表1.略2.在Sheet4表的B1单元格：=OR(AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)0),MOD(YEAR(TODAY()),400)=0)3.在Sheet1表的C9单元格：=HLOOKUP(B9,$A$2:$C$3,2,0)，然后双击填充柄。4.略5.在Sheet1表的G9单元格：=IF(HOUR(F9)1,1,IF(MINUTE(F9)15,HOUR(F9),HOUR(F9)+1))*C9，然后双击填充柄。6.在Sheet1表的J8单元格：=COUNTIF(G9:G39,=40)在Sheet1表的J9单元格：=MAX(G9:G39)7.略8.略七、温度情况表1.略2.在Sheet5表的B2单元格：=IF(MOD(MID(A2,17,1),2)=0,女,男)3.在Sheet1表的D3单元格：=IF(B3C3,杭州,上海)，然后双击填充柄。4.略5.在Sheet1表的C19单元格：=MAX(B3:B17)在Sheet1表的C20单元格：=MAX(B3:B17)在Sheet1表的C21单元格：=MAX(C3:C17)在Sheet1表的C22单元格：=MAX(C3:C17)。6.将Sheet1中区域A1:E17复制到Sheet2中，起始单元格为A1。在Sheet2表中单击区域E3:E17区域中的任一单元格并激活编辑栏，公式两边的大括号将消失，然后将公式改为“=ABS(B3:B17-C3:C17)”，然后同时按组合键Shift+Ctrl+Enter”，公式编辑栏显示{=ABS(B3:B17-C3:C17)}。7.筛选条件如图所示：8.略八、学生成绩排名1.略2.在Sheet5表的B2单元格：=SUM(MOD(A1:A10,2)，然后同时按组合键Shift+Ctrl+Enter”，公式编辑栏显示{=SUM(MOD(A1:A10,2)}。3.略4.在Sheet1表的H2单元格：=RANK.EQ(F2,$F$2:$F$39),然后双击填充柄。5.在Sheet1表的I2单元格：=AND(C2AVERAGE(Sheet1!$C$2:$C$39),D2AVERAGE(Sheet1!$D$2:$D$39),E2AVERAGE(Sheet1!$E$2:$E$39)),然后双击填充柄。6.在Sheet2表的B2单元格：=COUNTIF(Sheet1!D2:D39,20)在Sheet2表的B3单元格：=COUNTIF(Sheet1!D2:D39,40)-COUNTIF(Sheet1!D2:D39,20)在Sheet2表的B4单元格：=COUNTIF(Sheet1!D2:D39,60)-COUNTIF(Sheet1!D2:D39,40)在Sheet2表的B5单元格：=COUNTIF(Sheet1!D2:D39,80)-COUNTIF(Sheet1!D2:D39,60)在Sheet2表的B6单元格：=COUNTIF(Sheet1!D2:D39,=80)。7.略8.略九、衣物采购表1.在Sheet5表的B1单元格：=ROUND(A1/100,0)*1002.略3.在Sheet1表的D11单元格：=VLOOKUP(A11,$F$3:$G$5,2,0),然后双击填充柄。4.在Sheet1表的E11单元格：=IF(B11100,0,IF(B11200,0.06,IF(B11300,0.08,0.1))),然后双击填充柄。5.在Sheet1表的F11单元格：=D11*B11*(1-E11),然后双击填充柄。6.在Sheet1表的J12单元格：=SUMIF($A$11:$A$43,I12,$B$11:$B$43),然后双击填充柄。在Sheet1表的K12单元格：=SUMIF($A$11:$A$43,I12,$F$11:$F$43),然后双击填充柄。7.略8.略十、员工信息表1.略2.在Sheet4表的C1单元格：=ROUND(B1*24*4,0)/24/43.在Sheet1表的C3单元格：=REPLACE(B3,3,0,0),然后双击填充柄。4.在Sheet1表的F3单元格：=YEAR(2013-5-1)-YEAR(E3),然后双击填充柄。在Sheet1表的H3单元格：=YEAR(2013-5-1)-YEAR(G3),然后双击填充柄。5.在Sheet1表的N3单元格：=COUNTIF(D3:D66,男)在Sheet1表的N4单元格：=COUNTIF(I3:I66,高级工程师)在Sheet1表的N5单元格：=COUNTIF(H3:H66,=10)6.在Sheet1表的K3单元格：=AND(H320,I3=工程师),然后双击填充柄。7.筛选条件如图所示：8.略