Excel在市場調(diào)查和統(tǒng)計中的應(yīng)用

Excel在市場調(diào)查和統(tǒng)計中的應(yīng)用第1頁/共106頁一、繪制“工作進度表”（甘特圖）第2頁/共106頁第3頁/共106頁第4頁/共106頁第5頁/共106頁第6頁/共106頁第7頁/共106頁第8頁/共106頁第9頁/共106頁第10頁/共106頁第11頁/共106頁第12頁/共106頁二、簡單隨機抽樣假定要在10000個號碼中（編號0000-9999）抽出500個號碼第13頁/共106頁1、函數(shù)法第14頁/共106頁第15頁/共106頁第16頁/共106頁第17頁/共106頁第18頁/共106頁2、隨機數(shù)發(fā)生器法第19頁/共106頁第20頁/共106頁第21頁/共106頁第22頁/共106頁三、描述統(tǒng)計第23頁/共106頁第24頁/共106頁第25頁/共106頁3.1.2用Excel工作表函數(shù)描述集中趨勢1．均值（MEAN）（1）算術(shù)平均數(shù)（2）加權(quán)平均數(shù)（3）幾何平均數(shù)第26頁/共106頁

算術(shù)平均數(shù)：指將數(shù)據(jù)總和除以數(shù)據(jù)個數(shù)得到的值。在EXCEL中，用語法為AVERAGE(number1,number2,...)

指計算所有含數(shù)值數(shù)據(jù)的單元格的平均值。

算術(shù)平均數(shù)第27頁/共106頁第28頁/共106頁第29頁/共106頁加權(quán)平均數(shù)加權(quán)平均數(shù)：是指加權(quán)平均數(shù)是不同比重數(shù)據(jù)的平均數(shù)。主要用于計算分組數(shù)據(jù)的平均數(shù)。在EXCEL中，用語法為SUMPRODUCT(B4:B14,C4:C14)/SUM(B4:B14)第30頁/共106頁第31頁/共106頁幾何平均數(shù)用于計算特殊數(shù)據(jù)的一種平均數(shù)，主要用于計算比率或速度的平均值。分簡單幾何平均數(shù)和加權(quán)幾何平均數(shù)。這里主要介紹簡單幾何平均數(shù)在實際應(yīng)用中，通常采用幾何平均數(shù)1、計算變復(fù)利的平均增長率、平均發(fā)展速度、2、計算平均發(fā)展速度；等。語法：1、GEOMEAN(number1,number2,...)第32頁/共106頁第33頁/共106頁第34頁/共106頁第35頁/共106頁2．中位數(shù)中位數(shù)是指全體數(shù)值按大小排列后位于中間的數(shù)值。語法：MEDIAN(number1,number2,...)如果參數(shù)集合中包含有偶數(shù)個數(shù)字，函數(shù)MEDIAN()將返回位于中間的兩個數(shù)的平均值。不受極端值影響。適用與分配不對稱的數(shù)據(jù)。政府機關(guān)所公布的國民收入，常用中位數(shù)來作代表值。第36頁/共106頁

為什么呢？不妨先講一個笑話，如果比爾·蓋茨和十幾個窮光蛋在一個房間里，這個房間里十幾個人的平均收入就都超過億元。之所以是笑話，是因為比爾·蓋茨和窮光蛋的收入差距過大，導(dǎo)致平均數(shù)值缺乏實際參考意義。但如果用中位數(shù)來衡量，就知道這房間里起碼有一半人是窮光蛋。由此可見，中位數(shù)有助于了解普通民眾的收入水平。而中位數(shù)與平均數(shù)的差異，則有助于了解全體民眾的收入集中度。

什么是中位數(shù)呢？一組數(shù)據(jù)中處于最中間的一個數(shù)據(jù)就是中位數(shù)。我們可以再看一個例子。比如，某地有五個人，月工資分別是1000，1000，1300，1700，20000元。那么，月收入1300的那個人，就代表著收入中位數(shù)；而該五個人的平均收入為5000。平均收入是收入中位數(shù)的接近4倍。如果這五個人的月工資分別是3000，4000，5000，6000和7000元呢？月收入5000的那個人代表著該地區(qū)的收入中位數(shù)；該地區(qū)平均收入也是5000。平均收入=收入中位數(shù)。

