Excel数据建模与应用

ModelingWeng

游客，如果您要查看本帖隐藏内容请回复

[此贴子已经被作者于2007-6-27 20:15:05编辑过]

ModelingWeng

本质上，管理学应用的关键是数学模型。简单地说，数学模型是实际问题的量化表示方法。这种表示法可以用数学表达式（方程和不等式）或者电子表格中的一系列相互关联的单元格表示。在任何情况下，数学模型的目的都是为了以严密的形式表现问题的本质。数学模型有几个优点：首先，它能够使分析者更好地理解问题。特别是，它可以帮助确定问题的范围、可能的解决方法和数据要求。第二，它使分析者能够采用半个多世纪以来开发的各种数学求解过程。这些求解过程通常非常强调计算机，但是随着今天的计算能力越来越强大和成本越来越低廉，这些求解过程通常都能够实现。最后，如果执行正确，建模过程本身通常有助于把解决方案“推销”给必须使用并最终执行该系统的人。


如前所述，数学模型是表现或者模拟现实情况的一组数学关系。有些模型只描述情况，这种模型被称为描述性模型。有些模型提出希望的动作过程，这种模型被称为指示性模型，或者优化模型。首先，我们讨论下面这个简单的数学模型示例。它一开始是描述性模型，不过后来我们把它扩展成了优化模型。





管理学已经发展成为数学模型的集合。这些模型包括各种线性规划模型（运输模型、饮食模型、最短路径模型以及其他许多模型）、存货模型、排队模型，等等。


学习具体的模型一般倾向于记忆过程，学习的可能是如何“诱使”其他的问题变成我们学过的模型。另一方面，建模是一个过程，我们把实际问题的本质抽象成模型、电子表格或其他的东西。我们不会强行把每个问题都变成已经研究得相当好的几个模型之一。相反，我们根据各个问题的特点处理它，并使用我们掌握的逻辑、分析或电子表格技术——当然，还包括根据我们先前建立的相关模型，绘制模拟图——为它建立适当的模型。

[此贴子已经被作者于2007-5-12 13:30:31编辑过]

ModelingWeng

[em12]

[此贴子已经被作者于2007-6-27 20:16:48编辑过]

ModelingWeng

学习及使用之前,请先安装Excel自带插件,

方法:安装Excel时选择"完全"安装,安装后打开Excel选择"工具"——“加载宏",全部选中后按"确定"即可完成安装,如之前未安装完全版Excel,安装"加载宏"时插入Office安装牌即可.

模型中所使用的公式可使用"工具"——"公式审核"进行跟踪.

在今天上传的模型中将使用到"工具"——"单变量求解"和"规划求解"(安装"加载宏"即可看到)

希望Excel建模可为广大开发者提供新的开发和管理理念,谢谢!

ModelingWeng

Excel提示：公式中的相对地址和绝对地址

　　在Excel公式中使用相对和绝对地址是为了方便复制。某列或者行地址前面的美元符号表示这个地址是绝对地址，在复制时不会改变。没有美元符号表示该地址是相对地址，在复制时会发生改变。一旦你在公式中选择了单元格，你就可以重复按F4键，重复相对/绝对的可能性。例如，＝B4（列和行都是相对地址）。＝$B$4（列和行都是绝对地址）。＝B$4（列是相对地址，行是绝对地址），＝$B4（列是绝对地址，行是相对地址）。

ModelingWeng

Excel工具：“公式审核”工具栏

　　“公式审核”工具栏允许你查看所选单元格的依赖单元格（哪些单元格中的公式引用这个单元格），或者指定单元格的引用单元格（在这个单元格的公式中引用了哪些单元格）。事实上，你甚至可以查看其他工作表上的依赖单元格和引用单元格。在这种情况下，审计箭头显示为虚线，并且指向一个电子表格小图标。双击虚线，就可以看到其他工作表上的依赖单元格或者引用单元格列表。这些工具对于理解其他人的电子表格的工作逻辑尤其有用。要显示这个工具栏，需要选择“工具/公式审核/显示公式审核工具栏”菜单项。

ModelingWeng

Excel函数：VLOOKUP（见CH2模型B18单元格）

VLOOKUP函数与税率表作用相似，你从一张收入和税金表中查找你们公司的调整后总收入对应的税金。要使用这个函数，需要首先创建一个垂直的查找表，按照升序把用于比较的值排列在表的左列，把输入值放在你认为数量适当的右边列中。然后VLOOKUP函数通常采用3个参数：希望与大惑不解中值比较的值、查找表范围和希望的返回值所在的列编号，左列的编号为1，紧挨它的一列编号为2，以此类推。这里，“比较”表示从表的最左列开始搜索，直到找到小于或等于第一个参数的最后一个记录项。另外还有HLOOKUP函数。它的工作方式完全相同，不过它的查找表是按行构建，而不是按列。

