第10章　Excel建模

第10章　Excel建模

如果你已经阅读了本书前面所介绍的知识，那么恭喜你，你已经具备了Excel管理建模的最基本的知识了，下面结合实际的项目进行学习和演练。

在本书的这一部分，使大家熟悉几个工作中的实际管理模型，详细地介绍其开发思路和过程，通过本部分的学习，你将惊喜地发现，即使不会写VBA代码，照样能开发出能够解决管理实践问题的实用软件！

下面介绍的例子虽然具有某种行业性的特点，但是并不涉及具体行业的专业知识，因此你大可不必恐惧。每一个例子都经过必要的简化，目的是让读者熟悉Excel模型的开发思路和具体方法，而不会迷失在繁杂的行业知识中。

10.1　用Excel制作楼房销售记录表

假设我们是售楼处的销售人员，一栋楼房有很多层，每一层有很多单元，每销售一个单元需要在Excel表格中做一个记录，如何让这些数据可视化，让我们直观地了解一栋楼的哪些单元已被经销售或预订，以及这些已经被销售或预订的单元在整栋楼的分布情况呢？

Excel条件格式和公式函数能够帮我们的忙。该模型有两个工作表：“商品房楼层平面示意图”和“商品房售房登记明细表”，如图10-1所示。

图10-1

建立模型的目的是能够把“商品房售房登记明细表”中的信息可视化地展现在“商品房楼层平面示意图”中，并且能够随着数据的不断录入而即时更新，以便我们快速了解商品房销售情况。

制作该模型用到的Excel技术有Excel公式和函数，以及使用公式和函数作为判断条件的Excel条件格式，制作步骤如下。

（1）先制作如图10-1所示的“商品房楼层平面示意图”工作表。

（2）将“商品房售房登记明细表”工作表的D列设置名称为“SOLD”（“居室位置”列，即商品房单元号）。

（3）在工作表“商品房楼层平面示意图”中，由单元格C2开始，选中单元格区域“C2:M8”再选择“开始→条件格式→新建规则”命令，在弹出的对话框中选择“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”处针对当前活动单元格输入公式“=NOT(ISNA(VLOOKUP(C2,SOLD,1,0)))”，然后单击“格式”按钮，在弹出“设置单元格格式”对话框中，把单元格背景设置为红色。

（4）连续单击两次“确定”按钮后，关闭所有对话框。

条件格式中的公式“=NOT(ISNA(VLOOKUP(C2,SOLD,1,0)))”是针对当前活动单元格C2编写的。其含义是：用VLOOKUP函数查找当前单元格C2中的内容是否在自定义名称SOLD中出现过，如果出现过，即“NOT(ISNA())”，则对当前单元格施加条件格式。

虽然我们只是针对活动单元格设置的条件格式，但是，当我们设置完Excel条件格式，单击“确定”按钮时，Excel会把刚才设置的条件格式判断逻辑应用到我们所选中的区域内的所有单元格。应用过程符合“公式相对引用绝对引用”的规则。

现在，我们可以在“商品房售房登记明细表”中，增加或减少数据，查看动态效果。

10.2　用Excel制作准时交货模型

在制造型企业里，产品准时交货率是衡量工厂绩效的一个重要指标。Excel可以帮我们制作产品准时交货模型。

如图10-2所示是一个动态监控当前出货量已经满足多少周订单需求的模型，虽然这个表格看起来比较简单，经过适当改造，完全可以在实践中发挥巨大作用！

图10-2

在图10-2中：

（1）B2:K3单元格是某种产品在将来各个周别的订单需求数量；

（2）A3单元格里面的数值是工厂当前累计（Week to Date）出货数量；

（3）M2:V3单元格是模型计算的中间结果，也可以称做“辅助列”，在模型完成后这些列是需要隐藏起来的。

（4）X2:AG3单元格是模型计算的最终结果，0表示该周的客户订单需求已经满足，负数表示该周的订单需求没有满足。

