Excel函数辅助模式下模拟杂交、测交与随机交配实验

林建春（浙江省温州市洞头区第一中学 浙江温州 325700）

开展高中生物学模拟实验有助于转变学生的学习方式，促进和丰富探究性学习，改变科学态度，并提升学生的生物学核心素养［1］。杂交、测交及随机交配是高中遗传学中最常见的3 种交配方式。因此，在进行“模拟孟德尔杂交实验”的基础之上，可进一步拓展测交、随机交配模拟实验，从而锻炼学生的动手操作能力，增强学生的直观认识。

模拟实验大数据的获得是该实验能否成功的关键。在浙科版必修2《生物学·遗传与进化》（2019年版）“模拟孟德尔杂交实验”活动中，通过模拟10 次以上以获取杂交实验数据，往往很难得到接近的分离比，尤其是在模拟2 对相对性状的杂交实验中［2］。因此，在学生动手进行模拟实验的基础上，急需一种能快速获取模拟实验数据并进行统计分析的方法。

Excel 软件自带函数功能，可将其应用于杂交、测交及随机交配模拟实验中，从而实现实验大数据的获取、分析。该方法操作简便、快速且有效，适合在生物学实验教学中进行推广与使用。

1 相关函数说明

构建杂交、测交与随机交配模拟实验时，运用Excel 软件中的函数主要有以下5 个。

1）RAND（）函数可生成介于［0，1）间的随机数字。

2）IF（条件，甲，乙）函数为逻辑函数，若括号内的条件得到满足，则返回甲值；若不满足，则返回乙值。

3）EXACT（）函数是用于测试2 个字符串是否完全相同，如果它们完全相同，则返回TRUE；否则，返回FALSE。

4）N（）函数是一个用来返回转换为数值的函数。

5）SUMPRODUCT（）是进行条件累积求和的函数。

2 一对相对性状的模拟实验

如图1所示，在Excel 软件里创建一个包含模拟实验流程图、模拟过程、数据统计与分析的界面。其中，灰色单元格表示其需要输入数字或函数。流程图中的信封1（雌1）与信封2（雄1）分别代表F1杂交个体，信封中的Y、y表示F1产生的配子种类及数目。步骤（1）与（2）模拟F1按照一定的概率产生某种配子，步骤（3）表示雌、雄配子随机结合，即模拟受精作用。

图1 构建“1 对相对性状的模拟实验”Excel 界面

2.1 模拟杂交实验 在模拟1 对相对性状的杂交实验中，每个信封中的Y、y数目必须相等，代表基因型为Yy的个体产生2 种数目相等的配子。如图1所示，信封里Y、y数量均为20，教师可根据教学实际情况，更改信封中配子的数量。各灰色单元格中输入的公式如下所示。

①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 的数值相等（均为20），所以$C$8／（$C$8+$D$8）=0.5。整条公式①代表的含义为若RAND（）随机函数生成的数字小于0.5［即生成的随机数在（0，0.5）之间］则输出Y；若大于等于0.5［即生成的随机数在（0.5，1）之间］，则输出y，与模拟基因型Yy个体随机产生Y、y配子的情况完全一致。公式②与公式①的含义完全相同。公式③中的连字符“&”用于合并文本，单元格L3 中的公式“=J3&K3”是将J3 和K3单元格中所生成的字符串进行合并，即模拟受精过程。公式④是将单元格C23 至C26 进行求和。公式⑤是指在L 这一列单元格中，遇到与“YY”字符串完全相同的，则EXACT（）函数输出为TRUE，N（）函数计为1，SUMPRODUCT（）函数进行累加，从而实现对“YY”字符串的数量统计。公式⑥、⑦、⑧与公式⑤类似。公式⑨是对YY，Yy和yy的数量以比值的形式输出（注：Yy与yY在统计时均合计为Yy的数量）。

进行模拟时，选中J3、K3 和L3，下拉至1 002行（即模拟1 000 次），便可得到模拟结果及对应的数据分析。应用上述方法，连续模拟5 次，所获得的数据如表1所示。5 次模拟结果与孟德尔1 对相对性状的杂交实验分离比YY∶Yy∶yy=1∶2∶1均极其接近，说明模拟效果佳，方法可行、有效。

表1 Excel 函数辅助模式下模拟实验数据

