用Excel解决经典“鸡兔问题”的五种方法

“鸡兔问题”是一道古典数学问题， <-- 段落关注点 -->源自我国古代四、五世纪的数学著作《孙子算经》。 <-- 该句已结束 -->算经卷下第三十一题为：“今有雉、兔同笼， <-- 段落关注点 -->上有三十五头， <-- 段落关注点 -->下有九十四足。 <-- 该句已结束 -->问雉、兔各几何?”原著的解法为：“上署头， <-- 段落关注点 -->下置足。 <-- 该句已结束 -->半其足， <-- 段落关注点 -->以头除足， <-- 段落关注点 -->以足除头， <-- 段落关注点 -->即得。 <-- 该句已结束 -->”具体解法即：分别列出总头数(35)和总足数(94)， <-- 段落关注点 -->总足数除以二， <-- 段落关注点 -->再减去总头数(94÷2-35)， <-- 段落关注点 -->得到兔数为12， <-- 段落关注点 -->总头数减去兔数35-12得到鸡数为23。 <-- 该句已结束 -->

鸡兔问题本身并不难， <-- 段落关注点 -->使用2元1次方程组的消元算法， <-- 段落关注点 -->可以很快得到答案。 <-- 该句已结束 -->我们可以尝试着利用Excel提供的各种计算工具来进行计算， <-- 段落关注点 -->不仅别有趣味， <-- 段落关注点 -->而且还会加深对Excel功能的综合掌握， <-- 段落关注点 -->对于讲授Excel的教师而言， <-- 段落关注点 -->则是典型的一题多解的素材。 <-- 该句已结束 -->

一、 利用IF函数试探求解

如图1， <-- 段落关注点 -->创建一个二维表， <-- 段落关注点 -->假设鸡数B2为要求解的单元格， <-- 段落关注点 -->将鸡兔的总头数和脚数分别写入D2和D3单元格， <-- 段落关注点 -->利用已知条件在其他单元格中写入公式：因兔头数=总头数-鸡头数， <-- 段落关注点 -->故在C2单元格中写入=D2-B2;鸡脚数=鸡头数*2， <-- 段落关注点 -->故B3单元格写入=B2*2;兔脚数=兔头数*4， <-- 段落关注点 -->故C3单元格写入=C2*4。 <-- 该句已结束 -->

接下来我们在任意其他单元格输入一个判断公式(本例中使用F1单元格)， <-- 段落关注点 -->公式内容为=IF(D3=B3+C3,"正解!",IF(D3>B3+C3,"高了","低了"))。 <-- 该句已结束 -->公式的本质是判断鸡脚数+兔脚数与总脚数之间的关系， <-- 段落关注点 -->如果判断表达式D3=B3+C3结果为True， <-- 段落关注点 -->就意味着我们已经得到了正确答案。 <-- 该句已结束 -->

最后在B2中输入35以内的任意整数进行试探求解。 <-- 该句已结束 -->如果输入的数值高于正解， <-- 段落关注点 -->判断单元格F1会提示“高了”， <-- 段落关注点 -->若数值小于正解则提示“低了”， <-- 段落关注点 -->用户根据提示再继续输入其他一个数字， <-- 段落关注点 -->直到输入了正确答案23， <-- 段落关注点 -->F1单元格会显示“正解!”。 <-- 该句已结束 -->

这种方法比较直观， <-- 段落关注点 -->但是非常笨拙， <-- 段落关注点 -->需要人工干预。 <-- 该句已结束 -->即使用户聪明地使用二分法试探， <-- 段落关注点 -->也需要多次输入才能解决问题， <-- 段落关注点 -->对于更庞大的问题， <-- 段落关注点 -->这种解法几乎是不可行的。 <-- 该句已结束 -->

利用Excel的规划求解功能， <-- 段落关注点 -->我们可以利用计算机高速计算的特性求解鸡兔问题。 <-- 该句已结束 -->如果用户的“工具”菜单中没有“规划求解”菜单项， <-- 段落关注点 -->可以选择“工具”à“加载宏”， <-- 段落关注点 -->在“加载宏”对话框中选中“规划求解”并按下“确定”(如图 5)， <-- 段落关注点 -->此后在“工具”菜单就可以看到“规划求解”功能了。 <-- 该句已结束 -->