M3单元格中的公式为：=$A$3-SUM($B$3:B3)，即：（当前累计出货量）-（第00周到当前周的客户订单需求总数量），注意公式的相对引用和绝对引用设置，然后把该公式复制到N3:V3。

此时，我们看到，在“中间结果”部分的“第00周”单元格下方对应的数字“26”表示当前出货量（A3单元格中的数字为“29”）除了能满足当前周（第00周）订单（3个）外还多出26个产品；在中间结果部分的“第01周”单元格下方的数字“22”表示当前出货量（A3单元格中的数字为：29）除了能满足当前周（第00周）订单（3个）加上当前周订单的下一周（第01周）订单数量（4个）之和外还剩22个（29-3-4=22）产品，以此类推。

X3单元格中的公式为=IF(M3>=0,0,-B3)，其含义是：如果累计出货量（A3单元格）能够满足“第00周”到当周的累计客户订单需求（M3>=0），则公式所在的当前单元格为0（表示当前周的客户订单需求已经满足），否则给当前周订单数量加一个负号，表示当前周订单需求没有完全满足。最后把该公式复制到Y3:AG3单元格区域。

为了方便地观察数据的变化，这是在A3单元格的“当前出货量”设置了一个滚动条控件，此时，可以滑动滚动条改变A3单元格中的当前出货量的数字，查看该模型的动态计算结果，在实战中，A3单元格中的数字可以通过Excel的“数据→获取外部数据”功能实时更新。

滚动条的设置方法如下：选择“开发工具→插入→表单控件→滚动条”命令，在工作表的合适位置画一个滚动条控件，然后右键单击滚动条控件，在弹出的快捷菜单中选择“设置控件格式”命令。在弹出“设置对象格式”对话框中进行如图10-3所示的设置。设置完成单击“确定”按钮关闭对话框，这时用鼠标拖动滚动条即可达到控制单元格数值变化的目的。

图10-3

10.3　用Excel教宝宝识字

Excel不仅仅在工作中能发挥作用，在生活中也能给我们带来乐趣。下面我们用Excel制作一个教宝宝识字的小软件。最后完成的效果如图10-4所示，当我们用鼠标移动滚动条上的滑块时，左边区域的绿色的字母会随之变换。

图10-4

这里用到的技术有：

（1）Excel合并单元格功能；

（2）Excel查找和引用公式功能；

（3）Excel窗体控件。

首先在J1:J26单元格区域输入字母A到Z（当然也可以是其他字符）。这就是我们的“宝宝爱识字”这个Excel小工具的字库。然后再次选中J1:J26单元格区域，在Excel公式栏左侧的名称栏里输入“myLetters”，这样我们就给J1:J26单元格区域取一个容易记忆的名称“myLetters”。

选择单元格区域A1:H26，选择“开始→对齐方式→合并单元格”命令。合并后的单元格区域将作为我们的显示字符用的“大屏幕”，如图10-5所示。

图10-5

选中刚刚合并的单元格区域，在其中输入公式“=INDEX(myLetters,1,1)”，该公式的作用是把myLetters单元格区域中的第一行的第一列中的内容取到当前所在的单元格，现在合并单元格区域里显示字符“A”。我们知道：通过改变公式“=INDEX(myLetters,1,1)”中第二个参数的数值，可以取不同的字母到当前的合并单元格，如图10-6所示。

图10-6

调整合并单元格区域中的字符的大小和颜色。然后选择单元格K1作为控制公式“=INDEX(myLetters,1,1)”中第二个参数的“控制单元格”，在K1单元格中输入数字“1”，并把“大屏幕”中的公式修改为“=INDEX(myLetters,K1,1)”。这样我们通过修改“控制单元格”K1中的数字就达到控制大屏幕中显示字符的目的，如图10-7所示。

图10-7

把K1单元格中的数字改为“2”，我们发现“大屏幕”中的数字变成了“B”，如图10-8所示。但是在“控制单元格”中输入数字的方式改变“大屏幕”中字符太麻烦，下面可以借助Excel控件来完成这个任务。

图10-8

选择“开发工具→控件”功能组中的“滚动条”控件。在Excel工作表的合适的位置画一个合适大小的滚动条。

