excel在統(tǒng)計學(xué)當(dāng)中的應(yīng)用正式教案

1、第一章excel數(shù)據(jù)表及其相關(guān)基礎(chǔ)知識 本章要點 Excel與統(tǒng)計學(xué)的關(guān)系 Excel與統(tǒng)計有關(guān)的基礎(chǔ)知識1.1excel與統(tǒng)計學(xué)的關(guān)系。以例1.1為例，這是一個咖啡店的咖啡銷售情況，我們現(xiàn)在要統(tǒng)計其一周各種咖啡的銷售情況，應(yīng)用excel快速統(tǒng)計出銷售數(shù)量，可以簡化得到結(jié)果的過程，通過以上案例我們可以知道我們可以通過excel中的函數(shù)公式和圖表快速的得到統(tǒng)計結(jié)果。接下來我們要了解excel中與統(tǒng)計有關(guān)的基礎(chǔ)知識1.2.excel與統(tǒng)計有關(guān)的基礎(chǔ)知識一數(shù)據(jù)的手工輸入單元格中可以輸入文本，數(shù)值（包括日期），公式，其中，前兩類是常量，公式則為變量，一旦該公式所引用的單元格的數(shù)值發(fā)生變化，該公式的值將

2、立即自動重新計算。要注意的是當(dāng)輸入數(shù)字型文本的時候，即數(shù)字串實際屬于字符串, 比如說電話號碼，應(yīng)按 6393672方法輸入（即先輸入） 或者對選定的單元格設(shè)定為文字格式(通過”格式”菜單中的”單元格”中的“數(shù)字”下的“文本”。（尤其是在函數(shù)當(dāng)中，這點很重要。）公式的輸入要以“=”開始，公式可以包括以下內(nèi)容之一：函數(shù)，引用，運(yùn)算符，常量。例：=pi()*c42二.Excel公式及函數(shù)基礎(chǔ)1.運(yùn)算符及其類型 算術(shù)運(yùn)算符（加法） （減法） *（乘法） （除法） （乘方） %（百分比） 比較運(yùn)算符=（等于）<>（不等于）、>(大于)，<（小于），<=（小于等于）,>

3、=(大于等于) 文本運(yùn)算符&(連接) 例 本月銷售的結(jié)果是本月銷售。（注意，在公式中輸入文本常量得加上符號）若要改變，請加括弧。在公式中，每個運(yùn)算符都需要特定類型的數(shù)據(jù)與之對應(yīng)，如果輸入數(shù)據(jù)的類型與其不符合，excel將對數(shù)據(jù)的格式自動進(jìn)行轉(zhuǎn)換。2.單元格的引用在excel中單元格的默認(rèn)引用樣式為A1樣式，即字母+數(shù)字格式，我們在excel中會經(jīng)常使用公式的復(fù)制，在復(fù)制中我們會對單元格引用產(chǎn)生不同的需求，針對這種情況，excel把單元格的引用分為3種類型。 相對引用：在默認(rèn)引用樣式條件下，直接采用A1樣式，如=B1+B2則把公式復(fù)制到其他單元格時，行或列的引用會改變 絕對引用：在默認(rèn)引

4、用樣式條件下，在行列標(biāo)前都加上“$”符號，如=$B$1+$B$2,則在復(fù)制公式時，行和列的引用不會改變。 混合引用：在默認(rèn)引用樣式條件下，行或列中有一個是相對引用，一個是絕對引用。如=$B1+B$2最后談一下三維地址引用：如果引用的是本工作表里的單元格，可以不用在單元格地址前加工作表名和感嘆號，否則就要用例如sheet2!c7來引用sheet2工作表中的c7單元格。例sheet1！1+2(注意:其中的a2是指公式所在的工作表的a2)3.函數(shù)函數(shù)分為內(nèi)置函數(shù)，擴(kuò)展函數(shù)和用戶自定義函數(shù)，內(nèi)置函數(shù)只要啟動excel就可以使用；擴(kuò)展函數(shù)必須通過選擇工具/加載宏命令加載，然后才能像內(nèi)置函數(shù)那樣使用；用戶

5、自定義函數(shù)可以通過visual basic for applications(VBA)來創(chuàng)建。參數(shù)：函數(shù)右邊括號中的部分稱為參數(shù)，是我們傳給函數(shù)用來執(zhí)行運(yùn)算的數(shù)值各個參數(shù)需要用括號（ ）括起來，假如使用多個參數(shù)，那么參數(shù)與參數(shù)之間使用半角逗號進(jìn)行分隔。各個函數(shù)對參數(shù)的要求不同，可以根據(jù)函數(shù)的要求進(jìn)行輸入?yún)?shù)。注意: 如果將引用作為一個參數(shù), 而且這一引用使用逗號做合并運(yùn)算,則用括弧將引用括起來.例：sum(A1,C1).嵌套函數(shù)：即一個函數(shù)是另一個函數(shù)的參數(shù)。準(zhǔn)許多達(dá)七級的嵌套函數(shù)。函數(shù)嵌套：例：IF(AVERAGE(A1:A3)>50,MAX(A1:A3),SUM(A1:A3)。含義是

6、:如果A1,A2,A3三個數(shù)的均值>50,則求A1,A2,A3三個數(shù)的最大值，否則就求A1,A2,A3三個數(shù)的和。函數(shù)的輸入： 1手工輸入函數(shù)2.利用插入函數(shù)按鈕。4.公式的編輯雙擊單元格可以在單元格內(nèi)部直接編輯單元格內(nèi)容，或者選擇要編輯的單元格，然后單擊編輯欄，可以在編輯欄中編輯單元格內(nèi)容。三Excel圖表基礎(chǔ)所謂圖表是工作表內(nèi)容的圖形表示。圖表是由圖表區(qū)域及區(qū)域中的對象組成，即包括圖表區(qū)，圖表標(biāo)題，繪圖區(qū)，網(wǎng)格線，系列，坐標(biāo)軸，坐標(biāo)軸標(biāo)題，圖例。Excel中提供我們3類圖表-標(biāo)準(zhǔn)圖表類型，excel自定義的圖表類型，用戶自定義圖表類型，我們以例1.2來看一下他們的樣子。我們可以通過圖

7、表向?qū)Чぞ邅斫D表。圖表的編輯：1. 圖表區(qū)域及其選擇：圖表的選擇和圖表對象（圖例，圖表標(biāo)題）的選擇2. 圖表區(qū)域的修飾與編輯：a修飾圖表區(qū)域：單擊圖表區(qū)域/圖表區(qū)格式按鈕。b編輯（移動，縮放，復(fù)制，刪除）3. 圖表對象的修飾：a.圖表標(biāo)題的修飾。b.圖表坐標(biāo)軸的修飾。c.圖表數(shù)據(jù)系列的修飾，雙擊某個數(shù)據(jù)系列d.圖表圖例的修飾。e.編輯網(wǎng)格線4. 數(shù)據(jù)修改：a.增加和刪除圖表數(shù)據(jù)，使用源數(shù)據(jù)對話框添加或刪除數(shù)據(jù)b.改變圖表數(shù)據(jù)，默認(rèn)下圖表與原始數(shù)據(jù)之間是相互鏈接的，因此改變原始數(shù)據(jù)（或圖表數(shù)據(jù)），則圖表數(shù)據(jù)（或原始中的數(shù)據(jù)）將隨之做相應(yīng)的變動c.為數(shù)據(jù)系列添加趨勢線：可以在非堆積型二維條形圖

