AOA浙江省二级计算机考试高级办公软件运用历年真题Excel解答详细操作步骤

1、e1-e1-产品灯：产品灯：1. 使用数组公式，计算sheet1 中的每种产品的价值，将结果保存到表中的“价值”列中。计算价值的计算方法为： “单价*每盒数量*采购盒数” 。操作步骤：操作步骤：将鼠标定位在 h2，输入“=e2*f2*g2” ，并以“ctrl+shift+enter”结束该公式的输入（数组公式的标志为“” ） ，自动填充。2. 在 sheet2 中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存相应的单元格中。a. 计算：商标为上海，寿命大于等于1000 的白炽灯的平均单价；b. 计算：产品为白炽灯，其瓦数大于等于80 且小于等于 100 的盒数。操作步骤：

2、操作步骤：a将鼠标定位在 g23，插入数据库函数 daverage（计算满足给定条件的列表或数据库的列中数值的平均值） ， “=daverage(a1:h17,e1,j2:l3)” ， （其中 a1:h17 是 datebase，构成列表或数据库的单元格区域，数据库是一系列相关的数据的列表； e1 是 field，用双引号括住的列标签，或是表示该列在类表中位置的数值； j2:l3 是 criteria，包含给定条件的单元格区域，该区域包括标签及列标签下满足条件的单元格。 ）b在 i 列增加一列“数量” ，并求出其数量（=每盒数量*采购盒数） ；将鼠标定位在 g24，插入数据库函数 dsum（求

3、满足给定条件的数据库中记录的字段（列）数据的和） ， “= dsum(a1:i17,i1,j7:l8)” ，各区域含义同上。3. 某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查对象，只能回答y（吸烟）或者 n（不吸烟） 。根据调查情况，制做出sheet3。请使用函数，统计符合以下条件的数值。a. 统计未登记的部门个数；b. 统计在登记的部门中，吸烟的部门个数。操作步骤：操作步骤：a. 将鼠标定位在 b14，插入函数 countblank， “=countblank(b2:e11)”b. 将鼠标定位在 b15，插入函数 countif， “=countif(b2:

4、e11,y)”4. 使用函数，对sheet3 中的 b21 单元格中的内容进行判断，判断其是否为文本，如果是，结果为“true” ；如果不是，结果为“ false” ，并将结果保存在sheet3 中的 b22 单元格当中。操作步骤：操作步骤：将鼠标定位在 b22， 插入函数 istext （检测一个值是否为文本， 返回 true 或 false） ，“=istext(b21)” 。5. 将 sheet1 复制到 sheet4 中，对 sheet4 进行高级筛选，要求：a. 筛选条件： “产品为白炽灯，商标为上海” ，并将结果保存；b. 将结果保存在 sheet4 中。操作步骤：操作步骤：将 s

5、heet1 复制到 sheet4。建立条件区域：产品产品商标商标白炽灯6. 根据 sheet1 的结果，在 sheet5 中创建一张数据透视表，要求：a. 显示不同商标的不同产品的采购数量；b. 行区域设置为“产品” ；c. 列区域设置为“商标” ；d. 计数项为“采购盒数” 。操作步骤：操作步骤： 执行“数据”|“数据透视表和数据透视图” ，按图 2.1 所示选择“数据源类型” 和“报表类型” 。上海执行“插入”|“高级筛选” ，选择列表区域和条件区域，确定即可。图 2.1 选择“数据源类型”和“报表类型”单击“下一步” ，选定区域，如图 2.2 所示。图 2.2 选定数据源区域单击“下一步

6、” ，选择数据透视表的显示位置。在sheet5 中单击 a18 单元格。图 2.3 选择数据透视表的显示位置 单击“完成” ，即出现如图 2.4 所示的空的数据库透视表及相关工具。图 2.4 空的数据库透视表及相关工具 将图 2.4 中“数据透视表字段列表”中的“产品”拖到“行字段” ，将“商标”拖到“列字段” ，将“采购盒数”拖到“数据项” 。最后的数据透视表效果如图2.5所示。图 2.5 数据透视表效果图e2-e2-房产销售表房产销售表1. 利用公式，计算 sheet1 中的房价总额。房价总额的计算公式为：“面积*单价”操作步骤：操作步骤：将鼠标定位在 i3，输入公式“= f3*g3” ，

