使用EXCEL進(jìn)行數(shù)據(jù)分析.ppt

1、使用EXCEL進(jìn)行數(shù)據(jù)分析,主講老師：李玲娟,數(shù)據(jù)分析與數(shù)據(jù)挖掘,數(shù) 據(jù),信 息,統(tǒng)計分析（報表）,數(shù)據(jù)挖掘（專業(yè)工具和算法）,掌握EXCEL的常用統(tǒng)計函數(shù)； 熟悉使用EXCEL透視表進(jìn)行數(shù)據(jù)分析和分類匯總； 了解使用EXCEL進(jìn)行數(shù)據(jù)分析,教學(xué)要求,實驗環(huán)境,版本：EXCEL 2000 完全安裝 典型安裝 最小化安裝,講授內(nèi)容,常用統(tǒng)計函數(shù) 數(shù)據(jù)透視分析 圖表 數(shù)據(jù)透視表(圖) 數(shù)據(jù)分析 描述統(tǒng)計 檢驗分析 z-檢驗：雙樣本平均差檢驗 F-檢驗：雙樣本方差 t-檢驗：雙樣本等方差假設(shè) t-檢驗：雙樣本異方差假設(shè) t-檢驗：平均值的成對二樣本分析,方差分析 單因素方差分析 無重復(fù)雙因素分析 可

2、重復(fù)雙因素分析 相關(guān)系數(shù)與回歸分析 協(xié)方差 相關(guān)系數(shù) 回歸分析,常用統(tǒng)計函數(shù),一般統(tǒng)計函數(shù)：用來統(tǒng)計給定某個區(qū)域的數(shù)據(jù)的平均值、最大值或者最小值等,數(shù)理統(tǒng)計函數(shù)：對數(shù)據(jù)進(jìn)行相關(guān)的概率分 布的統(tǒng)計，進(jìn)行回歸分析等。,應(yīng)用舉例一,某班級的成績表，如下圖所示，其中有兩個學(xué)生的該門課程缺考，需要分別用到AVERAGE和AVERAGEA函數(shù)來計算兩個不同的平均值。,【操作步驟】,在單元格D3中輸入公式“=AVERAGE(B2:B9)”，可以計算參加考試學(xué)生的平均成績，函數(shù)AVERAGE在計算時對缺考的兩個學(xué)生的成績自動認(rèn)為是0。 在單元格D6中輸入公式“=AVERAGEA(B2:B9)”，可以計算全體學(xué)

3、生的平均成績，對缺考的學(xué)生成績，AVERAGE函數(shù)將自動忽略。,應(yīng)用舉例二,某班級的成績表，如下圖所示。需要用到FREQUENCY函數(shù)來來統(tǒng)計各個分?jǐn)?shù)段的分布學(xué)生人數(shù)。,FREQUENCY函數(shù),功能：統(tǒng)計指定單元格區(qū)域中數(shù)據(jù)的頻率分布。由于函數(shù) FREQUENCY 返回一個數(shù)組，必須以數(shù)組公式的形式輸入。 語法形式: FREQUENCY(data_array,bins_array) data_array為一數(shù)組或?qū)σ唤M數(shù)值的引用，用來計算頻率。如果 data_array 中不包含任何數(shù)值，函數(shù) FREQUENCY 返回零數(shù)組。 bins_array為一數(shù)組或?qū)?shù)組區(qū)域的引用，設(shè)定對 data_

4、array 進(jìn)行頻率計算的分段點。如果 bins_array 中不包含任何數(shù)值，函數(shù) FREQUENCY 返回 data_array 元素的數(shù)目。,【操作步驟】,在E4E7單元格輸入進(jìn)行頻率計算的分段點59,70,80,90； 選中F4F8單元格,作為輸出統(tǒng)計結(jié)果的單元格； 在公式欄輸入“=FREQUENCY(B2:B11,E4:E8)”，然后按下“CtrlShiftEnter” 組合鍵鎖定數(shù)組公式即可。,輸入數(shù)組公式首先必須選擇用來存放結(jié)果的單元格區(qū)域（可以是一個單元格），在編輯欄輸入公式，然后按CtrlShiftEnter組合鍵鎖定數(shù)組公式，Excel將在公式兩邊自動加上花括號“ ”。,C

