设计性实验：百货公司销售人员的相关数据分析

6.3设计性实验：百货公司销售人员的相关数据分析1.实验目的掌握EXCEL软件的自动筛选、条件格式和数据透视表、方案管理器等工具方法；理解EXCEL对数据分析和决策支持的作用。2.实验环境计算机；EXCEL软件。3.实验步骤和要求M百货公司是N城市商业区最负盛誉的商场，有女装、男装、儿童、鞋类与箱包、化妆品、家居用品、餐饮等几大部门，每个部门都有一批训练有素的销售人员。为了更好地鼓励销售人员的辛勤工作，公司销售部门主管制定了如下的薪酬支付方法。在公司内部，销售代表进行分级，并按照他们的工作经验和在本公司的工作年限领取薪水。销售代表如果超额完成他们的分配份额任务将会得到一份提成。因为销售人员的工作时间是不同的，所以销售任务分配就基于工作时间来定。例如，如果一个销售代表一小时销售任务是100美元，并且他工作5小时，那么他一天的销售任务就是500美元。对于超过500美元的销售，销售代表将会得到一定提成。表6-1是时工资和制定的每小时的销售任务。表6-1 时工资和制定的销售任务销售员级别代码销售职位时工资（美元）时销售任务（美元）分成率AM经理助理202003%PT1销售助理8.51001%PT2销售伙伴8.751251.50%S1销售合伙人10.51502%S1销售顾问121752.50%每周工作一结束，销售部门主管都要做每周的销售报表，格式如表6-2所示。6-2 周销售报表示例M百货公司销售报表员工编号级别部门周销售额（美元）工作时间（小时）基本工资（美元）提成（美元）薪水总额（美元）00001AM男装部400081607223200003S2化妆品部456.762.5300.4830.4800004S1家居用品部450.98336036周销售报表总结了员工的销售表现（表6-2中的数据只是起到一个示例，报表格式和数据可以适当调整）。这个报表详细说明了员工的编号、级别、所属部门、周销售额、工作时间、基本工资、提成和薪水总额。每周初，把每个销售人员上一周的销售额和工作时间输入周销售工作表，而基本工资、提成和薪水总额需要参考表6-1中的相关参数进行如下计算获得：基本工资=时工资工作时间提成=（周销售额-时销售任务工作时间）分成率薪水总额=基本工资+提成基本工资公式利用销售员工的销售级别来检索适当的小时工资，然后把时工资乘上工作时间，使用VLOOKUP函数把基本工资等级算法编入基本工资。提成 公式由相应的提成率和基于这个提成率的销售额部分来计算。因为提成公式包括查找和计算，你需要把IF和VLOOKUP函数嵌套进去（你可以 “阅读系统在线帮助”来查找IF和VLOOKUP函数以及怎样嵌套的方法）。现有M百货公司上个星期销售人员工作情况的记录，如表6-3所示。表6-3 销售人员上星期的工作情况员工编号级别部门周销售额（美元）周工作时间（美元）00001AM男装部200004000002PT2男装部50326.154000003S2化妆品部2283.812.500004S2家居用品部2254.91500005AM餐饮部3253.753000006PT1鞋类与箱包1253.92000007PT1儿童部75002000008S1儿童部0000009PT1男装部25002000010PT1儿童部7014.92000011PT2化妆品部0000012S2儿童部4365.42500013S1鞋类与箱包22503.352500014S1化妆品部1781.337.500015S1家居用品部7501.252500016PT1女装部12502000017PT1鞋类与箱包7348.92500018PT2鞋类与箱包37510.151000019S1化妆品部0000020AM家居用品部4452.354000021PT2鞋类与箱包0000022PT2化妆品部50002500023S1儿童部55003000024AM女装部6010.16000025PT2儿童部999.952000026S1家居用品部4012.34000027S1餐饮部2504000028S2餐饮部7514.82000029PT1家居用品部0000030AM鞋类与箱包125006000031PT2家居用品部0000032S1男装部0000033S1男装部0000034S2餐饮部984.45500035S2男装部0000036PT1女装部1253.751000037S1餐饮部4253.1532.500038PT1餐饮部1256.81500039S1鞋类与箱包0000040AM儿童部119956000041PT2男装部7733.94000042PT2餐饮部95404000043S2男装部10798.456000044S2女装部0000045S1女装部4106.83500046PT1女装部1729.81000047AM化妆品部88305000048S2女装部0000049S2男装部0000050S1女装部1172.2532.500051S1鞋类与箱包0000052PT2化妆品部1112540根据上述内容的描述和数据，完成下列任务：采用EXCEL软件建立一个符合案例情景描述要求的工作表；销售报表需要统计销售额、基本工资、提成、薪水总额的总计、平均值、最小值与最大值；应用数据透视表分析销售数据，并比较下列情况：不同部门的销售情况；不同部门、不同级别的提成； 不同部门、不同级别的薪水总额；采用EXCEL提供的各种分析工具，回答以下问题：上周哪十个销售代表得到了最高的提成？如果销售人员至少应该完成他的销售任务的50%，哪些销售人员没有完成这个目标？对于每一个部门，哪一个销售人员的销售额最高？平均来说，销售人员在多大程度上完成了销售任务？按照销售职位分类，基本工资、提成和薪水总额分别是多少？哪一个部门的销售人员完成销售任务的情况比其他部门要好？=IF(D2-H2*E2)0,(D2-H2*E2)*I2,0)截止时间：11月20日上实验课时；电子版+纸质版；避免雷同！出师表两汉：诸葛亮先帝创业未半而中道崩殂，今天下三分，益州疲弊，此诚危急存亡之秋也。然侍卫之臣不懈于内，忠志之士忘身于外者，盖追先帝之殊遇，欲报之于陛下也。诚宜开张圣听，以光先帝遗德，恢弘志士之气，不宜妄自菲薄，引喻失义，以塞忠谏之路也。宫中府中，俱为一体；陟罚臧否，不宜异同。若有作奸犯科及为忠善者，宜付有司论其刑赏，以昭陛下平明之理；不宜偏私，使内外异法也。侍中、侍郎郭攸之、费祎、董允等，此皆良实，志虑忠纯，是以先帝简拔以遗陛下：愚以为宫中之事，事无大小，悉以咨之，然后施行，必能裨补阙漏，有所广益。将军向宠，性行淑均，晓畅军事，试用于昔日，先帝称之曰“能”，是以众议举宠为督：愚以为营中之事，悉以咨之，必能使行阵和睦，优劣得所。亲贤臣，远小人，此先汉所以兴隆也；亲小人，远贤臣，此后汉所以倾颓也。先帝在时，每与臣论此事，未尝不叹息痛恨于桓、灵也。侍中、尚书、长史、参军，此悉贞良死节之臣，愿陛下亲之、信之，则汉室之隆，可计日而待也。臣本布衣，躬耕于南阳，苟全性命于乱世，不求闻达于诸侯。先帝不以臣卑鄙，猥自枉屈，三顾臣于草庐之中，咨臣以当世之事，由是感激，遂许先帝以驱驰。后值倾覆，受任于败军之际，奉命于危难之间，尔来二十有一年矣。先帝知臣谨慎，故临崩寄臣以大事也。受命以来，夙夜忧叹，恐托付不效，以伤先帝之明；故五月渡泸，深入不毛。今南方已定，兵甲已足，当奖率三军，北定中原