注意：如果你在Excel 2010界面中没有找到“开发工具”标签，请打开“Excel选项”对话框，在右侧的“自定义功能区”下拉框中选择“主选项卡”，在下方的主选项卡内容列表中勾选“开发工具”复选框即可。

下面要用滚动条控制我们的“控制单元格”中的内容，右键单击滚动条控件，在弹出的快捷菜单中选择“设置控件格式”命令，弹出“设置对象格式”对话框。

在弹出的“设置对象格式”对话框中，设置滚动条的最小值和最大值分别为1和26，即滚动条的滑块滚动到最上边的时候，代表数字1；滚动条的滑块滚动到最下边的时候，代表数字26。在对话框中的“单元格链接”文本框中输入“$K$1”（或用文本框右边的拾取按钮选择K1单元格），表示我们要用滚动条控制K1单元格中的数值，如图10-9所示。

图10-9

单击“设置对象格式”对话框下方的“确定”按钮，关闭对话框。

至此我们完成了我们的“宝宝爱识字”小工具的制作。其基本逻辑是，用Excel滚动条控件控制“控制单元格”K1中数字的变化，“控制单元格”K1中的数字又控制“大屏幕”中公式第二个参数的变化，公式运算结果返回不同的字母。

现在，让我们用鼠标单击滚动条上下两端的箭头或者滑动滚动条上的滑块看一看效果，如图10-10所示。怎么样？效果还不错吧！

图10-10

10.4　Excel图片放大器

熟练掌握Excel的各种基本技术，并对这些技术加以灵活地组合，我们就可以创造性地制作出各种意想不到的效果来。

下面使用Excel的各种内置功能，制作一个“图片放大器”。通过该工具的制作过程的演示，让大家了解以下内容

（1）Excel的动态图片链接功能；

（2）命名公式；

（3）OFFSET函数的用法。

如图10-11中，单元格区域O1:S7处有一个图片，这就是我们用来“放大”的图片。首先选定L1单元格作为“控制单元格”，用来控制函数中的参数。选择“公式→定义名称→新建名称”命令，弹出“新建名称”对话框。在该对话框中的“名称”文本框中，给我们即将定义的公式取一个名字“myPic”；在该对话框的“范围”后面的文本框中，保持默认值为“工作簿”。

图10-11

在该对话框的“引用位置”后面的文本框中输入我们自定义的公式：“=OFFSET($O$1,0,0,$L$1,$L$1)”，如图10-11所示。该公式的含义是我们需要得到一个新的矩形单元格区域的地址引用，该新的矩形单元格区域的右上角的位置是：以单元格$O$1为参照点（始发点），向下移动0个单元格，向右移动0个单元格所得到的新的位置作为该新区域的左上角的位置，该新的矩形单元格区域的高度为$L$1单元格中数字所指定的高度，该新的矩形单元格区域的宽度为$L$1单元格中数字所指定的宽度。

就这样，我们通过改变myPic这个公式中的第4个和第5个参数$L$1单元格中的数值就能得到一个动态的矩形单元格区域引用，也就是该动态的矩形单元格区域引用的大小由$L$1来控制。当$L$1中的数值为1时，myPic的大小就是$O$1本身；当$L$1中的数字为7时，myPic的大小能够完整地把MrExcel这个图案包括进来。

下面的任务是制作一个“相框”，任选一个空白单元格（比如F4单元格），单击鼠标右键，在弹出的快捷菜单中选择“复制”命令。然后单击“开始→剪贴板→粘贴”按钮下方的下拉箭头，在弹出下拉列表中的“其他粘贴选项”组中单击“链接的图片”按钮。此时在工作表中粘贴了一个和刚才所复制的单元格（F4单元格）动态关联的图片，我们暂且称之为“动态相框”，如图10-12所示。

图10-12

调整“相框”到合适的大小，在保持相框选中状态下，观察公式栏，我们发现相框里的公式为：“=$F$4”，表示该“相框”和单元格$F$4建立了动态链接。在单元格$F$4的边框范围内的任何图案和字符都会动态地反应到这个“相框”中，如图10-13所示。