如果不提收入中位數(shù)，而只講平均收入，在操作層面會遇到一些可笑的做法。比如，一個一千人的社區(qū)，如果要把人均收入從3000元提高10%，只需要向社區(qū)引進一個年收入30萬元的小老板落戶即可。雖然提高人均收入的“政績工程”效果顯著，但社區(qū)群眾的收入水平并未發(fā)生明顯變化。

第37頁/共106頁

圖3-11家用電器銷售量中位數(shù)第38頁/共106頁3．眾數(shù)眾數(shù)是一組數(shù)列中出現(xiàn)次數(shù)最多的數(shù)值，眾數(shù)函數(shù)MODE()返回某一數(shù)組或數(shù)據(jù)區(qū)域中出現(xiàn)頻率最多的數(shù)值。與MEDIAN相同，MODE也是一個位置測量函數(shù)。語法：MODE(number1,number2,...)如果數(shù)據(jù)集合中不含有重復(fù)的數(shù)據(jù)，則MODE()函數(shù)返回錯誤值N/A。不容易受極端值影響。第39頁/共106頁圖3-12家用電器銷售量眾數(shù)返回本節(jié)第40頁/共106頁4．最大值最大值函數(shù)可以返回數(shù)據(jù)集中的最大數(shù)值。語法：MAX(number1,number2,...)MAXA(number1,number2,...)如果參數(shù)不包含數(shù)字，函數(shù)MAX返回0。

MAX是求所有數(shù)值數(shù)據(jù)的最大值，而MAXA是求所有非空白單元格的最大值。注意：當(dāng)數(shù)值全為負值時，若其中含文本單元格，用MAXA（）求得所有非空白單元格的最大值將為0。第41頁/共106頁4．最小值最大值函數(shù)可以返回數(shù)據(jù)集中的最小數(shù)值。語法：MIN(number1,number2,...)MINA(number1,number2,...)如果參數(shù)不包含數(shù)字，函數(shù)MIN返回0。MIN是求所有數(shù)值數(shù)據(jù)的最大值，而MINA是求所有非空白單元格的最小值。注意：當(dāng)數(shù)值全為正值及含字符串的單元格時，時，若其中含文本單元格，用MINA

（）求得所有非空白單元格的最小值將為0。第42頁/共106頁第43頁/共106頁眾數(shù)、中位數(shù)、均值的異同眾數(shù)是一組數(shù)據(jù)中出現(xiàn)次數(shù)最多的變量值，它用于對分類數(shù)據(jù)的概括性度量，其特點是不受極端值的影響，但它沒有利用全部數(shù)據(jù)信息，而且還具有不惟一性。一組數(shù)據(jù)可能有眾數(shù)，也可能沒有眾數(shù)；可能有一個眾數(shù)，也可能有多個眾數(shù)。中位數(shù)是一組數(shù)據(jù)按大小順序排序后處于中間位置上的變量，它主要用于對順序數(shù)據(jù)的概括性度量。均值是一組數(shù)據(jù)的算術(shù)平均，它利用了全部數(shù)據(jù)信息，是概括一組數(shù)據(jù)最常用的一個值。第44頁/共106頁例某商場家用電器銷售情況如圖3-9所示。（1）計算各種電器的全年平均銷售量，如圖3-10所示。（2）計算各種電器銷售量的中位數(shù)，如圖3-11所示。（3）計算各種電器銷售量的眾數(shù)，如圖3-12所示。第45頁/共106頁

圖3-9某商場家用電器銷售情況第46頁/共106頁

