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分析：受訪者中已婚者購買的數量多還是單身的人購買的數量多？ 受訪者中女性購買的數量多還是男性購買的數量多？ 在上述四類人中,哪種類型