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

Excel高级应用医药超市药品销售数据分析实训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所示)。

统计各医药超市和各个区中各种药品的“销售额”和“毛利润”。

操作提示:
(1)将销售记录表复制
一分，命为“销售记录表2”。

(2)在“销售记录表2”
中按“店名”排序。

(3)选择“数据”?“分
类汇总”命令，打开“分类汇
总”对话框，按店名分类，然
后对销售额和毛利润进行分
类汇总。

(如图15-7所示)。

(4)单击“确定”，最后
的汇总结果显示在数据的下
方。

(如图15-8所示)。

图15-6 分类汇总对话框说明:分类汇总时，首行
要明白它的意思，既按什么分
类和对什么进行汇总，并且在操作的时候，一定要对分类的字段进行排序。

图15-7 汇总结果
用“数据透视表”分析各区各种药品的销售情况
操作提示:
(1)单击“销售记录”工作表的任一单元格。

(2)选择“数据”?“数据和透视表”命令，出现如下示对话框。

(如图15-9)。

图15-8 数据透视表第一步
(3)选定数据源类型为“,ICROSOFT数据系表或数据库”，在报表类型中选择“数
据透视表”，点下一步，出现如图15-10所示对话框。

图15-9 数据透视表第二步
(4)选定数据源区域“$A$1:$J$61”，点下一步出现如图15-11所示对话框。

图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)选择“数据”?“筛选”?“高级筛选”命令，出现如图15-18所示的对话框，指定列表区域和条件区域。

图15-15 自定义筛选结果
图15-16 高级筛选条件
(3)单击“确定”出现如图15-19所示的结果。

将标题和表头冻结，方便浏览信息。

操作提示:
(1)在“销售记录”表中选中A2单元格，该单元格的左上角将成为冻结点。

(2)选“窗口”?“冻结窗口”命令，出现一条黑色细线为冻结线。

(3)拖动垂直滚动条，不管怎样拖动，标题始终在上方(如图15-68所示)。

图15-17 高级筛选图图15-18 高级筛选结果
图15-19 冻结表头和标题
4.小结:
通过本项目主要是掌握VLOOKUP函数的功能:在数据区域的第,列中查找指定的值，并返回数据区域当前行中指定列处的值;能够对指定工作中的数据进行分类汇
总计算，但要注意，分类汇总前要先对分类字段进行排序;数据透视表是一种交互式的工作表，用于对现有数据进行汇总和分析，根据不同需要，依据不同的关系来提取和组织数据;利用筛选可以轻松的显示工作表中满足条件的记录，高级筛选能提供多条件的、复杂的条件查询;当记录太多进，可以将表头和标题冻结，方便浏览信息。