Excel函數(shù)輔助模式下模擬雜交、測交與隨機交配實驗

林建春（浙江省溫州市洞頭區(qū)第一中學(xué) 浙江溫州 325700）

開展高中生物學(xué)模擬實驗有助于轉(zhuǎn)變學(xué)生的學(xué)習(xí)方式，促進(jìn)和豐富探究性學(xué)習(xí)，改變科學(xué)態(tài)度，并提升學(xué)生的生物學(xué)核心素養(yǎng)［1］。雜交、測交及隨機交配是高中遺傳學(xué)中最常見的3 種交配方式。因此，在進(jìn)行“模擬孟德爾雜交實驗”的基礎(chǔ)之上，可進(jìn)一步拓展測交、隨機交配模擬實驗，從而鍛煉學(xué)生的動手操作能力，增強學(xué)生的直觀認(rèn)識。

模擬實驗大數(shù)據(jù)的獲得是該實驗?zāi)芊癯晒Φ年P(guān)鍵。在浙科版必修2《生物學(xué)·遺傳與進(jìn)化》（2019年版）“模擬孟德爾雜交實驗”活動中，通過模擬10 次以上以獲取雜交實驗數(shù)據(jù)，往往很難得到接近的分離比，尤其是在模擬2 對相對性狀的雜交實驗中［2］。因此，在學(xué)生動手進(jìn)行模擬實驗的基礎(chǔ)上，急需一種能快速獲取模擬實驗數(shù)據(jù)并進(jìn)行統(tǒng)計分析的方法。

Excel 軟件自帶函數(shù)功能，可將其應(yīng)用于雜交、測交及隨機交配模擬實驗中，從而實現(xiàn)實驗大數(shù)據(jù)的獲取、分析。該方法操作簡便、快速且有效，適合在生物學(xué)實驗教學(xué)中進(jìn)行推廣與使用。

1 相關(guān)函數(shù)說明

構(gòu)建雜交、測交與隨機交配模擬實驗時，運用Excel 軟件中的函數(shù)主要有以下5 個。

1）RAND（）函數(shù)可生成介于［0，1）間的隨機數(shù)字。

2）IF（條件，甲，乙）函數(shù)為邏輯函數(shù)，若括號內(nèi)的條件得到滿足，則返回甲值；若不滿足，則返回乙值。

3）EXACT（）函數(shù)是用于測試2 個字符串是否完全相同，如果它們完全相同，則返回TRUE；否則，返回FALSE。

4）N（）函數(shù)是一個用來返回轉(zhuǎn)換為數(shù)值的函數(shù)。

5）SUMPRODUCT（）是進(jìn)行條件累積求和的函數(shù)。

2 一對相對性狀的模擬實驗

如圖1所示，在Excel 軟件里創(chuàng)建一個包含模擬實驗流程圖、模擬過程、數(shù)據(jù)統(tǒng)計與分析的界面。其中，灰色單元格表示其需要輸入數(shù)字或函數(shù)。流程圖中的信封1（雌1）與信封2（雄1）分別代表F1雜交個體，信封中的Y、y表示F1產(chǎn)生的配子種類及數(shù)目。步驟（1）與（2）模擬F1按照一定的概率產(chǎn)生某種配子，步驟（3）表示雌、雄配子隨機結(jié)合，即模擬受精作用。

圖1 構(gòu)建“1 對相對性狀的模擬實驗”Excel 界面

2.1 模擬雜交實驗 在模擬1 對相對性狀的雜交實驗中，每個信封中的Y、y數(shù)目必須相等，代表基因型為Yy的個體產(chǎn)生2 種數(shù)目相等的配子。如圖1所示，信封里Y、y數(shù)量均為20，教師可根據(jù)教學(xué)實際情況，更改信封中配子的數(shù)量。各灰色單元格中輸入的公式如下所示。

①J3=IF（RAND（）<$C$8／（$C$8+$D$8），"Y"，"y"）

②K3=IF（RAND（）<$F$8／（$F$8+$G$8），"Y","y"）

③L3=J3&K3

④C22=SUM（C23∶C26）

⑤C23=SUMPRODUCT（N（EXACT（L∶L，"YY"）））

⑥C24=SUMPRODUCT（N（EXACT（L∶L，"Yy"）））

⑦C25=SUMPRODUCT（N（EXACT（L∶L，"yY"）））

