運(yùn)用ＥＸＣＥＬ函數(shù)進(jìn)行區(qū)組試驗(yàn)設(shè)計(jì)

摘要：區(qū)組設(shè)計(jì)是科學(xué)研究和加工工藝中經(jīng)常用到的試驗(yàn)設(shè)計(jì)方法，本文通過對區(qū)組設(shè)計(jì)中的配對設(shè)計(jì)和完全區(qū)組設(shè)計(jì)實(shí)質(zhì)的分析，提出了運(yùn)用EXCEL進(jìn)行配對設(shè)計(jì)和完全區(qū)組設(shè)計(jì)的方法。結(jié)果認(rèn)為靈活利用Excel的隨機(jī)函數(shù)如RAND、RANDOMBETWEEN等和排序按鈕可以快速進(jìn)行區(qū)組試驗(yàn)設(shè)計(jì)。

關(guān)鍵詞：配對設(shè)計(jì)；區(qū)組設(shè)計(jì)；EXCEL函數(shù)；局部控制

中圖分類號(hào)：TP391文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)：1009-3044(2008)30-0705-03

Block Experimental Design by EXCEL Function

LI Xue-bin1， YU Xiao-Ling2

(1.College of Animal Science， Henan Institute of Science and Technology， Xinxiang 453003， China;2.College of Food Science， Henan Institute of Science and Technology， Xinxiang 453003， China)

Abstract: Block experimental design is one of the recommendations design in science experiment and produce research. In this article， block experimental design including paired design with EXCEL function is introduced. Finally， we thinks that the function such as RAND， RANDOMBETWEEN etc. in Excel can be used to do block experimental design rapidly.

Key words: paired design; block experimental design; EXCEL function; local control

1 引言

在科學(xué)研究和加工工藝試驗(yàn)中，當(dāng)試驗(yàn)環(huán)境或試驗(yàn)單位差異較大時(shí)，僅根據(jù)重復(fù)和隨機(jī)化兩原則進(jìn)行設(shè)計(jì)不能將試驗(yàn)環(huán)境或試驗(yàn)單位差異所引起的變異從試驗(yàn)誤差中分離出來，因而試驗(yàn)誤差較大，試驗(yàn)的精確性與檢驗(yàn)的靈敏度較低。為解決這一問題，在試驗(yàn)環(huán)境或試驗(yàn)單位差異大時(shí)，可根據(jù)局部控制的原則，將整個(gè)試驗(yàn)環(huán)境或加工環(huán)境分成若干個(gè)小環(huán)境或小組，在小環(huán)境或小組內(nèi)使非處理因素盡量一致，這樣的試驗(yàn)設(shè)計(jì)就是區(qū)組試驗(yàn)設(shè)計(jì)[1，3]。配對設(shè)計(jì)和完全區(qū)組設(shè)計(jì)就是兩種最重要的區(qū)組設(shè)計(jì)。雖然在有些資料中有利用EXCEL進(jìn)行配對設(shè)計(jì)試驗(yàn)結(jié)果的統(tǒng)計(jì)分析方法，然而在生物統(tǒng)計(jì)學(xué)教材和專業(yè)的EXCEL書籍中均沒有見到關(guān)于運(yùn)用EXCEL進(jìn)行區(qū)組設(shè)計(jì)的介紹[4]，本文將對如何運(yùn)用EXCEL快速進(jìn)行配對設(shè)計(jì)和完全區(qū)組試驗(yàn)設(shè)計(jì)加以介紹，從而為科學(xué)研究和加工工藝的

優(yōu)化提供參考。

2 配對試驗(yàn)設(shè)計(jì)

2.1 配對設(shè)計(jì)的實(shí)質(zhì)

配對設(shè)計(jì)是區(qū)組設(shè)計(jì)中最簡單的一種設(shè)計(jì)，其是將性質(zhì)相同或性質(zhì)相近的對象配成一對，然后將同一對內(nèi)的兩個(gè)試驗(yàn)對象完全隨機(jī)的分到兩個(gè)不同的處理中所進(jìn)行的試驗(yàn)設(shè)計(jì)。其要求配成對子的兩個(gè)試驗(yàn)單位的初始條件盡量一致，不同對子間試驗(yàn)單位的初始條件允許有差異。每一個(gè)對子就是試驗(yàn)處理的一個(gè)重復(fù)，然后將配成對子的兩個(gè)試驗(yàn)單位隨機(jī)地分配到兩個(gè)處理組中[1-3]。

