Excel高级应用医药超市药品销售数据分析.docVIP

实训15 Excel高级应用—医药超市药品销售数据分析 1．实训目的 （1）掌握函数VLOOKUP的使用 （2）学会跨工作表数据的引用 （3）掌握分类汇总和数据筛选的方法 （4）掌握创建“数据透视表”的方法 （5）冻结表头和标题 2．实训内容 （1）利用VLOOKUP函数查找数据。 （2）统计各科室各种药品的销售情况。 （3）利用“数据透视表”分析各科室各药品的销售情况和畅销药品。 （4）利用自动筛选和高级筛查找相关信息。 （5）将标题和表头冻结，便于于浏览。 3．实训任务 任务一 利用vlookup函数查找“单位”、“进价”、“售价”。 操作提示： （1）打开“药物销售数据分析”工作薄，选定“销售记录表”，选定Ｆ2单元格，选择“插入”→“函数”，在类别查找与引用中选定“Vlookup”函数。（如图15-1所示）。 图15-1 　VLOOKUP函数对话框 （2）要根据药品名称查找药品“单位”，所以VLOOKUP函数的第一个参数应该选择药品名称所在的单元格D2。（如图15-2所示）。 图15-2　参数对话框 （3）在函数参数的第二个对话框中选择“药品价格”工作表中的区域$B$2:$E$11。(如图15-3所示)。 （4）在函数参数的第三个参数是决定VLOOKUP函数找到匹配药品名称所在的行后，哪一列的数据被返回，由于“单位”在第二列，所以直接通过键盘输入数字“2”。(如图15-3所示）。 （5）要求药品名称大致匹配，所以在最后一个对话框中输入FALSE,点击确定。（如图15-4所示）。 图15-3 计算机药品单位函数 （6）再利用函数填充功能，纵向拖动填充柄可计算出其它药品的单位（如图15-5所示）。 （7）在G2中输入公式“＝VLOOKUP（D2,药品价格！$B$2:$E$11,3,FALSE）”，计算出第一个药品的进价，利用纵向拖动填充柄可计算出其它药品进价。 图15-4　拖动填充效果 图15-5　最终结果 （8）在H2中输入公式“＝VLOOKUP（D2,药品价格！$B$2:$E$11,4,FALSE）”，计算出第一个药品的进价，利用纵向拖动填充柄可计算出其它药品售价。 （9）在I2单元格中输入公式“＝H2*E2”,计算出第一个药品的销售额，利用纵向拖动填充柄可计算出其 （10）在J2单元格中输入公式“＝（H2-G2）*E2”,计算出第一个药品的毛利润，利用纵向拖动填充柄可计算出其它药品毛利润。（最后结果如图15-6 任务二 统计各医药超市和各个区中各种药品的“销售额”和“毛利润”。 图15- 图15-6　分类汇总对话框 （1）将销售记录表复制一分，命为“销售记录表2” （2）在“销售记录表2”中按“店名” （3）选择“数据”→“分类汇总”命令，打开“分类汇总”对话框，按店名分类，然后对销售额和毛利润进行分类汇总。（如图15-7所示）。 （4）单击“确定”，最后的汇总结果显示在数据的下方。（如图15-8所示）。 说明：分类汇总时，首行要明白它的意思，既按什么分类和对什么进行汇总，并且在操作的时候，一定要对分类的字段进行排序。 图15-7　汇总结果 任务三 用“数据透视表”分析各区各种药品的销售情况 操作提示： （1）单击“销售记录”工作表的任一单元格。 （2）选择“数据”→“数据和透视表”命令，出现如下示对话框。（如图15-9）。 图15-8　数据透视表第一步 （3）选定数据源类型为“ＭICROSOFT数据系表或数据库”，在报表类型中选择“数据透视表”，点下一步，出现如图15-10所示对话框。 图15-9　数据透视表第二步 （4）选定数据源区域“$A$1:$J$61” 图15-10　数据透视表第三步 （5）选定透视表的显示位置为新建工作表，再选“布局”按钮，出现如图15-12所示的对话框。 图15-11　数据透视表布局 图15-12　透视表结果 （6）将“药品名称”字段拖入到行位置，“所在区域”拖入到列位置，“销售额”拖入到数据区，单击“确定”，然后单击“完成”按钮，出现如图15-13所示的结果。 （7）将4-14所示的数据透视表重命名为“药物统计”表。 任务四 利用自动筛选和高级筛查找相关符合条件的记录 操作提示： １．查找毛利润在120以上或30元以下的药品信息。 （1）在“销售记录”表中选定某个单元格。 （2）选择“数据”→“筛选”→“自动筛选”，结果如图15-15所示。 图15-13自动筛选 图15-14自定义筛选 （3）单击“毛利润”列下的三角按钮，选择自定义选项，出现如图15-16所示的对话框，选择相应的比较运算符和数据。 （4）点“确定”按钮，出现如图15-17所示的筛选结果。 2．查找“数量”在80以上和“毛利润”在150以下的药品信息。 （1）在表的空白行处输入如图15-65所示的条件。 （2）选择“数