excel典型试题操作步骤.docx

excel典型试题操作步骤.docx

《excel典型试题操作步骤.docx》由会员分享，可在线阅读，更多相关《excel典型试题操作步骤.docx（21页珍藏版）》请在冰点文库上搜索。

excel典型试题操作步骤

典型试题

典型试题1

打开素材库中的“典型试题”文件，按下面的操作要求进行操作，并把操作

结果存盘。

注意：

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

一、操作要求

1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。

当录入位数错误时，提示

错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。

2.使用数组公式，对Sheet1中“教材订购情况表”的订购金额进行计算，并将结果

保存在该表的“金额”列当中。

计算方法：

金额=订数*单价

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

要求：

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

中L2单元格中。

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

L3单元格中。

4.使用函数，计算每个用户所订购图书所需支付的金额总数，并将结果保存在Sheet1

中“用户支付情况表”的“支付总额”列中。

5.使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”，如果

不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。

说明：

闰年定义：

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

6.将Sheet1中的“教材订购情况表”复制到Sheet3中，对Sheet3进行高级筛选.

要求：

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

（2）将结果保存在Sheet3中。

注意：

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

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

（3）数据表必须顶格放置。

7.根据Sheet1中“教材订购情况表”的结果，在Sheet4中新建一张数据透视

表。

要求：

（1）显示每个客户在每个出版社所订的教材数目。

（2）行区域设置为“出版社”。

（3）列区域设置为“客户”。

（4）求和项为“订数”。

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

二、解答

步骤1：

选中Sheet5工作表中的A1单元格，选择菜单“数据”→“有效性”命令（如图6-13所示），在打开的“数据有效性”对话框中，选择“设置”选项卡，再选择“允许”为“文本长度”，“数据”为“等于”，在“长度”文本框中输入“5”，如图6-14所示。

选择“出错警告”选项卡，再选择“样式”为“警告”，在“错误信息”文本框

中输入“只能录入5位数字或文本”，如图6-15所示，单击“确定”按钮。

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

图6-14“设置”选项卡

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

步骤2：

在Sheet1工作表中，先选中I3:

I52单元格区域，再输入公式

“=G3:

G52*H3:

H52”，然后同时按组合键Shift+Ctrl+Enter，此时，公式编辑栏显示

“{=G3:

G52*H3:

H52}”。

说明：

公式中单元格或单元格区域也可用鼠标选择输入，下同。

步骤3：

选中L2单元格，单击插入函数按扭，打开“插入函数”对话框，在“选择类别”下拉列表框中选择“统计”，在“选择函数”列表框中选择“COUNTIF”函数，如图6-16所示。

单击“确定”按钮，弹出“函数参数”对话框，输入“Range”参数为“D3：

D52”，“Criteria”参数为“高等教育出版社”，如图6-17所示。

再按“确定”按钮，即可统计出出版社名称为“高等教育出版社”的书的种类数（6）。

此时，公式编辑栏显示“=COUNTIF（D3:

D52,"高等教育出版社"）”。

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

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

选中L3单元格，在公式编辑栏中输入公式“=COUNTIF（G3:

G52,"<850"）-COUNTIF（G3:

G52,"<=110"）”，再按回车键确认，即可统计出订购数量大于110且小于850的书的种类数（28）。

说明：

COUNTIF函数的功能和用法，请参见上一节的相关内容。

步骤4：

选中L8单元格，单击插入函数按扭，打开“插入函数”对话框，在“选择类别”下拉列表框中选择“数学与三角函数”，在“选择函数”列表框中选择“SUMIF”函数。

单击“确定”按钮，弹出“函数参数”对话框，输入“Range”参数为“A3：

A52”，“Criteria”参数为“"=c1"”，“Sum_range”参数为“I3:

I52”，如图

6-18所示。

再按“确定”按钮，即可计算出c1用户所订购图书需支付的金额总数

（721301）。

此时，公式编辑栏显示“=SUMIF（A3:

A52,"=c1",I3:

I52）”。

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

在L9单元格中输入公式“=SUMIF（A3:

A52,"=c2",I3:

I52）”，按回车键确认，即

可计算出c2用户所订购图书需支付的金额总数（53337）。