8、，柱形圖等圖中為數(shù)據(jù)系列添加趨勢線。來顯示實際數(shù)據(jù)變動的情況。5. 改變圖表的類型。6.標(biāo)準(zhǔn)圖表按用途可分為 表示變化趨勢的柱形圖，條形圖，折線圖,面積圖,雷達(dá)圖，圓錐圖，圓柱圖，棱錐圖。 表示所占比例的餅圖，圓環(huán)圖 表示變量之間關(guān)系的XY散點圖，氣泡圖 用來畫圖的XY散點圖，曲面圖。 表示股價變化的股價圖。第二章使用 Excel輸入.整理及顯示統(tǒng)計數(shù)據(jù)本章要點 數(shù)據(jù)的輸入 數(shù)據(jù)的整理 數(shù)據(jù)的圖形顯示2.1 數(shù)據(jù)的輸入一導(dǎo)入數(shù)據(jù)1.手工輸入，2.從數(shù)據(jù)庫和文件導(dǎo)入數(shù)據(jù)：數(shù)據(jù)/導(dǎo)入外部數(shù)據(jù)/導(dǎo)入數(shù)據(jù)，在選取數(shù)據(jù)源對話框中選取相應(yīng)的數(shù)據(jù)庫文件（比如Access文件），若數(shù)據(jù)不在本機(jī)，則單擊選取數(shù)據(jù)

