AOA-EXCEL答案整理

第 13 套 等级考试

先选中第F列，点击[数据]→[数据有效性]，打开对话框，输入自定义公式：

说 明 ： 在 Excel 中 第 F 列 用 “ F:F ” 表 示 ； 在 选 中 第 F 列 时 ， 单 元 格 F1 被 激 活 ，式

“=COUNTIF(F:F,F1)=1”实际是输入在F1上的，所以在F2会自动复制成“=COUNTIF(F:F,F2)=1”， 依此类推，各单元格的值只能出现一次即不能重复。

2.在Sheet5中，使用条件格式将“性别”列中数据为“男”的单元格中字体颜色设置为红色、 加粗显示。

先选中C2:C56，再点击[开始]→[条件格式]→[新建规则]，跳出如下对话框进行相应设置：

3.使用数组公式，根据 Sheet1 中“学生成绩表”的数据，计算考试总分，并将结果填入到“总 分”列中。

??计算方法：总分=单选题+判断题+windows 操作题+Excel 操作题+PowerPoint 操作题+IE 操作题

数组公式为：

{=D3:D57+E3:E57+F3:F57+G3:G57+H3:H57+I3:I57}

注意：先选中 J3:J57，输入公式，再按组合键“Shift+Ctrl+Enter”。

4.使用文本函数中的一个函数，在 Sheet1 中，利用“学号”列的数据，根据以下要求获得考 生所考级别，并将结果填入“级别”列中。要求：

??学号中的第八位指示的考生所考级别，例如：“085200821023080”中的“2”标识了该 考生所考级别为二级；

33

公 第 13 套 等级考试

? 在“级别”列中，填入的数据是函数的返回值。 公式为： =MID(A3,8,1)

5.使用统计函数，根据以下要求对 Sheet1 中“学生成绩表”的数据进行统计。要求： ??统计“考 1 级的考生人数”，并将计算结果填入到 N2 单元格中； ??统计“考试通过人数（>=60）”,并将计算结果填入到 N3 单元格中；

??统计“全体 1 级考生的考试平均分”，并将计算结果填入到 N4 单元格中； ??注意：计算时，分母直接使用“N2”单元格的数据。 N2 单元格填入：

=COUNTIF(C3:C57,\ N3 单元格填入：

=COUNTIF(J3:J57,\ N4 单元格填入：

=SUMIF(C3:C57,\

6.使用财务函数，根据以下要求对 Sheet2 中的数据进行计算。要求：

??根据“投资情况表 1”中的数据，计算 10 年以后得到的金额，并将结果填入到 B7 单元 格中；

??根据“投资情况表 2”中的数据，计算预计投资金额，并将结果填入到 E7 单元格中。 （a）10 年以后得到的金额： =FV(B3,B5,B4,B2) （b）预计投资金额： =PV(E3,E4,E2)

7.将 Sheet1 中的“学生成绩表”复制到 Sheet3，并对 Sheet3 进行高级筛选。要求： ??筛选条件为：“级别”－2、“总分”－>=70； ??将筛选结果保存在 Sheet3 中。

先复制“学生成绩表”至 Sheet3，再设置条件区域如下：

点击[数据]→[筛选]→[高级]，经高级筛选后的结果为：

学号姓名

085200821024035胡四085200821049003郑五

级别22

单选题85

学生成绩表

判断题 w i n d o w s 操作题e l 操作题题 P o i n t 操作题操作题ExcPowerIE总分9202019179361016171670

8.根据 Sheet1 中的“学生成绩表”，在 Sheet4 中新建一张数据透视表。要求： ??显示每个级别不同总分的人数汇总情况； ??行区域设置为“级别”； ??列区域设置为“总分”； ??数据区域设置为“总分”； ??计数项为总分。

34

第 14 套 通讯费计划表

鼠标先点在 Sheet1 的表上，再选[插入]→[数据透视表]，“现有工作表”“Sheet4!$A$1”选为； 进入数据布局窗口后，把字段“级别”拖入行标签、“总分”拖入列标签和数值区，并把“求 和项”改为“计数项”，如下图：

点击，改“求和

项”为“计数项”

在 Sheet4 上产生的数据透视表为：

计数项:总分行标签123总计

列标签

39 45 48 49 51 52 55 57 58 60 62 63 64 65 66 67 68 69 70 71 72 73 74 75 78 82 83 93 总计 1112111111313211133121134 1111231111 11112111110

211121111133415234114142211155

第 14 套 通讯费计划表

在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存 盘。

1.在Sheet1的B40单元格中输入分数 2/3 。 输入： 0 2/3

2.在Sheet1中设定41行中不能输入重复的数值。

先选中第41行，点击[数据]→[数据有效性]，打开对话框，输入自定义公式：

说明：在Excel中第41行用“41:41”表示；在选中第41行时，单元格A41被激活，公式

35

第 14 套 通讯费计划表

“ =COUNTIF(41:41, A41)=1 ” 实 际 是 输 入 在 A41 上 的 ， 所 以 在 B41 会 自 动 复 制 成 “=COUNTIF(41:41, B41)=1”，依此类推，各单元格的值只能出现一次即不能重复。

3.使用VLOOKUP函数，根据Sheet1中的“岗位最高限额明细表”，填充“通讯费年度计划表” 中的“岗位标准”列。

公式为：

=VLOOKUP(C4,$K$4:$L$12,2,FALSE) 或 =VLOOKUP(C4,$K$4:$L$12,2,0)

4.使用INT函数，计算Sheet1中“通讯费年度计划表”的“预计报销总时间”列。 要求： ??每月以30天计算；

??将结果填充在“预计报销总时间”列中。 公式为：

=INT((F4-E4)/30)

5.使用数组公式，计算Sheet1中“通讯费年度计划表”的“年度费用”列。 ??计算方法为：年度费用 = 岗位标准 * 预计报销总时间。 数组公式为：

=｛D4:D26*G4:G26｝

注意：先选中 H4:H26，输入公式，再按组合键“Shift+Ctrl+Enter”。

6.使用函数，根据Sheet1中“通讯费年度计划表”的“年度费用”列，计算预算总金额。要 求：

??将结果保存在Sheet1中的C2单元格中。

??并根据C2单元格中的结果，转换为金额大写形式，保存在Sheet1中的F2单元格中。 在C2中输入公式： =SUM(H4:H26)

在F2中确认单元格格式为“数字→特殊→中文大写数字”，并输入公式： =C2

7.把Sheet1中的“通讯费年度计划表”复制到Sheet2中，并对Sheet2进行自动筛选。要求： ??筛选条件为：“岗位类别”-技术研发、“报销地点”-武汉； ??将筛选条件保存在Sheet2中。

先复制“通讯费年度计划表”至Sheet2，其中多列数据会出错，需重新复制、粘贴“岗位标 准”列（用“值”粘贴）即可恢复正常。

点击菜单[数据]→[筛选]，并依次设置“岗位类别” “技术研发”为、“报销地点” “武汉”为， 筛选结果如下：

通讯费年度计划表

起始时间

截止时间

预算总金额（元）: 员工编号 A006 A017 A022

姓名 陈文玮 任丽娜 李军

286300 岗位类别 技术研发 技术研发 技术研发

金额( 大写) : 岗位标准 200 200 200

2004年6月 2005年9月 2004年12月 2005年9月 2004年6月 2005年1月

贰拾捌万陆仟叁佰 预计报销 年度费用 总时间 163200 102000 81600

报销地点 武汉 武汉 武汉

8.根据Sheet1中的“通讯费年度计划表”，在Sheet3中新建一张数据透视表。要求：

36