5、ORREL函數(shù),功能是單元格區(qū)域中的兩個數(shù)列之間的相關(guān)系數(shù)。 表達(dá)式為CORREL（arrnyl，arrny2）。 arrnyl和arrny2表示的都是兩個數(shù)據(jù)列 CORREL函數(shù)可以用來分辨兩種屬性之間的關(guān)系。,應(yīng)用舉例三,某醫(yī)生為了探討缺碘地區(qū)母嬰TSH水平關(guān)系，應(yīng)用免疫放射分析測定了160名孕婦（孕周1517w）及分娩時臍帶血TSH水平（mU/L），現(xiàn)隨機抽取10對數(shù)據(jù)如圖2-8所示，試求臍帶血TSH水平對母血TSH水平的相關(guān)系數(shù)。,【操作步驟】,選中結(jié)果輸出單元格B14； 在公式欄輸入“=CORREL(C3:C12,B3:B12)”,點擊公式欄 按鈕； 在單元格A14輸入“相關(guān)系數(shù)”即

6、可。,應(yīng)用舉例四,某地120名7歲男童的身高，已知身高均數(shù)為119.41cm，標(biāo)準(zhǔn)差為4.38cm。試估計該地7歲男童身高在110cm以下者的概率，身高在110cm120cm之間的概率。如果要制定一個健康標(biāo)準(zhǔn)使得90的7歲男童符合健康標(biāo)準(zhǔn)，那么健康標(biāo)準(zhǔn)應(yīng)該定多少？,NORMDIST和NORMINV函數(shù),NORMDIST函數(shù) 功能：返回指定平均值和標(biāo)準(zhǔn)偏差的正態(tài)分布函數(shù) 表達(dá)式：NORMDIST（x，mean，standard_dev，cumulative）。 x是需要計算其分布的數(shù)值； mean是分布的算術(shù)平均值： standard_dev表示分布的標(biāo)準(zhǔn)偏差； cumulative是一邏輯值，

7、指明函數(shù)的形式。如果cumulative為TRUE，NORMDIST函數(shù)返回累積分布函數(shù)，否則返回概率密度函數(shù)。此函數(shù)在統(tǒng)計方面應(yīng)用范圍廣泛。 NORMINV函數(shù) 功能：返回指定平均值和標(biāo)準(zhǔn)偏差的正態(tài)累積分布函數(shù)的反函數(shù)。 表達(dá)式：NORMINV（probability，mean，standard_dev） probability是正態(tài)分布的概率； mean是分布的算術(shù)平均值； standard_dev表示分布的標(biāo)準(zhǔn)偏差。,應(yīng)用舉例五,某地120名7歲男童的身高，已知身高均數(shù)為119.41cm，標(biāo)準(zhǔn)差為4.38cm, 如下圖所示。試估計該地7歲男童身高在110cm以下者的概率，身高在110cm

8、120cm之間的概率。如果要制定一個健康標(biāo)準(zhǔn)使得90的7歲男童符合健康標(biāo)準(zhǔn)，那么健康標(biāo)準(zhǔn)應(yīng)該定多少？,【操作步驟】,在單元格C5中輸入公式“=NORMDIST(110,B2,B3,TRUE)”，計算身高在110cm以下者的概率為0.015841； 在單元格C6中輸入公式“=NORMDIST(120,B2,B3,TRUE)-C5”，計算成功身高在110cm120cm之間的概率為0.537736； 在單元格C7中輸入公式“=NORMINV(0.9,B2,B3)”，計算使90的7歲男童身高達(dá)標(biāo)的健康標(biāo)準(zhǔn)為125.0232cm。,分析結(jié)果,=NORMDIST(110,B1,B2,TRUE),=NORM

