Excel軟件在_試驗設(shè)計與分析_課程教學(xué)中的應(yīng)用_圖文

1、第17卷 第2期 天 津 農(nóng) 學(xué) 院 學(xué) 報 V ol.17,No.2 2010年6月 Journal of Tianjin Agricultural University June ,2010*收稿日期:2009-10-10基金項目:中國農(nóng)業(yè)大學(xué)2008年研究生重點課程建設(shè)項目“多元統(tǒng)計分析及其應(yīng)用重點課程建設(shè)”(KC-0804作者簡介:劉旭華(1979-,男,山東煙臺人,講師,博士在讀,研究方向為應(yīng)用統(tǒng)計。E-mail :liuxuhua 。 文章編號:1008-5394(201002-0036-04Excel 軟件在試驗設(shè)計與分析課程教學(xué)中的應(yīng)用劉旭華1,杜曉林2,張錄達1,通訊作者(1

2、. 中國農(nóng)業(yè)大學(xué) 理學(xué)院,北京100193;2. 北京農(nóng)學(xué)院 基礎(chǔ)科學(xué)系,北京 102206摘 要:根據(jù)試驗設(shè)計與分析課程中設(shè)計表格多、數(shù)據(jù)分析規(guī)律性強的特點,利用Excel 軟件的表格化和強大的統(tǒng)計計算功能,在Excel 中制作試驗設(shè)計與數(shù)據(jù)分析模板,實現(xiàn)設(shè)計與數(shù)據(jù)分析過程動態(tài)可視化,能夠極大地提高教學(xué)效率,并方便實際應(yīng)用。本文以一個二水平正交設(shè)計為例制作正交表L 8(27的應(yīng)用模板,說明了模板的制作與應(yīng)用過程。 關(guān)鍵詞:試驗設(shè)計與分析;Excel ;教學(xué);模板中圖分類號:O212 文獻標(biāo)識碼:BApplication of Excel Software in TeachingExperim

3、ental Design and AnalysisLIU Xu-hua 1,DU Xiao-lin 2,ZHANG Lu-da 1(1. College of Science ,China Agriculture University ,Beijing 100083,China ;2. Department of Basic Science ,BeijingAgricultural College ,Beijing 102206,China Abstract: In the course of Experimental Design and Analysis ,there are many f

4、orms for experiments' design ,but data analysis of the designs has much regularity. Based on these characters of the course ,using Excel's powerful form and statistical calculation functions ,the experimental design and data analysis template in Excel is produced ,the dynamic visualization o

5、f the design and data analysis realized ,then the teaching efficiency is greatly improved ,and the practical application is facilitated. In this article ,it is adopted a two-level orthogonal design as an example ,and the template of the orthogonal design table L 8(27is produced ,the process of templ

6、ate's production and application is demonstrated. Key words: Experimental Design and Analysis; Excel; teaching; template1 引言試驗設(shè)計與分析是農(nóng)學(xué)、工學(xué)等專業(yè)學(xué)生的一門非常重要的實用型課程,這門課程的特點之一是設(shè)計表格多、計算過程繁雜,但是數(shù)據(jù)分析的規(guī)律性強1-2。在教學(xué)過程中,結(jié)合這些特點,采用適當(dāng)?shù)姆椒?可以讓學(xué)生更易于接受所講的內(nèi)容。Microsoft Office 中的電子表格Excel 是一個技術(shù)先進、使用方便的表格式數(shù)據(jù)綜合管理和分析系統(tǒng),它提供了許多高

7、級的功能和豐富的函數(shù)來進行數(shù)理統(tǒng)計中的數(shù)據(jù)運算與分析3。一些基本的統(tǒng)計方法及其運算(如方差分析、線性回歸分析等在Excel 中已有函數(shù)或命令實現(xiàn),但對試驗設(shè)計與分析來講還不能夠直接運用,而且輸入數(shù)據(jù)以后無法自動生成相應(yīng)的結(jié)果,即原始數(shù)據(jù)與運算結(jié)果之間沒有實現(xiàn)動態(tài)鏈接,尤其是當(dāng)數(shù)據(jù)輸入有誤,必須進行重新運算時。這些缺點非常明顯,為此,可充分利用Excel 的表格化、可視化及使用方便等優(yōu)點,在試驗設(shè)計與分析的教學(xué)中,把試驗設(shè)計中的正交設(shè)計、回歸設(shè)計等涉及到的眾多表格存入Excel ,利用Excel 強大的數(shù)據(jù)運算功能,實現(xiàn)設(shè)計表格與數(shù)據(jù)分析過程一體化、動態(tài)可視化,從而便于在教學(xué)過程中利用,以達到既

8、節(jié)省了時間,又易于學(xué)生實踐運用的目的。第2期 劉旭華,等:Excel 軟件在試驗設(shè)計與分析課程教學(xué)中的應(yīng)用 ·37·2 利用Excel 制作試驗設(shè)計與數(shù)據(jù)分析的模板及其應(yīng)用要實現(xiàn)設(shè)計表格與數(shù)據(jù)分析過程的動態(tài)可視化,關(guān)鍵是要實現(xiàn)模板化。我們通過一個例子來說明如何實現(xiàn)試驗設(shè)計表格與數(shù)據(jù)分析過程的模板化以及模板如何使用。例:為提高某種農(nóng)藥的收率,需要進行實驗。經(jīng)分析,影響農(nóng)藥收率的因子有4個,分別是反應(yīng)溫度A 、反應(yīng)時間B 、原料配比C 、真空度D 。根據(jù)經(jīng)驗可知,反應(yīng)溫度與反應(yīng)時間對收率的交互作用較大,因此,本實驗還要考慮交互作用A ×B ,4個因子及其水平表如下。表

9、1 因子及水平表因子 一水平二水平反應(yīng)溫度A / 60 80 反應(yīng)時間B /h 2.5 3.5 原料配比C 1.1/1 1.2/1 真空度D /kPa50 602.1 建立Excel 模板文件,完成正交表的基本內(nèi)容處理該問題的第一步是選取合適的正交表,并進行表頭設(shè)計。該實驗有4個兩水平因子,還要考慮一個交互作用,因此,選用二水平正交表L 8(27則可。在Excel 中新建一個表格文件,以1和-1分別表示因子的兩個不同水平,將二水平正交表L 8(27輸入到表格中,如圖1中單元格A 1到H 11所示。圖1中的I 列和J 列用來記錄實驗的結(jié)果,由于可能有重復(fù)實驗或?qū)嶒炗卸鄠€指標(biāo),所以應(yīng)預(yù)先留出適當(dāng)?shù)?/p>

10、位置。A 12至H 12用來進行表頭設(shè)計。A 13至H 14用來記錄因素的1和-1水平的實際值。 圖1 二水平正交設(shè)計表L 8(27與直觀分析結(jié)果下面考慮利用該正交設(shè)計表進行實驗后所得實驗數(shù)據(jù)的直觀分析和方差分析。直觀分析和方差分析中相關(guān)的數(shù)據(jù)計算表達式可參考文獻1、2的相關(guān)章節(jié)。2.2 在Excel 模板中實現(xiàn)直觀分析中的相關(guān)運算直觀分析表見圖1中的A 15到J 19,T 1表示該列1水平對應(yīng)的實驗指標(biāo)的和值,T -1表示該列-1水平對應(yīng)的實驗指標(biāo)的和值,以正交表第一列為例,在B 16中輸入函數(shù)表達式“=$I$4+$I$5+$I$6+$I$7”,B 17中輸入“=$I$8+$I$9+$I$1

11、0+$I$11”,C 16至H 17等列可類似輸入。表中極差R 表示T 1 、T -1之差的絕對值,如B 18中輸入“=ABS (B16-B17”,C 18至H 18可類似輸入;更簡單的方法是單擊B 18,將鼠標(biāo)放在B 18的右下角,鼠標(biāo)指針變成黑十字時,按住并向右拖動至H 18,則C 18至H 18可自動生成所需要的結(jié)果。SS 表示該列的平方和,具體的算法是在B 19中輸入“=B182/8”,C 19至H 19類似輸入。至此就完成了直觀分析表的建立。天津農(nóng)學(xué)院學(xué)報第17卷·38·2.3 在Excel模板中實現(xiàn)方差分析中的相關(guān)運算通過直觀分析,可以大致了解哪些因素或交互作用

12、的影響比較顯著,但是要得到更多的理論支持,還需要進行方差分析。方差分析的模板化需要具體問題具體分析,因為要根據(jù)表頭設(shè)計的結(jié)果來確定因素和誤差的偏差平方和,但有了上述直觀分析的結(jié)果,計算方差分析表中的相應(yīng)數(shù)據(jù)并不困難,我們?nèi)砸陨侠f明一般的做法。方差分析的第一行應(yīng)該列出偏差來源、偏差平方和(SS、自由度(dF、均方和(MS、F比、p值和是否顯著等項,如圖2中A24到G24所示。本例中偏差來源有6部分,因素A、B、C、D和交互作用A×B,再加上誤差項,分別在A25到A30輸入,其中前五項,即A、B、交互作用A×B、C、D對應(yīng)的SS分別是正交表中1、2、3、4、7列對應(yīng)的SS,如

13、直接在B25中輸入“=B19”,類此輸入其他幾項,而誤差的偏差平方和SS則對應(yīng)剩下的列的偏差平方和之和,本例應(yīng)在B30中輸入“=F19+G19”,自由度(dF分別輸入各偏差來源對應(yīng)的列的自由度或自由度之和。有了前兩列的結(jié)果,后面的“MS=SS/dF”,如在D25中輸入“=B25/C25”,類似輸入D26至D30;F 比一列中,如E25輸入“=D25/$D$30”,類此依次輸入E26到E29;在p值一列中,是要計算一個F分布的概率,并與預(yù)先設(shè)定的顯著性水平比較大小,判斷該因素是否顯著,如在F25中應(yīng)輸入“=FDIST(E25,C25,$C$30”,該函數(shù)表示一個服從自由度分別為“C25”和“$C

14、$30”的F分布的隨機變量大于E25的概率,同理輸入F26到F29,最后一列給出因素是否顯著的判斷結(jié)論,我們可以利用邏輯函數(shù)來實現(xiàn),在G25中輸入函數(shù)“=IF(F25<=0.05,“顯著”,“不顯著”,表示如果F25,即p值若小于等于預(yù)先設(shè)定的顯著性水平0.05,則輸出“顯著”,否則輸出“不顯著”,使分析結(jié)果一目了然,在G26到G29中可建立類似的邏輯函數(shù)。至此,我們就完成了二水平正交表L8(27的分析模板。此外,為便于查詢,我們還將該表對應(yīng)的交互作用列表放在模板下方,如圖2中A32至H39所示,使模板更加完善。2.4 模板的使用對二水平正交設(shè)計表L8(27而言,幾乎所有用來演示、分析和

15、講解的內(nèi)容都包含在上述模板文件中。比如對本節(jié)開始的例子,先將因素A、B、C、D置入選定的1、2、4、7列中,然后查模板中的交互作用列表可知1、2列的交互作用列,即交互作用A×B應(yīng)放置在第3列,此時可以解釋為何在考慮交互作用時因素所在列不能隨便安排。接下來將各因素的兩個水平分別置入相應(yīng)的空格,見圖1中Excel表格的13、14行,到此試驗設(shè)計部分完成。下面進行數(shù)據(jù)分析。將實驗數(shù)據(jù)輸入到I4至I11,則模板中直觀分析表和方差分析表中的相應(yīng)項自動生成最終的結(jié)果。此時,可以針對表中每個空格并結(jié)合課本相關(guān)的運算表達式進行解釋。當(dāng)實驗數(shù)據(jù)由于輸入錯誤等原因需要進行改動的時候,輸出的分析結(jié)果可以即

16、時做相應(yīng)的改變,實現(xiàn)了動態(tài)鏈接,使用起來非常方便。 圖2 二水平正交設(shè)計表L8(27方差分析與交互作用列表第2期劉旭華,等:Excel軟件在試驗設(shè)計與分析課程教學(xué)中的應(yīng)用·39·以上我們以二水平正交表L8(27為例設(shè)計模板,敘述了模板的制作與應(yīng)用過程。在教學(xué)或?qū)嶋H應(yīng)用中,對每一種設(shè)計均可以根據(jù)具體情況制作相應(yīng)的模板。在課堂上利用統(tǒng)計分析模板講解有如下的優(yōu)點:(1不僅省卻了用大量板書做表格的時間,而且避免了利用PPT課件演示所存在的一屏放不下、需要多次翻頁的問題,這樣可以把更多的時間用在原理的講解上;(2形式直觀,且具有動態(tài)性,即當(dāng)提交分析的數(shù)據(jù)更新后,相應(yīng)的分析結(jié)果立即隨之

17、更新,這一點也更加有利于在實際中應(yīng)用;(3靈活性,即在方差分析表中,使用者可根據(jù)實際情況選擇具體的因素安排,采用不同的模板來實現(xiàn);(4廣泛的適應(yīng)性,即可以很方便地根據(jù)不同設(shè)計表格的特點編制適合應(yīng)用者需要的模板。3 結(jié)束語將Excel與試驗設(shè)計與分析課程的教學(xué)結(jié)合起來,很容易地實現(xiàn)了試驗設(shè)計表格的可視化與統(tǒng)計分析計算結(jié)果的同步性,極大地提高了教學(xué)效率,并且使學(xué)生對試驗設(shè)計的安排有了直觀的理解,收到了很好的效果。另外,將Excel 作為統(tǒng)計軟件用于數(shù)據(jù)分析和統(tǒng)計類課程的實踐教學(xué)中,與應(yīng)用SAS 和SPSS 等統(tǒng)計軟件相比較,它具有一些特殊的優(yōu)勢。首先,該軟件非常普及,有廣大的用戶群;另外,Exce

18、l 的操作使用也較SAS、SPSS 等專業(yè)統(tǒng)計分析軟件簡便易學(xué)得多,容易為非統(tǒng)計專業(yè)的學(xué)生所接受。雖然Excel的統(tǒng)計分析功能較SAS和SPSS少,但通過教學(xué)實踐,我們認為,它基本可以滿足非統(tǒng)計專業(yè)本科生、碩士研究生層次的試驗設(shè)計與分析的教學(xué)需要。參考文獻:1 王萬中,茆詩松. 試驗的設(shè)計與分析M. 上海:華東師范大學(xué)出版社,1997:1-78.2 周紀薌. 質(zhì)量管理統(tǒng)計方法M. 北京:中國統(tǒng)計出版社,1999:17-85.3 于洪彥. Excel統(tǒng)計分析與決策M. 北京:高等教育出版社,2001:64-65. 國家級教學(xué)名師尹貽林教授來我校做專題報告為了更好地開展我?！敖處熍嘤?xùn)工程”,人事處將陸續(xù)邀請部分專家開展一系列教學(xué)示范講座活動。6月11日,我校特邀國家級教學(xué)名師天津理工大學(xué)尹貽林教授為我校全體教師做了一場題為“做一名有專業(yè)精神的大學(xué)教師”的報告。200余名專業(yè)教師到會參加培訓(xùn)并認真聽取報告。尹貽林教授的報告分為“立志做一個有專業(yè)精神的大學(xué)老師”和“大學(xué)教師的專業(yè)精神”兩個部分。他從新形勢下教師面臨的機遇和挑戰(zhàn)出發(fā),提出了作為高校教師應(yīng)