9、源對話框中的新建源，出現(xiàn)數(shù)據(jù)連接向?qū)?，以此來從外部?shù)據(jù)連接導(dǎo)入數(shù)據(jù)3.用Microsoft Query導(dǎo)入數(shù)據(jù)：要使用Query導(dǎo)入數(shù)據(jù)，首先安裝Query：在首次使用數(shù)據(jù)/導(dǎo)入外部數(shù)據(jù)/新建數(shù)據(jù)庫查詢時，系統(tǒng)將提示安裝Query，安裝后，再利用數(shù)據(jù)/導(dǎo)入外部數(shù)據(jù)/新建數(shù)據(jù)庫查詢打開query查詢，利用query向?qū)?，獲得我們所需的數(shù)據(jù)。4.從web導(dǎo)入數(shù)據(jù)：數(shù)據(jù)/導(dǎo)入外部數(shù)據(jù)/新增web查詢，在指定網(wǎng)站位置即可。2.2數(shù)據(jù)的整理一初步的數(shù)據(jù)整理1.排序，根據(jù)某特定列的內(nèi)容來重排數(shù)據(jù)清單（就是一個選定的表區(qū)域）中的行。應(yīng)用數(shù)據(jù)/排序命令進(jìn)行排序工作。2.篩選是查找和處理數(shù)據(jù)清單中數(shù)據(jù)子集的快捷

10、方式，篩選后將暫時隱藏不必顯示的行。Excel中有兩種篩選命令：自動篩選和高級篩選。 自動篩選：首先選擇表，再單擊數(shù)據(jù)/篩選/自動篩選 高級篩選：首先得制定兩個區(qū)域：數(shù)據(jù)區(qū)域和條件區(qū)域，其中條件區(qū)域首行為列標(biāo)志，后面的行為條件。如例2.1二數(shù)據(jù)分組整理成次數(shù)分布表首先，將原始數(shù)據(jù)輸入工作表的單元格中，之后進(jìn)行編組，計次，讓數(shù)據(jù)呈現(xiàn)出具體的意義，即分組整理成次數(shù)分布表1. 單項分組（使用countif()）使用COUNTIF（）函數(shù)計算區(qū)域中滿足給定條件的單元格的個數(shù)。 語法：COUNTIF(range,criteria)Range：為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteri

