AOA-EXCEL答案整理

第 5 套 灯泡采购

点中产生的图表，[鼠标右键]→[移动图表]，出现窗口，选“新工作表”为“Chart1”。即 在 Chart1 上产生如下透视图：

汇总

8 7 6 5 4 3 2 1 0

拱墅区 江干区 上城区 西湖区 下城区 余杭区

汇总

同时，在 Sheet4 上产生的数据透视表如下：

行标签 拱墅区 江干区 上城区 西湖区 下城区 余杭区 总计

计数项:所在区域

6 6 5 6 6 7 36

第 5 套 灯泡采购

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

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

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

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

13

第 5 套 灯泡采购

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

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

3.使用数组公式，计算 Sheet1 中的每种产品的价值，将结果保存到表中的“价值”列中。 ??计算价值的计算方法为：“单价*每盒数量*采购盒数”。 数组公式为： {=E2:E17*F2:F17*G2:G17}

说明：先选中 H2:H17，再输入公式，结束按 Ctrl+Shift+Enter。

4.在 Sheet2 中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存 相应的单元格中。

a. 计算：商标为上海，瓦数小于 100 的白炽灯的平均单价； =DAVERAGE(A1:H17,E1,J2:L3)

b. 计算：产品为白炽灯，其瓦数大于等于 80 且小于等于 100 的数量。 =DSUM(A1:H17,G1,J7:L8)

5.某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查 对象，只能回答 Y（吸烟）或者 N（不吸烟）。根据调查情况，制做出 Sheet3。请使用函数， 统计符合以下条件的数值。

a.统计未登记的部门个数； =COUNTBLANK(B2:E11)

b.统计在登记的部门中，吸烟的部门个数。 =COUNTIF(B2:E11,\

6.使用函数，对 Sheet3 中的 B21 单元格中的内容进行判断，判断其是否问文本，如果是，结 果为“TRUE”；如果不是，结果为“FALSE”，并将结果保存在 Sheet3 中的 B22 单元格当中。 在 B22 单元格写入公式： =ISTEXT(B21)

7.将 Sheet1 复制到 Sheet4 中，对 Sheet4 进行高级筛选，要求： ??筛选条件：“产品为白炽灯，商标为上海”，并将结果保存； ??将结果保存在 Sheet4 中。

先将表格从 Sheet1 复制到 Sheet4，再设置筛选所需的条件区域为：

14

第 6 套 房产销售

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

产品

瓦数 寿命（小时） 商标

单价

每盒数量 采购盒数

价值

白炽灯 白炽灯 白炽灯 白炽灯 白炽灯 200 80 10 100 40 3000 1000 800 2000 1000 上海 上海 上海 上海 上海 4.50 0.20 0.20 0.80 0.10 4 40 25 10 20 3 3 2 5 5 54.00 24.00 10.00 40.00 10.00

8.根据 Sheet1 的结果，在 Sheet5 中创建一张数据透视表，要求： ??显示不同商标的不同产品的采购数量； ??行区域设置为“产品”； ??列区域设置为“商标”； ??计数项为“采购盒数”。

鼠标先点在 Sheet1 的表上，再选[插入]→[数据透视表]，“现有工作表”“Sheet5!$A$1”选为； 进入数据布局窗口后，把字段“产品”拖入行标签、“商标”拖入列标签、“采购盒数”拖入 数值区，如下图：

在 Sheet5 上产生的数据透视表如下所示：

计数项:采购盒数 行标签 白炽灯 氖管 其他 日光灯 总计

列标签 北京

上海 (空白) 总计 459 112 22 22 7815

第 6 套 房产销售

在考生文件夹的Excel子文件夹中，已有Excel.xlsx文件。按下列要求操作，并将结果存盘。 1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因， 样式为“警告”，错误信息为“只能录入5位数字或文本”。

15

第 6 套 房产销售

选定Sheet5的A1，点击[数据]→[数据有效性]，打开对话框进行设置：

2.在Sheet1中，使用条件格式将“预定日期”列中日期为2008-4-1后的单元格中字体颜色设置 为红色、加粗显示。

先选中“预定日期”这列的单元格，再点击[开始]→[条件格式]→[新建规则]，跳出如下对话 框进行相应设置：

3. 使用公式，计算 Sheet1 中“房产销售表”的房价总额，并保存在“房产总额”列中。 ??计算公式为：房价总额 = 面积 * 单价。 公式为： =F3*G3

4.使用数组公式，计算 Sheet1 中“房产销售表”的契税总额，并保存在“契税总额”列中。 ??计算公式为：契税总额 = 契税 * 房价总额。 数组公式为： {=H3:H26*I3:I26}

先选中 J3:J26，再输入公式，结束按 Ctrl+Shift+Enter。

5.使用函数，根据 Sheet1 中“房产销售表”的结果，在 Sheet2 中统计每个销售人员的销售 总额，将结果保存在 Sheet2 中的“销售总额”列中。

“人员甲”的销售总额：

=SUMIF(Sheet1!K$3:K$26,A2,Sheet1!I$3:I$26) 其它人员的销售总额可用填充柄复制公式求得。

注意：由于销售总额数值较大，应拉大 B 列的宽度，使得数据全部显示出来，否则扣分。

16