7、自动填充。2使用数组公式，计算sheet1 中的契税总额。契税总额的计算公式为：“契税*房价总额”操作步骤：操作步骤：将鼠标定位在 j3，输入公式“=i3*j3” ，并以 “ctrl+shift+enter”结束该公式的输入，自动填充。3使用函数，根据 sheet1 中的结果，统计每个销售人员的销售总额，将结果保存在sheet2 中的相应的单元格中。操作步骤：操作步骤：将鼠标定位在sheet2的b2， 插入函数 “=sumif(sheet1!k3:k26,b2,sheet1!i3:i26)”，自动填充。4使用 rank 函数，根据 sheet2 的结果，对每个销售人员的销售情况进行排序，并将结

8、果保存在“排名”列当中。操作步骤：操作步骤：将鼠标定位在 sheet2 的 b3，插入函数“=rank(b2,$b$2:$b$6,0)”，自动填充。5将 sheet1 复制到 sheet3 中，并对 sheet3 进行高级筛选，要求：a筛选条件为：“户型”为两室一厅，“房价总额”1000000；b将结果保存在 sheet3 中。操作步骤：操作步骤：将 sheet1 复制到 sheet3。建立条件区域：户型户型房价总额房价总额两室一厅1000000执行“插入”|“高级筛选” ，选择列表区域和条件区域，确定即可。6根据 sheet1 的结果，创建一张数据透视图chart1，要求：a显示每个销售人员

9、销售房屋所缴纳契税总额；b行区域设置为“销售人员”；c计数项设置为契税总额；d将对应的数据透视表保存在sheet4 中。操作步骤：操作步骤：执行“数据”|“数据透视表和数据透视图” ，按图2.6 所示选择“数据源类型”和“报表类型” 。图 2.6 选择“数据源类型”和“报表类型”单击“下一步” ，选定区域，如图 2.7 所示。图 2.7 选定数据源区域单击“下一步” ，选择数据透视表的显示位置。单击 sheet4 工作表标签选择数据透视表的显示位置。图 2.8 选择数据透视表的显示位置 单击“完成” ，即出现如图 2.9 所示的空的数据库透视表及相关工具图 2.9 空的数据库透视表及相关工具

