产品类别二级分类_全国计算机二级Office Excel 第三套

计算机二级Excel第三套

阿文是某食品贸易公司销售部助理，现需要对2015年的销售数据进行分析，根据以下要求，帮助她完成此项工作。

01.

在考生文件夹下，将“Excel_素材.xlsx”文件另存为“Excel.xlsx”(“.xlsx”为文件扩展名)，后续操作均基于此文件，否则不得分。

打开考生文件夹下的“Excel素材.xlsx"文件，单击【文件】选项卡下的“另存为”按钮

在弹出的“另存为”对话框中将文件保存到考生文件夹下，并命名为“Excel.xlsx”。

02.

命名“产品信息”工作表的单元格区域A1:D78名称为“产品信息”；命名“客户信息”工作表的单元格区域A1:G92名称为“客户信息”。

单击选择“产品信息”工作表，在“产品信息”工作表中选中单元格区域A1:D78，在左上角的“名称框”中输入“产品信息”，输入完成后，使用键盘上的“Enter”键完成输入。

单击选择“客户信息”工作表，在“客户信息”工作表中选中单元格区域A1:G92，在左上角的“名称框”中输入“客户信息”，输入完成后，使用键盘上的“Enter”键完成输入。

03.

在“订单明细”工作表中，完成下列任务：① 根据B列中的产品代码，在C列、D列和E列填入相应的产品名称、产品类别和产品单价(对应信息可在“产品信息”工作表中查找)。② 设置G列单元格格式，折扣为0的单元格显示“-”，折扣大于0的单元格显示为百分比格式，并保留0位小数(如15%)。③ 在H列中计算每个订单的销售金额，公式为“金额=单价×数量×(1-折扣)”，设置E列和H列单元格为货币格式，保留2位小数。

在“订单明细”工作表，选中C2单元格，输入公式

“=VLOOKUP(B2,产品信息,2,0)”，输入完成后，使用键盘上的“Enter”键确认输入，双击单元格右下角的填充句柄。

选中D2单元格，输入公式“=VLOOKUP(B2,产品信息,3,0)”，输入完成后，使用键盘上的“Enter”键确认输入，双击单元格右下角的填充句柄。

选中E2单元格，输入公式“=VLOOKUP(B2,产品信息,4,0)”，输入完成后，使用键盘上的 “Enter”键确认输入，双击单元格右下角的填充句柄。

选中G2:G907单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”

弹出“设置单元格格式”对话框，在“数字”选项卡中的“分类”列表框中选中“自定义”选项，在右侧的“类型”文本框中删除默认内容“G/通用格式”，然后输入“0%;;"-"”，最后单击“确定”按钮，关闭对话框。

选中H2单元格，输入公式“=E2*F2*(1-G2)”，输入完成后，使用键盘上的 “Enter”键确认输入，双击单元格右下角的填充句柄。

选中E列和H列单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”

弹出“设置单元格格式”对话框，在“数字”选项卡下的“分类”列表框中，单击选中“货币”类型，将右侧的“小数位数”设置为“2”，单击“确定”按钮，关闭对话框。

04.

在“订单信息”工作表中，完成下列任务：① 根据B列中的客户代码，在E列和F列填入相应的发货地区和发货城市(提示：需首先清除B列中的空格和不可见字符)，对应信息可在“客户信息”工作表中查找。② 在G列计算每个 订单的订单金额，该信息可在“订单明细”工作表中查找(注意：一个订单可能包含多个产品)，计算结果设置为货币格式，保留2位小数。③ 使用条件格式，将每订单订货日期与发货日期间隔大于10天的记录所在单元格填充颜色设置为“红色”，字体颜色设置为“白色,背景1”。

单击选择“订单信息”工作表，选中H2单元格，输入公式

“=Trim(Clean(B2))”，输入完成后，使用键盘上的“Enter”键确认输入，双击单元格右下角的填充句柄。

选中H2:H342单元格区域，使用键盘上的“Ctrl+C”复制

单击选中B2单元格，右击，在弹出的快捷菜单中选择“粘贴选项/值”(图标为“123”的选项)，

右击H列列标，在弹出的快捷菜单中选择“删除”命令。