⑧C26=SUMPRODUCT（N（EXACT（L∶L，"yy"）））

⑨C27=C23&"∶"&SUM（C24∶C25）&"∶"&C26

公式①中的“$C$8／（$C$8+$D$8”是指Y配子在全部配子中所占的比例，由于在1 對相對性狀的雜交實驗中，單元格C8 與D8 的數(shù)值相等（均為20），所以$C$8／（$C$8+$D$8）=0.5。整條公式①代表的含義為若RAND（）隨機函數(shù)生成的數(shù)字小于0.5［即生成的隨機數(shù)在（0，0.5）之間］則輸出Y；若大于等于0.5［即生成的隨機數(shù)在（0.5，1）之間］，則輸出y，與模擬基因型Yy個體隨機產(chǎn)生Y、y配子的情況完全一致。公式②與公式①的含義完全相同。公式③中的連字符“&”用于合并文本，單元格L3 中的公式“=J3&K3”是將J3 和K3單元格中所生成的字符串進(jìn)行合并，即模擬受精過程。公式④是將單元格C23 至C26 進(jìn)行求和。公式⑤是指在L 這一列單元格中，遇到與“YY”字符串完全相同的，則EXACT（）函數(shù)輸出為TRUE，N（）函數(shù)計為1，SUMPRODUCT（）函數(shù)進(jìn)行累加，從而實現(xiàn)對“YY”字符串的數(shù)量統(tǒng)計。公式⑥、⑦、⑧與公式⑤類似。公式⑨是對YY，Yy和yy的數(shù)量以比值的形式輸出（注：Yy與yY在統(tǒng)計時均合計為Yy的數(shù)量）。

進(jìn)行模擬時，選中J3、K3 和L3，下拉至1 002行（即模擬1 000 次），便可得到模擬結(jié)果及對應(yīng)的數(shù)據(jù)分析。應(yīng)用上述方法，連續(xù)模擬5 次，所獲得的數(shù)據(jù)如表1所示。5 次模擬結(jié)果與孟德爾1 對相對性狀的雜交實驗分離比YY∶Yy∶yy=1∶2∶1均極其接近，說明模擬效果佳，方法可行、有效。

表1 Excel 函數(shù)輔助模式下模擬實驗數(shù)據(jù)

2.2 模擬測交實驗 欲進(jìn)行模擬1 對相對性狀的測交實驗（例如Yy與yy進(jìn)行測交），教師只需將上述圖1信封2 內(nèi)Y數(shù)量改為0 即可，代表信封2 里只能產(chǎn)生y一種類型的配子。各灰色單元格中的公式不需要改變。需進(jìn)行說明的是，由于信封2 中Y數(shù)量為0，則K3 單元格公式中的“F$8／（$F$8+$G$8）” 結(jié)果為0，整條公式的含義為若RAND（）隨機函數(shù)生成的數(shù)字小于0，則輸出Y，若大于等于0，則輸出y。實際上，由于RAND（）隨機函數(shù)生成的數(shù)字不可能小于0，所以只能輸出y，即信封2 中所產(chǎn)生的配子只有y一種。

模擬時選中J3、K3 和L3，下拉至1 002 行（模擬1 000 次），即可得到模擬結(jié)果。應(yīng)用上述方法進(jìn)行連續(xù)模擬5 次，所獲得的模擬實驗數(shù)據(jù)如表1所示，每次模擬結(jié)果與實際分離比1∶1十分接近。

2.3 模擬隨機交配實驗 模擬1 對相對性狀的隨機交配實驗同樣也可利用上述方法。假設(shè)在一個群體中，基因型為YY、Yy的雌、雄個體比值均為2∶1，且雌、雄個體間進(jìn)行隨機交配，求隨機交配后代基因型及比例。首先，通過計算發(fā)現(xiàn)雌、雄群體各自產(chǎn)生2 種類型配子且比例均為Y∶y=5∶1。緊接著，在信封1 與信封2 里輸入Y、y的數(shù)量與5∶1成比例即可，而單元格中的公式無需改變，例如Y數(shù)量為25，y數(shù)量為5。此時，K3 單元格公式中的“F$8／（$F$8+$G$8）”結(jié)果為，整條公式1）的含義為若RAND（） 隨機函數(shù)生成的數(shù)字小于［即生成的隨機數(shù)在（0，）之間］，則輸出Y；若大于等于［即生成的隨機數(shù)在（，1）之間］，則輸出y，即生成Y配子概率為，生成y配子概率為。公式②與公式①情況相同。模擬5 次后，模擬實驗數(shù)據(jù)如表1所示，結(jié)果與理論計算比YY∶Yy∶yy=25∶10∶1十分接近。