[此贴子已经被作者于2007-5-15 19:46:34编辑过]

ModelingWeng

Excel函数:SUMPRODUCT

　　SUMPRODUCT函数需要两个区域参数（这两个参数的大小和形状必须完全相同）。它把两个区域中的对应值的乘积相加。例如。=SUMPRODUCT(A10:B11,E12:F13）是涉及4个乘积的公式——＝A10*E12+A11*E13+B10*F12+B11*F13——的快捷方式。这个函数非常有用，特别是在涉及的区域较大时（实际上，SUMPRODUCT函数可以有两个以上的区域参数，所有参数的大小和形状必须相同，但是人们常在涉及两个区域时使用SUMPRODUCT）。

ModelingWeng

优化

　　所有优化问题都包含几个共同的要素。它们都有决策变量，即允许决策者选择其值的变量。这些变量的值直接或间接决定总成本、收入和利润等输出。实际上，公司或者机构必须了解这些变量才能正常的运营；它们决定其他所有内容。所有优化问题都有目标函数（或者简称为目标），它的值是准备优化的值——或者最大化或者最小化。最后，大多数优化问题都必须满足的限制条件。这些条件根据问题的性质，通常是物理、逻辑或者节约限制。在寻找目标最优化的决策变量的值时，可以只选择满足所有限制条件的那些值。

　　Excel把决策变量称为可变单元格。这些单元格包含的数字必须能够被自由改变；不允许它们包含公式。Excel把目标称为目标单元格。目标单元格只能有一个，它可以是利润、总成本、总行程或者其他量，而且它必须通过公式与可变单元格相关。当可变单元格改变时，目标单元格应当随之改变。最后，必须有适当的单元格和单元格公式允许施加限制条件。例如，可能会有手忙脚乱人工量不超过可用人工量的限制。在这种情况下，必须有单元格包含这两个量，通常情况下至少有一个量（可能是所用人工量）通过公式与可变单元格相关。限制条件可以以多种形式出现。一种非常常见的形式是非负数。这种限制表示可变单元格一定要包含非负数值（0或者正数）。由于实际的原因，通常都包括非负数限制条件。例如，不可以生产出负的汽车数量。

　　可变单元格包含决策变量的值。

　　目标单元格包含准备最小化或者最大化的目标。

　　限制条件对可变单元格中的值加以限制。

　　非负数限制意味着可变单元格必定包含非负数值。

　　求解优化问题基本上分为两步。第一步是开发模型。在这一步中确定决策变量是什么，目标是什么，需要哪些限制，所有内容的结合情况。如果开发的是代数模型，必须推引出正确的代数表达式。开发的是电子表格模型，则必须用适当的单元格建立所有变量的相关关系。特别是，必须保证模型包含的公式能够表达可变单元格与目标单元格的关系，并且包含施加限制的公式。优化模型的第二个步骤是优化。必须系统地选择决策变量的值，使目标尽可能大（实现最大化）或者尽可能小（实现最小化），并且满足所有的限制条件。满足所有限制条件的决策变量值的集合被称为可行解。所有可行解的集合被称为可行范围。相反，不可行解是一个限制条件都不满足的解。必须排除不可行解。我们希望可行解提供目标的最优值——最小化问题的最小值，最大化问题的最大值。这个解称为最优解。

　　可行解是满足所有限制条件的解。

　　可行范围是所有可行解的集合。

　　不可行解至少违反一个限制条件。

　　最优解是使目标最优化的可行解。

　　求最优解主要使用Excel自带插件“规划求解”（需运行“加载宏”从OFFICE安装盘安装）完成。只需要开发模型，然后告诉“规划求解”目标单元格是什么，可变单元格是什么，限制条件是什么，模型属于哪种类型（线性、整数还是非线性）。然后“规划求解”就会开始工作，使用最适合的算法寻找最优的可行解。应当意识到，如果使用反复试验的程序，即使是很聪明，工作效率很高的一个人，也需要花费若干小时、星期甚至若干年才能完成。但是，使用适当的算法，“规划求解”通常只需要几秒钏就可以求出最优解。

ModelingWeng

Excel函数：TRANSPOSE数组函数（参见（线性规划模型/生产过程模型B1818区域）

　　TRANSPOSE函数用于将一行链接到一列，或者反之；其语法形式是：＝TRANSPOSE(Range)。为了实现该函数，选中用于旋转结果的行或列区域，键入该公式，并按下Ctrl-Shift-Enter键。该函数是Excel中若干数组函数之一，函数将一次填满整个区域，而不仅仅是一个单元格。所有数组公式都要求我们选中将容纳结果的整个区域，键入该公式，然后按下Ctrl-Shift-Enter键。一旦这样做之后，我们将在公式栏上看到该公式被花括号包围。我们不应该键入这些花括号，它们只是指示数组函数的存在。