excel25

题目

1.更名

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

选择表格区域进行命名：选中→公式→定义名称→更改名称

3. 在“订单明细”工作表中，完成下列任务：

　　① 根据B列中的产品代码，在C列、D列和E列填入相应的产品名称、产品类别和产品单价(对应信息可在“产品信息”工作表中查找)。-----简单vlookup函数

　　② 设置G列单元格格式，折扣为0的单元格显示“-”，折扣大于0的单元格显示为百分比格式，并保留0位小数(如15%)。

选中G列（除第一行）→修改数字格式→自定义输入0%;;"-"

　　③ 在H列中计算每订单的销售金额，公式为“金额=单价×数量×(1-折扣)”，设置E列和H列单元格为货币格式，保留2位小数。---简单

　　4. 在“订单信息”工作表中，完成下列任务：

　　① 根据B列中的客户代码，在E列和F列填入相应的发货地区和发货城市(提示：需首先清除B列中的空格和不可见字符)，对应信息可在“客户信息”工作表中查找。

第一问：关于清除

选中B列（除第一行）→数据→分列→完成

选中B2，复制最后的，CTRL+c→选中B列（除第一行）→开始→查找和筛选→替换→查找内容：粘贴；替换为：不需要输入→全部替换

第二问：vlookup函数

示例：

发货地区：=VLOOKUP(B2,客户信息!$A$2:$F$92,6,0)

发货城市：=VLOOKUP(B2,客户信息!$A$2:$F$92,5,0)【就是第三行不同，6改成5】

　　② 在G列计算每订单的订单金额，该信息可在“订单明细”工作表中查找(注意：一个订单可能包含多个产品)，计算结果设置为货币格式，保留2位小数。-----条件求和sumif

示例：=SUMIF(订单明细!$A$2:$A$907,A2,订单明细!$H$2:$H$907)

第一行：“订单明细”的A列（除第一行）【绝对引用】

第二行：“订单信息”的A2

第三行：“订单明细”的H列（除第一行）【绝对引用】

再设置数字格式：货币，2位数

　　③ 使用条件格式，将每订单订货日期与发货日期间隔大于10天的记录所在单元格填充颜色设置为“红色”，字体颜色设置为“白色,背景1”。

选中当前表的数据范围A2::G342→开始→样式→条件格式→新建规则→使用公式→($D2-$C2)>10→格式：填充红色，字体白色

　　5. 在“产品类别分析”工作表中，完成下列任务：

　　① 在B2:B9单元格区域计算每类产品的销售总额，设置单元格格式为货币格式，保留2位小数;并按照销售额对表格数据降序排序。

第一问：----sumif函数

示例：=SUMIF(订单明细!$D$2:$D$907,A2,订单明细!$H$2:$H$907)

第一行：“订单明细”工作表中B列（除第一行）【绝对引用】

第二行：目前表“产品类别分析”D2

第三行：“订单明细”工作表中H列即金额列（除第一行）【绝对引用】

第二问：修改数字格式：货币格式，保留2位小数

第三问：将鼠标放在销售额列随便一单元格→开始→排序和筛选→降序

　　② 在单元格区域D1:L17中创建复合饼图，并根据样例文件“图表参考效果.png”设置图表标题、绘图区、数据标签的内容及格式。

鼠标放在产品类别下面随便一个，插入→饼图→复合饼图（第3个）

→将图标标题照样例修改

→布局→图例→无

设置数据标签（百分比，类别名称）：布局→数据标签→其他→标签选项→取消勾选：值，显示引导线；勾选：类别名称，百分比；标签位置：数据标签内

发现还有个地方不一样，就是第二个小的饼图样例是分了4份，而图标给了3份，点击不一样的那部分，右键→设置数据系列格式→第二绘图区将3改为4

最后将图放在相应要求的区域。

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

步骤一：在表“订单信息”任选一个单元格→插入数据透视表→选择现有工作表：“地区和城市分析”新工作表A1:C19

步骤二：观察样例城市是放在第二列-----→鼠标放在A1单元格→设计→布局→报表布局→以表格形式

步骤三：发现所做的有汇总，而样例没有-----设计→分类汇总→不显示汇总

步骤四：选中C1，单击右键→数据透视表选项→布局和格式→合并并居中

步骤五：数值列也要进行格式设置--在右下角4个框找到数值，点右边向下的按钮→数字格式→货币，2位小数→自定义名称：订单金额汇总

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

第一问：

在“客户信息”工作表“客户等级”运用查询函数lookup函数

示例：=LOOKUP(SUMIF(订单信息!$B$2:$B$342,客户信息!A2,订单信息!$G$2:$G$342),客户等级!$A$2:$A$11,客户等级!$B$2:$B$11)

第一行：（查找的依据）运用sumif函数

示例：=SUMIF(订单信息!$B$2:$B$342,客户信息!A2,订单信息!$G$2:$G$342)

sumif函数第一行：“订单信息”B列即客户代码（除第一行，绝对引用）

sumif函数第二行：“客户信息”A2即客户代码

sumif函数第三行：“订单信息”G列即订单金额

第二行：“客户等级”A列

第三行：“客户等级”B列

第二问：在“客户信息”选中等级→条件格式→新建规则→公式→=MIDB(G2,1,2)*1<6→填充红，字体白

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

文件→属性→高级属性→自定义→名称：机密。类型：是或否。取值：是→添加→确定

完

-2020.1.24