11、a ：為確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 32、"32"、"apples"或">32" （表達(dá)式要用“”包起來）。見例2.22. 組距分組數(shù)據(jù)的次數(shù)分布表 求出全距R ，使用max（）-min（）函數(shù) 決定組數(shù)，主要是憑經(jīng)驗確定，也可以按照一個經(jīng)驗公式即組數(shù)m=1+3.322lgN,N為總次數(shù)。（LOG10(number)，） 決定組距，如果是等距的話,組距i=R/m在用經(jīng)驗公式計算m和i時，計算的結(jié)果采用舍去進(jìn)一法。即直接將小數(shù)舍去，并在整數(shù)位上加1。先應(yīng)用trunc(nu

12、mber,num_digits)截尾取整，（Number：需要截尾取整的數(shù)字， Num_digits（可選參數(shù)）用于指定取整精度的數(shù)字，不寫該參數(shù)表示把小數(shù)點后的數(shù)都去掉），之后在加1。 組距上，下限的選擇(一般遵循上組限不在內(nèi)的原則) 計算組中值，對于缺上限的開口組組中值=下限+鄰組組距/2 計算各組的次數(shù)，利用frequency( array,bins )函數(shù)來計算某個范圍內(nèi)數(shù)值出現(xiàn)的次數(shù)。(array是指原始資料的存放區(qū)域，bins指統(tǒng)計分組的組上限構(gòu)成的數(shù)值序列。Frequency函數(shù)在統(tǒng)計次數(shù)時，將與對應(yīng)上限值一樣大的數(shù)也統(tǒng)計在內(nèi)，由于函數(shù) FREQUENCY 返回一個數(shù)組，所以它是

13、數(shù)組公式，即首先選擇輸出的區(qū)域，打入函數(shù)，之后按ctr+shift+enter，這點與非數(shù)組公式不同，非數(shù)組公式是直接按回車) 累加次數(shù)分布見例2.3。三數(shù)據(jù)透視表和透視圖數(shù)據(jù)透視表其實是對表內(nèi)數(shù)據(jù)的分類匯總（與數(shù)據(jù)/分類匯總命令類似，但是它的匯總是不管相鄰與否，一律匯總，不像數(shù)據(jù)/分類匯總只是匯總相鄰?fù)诸愖侄沃档膶?yīng)匯總字段數(shù)值。）和建立交叉表的交互式格式表格，它是一種組織數(shù)據(jù)的軟設(shè)備。用戶可以在透視表中指定想顯示的字段和數(shù)據(jù)項，以確定如何組織數(shù)據(jù)見例2.4。1.透視表的創(chuàng)建 單擊表格區(qū)域中的任意單元格 單擊數(shù)據(jù)/數(shù)據(jù)透視表和數(shù)據(jù)透視圖，按要求選擇相應(yīng)的選項。 拖動右邊的字段按鈕到圖中來做

14、成透視表的行列數(shù)據(jù) 例如把地區(qū)作為頁字段，產(chǎn)品代號作為行字段，季度作為列字段，銷售額作為數(shù)據(jù)項。2.修改透視表 行列互換：在列字段名單元格A4按下左鍵拖曳到行字段中即可。（行同上） 添加和刪除字段：添加額外的字段:單擊工具欄上的“數(shù)據(jù)透視表”/向?qū)е匦逻M(jìn)行區(qū)域選擇,將已有字段添加或刪除：單擊數(shù)據(jù)透視表工具欄/字段列表，將字段拖入數(shù)據(jù)透視表（添加）或?qū)⒆侄瓮先胱侄瘟斜碇校▌h除）3. 刷新透視表（有以下兩種方法） 選擇數(shù)據(jù)透視表，執(zhí)行數(shù)據(jù)/更新數(shù)據(jù) 單擊“數(shù)據(jù)透視表”工具欄上的“！”4. 對數(shù)據(jù)透視表排序選擇字段，在選擇數(shù)據(jù)/排序。5. 改變透視表的匯總方式選擇匯總字段，右擊選擇字段設(shè)置，在彈出的

15、對話框內(nèi)選擇匯總方式。6. 顯示和隱藏明細(xì)數(shù)據(jù) 右單擊透視表中的列字段名（如“季度”） 執(zhí)行“組及顯示明細(xì)數(shù)據(jù)”顯示明細(xì)數(shù)據(jù) 在對話框中選擇明細(xì)數(shù)據(jù)所在的字段，（如“地區(qū)”）確定7. 創(chuàng)建計算字段 選擇匯總字段，單擊數(shù)據(jù)表工具欄數(shù)據(jù)透視表公式計算字段彈出對話框 在“名稱”欄中輸入計算字段的名稱，在“公式”欄中輸入計算字段的公式， 單擊“添加”確定8.數(shù)據(jù)透視圖的創(chuàng)建和編輯數(shù)據(jù)透視圖是數(shù)據(jù)透視表的可視化，可以通過數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)斫⒒蛲ㄟ^數(shù)據(jù)透視表直接建立數(shù)據(jù)透視圖（激活數(shù)據(jù)透視表后，單擊數(shù)據(jù)透視表工具欄中的圖表向?qū)О粹o，可以建立數(shù)據(jù)透視圖）數(shù)據(jù)透視圖的編輯和之前一般圖表的編輯類似。

