excel部分实训教案-1超市销售明细单.doc

办公软件高级应用项目教学实训教案班 级姓名张学慧实训日期课程名称办公软件高级应用office 2010实训地点5机房项目名称Excel部分综合案例一：超市销售明细单 知识要点常用函数创建名称（绝对引用）、附带可以说明单元格的引用使用名称创建自定义下拉列表条件格式IF函数及其嵌套使用HLOOKUP函数数组公式的使用SUMIF函数RANK函数数据库函数-DCOUNT函数项目描述晓泽是一名刚毕业的学生，顺利通过应聘成为一家小型超市的主管助理。上班第一天，主管交给晓泽一份本月休闲食品销售明细单，要求他迅速完成相关统计工作，以便准备第二天的月度销售会议。清单上已经填写了食品的名称、规格、进价、库存量和销售量等信息，同时标注了必要的经营数据，见素材文件超市休闲食品销售.xls 。项目解析晓泽需要完成的是以下一些工作：1将明细表中的食品按照所分类别进行归类：蜜饯类：乌梅、橄榄、芒果干、山楂、苹果干；饼干类：夹心饼干、蛋卷饼干、曲奇饼干、威化饼干、压缩饼干；肉脯类：猪肉脯、牛肉脯、羊肉脯、烤鱼片；糖果类：软糖、水果糖、椰子糖、奶糖、瑞士糖。2如果食品库存不大于50，将其库存量用红底标出。3按下列标准填写库存情况列：商品脱销：库存等于0；库存不足：库存小于等于50；商品滞销：库存大于400；库存正常：库存大于50，小于等于400。4自动填写食品的利润率；蜜饯类：15%；饼干类：10%；肉脯类：20%；糖果类：5%。5计算明细表中各种食品的销售总额。6统计各类食品的销售情况，并根据销售总额进行排序。7统计糖果类销售量大于200包的食品种类。解决思路1根据“食品类别”表，创建一个名称“食品类别”。2使用“食品类别”名称，在“类别”列中创建自定义下拉列表，并对各个商品进行类别填充。3在“库存量”列中，使用条件格式标出库存量低于50（包含50），将其单元格底色设置为红色。4使用IF函数，对“库存情况”列进行自动填充，内容根据以下条件进行填充： 1） 如果库存数量等于0，表示商品库存量严重不足，填充“商品脱销”； 2） 如果库存数量小于50（包含50），表示商品库存量不足，填充“库存不足”； 3） 如果库存数量大于400（不包含400），表示商品库存量积累过多，填充“商品滞销”； 4） 如果库存量大于50，小于等于400，填充“库存正常”；5使用HLOOKUP函数，填充利润率，并将结果填充到“利润率”列中。6根据进货单价、利润率和销售数量，使用数组公式计算销售总额。计算公式为：（进货单价+进货单间*利润率）*销售数量。7统计每类食品的销售总额，并将结果填入到统计表中的相应单元格中。8使用RANK函数，对各类食品进行销售排名。9使用DCOUNT函数，对“糖果类”食品，“销售数量”200包的食品种类数进行统计操作步骤1 公式自定义名称；名称：食品类别；引用位置：2 数据有效性设置允许：序列，来源：=食品类别；3 条件格式：只为包含以下内容的单元格设置格式，单元格值小于或等于50，格式填充背景色：红色4 在单元格E2输入公式：=if(d2=0,”商品脱销”,if(d2400,”商品滞销”,”库存正常”)5 在单元格G2输入公式：=hlookup(b2,$o$3:$s$4,2,false)6 选定I2:I20，输入公式：=(f2:f20+f2:f20*g2:g20)*h2:h20，按shift+ctrl+enter7 在单元格P8中输入：=sumif($b$2:$b$20,o8,$i$2:$i$20)8 在单元格Q8输入公式：=rank.eq(p8,$p$8:$p$11,0)9 选择一个