10、将图 2.9 中“数据透视表字段列表”中的“销售人员”拖到“分类字段” ，将“契税总额”拖到“数据项” 。最后的数据透视图效果如图 2.10 所示。 （注：数据透视图在 chart1生成的同时在 sheet5 中生成了数据透视表）图 2.10 数据透视图效果图e3-e3-电话号码电话号码1. 使用时间函数，对 sheet1 中用户的年龄进行计算。要求：计算用户的年龄， 并将其计算结果填充到“年龄”列当中。操作步骤：操作步骤：将鼠标定位在 d2，插入函数“=year(now()-year(c2)” ，自动填充即可。2. 使用 replace 函数， 对 sheet1 中用户的电话号码进行升级。

11、要求： 对 “原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。操作步骤：操作步骤：将鼠标定位在 g2，插入文本函数 replace（将一个字符串中的部分字符用另一个字符串替换） ， “=replace(f2,5,0,8) ” （其中，f2 是 old_text，要进行字符替换的文本；5是 start_num， 要替换为 new_text 的字符在 old_text 中的位置； 0 是 num_chars， 要从 old_text中替换的字符个数，8 是 new_text，用来对 old_text 中指定字符串

12、进行替换的字符串） ，自动填充即可。3. 使用逻辑函数，判断sheet1 中的“大于等于40 岁的男性”，将结果保存在sheet1中的“是否=40 男性”。操作步骤：操作步骤：将鼠标定位在 h2，插入条件函数 if， “=if(and(d2=40,b2=男),是,否 )” ，自动填充即可。4. 对 sheet1 中的数据，根据以下条件,利用函数进行统计：a. 统计性别为“男”的用户人数，将结果填入sheet2 的 b2 单元格中；b. 统计年龄为“40”岁的用户人数，将结果填入sheet2 的 b3 单元格中。操作步骤：操作步骤：a. 将鼠标定位在 sheet2 的 b2，插入函数 count

13、if（计算某个区域中满足给定的单元格数目） ， “=countif(sheet1!b2:b37,男)” 。 （其中， “sheet1!b2:b37”是 range，要计算其中非空单元格数目的区域； “男”是 criteria，以数字、表达式或文本形式定义的条件。 ）b. 将鼠标定位在 sheet2 的 b3，插入函数“=countif(sheet1!d2:d37,40)” 。5. 将 sheet1 复制到 sheet3，并对 sheet3 进行高级筛选。a. 筛选条件为：“性别”女、“所在区域”西湖区；b. 将筛选结果保存在 sheet3 中。操作步骤：操作步骤：将 sheet1 复制到 sh

14、eet3。建立条件区域：性性 别别所在区域所在区域女西湖区执行“插入”|“高级筛选” ，选择列表区域和条件区域，确定即可。6. 根据 sheet1 的结果，创建一数据透视图chart1，要求：a. 显示每个区域所拥有的用户数量；b. x 坐标设置为“所在区域”；c. 计数项为“所在区域”；d. 将对应的数据透视表保存在sheet4 中。操作步骤：操作步骤： （参见（参见 e2-6e2-6）e4-e4-气温气温1. 使用 if 函数，对 sheet1 中的“温度较高的城市”列进行。操作步骤：操作步骤：将鼠标定位在 d2，插入 if 函数， “=if(b2c2,杭州,上海) ” ， 自动填充。2使

15、用数组公式，对 sheet1 中的相差温度值（杭州相对于上海的温差）进行填充。操作步骤：操作步骤：将鼠标定位在 e2，输入公式“=b2-c2” ，并以 “ctrl+shift+enter”结束该公式的输入，自动填充。3利用函数，根据 sheet1 中的结果，符合以下条件的进行统计。a杭州这半个月以来的最高温度和最低温度；b上海这半个月以来的最高温度和最低温度。操作步骤：操作步骤：a. 将鼠标定位在 c18，插入函数 max， “=max(b2:b16)” ；将鼠标定位在 c19，插入函数 min， “=min(b2:b16) ” 。b. 将鼠标定位在 c20，插入函数max， “=max(c2

16、: c16)” ；将鼠标定位在c21，插入函数 min， “=min（c2: c16） “。4 将 sheet1 复制到 sheet2 中， 在 sheet2 中， 重新编辑数组公式， 将 sheet2 中的 “相差的温度值”中的数值取其绝对值（均为正数）。操作步骤：操作步骤：将sheet1复制到sheet2， 将鼠标定位在sheet2的e2， 插入函数abs，“=abs(b12-c12)”，自动填充。5将 sheet2 复制到 sheet3 中，并对 sheet3 进行高级筛选，要求：筛选条件：“杭州平均气温”=20，“上海平均气温”=2010)”。4使用逻辑函数，判断员工是否有资格评“高级

17、工程师”。评选条件为：工龄大于 20，且为工程师的员工。操作步骤：操作步骤：将鼠标定位在 k2，插入函数“=if(and(h220,i2=工程师),是,否)”，自动填充。5将 sheet1 复制到 sheet2 中，并对 sheet2 进行高级筛选，要求：a筛选条件为：“性别”男，“年龄”30，“工龄”=10，“职称”助工；b将结果保存在 sheet2 中。操作步骤：操作步骤：将 sheet1 复制到 sheet2建立条件区域：性别性别年龄年龄工龄工龄职称职称30=10女助工执行“插入”|“高级筛选” ，选择列表区域和条件区域，确定即可。6根据 sheet1 中的数据，新建一个数据透视图cha

18、rt1，要求：a显示工厂中各个职称的人数；bx 坐标设置为“职称；c计数项为职称；d将对应的数据透视表保存在sheet3 中。操作步骤：操作步骤： （参见（参见 e2-6e2-6）e6-e6-计算机考试计算机考试1.使用数组公式， 根据sheet1中的数据， 计算考试总分， 并将结果填入到“ （总分100） ”列中。计算方法： 总分=单选题+判断题+windows 操作题+excel 操作题+powerpoint 操作题+ie操作题操作步骤：操作步骤：将鼠标定位在 j2，输入公式“=d2+e2+f2+g2+h2+i2” ，并以 “ctrl+shift+enter”结束该公式的输入，自动填充。2

