☰

Excel 中的偏差随机结果

tag-icon • tag-icon microsoft-excel random-number-generator
Excel 中的偏差随机结果

在世界建设我们喜欢玩科幻主题，包括“如果有两种以上的性别会怎样”（例子这里，这里，这里）。一切都很干净有趣，而且我认为，这也是我磨练电子表格技能的机会（我使用 Excel 2003）。

我使用 X 和 Y 染色体构建了一个三性别蒙特卡罗模型的电子表格：米啤酒（YY），F女性 (XX) 和H雌雄同体（XY），使用普通孟德尔遗传。因此MF总是会产生H，MH将产生 50% 的 M 和 50% 的 H，跳频类似，并且赫赫将产生 25% 的 M、25% 的 F 和 50% 的 H。

我培育了 200 个种群，繁殖了 256 代，结果看起来很合理，除了一件事。我曾期望种群稳定在 50% H 和 25% M 和 F，但我一直得到大约 54% H 和只有 23% M 和 F。这似乎不是 Excel 错误，我用 LibreOffice 得到了类似的结果，也没有受到“缓冲”父母的性别（见下文）或初始分布的影响。

电子表格可用这里（我的个人网站，当然是通常的免责声明）我已将其记录在下面。看起来我的随机函数IF(RAND()>0.5并CHOOSE(INT(4*RAND()+1)没有按预期返回 50% 和 25%，但我不知道如何直接测试。难道我做错了什么？

代码详细信息

我将Male 编码为1，F将 emale 编码为 ，2将Hermaphrodite 编码为 ，4这样我就可以简单地添加两个父母。大多数单元格 (B6:IV205) 计算下一代并包含（这是单元格 B6）：

=CHOOSE (A5+A7,                        // sum of parents  
  -99,                                 // 1 can't happen  
  A6,                                  // 2 = MM = don't breed, copy old  
  4,                                   // 3 = MF -> H  
  A6,                                  // 4 = FF = don't breed, copy old  
  IF(RAND()>0.5,1,4),                  // 5 = MH -> M or H  
  IF(RAND()>0.5,2,4),                  // 6 = FH -> F or H  
  -99,                                 // 7 can't happen  
  CHOOSE(INT(4*RAND()+1),1,2,4,4)      // 8 = HH -> M or F or H or H  
 )

Sheet1 的第一行显示代数，第二、三和四行显示该代中每个“性别”的数量（除以 2 得到 200 的百分比）。第 5 行和第 206 行为第 6 行和第 205 行提供了（缓冲）父代。A 列根据 A2 中的值构建初始种群。

我使用条件格式将雄性设为蓝色，雌性设为粉色，雌雄同体设为绿色；使用 25% 缩放和 3 列宽度，我可以在显示器上看到整个范围。在 Sheet2 上，我使用 Sheet1 的 2-3-4 行绘制种群。

Sheet1 上所有单元格的详细信息：

A1 "gen1"  
B1:IV1 ="g"&COLUMN()  // column title  
A2 50%  // initial hermaphrodite percentage  
B2:IV2 =COUNTIF(B6:B205,"=4")/2  // hermaphrodite percentage  
A3 ="M:"&COUNTIF(A6:A205,"=1")/2  
B3:IV3 =COUNTIF(B6:B205,"=1")/2  // male percentage  
A4 ="F:"&COUNTIF(A6:A205,"=2")/2  
B4:IV4 =COUNTIF(B6:B205,"=2")/2  // female percentage  
A5 4  // lower buffer parent  
B5:IV5 =A5  // echo buffer  
A6:A205 =IF(RAND()>$A$2,IF(RAND()>0.5,1,2),4)  // initial population  
B6:IV205 =CHOOSE(A5+A7,-99,A6,4,A6,IF(RAND()>0.5,1,4),IF(RAND()>0.5,2,4),-99,CHOOSE(INT(4*RAND()+1),1,2,4,4))  
A206 4  // upper buffer parent  
B206:IV206 =A206  // echo buffer

当然，我愿意接受有关我各方面的建议作品。

答案1

我怀疑这不是 Excel 的错误，但它是正确的（至少，基于随机机会尽可能正确。）

造成这种情况的原因不是由于随机性，而是您在计算中的概率：

=CHOOSE (A5+A7,                        // sum of parents  
  -99,                                 // 1 can't happen  
  A6,                                  // 2 = MM = don't breed, copy old  
  4,                                   // 3 = MF -> H  
  A6,                                  // 4 = FF = don't breed, copy old  
  IF(RAND()>0.5,1,4),                  // 5 = MH -> M or H  
  IF(RAND()>0.5,2,4),                  // 6 = FH -> F or H  
  -99,                                 // 7 can't happen  
  CHOOSE(INT(4*RAND()+1),1,2,4,4)      // 8 = HH -> M or F or H or H  
 )

具体来说，你的最后的情况，HH -> M or F or H or H。这意味着，如果我们使用的结果，你有 50% 的机会获得 H，而 M 或 F 的机会分别为 25%。HH因此，倾斜向应该预期 H 超过 M 或 F。

我怀疑如果你把这部分改成

CHOOSE(INT(3*RAND()+1),1,2,4)

你会看到你的可能性期待，但不一定是正确的（我实际上没有做过计算。）

相关内容