圖3-10家用電器銷售量平均數(shù)第47頁/共106頁5、離中趨勢的測定內(nèi)容在研究現(xiàn)象總體標(biāo)志的一般水平時，不僅要研究總體標(biāo)志的集中趨勢，還要研究總體標(biāo)志的離中趨勢，如研究價格背離價值的平均程度。研究離中趨勢可以通過計算標(biāo)志變異指標(biāo)來進行。標(biāo)志變異指標(biāo)是同統(tǒng)計平均數(shù)相聯(lián)系的一種綜合指標(biāo)，用于度量隨機變量在取值區(qū)間內(nèi)的分布情況，主要有極差、平均差、標(biāo)準(zhǔn)差、方差、四分位數(shù)、百分位數(shù)等。在一般計算中，這些指標(biāo)計算是比較復(fù)雜的，但在Excel中都有相應(yīng)的函數(shù)，因而使計算變得很簡單。返回本節(jié)第48頁/共106頁1.極差一組數(shù)據(jù)中的最大值減去最小值在EXCEL中，用MAX()-MIN()或LARGE()-SMALL()來計算極差。極差越小表示數(shù)據(jù)分布越集中。易受極端值影響。反應(yīng)不夠靈敏，因此不是很好的離散程度衡量法在工廠看燈泡的使用壽命適合看極差以及股票組對股價的漲跌等也適合看極差。第49頁/共106頁2.平均差（平均絕對離差）每一個觀測值與其平均值間差異的絕對值的算術(shù)平均。

分組數(shù)據(jù)未分組數(shù)據(jù)語法：AVEDEV(number1,number2,...)第50頁/共106頁3．樣本標(biāo)準(zhǔn)差樣本標(biāo)準(zhǔn)差函數(shù)用來估算樣本的標(biāo)準(zhǔn)偏差，反映相對于平均值（mean）的離散程度，Excel計算樣本標(biāo)準(zhǔn)差采用不偏估計式（亦即自由度＝n-1），其計算公式為語法：STDEV(number1,number2,...)第51頁/共106頁

總體標(biāo)準(zhǔn)差總體標(biāo)準(zhǔn)差函數(shù)返回以參數(shù)形式給出的整個樣本總體的標(biāo)準(zhǔn)偏差，反映相對于平均值（mean）的離散程度。計算總體標(biāo)準(zhǔn)差使用整個總體的變量，通常采用偏性估計式（亦即自由度為n），其計算公式為語法：STDEVP(number1,number2,...)第52頁/共106頁樣本方差樣本方差：樣本中各數(shù)據(jù)與樣本平均數(shù)的差的平方和的平均數(shù)叫做樣本方差；樣本方差的算術(shù)平方根叫做樣本標(biāo)準(zhǔn)差。樣本方差和樣本標(biāo)準(zhǔn)差都是衡量一個樣本波動大小的量，樣本方差或樣本標(biāo)準(zhǔn)差越大，樣本數(shù)據(jù)的波動就越大。第53頁/共106頁例計算各家用電器電銷售量的總體標(biāo)準(zhǔn)差，如圖3-13所示。圖3-13計算總體標(biāo)準(zhǔn)差第54頁/共106頁例兩組工人生產(chǎn)某種零件的產(chǎn)量如圖3-14所示。圖3-14兩組工人產(chǎn)量第55頁/共106頁圖3-15計算產(chǎn)量平均數(shù)第56頁/共106頁圖3-16計算兩組工人產(chǎn)量標(biāo)準(zhǔn)差第57頁/共106頁圖3-17計算兩組工人產(chǎn)量標(biāo)準(zhǔn)差系數(shù)返回本節(jié)第58頁/共106頁4.四分位數(shù)與四分位距四分位數(shù)是將中值的前后兩部分數(shù)值再等分為二，以數(shù)值小的一端算起，前半部的分區(qū)點稱為第1四分位數(shù)，后半部的分區(qū)點稱為第3四分位數(shù)，而中值即為第2四分位數(shù)。四分位數(shù)通常用于在銷售額和測量值數(shù)據(jù)集中對總體進行分組。語法：QUARTILE(array,quart)array：需要求四分位數(shù)值的數(shù)組或數(shù)字型單元格區(qū)域。quart：決定返回哪一個四分位值（1、2、3、4）。Quart值與QUARTILE返回值的對應(yīng)關(guān)系見表3-1所示。第59頁/共106頁quart值函數(shù)QUARTILE返回值0最小數(shù)值1第一個四分位數(shù)（第25個百分排位）2中分位數(shù)（第50個百分排位）3第三個四分位數(shù)（第75個百分排位）4最大數(shù)值表3-1Quart值與QUARTILE返回值的對應(yīng)關(guān)系第60頁/共106頁例