16、四做匯總統(tǒng)計表對多個工作表和工作薄內(nèi)相類似的數(shù)據(jù)進(jìn)行合并計算，將合并的結(jié)果放到一張合并的工作表內(nèi)，利用數(shù)據(jù)/合并計算按鈕。見例2.52.3統(tǒng)計數(shù)據(jù)的圖形顯示按照不同的實際要求使用我們前面所提到的圖表。比如說對于等距數(shù)列，我們關(guān)心的是頻數(shù)或頻率，如果是不等距數(shù)列，我們關(guān)心的是頻數(shù)密度或頻率密度，則將相應(yīng)的數(shù)值算出來，然后做圖。第三章計算數(shù)值分布特征的指標(biāo) 本章知識要點 集中趨勢的計算 離散趨勢的計算 偏度和峰度的計算31 集中趨勢 算術(shù)平均數(shù)（average函數(shù)） 調(diào)和平均數(shù)（harmean()） 幾何平均數(shù)(geomean() 中位數(shù)（median函數(shù)） 分位數(shù)(四分位數(shù)quartile(ar

17、ray,quart)，百分位數(shù)percentile(array,k) 眾數(shù)（mode函數(shù)）3.2離散趨勢 極差 用max()-min() 四分位差 平均差 avedev() 方差 分為總體方差和樣本方差 （var(),varp()） 標(biāo)準(zhǔn)差（stdev(),stdevp()） 離散系數(shù) 數(shù)據(jù)的標(biāo)準(zhǔn)化3.3分布的偏態(tài)和峰度 偏態(tài)系數(shù)（skew()） 峰度系數(shù)（kurt（）3.4總結(jié)我們除了利用函數(shù)來求得這些數(shù)值以外呢，我們還可以利用excel中的分析工具箱加載宏來快速求得以上的各種數(shù)值即點擊工具/數(shù)據(jù)分析/描述統(tǒng)計。見例3.1。第四章估計和檢驗本章知識要點 掌握幾個常用的分布函數(shù) 抽樣 估計和檢

18、驗 方差分析4.1掌握幾個常用的分布函數(shù)一.正態(tài)分布1. NORMDIST(x,mean,standard_dev,cumulative)其中X 為需要計算其分布的數(shù)值，Mean 分布的算術(shù)平均值。Standard_dev 分布的標(biāo)準(zhǔn)偏差。Cumulative 為一邏輯值，指明函數(shù)的形式。如果 cumulative 為 TRUE，函數(shù) NORMDIST 返回累積分布函數(shù)；如果為 FALSE，返回概率密度函數(shù)?？梢岳蒙鲜龊瘮?shù)和xy散點圖制作正態(tài)分布圖。2. NORMSDIST(z),是用來返回標(biāo)準(zhǔn)正態(tài)累計分布的函數(shù)。3. NORMINV(probability,mean,standard_de

19、v),其中Probability 正態(tài)分布的概率值，Mean分布的算術(shù)平均值，Standard_dev分布的標(biāo)準(zhǔn)偏差。函數(shù)返回指定平均值和標(biāo)準(zhǔn)偏差的正態(tài)累積分布函數(shù)的反函數(shù)。4. NORMSINV(probability)，其中Probability 正態(tài)分布的概率值，返回標(biāo)準(zhǔn)正態(tài)累積分布函數(shù)的反函數(shù)。5. ZTEST(array,0,sigma)其中Array為用來檢驗 0 的數(shù)組或數(shù)據(jù)區(qū)域。0為被檢驗的值。Sigma為樣本總體（已知）的標(biāo)準(zhǔn)偏差，如果省略，則使用樣本標(biāo)準(zhǔn)偏差。函數(shù)(Z檢驗) 返回 z 檢驗的單尾概率值二.t分布1. TDIST(x,degrees_freedom,tails) X 為需要計算分布的數(shù)字，Degrees_freedom為表示自由度的整數(shù)。Tails指明返回的分布函數(shù)是單尾分布還是雙尾分布。如果 tails = 1，函數(shù) TDIST 返回單尾分布。如果 tails = 2，函數(shù) TDIST 返回雙尾分布。即 tails = 1，TDIST 的計算公式為 TDIST = P( X>x )，其中 X 為服從 t 分布的隨機(jī)變量。如果 tails = 2，TDIST 的計算公式為 TDIST = P(|X| > x) = P(X > x or X < -x)。2. TINV()函數(shù)用來返回t分布的t值。3.