图10-13

由此可见，如果我们把“相框”中的公式，变为$O$1，就会把在单元格$O$1的边框范围内的任何图案和字符都会动态地反应到这个“相框”中；如果我们把“相框”中的公式变为$O$1:$S$7，就会把在单元格$O$1:$S$7的边框范围内的任何图案和字符都会动态地反应到这个“相框”中，而O$1:$S$7中恰恰是我们放置MrExcel图案的位置。

如果将“相框”中的公式变为一个动态的单元格区域地址，那么随着公式返回的单元格区域地址的变化，“相框”中的内容也会跟着变化。

现在我们用鼠标选中相框，在公式栏中输入前面的命名公式“=myPic”，按Enter键确认，因为控制myPic这个公式的第四个和第五个参数的初始值为10（L1单元格中的数值），myPic返回得到的单元格区域的引用为$O$1:$X$10，因此，我们发现MrExcel整个图案都包含到“相框”中去了，如图10-14所示。

图10-14

这样，我们通过改变$L$1单元格中的数字，就可以控制在“相框”中显示的单元格区域的大小，从而达到图片放大和缩小的目的。

为了更容易地改变“控制单元格”$L$1中的数值，我们增加一个Excel滚动条窗体控件，选择“开发工具→控件”功能组中的“滚动条”控件。在Excel工作表的合适的位置插入一个合适大小的滚动条，滚动条的具体制作方法请参见上面的案例。

可能有的读者会问，我们为什么不直接在“相框”输入公式“=OFFSET($O$1,0,0,$L$1,$L$1)”，而非要绕一个弯子，给公式取一个名字后在“相框”输入公式的名字呢？这是因为Excel图片链接功能目前不支持直接在“相框”输入公式，只能通过命名公式实现。不信你可以试一下直接在“相框”输入公式，你会得到一个错误信息。

最后，为了使工作表更整洁，取消勾选“视图→显示/隐藏”中的“网格线”复选框，这样，工作表中不再有网格线，整个画面看起来更清晰了。

10.5　Excel模型的“3层1表”结构

看过了前面介绍的几个Excel应用的案例，我们可能会觉得：Excel建模也不是很复杂！其实，单纯地Excel建模技术本身并不复杂，每个人都能学会，所以我们完全不必恐惧。Excel建模重点关心的问题：如何有效地结合自己的行业知识，把工作中的问题有效地转化到Excel模型中。

对于一般Excel用户来说，Excel模型可大可小，只要能够帮助我们解决工作中的实际问题，减少手工重复性的劳动，都可以叫做模型。但如果你想建立规范一点的模型，还需要遵循一些规则和约定，这会为我们将来模型的维护和扩展带来相当大的便利。

在Excel建模技术中，比较通行思路的逻辑是把模型分为三层结构，即数据层、分析层、展示层。在长期的建模实践中，Excel建模技术的三层结构的描述还不是很精确，我在这里提出了Excel建模的“三层一表”的概念。

“三层一表”是指规划Excel模型的一般要遵守的整体结构，“三层”是指Excel模型的数据层、分析层、展示层；“一表”是指Excel模型的“参数表”。

●　数据层：Excel模型所需要的数据放置于此，Excel模型所需的数据不仅仅是指那些复制到Excel模型里面静态的数据，还可以是通过Excel的“获取外部数据”功能导入的来自数据库或者其他数据源可随时刷新的动态数据。

●　分析层：Excel模型在这里对数据进行各种分析和处理，用到的Excel功能可能有Excel数据透视表、Excel模拟运算表、各种Excel汇总函数、Excel规划求解工具等。

●　展示层：在这里，Excel模型对数据分析处理的结果以对用户友好的界面形式进行展示，在这一层面经常使用的Excel技术有图表、表格、条件格式、Excel控件等。

●　参数表：是指用来集中管理Excel模型中用到的各种参数的工作表。比如在展示层中用来与用户互动的下拉框中的下拉选项、在多个工作表使用的公司LOGO图案等。

