excel典型试题操作步骤

6.2 典型试

题

典型试题1

打开素材库中的“典型试题6-1.x ls”文件，按下面的操作要求进行操作，并把操作结果存盘。

注意：在做题时，不得将数据表进行更改。

一、操作要求

1. 在Sh e et5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。

2. 使用数组公式，对S he e t1中“教材订购情况表”的订购金额进行计算，并将结果保存在该表的“金额”列当中。

计算方法：金额 = 订数* 单价

3. 使用统计函数，对S he e t1中“教材订购情况表”的结果按以下条件进行统计，并将结果保存在S heet1 中的相应位置。

要求：

（1）统计名称为“高等教育”的书的种类数,并将结果保存在s heet1

中L2 单元格中。

（2）统计订购数量大于110 且小于850 的书的种类数,并将结果保存在sheet1 中L3 单元格中。

4. 使用函数，计算每个用户所订购图书所需支付的金额总数，并将结果保存在S h e et1中“用户支付情况表”的“支付总额”列中。

5. 使用函数，判断S heet2 中的年份是否为闰年，如果是，结果保存“闰年”，如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。

说明：

闰年定义：年数能被4整除而不能被100 整除，或者能被400 整除的年份。

6. 将S hee t1中的“教材订购情况表”复制到S hee t3中，对S heet3 进行高级筛选.

要求：

（1）筛选条件为“订数>=500，且金额总数<=30000”。

（2）将结果保存在S heet3 中。

注意：

（1）无需考虑是否删除或移动筛选条件。

（2）复制过程中，将标题项“教材订购情况表”连同数据一同复制。 （3）数据表必须顶格放置。

7. 根据 Sheet1 中“教材订购情况表”的结果，在 S heet4 中新建一数据透视 表。 要求：

（1）显示每个客户在每个所订的教材数目。 （2）行区域设置为“”。 （3）列区域设置为“客户”。 （4）

求和项为“。 （5）数据区域设置为“订数”。

二、解答

步骤

1：选中 S heet5 工作表中的 A 1 单元格，选择菜单“数据”→“有效性”命 令（如图 6-13 所示），在打开的“数据有效性”对话框中，选择“设置”选项卡， 再选择“允许”为“文本长度”，“数据”为“等于”，在“长度”文本框中输入“5”， 如图 6-14 所示。 选择“出错警告”选项卡，再选择“样式”为“警告”，在“错误信息”文本框 中输入“只能录入 5 位数字或文本”，如图 6-15 所示，单击“确定”按钮。

图 6-13 选择“有效性”命令

图6-14 “设置”选项卡

图6-15 “出错警告”选项卡

步骤2：在Sheet1 工作表中，先选中I3:I52单元格区域，再输入公式

“=G 3:G 52*H 3:H

，然后同时按组合键 Sh i ft+Ctr l +Enter ，此时，公式编辑栏显示

“{=G 3:G 52*H 3:H 5。 说明：公式中单元格或单元格区域也可用鼠标选择输入，下同。 步

骤 3：选中 L 2 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “选择类别”下拉

图 6-16 “插入函数”对话框

图 6-17 “函数参数”对话框

选 中 L3 单 元 格 ， 在 公 式 编 辑 栏 中 输 入

公 式 “=C O U N T I F (G 3:G 52,"<8说明：COUN T IF 函数的功能和用法，请参见上一节的相关容。 步骤

4：选中 L8 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “选择类别”下拉列表框中选择“数学与三角，在“选择函数”列表框中选择 “SUMIF ”函数。单击“确定”按钮，弹出“函数参数”对话框，输入“R a 6-18 所示。再按“确定”按钮，即可计算出 c1 用户所订购图书需支付的金额总数 （7213。此时，公式编辑栏显示“=S U M

图 6-18 “函数参数”对话框

在

L 9 单元格中输

入公式“=S U M I F (A 3:A 52,"=可计算出 c 2 用户所订购图书需支付的金额总数（53在 L 10 单元格中输入公式“=S U

M I F (A 3:可计算出 c 3 用户所订购图书需支付的金额总数（在

L 11 单元格中输入公式

“

=S U M I F (A 可计算出 c 4 用户所订购图书需支付的金额总说明：SUMIF 函数的功能和用法，请参见上一节的相关容。

步骤 5：在 Sheet2 工作表中，选择 B2 单元格，在公式编辑栏中输入公式 “=IF(OR(AND(M OD(A2,4)=0,MOD(A 2,100)<>0),MOD (A 2,400)=0),"闰年","平年")”， 按回车键确认，即可判断出该年份是闰双击 B 2 单元格的填充柄，即可

判断

说明：IF 、OR 、AND 、MOD 函数的功能和用法，请参见上一节的相关容。 步骤 6：选中 S heet1 工作表中的 A1：I52 单元格区域，单击右键在快捷菜单中选 择“复制”命令。单击 S h eet3 工作表中的 A1 单元格，单击右键在快捷菜单中选择“粘 贴”命令。 在

Sheet3 工作表的空白区域创建筛选条件，如图 6-19 所示，在 K2：L3 单元格 区域中输入筛选条件。单击 Sheet3 工作表中 A1：I 52 单元格区域中的任一单元格， 选择菜单“数据”→“筛