新建一个工作表， <-- 段落关注点 -->单元格B1为总脚数， <-- 段落关注点 -->输入公式=2*B3+4*B4;B2为总头数， <-- 段落关注点 -->输入公式=2*B3+4*B4， <-- 段落关注点 -->B3和B4单元格用于显示计算鸡数和兔数的结果， <-- 段落关注点 -->暂时留空。 <-- 该句已结束 -->为求直观友好， <-- 段落关注点 -->可分别在A1、A2、A3、A4单元格中输入文字提示：“总脚数”、“总头数”、“鸡数”和“兔数”。 <-- 该句已结束 -->如图 6所示。 <-- 该句已结束 -->

然后选择“工具”菜单下的“规划求解”， <-- 段落关注点 -->在“规划求解参数”对话框中， <-- 段落关注点 -->设置目标单元格$B$1等于固定值94(即总脚数等于94)， <-- 段落关注点 -->将可变单元格设置为$B$3:$B$4， <-- 段落关注点 -->即欲求解的鸡数B3和兔数B4。 <-- 该句已结束 -->在“约束”栏中， <-- 段落关注点 -->添加三个约束条件：$B$2=35(即总头数等于35)， <-- 段落关注点 -->$B$3和$B$4为整数， <-- 段落关注点 -->如图 7所示。 <-- 该句已结束 -->

规划求解参数设置完毕后， <-- 段落关注点 -->按下“求解”按钮， <-- 段落关注点 -->Excel很快地给出了正确答案：鸡数B3单元格为35， <-- 段落关注点 -->兔数B4单元格为11.99999975。 <-- 该句已结束 -->求解结果中兔数为小数形式， <-- 段落关注点 -->是规划求解过程中的计算误差。 <-- 该句已结束 -->因为本问题是二元一次方程组求解， <-- 段落关注点 -->属于线性问题， <-- 段落关注点 -->用户可以在规划求解参数对话框中按下“选项”按钮， <-- 段落关注点 -->选中“采用线性模型”即可在计算结果中正确显示整数。 <-- 该句已结束 -->

使用规划求解， <-- 段落关注点 -->可以利用计算机高速计算的特点对复杂问题建模求解， <-- 段落关注点 -->同样的思路也适合于解决多解的方程问题。

四、 利用矩阵函数， <-- 段落关注点 -->线性代数思路解决问题

鸡兔问题是二元一次方程组， <-- 段落关注点 -->可以利用线性代数方法进行求解。 <-- 该句已结束 -->根据题意列出二元一次方程组为：

其中x为鸡数， <-- 段落关注点 -->y为兔数。 <-- 该句已结束 -->根据方程组由线性代数方法可以列出如下两组矩阵A和矩阵B：

设所求矩阵为x， <-- 段落关注点 -->则方程组转化为AX=B， <-- 段落关注点 -->即

。 <-- 该句已结束 -->

根据如上的数学分析， <-- 段落关注点 -->我们可以利用Excel中矩阵函数的独特功能， <-- 段落关注点 -->使用矩阵逆函数MINVERSE对矩阵A求逆， <-- 段落关注点 -->然后利用矩阵乘函数MMULT对矩阵A的逆矩阵和B矩阵进行乘法运算， <-- 段落关注点 -->得到的结果矩阵就是方程组的解。 <-- 该句已结束 -->

具体做法如下(参见图 8)：

1. 在A1:B2区域中输入矩阵A的数值， <-- 段落关注点 -->在D1:D2区域中输入矩阵B的数值;

2. 求取A的逆矩阵。 <-- 该句已结束 -->选中B4:C5单元格， <-- 段落关注点 -->输入数组公式=MINVERSE(A1:B2)， <-- 段落关注点 -->确认时必须按下Ctrl+Shift+Enter组合键;

3. 求取A的逆矩阵和B矩阵的乘积。 <-- 该句已结束 -->选中B7:B8单元格， <-- 段落关注点 -->输入数组公式=MMULT(B4:C5,D1:D2)， <-- 段落关注点 -->确认时必须按下Ctrl+Shift+Enter组合键;

4. B7、B8单元格的计算结果为23和12， <-- 段落关注点 -->即鸡数为23， <-- 段落关注点 -->兔数为12。 <-- 该句已结束 -->

使用矩阵函数的方法， <-- 段落关注点 -->在本质上是解决数学中的n元一次方程组的问题， <-- 段落关注点 -->具有比较广泛的通用性。