案例10：淘宝店宝贝销售情况分析精要.ppt

办公软件高级应用 办公软件高级应用 办公软件高级应用 案例10 淘宝店宝贝销售情况分析 知识要点 Excel函数 查找和引用函数 文本函数 逻辑（条件）函数及的嵌套 时间与日期函数 财务函数 排位函数 数据填充 数组公式的使用 分类汇总 数据有效性 数据透视表和数据透视图 任务描述 淘宝网中某商店宝贝销售记录是网络销售最终用电子表格Excel以流水帐的形式记录、保存和处理。通过Excel 2003提供的功能进行销售金额、折扣率等的计算，对销售情况进行统计和分析，如商品结构分析、各省份销售量分析和分析一年中的旺销季节，根据财务分析知道自己的贷款每月还贷金额。商家可根据分析的结果调整商品结构和营销策略，以求得投资的最大效益。同时根据客服电话迅速查询到顾客的具体情况，缩短了响应时间，提高顾客的满意度。 具体要求 任务1：数据填充 使用VLOOKUP函数，对Sheet1中的“宝贝单价”列进行填充。 要求：根据Sheet2中的“宝贝价格表”价格，使用VLOOKUP函数对“宝贝销售记录表”中的“宝贝单价”列根据不同的宝贝标题进行填充。 注意：函数中如果需要用到绝对地址的将使用绝对地址进行计算，其他方式无效。 具体要求 任务2：使用数组公式计算 在Sheet1中使用数组公式计算宝贝的销售金额。 要求：金额=宝贝数量×宝贝单价，并将“宝贝销售记录表”复制到Sheet3 任务3： 使用LEFT函数与分类汇总 在Sheet3中完成以下操作： 将销售金额列的数值数据内容清除，重新使用金额=宝贝数量×宝贝单价（不使用数组公式计算，因数组公式计算后数据清单中记录不能移动）。 使用函数和选择性粘贴将收货地址只显示前三个汉字（省分名），并将“宝贝销售记录表”复制到Sheet4。 使用分类汇总功能按各省份对宝贝数量和销售金额进行汇总。 具体要求 任务4：数据有效性设置 在Sheet2的H2单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。 任务5：使用财务函数PMT、IPMT 根据Sheet2中的贷款情况，使用财务函数对贷款偿还金额进行计算。要求： 计算“按年偿还还款金额（年末）”，将结果填入Sheet2中的I13单元格中。 计算“第9个月贷款利息金额”，并将结果填入到Sheet2中I14单元格中。 具体要求 任务6：IF函数的嵌套和高级筛选 在Sheet4中完成以下操作： 在销售金额列后增加一列“打折后金额”，并根据Sheet2中的“折扣率表”的折扣率计算打折后金额填入相应单元格（保留2位小数）。 对“宝贝销售记录表”进行高级筛选，筛选条件为“收货人姓名”→李惜，“联系手机”筛选结果保存放在启始位置是Sheet4中A208单元格。 具体要求 任务7：创建数据透视图和使用RANK函数排名 根据Sheet4中的“宝贝销售记录表”创建一个数据透视图Chart1。 显示各月份各省的销售金额的汇总。 将对应的数据透视表保存在Sheet5中。 在Sheet5数据透视表中的总计行后增加一行，该行第1列输入“销售排名”并使用RANK函数对各省宝贝的销售金额进行排名，并将结果保存在“销售排名”行中。 知识拓展 知识点1：财务函数的使用 财务函数是财务计算和财务分析的专业工具，有了这些函数，可能很方便地解决复杂的财务运算，在提高财务工作效率的同时，更有效地保障了财务数据计算的准确性。下面我们再介绍几种常用的处理财务中相关的函数。 使用FV函数计算投资未来收益值 FV函数是基于固定利率及等额分期付款方式，返回某项投资的未来值，其完整格式为：FV（rate,nper,pmt,pv,type） 其中：rate表示的是各期利率，例如当利益为8%时，使用8%/4计算一个季度的还款额；nper表示总投资期，即该项投资总的付款期数；pmt表示各期支出金额，在整个投资期内不变；pv表示从该项投资开始计算时已经入账的款项，或一系列未来付款当前值的累积和。如果忽略，pv=0；type数值0或1，指定付款时间是期初还是期末。1=期初，0或忽略=期末。 使用SLN函数计算设备每日、每月、每年的折旧值 SLN函数计算机的是某项资产在一个期间中的线性折旧值，其完整格式为：SLN（cost,salvage,life） 其中：cost表示的是资产原值；salvage表示的是资产在折旧期末的价值，即资产残值；life表示折旧期限，即资产的使用寿命。 知识拓展 知识点2：分类汇总 分类汇总是对数据列表指定的行或列中的数据进行汇总统计，统计的内容可以由用户指定，通过折叠或展开行、列数据和汇总结果。从汇总和明细两种角度显示数据，可以快捷地创建各种汇总报告。 在创建分类汇总之前，首先要保证要进行分类汇总的