选中E2单元格，输入公式“=VLOOKUP(B2,客户信息!$A$2:$F$92,6,0)”，输入完成后，使用键盘上的 “Enter”键确认输入，双击单元格右下角的填充句柄。

选中F2单元格，输入公式“=VLOOKUP(B2,客户信息!$A$2:$F$92,5,0)”，输入完成后，使用键盘上的 “Enter”键确认输入，双击单元格右下角的填充句柄。

选中G2单元格，输入公式“=SUMIF(订单明细!$A$2:$A$907,A2,订单明细!$H$2:$H$907)”，输入完成后，使用键盘上的 “Enter”键确认输入，双击单元格右下角的填充句柄。

然后选中G2:G342单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”

弹出“设置单元格格式”对话框，在“数字”选项卡下的“分类”列表框中，单击选中“货币”类型，将右侧的“小数位数”设置为“2”，设置完成后单击“确定”按钮，关闭对话框。

在“订单信息”工作表中选中“A2:G342”数据区域，单击开始→样式组→“条件格式”，在下拉列表中选择“新建规则”。

弹出“新建格式规则”对话框，在“选择规则类型”列表框中选中“使用公式确定要设置格式的单元格”，在下方的“为符合此公式的值设置格式”文本框中输入公式“=$D2-$C2>10”。单击下方的“格式”按钮，

弹出“设置单元格格式”对话框，切换到“填充”选项卡，设置背景颜色为“标准色/红色”

再切换到“字体”选项卡，设置字体颜色为“主题颜色/白色，背景1”，单击“确定”按钮，关闭所有对话框。

05.

在“产品类别分析”工作表中，完成下列任务：① 在B2:B9单元格区域计算每类产品的销售总额，设置单元格格式为货币格式，保留2位小数；并按照销售额对表格数据降序排序。② 在单元格区域D1:L17中创建复合饼图，并根据样例文件“图表参考效果.png”设置图表标题、绘图区、数据标签的内容及格式。

单击选择“成品类别分析”工作表，选中B2单元格，输入公式“=SUMIF(订单明细!$D$2:$D$907,A2,订单明细!$H$2:$H$907)”，输入完成后，使用键盘上的 “Enter”键确认输入，拖动单元格右下角的填充句柄，填充到B9单元格。

然后选中B2:B9单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”

弹出“设置单元格格式”对话框，在“数字”选项卡下的“分类”列表框中，单击选中“货币”类型，将右侧的“小数位数”设置为“2”，设置完成后单击“确定”按钮，关闭对话框。

选中工作表数据区域“A1:B9”，单击开始→编辑组→“排序和筛选”，在下拉列表中选择“自定义排序”

弹出“排序”对话框，在对话框中将主要关键字选择为“销售额”，将“次序”设置为“降序”，最后单击“确定”按钮，关闭“排序”对话框。

选中工作表数据区域“A1:B9”，单击【插入】选项卡下【图表】功能组中的“饼图”按钮，在下拉列表中选择“二维饼图/复合饼图”，在工作表中插入一个复合饼图对象。

选中该饼图对象，单击图表工具/布局→标签组→“图例”，在下拉列表中选择“无”。

选中图表对象上方的标题文本框，参考考生文件夹下的“图表参考效果.png”文件，输入图表标题“各类产品所占比例”。

选中图表对象，单击【图表工具/布局】选项卡下【当前所选内容】功能组中最上端的下拉按钮，在下拉列表框中选择“系列：销售额”

然后单击下方的“设置所选内容格式”，弹出“设置数据系列格式”对话框，在右侧的“第二绘图区包含最后一个”对应的文本框中输入“4”。

单击上方的“系列分割依据”下拉按钮，在下拉列表中选择“百分比值”，其他选项采用默认设置，单击“关闭”按钮。

选中图表对象，单击图表工具/布局→标签组→“数据标签”，在下拉列表中选择“其他数据标签选项”。

弹出“设置数据标签格式”对话框，在右侧的“标签选项”中仅勾选“类别名称”和“百分比”；在“标签位置”中选择“数据标签内”。最后单击“关闭”按钮，关闭对话框。

适当调整图表对象的大小及位置，将其放置于工作表的D1:L17单元格区域中。

