excel典型试题操作步骤.docx
《excel典型试题操作步骤.docx》由会员分享,可在线阅读,更多相关《excel典型试题操作步骤.docx(33页珍藏版)》请在冰豆网上搜索。
excel典型试题操作步骤
6.2典型试题
典型试题1
打开素材库中的“典型试题6-1.xls”文件,按下面的操作要求进行操作,并把操作
结果存盘。
注意:
在做题时,不得将数据表进行更改。
一、操作要求
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
打开素材库中的“典型试题6-2.xls”文件,按下面的操作要求进行操作,并把操作
结果存盘。
注意:
在做题时,不得将数据表进行更改。
一、操作要求
1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。
当录入位数错误时,提示
错误原因,样式为“警告”,错误信息为“只能录入5位数字或文本”。
2.使用时间函数,对Sheet1中用户的年龄进行计算。
要求:
假设当前时间是“2008-11-15”,结合用户的出生年月,计算用户的年龄,
并将其计算结果保存在“年龄”列当中。
计算方法为两个时间年份之差。
3.使用REPLACE函数,对Sheet1中用户的进行升级。
要求:
对“原”列中的进行升级。
升级方法是在区号(0571)
后面加上“8”,并将其计算结果保存在“升级”列的相应单元格中。
例如:
“”升级后为“8”
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
打开素材库中的“典型试题6-3.xls”文件,按下面的操作要求进行操作,并把操作
结果存盘。
注意:
在做题时,不得将数据表进行更改。
一、操作要求
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工作表有如图6-46所示的相应的数据透