每天学一点Excel2010 (36)——模拟分析之鸡兔同笼(每天懂一点人情世故)

049 模拟分析之鸡兔同笼

鸡兔同笼问题，源自我国古代四、五世纪的数学著作《孙子算经》，卷下第三十一题为：“今有雉、兔同笼，上有三十五头，下有九十四足。问雉、兔各几何?”，用数学语言表达就是：

鸡数+兔数=35

鸡足数+兔足数=94

我们已经学习了公式的构成，下面结合这一简单示例讲解一下。

1）新建一个工作簿。“模拟分析”功能在“数据”选项卡的“数据工具”区，鼠标悬停后给出的说明“共有三种：

2）在sheet1中我们先使用方案管理器，A1单元格输入“鸡数”，A2单元格输入“兔数”，A3单元格输入“总足数”。

3）我们假定B1中的鸡数为变量，在B2中输入“=35-B1”，在B3中输入“=2*B1+4*B2”。

4）点击B1，在“模拟分析”中的“方案管理器”，在对话框中选择“添加”，在“方案名中”输入“0”，确认“可变单元格”是B1（不是的话可以输入或选择），“备注”随便填，然后确定。

5）在“方案变量值”对话框，先按插值法从0～35，每隔5只看一下各方案的结果。输入“0”后点“添加”（如点了“确定，再点“添加”即可）

6）重复4）、5）步，依次添加“方案名”和“可变单元格的值”为5、10、15、20、25、30、35，确定。

方案管理器

7）如图，共添加了8种方案，在对话框可以“添加”、“删除”、“编辑”、“合并”方案，选择一个方案，按下面的“显示”，B1:B3会出现该方案的结果，我们点击“摘要”，确定选择了“方案摘要”单选框，并且“结果单元格”输入B2:B3后确定。

方案摘要

8）自动新建了一个“方案摘要”工作表，对应每一个鸡数，列出了相应的兔数和总足数，从趋势可以判断出，94的总足数对应的鸡数在20～25之间。

9）回到Sheet1重复4）、5）步，依次添加“方案名”和“可变单元格的值”为21、22、23、24，确定后查看摘要，发现鸡数23的方案满足题目要求，兔12只，足数94。

最终满足题目要求的方案

10）实际生活中我们出门旅游乘坐交通工具便可以确定多种方案，我们可以把飞机、汽车、轮船、出租、骑行、步行、休息等所花费的时间和费用等逐一列出，可以通过对比找出省时、省钱的方案。高德地图导航路线一般给出三个方案，包括里程、时间、红绿灯个数。

11）回到Sheet1，单击B3单元格，点击“模拟分析”、“单变量求解”，在对话框中确保“目标单元格”为B3，“目标值”输入94，“可变单元格”输入B1，确定。

单变量求解

12）电脑自动算出结果，但这种方法仅适用于单变量，即一元方程。如果我们把鸡数和兔数都当成变量时，单变量求解便无能为力了。

13）现在我们使用模拟运算表，堪称暴力破解，在B4单元格输入0。再次点击B4，点击“开始选项卡”中“编辑”区的“填充”、“序列”，选择“列”单选框，终止值输入35后确定。

向下填充列

14）同样，在C3输入0。再次点击C3，如图向右填充行序列到35。

向右填充行

15）在名称框输入B3:AL39后回车，选择矩形区域，点击“数据”选项卡的“模拟分析”、“模拟运算表”，“输入引用行的单元格”中输入B1，“输入引用列的单元格”中输入B2，确定。

模拟运算表

16）所谓模拟运算表就是在选定的表格区域，行单元格取所有行值，列单元格取所有列值，按照行列交叉处的公式，把所有组合都算一遍。我们把B3单元格的公式改成“=B1+B2”看看。

17）两个条件同时满足，需要用到逻辑与的关系，我们把B3的公式改成“=and(B1+B2=35,2*B1+4*B2=94)”。回车看看，用条件格式或筛选把TRUE找出来吧。

18）留两道思考题，看上面三种方法能不能用？

（待续）

本文原作者为陈成美，转载请注明：出处！如该文有不妥之处，请联系站长删除，谢谢合作~

原创文章，作者：陈成美，如若转载，请注明出处：http://www.kaicen.cn/20220514321485.html