2.2 配對試驗(yàn)設(shè)計(jì)的Excel實(shí)現(xiàn)

配對試驗(yàn)設(shè)計(jì)可以借助Excel的隨機(jī)抽樣函數(shù)非常快速的實(shí)現(xiàn)對子內(nèi)處理的隨機(jī)化。例如，現(xiàn)有供試單元18個(gè)，分別將條件相同的兩個(gè)試驗(yàn)單位（如來源相同的原材料）配成對子，共9對，編號(hào)為1-9號(hào)。試用隨機(jī)方法將每個(gè)對子中的兩個(gè)供試單元分到甲、乙兩個(gè)處理組中。具體操作步驟如下：

方法一：

1）新建一個(gè)Excel表格，在A2單元格輸入“對子編號(hào)。

2）選擇B2和C2單元格，分別輸入個(gè)體編號(hào)1和2。

3）按下shift鍵，選擇B2和C2單元格，拖動(dòng)C2單元格右下角的填充柄至J2 單元格，填好對子編號(hào)，這個(gè)編號(hào)也是對子中第一個(gè)試驗(yàn)單位的編號(hào)，第二個(gè)試驗(yàn)單位不編號(hào)。哪個(gè)為第一個(gè)試驗(yàn)單位，哪個(gè)為第二個(gè)試驗(yàn)單位可以任意設(shè)定。

4）在A3單元格輸入“隨機(jī)數(shù)字”字符。A4單元格輸入“對子中第一個(gè)試驗(yàn)單位組別”字符。A5單元格輸入“對子中第二個(gè)試驗(yàn)單位組別”字符。

5）選擇B3到J3單元格，設(shè)定單元格格式，數(shù)值保留0位有效數(shù)字見圖1，確定。

6）選擇B3 單元格，輸入公式“=RAND()*100”，見圖2。

7）回車，拖動(dòng)B3 單元格右下角的填充柄至J3單元格，見圖3。

8）選擇B3到J3 單元格，點(diǎn)擊右鍵復(fù)制，再點(diǎn)擊右鍵，選擇性粘貼設(shè)為數(shù)值型。

9）將奇數(shù)定為甲處理，偶數(shù)定為乙處理。這樣便將對子中編有號(hào)碼的第一個(gè)試驗(yàn)單位隨機(jī)分配了處理。

10）未編號(hào)者的第二個(gè)試驗(yàn)單位隨機(jī)分配處理結(jié)果相反。最后配對設(shè)計(jì)結(jié)果見圖4。

方法二：

1）新建一個(gè)Excel表格，在A2 單元格輸入“對子編號(hào)。

2）選擇B2和 C2 單元格，分別輸入個(gè)體編號(hào)1和2。

3）按下shift鍵，選擇B2和 C2 單元格，拖動(dòng)C2 單元格右下角的填充柄至J2 單元格，填好個(gè)體編號(hào)。

4）選擇A3 單元格，輸入“隨機(jī)數(shù)字”字符。A4 單元格輸入“對子中第一個(gè)試驗(yàn)單位組別”字符。A5 單元格輸入“對子中第二個(gè)試驗(yàn)單位組別”字符。

5）選擇B3 單元格，輸入公式“= INT（RAND()*10）+1”或“=RANDBETWEEN（1，2）”，本例用“=RANDBETWEEN（1，2）” 圖5。

6）回車，拖動(dòng)B3 單元格右下角的填充柄至J3 單元格。

7）選擇B3到J3 單元格，點(diǎn)擊右鍵復(fù)制，再點(diǎn)擊右鍵選擇性粘貼，選擇性粘貼設(shè)為數(shù)值型，結(jié)果見圖6。

8）選擇B4到J4 單元格，點(diǎn)擊右鍵，選擇性粘貼設(shè)為數(shù)值型，結(jié)果見圖7。

9）選擇B5 單元格，輸入公式“=IF(B4=2，1，2)”，回車，拖動(dòng)B5 單元格右下角的填充柄至J5 單元格見圖8。

10）將對子中試驗(yàn)單位行中的1定為甲處理，2數(shù)定為乙處理。配對設(shè)計(jì)結(jié)果見圖9。