在L10单元格中输入公式“=SUMIF（A3:

A52,"=c3",I3:

I52）”，按回车键确认，即

可计算出c3用户所订购图书需支付的金额总数（65122）。

在L11单元格中输入公式“=SUMIF（A3:

A52,"=c4",I3:

I52）”，按回车键确认，即

可计算出c4用户所订购图书需支付的金额总数（71253）。

说明：

SUMIF函数的功能和用法，请参见上一节的相关内容。

步骤5：

在Sheet2工作表中，选择B2单元格，在公式编辑栏中输入公式“=IF（OR（AND（MOD（A2,4）=0,MOD（A2,100）<>0）,MOD（A2,400）=0）,"闰年","平年"）”，按回车键确认，即可判断出该年份是“闰年”还是“平年”。

双击B2单元格的填充柄，即可判断出其余年份是“闰年”还是“平年”。

说明：

IF、OR、AND、MOD函数的功能和用法，请参见上一节的相关内容。

步骤6：

选中Sheet1工作表中的A1：

I52单元格区域，单击右键在快捷菜单中选择“复制”命令。

单击Sheet3工作表中的A1单元格，单击右键在快捷菜单中选择“粘贴”命令。

在Sheet3工作表的空白区域创建筛选条件，如图6-19所示，在K2：

L3单元格区域中输入筛选条件。

单击Sheet3工作表中A1：

I52单元格区域中的任一单元格，选择菜单“数据”→“筛选”→“高级筛选”命令（如图6-20所示），打开“高级筛选”对话框，“列表区域”文本框中已自动填入数据清单所在的单元格区域（如图6-21所示）。

将光标定位在“条件区域”文本框内，用鼠标拖选前面创建的筛选条件单元

格区域K2：

L3，“条件区域”文本框内会自动填入该区域地址（如图6-22所示）。

再

单击“确定”按钮。

图6-19创建筛选条件

图6-20选择“高级筛选”命令

图6-21设置“列表区域”图6-22设置“条件区域”

步骤7：

单击Sheet1工作表中A1：

I52单元格区域中的任一单元格，选择菜单“数据”→“数据透视表和数据透视图”命令（如图6-23所示），打开“数据透视表和数据透视图向导——3步骤之1”对话框（如图6-24所示），选中“数据透视表”单选钮后，再单击“下一步”按钮，打开“数据透视表和数据透视图向导——3步骤之2”对话框，“选定区域”文本框中已自动填入数据清单所在的单元格区域（如图6-25所示），单击“下一步”按钮，打开“数据透视表和数据透视图向导——3步骤之3”对话框，选中“现有工作表”单选钮，再单击Sheet4工作表中的A1单元格，该单元格地址会自动填入“现有工作表”下方的地址文本框中（如图6-26所示），单击“完成”按钮。

在弹出的如图6-27所示界面中，将字段“出版社”拖至行字段区域，将字段“客

户”拖至列字段区域，将字段“订数”拖至数据项区域，结果如图6-28所示。

保存文件后退出。

图6-23选择“数据透视表和数据透视图”命令

图6-24“数据透视表和数据透视图向导——3步骤之1”对话框

图6-25“数据透视表和数据透视图向导——3步骤之2”对话框

图6-26“数据透视表和数据透视图向导——3步骤之3”对话框

图6-27“数据透视表”布局

图6-28数据透视表

典型试题2

打开素材库中的“典型试题”文件，按下面的操作要求进行操作，并把操作

结果存盘。

注意：

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

一、操作要求

1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。

当录入位数错误时，提示

错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。

2.使用时间函数，对Sheet1中用户的年龄进行计算。

要求：

假设当前时间是“2008-11-15”，结合用户的出生年月，计算用户的年龄，

并将其计算结果保存在“年龄”列当中。

计算方法为两个时间年份之差。

3.使用REPLACE函数，对Sheet1中用户的电话号码进行升级。

要求：

对“原电话号码”列中的电话号码进行升级。

升级方法是在区号（0571）

后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。

例如：

电话号码“008”升级后为“0808”

4.在Sheet1中，使用AND函数，根据“性别”及“年龄”列中的数据，判断所有用

