使用EXCEL進行數(shù)據(jù)分析

使用EXCEL進行數(shù)據(jù)分析

主講老師：李玲娟數(shù)據(jù)分析與數(shù)據(jù)挖掘數(shù)據(jù)信息統(tǒng)計分析（報表）數(shù)據(jù)挖掘（專業(yè)工具和算法）掌握EXCEL的常用統(tǒng)計函數(shù)；熟悉使用EXCEL透視表進行數(shù)據(jù)分析和分類匯總；了解使用EXCEL進行數(shù)據(jù)分析教學要求實驗環(huán)境

版本：EXCEL2000完全安裝典型安裝最小化安裝講授內容常用統(tǒng)計函數(shù)數(shù)據(jù)透視分析圖表數(shù)據(jù)透視表(圖)數(shù)據(jù)分析描述統(tǒng)計檢驗分析z-檢驗：雙樣本平均差檢驗

F-檢驗：雙樣本方差

t-檢驗：雙樣本等方差假設t-檢驗：雙樣本異方差假設t-檢驗：平均值的成對二樣本分析方差分析單因素方差分析無重復雙因素分析可重復雙因素分析

相關系數(shù)與回歸分析

協(xié)方差相關系數(shù)回歸分析

常用統(tǒng)計函數(shù)一般統(tǒng)計函數(shù)：用來統(tǒng)計給定某個區(qū)域的數(shù)據(jù)的平均值、最大值或者最小值等

數(shù)理統(tǒng)計函數(shù)：對數(shù)據(jù)進行相關的概率分布的統(tǒng)計，進行回歸分析等。應用舉例一某班級的成績表，如下圖所示，其中有兩個學生的該門課程缺考，需要分別用到AVERAGE和AVERAGEA函數(shù)來計算兩個不同的平均值?！静僮鞑襟E】①在單元格D3中輸入公式“=AVERAGE(B2:B9)”，可以計算參加考試學生的平均成績，函數(shù)AVERAGE在計算時對缺考的兩個學生的成績自動認為是0。②在單元格D6中輸入公式“=AVERAGEA(B2:B9)”，可以計算全體學生的平均成績，對缺考的學生成績，AVERAGE函數(shù)將自動忽略。應用舉例二某班級的成績表，如下圖所示。需要用到FREQUENCY函數(shù)來來統(tǒng)計各個分數(shù)段的分布學生人數(shù)。FREQUENCY函數(shù)功能：統(tǒng)計指定單元格區(qū)域中數(shù)據(jù)的頻率分布。由于函數(shù)FREQUENCY返回一個數(shù)組，必須以數(shù)組公式的形式輸入。語法形式:FREQUENCY(data_array,bins_array)data_array為一數(shù)組或對一組數(shù)值的引用，用來計算頻率。如果data_array中不包含任何數(shù)值，函數(shù)FREQUENCY返回零數(shù)組。bins_array為一數(shù)組或對數(shù)組區(qū)域的引用，設定對data_array進行頻率計算的分段點。如果bins_array中不包含任何數(shù)值，函數(shù)FREQUENCY返回data_array元素的數(shù)目。【操作步驟】①在E4~E7單元格輸入進行頻率計算的分段點{59,70,80,90}；②選中F4~F8單元格,作為輸出統(tǒng)計結果的單元格；③在公式欄輸入“=FREQUENCY(B2:B11,E4:E8)”，然后按下“Ctrl＋Shift＋Enter”組合鍵鎖定數(shù)組公式即可。輸入數(shù)組公式首先必須選擇用來存放結果的單元格區(qū)域（可以是一個單元格），在編輯欄輸入公式，然后按Ctrl＋Shift＋Enter組合鍵鎖定數(shù)組公式，Excel將在公式兩邊自動加上花括號“{}”。CORREL函數(shù)功能是單元格區(qū)域中的兩個數(shù)列之間的相關系數(shù)。表達式為CORREL（arrnyl，arrny2）。arrnyl和arrny2表示的都是兩個數(shù)據(jù)列CORREL函數(shù)可以用來分辨兩種屬性之間的關系。應用舉例三某醫(yī)生為了探討缺碘地區(qū)母嬰TSH水平關系，應用免疫放射分析測定了160名孕婦（孕周15～17w）及分娩時臍帶血TSH水平（mU/L），現(xiàn)隨機抽取10對數(shù)據(jù)如圖2-8所示，試求臍帶血TSH水平對母血TSH水平的相關系數(shù)。【操作步驟】①選中結果輸出單元格B14；②在公式欄輸入“=CORREL(C3:C12,B3:B12)”,點擊公式欄按鈕；③在單元格A14輸入“相關系數(shù)”即可。應用舉例四某地120名7歲男童的身高，已知身高均數(shù)為119.41cm，標準差為4.38cm。試估計該地7歲男童身高在110cm以下者的概率，身高在110cm～120cm之間的概率。如果要制定一個健康標準使得90％的7歲男童符合健康標準，那么健康標準應該定多少？

NORMDIST和NORMINV函數(shù)