需要說明的是，也可在上述第7步之后直接在B4單元格輸入“=IF(B3=2，\"乙\"，\"甲\")”，向后推動(dòng)填充柄至J4 單元格，在B5單元格輸入“=IF(B3=2，\" 甲\"，\"乙\")”，向后推動(dòng)填充柄至J5 單元格，所得結(jié)果與圖9相同，并且操作更簡便。

3 完全區(qū)組試驗(yàn)設(shè)計(jì)

3.1 完全區(qū)組設(shè)計(jì)的實(shí)質(zhì)

將整個(gè)試驗(yàn)環(huán)境或試驗(yàn)單位分成若干個(gè)小環(huán)境（如用一個(gè)生產(chǎn)流水線，同一臺(tái)試驗(yàn)儀器等）或小組（如同一個(gè)操作人員或同一批加工材料），在小環(huán)境或小組內(nèi)使非處理因素盡量一致。每個(gè)比較一致的小環(huán)境或小組，稱為單位組（或區(qū)組）。它是根據(jù)局部控制的原則，將各單位組的試驗(yàn)動(dòng)物隨機(jī)分配到各處理組，這種設(shè)計(jì)稱為隨機(jī)單位組設(shè)計(jì)，也即隨機(jī)區(qū)組設(shè)計(jì)。也就是說，當(dāng)試驗(yàn)規(guī)模擴(kuò)大到一定程度后，完全隨機(jī)設(shè)計(jì)有時(shí)很難做到使試驗(yàn)條件一致，此時(shí)，可以把整個(gè)試驗(yàn)分成幾個(gè)部分，每一部分內(nèi)進(jìn)行整套的試驗(yàn)，這每一個(gè)部分也就是區(qū)組，在同一區(qū)組內(nèi)的供試對象和試驗(yàn)條件應(yīng)盡可能一致，而區(qū)組之間則可有一定的差異，且不必控制；實(shí)際上，隨機(jī)區(qū)組設(shè)計(jì)是配對設(shè)計(jì)的擴(kuò)展和延伸，配對設(shè)計(jì)中的對子即相當(dāng)于區(qū)組設(shè)計(jì)中的區(qū)組；但當(dāng)試驗(yàn)的處理在兩個(gè)以上時(shí)，如果存在某種對試驗(yàn)指標(biāo)有較大影響的干擾因素（如試驗(yàn)單元的差別、試驗(yàn)空間、時(shí)間、測試儀器、操作人員等條件存在明顯差異），根據(jù)配對試驗(yàn)可以通過局部控制降低試驗(yàn)誤差的原理，將配對試驗(yàn)設(shè)計(jì)中的對子擴(kuò)大為組，每組試驗(yàn)單元基本一致，不同組之間在該干擾因素方面有差別，組內(nèi)不同的試驗(yàn)單元要隨機(jī)實(shí)施不同的處理，這樣的設(shè)計(jì)即為隨機(jī)區(qū)組設(shè)計(jì)[1-3]，每個(gè)區(qū)組能安排所有處理數(shù)的區(qū)組設(shè)計(jì)為完全區(qū)組設(shè)計(jì)。

3.2 完全區(qū)組設(shè)計(jì)的Excel實(shí)現(xiàn)

由于在區(qū)組試驗(yàn)設(shè)計(jì)中，同一區(qū)組組內(nèi)的試驗(yàn)單元應(yīng)隨機(jī)實(shí)施不同的處理，因此區(qū)組設(shè)計(jì)也可借助Excel的隨機(jī)抽樣函數(shù)快速的實(shí)現(xiàn)區(qū)組內(nèi)處理的隨機(jī)化，從而實(shí)現(xiàn)區(qū)組試驗(yàn)設(shè)計(jì)。

例如，5種食品添加劑分別以A1、A2、A3、A4、A5表示，供試單元20個(gè)有4個(gè)來源Ⅰ~Ⅳ。供試單元分別按編號(hào)為：1-5號(hào)為第Ⅰ組，6-10號(hào)為第Ⅱ組，11-15號(hào)為第Ⅲ組，16-20為第Ⅳ組。試按隨機(jī)區(qū)組設(shè)計(jì)將供試單元分組。具體操作步驟如下：

2）選擇B5 單元格，輸入“=rand()”，見圖11。

3）回車，選擇B5，拖動(dòng)B5單元格右下角的填充柄至U5單元格，填好隨機(jī)數(shù)字。

4）選擇B5到U5單元格，點(diǎn)擊右鍵復(fù)制，再點(diǎn)擊右鍵，選擇性粘貼設(shè)為數(shù)值型，見圖12。

5）將5種添加劑編號(hào)按順序填入添加劑行對應(yīng)的區(qū)組列中，見圖13。

6）新建一個(gè)Excel工作表，將上述表2區(qū)組試驗(yàn)設(shè)計(jì)的內(nèi)容復(fù)制到這個(gè)新的Excel工作表中，并刪除供試單元編號(hào)行，結(jié)果見圖14。

7）單擊數(shù)據(jù)按鈕，選擇排序，見圖15。

8）單擊排序，出現(xiàn)排序?qū)υ捒颍瑔螕暨x項(xiàng)按鈕，出現(xiàn)排序選項(xiàng)對話框。方向選擇按行排序，見圖16。確定。

