保險(xiǎn)市場調(diào)查與分析實(shí)訓(xùn)三任務(wù)一2.3.1任務(wù)一運(yùn)用Excel整理市場調(diào)查問卷數(shù)據(jù)

1、實(shí)訓(xùn)三：運(yùn)用Excel整理和分析市場調(diào)查問卷數(shù)據(jù)實(shí)訓(xùn)I目的：主要訓(xùn)練學(xué)生運(yùn)用 Excel軟件，對市場調(diào)查問卷數(shù)據(jù)進(jìn)行整理匯總， 在此基礎(chǔ)上，運(yùn)用Excel的制圖制表以及數(shù)據(jù)分析功能實(shí)現(xiàn)問卷數(shù)據(jù)的分析。實(shí) 訓(xùn)任務(wù)主要包括市場調(diào)查問卷整理和調(diào)查問卷數(shù)據(jù)分析兩項(xiàng)。實(shí)訓(xùn)任務(wù)一：問卷數(shù)據(jù)整理和分析一（單選題和數(shù)值填空題）實(shí)訓(xùn)要求：將下列調(diào)查數(shù)據(jù)錄入到 Excel中，然后用Excel按照規(guī)定要求進(jìn)行數(shù) 據(jù)分析，輸出分析結(jié)果。具體任務(wù)：Pelican商店最近舉辦一場商場促銷活動(dòng)，公司高層要求其市場研究 部對次此促銷的市場反映作一項(xiàng)調(diào)查。市場研究部在促銷當(dāng)天對在Pelican商店消費(fèi)的全部100位顧客進(jìn)行調(diào)查

2、，調(diào)查的結(jié)果如下。促銷當(dāng)天對100位顧客的調(diào)查數(shù)據(jù)資料顧客編號支付方式數(shù)量折扣總額銷售額性別婚姻狀況年齡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.將上述調(diào)查數(shù)據(jù)中的“支付方式”和

13、“數(shù)量”在 Excel中進(jìn)行格式設(shè)置和數(shù) 據(jù)錄入。【三種數(shù)據(jù)錄入方式及優(yōu)缺點(diǎn)，選項(xiàng)錄入方式（優(yōu)點(diǎn)：分析輸出形象； 缺點(diǎn)：錄入速度慢、錯(cuò)誤率高）、編碼錄入方式（優(yōu)點(diǎn)：錄入速度快；缺點(diǎn)： 分析輸出不夠形象，錯(cuò)誤率高）、有效性設(shè)置錄入方式（優(yōu)點(diǎn)：準(zhǔn)確度高、錄 入速度快、分析輸出形象；缺點(diǎn)：前期需要進(jìn)行有效性設(shè)置）】.運(yùn)用Excel軟件的數(shù)據(jù)透視圖和透視表進(jìn)行問卷數(shù)據(jù)分析（1）按照支付方式統(tǒng)計(jì)頻數(shù)和百分比后效計(jì)數(shù)百分比American22%Discover44%Masterca1414%Propriet7070%Visa1010%合計(jì)100100%（2）將100位顧客按照性別比例制作成如下餅圖?祥.：

14、哪i： J.：. IE：.Female , 93, 93%Male ,7, 7%(3)將100位顧客按照性別和婚姻狀況統(tǒng)計(jì)人數(shù)和百分比性別MarriedSingle總計(jì)Female801393Male437總計(jì)8416100(4)將100位顧客分性別統(tǒng)計(jì)平均享受的折扣額性別平均折扣額Female23.17Male12.94總計(jì)22.45(5)將100位顧客同時(shí)分性別和婚姻統(tǒng)計(jì)平均折扣額性別MarriedSingle總計(jì)Female1889.39265.192154.58Male78.0012.6090.60總計(jì)1967.39277.792245.18.運(yùn)用統(tǒng)計(jì)函數(shù)計(jì)算數(shù)值型變量的常用指標(biāo)，如平

15、均值、標(biāo)準(zhǔn)差等商品購買數(shù)量指標(biāo)平均值3.22標(biāo)準(zhǔn)差2.72合計(jì)值322.運(yùn)用分析數(shù)據(jù)庫輸出數(shù)值型變量的區(qū)間統(tǒng)計(jì)表格菜單提示：第一步：工具一加載宏；第二步：工具一數(shù)據(jù)分析一直方圖，輸入 區(qū)域設(shè)置待統(tǒng)計(jì)的數(shù)據(jù)范圍，接受區(qū)域里設(shè)置分界點(diǎn)50、100、150、200、250, 在，勾選累計(jì)百分率選項(xiàng)，點(diǎn)擊確定完成】0-503939%50-1003574%100-1501690%150-200696%200-250197%250-3100%.綜合練習(xí)：請將上述數(shù)據(jù)整理到Excel中，然后運(yùn)用Excel軟件輸出如下統(tǒng)計(jì)結(jié)果，并按照要求對輸出結(jié)果進(jìn)行分析 (1)用Excel分析輸出如下表格結(jié)果表：支付方式分

16、析有效計(jì)數(shù)百分比American22%DiscoverMastercaProprietVisa1010%合計(jì)分析：受訪者中使用哪種支付方式的人最多？ (2)用Excel分析輸出如下表格結(jié)果。表：不同性別不同婚姻狀況消費(fèi)者的頻數(shù)統(tǒng)計(jì)性別婚姻狀況MarriedSingle總計(jì)Female80Male3總計(jì)100分析：受訪者中哪類人最多？ (3)用Excel分析輸出如下表格并補(bǔ)充缺失的數(shù)據(jù)。表：不同性別顧客的銷售總額和平均銷售額分析性別銷售總額平均銷售額Female7364.6579.19Male合計(jì)分析：哪類人的在這次促銷活動(dòng)中花費(fèi)的錢更多？男性還是女性(4)用Excel分析輸出如下表格并補(bǔ)充缺失的數(shù)據(jù)。表：不同性別不同婚姻狀況消費(fèi)者的平均購買數(shù)量性別婚姻狀況MarriedSingle總計(jì)均值均值均值Female3.40Male1.33總計(jì)3.22分析：受訪者中已婚者購買的數(shù)量多還是單身的人購買的數(shù)量多？ 受訪者中女性購買的數(shù)量多還是男性購買的數(shù)量多？ 在上述四類人中,哪種類型