06.

在所有工作表的最右侧创建一个名为“地区和城市分析”的新工作表，并在该工作表A1:C19单元格区域创建数据透视表，以便按照地区和城市汇总订单金额。数据透视表设置需与样例文件“透视表参考效果.png”保持一致。

单击工作表“产品类别分析”右侧的“插入工作表”按钮，新建一个空白工作表Sheet1，双击工作表名“Sheet1”，修改工作表名为“地区和城市分析”。

选中“订单信息”工作表中的“A1：G342”数据区域，单击【插入】选项卡下【表格】功能组中的“数据透视表”按钮，

弹出“创建数据透视表”对话框，在“选择放置数据透视表的位置”选项组中选择“现有工作表”，在“位置”文本框中单击右侧的“压缩对话框”按钮，然后单击选中“地区和城市分析”工作表的“A1”单元格，单击“确定”按钮。

在“地区和城市分析”工作表中，参考考生文件夹下的“透视表参考效果.png”文件，将右侧“数据透视表字段列表”窗格中的“发货地区”和“发货城市”两个字段分别拖动到下方的“行标签”区域中，将“订单金额”字段拖动到“数值”区域中。

选中A1:B25数据区域，单击【数据透视表工具/选项】选项卡下的【数据透视表】功能组中的“选项”按钮，在下拉列表中选择“选项”命令。

弹出“数据透视表选项”对话框，在“布局和格式”选项卡中，勾选“合并且居中排列带标签的单元格”，

切换到“显示”选项卡，取消勾选“显示展开/折叠按钮”，单击“确定”按钮。

单击【数据透视表工具/设计】选项卡下【布局】功能组中的“分类汇总”按钮，在下拉列表中选择“不显示分类汇总”命令。

单击右侧的“报表布局”按钮，在下拉列表中选择“以表格形式显示”。

双击C1单元格，在“自定义名称”框中输入文本“订单金额汇总”，单击“确定”按钮；

选中C2:C19数据区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令。

弹出“设置单元格格式”对话框，在“数字”选项卡下的“分类”列表框中，单击选中“货币”类型，将右侧的“小数位数”设置为“2”，设置完成后单击“确定”按钮，关闭对话框。

07.

在“客户信息”工作表中，根据每个客户的销售总额计算其所对应的客户等级(不要改变当前数据的排序)，等级评定标准可参考“客户等级”工作表；使用条件格式，将客户等级为1级～5级的记录所在单元格填充颜色设置为“红色”，字体颜色设置为“白色,背景1”。

选中“客户信息”工作表，在G2单元格中输入公式

“=VLOOKUP(SUMIF(订单信息!$B$2:$B$342,A2,订单信息!$G$2:$G$342),客户等级!$A$2:$B$11,2,1)”，输入完成后，使用键盘上的 Enter键确认输入，拖动单元格右下角的填充句柄，填充到G92单元格。

选中A2:G92数据区域，单击【开始】选项卡下【样式】功能组中的“条件格式”按钮，在下拉列表中选择“新建规则”命令

弹出“新建格式规则”对话框，在“选择规则类型”列表框中选中“使用公式确定要设置格式的单元格”，在下方的“为符合此公式的值设置格式”文本框中输入公式“=MID($G2,1,LEN($G2)-1) <6”。单击下方的“格式”按钮，弹出“设置单元格格式”对话框，切换到“填充”选项卡，设置背景颜色为“标准色/红色”，

再切换到“字体”选项卡，设置字体颜色为“主题颜色/白色，背景1”，单击“确定”按钮，关闭所有对话框。

08.

为文档添加自定义属性，属性名称为“机密”，类型为“是或否”，取值为“是”。

单击【文件】选项卡，打开Office后台视图，单击最右侧页面中的“属性”，在下拉列表中选择“高级属性”。

弹出“Excel.xlsx属性”对话框，切换到“自定义”选项卡，在“名称”行中输入“机密”；在“类型”行中选择“是或否”；在“取值”行中选择“是”，最后单击“添加”按钮，单击“确定”按钮关闭对话框。

最后 Ctrl+S 保存，关闭工作簿。

计算机二级Excel第一套

计算机二级Excel第二套