19、.使用文本函数中的一个函数，在sheet1 中，利用“学号”列的数据，根据以下条件获得考生所考级别，并将结果填入“级别”列中。要求： a.学号中的第八位指示的考试所考级别， 例如： “085200821023080”中的“2”标识了该考生所考级别为二级。 b.在“级别”列中，填入数据是函数的返回值。操作步骤：操作步骤：将鼠标定位在 c2，输入公式“=mid(a2,8,1)”，3.利用统计函数，根据以下要求对sheet1 中的数据进行计算： a统计“考 1 级的考生人数”，并将计算结果填入到n2 单元格中； b统计“考试通过人数（=60）”，并将结果填入到 n3 单元格中； c统计“1级考生考试

20、的平均分”，并将结果填入到n4 单元格中。（其中，计算时候的分母直接使用“n2”）操作步骤：操作步骤：a. 将鼠标定位在 n2 , 插入函数“=countif(c2:c56,1)”。b. 将鼠标定位在 n3 , 插入函数“=countif(j2:j56,=60)”。c. 将鼠标定位在 n4 , 插入函数“=sumif(c2:c56,1,j2:j56)/34” 。4.利用财务函数，根据以下要求对sheet2 中的数据进行计算：要求：a根据“投资情况表1”中的数据，计算10 年以后得到的金额，并将结果填入到 b7 单元格中； b根据“投资情况表 2”中的数据，计算预计投资金额，并将结果填入到e7单

21、元格中。操作步骤：操作步骤：a. =fv(b3,b5,b4,b2)b. = pv(e3,e4,e2)5.将 sheet1 复制到 sheet3，并对 sheet3 进行高级筛选： a筛选条件为：“级别”2、“总分”=70; b将筛选结果保存在sheet3 中。（注：无需考虑是否删除筛选条件）操作步骤：操作步骤：筛选条件为:级别2总分=706.根据 sheet1，在 sheet4 中新建一数据透视表。要求： a显示每个级别不同总分的人数汇总情况； b行区域设置为“级别” c列区域设置为“总分” d数据区域设置为“总分”； e计数项为总分。操作步骤：（参见操作步骤：（参见 e1-6e1-6）e7-

22、e7-员工资料表员工资料表1.仅使用文本函数 mid 函数和 concatenate 函数，对 sheet1 中的“出生日期”列进行自动填充。要求：a.填充的内容根据“身份证号码”列的内容来确定：-身份证号码中的第 7 位第 10 位：表示出生年份；-身份证号码中的第 11 位第 12 位：表示出生月份；-身份证号码中的第 13 位第 14 位：表示出生日；b.填充结果的格式为：xxxx 年 xx 月 xx 日（注意：不使用单元格格式进行设置）操作步骤：操作步骤：=concatenate(mid(e3,7,4),年,mid(e3,11,2),月,mid(e3,13,2),日),自动填充。2.根

23、据 sheet1 中“职务补贴率表”的数据，使用vlookup 函数，对“员工资料表”中的“职务补贴率”列进行自动填充。操作步骤：操作步骤：=vlookup(h3,$a$3:$b$6,2)，自动填充3.使用数组公式，在 sheet1 中“员工资料表”的“工资总额”列进行计算：计算方法：工资总额=基本工资*（1+职务补贴）操作步骤：操作步骤：=i3*(1+j3)，并以 “ctrl+shift+enter”结束该公式的输入，自动填充。4.在 sheet2 中，根据“固定资产情况表”，利用财务函数，对以下条件进行计算：条件：a.计算“每天折旧值”，并将结果填入到e2 单元格中； b.计算“每月折旧值

24、”，并将结果填入到e3 单元格中； c.计算“每年折旧值”，并将结果填入到e4 单元格中；操作步骤：操作步骤：=sln(b2,b3,b4*24*30)=sln(b2,b3,b4*24)=sln(b2,b3,b4)5.将 sheet1 中的“员工资料表”复制到sheet3，并对 sheet3 进行高级筛选： a筛选条件为：“性别”女、“职务”高级工程师 b将筛选结果保存在sheet3 中。（注：无需考虑是否删除筛选条件）操作步骤：操作步骤：筛选条件：性性 别别女职务职务高级工程师6.根据 sheet1 中的“员工资料表”，在sheet4 中新建一数据透视表。要求： a显示每种姓名的不同职务的人数