户是否为大于等于40岁的男性，并将结果保存在“是否>=40男性”列中。

注意：

如果是，保存结果为TRUE；否则，保存结果为FALSE。

5.根据Sheet1中的数据，对以下条件，使用统计函数进行统计。

要求：

（1）统计性别为“男”的用户人数，将结果填入Sheet2的B2单元格中。

（2）统计年龄为“>40”岁的用户人数，将结果填入Sheet2的B3单元格中。

6.将Sheet1复制到Sheet3，并对Sheet3进行高级筛选。

要求：

（1）筛选条件为：

“性别”－女、“所在区域”－西湖区。

（2）将筛选结果保存在Sheet3中。

注意：

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

（2）数据表必须顶格放置。

7.根据Sheet1的结果，创建一个数据透视图Chart1。

要求：

（1）显示每个区域所拥有的用户数量。

（2）x坐标设置为“所在区域”。

（3）计数项为“所在区域”。

（4）将对应的数据透视表保存在Sheet4中。

二、解答

步骤1：

操作方法参见“典型试题1”中的步骤1。

步骤2：

在Sheet1工作表中，选中D2单元格，在公式编辑栏中输入公式“=YEAR（"2008-11-15"）-YEAR（C2）”，再按回车键确认，即计算出该用户的年龄（41）。

双击D2单元格的填充柄，即可计算出其余用户的年龄。

说明：

YEAR函数的功能和用法，请参见上一节的相关内容。

步骤3：

选中G2单元格，单击插入函数按扭，打开“插入函数”对话框，在“选择类别”下拉列表框中选择“文本”，在“选择函数”列表框中选择“REPLACE”函数，单击“确定”按钮后，弹出“函数参数”对话框，输入“Old_text”参数为“F2”，“Start_num”参数为“5”，“Num_chars”参数为“0”，“New_text”参数为“8”，如图6-29所示。

再按“确定”按钮，即可对第一个电话号码进行升级。

此时，公式编辑栏显示“=REPLACE（F2,5,0,"8"）”。

双击G2单元格的填充柄，即可对其余电话号码进行升级。

说明：

REPLACE函数的功能和用法，请参见上一节的相关内容。

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

步骤4：

在H2单元格中，输入公式“=AND（B2="男",D2>=40）”，再按回车键确认，即

可判断该用户是否为大于等于40岁的男性。

双击H2单元格的填充柄，即可判断其余用户是否为大于等于40岁的男性。

步骤5：

在Sheet2工作表的B2单元格中，输入公式“=COUNTIF（Sheet1!

B2:

B37,"男

"）”，再按回车键确认，即可统计出男性用户人数（18）。

在Sheet2工作表的B3单元格中，输入公式“=COUNTIF（Sheet1!

D2:

D37,">40"）”，再

按回车键确认，即可统计出大于40岁的用户人数（14）。

步骤6：

选中Sheet1工作表中的A1：

H37单元格区域，单击右键在快捷菜单中选择“复制”命令。

单击Sheet3工作表中的A1单元格，单击右键在快捷菜单中选择“粘贴”命令。

在Sheet3工作表的空白区域创建筛选条件，如图6-30所示，在J1：

K2单元格区域中输入筛选条件。

单击Sheet3工作表中A1：

H37单元格区域中的任一单元格，选择菜单“数据”→“筛选”→“高级筛选”命令，打开“高级筛选”对话框，“列表区域”文本框中已自动填入数据清单所在的单元格区域。

将光标定位在“条件区域”文本框内，用鼠标拖选前面创建的筛选条件单元格区域J1：

K2，“条件区域”文本框内会自动填入该区域地址（如图6-31所示）。

再单击“确定”按钮。

注意：

J1单元格中“性”和“别”之间有空格，原因是B1单元格中“性”和“别”

之间有空格。

图6-30创建筛选条件

图6-31设置“条件区域”

步骤7：

单击Sheet1工作表中A1：

H37单元格区域中的任一单元格，选择菜单“数

据”→“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导——

