excel31

题目

运营部经理老钱需要对公司本年度的购销数据进行统计，按照下列要求完成相关数据的整理、计算和分析工作：

（1）更名

（2）在工作表“年度销售汇总”右侧插入一个名为“品名”的工作表，按照下列要求设置：----下标插入表，而不是新建列

①将以逗号“，”分隔的文本文件“品名表.txt”中的数据自A1单元格开始导入到工作表“品名”中。---在“品名”表的A1中→数据→自文本→选择txt→向导第一步：文件原始格式windows(ANSI)t→向导第二步：取消tab键，选择逗号t→确定

② 参照下图示例将“商品名称”分为两列显示，下划线左边为“品牌”、右边为具体的“商品名称”。

选中B列（除第一行）→数据→分列→向导第二步：取消tab键，选择其他，输入下划线—（注意：英文状态下输入的，按shift＋一杠）→在B1输入品牌；C1输入商品名称→⭐选中BC2列，鼠标放在中间双击，就能和图例一样宽度了

③通过设置“条件格式”查找并删除工作表中“商品名称”重复的记录，对于重复信息只保留最前面的一个。

选中C列（除第一行）→开始→样式→条件格式→新建规则→使用公式→=COUNTIF($C$2:C2,C2)>1；格式：字体颜色（例如红）

随便点一个单元格→开始→排序和筛选→自定义排序→勾选“数据包含标题”；关键字：商品名称；排序依据：字体颜色；次序：自动，在顶端

再将红色的整列全部删除

④按“商品代码”升序对商品信息进行排列。

将鼠标放在这列的随便1个单元格→→排序和筛选→自定义排序→勾选“数据包含标题”；关键字：商品代码；排序依据：数值；次序：升序

⑤删除与源数据“品名表.txt”的链接。

随便点一个→数据→连接→选择品名表→删除

（3）按照下列要求对工作表“年度销售汇总”中的数据设置：

①将A1单元格中的标题内容在表格数据上方“跨列居中”，并应用“标题2”单元格样式。----合并；水平：跨列居中（对齐方式）；样式：标题2

②令“序号”列中的序号以“0001”式的格式显示，但仍需保持可参与计算的数值格式。

选择数字列（A4起）→数字格式自定义，0000

③自工作表“品名”中获取与商品代码相对应的“品牌”及“商品名称”依次填入C列和D列。

在C4单元格运用vlookup函数，

示例：=VLOOKUP(B4,品名!$A$2:$C$151,2,0)

第一行：当行的代码B4

第二行：表“品名”选中A-C列（除第一行）【绝对引用】

第三行：2

第四行：0

D4也是如此操作，直接复制，唯一不同的是第三行列数是3

示例：=VLOOKUP(B4,品名!$A$2:$C$151,3,0)

④商品代码的前两位字母代表了商品的类别。按照下列对应关系，在E列中填入与商品代码相适应的商品类别。

在表“年销售”E4单元格运用LEFT函数-----只得出商品代码前2位字母

第一行：当行的代码B4

第二行：2（因为要的是2个字符）

再在表"品名"空白列，按图例码字

在LEFT函数前加上vlookup函数

示例：=VLOOKUP(LEFT(B4,2),品名!$D$1:$E$8,2,0)

第一行：LEFT函数

第二行：码字的2列【绝对引用】

第三行：2

第四行：0

⑤在J列中填入销售单价，每种商品的销售单价可从工作簿“价格表.xlsx”中的“单价”表中获取。

在表“年销售”J4单元格运用vlookup函数

示例：=VLOOKUP(B4,[价格表.xlsx]单价!$A$2:$B$152,2,0)

第一行：当行商品代码B4

第二行：“价格”excel中的单价表中AB2列（除第一行）

⑥根据公式“销售额=销量×销售单价”计算出每种商品的销售额并填入K列中。---简单乘法

⑦根据公式“进货成本=销量×进价”计算出每种商品的进货成本填入L列中。其中进价可从工作簿“价格表.xlsx”中的“进价”表中获取。

在表“年销售”L4单元格运用vlookup函数

示例：=VLOOKUP(B4,[价格表.xlsx]进价!$A$2:$B$151,2,0)

第一行：当行商品代码B4

第二行：“价格”excel中的进价表中AB2列（除第一行）

注意还要乘以销量！！！！

示例=VLOOKUP(B4,[价格表.xlsx]进价!$A$2:$B$151,2,0)*I4

⑧将单价、销售额和进货成本3列数据设为保留两位小数、使用千位分隔的数值格式；为整个数据区域套用一个表格格式，并适当加大行高、调整各列列宽以使数据显示完整。

选中价、销售额和进货成本3列即JKL列（除第一行）→设置数字格式：数值，小数位2，千位分隔

随点一个→开始→套用表格格式

选中全部→开始→格式→自动调整列宽

还有行高要加大一点（全选中→靠左侧右键→行高→修改数值）

⑨锁定工作表的1-3行和A-D列，使之始终可见。

在E4单元格→视图→冻结拆分窗格

（4）参照考生文件夹下的“数据透视示例.jpg”，以“年度销售汇总”为数据源，自新工作表“透视分析”的A3单元格开始创建数据透视表，要求如下：

步骤一：在表“年销售”随点一个插入数据透视表

步骤二：对照样例，发现还有毛利，需要手动添加。 ----选项→域、项目和集→计算字段

步骤三：观察到B4和E4是具体日期，而样例则显示的是月份----选中B4→右键创建组→步长选择月

步骤四：观察发现样例的月份只有1-3月，因此选中B3按钮→取消全选→只勾选1-3月

步骤五：设计→布局→分类汇总→（根据样例）在组的底部

步骤六：设计→布局→报表布局→以表格形式

步骤七：B3右键→数据透视表选项→勾选“合并并居中排列”

步骤八：

（一）选中C5右键→值字段设置→数字格式：自定义0.00，

                                                       →自定义名称：销售额（千元）

（二)D5也是如此设置→值字段设置→数字格式：自定义0.00，

                                                               →自定义名称：进货成本（千元）

（三)E5也是如此设置→值字段设置→数字格式：自定义0.00，

                                                               →自定义名称：毛利（千元）

步骤九：观察图例，销售额/进货成本/毛利（千元），“销售额/进货成本/毛利”与“（千元）”是做2行显示的

-----将鼠标放在“销售额/进货成本/毛利”与“（千元）”中间，按alt+enter,再手动拉长该行的行高

步骤十（题目第三小问）：按B5按钮→其他排序选项→升序：分部

步骤十一：还需要手动按照样例移动分部的名称。当箭头变为向四周分散的箭头时，再拖动

步骤十二：按照样例修改样式。--设计→中部深浅11（2行第4个）

步骤十三：将D3修改为：销售情况

步骤十四：将透视表命名“透视分析”

①透视表结构、以及各行数据的列标题应与示例完全相同，不得多列或少列。

②透视结果应该可以方便地筛选不同的商品类别的销售情况。

③B列的分部名称按汉字的字母顺序升序排列。

④通过设置各列数据的数字格式，使得结果以千元为单位显示，且保留两位小数，但不得改变各数据的原始值。

⑤适当改变透视表样式。

完

-2020.1.23