25、汇总情况； b行区域设置为“性别” c列区域设置为“职务” d数据区域设置为“职务” e计数项为职务。操作步骤：操作步骤： （参见（参见 e1-6e1-6）e8-e8-停车价目表停车价目表1使用 hlookup 函数，对 sheel1 中的停车单价进行自动填充。要求：根据sheel1 中的“停车价目表”价格，利用hlookup 函数对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。操作步骤：操作步骤：=hlookup(b9,$a$2:$c$3,2,false)，自动填充2在 sheel1 中，利用时间函数计算汽车在停车库中的停放时间，要求：a公式计算方法为“出库时间-入库时间”b格

26、式为：“小时：分钟：秒”（例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12”）操作步骤：操作步骤：=e9-d9（格式可另外设定）3使用函数公式，计算停车费用，要求：根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。注意：a停车按小时收费，对于不满一个小时的按一个小时计费；b对于超过整点小时十五分钟的多累积一个小时。 （例如 1 小时 23 分，将以 2 小时计费）操作步骤：操作步骤：=if(hour(f9)=0,1,hour(f9)+(minute(f9)15)*c94使用统计函数，对sheel1 中的“停车情况记录表”根据下列条件进行统计并填入相应单元格，要求：

27、a统计停车费用大于等于40 元的停车记录条数b统计最高的停车费用操作步骤：操作步骤：=countif(g9:g39,40)=max(g9:g39)5对 sheel2 进行高级筛选，要求：a筛选条件为：“车型” 小汽车，“应付金额”=30；b将结果保存在 sheel2 中。操作步骤：操作步骤：筛选条件：车型车型小汽车应付金额应付金额=306根据 sheel1，创建一个数据透视图 chart1，要求：a显示各种车型所收费用的汇总；b行区域设置为“车型”；c计费项为“应付金额”；d将对应的数据透视表保存在sheel3 中。操作步骤：操作步骤： （参看（参看 e2-6e2-6）e9-e9-公务员考试成

28、绩公务员考试成绩1. 使用 if 函数，对 sheet1 中的“学位”列进行自动填充。 要求：填充的内容根据 “学历”列的内容来确定（假定学生均已获得相应学位）：- 博士研究生博士- 硕士研究生硕士- 本科学士- 其他无操作步骤：操作步骤：=if(g3=博士研究生,博士,if(g3=硕士研究生,硕士,if(g3=本科,学士,无),自动填充2. 使用数组公式，在 sheet1 中计算： a. “笔试比例分”，计算方法为：（笔试成绩/3）*60 b. “面试比例分”，计算方法为：面试成绩*40 c. “总成绩”，计算方法为：笔试比例分+面试比例分操作步骤：操作步骤：=i3/3*60%，并以 “ct

29、rl+shift+enter”结束该公式的输入，自动填充。=k3*40%，并以 “ctrl+shift+enter”结束该公式的输入，自动填充。=j3+l3，并以 “ctrl+shift+enter”结束该公式的输入，自动填充。3. 修改数组公式，将sheet1 复制到 sheet2，在 sheet2 中计算：要求：修改“笔试比例分”的计算，计算方法为：（笔试成绩/2）*60）。操作步骤：操作步骤：=i3/2*60%，并以 “ctrl+shift+enter”结束该公式的输入，自动填充。4. 在 sheet2 中，添加一列，将其命名为“排名”。要求：使用rank 函数，根据“总成绩”对所有考生

30、排名。操作步骤：操作步骤：=rank(m3,m$3:m$18,0)，自动填充。5. 将 sheet2 复制到 sheet3，并对 sheet3 进行高级筛选。 a. 筛选条件为：“报考单位”中院、“性别”男、“学历”硕士研究生 b. 将筛选结果保存在 sheet3 中操作步骤：操作步骤： （略）（略）6. 根据 sheet2，在 sheet4 中新建一数据透视表。要求： a. 显示每个报考单位的人的不同学历的总人数 b. 行区域设置为“报考单位” c. 列区域设置为“学历” d. 数据区域设置为“学历” e. 计数项为学历操作步骤：操作步骤： （参看（参看 e1-6e1-6）e10-e10-体育成