2.2 模拟测交实验 欲进行模拟1 对相对性状的测交实验（例如Yy与yy进行测交），教师只需将上述图1信封2 内Y数量改为0 即可，代表信封2 里只能产生y一种类型的配子。各灰色单元格中的公式不需要改变。需进行说明的是，由于信封2 中Y数量为0，则K3 单元格公式中的“F$8／（$F$8+$G$8）” 结果为0，整条公式的含义为若RAND（）随机函数生成的数字小于0，则输出Y，若大于等于0，则输出y。实际上，由于RAND（）随机函数生成的数字不可能小于0，所以只能输出y，即信封2 中所产生的配子只有y一种。

模拟时选中J3、K3 和L3，下拉至1 002 行（模拟1 000 次），即可得到模拟结果。应用上述方法进行连续模拟5 次，所获得的模拟实验数据如表1所示，每次模拟结果与实际分离比1∶1十分接近。

2.3 模拟随机交配实验 模拟1 对相对性状的随机交配实验同样也可利用上述方法。假设在一个群体中，基因型为YY、Yy的雌、雄个体比值均为2∶1，且雌、雄个体间进行随机交配，求随机交配后代基因型及比例。首先，通过计算发现雌、雄群体各自产生2 种类型配子且比例均为Y∶y=5∶1。紧接着，在信封1 与信封2 里输入Y、y的数量与5∶1成比例即可，而单元格中的公式无需改变，例如Y数量为25，y数量为5。此时，K3 单元格公式中的“F$8／（$F$8+$G$8）”结果为，整条公式1）的含义为若RAND（） 随机函数生成的数字小于［即生成的随机数在（0，）之间］，则输出Y；若大于等于［即生成的随机数在（，1）之间］，则输出y，即生成Y配子概率为，生成y配子概率为。公式②与公式①情况相同。模拟5 次后，模拟实验数据如表1所示，结果与理论计算比YY∶Yy∶yy=25∶10∶1十分接近。

3 2 对相对性状的模拟实验

在1 对相对性状的模拟实验基础之上，与Excel软件中继续构建2 对相对性状的模拟实验（图2）。

图2 构建“2 对相对性状的模拟实验”Excel 界面

3.1 模拟杂交实验 模拟2 对相对性状的杂交实验中，每个信封中的2 种基因数必须相等，代表基因型为YyRr的杂交个体。灰色单元格中输入的公式如下所示（公式说明可参照“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"）））（说明：其他进行基因型数量统计的单元格中输入的公式与此类似）

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

由于2 对相对性状的杂交实验后代总共出现16 种组合类型，模拟几十次至几百次很难得到接近的性状分离比。因此，模拟操作时，选中P3 至V3下拉至5 002 行（即模拟5 000 次），以获得更多的模拟数据，从而使实验结果更加精确。应用上述方法连续模拟5 次（表1），每次模拟结果与孟德尔2对相对性状的杂交实验分离比9∶3∶3∶1 接近。

3.2 模拟测交实验 开展模拟2 对相对性状的测交实验（例如进行YyRr与yyrr测交），教师需将上述图2中信封2 内Y数量改为0、信封4 内R数量改为0 即可，代表信封2 只能产生y、信封4 只能产生r一种类型的配子。各灰色单元格中的公式不需要改变。选中P3 至V3 下拉至5 002行（即模拟5 000 次），可得到模拟结果及数据分析。连续模拟5 次后的数据如表1所示，5 次模拟结果与孟德尔2 对相对性状的测交实验分离比1∶1∶1∶1 均十分接近。

4 总结

在开展“模拟孟德尔杂交实验”活动时，需在学生亲自实践的基础上，进一步利用Excel 软件中的函数获取实验大数据并进行分析，从而使模拟实验结果更加接近真实分离比，发挥模拟实验应有的教学价值和地位。此外，还可进一步进行实验拓展，即进行模拟测交、随机交配实验，从而增强学生的动手操作能力，培养生物学核心素养。

基于Excel 函数辅助模式下构建的杂交、测交与随机交配模拟实验，具有操作简便、结果有效等优势，适合应用于高中生物学实验教学中。此外，在所构建的Excel 软件界面中，一次性输入公式，即可满足杂交、测交与随机交配模拟实验，减少了公式编辑的时间，因而具有高效性。当然，该方法也有不足，例如不适合应用于具有2 对相对性状的多个基因型个体之间进行随机交配模拟实验。