3步骤之1”对话框，选中“数据透视图（及数据透视表）”单选钮（如图6-32所示），再单击“下一步”按钮，打开“数据透视表和数据透视图向导——3步骤之2”对话框，“选定区域”文本框中已自动填入数据清单所在的单元格区域（如图6-33所示），单击“下一步”按钮，打开“数据透视表和数据透视图向导——3步骤之3”对话框，选中“现有工作表”单选钮，再单击Sheet4工作表中的A1单元格，该单元格地址会自动填入“现有工作表”下方的地址文本框中（如图6-34所示）。

单击“布局”按钮，在弹出的如图6-35所示界面中，将字段“所在区域”分别拖至“行”区域和“数据”区域内，然后按“确定”按钮返回图6-34所示对话框，按“完成”按钮。

这时在Sheet4工作表前会插入一张名为Chart1的工作表。

打开该工作表可看到根据Sheet1工作表创建的数据透视图（如图6-36所示），打开Sheet4工作表有如图6-37所示的相应的数据透视表。

保存文件后退出。

图6-32“数据透视表和数据透视图向导——3步骤之1”对话框

图6-33“数据透视表和数据透视图向导——3步骤之2”对话框

图6-34“数据透视表和数据透视图向导——3步骤之3”对话框

图6-35“数据透视表”布局

图6-36数据透视图

图6-37数据透视表

典型试题3

打开素材库中的“典型试题”文件，按下面的操作要求进行操作，并把操作

结果存盘。

注意：

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

一、操作要求

1.在Sheet4的A1单元格中设置为只能录入5位数字或文本。

当录入位数错误时，提示

错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。

2.使用VLOOKUP函数，对Sheet1中的“三月份销售统计表”的“产品名称”列和“产品单价”列进行填充。

要求：

根据“企业销售产品清单”，使用VLOOKUP函数，将产品名称和产品单价填充

到“三月份销售统计表”的“产品名称”列和“产品单价”列中。

3.使用数组公式，计算Sheet1中的“三月份销售统计表”中的销售金额，并将结果

填入到该表的“销售金额”列中。

计算方法：

销售金额=产品单价*销售数量

4.使用统计函数，根据“三月份销售统计表”中的数据，计算“分部销售业绩统计

表”中的总销售额，并将结果填入该表的“总销售额”列。

5.在Sheet1中，使用RANK函数，在“分部销售业绩统计”表中，根据“总销售额”

对各部门进行排名，并将结果填入到“销售排名”列中。

6.将sheet1中的“三月份销售统计表”复制到Sheet2中，对Sheet2进行高级筛选。

要求：

（1）筛选条件为：

“销售数量”－>3、“所属部门”－市场1部、“销售金额”-

>1000。

（2）将筛选结果保存在Sheet2中。

注意：

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

（2）复制过程中，将标题项“三月份销售统计表”连同数据一同复制。

（3）数据表必须顶格放置。

7.根据Sheet1的“三月份销售统计表”中的数据，新建一个数据透视图Chart1。

要求：

（1）该图形显示每位经办人的总销售额情况。

（2）x坐标设置为“经办人”。

（3）数据区域设置为“销售金额”。

（4）求和项为“销售金额”。

（5）将对应的数据透视表保存在Sheet3中。

二、解答

步骤1：

操作方法参见“典型试题1”中的步骤1。

步骤2：

选中Sheet1工作表中的G3单元格，单击插入函数按扭，打开“插入函数”对话框，在“选择类别”下拉列表框中选择“查找与引用”，在“选择函数”列表框中选择“VLOOKUP”函数，单击“确定”按钮后，弹出“函数参数”对话框。

输入“Lookup_value”参数为“F3”，“Table_array”参数为“$A$3:

$B$10”，“Col_index_num”参数为“2”，“Range_lookup”参数为“FALSE”，如图6-38所示。

再按“确定”按钮，即可查找到产品型号为“A01”的产品名称（卡特扫描枪）。

此时，公式编辑栏显示“=VLOOKUP（F3,$A$3:

$B$10,2,FALSE）”。

双击G3单元格的填充柄，即可查找到其余产品型号的产品名称。

在H3单元格中，输入公式“=VLOOKUP（F3,$A$3:

$C$10,3,FALSE）”，再按回车键确认，即可查找到产品型号为“A01”的产品单价（368）。