使用前面例子資料，計算四分位數(shù)和四分位距，如圖3-18所示。圖3-18計算四分位數(shù)和四分位距返回本節(jié)第61頁/共106頁5．偏度系數(shù)偏度系數(shù)返回分布的偏斜度。偏斜度反映以平均值為中心的分布的不對稱程度。其計算公式為語法：SKEW(number1,number2,...)第62頁/共106頁正態(tài)分布的偏度為0，兩側(cè)尾部長度對稱。bs<0稱分布具有負偏離，也稱左偏態(tài)，此時數(shù)據(jù)位于均值左邊的比位于右邊的多，直觀表現(xiàn)為左邊的尾部相對于與右邊的尾部要長；bs>0稱分布具有正偏離，也稱右偏態(tài)，情況相反；而bs接近0則可認為分布是對稱的。左偏時相反，即眾數(shù)>中位數(shù)>平均數(shù)。右偏時一般算術(shù)平均數(shù)>中位數(shù)>眾數(shù)，正態(tài)分布三者相等。第63頁/共106頁6．峰度系數(shù)峰態(tài)系數(shù)。表征概率密度分布曲線在平均值處峰值高低的特征數(shù)。直觀看來，峰度反映嘞尾部的厚度。語法：KURT(number1,number2,...)第64頁/共106頁峰度以bk表示，Yi是樣本測定值，Ybar是樣本n次測定值的平均值。正態(tài)分布的峰度為3。簡單來講，峰度是描述分布形態(tài)的陡緩程度。峰度為3表示與正態(tài)分布相同，峰度大于3表示比正態(tài)分布陡峭，小于3表示比正態(tài)分布平坦。第65頁/共106頁四、樣本大小的選擇樣本不要過大，過大會造成浪費成本，但也不能過小，過小會有太大的抽要誤差。在非概率抽樣的情況下，除了依靠抽樣人員的主觀判斷或假設(shè)外，還有以下客觀科學(xué)方法可以參考第66頁/共106頁未知總體方差的樣本大小選擇事實上，很多情況是無法得知總體方差的（σ2）.如果總體方差未知，則可以用過去調(diào)查的樣本方差（S2）替代。若過去也無類似調(diào)查，可先進性小規(guī)模測驗，計算出樣本方差，然后再計算需要樣本數(shù)：α即是顯著水平，e即是可容忍誤差S即是樣本標(biāo)準(zhǔn)差第67頁/共106頁舉例：現(xiàn)在需要進行一個大學(xué)的學(xué)生信用卡平均簽帳金額抽樣，現(xiàn)在要確定抽樣的最少人數(shù)，但總體方差未知，已先舉行一次小規(guī)模測驗，取得了40位大學(xué)生的信用卡每月平均簽帳金額。要求在顯著水平α=0.05（Zα/2=1.96），希望總體誤差不超過50元。求調(diào)查的最少樣本是多大？第68頁/共106頁第69頁/共106頁四、設(shè)計問卷和取得數(shù)據(jù)1、列舉所要收集的信息，初學(xué)者可找?guī)追菹嚓P(guān)研究的論文參考，并與同組的同學(xué)和指導(dǎo)老師討論，得出要收集的信息。2、決定訪問的形式，是直接訪問還是結(jié)構(gòu)訪問，或者是同時結(jié)合進行。3、決定訪問的方式，是人員訪問、電話訪問、還是郵件訪問、問卷訪問。4、決定問題的內(nèi)容，原則上你最終你想得到什么你就問什么問題。第70頁/共106頁但要考慮以下問題：此問題有無必要？受訪者能否回答、愿意回答、容易回答？5、決定問題的順序，問題有先后邏輯性，比如調(diào)查手機，你如果遇到?jīng)]有手機的怎么辦？Q2購買理由Q3使用時間Q4付費形式Q5產(chǎn)品屬性Q1-1未買原因Q1有無手機Q6期望價位第71頁/共106頁6、問卷外觀、測驗和修訂，要問卷的紙質(zhì)、顏色、單雙面印刷等因素；還要考慮找20人先做問卷的小測驗，一邊測驗一邊修改問卷。幾種典型題目：單選、多選、填寫、等級等等。第72頁/共106頁4.2舉例：新產(chǎn)品市場調(diào)查分析4.2.1設(shè)計調(diào)查流程設(shè)計調(diào)查問卷完成調(diào)查結(jié)果數(shù)據(jù)庫樣本組成分析產(chǎn)品