9、DIST(120,B1,B2,TRUE)-C5,=NORMINV(0.9,B2,B3),圖表,概念：工作表數(shù)據(jù)的圖形化表示、清晰易懂、形象直觀。 種類：14種圖表類型，包含了若干種不同的圖表子類型。在處理醫(yī)藥數(shù)據(jù)中常用的幾類圖表： 1、折線圖 2、柱形圖 3、餅圖 4、XY散點圖 5、條形圖 6、雷達(dá)圖,柱形圖,柱形圖是Excel 的默認(rèn)圖表類型，也是用來處理醫(yī)藥數(shù)據(jù)常用的圖表類型之一。通常把每個數(shù)據(jù)點顯示為一個垂直柱體，其高度對應(yīng)于數(shù)值，用來顯示一段時期內(nèi)數(shù)據(jù)的變化或描述各項之間的比較關(guān)系。分類項水平組織，數(shù)值垂直組織，即強調(diào)數(shù)據(jù)隨時間的變化。 柱形圖共有7 種子圖表類型：簇狀柱形圖、堆積柱

10、形圖、百分比堆積柱形圖、三維簇狀柱形圖、三維堆積柱形圖、三維百分比堆積柱形圖和三維柱形圖。,應(yīng)用舉例五,在藥物實驗中的應(yīng)用，現(xiàn)有藥物止血效果實驗采集的數(shù)據(jù)表，A列為實驗對照組和七種不同的止血藥物，分別做了六次藥物止血實驗，得到六組凝血時間實驗數(shù)據(jù)（單位：秒）。通過柱形圖可以形象地描繪了這七種不同藥物止血功能的差異。,返回,【操作步驟】,(1)選擇圖表類型，單擊“插入”|“圖表”。 (2)選擇圖表源數(shù)據(jù)。 (3)設(shè)置標(biāo)題和圖例。 (4)確定圖表位置。 (5)編輯圖表區(qū)。用戶需要編輯的內(nèi)容包括顏色、填充和邊框等 。 (6)編輯數(shù)據(jù)軸、分類軸。,雷達(dá)圖,雷達(dá)圖是由一個中心向四周射出多條數(shù)值坐標(biāo)軸，每

11、個指標(biāo)都擁有自己的數(shù)值坐標(biāo)軸，把同一數(shù)據(jù)序列的值用折線連接起來而形成。雷達(dá)圖用來比較若干個數(shù)據(jù)序列指標(biāo)的總體情況，可比較多個單位不同指標(biāo)的數(shù)據(jù)特征，如最大值、最小值等。雷達(dá)圖的3 個子圖表類型分別為雷達(dá)圖、數(shù)據(jù)點雷達(dá)圖和填充雷達(dá)圖。,為了反映醫(yī)院管理的質(zhì)量，某市抽樣調(diào)查了幾所醫(yī)院的6 項指標(biāo)（指標(biāo)值越大表示質(zhì)量越高），其中三所醫(yī)院的調(diào)查結(jié)果如圖2-65所示，請繪制雷達(dá)圖反映三所醫(yī)院的管理質(zhì)量。,應(yīng)用舉例六,數(shù)據(jù)透視分析,數(shù)據(jù)透視表 數(shù)據(jù)透視圖,數(shù)據(jù)透視表,數(shù)據(jù)透視表是一種對大量數(shù)據(jù)快速匯總和建立交叉列表的交互式表格和圖表。 在Excel50以上版本就提供有這一功能，它不僅可以轉(zhuǎn)換行和列以查看源

12、數(shù)據(jù)的不同匯總結(jié)果，顯示不同頁面以篩選數(shù)據(jù)，還可以根據(jù)需要顯示區(qū)域中的明細(xì)數(shù)據(jù)，這對分析、組織復(fù)雜數(shù)據(jù)是非常方便有用的。 數(shù)據(jù)透視表最大的特點就是它的交互性。創(chuàng)建一個數(shù)據(jù)透視表以后，可以任意重新排列數(shù)據(jù)信息，并且還可以根據(jù)習(xí)慣將數(shù)據(jù)分組。,數(shù)據(jù)透視表布局,橫向分類依據(jù)的字段,縱向分類依據(jù)的字段,統(tǒng)計依據(jù)的字段,分類顯示（篩選）依據(jù)的字段,應(yīng)用舉例七,以下圖數(shù)據(jù)為例分析藥材的分布、產(chǎn)量情況。,數(shù)據(jù)透視表的制作,選擇“數(shù)據(jù)”菜單中的“數(shù)據(jù)透視表和數(shù)據(jù)透視圖” 選項，彈出 “數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂υ捒?，按步驟依次完成。 編輯數(shù)據(jù)透視表 添加、刪除分類和統(tǒng)計字段 改變統(tǒng)計字段的算法 修改、刪除