9）將排序?qū)υ捒蛑兄饕P(guān)鍵字設(shè)定為行1，次要關(guān)鍵字設(shè)定為行2，確定，即出現(xiàn)圖17結(jié)果。

10）將添加劑一行結(jié)果復(fù)制粘貼到圖12表中，如圖18。

11）刪除隨機(jī)數(shù)字行，并將區(qū)組編號(hào)相同的單元格合并，便得到區(qū)組試驗(yàn)設(shè)計(jì)結(jié)果，見圖19。這說明在第一個(gè)區(qū)組內(nèi)1-5供試單元應(yīng)分別用A2、A4、A3、A1和A5種添加劑，余類推。

4 其他區(qū)組設(shè)計(jì)

廣義的區(qū)組設(shè)計(jì)還包括拉丁方試驗(yàn)設(shè)計(jì)和不完全區(qū)組設(shè)計(jì)，拉丁方設(shè)計(jì)是從橫行和直列兩個(gè)方向進(jìn)行雙重局部控制，使得橫行和直列兩向皆成單位組，是比隨機(jī)單位組設(shè)計(jì)多一個(gè)單位組的設(shè)計(jì)。完全區(qū)組設(shè)計(jì)的每個(gè)區(qū)組能安排所有處理數(shù)，可使所有處理間的比較能在同區(qū)組內(nèi)進(jìn)行，但有時(shí)由于實(shí)驗(yàn)條件的限制，區(qū)組的容量小于處理數(shù)，即區(qū)組并不能容納全部的試驗(yàn)水平，這樣就不能將所有的處理在同區(qū)組內(nèi)比較。這樣的區(qū)組就稱為“不完全區(qū)組”。需要采用不完全隨機(jī)區(qū)組設(shè)計(jì)[5]。這兩種較為復(fù)雜的區(qū)組設(shè)計(jì)在由于篇幅所限，本文不再討論。

5小結(jié)

1）配對設(shè)計(jì)實(shí)施處理的實(shí)質(zhì)是將性質(zhì)相同或性質(zhì)相近的試驗(yàn)單位配成一對，然后將同一對內(nèi)的兩個(gè)試驗(yàn)單位完全隨機(jī)的分到兩個(gè)不同的處理中所進(jìn)行的試驗(yàn)。靈活利用Excel的隨機(jī)函數(shù)如RAND、RANDOMBETWEEN等就可以快速地進(jìn)行科學(xué)研究中的配對設(shè)計(jì)。

2）完全區(qū)組設(shè)計(jì)實(shí)質(zhì)是將整個(gè)試驗(yàn)環(huán)境分成若干個(gè)小環(huán)境或小組，在小環(huán)境或小組內(nèi)使非處理因素盡量一致。每一獨(dú)立供試單位在單位組內(nèi)隨機(jī)實(shí)施不同的處理，因此靈活利用Excel的隨機(jī)函數(shù)如RAND、RANDOMBETWEEN等和排序按鈕，可以快速地進(jìn)行產(chǎn)品加工工藝優(yōu)化中完全隨機(jī)區(qū)組設(shè)計(jì)。

參考文獻(xiàn)：

[1] 謝莊，賈青.獸醫(yī)統(tǒng)計(jì)學(xué)[M].北京：高等教育出版社，2005:193-195

[2] 明道緒.生物統(tǒng)計(jì)附試驗(yàn)設(shè)計(jì)[M].3版.北京：中國農(nóng)業(yè)出版社，2002:227-228

[3] Gerry P， Michael J. Experimental Design and Data Analysis for Biologists[M]. Cambridge: Cambridge University Press，2002:157-164.

[4] 吳權(quán)威，呂琳琳.Excel 2003函數(shù)與統(tǒng)計(jì)應(yīng)用實(shí)務(wù)[M].北京:中國鐵道出版社，2005:109-253.

[5] 楊德.試驗(yàn)設(shè)計(jì)與分析[M].北京:中國農(nóng)業(yè)出版社，2002:95-113.