3 2 對相對性狀的模擬實驗

在1 對相對性狀的模擬實驗基礎(chǔ)之上，與Excel軟件中繼續(xù)構(gòu)建2 對相對性狀的模擬實驗（圖2）。

圖2 構(gòu)建“2 對相對性狀的模擬實驗”Excel 界面

3.1 模擬雜交實驗 模擬2 對相對性狀的雜交實驗中，每個信封中的2 種基因數(shù)必須相等，代表基因型為YyRr的雜交個體?；疑珕卧裰休斎氲墓饺缦滤荆ü秸f明可參照“2.1 模擬雜交實驗”）。

①P3=IF（RAND（）<$C$8／（$C$8+$D$8），"Y"，"y"）（說明：R3 單元格輸入的公式與此類似）

②）Q3=IF（RAND（）<$F$8／（$F$8+$G$8），"R"，"r")（說明：S3 單元格輸入的公式與此類似）

③T3=P3&Q3

④U3=R3&S3

⑤V3=P3&R3&Q3&S3

⑥C21=SUM（C22∶C25，G22∶G25，J22∶J25，M22∶M25）

⑦C22=SUMPRODUCT（N（EXACT（V∶V，"YYRR"）））（說明：其他進(jìn)行基因型數(shù)量統(tǒng)計的單元格中輸入的公式與此類似）

⑧H27=SUM（C22∶C25，G22∶G25，J22）&"∶"&SUM（J23∶J25）&"∶"&SUM（M22∶M24）&"∶"&M25

由于2 對相對性狀的雜交實驗后代總共出現(xiàn)16 種組合類型，模擬幾十次至幾百次很難得到接近的性狀分離比。因此，模擬操作時，選中P3 至V3下拉至5 002 行（即模擬5 000 次），以獲得更多的模擬數(shù)據(jù)，從而使實驗結(jié)果更加精確。應(yīng)用上述方法連續(xù)模擬5 次（表1），每次模擬結(jié)果與孟德爾2對相對性狀的雜交實驗分離比9∶3∶3∶1 接近。

3.2 模擬測交實驗 開展模擬2 對相對性狀的測交實驗（例如進(jìn)行YyRr與yyrr測交），教師需將上述圖2中信封2 內(nèi)Y數(shù)量改為0、信封4 內(nèi)R數(shù)量改為0 即可，代表信封2 只能產(chǎn)生y、信封4 只能產(chǎn)生r一種類型的配子。各灰色單元格中的公式不需要改變。選中P3 至V3 下拉至5 002行（即模擬5 000 次），可得到模擬結(jié)果及數(shù)據(jù)分析。連續(xù)模擬5 次后的數(shù)據(jù)如表1所示，5 次模擬結(jié)果與孟德爾2 對相對性狀的測交實驗分離比1∶1∶1∶1 均十分接近。

4 總結(jié)

在開展“模擬孟德爾雜交實驗”活動時，需在學(xué)生親自實踐的基礎(chǔ)上，進(jìn)一步利用Excel 軟件中的函數(shù)獲取實驗大數(shù)據(jù)并進(jìn)行分析，從而使模擬實驗結(jié)果更加接近真實分離比，發(fā)揮模擬實驗應(yīng)有的教學(xué)價值和地位。此外，還可進(jìn)一步進(jìn)行實驗拓展，即進(jìn)行模擬測交、隨機交配實驗，從而增強學(xué)生的動手操作能力，培養(yǎng)生物學(xué)核心素養(yǎng)。

基于Excel 函數(shù)輔助模式下構(gòu)建的雜交、測交與隨機交配模擬實驗，具有操作簡便、結(jié)果有效等優(yōu)勢，適合應(yīng)用于高中生物學(xué)實驗教學(xué)中。此外，在所構(gòu)建的Excel 軟件界面中，一次性輸入公式，即可滿足雜交、測交與隨機交配模擬實驗，減少了公式編輯的時間，因而具有高效性。當(dāng)然，該方法也有不足，例如不適合應(yīng)用于具有2 對相對性狀的多個基因型個體之間進(jìn)行隨機交配模擬實驗。