13、數(shù)據(jù)透視表 更新數(shù)據(jù)透視表中的數(shù)據(jù),數(shù)據(jù)透視圖,根據(jù)透視表建立透視圖:選擇數(shù)據(jù)透視表中的任意單元格，然后在“數(shù)據(jù)透視表”工具欄上單擊“數(shù)據(jù)透視表”按鈕，單擊“數(shù)據(jù)透視圖”命令，。,數(shù)據(jù)透視圖,根據(jù)源數(shù)據(jù)建立數(shù)據(jù)透視圖：根據(jù)數(shù)據(jù)庫的源數(shù)據(jù)建立透視圖的方法類似于創(chuàng)建數(shù)據(jù)透視表，具體操作步驟如下： (1)單擊欲建立數(shù)據(jù)透視圖的數(shù)據(jù)清單。 (2) 選擇“數(shù)據(jù)”菜單中的“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”選項，在彈出的對話框中選擇“數(shù)據(jù)透視圖”，按步驟依次完成。,返回,數(shù)據(jù)統(tǒng)計與分析,描述統(tǒng)計 檢驗分析 z-檢驗：雙樣本平均差檢驗 F-檢驗：雙樣本方差 t-檢驗：雙樣本等方差假設(shè) t-檢驗：雙樣本異方差假設(shè) t-

14、檢驗：平均值的成對二樣本分析 方差分析 單因素方差分析 無重復(fù)雙因素分析 可重復(fù)雙因素分析 相關(guān)系數(shù)與回歸分析 協(xié)方差 相關(guān)系數(shù) 回歸分析,統(tǒng)計檢驗計算,檢驗分析報告,“分析工具庫”的加載,其基本步驟如下： 1、啟動Excel2000，執(zhí)行菜單欄中的“工具”|“加載宏”命令，打開“加載宏”對話框，選中“分析工具庫”復(fù)選框。 2、單擊“確定”按鈕，返回到工作表。執(zhí)行菜單欄的“工具”|“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對話框 ，即可使用各種數(shù)據(jù)分析工具。 注意：EXCEL必須是“完全安裝”,描述統(tǒng)計,“描述統(tǒng)計”是基礎(chǔ)分析工具之一，用于生成對輸入?yún)^(qū)域中數(shù)據(jù)的單變量統(tǒng)計分析報表，提供有關(guān)數(shù)據(jù)趨中性和

15、易變性的信息，而這些信息是進(jìn)一步分析數(shù)據(jù)的起點，可以指示哪一種檢驗適合進(jìn)一步的探討。 描述統(tǒng)計的統(tǒng)計函數(shù)一般包括：MAX、MIN、AVERAGE、MEDIAN、GEOMEAN、HARMEAN、AVEDEV、STDEV、VAR、KURT和SKEW函數(shù)等，他們分別用于計算最大值、最小值、平均值、中位數(shù)、幾何平均數(shù)、調(diào)和平均數(shù)，以及變異統(tǒng)計的平均差、標(biāo)準(zhǔn)差、方差峰度和偏度。,應(yīng)用舉例八,某老師對幾名學(xué)生的五次考試成績進(jìn)行分析，以便對這幾個學(xué)生在學(xué)習(xí)方面存在的問題加以解決。使用“描述統(tǒng)計”分析工具對其數(shù)據(jù)進(jìn)行分析。,返回,【操作步驟】,1、執(zhí)行菜單欄的“工具”|“數(shù)據(jù)分析”命令，打開“描述統(tǒng)計”對話框

