保险市场调查与分析实训三任务一2.3.1任务一运用Excel整理市场调查问卷数据

1、实训三：运用Excel整理和分析市场调查问卷数据实训I目的：主要训练学生运用 Excel软件，对市场调查问卷数据进行整理汇总， 在此基础上，运用Excel的制图制表以及数据分析功能实现问卷数据的分析。实 训任务主要包括市场调查问卷整理和调查问卷数据分析两项。实训任务一：问卷数据整理和分析一（单选题和数值填空题）实训要求：将下列调查数据录入到 Excel中，然后用Excel按照规定要求进行数 据分析，输出分析结果。具体任务：Pelican商店最近举办一场商场促销活动，公司高层要求其市场研究 部对次此促销的市场反映作一项调查。市场研究部在促销当天对在Pelican商店消费的全部100位顾客进行调查

2、，调查的结果如下。促销当天对100位顾客的调查数据资料顾客编号支付方式数量折扣总额销售额性别婚姻状况年龄1Discover1039.5MaleMarried322Propriet125.6102.4FemaleMarried363Propriet1022.5FemaleMarried324Propriet5121.1100.4FemaleMarried285Masterca2054FemaleMarried346Masterca1044.5FemaleMarried447Propriet219.578FemaleMarried308Visa1022.5FemaleMarried409Propr

3、iet222.4856.52FemaleMarried4610Propriet1044.5FemaleMarried3611Propriet1029.5FemaleMarried4812Propriet17.931.6FemaleMarried4013Visa9103.6160.4FemaleMarried4014Visa224.564.5FemaleMarried4615Visa1049.5MaleSingle2416Propriet212.671.4MaleSingle3617Propriet35394FemaleSingle2218Discover3054.5FemaleMarried4

4、019Masterca21938.5FemaleMarried3220Propriet632.244.8FemaleMarried5621Propriet17.931.6FemaleSingle2822Propriet429.6770.82FemaleMarried3823American766.5266FemaleMarried5024Propriet2074FemaleMarried4225Visa214.539.5MaleMarried4826Propriet19.4830.02FemaleMarried6027Propriet1044.5FemaleMarried5428Proprie

5、t548.2192.8FemaleSingle4229Propriet337.371.2FemaleMarried3230Propriet14.518FemaleMarried7031Masterca215.863.2FemaleMarried2832Propriet1075FemaleMarried5233Propriet315.863.2FemaleMarried4434Propriet1040FemaleMarried3435Masterca510105.5FemaleMarried5636Masterca1029.5MaleSingle3637Visa20102.5FemaleSing

6、le4238Propriet614.5117.5FemaleMarried5039Propriet525.0213.23FemaleMarried4440Propriet2052.5FemaleMarried5841Propriet1362.7198.8FemaleMarried4242Visa416.519.5FemaleMarried4643Propriet20123.5FemaleMarried4844Propriet115.662.4FemaleMarried5445Propriet235.223.8FemaleMarried3846Propriet219.439.6FemaleMar

7、ried6047Masterca1025FemaleMarried4648Propriet320.1163.64FemaleMarried3049Propriet14.6814.82FemaleMarried3250Masterca994.3145.2FemaleMarried4651Propriet658.88176.62FemaleMarried3852Propriet551.7118.8MaleMarried6853Discover1058FemaleSingle7854Visa2074FemaleSingle2055Masterca2049.5FemaleMarried3256Prop

8、riet335.4141.6FemaleMarried3857Propriet656.4123.1FemaleMarried5458Propriet220.180.4FemaleMarried4859Masterca428.365.2FemaleMarried4660Propriet419.5113FemaleSingle5061Propriet119.2108.8FemaleMarried4662Propriet391.4859.91FemaleSingle3063Propriet537.453.6FemaleMarried5464Propriet17.931.6FemaleSingle42

9、65Propriet29.549.5FemaleMarried4866Propriet19.939.6FemaleMarried6267Propriet229.559.5FemaleMarried3468Propriet536.7146.8FemaleMarried2869Propriet211.847.2MaleMarried4670Propriet818.9595.05FemaleMarried5471Propriet557.18155.32FemaleMarried3072Masterca41058FemaleMarried3273Propriet1069FemaleSingle2274