Excel建模的“三层一表”中的每一个元素可以根据实际情况的需要分别占用一个或者多个工作表。为了具体阐明Excel建模的“三层一表”的概念，下面举例说明。

假设我们现在有某个公司各个销售部门在某一年度的详细销售数据，该数据可能是从公司的业务数据库中自动导入到Excel中来的。现在的任务是：设计一个Excel模型，让老板能够方便地浏览各个销售部门在各个月份的销售数据，以便作为制订下一年度销售计划的参考。

外部数据导入Excel中后，如图10-15所示，我们把存放外部数据的工作表取名为“数据源”，其实就是我们Excel建模“三层一表”结构中的数据层。

图10-15

下面我们对数据层的原始数据进行分析。在原始数据的基础上，建立数据透视表，以“销售部门”作为行标签，以“月份”作为列标签，对“销售额”进行汇总。在这里我们只是对原始数据进行了示意性的分析汇总，在实际工作中遇到的情况可能比较复杂，应用到的Excel功能也可能比较多。

在数据透视表的下方，我们用GETPIVOTDATA函数把数据透视表中的数据取出存放到一个新的标准Excel表格中，之所以用GETPIVOTDATA函数把数据透视表中的数据抓取到一个新的Excel表格中，是因为数据透视标的结构容易发生变化，如果Excel模型的“展示层”直接以数据透视表中的数据制作各种图表，可能在数据透视表结构发生变化了时取得错误的数据。而使用GETPIVOTDATA函数先把数据抓到一个新的表格中，再基于新的表格制作图表，那么当数据透视表结构发生变化时，图表要么取得新的正确的数据，要么取不到数据，而不会取得错误的数据。

选中图10-16中的B13单元格，然后输入“=”号，用鼠标选取B4单元格，这时在B13单元格中自动生成了如下公式：

图10-16

=GETPIVOTDATA("销售额",$A$2,"销售部门","市场1部","月份","一月")

注意：如果没有自动生成GETPIVOTDATA函数，需要依次单击“选项→数据透视表→选项”命令勾选“生成GetPivotData”选项前面的检查框，然后重复上述操作。

有关GETPIVOTDATA函数的详细介绍，请参照本书前面“获取数据透视表中的数据”一节。在这里，为了让GETPIVOTDATA函数更加灵活，我们对该函数中的参数做了修改，这样GETPIVOTDATA函数就可以按照新表格的行标签和列标签取得数据透视表中的数值了。修改后的公式为：

=GETPIVOTDATA("销售额",$A$2,"销售部门",$A17,"月份",M$12)

然后把修改后的公式复制到新表格中所有需要公式的区域，如图10-17所示。

图10-17

为了后继数据分析和处理的方便，我们给新的表格的各个部分命名，新的表格行标签部分命名为“行标签”；新的表格列标签部分命名为“列标签”；整个新的表格命名为“数据表”。名称以及其所对应的单元格区域请参看图10-18下部所示的列表。

图10-18

至此，我们的数据分析和处理阶段就算告一段落。下面开始数据展示阶段。在Excel模型中名字为“展示”的工作表中绘制一个如图10-19所示的表格结构。在该工作表的A2单元格中，我们要设置一个数据有效性下拉列表，当用户选择任意一个销售部门时，能够把“分析层”的数据抓取到该表格中用于驱动基于该表格的图表变化。

图10-19

为了演示Excel模型的“三层一表”结构的需要，我们把数据有效性的下拉列表中的选项放置在Excel模型的“参数表”中。

注意：在真实的管理建模问题中，可能会有很多参数，因此，对这些参数的集中管理是非常重要的。选择“数据→数据有效性→设置→序列”命令，打开“数据有效性”对话框，该对话框设置如图10-19所示。（如果你是用的是Excel 2010以前的版本，必须给参数列表命名一个名称，才能在“数据有效性”对话框的“来源”处引用不在当前工作表中的数据序列。）

数据有效性设置完毕，关闭对话框后，效果如图10-20所示。