NORMDIST函數(shù)功能：返回指定平均值和標準偏差的正態(tài)分布函數(shù)表達式：NORMDIST（x，mean，standard_dev，cumulative）。x是需要計算其分布的數(shù)值；mean是分布的算術平均值：standard_dev表示分布的標準偏差；cumulative是一邏輯值，指明函數(shù)的形式。如果cumulative為TRUE，NORMDIST函數(shù)返回累積分布函數(shù)，否則返回概率密度函數(shù)。此函數(shù)在統(tǒng)計方面應用范圍廣泛。NORMINV函數(shù)功能：返回指定平均值和標準偏差的正態(tài)累積分布函數(shù)的反函數(shù)。表達式：NORMINV（probability，mean，standard_dev）probability是正態(tài)分布的概率；mean是分布的算術平均值；standard_dev表示分布的標準偏差。應用舉例五某地120名7歲男童的身高，已知身高均數(shù)為119.41cm，標準差為4.38cm,如下圖所示。試估計該地7歲男童身高在110cm以下者的概率，身高在110cm～120cm之間的概率。如果要制定一個健康標準使得90％的7歲男童符合健康標準，那么健康標準應該定多少？【操作步驟】①在單元格C5中輸入公式“=NORMDIST(110,B2,B3,TRUE)”，計算身高在110cm以下者的概率為0.015841；②在單元格C6中輸入公式“=NORMDIST(120,B2,B3,TRUE)-C5”，計算成功身高在110cm～120cm之間的概率為0.537736；③在單元格C7中輸入公式“=NORMINV(0.9,B2,B3)”，計算使90％的7歲男童身高達標的健康標準為125.0232cm。分析結果=NORMDIST(110,B1,B2,TRUE)=NORMDIST(120,B1,B2,TRUE)-C5=NORMINV(0.9,B2,B3)圖表概念：工作表數(shù)據(jù)的圖形化表示、清晰易懂、形象直觀。種類：14種圖表類型，包含了若干種不同的圖表子類型。在處理醫(yī)藥數(shù)據(jù)中常用的幾類圖表：1、折線圖2、柱形圖3、餅圖4、XY散點圖5、條形圖6、雷達圖柱形圖柱形圖是Excel的默認圖表類型，也是用來處理醫(yī)藥數(shù)據(jù)常用的圖表類型之一。通常把每個數(shù)據(jù)點顯示為一個垂直柱體，其高度對應于數(shù)值，用來顯示一段時期內數(shù)據(jù)的變化或描述各項之間的比較關系。分類項水平組織，數(shù)值垂直組織，即強調數(shù)據(jù)隨時間的變化。柱形圖共有7種子圖表類型：簇狀柱形圖、堆積柱形圖、百分比堆積柱形圖、三維簇狀柱形圖、三維堆積柱形圖、三維百分比堆積柱形圖和三維柱形圖。。應用舉例五在藥物實驗中的應用，現(xiàn)有藥物止血效果實驗采集的數(shù)據(jù)表，A列為實驗對照組和七種不同的止血藥物，分別做了六次藥物止血實驗，得到六組凝血時間實驗數(shù)據(jù)（單位：秒）。通過柱形圖可以形象地描繪了這七種不同藥物止血功能的差異。

返回【操作步驟】(1)選擇圖表類型，單擊“插入”|“圖表”。

(2)選擇圖表源數(shù)據(jù)。

(3)設置標題和圖例。

(4)確定圖表位置。

(5)編輯圖表區(qū)。用戶需要編輯的內容包括顏色、填充和邊框等。(6)編輯數(shù)據(jù)軸、分類軸。雷達圖雷達圖是由一個中心向四周射出多條數(shù)值坐標軸，每個指標都擁有自己的數(shù)值坐標軸，把同一數(shù)據(jù)序列的值用折線連接起來而形成。雷達圖用來比較若干個數(shù)據(jù)序列指標的總體情況，可比較多個單位不同指標的數(shù)據(jù)特征，如最大值、最小值等。雷達圖的3個子圖表類型分別為雷達圖、數(shù)據(jù)點雷達圖和填充雷達圖。

為了反映醫(yī)院管理的質量，某市抽樣調查了幾所醫(yī)院的6項指標（指標值越大表示質量越高），其中三所醫(yī)院的調查結果如圖2-65所示，請繪制雷達圖反映三所醫(yī)院的管理質量。應用舉例六數(shù)據(jù)透視分析數(shù)據(jù)透視表數(shù)據(jù)透視圖

數(shù)據(jù)透視表數(shù)據(jù)透視表是一種對大量數(shù)據(jù)快速匯總和建立交叉列表的交互式表格和圖表。在Excel5．0以上版本就提供有這一功能，它不僅可以轉換行和列以查看源數(shù)據(jù)的不同匯總結果，顯示不同頁面以篩選數(shù)據(jù)，還可以根據(jù)需要顯示區(qū)域中的明細數(shù)據(jù)，這對分析、組織復雜數(shù)據(jù)是非常方便有用的。數(shù)據(jù)透視表最大的特點就是它的交互性。創(chuàng)建一個數(shù)據(jù)透視表以后，可以任意重新排列數(shù)據(jù)信息，并且還可以根據(jù)習慣將數(shù)據(jù)分組。數(shù)據(jù)透視表－布局橫向分類依據(jù)的字段縱向分類依據(jù)的字段統(tǒng)計依據(jù)的字段分類顯示（篩選）依據(jù)的字段應用舉例七以下圖數(shù)據(jù)為例分析藥材的分布、產(chǎn)量情況。數(shù)據(jù)透視表的制作選擇“數(shù)據(jù)”菜單中的“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”選項，彈出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向導”對