二级办公室自动化excel题目答案

二级办公室自动化excel题目答案,二级AOA

二级办公高级应用EXCEL练习题标准答案

一、姓名---三科成绩是否均超过平均

1. {=C2:C39+D2:D39+E2:E39}

2. {=F2:F39/3}

3. =RANK(F2,F$2:F$39)

4.

=IF(C2>AVERAGE($C$2:$C$39),IF(D2>AVERAGE($D$2:$D$39),IF(E2>AVERAGE($E$2:$E$39),TRU

5. =COUNTIF(Sheet1!$D$2:$D$39,"<20")

6. =COUNTIF(Sheet1!$D$2:$D$39,"<40")-COUNTIF(Sheet1!$D$2:$D$39,"<20")

7. =COUNTIF(Sheet1!$D$2:$D$39,"<60")-COUNTIF(Sheet1!$D$2:$D$39,"<40")

8. =COUNTIF(Sheet1!$D$2:$D$39,"<80")-COUNTIF(Sheet1!$D$2:$D$39,"<60")

9. =COUNTIF(Sheet1!$D$2:$D$39,"<100")-COUNTIF(Sheet1!$D$2:$D$39,"<80")

10. 高级筛选

11. 透视表……行：三科成绩是否均超过平均，数据项：姓名

二、采购表

1. =VLOOKUP(A11,F$2:G$4,2,FALSE)

2. =IF(B11>=A$6,B$6,IF(B11>=A$5,B$5,IF(B11>=A$4,B$4,B$3)))

3. =D11*B11*(1-E11)

4. =SUMIF(A$11:A$43,I12,B$11:B$43)

5. =SUMIF(A$11:A$43,I12,F$11:F$43)

6. 高级筛选

7. 透视表带图……行：采购时间，列：项目，数据项：采购数量

三、客户

1. {=H2:H51*G2:G51}

2. =COUNTIF(D2:D51,"高等教育出版社")

3. =COUNTIF(G2:G51,">110")-COUNTIF(G2:G51,">=850")

4. =SUMIF(A2:A51,K8,I2:I51)

5. =IF(MOD(A2,400)=0,"闰年",IF(MOD(A2,4)<>0,"平年",IF(MOD(A2,100)<>0,"闰年","平年")))

6. 高级筛选

7. 透视表带图……行：出版社，列：客户，数据项：订数

四、姓名---出生年月

1. =YEAR(NOW())-YEAR(C2)

2. =REPLACE(F2,1,4,"05718")

3. =IF(D2>=40,IF(B2="男",TRUE,FALSE),FALSE)

4. =COUNTIF(Sheet1!B2:B37,"男")

5. =COUNTIF(Sheet1!D2:D37,">40")

6. 高级筛选

7. 透视表……行：所在区域，数据项：姓名

二级办公室自动化excel题目答案,二级AOA

五、产品---白炽灯

1. {=E2:E17*F2:F17*G2:G17}

2. =DAVERAGE(A1:H17,E1,J2:L3)

3. =DSUM(A1:H17,G1,J7:L8)

4. =COUNTBLANK(B2:E11)

5. =COUNTIF(B2:E11,"Y")

6. =IF(T(B21)="15",TRUE,FALSE)

7. 高级筛选

8. 透视表……行：产品，列：商标，数据项：采购合数

六、房产销售表

1. =F3*G3

2. {=H3:H26*I3:I26}

3. =SUMIF(Sheet1!K3:K26,Sheet1!K3,Sheet1!I3:I26)

4. =RANK(B2,B$2:B$6)

5. 高级筛选

6. 透视表……行：销售人员，数据项：契税总额

七、公务员考试成绩表

1. IF(G3="博士研究生","博士",IF(G3="硕士研究生","硕士",IF(G3="本科","学士","无")))

2. {=(I3:I18/3)*60%}

3. {=K3:K18*40%}

4. =RANK(M3,M$3:M$18)

4. 高级筛选

8. 透视表……行：报考单位，列：学历，数据项：学历

八、员工姓名---员工代码

1. REPLACE(B2,1,2,"PAO")

2. YEAR(NOW())-YEAR(E2)、YEAR(NOW())-YEAR(G2)

3. =IF(I2="工程师",IF(H2>20,TRUE,FALSE),FALSE) 或 IF(AND(H2>=20,I2="工程师"), TRUE 3. COUNTIF(D2:D65,"男")、COUNTIF(I2:I65,"高级工程师")、COUNTIF(H2:H65,">=10")

5. 高级筛选

7. 透视表带图……行：职称，数据项：职称

九、停车价目表

1.HLOOKUP(B9,A$2:C$3,2, FALSE)

2. =E9-D9

3.IF(HOUR(F9)=0,1,IF(MINUTE(F9)>15,HOUR(F9)+1,HOUR(F9)))*C9

4. =COUNTIF(G9:G39,">=40")

5. =MAX(G9:G39)

5.高级筛选

7. 透视表带图……行：车型，数据项：应付金额

二级办公室自动化excel题目答案,二级AOA

十、日期---杭州平均温度

1.=IF(B2>C2,"杭州","上海")

2. {=B2:B16-C2:C16}

3. =MAX(B2:B16)

4. =MIN(B2:B16)

5. =MAX(C2:C16)

6. =MIN(C2:C16)

7.{=ABS(B2:B16-C2:C16)}

8.高级筛选

9. 透视表……行：温度较高的城市，数据项：温度较高的城市十一、学生成绩表

1.{=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24}

2. =IF(J3>=350,"合格","不合格")

3. =DCOUNTA(A2:K24,B2,M2:N3)

4. =DGET(A2:K24,C2,M7:N8)

5. =DAVERAGE(A2:K24,I2,M12:M13)

6. =DMAX(A2:K24,I2,M12:M13)

7.高级筛选

9. 透视表……行：性别，列：考评，数据项：考评

二级办公室自动化excel题目答案,二级AOA

准答案

二级办公室自动化excel题目答案,二级AOA

TRUE, FALSE)