16、，選中“分析工具”列表框中的“描述統(tǒng)計”選項，單擊“確定”按鈕，打開“描述統(tǒng)計”對話框。 3、在“輸入?yún)^(qū)域”編輯框中鍵入三列數(shù)據(jù)所在的單元格區(qū)域引用（$A$4:$F$8）。 4、單擊“逐行”選項。 5、選中“標(biāo)志位于第一列”選項。 6、在“輸出選項”下單擊“新工作表組”選項，并在對應(yīng)編輯框中輸入新工作表的名稱，如：“描述統(tǒng)計結(jié)果”。,t-檢驗：平均值的成對雙樣本分析,“t-檢驗：平均值的成對雙樣本分析”分析工具及其公式可以進(jìn)行成對雙樣本t-檢驗，用來確定樣本平均值是否不等。此t-檢驗窗體并不假設(shè)兩個總體的方差是相等的。當(dāng)樣本中出現(xiàn)自然配對的觀察值時，可以使用這種成對檢驗。例如，對一個樣本組進(jìn)行

17、了兩次檢驗，抽取實驗前的一次和實驗后的一次。,應(yīng)用舉例九,某醫(yī)院用中藥治療9例再生障礙性貧血，結(jié)果如下圖所示。試用“t-檢驗：平均值的成對二樣本分析”工具，分析治療前后是否有改變？,返回,【操作步驟】,（1）在Excel2003，創(chuàng)建一個新的Excel工作薄，如圖2-108所示輸入需要分析的數(shù)據(jù)； （2）執(zhí)行菜單欄中的“工具”|“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對話框，選中“分析工具”列表框中的“t-檢驗：平均值的成對二樣本分析”選項。,單因素方差分析,該分析工具通過簡單的方差分析，對兩個以上樣本平均值進(jìn)行相等性假設(shè)檢驗（樣本取自具有相同平均值的樣本總體）。此方法是對雙平均值檢驗（如t-檢驗）

18、的擴充。,應(yīng)用舉例十,某醫(yī)院用三種方法治療某種疾病，結(jié)果如下表2-2所示，試問三組治愈天數(shù)有無差別？,返回,返回,【操作步驟】,（1）執(zhí)行菜單欄中的“工具”|“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對話框，選中“分析工具”列表框中的“方差分析：單因素方差分析”選項； （2）單擊“確定”按鈕，執(zhí)行“方差分析：單因素方差分析”對話框；,協(xié)方差,“協(xié)方差”工具用來度量兩個區(qū)域中數(shù)據(jù)的關(guān)系，用于返回各數(shù)據(jù)點與其各自所在數(shù)據(jù)組的平均值之間的偏差乘積的平均值。 使用協(xié)方差工具可以用來確定兩個區(qū)域中數(shù)據(jù)的變化是否相關(guān)，即一個集合的較大數(shù)據(jù)是否與另一個集合的較大數(shù)據(jù)相對應(yīng)（正協(xié)方差）；或者一個集合的較小數(shù)據(jù)是否與另一個集合的較大數(shù)據(jù)相對應(yīng)（負(fù)協(xié)方差）；還是兩個集合中的數(shù)據(jù)互不相關(guān)（協(xié)方差為零）。協(xié)方差對度量單位是敏感的。,18名兒童身高、體重、表面積測定數(shù)據(jù)如下圖所示，試?yán)谩皡f(xié)方差”分析工具分析身高、體重、表面積三個指標(biāo)之間的關(guān)系。,應(yīng)用舉例十一,返回,【操作步驟】,（1）執(zhí)行菜單欄中的“工具”|“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對話框，選中“分析工具”列表框中的“協(xié)方差”選項； （2）單擊“確定”按鈕，執(zhí)行“協(xié)方差”對話框。,相關(guān)系數(shù),用于度量兩個數(shù)據(jù)集（可以使用不同的度量單位）之間的相關(guān)性，與兩組數(shù)據(jù)的相關(guān)分析和度量單位無關(guān)?？傮w相關(guān)性計算的返回值為兩組數(shù)據(jù)集的協(xié)方差除以