用Excel解决经典鸡兔问题的五种方法.docx
《用Excel解决经典鸡兔问题的五种方法.docx》由会员分享，可在线阅读，更多相关《用Excel解决经典鸡兔问题的五种方法.docx（34页珍藏版）》请在冰豆网上搜索。
用Excel解决经典鸡兔问题的五种方法
用Excel解决经典鸡兔问题的五种方法
天极yesky
作者：
文飞原创
“鸡兔问题”是一道古典数学问题，源自我国古代四、五世纪的数学著作《孙子算经》。
算经卷下第三十一题为：
“今有雉、兔同笼，上有三十五头，下有九十四足。
问雉、兔各几何?
”原著的解法为：
“上署头，下置足。
半其足，以头除足，以足除头，即得。
”具体解法即：
分别列出总头数（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单元格会显示“正解!
”。
这种方法比较直观，但是非常笨拙，需要人工干预。
即使用户聪明地使用二分法试探，也需要多次输入才能解决问题，对于更庞大的问题，这种解法几乎是不可行的。
图1利用IF函数试验求解
二、使用模拟运算表，让Excel自动给出答案
第一种方法存在的问题就是非常繁琐，需要用户干预。
为了避免用户干预，可以考虑将鸡兔问题转化为双变量模拟运算表，将鸡数和兔数设置为两个变量。
具体做法是：
1.先在A1:
D2单元格中输入参考数据如下（图2）：
图2参考数据
2.创建一个二维模拟运算表的框架，因为鸡的数目不会超过脚数/2，即鸡最多为47只，同理兔子数目不会超过94/4，即兔最多为24只。
我们用第4行表示兔的数目，用第C列表示鸡的数目。
在D4:
AA4中填充1,2,3…24等数值，在C5:
C47中填充1,2,3…47，参见图4;
3.在模拟运算表的左上方C4单元格中输入模拟运算表的公式：
=IF（2*A2+4*B2=$C$2,IF（A2+B2=$D$2,"正解","X"）,"X"），公式中的$C$2和$D$2单元格为已知的总脚数和总头数，A2和B2将作为模拟运算表的两个变量;
4.选中模拟运算表区域，即C4:
AA47区域，然后选择“数据”菜单中的“模拟运算表”菜单项，打开模拟运算表对话框（如图3）。
在对话框中，输入引用行的单元格为$A$2（即鸡数），输入引用列的单元格为$B$2（即兔数），单击“确定”按钮;
图3输入引用行和引用列的单元格
5.在模拟运算表中会显示出计算结果，在所有的运算表区域中，只有Z16单元格中显示了“正确”两字，其余单元格均为“X”，表示Z16单元格为问题的正解，查表可知，Z16单元格的兔数为12，鸡数为23（如图4）。
图4模拟运算表运算结果
这种使用模拟运算表的方法比较“另类”。
利用这种思路，不仅可以求解多元一次方程组，还可以求解多解问题。
三、使用规划求解，将苦活抛给Excel
利用Excel的规划求解功能，我们可以利用计算机高速计算的特性求解鸡兔问题。
如果用户的“工具”菜单中没有“规划求解”菜单项，可以选择“工具”à“加载宏”，在“加载宏”对话框中选中“规划求解”并按下“确定”（如图5），此后在“工具”菜单就可以看到“规划求解”功能了。
图5规划求解加载宏
新建一个工作表，单元格B1为总脚数，输入公式=2*B3+4*B4;B2为总头数，输入公式=2*B3+4*B4，B3和B4单元格用于显示计算鸡数和兔数的结果，暂时留空。
为求直观友好，可分别在A1、A2、A3、A4单元格中输入文字提示：
“总脚数”、“总头数”、“鸡数”和“兔数”。
如图6所示。
图6规划求解表
然后选择“工具”菜单下的“规划求解”，在“规划求解参数”对话框中，设置目标单元格$B$1等于固定值94（即总脚数等于94），将可变单元格设置为$B$3:
$B$4，即欲求解的鸡数B3和兔数B4。
在“约束”栏中，添加三个约束条件：
$B$2=35（即总头数等于35），$B$3和$B$4为整数，如图7所示。
图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。
图8矩阵函数求解
使用矩阵函数的方法，在本质上是解决数学中的n元一次方程组的问题，具有比较广泛的通用性。
五、使用VBA编程求解
鸡兔问题也可以编程解决。
打开菜单“工具”à“宏”à“VisualBasic编辑器”，选择VBA编辑器的“插入”à“模块”菜单，并输入如下代码：
Subchickrabbit（）
Forchick=1To35
Forrabbit=1To35
If（chick+rabbit=35）And（2*chick+4*rabbit=94）Then
MsgBox"鸡的数量为"&chick&"，兔为"&rabbit
EndIf
Nextrabbit
Nextchick
EndSub
编辑完毕后关闭VBA窗口，然后选择“工具”à“宏”，然后执行chickrabbit宏，就会弹出正确答案，如图9所示。
该程序可以做进一步的改进，例如可以改进执行方式、直接调用工作表数据、将输出结果显示在Excel单元格中等等，篇幅所限，不再赘述。
图9
本文分别使用了IF函数试探、双变量模拟运算表、规划求解、矩阵函数和VBA编程等方法对古典鸡兔问题进行求解，目的并不是求解简单数学问题的答案，而是旨在通过多种方法求解，展示Excel的多功能性和解决方式的灵活性，进一步开拓分析问题、解决问题的思路。
教你使用Excel做数据分析之回归分析方法
天极yesky
作者：
大鸟原创
在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势预测和残差分析等等。
很多专业读者遇见此类问题时往往寻求专业软件，比如在化工中经常用到的Origin和数学中常见的MATLAB等等。
它们虽很专业，但其实使用Excel就完全够用了。
我们已经知道在Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。
注：
本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘支持下加载“分析数据库”。
加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项
实例某溶液浓度正比对应于色谱仪器中的峰面积，现欲建立不同浓度下对应峰面积的标准曲线以供测试未知样品的实际浓度。
已知8组对应数据，建立标准曲线，并且对此曲线进行评价，给出残差等分析数据。
这是一个很典型的线性拟合问题，手工计算就是采用最小二乘法求出拟合直线的待定参数，同时可以得出R的值，也就是相关系数的大小。
在Excel中，可以采用先绘图再添加趋势线的方法完成前两步的要求。
选择成对的数据列，将它们使用“X、Y散点图”制成散点图。
X、Y散点图
在数据点上单击右键，选择“添加趋势线”-“线性”，并在选项标签中要求给出公式和相关系数等，可以得到拟合的直线。
给出公式和相关系数
由图中可知，拟合的直线是y=15620x+6606.1，R2的值为0.9994。
因为R2>0.99，所以这是一个线性特征非常明显的实验模型，即说明拟合直线能够以大于99.99%地解释、涵盖了实测数据，具有很好的一般性，可以作为标准工作曲线用于其他未知浓度溶液的测量。
为了进一步使用更多的指标来描述这一个模型，我们使用数据分析中的“回归”工具来详细分析这组数据。
回归工具
在选项卡中显然详细多了，注意选择X、Y对应的数据列。
“常数为零”就是指明该模型是严格的正比例模型，本例确实是这样，因为在浓度为零时相应峰面积肯定为零。
先前得出的回归方程虽然拟合程度相当高，但是在x=0时，仍然有对应的数值，这显然是一个可笑的结论。
所以我们选择“常数为零”。
“回归”工具为我们提供了三张图，分别是残差图、线性拟合图和正态概率图。
重点来看残差图和线性拟合图。
残差图、线性拟合图和正态概率图
在线性拟合图中可以看到，不但有根据要求生成的数据点，而且还有经过拟和处理的预测数据点，拟合直线的参数会在数据表格中详细显示。
本实例旨在提供更多信息以起到抛砖引玉的作用，由于涉及到过多的专业术语，请各位读者根据实际，在具体使用中另行参考各项参数，此不再对更多细节作进一步解释。
残差图是有关于世纪之与预测值之间差距的图表，如果残差图中的散点在中州上下两侧零乱分布，那么拟合直线就是合理的，否则就需要重新处理。
图表
更多的信息在生成的表格中，详细的参数项目完全可以满足回归分析的各项要求。
下图提供的是拟合直线的得回归分析中方差、标准差等各项信息。
表格
教你用单元格数据作为Excel工作簿名称
天极yesky
作者：
陈秀峰原创
在Excel中，通常用Book1、Book2……作为工作簿名称。
能不能让Excel采用我们选定的某个单元格中的数据做为工作簿名称来保存文档呢?
答案是肯定的。
1、启动Excel，执行“工具→宏→VisualBasic编辑器”命令，进入VBA编辑状态（如图1）。
图1VBA编辑
2、在左侧的“工程资源管理器”窗口中，选中“VBAproject（PERSONAL.XLS）”（个人宏工作簿）选项。
3、执行“插入→模块”命令，插入一个模块（模块1）。
4、将下述代码输入到右侧的代码编辑窗口中：
Subbaocun（）
lj=InputBox（"请输入文档保存路径"）
ActiveWorkbook.SaveAsFilename:
=lj&ActiveCell.Value&".xls"
EndSub
退出VBA编辑状态。
5、以后要保存某个工作簿文档时，先选中作为名称的字符所在的单元格（参见图2），然后执行“工具→宏→宏”命令，打开“宏”对话框（如图3）。
图2单元格
图3宏对话框
6、选中刚才编辑的宏（PERSONAL.XLS!
baocun），单击“执行”按钮，系统弹出如图4所示的对话框。
图4对话框
7、输入保存文档的路径（如“E:
\office技巧\”），单击“确定”按钮。
文档保存成功（参见图5）。
图5保存成功
注意：
如果不需要保存路径，文档将被保存到“我的文档”文件夹中。
利用宏给Excel工作簿文档自动添加密码
天极yesky
作者：
陈秀峰原创
在Excel中在给工作簿文档添加密码时，需要通过选项一个一个的设置，比较麻烦。
下面，我们利用一个自动运行的宏，让软件自动给文档添加密码。
1、启动Excel，执行“工具→宏→VisualBasic编辑器”命令，进入VBA编辑状态（如图1）。
进入VBA编辑状态
2、在左侧的“工程资源管理器”窗口中，选中“VBAproject（PERSONAL.XLS）”（个人宏工作簿）选项。
3、执行“插入→模块”命令，插入一个模块（模块1）。
4、将下述代码输入到右侧的代码编辑窗口中：
SubAuto_close（）
ActiveWorkbook.Password="123456"
ActiveWorkbook.Save
EndSub
退出VBA编辑状态。
注意：
这是一个退出Excel时自动运行的宏，其宏名称（Auto_close）不能修改。
5、以后在退出Excel时，软件自动为当前工作簿添加上密码（123456，可以根据需要修改），并保存文档。
在Excel中巧用右键拖移实现快速复制
天极yesky
作者：
李东博原创
在Excel工作表中，我们经常会将一个单元格或区域中的数据复制到另一位置。
实现复制Excel表格数据的方法有许多种，最基本的是采用“编辑”菜单或鼠标右键中的复制/粘贴命令，或者使用快捷键Ctrl+C和Ctrl+V。
其实，还有一种鲜为人知的方法，让我们可以用最省时省力的操作来实现数据的快速复制。
下面通过实例向大家介绍这一技巧。
1.例如，我们要将如图1所示的A13:
E21中的内容复制到A1:
E9处。
首先选中A13:
E21。
图1
2.移动鼠标指针到选中区域的黑色边框处，直到鼠标指针变为如图2所示的形状。
图2
3.这时按下鼠标右键拖动鼠标，当移动到A1:
E9时，松开右键，出现如图3所示菜单，单击“链接此处”。
图3
这样就实现了所选内容的快速复制，结果如图4所示。
图4
举一反三：
细心的读者一定会注意到，如图3所示的右键菜单中还有许多其它命令，都是跟复制和移动有关的，以后如果要实现这些相关操作，都可以用以上所介绍的方法来实现。
使用Excel2007完成多人协同录入工作
天极yesky
作者：
极地圣火原创
为了提高速度，可能需要多人同时处理一张Excel表格。
Excel2007为了满足这种需求，为我们提供了“共享工作簿”的功能。
通过这个功能，我们可以通过网络将一个Excel文件共享的同时供多人同时编辑这个工作薄。
而且在编辑的同时，Excel会自动保持信息不断更新。
在一个共享工作簿中，用户可以输入数据、插入行和列以及更改公式等，甚至还可以筛选出自己关心的数据，保留自己的视窗。
而且在多人同时编辑一个单元格时还可以进行冲突处理。
下面我举个例子来说明如何通过Excel2007来共享工作簿。
一、设置共享工作簿
首先让我们来启动Excel2007。
启动后，在其中输入一个“产品销售”表，如图1所示。
图1产品销售表
选择“审阅”菜单（在老版本的Excel中对应的是“工具”菜单），然后点击菜单下方的“共享工作簿”按钮，弹出用于设置共享工作簿的对话框。
运行结果如图2所示。
图2共享工作簿对话框
在共享工作簿对话框中将“允许多用户同时编辑，同时允许工作簿合并”复选框选中。
然后可以更据自己的需要进行高级设置。
高级设置的界面如图3所示。
图3高级设置
从这个界面可以看出，Excel可以管理共享工作薄的历史记录，而且还可以自动更新其它用户的变化。
在默认情况下，当前用户在保存文件时更新其它用户的修改，但是我们也可以设置在一定时间间隔内自动更新工作薄的变化。
在第3部分“用户间的修订冲突”设置区内最后设置第一项“询问保存哪些修订信息”，否则可能产生不确定的结果。
在设置完后，点击“确定”按钮后，如果当前文档未保存，系统会提示你保存该文档。
你可以将这个文档保存在一个其它人都可以访问到的地方，如一个共享文件夹。
共享工作薄后的界面如图4所示。
图4共享工作薄
在工作薄共享后，Excel上方的标题栏出现了“[共享]”提示。
二、使用共享工作薄
在设置完共享后，就可以使用多个Excel打开这个Excel文件了。
让我们使用一个Excel编辑一下这个“产品销售”表。
然后保存。
再切换到另外一个打开这个文件的Excel，按Ctrl+S保存，你会看到在这个Excel中表格内容被更新。
如图5所示。
图5被更新的界面
在更新后，会出现一个如图5所对话框，提示该文档已经被更新。
如果两个Excel同时修改同一个单元格，而且都进行保存，将会发生内容冲突。
如一个用户将0001号产品的销售数据改为18，而另一个用户将0001号产品的销售数量改为20。
在他们保存后，将出现如图6的的提示对话框。
图6冲突处理对话框
然后由用户决定是按用户1还是用户2来修改表格中的内容。
Excel不仅能处理当前的修改冲突，而且还可以查询冲突历史，并按冲突历史进行修改。
点击“审阅”菜单中的“修改”按钮菜单中的“突出显示修订”项，将显示如图7所示的对话框。
图7
可以根据这个对话框对来选择查看修订历史。
在选择完后，在每一个单元格的左上角会显示一个“小黑角”，将鼠标放在这个单元格上，将显示这个单元格的修改历史。
再点击“接收/拒绝修订”菜单项，将显示如图8所示的界面。
图8
这个对话框显示了从某个单元格从最初的值到历次所修改的值（按时间从小到大排序）。
你可以点中其中某一项以恢复到相应的历史值。
也可以通过“全部接受”和“全部拒绝”按钮恢复到最后和最初的值。
Excel2007还可以为工作薄设置密码，以防止其它用户关闭修订。
还有就是在工作薄进行共享时不能进行一些高级的操作，如图形，图表等。
用EXCEL表格帮忙绘制AUTOCAD同心圆
天极yesky
作者：
水瓶原创
在某个技术论坛上看到有朋友提到这样一个问题：
如何快速在AUTOCAD中绘制多个同心圆？
对于这个问题网友们的回答可谓是多种多样，有回答说用偏移命令的，有回答用阵列的……当然也有朋友提出了比较实际的VBA解决方案和AUTOLISP解决方案。
笔者认为，用VBA或者是AUTOLISP作为解决方案当然是非常好的方法，但是楼主既然提出了这样的问题，想必对AUTOCAD中的VBA和AUTOLISP功能了解不够，想要在短时间内学会并理解这两种编程的技巧想必是有难度的。
回想起以前曾看到过有朋友曾撰文提出可以用EXCEL帮助在AUTOCAD中绘制点，笔者不禁想到可以借助EXCEL表格来作为AUTOCAD的辅助绘制工具试试看。
首先当然要打开一个空白的EXCEL表格，在A1单元格输入AUTOCAD中的创建圆的快捷命令“C”，在B1单元格输入我们想要绘制圆的圆心，这里我们用“0,0”做为圆心，那么在第三列C1单元格当然输入的就是第一个圆的半径了，这里我们让最小圆的半径为1，这样第一行单元格的填写就完成了，如下图：
填写圆的半径
这时笔者还不敢判断这种方法是否可行，尝试一下先：
选择A1B1C1三个单元格，复制，切换到AUTOCAD中，用鼠标点击命令行，这时光标在命令行中闪烁，按Ctrl+V粘贴剪贴板内容到命令行，再看AUTOCAD绘图窗口上已经绘制好一个圆，显然这种方法是可行的。
下面的工作就是在EXCEL下面的继续输入绘制命令了，难道还要象刚才一样用键盘往里敲?
当然不必，EXCEL本来就是批量处理的专家，我们只需要在A2B2C2单元格顺次填写好第二个圆的数据，就可以借助EXCEL的填充功能下拉到第十行完成所有数据的填写：
完成所有数据的填写
这时我们再次在EXCEL中选中A1:
C10区域，按Ctrl+C复制该区域，切换到AUTOCAD中，用鼠标点击命令行，按Ctrl+V粘贴剪贴板内容到命令行，这样AUTOCAD中同心圆的绘制过程就完成了，值得注意的是，如果我们在粘贴之前不用鼠标点击命令行把AUTOCAD的焦点设定到命令行，粘贴的结果可能会把表格的图案粘贴到绘图区域。
如果绘制的同心圆更多的话，过程的区别只不过是在EXCEL中拉动填充到更多的行数而已。
从上面的这个例子我们可以看出，如果我们有比较规律的多个简单命令的输入，可以先通过EXCEL的帮助先把数据处理好，然后借助剪贴板代替我们作“命令输入”，不仅仅是绘制圆的命令可以如此，还有许多命令可以使用类似的方法，有兴趣的朋友不妨试试看。
升级会员 