10、Propriet215.546.5FemaleMarried3275Propriet214.2845.22FemaleMarried7476Propriet446.2684.74FemaleMarried6277Propriet2039FemaleMarried4278Propriet435.11111.14FemaleMarried2879Propriet331.286.8FemaleMarried3880Discover2089FemaleMarried5481Masterca219.578FemaleMarried6882Propriet629.3153.2FemaleSingle308

11、3Visa414.558.5FemaleMarried3684Propriet319.546FemaleMarried4485Visa2037.5FemaleMarried4486Propriet15.220.8FemaleMarried6287Masterca60144FemaleSingle4888Propriet40107FemaleMarried3689Propriet17.931.6FemaleSingle2090Propriet646.457.6FemaleMarried4291Propriet4158.395.2FemaleMarried5492Propriet17.0822.4

12、2FemaleMarried5493Propriet50159.75FemaleMarried7294Propriet1733229.5FemaleMarried3095American3066FemaleMarried4696Masterca1039.5FemaleMarried4497Propriet982.75253FemaleMarried3098Propriet1018287.59FemaleMarried5299Propriet231.447.6FemaleMarried30100Propriet111.0628.44FemaleMarried44.将上述调查数据中的“支付方式”和

13、“数量”在 Excel中进行格式设置和数 据录入。【三种数据录入方式及优缺点，选项录入方式（优点：分析输出形象； 缺点：录入速度慢、错误率高）、编码录入方式（优点：录入速度快；缺点： 分析输出不够形象，错误率高）、有效性设置录入方式（优点：准确度高、录 入速度快、分析输出形象；缺点：前期需要进行有效性设置）】.运用Excel软件的数据透视图和透视表进行问卷数据分析（1）按照支付方式统计频数和百分比后效计数百分比American22%Discover44%Masterca1414%Propriet7070%Visa1010%合计100100%（2）将100位顾客按照性别比例制作成如下饼图?祥.：

14、哪i： J.：. IE：.Female , 93, 93%Male ,7, 7%(3)将100位顾客按照性别和婚姻状况统计人数和百分比性别MarriedSingle总计Female801393Male437总计8416100(4)将100位顾客分性别统计平均享受的折扣额性别平均折扣额Female23.17Male12.94总计22.45(5)将100位顾客同时分性别和婚姻统计平均折扣额性别MarriedSingle总计Female1889.39265.192154.58Male78.0012.6090.60总计1967.39277.792245.18.运用统计函数计算数值型变量的常用指标，如平

15、均值、标准差等商品购买数量指标平均值3.22标准差2.72合计值322.运用分析数据库输出数值型变量的区间统计表格菜单提示：第一步：工具一加载宏；第二步：工具一数据分析一直方图，输入 区域设置待统计的数据范围，接受区域里设置分界点50、100、150、200、250, 在，勾选累计百分率选项，点击确定完成】0-503939%50-1003574%100-1501690%150-200696%200-250197%250-3100%.综合练习：请将上述数据整理到Excel中，然后运用Excel软件输出如下统计结果，并按照要求对输出结果进行分析 (1)用Excel分析输出如下表格结果表：支付方式分

16、析有效计数百分比American22%DiscoverMastercaProprietVisa1010%合计分析：受访者中使用哪种支付方式的人最多？ (2)用Excel分析输出如下表格结果。表：不同性别不同婚姻状况消费者的频数统计性别婚姻状况MarriedSingle总计Female80Male3总计100分析：受访者中哪类人最多？ (3)用Excel分析输出如下表格并补充缺失的数据。表：不同性别顾客的销售总额和平均销售额分析性别销售总额平均销售额Female7364.6579.19Male合计分析：哪类人的在这次促销活动中花费的钱更多？男性还是女性(4)用Excel分析输出如下表格并补充缺失的数据。表：不同性别不同婚姻状况消费者的平均购买数量性别婚姻状况MarriedSingle总计均值均值均值Female3.40Male1.33总计3.22分析：受访者中已婚者购买的数量多还是单身的人购买的数量多？ 受访者中女性购买的数量多还是男性购买的数量多？ 在上述四类人中,哪种类型