双击H3单元格的填充柄，即可查找到其余产品型号的产品单价。

说明：

VLOOKUP函数的功能和用法，请参见上一节的相关内容。

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

步骤3：

先选中L3:

L44单元格区域，再输入公式“=H3:

H44*I3:

I44”，然后同时

按组合键Shift+Ctrl+Enter，此时，公式编辑栏显示“{=H3:

H44*I3:

I44}”。

步骤4：

选中O3单元格，单击插入函数按扭，打开“插入函数”对话框，在

“选择类别”下拉列表框中选择“数学与三角函数”，在“选择函数”列表框中选择

“SUMIF”函数。

单击“确定”按钮，弹出“函数参数”对话框，输入“Range”参数为“K3：

K44”，“Criteria”参数为“"=市场1部"”，“Sum_range”参数为“L3:

L44”，如图6-39所示。

再按“确定”按钮，即可计算出市场1部的总销售额（35336）。

此时，公式编辑栏显示“=SUMIF（K3:

K44,"=市场1部",L3:

L44）”。

在O4单元格中，输入公式“=SUMIF（K3:

K44,"=市场2部",L3:

L44）”后，按回车键确

认。

在O5单元格中，输入公式“=SUMIF（K3:

K44,"=市场3部",L3:

L44）”后，按回车键确

认。

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

步骤5：

选中P3单元格，单击插入函数按扭，打开“插入函数”对话框，在“选择类别”下拉列表框中选择“统计”，在“选择函数”列表框中选择“RANK”函数。

单击“确定”按钮，弹出“函数参数”对话框，输入“Number”参数为“O3”，“Ref”参数为“$O$3:

$O$5”，“Order”参数为“0”，如图6-40所示。

再按“确定”按钮，即可统计出市场1部的总销售额排名

（1）。

此时，公式编辑栏显示“=RANK（O3,$O$3:

$O$5,0）”。

双击P3单元格的填充柄，可统计出其他部门的总销售额排名。

说明：

RANK函数的功能和用法，请参见上一节的相关内容。

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

步骤6：

选中Sheet1工作表中的E1：

L44单元格区域，单击右键在快捷菜单中选择“复制”命令。

单击Sheet2工作表中的A1单元格，单击右键在快捷菜单中选择“选择性粘贴”命令，打开如图6-41所示的“选择性粘贴”对话框，选中“值和数字格式”单选钮，再单击“确定”按钮。

在Sheet2工作表的空白区域创建筛选条件，如图6-42所示，在J2：

L3单元格区域中输入筛选条件。

单击Sheet2工作表中A1：

H44单元格区域中的任一单元格，选择菜单“数据”→“筛选”→“高级筛选”命令，打开“高级筛选”对话框，“列表区域”文本框中已自动填入数据清单所在的单元格区域。

将光标定位在“条件区域”文本框内，用鼠标拖选前面创建的筛选条件单元格区域J2：

L3，“条件区域”文本框内会自动填入该区域地址（如图6-43所示）。

再单击“确定”按钮。

图6-41“选择性粘贴”对话框

图6-42创建筛选条件

图6-43设置“条件区域”

步骤7：

单击Sheet1工作表中E1：

L44单元格区域中的任一单元格，选择菜单“数

据”→“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导——

3步骤之1”对话框，选中“数据透视图（及数据透视表）”单选钮，再单击“下一步”按钮，打开“数据透视表和数据透视图向导——3步骤之2”对话框，“选定区域”文本框中已自动填入数据清单所在的单元格区域，单击“下一步”按钮，打开“数据透视表和数据透视图向导——3步骤之3”对话框，选中“现有工作表”单选钮，再单击Sheet3工作表中的A1单元格，该单元格地址会自动填入“现有工作表”下方的地址文本框中。

单击“布局”按钮，在弹出的如图6-44所示界面中，把字段“经办人”拖至“行”区域内，把字段“销售金额”拖至“数据”区域内，然后按“确定”按钮返回，再按“完成”按钮。

这时在Sheet3工作表前会插入一张名为Chart1的工作表。

打开该工作表可看到根据Sheet1中“三月份销售统计表”创建的数据透视图（如图

6-45所示），打开Sheet3