管理信息系统实验教程(北京师范大学出版社)第6章

实验概述：

【实验任务或内容】

1.掌握Excel软件的自动筛选、条件格式和数据透视图、方案管理器等工具方法；

2.理解Excel对数据分析和决策支持的作用。

【实验环境】（使用的软件）

Microsoft Office Excel 2003

实验内容：

M百货公司是N城市商业区最负盛名的商场，有几大部门。为了更好地鼓励销售人员的辛勤工作，公司销售部门主管制订了如下的薪酬支付方法。

在公司内部，销售代表进行分级，并按照他们的工作经验和在本公司的年限领取薪水。下表是时工资和指定的每小时的销售任务。

每周工作一结束，销售部门主管都要做每周的销售报表。现有M百货公司上周的销售人员工作情况的记录，如图（以下截图只是一部分）

根据以上内容，完成下列任务：

（1）用Excel建立一个符合案例情景描述要求的工作表：

（2）销售报表需要统计销售额、基本工资、提成、薪水总额的总计、平均值、最小值和最大值；

（3）应用数据透视表，比较下列情况：

不同部门的销售情况；

不同部门、不同级别的提成；

不同部门、不同级别的薪水总额。

（4）采用Excel工具，回答下列问题：

上周哪十个销售代表的到了最高的提成？

如果销售人员至少应该完成他的销售任务的50%，哪些销售人员没有

完成这个目标？

对于每一个部门，哪个销售人员的销售额最高？

平均来说，销售人员在多达程度上完成了销售任务？

按照销售职位分类，基本工资、提成和薪水分别是多少？

哪一个部门的销售人员完成销售任务的情况比其他部门好？

【实验过程】（方案设计、实验步骤、记录、数据、分析）

1、打开Microsoft Office Excel 2003，新建工作表。

2、将提供的两个表格分别复制到该工作表sheet1和sheet2中，并对其命名“时工资”和“工作情况”。

3、在sheet3中建立题中所需的工作表，如图所示。其中，

基本工资=时工资×工作时间，即

F3=VLOOKUP(B3,时工资!$A$3:$E$7,3)*Sheet3!E3

提成=（周销售额-时销售任务×工作时间）×分成率，即

G3= (D3-E3*VLOOKUP(B3,时工资!$A$3:$E$7,4))*VLOOKUP(Sheet3!B3,时工

资!$A$3:$E$7,5)

薪水总额=基本工资+提成，即H3=F3+G3

薪水总额的平均值=总薪水总额÷人数

最大值和最小值则使用max（）和min（）函数求得。

注：在此题中未考虑提成不能为负的情况。

4、单击A1单元格，单击“数据”—“数据透视表和数据透视图”。通过“数据透视表和数据透视图向导”，得到如图界面。

按照不同部门的销售情况统计，如图所示。

按照不同部门、不同级别统计提成，如图所示。

按照不同部门、不同级别统计薪水总额，如图所示。

5、题目要求找出得到最高提成的十个销售代表。在“数据”中点击“筛选”，点击“提成”旁的下拉按钮，选择“前十个”，显示“最大前10项”，得到如图所示的结论。

6、题目要求找出没有完成销售任务50%的销售人员，利用公式“销售任务=时销售任务×工作时间”先算出销售任务，即I3=E3*VLOOKUP(B3,时工资!$A$3:$E$7,4)，再算出超过销售任务的50%的部分，即J3=D3-0.5*I3，最后利用“筛选”，选出<0的部分，如图所示。

7、题目要求对于每一个部门销售人员的销售额最高，先将“部门”进行“升序排列”，然后利用“分类汇总”，“分类字段”为“部门”，汇总方式为“最大值”，“选定汇总项”为“周销售额”和“员工编号”，得到如图所示的表格。

8、题中要求求出销售人员在多大程度上完成了销售任务，可以对“销售任务”和“周销售额”分别求平均数，然后做除法。如图所示

9、题目要求按照销售职位分类，并算出基本工资、提成和薪水，可以用“分类汇总”方式解决。销售职位对应“级别”，即可以按照级别分类，先对“级别”“升序排列”，然后进行分类汇总。如图所示

10、题目要求得出哪一个部门的销售人员完成销售任务的情况最好，可以运用“数据透视表”。将部门的“周销售额”统计出来，并且将“求和项:周销售额”改为“求平均值”，得出表格。

可以明显看出男装部的完成情况最好。

【结论】（结果）

1、上周编号为1、

2、7、10、1

3、15、18、28、42、52为得到了最高提成的十个销售代表。

2、上周编号为14、25、27、50没有完成目标。

3、每个部门的最大销售额如下表格：

员工编号部门最大周销售额

42 餐饮部9540

40 儿童部11995

52 化妆品部11125

31 家居用品部7501.25

49 男装部50326.15

50 女装部6010.1

51 鞋类和箱包37510.15

4、平均来说，销售人员超额完成任务，超额66%。

5、按销售职位分类得：

级别基本工资提成薪水总额

AM 6800 115.2 6915

PT1 1360 257 1617

PT2 1881 2194 4075

S1 3123 -48 3075

S2 1650 82 1731 6、男装部的销售人员完成情况最好。

指导教师评语及成绩：

评语：

成绩：指导教师签名：

批阅日期：

附件：

实验报告说明

1．实验项目名称：要用最简练的语言反映实验的内容。要求与实验指导书中相一致。2．实验类型：一般需说明是验证型实验还是设计型实验，是创新型实验还是综合型实验。3．实验目的与要求：目的要明确，要抓住重点，符合实验指导书中的要求。

4．实验原理：简要说明本实验项目所涉及的理论知识。

5．实验环境：实验用的软硬件环境（配置）。

6．实验方案设计（思路、步骤和方法等）：这是实验报告极其重要的内容。概括整个实验

过程。

对于操作型实验，要写明依据何种原理、操作方法进行实验，要写明需要经过哪几个步骤来实现其操作。（如：决策问题背景、问题定义及解决方法思路）

对于设计型和综合型实验，在上述内容基础上还应该画出流程图、设计思路和设计方法，再配以相应的文字说明。

对于创新型实验，还应注明其创新点、特色。

7．实验过程（实验中涉及的记录、数据、分析）：写明具体上述实验方案的具体实施，包括实验过程中的记录、数据和相应的分析。（如：演示并介绍数据区设计内容、演示并介绍图表区设计内容等）

8．结论（结果）：即根据实验过程中所见到的现象和测得的数据，做出结论。（如：分析决策模型中各参数的变化对决策的影响；模型的适用条件、在复杂条件下决策分析中如何进行扩展）

9．小结：对本次实验的心得体会、思考和建议。（如：在这次实验中，我进行了……。我感觉到……。我认为……比较有用，……以前不会，现在……，……以前没有注意到，现在……。我还感觉……还有待……。）

10．指导教师评语及成绩：指导教师依据学生的实际报告内容，用简练语言给出本次实验报

告的评价和价值。

注意：

实验报告将记入实验成绩；