典型试题1题目要求与操作解答

1、excel 2010 高级应用操作题典型试题1打开素材库中的“典型试题 5-1.xlsx ”文件，按下面的操作要求进行操作，并把操作结果存盘。注意 ：在做题时，不得将数据表进行随意更改。1操作要求（ 1）在 sheet5 的 a1单元格中设置为只能录入错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本。 当录入位数错误时，提示5位数字或文本”。（ 2）在 sheet5 的 b1 单元格中输入分数1/3 。（ 3）使用数组公式，对sheet1 中“教材订购情况表”的订购金额进行计算。* 将结果保存在该表的“金额”列当中。* 计算方法为：金额订数*单价。（ 4）使用统计函数， 对 she

2、et1 中“教材订购情况表”的结果按以下条件进行统计，将结果保存在sheet1 中的相应位置。要求：* 统计出版社名称为“高等教育出版社”的书的种类数, 并将结果保存在sheet1中并l2单元格中。* 统计订购数量大于 110 且小于 850 的书的种类数， 并将结果保存在 sheet1 中 l3 单元格中。（ 5）使用函数，计算每个用户所订购图书所需支付的金额总数， 并将结果保存在 sheet1 中“用户支付情况表”的“支付总额”列中。（ 6）使用函数，判断 sheet2 中的年份是否为闰年，如果是，结果保存“闰年”；如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。* 闰年定

3、义：年数能被4 整除而不能被100 整除，或者能被400 整除的年份。（ 7）将 sheet1 中的“教材订购情况表”复制到sheet3 中，对 sheet3 进行高级筛选。 要求：* 筛选条件为“订数 500，且金额 30000”。* 将结果保存在 sheet3 中。 注意：* 无须考虑是否删除或移动筛选条件。* 复制过程中，将标题项“教材订购情况表”连同数据一同复制。* 数据表必须顶格放置。* 复制过程中，数据保持一致。（ 8）根据 sheet1 中“教材订购情况表”的结果，在sheet4 中新建一张数据透视表。要求：* 显示每个客户在每个出版社所订的教材数目。* 行区域设置为“出版社”。

4、* 列区域设置为“客户”。* 求和项为“订数” 。* 数据区域设置为“订数”。2解答步骤 1：根据操作要求（1），选中 sheet5 工作表中的a1 单元格， 在“数据” 选项卡中，单击 “数据工具” 组中的 “数据有效性” 下拉按钮， 在打开的下拉列表中选择 “数据有效性”选项，如图 5-13 所示。步骤 2：在打开的“数据有效性”对话框中，选择“设置”选项卡，再选择“允许”为“文本长度”，“数据”为“等于”，在“长度”文本框中输入“5”，如图5-14步骤 3：在“出错警告”选项卡中，选择“样式”为“警告”，在“错误信息”文本框中输入“只能录入5 位数字或文本”，如图5-15所示，单击“确定

5、”按钮。所示。图 5-13“数据有效性”下拉列表图 5-14“设置”选项卡图 5-15“出错警告”选项卡步骤 4：根据操作要求（2），在sheet5的 b1 单元格中，输入“0 1/3”，则在b1 单元格中输入了分数1/3 。步骤 5：根据操作要求（3），在 sheet1 工作表中，先选中i3:i52单元格区域，再输入公式“ g3:g52*h3:h52”，然后同时按组合键ctrl shift enter ，此时，公式编辑栏中显示“ g3:g52*h3:h52 ”。说明 ：公式中单元格或单元格区域也可用鼠标选择输入，下同。步骤 6：根据操作要求（4），选中 l2 单元格，单击编辑栏左侧的“插入函

6、数”按扭，打开“插入函数”对话框，在“或选择类别”下拉列表框中选择“统计”，在“选择函数”列表框中选择“countif”函数，如图5-16所示。步骤 7：单击“确定”按钮，打开“函数参数”对话框，输入range 参数为“ d3:d52”，criteria参数为“高等教育出版社”，如图 5-17所示，再单击“确定”按钮，即可统计出出版社名称为“高等教育出版社”的书的种类数（6 ）。此时，公式编辑栏中显示“countif(d3:d52,高等教育出版社 ) ”。图 5-16“插入函数”对话框图 5-17“函数参数”对话框步骤8：选中l3 单元格，在公式编辑栏中输入公式“countif(g3:g52,

7、 110) countif(g3:g52, 850 ) ”，再按回车键确认，即可统计出订购数量大于110 且小于 850的书的种类数（28）。说明 ： countif函数的功能和用法，请参见上一节中的相关内容。步骤 9：根据操作要求（5），选中 l8 单元格，单击编辑栏左侧的“插入函数”按扭，打开 “插入函数” 对话框， 在“或选择类别” 下拉列表框中选择“数学与三角函数” ，在“选择函数”列表框中选择“sumif”函数。步骤 10：单击“确定”按钮，打开“函数参数”对话框，输入range 参数为“ a3:a52 ”，criteria参数为“ c1”， sum_range参数为“ i3:i52

8、”，如图 5-18所示，再单击“确定”按钮，即可计算出 c1 用户所订购图书需支付的金额总数（ 721301）。此时，公式编辑栏中显示“ sumif(a3:a52, c1,i3:i52) ”。图 5-18“函数参数”对话框步骤 11：在 l9 单元格中输入公式“ sumif(a3:a52, c2,i3:i52)”，按回车键确认，即可计算出c2 用户所订购图书需支付的金额总数（53337）。步骤 12：在 l10 单元格中输入公式“sumif(a3:a52, c3 ,i3:i52)”，按回车键确认，即可计算出c3 用户所订购图书需支付的金额总数（65122）。步骤 13：在 l11 单元格中输入

9、公式“sumif(a3:a52, c4 ,i3:i52)”，按回车键确认，即可计算出c4 用户所订购图书需支付的金额总数（71253）。说明 ： sumif函数的功能和用法，请参见上一节中的相关内容。步骤 14：根据操作要求（6），在 sheet2 工作表中，选中b2 单元格，在公式编辑栏中输入公式“if(or(and(mod(a2,4) 0,mod(a2,100) 0),mod(a2,400) 0), 闰年 , 平年 ) ”，按回车键确认，即可判断出该年份是“闰年”还是“平年”。步骤 15：双击 b2 单元格的填充柄，即可判断出其余年份是“闰年”还是“平年”。说明 ： if 、 or、 an

10、d、 mod函数的功能和用法，请参见上一节中的相关内容。步骤 16：根据操作要求（7），选中 sheet1 工作表中的a1:i52单元格区域，右击鼠标选择“复制”命令，在sheet3 工作表的a1 单元格中，右击鼠标选择“粘贴”命令。步骤 17：在 sheet3 工作表的空白区域创建高级筛选条件，可在k2:l3 单元格区域中输入高级筛选条件，如图5-19所示。步骤 18：在 sheet3 工作表中，单击a1:i52单元格区域中的任一单元格，在“数据”选项卡中，单击“排序和筛选”组中的“高级”按钮，如图5-20所示，打开“高级筛选”对话框，“列表区域”文本框中已自动填入数据清单所在的单元格区域，

11、如图5-21所示。步骤 19：将光标置于“条件区域”文本框内，用鼠标拖选前面创建的高级筛选条件单元格区域k2:l3 ，“条件区域” 文本框内会自动填入该区域地址，如图 5-22所示，再单击“确定”按钮。图 5-19创建高级筛选条件图 5-20“排序和筛选”组图 5-21设置“列表区域”图 5-22设置“条件区域”步骤 20：根据操作要求（ 8），在 sheet1 工作表中，单击 a1:i52 单元格区域中的任一单元格，在“插入”选项卡中，单击“表格”组中的“数据透视表”下拉按钮，在打开的下拉列表中选择“数据透视表”选项，如图5-23所示。步骤 21：在打开的“创建数据透视表”对话框中，“表 / 区域”文本框中已经自动填入光标所在的单元格区域（数据清单），如图 5-24所示，选中“现有工作表”单选按钮，将光标置于“位置”文本框中，再单击sheet4 工作表中的a1 单元格，该单元格地址会自动填入“位置”文本框中，如图5-25所示。图 5-23“数据透视表”下拉列表图5